using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Transactions;
namespace LenovoDataCrawlService.DBAccessor
{
public class DBHelper
{
#region Properties
protected bool disposed = false;
public static int TRANS_TIMEOUT_SEC = 180;
public static int COMMAND_TIMEOUT = 60;
public static string ConnectionString { get; set; }
private static ILog log = LogManager.GetLogger(typeof(DBHelper));
private DbProviderFactory providerFactory;
#endregion
#region Constructor
public DBHelper(DbProviderType providerType)
: this(providerType, ConnectionString)
{
}
public DBHelper(DbProviderType providerType, string connectionString)
{
ConnectionString = connectionString;
providerFactory = ProviderFactory.GetDbProviderFactory(providerType);
if (providerFactory == null)
{
throw new ArgumentException("Can't load DbProviderFactory for given value of providerType");
}
}
#endregion
#region Create DbCommand
private DbCommand CreateCommand(string sql, CommandType commandType, params DbParameter[] args)
{
return CreateCommand(sql, commandType, true, args);
}
private DbCommand CreateCommand(string sql, CommandType commandType, bool open, params DbParameter[] args)
{
DbConnection connection = providerFactory.CreateConnection();
connection.ConnectionString = ConnectionString;
DbCommand command = providerFactory.CreateCommand();
command.Connection = connection;
command.CommandType = commandType;
command.CommandText = sql;
command.CommandTimeout = COMMAND_TIMEOUT;
if (open)
{
command.Connection.Open();
}
if (!(args == null || args.Length == 0))
{
foreach (DbParameter param in args)
{
command.Parameters.Add(param);
}
}
return command;
}
#endregion
#region Exec Members
public int ExecNonQuery(string sql, params DbParameter[] args)
{
using (DbCommand cmd = CreateCommand(sql, CommandType.Text, args))
{
int result = cmd.ExecuteNonQuery();
cmd.Connection.Dispose();
return result;
}
}
public int ExecNonQueryProc(string proc, params DbParameter[] args)
{
using (DbCommand cmd = CreateCommand(proc, CommandType.StoredProcedure, args))
{
int result = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return result;
}
}
public object ExecScalar(string sql, params DbParameter[] args)
{
using (DbCommand cmd = CreateCommand(sql, CommandType.Text, args))
{
object result = cmd.ExecuteScalar();
cmd.Connection.Close();
return result;
}
}
public object ExecScalarProc(string sql, params DbParameter[] args)
{
using (DbCommand cmd = CreateCommand(sql, CommandType.StoredProcedure, args))
{
object result = cmd.ExecuteScalar();
cmd.Connection.Close();
return result;
}
}
public DbDataReader ExecDataReader(string sql, out DbCommand command, params DbParameter[] args)
{
command = CreateCommand(sql, CommandType.Text, args);
DbDataReader result = command.ExecuteReader();
return result;
}
public DbDataReader ExecDataReaderProc(string sql, out DbCommand command, params DbParameter[] args)
{
command = CreateCommand(sql, CommandType.StoredProcedure, args);
DbDataReader result = command.ExecuteReader();
return result;
}
public DataSet ExecDataSet(string sql, params DbParameter[] args)
{
using (DbCommand cmd = CreateCommand(sql, CommandType.Text, false, args))
{
return GetDataSet(cmd);
}
}
public DataSet ExecDataSetProc(string sql, params DbParameter[] args)
{
using (DbCommand cmd = CreateCommand(sql, CommandType.StoredProcedure, false, args))
{
return GetDataSet(cmd);
}
}
public DataTable ExecDataTable(string sql, params DbParameter[] args)
{
using (DbCommand cmd = CreateCommand(sql, CommandType.Text, false, args))
{
return GetDataTable(cmd);
}
}
public DataTable ExecDataTableProc(string sql, params DbParameter[] args)
{
using (DbCommand cmd = CreateCommand(sql, CommandType.StoredProcedure, false, args))
{
return GetDataTable(cmd);
}
}
private DataSet GetDataSet(DbCommand cmd)
{
using (DbDataAdapter adapter = providerFactory.CreateDataAdapter())
{
adapter.SelectCommand = cmd;
DataSet dataset = new DataSet();
adapter.Fill(dataset);
return dataset;
}
}
private DataTable GetDataTable(DbCommand cmd)
{
using (DbDataAdapter adapter = providerFactory.CreateDataAdapter())
{
adapter.SelectCommand = cmd;
DataTable table = new DataTable();
adapter.Fill(table);
return table;
}
}
#endregion
#region Transaction Members
public static TransactionScope BeginTransaction()
{
return new TransactionScope(TransactionScopeOption.Required, new TransactionOptions()
{
IsolationLevel = System.Transactions.IsolationLevel.RepeatableRead,
Timeout = new TimeSpan(TimeSpan.TicksPerSecond * TRANS_TIMEOUT_SEC)
});
}
public static void BeginTransaction(Action action)
{
using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions()
{
IsolationLevel = System.Transactions.IsolationLevel.RepeatableRead,
Timeout = new TimeSpan(TimeSpan.TicksPerSecond * TRANS_TIMEOUT_SEC)
}))
{
try
{
if (action != null)
{
action();
trans.Complete();
}
}
catch (Exception ex)
{
log.Error("Execute sql failed", ex);
}
}
}
public static void EndTransaction(TransactionScope transactionSope)
{
if (transactionSope != null)
{
transactionSope.Dispose();
}
}
#endregion
#region IDisposable Members
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
protected virtual void Dispose(bool disposing)
{
if (!disposed)
{
if (disposing)
{
if (providerFactory != null)
{
providerFactory = null;
}
}
disposed = true;
}
}
#endregion
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace LenovoDataCrawlService.DBAccessor
{
public enum DbProviderType
{
SqlServer,
MySql,
SQLite,
Oracle,
ODBC,
OleDb,
Firebird,
PostgreSql,
DB2,
Informix,
SqlServerCe
}
}
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
namespace LenovoDataCrawlService.DBAccessor
{
public class ProviderFactory
{
private static ConcurrentDictionary<DbProviderType, string> providerInvariantNames = new ConcurrentDictionary<DbProviderType, string>();
private static ConcurrentDictionary<DbProviderType, DbProviderFactory> providerFactoies = new ConcurrentDictionary<DbProviderType, DbProviderFactory>();
static ProviderFactory()
{
providerInvariantNames.TryAdd(DbProviderType.SqlServer, "System.Data.SqlClient");
providerInvariantNames.TryAdd(DbProviderType.OleDb, "System.Data.OleDb");
providerInvariantNames.TryAdd(DbProviderType.ODBC, "System.Data.ODBC");
providerInvariantNames.TryAdd(DbProviderType.Oracle, "Oracle.DataAccess.Client");
providerInvariantNames.TryAdd(DbProviderType.MySql, "MySql.Data.MySqlClient");
providerInvariantNames.TryAdd(DbProviderType.SQLite, "System.Data.SQLite");
providerInvariantNames.TryAdd(DbProviderType.Firebird, "FirebirdSql.Data.Firebird");
providerInvariantNames.TryAdd(DbProviderType.PostgreSql, "Npgsql");
providerInvariantNames.TryAdd(DbProviderType.DB2, "IBM.Data.DB2.iSeries");
providerInvariantNames.TryAdd(DbProviderType.Informix, "IBM.Data.Informix");
providerInvariantNames.TryAdd(DbProviderType.SqlServerCe, "System.Data.SqlServerCe");
}
public static string GetProviderInvariantName(DbProviderType providerType)
{
return providerInvariantNames[providerType];
}
public static DbProviderFactory GetDbProviderFactory(DbProviderType providerType)
{
if (!providerFactoies.ContainsKey(providerType))
{
providerFactoies.TryAdd(providerType, ImportDbProviderFactory(providerType));
}
return providerFactoies[providerType];
}
private static DbProviderFactory ImportDbProviderFactory(DbProviderType providerType)
{
string providerName = providerInvariantNames[providerType];
DbProviderFactory factory = null;
try
{
factory = DbProviderFactories.GetFactory(providerName);
}
catch (ArgumentException e)
{
factory = null;
}
return factory;
}
}
}
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Text;
namespace LenovoDataCrawlService
{
public class DbDataReaderReflection
{
public static List<T> Convert<T>(DbDataReader dataReader) where T : new()
{
List<T> result = new List<T>();
if (dataReader != null)
{
using (dataReader)
{
while (dataReader.Read())
{
T data = new T();
Type type = data.GetType();
PropertyInfo[] propertyInfos = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (var item in propertyInfos)
{
var obj = new object();
try
{
obj = dataReader[item.Name];
}
catch (Exception)
{
continue;
}
if (obj == DBNull.Value || obj == null)
{
continue;
}
if (item.CanWrite)
{
item.SetValue(data, obj, null);
}
}
result.Add(data);
}
}
}
return result;
}
}
}
<system.data>
<DbProviderFactories>
<remove invariant="MySql.Data.MySqlClient" />
<add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.9.8.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</DbProviderFactories>
</system.data>
<connectionStrings>
<clear/>
<add name="MysqlConnectionString" connectionString="Server=*;Port=*;Database=*;Uid=root;Pwd=*;MinimumPoolSize=10;maximumpoolsize=100;" providerName="MySql.Data.MySqlClient"/>
</connectionStrings>