Monday 7 January 2008

convert datareader to datatable

Dim objSQLdatareader As SqlClient.SqlDataReader = .ExecuteReader()

Dim dt As New DataTable
dt.Load(objSQLdatareader)

now datatable contains all data from datareader

we can assign datatable to datagrid, gridview datalist etc. as below

gridview.DataSource = dt
gridview1.DataBind()

Datepicker control asp.net



1. create new page:DatePicker.aspx
2. code behild : DatePicker.aspx.vb

code to place the all this page
1. Under head section of sample.aspx:


2. code sample.aspx page







3. code DatePicker.aspx
see the picture for -html code


4. Code DatePicker.aspx.vb

Imports System.Text

Public Class DatePicker
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
Private Sub InitializeComponent()

End Sub
'Protected WithEvents Calendar1 As System.Web.UI.WebControls.Calendar

'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Nothing to do, here
End Sub

Private Sub Calendar1_DayRender(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DayRenderEventArgs) Handles Calendar1.DayRender
'// Clear the link from this day
e.Cell.Controls.Clear()

'// Add the custom link
Dim Link As System.Web.UI.HtmlControls.HtmlGenericControl
Link = New System.Web.UI.HtmlControls.HtmlGenericControl
Link.TagName = "a"
Link.InnerText = e.Day.DayNumberText
Link.Attributes.Add("href", String.Format("JavaScript:window.opener.document.{0}.value = '{1:d}'; window.close();", Request.QueryString("field"), e.Day.Date))

'// By default, this will highlight today's date.
If e.Day.IsSelected Then
Link.Attributes.Add("style", Me.Calendar1.SelectedDayStyle.ToString())
End If

'// Now add our custom link to the page
e.Cell.Controls.Add(Link)

End Sub

End Class

Messagebox in ASP.Net using div

1. Place a 'place holder' control in asp.net page and
2. call the class below with setting values for the properties

sample:
Dim newBox As MessageBox = New MessageBox()
newBox.MessageBoxButton = 2
newBox.MessageBoxTop = 150
newBox.MessageBoxLeft = 50
newBox.MessageBoxWidth = 500
newBox.MessageBoxHeight = 150
newBox.MessageBoxButtonWidth = 50
newBox.MessageBoxIDYes = "yesno"
newBox.MessageBoxIDNo = "yesno"
newBox.MessageBoxButtonYesText = "Continue"
newBox.MessageBoxButtonNoText = "Cancel"
NewBox.MessageBoxTitle = "Postcode has been covered."
newBox.MessageBoxMessage = aMessage '"Do you want to continue?"
newBox.MessageBoxImage = "images/information.gif"
PlaceHolder1.Controls.Add(newBox)



'Main Class here
Imports Microsoft.VisualBasic
Imports System
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls

Imports System.IO
Imports System.Drawing
Imports System.Drawing.Imaging
Imports System.Drawing.Drawing2D


Public Class js

Public Function js()
End Function
End Class

Public Class MessageBox
Inherits WebControl
Private strLeft As String
Private strTop As String
Private intButton As Integer
Private strMessage As String

Private strTitle As String
Private strImage As String
Private strCss As String
Private strCssTitle As String
Private strCssMessage As String
Private strButtonYes As String
Private strButtonNo As String
Private strButtonCancel As String
Private strButtonWidth As Integer
Private strMessageBoxIDYes As String
Private strMessageBoxIDNo As String
Private strMessageBoxIDCancel As String

Private intMessageBoxWidth As Integer
Private intMessageBoxHeight As Integer
Private intMessageBoxImageWidth As Integer
Private intMessageBoxImageHeight As Integer
Dim homedirectory As String

Private strMessageBoxWidth As String
' Message box left position

#Region "properties"


Public Property MessageBoxLeft() As String

Get
Return strLeft
End Get

Set(ByVal Value As String)
strLeft = Value
End Set

End Property

' Message box top position

Public Property MessageBoxTop() As String

Get
Return strTop
End Get

Set(ByVal Value As String)
strTop = Value
End Set

End Property

' Number of buttons you want to display in the message box

Public Property MessageBoxButton() As Integer

Get
Return intButton
End Get



Set(ByVal Value As Integer)
intButton = Value
End Set

End Property

' Customize message you want to display in the message box

Public Property MessageBoxMessage() As String

Get
Return strMessage
End Get

