Net存储过程的应用

存储过程参数使用主要分

1. 输入参数
2. 输出参数
3 返回参数

Sql脚本

create table Student11
(
Idx int IDENTITY(1,1) NOT NULL primary key,
userName nvarchar(50) NULL,
uPwd nvarchar(50) NULL,
createdate datetime NULL,
)

create proc proc_Insert
@userName varchar(50),
@uPwd varchar(50),
@createdate datetime,

@currentIdx varchar(50) output
As
Begin
	insert into Student(userName,uPwd,createdate) values(@userName,@uPwd,@createdate)
	
	declare @strTemp varchar(50)
	select @strTemp=MAX(idx) from Student
	set @strTemp = cast(@strTemp as int) + 1
	set @currentIdx = @strTemp

	If @@ERROR<>0 
		return -1
	Else
		return 1
End

 

C#开发代码

  private void btnAdd_Click(object sender, EventArgs e)
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = "Server=.;UId=william5;Pwd=william5;Database=testDb";
                conn.Open();

                using (SqlCommand commd = new SqlCommand())
                {
                    commd.Connection = conn;
                    commd.CommandType = CommandType.StoredProcedure;
                    commd.CommandText = "proc_insert";

                    SqlParameter[] parms = { 
                                            new SqlParameter("@userName",SqlDbType.VarChar,50),
                                            new SqlParameter("@uPwd",SqlDbType.Int),
                                            new SqlParameter("@createdate",SqlDbType.DateTime)
                                           };
                    parms[0].Value = this.txtName.Text;
                    parms[1].Value = Convert.ToInt32(this.txtAge.Text);
                    parms[2].Value = Convert.ToDateTime(this.txtCreatedate.Text);
                    commd.Parameters.AddRange(parms);

                    SqlParameter parmReturn = new SqlParameter("@return", SqlDbType.Int);
                    parmReturn.Direction = ParameterDirection.ReturnValue;
                    commd.Parameters.Add(parmReturn);

                    SqlParameter parmOutput = new SqlParameter("@currentIdx",SqlDbType.VarChar,50);
                    parmOutput.Direction = ParameterDirection.Output;
                    commd.Parameters.Add(parmOutput);

                    commd.ExecuteNonQuery();

                    int iReturn = Convert.ToInt32(commd.Parameters["@return"].Value);
                    if (iReturn == 1)
                    {
                        MessageBox.Show("添加成功");
                        MessageBox.Show("当前的Idx=" + commd.Parameters["@currentIdx"].Value);
                    }
                    else
                    {
                        MessageBox.Show("添加失败");
                    }

                }
            }
        }

 

========================= 基本使用如上
 部分代码备忘录

SqlHelper添加方法

 public static SqlParameter MakeReturnParameter(string parameterName, SqlDbType dbType, int length)
        {
            SqlParameter parameter = new SqlParameter();
            parameter.ParameterName = parameterName;
            parameter.SqlDbType = dbType;
            parameter.Direction = ParameterDirection.ReturnValue;
            if (dbType != SqlDbType.DateTime)
                parameter.Size = length;

            return parameter;
        }

        public static SqlParameter MakeReturnParameter(string parameterName, SqlDbType dbType)
        {
            SqlParameter parameter = new SqlParameter();
            parameter.ParameterName = parameterName;
            parameter.SqlDbType = dbType;
            parameter.Direction = ParameterDirection.ReturnValue;

            return parameter;
        }

应用时所写

 private void myAdd()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "Server=.;UId=william5;Pwd=william5;Database=testDb";
            conn.Open();

            SqlParameter[] parms = {SqlHelper.MakeInParameter("@userName", SqlDbType.VarChar, 50, this.txtName.Text),
                        SqlHelper.MakeInParameter("@uPwd", SqlDbType.Int, this.txtAge.Text),
                        SqlHelper.MakeInParameter("@createdate", SqlDbType.DateTime, this.txtCreatedate.Text),
                        SqlHelper.MakeReturnParameter("@return", SqlDbType.Int),
                        SqlHelper.MakeOutParameter("@currentIdx", SqlDbType.VarChar, 50)};

            SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "proc_insert", parms);

            MessageBox.Show(parms[3].Value.ToString()); //获取存储过程返回值
            MessageBox.Show(parms[4].Value.ToString()); //获取存储过程输出参数
        }