本文共 3112 字,大约阅读时间需要 10 分钟。
using System.Collections.Generic;
using System.Data; using System.Data.Common;namespace ConsoleApp1
{ public class DbHelper { #region 私有成员/// <summary>
/// 数据库类型 /// </summary> private static string ProviderName;/// <summary>
/// 连接字符串 /// </summary> private static string ConnectionString;#endregion
#region 外部接口
/// <summary>
/// 通过数据库连接字符串和Sql语句查询返回DataTable /// </summary> /// <param name="sql">Sql语句</param> /// <returns></returns> public static DataTable GetDataTableWithSql(string sql) { DbProviderFactory dbProviderFactory = DbProviderFactories.GetFactory(ProviderName); using (DbConnection conn = dbProviderFactory.CreateConnection()) { conn.ConnectionString = ConnectionString; conn.Open();using (DbCommand cmd = conn.CreateCommand())
{ cmd.Connection = conn; cmd.CommandText = sql;DbDataAdapter adapter = dbProviderFactory.CreateDataAdapter();
adapter.SelectCommand = cmd; DataSet table = new DataSet(); adapter.Fill(table); cmd.Parameters.Clear(); return table.Tables[0]; } } }/// <summary>
/// 通过数据库连接字符串和Sql语句查询返回DataTable,参数化查询 /// </summary> /// <param name="sql">Sql语句</param> /// <param name="parameters">参数</param> /// <returns></returns> public static DataTable GetDataTableWithSql(string sql, List<DbParameter> parameters) { DbProviderFactory dbProviderFactory = DbProviderFactories.GetFactory(ProviderName); using (DbConnection conn = dbProviderFactory.CreateConnection()) { conn.ConnectionString = ConnectionString; conn.Open();using (DbCommand cmd = conn.CreateCommand())
{cmd.Connection = conn;
cmd.CommandText = sql; if (parameters != null && parameters.Count > 0) { foreach (var item in parameters) { cmd.Parameters.Add(item); } } DbDataAdapter adapter = dbProviderFactory.CreateDataAdapter(); adapter.SelectCommand = cmd; DataSet table = new DataSet(); adapter.Fill(table); cmd.Parameters.Clear();return table.Tables[0];
} } }/// <summary>
/// 执行无返回值的Sql语句 /// </summary> /// <param name="sql">Sql语句</param> public static int ExecuteSql(string sql) { int count = 0; DbProviderFactory dbProviderFactory = DbProviderFactories.GetFactory(ProviderName); using (DbConnection conn = dbProviderFactory.CreateConnection()) { conn.ConnectionString = ConnectionString; conn.Open(); using (DbCommand cmd = dbProviderFactory.CreateCommand()) { cmd.Connection = conn; cmd.CommandText = sql; count = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return count; } } }/// <summary>
/// 执行无返回值的Sql语句 /// </summary> /// <param name="sql">Sql语句</param> /// <param name="spList">查询参数</param> public static int ExecuteSql(string sql, List<DbParameter> paramters) { int count = 0; DbProviderFactory dbProviderFactory = DbProviderFactories.GetFactory(ProviderName); using (DbConnection conn = dbProviderFactory.CreateConnection()) { conn.ConnectionString = ConnectionString; conn.Open(); using (DbCommand cmd = dbProviderFactory.CreateCommand()) { cmd.Connection = conn; cmd.CommandText = sql;if (paramters != null && paramters.Count > 0)
{ foreach (var item in paramters) { cmd.Parameters.Add(item); } } count = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return count; } } }#endregion
} }转载地址:http://jjbkf.baihongyu.com/