使用linq列表进行数据库操作 [英] Database manipulation with linq lists

问题描述

大家好，
我是C#的新手，我正在尝试制作一个将excel文件导入linq列表的应用程序，然后将其导入数据库.在表单本身上，我有2个按钮，导入"和取消"(取消"按钮适用于后台工作人员)和4个datagridview.首次单击导入按钮时，它将完美运行，所有内容均写入数据库.但是如果您取消或导入新文件，则会收到错误消息:

属性"ID"是对象键信息的一部分，不能修改.

我发现datagridview，list或objData4中的项目仍然包含上一次导入的项目，因此我有一行代码可以清除所有来源中的所有内容，包括objData，list，datagridview，datasource，但是我仍然得到那个错误.我已经在这里待了将近2个星期了.如果有人可以帮助我，那就太好了！

在此先感谢
如果您需要查看代码，请告诉我.


更新6.27.11
我取消注释以下几行，以确保在导入新文件之前清除所有内容:
itemMasterList.Clear();
objData1.Clear();

但是然后我在dmConsumption.SaveChanges();处得到了这个新错误.行:
已成功提交对数据库的更改，但是在更新对象上下文时发生了错误. ObjectContext可能处于不一致状态.内部异常消息:AcceptChanges无法继续，因为该对象的键值与ObjectStateManager中的另一个对象发生冲突.在调用AcceptChanges之前，请确保键值是唯一的.
我完全不知所措，有人可以帮我吗?


这是代码^^

Hi All,
I''m new to C# and I''m trying to make an application that will import an excel file into a linq list which in turn will import that to a database. On the form itself i have a 2 buttons, Import and Cancel (the cancel button is for the background worker) and 4 datagridview. When you click the import button for the first time, it will run perfectly, everything is written into the database. but if you cancel, or import a new file, I get an error message:

The property ''ID'' is part of the object''s key information and cannot be modified.

I figured that the items in either the datagridview, list, or the objData4 still contains the items from the previous import, so I have a line of code that will clear everything from all sources, objData, list, datagridview, datasource, but i still get that error. I''ve been at this for almost 2 weeks now. if anyone can help me it would be so amazing!!!

Thanks in Advance
if you need to see the code, please let me know.


Update 6.27.11
I un-commented out the following lines, to ensure that everything was cleared before importing a new file:
itemMasterList.Clear();
objData1.Clear();

but then i get this new error at the dmConsumption.SaveChanges(); line:
The changes to the database were committed successfully, but an error occurred while updating the object context. The ObjectContext might be in an inconsistent state. Inner exception message: AcceptChanges cannot continue because the object''s key values conflict with another object in the ObjectStateManager. Make sure that the key values are unique before calling AcceptChanges.
im totally at a loss, can someone please help me out?


heres the code ^^

