RSS
 

Archive for June, 2010

Dynamic Parameterized Queries in VB.Net

01 Jun

I have a bit of code which can be found here. I use this all the time when I am developing classic ASP web application.

I recently found that I have a need for the same methodolgy was needed in a .Net application I recently started developing, so I came up with the following:

Code:

  1. Imports System
  2. Imports System.Data
  3. Imports System.Data.SqlClient
  4.  
  5.  
  6. Namespace ZipCM
  7.  
  8.     Public Class Database
  9.  
  10. #Region "Properties"
  11.         Public ConnString As String
  12.         Public CmdType As Integer
  13.         Public Query As String
  14.         Public Values As Object
  15.         Public ParamNames As Object
  16.         Public ReturnValues As Object
  17.         Public FieldCount As Long
  18.         Public TotalRecords As Long
  19.         Public AffectedRows As Long
  20.         Public ReturnValue As Object
  21.         Public HasReturn As Boolean
  22.         Public ReturnParamName As String
  23.         Public ReturnDataType As Object
  24.  
  25.         Private i As Integer
  26. #End Region
  27.  
  28. #Region "ExecuteDataReader"
  29.         Public Sub ExecuteDataReader()
  30.             Try
  31.                 Dim objConn As New SqlConnection(ConnString)
  32.                 Dim objCmd As New SqlCommand(Query, objConn)
  33.                 Dim objDA As New SqlDataAdapter()
  34.                 Dim objDS As New DataSet()
  35.                 If IsArray(Values) And IsArray(ParamNames) Then
  36.                     If UBound(Values) = UBound(ParamNames) Then
  37.                         ‘Prepare our parameters
  38.                        For Me.i = 0 To UBound(Values)
  39.                             objCmd.Parameters.AddWithValue(ParamNames(i), Values(i))
  40.                         Next
  41.                         objCmd.CommandType = CmdType
  42.                         objCmd.Connection.Open()
  43.                         objDA.SelectCommand = objCmd
  44.                         objDA.Fill(objDS, "Tbl")
  45.                         objCmd = Nothing
  46.                         objConn.Close() : objConn = Nothing
  47.                         Dim DT As DataTable = objDS.Tables("Tbl")
  48.                         FieldCount = DT.Columns.Count
  49.                         TotalRecords = DT.Rows.Count
  50.                         Dim c As Integer
  51.                         Dim r As Integer
  52.                         Dim tmpRetVals(FieldCount – 1, TotalRecords – 1) As Array
  53.                         For c = 0 To FieldCount – 1
  54.                             For r = 0 To TotalRecords – 1
  55.                                 tmpRetVals(c, r) = New String() {DT.Columns.Item(c).ColumnName, DT.Rows(r)(c).ToString()}
  56.                             Next
  57.                         Next
  58.                         ReturnValues = tmpRetVals
  59.  
  60.                     End If
  61.                 End If
  62.             Catch ex As Exception
  63.  
  64.             End Try
  65.         End Sub
  66.  
  67. #End Region
  68.  
  69. #Region "Execute Non Data Reader"
  70.  
  71.         Public Sub ExecuteNDQuery()
  72.             Try
  73.                 Dim objConn As New SqlConnection(ConnString)
  74.                 Dim objCmd As New SqlCommand(Query, objConn)
  75.                 Dim objParam As SqlParameter
  76.                 If IsArray(Values) And IsArray(ParamNames) Then
  77.                     If UBound(Values) = UBound(ParamNames) Then
  78.                         ‘Prepare our parameters
  79.                        If HasReturn Then
  80.                             objParam = New SqlParameter(ReturnParamName, ReturnDataType)
  81.                             objParam.Direction = ParameterDirection.Output
  82.                             objCmd.Parameters.Add(objParam)
  83.                             objParam = Nothing
  84.                         End If
  85.                         For Me.i = 0 To UBound(Values)
  86.                             objCmd.Parameters.AddWithValue(ParamNames(i), Values(i))
  87.                         Next
  88.                         objCmd.CommandType = CmdType
  89.                         objCmd.Connection.Open()
  90.                         If HasReturn Then
  91.                             objCmd.ExecuteNonQuery()
  92.                             ReturnValue = objCmd.Parameters(ReturnParamName).Value
  93.                         Else
  94.                             objCmd.ExecuteNonQuery()
  95.                         End If
  96.                         objCmd.Connection.Close() : objConn = Nothing
  97.  
  98.                     End If
  99.                 End If
  100.             Catch ex As Exception
  101.  
  102.             End Try
  103.         End Sub
  104.  
  105. #End Region
  106.  
  107.     End Class
  108. End Namespace
Link to this post!
 
No Comments

Posted in Web Design

 
 

Optimized by SEO Ultimate