public ConnectorObjects(System.String ConnectionString, System.String ProcedureNameOrCommandText, System.Collections.Generic.List <System.Data.Common.DbParameter> Parameters, System.Data.CommandType CommandType, System.Boolean ExecuteForJSON, System.Boolean ShowPlan, System.Boolean ReadSummaries) : base(ConnectionString, ref ProcedureNameOrCommandText, Parameters, CommandType, ExecuteForJSON, ShowPlan, ReadSummaries)
{
this.Connection = new global::Oracle.ManagedDataAccess.Client.OracleConnection(ConnectionString);
this.Command = new global::Oracle.ManagedDataAccess.Client.OracleCommand(ProcedureNameOrCommandText, this.Connection)
{
CommandTimeout = SoftmakeAll.SDK.DataAccess.Oracle.Environment.CommandsTimeout,
CommandType = CommandType,
};
System.Boolean HasShowResultsTableParameter = false;
if ((Parameters != null) && (Parameters.Any()))
{
foreach (System.Data.Common.DbParameter Parameter in Parameters)
{
if (Parameter.ParameterName == "ShowResultsTable")
{
HasShowResultsTableParameter = true;
}
Parameter.Value = Parameter.Value ?? System.Convert.DBNull;
this.Command.Parameters.Add(Parameter);
}
}
if (!(ReadSummaries))
{
return;
}
if ((CommandType == System.Data.CommandType.StoredProcedure) && (!(HasShowResultsTableParameter)))
{
this.Command.Parameters.Add(new global::Oracle.ManagedDataAccess.Client.OracleParameter
{
Direction = System.Data.ParameterDirection.Input,
ParameterName = "ShowResultsTable",
OracleDbType = global::Oracle.ManagedDataAccess.Client.OracleDbType.Int32,
Size = 0,
Value = true
});
}
}
public int ExecuteNonQuery(System.Data.Common.DbTransaction transaction, System.Data.CommandType commandType, string commandText)
{
return(db.ExecuteNonQuery(transaction, commandType, commandText));
}
public System.Data.IDataReader ExecuteReader(System.Data.Common.DbTransaction transaction, System.Data.CommandType commandType, string commandText)
{
return(db.ExecuteReader(transaction, commandType, commandText));
}
public override IReference <ClassType, string> LoadUsingCommand(string Command, System.Data.CommandType CommandType)
{
this.CommandToLoad = new Command(Command, CommandType);
return((IReference <ClassType, string>) this);
}
public System.Data.DataSet ExecuteDataSet(System.Data.Common.DbTransaction transaction, System.Data.CommandType commandType, string commandText)
{
return(db.ExecuteDataSet(transaction, commandType, commandText));
}
/// <summary>
/// Sets the default select any command
/// </summary>
/// <param name="Command">Command</param>
/// <param name="CommandType">Command type</param>
/// <returns>This</returns>
public IMapping SetSelectAnyCommand(string Command, System.Data.CommandType CommandType)
{
SelectAnyCommand = Command;
SelectAnyCommandType = CommandType;
return(this);
}
public static int ExecuteNonQuery(string sql, System.Data.CommandType commandType, params SqlParameter[] parameters)
{
return(GetCommand(sql, commandType, parameters).ExecuteNonQuery());
}
/// <summary>
/// Execute any non-DML SQL Query
/// </summary>
/// <param name="connectionString">Connection string to database</param>
/// <param name="sql">Any SELECT SQL that you want to perform with/without parameterized parameters (Do not directly put sql parameter in this parameter)</param>
/// <param name="parameters">SQL parameters according to the sql parameter</param>
/// <param name="commandType">Type of SQL Command</param>
/// <returns></returns>
/// <exception cref="Exception"/>
public static async Task <int> ExecuteNonQueryAsync <TDatabaseType>(string connectionString, string sql, IEnumerable <DbParameter> parameters = null, System.Data.CommandType commandType = System.Data.CommandType.Text)
where TDatabaseType : DbConnection, new()
{
DbTransaction transaction = null;
try
{
int result = -1;
using (var connection = new TDatabaseType())
{
connection.ConnectionString = connectionString;
await connection.OpenAsync();
transaction = connection.BeginTransaction();
using (var command = connection.CreateCommand())
{
command.CommandText = sql;
command.Transaction = transaction;
command.CommandType = commandType;
if (parameters != null)
{
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
result = await command.ExecuteNonQueryAsync();
}
transaction.Commit();
connection.Close();
}
return(result);
}
catch (Exception e)
{
transaction?.Rollback();
throw e;
}
}
/// <summary>
/// Execute SELECT SQL query and return IEnumerable of specified POCO that is matching with the query columns
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connectionString">Connection string to database</param>
/// <param name="sql">Any SELECT SQL that you want to perform with/without parameterized parameters (Do not directly put sql parameter in this parameter)</param>
/// <param name="parameters">SQL parameters according to the sql parameter</param>
/// <param name="commandType">Type of SQL Command</param>
/// <returns>IEnumerable of POCO</returns>
/// <exception cref="Exception"/>
public static IEnumerable <T> ExecuteReader <T, TDatabaseType>(string connectionString, string sql, IEnumerable <DbParameter> parameters = null, System.Data.CommandType commandType = System.Data.CommandType.Text)
where T : new()
where TDatabaseType : DbConnection, new()
{
return(ExecuteReader <T, TDatabaseType>(connectionString, sql, parameters, (cursor) => Data.RowBuilder <T>(cursor), commandType));
}
/// <summary>
/// Execute SELECT SQL query and return a scalar object
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connectionString">Connection string to database</param>
/// <param name="sql">Any SELECT SQL that you want to perform with/without parameterized parameters (Do not directly put sql parameter in this parameter)</param>
/// <param name="parameters">SQL parameters according to the sql parameter</param>
/// <param name="commandType">Type of SQL Command</param>
/// <returns></returns>
/// <exception cref="Exception"/>
public static T ExecuteScalar <T, TDatabaseType>(string connectionString, string sql, IEnumerable <DbParameter> parameters = null, System.Data.CommandType commandType = System.Data.CommandType.Text)
where TDatabaseType : DbConnection, new()
{
DbTransaction transaction = null;
try
{
T result = default;
using (var connection = new TDatabaseType())
{
connection.ConnectionString = connectionString;
connection.Open();
transaction = connection.BeginTransaction();
using (var command = connection.CreateCommand())
{
command.CommandText = sql;
command.Transaction = transaction;
command.CommandType = commandType;
if (parameters != null)
{
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
result = (T)command.ExecuteScalar();
}
transaction.Commit();
connection.Close();
}
return(result);
}
catch (Exception e)
{
transaction?.Rollback();
throw e;
}
}
/// <summary>
/// Execute SELECT SQL query and return IEnumerable of specified POCO that is matching with the query columns
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connectionString">Connection string to database</param>
/// <param name="sql">Any SELECT SQL that you want to perform with/without parameterized parameters (Do not directly put sql parameter in this parameter)</param>
/// <param name="parameters">SQL parameters according to the sql parameter</param>
/// <param name="objectBuilder">How the POCO should build with each giving row of SqlDataReader</param>
/// <param name="commandType">Type of SQL Command</param>
/// <returns>IEnumerable of POCO</returns>
/// <exception cref="Exception"/>
public static IEnumerable <T> ExecuteReader <T, TDatabaseType>(string connectionString, string sql, IEnumerable <DbParameter> parameters, Func <DbDataReader, T> objectBuilder, System.Data.CommandType commandType = System.Data.CommandType.Text)
where T : new()
where TDatabaseType : DbConnection, new()
{
DbTransaction transaction = null;
try
{
List <T> result = new List <T>();
using (var connection = new TDatabaseType())
{
connection.ConnectionString = connectionString;
connection.Open();
transaction = connection.BeginTransaction();
using (var command = connection.CreateCommand())
{
command.CommandText = sql;
command.Transaction = transaction;
command.CommandType = commandType;
if (parameters != null)
{
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
using (var cursor = command.ExecuteReader())
{
while (cursor.Read())
{
result.Add(objectBuilder(cursor));
}
}
}
transaction.Commit();
connection.Close();
}
return(result);
}
catch (Exception e)
{
transaction?.Rollback();
throw e;
}
}
public ConnectorObjectsBase(System.String ConnectionString, ref System.String ProcedureNameOrCommandText, System.Collections.Generic.List <System.Data.Common.DbParameter> Parameters, System.Data.CommandType CommandType, System.Boolean ExecuteForJSON, System.Boolean ShowPlan, System.Boolean ReadSummaries)
{
if (System.String.IsNullOrWhiteSpace(ConnectionString))
{
throw new System.Exception(SoftmakeAll.SDK.Environment.NullConnectionString);
}
if (System.String.IsNullOrWhiteSpace(ProcedureNameOrCommandText))
{
throw new System.Exception(SoftmakeAll.SDK.Environment.NullProcedureNameOrCommandText);
}
}
protected virtual async System.Threading.Tasks.Task <SoftmakeAll.SDK.OperationResult <System.Text.Json.JsonElement> > ExecuteCommandForJSONAsync(System.String ProcedureNameOrCommandText, System.Collections.Generic.List <System.Data.Common.DbParameter> Parameters, System.Data.CommandType CommandType)
{
SoftmakeAll.SDK.OperationResult <System.Text.Json.JsonElement> Result = new SoftmakeAll.SDK.OperationResult <System.Text.Json.JsonElement>();
SoftmakeAll.SDK.OperationResult <System.Data.DataSet> DatabaseOperationResult = await this.ExecuteCommandAsync(ProcedureNameOrCommandText, Parameters, CommandType);
Result.Count = DatabaseOperationResult.Count;
Result.ID = DatabaseOperationResult.ID;
Result.Message = DatabaseOperationResult.Message;
Result.ExitCode = DatabaseOperationResult.ExitCode;
if (DatabaseOperationResult.ExitCode != 0)
{
return(Result);
}
Result.Data = SoftmakeAll.SDK.DataAccess.DatabaseValues.DataSetToJSON(DatabaseOperationResult.Data);
return(Result);
}
protected abstract System.Threading.Tasks.Task <SoftmakeAll.SDK.OperationResult <System.Data.DataSet> > ExecuteCommandAsync(System.String ProcedureNameOrCommandText, System.Collections.Generic.List <System.Data.Common.DbParameter> Parameters, System.Data.CommandType CommandType);
/// <summary>
/// Sets the default delete command
/// </summary>
/// <param name="Command">Command</param>
/// <param name="CommandType">Command type</param>
/// <returns>This</returns>
public IMapping SetDeleteCommand(string Command, System.Data.CommandType CommandType)
{
DeleteCommand = Command;
DeleteCommandType = CommandType;
return(this);
}
/// <summary>
/// Execute SELECT SQL query and return IEnumerable of dynamic object
/// </summary>
/// <param name="connectionString">Connection string to database</param>
/// <param name="sql">Any SELECT SQL that you want to perform with/without parameterized parameters (Do not directly put sql parameter in this parameter)</param>
/// <param name="parameters">SQL parameters according to the sql parameter</param>
/// <param name="commandType">Type of SQL Command</param>
/// <returns>IEnumerable of dynamic object</returns>
/// <exception cref="Exception"/>
public static IEnumerable <dynamic> ExecuteReader <TDatabaseType>(string connectionString, string sql, IEnumerable <DbParameter> parameters = null, System.Data.CommandType commandType = System.Data.CommandType.Text)
where TDatabaseType : DbConnection, new()
{
DbTransaction transaction = null;
try
{
List <dynamic> result = new List <dynamic>();
using (var connection = new TDatabaseType())
{
connection.ConnectionString = connectionString;
connection.Open();
transaction = connection.BeginTransaction();
using (var command = connection.CreateCommand())
{
command.CommandText = sql;
command.Transaction = transaction;
command.CommandType = commandType;
if (parameters != null)
{
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
using (var cursor = command.ExecuteReader())
{
var columns = Enumerable.Range(0, cursor.FieldCount).Select(cursor.GetName).ToList();
while (cursor.Read())
{
result.Add(Data.RowBuilder(cursor, columns));
}
}
}
transaction.Commit();
connection.Close();
}
return(result);
}
catch (Exception e)
{
transaction?.Rollback();
throw e;
}
}
/// <summary>
/// Sets the default insert command
/// </summary>
/// <param name="Command">Command</param>
/// <param name="CommandType">Command type</param>
/// <returns>This</returns>
public IMapping SetInsertCommand(string Command, System.Data.CommandType CommandType)
{
InsertCommand = Command;
InsertCommandType = CommandType;
return(this);
}
/// <summary>
/// Execute SELECT SQL query and return IEnumerable of specified POCO that is matching with the query columns
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connectionString">Connection string to database</param>
/// <param name="sql">Any SELECT SQL that you want to perform with/without parameterized parameters (Do not directly put sql parameter in this parameter)</param>
/// <param name="parameters">SQL parameters according to the sql parameter</param>
/// <param name="commandType">Type of SQL Command</param>
/// <returns>IEnumerable of POCO</returns>
/// <exception cref="Exception"/>
public static async Task <IEnumerable <T> > ExecuteReaderAsync <T>(string connectionString, string sql, IEnumerable <SqlParameter> parameters = null, System.Data.CommandType commandType = System.Data.CommandType.Text) where T : new()
{
return(await ExecuteReaderAsync(connectionString, sql, parameters, (cursor) => Data.RowBuilder <T>(cursor), commandType));
}
/// <summary>
/// Sets the default update command
/// </summary>
/// <param name="Command">Command</param>
/// <param name="CommandType">Command type</param>
/// <returns>This</returns>
public IMapping SetUpdateCommand(string Command, System.Data.CommandType CommandType)
{
UpdateCommand = Command;
UpdateCommandType = CommandType;
return(this);
}
/// <summary>
/// Execute SELECT SQL query and return a scalar object
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connectionString">Connection string to database</param>
/// <param name="sql">Any SELECT SQL that you want to perform with/without parameterized parameters (Do not directly put sql parameter in this parameter)</param>
/// <param name="parameters">SQL parameters according to the sql parameter</param>
/// <param name="commandType">Type of SQL Command</param>
/// <returns></returns>
/// <exception cref="Exception"/>
public static async Task <T> ExecuteScalarAsync <T>(string connectionString, string sql, IEnumerable <SqlParameter> parameters = null, System.Data.CommandType commandType = System.Data.CommandType.Text)
{
return(await StaticDatabseConnector.ExecuteScalarAsync <T, SqlConnection>(connectionString, sql, parameters, commandType));
}
public static SqlDataReader ExecuteReader(string sql, System.Data.CommandType commandType, params SqlParameter[] parameters)
{
return(GetCommand(sql, commandType, parameters).ExecuteReader());
}
/// <summary>
/// Execute SELECT SQL query and return IEnumerable of specified POCO that is matching with the query columns
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connectionString">Connection string to database</param>
/// <param name="sql">Any SELECT SQL that you want to perform with/without parameterized parameters (Do not directly put sql parameter in this parameter)</param>
/// <param name="parameters">SQL parameters according to the sql parameter</param>
/// <param name="objectBuilder">How the POCO should build with each giving row of SqlDataReader</param>
/// <param name="commandType">Type of SQL Command</param>
/// <returns>IEnumerable of POCO</returns>
/// <exception cref="Exception"/>
public static IEnumerable <T> ExecuteReader <T>(string connectionString, string sql, IEnumerable <SqlParameter> parameters, Func <DbDataReader, T> objectBuilder, System.Data.CommandType commandType = System.Data.CommandType.Text) where T : new()
{
return(StaticDatabseConnector.ExecuteReader <T, SqlConnection>(connectionString, sql, parameters, objectBuilder, commandType));
}
public virtual void Any(string Command, System.Data.CommandType CommandType)
{
AnyCommand = new Command(Command, CommandType);
}
/// <summary>
/// Execute SELECT SQL query and return IEnumerable of specified POCO that is matching with the query columns
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connectionString">Connection string to database</param>
/// <param name="sql">Any SELECT SQL that you want to perform with/without parameterized parameters (Do not directly put sql parameter in this parameter)</param>
/// <param name="parameters">SQL parameters according to the sql parameter</param>
/// <param name="commandType">Type of SQL Command</param>
/// <returns>IEnumerable of POCO</returns>
/// <exception cref="Exception"/>
public static IEnumerable <T> ExecuteReader <T>(string connectionString, string sql, IEnumerable <SqlParameter> parameters = null, System.Data.CommandType commandType = System.Data.CommandType.Text) where T : new()
{
return(StaticDatabseConnector.ExecuteReader <T, SqlConnection>(connectionString, sql, parameters, (cursor) => Data.RowBuilder <T>(cursor), commandType));
}
public int ExecuteNonQuery(System.Data.CommandType commandType, string commandText)
{
return(db.ExecuteNonQuery(commandType, commandText));
}
/// <summary>
/// Execute SELECT SQL query and return IEnumerable of dynamic object
/// </summary>
/// <param name="connectionString">Connection string to database</param>
/// <param name="sql">Any SELECT SQL that you want to perform with/without parameterized parameters (Do not directly put sql parameter in this parameter)</param>
/// <param name="parameters">SQL parameters according to the sql parameter</param>
/// <param name="commandType">Type of SQL Command</param>
/// <returns>IEnumerable of dynamic object</returns>
/// <exception cref="Exception"/>
public static IEnumerable <dynamic> ExecuteReader(string connectionString, string sql, IEnumerable <SqlParameter> parameters = null, System.Data.CommandType commandType = System.Data.CommandType.Text)
{
return(StaticDatabseConnector.ExecuteReader <SqlConnection>(connectionString, sql, parameters, commandType));
}
public System.Data.IDataReader ExecuteReader(System.Data.CommandType commandType, string commandText)
{
return(db.ExecuteReader(commandType, commandText));
}
/// <summary>
/// Execute any non-DML SQL Query
/// </summary>
/// <param name="connectionString">Connection string to database</param>
/// <param name="sql">Any SELECT SQL that you want to perform with/without parameterized parameters (Do not directly put sql parameter in this parameter)</param>
/// <param name="parameters">SQL parameters according to the sql parameter</param>
/// <param name="commandType">Type of SQL Command</param>
/// <returns></returns>
/// <exception cref="Exception"/>
public static int ExecuteNonQuery(string connectionString, string sql, IEnumerable <SqlParameter> parameters = null, System.Data.CommandType commandType = System.Data.CommandType.Text)
{
return(StaticDatabseConnector.ExecuteNonQuery <SqlConnection>(connectionString, sql, parameters, commandType));
}
public object ExecuteScalar(System.Data.CommandType commandType, string commandText)
{
return(db.ExecuteScalar(commandType, commandText));
}
/// <summary>
/// 执行语句
/// </summary>
/// <param name="ado">数据库访问对象</param>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdText">命令</param>
/// <param name="models">模型</param>
/// <returns></returns>
public static int ExecuteNonQueryWithModels(this IAdo ado, System.Data.CommandType cmdType, string cmdText, params object[] models)
{
return(ado.ExecuteNonQueryWithModelsAsync(cmdType, cmdText, models).GetAwaiter().GetResult());
}