The first processing of several objects of ado.net

ado| objects

Namespace Tonton.dal
'//----------------------------------------
'//Data link layer ' data connection and command line
'//----------------------------------------
'//Author: Zhang Shaoji (Tonton)
'//Time: August 29, 2005
'//ZIP code: tonton@yeah.net
'//home: http://www.tonton.cn
'//Blog: http://blog.tonton.cn
'//----------------------------------------

'//----------------------------------------
'//Example:
'//----------------------------------------
' Dim Conn as New Tonton.DAL.Connection
' Dim CMD as Tonton.DAL.Command
'
' Try
'//Open an Access database, or you can use the connection string and use the Open method.
'//conn.open ("Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & Server.MapPath ("Db.mdb"))
Of course, if you know it's using access, would you use the above troublesome method?
'//If it is SQL Server, you can use the Conn.opensqlserver method.
'　　　　'
' Conn.openaccess (Server.MapPath ("Db.mdb"))

'//Add record
' CMD = Conn.execute (Insert into [Item] ([Value]) VALUES (?) ")
' Cmd.addparam ("added content")
' Cmd.update ()

'//delete record
' CMD = Conn.execute ("Delete from [Item] WHERE id=?")
' Cmd.addparam (6) '//record number to be deleted
' Cmd.update ()

'//update record
' CMD = Conn.execute (Update [Item] Set [value]=? WHERE id=? ")
' Cmd.addparam ("New content")
' Cmd.addparam (5)
' Cmd.update ()

"//Read record
' CMD = Conn.execute ("select * from [Item]")
' If cmd.read Then
' Response.Write (CMD ("Value"))
' Else
' Response.Write ("OK")
' End If

' Catch ex as Exception
'//Error handling
' Response.Write (ex)
' Finally
"Close the connection
' Conn.close ()
' CMD = Nothing
' End Try
'//----------------------------------------
'//Example End
'//----------------------------------------

'//----------------------------------------
'//class definition Start
'//----------------------------------------

' Data connection Type enumeration
Public Enum ConnectionType as Integer
OLE DB = 1
SQL Server = 2
' Oracle = 3
' MYSQL = 4
End Enum

Connection string constructor class
public class Connectstringbuilder
public Shared Function jetoledb (ByVal DatabasePath as String , Optional ByVal PassWord As String = "") As String
If databasepath <> "" Then
Jetoledb = "Provi Der=microsoft.jet.oledb.4.0;data source= "& DatabasePath &";
If PassWord <> "Then
jetoledb &=" User id= ' admin "; password=; Jet oledb:database password= "& Password
End If
End-if
End Function

Public Shared Function SQLOLEDB (Optional ByVal HostName As String = localhost, Optional ByVal Catalog As String = "", Optional ByVal UserName As String = "sa", Optional ByVal PassWord As String = "") As String
SQLOLEDB = "Prov Ider=sqloledb.1; Persist security Info=false;data source= "& HostName &"; Password= "& Password &"; User id= "& UserName &";
If Catalog <> "" Then SQLOLEDB &= "Initial catalog=" & Catalog & ";"
End Function

Public Shared Function SqlClient (Optional ByVal HostName As String = "localhost", Optional ByVal As String = "", O Ptional ByVal UserName As String = "sa", Optional ByVal PassWord As String = "") As String
SqlClient = "Persist security info=false;data source=" & HostName &; Password= "& Password &"; User id= "& UserName &";
If Catalog <> "" Then SqlClient &= "Initial catalog=" & Catalog & ";"
End Function

Public Shared Function DSN (ByVal dsnname As String) as String
Return "dsn=" & DSNname
End Function
End Class

' Connection object class
Public Class Connection
Private _dbconn as IDbConnection
Private _connstr as String
Private _dbtype as ConnectionType = Connectiontype.oledb

Public Sub New (Optional ByVal connecttype as ConnectionType = connectiontype.oledb)
_dbtype = Connecttype
End Sub

