how to insert values from dataset or datatable to database table in C#.net

[haarigee]

I need to insert without any parameters and execution query.

Since, I have bulk of data's in a dataset the amount of transaction makes the application delay. So i need to directly insert my dataset to database table.

DataSet FilecontentDataset = new DataSet();
        String fileName = string.Empty;
 
        try
        {
            string full = Path.GetFullPath(PathtoTextFile.PostedFile.FileName);
            string file = Path.GetFileName(PathtoTextFile.PostedFile.FileName);
            string dir = Path.GetDirectoryName(PathtoTextFile.PostedFile.FileName);
 
            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=\"" + dir + "\\\";"    + "Extended Properties=\"text;HDR=YES;FMT=Delimited\"";
 
            //create the database query
            string query = "SELECT * FROM " + file;
 
            //create a DataTable to hold the query results
            DataTable dTable = new DataTable();
 
            //create an OleDbDataAdapter to execute the query
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);
            dAdapter.Fill(FilecontentDataset, "CSV");
 
            SqlConnection con = OPENSQLCONNECTION();
            con.Open();
 
 
            //foreach (DataRow dbNewRow in FilecontentDataset.Tables[0].Rows)
            //{
            //    SqlCommand cmd_SubmitEmployeeDetail = new SqlCommand("LoadCSVDetails", con);
            //    cmd_SubmitEmployeeDetail.CommandType = CommandType.StoredProcedure;
 
 
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@UserName", SqlDbType.VarChar).Value = dbNewRow.ItemArray[0].ToString();
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@Password", SqlDbType.VarChar).Value = dbNewRow.ItemArray[1].ToString();
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@FullName", SqlDbType.VarChar).Value = dbNewRow.ItemArray[2].ToString();
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@Restrictionlistname", SqlDbType.VarChar).Value = dbNewRow.ItemArray[3].ToString();
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@Bannedwordchecking", SqlDbType.VarChar).Value = dbNewRow.ItemArray[4].ToString();
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@Allowattachments", SqlDbType.VarChar).Value = dbNewRow.ItemArray[5].ToString();
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@Template", SqlDbType.VarChar).Value = dbNewRow.ItemArray[6].ToString();
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@Group1", SqlDbType.VarChar).Value = dbNewRow.ItemArray[7].ToString();
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@Mailboxsize", SqlDbType.VarChar).Value = dbNewRow.ItemArray[8].ToString();
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@Calendar", SqlDbType.VarChar).Value = dbNewRow.ItemArray[9].ToString();
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@SyncML", SqlDbType.VarChar).Value = dbNewRow.ItemArray[10].ToString();
            //    int i = cmd_SubmitEmployeeDetail.ExecuteNonQuery();
            //}
 
 
            //con.Close();
 
            //SqlDataAdapter adapter = new SqlDataAdapter();
 
            //// A table mapping names the DataTable.
            //adapter.TableMappings.Add("Table","CSVLoader");
 
            //// Open the connection.
            //con.Open();
 
            //GridView1.DataSource = FilecontentDataset.Tables[0];
            //GridView1.DataBind();
 
         //   // Create a SqlCommand to retrieve Suppliers data.
            SqlCommand command = new SqlCommand("select * from CSVLoader", con);
            //  command.CommandType = CommandType.Text;
 
            adapter.SelectCommand = command;
            int i = command.ExecuteNonQuery();
            DataSet DB = new DataSet();
            adapter.Fill(DB);
 
            // Set the SqlDataAdapter's SelectCommand.
            adapter.InsertCommand = command;
 
            // Fill the DataSet.
            // DataSet dataSet = new DataSet("Suppliers");
            // adapter.Fill(FilecontentDataset);
            adapter.Update(FilecontentDataset, "CSV");
 
 
            //   SqlDataAdapter da = new SqlDataAdapter("Insert into CSVLoader (UserName, Password)values(6,6)", con);
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
            DataSet ds = new DataSet();
            // da.Fill(FilecontentDataset, "CSVLoader");
            //   da.Update("CSVLoader","CSV");
            con.Close();
 
 
            SqlDataAdapter myDataAdapter = new SqlDataAdapter();
            myDataAdapter.InsertCommand = new SqlCommand("insert into CSVLoader select * from " + FilecontentDataset.Tables[0] + "" , con);
            SqlCommandBuilder cb = new SqlCommandBuilder(myDataAdapter);
 
          //  myDataAdapter.Update(FilecontentDataset.Tables["CSV"],"CSVLoader");
            myDataAdapter.SelectCommand = new SqlCommand("select * from CSVLOader", con);
            DataSet ds = new DataSet();
 
            myDataAdapter.Fill(ds, "myTableName");
 
          //  ds.Tables.Clear();
           // ds.Tables[0]=(FilecontentDataset.Tables[0]);
 
            //code to modify data in DataSet here
 
            //Without the SqlCommandBuilder this line would fail
            cb.GetInsertCommand();
            myDataAdapter.Update(FilecontentDataset.Tables[0]);
 
 
           // return ds;                                 
        }
        catch (Exception ex)
        {
            Label2.Text = ex.Message.ToString();
 
        }

[haarigee]

