SQL Browser

• Download demo - 90.5 KB
• Download project - 408.5 KB

Introduction

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

Background

To explore your tables, you need to run SQL Server Management Studio which needs a lot of RAM and time to surf from one 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 importing the results from the SQL Server Management Studio to the datagridview controller.

How It Basically Works?

It's too easy, just in 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, 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 enabled.
• 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 a 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, for example:

Now append this code to the button'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, for 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, for 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 on your own, for 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 way easier.
• The demo is free to share (including some rights like: Codeproject's rights & my information)

History

• 7^th July, 2015: Initial version