Table of Contents
ASP.NET SQL Server Manager
1. Introduction
Working whit the data; I believe that the Microsoft SQL Server is the best choice for managing your huge amount of data / information. Any database related application development, all the developers are very much familiar with the Microsoft SQL Server database management system with the Server tools, Client tools and it’s common developing environments as well.
Just think for a few moments; if we have a WEB based SQL Server admin tools where the developers can do all the basic common tasks for a database related application I think it might be good for all of us. The purpose of this article is to share with you on how to create an ASP.NET SQL Server admin tools. This article is about to give you an overall idea on how you can directly access the SQL Server database objects, Working with the objects like tables, views, stored procedure etc.
2. ASP.NET SQL Server Manager
This application is a WEB based SQL Server admin tool. It will give you some flexibility to handle various database objects. It’s allowed you to quick show of any table / views data of a particular database. You will enjoy working with views, stored procedure, and function as well.
In a word the ASP.NET SQL Server Manager will give you an overall idea of a Web based SQL Server Admin Tools.
3. Features
As we shortly discussed earlier that it is a admin tools So I try to include all the basic features that are need for general application development purpose. Some of the features are given below:
- Local & Remote Environment
- Working with Objects
- Tables
- Views
- Stored Procedures
- User define Functions
It will facilitate you to generate auto SQL script such as views, stored procedures, functions etc. You can also create, alter or delete any database objects and many more....
3.1 Local & Remote Environment
When you work with Microsoft SQL Server Client Tools, you must need to register with the Database Server to works. The environment may be local area network (LAN) or it may remote / internet. Microsoft SQL Server internally manages the registration process and the connectivity in local or remote environment.
In this article I showed how to connect with the database server in a local area network (LAN) and remote / internet environment as well.
Figure - (1). Login to Microsoft SQL Server(Local / Remote) environment.
3.2 Working Objects
As we discussed before you can work with any database objects as likely as you work on Microsoft SQL Server Client tools. Here I try to demonstrate you on how easily you can get the SQL Server database objects, working with the object like performing the basic database manipulation activates, executing your custom transact-SQL statements etc.
Lets consider this scenario. For any purpose you select a object such as view, stored procedure etc... form a particular database. This application will auto generate the transact-SQL statements and allowing you to perform the basic like CREATE/ALTER / DROP object etc.
You will get more detail on the section "Understand & Using the code".
3.2.1 Tables
The base of every Relational Database Management System (RDBMS) is a database object called table. Every database consists of one or more then one table, which store the database’s data or information or records. Each table has its own unique name and consists of columns and rows.
The basic task of a table such as creating, altering and deleting as well. This application allows you to perform all the mentioned task. The transact-SQL syntax of creating, altering and deleting tables are given below:
Figure - (2). Working with TABLE Object.
Create Table Syntax
CREATE TABLE
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint > } [ ,...n ]
)
[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]
For more information can be found at this link.
Alter Table Syntax
ALTER TABLE table_name
ALTER COLUMN column_name
{
type_name[({precision[.scale]})][NULL|NOT NULL]
{DROP DEFAULT
| SET DEFAULT constant_expression
| IDENTITY [ ( seed , increment ) ]
}
| ADD
{ < column_definition > | < table_constraint > } [ ,...n ]
| DROP
{ [ CONSTRAINT ] constraint_name
| COLUMN column }
] }
For more information can be found at this link.
Delete Table Syntax
DROP TABLE [ database_name . [ schema_name ] . | schema_name . ]
table_name [ ,...n ] [ ; ]
For more information can be found at this link.
3.2.2 Views
In SQL Server, a view is a pre-written query that is stored on the database. A view consists of a SELECT statement, and when you run the view, you see the results of it like you would when opening a table. Some people like to think of a view as a virtual table. This is because a view can pull together data from multiple tables, as well as aggregate data, and present it as though it is a single table.
This application allows you to perform all the basic tasks of a SQL Server view objects such as as creating, altering and deleting as well. The transact-SQL syntax of creating, altering and deleting view are given below:
Figure - (3). Working with VIEW Object.
Create view
Creates a virtual table that represents the data in one or more tables in an alternative way. CREATE VIEW must be the first statement in a query batch.
Syntax
CREATE VIEW [< owner > . ] view_name [ ( column [ ,...n ] ) ]
[ WITH < view_attribute > [ ,...n ] ]
AS
select_statement
[ WITH CHECK OPTION ]
For more information can be found at this link.
Alter View
ALTER VIEW [ schema_name . ] view_name [ ( column [ ,...n ] ) ]
[ WITH <view_attribute> [ ,...n ] ]
AS select_statement
[ WITH CHECK OPTION ] [ ; ]
For more information can be found at this link.
Drop / delete view
DROP VIEW [ schema_name . ] view_name [ ...,n ] [ ; ]
For more information can be found at this link.
3.2.3 Stored Procedure
Few month ago I wrote an article on the basic overview of SQL Server Stored Procedure at codeproject.com. I would like to request you read this article form this link.
Figure - (4). Working with Stored Procedure Object.
3.2.4 User define Functions
Microsoft SQL Server classified function into two types:
- Scalar value function
- Table value function
Scalar value function
A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages.
Table value function
An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parametrized, non-updateable view of the underlying tables.
Figure - (5). Working with Function Object.
For more information can be found at this link.
4. Understanding & Using the code
This is not a complex task, It's really easy to implements because of the outstanding features and flexibility of Microsoft products. Microsoft SQL Server provide us to integrate / use to create a custom applications as well. I think it should not be a problems for the developers who have the basic knowledge on Microsoft SQL Server and Microsoft C#.Net language.
So let's start to get an idea on how it works actually.
4.1 Login to the SQL Server
Microsoft .NET frame work provide us a huge number of namespaces with various classes for doing complex tasks with in a easy way. Everybody must agree with me on the flexibility and the comprehensive features of .NET framework .
In this application I use sqlclient for open a new connection. Lets get back to the login process. Here I try to showed on the both that is local & remote environment. When you work in local environment you do not need to provide the port address. You just need tp provide the server ip, login Id and the password. The application will perform an auto check operation that the provided database server address is valid or not. by ping the server with 32 bit data. A simple code example is given below:
Example - 1 (Ping the database server)
public bool IsSqlSeverExist(string strServerIP)
{
bool Result = false;
try
{
if (strServerIP != "")
{
Ping ObjRequestFrom = new Ping();
PingOptions ObjPingOptions = new PingOptions();
PingReply ObjPingReply;
string strBuffer = "Test";
byte[] bytBuffer = Encoding.ASCII.GetBytes(strBuffer);
int intTimeOut = 60;
ObjPingOptions.DontFragment = true;
ObjPingReply = ObjRequestFrom.Send(strServerIP, intTimeOut, bytBuffer, ObjPingOptions);
if (ObjPingReply.Status.Equals(IPStatus.Success))
Result = true;
else Result = false;
}
}
catch (Exception ex)
{
Result = false;
throw ex;
}
return Result;
}
Example - 2 (login to the database server)
protected void ButtonLogin_Click(object sender, EventArgs e)
{
DAL.Interface.IFacade ObjFacade;
ObjFacade = new DAL.ClsSQLHelper();
Tools.IFacade ObjTools;
ObjTools = new Tools.ClsTools();
this.ImageLoading.Visible = true;
if (this.GetEnvironment() == "local")
{
if (ObjTools.IsSqlSeverExist(this.TextBoxServerIP.Text.ToString()))
{
if (ObjFacade.ExamineConnection(
ObjTools.DefaultConnectionString(this.TextBoxServerIP.Text
, this.TextBoxRemotePort.Text
, this.TextBoxUserName.Text
, this.TextBoxPassword.Text)))
{
this.setLoginInfo();
this.LabelStatus.Text = "Connecting to the local server.";
Server.Transfer("~/Default.aspx", true);
}
else
{
this.LabelStatus.Text = "SQL server ["
+ this.TextBoxServerIP.Text.ToString()
+ "] deny the request of the user [" + this.TextBoxUserName.Text + "].";
}
}
else
{
this.LabelStatus.Text = "SQL server ["
+ this.TextBoxServerIP.Text.ToString()
+ "] does not exist.";
}
}
else
{
if (ObjFacade.ExamineConnection(
ObjTools.DefaultConnectionString(this.TextBoxServerIP.Text
, this.TextBoxRemotePort.Text
, this.TextBoxUserName.Text
, this.TextBoxPassword.Text)))
{
this.setLoginInfo();
this.LabelStatus.Text = "Connecting to the remote server.";
Server.Transfer("~/Default.aspx", true);
}
else
{
this.LabelStatus.Text = "SQL server ["
+ this.TextBoxServerIP.Text.ToString()
+ "] deny the request of the user [" + this.TextBoxUserName.Text + "].";
}
}
}
4.2 Generate Object Tree
In this application i consider the main four database object of Microsoft SQL Server. They are as following:
- Table
- View
- Stored Procedure
- Function
Figure - (6). Database Object Explorer.
A code example of Object tree generation is given below:
#region "Generate SQL Server Object Hierarchy"
private void PopulateRootTree()
{
DAL.Interface.IFacade ObjFacade;
ObjFacade = new DAL.ClsSQLHelper();
Tools.IFacade ObjTools;
ObjTools = new Tools.ClsTools();
string strSQL = "exec sp_databases";
string strDBNodeCaption = null;
string strTables = null;
string strViews = null;
string strStoredProcedure = null;
string strFunctions = null;
string strOwner = null;
string strObjuri = null;
string strConnectionString = null;
DataSet ObjNode;
DataSet ObjTables;
DataSet ObjViews;
DataSet ObjStoredProcedure;
DataSet ObjFunctions;
try
{
ObjNode = (ObjFacade.GetRecordSet(
ObjTools.DefaultConnectionString(ObjGlobal.ServerIP
, ObjGlobal.Port
, ObjGlobal.LoginID
, ObjGlobal.Password)
, strSQL));
TreeNode treeNodeRoot = new TreeNode(ObjGlobal.ServerIP + " " + ObjGlobal.Port, "S", "~/App_Themes/SQL/Images/DBServer.png", "Default.aspx", "");
this.tvSQLServer.Nodes.Add(treeNodeRoot);
TreeNode treeNodeBranches = new TreeNode("Database", "C", "~/App_Themes/SQL/Images/Folder.png", "Default.aspx", "");
treeNodeRoot.ChildNodes.Add(treeNodeBranches);
int intCounter = 0;
foreach (DataRow ObjDBDataRow in ObjNode.Tables[0].Rows)
{
strDBNodeCaption = Convert.ToString(ObjNode.Tables[0].Rows[intCounter][0]);
TreeNode treeNodeBranchesLeaf_01 = new TreeNode(strDBNodeCaption, "D", "~/App_Themes/SQL/Images/dbObj.png", "", "");
treeNodeBranches.ChildNodes.Add(treeNodeBranchesLeaf_01);
#region "Common Sql nodes "
TreeNode treeNodeBranchesLeaf_02 = new TreeNode("Tables", "T", "~/App_Themes/SQL/Images/dbfiles.png", "", "");
treeNodeBranchesLeaf_01.ChildNodes.Add(treeNodeBranchesLeaf_02);
#region "Get tables"
strSQL = "SELECT * FROM "
+ strDBNodeCaption
+ ".INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE <> 'VIEW' ";
ObjTables = new DataSet();
strConnectionString = ObjTools.FormatConnectionString(ObjGlobal.ServerIP
, ObjGlobal.Port
, strDBNodeCaption
, ObjGlobal.LoginID
, ObjGlobal.Password);
ObjTables = (ObjFacade.GetRecordSet(strConnectionString, strSQL));
if (ObjTables.Tables[0].Rows.Count != 0)
{
int intTableCounter = 0;
foreach (DataRow ObjTableDataRow in ObjTables.Tables[0].Rows)
{
strOwner = Convert.ToString(ObjTables.Tables[0].Rows[intTableCounter][1]);
strTables = Convert.ToString(ObjTables.Tables[0].Rows[intTableCounter][2]);
strObjuri = "~/Pages/SqlEditor.aspx?ObjType=0&db=" + strDBNodeCaption + "&dbObj=" + strTables ;
TreeNode treeNodeBranchesLeaf_020 = new TreeNode(strOwner + "." + strTables, "T", "~/App_Themes/SQL/Images/Tables.png", strObjuri, "");
treeNodeBranchesLeaf_02.ChildNodes.Add(treeNodeBranchesLeaf_020);
intTableCounter++;
}
ObjTables.Dispose();
ObjTables = null;
strOwner = null;
strTables = null;
strConnectionString = null;
}
#endregion
#region "Get views"
TreeNode treeNodeBranchesLeaf_03 = new TreeNode("Views", "V", "~/App_Themes/SQL/Images/dbfiles.png", "", "");
treeNodeBranchesLeaf_01.ChildNodes.Add(treeNodeBranchesLeaf_03);
strSQL = "SELECT * FROM "
+ strDBNodeCaption
+ ".INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE <> 'BASE TABLE'";
ObjViews = new DataSet();
strConnectionString = ObjTools.FormatConnectionString(ObjGlobal.ServerIP
, ObjGlobal.Port
, strDBNodeCaption
, ObjGlobal.LoginID
, ObjGlobal.Password);
ObjViews = (ObjFacade.GetRecordSet(strConnectionString, strSQL));
if (ObjViews.Tables[0].Rows.Count != 0)
{
int intViewsCounter = 0;
foreach (DataRow ObjViewsDataRow in ObjViews.Tables[0].Rows)
{
strOwner = Convert.ToString(ObjViews.Tables[0].Rows[intViewsCounter][1]);
strViews = Convert.ToString(ObjViews.Tables[0].Rows[intViewsCounter][2]);
strObjuri = "~/Pages/SqlEditor.aspx?ObjType=1&db=" + strDBNodeCaption + "&dbObj=" + strViews;
TreeNode treeNodeBranchesLeaf_020 = new TreeNode(strOwner + "." + strViews, "T", "~/App_Themes/SQL/Images/vw.png", strObjuri, "");
treeNodeBranchesLeaf_03.ChildNodes.Add(treeNodeBranchesLeaf_020);
intViewsCounter++;
}
ObjViews.Dispose();
ObjViews = null;
strOwner = null;
strViews = null;
strConnectionString = null;
}
#endregion
TreeNode treeNodeBranchesLeaf_04 = new TreeNode("Programmability", "P", "~/App_Themes/SQL/Images/dbfiles.png", "", "");
treeNodeBranchesLeaf_01.ChildNodes.Add(treeNodeBranchesLeaf_04);
#region "Get Stored Procedures"
TreeNode treeNodeBranchesLeaf_05 = new TreeNode("Stored Procedures", "S", "~/App_Themes/SQL/Images/dbfiles.png", "", "");
treeNodeBranchesLeaf_04.ChildNodes.Add(treeNodeBranchesLeaf_05);
strSQL = "SELECT * FROM "
+ strDBNodeCaption
+ ".INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE'";
ObjStoredProcedure = new DataSet();
strConnectionString = ObjTools.FormatConnectionString(ObjGlobal.ServerIP
, ObjGlobal.Port
, strDBNodeCaption
, ObjGlobal.LoginID
, ObjGlobal.Password);
ObjStoredProcedure = (ObjFacade.GetRecordSet(strConnectionString, strSQL));
if (ObjStoredProcedure.Tables[0].Rows.Count != 0)
{
int intStoredProcedureCounter = 0;
foreach (DataRow ObjStoredProcedureDataRow in ObjStoredProcedure.Tables[0].Rows)
{
strOwner = Convert.ToString(ObjStoredProcedure.Tables[0].Rows[intStoredProcedureCounter][1]);
strStoredProcedure = Convert.ToString(ObjStoredProcedure.Tables[0].Rows[intStoredProcedureCounter][2]);
strObjuri = "~/Pages/SqlEditor.aspx?ObjType=2&db=" + strDBNodeCaption + "&dbObj=" + strStoredProcedure;
TreeNode treeNodeBranchesLeaf_020 = new TreeNode(strOwner + "." + strStoredProcedure, "T", "~/App_Themes/SQL/Images/sp.png", strObjuri, "");
treeNodeBranchesLeaf_05.ChildNodes.Add(treeNodeBranchesLeaf_020);
intStoredProcedureCounter++;
}
ObjStoredProcedure.Dispose();
ObjStoredProcedure = null;
strOwner = null;
strStoredProcedure = null;
strConnectionString = null;
}
#endregion
#region "Get Functions"
TreeNode treeNodeBranchesLeaf_07 = new TreeNode("Functions", "S", "~/App_Themes/SQL/Images/dbfiles.png", "", "");
treeNodeBranchesLeaf_04.ChildNodes.Add(treeNodeBranchesLeaf_07);
strSQL = "SELECT * FROM "
+ strDBNodeCaption
+ ".INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION'";
ObjFunctions = new DataSet();
strConnectionString = ObjTools.FormatConnectionString(ObjGlobal.ServerIP
, ObjGlobal.Port
, strDBNodeCaption
, ObjGlobal.LoginID
, ObjGlobal.Password);
ObjFunctions = (ObjFacade.GetRecordSet(strConnectionString, strSQL));
if (ObjFunctions.Tables[0].Rows.Count != 0)
{
int intFunctionsCounter = 0;
foreach (DataRow ObjFunctionsDataRow in ObjFunctions.Tables[0].Rows)
{
strOwner = Convert.ToString(ObjFunctions.Tables[0].Rows[intFunctionsCounter][1]);
strFunctions = Convert.ToString(ObjFunctions.Tables[0].Rows[intFunctionsCounter][2]);
strObjuri = "~/Pages/SqlEditor.aspx?ObjType=3&db=" + strDBNodeCaption + "&dbObj=" + strFunctions;
TreeNode treeNodeBranchesLeaf_020 = new TreeNode(strOwner + "." + strFunctions, "F", "~/App_Themes/SQL/Images/Function.png", strObjuri, "");
treeNodeBranchesLeaf_07.ChildNodes.Add(treeNodeBranchesLeaf_020);
intFunctionsCounter++;
}
ObjFunctions.Dispose();
ObjFunctions = null;
strOwner = null;
strFunctions = null;
strConnectionString = null;
}
#endregion
intCounter++;
}
#endregion
TreeNode treeNodeHelp = new TreeNode("Help", "H", "~/App_Themes/SQL/Images/Help.png", "Help.aspx", "");
treeNodeRoot.ChildNodes.Add(treeNodeHelp);
}
catch (Exception ex)
{
ObjNode = null;
MessageBox.Show(ex.Message.ToString());
}
}
private bool SetProperties()
{
HttpCookie ObjCookie = new HttpCookie("LastVisit");
bool Result = false;
try
{
if (Request.Cookies["LastVisit"] == null)
{
ObjCookie.Value = DateTime.Now.ToString();
ObjCookie.Expires = DateTime.Now.AddMonths(1);
Response.Cookies.Add(ObjCookie);
}
if (Request.Cookies["LastVisit"] != null)
{
UserNameLabel.Text = "Welcome, " + Session["LoginID"].ToString();
LabelLastVisit.Text = "Last Visit :" + Server.HtmlEncode(Request.Cookies["LastVisit"].Value);
Response.Cookies["LastVisit"].Value = DateTime.Now.ToString();
Response.Cookies["LastVisit"].Expires = DateTime.Now.AddMonths(1);
}
ObjGlobal.ServerIP = Convert.ToString(Session["ServerIP"]);
ObjGlobal.Port = Convert.ToString(Session["RemotePort"]);
ObjGlobal.NTSecurity = Convert.ToString(Session["NT"]);
ObjGlobal.LoginID = Convert.ToString(Session["LoginID"]);
ObjGlobal.Password = Convert.ToString(Session["Password"]);
Result = true;
}
catch (Exception ex)
{
Result = false;
MessageBox.Show(ex.Message);
}
return Result;
}
#endregion
4.3 Generate Transact-SQL
When you select a view, stored procedure or even a function this application generate the SQL script for you furthen modification. A sample code is given below:
private void GetCurrentObjScript()
{
string strScript = null;
Tools.IFacade ObjTools;
ObjTools = new Tools.ClsTools();
DAL.Interface.IFacade ObjFacade;
ObjFacade = new DAL.ClsSQLHelper();
strConnectionString = ObjTools.FormatConnectionString(ObjGlobal.ServerIP
, ObjGlobal.Port
, ObjGlobal.Database
, ObjGlobal.LoginID
, ObjGlobal.Password);
strScript = ObjFacade.GetScript(strConnectionString, ObjGlobal.SqlObject, 1);
this.TextBoxScript.Text = strScript;
}
4.4 Display data
I use a simple grid view control to display the data from your selected object..Actually It will show the data when you select a table or a view from a particular database. A sample code is given below:
private void LoadDataObjType01()
{
int intObjType = 0;
string strSQL = null;
intObjType = ObjGlobal.SqlObjectType;
DAL.Interface.IFacade ObjFacade;
ObjFacade = new DAL.ClsSQLHelper();
Tools.IFacade ObjTools;
ObjTools = new Tools.ClsTools();
DataSet ObjDataSet = new DataSet();
if (intObjType == 0 || intObjType == 1)
{
this.GetCurrentObjScript();
strSQL = "SELECT * FROM " + ObjGlobal.SqlObject;
strConnectionString = ObjTools.FormatConnectionString(ObjGlobal.ServerIP
, ObjGlobal.Port
, ObjGlobal.Database
, ObjGlobal.LoginID
, ObjGlobal.Password);
ObjDataSet = ObjFacade.GetRecordSet(strConnectionString, strSQL);
this.GridViewSqlObject.DataSource = ObjDataSet;
this.GridViewSqlObject.DataBind();
this.GridViewSqlObject.Dispose();
}
else if (intObjType == 2 || intObjType == 3)
{
this.GetCurrentObjScript();
}
}
5. SQL Query editor
This features is not included yet in this version... I hope that I will add this features as early as possible.
6. Application architecture
I try to follow the n-tire archicture for this application..Here I create three layers:
- Data Access Layer
- Interface
- Tools
The class diagram of each layer is given below:
Data Access Layer
Interface
Tools
7. References
8. Conclusion
I hope that this article might be helpful to you. Enjoy!
9. History
- 14th September 2009: Initial post.