<br />
<pre lang="cs">#region Excel Import Button<br />
       private void button1_Click(object sender, EventArgs e)<br />
       {<br />
           pBarStat.Value = 0;<br />
           dgView1.DataSource = null;<br />
<br />
//with this uncommented i get this error:<br />
//The property ''ID'' is part of the object''s key information and cannot be modified.<br />
           //itemMasterList.Clear();<br />
           //salesLineList.Clear();<br />
           //itemLedgerList.Clear();<br />
           //purchaseLineList.Clear();<br />
           //objData4.Clear();<br />
           //itemMasterList.Clear();<br />
<br />
           #region Delete Statements<br />
<br />
           //Sales Line<br />
           foreach (var deleteMe in dmConsumption.SalesLines)<br />
           {<br />
               dmConsumption.SalesLines.DeleteObject(deleteMe);<br />
           }<br />
<br />
<br />
           //Purchase Line<br />
           foreach (var deleteMe in dmConsumption.PurchaseLines)<br />
           {<br />
               dmConsumption.PurchaseLines.DeleteObject(deleteMe);<br />
           }<br />
<br />
<br />
           //Item Ledger<br />
           foreach (var deleteMe in dmConsumption.ItemLedgers)<br />
           {<br />
               dmConsumption.ItemLedgers.DeleteObject(deleteMe);<br />
           }<br />
<br />
<br />
           //Item Master<br />
           foreach (var deleteMe in dmConsumption.ItemMasters)<br />
           {<br />
               dmConsumption.ItemMasters.DeleteObject(deleteMe);<br />
           }<br />
           dmConsumption.SaveChanges();<br />
           #endregion<br />
<br />
           try<br />
           {<br />
               openFile.InitialDirectory = System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop);<br />
               openFile.Title = "Import Excel File From:";<br />
               openFile.FileName = "";<br />
               openFile.Filter = "Excel 97-2003|*.xls";<br />
<br />
               if (openFile.ShowDialog() == DialogResult.OK)<br />
               {<br />
                   source = openFile.FileName;<br />
                   if (IMWorker.IsBusy)<br />
                   {<br />
                       button1.Enabled = false;<br />
                       Status.Text = "Cancelling...";<br />
                       IMWorker.CancelAsync();<br />
                   }<br />
                   else<br />
                   {<br />
                       button1.Enabled = false;<br />
                       button3.Enabled = true;<br />
                       button3.Visible = true;<br />
                       Status.Text = "Processing data...";<br />
<br />
                       IMWorker.RunWorkerAsync();<br />
                   }<br />
               }<br />
           }<br />
           catch (Exception ex)<br />
           {<br />
               MessageBox.Show(ex.ToString());<br />
           }<br />
       }<br />
       #endregion</pre><br />
<br />
<br />
<pre lang="cs">#region IMWorker<br />
<br />
 #region IMWorker DoWork<br />
 public void IMworker_DoWork(object sender, DoWorkEventArgs e)<br />
{<br />
    Thread.Sleep(1000);<br />
     excelPop = ExcelConnection();<br />
    objDA1.SelectCommand = excelPop[0];<br />
    objDA1.Fill(objData1);<br />
    increment = objData1.Tables[0].Rows.Count;<br />
<br />
     pBarStat.ProgressBar.Invoke((MethodInvoker)delegate()<br />
    {<br />
        pBarStat.Maximum = increment;<br />
    });<br />
<br />
    if (dgView1.InvokeRequired)<br />
    {<br />
        dgView1.Invoke(new MethodInvoker(delegate { dgView1.DataSource = objData1.Tables[0].DefaultView; }));<br />
    }<br />
        foreach (DataRow row in objData1.Tables[0].Rows)<br />
        {<br />
<br />
            var IM = new itemMaster();<br />
                try<br />
                {<br />
                    IM.ID = row["No#"].ToString();<br />
                }<br />
                catch (Exception ex)<br />
                {<br />
                    IM.IMError = "Error";<br />
                }<br />
<br />
                try<br />
                {<br />
                    IM.name1 = row["Description"].ToString();<br />
                }<br />
                catch (Exception ex)<br />
                {<br />
                    IM.IMError = "Error";<br />
                }<br />
<br />
                try<br />
                {<br />
                    IM.name2 = row["Description 2"].ToString();<br />
                }<br />
                catch (Exception ex)<br />
                {<br />
                    IM.IMError = "Error";<br />
                }<br />
<br />
                try<br />
                {<br />
                    IM.unitPrice = double.Parse(row["Unit Cost"].ToString());<br />
                }<br />
                catch (Exception ex)<br />
                {<br />
                    IM.unitPrice = 0;<br />
                    IM.IMError = "Error";<br />
                }<br />
<br />
                itemMasterList.Add(IM);<br />
                pBarStat.ProgressBar.Invoke((MethodInvoker)delegate()<br />
                {<br />
                    pBarStat.Value = increment;<br />
                });<br />
                //itemMasterWorker.ReportProgress(xy);<br />
        }<br />
<br />
<br />
        foreach (var imItem in itemMasterList)<br />
        {<br />
            ItemMaster newItemMaster = new ItemMaster();<br />
            newItemMaster.ID = imItem.ID.ToString();<br />
            newItemMaster.Generic = imItem.name1.ToString();<br />
            newItemMaster.Brand = imItem.name2.ToString();<br />
            newItemMaster.UnitPrice = decimal.Parse(imItem.unitPrice.ToString());<br />
<br />
            dmConsumption.ItemMasters.AddObject(newItemMaster);<br />
        }<br />
    dmConsumption.SaveChanges();<br />
//i get this error here {"Violation of PRIMARY KEY constraint ''PK_ItemMasters''. Cannot insert duplicate key in object ''dbo.ItemMasters''.\r\nThe statement has been terminated."}<br />
}<br />
 #endregion<br />
