ADO.NET中调用存储过程

下面这个例子展示如何在ADO.NET调用存储过程。我只选择了一种方式，说实话不太喜欢多样化的方式去处理问题，这种发散式的做法在编程中似乎没有必要。选择自己喜欢的一种方式。

这里SQL Server有一个样本数据库Northwind.

以Northwind里的Region表为例：

Region表有2个字段：

• RegionID, int 主键
• RegionDescription, nchar(50)

写以下几个存储过程：

RegionUpdate: 多个参数

CREATE PROCEDURE RegionUpdate(@RegionID INTEGER, @RegionDescription NCHAR(50))
AS
SET NOCOUNT OFF
UPDATE Region
SET RegionDescription = @RegionDescription
WHERE RegionID = @RegionID

GO

RegionDelete 一个参数

CREATE PROCEDURE RegionUpdate(@RegionID INTEGER)
AS
SET NOCOUNT OFF
DELETE FROM Region
WHERE RegionID = @RegionID
GO

RegionInsert 带返回参数

CREATE PROCEDURE RegionInsert(@RegionDescription NCHAR(50), @RegionID INTEGER OUTPUT)
AS
SET NOCOUNT OFF
SELECT @RegionID = MAX(RegionID) + 1 
FROM Region
INSERT INTO Region VALUES(@RegionID, @RegionDescription)
GO

 

工程添加下面的配置文件

App.confi

 

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="Northwind"
         providerName ="System.Data.SqlClient"
         connectionString ="server=(local); integrated security = SSPI;
         database = Northwind" />
  </connectionStrings>
</configuration>

关于连接字符串怎么写参考：

http://www.connectionstrings.com

下面是C# code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;


namespace SQLProcedureDemo
{
    class Program
    {
        private static string GetConnectionStringsConfig(string connectionName)
        {
            string connectionString = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString.ToString();
            Console.WriteLine(connectionString);
            return connectionString;
        }


        static void Main(string[] args)
        {
            string source = GetConnectionStringsConfig("Northwind");

            try
            {
                using (SqlConnection conn = new SqlConnection(source))
                {
                    conn.Open();

                    // Invoke RegionUpdate Procedure
                    SqlCommand cmd = new SqlCommand("RegionUpdate", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter updatepara1 = new SqlParameter("@RegionID", SqlDbType.Int);
                    updatepara1.Value = 3;
                    cmd.Parameters.Add(updatepara1);
                    SqlParameter updatepara2 = new SqlParameter("@RegionDescription", SqlDbType.NChar);
                    updatepara2.Value = "Northern";
                    cmd.Parameters.Add(updatepara2);

                    // You can also use the following statement
                    //cmd.Parameters.AddWithValue("@RegionID", 3);
                    //cmd.Parameters.AddWithValue("@RegionDescription", "Northern");

                    cmd.ExecuteNonQuery();

                    // Invoke RegionDelete Procedure                    
                    SqlCommand cmdDel = new SqlCommand("RegionDelete", conn);
                    cmdDel.CommandType = CommandType.StoredProcedure;
                    SqlParameter myParameter = new SqlParameter("@RegionID", SqlDbType.Int);
                    myParameter.Value =5;
                    cmdDel.Parameters.Add(myParameter);
                    cmdDel.ExecuteNonQuery();
                    

                    // Invoke RegionInsert Procedure
                    SqlCommand cmdInsert = new SqlCommand("RegionInsert", conn);
                    cmdInsert.CommandType = CommandType.StoredProcedure;
                    
                    SqlParameter para1 = new SqlParameter("@RegionDescription", SqlDbType.NChar);
                    para1.Value = "South West";
                    cmdInsert.Parameters.Add(para1);

                    SqlParameter para2 = new SqlParameter("@RegionID", SqlDbType.Int);
                    para2.Direction = ParameterDirection.Output;
                    cmdInsert.Parameters.Add(para2);

                    cmdInsert.ExecuteNonQuery();

                    int newRegionID = (int)cmdInsert.Parameters["@RegionID"].Value;
                    Console.WriteLine(newRegionID);                    
                    conn.Close();
                }
            }
            catch (SqlException ex)
            {
                //

                Console.WriteLine(ex.Message);
 
            }

            Console.ReadKey();
        }
    }
}