DBHelper（使⽤参数化+事务查询和执⾏）
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
#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);//适配器sqltransaction什么意思
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();//开始事务
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;
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.使⽤参数化执⾏可以不在数据库创建存储过程，直接在代码⾥执⾏操作即可完成！个⼈认为较为⽅便！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。