Advertisement
Advertisement
| 05.23.2008 at 12:28PM PDT, ID: 23428716 |
|
[x]
Attachment Details
|
||
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50: 51: 52: 53: 54: 55: 56: 57: 58: 59: 60: 61: 62: 63: 64: 65: 66: 67: 68: 69: 70: 71: 72: 73: 74: 75: 76: 77: 78: 79: 80: 81: 82: 83: 84: 85: 86: 87: 88: 89: 90: 91: 92: 93: 94: 95: 96: 97: 98: 99: 100: 101: 102: 103: 104: 105: 106: 107: 108: 109: 110: 111: 112: 113: 114: 115: 116: 117: 118: 119: 120: 121: 122: 123: 124: 125: 126: 127: 128: 129: 130: 131: 132: 133: 134: 135: 136: 137: 138: 139: 140: 141: 142: 143: 144: 145: 146: 147: 148: 149: 150: 151: 152: 153: 154: 155: 156: 157: 158: 159: 160: 161: 162: 163: 164: 165: 166: 167: 168: 169: 170: 171: 172: 173: 174: 175: 176: 177: 178: 179: 180: 181: 182: 183: 184: 185: 186: 187: 188: 189: 190: 191: 192: 193: 194: 195: 196: 197: 198: 199: 200: 201: 202: 203: 204: 205: 206: 207: 208: 209: 210: 211: 212: 213: 214: 215: 216: 217: 218: 219: 220: 221: 222: 223: 224: 225: 226: 227: 228: 229: 230: 231: 232: 233: 234: 235: 236: 237: 238: 239: 240: 241: 242: 243: 244: 245: 246: 247: 248: 249: 250: 251: 252: 253: 254: 255: 256: 257: 258: 259: 260: 261: 262: 263: 264: 265: 266: 267: 268: 269: 270: 271: 272: 273: 274: 275: 276: 277: 278: 279: 280: 281: 282: 283: 284: 285: 286: 287: 288: 289: 290: 291: 292: 293: 294: 295: 296: 297: 298: 299: 300: 301: 302: 303: 304: 305: 306: 307: 308: 309: 310: 311: 312: 313: 314: 315: 316: 317: 318: 319: 320: 321: 322: 323: 324: 325: 326: 327: 328: 329: 330: 331: 332: 333: 334: 335: 336: 337: 338: 339: 340: 341: 342: 343: 344: 345: 346: 347: 348: 349: 350: 351: 352: 353: 354: 355: 356: 357: 358: 359: 360: 361: 362: 363: 364: 365: 366: 367: 368: 369: 370: 371: 372: 373: 374: 375: 376: 377: 378: 379: 380: 381: 382: 383: 384: 385: 386: 387: 388: 389: 390: 391: 392: 393: 394: 395: 396: 397: 398: 399: 400: 401: 402: 403: 404: 405: 406: 407: 408: 409: 410: 411: 412: 413: 414: 415: 416: 417: 418: 419: 420: 421: 422: 423: 424: 425: 426: 427: 428: 429: 430: 431: 432: 433: 434: 435: 436: 437: 438: 439: 440: 441: 442: 443: 444: 445: 446: 447: 448: 449: 450: 451: 452: 453: 454: 455: 456: 457: 458: 459: 460: 461: 462: 463: 464: 465: 466: 467: 468: 469: 470: 471: 472: 473: 474: 475: 476: 477: 478: 479: 480: 481: 482: 483: 484: 485: 486: 487: 488: 489: 490: 491: 492: 493: 494: 495: 496: 497: 498: 499: 500: 501: 502: 503: 504: 505: 506: 507: 508: 509: 510: 511: 512: 513: 514: 515: 516: 517: 518: 519: 520: |
Imports System
Imports System.Data
Imports System.Data.Common
Public Class DAL
Private Shared _factory As Providers = Providers.SqlClient 'Default
Private Shared _connectionString As String = "Connection string will go here" 'Default
''' <summary>
''' Get or Sets the Connection String
''' </summary>
''' <value></value>
''' <returns>String</returns>
''' <remarks></remarks>
Public Shared Property ConnectionString() As String
Get
Return _connectionString
End Get
Set(ByVal value As String)
_connectionString = value
End Set
End Property
''' <summary>
''' Get Factory By Provider
''' </summary>
''' <param name="oGetFactory"></param>
''' <returns></returns>
''' <remarks></remarks>
Private Shared Function GetFactoryByProvider(ByVal oGetFactory As Providers) As String
Select Case CType(oGetFactory, Providers)
Case Providers.Odbc
Return "System.Data.Odbc"
Case Providers.OleDb
Return "System.Data.OleDb"
Case Providers.SqlClient
Return "System.Data.SqlClient"
Case Providers.OracleClient
Return "System.Data.OracleClient"
Case Providers.MySql
Return "CorLab.MySql.MySqlClient"
End Select
Return ""
End Function
''' <summary>
''' Creates a new instance of a System.Data.Commom.dbParameter object.
''' </summary>
''' <param name="name"></param>
''' <param name="type"></param>
''' <param name="value"></param>
''' <returns>A System.Data.Commom.dbParameter object</returns>
''' <remarks></remarks>
Public Shared Function CreateParameter(ByVal name As String, ByVal type As DbType, ByVal value As Object) As IDataParameter
Return CreateParameter(name, type, value, ParameterDirection.Input)
End Function
''' <summary>
''' Creates a new instance of a System.Data.Commom.dbParameter object.
''' </summary>
''' <param name="name"></param>
''' <param name="type"></param>
''' <param name="value"></param>
''' <param name="direction"></param>
''' <returns>A System.Data.Commom.dbParameter object</returns>
''' <remarks></remarks>
Public Shared Function CreateParameter(ByVal name As String, ByVal type As DbType, ByVal value As Object, ByVal direction As ParameterDirection) As IDataParameter
Dim param As DbParameter = Nothing
Dim oProviderFactory As DbProviderFactory = DbProviderFactories.GetFactory(GetFactoryByProvider(_factory))
Dim Con As DbConnection = oProviderFactory.CreateConnection
Dim cmd As DbCommand = Con.CreateCommand
param = cmd.CreateParameter()
If Not param Is Nothing Then
param.ParameterName = name
param.DbType = type
param.Direction = direction
param.Value = value
End If
Return param
End Function
''' <summary>
''' Executes a Transact-SQL statement against the connection and returns the number of rows affected.
''' </summary>
''' <param name="cmdType">Set the Transact-SQL statement or stored procedure to execute at the data source.</param>
''' <param name="cmdText">The text of the query.</param>
''' <returns></returns>
''' <remarks>The number of rows affected.</remarks>
Public Shared Function ExecuteNonQuery(ByVal cmdType As CommandType, ByVal cmdText As String) As Integer
Return ExecuteNonQuery(cmdType, cmdText, Nothing)
End Function
''' <summary>
''' Executes a Transact-SQL statement against the connection and returns the number of rows affected.
''' </summary>
''' <param name="cmdType">Set the Transact-SQL statement or stored procedure to execute at the data source.</param>
''' <param name="cmdText">The text of the query.</param>
''' <param name="cmdParms">Set Array of Parameter</param>
''' <returns>The number of rows affected.</returns>
''' <remarks></remarks>
Public Shared Function ExecuteNonQuery(ByVal cmdType As CommandType, ByVal cmdText As String, ByVal cmdParms As DbParameter()) As Integer
Dim oProviderFactory As DbProviderFactory = DbProviderFactories.GetFactory(GetFactoryByProvider(_factory))
Dim Con As DbConnection = oProviderFactory.CreateConnection
Dim cmd As DbCommand = Con.CreateCommand
Dim trans As DbTransaction = Nothing
Try
Con.ConnectionString = ConnectionString
cmd.Connection = Con
cmd.CommandText = cmdText
cmd.Parameters.Clear()
cmd.CommandType = cmdType
If Not (IsNothing(cmdParms)) Then
Dim param As DbParameter
For Each param In cmdParms
cmd.Parameters.Add(param)
Next
End If
Con.Open()
trans = Con.BeginTransaction
cmd.Transaction = trans
Dim val As Integer = cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
trans.Commit()
Return val
Catch ex As DbException
trans.Rollback()
Throw New Exception("DB Exception " & ex.Message)
Catch exx As Exception
trans.Rollback()
Throw New Exception("ExecuteNonQuery Function", exx)
Finally
Con.Close()
cmd = Nothing
cmdParms = Nothing
End Try
End Function
''' <summary>
''' Executes the query, and returns the first column of the first row in the result set returned by the query.
''' </summary>
''' <param name="cmdType">Set the Transact-SQL statement or stored procedure to execute at the data source.</param>
''' <param name="cmdText">The text of the query.</param>
''' <returns></returns>
''' <remarks>The first column of the first row in the result set, or a null reference if the result set is empty.</remarks>
Public Shared Function ExecuteScalar(ByVal cmdType As CommandType, ByVal cmdText As String) As Object
Return ExecuteScalar(cmdType, cmdText, Nothing)
End Function
''' <summary>
''' Executes the query, and returns the first column of the first row in the result set returned by the query.
''' </summary>
''' <param name="cmdType">Set the Transact-SQL statement or stored procedure to execute at the data source.</param>
''' <param name="cmdText">The text of the query.</param>
''' <param name="cmdParms">Set Array of Parameter</param>
''' <returns></returns>
''' <remarks>The first column of the first row in the result set, or a null reference if the result set is empty.</remarks>
Public Shared Function ExecuteScalar(ByVal cmdType As CommandType, ByVal cmdText As String, ByVal cmdParms As DbParameter()) As Object
Dim oProviderFactory As DbProviderFactory = DbProviderFactories.GetFactory(GetFactoryByProvider(_factory))
Dim Con As DbConnection = oProviderFactory.CreateConnection
Dim cmd As DbCommand = Con.CreateCommand
Dim trans As DbTransaction = Nothing
Try
Con.ConnectionString = ConnectionString
cmd.Connection = Con
cmd.CommandText = cmdText
cmd.Parameters.Clear()
cmd.CommandType = cmdType
If Not (IsNothing(cmdParms)) Then
Dim param As DbParameter
For Each param In cmdParms
cmd.Parameters.Add(param)
Next
End If
Con.Open()
trans = Con.BeginTransaction
cmd.Transaction = trans
Dim val As Object = cmd.ExecuteScalar()
cmd.Parameters.Clear()
trans.Commit()
Return val
Catch ex As DbException
trans.Rollback()
Throw New Exception("DB Exception " & ex.Message)
Catch exx As Exception
trans.Rollback()
Throw New Exception("ExecuteNonQuery Function", exx)
Finally
Con.Close()
cmd = Nothing
cmdParms = Nothing
End Try
End Function
''' <summary>
''' ExecuteTable Return DataTable
''' </summary>
''' <param name="cmdType">The command Type</param>
''' <param name="cmdText">The command text to execute</param>
''' <returns>DataTable</returns>
''' <remarks></remarks>
Public Shared Function ExecuteTable(ByVal cmdType As CommandType, ByVal cmdText As String) As DataTable
Return ExecuteTable(cmdType, cmdText, Nothing)
End Function
''' <summary>
''' ExecuteTable Return DataTable
''' </summary>
''' <param name="cmdType">The command Type</param>
''' <param name="cmdText">The command text to execute</param>
''' <param name="cmdParms">Array of Parameters</param>
''' <returns>DataTable</returns>
''' <remarks></remarks>
Public Shared Function ExecuteTable(ByVal cmdType As CommandType, ByVal cmdText As String, ByVal cmdParms As DbParameter()) As DataTable
Dim oProviderFactory As DbProviderFactory = DbProviderFactories.GetFactory(GetFactoryByProvider(_factory))
Dim oDataAdapter As DbDataAdapter
Dim Con As DbConnection = oProviderFactory.CreateConnection
Dim cmd As DbCommand
Try
Con.ConnectionString = ConnectionString
cmd = Con.CreateCommand
PrepareCommand(cmd, Con, cmdType, cmdText, cmdParms)
oDataAdapter = oProviderFactory.CreateDataAdapter
Dim oDataTable As New DataTable
oDataAdapter.SelectCommand = cmd
oDataAdapter.Fill(oDataTable)
cmd.Parameters.Clear()
Return oDataTable
Catch ex As DbException
Throw New Exception("DB Exception ", ex)
Catch exx As Exception
Throw New Exception("ExecuteTable Exception :", exx)
Finally
Con.Close()
cmd = Nothing
oDataAdapter = Nothing
End Try
End Function
''' <summary>
''' <para>Executes the <paramref name="commandText"/> as part of the given <paramref name="transaction" /> and returns the results in a new <see cref="DataSet"/>.</para>
''' </summary>
''' <param name="cmdType"></param>
''' <param name="cmdText">The command text to execute.</param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function ExecuteDataSet(ByVal cmdType As CommandType, ByVal cmdText As String) As DataSet
Return ExecuteDataSet(cmdType, cmdText, Nothing)
End Function
''' <summary>
''' <para>Executes the <paramref name="commandText"/> as part of the given <paramref name="transaction" /> and returns the results in a new <see cref="DataSet"/>.</para>
''' </summary>
''' <param name="cmdType">One of the <see cref="CommandType"/> values.</param>
''' <param name="cmdText">The command text to execute.</param>
''' <param name="cmdParms"></param>
''' <returns>DataSet</returns>
''' <remarks></remarks>
Public Shared Function ExecuteDataSet(ByVal cmdType As CommandType, ByVal cmdText As String, ByVal cmdParms As DbParameter()) As DataSet
Dim oProviderFactory As DbProviderFactory = DbProviderFactories.GetFactory(GetFactoryByProvider(_factory))
Dim con As DbConnection = oProviderFactory.CreateConnection
Dim oDataAdapter As DbDataAdapter = oProviderFactory.CreateDataAdapter
Dim cmd As DbCommand = con.CreateCommand
Try
con.ConnectionString = ConnectionString
cmd = con.CreateCommand
PrepareCommand(cmd, con, cmdType, cmdText, cmdParms)
oDataAdapter = oProviderFactory.CreateDataAdapter
Dim oDataSet As New DataSet
oDataAdapter.SelectCommand = cmd
oDataAdapter.Fill(oDataSet)
cmd.Parameters.Clear()
Return oDataSet
Catch ex As DbException
Throw New Exception("SQL Exception ", ex)
Catch exx As Exception
Throw New Exception("Execute DataSet", exx)
Finally
con.Close()
cmd = Nothing
oDataAdapter = Nothing
End Try
End Function
''' <summary>
''' Sends the System.Data.Common.DbCommand.CommandText to the System.Data.Common.DbCommand.Connection and builds a System.Data.Common.DbDataReader.
''' </summary>
''' <param name="conn">A System.Data.Common.DbConnection that represents the connection to an instance of DataSource.</param>
''' <param name="cmdType">Set the Transact-SQL statement or stored procedure to execute at the data source.</param>
''' <param name="cmdText">The text of the query.</param>
''' <returns>A System.Data.Common.DbDataReader object.</returns>
''' <remarks></remarks>
Public Shared Function ExecuteReader(ByRef conn As DbConnection, ByVal cmdType As CommandType, ByVal cmdText As String) As DbDataReader
Return ExecuteReader(conn, cmdType, cmdText, Nothing)
End Function
''' <summary>
''' Sends the System.Data.Common.DbCommand.CommandText to the System.Data.Common.DbCommand.Connection and builds a System.Data.Common.DbDataReader.
''' </summary>
''' <param name="conn">A System.Data.Common.DbConnection that represents the connection to an instance of DataSource.</param>
''' <param name="cmdType">Set the Transact-SQL statement or stored procedure to execute at the data source.</param>
''' <param name="cmdText">The text of the query.</param>
''' <param name="cmdParms">Set Array of Parameter</param>
''' <returns>A System.Data.Common.DbDataReader object.</returns>
''' <remarks></remarks>
Public Shared Function ExecuteReader(ByRef conn As DbConnection, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal cmdParms As DbParameter()) As DbDataReader
Dim oProviderFactory As DbProviderFactory = DbProviderFactories.GetFactory(GetFactoryByProvider(_factory))
conn = oProviderFactory.CreateConnection
Dim oDataAdapter As DbDataAdapter = oProviderFactory.CreateDataAdapter
Dim cmd As DbCommand = conn.CreateCommand
Dim rdr As DbDataReader
Try
PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms)
rdr = cmd.ExecuteReader()
cmd.Parameters.Clear()
If Not (IsNothing(cmdParms)) Then
Dim param As DbParameter
For Each param In cmdParms
cmd.Parameters.Add(param)
Next
End If
Return rdr
Catch ex As DbException
Throw New Exception("SQL Exception ", ex)
Catch exx As Exception
Throw New Exception("ExecuteReader", exx)
Finally
cmd = Nothing
End Try
End Function
''' <summary>
'''
''' </summary>
''' <param name="cmdType"></param>
''' <param name="cmdText"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function ExecuteRow(ByVal cmdType As CommandType, ByVal cmdText As String) As DataRow
Return ExecuteRow(cmdType, cmdText, Nothing)
End Function
''' <summary>
'''
''' </summary>
''' <param name="cmdType"></param>
''' <param name="cmdText"></param>
''' <param name="cmdParms"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function ExecuteRow(ByVal cmdType As CommandType, ByVal cmdText As String, ByVal cmdParms As DbParameter()) As DataRow
Dim oProviderFactory As DbProviderFactory = DbProviderFactories.GetFactory(GetFactoryByProvider(_factory))
Dim Con As DbConnection = oProviderFactory.CreateConnection
Con.ConnectionString = ConnectionString
Dim cmd As DbCommand = Con.CreateCommand
Dim oDataAdapter As DbDataAdapter = oProviderFactory.CreateDataAdapter
Dim oDataRow As DataRow = Nothing
Dim oDataTable As New DataTable
Try
PrepareCommand(cmd, Con, cmdType, cmdText, cmdParms)
oDataAdapter.SelectCommand = cmd
oDataAdapter.Fill(oDataTable)
cmd.Parameters.Clear()
If oDataTable.Rows.Count = 0 Then
Return Nothing
Else
Dim oRow As DataRow = oDataTable.Rows(0)
Return oRow
End If
Catch ex As DbException
Throw New Exception("DB Exception ", ex)
Catch exx As Exception
Throw New Exception("ExecuteRow", exx)
Finally
Con.Close()
oDataTable = Nothing
cmd = Nothing
oDataAdapter = Nothing
End Try
End Function
''' <summary>
''' Excute Adapter
''' </summary>
''' <param name="oTable"></param>
''' <param name="cmdText"></param>
''' <param name="lngMaxID"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function ExcuteAdapter(ByVal oTable As DataTable, ByVal cmdText As String, Optional ByRef lngMaxID As Long = 0) As Boolean
Dim oProviderFactory As DbProviderFactory = DbProviderFactories.GetFactory(GetFactoryByProvider(_factory))
Dim conn As DbConnection = oProviderFactory.CreateConnection
conn.ConnectionString = ConnectionString
Dim oSqlCmd As DbCommand = conn.CreateCommand
Dim oDataAdapter As DbDataAdapter = oProviderFactory.CreateDataAdapter
Dim oCmdBuilder As DbCommandBuilder = oProviderFactory.CreateCommandBuilder
Dim trans As DbTransaction = Nothing
Try
If Not conn.State = ConnectionState.Open Then
conn.Open()
End If
trans = conn.BeginTransaction
oSqlCmd.Transaction = trans
oSqlCmd.Connection = conn
oSqlCmd.CommandText = cmdText
oSqlCmd.CommandType = CommandType.Text
oDataAdapter.SelectCommand = oSqlCmd
oCmdBuilder.DataAdapter = oDataAdapter
oCmdBuilder.GetUpdateCommand()
oCmdBuilder.GetInsertCommand()
oCmdBuilder.GetDeleteCommand()
oDataAdapter.Update(oTable)
oDataAdapter.SelectCommand.CommandText = "SELECT @@IDENTITY"
trans.Commit()
' lngMaxID = CType(oDataAdapter.SelectCommand.ExecuteScalar(), Long)
Catch ex As DbException
trans.Rollback()
Throw New Exception("DB Exception ", ex)
Catch exx As Exception
trans.Rollback()
Throw New Exception("ExeculateAdapter", exx)
Finally
If conn.State = ConnectionState.Open Then conn.Close()
oSqlCmd = Nothing
oDataAdapter = Nothing
oCmdBuilder = Nothing
End Try
End Function
''' <summary>
''' Prepare Command
''' </summary>
''' <param name="cmd">Assigns a <paramref name="connection"/> to the <paramref name="command"/> and discovers parameters if needed.</param>
''' <param name="conn">The connection to assign to the command.</param>
''' <param name="cmdType">The command that contains the query to prepare.</param>
''' <param name="cmdText"></param>
''' <param name="cmdParms"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function PrepareCommand(ByRef cmd As DbCommand, ByRef conn As DbConnection, ByRef cmdType As CommandType, ByRef cmdText As String, ByRef cmdParms As DbParameter()) As Boolean
If Not conn.State = ConnectionState.Open Then
conn.Open()
End If
Try
cmd.Connection = conn
cmd.CommandText = cmdText
cmd.Parameters.Clear()
cmd.CommandType = cmdType
If Not (IsNothing(cmdParms)) Then
Dim param As DbParameter
For Each param In cmdParms
cmd.Parameters.Add(param)
Next
End If
Catch ex As DbException
Throw New Exception("DB Exception ", ex)
Catch exx As Exception
Throw New Exception("PrepareCommand : ", exx)
End Try
End Function
End Class
Public Enum Providers As Integer
Odbc = 1
OleDb = 2
SqlClient = 3
OracleClient = 4
MySql = 5
End Enum
|