ADO_调用存储过程_总结

参数---输入参数

1.

//传入参数，简单的添加操作
        SqlConnection con = new SqlConnection();
        con.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
        con.Open();
        //创建Command

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "usp_insertuser";//此处不是SQL语句，是存储过程名
        cmd.CommandType = CommandType.StoredProcedure;//类型必须指定为存储过程

        //开始参数处理
        cmd.Parameters.AddWithValue("@username", this.txtusername.Text);//方式1@username为存储过程的输入参数
        cmd.Parameters.Add("@userpwd", SqlDbType.NVarChar).Value = this.txtpwd.Text;//方式2@userpwd为存储过程的输入参数

        int count = cmd.ExecuteNonQuery();
        con.Close();
        this.lblinfo.Text = "操作成功！";

 

2.

//传入参数返回结果集
        SqlConnection con = new SqlConnection();
        con.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
        con.Open();
        //创建Command

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "usp_selectuser";//此处不是SQL语句，是存储过程名
        cmd.CommandType = CommandType.StoredProcedure;//类型必须指定为存储过程

        //开始参数处理
        cmd.Parameters.AddWithValue("@username", this.txtusername.Text);//方式1@username为存储过程的输入参数

        this.GridView1.DataSource = cmd.ExecuteReader();
        this.GridView1.DataBind();
        con.Close();

 

参数---输出参数

 

        SqlConnection con = new SqlConnection();
        con.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["NorthwindconnectionString"].ConnectionString;
        con.Open();
        //创建Command

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "usp_sumcustom";//此处不是SQL语句，是存储过程名
        cmd.CommandType = CommandType.StoredProcedure;//类型必须指定为存储过程

        //输入参数
        cmd.Parameters.AddWithValue("@customid", this.TextBox1.Text);

        //输出参数
        //单独定义参数
        SqlParameter spcount = new SqlParameter();
        spcount.ParameterName = "@ordercount";
        //因为输出所以不定义Value
        spcount.SqlDbType = SqlDbType.Float;
        //必须要定义方向
        spcount.Direction = ParameterDirection.Output;

        SqlParameter spsum = new SqlParameter();
        spsum.ParameterName = "@ordersum";
        //因为输出所以不定义Value
        spsum.SqlDbType = SqlDbType.Money;
        //必须要定义方向
        spsum.Direction = ParameterDirection.Output;

        cmd.Parameters.AddRange(new SqlParameter[] { spcount, spsum });

        try
        {
            cmd.ExecuteNonQuery();
            this.Label1.Text = string.Format("客户:{0},共有:{1}个订单，总金额为{2:#.00}", cmd.Parameters["@customid"].Value,
                cmd.Parameters["@ordercount"].Value, cmd.Parameters["@ordersum"].Value);//总金额为{2:#.00}格式化小数
        }
        catch (Exception ex)
        {
            this.Label1.Text = ex.Message;
        }

 参数---返回值

不需名字，只需要调协方向为ReturnValue

  SqlParameter sprv = new SqlParameter();
        sprv.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(sprv);

 

调用

 sprv.Value.ToString()