Click here to Skip to main content
14,389,354 members

SQL Browser

Rate this:
3.91 (27 votes)
Please Sign up or sign in to vote.
3.91 (27 votes)
2 Nov 2019CPOL
Explore your MMSQL server tables in a professional way

Image 1

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):

Image 2

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.

Image 3

After you did add your settings correctly, you have to connect using "Connect" button, here the result comes out.

Image 4

  • 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:

Image 5

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:

Image 6

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:

Image 7

Result from the sample:

Image 8

 

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
*/

Image 9

All you have to do in this case is to write your command manually, like this example below shows:

Image 10

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

  • 7th July, 2015: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Alaa Ben Fatma
Student
Tunisia Tunisia
I am a 19 years old software developer & a university student. I am a curious person, I love learning about new things and meeting more experienced developers & engineers.
alaabenfatma[a]yahoo.fr

Comments and Discussions

 
SuggestionCommand Did Not Execute Pin
Júnior Pacheco5-Nov-19 0:47
professionalJúnior Pacheco5-Nov-19 0:47 
QuestionMessage Closed Pin
4-Dec-18 1:09
MemberMember 140769184-Dec-18 1:09 
Questionhow to connect a local server with the windows authentication - what to write in server/user/password Pin
Member 103096048-Jul-15 9:31
MemberMember 103096048-Jul-15 9:31 
AnswerRe: how to connect a local server with the windows authentication - what to write in server/user/password Pin
Alaa Ben Fatma8-Jul-15 14:19
professionalAlaa Ben Fatma8-Jul-15 14:19 
GeneralRe: how to connect a local server with the windows authentication - what to write in server/user/password Pin
Member 103096049-Jul-15 11:48
MemberMember 103096049-Jul-15 11:48 
GeneralRe: how to connect a local server with the windows authentication - what to write in server/user/password Pin
Alaa Ben Fatma10-Jul-15 10:47
professionalAlaa Ben Fatma10-Jul-15 10:47 
QuestionThoughts Pin
Wendelius7-Jul-15 21:14
mveWendelius7-Jul-15 21:14 
AnswerRe: Thoughts Pin
Alaa Ben Fatma8-Jul-15 3:09
professionalAlaa Ben Fatma8-Jul-15 3:09 
GeneralMy vote of 5 Pin
Mohamed Aziz Lahbecha7-Jul-15 11:33
MemberMohamed Aziz Lahbecha7-Jul-15 11:33 
GeneralMy vote of 5 Pin
csharpukiller7-Jul-15 10:50
Membercsharpukiller7-Jul-15 10:50 
QuestionBroken ... Pin
Aubrey Offer6-Jul-15 23:34
MemberAubrey Offer6-Jul-15 23:34 
AnswerRe: Broken ... Pin
Alaa Ben Fatma7-Jul-15 1:09
professionalAlaa Ben Fatma7-Jul-15 1:09 
QuestionBroken images Pin
Tridip Bhattacharjee6-Jul-15 22:39
professionalTridip Bhattacharjee6-Jul-15 22:39 
AnswerRe: Broken images Pin
Alaa Ben Fatma7-Jul-15 1:09
professionalAlaa Ben Fatma7-Jul-15 1:09 
I have no idea about what happened , I contacted the administration and I hope they'll correct the error ASAP .
BugNO image, No Links Pin
Paw Jershauge6-Jul-15 20:55
MemberPaw Jershauge6-Jul-15 20:55 
GeneralRe: NO image, No Links Pin
Alaa Ben Fatma7-Jul-15 1:11
professionalAlaa Ben Fatma7-Jul-15 1:11 
GeneralRe: NO image, No Links Pin
Paw Jershauge8-Jul-15 0:28
MemberPaw Jershauge8-Jul-15 0:28 
GeneralRe: NO image, No Links Pin
Alaa Ben Fatma8-Jul-15 3:06
professionalAlaa Ben Fatma8-Jul-15 3:06 
AnswerRe: NO image, No Links Pin
Paw Jershauge8-Jul-15 3:07
MemberPaw Jershauge8-Jul-15 3:07 
GeneralRe: NO image, No Links Pin
Alaa Ben Fatma8-Jul-15 3:09
professionalAlaa Ben Fatma8-Jul-15 3:09 
GeneralRe: NO image, No Links Pin
Paw Jershauge8-Jul-15 3:10
MemberPaw Jershauge8-Jul-15 3:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Article
Posted 6 Jul 2015

Stats

3.8K views
2.4K downloads
32 bookmarked