/// <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)); }
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; }