c#调用sqlserver的存储过程

c#调用sqlserver的存储过程 

 public class DBUntil
    {
        public static string ConnectStrSL = "Data Source=xxx;Initial Catalog=xxx;uid=xxx;pwd=xxx";

        #region 查询数据
        /// <summary>
        /// 查询主单记录
        /// </summary>
        /// <param name="dtStrat">开始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <returns></returns>
        public static DataTable QureyMasterOrderDatas(DateTime dtStrat,DateTime dtEnd)
        {
            List<SqlParameter> lstParam = new List<SqlParameter>();

            lstParam.Add(new SqlParameter("@Bdate", SqlDbType.DateTime));
            lstParam.Add(new SqlParameter("@Edate", SqlDbType.DateTime));
            lstParam.Add(new SqlParameter("@mark", SqlDbType.SmallInt));
            lstParam.Add(new SqlParameter("@billid", SqlDbType.VarChar));

            List<object> lstValues = new List<object>();
            lstValues.Add(dtStrat.ToString("D"));
            lstValues.Add(dtEnd.ToString("D"));
            lstValues.Add(4);//类型 4 主单数据
            lstValues.Add("0");


            var tb = ExeProdurce("aa_1_asale", lstParam, lstValues);

            return tb;
        }

        /// <summary>
        /// 查询项目列表记录
        /// </summary>
        /// <param name="billid">主单ID</param>
        /// <param name="mark">类型 1 2 3 </param>
        /// <returns></returns>
        public static DataTable QureyDetailOrderDatas(string billid,int mark)
        {
            List<SqlParameter> lstParam = new List<SqlParameter>();

            lstParam.Add(new SqlParameter("@Bdate", SqlDbType.DateTime));
            lstParam.Add(new SqlParameter("@Edate", SqlDbType.DateTime));
            lstParam.Add(new SqlParameter("@mark", SqlDbType.SmallInt));
            lstParam.Add(new SqlParameter("@billid", SqlDbType.VarChar));

            List<object> lstValues = new List<object>();
            lstValues.Add(DateTime.Now);
            lstValues.Add(DateTime.Now);
            lstValues.Add(mark);
            lstValues.Add(billid);


            var tb = ExeProdurce("aa_1_asale", lstParam, lstValues);

            return tb;
        }

        #endregion 查询数据



        #region 调用存储过程

        /// <summary>
        /// 调用存储过程
        /// </summary>
        /// <param name="pname">存储过程名称</param>
        /// <param name="lstParam">参数列表</param>
        /// <param name="lstValues">参数值列表</param>
        /// <returns></returns>
        public static DataTable ExeProdurce(string pname,List<SqlParameter> lstParam, List<object> lstValues)
        {
            string sql = pname;   //调用的存储过程名称     
            SqlConnection conn = new SqlConnection();
            conn = new SqlConnection();
            conn.ConnectionString = ConnectStrSL;
            if (conn.State == ConnectionState.Closed)
            { conn.Open(); }
            DataTable dt = new DataTable();//新建一个临时表存放结果                       
            SqlCommand com1 = new SqlCommand(sql, conn);
            com1.CommandType = CommandType.StoredProcedure;  //因为要使用的是存储过程，所以设置执行类型为存储过程    @billcode      

            int idx = 0;
            foreach(SqlParameter pm in lstParam)
            {
                com1.Parameters.Add(pm);
                com1.Parameters[idx].Value = lstValues[idx];
                idx++;
            }

            

            com1.ExecuteNonQuery();
            SqlDataAdapter sda1 = new SqlDataAdapter(com1);
            sda1.Fill(dt);
            conn.Close();

            return dt;

        }
        #endregion 调用存储过程
    }