Dataset and Datatable

[soniix]

ive got a question,

 

if i creat a dataset and a datatable at runtime that means it doesnt comes from the server how do i write its data to a table in the database? is it possible with a for each loop?

[Gladimir]

Writing dataset table to database table

 

Well, let's assume we're talking about Microsoft Access, but I think the steps apply generally.

 

If you already have a table in your database with the same fields (coulmns) as your dataset table, then all you need to do is whip up a connection object, a dataadapter object, and a commandbuilder object. That would allow you to use the data adapter's Update method to insert all the records from your dataset table to your database table.

 

DataSet ds1 = new DataSet();
ds1.Tables.Add("assets"); // dataset table index 0

/* I have a class that use for my database connection.  
* I'll include that code at the bottom */
// initialize master data connection object
dbMaster masterdb = new dbMaster();
OleDbConnection master = masterdb.m_master;

// create and initialize data adapter objects
string strassetSelect = "SELECT * FROM assets";
OleDbDataAdapter assetAdapter = new OleDbDataAdapter(strassetSelect, master);

// create and initialize command object required to add new rows
OleDbCommandBuilder assetCommand = new OleDbCommandBuilder(assetAdapter);

// fill the dataset tables in memory from the database tables
assetAdapter.Fill(ds1, "assets"); // table index 0

/* populate your dataset table and have all sorts of fun */

// update the database table in the master database
assetAdapter.Update(ds1, "assets");

 

I'm a little new to this, but this will get you connected to an Access database, then you'll just need to create a data adapter for each database table you want to read/write from and a commandbuilder object for each database table that you want to actually update.

 

Here is my public database connection class for an Access database. It also assumes the database is named 'master.mdb' and in a subdirectory named 'data'.

 

public class dbMaster
{
 public OleDbConnection m_master;
 public dbMaster()
 {
   string strStartupPath = Application.StartupPath;

   // comment these two lines out before deployement
   int intStartupPathLength = strStartupPath.Length;
   string strdbPath = strStartupPath.Remove(intStartupPathLength - 10, 10);
   string strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
       "Data Source=" + strdbPath + "\\data\\master.mdb";
   m_master = new OleDbConnection(strConnectionString);
 }
}

[APaule]

You have to create the tables using SQL-commands first and then fill them.

To give you an idea of the necessary syntax read the following two articles for Access and SQL Server.

 

Fundamental Microsoft Jet SQL for Access 2000

 

Transact-SQL Reference