public SqlDefinition (string cmdText, CommandType cmdType, SqlMetaData[] metadata, ParameterDirection[] parmDirection)
{
this.cmd = null;
this.cmdText = cmdText;
this.cmdType = cmdType;
this.metadata = metadata;
this.parmDirection = parmDirection;
}
public override void Append(ISqlCommand command)
{
Commands.Add(command);
_sqlString = _sqlString.Append("\nOPEN :cursor")
.Append(Convert.ToString(_cursorCount++))
.Append("\nFOR\n")
.Append(command.Query).Append("\n;\n");
}
public Controller()
{
ConnectionStringSettingsCollection settings = ConfigurationManager.ConnectionStrings;
_connectionString = settings[2].ConnectionString;
ExecuteButtonClickCommand = new Command(ExecuteButtonClick);
Parameters = new ObservableCollection<CustomSqlParameter>() {new CustomSqlParameter() {Value = "Markov"}};
if (ConfigurationManager.AppSettings["ConnectionType"] == "ODBC")
{
SqlCommand = new OdbcSqlCommand();
}
else if (ConfigurationManager.AppSettings["ConnectionType"] == "OLEDB")
{
SqlCommand = new OleDbSqlCommand();
}
}
public async Task<IDataReader> ExecuteReaderAsync(ISqlCommand sqlCommand)
{
if (sqlCommand == null)
{
throw new ArgumentNullException("sqlCommand");
}
var connection = new SqlConnection(this.connectionString);
try
{
await connection.OpenAsync().ConfigureAwait(false);
using (var command = sqlCommand.GetCommand())
{
command.Connection = connection;
var watch = new Stopwatch();
watch.Start();
var reader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection);
watch.Stop();
this.logger.LogInformation("SQL query {0} was done in {1} ms.", new object[] { command.CommandText + " AdoNet Async", watch.ElapsedMilliseconds });
return reader;
}
}
catch (Exception exception)
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
this.logger.LogError(exception.Message);
throw;
}
}
internal override T QueryScalar <T>(ISqlCommand command, String connectionName)
{
return(ExecuteScalar(CreateDbCommand(command, connectionName), connectionName).ConvertTo <T>());
}
internal override DataSet QueryDataSet(ISqlCommand command, String connectionName)
{
return(ExecuteDataSet(CreateDbCommand(command, connectionName), connectionName));
}
public void ExecuteCommand(ISqlCommand sqlCommand)
{
QueryCount++;
sqlCommand.Execute(CreateCommand(null));
sqlConnection.Close();
}
public void _runSelect(ISqlCommand nSqlCommand)
{
nSqlCommand._serialize(ref mAccountId, @"accountId");
nSqlCommand._serialize(ref mTicks, @"ticks");
nSqlCommand._serialize(ref mBytes, @"statusIds");
}
protected void AddParams_Having(ISqlObjectFactory factory, ISqlCommand sqlCommand, int?commandCount)
{
AddParams_Having(factory, sqlCommand, SqlHavingCollection, commandCount);
}
public void _runWhere(ISqlCommand nSqlCommand)
{
}
private void Build_InsertStatement(
ISqlObjectFactory factory,
SqlContainer sqlContainer,
ISqlCommand sqlCommand,
StringBuilder commandText,
int?commandCount)
{
var tableBracket = TableBracket;
switch (TableType)
{
case Sqls.TableTypes.History: tableBracket = HistoryTableBracket; break;
case Sqls.TableTypes.Deleted: tableBracket = DeletedTableBracket; break;
}
var columnNameCollection = new List <string>();
var valueCollection = new List <string>();
if (AddUpdatorParam)
{
columnNameCollection.Add("\"Creator\"");
columnNameCollection.Add("\"Updator\"");
valueCollection.Add($"{Parameters.Parameter.SqlParameterPrefix}U");
valueCollection.Add($"{Parameters.Parameter.SqlParameterPrefix}U");
}
SqlParamCollection?
.Where(o => o.Using)
.ForEach(sqlParam =>
{
columnNameCollection.Add(sqlParam.ColumnBracket);
if (!sqlParam.Raw.IsNullOrEmpty())
{
switch (sqlParam.Raw?.ToString())
{
case "@@identity":
valueCollection.Add($"{Parameters.Parameter.SqlParameterPrefix}I");
break;
default:
valueCollection.Add(sqlParam.Raw);
break;
}
}
else if (sqlParam.Sub != null)
{
valueCollection.Add("(" + sqlParam.Sub.GetCommandText(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
prefix: "_sub",
commandCount: commandCount) + ")");
}
else
{
valueCollection.Add("@" + sqlParam.VariableName + commandCount
.ToString());
}
});
commandText.Append(
"insert into ",
tableBracket,
"(", columnNameCollection.Join(), ") ",
Values(
factory: factory,
valueCollection: valueCollection,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandCount: commandCount));
}
public void _runSelect(ISqlCommand nSqlCommand)
{
nSqlCommand._serialize(ref mNickName, @"nickName");
nSqlCommand._serialize(ref mPassward, @"passward");
nSqlCommand._serialize(ref mTicks, @"createTime");
}
public override void BuildCommandText(
ISqlObjectFactory factory,
SqlContainer sqlContainer,
ISqlCommand sqlCommand,
StringBuilder commandText,
int?commandCount = null)
{
switch (TableType)
{
case Sqls.TableTypes.History:
AddTableTypeColumn("History");
BuildHistoryWithoutFlag(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount,
unionType: Sqls.UnionTypes.None);
break;
case Sqls.TableTypes.HistoryWithoutFlag:
BuildHistoryWithoutFlag(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount,
unionType: Sqls.UnionTypes.None);
break;
case Sqls.TableTypes.NormalAndDeleted:
BuildNormalAndDeleted(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount);
break;
case Sqls.TableTypes.NormalAndHistory:
BuildNormalAndHistory(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount);
break;
case Sqls.TableTypes.Deleted:
BuildDeleted(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount,
unionType: Sqls.UnionTypes.None);
break;
case Sqls.TableTypes.All:
AddTableTypeColumn("Normal");
BuildNormal(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount);
BuildDeleted(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount,
unionType: Sqls.UnionTypes.Union);
AddTableTypeColumn("History");
BuildHistoryWithoutFlag(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount,
unionType: Sqls.UnionTypes.Union);
break;
default:
BuildNormal(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount);
break;
}
}
public void _runSelect(ISqlCommand nSqlCommand)
{
nSqlCommand._serialize(ref mAccountMgrId, @"accountMgrId");
nSqlCommand._serialize(ref mTableId, @"tableId");
}
public ISqlDataAdapter CreateSqlDataAdapter(ISqlCommand sqlCommand)
{
return GetSqlObjectFactory().CreateSqlDataAdapter(sqlCommand);
}
public string Sql(
ISqlObjectFactory factory,
SqlContainer sqlContainer,
ISqlCommand sqlCommand,
string tableBracket,
int?commandCount,
bool select)
{
if (Using)
{
var left = Left(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandCount: commandCount);
if (!Raw.IsNullOrEmpty())
{
return(Sql_Raw(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
left: left,
tableBracket: tableBracket,
commandCount: commandCount,
select: select));
}
else if (Sub != null)
{
return(Sql_Sub(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
left: left,
tableBracket: tableBracket,
commandCount: commandCount,
select: select));
}
else if (And != null)
{
return(Sql_And(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandCount: commandCount,
select: select));
}
else if (Or != null)
{
return(Sql_Or(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
tableBracket: tableBracket,
commandCount: commandCount,
select: select));
}
else
{
return(Sql_General(
left: left,
tableBracket: tableBracket,
commandCount: commandCount,
select: select));
}
}
else
{
return(string.Empty);
}
}
internal abstract T QueryScalar <T>(ISqlCommand command, String connectionName);
internal abstract IDataReader QueryReader(ISqlCommand command, String connectionName);
internal abstract DataSet QueryDataSet(ISqlCommand command, String connectionName);
public virtual void Append(ISqlCommand command)
{
Commands.Add(command);
sqlString = sqlString.Append(command.Query).Append(";").Append(Environment.NewLine);
}
/// <summary>
/// Execute the command and return a result. Commands will not
/// generate an IDataReader or IDataResults, so results will either need to be calculated or
/// derived from output parameters. Maps internally to a call to IDbCommand.ExecuteNonQuery()
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="runner"></param>
/// <param name="command"></param>
/// <returns></returns>
public static Task <T> ExecuteAsync <T>(this ISqlRunner runner, ISqlCommand <T> command, CancellationToken cancellationToken = new CancellationToken())
{
Argument.NotNull(runner, nameof(runner));
Argument.NotNull(command, nameof(command));
return(runner.ExecuteAsync(c => new SqlCommandStrategy(runner.InteractionFactory).ExecuteAsync(command, c, 0, cancellationToken)));
}
public void _runSelect(ISqlCommand nSqlCommand)
{
nSqlCommand._serialize(ref mStatusOptionBs, @"statusOptionBs");
}
/// <summary>
/// Execute the command and return a result. Commands will not
/// generate an IDataReader or IDataResults, so results will either need to be calculated or
/// derived from output parameters. Maps internally to a call to IDbCommand.ExecuteNonQuery()
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="runner"></param>
/// <param name="command"></param>
/// <returns></returns>
public static T Execute <T>(this ISqlRunner runner, ISqlCommand <T> command)
{
Argument.NotNull(runner, nameof(runner));
Argument.NotNull(command, nameof(command));
return(runner.Execute(c => new SqlCommandStrategy(runner.InteractionFactory).Execute(command, c, 0)));
}
protected void AddParams_Where(ISqlObjectFactory factory, ISqlCommand sqlCommand, int?commandCount)
{
AddParams_Where(factory, sqlCommand, SqlWhereCollection, commandCount);
}
public void _runWhere(ISqlCommand nSqlCommand)
{
nSqlCommand._serialize(ref mAccountId, @"WHERE accountId=");
}
public void Validate(ShardingRule shardingRule, ISqlCommand sqlCommand, ParameterContext parameterContext)
{
Validate(shardingRule, (InsertCommand)sqlCommand, parameterContext);
}
public ISqlDataAdapter CreateSqlDataAdapter(ISqlCommand sqlCommand)
{
return(new PostgreSqlDataAdapter(sqlCommand));
}
public void _runWhere(ISqlCommand nSqlFormat)
{
}
protected virtual object DispatchInvoking(IMethodInvocation invocation)
{
MethodBase method = invocation.Method;
if (!method.IsAbstract)
{
return(invocation.Proceed());
}
bool logEnabled = IsLogEnabled();
// - - - - - - - - - - - - -
// Initialize DAO meta data
// - - - - - - - - - - - - -
if (method.Name.Equals("InitializeDaoMetaData"))
{
InitializeSqlCommand(invocation);
return(null); // The end! (Initilization Only)
}
// - - - - - - - - - - -
// Preprocess outsideSql
// - - - - - - - - - - -
PreprocessOutsideSql(invocation);
// - - - - - - - - - - - - -
// Preprocess conditionBean
// - - - - - - - - - - - - -
ConditionBean cb = PreprocessConditionBean(invocation);
// - - - - - - - - -
// Set up sqlCommand
// - - - - - - - - -
ISqlCommand cmd = null;
try {
DateTime?beforeCmd = null;
if (logEnabled)
{
beforeCmd = DateTime.Now;
}
cmd = FindSqlCommand(invocation);
if (logEnabled)
{
DateTime afterCmd = DateTime.Now;
if (!afterCmd.Equals(beforeCmd.Value))
{
LogSqlCommand(invocation, cmd, beforeCmd.Value, afterCmd);
}
}
} finally {
if (IsLogEnabled())
{
LogInvocation(invocation);
}
}
SqlResultHandler sqlResultHandler = GetSqlResultHander();
bool existsSqlResultHandler = sqlResultHandler != null;
DateTime? before = null;
if (logEnabled || existsSqlResultHandler)
{
before = DateTime.Now; // for performance view
}
// - - - - - - - - - -
// Execute sqlCommand!
// - - - - - - - - - -
object ret = null;
try {
ret = cmd.Execute(invocation.Arguments);
} catch (Exception e) {
if (e.GetType().Equals(typeof(NotSingleRowUpdatedRuntimeException)))
{
throw new EntityAlreadyUpdatedException((NotSingleRowUpdatedRuntimeException)e);
}
throw;
} finally {
PostprocessConditionBean(invocation, cb);
}
// - - - - - - - - - -
// Convert and Return!
// - - - - - - - - - -
Type retType = ((MethodInfo)method).ReturnType;
ret = Seasar.Framework.Util.ConversionUtil.ConvertTargetType(ret, retType);
if (logEnabled || existsSqlResultHandler)
{
DateTime after = DateTime.Now; // for performance view
if (logEnabled)
{
LogReturn(invocation, retType, ret, before.Value, after);
}
if (existsSqlResultHandler)
{
SqlResultInfo info = new SqlResultInfo();
info.Result = ret;
info.CommandName = method.Name;
info.DisplaySql = (String)InternalMapContext.GetObject("df:DisplaySql");
info.BeforeDateTime = before.Value;
info.AfterDateTime = after;
sqlResultHandler.Invoke(info);
}
}
return(ret);
}
internal override int ExecuteNonQuery(ISqlCommand command, String connectionName)
{
return(ExecuteNonQuery(CreateDbCommand(command, connectionName), connectionName));
}
public virtual void Append(ISqlCommand command)
{
Commands.Add(command);
sqlString = sqlString.Append(command.Query).Append(";").Append(Environment.NewLine);
}
internal override IDataReader QueryReader(ISqlCommand command, String connectionName)
{
return(ExecuteReader(CreateDbCommand(command, connectionName), connectionName));
}
public int ExecuteCommand(ISqlCommand sqlCommand, TransactionScopeOption transactionScopeOption)
{
if (sqlCommand == null)
{
throw new ArgumentNullException("sqlCommand");
}
int rowsAffected;
using (var scope = new TransactionScope(transactionScopeOption))
{
try
{
using (var sqlConnection = new SqlConnection(this.connectionString))
{
sqlConnection.Open();
using (var command = sqlCommand.GetCommand())
{
command.Connection = sqlConnection;
////string.Format(CultureInfo.InvariantCulture, "Executing Database command '{0}'.", command.CommandText).LogAsInformation();
rowsAffected = command.ExecuteNonQuery();
}
}
scope.Complete();
}
catch (Exception exception)
{
this.logger.LogError(exception.Message);
throw;
}
}
return rowsAffected;
}
// ===============================================================================
// Log SqlCommand
// ==============
protected void LogSqlCommand(IMethodInvocation invocation, ISqlCommand cmd, DateTime beforeCmd, DateTime afterCmd)
{
Log("SqlCommand Initialization Cost: [" + TraceViewUtil.ConvertToPerformanceView(beforeCmd, afterCmd) + "]");
}
internal abstract int ExecuteNonQuery(ISqlCommand command, String connectionName);
public SqlDefinition (ISqlCommand cmd)
{
this.cmd = cmd;
this.cmdText = cmd.CommandText;
this.cmdType = cmd.CommandType;
}
internal IDbCommand CreateStoredProcedureCommand(ISqlCommand command)
{
return(CreateCommand(CommandType.StoredProcedure, command));
}
internal IDbCommand CreateTextCommand(ISqlCommand sqlCommand)
{
return(CreateCommand(CommandType.Text, sqlCommand));
}
public int ExecuteCommand(ISqlCommand sqlCommand)
{
if (sqlCommand == null)
{
throw new ArgumentNullException("sqlCommand");
}
using (var connection = new SqlConnection(this.connectionString))
{
try
{
connection.Open();
using (var command = sqlCommand.GetCommand())
{
command.Connection = connection;
command.CommandTimeout = 1000;
////string.Format(CultureInfo.InvariantCulture, "Executing Database command '{0}'.", command.CommandText).LogAsInformation();
return command.ExecuteNonQuery();
}
}
catch (Exception exception)
{
this.logger.LogError(exception.Message);
throw;
}
}
}
