本站首页    管理页面    写新日志    退出


«January 2020»
1234
567891011
12131415161718
19202122232425
262728293031

最新日志

Suprisingly Good Mar
Software Metric Issu
又过了一天
学做游戏
Software engineering
MICROSOFT新的DSL语言
介绍SME和研究 心得
A summary of SME
tips to improve engl
davidxiem

最近的评论

回复:学做游戏
回复:安装Nginx(负载均衡器)
回复:介绍SME和研究 心得
回复:目前想看/在看的电影和TV
回复:有点担心这次的examinatio
回复:观后感《非诚勿扰》
回复:观后感《非诚勿扰》
回复:生财有道
回复:总算是忙完了啦,要想想有哪些事情要
回复:Summary of this m

连接





[Progam]A Concrete DataAccessor Object in VB.NET
davidxiem 发表于 2008-10-29 8:03:12

'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

阅读全文(2082) | 回复(0) | 编辑 | 精华


发表评论:
昵称:
密码:
主页:
标题:
验证码:  (不区分大小写,请仔细填写,输错需重写评论内容!)



公告

 Anybody can contact me through Email:



or through instant message messageing

MSN davidxiem@hotmail.com

Thanks Nexodyne  for email icon generation.

专题

首页(174)
Progam(7)
english learning(41)
个人日志(72)
Forever QuakeIII(4)
Software Process(2)
lgp(13)
Movie and TV(12)
DataBase(0)
ILC(5)

留言

签写新留言

hihi

统计

blog名称:
日志总数:174
评论数量:98
留言数量:-1
访问次数:493808
建立时间:2007年7月20日

 

 

 


站点首页 | 联系我们 | 博客注册 | 博客登陆

Sponsored By W3CHINA
W3CHINA Blog 0.8 Processed in 0.047 second(s), page refreshed 144346654 times.
《全国人大常委会关于维护互联网安全的决定》  《计算机信息网络国际联网安全保护管理办法》
苏ICP备05006046号