/// <summary> /// Sql查询返回DataTable,参数化查询 /// </summary> /// <param name="sql">Sql语句</param> /// <param name="parameters">参数(可选)</param> /// <returns></returns> public DataTable GetDataTableWithSql(string sql, List <DbParameter> parameters = null) { 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) { parameters.ForEach(item => 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]); } } }
/// <summary> /// 执行无返回值的Sql语句 /// </summary> /// <param name="sql">Sql语句</param> /// <param name="parameters">查询参数(可选)</param> /// <returns>返回受影响的行数</returns> public int ExecuteSql(string sql, List <DbParameter> parameters = null) { 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 = conn.CreateCommand()) { cmd.Connection = conn; cmd.CommandText = sql; if (parameters != null && parameters.Count > 0) { parameters.ForEach(param => cmd.Parameters.Add(param)); } count = cmd.ExecuteNonQuery(); } } return(count); }
/// <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) })); }