/// <summary>
/// 返回查询结果中第一行第一列
/// </summary>
/// <param name="commandText"></param>
/// <param name="cmdType"></param>
/// <param name="paraName"></param>
/// <param name="paraValue"></param>
/// <param name="type"></param>
/// <param name="adapter"></param>
/// <param name="dbString">分库名</param>
/// <returns></returns>
public static object ExcuteScalar(string commandText, CommandType cmdType = CommandType.Text, string[] paraName = null, object[] paraValue = null, DbConntionType type = DbConntionType.OnlyRead, FluDataAdapter adapter = null, string dbString = null)
{
if (adapter == null)
{
using (FluDataAdapter da = GetAdapter(commandText, cmdType, paraName, paraValue, type, dbString))
{
da.OpenConnection();
object obj = da.SelectCommand.ExecuteScalar();
da.CloseConnection();
return(obj);
}
}
adapter.ClearParameter();
adapter.CommandText = commandText;
adapter.CommandType = cmdType;
if (paraName != null && paraValue != null && paraName.Length == paraValue.Length)
{
for (int i = 0; i < paraName.Length; i++)
{
adapter.AddParameter(paraName[i], paraValue[i]);
}
}
object result2 = adapter.SelectCommand.ExecuteScalar();
return(result2);
}
/// <summary>
/// 返回查询结果中第一行第一列
/// </summary>
/// <param name="commandText"></param>
/// <param name="parameters"></param>
/// <param name="cmdType"></param>
/// <param name="type"></param>
/// <param name="adapter"></param>
/// <param name="dbString">分库名</param>
/// <returns></returns>
public static object ExcuteScalar3(string commandText, IList <IDbDataParameter> parameters = null, CommandType cmdType = CommandType.Text, DbConntionType type = DbConntionType.OnlyRead, FluDataAdapter adapter = null, string dbString = null)
{
if (adapter == null)
{
using (FluDataAdapter da = GetAdapter3(commandText, parameters, cmdType, type, dbString))
{
da.OpenConnection();
object obj = da.SelectCommand.ExecuteScalar();
da.CloseConnection();
return(obj);
}
}
adapter.ClearParameter();
adapter.CommandText = commandText;
adapter.CommandType = cmdType;
if (parameters != null)
{
foreach (var p in parameters)
{
adapter.AddParameter(p);
}
}
object result2 = adapter.SelectCommand.ExecuteScalar();
return(result2);
}
/// <summary>
/// 含事务处理DataAdapter
/// </summary>
/// <param name="action">DataApapter, Transaction需要手动commit or rollback</param>
/// <param name="dbString">数据库名称</param>
/// <returns></returns>
public static bool ExcuteTransaction(Action <FluDataAdapter, DbTransaction> action, string dbString = null)
{
using (FluDataAdapter da = GetAdapter(dbString: dbString))
{
da.OpenConnection();
using (DbTransaction ts = da.Connection.BeginTransaction())
{
try
{
da.SelectCommand.Transaction = ts;
action.Invoke(da, ts);
return(true);
}
catch (Exception ex)
{
ts.Rollback();
throw ex;
}
finally
{
da.CloseConnection();
}
}
}
}
/// <summary>
/// 含事务处理DataAdapter
/// </summary>
/// <param name="action">一系列的数据库更新语句</param>
/// <param name="dbString">数据库名称</param>
public static bool ExcuteTransaction(Action <FluDataAdapter> action, string dbString = null)
{
using (FluDataAdapter da = GetAdapter(dbString: dbString))
{
da.OpenConnection();
using (DbTransaction ts = da.Connection.BeginTransaction())
{
try
{
da.SelectCommand.Transaction = ts;
action.Invoke(da);
ts.Commit();
return(true);
}
catch (Exception ex)
{
ts.Rollback();
//Logging4net.WriteError(ex, "事务执行出错："+ da.SelectCommand.CommandText);
throw ex;
}
finally
{
da.CloseConnection();
}
}
}
}
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="commandText"></param>
/// <param name="parameters">参数化列表</param>
/// <param name="cmdType"></param>
/// <param name="adapter"></param>
/// <param name="dbString">分库名</param>
/// <returns></returns>
public static int ExcuteNonQuery3(string commandText, IList <IDbDataParameter> parameters = null, CommandType cmdType = CommandType.Text, FluDataAdapter adapter = null, string dbString = null)
{
if (adapter == null)
{
using (adapter = GetAdapter3(commandText, parameters, cmdType, DbConntionType.WriteRead, dbString))
{
adapter.OpenConnection();
int result = adapter.SelectCommand.ExecuteNonQuery();
adapter.CloseConnection();
return(result);
}
}
adapter.ClearParameter();
adapter.CommandText = commandText;
adapter.CommandType = cmdType;
if (parameters != null)
{
foreach (var p in parameters)
{
adapter.AddParameter(p);
}
}
int result2 = adapter.SelectCommand.ExecuteNonQuery();
return(result2);
}
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="commandText"></param>
/// <param name="cmdType"></param>
/// <param name="paraName">参数名数组</param>
/// <param name="paraValue">参数值数组</param>
/// <param name="adapter">是否外部传递，如使用事务</param>
/// <param name="dbString">分库名</param>
/// <returns></returns>
public static int ExcuteNonQuery(string commandText, CommandType cmdType = CommandType.Text, string[] paraName = null, object[] paraValue = null, FluDataAdapter adapter = null, string dbString = null)
{
if (adapter == null)
{
using (adapter = GetAdapter(commandText, cmdType, paraName, paraValue, DbConntionType.WriteRead, dbString))
{
adapter.OpenConnection();
int result = adapter.SelectCommand.ExecuteNonQuery();
adapter.CloseConnection();
return(result);
}
}
adapter.ClearParameter();
adapter.CommandText = commandText;
adapter.CommandType = cmdType;
if (paraName != null && paraValue != null && paraName.Length == paraValue.Length)
{
for (int i = 0; i < paraName.Length; i++)
{
adapter.AddParameter(paraName[i], paraValue[i]);
}
}
int result2 = adapter.SelectCommand.ExecuteNonQuery();
return(result2);
}
/// <summary>
/// 返回DataReader，must using dispose
/// </summary>
/// <param name="commandText"></param>
/// <param name="cmdType"></param>
/// <param name="paraName"></param>
/// <param name="paraValue"></param>
/// <param name="type"></param>
/// <param name="dbString">分库名</param>
/// <returns></returns>
public static IDataReader ExecuteReader(string commandText, CommandType cmdType = CommandType.Text, string[] paraName = null, object[] paraValue = null, DbConntionType type = DbConntionType.OnlyRead, string dbString = null)
{
FluDataAdapter da = GetAdapter(commandText, cmdType, paraName, paraValue, type, dbString);
da.OpenConnection();
IDataReader dr = da.SelectCommand.ExecuteReader(CommandBehavior.CloseConnection);
return(dr);
}
/// <summary>
/// 取得查询数据
/// </summary>
/// <param name="tableName"></param>
/// <param name="columns"></param>
/// <param name="conditions"></param>
/// <param name="orderby"></param>
/// <param name="groupby"></param>
/// <param name="type"></param>
/// <param name="dbString">分库名</param>
/// <returns></returns>
public static DataTable GetData(string tableName, string columns, string conditions, string orderby, string groupby, DbConntionType type = DbConntionType.OnlyRead, string dbString = null)
{
using (FluDataAdapter da = GetAdapter(type: type, dbString: dbString))
{
da.OpenConnection();
DataTable dt = da.GetData2(tableName, columns, conditions, orderby, groupby);
da.CloseConnection();
return(dt);
}
}
/// <summary>
/// 取得分页数据
/// </summary>
/// <param name="page"></param>
/// <param name="type"></param>
/// <param name="dbString">分库名</param>
/// <returns></returns>
public static DataTable GetPagingData(PagingInfo page, DbConntionType type = DbConntionType.OnlyRead, string dbString = null)
{
using (FluDataAdapter da = GetAdapter(type: type, dbString: dbString))
{
da.OpenConnection();
DataTable dt = da.GetPagingData2(page);
da.CloseConnection();
return(dt);
}
}
/// <summary>
/// 返回DataTable
/// </summary>
/// <param name="commandText"></param>
/// <param name="parameters">参数对象列表</param>
/// <param name="cmdType"></param>
/// <param name="type"></param>
/// <param name="dbString">分库名</param>
/// <returns></returns>
public static DataTable Fill3(string commandText, IList <IDbDataParameter> parameters = null, CommandType cmdType = CommandType.Text, DbConntionType type = DbConntionType.OnlyRead, string dbString = null)
{
using (FluDataAdapter da = GetAdapter3(commandText, parameters, cmdType, type, dbString))
{
da.OpenConnection();
DataTable dt = new DataTable();
da.Fill(dt);
da.CloseConnection();
return(dt);
}
}
/// <summary>
/// 返回DataTable
/// </summary>
/// <param name="commandText"></param>
/// <param name="cmdType"></param>
/// <param name="paraName"></param>
/// <param name="paraValue"></param>
/// <param name="type"></param>
/// <param name="dbString">分库名</param>
/// <returns></returns>
public static DataTable Fill(string commandText, CommandType cmdType = CommandType.Text, string[] paraName = null, object[] paraValue = null, DbConntionType type = DbConntionType.OnlyRead, string dbString = null)
{
using (FluDataAdapter da = GetAdapter(commandText, cmdType, paraName, paraValue, type, dbString))
{
da.OpenConnection();
DataTable dt = new DataTable();
da.Fill(dt);
da.CloseConnection();
return(dt);
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="commandText"></param>
/// <param name="parameters"></param>
/// <param name="dbString"></param>
/// <returns></returns>
public static object ExcuteStoredProcedure(string commandText, IList <IDbDataParameter> parameters = null, string dbString = null)
{
using (FluDataAdapter adapter = GetAdapter3(commandText, parameters, CommandType.StoredProcedure, DbConntionType.WriteRead, dbString))
{
adapter.OpenConnection();
object result = adapter.SelectCommand.ExecuteNonQuery();
if (parameters != null)
{
foreach (var p in parameters)
{
if (p.Direction == ParameterDirection.Output)
{
return(p.Value);
}
}
adapter.CloseConnection();
}
return(result);
}
}