INTRODUCTION
This topic's title may look a bit weird , but yes today's article is about how to create a 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 )
No comments:
Post a Comment