存储过程

sqlcommand cmd=new sqlcommand("存储过程名称",conn)
cmd.commandType=commandType.storedprocedure//定义cmd的命令为存储过程
sqlparameter xp1=new sqlparameter("@id",5);//5为定义的@id值
cmd.parameters.add(xp1);//向参数库中添加参数xp1
xp2.Direction=ParameterDirection.output//定义参数的输出类型
xp2.sqlvalue//sql的数据类型
xp2.value//C#的数据类型
cmd.Parameters.AddRange(数组)//批量添加数据
object obj=default(Nullable);定义空
调用存储过程
declare @outputcount int
exec(ute) xp_hello(储存过程名称) 4,18,@outputcount output
print @outputcount//输出打印参数
//调用存储过程
1.SqlCommand cmd = new SqlCommand("xp_hello", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] xp ={
                            new SqlParameter ("@id",4),
                            new SqlParameter ("@min_lvl",18),
                            new SqlParameter ("@count",SqlDbType.Int)
                             };
            xp[2].Direction = ParameterDirection.Output;
            cmd.Parameters.AddRange(xp);
            conn.Open();
            cmd.ExecuteScalar();
           
            Response.Write(xp[2].Value );
2.string sql = "select * from jobs";
            SqlCommand cmd = new SqlCommand("xp_hello", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter xp1 = new SqlParameter("@id", 4);
            SqlParameter xp2 = new SqlParameter("@min_lvl", 18);
            SqlParameter xp3 = new SqlParameter("@count", SqlDbType.Int);
            xp3.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(xp1);
            cmd.Parameters.Add(xp2);
            cmd.Parameters.Add(xp3);
            conn.Open();
            cmd.ExecuteScalar();
            Response.Write(xp3.Value + "<br>");//C#中的数据类型
            Response.Write(xp3.SqlValue);//sql中的数据类型

// ExecuteDataReader绑定gridview控件
string connstr = "Data Source=.;initial catalog='pubs';user id=sa;password=wy";
        using (SqlConnection conn = new SqlConnection(connstr))
        {
            string sql = "select * from jobs";
            SqlCommand cmd = new SqlCommand(sql,conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Columns.Add("编号",typeof (Int32));
            dt.Columns.Add("职工描述", typeof(string ));
            dt.Columns.Add("最小值", typeof(Int32));
            dt.Columns.Add("最大值", typeof(Int32));
            while (dr.Read())
            {
                DataRow datarow = dt.NewRow();
                datarow[0] = dr[0];
                datarow["职工描述"] = dr["job_desc"];
                datarow[2] = dr[2];
                datarow[3] = dr[3];
                dt.Rows.Add(datarow);
               
            }
            this.gvjobs.DataSource = dt.DefaultView;
            this.gvjobs.DataBind();

        }

//用DBHelp类实现调用存储过程
SqlParameter[] xp1 ={
                               new SqlParameter ("@id",4),
                               new SqlParameter ("@min_lvl",18),
                               new SqlParameter ("@count",SqlDbType.Int )
                           };
        xp1[2].Direction = ParameterDirection.Output;
      new DBHelp ().ExecuteNonQuery("xp_hello",CommandType.StoredProcedure,xp1);

        Response.Write(xp1[2].Value );
// DBHelp类
public DBHelp()
 {
  //
  //TODO: 在此处添加构造函数逻辑
  //
 }
    public string ConnStr = "Data Source=.;Initial Catalog='pubs';User ID=sa;Password=wy";
    public SqlCommand CreateSqlcommand(string cmdText,CommandType cmdType,SqlParameter []xp)
    {
        SqlConnection conn = new SqlConnection(ConnStr );
        SqlCommand cmd = new SqlCommand(cmdText,conn );
        cmd.CommandType = cmdType;
        if (xp != null)
        {
            cmd.Parameters.AddRange(xp);
        }
        return cmd;
    }
    public int ExecuteNonQuery(string cmdText, CommandType cmdType, SqlParameter[] xp)
    {
        using (SqlCommand cmd = this .CreateSqlcommand (cmdText ,cmdType,xp))
        {
            cmd.Connection.Open();
           return  cmd.ExecuteNonQuery();
 
        }
    }
    public object ExecuteScalar(string cmdText, CommandType cmdType, SqlParameter[] xp)
    {
        using (SqlCommand cmd = this.CreateSqlcommand(cmdText, cmdType, xp))
        {
            cmd.Connection.Open();
            return cmd.ExecuteScalar();

        }
    }

    public DataTable ExecuteDataTable(string cmdText, CommandType cmdType, string tableName,SqlParameter[] xp)
    {
        using (SqlCommand cmd = this.CreateSqlcommand(cmdText, cmdType, xp))
        {
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable(tableName);
            da.Fill(dt);
            cmd.Connection.Close();
            return dt;
        }
    }
    public void LoadDataTable(string cmdText, CommandType cmdType, DataTable table, SqlParameter[] xp)
    {
        using (SqlCommand cmd = this.CreateSqlcommand(cmdText, cmdType, xp))
        {
            SqlDataAdapter da = new SqlDataAdapter(cmd);
          
            da.Fill(table);
            cmd.Connection.Close();
           
        }
    }//在Page_Load（）中调用DBHelp类中的函数