SqlDataAdapter上调用存储过程

大多数的时候，我们对数据库的更新，读取等都是通过存储过程来操作的。存储过程更加快速和容易调整。这样就要求熟悉如何在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 table
            SqlDataAdapter 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 adapter
            DataSet 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 file
            DataSet 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 table
            DataSet 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 connection
            conn.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;
}