| « | January 2020 | » | | 日 | 一 | 二 | 三 | 四 | 五 | 六 | | | | 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 | | |
|
|
 [Progam]A Concrete DataAccessor Object in VB.NET |
| 'Publish Date: Oct 29th, 2008'Author: david xie'Email: davidxiem@hotmail.comImports CommonImports Common.StringModuleImports System.CollectionsImports System.Collections.SpecializedImports System.DataImports System.Data.OleDbImports System.Text'A helper provide other layer several interfaces for easy accessing physical'Database include read, insert, update.Public Class DataAccessor Implements IDisposable 'Private connection As OleDbConnection Protected connection As OleDbConnection 'Remember that i have changed connection into protected Public Sub New() Try connection = New OleDbConnection(DATABASE_CONNECTIONSTRING) Catch ex As OleDbException Throw New Exception("unable to open connection to '" & DATABASE_CONNECTIONSTRING & "'", ex) End Try End Sub 'Reads data from a table 'table The table 'columns The columns to read, or null to read all ' the columns in the table 'selectionRow A set of filter columns and values used to subset the rows, or null to read all rows in the table. 'sortColumns The columns to sort, or null to read without sorting 'return The result dataset. Public Overloads Function Read(ByVal table As String, ByVal columns As StringCollection, ByVal selectionRow As IList, ByVal sortColumns As StringCollection) As DataTable Dim query As String = BuildSelectCmd(table, columns, selectionRow, sortColumns) Return Read(query) End Function Public Overloads Function ReadToReader(ByVal table As String, ByVal columns As StringCollection, ByVal selectionRow As IList, ByVal sortColumns As StringCollection) As IDataReader Dim query As String = BuildSelectCmd(table, columns, selectionRow, sortColumns) Return ExecuteReader(query) End Function ''' <summary> ''' ''' </summary> ''' <param name="commandtext"></param> ''' <returns></returns> ''' <remarks>This function will manage own connection.</remarks> Public Overloads Function Read(ByVal commandtext As String) As DataTable Dim datable As New DataTable 'Execute the command to pop data from database Dim dataadp As OleDbDataAdapter = New OleDbDataAdapter(commandtext, connection) dataadp.Fill(datable) Return datable End Function ''' <summary> ''' Execute a sql command to get a datareader object. ''' </summary> ''' <param name="commandtext">SQL command to be executed</param> ''' <returns>Datareader responds the command</returns> ''' <remarks></remarks> Public Function ExecuteReader(ByVal commandtext As String) As IDataReader Dim cmd As New OleDbCommand(commandtext, connection) connection.Open() Return cmd.ExecuteReader() End Function 'Inserts data into a table '@param table The table '@param rows The rows to insert Public Function Insert(ByVal table As String, ByVal rows As IDictionary) As Integer Dim buffer As New StringBuilder Dim firstcolumn As Boolean = True Dim affcteds As Integer Dim cmd As OleDbCommand Try cmd = New OleDbCommand() 'Generate the SQL INSERT startement based on 'the caller's input buffer.Append("INSERT INTO ") buffer.Append(table) 'List the column names buffer.Append(" (") For Each row As DictionaryEntry In rows If Not firstcolumn Then buffer.Append(", ") Else firstcolumn = False End If buffer.Append(getunformatname(row.Key.ToString())) Next 'List the column values. buffer.Append(") VALUES (") firstcolumn = True For Each row As DictionaryEntry In rows If Not firstcolumn Then buffer.Append(", ") Else firstcolumn = False End If ''TODO: Add a generatlitertureValues function to ''check the key if followby a ? mark and convert to associate value ''like that ''buffer.append(generatelitertureValue(dictentry)) 'Dim obj As Object = row.Value 'If Not IsNumeric(obj) Then ' If IsArray(obj) Then ' buffer.Append("?") ' AddPhoto(cmd, "", obj) ' Else ' buffer.Append("'" & obj.ToString() & "'") ' End If 'Else ' buffer.Append(obj.ToString()) 'End If buffer.Append(GenerateLiteralValues(row, cmd)) Next buffer.Append(")") 'Execute the statement to insert data cmd.CommandText = buffer.ToString() cmd.Connection = connection If connection.State = ConnectionState.Closed Then connection.Open() End If affcteds = cmd.ExecuteNonQuery() cmd.CommandText = "SELECT @@identity" Dim result As Integer = CInt(cmd.ExecuteScalar()) Return result connection.Close() Catch ex As OleDbException Throw New Exception("unable to insert into table" & table, ex) End Try End Function Public Function GetSingleNum(ByVal commandtext As String) As Integer Dim cmd As New OleDbCommand(commandtext, connection) If connection.State = ConnectionState.Closed Then connection.Open() End If Dim result As Integer = CInt(cmd.ExecuteScalar()) connection.Close() Return result End Function ''' <summary> ''' Updates data in a table ''' </summary> ''' <param name="table">The table</param> ''' <param name="selectionrow">A set of filter columns and values used to subset the rows, ''' or null to update all the rows in the table</param> ''' <param name="updaterow">A set of update columns and values.</param> ''' <remarks></remarks> Public Sub Update(ByVal table As String, ByVal selectionrow As IList, ByVal updaterow As IDictionary) Try Dim cmd As OleDbCommand = New OleDbCommand() 'Generate the SQL UPDATE statement based on the 'caller's input Dim buffer As New StringBuilder Dim firstcolumn As Boolean buffer.Append("UPDATE ") buffer.Append(table) 'Generate the SET clause buffer.Append(" SET ") firstcolumn = True For Each row As DictionaryEntry In updaterow If Not firstcolumn Then buffer.Append(", ") Else firstcolumn = False End If buffer.Append(getunformatname(row.Key.ToString())) buffer.Append(" = ") 'Dim obj As Object 'obj = row.Value 'If Not IsNumeric(obj) Then ' buffer.Append("'" & obj.ToString() & "'") 'Else ' buffer.Append(obj.ToString()) 'End If buffer.Append(GenerateLiteralValues(row, cmd)) Next 'Generate the WHERE clause if the 'caller specified a selection row. If IsNothing(selectionrow) = False Then buffer.Append(generateWhereClause(selectionrow)) End If 'Execute the command cmd.CommandText = buffer.ToString() cmd.Connection = connection If connection.State = ConnectionState.Closed Then connection.Open() End If cmd.ExecuteNonQuery() Catch ex As OleDbException Throw New Exception("Unable to update table" & table, ex) End Try End Sub Public Sub Delete(ByVal table As String, ByVal selectionRow As IList) Try Dim cmd As OleDbCommand = New OleDbCommand() 'Generate the SQL DELET statement based on the 'caller's input Dim buffer As New StringBuilder buffer.Append("DELETE FROM ") buffer.Append(table) If IsNothing(selectionRow) = False Then buffer.Append(generateWhereClause(selectionRow)) End If 'Execute the command cmd.CommandText = buffer.ToString() cmd.Connection = connection If connection.State = ConnectionState.Closed Then connection.Open() End If cmd.ExecuteNonQuery() Catch ex As Exception Throw New Exception("Unable to delete from table " + table, ex) End Try End Sub Public Function GenerateCmdPara(ByVal name As String) As String Return name + "?" End Function Private Function getunformatname(ByVal value As String) As String If value.EndsWith("?") = True Then Return value.Remove(value.Length - 1) Else Return value End If End Function Private Function GenerateLiteralValues(ByVal entry As DictionaryEntry, ByVal cmd As OleDbCommand) As String Dim key As String = entry.Key.ToString() Dim obj As Object = entry.Value If key.EndsWith("?") = True Then 'This block is for data that can't not directly insert or update. 'To insert or update it needs a command parameter and assign the obj to it. cmd.Parameters.Add(New OleDbParameter()) cmd.Parameters(cmd.Parameters.Count - 1).Value = obj 'Don't put the obj as a string instead put a question mark into command text. Return "?" Else Dim buffer As StringBuilder = New StringBuilder() If Not TypeOf obj Is Integer Then buffer.Append("'") End If buffer.Append(obj.ToString()) If Not TypeOf obj Is Integer Then buffer.Append("'") End If Return buffer.ToString() End If End Function Private disposedValue As Boolean = False ' To detect redundant calls ' IDisposable Protected Overridable Sub Dispose(ByVal disposing As Boolean) If Not Me.disposedValue Then If disposing Then If connection IsNot Nothing Then If connection.State = ConnectionState.Open Then connection.Close() End If connection.Dispose() End If End If ' TODO: free shared unmanaged resources End If Me.disposedValue = True End Sub#Region " IDisposable Support " ' This code added by Visual Basic to correctly implement the disposable pattern. Public Sub Dispose() Implements IDisposable.Dispose ' Do not change this code. Put cleanup code in Dispose(ByVal disposing As Boolean) above. Dispose(True) GC.SuppressFinalize(Me) End Sub#End Region 'Generate a sql where clause based on a selection row. Private Function generateWhereClause(ByVal selectionRow As IList) As String ' Dim buffer As New StringBuilder buffer.Append(" WHERE ") Dim firstcolumn As Boolean = True For Each i As Array In selectionRow If Not firstcolumn Then buffer.Append(" AND ") Else firstcolumn = False End If If i.Length > 1 Then buffer.Append(" (") End If Dim colname As Boolean = True Dim firstexpression As Boolean = True Dim name As String = "" For Each obj As Object In i If colname Then name = CType(obj, String) colname = False Else If Not firstexpression Then buffer.Append(" OR ") Else firstexpression = False End If If Not TypeOf obj Is Integer Then buffer.Append(name & "='" & obj.ToString() & "'") Else buffer.Append(name & "=" & obj.ToString()) End If End If Next If i.Length > 1 Then buffer.Append(" )") End If Next Return buffer.ToString() End Function Private Function BuildSelectCmd(ByVal table As String, ByVal columns As StringCollection, ByVal selectionRow As IList, ByVal sortColumns As StringCollection) As String Dim buffer As New StringBuilder buffer.Append(" SELECT ") 'List the columns if the caller sepcified any. If (columns IsNot Nothing) Then Dim i As Integer = 0 For Each col As String In columns 'Test if it is not only one column If i > 0 Then buffer.Append(", ") End If i = i + 1 buffer.Append(col) Next Else buffer.Append(" * ") End If 'Include the resolved qualifed table name. buffer.Append(" FROM ") buffer.Append(table) 'Generate the WHERE clause if the caller specified a selection row If (selectionRow IsNot Nothing) Then buffer.Append(generateWhereClause(selectionRow)) End If 'Generate the ORDER By clause if the caller specified sort columns If (sortColumns IsNot Nothing) Then buffer.Append(" ORDER BY ") Dim i As Integer For Each col As String In sortColumns If i > 0 Then buffer.Append(", ") End If i = i + 1 buffer.Append(col) 'buffer.Append(" ASC ") Next End If Return buffer.ToString() End FunctionEnd Class | |
|
|
|
|
| 公告 |
Anybody can contact me through Email:
or through instant message messageing
 davidxiem@hotmail.com
Thanks Nexodyne for email icon generation. |
| 统计 |
blog名称:
日志总数:174
评论数量:98
留言数量:-1
访问次数:493808
建立时间:2007年7月20日 | |
