locked
Add record RRS feed

  • Question

  • Can I add a record in to a sql table by using only a DataRow?
    Tuesday, July 10, 2012 10:22 AM

Answers

  • You need to add it to command parameters to insert a datarow into sql table. Like this
    string InsertTrans = "INSERT INTO RAS2_Trans (ENV_NO, LINE_NO) Values(@EnvNo, @LineNo)";
    
    SqlCommand cmd = new SqlCommand(InsertTrans, conn1);
    
    
    
     
    
    foreach (DataRow drRow in DS_DocLines.Tables[0].Rows)
    
    {
    
    SqlParameter theEnvNo = new SqlParameter("@EnvNo", sqlDbType.TypeHere, DataLength);
    
    theEnvNo.Value = Convert.ToInt32(drRow["Env_No"]);
    
    cmd.Parameters.Add(theEnvNo);
    
     
    
    SqlParameter theLineNo = new SqlParameter("@LineNo", sqlDbType.TypeHere, DataLength);
    
    theLineNo.Value = Convert.ToInt32(drRow["Line_No"]);
    
    cmd.Parameters.Add(theLineNo);
    
    //Execuete the SQL command (InsertTrans)
    
    cmd.ExecuteNonQuery();
    
    cmd.Parameters.Clear();
    
    }
    

    • Proposed as answer by Srithar Wednesday, July 11, 2012 4:32 AM
    • Marked as answer by Bob Shen Friday, July 27, 2012 8:02 AM
    Tuesday, July 10, 2012 11:43 AM
  • you can also try this,

    using (SqlConnection connection = new SqlConnection(@"Data Source=[your server];Initial Catalog=[your database];Integrated Security=true;"))
                {
                    using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
                    {
                        dataAdapter.SelectCommand = new SqlCommand("select Foo, Bar from dbo.Foos", connection);
                        dataAdapter.InsertCommand = new SqlCommand("insert into dbo.Foos (Foo, Bar) values (@Foo, @Bar)", connection);
                        dataAdapter.InsertCommand.Parameters.Add(new SqlParameter("Foo", SqlDbType.Int, 4, "Foo"));
                        dataAdapter.InsertCommand.Parameters.Add(new SqlParameter("Bar", SqlDbType.NText, 50, "Bar"));
    
                        using (DataSet dataSet = new DataSet())
                        {
                            dataAdapter.Fill(dataSet);
    
                            Console.WriteLine("There are {0} rows in the table", dataSet.Tables[0].Rows.Count);
    
                            DataRow newRow = dataSet.Tables[0].NewRow();
                            newRow["Foo"] = 5;
                            newRow["Bar"] = "Hello World!";
                            dataSet.Tables[0].Rows.Add(newRow);
    
                            dataAdapter.Update(dataSet);
                        }                
    
                        //Just to prove we inserted
                        using (DataSet newDataSet = new DataSet())
                        {
                            dataAdapter.Fill(newDataSet);
                            Console.WriteLine("There are {0} rows in the table", newDataSet.Tables[0].Rows.Count);                
                        }                
                    }
                }
                Console.ReadLine();        
            }
    

    • Marked as answer by Bob Shen Friday, July 27, 2012 8:02 AM
    Tuesday, July 10, 2012 11:48 AM

All replies

  • MSDN Answer for your question

    Mark Answered, if it solves your question and Vote if you found it helpful.
    Rohit Arora

    Tuesday, July 10, 2012 10:34 AM
  • No, I need to add in to SQL table.
    Tuesday, July 10, 2012 11:00 AM
  • You need to add it to command parameters to insert a datarow into sql table. Like this
    string InsertTrans = "INSERT INTO RAS2_Trans (ENV_NO, LINE_NO) Values(@EnvNo, @LineNo)";
    
    SqlCommand cmd = new SqlCommand(InsertTrans, conn1);
    
    
    
     
    
    foreach (DataRow drRow in DS_DocLines.Tables[0].Rows)
    
    {
    
    SqlParameter theEnvNo = new SqlParameter("@EnvNo", sqlDbType.TypeHere, DataLength);
    
    theEnvNo.Value = Convert.ToInt32(drRow["Env_No"]);
    
    cmd.Parameters.Add(theEnvNo);
    
     
    
    SqlParameter theLineNo = new SqlParameter("@LineNo", sqlDbType.TypeHere, DataLength);
    
    theLineNo.Value = Convert.ToInt32(drRow["Line_No"]);
    
    cmd.Parameters.Add(theLineNo);
    
    //Execuete the SQL command (InsertTrans)
    
    cmd.ExecuteNonQuery();
    
    cmd.Parameters.Clear();
    
    }
    

    • Proposed as answer by Srithar Wednesday, July 11, 2012 4:32 AM
    • Marked as answer by Bob Shen Friday, July 27, 2012 8:02 AM
    Tuesday, July 10, 2012 11:43 AM
  • you can also try this,

    using (SqlConnection connection = new SqlConnection(@"Data Source=[your server];Initial Catalog=[your database];Integrated Security=true;"))
                {
                    using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
                    {
                        dataAdapter.SelectCommand = new SqlCommand("select Foo, Bar from dbo.Foos", connection);
                        dataAdapter.InsertCommand = new SqlCommand("insert into dbo.Foos (Foo, Bar) values (@Foo, @Bar)", connection);
                        dataAdapter.InsertCommand.Parameters.Add(new SqlParameter("Foo", SqlDbType.Int, 4, "Foo"));
                        dataAdapter.InsertCommand.Parameters.Add(new SqlParameter("Bar", SqlDbType.NText, 50, "Bar"));
    
                        using (DataSet dataSet = new DataSet())
                        {
                            dataAdapter.Fill(dataSet);
    
                            Console.WriteLine("There are {0} rows in the table", dataSet.Tables[0].Rows.Count);
    
                            DataRow newRow = dataSet.Tables[0].NewRow();
                            newRow["Foo"] = 5;
                            newRow["Bar"] = "Hello World!";
                            dataSet.Tables[0].Rows.Add(newRow);
    
                            dataAdapter.Update(dataSet);
                        }                
    
                        //Just to prove we inserted
                        using (DataSet newDataSet = new DataSet())
                        {
                            dataAdapter.Fill(newDataSet);
                            Console.WriteLine("There are {0} rows in the table", newDataSet.Tables[0].Rows.Count);                
                        }                
                    }
                }
                Console.ReadLine();        
            }
    

    • Marked as answer by Bob Shen Friday, July 27, 2012 8:02 AM
    Tuesday, July 10, 2012 11:48 AM