Uploading a dataset.datatable upto a database table.

mike55

mike55

Well-known member
Joined
Mar 26, 2004
Messages
726
Location
Ireland
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
 
Ive 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 adapters Insert command. Ill look into it a bit more.
 
Assuming SQL Server:

Code: 
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.
 
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)
Code: 
  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
 
Back
Top