Find Code:
All Words
Any of the Words
Exact Phrase
Home
:
Code
:
Forums
:
Submit
:
Mailing List
:
About
:
Contact
Code
All
VB.NET
ASP.NET
C#
VB Classic
ASP Classic
Snippets
Popular
Resources
Submit Code
Forums
Articles
Tips
Links
Books
Contest
Link to us
Using Hash Tables
Author:
Rajesh Kumar
Submitted:
1/12/2006
Version:
VB.NET 2003
Compatibility:
VB.NET 2003, VB 2005
Category:
Miscellaneous
Views:
37761
This sample Class file gives the complete Data Access methodology by using Hashtables as parameter collection.
Declarations:
'none
Code:
Imports System.Data.SqlClient Imports System.Xml Public Class GENERICDAL Dim DBSetting As System.Configuration.ConfigurationSettings Public strCon As String Dim sqlCon As SqlConnection Dim parameters As SqlParameterCollection Dim intStartRec As Integer Dim dsGeneric As New DataSet Public Sub FetchRecords(ByVal strQry As String, ByRef dt As DataTable) Dim ds As New DataSet Dim sqlDA As New SqlDataAdapter(strQry, strCon) sqlDA.Fill(dt) End Sub Public Function FetchRecords(ByVal strQry As String) As DataSet Dim ds As New DataSet Dim sqlDA As New SqlDataAdapter(strQry, strCon) sqlDA.Fill(ds) Return ds End Function Public Sub OpenConnection() sqlCon = New SqlConnection(strCon) sqlCon.Open() End Sub Public Sub CloseConnection() sqlCon = New SqlConnection(strCon) sqlCon.Close() End Sub Public Sub ExecuteQuery(ByVal strQry As String, ByVal htParam As Hashtable, ByRef dt As DataTable) OpenConnection() Dim sqlcom As New SqlCommand(strQry, sqlCon) Dim myEnumerator As IDictionaryEnumerator = htParam.GetEnumerator() While myEnumerator.MoveNext() sqlcom.Parameters.Add(myEnumerator.Key.ToString(), myEnumerator.Value.ToString()) End While Dim sqlda As New SqlDataAdapter(sqlcom) sqlda.Fill(dt) CloseConnection() End Sub Public Function ExecuteNonQuery(ByVal strQry As String, Optional ByVal htParam As Hashtable = Nothing) As Integer Try Dim intRetVal As Integer OpenConnection() Dim sqlcom As New SqlCommand(strQry, sqlCon) If Not htParam Is Nothing Then Dim myEnumerator As IDictionaryEnumerator = htParam.GetEnumerator() While myEnumerator.MoveNext() If myEnumerator.Key.ToString() <> Nothing And myEnumerator.Value.ToString() <> Nothing Then sqlcom.Parameters.Add(myEnumerator.Key.ToString(), myEnumerator.Value.ToString()) Else sqlcom.Parameters.Add(myEnumerator.Key.ToString(), DBNull.Value) End If End While End If intRetVal = sqlcom.ExecuteNonQuery() CloseConnection() Return intRetVal Catch ex As SqlClient.SqlException MsgBox(ex.Message) End Try End Function Public Function ExecuteScalar(ByVal strQry As String, Optional ByVal htParam As Hashtable = Nothing) Dim RetVal As Object OpenConnection() Dim sqlcom As New SqlCommand(strQry, sqlCon) If Not htParam Is Nothing Then Dim myEnumerator As IDictionaryEnumerator = htParam.GetEnumerator() While myEnumerator.MoveNext() If myEnumerator.Key.ToString() <> Nothing And myEnumerator.Value.ToString() <> Nothing And myEnumerator.Value.ToString() <> "0" Then sqlcom.Parameters.Add(myEnumerator.Key.ToString(), myEnumerator.Value.ToString()) Else sqlcom.Parameters.Add(myEnumerator.Key.ToString(), DBNull.Value) End If End While End If RetVal = sqlcom.ExecuteScalar() CloseConnection() Return RetVal End Function End Class
Home
|
Forums
|
Submit
|
Books
|
Mailing List
|
Advertising
|
About
|
Contact
© 2024 A1VBCode. All rights reserved.
Legal disclaimer & terms of use
Privacy statement