Public Sub New (ByRef Connect as IDbConnection)
If TypeOf Connect is sqlclient.sqlconnection Then
_dbtype = Connectiontype.sqlserver
ElseIf TypeOf Connect is oledb.oledbconnection Then
_dbtype = Connectiontype.oledb
End If
End Sub

Public Sub New (ByVal connstring as String, Optional ByVal connecttype as ConnectionType = connectiontype.oledb)
_dbtype = Connecttype
me.connectstring = connstring
End Sub

' Set/return connection string, set to generate new instance of connection object
Public Property connectstring () as String
Get
Returns _connst R
End Get
Set (ByVal Value as String)
_connstr = Value
Try
_dbconn.clo SE ()
Catch ex as Exception
Finally
If Value <> "Then
Select Case"　　　　　　　　　　　　　　_dbtype
Case connectiontype.oledb
_dbconn = New oledb.oledbconnection (_connstr)
　　　　　　　　　　　　　　Case Connectiontype.sqlserver
_dbconn = New sqlclient.sqlconnection (_connstr)
Case Else
_dbconn = Nothing
End Select
Else
_dbconn = No Thing
End If
The end of Try
End Set
End Property

' Set/return connection type
Public Property Connecttype () as ConnectionType
Get
Return _dbtype
End Get
Set (ByVal Value as ConnectionType)
_dbtype = Value
me.connectstring = _connstr
End Set
End Property

Protected Overrides Sub Finalize ()
Try
_dbconn.close ()
_dbconn.dispose ()
Catch ex as Exception
Finally
Mybase.finalize ()
End Try
End Sub

' Returns the Connection object
Public ReadOnly Property Connection () as IDbConnection
Get
Return _dbconn
End Get
End Property

Open a database connection to specify the connection type (default is OLE DB)
Public Function open (Optional ByVal connstring as String = "", Optional ByVal C　　　　　　　　　　Onntype as ConnectionType = connectiontype.oledb) as Boolean
Try
If connstring <> "" Then
_connstr = connstring
_dbtype = conntype
End If
_dbconn.open ()
Return True
Catch ex as Exception
Throw ex
return False
End Try
End Function

Open an Access connection
public Function openaccess (ByVal dbpath As String, Optional ByVal PassWord As String = "") as Boolean
If dbpath <> "Then
Try
_connstr =" Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "& DBPath &";
If PassWord <> "Then
_connstr &=" User id= ' admin "; password=;　　　　　　　　　　Jet oledb:database password= "& Password
End If
_dbtype = Connectiontype.oledb
_dbconn = New oledb.oledbconnection (_connstr)
return True
Catch ex as Exception
Throw Ex
return False
End Try
End If
End Function

' Open a SQL Server connection
Public Function opensqlserver (Optional ByVal HostName As String = "localhost", Optional ByVal As String = "", Catalog Onal ByVal UserName As String = "sa", Optional ByVal PassWord As String = "") as Boolean
Try
_CONNSTR = "Persist security info=false;data source=" & HostName &; Password= "& Password &"; User id= "& UserName &";
If Catalog <> "" Then _connstr &= "Initial catalog=" & Catalog & ";"

_dbtype = Connectiontype.sqlserver
_dbconn = New sqlclient.sqlconnection (_connstr)
Return True
Catch ex as Exception
Throw EX
Return False
End Try
End Function

' Turn off data connections
Public Sub Close ()
Try
_dbconn.close ()
_dbconn = Nothing
Catch ex as Exception
End Try
End Sub

' Executes an SQL statement that generates or does not generate a Command object
Public Function Execute (ByVal Sql as String, Optional ByVal nonquery as Boolean = False) as Command
Execute = New Command (_dbconn, SQL)
If nonquery Then execute.update ()
End Function

