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
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();
}