SqlDataAdapter.UpdateCommand 属性（极易出错的地方）

public static SqlDataAdapter CreateCustomerAdapter( SqlConnection connection){ SqlDataAdapter adapter = new SqlDataAdapter(); // Create the SelectCommand. SqlCommand command = new SqlCommand("SELECT * FROM Customers " + "WHERE Country = @Country AND City = @City", connection); // Add the parameters for the SelectCommand. command.Parameters.Add("@Country", SqlDbType.NVarChar, 15); command.Parameters.Add("@City", SqlDbType.NVarChar, 15); adapter.SelectCommand = command; //以上查询语句中的parameters没有注明对应的字段名称，没错，可以实现查询。否则会出现异常，导致Update方法无法执行。 // Create the InsertCommand. command = new SqlCommand( "INSERT INTO Customers (CustomerID, CompanyName) " + "VALUES (@CustomerID, @CompanyName)", connection); // Add the parameters for the InsertCommand. command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID"); command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName"); adapter.InsertCommand = command; // Create the UpdateCommand. command = new SqlCommand( "UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " + "WHERE CustomerID = @oldCustomerID", connection); // Add the parameters for the UpdateCommand. command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID"); command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName"); SqlParameter parameter = command.Parameters.Add( "@oldCustomerID", SqlDbType.NChar, 5, "CustomerID"); parameter.SourceVersion = DataRowVersion.Original; adapter.UpdateCommand = command; // Create the DeleteCommand. command = new SqlCommand( "DELETE FROM Customers WHERE CustomerID = @CustomerID", connection); // Add the parameters for the DeleteCommand. parameter = command.Parameters.Add( "@CustomerID", SqlDbType.NChar, 5, "CustomerID"); parameter.SourceVersion = DataRowVersion.Original; adapter.DeleteCommand = command; return adapter;}

 

调用update方法

public DataSet CreateCmdsAndUpdate(DataSet dataSet, string connectionString, string queryString) { using (OleDbConnection connection = new OleDbConnection(connectionString)) { OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = new OleDbCommand(queryString, connection); OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter); connection.Open(); DataSet customers = new DataSet(); adapter.Fill(customers); //code to modify data in dataset here adapter.Update(customers); return customers; }}

 

 

实例：

public DataSet InsertValues() { SqlConnection con = new SqlConnection(Configuration.Conn); SqlDataAdapter sdp = new SqlDataAdapter(); DataSet dt = new DataSet(); SqlCommand comm = new SqlCommand(); comm.Connection = con; comm.CommandText = "select Names,Address,Pid,Image from Users"; sdp.SelectCommand = comm;//首先要指定selectitem，并且字段要制定清楚，和insert字段个数和名称一致 SqlCommandBuilder scom = new SqlCommandBuilder(sdp); sdp.Fill(dt, "Users"); comm.CommandText = "Insert into Users values(@Pid,@Names,@Address,@Image)"; SqlParameter[] pars = new SqlParameter[] { new SqlParameter("@Pid",SqlDbType.Int,4,"Pid") ,new SqlParameter("@Names",SqlDbType.VarChar,10,"Names") ,new SqlParameter("@Address",SqlDbType.NVarChar,20,"Address") ,new SqlParameter("@Image",SqlDbType.Image,200,"Image") }; pars.ToList<SqlParameter>().ForEach(parm => comm.Parameters.Add(parm)); DataRow dr = null; for (int i = 1; i < 3; i++) { dr = dt.Tables[0].NewRow(); dr["Names"] = "Jeep" + i.ToString(); dr["Address"] = "第" + i.ToString() + "街道"; dr["Pid"] = i + 8; dt.Tables[0].Rows.Add(dr); }//通过对表值的修改，实现方法Update（table) SqlCommandBuilder icom = new SqlCommandBuilder(sdp); sdp.InsertCommand = comm;//insert com语句 sdp.Update(dt.Tables["Users"]); dt.AcceptChanges(); return dt; }