GNIIT HELP: SQL browser gniithelp.blogspot.in

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 createa sql 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 :

Connect to the SQL server
Execute the command
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 :

Hide Copy Code

 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 :

Hide Copy Code

using System.Data.Sql;

Now to make the button functional one , add this code into it :

Hide Copy Code

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

Hide Copy Code

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 :

Hide Copy 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 :

Connect to the server
Execute our command
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 :

Hide Copy Code

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 :

Hide Copy Code

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 :

Hide Copy Code

 public string Gcommand { get; set; }

Now double click on the button and add this code into it :

Hide Copy Code

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 :

Hide Copy Code

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 :

Hide Copy Code

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 )