public IDbProperty GetPrimaryProperty()
{
var clsName = this.Name;
if (clsName.EndsWith("Entity"))
{
clsName = clsName.Substring(0, clsName.Length - "Entity".Length);
}
else if (clsName.EndsWith("Class"))
{
clsName = clsName.Substring(0, clsName.Length - "Class".Length);
}
var tbName = this.GetTablename();
var idName1 = clsName.ToLower() + "id";
var idName2 = clsName.ToLower() + "id";
IDbProperty idprop = null;
foreach (var prop in this.FieldedProps)
{
var n = prop.Key.ToLower();
if (n == "id")
{
idprop = prop.Value; break;
}
if (n == idName1 || n == idName2 && idprop == null)
{
idprop = prop.Value;
}
}
return(idprop);
}
public virtual string GetSqlFieldType(IDbProperty prop)
{
var hash = prop.NonullableType.GetHashCode();
if (typeof(byte).GetHashCode() == hash)
{
return("TINYINT");
}
if (typeof(short).GetHashCode() == hash)
{
return("MEDIUMINT");
}
if (typeof(ushort).GetHashCode() == hash)
{
return("MEDIUMINT");
}
if (typeof(int).GetHashCode() == hash)
{
return("INT");
}
if (typeof(uint).GetHashCode() == hash)
{
return("INT");
}
if (typeof(long).GetHashCode() == hash)
{
return("BIGINT");
}
if (typeof(ulong).GetHashCode() == hash)
{
return("UNSIGNED BIG INT");
}
if (typeof(float).GetHashCode() == hash)
{
return("FLOAT");
}
if (typeof(double).GetHashCode() == hash)
{
return("DOUBLE");
}
if (typeof(bool).GetHashCode() == hash)
{
return("INT");
}
if (typeof(decimal).GetHashCode() == hash)
{
return("DECIMAL");
}
if (typeof(Guid).GetHashCode() == hash)
{
return("CHAR");
}
if (typeof(DateTime).GetHashCode() == hash)
{
return("DATETIME");
}
return("TEXT");
}
void GenParam(string fname, IDbProperty prop, Expression dataExpr, Expression cmdExpr, List <Expression> codes, List <ParameterExpression> locals)
{
var paramExpr = Expression.Parameter(typeof(DbParameter), fname);
locals.Add(paramExpr);
codes.Add(Expression.Assign(paramExpr, Expression.Call(cmdExpr, CreateParameterMethodInfo)));
codes.Add(Expression.Assign(Expression.PropertyOrField(paramExpr, "ParameterName"), Expression.Constant("@" + fname)));
DbType dbType = prop.Field.DbType;
codes.Add(Expression.Assign(Expression.PropertyOrField(paramExpr, "DbType"), Expression.Constant(dbType)));
Expression valueExpr = Expression.PropertyOrField(dataExpr, prop.Name);
if (prop.Field.Nullable)
{
if (prop.Nullable)
{
valueExpr = Expression.Condition(
Expression.PropertyOrField(valueExpr, "HasValue")
, Expression.Convert(Expression.PropertyOrField(valueExpr, "Value"), typeof(object))
, Expression.Convert(Expression.Constant(DBNull.Value), typeof(object))
);
}
else if (prop.PropertyType == typeof(string))
{
valueExpr = Expression.Condition(
Expression.Equal(valueExpr, Expression.Constant(null, typeof(string)))
, Expression.Convert(Expression.Constant(DBNull.Value), typeof(object))
, Expression.Convert(valueExpr, typeof(object))
);
}
}
else
{
if (prop.Nullable)
{
valueExpr = Expression.Condition(
Expression.PropertyOrField(valueExpr, "HasValue")
, Expression.Convert(Expression.PropertyOrField(valueExpr, "Value"), typeof(object))
, Expression.Convert(Expression.Constant(prop.DefaultValue), typeof(object))
);
}
else if (prop.PropertyType == typeof(string))
{
valueExpr = Expression.Condition(
Expression.Equal(valueExpr, Expression.Constant(null, typeof(string)))
, Expression.Constant(string.Empty)
, valueExpr
);
}
}
codes.Add(Expression.Assign(Expression.PropertyOrField(paramExpr, "Value"), Expression.Convert(valueExpr, typeof(object))));
codes.Add(Expression.Call(Expression.Property(cmdExpr, "Parameters"), AddParameterMethodInfo, paramExpr));
//DbParameter par;
//par.Value
}
public SQL(string membersString, SQLBag <T> sqlBag)
{
this.SqlBag = sqlBag;
this.Database = sqlBag.Database;
this.DbSettings = this.Database.Settings;
this.DbTrait = this.Database.Trait;
this.DbClass = sqlBag.DbClass;
this.FieldedProps = this.DbClass.FieldedProps;
this.MembersString = membersString ?? string.Empty;
if (string.IsNullOrEmpty(this.MembersString) || this.MembersString == "*")
{
this._AllowedProps = this.DbClass.FieldedProps;
this.MembersString = "*";
}
else
{
var memberNames = MembersString.Split(',');
var props = new Dictionary <string, IDbProperty>();
_AllowedProps = props;
foreach (var mName in memberNames)
{
var memName = mName.Trim();
IDbProperty prop = null;
if (this.DbClass.FieldedProps.TryGetValue(memName, out prop))
{
props.Add(prop.Name, prop);
}
}
if (!props.ContainsKey(this.DbClass.PrimaryProperty.Name))
{
props.Add(this.DbClass.PrimaryProperty.Name, this.DbClass.PrimaryProperty);
}
}
this.Create = new Create <T>(this);
this.Insert = new Insert <T>(this);
this.Select = new Select <T>(this);
this.Count = new Count <T>(this);
this.Get = new Get <T>(this);
this.GetById = new GetById <T>(this);
this.Update = new Update <T>(this);
this.Save = new Save <T>(this);
this.Delete = new Delete <T>(this);
this.DeleteById = new DeleteById <T>(this);
//this.Select = new Select(model, membersString);
}
public void BuildParameters(DbCommand cmd, T data, IDbProperty constProp = null)
{
if (this.DbTrait.ParametricKind == SqlParametricKinds.Value)
{
return;
}
if (_ParametersBuilder == null)
{
lock (this)
{
if (_ParametersBuilder == null)
{
_ParametersBuilder = GenParametersBuilder(constProp);
}
}
}
_ParametersBuilder(data, cmd);
}
public virtual string GetSqlPrecision(IDbProperty prop)
{
var field = prop.Field;
if (field.DbType == DbType.Guid)
{
return("64");
}
if (field.DbType == DbType.String || field.DbType == DbType.StringFixedLength || field.DbType == DbType.AnsiString || field.DbType == DbType.AnsiStringFixedLength)
{
return((field.Length ?? 256).ToString());
}
if (field.DbType == DbType.Currency || field.DbType == DbType.Decimal)
{
return((field.Length ?? 48).ToString() + "," + (field.Precision ?? 8).ToString());
}
return(null);
}
Action <T, DbCommand> GenParametersBuilder(IDbProperty constProp)
{
ParameterExpression cmdExpr = Expression.Parameter(typeof(DbCommand), "cmd");
ParameterExpression dataExpr = Expression.Parameter(typeof(T), "data");
List <Expression> codes = new List <Expression>();
List <ParameterExpression> locals = new List <ParameterExpression>();
foreach (var pair in this.AllowedProps)
{
var prop = pair.Value;
GenParam(prop.Field.Name, prop, dataExpr, cmdExpr, codes, locals);
}
if (constProp != null && !this.AllowedProps.ContainsKey(constProp.Name))
{
GenParam(constProp.Field.Name, constProp, dataExpr, cmdExpr, codes, locals);
}
var block = Expression.Block(locals, codes);
var lamda = Expression.Lambda <Action <T, DbCommand> >(block, dataExpr, cmdExpr);
return(lamda.Compile());
}
public SQL(string membersString, Database db, IDbClass dbClass)
{
this.Database = db;
this.DbSettings = db.Settings;
this.DbTrait = db.Trait;
this.DbClass = dbClass;
this.FieldedProps = this.DbClass.FieldedProps;
this.MembersString = membersString ?? string.Empty;
if (string.IsNullOrEmpty(this.MembersString) || this.MembersString == "*")
{
this._AllowedProps = this.DbClass.FieldedProps;
this.MembersString = "*";
}
else
{
var memberNames = MembersString.Split(',');
var props = new Dictionary <string, IDbProperty>();
_AllowedProps = props;
foreach (var mName in memberNames)
{
var memName = mName.Trim();
IDbProperty prop = null;
if (this.DbClass.FieldedProps.TryGetValue(memName, out prop))
{
props.Add(prop.Field.Name, prop);
}
}
}
this.Create = new Create <T>(this);
this.Insert = new Insert <T>(this);
//this.Update = new Update(model, membersString);
//this.Select = new Select(model, membersString);
}
protected string GenSaveSql(T data, Expression <Func <T, bool> > exp, DbCommand cmd, IDbProperty idfn)
{
var sql = $"UPDATE {this.Sql.Tablename(true)} SET ";
var hasField = false;
//var idfn = this.GetIdField();
foreach (var prop in this.Sql.AllowedProps.Values)
{
if (hasField)
{
sql += " , ";
}
else
{
hasField = true;
}
if (prop.Field.Name == idfn.Field.Name)
{
continue;
}
sql += this.Sql.DbTrait.SqlFieldname(prop.Field.Name) + "=";
switch (this.Sql.DbTrait.ParametricKind)
{
case SqlParametricKinds.At:
sql += "@" + prop.Field.Name; break;
case SqlParametricKinds.Question:
sql += "?"; break;
default:
sql += SQL <T> .SqlValue(prop.GetValue(data), prop.Field.Nullable, prop.DefaultValue);
break;
}
}
sql += " WHERE " + this.Sql.DbTrait.SqlFieldname(idfn.Field.Name) + "=";
switch (this.Sql.DbTrait.ParametricKind)
{
case SqlParametricKinds.At:
sql += "@" + idfn; break;
case SqlParametricKinds.Question:
sql += "?"; break;
default:
sql += SQL <T> .SqlValue(idfn.GetValue(data), false, idfn.DefaultValue);
break;
}
if (exp != null)
{
var where = base.SqlWhere(exp, cmd, new WhereOpts()
{
});
sql += " AND " + where;
}
return(sql);
}
protected string GetSaveSql(T data, Expression <Func <T, bool> > exp, DbCommand cmd, IDbProperty primaryProperty)
{
if (this.Sql.DbTrait.ParametricKind == SqlParametricKinds.Value || exp != null)
{
return(this.GenSaveSql(data, exp, cmd, primaryProperty));
}
if (_SaveSql == null)
{
lock (this) {
if (_SaveSql == null)
{
_SaveSql = GenSaveSql(data, null, cmd, primaryProperty);
}
}
}
return(_SaveSql);
}