Click here to Skip to main content
15,870,130 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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

C#
using Finisar.SQLite;


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 :

C#
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 :

C#
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):

C#
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();

}
Posted
Updated 2-May-19 11:55am
v2
Comments
[no name] 2-May-19 15:49pm    
Go with the one that works; unless somebody is interested in "spotting the differences".

Do you know what a listview "data source" is? Did you know you can use that instead of the actual listview?
Eng Mohamed Bassuny 2-May-19 16:23pm    
listview data comes from the user of the program it is a temporary data still not inserted to database

you can see this picture https://www9.0zz0.com/2019/05/02/23/563315426.png


this is a sample of my program
[no name] 2-May-19 16:50pm    
So you're saying you don't know what a data source is, the user is loading something into a program you didn't write, but you want to do a "bulk insert".

Then my suggestion to "love the one that works" stands.
Eng Mohamed Bassuny 2-May-19 17:29pm    
i am not feeling good about your last words but thanks for trying to help me
[no name] 2-May-19 19:32pm    
The saying is: "Love the one you're with"; i.e. be satisfied with your blessings. I said: use the data source and you ignored me. My time's up.

1 solution

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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900