By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,561 Members | 3,083 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,561 IT Pros & Developers. It's quick & easy.

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

P: 2
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.

Expand|Select|Wrap|Line Numbers
  1. DataSet FilecontentDataset = new DataSet();
  2.         String fileName = string.Empty;
  3.  
  4.         try
  5.         {
  6.             string full = Path.GetFullPath(PathtoTextFile.PostedFile.FileName);
  7.             string file = Path.GetFileName(PathtoTextFile.PostedFile.FileName);
  8.             string dir = Path.GetDirectoryName(PathtoTextFile.PostedFile.FileName);
  9.  
  10.             string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=\"" + dir + "\\\";"    + "Extended Properties=\"text;HDR=YES;FMT=Delimited\"";
  11.  
  12.             //create the database query
  13.             string query = "SELECT * FROM " + file;
  14.  
  15.             //create a DataTable to hold the query results
  16.             DataTable dTable = new DataTable();
  17.  
  18.             //create an OleDbDataAdapter to execute the query
  19.             OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);
  20.             dAdapter.Fill(FilecontentDataset, "CSV");
  21.  
  22.             SqlConnection con = OPENSQLCONNECTION();
  23.             con.Open();
  24.  
  25.  
  26.             //foreach (DataRow dbNewRow in FilecontentDataset.Tables[0].Rows)
  27.             //{
  28.             //    SqlCommand cmd_SubmitEmployeeDetail = new SqlCommand("LoadCSVDetails", con);
  29.             //    cmd_SubmitEmployeeDetail.CommandType = CommandType.StoredProcedure;
  30.  
  31.  
  32.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@UserName", SqlDbType.VarChar).Value = dbNewRow.ItemArray[0].ToString();
  33.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@Password", SqlDbType.VarChar).Value = dbNewRow.ItemArray[1].ToString();
  34.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@FullName", SqlDbType.VarChar).Value = dbNewRow.ItemArray[2].ToString();
  35.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@Restrictionlistname", SqlDbType.VarChar).Value = dbNewRow.ItemArray[3].ToString();
  36.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@Bannedwordchecking", SqlDbType.VarChar).Value = dbNewRow.ItemArray[4].ToString();
  37.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@Allowattachments", SqlDbType.VarChar).Value = dbNewRow.ItemArray[5].ToString();
  38.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@Template", SqlDbType.VarChar).Value = dbNewRow.ItemArray[6].ToString();
  39.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@Group1", SqlDbType.VarChar).Value = dbNewRow.ItemArray[7].ToString();
  40.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@Mailboxsize", SqlDbType.VarChar).Value = dbNewRow.ItemArray[8].ToString();
  41.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@Calendar", SqlDbType.VarChar).Value = dbNewRow.ItemArray[9].ToString();
  42.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@SyncML", SqlDbType.VarChar).Value = dbNewRow.ItemArray[10].ToString();
  43.             //    int i = cmd_SubmitEmployeeDetail.ExecuteNonQuery();
  44.             //}
  45.  
  46.  
  47.             //con.Close();
  48.  
  49.             //SqlDataAdapter adapter = new SqlDataAdapter();
  50.  
  51.             //// A table mapping names the DataTable.
  52.             //adapter.TableMappings.Add("Table","CSVLoader");
  53.  
  54.             //// Open the connection.
  55.             //con.Open();
  56.  
  57.             //GridView1.DataSource = FilecontentDataset.Tables[0];
  58.             //GridView1.DataBind();
  59.  
  60.          //   // Create a SqlCommand to retrieve Suppliers data.
  61.             SqlCommand command = new SqlCommand("select * from CSVLoader", con);
  62.             //  command.CommandType = CommandType.Text;
  63.  
  64.             adapter.SelectCommand = command;
  65.             int i = command.ExecuteNonQuery();
  66.             DataSet DB = new DataSet();
  67.             adapter.Fill(DB);
  68.  
  69.             // Set the SqlDataAdapter's SelectCommand.
  70.             adapter.InsertCommand = command;
  71.  
  72.             // Fill the DataSet.
  73.             // DataSet dataSet = new DataSet("Suppliers");
  74.             // adapter.Fill(FilecontentDataset);
  75.             adapter.Update(FilecontentDataset, "CSV");
  76.  
  77.  
  78.             //   SqlDataAdapter da = new SqlDataAdapter("Insert into CSVLoader (UserName, Password)values(6,6)", con);
  79.             SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
  80.             DataSet ds = new DataSet();
  81.             // da.Fill(FilecontentDataset, "CSVLoader");
  82.             //   da.Update("CSVLoader","CSV");
  83.             con.Close();
  84.  
  85.  
  86.             SqlDataAdapter myDataAdapter = new SqlDataAdapter();
  87.             myDataAdapter.InsertCommand = new SqlCommand("insert into CSVLoader select * from " + FilecontentDataset.Tables[0] + "" , con);
  88.             SqlCommandBuilder cb = new SqlCommandBuilder(myDataAdapter);
  89.  
  90.           //  myDataAdapter.Update(FilecontentDataset.Tables["CSV"],"CSVLoader");
  91.             myDataAdapter.SelectCommand = new SqlCommand("select * from CSVLOader", con);
  92.             DataSet ds = new DataSet();
  93.  
  94.             myDataAdapter.Fill(ds, "myTableName");
  95.  
  96.           //  ds.Tables.Clear();
  97.            // ds.Tables[0]=(FilecontentDataset.Tables[0]);
  98.  
  99.             //code to modify data in DataSet here
  100.  
  101.             //Without the SqlCommandBuilder this line would fail
  102.             cb.GetInsertCommand();
  103.             myDataAdapter.Update(FilecontentDataset.Tables[0]);
  104.  
  105.  
  106.            // return ds;                                 
  107.         }
  108.         catch (Exception ex)
  109.         {
  110.             Label2.Text = ex.Message.ToString();
  111.  
  112.         }