Set(ByVal Value As String)
strMessage = Value
End Set

End Property

' Title you want to display in the message box

Public Property MessageBoxTitle() As String

Get
Return strTitle
End Get

Set(ByVal Value As String)
strTitle = Value
End Set

End Property

' Image you want to display in the message box (like information / warning)

Public Property MessageBoxImage() As String

Get
Return strImage
End Get

Set(ByVal Value As String)
strImage = Value
End Set

End Property

' Message box ID for Yes button

Public Property MessageBoxIDYes() As String

Get
Return strMessageBoxIDYes
End Get

Set(ByVal Value As String)
strMessageBoxIDYes = Value
End Set

End Property

' Message box ID for No button

Public Property MessageBoxIDNo() As String

Get
Return strMessageBoxIDNo
End Get

Set(ByVal Value As String)
strMessageBoxIDNo = Value
End Set

End Property

' Message box ID for Cancel button

Public Property MessageBoxIDCancel() As String

Get
Return strMessageBoxIDCancel
End Get



Set(ByVal Value As String)
strMessageBoxIDCancel = Value
End Set

End Property

' Style you want to incorporate for message box

Public Property MessageBoxCss() As String

Get
Return strCss
End Get

Set(ByVal Value As String)
strCss = Value
End Set

End Property

Public Property MessageBoxCssTitle() As String

Get
Return strCssTitle
End Get

Set(ByVal Value As String)
strCssTitle = Value
End Set

End Property

Public Property MessageBoxCssMessage() As String

Get
Return strCssMessage
End Get

Set(ByVal Value As String)
strCssMessage = Value
End Set

End Property

' Message box Text for Yes button

Public Property MessageBoxButtonYesText() As String

Get
Return strButtonYes
End Get

Set(ByVal Value As String)
strButtonYes = Value
End Set

End Property

' Message box Text for No button

Public Property MessageBoxButtonNoText() As String

Get
Return strButtonNo
End Get

Set(ByVal Value As String)
strButtonNo = Value
End Set

End Property

' Message box Text for Cancel button
Public Property MessageBoxButtonCancelText() As String
Get
Return strButtonCancel
End Get

Set(ByVal Value As String)
strButtonCancel = Value
End Set

End Property



' Message box buttons width
Public Property MessageBoxButtonWidth() As Integer

Get
Return strButtonWidth
End Get

Set(ByVal Value As Integer)
strButtonWidth = Value
End Set

End Property

'' Message box width
'Public Property MessageBoxWidth() As Integer

' Get
' Return intMessageBoxWidth
' End Get

' Set(ByVal Value As Integer)
' intMessageBoxWidth = Value
' End Set

'End Property

' Message box width
Public Property MessageBoxWidth() As String

Get
Return strMessageBoxWidth
End Get

Set(ByVal Value As String)
strMessageBoxWidth = Value
End Set

End Property

' Message box height

Public Property MessageBoxHeight() As Integer

Get
Return intMessageBoxHeight
End Get

Set(ByVal Value As Integer)
intMessageBoxHeight = Value
End Set

End Property

' Message box image width
Public Property MessageBoxImageWidth() As Integer

Get
Return intMessageBoxImageWidth
End Get

Set(ByVal Value As Integer)
intMessageBoxImageWidth = Value
End Set

End Property

' Message box image height
Public Property MessageBoxImageHeight() As Integer

Get
Return intMessageBoxImageHeight
End Get

Set(ByVal Value As Integer)
intMessageBoxImageHeight = Value
End Set

End Property

#End Region

Protected Friend layer As HtmlGenericControl
Protected Friend ilayer As HtmlGenericControl
Protected Friend img As HtmlGenericControl
Protected Friend div As HtmlGenericControl
Protected Friend ButtonOK As Button
Protected Friend ButtonYes As Button
Protected Friend ButtonNo As Button
Protected Friend ButtonCancel As Button
Protected Alertimage As System.Web.UI.WebControls.Image

Public Sub New()

MyBase.New("div")

End Sub

Protected Overrides Sub OnInit(ByVal e As EventArgs)

MyBase.OnInit(e)

' Default properties settings for message box control

If strLeft Is Nothing Then
strLeft = "250"
End If


If strTop Is Nothing Then
strTop = "250"
End If


If strTitle Is Nothing Then
strTitle = "MessageBox"
End If

