protected T Do <T>(DBBuilder dbBuilder, Func <IDbConnection, T> fn)
{
if (dbBuilder.Transaction != null)
{
return(fn(dbBuilder.Transaction.Connection));
}
else if (this.Transaction != null)
{
return(fn(dbBuilder.SetTransaction(this.Transaction).Transaction.Connection));
}
else
{
using (var connection = DBAdapter.CreateConnection(this.connection.ConnectionString))
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
T result = fn(connection);
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
return(result);
}
}
}
/// <summary>
/// 判断指定的数据字段是否存在
/// </summary>
/// <param name="tableName"></param>
/// <param name="fieldName"></param>
/// <returns></returns>
public static bool HasDataField(string tableName, string fieldName)
{
IDBQuery query = CFAspect.Resolve <IDBQuery>();
DBBuilder sql = DBBuilder.Select().Append(dbAdapter => "count(*) from " + dbAdapter.FormatFunction("getcolumns", dbAdapter.FormatValue(tableName)) + " where column_name=" + dbAdapter.FormatValue(fieldName));
return(Convert.ToInt32(query.ToScalar(sql)) > 0);
}
/// <summary>
/// 表示where
/// </summary>
/// <returns></returns>
public DBBuilder Where(DBBuilder builder)
{
if (builder == null || builder.sqlBuilder.Length == 0)
{
return(this);
}
return(this.Append("\nWHERE ").Append(builder));
}
public void ToReader(DBBuilder dbBuilder, Action <IDataReader> action)
{
this.Do(dbBuilder, connection =>
{
var reader = connection.ExecuteReader(dbBuilder.GetCommand(DBAdapter));
action(reader);
return(reader);
});
}
public DataTable ToDataTable(DBBuilder dbBuilder)
{
return(this.Do(dbBuilder, connection =>
{
DataTable dt = new DataTable();
using (var reader = connection.ExecuteReader(dbBuilder.GetCommand(DBAdapter)))
{
dt.Load(reader);
}
return dt;
}));
}
public List <T> ToList <T>(DBBuilder dbBuilder, string sortExpression = null) where T : new()
{
if (sortExpression == null)
{
return(this.Do(dbBuilder, connection => connection.Query <T>(dbBuilder.GetCommand(DBAdapter)).ToList()));
}
dbBuilder.Compile(this.DBAdapter);
var dbAnalyzer = DBAnalyzer.Init(dbBuilder.Sql, null, null, sortExpression);
dbBuilder.Sql = dbAnalyzer.Select();
return(this.Do(dbBuilder, connection => connection.Query <T>(dbBuilder.GetCommand(DBAdapter)).ToList()));
}
/// <summary>
/// 创建以delete from开头语的SQL语句
/// </summary>
/// <param name="tablename"></param>
/// <param name="whereParam"></param>
/// <returns></returns>
public static DBBuilder Delete(string tablename = null, dynamic whereParam = null)
{
var builder = new DBBuilder("DELETE ");
if (tablename != null)
{
builder.From(tablename);
}
if (whereParam != null)
{
builder.Where(whereParam);
}
return(builder);
}
/// <summary>
/// 创建以select FieldName1,FieldName2 from TableName开头的SQL语句
/// </summary>
/// <param name="tablename">要查询的表名</param>
/// <param name="whereParam">要查询的字段名</param>
/// <returns></returns>
public static DBBuilder Select(string tablename = null, object whereParam = null)
{
var builder = new DBBuilder("SELECT ");
if (tablename != null)
{
builder.Append("*").From(tablename);
}
if (whereParam != null)
{
builder.Where(whereParam);
}
return(builder);
}
/// <summary>
/// 创建以insert into开头语的SQL语句
/// </summary>
/// <returns></returns>
public static DBBuilder Insert(string tablename = null, dynamic valueParam = null)
{
var builder = new DBBuilder("INSERT INTO ");
if (tablename != null && valueParam != null)
{
var dp = new DBParameterList(valueParam);
builder.Table(tablename, dp.ParameterNames.ToArray()).Values(dp);
}
else if (tablename != null)
{
builder.Table(tablename);
}
return(builder);
}
public PagedList ToDataTable(DBBuilder dbBuilder, int pageIndex, int pageSize, string sortExpression = null)
{
dbBuilder.Compile(this.DBAdapter);
var dbAnalyzer = DBAnalyzer.Init(dbBuilder.Sql, pageIndex, pageSize, sortExpression);
dbBuilder.Sql = dbAnalyzer.Count();
var count = Convert.ToInt32(ToScalar(dbBuilder));
dbBuilder.Sql = dbAnalyzer.Select();
return(new PagedList
{
CurrentPage = pageIndex,
ItemsPerPage = pageSize,
TotalItems = count,
TotalPages = count % pageSize == 0 ? count / pageSize : (count / pageSize) + 1,
Items = ToDataTable(dbBuilder).AsEnumerable().ToList(),
});
}
/// <summary>
/// 创建以update开头语的SQL语句
/// </summary>
/// <param name="tablename"></param>
/// <param name="setParam"></param>
/// <param name="whereParam"></param>
/// <returns></returns>
public static DBBuilder Update(string tablename = null, dynamic setParam = null, dynamic whereParam = null)
{
var builder = new DBBuilder("UPDATE ");
if (tablename != null)
{
builder.Table(tablename);
}
if (setParam != null)
{
builder.Set(setParam);
}
if (whereParam != null)
{
builder.Where(whereParam);
}
return(builder);
}
/// <summary>
/// 执行SQL语句并获取返回值
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dbBuilder"></param>
/// <returns></returns>
public PagedList <T> ToList <T>(DBBuilder dbBuilder, int pageIndex = 1, int pageSize = 15, string sortExpression = null) where T : new()
{
dbBuilder.Compile(this.DBAdapter);
var dbAnalyzer = DBAnalyzer.Init(dbBuilder.Sql, pageIndex, pageSize, sortExpression);
dbBuilder.Sql = dbAnalyzer.Count();
var count = Convert.ToInt32(ToScalar(dbBuilder));
dbBuilder.Sql = dbAnalyzer.Select();
return(new PagedList <T>
{
CurrentPage = pageIndex,
ItemsPerPage = pageSize,
TotalItems = count,
TotalPages = count % pageSize == 0 ? count / pageSize : (count / pageSize) + 1,
Items = ToList <T>(dbBuilder)
});
}
/// <summary>
/// 使用语句和参数初始化SQL命令
/// </summary>
/// <param name="dbBuilder"></param>
/// <param name="sql">查询语句</param>
/// <param name="param">查询参数，例如：new {UserID = 1, UserName = "******"}</param>
/// <param name="fragmentArguments">片段参数</param>
private DBBuilder(DBBuilder dbBuilder, string sql = null, dynamic param = null, Dictionary <string, Func <IDBAdapter, string> > fragmentArguments = null)
{
this.sqlBuilder = new StringBuilder(dbBuilder.sqlBuilder.ToString());
if (sql != null)
{
this.sqlBuilder.Append(sql);
}
this.Parameters = new DBParameterList(dbBuilder.Parameters);
if (param != null)
{
this.Parameters.AddExpandoParams(param);
}
this.fragmentArguments = new Dictionary <string, Func <IDBAdapter, string> >();
if (fragmentArguments != null)
{
fragmentArguments.ForEach(kv => this.Replace(kv.Key, kv.Value));
}
this.CommandType = dbBuilder.CommandType;
this.Transaction = dbBuilder.Transaction;
}
public DBBuilder NotExists(DBBuilder dbBuilder, bool predicate = true)
{
return(Clause(dbBuilder, " NOT EXISTS ", predicate));
}
public DBBuilder NotIn(DBBuilder dbBuilder, bool predicate = true)
{
return(Clause(dbBuilder, " NOT IN ", predicate));
}
public DBBuilder Not(DBBuilder dbBuilder, bool predicate = true)
{
return(Clause(dbBuilder, " NOT "));
}
public DBBuilder Or(DBBuilder dbBuilder, bool predicate = true)
{
return(Clause(dbBuilder, "\n OR ", predicate));
}
public DBBuilder And(DBBuilder dbBuilder, bool predicate = true)
{
return(Clause(dbBuilder, "\n AND ", predicate));
}
public int Execute(DBBuilder dbBuilder)
{
return(this.Do(dbBuilder, connection => connection.Execute(dbBuilder.GetCommand(DBAdapter))));
}
/// <summary>
/// 表示 Prefix (dbBuilder)
/// </summary>
/// <param name="dbBuilder"></param>
/// <param name="prefix"></param>
/// <param name="predicate"></param>
/// <returns></returns>
public DBBuilder Clause(DBBuilder dbBuilder, string prefix = null, bool predicate = true)
{
return(Clause(d => d.Append(dbBuilder), prefix, predicate));
}
/// <summary>
/// 向现有查询语句末尾增加新条件
/// </summary>
/// <param name="builder"></param>
/// <returns></returns>
public DBBuilder Append(DBBuilder builder)
{
return(Append(builder.sqlBuilder.ToString(), builder.Parameters, builder.fragmentArguments));
}
/// <summary>
/// 创建SQL片段
/// </summary>
/// <param name="dbBuilder"></param>
/// <returns></returns>
public static DBBuilder Define(DBBuilder dbBuilder)
{
return(new DBBuilder(dbBuilder.sqlBuilder.ToString(), dbBuilder.Parameters, dbBuilder.CommandType, dbBuilder.fragmentArguments, dbBuilder.smartAnalyzing).SetTransaction(dbBuilder.Transaction));
}
public List <T> ToList <T>(DBBuilder dbBuilder) where T : new()
{
return(ToList <T>(dbBuilder, null));
}
/// <summary>
/// 获得请求的命令
/// </summary>
/// <param name="dbBuilder"></param>
/// <returns></returns>
public static CommandDefinition GetCommand(this DBBuilder dbBuilder, IDBAdapter dbAdapter)
{
return(new CommandDefinition(dbBuilder.Compile(dbAdapter).Sql, dbBuilder.Parameters.Instance, dbBuilder.Transaction, null, dbBuilder.CommandType));
}
public object ToScalar(DBBuilder dbBuilder)
{
return(this.Do(dbBuilder, connection => connection.ExecuteScalar(dbBuilder.GetCommand(DBAdapter))));
}