Inserting a DataTable into a Database 1

[4getit]

I am trying to insert a DataTable into a SQL Server database. I'm not sure the best way to go about this. Should I loop through each row in the DataTable and insert them indvidually with seperate Insert statements or is there a way to Insert the entire DataTable with one shot. Is anyone familiar with the TableAdapter.Insert method? I've looked this over and it looks somewhat like what I'm looking for. Here's an example of some of my code so you have and idea what I'm trying to do here. Please provide any examples you might have. Much appreciated.

public void InsertTrackingData(DataTable ClickTrackData)
{
DataTable returnVal = new DataTable("ClickTrack");
SqlCommand doit = new SqlCommand("INSERT click_tracking (User_ID, Session_ID, Client_Type, Action_Type, UI_Element, UI_Parent, UI_Form) VALUES (5, 'NorthWestern')");

 

[markknowsley]

Well, you've come to the right place.

Here's what I do (and I'm not saying that this is the best method, but it's one option); use the SqlDataAdapter to fill a DataTable with schema information for the database table, then add one or more rows to the DataTable, then use the SqlDataAdapter to insert the information.

//First, connect to your SQL database
string strConnection = "<insert connection string>";
SqlConnection connection = new SqlConnection(strConnection);
//Next, use the SqlDataAdapter
//SELECT statement gets the schema information - you'll
//need to set a PK on the database table for this to work
SqlDataAdapter adapter = new SqlDataAdapter("SELECT column1, column2 FROM table", connection);
//Use the SqlCommandBuilder to enable the Insert statement
//on your adapter
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
//Declare a DataSet
DataSet dsData = new DataSet();
//Then fill the DataSet with information from the database
//table - this creates a new DataTable within the DataSet
adapter.Fill(dsData);
//Add a new row to your DataTable, and put into it all the information that you want to add
DataRow row = dsData.Tables[0].NewRow();
row[0] = <some data>;
row[1] = <some data>;
dsData.Tables[0].Rows.Add(row);
//Then call the update statement - this puts your data into the database
adapter.Update(dsData);

 

[djj55]

I assume you have data going into the other five columns?
SQL server needs one to one, however you do not need to list all fields (assuming NULL is allowed).
djj

 

[4getit]

There are a few colums actually. I think I have it figured out. I'm trying to do something like this. Does this look right to you pros out there?

public void InsertTrackingData(DataTable ClickTrackData)
{
string sql = "";
int i;

for (i = 0; i < ClickTrackData.Rows.Count; i++)
{
sql = "INSERT click_tracking (User_ID, Session_ID, Client_Type, Action_Type, UI_Element, UI_Parent, UI_Form)";
sql += "VALUES (@User_ID, @Session_ID, @Client_Type, @Action_Type, @UI_Element, @UI_Parent, @UI_Form)";

SqlCommand cmd = new SqlCommand(sql, _databases.Common);
cmd.Parameters.Add("@User_ID", dt.Rows["User_ID"]);
cmd.Parameters.Add("@Session_ID", dt.Rows["Session_ID"]);
cmd.Parameters.Add("@Client_Type", dt.Rows["Client_Type"]);
cmd.Parameters.Add("@Action_Type", dt.Rows["Action_Type"]);
cmd.Parameters.Add("@UI_Element", dt.Rows["UI_Element"]);
cmd.Parameters.Add("@UI_Parent", dt.Rows["UI_Parent"]);
cmd.Parameters.Add("@UI_Form", dt.Rows["UI_Form"]);
cmd.ExecuteNonQuery();

}

}

 

[markknowsley]

Looks good to me. I read somewhere that running repeated transactions against the database as you are doing will have a negative effect on performance, but I'm not enough of a pro to say exactly what that effect will be.

 

[tperri]

in my experience, the inserts like this arent too bad (tested on a live hotel/airfare reservation site on my end)

what kills sql, for me, is a delete. run into locking issues, things like that. so i changed from deleting and inserting to just updating where i could and inserting only what i had to and it eliminated so much sql bog down that i had.

HTH