Confusion in inserting a whole 200 records in dataTable into a database file in my code

Question

Hello, I am a beginner in ADO.NET C# programming. My intent is to transform all records from dt (my DataTable) to a database file in Driver C (C:\datamining.mdf). However I am confused to do so, since I cannot use the known SQL Insert Statement {INSERT datatable VALUES (,,,,) } since my datatable has got 200 records. Here is my code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;

namespace TextToDataBase
{
publicpartialclassForm2 : Form

{
public Form2()
{
InitializeComponent();
}
DataTable dt;
DataTable dt2;
privatevoid button1_Click(object sender, EventArgs e)
{
string str;
SqlConnection myConn = newSqlConnection("Server=(local);Integrated security=SSPI;database=master");
str = "CREATE DATABASE datamining_fourth ON PRIMARY" +
"(NAME=dataming_dat, FILENAME='C:\\dataminingforth.mdf', SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=10%)" +
"LOG ON (NAME=datamining_log, FILENAME='C:\\dataminingforth.ldf', SIZE=1MB,MAXSIZE=5MB,FILEGROWTH=10%)";
SqlCommand myCommand = newSqlCommand(str, myConn); 
SqlConnection tableConn = newSqlConnection ("Server=(local);Integrated security=SSPI;database=datamining");
string str2;
str2= "CREATE TABLE drugdata ("+
"[Age] [int] IDENTITY(1,1) NOT NULL,"+
"[Sex] [bit] NOT NULL"+
"[BP] [int] NULL"+
"[Cholestrol] [int] NULL"+
"[NA] [int] NULL"+
"[K] [int] NULL"+
"[Drug] [string] NULL"; 
SqlCommand tablecmd = newSqlCommand(str2,tableConn);
try

{
myConn.Open();
myCommand.ExecuteNonQuery();
MessageBox.Show("پایگاه داده با موفقیت ایجاد شد!", "پیغام", MessageBoxButtons.OK, MessageBoxIcon.Information);
tableConn.Open();
tablecmd.ExecuteNonQuery();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message, "خطا", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally

{
if (myConn.State == ConnectionState.Open)
myConn.Close();
if (tableConn.State == ConnectionState.Open)
tableConn.Close();
}
}
privatevoid Form2_Load(object sender, EventArgs e)
{
dt = newDataTable();
dt.Columns.Add("Age");
dt.Columns.Add("Sex");
dt.Columns.Add("BP");
dt.Columns.Add("Cholestrol");
dt.Columns.Add("Na");
dt.Columns.Add("K");
dt.Columns.Add("Drug");
StreamReader fg = newStreamReader("c:\\drug data mining.txt");
while (!fg.EndOfStream)
{
string val = fg.ReadLine();
string[] cells = val.Split(',');
for (int j = 0; j < cells.Length - 6; j++)
dt.Rows.Add(cells[0], cells[1], cells[2], cells[3], cells[4], cells[5], cells[6]);
}
dataGridView1.DataSource = dt;
dt2 = dt.Copy();
}
privatevoid button2_Click(object sender, EventArgs e)
{

SqlConnection tableConn = newSqlConnection("Server=(local);Integrated security=SSPI;database=datamining");
string str2;
str2 = "CREATE TABLE drugdata (" +
"[Age] [int] IDENTITY(1,1) NOT NULL," +
"[Sex] [nvarchar] (50) NOT NULL," +
"[BP] [int] NULL," +
"[Cholestrol] [int] NULL," +
"[NA] [int] NULL," +
"[K] [int] NULL," +
"[Drug] [nvarchar] (50) NULL)";
SqlCommand tablecmd = newSqlCommand(str2, tableConn);
try

{
tableConn.Open();
tablecmd.ExecuteNonQuery();
MessageBox.Show("جدول با موفقیت ایجاد شد!","پیغام", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message, "خطا", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally

{
if (tableConn.State == ConnectionState.Open)
tableConn.Close();
}
}
privatevoid button3_Click(object sender, EventArgs e)
{
DataSet miningSet = newDataSet();
miningSet.Tables.Add(dt);
SqlConnection insrConn = newSqlConnection("Server=(local);Integrated security=SSPI;database=datamining");
SqlDataAdapter sda = newSqlDataAdapter("Select * FROM dt", insrConn);
sda.Fill(miningSet.Tables["dt"]);
/*


*/
try

{
insrConn.Open();
insrcmd.ExecuteNonQuery();
MessageBox.Show("عمل درج با موفقیت ایجاد شد!", "پیغام", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message, "خطا", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally

{
if (insrConn.State == ConnectionState.Open)
insrConn.Close();
}
}
}
}

There is a space filled with */ /* which needs to do the mentioned insertion. How could I do such operation? Could you please at least guide me with pseudocode?

Answer 1

If I understand correctly you are interested in loading data to dataset and saving changes back to database? Here is example where you edit data in dataSet and save changes.

    dataAdapter = new SqlDataAdapter(sqlQuery, databaseConnectionString);
    SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapter);
    builder.GetUpdateCommand();
    dataSet = new DataSet();
    DataAdapter.Fill(dataSet);
    //now you edit dataSet

SqlCommandBuilder should be used if you want to update changes made in dataSet back to SqlDatabase! Than you can write:

    dataAdapter.Update(dataSet);

Answer 2

If you don't want to deal with DataAdapter (which is the better way), try something like this:

string insertStatement;
foreach (DataRow dRow in dt.Rows)
{
     insertStatement = "INSERT datatable VALUES (";
     foreach (object objItem in dRow.ItemArray)
     {
         insertStatement += objItem.ToString() + ",";
     }
     insertStatement = insertStatement.Substring(0, insertStatement.LastIndexOf(','));
     insertStatement += ")";
     // Than execute the insert statement
}