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)))); }