protected DbCommandInfo Translate(DbExpression e)
{
IDbExpressionTranslator translator = this.DatabaseProvider.CreateDbExpressionTranslator();
DbCommandInfo dbCommandInfo = translator.Translate(e);
return(dbCommandInfo);
}
//Note: as of May/13, MySql does not preserve comments preceding SP header; they are going to patch this.
// For now, we put tag in a comment right after BEGIN
public override bool ConvertToStoredProc(DbCommandInfo command)
{
var table = command.Table;
string ProcBodyTemplate =
@"BEGIN
{0}
-- Description: {1}
{2}
{3}
END";
string CreateProcTemplate =
@"CREATE PROCEDURE {0}(
{1})
SQL SECURITY INVOKER
{2}
";
command.Schema = table.Schema;
//Build command that creates stored proc and execute it
var listParams = new StringList();
foreach (var prm in command.Parameters)
{
var strOut = (prm.Direction & ParameterDirection.Output) != 0 ? "OUT " : string.Empty;
var prmSpec = string.Format(" {0}{1} {2}", strOut, prm.Name, prm.TypeInfo.SqlTypeSpec);
listParams.Add(prmSpec);
}
var strParams = string.Join(",\r\n", listParams);
var tag = DbDriver.GeneratedCrudProcTagPrefix + command.DescriptiveTag;
command.SourceHash = SourceHasher.ComputeHash(command.FullCommandName, strParams, command.Sql);
command.StoredProcBody = string.Format(ProcBodyTemplate, tag, command.Description, command.Sql, SourceHasher.GetHashLine(command.SourceHash));
command.StoredProcText = string.Format(CreateProcTemplate, command.FullCommandName, strParams, command.StoredProcBody);
return(true);
}
private IDbCommand CreateDbCommand(DbCommandInfo commandInfo, DataConnection connection)
{
var cmd = connection.DbConnection.CreateCommand();
cmd.Transaction = connection.DbTransaction;
connection.Session.SetLastCommand(cmd);
bool isSp = commandInfo.CommandType == CommandType.StoredProcedure && !connection.Session.Options.IsSet(EntitySessionOptions.DisableStoredProcs);
if (isSp)
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = commandInfo.FullCommandName;
}
else
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = commandInfo.Sql;
}
//Create parameters collection
if (commandInfo.IsTemplatedSql)
{
}
else
{
for (int index = 0; index < commandInfo.Parameters.Count; index++)
{
_driver.AddParameter(cmd, commandInfo.Parameters[index]);
}
}
return(cmd);
}//method
private void SetCrudCommandParameterValues(DbCommandInfo commandInfo, IDbCommand command, EntityRecord record)
{
if (record.Status == EntityStatus.Stub)
{
record.Reload();
}
for (int i = 0; i < commandInfo.Parameters.Count; i++)
{
var prm = (IDbDataParameter)command.Parameters[i];
prm.Value = DBNull.Value;
var prmInfo = commandInfo.Parameters[i];
var isInput = prmInfo.Direction == ParameterDirection.Input || prmInfo.Direction == ParameterDirection.InputOutput;
if (!isInput)
{
continue;
}
var col = prmInfo.SourceColumn;
if (col == null || col.Member == null)
{
continue;
}
var value = record.GetValueDirect(col.Member);
if (value == null)
{
value = DBNull.Value;
}
var conv = prmInfo.TypeInfo.PropertyToColumnConverter;
if (value != DBNull.Value && conv != null)
{
value = conv(value);
}
prm.Value = value;
} //for i
}
/// <summary>
/// Executes the query, and returns the first column of the first row in the result set returned by the query.
/// Additional columns or rows are ignored.
/// </summary>
/// <typeparam name="T">Type of model.</typeparam>
/// <param name="query">The query.</param>
/// <returns>
/// The first column of the first row in the result set, or <see langword="null"/> if the result set is empty.
/// Returns a maximum of 2033 characters.
/// </returns>
/// <exception cref="ArgumentNullException">If query is null.</exception>
public object ExecuteScalar <T>(IQuery <T> query)
{
Check.NotNull(query, nameof(query));
using (var cnHelper = OpenConnection())
using (DbCommandInfo commandInfo = CreateCommand(query.Expression))
{
_logger.LogCommand(commandInfo.Command);
if (commandInfo.Reader == null)
{
return(commandInfo.Command.ExecuteScalar());
}
else
{
using (IDataReaderEnvelope reader = commandInfo.Reader)
{
reader.SetInnerReader(commandInfo.Command.ExecuteReader());
if (reader.Read())
{
return(reader.GetValue(0));
}
}
}
return(null);
}
}
//Note: be careful not to introduce trailing spaces, esp. when some of the template args are empty;
// latest PG driver cuts them off.
// TODO: need better solution for Postgres trailing spaces
private bool ConvertNonQueryToStoredProc(DbCommandInfo command)
{
var table = command.Table;
//TODO: refactor to return #of records
string ProcBodyTemplate =
@"{0}
BEGIN
{1}{2};
{3}
END;";
const string CreateProcTemplate = @"
CREATE OR REPLACE FUNCTION {0}({1}) {2} AS
$$
{3}
$$
LANGUAGE plpgsql;
";
// In Postgres, if function has output parameter(s), it must return scalar or record. If there's no out parameters, you must specify
// something as return type - we specify VOID. If we have out param(s), we skip 'returns' clause, and Postgres adds it automatically.
// If we have OUT parameter for identity field; we retrieve Identity value for INSERT using ' ... RETURNING "Id" INTO p_id; ' clause.
// Insert SQL has already "... RETURNING "Id" ' clause - we add only ' INTO p_Id ' extra.
command.Schema = table.Schema;
var listParams = new StringList();
bool hasOut = false;
var lstTargets = new List <string>();
foreach (var prm in command.Parameters)
{
string strOut = string.Empty;
if ((prm.Direction & ParameterDirection.Output) != 0)
{
hasOut = true;
strOut = "OUT ";
var col = prm.SourceColumn;
if (col != null && col.Flags.IsSet(DbColumnFlags.Identity))
{
lstTargets.Add(prm.Name);
}
}
listParams.Add(" " + strOut + prm.Name + " " + prm.TypeInfo.SqlTypeSpec);
}
string strReturns = hasOut ? string.Empty : "RETURNS VOID";
var strParams = listParams.Count > 0 ? "\r\n " + string.Join(",\r\n ", listParams) + "\r\n" : string.Empty;
var header = BuildProcHeader(command);
var strRetInto = string.Empty;
if (lstTargets.Count > 0)
{
strRetInto = "\r\n INTO " + string.Join(", ", lstTargets);
}
var sql = command.Sql.TrimEnd(' ', '\r', '\n', ';'); //trim ending semicolon so that we can append RETURNING clause
command.SourceHash = SourceHasher.ComputeHash(command.FullCommandName, strParams, sql);
command.StoredProcBody = string.Format(ProcBodyTemplate, header, sql, strRetInto, SourceHasher.GetHashLine(command.SourceHash));
command.StoredProcText =
string.Format(CreateProcTemplate, command.FullCommandName, strParams, strReturns, command.StoredProcBody);
return(true);
}
/// <summary>
/// Initializes a new instance of the <see cref="DbCommandProxy"/> class.
/// </summary>
public DbCommandProxy(DbCommand command, DbCommandInfo info)
{
Debug.Assert(command != null);
Debug.Assert(info != null);
_command = command;
_commandInfo = info;
}
public override void BuildStoredProcDropSql(DbObjectChange change, DbCommandInfo command)
{
if(command.CustomTag == null) { //try to recover it
var inpParams = command.Parameters.Where(p => p.Direction != ParameterDirection.Output);
command.CustomTag = string.Join(", ", inpParams.Select(p => p.TypeInfo.SqlTypeSpec));
}
var funcRef = string.Format(@"{0}.""{1}""({2})", command.Schema, command.CommandName, command.CustomTag);
change.AddScript(DbScriptType.RoutineDrop, "DROP FUNCTION {0};", funcRef);
}
// Another trouble - when deleting routine, you have to provide proc name AND list of parameter types -
// even if you have a single proc with this name; reason - proc name overloading.
// You might have 2+ procs with the same name but different parameter lists, so you MUST always reference proc
// using name and parameter types - even if you have just one defined. Seriously?!!!
public override void BuildStoredProcDropSql(DbObjectChange change, DbCommandInfo command)
{
if (command.CustomTag == null) //try to recover it
{
var inpParams = command.Parameters.Where(p => p.Direction != ParameterDirection.Output);
command.CustomTag = string.Join(", ", inpParams.Select(p => p.TypeInfo.SqlTypeSpec));
}
var funcRef = string.Format(@"{0}.""{1}""({2})", command.Schema, command.CommandName, command.CustomTag);
change.AddScript(DbScriptType.RoutineDrop, "DROP FUNCTION {0};", funcRef);
}
private string BuildProcHeader(DbCommandInfo command)
{
var entCommand = command.EntityCommand;
//table/category/operation; for ex: Product/CRUD/Update
const string ProcHeaderLineTemplate =
@"{0}{1}
-- Description: {2}
";
return(string.Format(ProcHeaderLineTemplate, DbDriver.GeneratedCrudProcTagPrefix, command.DescriptiveTag, entCommand.Description));
}
public override DbCommandInfo BuildSqlSequenceGetNextCommand(DbSequenceInfo sequence)
{
const string SqlTemplate = "SELECT nextval('{0}.\"{1}\"');"; //note sequence name in double quotes inside single-quote argument
//Load by primary key
var cmdName = sequence.Name + "_GetNextValue";
var cmdInfo = new DbCommandInfo(DbModel, sequence.Schema, cmdName, null, null);
cmdInfo.Sql = string.Format(SqlTemplate, sequence.Schema, sequence.Name);
cmdInfo.ExecutionType = DbExecutionType.Scalar;
return(cmdInfo);
}
public override DbCommandInfo BuildSqlSequenceGetNextCommand(DbSequenceInfo sequence)
{
const string SqlTemplate = "SELECT NEXT VALUE FOR {0};";
//Load by primary key
var cmdName = sequence.Name + "_GetNextValue";
var cmdInfo = new DbCommandInfo(DbModel, sequence.Schema, cmdName, null, null);
cmdInfo.Sql = string.Format(SqlTemplate, sequence.FullName);
cmdInfo.ExecutionType = DbExecutionType.Scalar;
return(cmdInfo);
}
} //method
//Tag sample: "CRUD/[books].[BookOrderLine]/Delete"
// Note: we need to link Proc to table at load time, in case we will be deleting table - we also need to delete crud procs
protected virtual void LinkCrudProcToTable(DbCommandInfo command, string tag)
{
var arr = tag.SplitNames('/');
var tblName = arr[1];
var tbl = Model.GetTable(tblName);
if (tbl != null)
{
command.Table = tbl;
tbl.CrudCommands.Add(command);
}
}
/// <summary>
/// Executes the specified query.
/// </summary>
/// <typeparam name="T">Type of model result.</typeparam>
/// <param name="query">The query.</param>
/// <returns>
/// IEnumerable of models, which was materialized by query
/// </returns>
/// <exception cref="ArgumentNullException">If query is null.</exception>
public IEnumerable <T> Execute <T>(IQuery <T> query)
{
Check.NotNull(query, nameof(query));
Data.ConnectionHelper cnHelper = OpenConnection();
DbCommandInfo commandInfo = CreateCommand(query.Expression);
_logger.LogCommand(commandInfo.Command);
IDataReader reader = new ModelBuilder.QueryDataReader(commandInfo.Command, commandInfo.Reader,
cnHelper.CloseConnection);
return(_modelBuilder.Materialize <T>(reader));
}
public virtual DbCommandInfo Translate(DbExpression expression)
{
SqlGenerator generator = this.CreateSqlGenerator();
expression = EvaluableDbExpressionTransformer.Transform(expression);
expression.Accept(generator);
DbCommandInfo dbCommandInfo = new DbCommandInfo();
dbCommandInfo.Parameters = generator.Parameters;
dbCommandInfo.CommandText = generator.SqlBuilder.ToSql();
return(dbCommandInfo);
}
public DbCommandInfo Translate(DbExpression expression)
{
SqlGenerator generator = SqlGenerator.CreateInstance();
expression = EvaluableDbExpressionTransformer.Transform(expression);
expression.Accept(generator);
DbCommandInfo result = new DbCommandInfo();
result.Parameters = generator.Parameters;
result.CommandText = generator.SqlBuilder.ToSql();
return(result);
}
public override bool ConvertToStoredProc(DbCommandInfo command)
{
switch (command.Kind)
{
case EntityCommandKind.SelectAll:
case EntityCommandKind.SelectAllPaged:
case EntityCommandKind.SelectByKey:
case EntityCommandKind.SelectByKeyManyToMany:
return(ConvertQueryToStoredProc(command));
case EntityCommandKind.SelectByKeyArray:
return(ConvertQueryToStoredProc(command));
default:
return(ConvertNonQueryToStoredProc(command));
}
}
}//method
// Sets parameter values.
private void SetCommandParameterValues(DbCommandInfo commandInfo, IDbCommand command, object[] args)
{
for (int i = 0; i < commandInfo.Parameters.Count; i++)
{
var prmInfo = commandInfo.Parameters[i];
if (prmInfo.ArgIndex >= 0)
{
var prm = (IDbDataParameter)command.Parameters[i];
var v = args[prmInfo.ArgIndex];
var conv = prmInfo.TypeInfo.PropertyToColumnConverter;
if (v != null && conv != null)
{
v = conv(v);
}
prm.Value = v;
}
} //for i
}
protected virtual void LoadRoutines()
{
var supportsRoutines = Driver.Supports(DbFeatures.StoredProcedures);
if (!supportsRoutines)
{
return;
}
var data = GetRoutines();
bool hasCustomTag = data.HasColumn("ROUTINE_CUSTOM");
foreach (DbRow row in data.Rows)
{
var schema = row.GetAsString("ROUTINE_SCHEMA");
if (!IncludeSchema(schema))
{
continue;
}
var name = row.GetAsString("ROUTINE_NAME");
var procText = row.GetAsString("ROUTINE_DEFINITION");
var descrTag = GetProcedureTag(procText);
//if will be added to model.Commands automatically
var dbCommand = new DbCommandInfo(Model, schema, name, null, descrTag);
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.StoredProcText = procText;
dbCommand.SourceHash = SourceHasher.ExtractHash(procText);
//detect if it is auto-generated
if (!string.IsNullOrEmpty(descrTag))
{
dbCommand.DescriptiveTag = descrTag;
dbCommand.Flags |= DbCommandFlags.AutoGenerated;
if (descrTag.StartsWith("CRUD"))
{
LinkCrudProcToTable(dbCommand, descrTag);
}
}
if (hasCustomTag)
{
dbCommand.CustomTag = row["ROUTINE_CUSTOM"];
}
} //foreach
} //method
}//method
public override void BuildStoredProcAddSql(DbObjectChange change, DbCommandInfo command)
{
base.BuildStoredProcAddSql(change, command);
const string GrantTemplate = "GRANT EXECUTE ON {0} TO [{1}];";
if (command.EntityCommand.Kind.IsSelect())
{
if (!string.IsNullOrWhiteSpace(Settings.GrantExecReadToRole))
{
change.AddScript(DbScriptType.Grant, GrantTemplate, command.FullCommandName, Settings.GrantExecReadToRole);
}
}
else
{
if (!string.IsNullOrWhiteSpace(Settings.GrantExecWriteToRole))
{
change.AddScript(DbScriptType.Grant, GrantTemplate, command.FullCommandName, Settings.GrantExecWriteToRole);
}
}
}
private void FormatTemplatedSql(DbCommandInfo commandInfo, IDbCommand command, object[] args)
{
if (args == null || args.Length == 0)
{
return;
}
var values = new string[args.Length];
for (int i = 0; i < commandInfo.Parameters.Count; i++)
{
var prmInfo = commandInfo.Parameters[i];
if (prmInfo.ArgIndex >= 0)
{
var v = args[prmInfo.ArgIndex];
var strV = prmInfo.ToLiteralConverter(v);
values[i] = strV;
}
command.CommandText = string.Format(commandInfo.Sql, values);
} //for i
}
protected void ReadCrudOutputParameterValues(IDbCommand command, DbCommandInfo commandInfo, EntityRecord record)
{
for (int i = 0; i < commandInfo.OutputParameters.Count; i++)
{
var prmInfo = commandInfo.OutputParameters[i];
var col = prmInfo.SourceColumn;
if (col == null)
{
continue;
}
var prm = command.Parameters[prmInfo.Name] as IDbDataParameter;
var value = prm.Value;
var conv = prmInfo.TypeInfo.ColumnToPropertyConverter;
if (value != DBNull.Value && conv != null)
{
value = conv(value);
}
record.ValuesModified[col.Member.ValueIndex] = value;
}//for
}
public override bool ConvertToStoredProc(DbCommandInfo command)
{
const string ProcBodyTemplate =
@"-- Description: {0}
{1}
{2}
{3}
";
const string CreateProcTemplate = @"CREATE PROCEDURE {0}
{1}
AS
BEGIN
SET NOCOUNT ON;
{2}
END
";
var table = command.Table;
command.Schema = table.Schema;
//Build command that creates stored proc and execute it
var listParams = new StringList();
foreach (var prm in command.Parameters)
{
var strOut = (prm.Direction & ParameterDirection.Output) != 0 ? " OUTPUT" : string.Empty;
// Add READONLY for table-type parameters
var strReadOnly = (prm.TypeInfo.VendorDbType.VendorDbType == (int)SqlDbType.Structured) ? " READONLY" : string.Empty;
var prmSpec = " " + prm.Name + " " + prm.TypeInfo.SqlTypeSpec + strReadOnly + strOut;
listParams.Add(prmSpec);
}
var strParams = string.Join(",\r\n", listParams);
var desc = command.EntityCommand.Description;
var tag = DbDriver.GeneratedCrudProcTagPrefix + command.DescriptiveTag;
command.SourceHash = SourceHasher.ComputeHash(command.FullCommandName, strParams, command.Sql);
command.StoredProcBody = string.Format(ProcBodyTemplate, desc, tag, command.Sql, SourceHasher.GetHashLine(command.SourceHash));
command.StoredProcText = string.Format(CreateProcTemplate, command.FullCommandName, strParams, command.StoredProcBody);
return(true);
}
public virtual DbCommandInfo CreateDbCommandInfo(EntityCommand entityCommand, string name, DbTableInfo mainTable, DbExecutionType executionType, string sql)
{
var descrTag = GetDbCommandDescriptiveTag(entityCommand);
var cmdInfo = new DbCommandInfo(entityCommand, name, mainTable, executionType, sql, descrTag);
//Create parameters from entity command parameters
var policy = DbModel.Config.NamingPolicy;
var prmPrefix = GetParameterPrefix();
for (int i = 0; i < entityCommand.Parameters.Count; i++)
{
var entParam = entityCommand.Parameters[i];
var paramName = prmPrefix + policy.GetDbParameterName(entParam.Name);
DbParamInfo prmInfo;
if (entParam.SourceMember != null)
{
var col = mainTable.Columns.FirstOrDefault(c => c.Member == entParam.SourceMember);
Util.Check(col != null, "Failed to find Db column for member {0}, entity {1}.", entParam.SourceMember.MemberName, mainTable.Entity.Name);
prmInfo = cmdInfo.AddParameter(entParam, paramName, col, i);
}
else
{
var typeInfo = GetDbTypeInfo(entParam.DataType, entParam.Size);
prmInfo = cmdInfo.AddParameter(entParam, paramName, typeInfo, i);
}
// SQL CE does not support output parameters
if (!this.DbModel.Driver.Supports(DbFeatures.OutputParameters))
{
prmInfo.Direction = ParameterDirection.Input;
}
if (prmInfo.Direction == ParameterDirection.Output || prmInfo.Direction == ParameterDirection.InputOutput)
{
cmdInfo.PostUpdateActions.Add((con, cmd, rec) => {
var prm = (IDbDataParameter)cmd.Parameters[prmInfo.Name];
rec.SetValueDirect(prmInfo.SourceColumn.Member, prm.Value);
});
}
}//foreach entParam
return(cmdInfo);
}
// See examples of stored procs at the end of this file
private bool ConvertQueryToStoredProc(DbCommandInfo command)
{
var table = command.Table;
string ProcBodyTemplate =
@"{0}
DECLARE
ref1 refcursor;
BEGIN
OPEN ref1 FOR
{1}
RETURN ref1;
{2}
END;
";
string CreateProcTemplate =
@"{0}
CREATE OR REPLACE FUNCTION {1} ({2}) RETURNS refcursor AS $$
{3}
$$ LANGUAGE plpgsql;
";
command.Schema = table.Schema;
//Build command that creates stored proc and execute it
var listParams = new StringList();
foreach (var prm in command.Parameters)
{
var strOut = (prm.Direction & ParameterDirection.Output) != 0 ? " OUT" : string.Empty;
listParams.Add(" " + strOut + prm.Name + " " + prm.TypeInfo.SqlTypeSpec);
}
var strParams = listParams.Count > 0 ? "\r\n " + string.Join(",\r\n ", listParams) + "\r\n" : string.Empty;
var header = BuildProcHeader(command);
command.SourceHash = SourceHasher.ComputeHash(command.FullCommandName, strParams, command.Sql);
command.StoredProcBody = string.Format(ProcBodyTemplate, header, command.Sql, SourceHasher.GetHashLine(command.SourceHash));
command.StoredProcText =
string.Format(CreateProcTemplate, header, command.FullCommandName, strParams, command.StoredProcBody);
return(true);
}
DbCommandFactor GenerateCommandFactor()
{
IQueryState qs = QueryExpressionResolver.Resolve(this._query.QueryExpression, new ScopeParameterDictionary(), new StringSet());
MappingData data = qs.GenerateMappingData();
IObjectActivator objectActivator;
if (this._query._trackEntity)
{
objectActivator = data.ObjectActivatorCreator.CreateObjectActivator(this._query.DbContext);
}
else
{
objectActivator = data.ObjectActivatorCreator.CreateObjectActivator();
}
IDbExpressionTranslator translator = this._query.DbContext.DatabaseProvider.CreateDbExpressionTranslator();
DbCommandInfo dbCommandInfo = translator.Translate(data.SqlQuery);
DbCommandFactor commandFactor = new DbCommandFactor(objectActivator, dbCommandInfo.CommandText, dbCommandInfo.GetParameters());
return(commandFactor);
}
protected virtual string GetGrantStatement(DbCommandInfo command)
{
return null;
}
public virtual DbCommandInfo CreateDbCommandInfo(EntityCommand entityCommand, string name, DbTableInfo mainTable, DbExecutionType executionType, string sql)
{
var descrTag = GetDbCommandDescriptiveTag(entityCommand);
var cmdInfo = new DbCommandInfo(entityCommand, name, mainTable, executionType, sql, descrTag);
//Create parameters from entity command parameters
var policy = DbModel.Config.NamingPolicy;
var prmPrefix = GetParameterPrefix();
for(int i=0; i< entityCommand.Parameters.Count; i++) {
var entParam = entityCommand.Parameters[i];
var paramName = prmPrefix + policy.ConstructDbParameterName(entParam.Name);
DbParamInfo prmInfo;
if (entParam.SourceMember != null) {
var col = mainTable.Columns.FirstOrDefault(c => c.Member == entParam.SourceMember);
Util.Check(col != null, "Failed to find Db column for member {0}, entity {1}.", entParam.SourceMember.MemberName, mainTable.Entity.Name);
prmInfo = cmdInfo.AddParameter(entParam, paramName, col, i);
} else {
var typeInfo = GetDbTypeInfo(entParam.DataType, entParam.Size);
prmInfo = cmdInfo.AddParameter(entParam, paramName, typeInfo, i);
}
// SQL CE does not support output parameters
if (!this.DbModel.Driver.Supports(DbFeatures.OutputParameters))
prmInfo.Direction = ParameterDirection.Input;
if (prmInfo.Direction == ParameterDirection.Output || prmInfo.Direction == ParameterDirection.InputOutput) {
cmdInfo.PostUpdateActions.Add((con, cmd, rec) => {
var prm = (IDbDataParameter) cmd.Parameters[prmInfo.Name];
rec.SetValueDirect(prmInfo.SourceColumn.Member, prm.Value);
});
}
}//foreach entParam
return cmdInfo;
}
protected virtual async Task <TEntity> Insert <TEntity>(TEntity entity, string table, bool @async)
{
PublicHelper.CheckNull(entity);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
Dictionary <PrimitivePropertyDescriptor, object> keyValueMap = PrimaryKeyHelper.CreateKeyValueMap(typeDescriptor);
Dictionary <PrimitivePropertyDescriptor, DbExpression> insertColumns = new Dictionary <PrimitivePropertyDescriptor, DbExpression>();
foreach (PrimitivePropertyDescriptor propertyDescriptor in typeDescriptor.PrimitivePropertyDescriptors)
{
if (propertyDescriptor.IsAutoIncrement)
{
continue;
}
object val = propertyDescriptor.GetValue(entity);
if (propertyDescriptor.IsPrimaryKey)
{
keyValueMap[propertyDescriptor] = val;
}
PublicHelper.NotNullCheck(propertyDescriptor, val);
DbParameterExpression valExp = DbExpression.Parameter(val, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType);
insertColumns.Add(propertyDescriptor, valExp);
}
PrimitivePropertyDescriptor nullValueKey = keyValueMap.Where(a => a.Value == null && !a.Key.IsAutoIncrement).Select(a => a.Key).FirstOrDefault();
if (nullValueKey != null)
{
/* 主键为空并且主键又不是自增列 */
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", nullValueKey.Property.Name));
}
DbTable dbTable = PublicHelper.CreateDbTable(typeDescriptor, table);
DbInsertExpression e = new DbInsertExpression(dbTable);
foreach (var kv in insertColumns)
{
e.InsertColumns.Add(kv.Key.Column, kv.Value);
}
PrimitivePropertyDescriptor autoIncrementPropertyDescriptor = typeDescriptor.AutoIncrement;
if (autoIncrementPropertyDescriptor == null)
{
await this.ExecuteNonQuery(e, @async);
return(entity);
}
IDbExpressionTranslator translator = this.DatabaseProvider.CreateDbExpressionTranslator();
DbCommandInfo dbCommandInfo = translator.Translate(e);
dbCommandInfo.CommandText = string.Concat(dbCommandInfo.CommandText, ";", this.GetSelectLastInsertIdClause());
//SELECT @@IDENTITY 返回的是 decimal 类型
object retIdentity = await this.ExecuteScalar(dbCommandInfo, @async);
if (retIdentity == null || retIdentity == DBNull.Value)
{
throw new ChloeException("Unable to get the identity value.");
}
retIdentity = PublicHelper.ConvertObjectType(retIdentity, autoIncrementPropertyDescriptor.PropertyType);
autoIncrementPropertyDescriptor.SetValue(entity, retIdentity);
return(entity);
}
private IDbCommand CreateDbCommand(DbCommandInfo commandInfo, DataConnection connection)
{
var cmd = connection.DbConnection.CreateCommand();
cmd.Transaction = connection.DbTransaction;
connection.Session.SetLastCommand(cmd);
bool isSp = commandInfo.CommandType == CommandType.StoredProcedure && !connection.Session.Options.IsSet(EntitySessionOptions.DisableStoredProcs);
if(isSp) {
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = commandInfo.FullCommandName;
} else {
cmd.CommandType = CommandType.Text;
cmd.CommandText = commandInfo.Sql;
}
//Create parameters collection
if(commandInfo.IsTemplatedSql) {
} else {
for(int index = 0; index < commandInfo.Parameters.Count; index++)
_driver.AddParameter(cmd, commandInfo.Parameters[index]);
}
return cmd;
}
// Sets parameter values.
private void SetCommandParameterValues(DbCommandInfo commandInfo, IDbCommand command, object[] args)
{
for(int i = 0; i < commandInfo.Parameters.Count; i++) {
var prmInfo = commandInfo.Parameters[i];
if(prmInfo.ArgIndex >= 0) {
var prm = (IDbDataParameter)command.Parameters[i];
var v = args[prmInfo.ArgIndex];
var conv = prmInfo.TypeInfo.PropertyToColumnConverter;
if(v != null && conv != null)
v = conv(v);
prm.Value = v;
}
} //for i
}
protected override async Task <object> Insert <TEntity>(Expression <Func <TEntity> > content, string table, bool @async)
{
PublicHelper.CheckNull(content);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
if (typeDescriptor.PrimaryKeys.Count > 1)
{
/* 对于多主键的实体，暂时不支持调用这个方法进行插入 */
throw new NotSupportedException(string.Format("Can not call this method because entity '{0}' has multiple keys.", typeDescriptor.Definition.Type.FullName));
}
PrimitivePropertyDescriptor keyPropertyDescriptor = typeDescriptor.PrimaryKeys.FirstOrDefault();
Dictionary <MemberInfo, Expression> insertColumns = InitMemberExtractor.Extract(content);
DbTable dbTable = PublicHelper.CreateDbTable(typeDescriptor, table);
DefaultExpressionParser expressionParser = typeDescriptor.GetExpressionParser(dbTable);
DbInsertExpression insertExp = new DbInsertExpression(dbTable);
object keyVal = null;
foreach (var kv in insertColumns)
{
MemberInfo key = kv.Key;
PrimitivePropertyDescriptor propertyDescriptor = typeDescriptor.GetPrimitivePropertyDescriptor(key);
if (propertyDescriptor.IsAutoIncrement)
{
throw new ChloeException(string.Format("Could not insert value into the identity column '{0}'.", propertyDescriptor.Column.Name));
}
if (propertyDescriptor.HasSequence())
{
throw new ChloeException(string.Format("Can not insert value into the column '{0}', because it's mapping member has define a sequence.", propertyDescriptor.Column.Name));
}
if (propertyDescriptor.IsPrimaryKey)
{
object val = ExpressionEvaluator.Evaluate(kv.Value);
if (val == null)
{
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", propertyDescriptor.Property.Name));
}
else
{
keyVal = val;
insertExp.InsertColumns.Add(propertyDescriptor.Column, DbExpression.Parameter(keyVal));
continue;
}
}
insertExp.InsertColumns.Add(propertyDescriptor.Column, expressionParser.Parse(kv.Value));
}
foreach (var item in typeDescriptor.PrimitivePropertyDescriptors.Where(a => a.HasSequence()))
{
DbMethodCallExpression getNextValueForSequenceExp = PublicHelper.MakeNextValueForSequenceDbExpression(item, dbTable.Schema);
insertExp.InsertColumns.Add(item.Column, getNextValueForSequenceExp);
}
if (keyPropertyDescriptor != null)
{
//主键为空并且主键又不是自增列
if (keyVal == null && !keyPropertyDescriptor.IsAutoIncrement && !keyPropertyDescriptor.HasSequence())
{
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", keyPropertyDescriptor.Property.Name));
}
}
if (keyPropertyDescriptor == null)
{
await this.ExecuteNonQuery(insertExp, @async);
return(keyVal); /* It will return null if an entity does not define primary key. */
}
if (!keyPropertyDescriptor.IsAutoIncrement && !keyPropertyDescriptor.HasSequence())
{
await this.ExecuteNonQuery(insertExp, @async);
return(keyVal);
}
insertExp.Returns.Add(keyPropertyDescriptor.Column);
IDbExpressionTranslator translator = this.DatabaseProvider.CreateDbExpressionTranslator();
DbCommandInfo dbCommandInfo = translator.Translate(insertExp);
object ret = this.Session.ExecuteScalar(dbCommandInfo.CommandText, dbCommandInfo.GetParameters());
if (ret == null || ret == DBNull.Value)
{
throw new ChloeException("Unable to get the identity/sequence value.");
}
ret = PublicHelper.ConvertObjectType(ret, typeDescriptor.AutoIncrement.PropertyType);
return(ret);
}
protected void ReadCrudOutputParameterValues(IDbCommand command, DbCommandInfo commandInfo, EntityRecord record)
{
for (int i = 0; i < commandInfo.OutputParameters.Count; i++) {
var prmInfo = commandInfo.OutputParameters[i];
var col = prmInfo.SourceColumn;
if (col == null) continue;
var prm = command.Parameters[prmInfo.Name] as IDbDataParameter;
var value = prm.Value;
var conv = prmInfo.TypeInfo.ColumnToPropertyConverter;
if (value != DBNull.Value && conv != null)
value = conv(value);
record.ValuesModified[col.Member.ValueIndex] = value;
}//for
}
protected virtual string GetGrantStatement(DbCommandInfo command)
{
return(null);
}
//Tag sample: "CRUD/[books].[BookOrderLine]/Delete"
// Note: we need to link Proc to table at load time, in case we will be deleting table - we also need to delete crud procs
protected virtual void LinkCrudProcToTable(DbCommandInfo command, string tag)
{
var arr = tag.SplitNames('/');
var tblName = arr[1];
var tbl = Model.GetTable(tblName);
if(tbl != null) {
command.Table = tbl;
tbl.CrudCommands.Add(command);
}
}
protected virtual void LoadRoutines()
{
var supportsRoutines = Driver.Supports(DbFeatures.StoredProcedures);
if (!supportsRoutines) return;
var data = GetRoutines();
bool hasCustomTag = data.Columns.Contains("ROUTINE_CUSTOM");
foreach (DataRow row in data.Rows) {
var schema = row.GetAsString("ROUTINE_SCHEMA");
if (!IncludeSchema(schema)) continue;
var name = row.GetAsString("ROUTINE_NAME");
var procText = row.GetAsString("ROUTINE_DEFINITION");
var descrTag = GetProcedureTag(procText);
//if will be added to model.Commands automatically
var dbCommand = new DbCommandInfo(Model, schema, name, null, descrTag);
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.StoredProcText = procText;
dbCommand.SourceHash = SourceHasher.ExtractHash(procText);
//detect if it is auto-generated
if (!string.IsNullOrEmpty(descrTag)) {
dbCommand.DescriptiveTag = descrTag;
dbCommand.Flags |= DbCommandFlags.AutoGenerated;
if (descrTag.StartsWith("CRUD"))
LinkCrudProcToTable(dbCommand, descrTag);
}
if (hasCustomTag)
dbCommand.CustomTag = row["ROUTINE_CUSTOM"];
}//foreach
}
public override DbCommandInfo BuildSqlSequenceGetNextCommand(DbSequenceInfo sequence)
{
const string SqlTemplate = "SELECT nextval('{0}.\"{1}\"');"; //note sequence name in double quotes inside single-quote argument
//Load by primary key
var cmdName = sequence.Name + "_GetNextValue";
var cmdInfo = new DbCommandInfo(DbModel, sequence.Schema, cmdName, null, null);
cmdInfo.Sql = string.Format(SqlTemplate, sequence.Schema, sequence.Name);
cmdInfo.ExecutionType = DbExecutionType.Scalar;
return cmdInfo;
}
public override bool ConvertToStoredProc(DbCommandInfo command)
{
switch (command.Kind) {
case EntityCommandKind.SelectAll:
case EntityCommandKind.SelectAllPaged:
case EntityCommandKind.SelectByKey:
case EntityCommandKind.SelectByKeyManyToMany:
return ConvertQueryToStoredProc(command);
case EntityCommandKind.SelectByKeyArray:
return ConvertQueryToStoredProc(command);
default:
return ConvertNonQueryToStoredProc(command);
}
}
//Creates default implementation for servers that do not support array parameters.
// The command is implemented as templated SQL
public virtual DbCommandInfo BuildSelectByKeyArrayCommand(EntityCommand entityCommand)
{
const string SqlSelectByFkTemplate = @"
SELECT {0} {1}
FROM {2}
WHERE {3}
{4}";
var table = DbModel.LookupDbObject<DbTableInfo>(entityCommand.TargetEntityInfo, throwNotFound: true);
var dbKey = DbModel.LookupDbObject<DbKeyInfo>(entityCommand.SelectKey);
if (dbKey.KeyColumns.Count > 1)
return null;
var keyCol = dbKey.KeyColumns[0].Column.ColumnName;
var cmdName = ModelConfig.NamingPolicy.ConstructDbCommandName(entityCommand, table.TableName, "SelectByArrayOf_", keyCol);
var descrTag = GetDbCommandDescriptiveTag(entityCommand);
var cmdInfo = new DbCommandInfo(entityCommand, cmdName, table, DbExecutionType.Reader, null, descrTag);
cmdInfo.IsTemplatedSql = true;
//Build column list
var outColumns = table.Columns.GetSelectable();
var strColumns = outColumns.GetSqlNameList();
//build WHERE clause
var whereExpr = '"' + keyCol + '"' + " IN ({0})"; //this {0} will remain in a template
if (!string.IsNullOrWhiteSpace(entityCommand.Filter))
whereExpr = whereExpr + " AND " + ProcessFilter(entityCommand, table);
string orderByExpr = null;
if (dbKey.KeyType == KeyType.PrimaryKey)
orderByExpr = null;
else
orderByExpr = BuildOrderBy(table.DefaultOrderBy);
string strTop = string.Empty;
var sql = string.Format(SqlSelectByFkTemplate, strTop, strColumns, table.FullName, whereExpr, orderByExpr);
sql = sql.Trim() + ";";
cmdInfo.Sql = sql;
cmdInfo.EntityMaterializer = CreateEntityMaterializer(table, outColumns);
//Create parameter for just-in-time formatting of SQL
var entPrm = entityCommand.Parameters[0];
Type elemType;
Util.Check(entPrm.DataType.IsListOfDbPrimitive(out elemType), "Parameter is not list of primitives.");
var elemTypeInfo = GetDbTypeInfo(elemType, 0);
Util.Check(elemTypeInfo != null, "Failed to get db type information for type {0}.", elemType);
var prm = new DbParamInfo(entPrm, "(array)", 0);
prm.ToLiteralConverter = (list) => ConvertList(list, elemTypeInfo);
cmdInfo.Parameters.Add(prm);
return cmdInfo;
}
protected override async Task <TEntity> Insert <TEntity>(TEntity entity, string table, bool @async)
{
PublicHelper.CheckNull(entity);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
DbTable dbTable = PublicHelper.CreateDbTable(typeDescriptor, table);
List <PrimitivePropertyDescriptor> outputColumns = new List <PrimitivePropertyDescriptor>();
Dictionary <PrimitivePropertyDescriptor, DbExpression> insertColumns = new Dictionary <PrimitivePropertyDescriptor, DbExpression>();
foreach (PrimitivePropertyDescriptor propertyDescriptor in typeDescriptor.PrimitivePropertyDescriptors)
{
if (propertyDescriptor.IsAutoIncrement)
{
outputColumns.Add(propertyDescriptor);
continue;
}
if (propertyDescriptor.HasSequence())
{
DbMethodCallExpression getNextValueForSequenceExp = PublicHelper.MakeNextValueForSequenceDbExpression(propertyDescriptor, dbTable.Schema);
insertColumns.Add(propertyDescriptor, getNextValueForSequenceExp);
outputColumns.Add(propertyDescriptor);
continue;
}
object val = propertyDescriptor.GetValue(entity);
PublicHelper.NotNullCheck(propertyDescriptor, val);
DbExpression valExp = DbExpression.Parameter(val, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType);
insertColumns.Add(propertyDescriptor, valExp);
}
DbInsertExpression e = new DbInsertExpression(dbTable);
foreach (var kv in insertColumns)
{
e.InsertColumns.Add(kv.Key.Column, kv.Value);
}
e.Returns.AddRange(outputColumns.Select(a => a.Column));
DbCommandInfo dbCommandInfo = this.Translate(e);
await this.ExecuteNonQuery(dbCommandInfo, @async);
List <DbParam> outputParams = dbCommandInfo.Parameters.Where(a => a.Direction == ParamDirection.Output).ToList();
for (int i = 0; i < outputColumns.Count; i++)
{
PrimitivePropertyDescriptor propertyDescriptor = outputColumns[i];
string putputColumnName = Utils.GenOutputColumnParameterName(propertyDescriptor.Column.Name);
DbParam outputParam = outputParams.Where(a => a.Name == putputColumnName).First();
var outputValue = PublicHelper.ConvertObjectType(outputParam.Value, propertyDescriptor.PropertyType);
outputColumns[i].SetValue(entity, outputValue);
}
return(entity);
}
public override DbCommandInfo BuildSqlSequenceGetNextCommand(DbSequenceInfo sequence)
{
const string SqlTemplate = "SELECT NEXT VALUE FOR {0};";
//Load by primary key
var cmdName = sequence.Name + "_GetNextValue";
var cmdInfo = new DbCommandInfo(DbModel, sequence.Schema, cmdName, null, null);
cmdInfo.Sql = string.Format(SqlTemplate, sequence.FullName);
cmdInfo.ExecutionType = DbExecutionType.Scalar;
return cmdInfo;
}
private void SetCrudCommandParameterValues(DbCommandInfo commandInfo, IDbCommand command, EntityRecord record)
{
if (record.Status == EntityStatus.Stub)
record.Reload();
for (int i = 0; i < commandInfo.Parameters.Count; i++) {
var prm = (IDbDataParameter)command.Parameters[i];
prm.Value = DBNull.Value;
var prmInfo = commandInfo.Parameters[i];
var isInput = prmInfo.Direction == ParameterDirection.Input || prmInfo.Direction == ParameterDirection.InputOutput;
if (!isInput) continue;
var col = prmInfo.SourceColumn;
if (col == null || col.Member == null) continue;
var value = record.GetValueDirect(col.Member);
if(value == null)
value = DBNull.Value;
var conv = prmInfo.TypeInfo.PropertyToColumnConverter;
if (value != DBNull.Value && conv != null)
value = conv(value);
prm.Value = value;
} //for i
}
public override bool ConvertToStoredProc(DbCommandInfo command)
{
const string ProcBodyTemplate =
@"-- Description: {0}
{1}
{2}
{3}
";
const string CreateProcTemplate = @"CREATE PROCEDURE {0}
{1}
AS
BEGIN
SET NOCOUNT ON;
{2}
END
";
var table = command.Table;
command.Schema = table.Schema;
//Build command that creates stored proc and execute it
var listParams = new StringList();
foreach (var prm in command.Parameters) {
var strOut = (prm.Direction & ParameterDirection.Output) != 0 ? " OUTPUT" : string.Empty;
// Add READONLY for table-type parameters
var strReadOnly = (prm.TypeInfo.VendorDbType.VendorDbType == (int)SqlDbType.Structured) ? " READONLY" : string.Empty;
var prmSpec = " " + prm.Name + " " + prm.TypeInfo.SqlTypeSpec + strReadOnly + strOut;
listParams.Add(prmSpec);
}
var strParams = string.Join(",\r\n", listParams);
var desc = command.EntityCommand.Description;
var tag = DbDriver.GeneratedCrudProcTagPrefix + command.DescriptiveTag;
command.SourceHash = SourceHasher.ComputeHash(command.FullCommandName, strParams, command.Sql);
command.StoredProcBody = string.Format(ProcBodyTemplate, desc, tag, command.Sql, SourceHasher.GetHashLine(command.SourceHash));
command.StoredProcText = string.Format(CreateProcTemplate, command.FullCommandName, strParams, command.StoredProcBody);
return true;
}
protected override async Task <TEntity> Insert <TEntity>(TEntity entity, string table, bool @async)
{
PublicHelper.CheckNull(entity);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
DbTable dbTable = PublicHelper.CreateDbTable(typeDescriptor, table);
Dictionary <PrimitivePropertyDescriptor, object> keyValueMap = PrimaryKeyHelper.CreateKeyValueMap(typeDescriptor);
Dictionary <PrimitivePropertyDescriptor, DbExpression> insertColumns = new Dictionary <PrimitivePropertyDescriptor, DbExpression>();
foreach (PrimitivePropertyDescriptor propertyDescriptor in typeDescriptor.PrimitivePropertyDescriptors)
{
if (propertyDescriptor.IsAutoIncrement)
{
continue;
}
if (propertyDescriptor.HasSequence())
{
DbMethodCallExpression getNextValueForSequenceExp = PublicHelper.MakeNextValueForSequenceDbExpression(propertyDescriptor, dbTable.Schema);
insertColumns.Add(propertyDescriptor, getNextValueForSequenceExp);
continue;
}
object val = propertyDescriptor.GetValue(entity);
PublicHelper.NotNullCheck(propertyDescriptor, val);
if (propertyDescriptor.IsPrimaryKey)
{
keyValueMap[propertyDescriptor] = val;
}
DbParameterExpression valExp = DbExpression.Parameter(val, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType);
insertColumns.Add(propertyDescriptor, valExp);
}
PrimitivePropertyDescriptor nullValueKey = keyValueMap.Where(a => a.Value == null && !a.Key.IsAutoIncrement).Select(a => a.Key).FirstOrDefault();
if (nullValueKey != null)
{
/* 主键为空并且主键又不是自增列 */
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", nullValueKey.Property.Name));
}
DbInsertExpression insertExp = new DbInsertExpression(dbTable);
foreach (var kv in insertColumns)
{
insertExp.InsertColumns.Add(kv.Key.Column, kv.Value);
}
List <Action <TEntity, IDataReader> > mappers = new List <Action <TEntity, IDataReader> >();
foreach (var item in typeDescriptor.PrimitivePropertyDescriptors.Where(a => a.IsAutoIncrement || a.HasSequence()))
{
mappers.Add(GetMapper <TEntity>(item, insertExp.Returns.Count));
insertExp.Returns.Add(item.Column);
}
if (mappers.Count == 0)
{
await this.ExecuteNonQuery(insertExp, @async);
return(entity);
}
IDbExpressionTranslator translator = this.DatabaseProvider.CreateDbExpressionTranslator();
DbCommandInfo dbCommandInfo = translator.Translate(insertExp);
IDataReader dataReader = this.Session.ExecuteReader(dbCommandInfo.CommandText, dbCommandInfo.GetParameters());
using (dataReader)
{
dataReader.Read();
foreach (var mapper in mappers)
{
mapper(entity, dataReader);
}
}
return(entity);
}
//Note: be careful not to introduce trailing spaces, esp. when some of the template args are empty;
// latest PG driver cuts them off.
// TODO: need better solution for Postgres trailing spaces
private bool ConvertNonQueryToStoredProc(DbCommandInfo command)
{
var table = command.Table;
//TODO: refactor to return #of records
string ProcBodyTemplate =
@"{0}
BEGIN
{1}{2};
{3}
END;";
const string CreateProcTemplate = @"
CREATE OR REPLACE FUNCTION {0}({1}) {2} AS
$$
{3}
$$
LANGUAGE plpgsql;
";
// In Postgres, if function has output parameter(s), it must return scalar or record. If there's no out parameters, you must specify
// something as return type - we specify VOID. If we have out param(s), we skip 'returns' clause, and Postgres adds it automatically.
// We have OUT parameter for identity field; we retrieving Identity value for INSERT using ' ... RETURNING "Id" INTO p_id; ' clause.
// Insert SQL has already "... RETURNING "Id" ' clause - we add only ' INTO p_Id ' extra.
command.Schema = table.Schema;
var listParams = new StringList();
bool hasOut = false;
var lstTargets = new List<string>();
foreach (var prm in command.Parameters) {
string strOut = string.Empty;
if ((prm.Direction & ParameterDirection.Output) != 0) {
hasOut = true;
strOut = "OUT ";
var col = prm.SourceColumn;
if (col != null && col.Flags.IsSet(DbColumnFlags.Identity)) {
lstTargets.Add(prm.Name);
}
}
listParams.Add(" " + strOut + prm.Name + " " + prm.TypeInfo.SqlTypeSpec);
}
string strReturns = hasOut ? string.Empty : "RETURNS VOID";
var strParams = listParams.Count > 0 ? "\r\n " + string.Join(",\r\n ", listParams) + "\r\n" : string.Empty;
var header = BuildProcHeader(command);
var strRetInto = string.Empty;
if (lstTargets.Count > 0)
strRetInto = "\r\n INTO " + string.Join(", ", lstTargets);
var sql = command.Sql.TrimEnd(' ', '\r', '\n', ';'); //trim ending semicolon so that we can append RETURNING clause
command.SourceHash = SourceHasher.ComputeHash(command.FullCommandName, strParams, sql);
command.StoredProcBody = string.Format(ProcBodyTemplate, header, sql, strRetInto, SourceHasher.GetHashLine(command.SourceHash));
command.StoredProcText =
string.Format(CreateProcTemplate, command.FullCommandName, strParams, strReturns, command.StoredProcBody);
return true;
}
private void FormatTemplatedSql(DbCommandInfo commandInfo, IDbCommand command, object[] args)
{
if(args == null || args.Length == 0)
return;
var values = new string[args.Length];
for(int i = 0; i < commandInfo.Parameters.Count; i++) {
var prmInfo = commandInfo.Parameters[i];
if(prmInfo.ArgIndex >= 0) {
var v = args[prmInfo.ArgIndex];
var strV = prmInfo.ToLiteralConverter(v);
values[i] = strV;
}
command.CommandText = string.Format(commandInfo.Sql, values);
} //for i
}
// Stored procedures ==============================================================
public virtual bool ConvertToStoredProc(DbCommandInfo command)
{
Util.Throw("Stored procedures are not supported by driver {0}", this.GetType().Name);
return false;
}
// See examples of stored procs at the end of this file
private bool ConvertQueryToStoredProc(DbCommandInfo command)
{
var table = command.Table;
string ProcBodyTemplate =
@"{0}
DECLARE
ref1 refcursor;
BEGIN
OPEN ref1 FOR
{1}
RETURN ref1;
{2}
END;
";
string CreateProcTemplate =
@"{0}
CREATE OR REPLACE FUNCTION {1} ({2}) RETURNS refcursor AS $$
{3}
$$ LANGUAGE plpgsql;
";
command.Schema = table.Schema;
//Build command that creates stored proc and execute it
var listParams = new StringList();
foreach (var prm in command.Parameters) {
var strOut = (prm.Direction & ParameterDirection.Output) != 0 ? " OUT" : string.Empty;
listParams.Add(" " + strOut + prm.Name + " " + prm.TypeInfo.SqlTypeSpec);
}
var strParams = listParams.Count > 0 ? "\r\n " + string.Join(",\r\n ", listParams) + "\r\n" : string.Empty;
var header = BuildProcHeader(command);
command.SourceHash = SourceHasher.ComputeHash(command.FullCommandName, strParams, command.Sql);
command.StoredProcBody = string.Format(ProcBodyTemplate, header, command.Sql, SourceHasher.GetHashLine(command.SourceHash));
command.StoredProcText =
string.Format(CreateProcTemplate, header, command.FullCommandName, strParams, command.StoredProcBody);
return true;
}
private string BuildProcHeader(DbCommandInfo command)
{
var entCommand = command.EntityCommand;
//table/category/operation; for ex: Product/CRUD/Update
const string ProcHeaderLineTemplate =
@"{0}{1}
-- Description: {2}
";
return string.Format(ProcHeaderLineTemplate, DbDriver.GeneratedCrudProcTagPrefix, command.DescriptiveTag, entCommand.Description);
}
protected virtual async Task <object> Insert <TEntity>(Expression <Func <TEntity> > content, string table, bool @async)
{
PublicHelper.CheckNull(content);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
if (typeDescriptor.PrimaryKeys.Count > 1)
{
/* 对于多主键的实体，暂时不支持调用这个方法进行插入 */
throw new NotSupportedException(string.Format("Can not call this method because entity '{0}' has multiple keys.", typeDescriptor.Definition.Type.FullName));
}
PrimitivePropertyDescriptor keyPropertyDescriptor = typeDescriptor.PrimaryKeys.FirstOrDefault();
Dictionary <MemberInfo, Expression> insertColumns = InitMemberExtractor.Extract(content);
DbTable explicitDbTable = null;
if (table != null)
{
explicitDbTable = new DbTable(table, typeDescriptor.Table.Schema);
}
DefaultExpressionParser expressionParser = typeDescriptor.GetExpressionParser(explicitDbTable);
DbInsertExpression e = new DbInsertExpression(explicitDbTable ?? typeDescriptor.Table);
object keyVal = null;
foreach (var kv in insertColumns)
{
MemberInfo key = kv.Key;
PrimitivePropertyDescriptor propertyDescriptor = typeDescriptor.GetPrimitivePropertyDescriptor(key);
if (propertyDescriptor.IsAutoIncrement)
{
throw new ChloeException(string.Format("Could not insert value into the identity column '{0}'.", propertyDescriptor.Column.Name));
}
if (propertyDescriptor.IsPrimaryKey)
{
object val = ExpressionEvaluator.Evaluate(kv.Value);
if (val == null)
{
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", propertyDescriptor.Property.Name));
}
else
{
keyVal = val;
e.InsertColumns.Add(propertyDescriptor.Column, DbExpression.Parameter(keyVal, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType));
continue;
}
}
e.InsertColumns.Add(propertyDescriptor.Column, expressionParser.Parse(kv.Value));
}
if (keyPropertyDescriptor != null)
{
//主键为空并且主键又不是自增列
if (keyVal == null && !keyPropertyDescriptor.IsAutoIncrement)
{
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", keyPropertyDescriptor.Property.Name));
}
}
if (keyPropertyDescriptor == null || !keyPropertyDescriptor.IsAutoIncrement)
{
await this.ExecuteNonQuery(e, @async);
return(keyVal); /* It will return null if an entity does not define primary key. */
}
IDbExpressionTranslator translator = this.DatabaseProvider.CreateDbExpressionTranslator();
DbCommandInfo dbCommandInfo = translator.Translate(e);
dbCommandInfo.CommandText = string.Concat(dbCommandInfo.CommandText, ";", this.GetSelectLastInsertIdClause());
//SELECT @@IDENTITY 返回的是 decimal 类型
object retIdentity = await this.ExecuteScalar(dbCommandInfo, @async);
if (retIdentity == null || retIdentity == DBNull.Value)
{
throw new ChloeException("Unable to get the identity value.");
}
retIdentity = PublicHelper.ConvertObjectType(retIdentity, typeDescriptor.AutoIncrement.PropertyType);
return(retIdentity);
}
//Note: as of May/13, MySql does not preserve comments preceding SP header; they are going to patch this.
// For now, we put tag in a comment right after BEGIN
public override bool ConvertToStoredProc(DbCommandInfo command)
{
var table = command.Table;
string ProcBodyTemplate =
@"BEGIN
{0}
-- Description: {1}
{2}
{3}
END";
string CreateProcTemplate =
@"CREATE PROCEDURE {0}(
{1})
SQL SECURITY INVOKER
{2}
";
command.Schema = table.Schema;
//Build command that creates stored proc and execute it
var listParams = new StringList();
foreach (var prm in command.Parameters) {
var strOut = (prm.Direction & ParameterDirection.Output) != 0 ? "OUT " : string.Empty;
var prmSpec = string.Format(" {0}{1} {2}", strOut, prm.Name, prm.TypeInfo.SqlTypeSpec);
listParams.Add(prmSpec);
}
var strParams = string.Join(",\r\n", listParams);
var tag = DbDriver.GeneratedCrudProcTagPrefix + command.DescriptiveTag;
command.SourceHash = SourceHasher.ComputeHash(command.FullCommandName, strParams, command.Sql);
command.StoredProcBody = string.Format(ProcBodyTemplate, tag, command.Description, command.Sql, SourceHasher.GetHashLine(command.SourceHash));
command.StoredProcText = string.Format(CreateProcTemplate, command.FullCommandName, strParams, command.StoredProcBody);
return true;
}
