/// <summary>
/// Return the Records
/// </summary>
/// <param name="CmdTxt"></param>
/// <param name="CmdType"></param>
/// <param name="CloseConnection"></param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string CmdTxt, SqlCommandType CmdType, SqlCommand _SqlComm, bool CloseConnection = true)
{
SqlDataReader SqlReadr = null;
try
{
SqlComm = _SqlComm;
CreateCammand(CmdTxt, CmdType);
//create a sqlcommand
//****** parameter building Routine *******
//SqlComm.Parameters.AddRange(m_SqlParameters.ToArray());
//**************************************
if (CloseConnection)
{
SqlReadr = SqlComm.ExecuteReader(CommandBehavior.CloseConnection);
}
else
{
SqlReadr = SqlComm.ExecuteReader();
}
}
catch (Exception ex)
{
_ErrorDescription = Get_ErrorString(ex.Message);
}
finally
{
//if ((SqlComm != null))
//{
// SqlComm.Parameters.Clear();
//}
if (CloseConnection & SqlComm.Connection.State != ConnectionState.Closed)
{
SqlComm.Connection.Close();
SqlComm.Dispose();
SqlCon.Dispose();
SqlCon.Close();
SqlCon = null;
}
}
return(SqlReadr);
}
public void CreateCammand(string CmdTxt, SqlCommandType SqlCommandType)
{
if (SqlComm == null)
{
SqlComm = new SqlCommand();
}
if (SqlCon == null)
{
OpenConnection();
}
else
{
if (SqlCon.State == 0)
{
SqlCon.Open();
}
}
if (_IsTransaction)
{
SqlComm.Transaction = SqlTran;
}
SqlComm.Connection = SqlCon;
SqlComm.CommandText = CmdTxt.ToString();
switch (SqlCommandType)
{
case SqlCommandType.StoredProcedure:
SqlComm.CommandType = CommandType.StoredProcedure;
break;
case SqlCommandType.Text:
SqlComm.CommandType = CommandType.Text;
break;
case SqlCommandType.TableDirect:
SqlComm.CommandType = CommandType.TableDirect;
break;
}
}
/// <summary>
/// Возвращает префикс всей команды до собственно имени процедуры/функции
/// </summary>
/// <param name="cmdtype"></param>
/// <param name="dbtype"></param>
/// <returns></returns>
public static string GetCallCommandPrefix(SqlCommandType cmdtype, DatabaseEngineType dbtype)
{
if (dbtype == DatabaseEngineType.SqlServer)
{
return("EXEC");
}
if (dbtype == DatabaseEngineType.MySql)
{
if (cmdtype == SqlCommandType.Call)
{
return("CALL");
}
return("CALL ALL");
}
if (cmdtype == SqlCommandType.Call)
{
return("SELECT");
}
return("SELECT * FROM");
}
public SqlCommandBuilder(SqlHandler sqlHandler, SqlCommandType commandType, bool UseWhere)
{
if (commandType == SqlCommandType.WHERE_HANDLE)
throw new Exception("This command type is only allowed by the core handler.");
this.CommandType = commandType;
InsertValues = new ConcurrentDictionary<string, object>();
UpdateValues = new ConcurrentDictionary<string, object>();
WhereValues = new ConcurrentDictionary<string, object>();
this.Handler = sqlHandler;
this.UseWhere = UseWhere;
int WhereFormatCount = 1;
switch (commandType)
{
case SqlCommandType.SELECT:
CommandString = "SELECT * FROM {0}";
goto case SqlCommandType.WHERE_HANDLE;
case SqlCommandType.INSERT:
CommandString = "INSERT INTO {0} ({1}) VALUES ({2})";
break;
case SqlCommandType.UPDATE:
CommandString = "UPDATE {0} SET {1}";
WhereFormatCount = 2;
goto case SqlCommandType.WHERE_HANDLE;
case SqlCommandType.DELETE:
CommandString = "DELETE {0}";
goto case SqlCommandType.WHERE_HANDLE;
case SqlCommandType.WHERE_HANDLE:
if (this.UseWhere)
CommandString = string.Format("{0} WHERE {{{1}}}", CommandString, WhereFormatCount);
break;
}
}
/// <summary>
/// Will return the DataTable
/// </summary>
/// <param name="CmdTxt"></param>
/// <param name="CmdType"></param>
/// <param name="tblName"></param>
/// <param name="CloseConnection"></param>
/// <returns></returns>
public DataTable ExecuteDataTable(string CmdTxt, SqlCommandType CmdType, SqlCommand _SqlComm, bool CloseConnection = true)
{
DataTable Dt = new DataTable();
SqlDataAdapter Adapter = new SqlDataAdapter();
try
{
SqlComm = _SqlComm;
CreateCammand(CmdTxt, CmdType);
//create a sqlcommand
//****** parameter building Routine *******
//SqlComm.Parameters.AddRange(m_SqlParameters.ToArray());
//**************************************
Adapter.SelectCommand = SqlComm;
Adapter.Fill(Dt);
}
catch (Exception ex)
{
_ErrorDescription = Get_ErrorString(ex.Message);
}
finally
{
//if ((SqlComm != null))
//{
// SqlComm.Parameters.Clear();
//}
if (CloseConnection & SqlComm.Connection.State != ConnectionState.Closed)
{
SqlComm.Connection.Close();
SqlComm.Dispose();
SqlCon.Dispose();
SqlCon.Close();
SqlCon = null;
}
}
return(Dt);
}
private void btnGenerateSqlText_Click(object sender, EventArgs e)
{
SqlCommandType type = GetCommandType();
if (type == SqlCommandType.Unknown)
{
return;
}
string bits;
if (type == SqlCommandType.Insert)
{
bits = GenerateBitString('0');
SetStringInSqlOutput(SqlGenerator.GenerateInsert(bits, selectedFlagList.name));
}
else if (type == SqlCommandType.Update)
{
bits = GenerateBitString('?');
SetStringInSqlOutput(SqlGenerator.GenerateUpdate(bits, selectedFlagList.name));
}
}
public static async Task ExecuteReaderAsync(this IDbConnection conn, SqlCommandType commandType, SqlCommandText commandText, Action <IDataReader, int> dataMapper, params SqlParameter[] sqlParameters)
=>
await UsingAsync(new SqlCommand(), async cmd =>
{
int resultSet = 0;
cmd.Connection = (SqlConnection)conn;
cmd.CommandType = commandType;
cmd.CommandText = commandText;
cmd.CommandTimeout = 5000;
if (sqlParameters != null)
{
foreach (SqlParameter parm in sqlParameters)
{
if ((parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Output) && parm.Value == null)
{
parm.Value = DBNull.Value;
}
cmd.Parameters.Add(parm);
}
}
SqlDataReader reader = await cmd.ExecuteReaderAsync();
while (true)
{
while (await reader.ReadAsync())
{
dataMapper?.Invoke(reader, resultSet);
}
resultSet++;
if (reader.IsClosed || !reader.NextResultAsync().Result)
{
break;
}
}
return(resultSet);
});
/// <summary>
/// MySql command for Insert and Replace.
/// </summary>
/// <returns>Last inserted Id.</returns>
public uint MySqlCommand(SqlCommandType sqlCommandType, string tableName, Dictionary <string, object> args, MySqlConnection connection,
MySqlTransaction transaction = null)
{
var columns = new StringBuilder();
var values = new StringBuilder();
foreach (var key in args.Keys)
{
columns.AppendFormat("`{0}`, ", key);
values.AppendFormat("@{0}, ", key);
}
string sqlQuery;
switch (sqlCommandType)
{
case SqlCommandType.Insert:
sqlQuery = "INSERT";
break;
case SqlCommandType.Replace:
sqlQuery = "REPLACE";
break;
default:
throw new ArgumentOutOfRangeException(nameof(sqlCommandType), sqlCommandType, null);
}
var commandText = $"{sqlQuery} INTO `{tableName}`({columns.ToString().Trim(' ', ',')}) VALUES ({values.ToString().Trim(' ', ',')});";
var command = new MySqlCommand(commandText, connection, transaction);
foreach (var(key, value) in args)
{
command.Parameters.AddWithValue("@" + key, value);
}
command.ExecuteNonQuery();
return((uint)command.LastInsertedId);
}
/// <summary>
/// will retrun the first column of first row and remaining will be igrnored.'
/// </summary>
/// <param name="CmdTxt"></param>
/// <param name="CmdType"></param>
/// <param name="CloseConnection"></param>
/// <returns></returns>
public object ExecuteScaler(string CmdTxt, SqlCommandType CmdType, SqlCommand _SqlComm, bool CloseConnection = true)
{
object val = new object();
try
{
SqlComm = _SqlComm;
CreateCammand(CmdTxt, CmdType);
//create a sqlcommand
//****** parameter building Routine *******
SqlComm.Parameters.AddRange(m_SqlParameters.ToArray());
//**************************************
val = SqlComm.ExecuteScalar();
}
catch (Exception ex)
{
_ErrorDescription = Get_ErrorString(ex.Message);
}
finally
{
//if ((SqlComm != null))
//{
// SqlComm.Parameters.Clear();
//}
if (CloseConnection & SqlComm.Connection.State != ConnectionState.Closed)
{
SqlComm.Connection.Close();
SqlComm.Dispose();
SqlCon.Dispose();
SqlCon.Close();
SqlCon = null;
}
}
return(val);
}
public static async Task <int> ExecuteNonQueryAsync(this IDbConnection conn, SqlCommandType commandType, SqlCommandText commandText, Action <IDbDataParameter[]> returnParams = null, IDbDataParameter[] sqlParameters = null)
=> await UsingAsync(new SqlCommand(), async cmd =>
{
cmd.Connection = (SqlConnection)conn;
cmd.CommandType = commandType;
cmd.CommandText = commandText;
cmd.CommandTimeout = 5000;
if (sqlParameters != null)
{
foreach (SqlParameter parm in sqlParameters)
{
if ((parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Output) && parm.Value == null)
{
parm.Value = DBNull.Value;
}
cmd.Parameters.Add(parm);
}
}
int result = await cmd.ExecuteNonQueryAsync();
returnParams?.Invoke(sqlParameters);
return(result);
});
/// <summary>
/// 创建新的Sql自定义语句
/// </summary>
/// <param name="commandType">Sql语句类型</param>
/// <param name="commandString">语句内容</param>
/// <returns>Sql自定义语句</returns>
public CustomCommand CreateCustomCommand(SqlCommandType commandType, String commandString)
{
return(new CustomCommand(this, commandType, commandString));
}
public static SqlCommand Command(SqlCommandType commandType)
{
return(new SqlCommand(commandType));
}
private static async Task <JToken> GetSqlCommandResult(string query, string connectionString, IEnumerable <Parameter> parameters, Options options, SqlCommandType commandType, CancellationToken cancellationToken)
{
using (var sqlConnection = new SqlConnection(connectionString))
{
await sqlConnection.OpenAsync(cancellationToken).ConfigureAwait(false);
cancellationToken.ThrowIfCancellationRequested();
IDictionary <string, object> parameterObject = new ExpandoObject();
foreach (var parameter in parameters)
{
parameterObject.Add(parameter.Name, parameter.Value);
}
if (options.SqlTransactionIsolationLevel == SqlTransactionIsolationLevel.None)
{
using (var result = await sqlConnection.ExecuteReaderAsync(
query,
parameterObject,
commandTimeout: options.CommandTimeoutSeconds,
commandType: commandType.GetSqlCommandType())
.ConfigureAwait(false))
{
var table = new DataTable();
table.Load(result);
return(JToken.FromObject(table));
}
}
using (var transaction =
options.SqlTransactionIsolationLevel == SqlTransactionIsolationLevel.Default
? sqlConnection.BeginTransaction()
: sqlConnection.BeginTransaction(options.SqlTransactionIsolationLevel.GetSqlTransactionIsolationLevel()))
{
using (var result = await sqlConnection.ExecuteReaderAsync(
query,
parameterObject,
commandTimeout: options.CommandTimeoutSeconds,
commandType: commandType.GetSqlCommandType(), transaction: transaction)
.ConfigureAwait(false))
{
var table = new DataTable();
table.Load(result);
transaction.Commit();
return(JToken.FromObject(table));
}
}
}
}
/// <summary>
/// 初始化新的Sql自定义语句类
/// </summary>
/// <param name="database">数据库</param>
/// <param name="commandType">语句类型</param>
/// <param name="commandString">语句内容</param>
internal CustomCommand(AbstractDatabase database, SqlCommandType commandType, String commandString)
: base(database, String.Empty)
{
this._commandType = commandType;
this._commandText = commandString;
}
/// <summary>
/// set sql command type
/// </summary>
/// <param name="type"><see cref="SqlCommandType"/></param>
public void SetSqlCommandType(SqlCommandType type)
{
_SqlCommandType = type;
}
public void Should_return_type_as_StoredProcedure_when_name_is_wrong_case()
{
SqlCommandType decider = new SqlCommandType(connectionStringMinPermissions);
Assert.That(decider.Get("addtotesttable"), Is.EqualTo(CommandType.StoredProcedure));
}
public void Should_return_type_as_StoredProcedure_for_existng_procedure_with_space_in_name()
{
SqlCommandType decider = new SqlCommandType(connectionStringMinPermissions);
Assert.That(decider.Get("[Sproc with spaces in name]"), Is.EqualTo(CommandType.StoredProcedure));
}
public void Should_return_type_as_text_when_commandText_includes_table_with_square_brackets()
{
SqlCommandType decider = new SqlCommandType(connectionStringTestSchemaOwnerPermissions);
Assert.That(decider.Get("SELECT * FROM [TestTable]"), Is.EqualTo(CommandType.Text));
}
public void Should_return_type_as_StoredProcedure_for_existng_procedure()
{
SqlCommandType decider = new SqlCommandType(connectionStringMinPermissions);
Assert.That(decider.Get("AddToTestTable"), Is.EqualTo(CommandType.StoredProcedure));
}
public static DataTable RunCommand(string connectionString, string rawScript, SqlCommandType commandType)
{
DataTable table = null;
if (string.IsNullOrWhiteSpace(rawScript))
{
return(null);
}
for (var retries = 0; retries < MAX_RETRIES; retries++)
{
SqlTransaction transaction = null;
var cn = new SqlConnection(connectionString);
try
{
cn.Open();
transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);
using (var command = cn.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = rawScript;
command.CommandType = CommandType.Text;
command.CommandTimeout = 0;
switch (commandType)
{
case SqlCommandType.ExecuteWithData:
{
table = new DataTable();
var adapter = new SqlDataAdapter(command);
adapter.Fill(table);
break;
}
case SqlCommandType.ExecuteStoredProc:
{
command.CommandType = CommandType.StoredProcedure;
table = new DataTable();
var adapter = new SqlDataAdapter(command);
adapter.Fill(table);
break;
}
case SqlCommandType.ExecuteWithoutData:
{
command.ExecuteNonQuery();
break;
}
}
}
transaction.Commit();
break;
}
catch (Exception)
{
if (cn.State != ConnectionState.Open)
{
// The transaction must have been rolledback with the client being disconnected
try
{
transaction?.Rollback();
}
catch (Exception)
{
}
continue;
}
throw;
}
finally
{
cn.Close();
}
}
return(table);
}
// Constructors
internal SqlCommand(SqlCommandType commandType)
: base(SqlNodeType.Command)
{
CommandType = commandType;
}
/// <summary>
/// 初始化新的Sql自定义语句类
/// </summary>
/// <param name="database">数据库</param>
/// <param name="commandType">语句类型</param>
/// <param name="commandString">语句内容</param>
public CustomCommand(Database database, SqlCommandType commandType, String commandString)
: base(database, String.Empty)
{
this._commandType = commandType;
this._commandString = commandString;
}
private void CheckParsingSupersateParse()
{
string[] actions = new string[3]{ "INSERT", "DELETE", "UPDATE" };
string actionTemplate = "The {0} statement conflicted with the";
string action;
parsingState = ParsingState.UnParsedParsingState;
for (int i = 0; i < actions.Length; i++)
{
action = string.Format(actionTemplate, actions[i]);
if (messageToParse.IndexOf(action, 0) != -1)
{
parsingState = ParsingState.ActionParsedState;
commandType = (SqlCommandType)i;
break;
}
}
}
internal static CommandType GetSqlCommandType(this SqlCommandType sqlCommandType)
{
return(GetEnum <CommandType>(sqlCommandType));
}
/// <summary>
/// 初始化新的Sql自定义语句类
/// </summary>
/// <param name="database">数据库</param>
/// <param name="commandType">语句类型</param>
/// <param name="commandString">语句内容</param>
internal CustomCommand(AbstractDatabase database, SqlCommandType commandType, String commandString)
: base(database, null, String.Empty)
{
this._commandType = commandType;
this._commandText = commandString;
}
public void Should_return_type_as_Text_if_table_not_found()
{
SqlCommandType decider = new SqlCommandType(connectionStringMinPermissions);
Assert.That(decider.Get("MissingTable"), Is.EqualTo(CommandType.Text));
}
private void PopulateSchemaDetails()
{
commandTypeDecider = new SqlCommandType(connectionStringMinPermissions);
commandTypeDecider = new SqlCommandType(connectionStringAdditionalDb);
}
public void Should_return_type_as_Text_for_sql_string_with_tab_between_keywords()
{
SqlCommandType decider = new SqlCommandType(ConnectionString);
Assert.That(decider.Get("SELECT\t*\tFROM\tTestTable"), Is.EqualTo(CommandType.Text));
}
/// <summary>
/// 初始化新的Sql自定义语句类
/// </summary>
/// <param name="database">数据库</param>
/// <param name="commandType">语句类型</param>
/// <param name="commandString">语句内容</param>
public CustomCommand(Database database, SqlCommandType commandType, String commandString)
: base(database, String.Empty)
{
this._commandType = commandType;
this._commandString = commandString;
}
public void Should_return_StoredProcedure_when_procedure_is_in_another_schema_user_access_to()
{
SqlCommandType decider = new SqlCommandType(connectionStringTestSchemaOwnerPermissions);
Assert.That(decider.Get("[SelectAllFromTestSchemaTable]"), Is.EqualTo(CommandType.StoredProcedure));
}
internal void SetSqlCommandType(SqlCommandType type)
{
this._sqlCommandType = type;
}
private StatementAttribute PreStatement(Type interfaceType, string scope, MethodInfo methodInfo, Type returnType, bool isTaskReturnType, ISmartSqlMapper smartSqlMapper)
{
returnType = isTaskReturnType ? returnType.GetGenericArguments().FirstOrDefault() : returnType;
var statementAttr = methodInfo.GetCustomAttribute <StatementAttribute>();
var methodName = _sqlIdNamingConvert == null ? methodInfo.Name : _sqlIdNamingConvert.Invoke(interfaceType, methodInfo);
if (isTaskReturnType && methodInfo.Name.EndsWith("Async") && _sqlIdNamingConvert == null)
{
methodName = methodName.Substring(0, methodName.Length - 5);
}
if (statementAttr != null)
{
statementAttr.Id = !String.IsNullOrEmpty(statementAttr.Id) ? statementAttr.Id : methodName;
statementAttr.Scope = !String.IsNullOrEmpty(statementAttr.Scope) ? statementAttr.Scope : scope;
}
else
{
statementAttr = new StatementAttribute
{
Scope = scope,
Id = methodName
};
}
if (returnType == typeof(DataTable))
{
statementAttr.Execute = ExecuteBehavior.GetDataTable;
return(statementAttr);
}
if (returnType == typeof(DataSet))
{
statementAttr.Execute = ExecuteBehavior.GetDataSet;
return(statementAttr);
}
if (IsValueTuple(returnType))
{
statementAttr.Execute = ExecuteBehavior.FillMultiple;
return(statementAttr);
}
if (statementAttr.Execute == ExecuteBehavior.Auto)
{
SqlCommandType cmdType = SqlCommandType.Unknown;
if (String.IsNullOrEmpty(statementAttr.Sql))
{
var sqlStatement = smartSqlMapper.SmartSqlOptions.SmartSqlContext.GetStatement($"{statementAttr.Scope}.{statementAttr.Id}");
cmdType = sqlStatement.SqlCommandType;
if (sqlStatement.MultipleResultMap != null && !returnType.IsValueType)
{
statementAttr.Execute = ExecuteBehavior.GetNested;
return(statementAttr);
}
}
else
{
cmdType = _commandAnalyzer.Analyse(statementAttr.Sql);
}
if (returnType == typeof(int) || returnType == _voidType || returnType == null)
{
statementAttr.Execute = ExecuteBehavior.Execute;
if (returnType == typeof(int))
{
if (cmdType.HasFlag(SqlCommandType.Select))
{
statementAttr.Execute = ExecuteBehavior.ExecuteScalar;
}
}
}
else if (returnType.IsValueType || returnType == typeof(string))
{
statementAttr.Execute = ExecuteBehavior.ExecuteScalar;
if (!cmdType.HasFlag(SqlCommandType.Select))
{
statementAttr.Execute = ExecuteBehavior.Execute;
}
}
else
{
var isQueryEnumerable = typeof(IEnumerable).IsAssignableFrom(returnType);
if (isQueryEnumerable)
{
statementAttr.Execute = ExecuteBehavior.Query;
}
else
{
statementAttr.Execute = ExecuteBehavior.QuerySingle;
}
}
}
return(statementAttr);
}
/// <summary>
/// Creates command for given stored proc or function
/// </summary>
/// <param name="schema"></param>
/// <param name="name"></param>
/// <param name="calltype"></param>
/// <param name="parameters"></param>
public UniSqlQuery(string schema, string name, object parameters = null,SqlCommandType calltype = SqlCommandType.Call ) {
Schema = schema;
Name = name;
CommandType = calltype;
ParameterDefinition = parameters;
}
