C# this.CreateCommand方法代码示例

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