大多數的時候，我們對數據庫的更新，讀取等都是通過存儲過程來操作的。存儲過程更加快速和容易調整。這樣就要求熟悉如何在SqlDataAdapter上調用存儲過程，當然也可以不通過SqlDataAdapter來調用，直接調用在前面里面已經說過了。

下面是關于SqlDataAdapter如何更新數據的一個示例：

里面關于配置文件可以參考前面的文章

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Configuration; using System.Data; using System.Data.SqlClient;namespace DataDemo {class Program{private static string GetConnectionStringsConfig(string connectionName){string connectionString = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString.ToString();Console.WriteLine(connectionString);return connectionString;}public static void ManufactureProductDataTable(DataSet ds){DataTable products = new DataTable("Products");products.Columns.Add(new DataColumn("ProductID", typeof(int)));products.Columns.Add(new DataColumn("ProductName", typeof(string)));products.Columns.Add(new DataColumn("SupplierID", typeof(int)));products.Columns.Add(new DataColumn("CategoryID", typeof(int)));products.Columns.Add(new DataColumn("QuantityPerUnit", typeof(string)));products.Columns.Add(new DataColumn("UnitPrice", typeof(decimal)));products.Columns.Add(new DataColumn("UnitsInStock", typeof(short)));products.Columns.Add(new DataColumn("UnitsOnOrder", typeof(short)));products.Columns.Add(new DataColumn("RecordLevel", typeof(short)));products.Columns.Add(new DataColumn("Discontinued", typeof(bool)));ds.Tables.Add(products);}private static SqlCommand GenerateSelectCommand(SqlConnection conn){SqlCommand cmd = new SqlCommand("RegionSelect", conn);cmd.CommandType = CommandType.StoredProcedure;cmd.UpdatedRowSource = UpdateRowSource.None;return cmd;}private static SqlCommand GenerateInsertCommand(SqlConnection conn){SqlCommand cmd = new SqlCommand("RegionInsert", conn);cmd.CommandType = CommandType.StoredProcedure;SqlParameter para1 = new SqlParameter("@RegionDescription", SqlDbType.NChar);para1.Value = "North West";cmd.Parameters.Add(para1);SqlParameter para2 = new SqlParameter("@RegionID", SqlDbType.Int);para2.Direction = ParameterDirection.Output;cmd.Parameters.Add(para2);cmd.UpdatedRowSource = UpdateRowSource.OutputParameters;return cmd;}static void Main(string[] args){string source = GetConnectionStringsConfig("Northwind");string select = "SELECT * FROM Products";SqlConnection conn = new SqlConnection(source);conn.Open();// Use customized tableSqlDataAdapter cmd = new SqlDataAdapter(select, conn);DataSet ds = new DataSet();ManufactureProductDataTable(ds);cmd.Fill(ds, "Products");foreach (DataRow row in ds.Tables["Products"].Rows)Console.WriteLine("'{0}' from {1}", row[0], row[1]);// Use stored procedure on adapterDataSet ds2 = new DataSet();SqlDataAdapter da2 = new SqlDataAdapter();da2.SelectCommand = GenerateSelectCommand(conn);da2.Fill(ds2, "Region");foreach (DataRow row in ds2.Tables["Region"].Rows)Console.WriteLine("'{0}' is {1}", row[0], row[1]);// Use dataset to read XML fileDataSet ds3 = new DataSet();ds3.ReadXml(".\\MyData.xml");foreach (DataRow row in ds3.Tables[0].Rows)Console.WriteLine("'{0}' comes {1}", row[0], row[1]);// Insert a row to region tableDataSet ds4 = new DataSet();SqlDataAdapter da3 = new SqlDataAdapter();da3.InsertCommand = GenerateInsertCommand(conn);da3.InsertCommand.ExecuteNonQuery();da3.SelectCommand = GenerateSelectCommand(conn);da3.Fill(ds4, "Region");foreach (DataRow row in ds4.Tables["Region"].Rows)Console.WriteLine("'{0}' is {1}", row[0], row[1]);// Close SQL connectionconn.Close();Console.ReadKey();}} }

?

InsertCommand調用的時候不要忘記這句話：

da3.InsertCommand.ExecuteNonQuery();

不然是不執行的，但是selectCommand卻是可以不用這句話的。

在調用

da3.InsertCommand.ExecuteNonQuery()

之前，要確保conn是open的，也就是這句話要有

conn.Open();

開始沒注意，害的我找了半天不知道錯哪兒了o(╯□╰)o

?

下面這個是來自MSDN上SqlDataAdapter的一個示例，展示各種command

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;// 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; }

轉載于:https://www.cnblogs.com/herbert/archive/2010/07/14/1777292.html

總結

以上是生活随笔為你收集整理的SqlDataAdapter上调用存储过程的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。