如何在listview中的sqlite数据库窗体中进行批量插入 [英] How do I make bulk insertion in sqlite database windows forms from listview

问题描述

大家好



首先请不要判断我是编程新手



我尝试了很多代码来插入多个录像机在SQLite中批量插入并且我总是得到例外



i使用

1. SQLite.NET.dll

hi Everybody

First of all "Please don't judge me I am New In Programming"

I tried a lot of codes to insert multi recorders "Bulk Insertion In SQLite" and i Always get exceptions

i used
1. SQLite.NET.dll

using Finisar.SQLite;

2. SQLite3.dll

< br $> b $ b

i有3个代码其中2个让我异常而另一个工作但是它不是更好的一个



i更喜欢第一个和第二个因为我认为如果出现错误我可以回滚



我自己写的最后一个代码是第一个和第二个第二，我从INTERNET得到它，但我不能使它工作良好



>>>>>> < a href =https://www9.0zz0.com/2019/05/02/23/563315426.png>我的界面示例

所以，如果是nyone可以帮助我，我会感激的



我尝试过：



第一个代码：

2. SQLite3.dll


i have 3 codes 2 of them got me exceptions and the other one work but it is not the better one of them

i prefer the first and second one because i think i can roll back if there is an error

the last code i write it by myself ant the first and second i got it from INTERNET but i cant make it work good

>>>>>> "Sample of My Interface"
so please if anyone can help me i will be thankful

What I have tried:

First Code :

dbConnection StartConn = new dbConnection();
SQLiteConnection MyConnetion = StartConn.GetConnection();

SQLiteCommand Cm = new SQLiteCommand();

MyConnetion.Open();

using (Cm = new SQLiteCommand())
{
    using (var transaction = MyConnetion.BeginTransaction())
    {
        for (var i = 0; i < listView.Items.Count; i++)
        {
            Cm.CommandText = "insert into InvoiceDetails (Invoice_Id,Item_Id,Item_Description,Item_NeededQuantity,Item_UnitPrice,Item_Discount,Item_Total) " +
                "values (@Invoice_Id,@Item_Id,@Item_Description,@Item_NeededQuantity,@Item_UnitPrice,@Item_Discount,@Item_Total)";

            Cm.Parameters.Add("@Invoice_Id", it.TempInvoice_Id.ToString());
            Cm.Parameters.Add("@Item_Id", listView.Items[i].SubItems[0].Text);
            Cm.Parameters.Add("@Item_Description", listView.Items[i].SubItems[1].Text);
            Cm.Parameters.Add("@Item_NeededQuantity", listView.Items[i].SubItems[2].Text);
            Cm.Parameters.Add("@Item_UnitPrice", listView.Items[i].SubItems[3].Text);
            Cm.Parameters.Add("@Item_Discount", listView.Items[i].SubItems[4].Text);
            Cm.Parameters.Add("@Item_Total", listView.Items[i].SubItems[5].Text);

            Cm.ExecuteNonQuery();
        }

        transaction.Commit();
    }
}

MyConnetion.Close();

第二代码：

Second Code :

dbConnection StartConn = new dbConnection();
SQLiteConnection MyConnetion = StartConn.GetConnection();

using (MyConnetion)
{
    var command = MyConnetion.CreateCommand();

    command.CommandText = "insert into InvoiceDetails (Invoice_Id,Item_Id,Item_Description,Item_NeededQuantity,Item_UnitPrice,Item_Discount,Item_Total) " +
        "values (@Invoice_Id,@Item_Id,@Item_Description,@Item_NeededQuantity,@Item_UnitPrice,@Item_Discount,@Item_Total)";
    command.Parameters.Add("@Invoice_Id");
    command.Parameters.Add("@Item_Id");
    command.Parameters.Add("@Item_Description");
    command.Parameters.Add("@Item_NeededQuantity");
    command.Parameters.Add("@Item_UnitPrice");
    command.Parameters.Add("@Item_Discount");
    command.Parameters.Add("@Item_Total");


    MyConnetion.Open();

    using (var transaction = MyConnetion.BeginTransaction())
    {
        command.Prepare();

        for (var i = 0; i < listView.Items.Count; i++)
        {
            command.Parameters["@Invoice_Id"].Value = it.TempInvoice_Id.ToString();
            command.Parameters["@Item_Id"].Value = listView.Items[i].SubItems[0].Text;
            command.Parameters["@Item_Description"].Value = listView.Items[i].SubItems[1].Text;
            command.Parameters["@Item_NeededQuantity"].Value = listView.Items[i].SubItems[2].Text;
            command.Parameters["@Item_UnitPrice"].Value = listView.Items[i].SubItems[3].Text;
            command.Parameters["@Item_Discount"].Value = listView.Items[i].SubItems[4].Text;
            command.Parameters["@Item_Total"].Value = listView.Items[i].SubItems[5].Text;

            command.ExecuteNonQuery();
        }

        transaction.Commit();
    }
}

最后一个代码（不是更好的一个在我的意见，但它的工作）：

Last Code (It is not The better one in My Opinion but it work):

dbConnection StartConn = new dbConnection();
SQLiteConnection MyConnetion = StartConn.GetConnection();

for (var i = 0; i < listView.Items.Count; i++)
{

    SQLiteCommand Cm = new SQLiteCommand("insert into InvoiceDetails (Invoice_Id,Item_Id,Item_Description,Item_NeededQuantity,Item_UnitPrice,Item_Discount,Item_Total) " +
                "values (@Invoice_Id,@Item_Id,@Item_Description,@Item_NeededQuantity,@Item_UnitPrice,@Item_Discount,@Item_Total)", MyConnetion);

Cm.Parameters.Add("@Invoice_Id", it.TempInvoice_Id.ToString());
Cm.Parameters.Add("@Item_Id", listView.Items[i].SubItems[0].Text);
Cm.Parameters.Add("@Item_Description", listView.Items[i].SubItems[1].Text);
Cm.Parameters.Add("@Item_NeededQuantity", listView.Items[i].SubItems[2].Text);
Cm.Parameters.Add("@Item_UnitPrice", listView.Items[i].SubItems[3].Text);
Cm.Parameters.Add("@Item_Discount", listView.Items[i].SubItems[4].Text);
Cm.Parameters.Add("@Item_Total", listView.Items[i].SubItems[5].Text);

MyConnetion.Open();

Cm.ExecuteNonQuery();

MyConnetion.Close();

}

推荐答案

最后一个对我好看，不知道为什么其他人不工作。您没有围绕它们进行交易的事实意味着它不再是批量插入。批量插入意味着您可以一次性插入它们，速度

The last one looks good to me, not sure why the others don't work. The fact you don't have a transaction around them, means it's no longer a bulk insert. A bulk insert means you insert them all at once, for speed

这篇关于如何在listview中的sqlite数据库窗体中进行批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！