Beispiel #1
0
        /// <summary>
        /// 通过数据库连接字符串和Sql语句查询返回DataTable,参数化查询
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public DataTable GetDataTableWithSql(string sql, List <DbParameter> parameters)
        {
            DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);

            using (DbConnection conn = dbProviderFactory.CreateConnection())
            {
                conn.ConnectionString = _conStr;
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.Connection  = conn;
                    cmd.CommandText = sql;
                    if (parameters != null && parameters.Count > 0)
                    {
                        foreach (var item in parameters)
                        {
                            cmd.Parameters.Add(item);
                        }
                    }

                    DbDataAdapter adapter = dbProviderFactory.CreateDataAdapter();
                    adapter.SelectCommand = cmd;
                    DataSet table = new DataSet();
                    adapter.Fill(table);
                    cmd.Parameters.Clear();

                    return(table.Tables[0]);
                }
            }
        }
Beispiel #2
0
        /// <summary>
        /// 通过数据库连接字符串和Sql语句查询返回DataTable
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <returns></returns>
        public DataTable GetDataTableWithSql(string sql)
        {
            DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);

            using (DbConnection conn = dbProviderFactory.CreateConnection())
            {
                conn.ConnectionString = _conStr;
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.Connection  = conn;
                    cmd.CommandText = sql;

                    DbDataAdapter adapter = dbProviderFactory.CreateDataAdapter();
                    adapter.SelectCommand = cmd;
                    DataSet table = new DataSet();
                    adapter.Fill(table);

                    return(table.Tables[0]);
                }
            }
        }
Beispiel #3
0
        /// <summary>
        /// 执行无返回值的Sql语句
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="paramters"></param>
        public int ExecuteSql(string sql, List <DbParameter> paramters)
        {
            int count = 0;
            DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);

            using (DbConnection conn = dbProviderFactory.CreateConnection())
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                using (DbCommand cmd = dbProviderFactory.CreateCommand())
                {
                    cmd.Connection  = conn;
                    cmd.CommandText = sql;

                    if (paramters != null && paramters.Count > 0)
                    {
                        foreach (var item in paramters)
                        {
                            cmd.Parameters.Add(item);
                        }
                    }
                    count = cmd.ExecuteNonQuery();

                    return(count);
                }
            }
        }
Beispiel #4
0
        /// <summary>
        /// 获取数据库中的所有表
        /// </summary>
        /// <param name="schemaName">模式(架构)</param>
        /// <returns></returns>
        public override List <DbTableInfo> GetDbAllTables(string schemaName = null)
        {
            DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);
            string            dbName            = string.Empty;

            using (DbConnection conn = dbProviderFactory.CreateConnection())
            {
                conn.ConnectionString = _conStr;
                dbName = conn.Database;
            }
            string sql = @"SELECT TABLE_NAME as TableName,table_comment as Description 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = @dbName";

            return(GetListBySql <DbTableInfo>(sql, new List <DbParameter> {
                new MySqlParameter("@dbName", dbName)
            }));
        }
Beispiel #5
0
        /// <summary>
        /// 执行无返回值的Sql语句
        /// </summary>
        /// <param name="sql">Sql语句</param>
        public int ExecuteSql(string sql)
        {
            int count = 0;
            DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);

            using (DbConnection conn = dbProviderFactory.CreateConnection())
            {
                conn.ConnectionString = _conStr;
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                using (DbCommand cmd = dbProviderFactory.CreateCommand())
                {
                    cmd.Connection  = conn;
                    cmd.CommandText = sql;
                    count           = cmd.ExecuteNonQuery();

                    return(count);
                }
            }
        }
Beispiel #6
0
 /// <summary>
 /// 构造函数
 /// </summary>
 /// <param name="dbType">数据库类型</param>
 /// <param name="conStr">连接名或连接字符串</param>
 public DbHelper(DatabaseType dbType, string conStr)
 {
     _dbType = dbType;
     _conStr = DbProviderFactoryHelper.GetConStr(conStr);
 }