public static bool TableExists(this IDbConnection connection,string tableName) { bool exists; try { connection.Open(); // ANSI SQL var cmd = connection.CreateCommand(); cmd.CommandText = string.Format("select case when exists((select * from information_schema.tables " + "where table_name = '{0}')) then 1 else 0 end", tableName); exists = (int) cmd.ExecuteScalar() == 1; } catch { try { // Other RDBMS. exists = true; var cmd = connection.CreateCommand(); cmd.CommandText = string.Format("select 1 from {0} where 1 = 0", tableName); cmd.ExecuteNonQuery(); } catch { exists = false; } } finally { connection.Close(); } return exists; }
public static IDbCommand CreateCommand(this IDbConnection connection, AdoOptions options) { if (options == null || options.CommandTimeout < 0) return connection.CreateCommand(); var command = connection.CreateCommand(); command.CommandTimeout = options.CommandTimeout; return command; }
public static SqlDataReader ExecuteReader(this SqlConnection connection, string commandText) { using (SqlCommand command = connection.CreateCommand(commandText)) { return command.ExecuteReader(); } }
public static int ExecuteNonQuery(this SqlConnection connection, string commandText) { using (SqlCommand command = connection.CreateCommand(commandText)) { return command.ExecuteNonQuery(); } }
/// <summary> /// Gets the names of all tables in the current database /// </summary> public static Dictionary<string, SqlDbType> GetColumns(this SqlConnection connection, string tableName, SqlTransaction transaction = null) { var results = new Dictionary<string, SqlDbType>(); using (var command = connection.CreateCommand()) { if (transaction != null) { command.Transaction = transaction; } command.CommandText = $"SELECT [COLUMN_NAME] AS 'name', [DATA_TYPE] AS 'type' FROM [INFORMATION_SCHEMA].[COLUMNS] WHERE [TABLE_NAME] = '{tableName}'"; using (var reader = command.ExecuteReader()) { while (reader.Read()) { var name = (string)reader["name"]; var typeString = (string)reader["type"]; var type = GetDbType(typeString); results[name] = type; } } } return results; }
public static bool TableExists(this SqliteConnection connection, string name) { using (var reader = connection.CreateCommand($"PRAGMA table_info(\"{name}\")").ExecuteReader()) { return reader.Read(); } }
public static int ExecuteUpdateQuery(this IDbConnection connection, string sql, Action<IDbCommand> prepare) { var command = connection.CreateCommand(); command.CommandText = sql; prepare(command); return command.ExecuteNonQuery(); }
static DbCommand GetCommand(this DbConnection connection, string toExecute) { DbCommand command = connection.CreateCommand(); command.CommandText = toExecute; return command; }
public static IDbCommand CreateCommand(this IDbProvider dbProvider, string sql, CommandType cmdType = CommandType.Text, Func<IDbCommand, IDbDataParameter[]> getParas = null) { var conn = dbProvider.CreateConnection(); if (conn.State != ConnectionState.Open) conn.Open(); var trans = conn.BeginTransaction(); var cmd = dbProvider.CreateCommand(conn); IDbDataParameter[] paras = null; if (getParas != null) paras = getParas(cmd); dbProvider.PrepareCommand(ref cmdType, ref sql, ref paras); cmd.CommandType = cmdType; cmd.CommandText = sql; if (paras != null && paras.Length > 0) { foreach (var p in paras) { dbProvider.AttachParameter(cmd, p); } } cmd.Transaction = trans; return cmd; }
/// <summary> /// An overload of CreateCommand that accepts a command text /// </summary> /// <param name="factory">The connection for which the command is created</param> /// <param name="query">The command text for the command</param> /// <returns>A command for the connection with command text set</returns> public static DbCommand CreateCommand(this DbConnection connection, string query) { var command = connection.CreateCommand(); command.CommandText = query; return command; }
public static SqlCommand CreateCommand(this SqlConnection connection, string commandText, CommandType commandType) { var command = connection.CreateCommand(); command.CommandText = commandText; command.CommandType = commandType; return command; }
public static DbCommand CreateCommand(this DbProviderFactory factory, Action<DbCommand> setup) { DbCommand command = factory.CreateCommand(); setup(command); return command; }
/// <summary> /// Gets a single Poison Message, including message body and exception details. /// </summary> public static PoisonMessageInfo GetPoisonMessage(this SqlConnection connection, Guid conversation_handle) { using (var cmd = connection.CreateCommand()) { var param = cmd.CreateParameter(); param.ParameterName = "@conversation_handle"; param.Value = conversation_handle; param.DbType = DbType.Guid; cmd.Parameters.Add(param); cmd.CommandText = Query_PosionMessage; using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { var message = new PoisonMessageInfo() { ConversationHandle = reader.GetGuid(0), InsertDateTimeUTC = reader.GetDateTime(1), QueueName = reader.GetString(2), ServiceName = reader.GetString(3), OriginServiceName = reader.GetString(4), Retries = reader.GetInt32(5), ErrorCode = reader.GetString(6), ErrorMessage = reader.GetString(7), MessageBody = reader.GetSqlBytes(8).Buffer, }; return message; } return null; } } }
/// <summary> /// 创建一个执行Sql语句的DbCommand /// </summary> /// <param name="sql"></param> /// <returns></returns> public static DbCommand CreateSqlStringCommand(this DbProviderFactory dbFactory, string sql) { DbCommand retValue = dbFactory.CreateCommand(); retValue.CommandText = sql; return retValue; }
public static NpgsqlCommand CreateCommand(this NpgsqlConnection conn, string command) { var cmd = conn.CreateCommand(); cmd.CommandText = command; return cmd; }
public static string DumpCSharpClass(this IDbConnection connection, string sql, string className = "") { var cmd = connection.CreateCommand(); cmd.CommandText = sql; var reader = cmd.ExecuteReader(); var builder = new StringBuilder(); do { if (reader.FieldCount <= 1) continue; className = string.IsNullOrWhiteSpace(className) ? "Info" : className; builder.AppendLine("public class " + className); builder.AppendLine("{"); var schema = reader.GetSchemaTable(); foreach (DataRow row in schema.Rows) { var type = (Type) row["DataType"]; var name = TypeAliases.ContainsKey(type) ? TypeAliases[type] : type.Name; var isNullable = (bool) row["AllowDBNull"] && NullableTypes.Contains(type); var collumnName = (string) row["ColumnName"]; builder.AppendLine(string.Format("\tpublic {0}{1} {2} {{ get; set; }}", name, isNullable ? "?" : string.Empty, collumnName)); } builder.AppendLine("}"); builder.AppendLine(); } while (reader.NextResult()); return builder.ToString(); }
public static SQLiteCommand PrepareCommand(this SQLiteConnection cxion, string sqlCmd) { var cmd = cxion.CreateCommand(); cmd.CommandText = sqlCmd; cmd.Prepare(); return cmd; }
/// <summary> /// Returns the current number of poison messages from each queue. The results are NOT buffered (yield return from the internal reader). /// </summary> public static IEnumerable<QueueCount> GetPoisonMessageCountForQueue(this IDbConnection connection, params string[] queues) { if (!queues.Any()) { throw new ArgumentException("Need at least 1 queue to query"); } using (var cmd = connection.CreateCommand()) { var paramString = ""; for (int i = 0; i < queues.Length; i++) { var param = cmd.CreateParameter(); param.ParameterName = "@p" + i; param.Value = queues[i].Trim(); param.DbType = DbType.String; cmd.Parameters.Add(param); paramString += param.ParameterName; if (i != queues.Length - 1) { paramString += ","; } } cmd.CommandText = string.Format("SELECT QueueName, COUNT(*) FROM FailedMessage WITH (NOLOCK) WHERE QueueName IN ({0}) GROUP BY QueueName", paramString); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { yield return new QueueCount() { Name = reader.GetString(0), Count = reader.GetInt32(1) }; } } } }
/// <summary> /// Create a text command with the given SQL command text. /// </summary> /// <param name="connection">The connection to create the command from.</param> /// <param name="commandText">The command text to use.</param> /// <returns>A new text command with the given command text.</returns> public static IDbCommand CreateTextCommand(this IDbConnection connection, string commandText) { var command = connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = commandText ?? String.Empty; return command; }
public static DbDataReader ExecuteReader(this DbConnection connection, string commandText) { var command = connection.CreateCommand(); command.CommandText = commandText; return command.ExecuteReader(); }
/// <summary> /// Returns the estimated size of each queue. The results are NOT buffered (yield return from the internal reader). /// </summary> public static IEnumerable<QueueCount> GetEstimatedRowCountInQueue(this IDbConnection connection, params string[] queues) { if (!queues.Any()) { throw new ArgumentException("Need at least 1 queue to query"); } using (var cmd = connection.CreateCommand()) { var paramString = ""; for (int i = 0; i < queues.Length; i++) { var param = cmd.CreateParameter(); param.ParameterName = "@p" + i; param.Value = queues[i].Trim(); param.DbType = DbType.String; cmd.Parameters.Add(param); paramString += param.ParameterName; if (i != queues.Length - 1) { paramString += ","; } } cmd.CommandText = string.Format(Query_QueuePartition, paramString); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { yield return new QueueCount() { Name = reader.GetString(0), Count = (int)reader.GetInt64(1) }; } } } }
public static DbCommand CreateCommand(this DbConnection connection, string commandText, params object[] parameters) { if (connection == null) throw new ArgumentNullException("connection"); return CreateCommandImpl(GetProviderFactory(connection).CreateCommandBuilder(), connection.CreateCommand(), commandText, parameters); }
public static DbCommand CreateTextCommand(this DbConnection dbconn, string query) { DbCommand command = dbconn.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = query; return command; }
public static object ExecuteScalar(this SqlConnection self, string query) { SqlCommand cmd = self.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = query; return cmd.ExecuteScalar(); }
public static void ExecuteSql(this IDbConnection connection, string sql) { using (var command = connection.CreateCommand()) { command.CommandText = sql; command.ExecuteNonQuery(); } }
/// <summary> /// Runs a proc. /// </summary> /// <param name="db">Open data connection</param> /// <param name="sqlstr">SQL string</param> /// <param name="_params">List of parameters to use with the SQL</param> public static int ExecuteProcedure(this MySqlConnection db, String name, params MySqlParameter[] _params) { MySqlCommand cmd = db.CreateCommand(); cmd.CommandText = name; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange(_params); return cmd.ExecuteNonQuery(); }
///<summary>Creates a DbCommand.</summary> ///<param name="connection">The connection to create the command for.</param> ///<param name="sql">The SQL of the command.</param> public static DbCommand CreateCommand(this DbConnection connection, string sql) { if (connection == null) throw new ArgumentNullException("connection"); var retVal = connection.CreateCommand(); retVal.CommandText = sql; return retVal; }
public static void DropAllObjects(this IDbConnection connection) { using (var cmd = connection.CreateCommand()) { cmd.CommandType = CommandType.Text; var stmts = CreateDropAllObjectsStatements(cmd); ExecuteDropStatements(stmts, cmd); } }
/// <summary> /// Creates a sqlcommand /// </summary> /// <param name="connection"> /// A <see cref="SqlConnection"/> /// </param> /// <param name="commandText"> /// A <see cref="System.String"/> of the sql query. /// </param> /// <param name="commandType"> /// A <see cref="CommandType"/> of the query type. /// </param> /// <returns> /// A <see cref="SqlCommand"/> /// </returns> public static SqlCommand GetCommand(this SqlConnection connection, string commandText, CommandType commandType) { SqlCommand command = connection.CreateCommand(); command.CommandTimeout = connection.ConnectionTimeout; command.CommandType = commandType; command.CommandText = commandText; return command; }
public static NpgsqlCommand CreateSprocCommand(this NpgsqlConnection conn, string command) { var cmd = conn.CreateCommand(); cmd.CommandText = command; cmd.CommandType = CommandType.StoredProcedure; return cmd; }