C#调用存储过程

用命令对象调用存储过程，就是定义存储过程的名字，给过程的每一个参数添加参数定义，然后再用SqlCommand命令执行。

下面实例主要以数据库Region表中插入更新的存储过程。

首先给项目添加一个资源文件命名为Strings，在添加存储过程语句：

<data name="CreateSprocs" xml:space="preserve">
    <value>IF OBJECT_ID('RegionInsert') IS NULL
EXEC ('
--
-- Procedure which inserts a region record and returns the key
--
CREATE PROCEDURE RegionInsert(@RegionDescription NCHAR(50),
                              @RegionID INTEGER OUTPUT)AS
  SET NOCOUNT OFF;

  SELECT @RegionID = MAX ( RegionID ) + 1
                       FROM Region ;
     
  INSERT INTO Region(RegionID, RegionDescription)
    VALUES(@RegionID, @RegionDescription);')

IF OBJECT_ID('RegionUpdate') IS NULL
EXEC ('
--
-- Procedure to update the description of a region
--
CREATE PROCEDURE RegionUpdate(@RegionID INTEGER,
                              @RegionDescription NCHAR(50))AS
  SET NOCOUNT OFF;
  
  UPDATE Region
    SET RegionDescription = @RegionDescription
    WHERE RegionID = @RegionID;');</value>
  </data>

　　在App.config文件里面配置数据库连接语句：

<connectionStrings>
    <clear/>
    <add name="MyDBPractice" connectionString="server=(local);integrated security=SSPI;database=MyDBPractice;" providerName="System.Data.SqlClient"/>
  </connectionStrings>

　　添加类DBConnection用于创建工厂：

 public class DBConnection
    {
        public static DbConnection GetDBConnection(string name)
        {
            ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[name];
            DbProviderFactory factory = DbProviderFactories.GetFactory(settings.ProviderName);
            DbConnection conn = factory.CreateConnection();
            conn.ConnectionString = settings.ConnectionString;
            return conn;
        }
    }

　　在Program.cs类中添加如下代码：

 class Program
    {
        static void Main(string[] args)
        {
            try
            {
                using (SqlConnection conn = (SqlConnection)DBConnection.GetDBConnection("MyDBPractice"))
                {
                    conn.Open();
                    InitDBData(conn);//ensure the procedure exist
                    SqlCommand updateCommand = GenerateUpdateCommand(conn);
                    SqlCommand insertCommand = GenerateInsertCommand(conn);
                    DumpRegion(conn, "check the result begin:");
                    insertCommand.Parameters["@RegionDescription"].Value = "South West";
                    insertCommand.ExecuteNonQuery();
                    int newRegionID = (int)insertCommand.Parameters["@RegionID"].Value;
                    updateCommand.Parameters["@RegionDescription"].Value = "England";
                    updateCommand.Parameters["@RegionID"].Value = newRegionID;
                    updateCommand.ExecuteNonQuery();
                }
            }
            catch (SqlException se)
            {
                Console.WriteLine(se.Errors);
            }
        }

        /// <summary>
        /// check the procedure
        /// </summary>
        /// <param name="conn"></param>
        private static void InitDBData(SqlConnection conn)
        {
            SqlCommand cmd = new SqlCommand(Strings.CreateSprocess, conn);
            cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// create a command that will update a region record
        /// </summary>
        /// <param name="conn">database connection</param>
        /// <returns>a command</returns>
        private static SqlCommand GenerateUpdateCommand(SqlConnection conn)
        {
            SqlCommand sCommand = new SqlCommand("RegionUpdate", conn);
            sCommand.CommandType = CommandType.StoredProcedure;
            sCommand.Parameters.Add(new SqlParameter("@RegionID",SqlDbType.Int,0,"RegionID"));
            sCommand.Parameters.Add(new SqlParameter("@RegionDescription", SqlDbType.NChar, 50, "RegionDescription"));
            sCommand.UpdatedRowSource = UpdateRowSource.None;//do not apply the result to dataset
            return sCommand;
        }

        private static SqlCommand GenerateInsertCommand(SqlConnection conn)
        {
            SqlCommand sCommand = new SqlCommand("RegionInsert", conn);
            sCommand.CommandType = CommandType.StoredProcedure;
            sCommand.Parameters.Add(new SqlParameter("@RegionID", SqlDbType.Int, 0, ParameterDirection.Output, false, 0, 0, 
                "RegionID",DataRowVersion.Default,null));
            sCommand.Parameters.Add(new SqlParameter("@RegionDescription", SqlDbType.NChar, 50, "RegionDescription"));
            return sCommand;
        }

        private static void DumpRegion(SqlConnection conn, string message)
        {
            SqlCommand sCommand = new SqlCommand("select t.* from Region", conn);
            SqlDataReader sqlReader = sCommand.ExecuteReader();
            Console.WriteLine(message);
            do
            {
                sqlReader.Read();
                Console.WriteLine("{0, -20},{1,-40}", sqlReader[0], sqlReader[1]);
            } while (sqlReader.Read());
        }
    }