If intButton < 0 Then
intButton = 1
End If

If strMessageBoxIDYes Is Nothing Then
strMessageBoxIDYes = "MessageBoxIDYes"
End If

If strMessageBoxIDNo Is Nothing Then
strMessageBoxIDNo = "MessageBoxIDNo"
End If

If strMessageBoxIDCancel Is Nothing Then
strMessageBoxIDCancel = "MessageBoxIDCancel"
End If

If strCss Is Nothing Then
strCss = ""
End If

If strCssMessage Is Nothing Then
strCssMessage = ""
End If

If strCssTitle Is Nothing Then
strCssTitle = ""
End If


If strMessage Is Nothing Then
strMessage = "No message to display here."
End If


If intButton = 1 Or intButton > 3 Or intButton < 1 Then
If strButtonYes Is Nothing Then
strButtonYes = "OK"
End If

ElseIf intButton > 1 And intButton < 4 Then
If strButtonYes Is Nothing Then
strButtonYes = "Approve"
End If

If strButtonNo Is Nothing Then
strButtonNo = "Cancel"
End If

If strButtonCancel Is Nothing Then
strButtonCancel = "Ignore"
End If

End If

If strButtonWidth < 5 Then
strButtonWidth = 70
End If


If intMessageBoxWidth < 10 Then
intMessageBoxWidth = 250
End If


If intMessageBoxHeight < 1 Then
intMessageBoxHeight = 8
End If


If intMessageBoxImageWidth < 5 Then
intMessageBoxImageWidth = 36
End If


If intMessageBoxImageHeight < 5 Then
intMessageBoxImageHeight = 36
End If


If homedirectory Is Nothing Then
homedirectory = Me.Page.Request.PhysicalApplicationPath
End If

End Sub


#Region "createChildControls"

Protected Overrides Sub createChildControls()

' Creating message box

Dim myRow As TableRow
Dim myCell As TableCell

Dim myTable As Table = New Table()
myTable.BorderWidth = New Unit(0)
myTable.CellSpacing = 0
myTable.Width = New Unit(intMessageBoxWidth)
myTable.Height = New Unit(intMessageBoxHeight)
Controls.Add(myTable)


myRow = New TableRow()
myRow.BorderWidth = New Unit(0)
myTable.Rows.Add(myRow)

myCell = New TableCell()

Dim NewLabel As Label = New Label()

NewLabel.Text = strTitle

NewLabel.CssClass = strCssTitle

myCell.Controls.Add(NewLabel)


myCell.ID = "dragbar"

myCell.ColumnSpan = 5



myCell.CssClass = strCssTitle

If strCssTitle = "" Then

myCell.ForeColor = System.Drawing.Color.White
myCell.BackColor = System.Drawing.Color.DarkBlue
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
myCell.Style.Add("CURSOR", "hand")

End If


myRow.Cells.Add(myCell)
myRow = New TableRow()
myRow.BorderWidth = New Unit(0)

myTable.Rows.Add(myRow)


myCell = New TableCell()
myCell.ColumnSpan = 5
myCell.CssClass = strCssMessage

If strCssMessage = "" Then
myCell.BackColor = System.Drawing.Color.LightGray
End If

myRow.Cells.Add(myCell)


Dim myRow1 As TableRow
Dim myCell1 As TableCell


Dim myTable1 As Table = New Table()
myTable1.BorderWidth = New Unit(0)
myTable1.CellSpacing = 0

myCell.Controls.Add(myTable1)
myRow1 = New TableRow()
myRow1.BorderWidth = New Unit(0)

myTable1.Rows.Add(myRow1)


myCell1 = New TableCell()
myCell1.CssClass = strCssMessage
myCell1.BorderWidth = New Unit(0)
myCell1.Width = New Unit(36)


Dim Alertimage As System.Web.UI.WebControls.Image = New System.Web.UI.WebControls.Image()
Alertimage.Height = New Unit(intMessageBoxImageHeight)
Alertimage.Width = New Unit(intMessageBoxImageWidth)
Alertimage.BorderWidth = New Unit(0)

Alertimage.ImageUrl = strImage
myCell1.Controls.Add(Alertimage)

myRow1.Cells.Add(myCell1)
myCell1 = New TableCell()
myCell1.CssClass = strCssMessage
myCell1.BorderWidth = New Unit(0)




