• 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));


     


    
    
  • 相关阅读:
    简单的纯css菜单
    提高 web 应用性能之 JavaScript 性能调优(转)
    从拖延者到行动派的10个秘诀(转)
    程序员,你应该知道(转)
    幽默的经济学+组织领导学
    [SQL基础]统计信息解释
    演讲集合
    最隐晦的程序设计指引(转)
    windows 7 "unmountable boot volume" 解决方法
    kafka与springboot集成2
  • 原文地址:https://www.cnblogs.com/cwyblog/p/2831170.html
Copyright © 2020-2023  润新知