/// <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 }