' Generate a dataset and no longer need to manually generate the DataAdapter object.
Public ReadOnly Property DataSource (ByVal Sql As String, Optional ByVal Name As String = "", Optional ByVal Schema as Bool EAN = False) as DataSet
Get
Dim Ds as IDataAdapter
Select Case _dbtype
Case Connectiontype.oledb
Ds = New Oledb.oledbdataadapter (SQL, _dbconn)
Case Connectiontype.sqlserver
Ds = New Sqlclient.sqldataadapter (SQL, _dbconn)
End Select

If Name = "" Then
DataSource = New DataSet
Else
DataSource = New DataSet (Name)
End If

If Schema Then Ds.fillschema (DataSource, SchemaType.Source)
Ds.fill (DataSource)
End Get
End Property
End Class

' Command line Class
Public Class Command
Private _cmd as IDbCommand
Private _sql as String
Private _conn as IDbConnection
Private _rdr as IDataReader
Private _dbtype as ConnectionType

Public Sub New (ByRef Connect as IDbConnection)
_conn = Connect
If TypeOf Connect is oledb.oledbconnection Then
_dbtype = Connectiontype.oledb
ElseIf TypeOf Connect is sqlclient.sqlconnection Then
_dbtype = Connectiontype.sqlserver
End If
End Sub

Public Sub New (ByRef Connect as IDbConnection, ByVal Sql as String)
_conn = Connect
If TypeOf Connect is oledb.oledbconnection Then
_dbtype = Connectiontype.oledb
ElseIf TypeOf Connect is sqlclient.sqlconnection Then
_dbtype = Connectiontype.sqlserver
End If
Me.sql = SQL
End Sub

Protected Overrides Sub Finalize ()
Try
_cmd.dispose ()
_rdr. Dispose ()
_conn.dispose ()
Catch ex as Exception
Finally
Mybase.finalize ()
End Try
End Sub

' Sets or returns the Connection object
Public Property Connect () as IDbConnection
Get
Return _conn
End Get
Set (ByVal Value as IDbConnection)
_conn = Value
End Set
End Property

' Read or SET SQL statement
Public Property Sql () as String
Get
Return _sql
End Get
Set (ByVal Value as String)
_sql = Value
If _dbtype = Connectiontype.sqlserver Then
_cmd = New Sqlclient.sqlcommand (SQL, _conn)
Else
_cmd = New Oledb.oledbcommand (_sql, _conn)
End If
End Set
End Property

' reads the next record row and opens automatically if the recordset is not open.
Public Function Read (Optional ByVal Behavior as System.Data.CommandBehavior = Commandbehavior.default) as Boolean
If _rdr is nothing Then
Try
_rdr = _cmd.executereader (Behavior)
Return _RDR. Read
Catch ex as Exception
Return False
End Try
Return False
Else
Return _RDR. Read
End If
End Function

'//return number of fields
Public Function FieldCount () as Integer
Try
Return _RDR. FieldCount
Catch ex as Exception
return 0
End Try
End Function

'//execute command
Public Function Update () as Boolean
Try
If _conn.state <> ConnectionState.Open Then
_conn.open ()
End If
_cmd.executenonquery ()
Return True
Catch ex as Exception
Throw EX
Return False
End Try
End Function

'//Read field or add sql named parameter
Default Public Property Item (ByVal Name as String)
Get
Return _rdr (Name)
End Get
Set (ByVal Value)
AddParam (Value, Name)
End Set
End Property

'//Read field
Default Public ReadOnly Property Item (ByVal Index as Integer)
Get
Return _rdr (Index)
End Get
End Property

'//Add parameters
Public Function AddParam (ByRef Value as Object, Optional ByVal Name as String = "")
If _dbtype = Connectiontype.oledb Then
Return CType (_cmd, Oledb.oledbcommand). Parameters.Add (Name, Value)
Else
Return _cmd.parameters.add (New sqlclient.sqlparameter ("@" & Name, Value)
End If
End Function
End Class
End Namespace