public static DataTable Select(string selectCommandText, CommandType commandType, DbConnection dbConnection, ISqlClientProvider sqlClientProvider)
{
var dt = new DataTable();
Select(selectCommandText, commandType, dt, dbConnection, sqlClientProvider);
return(dt);
}
public SqlHelper(ISqlClientProvider sqlClientProvider)
{
_sqlClientProvider = sqlClientProvider;
_conn = sqlClientProvider.CreateConnection();
_conn.StateChange += ConnectionStateChanged;
}
public static void Select(string selectCommandText, CommandType commandType, DataTable dt, DbConnection dbConnection, ISqlClientProvider sqlClientProvider)
{
dbConnection.OpenIfClosed();
using (var cmd = sqlClientProvider.CreateCommand(selectCommandText, commandType, dbConnection))
{
using (var sda = sqlClientProvider.CreateDataAdapter(cmd))
{
sda.Fill(dt);
}
}
}
public static int Insert(string tableName, Dictionary <string, object> colAndVals, DbTransaction dbTransaction, ISqlClientProvider sqlClientProvider)
{
ThrowIfTransactionIsInvalid(dbTransaction);
var colVal = BuildFieldsAndValues(colAndVals);
var sql = string.Format(_INSERT_SQL_TEXT, tableName, colVal.Fields, colVal.Values);
using (var cmd = sqlClientProvider.CreateCommand(sql, CommandType.Text, dbTransaction.Connection))
{
foreach (var kv in colAndVals)
{
var para = cmd.CreateParameter();
para.Direction = ParameterDirection.Input;
para.ParameterName = '@' + kv.Key;
para.Value = kv.Value;
para.DbType = DbType.AnsiString;
cmd.Parameters.Add(para);
}
return(cmd.ExecuteNonQuery());
}
}
public static T ExecuteScalar <T>(string commandText, CommandType commandType, DbConnection dbConnection, ISqlClientProvider sqlClientProvider)
{
using (var cmd = sqlClientProvider.CreateCommand(commandText, commandType, dbConnection))
{
var objValue = cmd.ExecuteScalar();
if (objValue == null)
{
if (typeof(T).IsValueType)
{
throw new Exception("查询结过为空");
}
}
if (typeof(T) == objValue.GetType())
{
return((T)objValue);
}
return((T)Convert.ChangeType(objValue, typeof(T)));
}
}
public static int ExecuteNonQuery(string commandText, CommandType commandType, DbConnection dbConnection, ISqlClientProvider sqlClientProvider)
{
using (var cmd = sqlClientProvider.CreateCommand(commandText, commandType, dbConnection))
{
return(cmd.ExecuteNonQuery());
}
}
public static int ExecuteNonQuery(string commandText, CommandType commandType, DbTransaction dbTransaction, ISqlClientProvider sqlClientProvider)
{
ThrowIfTransactionIsInvalid(dbTransaction);
using (var cmd = sqlClientProvider.CreateCommand(commandText, commandType, dbTransaction.Connection))
{
cmd.Transaction = dbTransaction;
return(cmd.ExecuteNonQuery());
}
}
