ServiceStack.OrmLite - slevenqin/blog.github.io GitHub Wiki
- 已实现的InsertAll 8秒多
- 扩展120多秒
- 原生手写insert sql 80多秒
// // ServiceStack.OrmLite: Light-weight POCO ORM for .NET and Mono // // Authors: // Demis Bellot ([email protected]) // // Copyright 2013 ServiceStack, Inc. All Rights Reserved. // // Licensed under the same terms of ServiceStack. //
using System; using System.Collections.Generic; using System.Data; using System.Linq.Expressions; using System.Threading; using System.Threading.Tasks; using ServiceStack.OrmLite.Converters; using ServiceStack.Text;
namespace ServiceStack.OrmLite { public interface IOrmLiteDialectProvider { void RegisterConverter(IOrmLiteConverter converter);
/// <summary>
/// Invoked when a DB Connection is opened
/// </summary>
Action<IDbConnection> OnOpenConnection { get; set; }
IOrmLiteExecFilter ExecFilter { get; set; }
/// <summary>
/// Gets the explicit Converter registered for a specific type
/// </summary>
IOrmLiteConverter GetConverter(Type type);
/// <summary>
/// Return best matching converter, falling back to Enum, Value or Ref Type Converters
/// </summary>
IOrmLiteConverter GetConverterBestMatch(Type type);
IOrmLiteConverter GetConverterBestMatch(FieldDefinition fieldDef);
string ParamString { get; set; }
string EscapeWildcards(string value);
INamingStrategy NamingStrategy { get; set; }
IStringSerializer StringSerializer { get; set; }
Func<string, string> ParamNameFilter { get; set; }
Dictionary<string, string> Variables { get; }
/// <summary>
/// Quote the string so that it can be used inside an SQL-expression
/// Escape quotes inside the string
/// </summary>
/// <param name="paramValue"></param>
/// <returns></returns>
string GetQuotedValue(string paramValue);
string GetQuotedValue(object value, Type fieldType);
string GetDefaultValue(Type tableType, string fieldName);
string GetDefaultValue(FieldDefinition fieldDef);
bool HasInsertReturnValues(ModelDefinition modelDef);
object GetParamValue(object value, Type fieldType);
object ToDbValue(object value, Type type);
object FromDbValue(object value, Type type);
object GetValue(IDataReader reader, int columnIndex, Type type);
int GetValues(IDataReader reader, object[] values);
IDbConnection CreateConnection(string filePath, Dictionary<string, string> options);
string GetTableName(ModelDefinition modelDef);
string GetTableName(string tableName, string schema = null);
string GetQuotedTableName(ModelDefinition modelDef);
string GetQuotedTableName(string tableName, string schema=null);
string GetQuotedColumnName(string columnName);
string GetQuotedName(string columnName);
string SanitizeFieldNameForParamName(string fieldName);
string GetColumnDefinition(FieldDefinition fieldDef);
long GetLastInsertId(IDbCommand command);
string GetLastInsertIdSqlSuffix<T>();
string ToSelectStatement(Type tableType, string sqlFilter, params object[] filterParams);
string ToSelectStatement(ModelDefinition modelDef, string selectExpression, string bodyExpression, string orderByExpression = null, int? offset = null, int? rows = null);
string ToInsertRowStatement(IDbCommand cmd, object objWithProperties, ICollection<string> insertFields = null);
void PrepareParameterizedInsertStatement<T>(IDbCommand cmd, ICollection<string> insertFields = null);
/// <returns>If had RowVersion</returns>
bool PrepareParameterizedUpdateStatement<T>(IDbCommand cmd, ICollection<string> updateFields = null);
/// <returns>If had RowVersion</returns>
bool PrepareParameterizedDeleteStatement<T>(IDbCommand cmd, IDictionary<string, object> deleteFieldValues);
void PrepareStoredProcedureStatement<T>(IDbCommand cmd, T obj);
void SetParameterValues<T>(IDbCommand dbCmd, object obj);
void SetParameter(FieldDefinition fieldDef, IDbDataParameter p);
void SetParameterValue<T>(FieldDefinition fieldDef, IDataParameter p, object obj);
Dictionary<string, FieldDefinition> GetFieldDefinitionMap(ModelDefinition modelDef);
object GetFieldValue(FieldDefinition fieldDef, object value);
object GetFieldValue(Type fieldType, object value);
void PrepareUpdateRowStatement(IDbCommand dbCmd, object objWithProperties, ICollection<string> updateFields = null);
void PrepareUpdateRowStatement<T>(IDbCommand dbCmd, Dictionary<string, object> args, string sqlFilter);
void PrepareUpdateRowAddStatement<T>(IDbCommand dbCmd, Dictionary<string, object> args, string sqlFilter);
void PrepareInsertRowStatement<T>(IDbCommand dbCmd, Dictionary<string, object> args);
string ToDeleteStatement(Type tableType, string sqlFilter, params object[] filterParams);
IDbCommand CreateParameterizedDeleteStatement(IDbConnection connection, object objWithProperties);
string ToExistStatement(Type fromTableType,
object objWithProperties,
string sqlFilter,
params object[] filterParams);
string ToSelectFromProcedureStatement(object fromObjWithProperties,
Type outputModelType,
string sqlFilter,
params object[] filterParams);
string ToExecuteProcedureStatement(object objWithProperties);
string ToCreateTableStatement(Type tableType);
string ToPostCreateTableStatement(ModelDefinition modelDef);
string ToPostDropTableStatement(ModelDefinition modelDef);
List<string> ToCreateIndexStatements(Type tableType);
List<string> ToCreateSequenceStatements(Type tableType);
string ToCreateSequenceStatement(Type tableType, string sequenceName);
List<string> SequenceList(Type tableType);
bool DoesTableExist(IDbConnection db, string tableName, string schema = null);
bool DoesTableExist(IDbCommand dbCmd, string tableName, string schema = null);
bool DoesColumnExist(IDbConnection db, string columnName, string tableName, string schema = null);
bool DoesSequenceExist(IDbCommand dbCmd, string sequenceName);
void DropColumn(IDbConnection db, Type modelType, string columnName);
object FromDbRowVersion(Type fieldType, object value);
SelectItem GetRowVersionSelectColumn(FieldDefinition field, string tablePrefix = null);
string GetRowVersionColumn(FieldDefinition field, string tablePrefix = null);
string GetColumnNames(ModelDefinition modelDef);
SelectItem[] GetColumnNames(ModelDefinition modelDef, string tablePrefix);
SqlExpression<T> SqlExpression<T>();
IDbDataParameter CreateParam();
//DDL
string GetDropForeignKeyConstraints(ModelDefinition modelDef);
string ToAddColumnStatement(Type modelType, FieldDefinition fieldDef);
string ToAlterColumnStatement(Type modelType, FieldDefinition fieldDef);
string ToChangeColumnNameStatement(Type modelType, FieldDefinition fieldDef, string oldColumnName);
string ToAddForeignKeyStatement<T, TForeign>(Expression<Func<T, object>> field,
Expression<Func<TForeign, object>> foreignField,
OnFkOption onUpdate,
OnFkOption onDelete,
string foreignKeyName = null);
string ToCreateIndexStatement<T>(Expression<Func<T, object>> field,
string indexName = null, bool unique = false);
//Async
Task OpenAsync(IDbConnection db, CancellationToken token = default(CancellationToken));
Task<IDataReader> ExecuteReaderAsync(IDbCommand cmd, CancellationToken token = default(CancellationToken));
Task<int> ExecuteNonQueryAsync(IDbCommand cmd, CancellationToken token = default(CancellationToken));
Task<object> ExecuteScalarAsync(IDbCommand cmd, CancellationToken token = default(CancellationToken));
Task<bool> ReadAsync(IDataReader reader, CancellationToken token = default(CancellationToken));
Task<List<T>> ReaderEach<T>(IDataReader reader, Func<T> fn, CancellationToken token = default(CancellationToken));
Task<Return> ReaderEach<Return>(IDataReader reader, Action fn, Return source, CancellationToken token = default(CancellationToken));
Task<T> ReaderRead<T>(IDataReader reader, Func<T> fn, CancellationToken token = default(CancellationToken));
Task<long> InsertAndGetLastInsertIdAsync<T>(IDbCommand dbCmd, CancellationToken token);
string GetLoadChildrenSubSelect<From>(SqlExpression<From> expr);
string ToRowCountStatement(string innerSql);
string ToUpdateStatement<T>(IDbCommand dbCmd, T item, ICollection<string> updateFields = null);
string ToInsertStatement<T>(IDbCommand dbCmd, T item, ICollection<string> insertFields = null);
string MergeParamsIntoSql(string sql, IEnumerable<IDbDataParameter> dbParams);
string SqlConflict(string sql, string conflictResolution);
string SqlConcat(IEnumerable<object> args);
string SqlCurrency(string fieldOrValue);
string SqlCurrency(string fieldOrValue, string currencySymbol);
string SqlBool(bool value);
string SqlLimit(int? offset = null, int? rows = null);
string SqlCast(object fieldOrValue, string castAs);
}
}
[Test]
public void Can_insert_into_InsertMultiValues_ModelWithFieldsOfDifferentTypes_table()
{
var len = 10000;
using (var db = OpenDbConnection())
{
db.CreateTable(true);
ModelWithFieldsOfDifferentTypes[] objs = new ModelWithFieldsOfDifferentTypes[len];
for (int i = 0; i < len; i++)
{
objs[i] = ModelWithFieldsOfDifferentTypes.Create(i);
}
var stopWatch = new Stopwatch();
stopWatch.Start();
// var loop = len + 1;
// string execSql = @"
//insert into modelwithfieldsofdifferenttypes(Id, Name, Int, Long, Guid, Bool, DateTime, TimeSpan, Double, Decimal) values ";
// Dictionary<string, object> dictionaryParam = new Dictionary<string, object>();
// for (int i = 0; i < len; i++)
// {
// execSql +=
// stopWatch.Stop();
// var mm = stopWatch.ElapsedMilliseconds;
// stopWatch.Start();
// db.ExecuteNonQuery(execSql, dictionaryParam);
//db.InsertMultiValues(objs);
db.InsertAll(objs);
stopWatch.Stop();
var millionSecond = stopWatch.ElapsedMilliseconds;
}
}
using System; using System.Collections; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text; using ServiceStack.Data; using ServiceStack.Text;
namespace ServiceStack.OrmLite { public static class OrmLiteWriteApi { ///
public static string GetLastSqlAndParams(this IDbCommand dbCmd)
{
var sb = StringBuilderCache.Allocate();
sb.AppendLine(dbCmd.CommandText)
.AppendLine("PARAMS: ");
foreach (IDataParameter parameter in dbCmd.Parameters)
{
sb.Append(parameter.ParameterName).Append(": ")
.Append(parameter.Value.ToJsv())
.Append(" : ").AppendLine((parameter.Value ?? DBNull.Value).GetType().Name);
}
sb.AppendLine();
return StringBuilderCache.ReturnAndFree(sb);
}
/// <summary>
/// Execute any arbitrary raw SQL.
/// </summary>
/// <returns>number of rows affected</returns>
public static int ExecuteSql(this IDbConnection dbConn, string sql)
{
return dbConn.Exec(dbCmd => dbCmd.ExecuteSql(sql));
}
/// <summary>
/// Execute any arbitrary raw SQL with db params.
/// </summary>
/// <returns>number of rows affected</returns>
public static int ExecuteSql(this IDbConnection dbConn, string sql, object dbParams)
{
return dbConn.Exec(dbCmd => dbCmd.ExecuteSql(sql, dbParams));
}
/// <summary>
/// Execute any arbitrary raw SQL with db params.
/// </summary>
/// <returns>number of rows affected</returns>
public static int ExecuteSql(this IDbConnection dbConn, string sql, Dictionary<string,object> dbParams)
{
return dbConn.Exec(dbCmd => dbCmd.ExecuteSql(sql, dbParams));
}
/// <summary>
/// Insert 1 POCO, use selectIdentity to retrieve the last insert AutoIncrement id (if any). E.g:
/// <para>var id = db.Insert(new Person { Id = 1, FirstName = "Jimi }, selectIdentity:true)</para>
/// </summary>
public static long Insert<T>(this IDbConnection dbConn, T obj, bool selectIdentity = false)
{
return dbConn.Exec(dbCmd => dbCmd.Insert(obj, commandFilter: null, selectIdentity: selectIdentity));
}
/// <summary>
/// Insert 1 POCO and modify populated IDbCommand with a commandFilter. E.g:
/// <para>var id = db.Insert(new Person { Id = 1, FirstName = "Jimi }, dbCmd => applyFilter(dbCmd))</para>
/// </summary>
public static long Insert<T>(this IDbConnection dbConn, T obj, Action<IDbCommand> commandFilter, bool selectIdentity = false)
{
return dbConn.Exec(dbCmd => dbCmd.Insert(obj, commandFilter: commandFilter, selectIdentity: selectIdentity));
}
/// <summary>
/// Insert 1 or more POCOs in a transaction using Table default values when defined. E.g:
/// <para>db.InsertUsingDefaults(new Person { FirstName = "Tupac", LastName = "Shakur" },</para>
/// <para> new Person { FirstName = "Biggie", LastName = "Smalls" })</para>
/// </summary>
public static void InsertUsingDefaults<T>(this IDbConnection dbConn, params T[] objs)
{
dbConn.Exec(dbCmd => dbCmd.InsertUsingDefaults(objs));
}
/// <summary>
/// Insert results from SELECT SqlExpression, use selectIdentity to retrieve the last insert AutoIncrement id (if any). E.g:
/// <para>db.InsertIntoSelect<Contact>(db.From<Person>().Select(x => new { x.Id, Surname == x.LastName }))</para>
/// </summary>
public static long InsertIntoSelect<T>(this IDbConnection dbConn, ISqlExpression query)
{
return dbConn.Exec(dbCmd => dbCmd.InsertIntoSelect<T>(query, commandFilter: null));
}
/// <summary>
/// Insert results from SELECT SqlExpression, use selectIdentity to retrieve the last insert AutoIncrement id (if any). E.g:
/// <para>db.InsertIntoSelect<Contact>(db.From<Person>().Select(x => new { x.Id, Surname == x.LastName }))</para>
/// </summary>
public static long InsertIntoSelect<T>(this IDbConnection dbConn, ISqlExpression query, Action<IDbCommand> commandFilter)
{
return dbConn.Exec(dbCmd => dbCmd.InsertIntoSelect<T>(query, commandFilter: commandFilter));
}
/// <summary>
/// Insert a collection of POCOs in a transaction. E.g:
/// <para>db.InsertAll(new[] { new Person { Id = 9, FirstName = "Biggie", LastName = "Smalls", Age = 24 } })</para>
/// </summary>
public static void InsertAll<T>(this IDbConnection dbConn, IEnumerable<T> objs)
{
dbConn.Exec(dbCmd => dbCmd.InsertAll(objs, commandFilter:null));
}
/// <summary>
/// Insert a collection of POCOs in a transaction and modify populated IDbCommand with a commandFilter. E.g:
/// <para>db.InsertAll(new[] { new Person { Id = 9, FirstName = "Biggie", LastName = "Smalls", Age = 24 } },</para>
/// <para> dbCmd => applyFilter(dbCmd))</para>
/// </summary>
public static void InsertAll<T>(this IDbConnection dbConn, IEnumerable<T> objs, Action<IDbCommand> commandFilter)
{
dbConn.Exec(dbCmd => dbCmd.InsertAll(objs, commandFilter: commandFilter));
}
/// <summary>
/// Insert 1 or more POCOs in a transaction. E.g:
/// <para>db.Insert(new Person { Id = 1, FirstName = "Tupac", LastName = "Shakur", Age = 25 },</para>
/// <para> new Person { Id = 2, FirstName = "Biggie", LastName = "Smalls", Age = 24 })</para>
/// </summary>
public static void Insert<T>(this IDbConnection dbConn, params T[] objs)
{
dbConn.Exec(dbCmd => dbCmd.Insert(commandFilter: null, objs: objs));
}
/// <summary>
/// Insert 1 or more POCOs in a transaction. E.g:
/// <para>db.Insert(new Person { Id = 1, FirstName = "Tupac", LastName = "Shakur", Age = 25 },</para>
/// <para> new Person { Id = 2, FirstName = "Biggie", LastName = "Smalls", Age = 24 })</para>
/// INSERT INTO tbl_name(a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
/// </summary>
public static void InsertMultiValues<T>(this IDbConnection dbConn, params T[] objs)
{
dbConn.Exec(dbCmd => dbCmd.InsertMultiValues(commandFilter: null, objs: objs));
}
/// <summary>
/// Insert 1 or more POCOs in a transaction and modify populated IDbCommand with a commandFilter. E.g:
/// <para>db.Insert(dbCmd => applyFilter(dbCmd),</para>
/// <para> new Person { Id = 1, FirstName = "Tupac", LastName = "Shakur", Age = 25 },</para>
/// <para> new Person { Id = 2, FirstName = "Biggie", LastName = "Smalls", Age = 24 })</para>
/// </summary>
public static void Insert<T>(this IDbConnection dbConn, Action<IDbCommand> commandFilter, params T[] objs)
{
dbConn.Exec(dbCmd => dbCmd.Insert(commandFilter: commandFilter, objs: objs));
}
/// <summary>
/// Updates 1 POCO. All fields are updated except for the PrimaryKey which is used as the identity selector. E.g:
/// <para>db.Update(new Person { Id = 1, FirstName = "Jimi", LastName = "Hendrix", Age = 27 })</para>
/// </summary>
public static int Update<T>(this IDbConnection dbConn, T obj, Action<IDbCommand> commandFilter = null)
{
return dbConn.Exec(dbCmd => dbCmd.Update(obj, commandFilter));
}
/// <summary>
/// Updates 1 or more POCOs in a transaction. E.g:
/// <para>db.Update(new Person { Id = 1, FirstName = "Tupac", LastName = "Shakur", Age = 25 },</para>
/// <para>new Person { Id = 2, FirstName = "Biggie", LastName = "Smalls", Age = 24 })</para>
/// </summary>
public static int Update<T>(this IDbConnection dbConn, params T[] objs)
{
return dbConn.Exec(dbCmd => dbCmd.Update(objs, commandFilter:null));
}
public static int Update<T>(this IDbConnection dbConn, Action<IDbCommand> commandFilter, params T[] objs)
{
return dbConn.Exec(dbCmd => dbCmd.Update(objs, commandFilter));
}
/// <summary>
/// Updates 1 or more POCOs in a transaction. E.g:
/// <para>db.UpdateAll(new[] { new Person { Id = 1, FirstName = "Jimi", LastName = "Hendrix", Age = 27 } })</para>
/// </summary>
public static int UpdateAll<T>(this IDbConnection dbConn, IEnumerable<T> objs, Action<IDbCommand> commandFilter = null)
{
return dbConn.Exec(dbCmd => dbCmd.UpdateAll(objs, commandFilter));
}
/// <summary>
/// Delete rows using an anonymous type filter. E.g:
/// <para>db.Delete<Person>(new { FirstName = "Jimi", Age = 27 })</para>
/// </summary>
/// <returns>number of rows deleted</returns>
public static int Delete<T>(this IDbConnection dbConn, object anonFilter)
{
return dbConn.Exec(dbCmd => dbCmd.Delete<T>(anonFilter));
}
/// <summary>
/// Delete 1 row using all fields in the filter. E.g:
/// <para>db.Delete(new Person { Id = 1, FirstName = "Jimi", LastName = "Hendrix", Age = 27 })</para>
/// </summary>
/// <returns>number of rows deleted</returns>
public static int Delete<T>(this IDbConnection dbConn, T allFieldsFilter)
{
return dbConn.Exec(dbCmd => dbCmd.Delete(allFieldsFilter));
}
/// <summary>
/// Delete 1 or more rows in a transaction using all fields in the filter. E.g:
/// <para>db.Delete(new Person { Id = 1, FirstName = "Jimi", LastName = "Hendrix", Age = 27 })</para>
/// </summary>
public static int Delete<T>(this IDbConnection dbConn, params T[] allFieldsFilters)
{
return dbConn.Exec(dbCmd => dbCmd.Delete(allFieldsFilters));
}
/// <summary>
/// Delete 1 or more rows using only field with non-default values in the filter. E.g:
/// <para>db.DeleteNonDefaults(new Person { FirstName = "Jimi", Age = 27 })</para>
/// </summary>
/// <returns>number of rows deleted</returns>
public static int DeleteNonDefaults<T>(this IDbConnection dbConn, T nonDefaultsFilter)
{
return dbConn.Exec(dbCmd => dbCmd.DeleteNonDefaults(nonDefaultsFilter));
}
/// <summary>
/// Delete 1 or more rows in a transaction using only field with non-default values in the filter. E.g:
/// <para>db.DeleteNonDefaults(new Person { FirstName = "Jimi", Age = 27 },
/// new Person { FirstName = "Janis", Age = 27 })</para>
/// </summary>
/// <returns>number of rows deleted</returns>
public static int DeleteNonDefaults<T>(this IDbConnection dbConn, params T[] nonDefaultsFilters)
{
return dbConn.Exec(dbCmd => dbCmd.DeleteNonDefaults(nonDefaultsFilters));
}
/// <summary>
/// Delete 1 row by the PrimaryKey. E.g:
/// <para>db.DeleteById<Person>(1)</para>
/// </summary>
/// <returns>number of rows deleted</returns>
public static int DeleteById<T>(this IDbConnection dbConn, object id)
{
return dbConn.Exec(dbCmd => dbCmd.DeleteById<T>(id));
}
/// <summary>
/// Delete 1 row by the PrimaryKey where the rowVersion matches the optimistic concurrency field.
/// Will throw <exception cref="OptimisticConcurrencyException">RowModefiedExeption</exception> if the
/// row does not exist or has a different row version.
/// E.g: <para>db.DeleteById<Person>(1)</para>
/// </summary>
public static void DeleteById<T>(this IDbConnection dbConn, object id, ulong rowVersion)
{
dbConn.Exec(dbCmd => dbCmd.DeleteById<T>(id, rowVersion));
}
/// <summary>
/// Delete all rows identified by the PrimaryKeys. E.g:
/// <para>db.DeleteById<Person>(new[] { 1, 2, 3 })</para>
/// </summary>
/// <returns>number of rows deleted</returns>
public static int DeleteByIds<T>(this IDbConnection dbConn, IEnumerable idValues)
{
return dbConn.Exec(dbCmd => dbCmd.DeleteByIds<T>(idValues));
}
/// <summary>
/// Delete all rows in the generic table type. E.g:
/// <para>db.DeleteAll<Person>()</para>
/// </summary>
/// <returns>number of rows deleted</returns>
public static int DeleteAll<T>(this IDbConnection dbConn)
{
return dbConn.Exec(dbCmd => dbCmd.DeleteAll<T>());
}
/// <summary>
/// Delete all rows provided. E.g:
/// <para>db.DeleteAll<Person>(people)</para>
/// </summary>
/// <returns>number of rows deleted</returns>
public static int DeleteAll<T>(this IDbConnection dbConn, IEnumerable<T> rows)
{
return dbConn.Exec(dbCmd => dbCmd.DeleteAll(rows));
}
/// <summary>
/// Delete all rows in the runtime table type. E.g:
/// <para>db.DeleteAll(typeof(Person))</para>
/// </summary>
/// <returns>number of rows deleted</returns>
public static int DeleteAll(this IDbConnection dbConn, Type tableType)
{
return dbConn.Exec(dbCmd => dbCmd.DeleteAll(tableType));
}
/// <summary>
/// Delete rows using a SqlFormat filter. E.g:
/// <para>db.Delete<Person>("Age > @age", new { age = 42 })</para>
/// </summary>
/// <returns>number of rows deleted</returns>
public static int Delete<T>(this IDbConnection dbConn, string sqlFilter, object anonType)
{
return dbConn.Exec(dbCmd => dbCmd.Delete<T>(sqlFilter, anonType));
}
/// <summary>
/// Delete rows using a SqlFormat filter. E.g:
/// <para>db.Delete(typeof(Person), "Age > @age", new { age = 42 })</para>
/// </summary>
/// <returns>number of rows deleted</returns>
public static int Delete(this IDbConnection dbConn, Type tableType, string sqlFilter, object anonType)
{
return dbConn.Exec(dbCmd => dbCmd.Delete(tableType, sqlFilter, anonType));
}
/// <summary>
/// Insert a new row or update existing row. Returns true if a new row was inserted.
/// Optional references param decides whether to save all related references as well. E.g:
/// <para>db.Save(customer, references:true)</para>
/// </summary>
/// <returns>true if a row was inserted; false if it was updated</returns>
public static bool Save<T>(this IDbConnection dbConn, T obj, bool references = false)
{
if (!references)
return dbConn.Exec(dbCmd => dbCmd.Save(obj));
return dbConn.Exec(dbCmd =>
{
var ret = dbCmd.Save(obj);
dbCmd.SaveAllReferences(obj);
return ret;
});
}
/// <summary>
/// Insert new rows or update existing rows. Return number of rows added E.g:
/// <para>db.Save(new Person { Id = 10, FirstName = "Amy", LastName = "Winehouse", Age = 27 })</para>
/// </summary>
/// <returns>number of rows added</returns>
public static int Save<T>(this IDbConnection dbConn, params T[] objs)
{
return dbConn.Exec(dbCmd => dbCmd.Save(objs));
}
/// <summary>
/// Insert new rows or update existing rows. Return number of rows added E.g:
/// <para>db.SaveAll(new [] { new Person { Id = 10, FirstName = "Amy", LastName = "Winehouse", Age = 27 } })</para>
/// </summary>
/// <returns>number of rows added</returns>
public static int SaveAll<T>(this IDbConnection dbConn, IEnumerable<T> objs)
{
return dbConn.Exec(dbCmd => dbCmd.SaveAll(objs));
}
/// <summary>
/// Populates all related references on the instance with its primary key and saves them. Uses '(T)Id' naming convention. E.g:
/// <para>db.SaveAllReferences(customer)</para>
/// </summary>
public static void SaveAllReferences<T>(this IDbConnection dbConn, T instance)
{
dbConn.Exec(dbCmd => dbCmd.SaveAllReferences(instance));
}
/// <summary>
/// Populates the related references with the instance primary key and saves them. Uses '(T)Id' naming convention. E.g:
/// <para>db.SaveReference(customer, customer.Orders)</para>
/// </summary>
public static void SaveReferences<T, TRef>(this IDbConnection dbConn, T instance, params TRef[] refs)
{
dbConn.Exec(dbCmd => dbCmd.SaveReferences(instance, refs));
}
/// <summary>
/// Populates the related references with the instance primary key and saves them. Uses '(T)Id' naming convention. E.g:
/// <para>db.SaveReference(customer, customer.Orders)</para>
/// </summary>
public static void SaveReferences<T, TRef>(this IDbConnection dbConn, T instance, List<TRef> refs)
{
dbConn.Exec(dbCmd => dbCmd.SaveReferences(instance, refs.ToArray()));
}
/// <summary>
/// Populates the related references with the instance primary key and saves them. Uses '(T)Id' naming convention. E.g:
/// <para>db.SaveReferences(customer, customer.Orders)</para>
/// </summary>
public static void SaveReferences<T, TRef>(this IDbConnection dbConn, T instance, IEnumerable<TRef> refs)
{
dbConn.Exec(dbCmd => dbCmd.SaveReferences(instance, refs.ToArray()));
}
// Procedures
public static void ExecuteProcedure<T>(this IDbConnection dbConn, T obj)
{
dbConn.Exec(dbCmd => dbCmd.ExecuteProcedure(obj));
}
/// <summary>
/// Generates inline UPDATE SQL Statement
/// </summary>
public static string ToUpdateStatement<T>(this IDbConnection dbConn, T item, ICollection<string> updateFields = null)
{
return dbConn.Exec(dbCmd => dbCmd.GetDialectProvider().ToUpdateStatement(dbCmd, item, updateFields));
}
/// <summary>
/// Generates inline INSERT SQL Statement
/// </summary>
public static string ToInsertStatement<T>(this IDbConnection dbConn, T item, ICollection<string> insertFields = null)
{
return dbConn.Exec(dbCmd => dbCmd.GetDialectProvider().ToInsertStatement(dbCmd, item, insertFields));
}
}
}
internal static int InsertMultiValues(this IDbCommand dbCmd, Action commandFilter, params T[] objs) { IDbTransaction dbTrans = null; int execCount = 0; try { if (dbCmd.Transaction == null) dbCmd.Transaction = dbTrans = dbCmd.Connection.BeginTransaction();
var dialectProvider = dbCmd.GetDialectProvider();
dialectProvider.PrepareParameterizedInsertStatement<T>(dbCmd);
var splitStr = " VALUES ";
var valuesIndexOf = dbCmd.CommandText.IndexOf(splitStr);
var insertIntoPrefix = dbCmd.CommandText.Substring(0, valuesIndexOf);
var valuesText = dbCmd.CommandText.Substring(valuesIndexOf + splitStr.Length);
var modelDef = typeof(T).GetModelDefinition();
var fieldMap = dialectProvider.GetFieldDefinitionMap(modelDef);
var fieldDefs = modelDef.FieldDefinitionsArray;
commandFilter?.Invoke(dbCmd);
string commandTextValues = " ";
int index = 0;
dbCmd.Parameters.Clear();
foreach (var obj in objs)
{
var paramPostfix = $"_{index}"; //参数下标后缀
var curValuesText = valuesText.Replace(",", $"{paramPostfix},");
curValuesText = curValuesText.Replace(")", $"{paramPostfix})");
commandTextValues += curValuesText;
if (index != objs.Length - 1)
{
commandTextValues += ", ";
}
foreach (var fieldDef in fieldDefs)
{
var p = dbCmd.CreateParameter();
dialectProvider.SetParameterValue<T>(fieldDef, p, obj);
p.ParameterName = $"{dialectProvider.GetParam(dialectProvider.SanitizeFieldNameForParamName(fieldDef.FieldName))}{paramPostfix}";
dbCmd.Parameters.Add(p);
}
index++;
}
dbCmd.CommandText = insertIntoPrefix + splitStr + commandTextValues;
execCount = dbCmd.ExecNonQuery();
dbTrans?.Commit();
}
finally
{
dbTrans?.Dispose();
}
return execCount;
}
internal static int InsertMultiValues(this IDbCommand dbCmd, Action commandFilter, params T[] objs) { IDbTransaction dbTrans = null; int execCount = 0; try { if (dbCmd.Transaction == null) dbCmd.Transaction = dbTrans = dbCmd.Connection.BeginTransaction();
var dialectProvider = dbCmd.GetDialectProvider();
dialectProvider.PrepareParameterizedInsertStatement<T>(dbCmd);
var splitStr = " VALUES ";
var valuesIndexOf = dbCmd.CommandText.IndexOf(splitStr);
var insertIntoPrefix = dbCmd.CommandText.Substring(0, valuesIndexOf);
var valuesText = dbCmd.CommandText.Substring(valuesIndexOf + splitStr.Length);
valuesText = valuesText.Replace("(", "").Replace(")", "");
var valuesArray = valuesText.Split(',');
var modelDef = typeof(T).GetModelDefinition();
var fieldMap = dialectProvider.GetFieldDefinitionMap(modelDef);
var fieldDefs = modelDef.FieldDefinitionsArray;
commandFilter?.Invoke(dbCmd);
string commandTextValues = " ";
int index = 0;
dbCmd.Parameters.Clear();
foreach (var obj in objs)
{
var paramPostfix = $"_{index}"; //参数下标后缀
commandTextValues += $"({valuesArray.Join($"{paramPostfix},")}{paramPostfix})";
//var curValuesText = valuesText.Replace(",", $"{paramPostfix},");
//curValuesText = curValuesText.Replace(")", $"{paramPostfix})");
if (index != objs.Length - 1)
{
commandTextValues += ", ";
}
foreach (var fieldDef in fieldDefs)
{
var p = dbCmd.CreateParameter();
dialectProvider.SetParameterValue<T>(fieldDef, p, obj);
p.ParameterName = $"{dialectProvider.GetParam(dialectProvider.SanitizeFieldNameForParamName(fieldDef.FieldName))}{paramPostfix}";
dbCmd.Parameters.Add(p);
}
index++;
}
dbCmd.CommandText = $"{insertIntoPrefix}{splitStr}{commandTextValues}";
execCount = dbCmd.ExecNonQuery();
dbTrans?.Commit();
}
finally
{
dbTrans?.Dispose();
}
return execCount;
}