Building Blocks For ASP.NET Applicationa: Part 1

• Download source files - 2 Kb

Introduction :

My Objective in this series of articles is to provide basic and general blocks for any asp.net application which helps the development more faster and easier.

Creating a .net application is easy when you are following an approach of a coder who only knows how to code the things but if you think yourself a bit more than a typical code wake up and write some thing which full fills the need of application and solve all the issues. There is a class file which is called SqlHelper very popular among the vb.net developers which helps you to access sql server database, that is good I also used that class for a long time but when I feel the importance of Stored Procedures I decide to write my own class which will have all the things inside.

The class file is very secure and you just don't need to replace ' with ' ' for keeping your application secure from sql injection.

Actually the thing which I am going to show you is a cobinationof two namespacess Called "Data Access Tools" (DAT) and "Security Tools" (ST). DAT is responsible for accessing every kind of data either from database or web.config and ST is responsible for taking care of database level security.

Data Access Tools:

It has the following methods.

1. RunSpDtNoParam (Used to run stored procedure with out parameter and return datatablle means use Data Adapter)
2. RunSpDt (Used to run stored procedure with parameter and return datatablle means use Data Adapter)
3. RunSpDrNoParam (Used to run stored procedure with out parameter and return Data reader)
4. RunSpDr (Used to run stored procedure with parameter and return Data reader)
5. RunSpNonQuery (Used to insert / update or delete by stored procedure)
6. RunQueryDt (Used to run inline query and return data table)
7. RunQueryDr (Used to run inline query and return data reader)
8. RunQueryNonQuery (Used to insert / update or delete by Inline query)

The following class have comments so there will be no problem to understrand the code.

