public static List <DBConfigEntity> SelectAllDataBase(string PatternDB, string connectionString, SqlConnection sqlcon = null, SqlTransaction sqlTran = null) { string StrCmd = @"SELECT name AS CompanyNameLatin FROM master..sysdatabases WHERE name Like '" + PatternDB + "%'"; SqlCommand Cmd = ErisConnectionManager.ConfigureCommand(sqlcon, sqlTran, "Master"); Cmd.CommandText = StrCmd; IDataReader dr = Cmd.ExecuteReader(); List <DBConfigEntity> LstConfig = new List <DBConfigEntity>(); while (dr.Read()) { DBConfigEntity DBConfig = new DBConfigEntity(); DBConfig.CompanyNameLatin = dr["CompanyNameLatin"].ToString(); LstConfig.Add(DBConfig); } dr.Close(); ErisConnectionManager.CloseConnection(Cmd.Connection, sqlTran); return(LstConfig); }
public static List <DBConfigEntity> SelectAllYear(string CompanyNameLatin, SqlConnection sqlcon = null, SqlTransaction sqlTran = null) { // string StrCmd = @"DECLARE @QQQ NVARCHAR(1000) //DECLARE @Query NVARCHAR(MAX) //SET @Query = 'SELECT CompanyName, CompanyNameLatin, AccYear, DBName FROM //(SELECT TOP 1 '''' AS CompanyName, '''' AS CompanyNameLatin, 0 AS AccYear, '''' AS DBName FROM master..sysdatabases' //DECLARE curs CURSOR FOR // SELECT 'SELECT CompanyName, CompanyNameLatin, AccYear, ''' + name + ''' AS DBName FROM ' + name + '..Setting' // FROM master..sysdatabases // WHERE name Like 'Eris%' //OPEN curs //FETCH NEXT FROM curs INTO @QQQ //WHILE @@FETCH_STATUS = 0 //BEGIN // SET @Query = @Query + ' //UNION //' + @QQQ // FETCH NEXT FROM curs INTO @QQQ //END //CLOSE curs //DEALLOCate curs //SET @Query = @Query + ') AS CompanyList //WHERE CompanyNameLatin = ''" + CompanyNameLatin + @"''' //EXEC (@Query);"; string StrCmd = @"DECLARE @AllTables table (DbName sysname) DECLARE @SearchDb nvarchar(200) ,@SearchSchema nvarchar(200) ,@SearchTable nvarchar(200) ,@SQL nvarchar(4000) SET @SearchDb='Eris%' SET @SearchSchema='%' SET @SearchTable='Setting' SET @SQL='SELECT ''?'' AS DbName FROM [?].sys.tables t inner join sys.schemas s on t.schema_id=s.schema_id WHERE ''?'' LIKE '''+@SearchDb+''' AND s.name LIKE '''+@SearchSchema+''' AND t.name LIKE '''+@SearchTable+'''' INSERT INTO @AllTables (DbName) EXEC sp_msforeachdb @SQL DECLARE @QQQ NVARCHAR(1000) DECLARE @Query NVARCHAR(MAX) SET @Query = 'SELECT CompanyName, CompanyNameLatin, AccYear, DBName FROM (SELECT TOP 1 '''' AS CompanyName, '''' AS CompanyNameLatin, 0 AS AccYear, '''' AS DBName FROM master..sysdatabases' DECLARE curs CURSOR FOR SELECT * FROM @AllTables ORDER BY DbName OPEN curs FETCH NEXT FROM curs INTO @QQQ WHILE @@FETCH_STATUS = 0 BEGIN SET @Query = @Query + ' UNION SELECT CompanyName, CompanyNameLatin, AccYear, ''' + @QQQ + ''' AS DBName FROM ' + @QQQ + '..Setting' FETCH NEXT FROM curs INTO @QQQ END CLOSE curs DEALLOCate curs SET @Query = @Query + ') AS CompanyList WHERE CompanyNameLatin = ''" + CompanyNameLatin + @"''' EXEC (@Query);"; SqlCommand cmd = ErisConnectionManager.ConfigureCommand(sqlcon, sqlTran, "Master"); cmd.CommandText = StrCmd; IDataReader dr = cmd.ExecuteReader(); List <DBConfigEntity> LstConfig = new List <DBConfigEntity>(); while (dr.Read()) { DBConfigEntity DBConfig = new DBConfigEntity(); DBConfig.AccYear = int.Parse(dr["AccYear"].ToString()); DBConfig.DBName = dr["DBName"].ToString(); LstConfig.Add(DBConfig); } return(LstConfig); }