将dataGridView中的添加/删除等修改保存至数据库

 

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1Class Form1

     Dim ds As DataSet
     Dim da As SqlDataAdapter
     Dim cb As SqlCommandBuilder
     Dim cmd As SqlCommand
     Dim conn As New SqlConnection("data source=localhost;Database=HAWK;Integrated Security=SSPI;")


     Private Sub Form1_Load()Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
          conn.Open()
          cmd = conn.CreateCommand
          cmd.CommandText = "Select sID,sName,class,pro from studentInfo"
          da = New SqlDataAdapter(cmd)
          ds = New DataSet()
          da.Fill(ds)
          DataGridView1.DataSource = ds.Tables(0)

     End Sub

     Private Sub Button1_Click()Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
          cmd.CommandText = "Select sID,sName,class,pro from studentInfo"
          da = New SqlDataAdapter(cmd)
          cb = New SqlCommandBuilder(da)
          da.Update(ds)
          DataGridView1.Update()

     End Sub
End Class

---------------------------------------------------------------------------------------------------------

C#示例代码如下：

using System.Data;
using System.Data.SqlClient;

public partial class Form1 : Form
    {

        DataSet ds =new DataSet();
        SqlDataAdapter da=new SqlDataAdapter();
        SqlCommandBuilder cb=new SqlCommandBuilder();
        SqlCommand cmd=new SqlCommand();
        private string sql = "select * from TEST_Table";
        private string connString = "Data Source=.;User ID=sa;Password=123;Initial Catalog=Test;";

private void Form1_Load(object sender, EventArgs e)
        {

            SqlConnection conn = new SqlConnection(connString);
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);

           // cmd = conn.CreateCommand();
            // cmd.CommandText = "select * from TEST_Table";
            // SqlDataAdapter da = new SqlDataAdapter(cmd);

            // private string tableName = "test";
            //da.Fill(ds, "test");
            //dataGridView1.DataSource = ds.Tables[tableName];

            da.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];
        }

   private void button1_Click(object sender, EventArgs e) //保存修改
        {
            SqlConnection conn = new SqlConnection(connString);
            cmd = conn.CreateCommand();
            cmd.CommandText = "select * from Test_Table";
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            SqlCommandBuilder cb = new SqlCommandBuilder(da);
         da.Update(ds);
         dataGridView1.Update();
        }
    }

--------------------------------------------------------------------------------------结束

 

保存的重点就在于使用了SqlCommandBuilder 成员，SqlCommandBuilder 成员的说明如下：

 

SqlCommandBuilder 成员。

备注

SqlDataAdapter 不会自动生成实现 DataSet 的更改与关联的 SQL Server 实例之间的协调所需的 Transact-SQL 语句。但是，如果设置了 SqlDataAdapter 的 SelectCommand 属性，则可以创建一个 SqlCommandBuilder 对象来自动生成用于单表更新的 Transact-SQL 语句。然后，SqlCommandBuilder 将生成其他任何未设置的 Transact-SQL 语句。

一旦设置 DataAdapter 属性，SqlCommandBuilder 就将其自身注册为 RowUpdating 事件的侦听器。一次只能将一个 SqlDataAdapter 与一个 SqlCommandBuilder 对象（或相反）互相关联。

为了生成 INSERT、UPDATE 或 DELETE 语句，SqlCommandBuilder 会自动使用 SelectCommand 属性来检索所需的元数据集。如果在检索元数据后（例如在第一次更新后）更改 SelectCommand，则应调用 RefreshSchema 方法来更新元数据。

SelectCommand 还必须至少返回一个主键列或唯一的列。如果什么都没有返回，就会产生 InvalidOperation 异常，不生成命令。

SqlCommandBuilder 还使用由 SelectCommand 引用的 Connection、CommandTimeout 和 Transaction 属性。如果修改了任何这些属性或者替换了 SelectCommand 本身，用户则应调用 RefreshSchema。否则，InsertCommand、UpdateCommand 和 DeleteCommand 属性将保留它们以前的值。

如果调用 Dispose，则会解除 SqlCommandBuilder 与 SqlDataAdapter 的关联，并且不再使用所生成的命令。

示例

[Visual Basic, C#, C++] 下面的示例使用 SqlCommand 以及 SqlDataAdapter 和 SqlConnection 从数据源选择行。给该示例传递一个初始化的 DataSet、一个连接字符串、一个查询字符串（它是一个 Transact-SQL SELECT 语句）和一个包含数据库表名称的字符串。然后该示例创建一个 SqlCommandBuilder。

[Visual Basic] 
Public Shared Function SelectSqlSrvRows(myConnection As String, mySelectQuery As String, myTableName As String) As DataSet
    Dim myConn As New SqlConnection(myConnection)
    Dim myDataAdapter As New SqlDataAdapter()
    myDataAdapter.SelectCommand = New SqlCommand(mySelectQuery, myConn)
    Dim cb As SqlCommandBuilder = New SqlCommandBuilder(myDataAdapter)

    myConn.Open()

    Dim ds As DataSet = New DataSet
    myDataAdapter.Fill(ds, myTableName)

    ' Code to modify data in DataSet here 

    ' Without the SqlCommandBuilder this line would fail.
    myDataAdapter.Update(ds, myTableName)

    myConn.Close()
End Function 'SelectSqlSrvRows

[C#] 
public static DataSet SelectSqlSrvRows(string myConnection, string mySelectQuery, string myTableName)
{
   SqlConnection myConn = new SqlConnection(myConnection);
   SqlDataAdapter myDataAdapter = new SqlDataAdapter();
   myDataAdapter.SelectCommand = new SqlCommand(mySelectQuery, myConn);
   SqlCommandBuilder cb = new SqlCommandBuilder(myDataAdapter);

   myConn.Open();

   DataSet ds = new DataSet();
   myDataAdapter.Fill(ds, myTableName);

   //code to modify data in DataSet here

   //Without the SqlCommandBuilder this line would fail
   myDataAdapter.Update(ds, myTableName);

   myConn.Close();

   return ds;
}
 

 

 

要求

命名空间: System.Data.SqlClient

平台: Windows 98, Windows NT 4.0, Windows ME, Windows 2000, Windows XP Home Edition, Windows XP Professional, Windows Server 2003 系列, .NET Framework 精简版

程序集: System.Data (在 System.Data.dll 中)

请参见

SqlCommandBuilder 成员 | System.Data.SqlClient 命名空间