WebService调用SQL数据或存储过程，小小的记录一下

 一、调用SQL语句

 public class SQLHelper
    {
        private SqlConnection connect = null;
        private SqlCommand cmd = null;
        private SqlDataReader sdr = null;

        public SQLHelper() { connect = new SqlConnection(); connect.ConnectionString = "Data Source =127.0.0.1; Initial Catalog=test;User ID=sa;password=123"; }

        private SqlConnection Getconn() { if (connect.State == ConnectionState.Closed) { connect.Open(); } return connect; }


        #region 不带参数的查询
        /// <summary>        
        /// /// 该方法执行传入的SQL查询语句
        /// /// </summary>        
        /// /// <param name="cmdText">SQL查询语句 或存储过程</param>       
        /// /// <returns></returns>        
        public DataTable ExecuteQuery(string cmdText, CommandType ct = CommandType.Text)
        {

            DataTable dt = new DataTable();
            cmd = new SqlCommand(cmdText, Getconn());
            cmd.CommandType = ct;
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            { dt.Load(sdr); }
            return dt;
        }
        #endregion

        #region
        /// <summary>
        /// 
        /// /// 该方法执行传入的SQL查询语句    
        /// /// </summary>      
        /// /// <param name="cmdText">sql语句或存储过程</param>     
        /// /// <param name="paras">参数集合</param>      
        /// /// <returns></returns>        
        public  DataTable ExecuteQuery(string cmdText, SqlParameter[] paras, CommandType ct = CommandType.Text)
        {
            DataTable dt = new DataTable();
            cmd = new SqlCommand(cmdText, Getconn());
            cmd.CommandType = ct;
            cmd.Parameters.AddRange(paras);
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr); if (connect.State == ConnectionState.Open) { connect.Close(); }
            }
            return dt;
        }      
        #endregion

    }

二、调用存储过程

//--调用存储过程的方法
 SqlConnection con = new SqlConnection(strCon);
 SqlDataAdapter sda = new SqlDataAdapter("GetTbpatient_zy_data", con); //调用存储过程，和前一个例子不同，这里用SqlDataAdapter，省去了打开数据库连接！ 
 SqlParameter para0 = new SqlParameter("@ip_no", ip_no); //给ip_no参数赋值，注意参数格式
 SqlParameter para1 = new SqlParameter("@name", name); //给name参数赋值，注意参数格式
 SqlParameter para2 = new SqlParameter("@reg_date", reg_date); //给reg_date参数赋值，注意参数格式
 SqlParameter para3 = new SqlParameter("@leave_date", leave_date); //给leave_date参数赋值，注意参数格式
 SqlParameter para4 = new SqlParameter("@pat_flag", pat_flag); //给pat_flag参数赋值，注意参数格式
 SqlParameter para5 = new SqlParameter("@dept_code", dept_code); //给dept_code参数赋值，注意参数格式
 sda.SelectCommand.Parameters.Add(para0); //添加ip_no参数值
 sda.SelectCommand.Parameters.Add(para1); //添加name参数值
 sda.SelectCommand.Parameters.Add(para2); //添加starttime参数值
 sda.SelectCommand.Parameters.Add(para3); //添加endtime参数值
 sda.SelectCommand.Parameters.Add(para4); //添加pat_flag参数值
 sda.SelectCommand.Parameters.Add(para5); //添加dept_code参数值
 sda.SelectCommand.CommandType = CommandType.StoredProcedure; //指定执行类型为存储过程

DataTable dt = null;
 DataSet ds = new DataSet();
 sda.Fill(ds);
 dt = ds.Tables[0];
 string json = DataTableConvertJson.DataTableToJson("Tbpatientdata", dt); //table转json格式
 return json;

 

//格式转换方法
 public class DataTableConvertJson
 {
 /// <summary>
 /// table格式转json
 /// </summary>
 /// <param name="jsonName"></param>
 /// <param name="dt"></param>
 /// <returns></returns>
 public static string DataTableToJson(string jsonName, DataTable dt)
 {
 StringBuilder Json = new StringBuilder();
 Json.Append("{\"" + jsonName + "\":[");
 if (dt.Rows.Count > 0)
 {
 for (int i = 0; i < dt.Rows.Count; i++)
 {
 Json.Append("{");
 for (int j = 0; j < dt.Columns.Count; j++)
 {
 Json.Append("\"" + dt.Columns[j].ColumnName.ToString() + "\":\"" + dt.Rows[i][j].ToString() + "\"");
 if (j < dt.Columns.Count - 1)
 {
 Json.Append(",");
 }
 }
 Json.Append("}");
 if (i < dt.Rows.Count - 1)
 {
 Json.Append(",");
 }
 }
 }
 Json.Append("]}");
 return Json.ToString();
 }
 }