using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DaoKongStudio
{
#region 使用示例
//Tips:当需要收用数字"0"的时候,请参照:
//list.Add(new SqlParameter("DataStatus",Convert.ToInt32(0)));
//否则可能会为空值而报错!
//string sql = "insert into Test values(@name,@psd)";
//List<SqlParameter> listpara = new List<SqlParameter>();
//listpara.Add(new SqlParameter("name", "1532718597"));
// listpara.Add(new SqlParameter("psd", "123"));
// if (DBHelper.Excute(sql,listpara))
// {
// label2.Text = "成功";
// }
/// <summary>
/// 使用多条sql执行语句时,可以这样使用事务;单条sql执行语句可以使用Excute即可。
/// </summary>
/// <returns></returns>
//public static bool Test()
//{
// bool result1 = false;
// bool result2 = false;
// DBHelper.OpenT();
// string sql1 = "insert into Testdk values(@Name,@Psd,@InLine)";
// List<SqlParameter> list1 = new List<SqlParameter>();
// list1.Add(new SqlParameter("Name", "yindenghui"));
// list1.Add(new SqlParameter("Psd", "123456"));
// list1.Add(new SqlParameter("InLine", 1));
// result1 = DBHelper.ExcuteBest(sql1, list1);
// if (!result1)
// {
// DBHelper.RollBackT();
// }
// string sql2 = "insert into Testdk values(@Name,@Psd,@InLine)";
// List<SqlParameter> list2 = new List<SqlParameter>();
// list2.Add(new SqlParameter("Name", "yindenghui1"));
// list2.Add(new SqlParameter("Psd", "123456"));
// list2.Add(new SqlParameter("InLine", 1));
// result2 = DBHelper.ExcuteBest(sql2, list2);
// if (!result2)
// {
// DBHelper.RollBackT();
// }
// DBHelper.CommitT();
// return (result1 && result2);
//}
#endregion
#region 类与方法
class DBHelperBase
{
protected static string constr = "Data Source=IP;Initial Catalog=DBName;User ID=UserName;Password=Psd";//连接字符串
/// <summary>
/// 使用参数化查询
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="listpara">参数</param>
/// <returns></returns>
public static DataTable Select(string sql, List<SqlParameter> listpara)
{
DataTable dt = new DataTable();//实例化一个datatable
SqlTransaction t_select = null;//事务
SqlConnection con = new SqlConnection(constr);//创建数据库连接实例
con.Open();//打开连接
try
{
t_select = con.BeginTransaction();//开始事务
SqlCommand cmd = new SqlCommand(sql);
cmd.Transaction = t_select;//绑定事务
cmd.Connection = con;
foreach (var item in listpara)
{
cmd.Parameters.Add(item);
}
SqlDataAdapter adapter = new SqlDataAdapter(cmd);//适配器
adapter.Fill(dt);//转datatable
t_select.Commit();//提交
//清空参数,否则第二次使用同样的参数的时候会
//造成“另一个 SqlParameterCollection 中已包含 SqlParameter”的异常
cmd.Parameters.Clear();
}
catch (Exception ex)
{
t_select.Rollback();//回滚
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();//关闭连接
}
}
return dt;
}
/// <summary>
/// 使用参数化执行
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="listpara">参数</param>
/// <returns></returns>
public static bool Excute(string sql, List<SqlParameter> listpara)
{
bool result = false;
SqlTransaction t_select = null;//事务
SqlConnection con = new SqlConnection(constr);//创建数据库连接实例
con.Open();//打开连接
try
{
t_select = con.BeginTransaction();//开始事务
SqlCommand cmd = new SqlCommand(sql);
cmd.Transaction = t_select;//绑定事务
cmd.Connection = con;
foreach (var item in listpara)
{
cmd.Parameters.Add(item);
}
result = cmd.ExecuteNonQuery() > 0;
t_select.Commit();//提交
cmd.Parameters.Clear();
}
catch (Exception ex)
{
t_select.Rollback();//回滚
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();//关闭连接
}
}
return result;
}
}
class DBHelper:DBHelperBase
{
private static SqlConnection con = new SqlConnection(constr);//创建数据库连接实例
private static SqlTransaction t_select = null;//事务
private static int T_Count = 0;//大于0就提交事务
//打开连接
private static void OpenCon() {
if (con.State!=ConnectionState.Open)
{
con.Open();
}
}
//关闭连接
private static void CloseCon() {
if (con.State==ConnectionState.Open)
{
con.Close();
}
}
//开启事务
public static void OpenT() {
OpenCon();//打开连接
t_select = con.BeginTransaction();//开始事务
}
//提交事务
public static void CommitT() {
if (T_Count > 0)
{
t_select.Commit();//提交
}
CloseCon();
}
//回滚事务
public static void RollBackT() {
if (T_Count>0)
{
t_select.Rollback();//回滚
T_Count = 0;
}
}
/// <summary>
/// 使用多条语句时可以使用此执行方法
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="listpara">参数</param>
/// <returns></returns>
public static bool ExcuteBest(string sql, List<SqlParameter> listpara)
{
bool result = false;
try
{
SqlCommand cmd = new SqlCommand(sql);
cmd.Transaction = t_select;//绑定事务
cmd.Connection = con;
foreach (var item in listpara)
{
cmd.Parameters.Add(item);
}
result = cmd.ExecuteNonQuery() > 0;
if (result)
{
T_Count++;
}
}
catch (Exception ex)
{
result = false;
}
return result;
}
}
#endregion
}
1.使用参数化执行可以不在数据库创建存储过程,直接在代码里执行操作即可完成!个人认为较为方便!