How to add a new row to an existing table using c# sql server

Question

I need to write a program. A part of the program is to write to an sql database (.mdf). I had a lot of trouble trying to add a new row to my table (called: "Data"). Here is the code:

...
DataSet ds = new DataSet();
System.Data.SqlClient.SqlDataAdapter da;
DataRow dRow;
string sql = "SELECT * From Data";
da = new System.Data.SqlClient.SqlDataAdapter(sql, con);
...
System.Data.SqlClient.SqlCommandBuilder cb;
cb = new System.Data.SqlClient.SqlCommandBuilder(da);
dRow = ds.Tables["Data"].NewRow();
dRow[0] = "my_data1";
dRow[1] = "my_data2";
dRow[2] = "my_data3";
...
ds.Tables["Data"].Rows.Add(dRow);
da.Update(ds, "Data");
...

I execute this code, but the data didn't get saved to the table. Does anyone know how to enter a new row to the table and to save it? Many Thanks!

Answer 1

You need an InsertCommand in your SqlDataAdapter.

EDIT:

Here's a quick example I whipped up. There are many others out there, but this should get you going. It assumes that you have a table (dbo.Foos) with two columns (Foo int, Bar nvarchar(50)).

namespace DataAdapterSample
{
    using System;
    using System.Data;
    using System.Data.SqlClient;

    class Program
    {
        static void Main(string[] args)
        {
            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();        
        }
    }
}

Answer 2

Two things I'm seeing, you're not initializing your Dataset (ds) or SqlDataAdapter (da) in anyway (unless you're simply leaving that out for post simplification). Part of the initialization of the da will be giving it an actual sql command.

Answer 3

Try instead to set

dRow = new DataRow();

instead of

dRow = ds.Tables["Data"].NewRow();

and change

da.Update(ds, "Data");

to

da.Update(ds);

Answer 4

Hello Aaron Daniels, I tried your code and make sure that myDataSet is full of the data put this code adds nothing to my data table ...so whats wrong!

            string conn=......
            using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
            {

                dataAdapter.SelectCommand = new SqlCommand("select date,ShortSMA from dbo.output", conn);
                dataAdapter.InsertCommand = new SqlCommand("insert into dbo.output (date, ShortSMA) values (@date,@ShortSMA)", conn);
                dataAdapter.InsertCommand.Parameters.Add(new SqlParameter("date", SqlDbType.DateTime, 22, "date"));
                dataAdapter.InsertCommand.Parameters.Add(new SqlParameter("ShortSMA", SqlDbType.Float, 5, "ShortSMA"));
                DataSet dataSet = new DataSet();
                dataSet.Clear();
                dataAdapter.Fill(dataSet, "output");
                foreach (KeyValuePair<string, float> prl in SSMA_date)
                {
                    DataRow row = dataSet.Tables["output"].NewRow();
                    row["date"] = DateTime.Parse(prl.Key);
                    row["ShortSMA"] = prl.Value;
                    dataSet.Tables["output"].Rows.Add(row);
                    dataAdapter.Update(dataSet, "output");

                }