ADO.NET中存储过程的调用

调用带输入输出参数的存储过程

        public string GetDataSetG(string patientid)
        {
            string connStr = 
System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnString"].ToString()
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "usp_GetCount";
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter sp = new SqlParameter("PatientId", patientid);
            sp.DbType = DbType.Int32;
            sp.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(sp);
            sp = new SqlParameter("count", SqlDbType.NVarChar,100); //这里是string类型
            //sp.ParameterName = "count"; //这里是int类型
            //sp.DbType = DbType.int;
            sp.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(sp);
            cmd.ExecuteNonQuery();
            string s = sp.Value.ToString();
            conn.Close();
            return s;
        }

调用带输入参数的存储过程

Private int GetSqlComand(int customerId){
string connStr = 
System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnString"].ToString()
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
SqlCommand cmd = new SqlCommand(＂Proc_DeleteCustomerById＂，conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter para = new SqlParameter("@CustomerId",customerId);cmd.Parameters.Add(para);
//执行命令，并返回受影响的行数return cmd.ExecuteNonQuery();
}

调用带多个输入参数的存储过程

Private int GetSqlComand(int customerId){
string connStr = 
System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnString"].ToString()
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
SqlCommand cmd = new SqlCommand(＂Proc_DeleteCustomerById＂，conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] parms = new SqlParameter[] {         new SqlParameter("@CustomerId",customerId),        new SqlParameter("@CustomerName",customerName)    };    foreach (SqlParameter pa in parms)        cmd.Parameters.Add(pa);//执行命令，并返回受影响的行数return cmd.ExecuteNonQuery();
}

调用带sql结果集的存储过程

存储过程代码
CREATE PROCEDURE [dbo].[sp_PatientVisit] 
(@pid int,--病人id
 @vid int,--随访id
 @biaoji int
)
AS
BEGIN
    declare @sql varchar(2000)
if(@biaoji=0)
  begin
    if(@vid=0)
    begin
    SET @sql='select * from hsopital'
    end
    else
    begin
    set @sql='select * from Section'
    end
end
else if(@biaoji=1)
begin
set @sql='select * from doctor'
end
exec (@sql)
END

调用方法

private DataSet GetDataSet(string sql)
        {
            string connStr = 
System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnString"].ToString();
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            conn.Close();
            cmd.Dispose();
            return ds;
        }
传进去的结果
 DataSet dsgg = GetDataSet(string.Format("exec usp_GetCount {0}", id));