myCell1.CssClass = strCssMessage

If strCssMessage = "" Then
myCell1.HorizontalAlign = HorizontalAlign.Center
myCell1.ForeColor = System.Drawing.Color.Black
myCell1.BackColor = System.Drawing.Color.LightGray
myCell1.BorderColor = System.Drawing.Color.LightGray
myCell1.Font.Name = "Verdana"
myCell1.Font.Bold = True
myCell1.Font.Size = New FontUnit(8)

End If


Dim NewLabel1 As Label = New Label()

NewLabel1.Text = strMessage

myCell1.Controls.Add(NewLabel1)

myRow1.Cells.Add(myCell1)

myRow = New TableRow()
myRow.BorderWidth = New Unit(0)
myTable.Rows.Add(myRow)

If intButton = 1 Or intButton > 3 Or intButton < 1 Then
myCell = New TableCell()
myCell.ColumnSpan = 5
myCell.BorderWidth = New Unit(0)
myCell.CssClass = strCssMessage
myCell.HorizontalAlign = HorizontalAlign.Center


If strCssMessage = "" Then
myCell.ForeColor = System.Drawing.Color.Black
myCell.BackColor = System.Drawing.Color.LightGray
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)
End If


ButtonOK = New Button()
ButtonOK.ID = strMessageBoxIDYes
ButtonOK.Text = strButtonYes
ButtonOK.Width = New Unit(strButtonWidth)
ButtonOK.Style.Add("CURSOR", "hand")
myCell.Controls.Add(ButtonOK)

myRow.Cells.Add(myCell)

End If


If intButton > 1 And intButton < 4 Then
myCell = New TableCell()
myCell.CssClass = strCssMessage
myCell.BorderWidth = New Unit(0)
myCell.HorizontalAlign = HorizontalAlign.Right


If strCssMessage = "" Then
myCell.ForeColor = System.Drawing.Color.Black
myCell.BackColor = System.Drawing.Color.LightGray
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)

End If

ButtonYes = New Button()
ButtonYes.ID = strMessageBoxIDYes
ButtonYes.Text = strButtonYes
ButtonYes.Width = New Unit(strButtonWidth)
ButtonYes.Style.Add("CURSOR", "hand")
myCell.Controls.Add(ButtonYes)


myRow.Cells.Add(myCell)

myCell = New TableCell()
myCell.Width = New Unit(20)
myCell.BorderWidth = New Unit(0)
myCell.CssClass = strCssMessage


If strCssMessage = "" Then
myCell.BackColor = System.Drawing.Color.LightGray
End If


myRow.Cells.Add(myCell)
myCell = New TableCell()
myCell.CssClass = strCssMessage
myCell.BorderWidth = New Unit(0)


If strCssMessage = "" Then

myCell.ForeColor = System.Drawing.Color.Black

myCell.BackColor = System.Drawing.Color.LightGray
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)

End If


If intButton = 2 Then
myCell.HorizontalAlign = HorizontalAlign.Left
ElseIf intButton = 3 Then
myCell.HorizontalAlign = HorizontalAlign.Center
End If


ButtonNo = New Button()
ButtonNo.ID = strMessageBoxIDNo
ButtonNo.Text = strButtonNo
ButtonNo.Width = New Unit(strButtonWidth)
ButtonNo.Attributes("WIDTH") = strButtonWidth.ToString()
ButtonNo.Attributes("HEIGHT") = strButtonWidth.ToString()
ButtonNo.Style.Add("CURSOR", "hand")
myCell.Controls.Add(ButtonNo)
myRow.Cells.Add(myCell)


If intButton = 3 Then

myCell = New TableCell()
myCell.Width = New Unit(10)
myCell.BorderWidth = New Unit(0)
myCell.CssClass = strCssMessage


If strCssMessage = "" Then

myCell.BackColor = System.Drawing.Color.LightGray

End If


myRow.Cells.Add(myCell)
myCell = New TableCell()
myCell.CssClass = strCssMessage
myCell.BorderWidth = New Unit(0)
myCell.HorizontalAlign = HorizontalAlign.Left


If strCssMessage = "" Then
myCell.ForeColor = System.Drawing.Color.Black
myCell.BackColor = System.Drawing.Color.LightGray
myCell.Font.Name = "Verdana"
myCell.Font.Bold = True
myCell.Font.Size = New FontUnit(8)

