2

I have a database with multiple tables each linked to the other.(Relational Database) I want to Insert Records from my Dataset to Database. First I tried using OledbCommandBuilder but that didn't work, I found out it couldn't work with relational databases. I haven't linked tables within my dataset, they are linked only in the database. What I have is a button named "Commit". When I press that button I want all the modifications in my dataset.datatable to be reflected in the respective database table. I only know how to INSERT, UPDATE, DELETE a single record at a time but that also is a lengthy task as first I have to do this.

CODE IN THE SAVE BUTTON

             DataRow dRow = bookDS.Tables["Book"].NewRow();

               dRow[0] = Convert.ToInt64(textBookID.Text);
               dRow[1] = textBookName.Text;
               dRow[2] = textISBN.Text;

and so on (there are 30 columns)

              bookDS.Tables["Book"].Rows.Add(dRow);

Then

CODE IN THE COMMIT BUTTON

con.Open();

                    string sql = "Insert INTO Book Values (@BookId, @BookName, @ISBNNo, @PublicationId, @CategoryId, @Pages,@Price,@Author1,@Author2,@TotalCopies,@IssuedCopies,@AvailableCopies,@SupplierName,@Note)";

                    OleDbCommand save = new OleDbCommand(sql);

                    save.Parameters.Add("@BookId", OleDbType.BigInt).Value = Convert.ToInt64(dRow[0]);
                    save.Parameters.Add("@BookName", OleDbType.BSTR).Value = dRow[1];
                    save.Parameters.Add("@ISBNNo", OleDbType.BSTR).Value = dRow[2];

and so on...

save.Connection = con; save.ExecuteNonQuery();

                    con.Close();

That is all i can think of but that only works for a single Row and so I its like I have to press the COMMIT button every time I press the SAVE button.

Can anyone please help me with this ???

P.S : I am using MSAccess and also don't want to use TABLEADAPTER ( I want to do everything with my code )

Waqas Ali
  • 189
  • 2
  • 18

2 Answers2

0

http://msdn.microsoft.com/en-us/library/xzb1zw3x.aspx

Try using OleDBDataAdapter for this job. Or you can even try using OleDBComamndBuilder (of OleDb for Access database instead of Sql):

http://www.daniweb.com/software-development/csharp/threads/380925/how-to-save-a-dataset-into-a-database

andy
  • 5,979
  • 2
  • 27
  • 49
  • I cannot use OLEDBCommandBuilder as the tables in the database are related. I know I can use OledbAdapter but there would be multiple records to insert/update/delete into the database rather than one. – Waqas Ali Oct 26 '12 at 10:25
0
       string sql = "Insert INTO Book Values (@BookId, @BookName, @ISBNNo, @PublicationId, @CategoryId, @Pages,@Price,@Author1,@Author2,@TotalCopies,@IssuedCopies,@AvailableCopies,@SupplierName,@Note)";

     DataRow dRow = bookDS.Tables["Book"].NewRow();

                   dRow[0] = Convert.ToInt64(textBookID.Text);
                   dRow[1] = textBookName.Text;
                   dRow[2] = textISBN.Text;
                   bookDS.Tables["Book"].Rows.Add(dRow);
//This would help you to commit all the changes at once.   
 bookDS.AcceptChanges();
Rahul Ranjan
  • 1,028
  • 1
  • 10
  • 27
  • bookDS.AcceptChanges() would only change the RowState property of the dataset.tables[0] and the changes are not reflected in the database ! – Waqas Ali Oct 26 '12 at 10:28
  • @WaqasAli:Ok,view this page :http://stackoverflow.com/questions/10405373/insert-entire-datatable-into-database-at-once-instead-of-row-by-row – Rahul Ranjan Oct 26 '12 at 10:37