• ADO.NET中存储过程的调用


    调用带输入输出参数的存储过程
    View Code
            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;
            }

    调用带输入参数的存储过程
    View Code
    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();
    }
    调用带多个输入参数的存储过程
    View Code
    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));


     


    
    
  • 相关阅读:
    python学习(9)字典的基本应用
    python学习(8)实例:写一个简单商城购物车的代码
    python学习(7)关于列表操作相关命令以及浅复制深复制
    python学习(6)选择排序算法简单代码
    python学习(5)写一个二分算法的程序
    python学习(4)循环语句
    添加页面+正则+三级联动
    SSM整合多对多表关系
    dubbo整合ssm
    爬虫爬取文章
  • 原文地址:https://www.cnblogs.com/cwyblog/p/2831170.html
Copyright © 2020-2023  润新知