End If


ButtonCancel = New Button()
ButtonCancel.ID = strMessageBoxIDCancel
ButtonCancel.Text = strButtonCancel
ButtonCancel.Width = New Unit(strButtonWidth)
ButtonCancel.Style.Add("CURSOR", "hand")
myCell.Controls.Add(ButtonCancel)


myRow.Cells.Add(myCell)


End If

End If

End Sub

#End Region
Protected Overrides Sub AddAttributesToRender(ByVal writer As HtmlTextWriter)

' Rendering message box control to the browser

MyBase.AddAttributesToRender(writer)
writer.AddAttribute(HtmlTextWriterAttribute.Id, "showimage")
'writer.AddAttribute(HtmlTextWriterAttribute.Style, "Z-INDEX: 9999; LEFT:" + strLeft + "px; WIDTH:" + strLeft + "px; POSITION: absolute; TOP: " + strTop + "px; filter:progid:DXImageTransform.Microsoft.Shadow(color='dimgray', direction="135," strength="3;"")
writer.AddAttribute(HtmlTextWriterAttribute.Style, "Z-INDEX: 9999; LEFT:" + strLeft + "px; WIDTH:" + strMessageBoxWidth + "px; POSITION: absolute; TOP: " + strTop + "px; filter:progid:DXImageTransform.Microsoft.Shadow(color='dimgray', direction=135), strength=3;")

End Sub

End Class

One database reusable class for SQL Server communication

create two classes
1. 'SQLConnection' (main class to communicate with db)
2. 'SQLStoredProcedureParameterInfo' to be called from main class to get parameter info.


Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Collections.Generic
Imports System.Data.SqlClient

'class 1
Public Class SQLConnection
Inherits MarshalByRefObject
Implements IDisposable

'Private variables
Private m_sConnectionString As String

Private m_objConnection As SqlClient.SqlConnection = Nothing
Private m_objTransaction As SqlClient.SqlTransaction = Nothing

Private disposedValue As Boolean = False ' To detect redundant calls

Private Shared m_objCommandParameterInfo As New Hashtable

Public Sub New(ByVal ConnectionString As String)
m_sConnectionString = ConnectionString
End Sub

Public Function TestConnection() As Boolean
Dim bRetVal As Boolean = False

Const iTestTimeout As Integer = 5 'seconds

Try
With OpenConnection(iTestTimeout)
bRetVal = (.State = ConnectionState.Open)
End With
Catch ex As Exception
bRetVal = False
End Try

Return bRetVal
End Function

Public Function OpenConnection(Optional ByVal Timeout As Integer = 15) As SqlClient.SqlConnection
If (m_objConnection Is Nothing) Then
Dim sConnString As New SqlClient.SqlConnectionStringBuilder(m_sConnectionString)

sConnString.ConnectTimeout = Timeout

m_objConnection = New SqlClient.SqlConnection(sConnString.ToString)
End If

If m_objConnection.State = ConnectionState.Closed Then
With m_objConnection
.Open()
End With
End If

Return m_objConnection
End Function

Public Sub CloseConnection()
If (m_objConnection IsNot Nothing) Then
If m_objConnection.State <> ConnectionState.Closed Then
m_objConnection.Close()
End If

m_objConnection = Nothing
End If
End Sub

Public Function ConnectSP(ByVal Name As String) As SqlClient.SqlCommand
Dim objRetVal As SqlClient.SqlCommand

'Set up SQL command
If m_objTransaction Is Nothing Then
objRetVal = New SqlClient.SqlCommand(Name, OpenConnection)
Else
objRetVal = New SqlClient.SqlCommand(Name, OpenConnection, m_objTransaction)
End If

With objRetVal
.CommandType = CommandType.StoredProcedure

RetrieveCommandParameters(objRetVal)
End With

Return objRetVal
End Function

Public Function ConnectSQL(ByVal SQL As String) As SqlClient.SqlCommand
Dim objRetVal As SqlClient.SqlCommand

'Set up SQL command
If m_objTransaction Is Nothing Then
objRetVal = New SqlClient.SqlCommand(SQL, OpenConnection)
Else
objRetVal = New SqlClient.SqlCommand(SQL, OpenConnection, m_objTransaction)
End If

With objRetVal
.CommandType = CommandType.Text
End With

Return objRetVal
End Function