Namespace DataAccessTools
Public Class Dal
Dim ObjWebConfigAccess As New WebConfigAccess
Dim ObjValidateData As New SecurityTools.ValidateData
Public Function RunSpDtNoParam(ByVal SP_name As String) As DataTable
Dim ObjCon As SqlConnection
Try
'declairing variables
Dim ObjCom As SqlCommand
Dim ObjDataAdapter As New SqlDataAdapter
Dim Return_Dt As New DataTable
'========================================
'Checking For Sql Injection
ObjValidateData.DetectSqlInjection(SP_name, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
'============================================

'Setting Command Object
ObjCom.CommandType = CommandType.StoredProcedure
ObjCom.CommandText = SP_name
'==========================

'Setting opened connection with 
'Command Object and Filling datatable
ObjCom.Connection = ObjCon
ObjDataAdapter = New SqlDataAdapter(ObjCom)
ObjDataAdapter.Fill(Return_Dt)
'==========================================

Return Return_Dt

Catch ex As Exception
Throw ex
Finally
ObjCon.Close()
End Try
End Function

'For Using Datatable and Stored Procedure
Public Function RunSpDt(ByVal sp_name _
       As String, ByVal param() As _
       SqlParameter) As DataTable
'declairing variables
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim ObjDataAdapter As New SqlDataAdapter
Dim Return_Dt As New DataTable
'========================================
'Checking For Sql Injection
ObjValidateData.DetectSqlInjection(sp_name, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
'============================================

'Setting Command Object
ObjCom.CommandType = CommandType.StoredProcedure
ObjCom.CommandText = sp_name
'==========================

'setting parameter in command object from parameter
Dim a As Integer
For a = 0 To param.Length - 1
ObjValidateData.DetectSqlInjection(param(a).Value, True)
ObjValidateData.DetectSqlInjection(param(a).ParameterName, True)
ObjCom.Parameters.Add(param(a))
Next
'=====================================================

'Setting opened connection with 
'Command Object and Filling datatable
ObjCom.Connection = ObjCon
ObjDataAdapter = New SqlDataAdapter(ObjCom)
ObjDataAdapter.Fill(Return_Dt)
'===============================================

Return Return_Dt

Catch ex As Exception
Throw ex
Finally
ObjCon.Close()
End Try
End Function
'For Using DataReader and Stored Procedure
Public Function RunSpDrNoParam(ByVal _
       sp_name As String) As SqlDataReader
'declairing variables
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim ObjDataReader As SqlDataReader
Dim Return_Dt As New DataTable
'========================================
'Checking For Sql Injection
ObjValidateData.DetectSqlInjection(sp_name, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
'============================================

'Setting Command Object
ObjCom.CommandType = CommandType.StoredProcedure
ObjCom.CommandText = sp_name
'==========================

'Setting opened connection with Command Object and Filling datatable
ObjCom.Connection = ObjCon
ObjDataReader = ObjCom.ExecuteReader(CommandBehavior.CloseConnection)

'============================================
Return ObjDataReader

Catch ex As Exception
Throw ex
Finally
ObjCon.Close()
End Try

End Function
'For Using DataReader and Stored Procedure
Public Function RunSpDr(ByVal sp_name As String,_
   ByVal param() As SqlParameter) As SqlDataReader
'declairing variables
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim ObjDataReader As SqlDataReader
Dim Return_Dt As New DataTable
'========================================
'Checking For Sql Injection
ObjValidateData.DetectSqlInjection(sp_name, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
'============================================

'Setting Command Object
ObjCom.CommandType = CommandType.StoredProcedure
ObjCom.CommandText = sp_name
'==========================

'setting parameter in command object from parameter
Dim a As Integer
For a = 0 To param.Length - 1
ObjValidateData.DetectSqlInjection(param(a).Value, True)
ObjValidateData.DetectSqlInjection(param(a).ParameterName, True)
ObjCom.Parameters.Add(param(a))
Next
'===================================

'Setting opened connection with Command 
'Object and Filling datatable
ObjCom.Connection = ObjCon
ObjDataReader = ObjCom.ExecuteReader(CommandBehavior.CloseConnection)

'===================================
Return ObjDataReader

Catch ex As Exception
Throw ex
Finally
ObjCon.Close()
End Try

End Function
'For Using Long and Stored Procedure
Public Function RunSpNonQuery(ByVal Sp_name _
       As String, ByVal Param() _
       As SqlParameter) As Long

'declairing variables
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim Results As Long
'==================================
'Checking For Sql Injection
ObjValidateData.DetectSqlInjection(Sp_name, True)

ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
'=================================

'Setting Command Object
ObjCom.CommandType = CommandType.StoredProcedure
ObjCom.CommandText = Sp_name
'==========================

'setting parameter in command object from parameter
Dim a As Integer
For a = 0 To Param.Length - 1
ObjValidateData.DetectSqlInjection(Param(a).Value, True)
ObjValidateData.DetectSqlInjection(Param(a).ParameterName, True)
ObjCom.Parameters.Add(Param(a))
Next
'================================
'Setting opened connection with Command Object and Filling datatable
ObjCom.Connection = ObjCon
Results = ObjCom.ExecuteNonQuery
'=================================
Return Results

Catch ex As Exception
Throw ex
Finally
ObjCon.Close()
End Try

End Function
'For Using Datatable and Sql Query
Public Function RunQueryDt(ByVal Sql_Query As String) As DataTable
'declairing variables
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim ObjDataAdapter As New SqlDataAdapter
Dim Return_Dt As New DataTable
'===============================
'Checking For Sql Injection
ObjValidateData.DetectSqlInjection(Sql_Query, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
'==============================

'Setting Command Object
ObjCom.CommandType = CommandType.Text
ObjCom.CommandText = Sql_Query
'==========================

'Setting opened connection with Command Object and Filling datatable
ObjCom.Connection = ObjCon
ObjDataAdapter = New SqlDataAdapter(ObjCom)
ObjDataAdapter.Fill(Return_Dt)
'====================================
Return Return_Dt

Catch ex As Exception
Throw ex
Finally
ObjCon.Close()
End Try
End Function
'For Using DataReader and Sql Query
Public Function RunQueryDr(ByVal Sql_Query As String) As SqlDataReader
'declairing variables
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim ObjDataReader As SqlDataReader
Dim Return_Dt As New DataTable
'========================================
'Checking For Sql Injection
ObjValidateData.DetectSqlInjection(Sql_Query, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
'========================================

'Setting Command Object
ObjCom.CommandType = CommandType.Text
ObjCom.CommandText = Sql_Query
'==========================

'Setting opened connection with Command Object and Filling datatable
ObjCom.Connection = ObjCon
ObjDataReader = ObjCom.ExecuteReader(CommandBehavior.CloseConnection)
'=======================================
Return ObjDataReader

Catch ex As Exception
Throw ex
Finally
ObjCon.Close()
End Try
End Function
'For Using Long and Sql Query
Public Function RunQueryNonQuery(ByVal Sql_Query As String) As Long
'declairing variables
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim Results As Long
'========================================
'Checking For Sql Injection


ObjValidateData.DetectSqlInjection(Sql_Query, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
'===============================

'Setting Command Object
ObjCom.CommandType = CommandType.Text
ObjCom.CommandText = Sql_Query
'==========================

'Setting opened connection with Command Object and Filling datatable
ObjCom.Connection = ObjCon
Results = ObjCom.ExecuteNonQuery
'==================================
Return Results
Catch ex As Exception
Throw ex
Finally
ObjCon.Close()
End Try

End Function
End Class
End Namespace

Security Tools:

There is only one class in this namespace which is known as "Validate Data". It has the following methods.

1. DetectSqlInjection (will call from DAT namespace and is responsible for catch any kind of sql injection keyword)
2. CheckAndFireSqlInjectionException (This method is used to fire the exception, may be some one want to perform any action in it)
3. GetSqlInjectionKeywords (This method will get all the injection keywords specified in web.config file)
   For example:

Namespace SecurityTools
Public Class ValidateData
Public Sub DetectSqlInjection(ByVal Input_Sql As Object, _
     Optional ByVal IgnoreColor As Boolean = False)
If IsDBNull(Input_Sql) = False Then
Dim SqlInjectionCharacter() As Char = GetSqlInjectionKeywords()
Dim a As Integer
For a = 0 To SqlInjectionCharacter.Length - 1
If IgnoreColor = True Then
If SqlInjectionCharacter(a) <> "'" Then
CheckAndFireSqlInjectionException(Input_Sql, SqlInjectionCharacter(a))
End If
Else
CheckAndFireSqlInjectionException(Input_Sql, SqlInjectionCharacter(a))
End If
Next
End If
End Sub
Protected Sub CheckAndFireSqlInjectionException(ByVal _
     InputSql As String, ByVal CharToMap As Char)
If InputSql.IndexOf(CharToMap) <> -1 Then
Throw New CustomExceptions.Exception_SqlInjection

End If
End Sub
Public Function GetSqlInjectionKeywords() As Char()
Dim ObjWebConfigAccess As New DataAccessTools.WebConfigAccess
Dim Keywords As String = ObjWebConfigAccess.GetAddKeyValue("SqlInjectionKeywords")
Return Keywords.ToCharArray()
End Function
End Class
End Namespace</CODE>

Well above file can help you alot and in many cases, I have created DLL of this namespace and just add reference whenever I start some thing from the start.
I have keep the above file for download for more help contact me : aghausman@gmail.com

Happy Programming :)