Connection.vb
Imports System.Configuration
Imports System.Data.SqlClient
Public Class DBConnection
Private objConnection As
SqlConnection
Public Shared
ConnectionString As String
= System.Configuration.ConfigurationManager.AppSettings.Get("con")
'Data Source=AMIT-PC;Initial Catalog=AMP;Integrated
Security=True
Public Shared
CurrentYear As String
Public Shared
CurrentDB As String
Public WriteOnly Property SetConnection() As
String
Set(ByVal Value As String)
objConnection
= New SqlConnection(Value)
End Set
End Property
Public ReadOnly Property GetConnection() As
SqlConnection
Get
Return objConnection
End Get
End Property
Public Sub New(ByVal
ConnectionString As String)
Try
SetConnection = ConnectionString
Catch ex As Exception
Throw ex
End Try
End Sub
Public Sub New()
Try
Dim ConnectionString As
String = DBConnection.ConnectionString
SetConnection = ConnectionString
Catch ex As Exception
Throw ex
End Try
End Sub
' This method use for Open connection
Public Sub
OpenConnection()
Try
GetConnection.Open()
Catch ex As Exception
Throw ex
End Try
End Sub
' This method use for Close connection
Public Sub
CloseConnection()
Try
GetConnection.Close()
Catch ex As Exception
Throw ex
End Try
End Sub
End Class
Operation.vb
Imports System.Data.SqlClient
Imports System.Collections
Public Class DBOperation
Public objConnection As
DBConnection
Private _objTransaction As
SqlTransaction
#Region "Constructor"
Public Sub New()
objConnection = New DBConnection
End Sub
#End Region
#Region " Property"
Public ReadOnly Property ConnectionObject() As
SqlConnection
Get
Return objConnection.GetConnection
End Get
End Property
Public ReadOnly Property Transaction() As
SqlTransaction
Get
Return _objTransaction
End Get
End Property
#End Region
#Region "Other"
Public Function
StartNewTransaction() As SqlTransaction
Try
If objConnection.GetConnection.State = ConnectionState.Closed Or
_
objConnection.GetConnection.State = ConnectionState.Broken
Then
objConnection.GetConnection.Open()
End If
_objTransaction = ConnectionObject.BeginTransaction()
Return _objTransaction
Catch ex As Exception
Throw ex
End Try
End Function
Public Sub
CloseConnection()
Try
If ConnectionObject.State <> ConnectionState.Broken And
_
ConnectionObject.State <> ConnectionState.Closed
Then objConnection.CloseConnection()
Catch ex As Exception
End Try
End Sub
'' This method use for exceute Sql command which is return
first column of First row object
'Public Function executeScalar(ByVal commandString As
String) As Object
' Dim objResult As
Object
' Try
' Dim
objSqlCommand As New SqlCommand
'
objSqlCommand.CommandTimeout = 500
' Try
' With
objSqlCommand
'
.CommandText = commandString
'
.CommandType = CommandType.StoredProcedure
'
.Connection = objConnection.GetConnection
' End With
' If objConnection.GetConnection.State
= ConnectionState.Closed Then objConnection.OpenConnection()
'
objResult = objSqlCommand.ExecuteScalar
' Catch ex As
Exception
' Throw ex
'
objResult = Nothing
' Finally
'
objConnection.CloseConnection()
' End Try
' Catch ex As
Exception
' Throw ex
' objResult =
Nothing
' End Try
' Return objResult
'End Function
' This method use for exceute Sql command
(insert,update,delete) which is return no. of Updated rows
Public Function
executeNonQuery(ByVal CommandString As String) As Integer
Dim intResult As Integer
Try
Dim objSqlCommand As New SqlCommand
objSqlCommand.CommandTimeout = 500
Try
With objSqlCommand
.CommandText = CommandString
.CommandType = CommandType.StoredProcedure
.Connection = objConnection.GetConnection
End With
If objConnection.GetConnection.State = ConnectionState.Closed Then
objConnection.OpenConnection()
intResult = objSqlCommand.ExecuteNonQuery
Catch ex As Exception
Throw
ex
intResult = Nothing
Finally
objConnection.CloseConnection()
End Try
Catch ex As Exception
Throw ex
intResult = Nothing
End Try
Return intResult
End Function
' This method use for exceute Sql command (select) which is
return rows
Public Function
execute(ByVal CommandString As String) As DataSet
Dim dsResult As DataSet
Dim objSqlCommand As New SqlCommand
objSqlCommand.CommandTimeout = 500
Dim objSqlDataAdapter As
New SqlDataAdapter
Try
With objSqlCommand
.CommandText = CommandString
.CommandType = CommandType.StoredProcedure
.Connection =
objConnection.GetConnection
End With
objSqlDataAdapter.SelectCommand = objSqlCommand
dsResult = New DataSet
objSqlDataAdapter.Fill(dsResult)
Catch ex As Exception
Throw ex
dsResult = Nothing
End Try
Return dsResult
End Function
' '' This method use for exceute Sql command with Parameter
which is return first column of First row object
'Public Function executeScalar(ByVal commandString As
String, ByVal parameter As ICollection) As Object
' Dim objResult As
Object
' Try
' Dim
objIEnumerator As IEnumerator
' Dim
objSqlCommand As New SqlCommand
'
objSqlCommand.CommandTimeout = 500
' Try
'
objIEnumerator = parameter.GetEnumerator
' With
objSqlCommand
'
.CommandText = commandString
'
.CommandType = CommandType.StoredProcedure
'
.Connection = objConnection.GetConnection
' End With
'
objSqlCommand.Parameters.Clear()
' While
objIEnumerator.MoveNext
'
objSqlCommand.Parameters.Add(objIEnumerator.Current)
' End
While
' If
objConnection.GetConnection.State = ConnectionState.Closed Then
objConnection.OpenConnection()
'
objResult = objSqlCommand.ExecuteScalar
' Catch ex As
Exception
' Throw ex
'
objResult = Nothing
' Finally
'
objConnection.CloseConnection()
' End Try
' Catch ex As
Exception
' Throw ex
' objResult =
Nothing
' End Try
' Return objResult
'End Function
'Public Function executeScalar(ByVal commandString As
String, ByVal parameter As ICollection, ByVal objTran As
SqlClient.SqlTransaction) As Object
' Dim objResult As
Object
' Try
' Dim
objIEnumerator As IEnumerator
' Dim
objSqlCommand As New SqlCommand
'
objSqlCommand.CommandTimeout = 500
' Try
'
objIEnumerator = parameter.GetEnumerator
' With
objSqlCommand
'
.CommandText = commandString
' .CommandType =
CommandType.StoredProcedure
'
.Connection = objConnection.GetConnection
'
.Transaction = objTran
' End With
'
objSqlCommand.Parameters.Clear()
' While objIEnumerator.MoveNext
'
objSqlCommand.Parameters.Add(objIEnumerator.Current)
' End
While
' If
objConnection.GetConnection.State = ConnectionState.Closed Then
objConnection.OpenConnection()
'
objResult = objSqlCommand.ExecuteScalar
' Catch ex As
Exception
' Throw ex
'
objResult = Nothing
' Finally
'
objConnection.CloseConnection()
' End Try
' Catch ex As
Exception
' Throw ex
' objResult =
Nothing
' End Try
' Return objResult
'End Function
' This method use for exceute Sql command
(insert,update,delete) with Parameter which is return no. of Updated rows
Public Function
executeNonQuery(ByVal CommandString As String, ByVal parameter As ICollection, _
Optional ByRef
ErrorCode As Integer
= 0) As Integer
Dim intResult As Integer
Try
Dim objIEnumerator As
IEnumerator
Dim objSqlCommand As New SqlCommand
objSqlCommand.CommandTimeout = 500
Try
objIEnumerator = parameter.GetEnumerator
With objSqlCommand
.CommandText = CommandString
.CommandType = CommandType.StoredProcedure
.Connection = objConnection.GetConnection
End With
objSqlCommand.Parameters.Clear()
While objIEnumerator.MoveNext
objSqlCommand.Parameters.Add(objIEnumerator.Current)
End While
If objConnection.GetConnection.State = ConnectionState.Closed Then
objConnection.OpenConnection()
intResult = objSqlCommand.ExecuteNonQuery
Catch exsql As SqlException
ErrorCode = exsql.ErrorCode
Throw exsql
Catch ex As Exception
Throw ex
intResult = Nothing
Finally
objConnection.CloseConnection()
End Try
Catch ex As Exception
Throw ex
intResult = Nothing
End Try
Return intResult
End Function
' This method use for exceute Sql command (insert,update,delete)
with Parameter which is return no. of Updated rows
' This is exclusively used for transcations.
Public Function
executeNonQuery(ByVal CommandString As String, ByVal parameter As ICollection, _
ByVal objTransaction As
SqlTransaction, Optional
ByRef ErrorCode As
Integer = 0) As
Integer
Dim intResult As Integer
Try
Dim objIEnumerator As
IEnumerator
Dim objSqlCommand As New SqlCommand
objSqlCommand.CommandTimeout = 500
Try
objIEnumerator = parameter.GetEnumerator
With objSqlCommand
.CommandText = CommandString
.CommandType = CommandType.StoredProcedure
.Connection = objTransaction.Connection
.Transaction = objTransaction
End With
objSqlCommand.Parameters.Clear()
While objIEnumerator.MoveNext
objSqlCommand.Parameters.Add(objIEnumerator.Current)
End While
intResult = objSqlCommand.ExecuteNonQuery
Catch exsql As SqlException
ErrorCode = exsql.ErrorCode
Throw exsql
Catch ex As Exception
Throw ex
intResult = Nothing
End Try
Catch ex As Exception
Throw ex
intResult = Nothing
End Try
Return intResult
End Function
' This method use for exceute Sql command (select) with
Parameter which is return rows
Public Function
execute(ByVal CommandString As String, ByVal parameter As ICollection) As DataSet
Dim dsResult As DataSet
Dim objIEnumerator As
IEnumerator
Dim objSqlCommand As New SqlCommand
objSqlCommand.CommandTimeout = 500
Dim objSqlDataAdapter As
New SqlDataAdapter
Try
objIEnumerator = parameter.GetEnumerator
With objSqlCommand
.CommandText = CommandString
.CommandType = CommandType.StoredProcedure
.Connection = objConnection.GetConnection
End With
objSqlCommand.Parameters.Clear()
While objIEnumerator.MoveNext
objSqlCommand.Parameters.Add(objIEnumerator.Current)
End While
objSqlDataAdapter.SelectCommand = objSqlCommand
dsResult = New DataSet
objSqlDataAdapter.Fill(dsResult)
Catch ex As Exception
Throw ex
dsResult = Nothing
End Try
Return dsResult
End Function
Public Function
execute(ByVal CommandString As String, ByVal parameter As ICollection, ByVal
objTran As SqlClient.SqlTransaction)
As DataSet
Dim dsResult As DataSet
Dim objIEnumerator As
IEnumerator
Dim objSqlCommand As New SqlCommand
objSqlCommand.CommandTimeout = 500
Dim objSqlDataAdapter As
New SqlDataAdapter
Try
objIEnumerator = parameter.GetEnumerator
With objSqlCommand
.CommandText = CommandString
.CommandType = CommandType.StoredProcedure
.Connection = objConnection.GetConnection
.Transaction = objTran
End With
objSqlCommand.Parameters.Clear()
While objIEnumerator.MoveNext
objSqlCommand.Parameters.Add(objIEnumerator.Current)
End While
objSqlDataAdapter.SelectCommand = objSqlCommand
dsResult = New DataSet
objSqlDataAdapter.Fill(dsResult)
Catch ex As Exception
Throw ex
dsResult = Nothing
End Try
Return dsResult
End Function
'Public Function ExecuteSQL(ByVal sSql As String) As
DataSet
' Dim dsResult As
DataSet
' Dim
objSqlCommand As New SqlCommand
'
objSqlCommand.CommandTimeout = 500
' Dim
objSqlDataAdapter As New SqlDataAdapter
' Try
' With
objSqlCommand
'
.CommandText = sSql
'
.CommandType = CommandType.Text
'
.Connection = objConnection.GetConnection
' End With
'
objSqlCommand.Parameters.Clear()
'
objSqlDataAdapter.SelectCommand = objSqlCommand
' dsResult =
New DataSet
'
objSqlDataAdapter.Fill(dsResult)
' Catch ex As
Exception
' Throw ex
' dsResult =
Nothing
' End Try
' Return dsResult
'End Function
''Executes sql query for scalar output (first column of
first row)
'Public Function ExecuteSQLScalar(ByVal sSql As String) As
Object
' Dim objResult As
Object
' Try
' Dim
objSqlCommand As New SqlCommand
'
objSqlCommand.CommandTimeout = 500
' Try
' With
objSqlCommand
'
.CommandText = sSql
'
.CommandType = CommandType.Text
'
.Connection = objConnection.GetConnection
' End With
' If objConnection.GetConnection.State
= ConnectionState.Closed Then objConnection.OpenConnection()
'
objResult = objSqlCommand.ExecuteScalar
' Catch ex As
Exception
' Throw ex
'
objResult = Nothing
' Finally
'
objConnection.CloseConnection()
' End Try
' Catch ex As
Exception
' Throw ex
' objResult =
Nothing
' End Try
' Return objResult
'End Function
'Public Function ExecuteDirect(ByVal CommandString As
String) As DataSet
' Dim dsResult As
DataSet
' Dim
objSqlCommand As New SqlCommand
'
objSqlCommand.CommandTimeout = 500
' Dim
objSqlDataAdapter As New SqlDataAdapter
' Try
' With
objSqlCommand
'
.CommandText = CommandString
'
.CommandType = CommandType.Text
'
.Connection = objConnection.GetConnection
' End With
'
objSqlCommand.Parameters.Clear()
' objSqlDataAdapter.SelectCommand
= objSqlCommand
' dsResult =
New DataSet
'
objSqlDataAdapter.Fill(dsResult)
' Catch ex As
Exception
' Throw ex
' dsResult =
Nothing
' End Try
' Return dsResult
'End Function
#End Region
End Class
BAL
CountryMasterBL.vb
Imports DAL
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Collections
Public Class CountryMasterBL
Public CountryID As String = String.Empty
Public CountryName As
String
Public objDBOperation As
New DAL.DBOperation
Public Function
Save() As Boolean
Dim blnReturn As Boolean = False
Try
Dim alparameter As New ArrayList
Dim spName As String = "SP_COUNTRY_MASTER_INSERT_UPDATE"
Dim flag As Integer = 1
If CountryID.ToString().Length = 0 Then
CountryID = Guid.NewGuid().ToString()
flag = 0
End If
alparameter.Add(New SqlParameter("@CountryId",
CountryID))
alparameter.Add(New SqlParameter("@CountryName",
CountryName))
alparameter.Add(New SqlParameter("@CountryCode",
12))
alparameter.Add(New SqlParameter("@Flag",
flag))
blnReturn
= objDBOperation.executeNonQuery(spName, alparameter)
Catch ex As Exception
Throw ex
End Try
Return blnReturn
End Function
Public Function
Search() As DataSet
Dim ds As New DataSet
Try
Dim
alparameter As New
ArrayList
Dim spName As String = "SP_COUNTRY_MASTER_SEARCH"
alparameter.Add(New SqlParameter("@CountryId",
CountryID))
ds = objDBOperation.execute(spName, alparameter)
Catch ex As Exception
Throw ex
End Try
Return ds
End Function
Public Function
Delete() As Integer
Dim intResult As Integer
Try
Dim alparameter As New ArrayList
Dim spName As String = "SP_COUNTRY_MASTER_DELETE"
alparameter.Add(New SqlParameter("@CountryId",
CountryID))
intResult = objDBOperation.executeNonQuery(spName, alparameter)
Catch ex As Exception
Throw ex
End Try
Return intResult
End Function
End Class
App.config
xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="con" value="Data
Source=AMIT-PC;Initial Catalog=AMP;Integrated Security=True"/>
<add key="Server" value="AMIT-PC"/>
<add key="Database" value="AMP"/>
<add key="UserId" value=""/>
<add key="Password" value=""/>
appSettings>
<system.diagnostics>
<sources>
<source name="DefaultSource" switchName="DefaultSwitch">
<listeners>
<add name="FileLog"/>
listeners>
source>
sources>
<switches>
<add name="DefaultSwitch" value="Information" />
switches>
<sharedListeners>
<add name="FileLog"
type="Microsoft.VisualBasic.Logging.FileLogTraceListener,
Microsoft.VisualBasic, Version=8.0.0.0, Culture=neutral,
PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL"
initializeData="FileLogWriter"/>
sharedListeners>
system.diagnostics>
configuration>
Fomr1.vb
Imports BAL
Public Class Form1
Dim CountryId As String
Private Sub
btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
btnAdd.Click
Try
Dim objsave As New CountryMasterBL
objsave.CountryID = CountryId
objsave.CountryName = txtCountryName.Text
If Not objsave.Save()
<> 1 Then
MsgBox("Record
not Save")
Else
MsgBox("Record Save")
FillGrid()
txtCountryName.Text = ""
End If
Catch ex As Exception
End Try
End Sub
Private Sub
Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles
Me.Load
FillGrid()
End Sub
Public Sub FillGrid()
CountryId = ""
Dim objsearch As New CountryMasterBL
objsearch.CountryID = CountryId
Dim dT As DataTable
dT =
objsearch.Search().Tables(0)
grdCountry.DataSource = dT
End Sub
Private Sub
grdCountry_RowHeaderMouseDoubleClick(ByVal
sender As Object,
ByVal e As
System.Windows.Forms.DataGridViewCellMouseEventArgs)
Handles grdCountry.RowHeaderMouseDoubleClick
CountryId = grdCountry.SelectedCells.Item(0).Value
txtCountryName.Text = grdCountry.SelectedCells.Item(1).Value
End Sub
Private Sub
btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
btnDelete.Click
Dim objDelete As New CountryMasterBL
objDelete.CountryID = CountryId
Dim bDelete As Boolean = objDelete.Delete()
If bDelete = True Then
MsgBox("Record Deleted")
FillGrid()
Else
MsgBox("Record Not Deleted")
End If
End Sub
Private Sub
Update_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles
Update.Click
Dim objupdate As New CountryMasterBL
objupdate.CountryID
= CountryId
objupdate.CountryName = txtCountryName.Text
If Not
objupdate.Save() <> 1 Then
MsgBox("Record not Updated")
Else
MsgBox("Record Updated")
FillGrid()
txtCountryName.Text
= ""
End If
End Sub
End Class
SP_COUNTRY_MASTER_DELETE
USE [AMP]
GO
/******
Object: StoredProcedure
[dbo].[SP_COUNTRY_MASTER_DELETE]
Script Date: 07/05/2012 06:49:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_COUNTRY_MASTER_DELETE]
(
@CountryId VARCHAR(50)
)
AS
IF EXISTS(SELECT 1 FROM STATE_MASTER WHERE
STATE_MASTER.COUNTRYID=@CountryId)
BEGIN
RAISERROR('Reference
Exists for this item , can not delete',16,1)
return
END
DELETE FROM COUNTRY_MASTER WHERE
CountryId=@CountryId
SP_COUNTRY_MASTER_SEARCH
USE [AMP]
GO
/******
Object: StoredProcedure
[dbo].[SP_COUNTRY_MASTER_SEARCH]
Script Date: 07/05/2012 06:52:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_COUNTRY_MASTER_SEARCH]
(
@CountryId varchar(50) = ''
)
as
if @CountryId=''
begin
select CountryId,CountryName,CountryCode
from Country_Master
Order By CountryName
end
else
begin
select CountryId,CountryName,CountryCode
from Country_Master
where CountryId = @CountryId
end
SP_COUNTRY_MASTER_INSERT_UPDATE
USE [AMP]
GO
/******
Object: StoredProcedure
[dbo].[SP_COUNTRY_MASTER_INSERT_UPDATE]
Script Date: 07/05/2012 06:54:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_COUNTRY_MASTER_INSERT_UPDATE]
(
@CountryId varchar(50),
@CountryName varchar(50),
@CountryCode varchar(50),
@Flag int
)
AS
if @flag = 0
begin
IF EXISTS(SELECT 1 FROM Country_Master WHERE
CountryName=@CountryName)
BEGIN
RAISERROR('Country name already
exists.. Cannot be inserted',16,1)
RETURN
END
insert into Country_Master (CountryId,CountryName,CountryCode)
values(@CountryId,@CountryName,@CountryCode)
end
else
begin
IF EXISTS(SELECT 1 FROM
Country_Master WHERE CountryName=@CountryName and CountryId
!= @CountryId)
BEGIN
RAISERROR('Country name already
exists.. Cannot be edited',16,1)
RETURN
END
update Country_Master set
CountryName =@CountryName,
CountryCode =
@CountryCode
where CountryId =@CountryId
end