Public Function BeginTransaction() As Boolean
Dim bRetVal As Boolean = True

Try
With OpenConnection()
m_objTransaction = .BeginTransaction()
End With
Catch
bRetVal = False
End Try

Return bRetVal
End Function

Public Function CommitTransaction() As Boolean
Dim bRetVal As Boolean = True

Try
m_objTransaction.Commit()
m_objTransaction = Nothing
Catch
bRetVal = False
End Try

Return bRetVal
End Function

Public Function RollbackTransaction() As Boolean
Dim bRetVal As Boolean = True

Try
m_objTransaction.Rollback()
m_objTransaction = Nothing
Catch
bRetVal = False
End Try

Return bRetVal
End Function

Private Sub RetrieveCommandParameters(ByVal SQLCommand As SqlClient.SqlCommand)
Dim lstParameterInfo As List(Of SQLStoredProcedureParameterInfo)

With SQLCommand
If m_objCommandParameterInfo.Contains(.CommandText) Then
'Get cached parameter info
lstParameterInfo = m_objCommandParameterInfo(.CommandText)

'Loop through and add a new parameter
For Each objParameterInfo As SQLStoredProcedureParameterInfo In lstParameterInfo
.Parameters.Add(objParameterInfo.CreateParameter())
Next
Else
'Retrieve parameters from database server
SqlClient.SqlCommandBuilder.DeriveParameters(SQLCommand)

'Build parameter info list
lstParameterInfo = New List(Of SQLStoredProcedureParameterInfo)
For Each objParameter As SqlClient.SqlParameter In .Parameters
lstParameterInfo.Add(New SQLStoredProcedureParameterInfo(objParameter))
Next

'...and add into cache for next time
m_objCommandParameterInfo.Add(.CommandText, lstParameterInfo)
End If
End With
End Sub

' IDisposable
Protected Overridable Sub Dispose(ByVal disposing As Boolean)
If Not Me.disposedValue Then
If disposing Then
m_objTransaction = Nothing

If m_objConnection IsNot Nothing Then
If m_objConnection.State <> ConnectionState.Closed Then
m_objConnection.Close()
End If

m_objConnection = Nothing
End If
End If

' TODO: free shared unmanaged resources
End If
Me.disposedValue = True
End Sub

#Region " IDisposable Support "
' This code added by Visual Basic to correctly implement the disposable pattern.
Public Sub Dispose() Implements IDisposable.Dispose
' Do not change this code. Put cleanup code in Dispose(ByVal disposing As Boolean) above.
Dispose(True)
GC.SuppressFinalize(Me)
End Sub
#End Region

End Class

'Class 2
Public Class SQLStoredProcedureParameterInfo
Public Name As String
Public Type As System.Data.SqlDbType
Public Direction As System.Data.ParameterDirection
Public DefaultValue As Object
Public Size As Integer

Public Sub New(ByVal Parameter As SqlClient.SqlParameter)
With Parameter
Name = .ParameterName
Type = .SqlDbType
Direction = .Direction
DefaultValue = .Value
Size = .Size
End With
End Sub

Public Function CreateParameter() As SqlClient.SqlParameter
Dim objRetVal As New SqlClient.SqlParameter

With objRetVal
.ParameterName = Name
.SqlDbType = Type
.Direction = Direction
.Value = DefaultValue
.Size = Size
End With

Return objRetVal
End Function
End Class

''''method to use this class as below:

Using objSqlConnection As SQLConnection = New SQLConnection(ConfigurationManager.AppSettings.Item("SQLConnString").ToString)
Dim strSQL As String

'for SQL
Dim BusinessRegionIntroducerGroupId As Integer
strSQL = "select * from table1 where employid=" & Session("empId")
With objSqlConnection.ConnectSQL(strSQL)
Dim objSQLdatareader As SqlClient.SqlDataReader = .ExecuteReader()
If objSQLdatareader.Read Then
empname= objSQLdatareader.Item("empname")
End If
objSQLdatareader.Close()
End With


'for SP
strSQL = "spSaveEmployee"
With objSqlConnection.ConnectSP(strSQL)
.Parameters("@DeptId").Value = DeptId
.Parameters("@empid").Value = Session("empid").ToString
Dim objSQLdatareader As SqlClient.SqlDataReader = .ExecuteReader()
objSQLdatareader.Close()
End With
End Using