//public static List<NestedStmtWhereClauses> StatementWhereClauses = new List<NestedStmtWhereClauses>();

        public static void ParseWorkload(String sqlFilename)
        {
            EDbVendor dbVendor = EDbVendor.dbvmssql;

            Console.WriteLine("\n");
            Console.WriteLine("Selected SQL dialect: " + ((dbVendor.ToString() == "dbvmssql") ? "Transact SQL for Microsoft SQL Server" : "SQL for Oracle."));
            Console.WriteLine("\n");

            TGSqlParser sqlparser = new TGSqlParser(dbVendor);

            sqlparser.sqlfilename = sqlFilename;

            int ret = sqlparser.parse();

            if (ret == 0)
            {
                Console.WriteLine("###################### Parsing Started : " + sqlFilename);

                // Print output Headers
                var consoletable = new ConsoleTable("Statement Number", "Statement Type", "Parse OK ?", "Query Text [Truncated]");

                for (int i = 0; i < sqlparser.sqlstatements.size(); i++)
                {
                    ESqlStatementType sqlStatementType = sqlparser.sqlstatements.get(i).sqlstatementtype;                   // Get the statementtype of the sql qwuery

                    if (sqlStatementType == ESqlStatementType.sstselect || sqlStatementType == ESqlStatementType.sstupdate) // Take the statement in dictionary only if it is a DML(SELECT/UPDATE) statement
                    {
                        // We have taken iterate statement inside if block since parsong for "Use Database", "Go", "Set variable values", etc is not useful for our applicaiton scope.
                        iterateStmt(sqlparser.sqlstatements.get(i));


                        // Create a new query object
                        SQLQueryStmt query = new SQLQueryStmt();

                        // get the query text of the sql statement from the workload
                        query.QueryText = sqlparser.sqlstatements.get(i).String;

                        // check if this statement appeared for the first time and add it to the dictionary, else increment the count of already added statement from the dictionary.
                        if (Utilities.DictWorkloadQueries.Where(qry => qry.Value.QueryText == query.QueryText).Any())                    // If the statement is occuring again.
                        {
                            Guid guid = Utilities.DictWorkloadQueries.FirstOrDefault(qry => qry.Value.QueryText == query.QueryText).Key; // Get the queryId of the SQL already in the Dictionary.
                            Utilities.DictWorkloadQueries[guid].NumOfOccurences += 1;                                                    // Increment the count of occurence.
                        }
                        else                                                                                                             // If the statement occurs for the first time.
                        {
                            query.QueryId = Guid.NewGuid();                                                                              // New key for query
                            Utilities.DictWorkloadQueries.Add(query.QueryId, query);                                                     // Add the query to Dictionary.
                            Utilities.DictWorkloadQueries[query.QueryId].NumOfOccurences = 1;
                        }

                        consoletable.AddRow("Parsed Statement[" + i + "]", (sqlStatementType == ESqlStatementType.sstselect)?"SELECT": "OTHER", "Successful", query.QueryText.Replace(System.Environment.NewLine, " ").Substring(0, 35) + "...");
                    }
                }
                consoletable.Write(Format.MarkDown);
                Console.WriteLine();
                Console.WriteLine("###################### " + sqlFilename + " Parsing Complete ####################");
            }
            else
            {
                Console.WriteLine(sqlparser.Errormessage);
            }
        }
Ejemplo n.º 2
0
        /// <summary>
        /// Checks the valididty of input parameters by connecting to the database using given credentials and also validating permissions of the user.
        /// </summary>
        /// <param name="DBFilepath">the access path of the actual Database file.</param>
        /// <param name="UserName">login username for the database.</param>
        /// <param name="Password">password for the the database login.</param>
        /// <param name="DBName">name of the database to be connected.</param>
        /// <returns>Boolean</returns>
        public Boolean CheckInputParameters(string ServerName, string DBFilepath, string UserName, string Password, string DBName)
        {
            bool GreenLights = false;

            /*
             * This implementation is pending
             * check the connection here and send true if all works fine. Check if all works fine by querying if the database exists.
             * check the user permission of the login to be of DBO or Sysadmin levels.
             *
             * The following code does a test run of the access to the database after the above mentioned initial checks are done.
             */

            /*
             * The following code does a test run of the access to the database after the above mentioned initial checks are done.
             */
            // AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL13.LEARNINGOWL\MSSQL\DATA\AdventureWorks2016_Data.mdf;

            string ConnectionString = "";

            if (UserName == "" && Password == "")
            {
                //Connection string example with Windows Authentication mode or Integrated Security mode.
                ConnectionString = @"Data Source= " + ServerName + @";
                          Initial Catalog=" + DBName + @";
                          Asynchronous Processing=True;
                          Integrated Security=True;
                          Connect Timeout=30";
            }
            else
            {
                // Connection string example with UserName and Password:
                ConnectionString = @"Data Source=" + ServerName + @";
                          Initial Catalog=" + DBName + @";
                          User Id= " + UserName + @" ;
                          Password= "******";
                          Asynchronous Processing=True;
                          Connect Timeout=30";
            }

            string queryString = "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = @FilterCondition";  // WHERE ColumnName > @FilterCondition

            // Create and open the connection in a using block. This
            // ensures that all resources will be closed and disposed
            // when the code exits.
            using (SqlConnection connection =
                       new SqlConnection(ConnectionString))
            {
                // Create the Command and Parameter objects.
                SqlCommand command    = new SqlCommand(queryString, connection);
                string     paramValue = "BASE TABLE"; // Just an example. Could be anything.
                command.Parameters.AddWithValue("@FilterCondition", paramValue);

                // Open the connection in a try/catch block.
                // Create and execute the DataReader, writing the result
                // set to the console window.
                try
                {
                    // Print output Headers
                    var consoletable = new ConsoleTable("Database", "SchemaName", "TableName");

                    // Print declaration statement
                    Console.WriteLine("######################  Printing schemas and tables that are accessible to provided user credential  ####################");

                    connection.Open();

                    SqlDataReader OutputReader = command.ExecuteReader();
                    while (OutputReader.Read())
                    {
                        consoletable.AddRow(OutputReader[0], OutputReader[1], OutputReader[2]);
                        DBTable tab = new DBTable();
                        tab.Table_Schema_Name = OutputReader[1].ToString();
                        tab.name = OutputReader[2].ToString();

                        Utilities.DictTablesCatalog[tab.name] = tab;
                    }

                    OutputReader.Close();

                    consoletable.Write(Format.MarkDown);
                    Utilities.ExportToPDF(consoletable, "PrintSchema");
                    Console.WriteLine();

                    GreenLights = true;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }


            return(GreenLights);
        }