Uploading a dataset.datatable upto a database table.

[mike55]

Hi all,

 

Have a dataset which contains a table called myTable. myTable contains three columns that match a table in my database in terms of names and datatyes. I have filled my dataset table will the necessary data. Is it possible for me to insert all the data in my dataset table into my database table in one move, rather than looping through my dataset table and inserting one row at a time.

 

Mike55

[Afraits]

I've not tried this but I think it might be worth a go, if you have a data adapter configured to your table in the DB and you assign that data adapter to the Dataset and table contained within, then as your rows in the table will have a state of 'added' (presumably) calling dataAdapter.Update() should transfer them to your DB.

It may be more complicated than that though and require use/customisation of the adapter's Insert command. I'll look into it a bit more.

[penfold69]

Assuming SQL Server:

 

Dim conn as New SqlConnection("yourconnectionstring")
Dim comm as New SqlCommand("SQL Statement to retrieve values from database", conn)
Dim rs as New SqlDataAdapter(comm)
Dim cb as New SqlCommandBuilder(rs)
Dim dt as New DataTable
rs.Fill(dt)



' modify your data in the datatable, add rows, delete rows etc.
' then

rs.Update(dt) ' (This will make all the changes in your Datatable effective in the database)

rs.dispose()
cb.dispose()
comm.dispose()
conn.close()
conn.dispose()

 

 

(all from memory, e&oe :P)

 

B.

[mike55]

Many thanks, guys!!

 

Mike55

[Afraits]

Had a chance to test this now and yes having a table built up from a non DB source, then creating a data adapter to the DB table and setting up the insert command appropriately does work.

Test code beneath (NB Pets table added to northwind DB before running)

 Console.WriteLine("Creating pets table");
 DataTable petsTable=new DataTable("Pets");
 Console.WriteLine("Adding Columns to table"); 
 petsTable.Columns.Add("PetName",System.Type.GetType("System.String"));
 petsTable.Columns.Add("Breed",System.Type.GetType("System.String"));
 petsTable.Columns.Add("IQ",System.Type.GetType("System.Int16"));
 petsTable.Rows.Add(new Object[] {"Frisky","Tortoise",112}) ;
 petsTable.Rows.Add(new Object[] {"Fluffy","Grizzly Bear",6}) ;
 petsTable.Rows.Add(new Object[] {"Jez","Hamster",1}) ;
 //attempt to add to a DB table with same details
 try
 {
   //overloaded creation of data adapter (to avoid connection)
   SqlDataAdapter Adapter=new SqlDataAdapter("SELECT * FROM Pets",
  "Data Source=K2-SQL2;Initial Catalog=Northwind;Integrated Security=SSPI");
  //add in insert command
  Adapter.InsertCommand=new SqlCommand("INSERT INTO Pets (PetName,Breed,IQ) VALUES (@Petname,@Breed,@IQ)",Adapter.SelectCommand.Connection);
 //add in parameters for the command
 Adapter.InsertCommand.Parameters.Add("@Breed",SqlDbType.VarChar ,50,"Breed");
 Adapter.InsertCommand.Parameters.Add("@Petname",SqlDbType.VarChar ,50,"PetName");
 Adapter.InsertCommand.Parameters.Add("@IQ",SqlDbType.SmallInt,2,"IQ");

 //update dataset
 Adapter.Update( petsTable);
				
 }
 catch(Exception Ex)
 {
   Console.WriteLine("There was a serious problem....");
   Console.WriteLine(Ex.ToString());
 }

 

My ADO.NET work has all been done in C# but as its mostly the data objects themselves it should be ok for the VBers