Home > Articles > .Net Framework Articles > Simple ASP.NET Code to Find All the SQL Statements Used in a Project

Simple ASP.NET Code to Find All the SQL Statements Used in a Project

by Karthik Sachin   on Jul 26, 2016   Category: .Net Framework  | Level: Beginner  |  Views: 524    |  Points: 100   
Like this article? Bookmark and Share:
In this article, I will explain how to fetch all the SQL statements which we were used in our project and show them in separate results.

Background

In our project we created plenty of tables and for some reason, some of them are used in our project and some of them are not used. We planned to move the project to the server, but we couldn't find in one shot what tables exactly were used in the project.

In a normal case we should find them manually one by one by using the find command. It was a very time consuming process. I tried to get some of the tools to find and display the result, but i couldn't get any so I decided to write this small tool to find them.

Aspx code

  1. <asp:Button ID="btnSqlfinder" runat="server" Text="SQL Finder" OnClick="btnSqlfinder_Click"/>    
  2.    <asp:TextBox ID="TxtResult" runat="server" TextMode="MultiLine" Height="1000px" Width="1000px" ></asp:TextBox>  
Code behind code
  1. protected void btnSqlfinder_Click(object sender, EventArgs e)  
  2. {  
  3.     //Defining the path of directory where all files saved  
  4.     string filepath = @ "D:\TPMS\App_Code\";  
  5.     //get the all file names inside the directory  
  6.     string[] files = Directory.GetFiles(filepath);  
  7.     //loop through the files to search file one by one   
  8.     for (int i = 0; i < files.Length; i++)  
  9.     {  
  10.         string sourcefilename = files[i];  
  11.         StreamReader sr = File.OpenText(sourcefilename);  
  12.         string sourceline = "";  
  13.         int lineno = 0;  
  14.         while ((sourceline = sr.ReadLine()) != null)  
  15.         {  
  16.             lineno++;  
  17.             //defining the Keyword for search  
  18.             if (sourceline.Contains("from"))  
  19.             {  
  20.                 //append the result to multiline text box  
  21.                 TxtResult.Text += sourcefilename + lineno.ToString() + sourceline + System.Environment.NewLine;  
  22.             }  
  23.             if (sourceline.Contains("into"))  
  24.             {  
  25.                 TxtResult.Text += sourcefilename + lineno.ToString() + sourceline + System.Environment.NewLine;  
  26.             }  
  27.             if (sourceline.Contains("set"))  
  28.             {  
  29.                 TxtResult.Text += sourcefilename + lineno.ToString() + sourceline + System.Environment.NewLine;  
  30.             }  
  31.             if (sourceline.Contains("delete"))  
  32.             {  
  33.                 TxtResult.Text += sourcefilename + lineno.ToString() + sourceline + System.Environment.NewLine;  
  34.             }  
  35.         }  
  36.     }  
  37. }  
Output



Once we click the above button, it will display the path of the file where the file is saved, line no., and the query contains the table name.

Hope it was useful to learn how to get all the tables in one shot. Kindly let me know your thoughts and feedback.

 



Like this article? Bookmark and Share:

Most viewed Articles

User Comments


No response found, be the first to review this article.

Submit feedback about this article

Please sign in to post feedback

Latest Posts