DataSet FilecontentDataset = new DataSet();
        String fileName = string.Empty;
 
        try
        {
            string full = Path.GetFullPath(PathtoTextFile.PostedFile.FileName);
            string file = Path.GetFileName(PathtoTextFile.PostedFile.FileName);
            string dir = Path.GetDirectoryName(PathtoTextFile.PostedFile.FileName);
 
            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=\"" + dir + "\\\";"    + "Extended Properties=\"text;HDR=YES;FMT=Delimited\"";
 
            //create the database query
            string query = "SELECT * FROM " + file;
 
            //create a DataTable to hold the query results
            DataTable dTable = new DataTable();
 
            //create an OleDbDataAdapter to execute the query
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);
            dAdapter.Fill(FilecontentDataset, "CSV");
 
            SqlConnection con = OPENSQLCONNECTION();
            con.Open();
 
 
            //foreach (DataRow dbNewRow in FilecontentDataset.Tables[0].Rows)
            //{
            //    SqlCommand cmd_SubmitEmployeeDetail = new SqlCommand("LoadCSVDetails", con);
            //    cmd_SubmitEmployeeDetail.CommandType = CommandType.StoredProcedure;
 
 
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@UserName", SqlDbType.VarChar).Value = dbNewRow.ItemArray[0].ToString();
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@Password", SqlDbType.VarChar).Value = dbNewRow.ItemArray[1].ToString();
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@FullName", SqlDbType.VarChar).Value = dbNewRow.ItemArray[2].ToString();
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@Restrictionlistname", SqlDbType.VarChar).Value = dbNewRow.ItemArray[3].ToString();
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@Bannedwordchecking", SqlDbType.VarChar).Value = dbNewRow.ItemArray[4].ToString();
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@Allowattachments", SqlDbType.VarChar).Value = dbNewRow.ItemArray[5].ToString();
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@Template", SqlDbType.VarChar).Value = dbNewRow.ItemArray[6].ToString();
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@Group1", SqlDbType.VarChar).Value = dbNewRow.ItemArray[7].ToString();
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@Mailboxsize", SqlDbType.VarChar).Value = dbNewRow.ItemArray[8].ToString();
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@Calendar", SqlDbType.VarChar).Value = dbNewRow.ItemArray[9].ToString();
            //    cmd_SubmitEmployeeDetail.Parameters.Add("@SyncML", SqlDbType.VarChar).Value = dbNewRow.ItemArray[10].ToString();
            //    int i = cmd_SubmitEmployeeDetail.ExecuteNonQuery();
            //}
 
 
            //con.Close();
 
            //SqlDataAdapter adapter = new SqlDataAdapter();
 
            //// A table mapping names the DataTable.
            //adapter.TableMappings.Add("Table","CSVLoader");
 
            //// Open the connection.
            //con.Open();
 
            //GridView1.DataSource = FilecontentDataset.Tables[0];
            //GridView1.DataBind();
 
         //   // Create a SqlCommand to retrieve Suppliers data.
            SqlCommand command = new SqlCommand("select * from CSVLoader", con);
            //  command.CommandType = CommandType.Text;
 
            adapter.SelectCommand = command;
            int i = command.ExecuteNonQuery();
            DataSet DB = new DataSet();
            adapter.Fill(DB);
 
            // Set the SqlDataAdapter's SelectCommand.
            adapter.InsertCommand = command;
 
            // Fill the DataSet.
            // DataSet dataSet = new DataSet("Suppliers");
            // adapter.Fill(FilecontentDataset);
            adapter.Update(FilecontentDataset, "CSV");
 
 
            //   SqlDataAdapter da = new SqlDataAdapter("Insert into CSVLoader (UserName, Password)values(6,6)", con);
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
            DataSet ds = new DataSet();
            // da.Fill(FilecontentDataset, "CSVLoader");
            //   da.Update("CSVLoader","CSV");
            con.Close();
 
 
            SqlDataAdapter myDataAdapter = new SqlDataAdapter();
            myDataAdapter.InsertCommand = new SqlCommand("insert into CSVLoader select * from " + FilecontentDataset.Tables[0] + "" , con);
            SqlCommandBuilder cb = new SqlCommandBuilder(myDataAdapter);
 
          //  myDataAdapter.Update(FilecontentDataset.Tables["CSV"],"CSVLoader");
            myDataAdapter.SelectCommand = new SqlCommand("select * from CSVLOader", con);
            DataSet ds = new DataSet();
 
            myDataAdapter.Fill(ds, "myTableName");
 
          //  ds.Tables.Clear();
           // ds.Tables[0]=(FilecontentDataset.Tables[0]);
 
            //code to modify data in DataSet here
 
            //Without the SqlCommandBuilder this line would fail
            cb.GetInsertCommand();
            myDataAdapter.Update(FilecontentDataset.Tables[0]);
 
 
           // return ds;                                 
        }
        catch (Exception ex)
        {
            Label2.Text = ex.Message.ToString();
 
        }

[madankarmukta]

Hi,

Check whether you followed these steps

1) create the connection obj
2) create the command object; assign the connection to this command obj
3) assign the command text as the name of the stored procedure, add the input parameters to the command object.
There Input parameters will be the values u wann to insert to the DB table.
4) create the Stored procedure which will insert the provided input values to the required table.
5) Call the ExecuteNonQuery and get your work done.

Hope this will help!

Thanks!

[tlhintoq]

http://bytes.com/topic/net/insights/...e-your-program

http://bytes.com/topic/net/insights/...e-your-program

[tlhintoq]

Please don't double post your messages.

This makes it hard to coordinate efforts to help you.

The two threads have been merged