Easy SQL Server Tool Using C#

• Kindly view my YouTube Video Link to learn about my Easy SQL SERVER Tool Using C#.

• Easy Form Design at Run Time C# Windows Forms

In this article we will see how to create an Easy SQL Server Tool.

• Overview (SMO)
• Create a Visual C# SMO Project in Visual Studio .NET
• Server Class

1. Connect to SQL Server.
2. Table Details
3. SQL Script Tab (Create Table tab / Insert and Delete records tab / Select Query tab).
4. Output Window

Here we will see details of each part,

SQL Server Connect

We can connect to any SQL Server within our network. We need to give SQL Server Name or IP Address, SQL Server Login ID and password to connect to SQL Server.

Create New Database

Check whether or not the Database name the user entered exists in the SQL Server. If it exists, then display the message to user. If there is no database with user entered name, then a new Database will be created.

Load Database

Load all the Database Name to combobox for creating Table.

Delete Database

User can delete the selected Database from SQL Server. Before deleting, a confirmation box will be displayed to user to delete or not. If the user clicks on ok then the selected database will be deleted.

Database Backup

Backup the selected Database to any folder as you have selected.

Database Restore

User can restore the database by selecting “.bak” file from the selected folder.

Step 2: Table Details

Load Table:

User needs to first select the Database to list all the Table Names. After selecting the database and by clicking on the Load Table Names all the Table Names to the Combobox will be loaded.

Delete Table

User can delete the selected Table from SQL Server. Before deleting, a confirmation box will be displayed for user to delete or not. If the user clicks on ok then the selected Table will be deleted.

Step 3: SQL Script Tab

Here we will be having 3 tabs; the first tab is for creating Table, the second tab is for Insert and Delete Records, and the third tab is for selecting query. We will see all three tab's details here.

Create Table Tab

Here the user can create their own table without writing SQL Query. Firstly, the user needs to select the Database where the table needs to be created. After selecting the Database the user can enter their SQL Table Name in the textbox. After entering the table name the user needs to add columns for that Table. Without adding columns the Table cannot be created.

Adding Columns

User can add columns for creating a new Table. All these columns will be added locally to the grid for finally creating a Table. The user can add any number of columns, delete the selected column and also delete all columns by clicking on Clear Columns. Adding and deleting columns does not deal with databases so this will be as temp place to add columns for creating Table. After adding all columns for the Table user can click on Create Table button to create a new Table with given column details.

Datatype:

Here for now only 3 datatypes have been used.

User can also add each column data size. For integer the size will not be used, but for Varchar and nVarchar Datatype the user can add the column data size. The user can also set Max size for both Varchar and nVarchar type by selecting the Max Size checkbox.

For example, we can see in the following image, here we have added four columns to the grid, and adding one more new column we can see each column Datatype and Size from the grid. After adding all column, we click the Create Table Button. We can see the confirmation message as Table was created and also we can see our new Table has been added in the Table List Combobox.

Insert and Delete Records Tab

We will be using the recently-created test Table for inserting demo.

Here we can see in the above image we have selected the Table “test” from Table List. To insert record to selected Table we need to click on the load table Details to insert button. By clicking this we will be displaying all the column names with textbox. Depending on the Datatype of each column we will be adding textbox with maxlength. If the column datatype is Integer then we will be adding Numeric textbox for that column' by this user can enter only numbers to that textbox. For Varchar and nVarchar we will check for each Column Size; if it's max then we set the textbox maxlength as 200; by this the user can enter only 200 characters. If size is set for the column then we will be setting the column size for textbox maxlength.

Insert:

After loading all the column details with TextBox user can enter the value to be inserted for each column. Once user enters the value, click on the Insert into Selected Table button for inserting a new record. Similarly, the user can enter any number of records for the selected table. If you want to add more functionality from the code you can add your functionality, for example, like checking duplicate value before insert and etc.

Delete All Records: User can delete all records of the selected table. Here in this sample we are not checking any condition for deleting the records. If you need to add more functionality you can change from the code and add your own functions, for example, such as Delete records of a table based on condition and etc.

Select Query Tab

For selecting the records from a Database and display the result the user needs to first select the Database and then select the Table from the Table List to create a SQL Select query.

User can select the details using three features,

1) All Columns: If the All Columns checkbox is checked then all column details results will be displayed to the output window grid. (This is same like * in select query, for example select * from tablename). We can see from the above image the All Column Check box will be loaded by default. After selecting the table and when user clicks on Run Select Query all column details will be loaded in the following output window grid.

2) User selected Columns: If user want to display only particular columns of the table then they can click on load Table Columns to Select button to display all the column name of the tables to CheckedListbox. Here user can select the columns they need and by default check all the Column Names. User can uncheck the columns which are not needed and also uncheck the All Columns Checkbox to display only selected columns as a result.

User can Write SQL Select Query: User can also write their SQL Select query to display the output.

For example, if the user wants to write their own SQL query to join 3 table and display the result, to write SQL Select query user needs to check the SQL Select Query checkbox, and after writing select query user can click on Run Select Query button to see the result. In the above image we can see, we wrote a sample SQL Join query to join the three tables and by clicking the Run Select Query the result has been bound in the grid.

SQL Injection Checking in user entered select query:

We have also checked for the SQL Injection before executing user-entered SQL Select query. We have created an array list to add all SQL injection strings and we will be checking if any of the array word matches with the user entered select query. For example, we can see the above image since after select query we have entered drop query. But when we click on Run Select Query button we display the message, as drop is not accepted in select query.

Here is the list of SQL Injection string I am checking; if you need to, you can add or remove as per your requirement.

Save Select Query: User can also save the Select Query as text file to selected folder for using the select query later.

Open saved Select Query: User can also open the saved Select Query to execute the select script.

Prerequisites

Visual Studio 2015: You can download it from here.

SQL SERVER SMO Reference: For working with SQL Server SMO we need to add 4 important reference to our project,

You can find the reference from this path (Here we have used SQL Server 2014),

C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\

Code part

The main aim was to create a simple and easy to use Easy SQL Tool. All the functionalities have been added in one main form with Tab. We have created 2 classes:

Connect to SQL Server

In this article I have mentioned that for using SQL Server SMO objects we need to add four important Assembly references to our project. Using SMO ServerConnection we can connect to SQL server. We need to set the SQL Server name to ServerInstance and set SQL Server login and password. After connecting we check if Connection is open and return the Boolean value to our main function to display the appropriate message to user.

Write Exception Message to Log File:

We will be writing all exception error message to Log file with Date and Time.

Load Database Name

In Load Database Name button click event, we will pass the comboBox control to the Biz class method. In biz class method we will call the SMO Class to get all the database names and add all the database names to combobox. Here in this method I will check for Master Database and will not add the name for security reasons, for example, the user can delete the database from our main form. Similarly, you can change the code to restrict any database that shows it in our main form.

In SMO Class we will be loading all the Database name for a given SQL Server and return as DatabaseCollection to calling class.