/// <summary> /// 通过数据库连接字符串和Sql语句查询返回DataTable,参数化查询 /// </summary> /// <param name="sql">Sql语句</param> /// <param name="parameters">参数</param> /// <returns></returns> public virtual 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; cmd.CommandTimeout = 5 * 60; if (parameters != null && parameters?.Count > 0) { cmd.Parameters.AddRange(parameters.ToArray()); } 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="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; cmd.CommandTimeout = 5 * 60; if (paramters != null && paramters?.Count > 0) { cmd.Parameters.AddRange(paramters.ToArray()); } 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) })); }
/// <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; OracleConnectionStringBuilder builder = new OracleConnectionStringBuilder(_conStr); dbName = builder.UserID; if (schemaName.IsNullOrEmpty()) { schemaName = dbName; } string sql = @"select TABLE_NAME as ""TableName"", COMMENTS as ""Description"" from all_tab_comments where owner =:schemaName"; return(GetListBySql <DbTableInfo>(sql, new List <DbParameter> { new OracleParameter("schemaName", schemaName) })); }