Jan 21 '10 #1
Share this Question
Share on Google+
4 Replies


P: 2
Expand|Select|Wrap|Line Numbers
  1. DataSet FilecontentDataset = new DataSet();
  2.         String fileName = string.Empty;
  3.  
  4.         try
  5.         {
  6.             string full = Path.GetFullPath(PathtoTextFile.PostedFile.FileName);
  7.             string file = Path.GetFileName(PathtoTextFile.PostedFile.FileName);
  8.             string dir = Path.GetDirectoryName(PathtoTextFile.PostedFile.FileName);
  9.  
  10.             string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=\"" + dir + "\\\";"    + "Extended Properties=\"text;HDR=YES;FMT=Delimited\"";
  11.  
  12.             //create the database query
  13.             string query = "SELECT * FROM " + file;
  14.  
  15.             //create a DataTable to hold the query results
  16.             DataTable dTable = new DataTable();
  17.  
  18.             //create an OleDbDataAdapter to execute the query
  19.             OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);
  20.             dAdapter.Fill(FilecontentDataset, "CSV");
  21.  
  22.             SqlConnection con = OPENSQLCONNECTION();
  23.             con.Open();
  24.  
  25.  
  26.             //foreach (DataRow dbNewRow in FilecontentDataset.Tables[0].Rows)
  27.             //{
  28.             //    SqlCommand cmd_SubmitEmployeeDetail = new SqlCommand("LoadCSVDetails", con);
  29.             //    cmd_SubmitEmployeeDetail.CommandType = CommandType.StoredProcedure;
  30.  
  31.  
  32.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@UserName", SqlDbType.VarChar).Value = dbNewRow.ItemArray[0].ToString();
  33.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@Password", SqlDbType.VarChar).Value = dbNewRow.ItemArray[1].ToString();
  34.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@FullName", SqlDbType.VarChar).Value = dbNewRow.ItemArray[2].ToString();
  35.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@Restrictionlistname", SqlDbType.VarChar).Value = dbNewRow.ItemArray[3].ToString();
  36.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@Bannedwordchecking", SqlDbType.VarChar).Value = dbNewRow.ItemArray[4].ToString();
  37.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@Allowattachments", SqlDbType.VarChar).Value = dbNewRow.ItemArray[5].ToString();
  38.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@Template", SqlDbType.VarChar).Value = dbNewRow.ItemArray[6].ToString();
  39.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@Group1", SqlDbType.VarChar).Value = dbNewRow.ItemArray[7].ToString();
  40.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@Mailboxsize", SqlDbType.VarChar).Value = dbNewRow.ItemArray[8].ToString();
  41.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@Calendar", SqlDbType.VarChar).Value = dbNewRow.ItemArray[9].ToString();
  42.             //    cmd_SubmitEmployeeDetail.Parameters.Add("@SyncML", SqlDbType.VarChar).Value = dbNewRow.ItemArray[10].ToString();
  43.             //    int i = cmd_SubmitEmployeeDetail.ExecuteNonQuery();
  44.             //}
  45.  
  46.  
  47.             //con.Close();
  48.  
  49.             //SqlDataAdapter adapter = new SqlDataAdapter();
  50.  
  51.             //// A table mapping names the DataTable.
  52.             //adapter.TableMappings.Add("Table","CSVLoader");
  53.  
  54.             //// Open the connection.
  55.             //con.Open();
  56.  
  57.             //GridView1.DataSource = FilecontentDataset.Tables[0];
  58.             //GridView1.DataBind();
  59.  
  60.          //   // Create a SqlCommand to retrieve Suppliers data.
  61.             SqlCommand command = new SqlCommand("select * from CSVLoader", con);
  62.             //  command.CommandType = CommandType.Text;
  63.  
  64.             adapter.SelectCommand = command;
  65.             int i = command.ExecuteNonQuery();
  66.             DataSet DB = new DataSet();
  67.             adapter.Fill(DB);
  68.  
  69.             // Set the SqlDataAdapter's SelectCommand.
  70.             adapter.InsertCommand = command;
  71.  
  72.             // Fill the DataSet.
  73.             // DataSet dataSet = new DataSet("Suppliers");
  74.             // adapter.Fill(FilecontentDataset);
  75.             adapter.Update(FilecontentDataset, "CSV");
  76.  
  77.  
  78.             //   SqlDataAdapter da = new SqlDataAdapter("Insert into CSVLoader (UserName, Password)values(6,6)", con);
  79.             SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
  80.             DataSet ds = new DataSet();
  81.             // da.Fill(FilecontentDataset, "CSVLoader");
  82.             //   da.Update("CSVLoader","CSV");
  83.             con.Close();
  84.  
  85.  
  86.             SqlDataAdapter myDataAdapter = new SqlDataAdapter();
  87.             myDataAdapter.InsertCommand = new SqlCommand("insert into CSVLoader select * from " + FilecontentDataset.Tables[0] + "" , con);
  88.             SqlCommandBuilder cb = new SqlCommandBuilder(myDataAdapter);
  89.  
  90.           //  myDataAdapter.Update(FilecontentDataset.Tables["CSV"],"CSVLoader");
  91.             myDataAdapter.SelectCommand = new SqlCommand("select * from CSVLOader", con);
  92.             DataSet ds = new DataSet();
  93.  
  94.             myDataAdapter.Fill(ds, "myTableName");
  95.  
  96.           //  ds.Tables.Clear();
  97.            // ds.Tables[0]=(FilecontentDataset.Tables[0]);
  98.  
  99.             //code to modify data in DataSet here
  100.  
  101.             //Without the SqlCommandBuilder this line would fail
  102.             cb.GetInsertCommand();
  103.             myDataAdapter.Update(FilecontentDataset.Tables[0]);
  104.  
  105.  
  106.            // return ds;                                 
  107.         }
  108.         catch (Exception ex)
  109.         {
  110.             Label2.Text = ex.Message.ToString();
  111.  
  112.         }
Jan 21 '10 #2

100+
P: 293
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!
Jan 21 '10 #3

tlhintoq
Expert 2.5K+
P: 3,525
Please don't double post your messages.

This makes it hard to coordinate efforts to help you.

The two threads have been merged
Jan 21 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.