Monday 18 April 2016

SQL browser gniithelp.blogspot.in

INTRODUCTION

This topic's title may look a bit weird , but yes today's article is about how to createsql tables browser for MMSQL server databases .

BACKGROUND

To explore your tables you need to run SQL server management studio which needs a lot ofRAM and time to surf from table to another , that's why a lot of people prefer to use an explorer or whatever to view the tables in a better , faster and easier way .
So that's why I made this sample .

HOW TO USE

Well , this project contains many different features because it's based on executing commands and import the results from the sql server management studio to the datagridview controller .
• How it basically works ?
It's too easy , just by three steps you'll be done with it :
  1. Connect to the SQL server
  2. Execute the command
  3. Import the result of the executed command
Let's start explaining how it works part by part ( including pictures )
As you can see that the groupbox which is surrounded by red color is where we'll put our connection settings in :
  • Server name
  • User name
  • Password
and the settings must be the same as the settings of your sql server's connection
After you did add your settings correctly you have to connect using "Connect" button , here the result comes out 
  • You are connected
  • Logs updated
  • The groupbox that contains the grid & browsing controllers is enbaled
  • The names of the databases are imported
Amazing, right ? Let's explain how it works then :
First of all , create 3 strings that will contain your connection settings :
 public string server { get; set; }
 public string user { get; set; }
 public string pass { get; set; }
and do not forget to Import the NameSpace required for the connection , using this code :
using System.Data.Sql;
Now to make the button functional one , add this code into it :
server = s.Text;
            user = us.Text;
            pass = p.Text;
            Properties.Settings.Default.Save();
            str = @"Data Source=" + server + ";uid=" + user + ";pwd=" + pass;
            string selectCommandText = "select name from sys.databases order by name";
            SqlConnection selectConnection = new SqlConnection(str);
            DataTable dataTable = new DataTable();
       
Now the settings are saved so you only have to added a try..catch expression to connect , but first let me explain what that string called selectCommandText means
select name from sys.databases order by name
this is a query that gets the names of all the databases in the sql server to add them into our ComboBox called databases , example :
now append this code to the buton's code :
 try
            {
                selectConnection.Open();
                new SqlDataAdapter(selectCommandText, selectConnection).Fill(dataTable);
                selectConnection.Close();
                if (dataTable.Rows.Count > 0)
                {
                    databases.DataSource = (object)dataTable;
                    databases.DisplayMember = "name";
                    databases.ValueMember = "name";
                }
 catch (Exception ex)
            {
}
This code will :
  1. Connect to the server
  2. Execute our command
  3. import the values of the rows under the columns named "name" and add the results into the ComboBox

How to Import the table after getting connected & the names of databases Imported ?
Easy , thanks to the list of names we imported we can import the tables that each one of them contains , just by selecting one of the databases , example :
Let's add this code to event SelectedIndexChanged from the ComboBox that contains the databases :
string str = @"Data Source=" + server + ";uid=" + user + ";pwd=" + pass;
            string selectCommandText = "USE "+databases.Text+" SELECT * FROM sys.Tables ";
            SqlConnection selectConnection = new SqlConnection(str);
            DataTable dataTable = new DataTable();
try
            {
                
                new SqlDataAdapter(selectCommandText, selectConnection).Fill(dataTable);
                if (dataTable.Rows.Count > 0)
                {
                    tables.DataSource = (object)dataTable;
                    tables.DisplayMember = "name";
                    tables.ValueMember = "name";
                }
catch (Exception ex)
            {
}
This code is too simple , it will connect again ( just to avoid the risk of losing connection ) and execute the command again , but the SQL commands this time are different :
USE DATABASE_NAME SELECT * FROM sys.Tables
this command will get all the names of tables from a specific database , and our code will simply add the names into the second ComboBox we have ( called tables )
Now after we finally connected and got the coordinates of the table that we want to show , we have to add this string variable to contain our Importing command :
 public string Gcommand { get; set; }
Now double click on the button and add this code into it :
Gcommand = "USE " + databases.Text + " SELECT * FROM " + tables.Text;

           try
           {
               string str = @"Data Source=" + server + ";uid=" + user + ";pwd=" + pass;
               string selectCommandText = Gcommand;
               SqlConnection selectConnection = new SqlConnection(str);
               DataSet dataSet = new DataSet();
               selectConnection.Open();
               new SqlDataAdapter(selectCommandText, selectConnection).Fill(dataSet);
               selectConnection.Close();
               this.Table.DataSource = (object)dataSet.Tables[0];
               MessageBox.Show("Table '" + tables.Text + "' Imported successfully .", "Result", MessageBoxButtons.OK, MessageBoxIcon.Information);
               Logs.AppendText(Environment.NewLine+@"[*] Table imported using this following command : """+Gcommand+@"""");
           }
           catch (Exception)
           {


           }
Using this button we will generate a command based on the coordinates of the database & table we chose , example :
USE Students SELECT * FROM Students 
/*
selected database : Students
selected table : Students 
*/
◘ Result from the SQL management studio :
◘ Result from the sample :

That's it , normally this is enough but I did add another option that allows you to execute complex commands manually , How ?
For example you want to show only one column inside of a table , you have to write the command that does this by your own , example :
USE Students SELECT Name FROM Students
/*
selected database : Students
selected table : Students
selected column : Name
*/
All you have to do in this case is to write your command manually , like this example below shows :
That's all !

NOTES :

  • This project is just a sample , you can base a complex project on it .
  • This project will save your RAM .
  • This project will save your time .
  • This project will make surfing your tables ways easier
  • The demo is free to share ( including some rights like : Codeproject's rights & my informations )



RELATED TOPICS

No comments:

Post a Comment