Example #1
0
 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;
 }
Example #2
0
        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();
     }
 }
Example #5
0
        /// <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;
        }
Example #6
0
 public static bool TableExists(this SqliteConnection connection, string name)
 {
     using (var reader = connection.CreateCommand($"PRAGMA table_info(\"{name}\")").ExecuteReader())
     {
         return reader.Read();
     }
 }
Example #7
0
 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;
        }
Example #9
0
        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;
        }
Example #10
0
        /// <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;
 }
Example #13
0
 /// <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;
        }
Example #15
0
        public static NpgsqlCommand CreateCommand(this NpgsqlConnection conn, string command)
        {
            var cmd = conn.CreateCommand();
            cmd.CommandText = command;

            return cmd;
        }
Example #16
0
        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();
        }
Example #17
0
 public static SQLiteCommand PrepareCommand(this SQLiteConnection cxion, string sqlCmd)
 {
     var cmd = cxion.CreateCommand();
     cmd.CommandText = sqlCmd;
     cmd.Prepare();
     return cmd;
 }
Example #18
0
        /// <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) };
                    }
                }
            }
        }
Example #19
0
 /// <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();
        }
Example #21
0
        /// <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) };
                    }
                }
            }
        }
Example #22
0
        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);
        }
Example #23
0
 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();
 }
Example #27
0
        ///<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);
     }
 }
Example #29
0
 /// <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;
 }
Example #30
0
        public static NpgsqlCommand CreateSprocCommand(this NpgsqlConnection conn, string command)
        {
            var cmd = conn.CreateCommand();
            cmd.CommandText = command;
            cmd.CommandType = CommandType.StoredProcedure;

            return cmd;
        }