/*
/' `\/ `.
. .' : `. `.
\\.' , `.` `.
`. ,___/|\. `. :
. \, .'./ ' '\ , '
.\ . \_.~ _; ; \/'.
`\ ..._`. : /.. ../
/' _._ \. ~ .' `\:
/'.'@ ` .---. `.
.' : ' @ `.\. \
/ ./`.._./ ~ . :\ `. __
.' / ( \....' `. .' /' `.
/'''\ .' `. / \ : ;' .' ..:
.' ; `\; : : : : .' : ; :
: `\. `\. ; : \.' " ' ;
`. `. \ / s . / `. .'
` . `. `\ `. ; /' ;___ ;
`. `. `. ` ; ;:__..'
`. `. `. :` ': _.' .' ; :
`. `. .\x./-`--...../' ; :
`. ..-:..-' ( :
`---'`. `; :
`. `,.. : :
`. `. `.___;
`. `.
`. `;
`-.,'
女神保佑 永无BUG
*/ public static class SQLHelper
{
private static SqlConnection Con;
private static SqlCommand cmd;
private static DataTable dt;
private static DataSet ds;
private static SqlDataAdapter da;
private static string sqlstr;
private static void SQLHelper()
{
sqlstr = "";
}
/// <summary>
/// 执行一个sql语句，返回第一行第一个值
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static object GetFirstValue(string sql)
{
using (Con = new SqlConnection(sqlstr))
{
Con.Open();
cmd = new SqlCommand(sql, Con);
return cmd.ExecuteScalar();
}
}
/// <summary>
/// 执行一个sql语句，返回受影响行数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int GetNonQuery(string sql)
{
using (Con = new SqlConnection(sqlstr))
{
Con.Open();
cmd = new SqlCommand(sql, Con);
return cmd.ExecuteNonQuery();
}
}
/// <summary>
/// 离线模式填充datatable并返回，数据少可以用此方法
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable GetDataTableA(string sql)
{
using (Con = new SqlConnection(sqlstr))
{
da = new SqlDataAdapter(sql, Con);
dt = new DataTable();
da.Fill(dt);
return dt;
}
} /// <summary>
/// 根据sql,填充datatable并返回。数据多的时候可以用此方法
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable GetDataTableB(string sql)
{
using (Con = new SqlConnection(sqlstr))
{
Con.Open();
cmd = new SqlCommand(sql, Con);
dt = new DataTable();
SqlDataReader reader = cmd.ExecuteReader();
dt.Load(reader);
Con.Close();
return dt;
}
}
/// <summary>
/// 传入sql语句集，返回一个dataset
/// </summary>
/// <param name="sqllist"></param>
/// <returns></returns>
public static DataSet GetDataSet(List<string> sqllist)
{
using (Con = new SqlConnection(sqlstr))
{
Con.Open();
ds = new DataSet();
foreach (string c in sqllist)
{
cmd = new SqlCommand(c, Con);
dt = new DataTable();
SqlDataReader reader = cmd.ExecuteReader();
dt.Load(reader);
ds.Tables.Add(dt);
}
Con.Close();
return ds;
}
}
/// <summary>
/// 在事务中执行多条sql语句
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public static bool ExecSQLSList(List<string> list)
{
using (Con = new SqlConnection(sqlstr))
{
Con.Open();
SqlTransaction tran = Con.BeginTransaction();
try
{
foreach (string sql in list)
{
cmd = new SqlCommand(sql, Con);
cmd.ExecuteNonQuery();
}
tran.Commit();
Con.Close();
return true; }
catch
{
tran.Rollback();
return false;
}
finally
{
Con.Close();
Con.Dispose();
}
} } /// <summary>
/// 执行一个不带参数的存储过程，返回datable
/// </summary>
/// <param name="StoredProcedureName"></param>
/// <returns></returns>
public static DataTable GetDataTableByStoredProcedure(string StoredProcedureName)
{
using (Con = new SqlConnection(sqlstr))
{
Con.Open();
cmd = new SqlCommand();
cmd.CommandText = StoredProcedureName;
cmd.CommandType = CommandType.StoredProcedure;
dt = new DataTable();
dt.Load(cmd.ExecuteReader());
return dt;
}
} }
备用~