public DataTable Search(string Id, string AnmaltAv, string Anmalan, string UtvecklatAv, string UtvecklarKommentar, string TestatAv, string TestNotering, string ProjektId) { StringBuilder sb = new StringBuilder(); sb.AppendLine("EXEC SP_FIND_TESTITEM '" + Id + "','" + Anmalan + "','" + UtvecklarKommentar + "','" + TestNotering + "','" + AnmaltAv + "','" + UtvecklatAv + "','" + TestatAv + "','" + ProjektId + "'"); return(DataSQLQuery.ExecuteSQL(sb.ToString(), true).Tables[0]); }
private DataTable GetDatabases() { StringBuilder sb = new StringBuilder(); sb.AppendLine("USE Master"); sb.AppendLine("SELECT NAME FROM master.dbo.sysdatabases"); sb.AppendLine(" ORDER BY NAME"); return(DataSQLQuery.ExecuteSQL(sb.ToString(), true).Tables[0]); }
private void AddPersHistStatisticFromDbs(DataRow dr, string databaseName, string tableName, string fieldName) { StringBuilder sb = new StringBuilder(); int Antal = 0; sb.Clear(); sb.AppendLine("USE [" + databaseName + "]"); sb.AppendLine("SELECT COUNT(*) - (SELECT COUNT(*) FROM BA_ANSTALLD_GRUND) ANTAL FROM " + tableName); Antal = (int)DataSQLQuery.ExecuteSQL(sb.ToString(), true).Tables[0].Rows[0]["ANTAL"]; dr[fieldName] = Antal; }
private void AddVersionsinfoFromDbs(DataRow dr, string databaseName, string tableName, string fieldName1, string fieldName2) { StringBuilder sb = new StringBuilder(); sb.Clear(); sb.AppendLine("USE [" + databaseName + "]"); sb.AppendLine("SELECT TOP 1 LOP_NR_VERSION, LOP_NR_VER FROM " + tableName); sb.AppendLine("ORDER BY LOP_NR_VERSION DESC"); DataTable dt = DataSQLQuery.ExecuteSQL(sb.ToString(), true).Tables[0]; string tmp = dt.Rows[0]["LOP_NR_VERSION"].ToString(); dr[fieldName1] = tmp.Replace(",", "."); tmp = dt.Rows[0]["LOP_NR_VER"].ToString(); dr[fieldName2] = tmp.Substring(0, tmp.Length - 2); }
public DataTable SorteradProjektLista() { StringBuilder sb3 = new StringBuilder(); StringBuilder sb = new StringBuilder(); sb.AppendLine("SELECT LOP_NR_PROJEKT ID , PROJEKT_ID + ' - ' + PROJEKTNAMN PROJEKT FROM BA_PROJEKT"); sb.AppendLine("WHERE AKTIV = 'J'"); sb.AppendLine("AND GETDATE() BETWEEN ISNULL(DATUM_FOM, '1900-01-01') AND ISNULL(DATUM_TOM,'2099-12-31') "); sb.AppendLine("ORDER BY PROJEKT_ID, PROJEKTNAMN"); DataTable dt = DataSQLQuery.ExecuteSQL(sb.ToString(), true).Tables[0]; DataRow dr = dt.NewRow(); dr["ID"] = -1; dr["PROJEKT"] = "--Inget värde--"; dt.Rows.InsertAt(dr, 0); return(dt); }
private void AddStamplingStatisticFromDbs(DataRow dr, string databaseName, string tableName, string fieldName, bool isTidreg) { StringBuilder sb = new StringBuilder(); int Antal = 0; sb.Clear(); sb.AppendLine("USE [" + databaseName + "]"); sb.AppendLine("SELECT COUNT(*) ANTAL FROM " + tableName); if (isTidreg) { sb.AppendLine("WHERE STATUS_TID2 IS NOT NULL"); } else { sb.AppendLine("WHERE STATUS_TID2 IS NULL"); } Antal = (int)DataSQLQuery.ExecuteSQL(sb.ToString(), true).Tables[0].Rows[0]["ANTAL"]; dr[fieldName] = Antal; }
public DataTable GetDbsStatsFromProcedure() { StringBuilder sb = new StringBuilder(); sb.AppendLine("DECLARE @DbsName sysname"); sb.AppendLine("DECLARE @SQL_ANTALANVANDARE nvarchar(4000)"); sb.AppendLine("DECLARE @AntalAnvandare int"); sb.AppendLine("DECLARE @AntalAnstallda int"); sb.AppendLine("DECLARE @AntalStamplingar int "); sb.AppendLine("DECLARE @AntalProjekt int"); sb.AppendLine("DECLARE @AntalMedarbSamt int"); sb.AppendLine("DECLARE @AntalKonteringsbegrepp int"); sb.AppendLine("DECLARE @AntalLoner int"); sb.AppendLine("DECLARE @AntalRevisionstillfallen int"); sb.AppendLine("DECLARE @AntalResor int"); sb.AppendLine("DECLARE @AntalRekryt int"); sb.AppendLine("USE Master"); sb.AppendLine("IF OBJECT_ID('tempdb..#TMP_DATABASER') IS NOT NULL"); sb.AppendLine("DROP TABLE #TMP_DATABASER"); sb.AppendLine("CREATE TABLE #TMP_DATABASER"); sb.AppendLine("("); sb.AppendLine(" NAME VARCHAR(50),"); sb.AppendLine(" ANTAL_ANVANDARE INT,"); sb.AppendLine(" ANTAL_ANSTALLDA INT,"); sb.AppendLine(" ANTAL_STAMPLINGAR INT,"); sb.AppendLine(" ANTAL_PROJEKT INT,"); sb.AppendLine(" ANTAL_MEDARB_SAMT INT,"); sb.AppendLine(" ANTAL_KONTBGR INT,"); sb.AppendLine(" ANTAL_LONER INT,"); sb.AppendLine(" ANTAL_REVISIONSTILLFALLEN INT,"); sb.AppendLine(" ANTAL_RESOR INT,"); sb.AppendLine(" ANTAL_REKRYT INT"); sb.AppendLine(")"); sb.AppendLine("DECLARE TEST_CURSOR CURSOR FOR "); sb.AppendLine("SELECT NAME FROM master.dbo.sysdatabases"); sb.AppendLine(" ORDER BY 1"); sb.AppendLine("OPEN TEST_CURSOR"); sb.AppendLine("FETCH NEXT FROM TEST_CURSOR "); sb.AppendLine(" INTO @DbsName"); sb.AppendLine("WHILE @@FETCH_STATUS = 0"); sb.AppendLine("BEGIN"); sb.AppendLine(" BEGIN TRY"); sb.AppendLine(" --Antal användare"); sb.AppendLine(" SET @SQL_ANTALANVANDARE = 'USE ' + @DbsName + ' SELECT @AntalAnvandare = COUNT(*) FROM BA_ANV'"); sb.AppendLine(" EXEC sp_executesql @SQL_ANTALANVANDARE,N'@AntalAnvandare int output', @AntalAnvandare output"); sb.AppendLine(" --Antal anställda"); sb.AppendLine(" SET @SQL_ANTALANVANDARE = 'USE ' + @DbsName + ' SELECT @AntalAnstallda = COUNT(*) FROM BA_ANSTALLD_GRUND'"); sb.AppendLine(" EXEC sp_executesql @SQL_ANTALANVANDARE,N'@AntalAnstallda int output', @AntalAnstallda output"); sb.AppendLine(" --Antal stämplingar"); sb.AppendLine(" SET @SQL_ANTALANVANDARE = 'USE ' + @DbsName + ' SELECT @AntalStamplingar = COUNT(*) FROM TI_STAMPLINGAR'"); sb.AppendLine("EXEC sp_executesql @SQL_ANTALANVANDARE,N'@AntalStamplingar int output', @AntalStamplingar output"); sb.AppendLine(" --Antal projekt"); sb.AppendLine(" SET @SQL_ANTALANVANDARE = 'USE ' + @DbsName + ' SELECT @AntalProjekt = COUNT(*) FROM PR_PROJEKT'"); sb.AppendLine(" EXEC sp_executesql @SQL_ANTALANVANDARE,N'@AntalProjekt int output', @AntalProjekt output"); sb.AppendLine(" --Antal medarbetarsamtal"); sb.AppendLine(" SET @SQL_ANTALANVANDARE = 'USE ' + @DbsName + ' SELECT @AntalMedarbSamt = COUNT(*) FROM PA_MEDARB_SAMT'"); sb.AppendLine(" EXEC sp_executesql @SQL_ANTALANVANDARE,N'@AntalMedarbSamt int output', @AntalMedarbSamt output"); sb.AppendLine(" --Antal konteringsbegrepp"); sb.AppendLine(" SET @SQL_ANTALANVANDARE = 'USE ' + @DbsName + ' SELECT @AntalKonteringsbegrepp = COUNT(*) FROM BA_KONTBGR'"); sb.AppendLine(" EXEC sp_executesql @SQL_ANTALANVANDARE,N'@AntalKonteringsbegrepp int output', @AntalKonteringsbegrepp output"); sb.AppendLine(" --Antal löner"); sb.AppendLine(" SET @SQL_ANTALANVANDARE = 'USE ' + @DbsName + ' SELECT @AntalLoner = COUNT(*) FROM BA_LONPER_PERS'"); sb.AppendLine(" EXEC sp_executesql @SQL_ANTALANVANDARE,N'@AntalLoner int output', @AntalLoner output"); sb.AppendLine(" --Antal revisionstillfällen"); sb.AppendLine(" SET @SQL_ANTALANVANDARE = 'USE ' + @DbsName + ' SELECT @AntalRevisionstillfallen = COUNT(*) FROM LR_REVTILLF'"); sb.AppendLine(" EXEC sp_executesql @SQL_ANTALANVANDARE,N'@AntalRevisionstillfallen int output', @AntalRevisionstillfallen output"); sb.AppendLine(" --Antal resor"); sb.AppendLine(" SET @SQL_ANTALANVANDARE = 'USE ' + @DbsName + ' SELECT @AntalResor = COUNT(*) FROM RE_RESOR'"); sb.AppendLine(" EXEC sp_executesql @SQL_ANTALANVANDARE,N'@AntalResor int output', @AntalResor output"); sb.AppendLine(" --Antal rekryteringar"); sb.AppendLine(" SET @SQL_ANTALANVANDARE = 'USE ' + @DbsName + ' SELECT @AntalRekryt = COUNT(*) FROM PA_JOBB'"); sb.AppendLine("EXEC sp_executesql @SQL_ANTALANVANDARE,N'@AntalRekryt int output', @AntalRekryt output"); sb.AppendLine(" INSERT INTO #TMP_DATABASER (NAME, ANTAL_ANVANDARE, ANTAL_ANSTALLDA, ANTAL_STAMPLINGAR, ANTAL_PROJEKT, ANTAL_MEDARB_SAMT, ANTAL_KONTBGR, "); sb.AppendLine(" ANTAL_LONER, ANTAL_REVISIONSTILLFALLEN, ANTAL_RESOR, ANTAL_REKRYT)"); sb.AppendLine(" VALUES (@DbsName, @AntalAnvandare, @AntalAnstallda, @AntalStamplingar, @AntalProjekt, @AntalMedarbSamt, @AntalKonteringsbegrepp,"); sb.AppendLine(" @AntalLoner, @AntalRevisionstillfallen, @AntalResor, @AntalRekryt)"); sb.AppendLine(" END TRY "); sb.AppendLine(" BEGIN CATCH"); sb.AppendLine(" --Empty catch"); sb.AppendLine(" END CATCH "); sb.AppendLine(" FETCH NEXT FROM TEST_CURSOR "); sb.AppendLine(" INTO @DbsName"); sb.AppendLine("END "); sb.AppendLine("CLOSE TEST_CURSOR"); sb.AppendLine("DEALLOCATE TEST_CURSOR"); sb.AppendLine("SELECT * FROM #TMP_DATABASER "); return(DataSQLQuery.ExecuteSQL(sb.ToString(), true).Tables[0]); }