<br />
 #region IMWorker Complete<br />
 private void IMWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)<br />
 {<br />
     if (e.Error != null)<br />
     {<br />
         MessageBox.Show(e.Error.Message);<br />
     }<br />
<br />
     button3.Visible = false;<br />
     button3.Enabled = false;<br />
     button1.Enabled = true;<br />
     button1.Visible = true;<br />
<br />
     if (e.Cancelled)<br />
     {<br />
         MessageBox.Show("Operation Cancelled", "Cancelled", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);<br />
         Status.Text = "Ready...";<br />
         return;<br />
     }<br />
     else<br />
     {<br />
         Status.Text = "Operation Successfull!";<br />
         pBarStat.Value = increment;<br />
     }<br />
 }<br />
 #endregion</pre><br />
<br />

推荐答案

也许我建议您检查 ItemMaster 的表结构.
如果我没记错的话，字段 ID 标识规范结构
被定义为身份增量1"，这是主键.

在这种情况下，您不应在其上分配要保存在表中的值.
这可能会导致 违反主键

建议您在下面查看您的代码:

foreach(itemMasterList中的var imItem)
{
ItemMaster newItemMaster =新的ItemMaster();
newItemMaster.ID = imItem.ID.ToString(); newItemMaster.Generic = imItem.name1.ToString();
newItemMaster.Brand = imItem.name2.ToString();
newItemMaster.UnitPrice = decimal.Parse(imItem.unitPrice.ToString());
dmConsumption.ItemMasters.AddObject(newItemMaster);
}

dmConsumption.SaveChanges();

我怀疑错误出现在 dmConsumption.SaveChanges();
当触发...


我注意到您正在处理一个事务中的多个表.
我建议您在代码中进行管理交易.
像:
SqlConnection aConn =新的SqlConnection(yourStrConnfromConfig);
SqlTransaction tran;
SqlCommand cmd;
试试
{
aConn.Open();
tran = aConn.BeginTransaction();
}
catch(Exception)
{
返回与数据库的连接失败."；
}

//1.销售线
字符串yourQueryString1; //这是销售行查询
cmd =新的SqlCommand(yourQueryString1，aConn);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();

//2.购买线
字符串yourQueryString2; //这是购买行查询
cmd =新的SqlCommand(yourQueryString2，aConn);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();

//3.物料分类帐
字符串yourQueryString3; //这是项目分类帐查询
cmd =新的SqlCommand(yourQueryString3，aConn);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();

//4.物品大师
字符串yourQueryString4; //这是 Item Master 查询
cmd =新的SqlCommand(yourQueryString4，aConn);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();

试试
{
tran.Commit(); //提交四笔交易
}
catch(Exception)
{
tran.Rollback(); //回滚事务
返回进​​程中止，事务已回滚"；
}
终于
{
aConn.Close();
tran.Dispose();
}

希望这可以帮助...

问候，

代数

Hi,

Maybe I could suggest you to check the table structure of ItemMaster.
If I am not mistaken, the field ID identification specification structure
was define as Identity Increment by 1, and this is the Primary Key.

