如何使用ado.net插入特定父类子类别中，以表？ [英] How to insert child category by specific parent category in to table using ado.net?
问题描述
我试图插入来自Excel的类别和子类别到数据库表。
I am trying to insert categories and subcategories from excel into database table.
我 1的Excel文件其中包含了一些数据，并从这个Excel文件，我创建集，其中包含大量的数据表。
I have 1 excel file which contains some data and from this excel file i am creating dataset which contains lots of datatables.
在此集我 2的DataTable 在这样的形式：
DataTable中0与记录：类别
Datatable 0 with records:Category
ParentCategory Description
Electronics jhdkhsd
Sports kjshfhs
数据表1记录：< STRONG>子类别
Subcategory ParentCategory Description
Mobile Electronics weprwp
Tv Electronics sdflskd
Balls Sports kjshdfkjh
Shoes Sports uytuyt
现在我的数据库表是这样的：
Now my Database tables is like this:
类别： ID，名称，描述，PARENTID
到目前为止，我是全成插入父类，但现在试图插入子类别，但是这正是我目前挣扎。
So far i am successfull inserting parent category but now trying to insert child categories but that is where currently i am struggling.
这我的代码到目前为止：
This my code so far:
var dsFinal = new DataSet();
//Some code to read excel sheets and data from excel and create datatables and records with it.
dsControlSheet.Tables[0].Columns.Add("Id");
DataColumn parentId = new DataColumn("ParentId", typeof(int));
parentId.DefaultValue = 0;
dsFinal.Tables[0].Columns.Add(parentId);
dsFinal.Relations.Add("Abc",dsFinal.Tables[0].Columns["ParentCategory"],
dsFinal.Tables[1].Columns["ParentCategory"],false); //creating relation ship between Category datatable
// and SubCategory datatable on field ParentCategory
using (SqlConnection connection = new SqlConnection(""))
{
SqlDataAdapter adapter = new SqlDataAdapter();
var insertCommand = new SqlCommand("insert into Category (Name,Description) values (@ParentCategory,@Description) SET @Id = SCOPE_IDENTITY()", connection);
var parameter = insertCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");
insertCommand.Parameters.Add("@ParentCategory", SqlDbType.NVarChar, 50, "ParentCategory");
insertCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 50, "Description");
parameter.Direction = ParameterDirection.Output;
insertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
adapter.InsertCommand = insertCommand;
adapter.Update(dsFinal.Tables[0]); //successfully inserted parent category and got autoincremented value in Id column of my 0th datatable
//trying to insert child category using above insert command
foreach (DataRow parentCategory in dsFinal.Tables[0].Rows)
{
var child = parentCategory.GetChildRows("Abc").CopyToDataTable();//get child category of particular parent
adapter.Update(child);
}
}
在这里，在最后的循环中插入子类我是困惑该如何使用
相同的InsertCommand 变量插入子类别??
Here in the last loop to insert child category i am confused that how to use
same insertCommand variable to insert child category??
任何人可以请帮助我这个???
Can anybody please help me with this???
更新：我已经使用数据表中表达来计算 PARENTID 是这样的：使用（SqlConnection的连接=新的SqlConnection（
Update:I have used datatable Expresion to calculate parentid like this:
using (SqlConnection connection = new SqlConnection(""))
{
SqlDataAdapter adapter = new SqlDataAdapter();
var insertCommand = new SqlCommand("insert into Category (Name,Description) values (@ParentCategory,@Description) SET @Id = SCOPE_IDENTITY()", connection);
var parameter = insertCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");
insertCommand.Parameters.Add("@ParentCategory", SqlDbType.NVarChar, 50, "ParentCategory");
insertCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 50, "Description");
parameter.Direction = ParameterDirection.Output;
insertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
adapter.InsertCommand = insertCommand;
adapter.Update(dsFinal.Tables[0]); //successfully inserted parent category and got autoincremented value in Id column of my 0th datatable
//For inserting child category..
//added column parentid to store child category
SqlDataAdapter da = new SqlDataAdapter();
dsFinal.Tables[1].Columns.Add("ParentId", typeof(int), "IIF(Parent.ParentCategory=ParentCategory,parent.Id,0)");
var insertChildCategoryCommand = new SqlCommand("insert into Category (Name,Description,ParentId) values (@Subcategory,@Description,@ParentId) SET @Id = SCOPE_IDENTITY()", connection);
var parameter1 = insertChildCategoryCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");
insertChildCategoryCommand.Parameters.Add("@Subcategory", SqlDbType.NVarChar, 50, "Subcategory");
insertChildCategoryCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 50, "Description");
insertChildCategoryCommand.Parameters.Add("@ParentId", SqlDbType.int, 0, "ParentId");
parameter1.Direction = ParameterDirection.Output;
insertChildCategoryCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
da.InsertCommand = insertChildCategoryCommand;
//Error here that computed column cannot be inserted.Here computed column is parentid
da.Update(dsFinal.Tables[1]);
}
错误：已计算列（PARENTID）不能插入。
推荐答案
您几乎没有用最新的代码。
You are almost there with latest code.
唯一的问题是，所计算的列不允许用于插入/更新数据库表。顺便说一句，该错误信息是不是的<em>计算列（PARENTID）不能插入的，而是：
The only problem is that the calculated columns are not allowed to be used for inserting/updating the database table. Btw, the error message is not "Computed column(parentid) cannot be inserted.", but:
从SourceColumn'的ParentId列映射失败，因为的DataColumn'的ParentId'是计算列。
The column mapping from SourceColumn 'ParentId' failed because the DataColumn 'ParentId' is a computed column.
我可以同意，该消息可能已经好了，我也没发现描述的任何文件。最有可能的理由是，计算列通常不存放。
I could agree that the message could have been better, and also I didn't find any documentation describing that. Most probably the rationale is that the computed columns are not normally stored.
不管是什么原因，这就是现实。你没有其他选择，而不是建立一个定期专栏，并与人工数据填充它。
Whatever the reason is, that's the reality. You have no other choice than creating a regular column and populating it with data manually.
有很多方法可以做到这一点（既有效率又ineffient），但一旦你已经创建的关系，你可以使用的 DataRow.GetParentRow 方法来定位相关的类别记录。
There are many ways to do that (both efficient and ineffient), but once you already created a relation, you can use the DataRow.GetParentRow method to locate the related category record.
与所有他这样说，更换行
With all that being said, replace the line
dsFinal.Tables[1].Columns.Add("ParentId", typeof(int),
"IIF(Parent.ParentCategory=ParentCategory,parent.Id,0)");
与下面的代码片段：
with the following snippet:
var dtSubCategory = dsFinal.Tables[1];
dtSubCategory.Columns.Add("ParentId", typeof(int));
foreach (var drSubCategory in dtSubCategory.AsEnumerable())
{
var drCategory = drSubCategory.GetParentRow("Abc");
drSubCategory["ParentId"] = drCategory != null ? drCategory["Id"] : 0;
}
和你做。
编辑：让我说清楚。这里唯一的一次关键操作的定位类别按名称标识。使用关系和 GetParentRow 等价的计算表达式访问父在你尝试。数据关系在内部维护支持这些行动的查找结构。
Let me make it clear. The only time critical operation here is locating the category id by name. Using the relation and GetParentRow is equivalent of the evaluating the expression accessing parent as in your attempt. Data relation internally maintains a lookup structure for supporting such operations.
如果你想获得最佳的性能，那么不创建关系，而是一本字典。你所需要的是一个名字（字符串），找到相应的ID（INT），所以词典<字符串，整数> 是一个完美的候选人：
If you want to get the best possible performance, then don't create a relation, but a dictionary. What you need is given a name (string), find the corresponding id (int), so Dictionary<string, int> is a perfect candidate for that:
var categoryIds = dsFinal.Tables[0].AsEnumerable().ToDictionary(
dr => dr.Field<string>("ParentCategory"), // key
dr => dr.Field<int>("Id") // value
);
var dtSubCategory = dsFinal.Tables[1];
dtSubCategory.Columns.Add("ParentId", typeof(int));
foreach (var drSubCategory in dtSubCategory.AsEnumerable())
{
int categoryId;
categoryIds.TryGetValue(drSubCategory.Field<string>("ParentCategory"), out categoryId);
drSubCategory["ParentId"] = categoryId;
}
这篇关于如何使用ado.net插入特定父类子类别中，以表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
