C#操作MSSQL存储过程

来源：互联网发布：p2p网络投资产品排名编辑：程序博客网时间：2024/05/17 06:37

以下的所有操作都是针对MSSQL2000，Northwin库中Region表的操作！

//############开始#####################

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

namespace Return_SQL
{
    class Program
    {
        SqlConnection conn;
        SqlCommand cmd;
        SqlDataReader rs;

        /// <summary>
        /// 获取存储过程的返回值
        /// </summary>
        public void GetCount() {
            /*
            --Northwin库中Region表，建立GetCount存储过程
            CREATE PROCEDURE GetCount AS
            DECLARE @count int
            SELECT @count=COUNT(*) FROM Region
            RETURN @count--返回有多少条记录
            GO
            */
            try
            {
                conn = new SqlConnection("server=.;database=Northwind;Integrated Security=SSPI");
                conn.Open();

                cmd = new SqlCommand("GetCount", conn);
                cmd.CommandType = CommandType.StoredProcedure;//指定commandText的类型
                SqlParameter count = cmd.Parameters.Add("count", SqlDbType.Int);
                count.Direction = ParameterDirection.ReturnValue;
                cmd.ExecuteNonQuery();
                conn.Close();
                Console.WriteLine("返回值为："+count.Value.ToString());
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                conn.Close();
            }
        }
        public void GetRs() {
            /*
            --Northwin库中Region表，建立ShowRS存储过程
            CREATE PROCEDURE ShowRS AS
            SELECT * FROM Region
            GO
            */
            try
            {
                conn = new SqlConnection("server=.;database=Northwind;Integrated Security=SSPI");
                conn.Open();

                cmd = new SqlCommand("ShowRS", conn);
                cmd.CommandType = CommandType.StoredProcedure;//指定commandText的类型
                //SqlParameter count = cmd.Parameters.Add("count", SqlDbType.Int);
                //count.Direction = ParameterDirection.ReturnValue;
                rs = cmd.ExecuteReader();
                while(rs.Read()){
                    Console.WriteLine("ID：{0} 值：{1}",rs[0].ToString(),rs[1].ToString());
                }

                conn.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 插入记录
        /// </summary>
        public void InsertRS() {
            /*
            --Northwin库中Region表，建立InsertRS存储过程
             CREATE PROCEDURE InsertRS @ID int,@value varchar(50) AS
            INSERT INTO Region VALUES(@ID,@value)
            GO
             */
            SqlTransaction tran=null;
            try
            {
                conn = new SqlConnection("server=.;database=Northwind;Integrated Security=SSPI");
                conn.Open();

tran= conn.BeginTransaction();//创建一个事务

                cmd = new SqlCommand("InsertRS", conn);
                cmd.Transaction = tran;//把接下来的操作加入到事务tran
                cmd.CommandType = CommandType.StoredProcedure;//指定commandText的类型

                SqlParameter key = cmd.Parameters.Add("@ID", SqlDbType.Int);
                key.Direction = ParameterDirection.Input;
                key.Value = 5;

                SqlParameter value = cmd.Parameters.Add("@value", SqlDbType.VarChar);
                value.Direction = ParameterDirection.Input;
                value.Value = "中国";

                cmd.ExecuteNonQuery();
                tran.Commit();
                Console.WriteLine("插入成功");

            }
            catch (Exception e)
            {
                tran.Rollback();
                Console.WriteLine(e.Message);
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 获取一个值，用OUTPUT输入
        /// </summary>
        public void GetOneOfRs() {
            /*
            CREATE PROCEDURE GetRs @ID int ,@value varchar(50) OUTPUT AS
            SELECT @value=RegionDescription FROM Region WHERE RegionID=@ID
            GO
             */
            SqlTransaction tran = null;
            try
            {
                conn = new SqlConnection("server=.;database=Northwind;Integrated Security=SSPI");
                conn.Open();

tran = conn.BeginTransaction();//创建一个事务

                cmd = new SqlCommand("GetRs", conn);
                cmd.Transaction = tran;//把接下来的操作加入到事务tran
                cmd.CommandType = CommandType.StoredProcedure;//指定commandText的类型

                SqlParameter key = cmd.Parameters.Add("@ID", SqlDbType.Int);
                key.Direction = ParameterDirection.Input;
                key.Value = 5;

SqlParameter value = cmd.Parameters.Add("@value", SqlDbType.VarChar, 50);
value.Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();
                tran.Commit();
                Console.WriteLine("值为："+value.Value.ToString());

            }
            catch (Exception e)
            {
                tran.Rollback();
                Console.WriteLine(e.Message);
            }
            finally
            {
                conn.Close();
            }
        }
        static void Main(string[] args)
        {
            Program p = new Program();
            p.GetCount();
            p.GetRs();
            //p.InsertRS();
            p.GetOneOfRs();
        }
    }
}
//#####结束##########