/// <summary>
/// 删除
/// </summary>
/// <param name="childTableName">从表名称如是多个从表，以逗号隔开</param>
/// <param name="primaryKeyName">主健字段名称</param>
/// <returns></returns>
public EntityResult Delete(string childTableName, string primaryKeyName)
{
List <string> sqllist = new List <string>();
if (childTableName.IndexOf(',') > 0)
{
string[] sqls = childTableName.Split(',');
for (int i = 0; i < sqls.Length; i++)
{
string childsql = "DELETE FROM " + sqls[i] + " WHERE " + primaryKeyName.ToUpper() + "='" + PrimaryKeyValue + "'";
sqllist.Add(childsql);
}
}
else
{
string childsql = "DELETE FROM " + childTableName + " WHERE " + primaryKeyName.ToUpper() + "='" + PrimaryKeyValue + "'";
sqllist.Add(childsql);
}
string mainsql = "DELETE FROM " + this.GetType().Name + " WHERE " + PrimaryKeyName.ToUpper() + "='" + PrimaryKeyValue + "'";
sqllist.Add(mainsql);
EntityResult result = GetEntityResult(SMT.DataProvider.ExecuteByTransaction(sqllist));
int n = result.RecordCount;
if (n > 0)
{
eb = null;
result.RecordCount = n;
}
else
{
result.RecordCount = 0;
}
return(result);
}
/// <summary>
/// Get the current query value bindings.
/// </summary>
/// <returns>A collection of columns and values.</returns>
private Dictionary <string, dynamic> PrepareBindings()
{
PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly);
Dictionary <string, dynamic> bindings = properties.Where(p => p.PropertyType.IsValueType || p.PropertyType == typeof(string)).ToDictionary(p => Attribute.IsDefined(p, typeof(ColumnAttribute)) ? p.GetCustomAttribute <ColumnAttribute>().Name : p.Name.ToSnakeCase(), p => p.GetValue(this));
bindings.Remove(PrimaryKeyName.ToSnakeCase());
return(bindings);
}
/// <summary>
/// 删除(通过事务,必须在BeginTransaction()和 CommitTransaction()二个方法中间)
/// </summary>
public EntityResult DeleteByTransaction(IDbCommand idbconn)
{
string sql = "DELETE FROM " + this.GetType().Name + " WHERE " + PrimaryKeyName.ToUpper() + "='" + PrimaryKeyValue + "'";
//SMT.DataProvider.ExecuteSQLByTransaction(idbconn, sql);
EntityResult result = GetEntityResult(SMT.DataProvider.ExecuteSQLByTransaction(idbconn, sql));
result.Sql = sql;
return(result);
}
public virtual TModel Update(TModel instance)
{
DbCommand cmd = db.GetSqlStringCommand("a");//随便赋一个，因为参数不能为空
string sql = "update " + TableName + " set ";
PropertyInfo[] Properties = instance.GetType().GetProperties();
var Fields = DbTableFields;
string temp = "";
foreach (var property in Properties)
{
if (property.Name.ToLower() == PrimaryKeyName.ToLower())
{
continue;
}
foreach (var obj in Fields)
{
//如果属性没有相应的数据库字段则不处理
if (obj.Key.Equals(property.Name, StringComparison.OrdinalIgnoreCase))
{
if (property.CanWrite && property.CanRead)
{
temp += "," + property.Name + "=@" + property.Name + "";
var pValue = property.GetValue(instance, null);// instance.GetValue(property);
if (pValue == null || !pValue.GetType().IsEnum)
{
db.AddInParameter(cmd, "@" + property.Name, instance.GetPropertyDbType(property), pValue);
}
else
{
db.AddInParameter(cmd, "@" + property.Name, DbType.Int32, Convert.ToInt32(pValue));
}
}
break;
}
}
}
if (temp != "")
{
temp = temp.Substring(1);
sql += temp;
sql += " where " + PrimaryKeyName + "=@" + PrimaryKeyName + "";
db.AddInParameter(cmd, "@" + PrimaryKeyName, typeof(TKey).ToDbType(), instance.GetValue(PrimaryKeyName) ?? DBNull.Value);
cmd.CommandText = sql;
db.ExecuteNonQuery(cmd);
}
//return (TModel)instance;
return(instance);
}
public async Task can_migrate_pk_index_name()
{
theTable.PrimaryKeyName = "pk_someothername";
writeTable();
var newTable = new DocumentTable(theMapping);
await writeAndApplyPatch(AutoCreate.CreateOrUpdate, newTable);
var theActual = await theTable.FetchExisting(_conn);
theActual !.PrimaryKeyName.ShouldBe($"pkey_{SchemaConstants.TablePrefix}{theMapping.Alias}_{theMapping.IdMember.Name.ToLower()}");
}
/// <summary>
/// 删除
/// </summary>
/// <returns></returns>
public EntityResult Delete()
{
string sql = "DELETE FROM " + this.GetType().Name + " WHERE " + PrimaryKeyName.ToUpper() + "='" + PrimaryKeyValue + "'";
EntityResult result = GetEntityResult(SMT.DataProvider.ExecuteSQL(sql));
int n = result.RecordCount;
if (n > 0)
{
eb = null;
result.RecordCount = n;
}
else
{
result.RecordCount = 0;
}
return(result);
}
public virtual FieldProperties GetFieldProperties(TableSchema schema, TableSchema.Field field)
{
var fieldProperties = new FieldProperties();
if (Regex.IsMatch(field.FieldName, IsFieldIndexedRegex, RegexOptions.IgnoreCase))
{
fieldProperties.Indexed = true;
}
string pkName = PrimaryKeyName.Replace(CLASS_NAME, schema.ObjectType.Name);
if (field.FieldName.Equals(pkName, StringComparison.OrdinalIgnoreCase))
{
fieldProperties.PrimaryKey = true;
fieldProperties.AutoIncrement = UseAutoIncrement && field.FieldInfo.Inspector().Type.Inspector().Is(TypeFlags.Integer);
fieldProperties.Indexed = false;
}
return(fieldProperties);
}
/// <summary>
/// 更改(通过事务,必须在BeginTransaction()和 CommitTransaction()二个方法中间)
/// </summary>
public EntityResult UpdateByTransaction(IDbCommand idbconn)
{
#region 参数化
eb = this;
string sql = "";
Type type = this.GetType();
string name = "";
if (has == null)
{
has = DataBaseType.GetTableColumn(this.GetType().Name);
}
SMT.DataProvider.CreateParameters(has.Count);
int i = 0;
foreach (PropertyInfo p in this.GetType().GetProperties())
{
//Console.WriteLine(p.Name + "=" + p.GetValue(eb, null));
//this.GetType().GetProperties()[3].Name
if (has.Contains(p.Name.ToUpper()))
{
if (PrimaryKeyName.ToUpper() != p.Name.ToUpper())
{
SMT.DataProvider.AddParameters(i, p.Name.ToUpper(), p.GetValue(eb, null));
sql += p.Name.ToUpper() + "='" + p.GetValue(eb, null) + "',";
name += p.Name.ToUpper() + "=:" + p.Name.ToUpper() + ",";
i++;
}
}
}
sql = "UPDATE " + this.GetType().Name + " SET " + sql.TrimEnd(',') + " WHERE " + PrimaryKeyName.ToUpper() + "='" + PrimaryKeyValue + "'";
SMT.DataProvider.AddParameters(i, PrimaryKeyName.ToUpper(), PrimaryKeyValue);
string exeSql = "UPDATE " + this.GetType().Name + " SET " + name.TrimEnd(',') + " WHERE " + PrimaryKeyName.ToUpper() + "=:" + PrimaryKeyName.ToUpper() + "";
EntityResult result = GetEntityResult(SMT.DataProvider.ExecuteSQLByTransaction(idbconn, exeSql));
result.Sql = sql;
return(result);
#endregion
}
/// <summary>
/// Asynchronously find a model by its primary key.
/// </summary>
/// <param name="id">The value of the primary key.</param>
/// <returns>An instance of the hydrated model.</returns>
public static async Task <T> FindAsync(int id)
{
return(await Query.Where(PrimaryKeyName.ToSnakeCase(), id).FirstAsync());
}
/// <summary>
/// Find a model by its primary key.
/// </summary>
/// <param name="id">The value of the primary key.</param>
/// <returns>An instance of the hydrated model.</returns>
public static T Find(int id)
{
return(Query.Where(PrimaryKeyName.ToSnakeCase(), id).First());
}
/// <summary>
/// Perform a model update operation asynchronously.
/// </summary>
private async Task PerformUpdateAsync()
{
await Query.Where(PrimaryKeyName.ToSnakeCase(), PrimaryKey).UpdateAsync(PrepareBindings());
}
/// <summary>
/// Perform a model update operation.
/// </summary>
private void PerformUpdate()
{
Query.Where(PrimaryKeyName.ToSnakeCase(), PrimaryKey).Update(PrepareBindings());
}
public virtual List <TModel> Update(List <TModel> instances)
{
DbCommand cmd = db.GetSqlStringCommand("a");//随便赋一个，因为参数不能为空
var sqlUpdate = string.Empty;
for (var i = 0; i < instances.Count(); i++)
{
var instance = instances.ElementAt(i);
string sql = "update " + TableName + " set ";
PropertyInfo[] Properties = instance.GetType().GetProperties();
var Fields = DbTableFields;
string temp = "";
foreach (var property in Properties)
{
if (property.Name.ToLower() == PrimaryKeyName.ToLower())
{
continue;
}
foreach (var obj in Fields)
{
//如果属性没有相应的数据库字段则不处理
if (obj.Key.Equals(property.Name, StringComparison.OrdinalIgnoreCase))
{
if (property.CanWrite && property.CanRead)
{
temp += "," + property.Name + "=@" + property.Name + "" + i + "";
var pValue = property.GetValue(instance, null); //instance.GetValue(property);
if (pValue == null || !pValue.GetType().IsEnum)
{
db.AddInParameter(cmd, "@" + property.Name + i, instance.GetPropertyDbType(property), pValue);
}
else
{
db.AddInParameter(cmd, "@" + property.Name + i, DbType.Int32, Convert.ToInt32(pValue));
}
}
break;
}
}
}
if (temp != "")
{
temp = temp.Substring(1);
sql += temp;
sql += " where " + PrimaryKeyName + "=@" + PrimaryKeyName + "" + i + "";
db.AddInParameter(cmd, "@" + PrimaryKeyName + i, typeof(TKey).ToDbType(), instance.GetValue(PrimaryKeyName) ?? DBNull.Value);
sqlUpdate += ";" + sql;
//cmd.CommandText = sql;
//db.ExecuteNonQuery(cmd);
}
}
if (!(string.IsNullOrEmpty(sqlUpdate) || string.IsNullOrEmpty(sqlUpdate)))
{
sqlUpdate = sqlUpdate.Substring(1);
cmd.CommandText = sqlUpdate;
db.ExecuteNonQuery(cmd);
}
//return instances.Select(item=>(TModel)item);
return(instances);
}
/// <summary>
/// 更新
/// </summary>
/// <returns></returns>
public EntityResult Update()
{
#region sql语句
//Result result = new Result();
//eb = this;
//string sql="";
//Type type=this.GetType();
//foreach (PropertyInfo p in this.GetType().GetProperties())
// {
// //Console.WriteLine(p.Name + "=" + p.GetValue(eb, null));
// //this.GetType().GetProperties()[3].Name
// if (has.Contains(p.Name.ToUpper()))
// {
// if (PrimaryKeyName.ToUpper() != p.Name.ToUpper())
// {
// sql += p.Name.ToUpper() + "='" + p.GetValue(eb, null) + "',";
// }
// }
// }
//string exeSql="UPDATE "+this.GetType().Name+" SET "+sql.TrimEnd(',')+" WHERE "+ PrimaryKeyName.ToUpper() + "='" + PrimaryKey + "'";
//result.Sql = exeSql;
//int n = SMT.DataProvider.ExecuteSQL(exeSql);
//if (n > 0)
//{
// result.RecordCount = n;
//}
//else
//{
// result.RecordCount = 0;
//}
//return result;
#endregion
#region 参数化
eb = this;
string sql = "";
Type type = this.GetType();
string name = "";
if (has == null)
{
has = DataBaseType.GetTableColumn(this.GetType().Name);
}
SMT.DataProvider.CreateParameters(has.Count);
int i = 0;
foreach (PropertyInfo p in this.GetType().GetProperties())
{
//Console.WriteLine(p.Name + "=" + p.GetValue(eb, null));
//this.GetType().GetProperties()[3].Name
if (has.Contains(p.Name.ToUpper()))
{
if (PrimaryKeyName.ToUpper() != p.Name.ToUpper())
{
SMT.DataProvider.AddParameters(i, p.Name.ToUpper(), p.GetValue(eb, null));
sql += p.Name.ToUpper() + "='" + p.GetValue(eb, null) + "',";
name += p.Name.ToUpper() + "=:" + p.Name.ToUpper() + ",";
i++;
}
}
}
sql = "UPDATE " + this.GetType().Name + " SET " + sql.TrimEnd(',') + " WHERE " + PrimaryKeyName.ToUpper() + "='" + PrimaryKeyValue + "'";
SMT.DataProvider.AddParameters(i, PrimaryKeyName.ToUpper(), PrimaryKeyValue);
string exeSql = "UPDATE " + this.GetType().Name + " SET " + name.TrimEnd(',') + " WHERE " + PrimaryKeyName.ToUpper() + "=:" + PrimaryKeyName.ToUpper() + "";
EntityResult result = GetEntityResult(SMT.DataProvider.ExecuteSQL(exeSql));
result.Sql = sql;
int n = result.RecordCount;
if (n > 0)
{
result.RecordCount = n;
}
else
{
result.RecordCount = 0;
}
return(result);
#endregion
}
