一、调用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();
}
}