sql 存储过程调用

 

if exists(select * from sys.objects where name='checkUserName' and type='p')
drop proc checkUserName
go

create proc checkUserName
(
  @checkResult int output,
  @userName varchar(20),
  @userAge varchar(10)
  )

  as 
  if exists(select * from Student where Name=@userName and Age=@userAge)
  set @checkResult=1 --通过验证
  else if exists(select * from Student where Name=@userName)
  set @checkResult=2
  else
  set @checkResult=0

  return isnull(@checkResult,2)
  go

  declare @checkResult int --声明一个返回值的变量
  exec checkUserName @checkResult output, 'NEWID','3' --执行
  select case @checkResult --select
   
   when 1 then 'ok'
   when 0 then 'not exist'
   when 2 then 'pwd error'
   end as '验证结果'
// 数据库中创建的存储过程

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        public static string connectionString = "data source=.\\sqlserver;initial catalog=;User ID=;Password=;";
        // 1. 初始化数据库连接
        SqlConnection conn = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand();
        private void button1_Click(object sender, EventArgs e)
        {
            cmd.Connection = conn;
            // 2. 打开数据库
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();

            }

            #region 调用存储过程方法一
            //Class1 sp = new Class1("checkUserName", connectionString);//类的对象
            //Object[] paraValues = new object[3];//注意,这里是存储过程中全部的参数,一共有三个,还要注意顺序啊,返回值是第一个,那么赋值时第一个参数就为空

            //paraValues[1] = this.textBox1.Text.Trim();//从第二个参数开始赋值
            //paraValues[2] = this.textBox2.Text.Trim();

            //sp.ExecProcOutput(out object[] output, 1, paraValues);//调用我们前面定义的方法,这里我就随便写了一个,能完成功能,大家改进
            //                                                      //object o = sp.ExecuteScalar(paraValues);//这个是查询的那个存储过程调用,很简单,不多说了
            //switch (Convert.ToInt32(output[0]))
            //{
            //    case 0:
            //        this.label1.Text = "用户不存在";
            //        break;
            //    case 1:
            //        this.label2.Text = "登录成功";
            //        break;
            //    case 2:
            //        this.label2.Text = "密码不正确";
            //        break;
            //    default:
            //        break;
            //}
            #endregion
            try
            {
                #region 方法二
                cmd.CommandText = "checkUserName";
                //cmd.CommandType = CommandType.StoredProcedure;
                //cmd.Parameters.Add("@userName", SqlDbType.VarChar);
                //cmd.Parameters["@userName"].Value = textBox1.Text.Trim();
                //cmd.Parameters.Add("@userAge", SqlDbType.VarChar);
                //cmd.Parameters["@userAge"].Value = textBox2.Text.Trim();
                //cmd.Parameters.Add("@checkResult", SqlDbType.Int);
                //cmd.Parameters["@checkResult"].Direction = ParameterDirection.Output;

                cmd.CommandType = CommandType.StoredProcedure;
                //cmd.Parameters.Add("@userName", SqlDbType.VarChar);
                // cmd.Parameters["@userName"].Value = textBox1.Text.Trim();
                //cmd.Parameters.Add("@userAge", SqlDbType.VarChar);
                //cmd.Parameters["@userAge"].Value = textBox2.Text.Trim();
                //cmd.Parameters.Add("@checkResult", SqlDbType.Int);

                cmd.Parameters.AddWithValue("@userName", this.textBox1.Text.Trim());
                cmd.Parameters.AddWithValue("@userAge", this.textBox2.Text.Trim());
                cmd.Parameters.AddWithValue("@checkResult", SqlDbType.Int);

                cmd.Parameters["@checkResult"].Direction = ParameterDirection.Output;
                int t = cmd.ExecuteNonQuery();
                switch (Convert.ToInt32(cmd.Parameters[2].Value))
                {
                    case 0:
                        this.label1.Text = "用户不存在";
                        break;
                    case 1:
                        this.label2.Text = "登录成功";
                        break;
                    case 2:
                        this.label2.Text = "密码不正确";
                        break;
                    default:
                        break;
                }
                cmd.Parameters.Clear();
                #endregion

            }
            catch (Exception ex)
            {
                string s = ex.ToString();
                throw;
            }
        } 
    }
}

 

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WindowsFormsApp1
{
    public class Class1
    {
        //连接字符串
        private string connectionString;

        //存储过程名称
        private string storeProcedureName;

        public Class1(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public Class1(string storeProcedureName, string conn)
        {
            this.connectionString = conn;
            this.storeProcedureName = storeProcedureName;
        }

        public string GetProcedureName
        {
            get { return storeProcedureName; }
            set { storeProcedureName = value; }
        }
         <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
        /// </summary>
        /// <param name="paraValues">传递给存储过程的参数值列表。</param>
        /// <returns>结果集中第一行的第一列或空引用（如果结果集为空）。</returns>
        public object ExecuteScalar(params object[] paraValues)
        {

            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {

                SqlCommand command = this.CreateSqlCommand(connection);

                try
                {
                    this.DeriveParameters(command);
                    this.AssignParameterValues(command, paraValues);
                    connection.Open();
                    object result = command.ExecuteScalar();
                    //string r = command.Parameters[1].Value.ToString();
                    return result;
                }
                catch
                {
                    throw;
                }
            }
        }


        public void ExecProcOutput(out object[] output, int outParaNum, params object[] paraValues)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = CreateSqlCommand(conn);
                output = new object[outParaNum];
                try
                {
                    DeriveParameters(cmd);
                    for(int i=0;i<cmd.Parameters.Count;i++)
                    {
                        var t = cmd.Parameters[i];
                    }
                    AssignParameterValues(cmd, paraValues);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    for(int i=0;i<outParaNum;i++)
                    {
                        output[i] = cmd.Parameters[i].Value;
                    }
                }
                catch(Exception ex)
                {
                    string s = ex.ToString();
                    throw;
                }
            }
        }
        //
         <summary>
        /// 执行存储过程，返回 System.Data.DataTable。
        /// </summary>
        /// <param name="paraValues">传递给存储过程的参数值列表。</param>
        /// <returns>包含查询结果的 System.Data.DataTable。</returns>
        public DataTable ExecuteDataTable(params object[] paraValues)
        {

            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {

                SqlCommand command = this.CreateSqlCommand(connection);

                try
                {
                    this.DeriveParameters(command);
                    this.AssignParameterValues(command, paraValues);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    DataTable dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    return dataTable;
                }
                catch
                {
                    throw;
                }
            }
        }

        public void ExecuteFillDataTable(DataTable dataTable,params object[] paraValues)
        {
            using(SqlConnection conn=new SqlConnection(connectionString))
            {
                SqlCommand cmd = CreateSqlCommand(conn);

                try
                {
                    DeriveParameters(cmd);
                    AssignParameterValues(cmd, paraValues);
                    conn.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(dataTable);

                }
                catch (Exception)
                {

                    throw;
                }

            }
        }

        /// <summary>
        ///  执行操作类(insert/delete/update)存储过程
        /// </summary>
        /// <param name="paraValues">传递给存储过程的参数列表</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(params object[] paraValues)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = CreateSqlCommand(conn); //创建存储过程

                try
                {
                    DeriveParameters(cmd);
                    AssignParameterValues(cmd, paraValues);
                    conn.Open();

                    int affectedRowsCount = cmd.ExecuteNonQuery();
                    return affectedRowsCount;

                }
                catch
                {
                    throw;
                }
            }
        }

        public SqlDataReader ExecuteDataReader(params object[] paraValues)
        {
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {

                SqlCommand command = this.CreateSqlCommand(connection);

                try
                {
                    this.DeriveParameters(command);
                    this.AssignParameterValues(command, paraValues);
                    connection.Open();
                    return command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                }
                catch
                {
                    throw;
                }

            }
        }

        /// <summary>
        /// 为指定的存储过程 赋值
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="paraValues"></param>
        private void AssignParameterValues(SqlCommand cmd, object[] paraValues)
        {
            if (paraValues != null)
            {
                if (cmd.Parameters.Count - 1 != paraValues.Length)
                {
                    throw new ArgumentException("the number of parameters does not match number of values for stored procedure.");
                }
            }
            for (int i = 0; i < paraValues.Length; i++)
            {
                cmd.Parameters[i + 1].Value = (paraValues[i] ?? DBNull.Value);

            }


        }

        /// <summary>
        /// 从指定存储过程检索信息
        /// </summary>
        /// <param name="cmd"></param>
        private void DeriveParameters(SqlCommand cmd)
        {
            try
            {
                cmd.Connection.Open();
                SqlCommandBuilder.DeriveParameters(cmd);
                cmd.Connection.Close();
            }
            catch
            {
                if (cmd.Connection != null)
                {
                    cmd.Connection.Close();
                }
                throw;
            }
        }

        private SqlCommand CreateSqlCommand(SqlConnection conn)
        {
            SqlCommand command = new SqlCommand(storeProcedureName, conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            return command;
        }
    }
}