如何使用c＃sql server向现有表中添加新行 [英] How to add a new row to an existing table using c# sql server

问题描述

我需要写一个程序。程序的一部分是写入一个sql数据库（.mdf）。
我试图添加一个新行到我的表（称为：数据）有很多麻烦。这里是代码：

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?

推荐答案

您需要一个 InsertCommand 在您的SqlDataAdapter中。

You need an InsertCommand in your SqlDataAdapter.

EDIT：

这是一个快速示例。还有很多其他的，但这应该让你去。它假定你有一个有两列（Foo int，Bar nvarchar（50））的表（dbo.Foos）。

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();        
        }
    }
}

这篇关于如何使用c＃sql server向现有表中添加新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！