public static class SqlHelper
{
//准备一个连接字符串
private static string connStr = ConfigurationManager.ConnectionStrings["myconnStr"].ConnectionString;
//private static string connStr = ConfigurationManager.ConnectionStrings["MyTestConnectionString1"].ConnectionString;
/// (1)执行一个存储过程得到一个DataTable对象
/// </summary>
/// <param name="Procname"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static DataTable ExecuteProc(string Procname, params SqlParameter[] paras)
{
SqlConnection con = new SqlConnection(SqlHelper.connStr);
SqlDataAdapter da = new SqlDataAdapter(Procname, con);
da.SelectCommand.Parameters.AddRange(paras);
DataTable table = new DataTable();
return table;//返回得到的DataTable对象
/// <summary>
/// (2)执行一个没有参数的存储过程得到一个DataTable对象
/// </summary>
/// <param name="Procname"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static DataTable ExecuteProcNoQuery(string Procname)
{
SqlConnection con = new SqlConnection(SqlHelper.connStr);
SqlDataAdapter da = new SqlDataAdapter(Procname, con);
//da.SelectCommand.Parameters.AddRange(paras);
DataTable table = new DataTable();
return table;//返回得到的DataTable对象
/// (3)执行一个没有参数的存储过程得到一个int返回值
/// (之所以用返回值是int类型是为了在执行增删改的时候做个判断)
/// </summary>
/// <param name="Procname"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static int ExecuteProcNoQueryInt(string Procname)
{
SqlConnection con = new SqlConnection(SqlHelper.connStr);
SqlDataAdapter da = new SqlDataAdapter(Procname, con);
//da.SelectCommand.Parameters.AddRange(paras);
DataTable table = new DataTable();
return table.Rows.Count;//返回得到的DataTable中数据行数
/// <summary>
/// (4)执行存储过程的增删改操作,返回值为int类型参数
/// </summary>
/// <param name="Procname"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static int ExecuteNonQueryProcInt(string Procname, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;//设置为存储过程
cmd.CommandText = Procname;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteNonQuery();
}
}
}
/// (5)如果从数据库中取到的数据是NULL的时候,则转化为C#中的null类型
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
public static object FromDbValue(object value)
{
if (value == DBNull.Value)
{
return null;
}
else
{
return value;
}
}
/// (6)如果数据为Null的话,则把数据转化为数据库中的DBNULL类型
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
public static object ToDbValue(object value)
{
if (value == null)
{
return DBNull.Value;
}
else
{
return value;
}
}
public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// (8)执行带参数的查询操作
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteScalar();
}
}
}
/// (9)执行不带参数的查询操作
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static object ExecuteScalar(string sql)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
//cmd.Parameters.AddRange(parameters);
return cmd.ExecuteScalar();
}
}
}
public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
DataSet dataset = new DataSet();
SqlDataAdapter apdater = new SqlDataAdapter(cmd);
apdater.Fill(dataset);
return dataset.Tables[0];
}
}
}
/// (11)执行存储过程,返回一个数据表
/// </summary>
/// <param name="procName"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static DataTable ExcuteProctest(string procName, params SqlParameter[] paras)
{
SqlConnection conn = new SqlConnection(SqlHelper.connStr);
SqlDataAdapter da = new SqlDataAdapter(procName, conn);
da.SelectCommand.Parameters.AddRange(paras);
da.SelectCommand.CommandType = CommandType.StoredProcedure;//设置 使用存储过程
//查询数据,并将返回的数据装入数据表dt
da.Fill(dt);
return dt;
}
/// (12)执行存储过程,返回一个数据表
/// </summary>
/// <param name="procName"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static DataTable ExcuteProctestTable(string procName, params SqlParameter[] paras)
{
SqlConnection conn = new SqlConnection(SqlHelper.connStr);
SqlDataAdapter da = new SqlDataAdapter(procName, conn);
da.SelectCommand.Parameters.AddRange(paras);
da.SelectCommand.CommandType = CommandType.StoredProcedure;//设置 使用存储过程
//查询数据,并将返回的数据装入数据表dt
da.Fill(dt);
return dt;
}
/// (13)执行存储过程,返回一个数据集
/// </summary>
/// <param name="procName"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static DataSet ExcuteProctestDataSet(string procName)
{
SqlConnection conn = new SqlConnection(SqlHelper.connStr);
SqlDataAdapter da = new SqlDataAdapter(procName, conn);
//da.SelectCommand.Parameters.AddRange(paras);
da.SelectCommand.CommandType = CommandType.StoredProcedure;//设置 使用存储过程
//da.Fill(dt);
//return dt;
DataSet ds = new DataSet();
//查询数据,并将返回的数据装入数据表dt
da.Fill(ds);
return ds;
public static SqlConnection GetConnection()
{
//temp="data source = (local);initial catalog = test; user id = sa;password = 111";
//StreamReader sr = File.OpenText(@"1.txt");
//string temp = sr.ReadToEnd();
myConn.ConnectionString = connStr;
return myConn;
}
//(15)查询数据库
public static DataSet GetDataSetStr(string sqlStr)
{
SqlConnection myConn = GetConnection();
myConn.Open();
DataSet ds = new DataSet();
SqlDataAdapter adapt = new SqlDataAdapter(sqlStr, myConn);
myConn.Close();
return ds;
}
public static bool notQuery(string sql)
{
SqlConnection conn3 = new SqlConnection(connStr);
conn3.Open();
SqlCommand cmd = new SqlCommand(sql, conn3);
try
{
cmd.ExecuteNonQuery();
return true;
}
catch (Exception)
{
return false;
}
finally
{
conn3.Close();
}