有关列表的创建其实网上已经有很多文章了，其中练习 ：利用Visual Studio 2010创建列表这篇文章个人感觉还不错，这里我强调的是对象模型来创建。在这之前我插入一点其他的东东，导入电子表格和数据表视图中的自定义列表。
创建“导入电子表格列表”
单击“导入”
单击“导入”
这些列名称不好用 我们需要修改为我们自己的列名称
修改前
修改后：
我们启用 视图的直接编辑属性
现在看看数据的编辑
这个编辑是不是很简单啊。
数据表视图中的自定义列表 这个没什么好说的，主要是数据浏览很好用，如果你和我一样用的是office64的话，会用“未安装与 Microsoft SharePoint Foundation 兼容的数据表组件”错误
需要安装“AccessDatabaseEngine.exe”来解决。
这个页面是不是很好 编辑数据啊。
SharePoint中还有一个外部列表，这里有比较详细的介绍：
现在切换到我们的正题 “关联列表” ，相信大家对Northwind数据库比较熟悉，这里我们用Categories和Products表来演示关联关系。
首先 列表的创建代码 很简单 如下：
Guid id = web.Lists.Add("Categories", string.Empty, SPListTemplateType.GenericList);
SPList list = web.Lists[id];列表 列的创建也很简单 如下：
string fieldname = list.Fields.Add("Description", SPFieldType.Text, true);
SPField field = list.Fields.GetFieldByInternalName("Description");
大家都应该知道sharepoint自定义列表由很多预定义好的列，典型的就是 ID（编号） 和Title（标题），这里Categories和Products都有自增长字段，用这个ID
来做是在好不过了。
field = list.Fields["ID"];
field.Title = "CategoryID";
field.StaticName = "CategoryID";
field.Update();还有在我们列表默认有一个 “所有项目”的视图，该视图有一个附件列，这里我们需要移除它。
SPView view = list.Views.OfType<SPView>().FirstOrDefault(x => x.DefaultView);
SPViewFieldCollection viewFields = view.ViewFields;
//附件
SPField field = list.Fields.GetFieldByInternalName("Attachments");
viewFields.Delete(field);创建Categories列表如下：
public static void CreateCategories()
{
SPSecurity.RunWithElevatedPrivileges(() =>
{
using (SPSite site = new SPSite(siteUrl))
{
using (SPWeb web = site.RootWeb)
{
bool originalupdate = web.AllowUnsafeUpdates;
web.AllowUnsafeUpdates = true;
Guid id = web.Lists.Add("Categories", string.Empty, SPListTemplateType.GenericList);
SPList list = web.Lists[id];
SPView view = list.Views.OfType<SPView>().FirstOrDefault(x => x.DefaultView);
SPViewFieldCollection viewFields = view.ViewFields;
//附件
SPField field = list.Fields.GetFieldByInternalName("Attachments");
viewFields.Delete(field);
field = list.Fields["ID"];
field.Title = "CategoryID";
field.StaticName = "CategoryID";
field.Update();
// viewFields.Add(field);
field = list.Fields.GetFieldByInternalName("Title");
field.Title = "CategoryName";
field.StaticName = "CategoryName";
field.EnforceUniqueValues = true;
field.Indexed = true;
field.Update();
string fieldname = list.Fields.Add("Description", SPFieldType.Text, true);
field = list.Fields.GetFieldByInternalName("Description");
viewFields.Add(field);
list.OnQuickLaunch = true;
view.Update();
list.Update();
//web.Update();
web.AllowUnsafeUpdates = originalupdate;
}
}
});
}
创建关联列我们需要用到SPFieldLookup对象，它需要指定关联那个列表那个列，具体实现如下：
SPList categorList = web.Lists.TryGetList("Categories");
string fieldname = list.Fields.AddLookup("CategoryID", categorList.ID, true);
SPFieldLookup categoryID = list.Fields.GetFieldByInternalName(fieldname) as SPFieldLookup;
categoryID.Required = true;
categoryID.Indexed = true;
categoryID.LookupField = "ID";// categorList.Fields["CategoryID"].InternalName;
categoryID.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.Cascade;
categoryID.Update();
viewFields.Add(categoryID);这里指定关联到Categories列表的CategoryID列（内部名称是ID），关联行为 是级联删除。 我们还可以添加依赖的列，依赖列需要指定那个关联列和查找列
fieldname = list.Fields.AddDependentLookup("CategoryName", categoryID.Id);
SPFieldLookup categoryName = (SPFieldLookup)list.Fields.GetFieldByInternalName("CategoryName");
categoryName.LookupField = "Title";// categorList.Fields["标题"].InternalName;
viewFields.Add(categoryName);
categoryName.Update();Products列表创建代码 如下：
public static void CreateProducts()
{
SPSecurity.RunWithElevatedPrivileges(() =>
{
using (SPSite site = new SPSite(siteUrl))
{
using (SPWeb web = site.RootWeb)
{
bool originalupdate = web.AllowUnsafeUpdates;
web.AllowUnsafeUpdates = true;
Guid id = web.Lists.Add("Products", string.Empty, SPListTemplateType.GenericList);
SPList list = web.Lists[id];
SPView view = list.Views.OfType<SPView>().FirstOrDefault(x => x.DefaultView);
SPViewFieldCollection viewFields = view.ViewFields;
//Remove Attachments
SPField field = list.Fields.GetFieldByInternalName("Attachments");
viewFields.Delete(field);
field = list.Fields.GetFieldByInternalName("ID");
field.Title = "ProductID";
field.StaticName = "ProductID";
field.Update();
// viewFields.Add(field);
field = list.Fields.GetFieldByInternalName("Title");
field.Title = "ProductName";
field.StaticName = "ProductName";
field.EnforceUniqueValues = true;
field.Indexed = true;
field.Update();
SPList categorList = web.Lists.TryGetList("Categories");
string fieldname = list.Fields.AddLookup("CategoryID", categorList.ID, true);
SPFieldLookup categoryID = list.Fields.GetFieldByInternalName(fieldname) as SPFieldLookup;
categoryID.Required = true;
categoryID.Indexed = true;
categoryID.LookupField = "ID";// categorList.Fields["CategoryID"].InternalName;
categoryID.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.Cascade;
categoryID.Update();
viewFields.Add(categoryID);
fieldname = list.Fields.AddDependentLookup("CategoryName", categoryID.Id);
SPFieldLookup categoryName = (SPFieldLookup)list.Fields.GetFieldByInternalName("CategoryName");
categoryName.LookupField = "Title";// categorList.Fields["标题"].InternalName;
viewFields.Add(categoryName);
categoryName.Update();
fieldname = list.Fields.Add("UnitPrice", SPFieldType.Number, true);
SPFieldNumber numberfield = list.Fields[fieldname] as SPFieldNumber;
numberfield.DisplayFormat = SPNumberFormatTypes.TwoDecimals;
viewFields.Add(numberfield);
field.Update();
list.OnQuickLaunch = true;
view.Update();
list.Update();
//web.Update();
web.AllowUnsafeUpdates = originalupdate;
}
}
});
}
现在列表已经创建好了，我们需要相应的代码来做删除操作，这里Categories原本可以直接删除，可是Products关联它的，所以必须先删除Products列表才能删除Categories，而Products列表中有关联列，默认是不能直接删除该列表，所以在删除列表前必须先删除相应的关联列，并且在删除关联列前也必须先删除依赖列，具体实现如下：
public static void DeleteList(string listname)
{
SPSecurity.RunWithElevatedPrivileges(() =>
{
using (SPSite site = new SPSite(siteUrl))
{
using (SPWeb web = site.RootWeb)
{
SPList list = web.Lists.TryGetList(listname);
if (list == null) return;
SPFieldLookup[] lookupFields = list.Fields.OfType<SPFieldLookup>().Where(c => c.IsRelationship).ToArray();
if (lookupFields != null && lookupFields.Count()>0)
{
foreach (SPFieldLookup field in lookupFields)
{
SPFieldLookup[] depentFields = list.Fields.OfType<SPFieldLookup>().Where(c => c.IsDependentLookup && c.PrimaryFieldId == field.Id.ToString()).ToArray();
foreach (SPFieldLookup item in depentFields)
{
item.Delete();
}
field.Delete();
}
}
list.Delete();
web.Update();
}
}
});
}
现在需要向这2个列表导入数据，这个就没什么特别
SPListItem item = list.Items.Add();
item["CategoryName"] = row["CategoryName"].ToString();
item["Description"] = row["Description"].ToString();
item.SystemUpdate();导入数据后 列表如图：
现在该说查询的事情，关联查询需要用到SPQuery的Joins和ProjectedFields属性，这2个属性这里不是很好设置，我做这个demo在这个花费了4个小时都没搞定，最后用ling来实现，把相应的caml拷贝出来才实现该功能。
/*
* 不过Joins 和ProjectedFields 对于字段有很多限制，join的表不支持多行文本，不支持用户或用户组
SPQuery查询列表查不出结果，提示“一个或多个域类型未正确安装。请转到列表设置页删除这些域”。
原因： 查询的时候，字段名称需要输入字段的内部(InternalName)名称，而不是显示名称(DisplayName)。
*/
SPList list = web.Lists.TryGetList("Products");
SPQuery query = new SPQuery();
query.Query = @"<Where>
<And>
<BeginsWith>
<FieldRef Name='ContentTypeId' />
<Value Type='ContentTypeId'>0x0100</Value>
</BeginsWith>
<Gt>
<FieldRef Name='UnitPrice' />
<Value Type='Number'>30</Value>
</Gt>
</And>
</Where>"; query.Joins = @"<Join Type='LEFT' ListAlias='CategoryID'>
<Eq>
<FieldRef Name='CategoryID' RefType='ID'/>
<FieldRef List='CategoryID' Name='ID' />
</Eq>
</Join>";
query.ProjectedFields = @"<Field Name='CategoryTitle' Type='Lookup' List='CategoryID' ShowField='Title' />
<Field Name='CategoryDescription' Type='Lookup' List='CategoryID' ShowField='Description' />";
query.ViewFields = @" <FieldRef Name='ID' />
<FieldRef Name='Title' />
<FieldRef Name='UnitPrice' />
<FieldRef Name='CategoryTitle' />
<FieldRef Name='CategoryDescription' />";
query.RowLimit = 10;
SPListItemCollection items = list.GetItems(query);在joins里面<FieldRef List='CategoryID' Name='ID' />这句我不知道我们非要写成CategoryID（Products里面的关联列名），而不是Categories（关联的列表名称），网上也没有找到相应的说明。如果大家有人知道原因还请赐教一下。个人建议用Linq to SharePoint来实现关联查询（用它来生成这里的caml也可以啊）。
完整的代码如下：
public partial class Programs
{
static string siteUrl = "http://center.beauty.com/sites/wiki/";
public static void DeleteList(string listname)
{
SPSecurity.RunWithElevatedPrivileges(() =>
{
using (SPSite site = new SPSite(siteUrl))
{
using (SPWeb web = site.RootWeb)
{
SPList list = web.Lists.TryGetList(listname);
if (list == null) return;
SPFieldLookup[] lookupFields = list.Fields.OfType<SPFieldLookup>().Where(c => c.IsRelationship).ToArray();
if (lookupFields != null && lookupFields.Count()>0)
{
foreach (SPFieldLookup field in lookupFields)
{
SPFieldLookup[] depentFields = list.Fields.OfType<SPFieldLookup>().Where(c => c.IsDependentLookup && c.PrimaryFieldId == field.Id.ToString()).ToArray();
foreach (SPFieldLookup item in depentFields)
{
item.Delete();
}
field.Delete();
}
}
list.Delete();
web.Update();
}
}
});
}
public static void CreateCategories()
{
SPSecurity.RunWithElevatedPrivileges(() =>
{
using (SPSite site = new SPSite(siteUrl))
{
using (SPWeb web = site.RootWeb)
{
bool originalupdate = web.AllowUnsafeUpdates;
web.AllowUnsafeUpdates = true;
Guid id = web.Lists.Add("Categories", string.Empty, SPListTemplateType.GenericList);
SPList list = web.Lists[id];
SPView view = list.Views.OfType<SPView>().FirstOrDefault(x => x.DefaultView);
SPViewFieldCollection viewFields = view.ViewFields;
//附件
SPField field = list.Fields.GetFieldByInternalName("Attachments");
viewFields.Delete(field);
field = list.Fields["ID"];
field.Title = "CategoryID";
field.StaticName = "CategoryID";
field.Update();
// viewFields.Add(field);
field = list.Fields.GetFieldByInternalName("Title");
field.Title = "CategoryName";
field.StaticName = "CategoryName";
field.EnforceUniqueValues = true;
field.Indexed = true;
field.Update();
string fieldname = list.Fields.Add("Description", SPFieldType.Text, true);
field = list.Fields.GetFieldByInternalName("Description");
viewFields.Add(field);
list.OnQuickLaunch = true;
view.Update();
list.Update();
//web.Update();
web.AllowUnsafeUpdates = originalupdate;
}
}
});
}
public static void CreateProducts()
{
SPSecurity.RunWithElevatedPrivileges(() =>
{
using (SPSite site = new SPSite(siteUrl))
{
using (SPWeb web = site.RootWeb)
{
bool originalupdate = web.AllowUnsafeUpdates;
web.AllowUnsafeUpdates = true;
Guid id = web.Lists.Add("Products", string.Empty, SPListTemplateType.GenericList);
SPList list = web.Lists[id];
SPView view = list.Views.OfType<SPView>().FirstOrDefault(x => x.DefaultView);
SPViewFieldCollection viewFields = view.ViewFields;
//Remove Attachments
SPField field = list.Fields.GetFieldByInternalName("Attachments");
viewFields.Delete(field);
field = list.Fields.GetFieldByInternalName("ID");
field.Title = "ProductID";
field.StaticName = "ProductID";
field.Update();
// viewFields.Add(field);
field = list.Fields.GetFieldByInternalName("Title");
field.Title = "ProductName";
field.StaticName = "ProductName";
field.EnforceUniqueValues = true;
field.Indexed = true;
field.Update();
SPList categorList = web.Lists.TryGetList("Categories");
string fieldname = list.Fields.AddLookup("CategoryID", categorList.ID, true);
SPFieldLookup categoryID = list.Fields.GetFieldByInternalName(fieldname) as SPFieldLookup;
categoryID.Required = true;
categoryID.Indexed = true;
categoryID.LookupField = "ID";// categorList.Fields["CategoryID"].InternalName;
categoryID.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.Cascade;
categoryID.Update();
viewFields.Add(categoryID);
fieldname = list.Fields.AddDependentLookup("CategoryName", categoryID.Id);
SPFieldLookup categoryName = (SPFieldLookup)list.Fields.GetFieldByInternalName("CategoryName");
categoryName.LookupField = "Title";// categorList.Fields["标题"].InternalName;
viewFields.Add(categoryName);
categoryName.Update();
fieldname = list.Fields.Add("UnitPrice", SPFieldType.Number, true);
SPFieldNumber numberfield = list.Fields[fieldname] as SPFieldNumber;
numberfield.DisplayFormat = SPNumberFormatTypes.TwoDecimals;
viewFields.Add(numberfield);
field.Update();
list.OnQuickLaunch = true;
view.Update();
list.Update();
//web.Update();
web.AllowUnsafeUpdates = originalupdate;
}
}
});
}
public static void ImportData()
{
string constr = "Data Source=192.168.1.21;Initial Catalog=Northwind;Integrated Security=SSPI;";
string sql = "SELECT CategoryName, Description FROM Categories";
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection(constr))
{
SqlDataAdapter ap = new SqlDataAdapter(sql, con);
ap.Fill(dt);
}
SPList list;
using (SPSite site = new SPSite(siteUrl))
{
using (SPWeb web = site.RootWeb)
{
list = web.Lists.TryGetList("Categories");
foreach (DataRow row in dt.Rows)
{
SPListItem item = list.Items.Add();
item["CategoryName"] = row["CategoryName"].ToString();
item["Description"] = row["Description"].ToString();
item.SystemUpdate();
}
}
}
//add Products
sql = "SELECT ProductName, CategoryID, UnitPrice FROM Products";
dt = new DataTable();
using (SqlConnection con = new SqlConnection(constr))
{
SqlDataAdapter ap = new SqlDataAdapter(sql, con);
ap.Fill(dt);
}
using (SPSite site = new SPSite(siteUrl))
{
using (SPWeb web = site.RootWeb)
{
list = web.Lists.TryGetList("Products");
foreach (DataRow row in dt.Rows)
{
SPListItem item = list.Items.Add();
item["ProductName"] = row["ProductName"].ToString();
item["CategoryID"] = int.Parse(row["CategoryID"].ToString());
item["UnitPrice"] = double.Parse(row["UnitPrice"].ToString());
item.SystemUpdate();
}
}
}
}
public static void GetListItems()
{
SPSecurity.RunWithElevatedPrivileges(() =>
{
using (SPSite site = new SPSite(siteUrl))
{
using (SPWeb web = site.RootWeb)
{
try
{
//var context = new NwindDataContext(siteUrl);
//var qry = from p in context.Products
// where p.标题 == "demo"
// select new { ProductID = p.Id, Title = p.标题, UnitPrice = p.UnitPrice, CategoryName = p.CategoryID.标题, Description = p.CategoryID.Description };
//context.Log = Console.Out;
//var result = qry.ToList();
/*
* 不过Joins 和ProjectedFields 对于字段有很多限制，join的表不支持多行文本，不支持用户或用户组
SPQuery查询列表查不出结果，提示“一个或多个域类型未正确安装。请转到列表设置页删除这些域”。
原因： 查询的时候，字段名称需要输入字段的内部(InternalName)名称，而不是显示名称(DisplayName)。
*/
SPList list = web.Lists.TryGetList("Products");
SPQuery query = new SPQuery();
query.Query = @"<Where>
<And>
<BeginsWith>
<FieldRef Name='ContentTypeId' />
<Value Type='ContentTypeId'>0x0100</Value>
</BeginsWith>
<Gt>
<FieldRef Name='UnitPrice' />
<Value Type='Number'>30</Value>
</Gt>
</And>
</Where>";
query.Joins = @"<Join Type='LEFT' ListAlias='CategoryID'>
<Eq>
<FieldRef Name='CategoryID' RefType='ID'/>
<FieldRef List='CategoryID' Name='ID' />
</Eq>
</Join>";
query.ProjectedFields = @"<Field Name='CategoryTitle' Type='Lookup' List='CategoryID' ShowField='Title' />
<Field Name='CategoryDescription' Type='Lookup' List='CategoryID' ShowField='Description' />";
query.ViewFields = @" <FieldRef Name='ID' />
<FieldRef Name='Title' />
<FieldRef Name='UnitPrice' />
<FieldRef Name='CategoryTitle' />
<FieldRef Name='CategoryDescription' />";
query.RowLimit = 10;
SPListItemCollection items = list.GetItems(query);
foreach (SPListItem item in items)
{
string productID = item["ProductID"].ToString();
string productionname = item["ProductName"].ToString();
string unitePrice = item["UnitPrice"].ToString();
string categoryName = new SPFieldLookupValue(item["CategoryTitle"].ToString()).LookupValue;
string categoryDescription = new SPFieldLookupValue(item["CategoryDescription"].ToString()).LookupValue;
Console.WriteLine(string.Format("ProductID={0},ProductName={1},CategoryName={2},UnitPrice={3}",
productID, productionname, categoryName, unitePrice));
}
}
catch (Exception ex)
{
throw;
}
}
}
});
}
}
}
View Code
public partial class Programs
{
static void Main(string[] args)
{
DeleteList("Products");
DeleteList("Categories");
CreateCategories();
CreateProducts();
ImportData();
GetListItems();
}
}
View Code
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
