internal Builder(SqlType type, SqlTableDefine tableDefine, List <SqlColumnDefine> columnDefines, ISqlAdapter adapter, string specialSchema)
{
_paramIndex = 0;
this._adapter = adapter;
this._type = type;
this._useField = true;
var tabDef = tableDefine;
var tname = tabDef.TableAttribute?.Name;
if (string.IsNullOrEmpty(tname))
{
tname = tabDef.Name;
}
_tableNames.Add(tname);
_schema = tabDef.TableAttribute?.Schema;
if (!string.IsNullOrEmpty(specialSchema))
{
_schema = specialSchema;
}
_tableDefine = tableDefine;
_columnDefines = columnDefines;
this._parameterDic = new Dictionary <string, object>(); //new ExpandoObject();
}
public static int Insert(this IDbConnection db, SqlTableDefine tableDefine, List <SqlColumnDefine> columnDefines, IEnumerable <object> entity, IDbTransaction trans = null, int?commandTimeout = null)
{
var sqllam = new SqlExp <object>(tableDefine, columnDefines, db.GetAdapter());
sqllam = sqllam.Insert(tableDefine, columnDefines);
try
{
return(db.Execute(sqllam.SqlString, entity, trans, commandTimeout, CommandType.Text));
}
catch (Exception ex)
{
if (Debugger.IsAttached)
{
Debug.WriteLine(ex.Message + ex.StackTrace);
Debug.WriteLine(sqllam.SqlString);
}
Console.WriteLine(ex.Message + ex.StackTrace);
Console.WriteLine(sqllam.SqlString);
throw new DapperLamException(ex.Message, ex, sqllam.SqlString)
{
Parameters = sqllam.Parameters
};
}
}
public SqlExp <T> Insert(SqlTableDefine tableDefine, List <SqlColumnDefine> columnDefines, bool key = false)
{
_builder.UpdateSqlType(SqlType.Insert);
_resolver.ResolveInsert(key, tableDefine, columnDefines);
return(this);
}
public SqlExpBase(SqlAdapterType adater, SqlTableDefine tableDefine, List <SqlColumnDefine> columnDefines)
{
_type = SqlType.Query;
_adapter = adater;
_builder = new Builder.Builder(_type, tableDefine, columnDefines, AdapterFactory.GetAdapterInstance(_adapter));
_resolver = new LambdaResolver(_builder);
}
public static void CreateTable(this IDbConnection db, SqlTableDefine tableDefine, List <SqlColumnDefine> columnList, IDbTransaction transaction = null)
{
var dbAdapter = AdapterFactory.GetAdapterInstance(db.GetAdapter());
var createTableSql = dbAdapter.CreateTableSql(tableDefine, columnList);
db.Execute(createTableSql, transaction: transaction);
}
public SqlExp(SqlTableDefine tableDefine, List <SqlColumnDefine> columnDefines, SqlAdapterType type = SqlAdapterType.SqlServer, bool forCount = false, string specialSchema = null)
: base(type, tableDefine, columnDefines, specialSchema)
{
useForCount = forCount;
_type = SqlType.Query;
//GetAdapterInstance(type);
//_builder = new Builder(_type, LambdaResolver.GetTableName<T>(), _defaultAdapter);
//_resolver = new LambdaResolver(_builder);
}
public static Task CreateTableAsync(this IDbConnection db, SqlTableDefine tableDefine,
List <SqlColumnDefine> columnList, IDbTransaction transaction = null)
{
var dbAdapter = AdapterFactory.GetAdapterInstance(db.GetAdapter());
var createTableSql = dbAdapter.CreateTableSql(tableDefine, columnList);
return(db.ExecuteSqlAsync(transaction, createTableSql));
}
public static bool SchemaExist(this IDbConnection db, SqlTableDefine tableDefine, IDbTransaction transaction = null)
{
var tableSchema = "";
if (!string.IsNullOrEmpty(tableDefine.TableAttribute?.Name))
{
tableSchema = tableDefine.TableAttribute.Schema;
}
return(db.SchemaExist(tableSchema, transaction));
}
public static void CreateSchemaIfNotExists(this IDbConnection db, SqlTableDefine tableDefine, IDbTransaction transaction = null)
{
var tableSchema = string.Empty;
if (!string.IsNullOrEmpty(tableDefine.TableAttribute?.Name))
{
tableSchema = tableDefine.TableAttribute.Schema;
}
db.CreateSchemaIfNotExists(tableSchema, transaction);
}
public static void TruncateTable(this IDbConnection db, SqlTableDefine tableDefine, IDbTransaction transaction = null)
{
var tableSchema = string.Empty;
var tableName = tableDefine.Name;
if (!string.IsNullOrEmpty(tableDefine.TableAttribute?.Name))
{
tableName = tableDefine.TableAttribute.Name;
tableSchema = tableDefine.TableAttribute.Schema;
}
db.TruncateTable(tableName, tableSchema, transaction);
}
public static Task <bool> TableExistAsync(this IDbConnection db, SqlTableDefine tableDefine,
IDbTransaction transaction = null)
{
var tableName = tableDefine.Name;
var tableSchema = "";
if (!string.IsNullOrEmpty(tableDefine.TableAttribute?.Name))
{
tableName = tableDefine.TableAttribute.Name;
tableSchema = tableDefine.TableAttribute.Schema;
}
return(db.TableExistAsync(tableName, tableSchema, transaction));
}
public static Task CreateTableIfNotExistAsync(this IDbConnection db, SqlTableDefine tableDefine,
List <SqlColumnDefine> columnList, IDbTransaction transaction = null)
{
return(db.TableExistAsync(tableDefine, transaction).ContinueWith((async task =>
{
if (!task.Result)
{
var dbAdapter = AdapterFactory.GetAdapterInstance(db.GetAdapter());
var createTableSql = dbAdapter.CreateTableSql(tableDefine, columnList);
await db.ExecuteSqlAsync(transaction, createTableSql);
}
})));
}
public string GetTableName(SqlTableDefine tableDefine)
{
if (tableDefine.TableAttribute != null)
{
var tname = tableDefine.TableAttribute.Name;
if (string.IsNullOrEmpty(tname))
{
tname = tableDefine.Name;
}
return(_builder.Adapter.Table(tname, tableDefine.TableAttribute.Schema));
}
else
{
return(tableDefine.Name);
}
}
public static int Insert(this IDbConnection con, SqlTableDefine tableDefine, List <SqlColumnDefine> columnDefines, IEnumerable <object> entity, IDbTransaction trans = null, int?commandTimeout = null)
{
var db = trans == null ? con : trans.Connection;
var sqllam = new SqlExp <object>(tableDefine, columnDefines, db.GetAdapter());
sqllam = sqllam.Insert(tableDefine, columnDefines);
var sqlString = sqllam.SqlString;
try
{
DebuggingSqlString(sqlString);
return(db.Execute(sqlString, entity, trans, commandTimeout, CommandType.Text));
}
catch (Exception ex)
{
DebuggingException(ex, sqlString);
throw new DapperLamException(ex.Message, ex, sqlString)
{
Parameters = sqllam.Parameters
};
}
}
public static void CreateTable(this IDbConnection db, SqlTableDefine tableDefine, List <SqlColumnDefine> columnList, IDbTransaction transaction = null)
{
var dbAdapter = AdapterFactory.GetAdapterInstance(db.GetAdapter());
var createTableSql = dbAdapter.CreateTableSql(tableDefine, columnList);
DapperLambdaExt.DebuggingSqlString(createTableSql);
if (transaction == null)
{
var trans = db.BeginTransaction();
try
{
db.Execute(createTableSql, transaction: trans);
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
DapperLambdaExt.DebuggingException(ex, createTableSql);
throw new DapperLamException(ex.Message, ex, createTableSql);
}
}
else
{
try
{
db.Execute(createTableSql, transaction: transaction);
}
catch (Exception ex)
{
DapperLambdaExt.DebuggingException(ex, createTableSql);
throw new DapperLamException(ex.Message, ex, createTableSql);
}
}
}
public void ResolveInsertValues(bool key, SqlTableDefine tableDefine, List <SqlColumnDefine> columnDefines)
{
string tableName = GetTableName(tableDefine);
ResolveParameter(key, tableName, columnDefines);
}
public virtual string CreateTableSql(SqlTableDefine tableDefine, List <SqlColumnDefine> columnDefines)
{
var sql = CreateTablePrefix;
var tableName = tableDefine.Name;
var tempTableName = tableName;
var tempSchemaName = string.Empty;
if (tableDefine.TableAttribute != null)
{
if (!string.IsNullOrEmpty(tableDefine.TableAttribute.Name))
{
tempTableName = tableDefine.TableAttribute.Name;// _leftToken + tableDefine.TableAttribute.Name + _rightToken;
}
tempSchemaName = tableDefine.TableAttribute.Schema;
//if (!string.IsNullOrEmpty(tableDefine.TableAttribute.Schema))
//{
// tableName = (_leftToken + tableDefine.TableAttribute.Schema + _rightToken + ".") + tableName;
//}
}
tableName = Table(tempTableName, tempSchemaName);
sql += tableName;
sql += " (";
var indexList = new List <IndexStructure>();
foreach (var c in columnDefines)
{
var tempCname = (c.AliasName ?? c.Name);
var cname = _leftToken + tempCname + _rightToken;
// edit by cheery, 2017/2/22
// change the datatype method.
//var datatypestr = "varchar(255)";
//if (c.DataTypeAttribute != null)
//{
// datatypestr = c.DataTypeAttribute.DataType;
//}
//else
//{
// datatypestr = GetColumnDefinition(c.ValueType);
//}
var datatypestr = GetColumnDefinition(c);
var primary = "";
var increment = "";
bool isprimary = false;
bool isincrement = false;
if (c.KeyAttribute != null)
{
primary = PrimaryKeyDefinition;
isprimary = true;
if (c.KeyAttribute.Increment)
{
increment = AutoIncrementDefinition;
isincrement = true;
}
}
var nullStr = "";
if (!c.NullAble)
{
nullStr = "not null";
}
if (isprimary || isincrement)
{
nullStr = "not null";
}
if (c.IndexAttribute != null)
{
if (string.IsNullOrEmpty(c.IndexAttribute.IndexName))
{
var schemaSuffix = tempSchemaName;
if (!string.IsNullOrEmpty(tempSchemaName))
{
schemaSuffix += "_";
}
c.IndexAttribute.IndexName = "lidx_" + schemaSuffix + tempTableName + "_" + tempCname;
}
if (indexList.Exists(p => p.IndexName == c.IndexAttribute.IndexName))
{
var ind = indexList.FirstOrDefault(v => v.IndexName == c.IndexAttribute.IndexName);
//if (ind != null)
//{
ind?.Columns.Add(new IndexColumnStructure()
{
ColumnName = cname, Asc = c.IndexAttribute.Asc
});
//}
}
else
{
indexList.Add(new IndexStructure()
{
IndexName = c.IndexAttribute.IndexName,
Unique = c.IndexAttribute.Unique,
TableName = tableName,
Columns = new List <IndexColumnStructure>()
{
new IndexColumnStructure()
{
ColumnName = cname, Asc = c.IndexAttribute.Asc
}
}
});
}
//var str = string.Format(CreateIndexFormatter, uniqueStr, c.IndexAttribute.IndexName, tableName,cname);
}
var columnDefStr = FormatColumnDefineSql(cname, datatypestr, nullStr, primary, increment); //$" {cname} {datatypestr} {nullStr} {primary} {increment},");
sql += columnDefStr;
}
sql = sql.TrimEnd(',');
sql += " );";
//process index create sql
//var indexSb = new StringBuilder();
foreach (var indexItem in indexList)
{
var uniqueStr = string.Empty;
if (indexItem.Unique)
{
uniqueStr = "UNIQUE";
}
var columnlist = new List <string>();
var columnstr = string.Empty;
foreach (var column in indexItem.Columns)
{
//var tmpStr = column.ColumnName + " " + (column.Asc? "ASC" : "DESC")+",";
columnlist.Add(column.ColumnName + " " + (column.Asc ? "ASC" : "DESC"));
}
columnstr = string.Join(",", columnlist);
var str = string.Format(CreateIndexFormatter, uniqueStr, indexItem.IndexName, indexItem.TableName, columnstr);
//indexSb.AppendLine(str);
sql += str;
}
//sql += indexSb.ToString();
return(sql);
}
public virtual string CreateTableSql(SqlTableDefine tableDefine, List <SqlColumnDefine> columnDefines)
{
var sql = CreateTablePrefix;
var tableName = tableDefine.Name;
if (tableDefine.TableAttribute != null)
{
if (!string.IsNullOrEmpty(tableDefine.TableAttribute.Name))
{
tableName = tableDefine.TableAttribute.Name;// _leftToken + tableDefine.TableAttribute.Name + _rightToken;
}
//if (!string.IsNullOrEmpty(tableDefine.TableAttribute.Schema))
//{
// tableName = (_leftToken + tableDefine.TableAttribute.Schema + _rightToken + ".") + tableName;
//}
tableName = Table(tableName, tableDefine.TableAttribute.Schema);
}
sql += tableName;
sql += " (";
foreach (var c in columnDefines)
{
var cname = _leftToken + (c.AliasName ?? c.Name) + _rightToken;
// edit by cheery, 2017/2/22
// change the datatype method.
//var datatypestr = "varchar(255)";
//if (c.DataTypeAttribute != null)
//{
// datatypestr = c.DataTypeAttribute.DataType;
//}
//else
//{
// datatypestr = GetColumnDefinition(c.ValueType);
//}
var datatypestr = GetColumnDefinition(c);
var primary = "";
var increment = "";
bool isprimary = false;
bool isincrement = false;
if (c.KeyAttribute != null)
{
primary = PrimaryKeyDefinition;
isprimary = true;
if (c.KeyAttribute.Increment)
{
increment = AutoIncrementDefinition;
isincrement = true;
}
}
var nullStr = "";
if (!c.NullAble)
{
nullStr = "not null";
}
if (isprimary || isincrement)
{
nullStr = "not null";
}
var columnDefStr = FormatColumnDefineSql(cname, datatypestr, nullStr, primary, increment); //$" {cname} {datatypestr} {nullStr} {primary} {increment},");
sql += columnDefStr;
}
sql = sql.TrimEnd(',');
sql += " );";
return(sql);
}
private static Tuple <SqlTableDefine, List <SqlColumnDefine> > GetEntityDefine(Type type)
{
//处理表定义
var name = type.Name;
DBTableAttribute tableAttr = new DBTableAttribute("");
if (EnvHelper.IsNetFX)
{
#if NETCOREAPP1_0 || NETSTANDARD1_6
#else
tableAttr = type.GetCustomAttribute <DBTableAttribute>();
#endif
}
else
{
tableAttr = type.GetTypeInfo().GetCustomAttribute <DBTableAttribute>();
}
var sqlTableDef = new SqlTableDefine(tableAttr, name);
//处理列定义
var colDeflist = new List <SqlColumnDefine>();
var columns = type.GetProperties();
foreach (var cp in columns)
{
var ignore = cp.GetCustomAttribute <DBIgnoreAttribute>();
if (ignore == null)
{
var keyAttr = cp.GetCustomAttribute <DBKeyAttribute>();
var columnAttr = cp.GetCustomAttribute <DBColumnAttribute>();
var dataTypeAttr = cp.GetCustomAttribute <DBCustomeDataTypeAttribute>();
var indexAttr = cp.GetCustomAttribute <DBIndexAttribute>();
var cname = cp.Name;
var alias = cname;
if (columnAttr != null)
{
alias = columnAttr.Name;
}
// edit by cheery 2017-2-21
var nullable = true;
// 如果是Key 不允许空
if (keyAttr != null)
{
nullable = false;
}
// 如果字段定义上有是否允许空标记 则依赖该标记
else if (columnAttr?.Nullable != null)
{
nullable = columnAttr.Nullable.Value;
}
// 否则 根据类型判断
else
{
nullable = cp.PropertyType.IsNullableType();
}
//var nullable = keyAttr == null && (columnAttr?.Nullable ?? cp.PropertyType.IsNullableType());
var cd = new SqlColumnDefine(cname, alias, null, cp.PropertyType, nullable, columnAttr, keyAttr, dataTypeAttr, null, indexAttr);
colDeflist.Add(cd);
}
}
return(new Tuple <SqlTableDefine, List <SqlColumnDefine> >(sqlTableDef, colDeflist));
}
public override string CreateTableSql(SqlTableDefine tableDefine, List <SqlColumnDefine> columnDefines)
{
var tableName = tableDefine.Name;
var tempTableName = tableName;
var tempSchemaName = string.Empty;
if (tableDefine.TableAttribute != null)
{
if (!string.IsNullOrEmpty(tableDefine.TableAttribute.Name))
{
tempTableName = tableDefine.TableAttribute.Name;// _leftToken + tableDefine.TableAttribute.Name + _rightToken;
}
tempSchemaName = tableDefine.TableAttribute.Schema;
if (string.IsNullOrEmpty(tempSchemaName))
{
tempSchemaName = "dbo";
}
}
tableName = Table(tempTableName, tempSchemaName);
var sql = $"IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES where table_schema='{tempSchemaName}' and table_name='{tempTableName}') BEGIN ";
sql += CreateTablePrefix;
sql += tableName;
sql += " (";
var indexList = new List <IndexStructure>();
foreach (var c in columnDefines)
{
var tempCname = (c.AliasName ?? c.Name);
var cname = _leftToken + tempCname + _rightToken;
var datatypestr = GetColumnDefinition(c);
var primary = "";
var increment = "";
bool isprimary = false;
bool isincrement = false;
if (c.KeyAttribute != null)
{
primary = PrimaryKeyDefinition;
isprimary = true;
if (c.KeyAttribute.Increment)
{
increment = AutoIncrementDefinition;
isincrement = true;
}
}
var nullStr = "";
if (!c.NullAble)
{
nullStr = "not null";
}
if (isprimary || isincrement)
{
nullStr = "not null";
}
if (c.IndexAttribute != null)
{
if (string.IsNullOrEmpty(c.IndexAttribute.IndexName))
{
var schemaSuffix = tempSchemaName;
if (!string.IsNullOrEmpty(tempSchemaName))
{
schemaSuffix += "_";
}
c.IndexAttribute.IndexName = "lidx_" + schemaSuffix + tempTableName + "_" + tempCname;
}
if (indexList.Exists(p => p.IndexName == c.IndexAttribute.IndexName))
{
var ind = indexList.FirstOrDefault(v => v.IndexName == c.IndexAttribute.IndexName);
//if (ind != null)
//{
ind?.Columns.Add(new IndexColumnStructure()
{
ColumnName = cname, Asc = c.IndexAttribute.Asc
});
//}
}
else
{
indexList.Add(new IndexStructure()
{
IndexName = c.IndexAttribute.IndexName,
Unique = c.IndexAttribute.Unique,
TableName = tableName,
Columns = new List <IndexColumnStructure>()
{
new IndexColumnStructure()
{
ColumnName = cname, Asc = c.IndexAttribute.Asc
}
}
});
}
//var str = string.Format(CreateIndexFormatter, uniqueStr, c.IndexAttribute.IndexName, tableName,cname);
}
var columnDefStr = FormatColumnDefineSql(cname, datatypestr, nullStr, primary, increment); //$" {cname} {datatypestr} {nullStr} {primary} {increment},");
sql += columnDefStr;
}
sql = sql.TrimEnd(',');
sql += " );";
//process index create sql
//var indexSb = new StringBuilder();
foreach (var indexItem in indexList)
{
var uniqueStr = string.Empty;
if (indexItem.Unique)
{
uniqueStr = "UNIQUE";
}
var columnlist = new List <string>();
var columnstr = string.Empty;
foreach (var column in indexItem.Columns)
{
//var tmpStr = column.ColumnName + " " + (column.Asc? "ASC" : "DESC")+",";
columnlist.Add(column.ColumnName + " " + (column.Asc ? "ASC" : "DESC"));
}
columnstr = string.Join(",", columnlist);
var str = string.Format(CreateIndexFormatter, uniqueStr, indexItem.IndexName, indexItem.TableName, columnstr);
//indexSb.AppendLine(str);
sql += str;
}
//sql += indexSb.ToString();
sql += " END;";
return(sql);
}