If this is the case, You should not asign the value on it to save in your table.
This could create an Violation of PRIMARY KEY

I suggest you to review your code below:

foreach (var imItem in itemMasterList)
{
ItemMaster newItemMaster = new ItemMaster();
newItemMaster.ID = imItem.ID.ToString(); newItemMaster.Generic = imItem.name1.ToString();
newItemMaster.Brand = imItem.name2.ToString();
newItemMaster.UnitPrice = decimal.Parse(imItem.unitPrice.ToString());
dmConsumption.ItemMasters.AddObject(newItemMaster);
}

dmConsumption.SaveChanges();

I suspected error goes in dmConsumption.SaveChanges();
when trigered...


I notice that you''re manipulating several tables in a transaction.
I sugest you to have a management transaction in your code.
like:
SqlConnection aConn = new SqlConnection(yourStrConnfromConfig);
SqlTransaction tran;
SqlCommand cmd;
try
{
aConn.Open();
tran = aConn.BeginTransaction();
}
catch (Exception)
{
return "Connection to database failed.";
}

//1. Sales Line
string yourQueryString1; // This is the Sales Line query
cmd = new SqlCommand(yourQueryString1, aConn);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();

//2. Purchase Line
string yourQueryString2; // This is the Purchase Line query
cmd = new SqlCommand(yourQueryString2, aConn);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();

//3. Item Ledger
string yourQueryString3; // This is the Item Ledger query
cmd = new SqlCommand(yourQueryString3, aConn);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();

//4. Item Master
string yourQueryString4; // This is the Item Master query
cmd = new SqlCommand(yourQueryString4, aConn);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();

try
{
tran.Commit(); // commit four Transactions
}
catch (Exception)
{
tran.Rollback(); // Roll back Transactions
return "Process aborted, Transaction were rolled back";
}
finally
{
aConn.Close();
tran.Dispose();
}

Hope this could help...

Regards,

Algem

尝试在代码之前插入以下代码行:

foreach(itemMasterList中的var imItem)
{
ItemMaster newItemMaster =新的ItemMaster();
newItemMaster.ID = imItem.ID.ToString();
newItemMaster.Generic = imItem.name1.ToString();
newItemMaster.Brand = imItem.name2.ToString();
newItemMaster.UnitPrice = decimal.Parse(imItem.unitPrice.ToString());
dmConsumption.ItemMasters.AddObject(newItemMaster);
}



这是为了确保
中没有空值 您的itemMasterList row ["No#"]列.
同样要确保没有重复
跟踪/调试，并查看下面循环行["No#"]值的值...



//删除row ["No#"]列上所有值为空的行

Try to insert this line of code before your code:

foreach (var imItem in itemMasterList)
{
ItemMaster newItemMaster = new ItemMaster();
newItemMaster.ID = imItem.ID.ToString();
newItemMaster.Generic = imItem.name1.ToString();
newItemMaster.Brand = imItem.name2.ToString();
newItemMaster.UnitPrice = decimal.Parse(imItem.unitPrice.ToString());
dmConsumption.ItemMasters.AddObject(newItemMaster);
}



This is to insure that there is no empty value in
your itemMasterList row["No#"] column.
Also for sure that there is no duplication
trace/debug and see the value on below for loop row["No#"] value...



// Delete all rows on row["No#"] column having empty value

for (int i = 0; i < itemMasterList.Rows.Count; i++)
{
  if (itemMasterList.Rows[i]row["No#"].ToString() == string.Empty)
  {
       itemMasterList.Rows[i].Delete();
  }
}
itemMasterList.AcceptChanges();

希望这会有所帮助..

问候，

Algem

Hope this could help..

Regards,

Algem

忘记了关闭连接.现在我觉得很蠢.感谢您的所有答复和帮助^^

forgot to close the connection. now i feel stupid. thanks for all the replies and help ^^

这篇关于使用linq列表进行数据库操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！