Posted By

indra on 08/24/10


Tagged

sqlserver


Versions (?)

SQL2KConnect


 / Published in: VB.NET
 

Class for access and manipulation database

  1. Imports System.Data.SqlClient
  2.  
  3. Namespace DBConnect
  4. ''' <summary>
  5. ''' Use this Class to Connect to SQL Server 2000
  6. ''' </summary>
  7. ''' <remarks></remarks>
  8. Public Class SQL2KConnect
  9. Private Shared strConString As String = ""
  10. Private Shared sqlCon As SqlConnection
  11. Private Shared sqlCmd As SqlCommand
  12. 'Private Shared sqlTrans As SqlTransaction
  13.  
  14. ''' <summary>
  15. ''' Create New Instance With Set the Connection String
  16. ''' </summary>
  17. ''' <param name="ConnectionString">Set the Connection String for Open New Connection</param>
  18. Sub New(ByVal ConnectionString As String)
  19. strConString = ConnectionString
  20. End Sub
  21.  
  22. ''' <summary>
  23. ''' Create New Instance Without Set the Connection String
  24. ''' </summary>
  25. Sub New()
  26.  
  27. End Sub
  28.  
  29. ''' <summary>
  30. ''' Get or Set the Connection String
  31. ''' </summary>
  32. Public Property ConnectionString() As String
  33. Get
  34. Return strConString
  35. End Get
  36. Set(ByVal value As String)
  37. strConString = value
  38. End Set
  39. End Property
  40.  
  41. ''' <summary>
  42. ''' Get State of the Open Connection
  43. ''' </summary>
  44. Public ReadOnly Property StateConnection() As ConnectionState
  45. Get
  46. Return sqlCon.State
  47. End Get
  48. End Property
  49.  
  50. ''' <summary>
  51. ''' Open the Connection
  52. ''' </summary>
  53. Public Function OpenConnection() As Boolean
  54. Try
  55. sqlCon = New SqlConnection(strConString)
  56. sqlCon.Open()
  57. 'sqlTrans = sqlCon.BeginTransaction()
  58. Return True
  59. Catch ex As SqlException
  60. Throw New Exception(ex.Message.ToString, ex)
  61. End Try
  62. End Function
  63.  
  64. ''' <summary>
  65. ''' Open the Connection
  66. ''' </summary>
  67. ''' <param name="conString">The Connection String</param>
  68. Public Function OpenConnection(ByVal conString As String) As Boolean
  69. Try
  70. strConString = conString
  71. sqlCon = New SqlConnection(strConString)
  72. sqlCon.Open()
  73. 'sqlTrans = sqlCon.BeginTransaction()
  74. Return True
  75. Catch ex As SqlException
  76. Throw New Exception(ex.Message.ToString, ex)
  77. End Try
  78. End Function
  79.  
  80. ''' <summary>
  81. ''' Close the Connection
  82. ''' </summary>
  83. Public Sub CloseConnection()
  84. Try
  85. If sqlCon.State = ConnectionState.Open Then
  86. 'sqlTrans.Commit()
  87. sqlCon.Close()
  88. End If
  89. Catch ex As Exception
  90. 'If Not sqlTrans.Connection Is DBNull.Value Then
  91. ' sqlTrans.Rollback()
  92. 'End If
  93. Throw New Exception(ex.Message.ToString, ex)
  94. End Try
  95. End Sub
  96.  
  97. ''' <summary>
  98. ''' Execute Update, Delete and Insert Query
  99. ''' </summary>
  100. ''' <param name="query">Query which you want to execute</param>
  101. ''' <returns></returns>
  102. Public Function ExecuteNonQuery(ByVal query As String) As Boolean
  103. Try
  104. 'sqlCmd = New SqlCommand(query, sqlCon, sqlTrans)
  105. sqlCmd = New SqlCommand(query, sqlCon)
  106. 'sqlCmd.CommandTimeout = 0
  107. sqlCmd.CommandType = CommandType.Text
  108. sqlCmd.ExecuteNonQuery()
  109. Return True
  110. Catch ex As Exception
  111. If sqlCon.State = ConnectionState.Open Then
  112. 'If Not sqlTrans.Connection Is DBNull.Value Then
  113. ' sqlTrans.Rollback()
  114. 'End If
  115. sqlCon.Close()
  116. End If
  117. Throw New Exception(ex.Message.ToString, ex)
  118. End Try
  119. End Function
  120.  
  121. ''' <summary>
  122. ''' Execute Select Query
  123. ''' </summary>
  124. ''' <param name="query">Query which you want to execute</param>
  125. Public Function ExecuteQuery(ByVal query As String) As SqlDataReader
  126. Dim reader As SqlDataReader
  127. Try
  128. 'sqlCmd = New SqlCommand(query, sqlCon, sqlTrans)
  129. sqlCmd = New SqlCommand(query, sqlCon)
  130. 'sqlCmd.CommandTimeout = 0
  131. reader = sqlCmd.ExecuteReader
  132. Return reader
  133. Catch ex As Exception
  134. If sqlCon.State = ConnectionState.Open Then
  135. 'If Not sqlTrans.Connection Is DBNull.Value Then
  136. ' sqlTrans.Rollback()
  137. 'End If
  138. sqlCon.Close()
  139. End If
  140. Throw New Exception(ex.Message.ToString, ex)
  141. End Try
  142. End Function
  143.  
  144. ''' <summary>
  145. ''' execute Select Query
  146. ''' </summary>
  147. ''' <param name="query">Query which you want to execute</param>
  148. ''' <param name="tableName">Table name</param>
  149. Public Function ExecuteQuery(ByVal query As String, ByVal tableName As String) As DataTable
  150. Dim dt As DataTable
  151. Dim da As New SqlDataAdapter
  152. Try
  153. 'sqlCmd = New SqlCommand(query, sqlCon, sqlTrans)
  154. sqlCmd = New SqlCommand(query, sqlCon)
  155. 'sqlCmd.CommandTimeout = 0
  156. dt = New DataTable(tableName)
  157. sqlCmd.CommandType = CommandType.Text
  158. da.SelectCommand = sqlCmd
  159. da.Fill(dt)
  160. Return dt
  161. Catch ex As Exception
  162. If sqlCon.State = ConnectionState.Open Then
  163. 'If Not sqlTrans.Connection Is DBNull.Value Then
  164. ' sqlTrans.Rollback()
  165. 'End If
  166. sqlCon.Close()
  167. End If
  168. Throw New Exception(ex.Message.ToString)
  169. End Try
  170. End Function
  171.  
  172. Public Function ExecuteStoredProcedure(ByVal spName As String) As DataTable
  173. Dim dt As DataTable
  174. Dim da As New SqlDataAdapter
  175. Try
  176. 'sqlCmd = New SqlCommand(spName, sqlCon, sqlTrans)
  177. sqlCmd = New SqlCommand(spName, sqlCon)
  178. 'sqlCmd.CommandTimeout = 0
  179. dt = New DataTable("table1")
  180. sqlCmd.CommandType = CommandType.StoredProcedure
  181. da.SelectCommand = sqlCmd
  182. da.Fill(dt)
  183. Return dt
  184. Catch ex As Exception
  185. If sqlCon.State = ConnectionState.Open Then
  186. 'If Not sqlTrans.Connection Is DBNull.Value Then
  187. ' sqlTrans.Rollback()
  188. 'End If
  189. sqlCon.Close()
  190. End If
  191. Throw New Exception(ex.Message.ToString, ex)
  192. End Try
  193. End Function
  194.  
  195. Public Function ExecuteStoredProcedure(ByVal spName As String, ByVal spParam As ArrayList) As DataTable
  196. Dim dt As DataTable
  197. Dim da As New SqlDataAdapter
  198. Try
  199. 'sqlCmd = New SqlCommand(spName, sqlCon, sqlTrans)
  200. sqlCmd = New SqlCommand(spName, sqlCon)
  201. 'sqlCmd.CommandTimeout = 0
  202. dt = New DataTable("table1")
  203. sqlCmd.CommandType = CommandType.StoredProcedure
  204. For Each obj As SqlParameter In spParam
  205. sqlCmd.Parameters.Add(New SqlParameter(obj.ParameterName, obj.Value))
  206. Next
  207. da.SelectCommand = sqlCmd
  208. da.Fill(dt)
  209. Return dt
  210. Catch ex As Exception
  211. If sqlCon.State = ConnectionState.Open Then
  212. 'If Not sqlTrans.Connection Is DBNull.Value Then
  213. ' sqlTrans.Rollback()
  214. 'End If
  215. sqlCon.Close()
  216. End If
  217. Throw New Exception(ex.Message.ToString, ex)
  218. End Try
  219. End Function
  220.  
  221. Public Function ExecuteNonQueryStoredProcedure(ByVal spName As String, ByVal spParam As ArrayList) As Boolean
  222. Dim da As New SqlDataAdapter
  223. Dim str As String = ""
  224. Try
  225. 'sqlCmd = New SqlCommand(spName, sqlCon, sqlTrans)
  226. sqlCmd = New SqlCommand(spName, sqlCon)
  227. 'sqlCmd.CommandTimeout = 0
  228. sqlCmd.CommandText = spName
  229. For Each obj As SqlParameter In spParam
  230. sqlCmd.Parameters.Add(New SqlParameter(obj.ParameterName, obj))
  231. Next
  232. sqlCmd.Connection = sqlCon
  233. 'sqlCmd.Transaction = sqlTrans
  234. sqlCmd.CommandType = CommandType.StoredProcedure
  235. sqlCmd.ExecuteScalar()
  236. Return True
  237. Catch ex As Exception
  238. If sqlCon.State = ConnectionState.Open Then
  239. 'If Not sqlTrans.Connection Is DBNull.Value Then
  240. ' sqlTrans.Rollback()
  241. 'End If
  242. sqlCon.Close()
  243. End If
  244. Throw New Exception(ex.Message.ToString, ex)
  245. End Try
  246. End Function
  247. End Class
  248. End Namespace

Report this snippet  

You need to login to post a comment.