`
wsql
  • 浏览: 11716336 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
文章分类
社区版块
存档分类
最新评论

VB.net版本的数据库访问类DataBaseAccess

 
阅读更多
在开发一个VB.net的项目时,曾经整理出了一个DataBaseAccess的访问类,现在将该类分享下:
Imports System
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient


Namespace SqlDataProvider

Public Class DataBaseAccess

#Region "Local Property Declaration"

Dim _connectionString As String

#End Region

#Region " Constructor "

''' <summary>
''' Initializes a new instance of the ADO.SqlDatabase class.
''' </summary>
''' <param name="connectionString">The connection used to open the SQL Server database.</param>
Public Sub New(ByVal connectionString As String)
_connectionString = connectionString
End Sub

#End Region

#Region " Public Properties "

''' <summary>
''' Gets or sets the string used to open a SQL Server database.
''' </summary>
''' <returns>The connection string that includes the source database name, and other parameters needed to establish the initial connection.</returns>
Public Property ConnectionString() As String
Get
Return _connectionString
End Get
Set(ByVal value As String)
_connectionString = value
End Set
End Property

#End Region

#Region " Private Methods "

Private Sub AssignParameters(ByVal cmd As SqlCommand, ByVal cmdParameters() As SqlParameter)
If (cmdParameters Is Nothing) Then Exit Sub
For Each p As SqlParameter In cmdParameters
cmd.Parameters.Add(p)
Next
End Sub

Private Sub AssignParameters(ByVal cmd As SqlCommand, ByVal parameterValues() As Object)
If Not (cmd.Parameters.Count - 1 = parameterValues.Length) Then Throw New ApplicationException("Stored procedure's parameters and parameter values does not match.")
Dim i As Integer
For Each param As SqlParameter In cmd.Parameters
If Not (param.Direction = ParameterDirection.Output) AndAlso Not (param.Direction = ParameterDirection.ReturnValue) Then
param.Value = parameterValues(i)
i += 1
End If
Next
End Sub

#End Region

#Region " ExecuteNonQuery "

''' <summary>
''' Executes a Transact-SQL statement against the connection and returns the number of rows affected.
''' </summary>
''' <param name="cmd">The Transact-SQL statement or stored procedure to execute at the data source.</param>
''' <param name="cmdType">A value indicating how the System.Data.SqlClient.SqlCommand.CommandText property is to be interpreted.</param>
''' <param name="parameters">The parameters of the Transact-SQL statement or stored procedure.</param>
''' <returns>The number of rows affected.</returns>
Public Function ExecuteNonQuery(ByVal cmd As String, ByVal cmdType As CommandType, Optional ByVal parameters() As SqlParameter = Nothing) As Integer
Dim connection As SqlConnection = Nothing
Dim transaction As SqlTransaction = Nothing
Dim command As SqlCommand = Nothing
Dim res As Integer = -1
Try
connection = New SqlConnection(_connectionString)
command = New SqlCommand(cmd, connection)
command.CommandType = cmdType
Me.AssignParameters(command, parameters)
connection.Open()
transaction = connection.BeginTransaction()
command.Transaction = transaction
res = command.ExecuteNonQuery()
transaction.Commit()
Catch ex As Exception
If Not (transaction Is Nothing) Then
transaction.Rollback()
End If
Throw New Exception(ex.Message, ex.InnerException)
Finally
If Not (connection Is Nothing) AndAlso (connection.State = ConnectionState.Open) Then connection.Close()
If Not (command Is Nothing) Then command.Dispose()
If Not (transaction Is Nothing) Then transaction.Dispose()
End Try
Return res
End Function

''' <summary>
''' Executes a Transact-SQL statement against the connection and returns the number of rows affected.
''' </summary>
''' <param name="spname">The stored procedure to execute at the data source.</param>
''' <param name="returnValue">The returned value from stored procedure.</param>
''' <param name="parameterValues">The parameter values of the stored procedure.</param>
''' <returns>The number of rows affected.</returns>
Public Function ExecuteNonQuery(ByVal spname As String, ByRef returnValue As Integer, ByVal ParamArray parameterValues() As Object) As Integer
Dim connection As SqlConnection = Nothing
Dim transaction As SqlTransaction = Nothing
Dim command As SqlCommand = Nothing
Dim res As Integer = -1
Try
connection = New SqlConnection(_connectionString)
command = New SqlCommand(spname, connection)
command.CommandType = CommandType.StoredProcedure
connection.Open()
SqlCommandBuilder.DeriveParameters(command)
Me.AssignParameters(command, parameterValues)
transaction = connection.BeginTransaction()
command.Transaction = transaction
res = command.ExecuteNonQuery()
returnValue = command.Parameters(0).Value
transaction.Commit()
Catch ex As Exception
If Not (transaction Is Nothing) Then
transaction.Rollback()
End If
Throw New Exception(ex.Message, ex.InnerException)
Finally
If Not (connection Is Nothing) AndAlso (connection.State = ConnectionState.Open) Then connection.Close()
If Not (command Is Nothing) Then command.Dispose()
If Not (transaction Is Nothing) Then transaction.Dispose()
End Try
Return res
End Function

#End Region

#Region " ExecuteScalar "

''' <summary>
''' Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.
''' </summary>
''' <param name="cmd">The Transact-SQL statement or stored procedure to execute at the data source.</param>
''' <param name="cmdType">A value indicating how the System.Data.SqlClient.SqlCommand.CommandText property is to be interpreted.</param>
''' <param name="parameters">The parameters of the Transact-SQL statement or stored procedure.</param>
''' <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
Public Function ExecuteScalar(ByVal cmd As String, ByVal cmdType As CommandType, Optional ByVal parameters() As SqlParameter = Nothing) As Object
Dim connection As SqlConnection = Nothing
Dim transaction As SqlTransaction = Nothing
Dim command As SqlCommand = Nothing
Dim res As Object = Nothing
Try
connection = New SqlConnection(_connectionString)
command = New SqlCommand(cmd, connection)
command.CommandType = cmdType
Me.AssignParameters(command, parameters)
connection.Open()
transaction = connection.BeginTransaction()
command.Transaction = transaction
res = command.ExecuteScalar()
transaction.Commit()
Catch ex As Exception
If Not (transaction Is Nothing) Then
transaction.Rollback()
End If
Throw New Exception(ex.Message, ex.InnerException)
Finally
If Not (connection Is Nothing) AndAlso (connection.State = ConnectionState.Open) Then connection.Close()
If Not (command Is Nothing) Then command.Dispose()
If Not (transaction Is Nothing) Then transaction.Dispose()
End Try
Return res
End Function

''' <summary>
''' Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.
''' </summary>
''' <param name="spname">The stored procedure to execute at the data source.</param>
''' <param name="returnValue">The returned value from stored procedure.</param>
''' <param name="parameterValues">The parameter values of the stored procedure.</param>
''' <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
Public Function ExecuteScalar(ByVal spname As String, ByRef returnValue As Integer, ByVal ParamArray parameterValues() As Object) As Object
Dim connection As SqlConnection = Nothing
Dim transaction As SqlTransaction = Nothing
Dim command As SqlCommand = Nothing
Dim res As Object = Nothing
Try
connection = New SqlConnection(_connectionString)
command = New SqlCommand(spname, connection)
command.CommandType = CommandType.StoredProcedure
connection.Open()
SqlCommandBuilder.DeriveParameters(command)
Me.AssignParameters(command, parameterValues)
transaction = connection.BeginTransaction()
command.Transaction = transaction
res = command.ExecuteScalar()
returnValue = command.Parameters(0).Value
transaction.Commit()
Catch ex As Exception
If Not (transaction Is Nothing) Then
transaction.Rollback()
End If
Throw New Exception(ex.Message, ex.InnerException)
Finally
If Not (connection Is Nothing) AndAlso (connection.State = ConnectionState.Open) Then connection.Close()
If Not (command Is Nothing) Then command.Dispose()
If Not (transaction Is Nothing) Then transaction.Dispose()
End Try
Return res
End Function

#End Region

#Region " ExecuteReader "

''' <summary>
''' Sends the System.Data.SqlClient.SqlCommand.CommandText to the System.Data.SqlClient.SqlCommand.Connection, and builds a System.Data.SqlClient.SqlDataReader using one of the System.Data.CommandBehavior values.
''' </summary>
''' <param name="cmd">The Transact-SQL statement or stored procedure to execute at the data source.</param>
''' <param name="cmdType">A value indicating how the System.Data.SqlClient.SqlCommand.CommandText property is to be interpreted.</param>
''' <param name="parameters">The parameters of the Transact-SQL statement or stored procedure.</param>
''' <returns>A System.Data.SqlClient.SqlDataReader object.</returns>
Public Function ExecuteReader(ByVal cmd As String, ByVal cmdType As CommandType, Optional ByVal parameters() As SqlParameter = Nothing) As IDataReader
Dim connection As SqlConnection = Nothing
Dim command As SqlCommand = Nothing
Dim res As SqlDataReader = Nothing
Try
connection = New SqlConnection(_connectionString)
command = New SqlCommand(cmd, connection)
command.CommandType = cmdType
Me.AssignParameters(command, parameters)
connection.Open()
res = command.ExecuteReader(CommandBehavior.CloseConnection)
Catch ex As Exception
Throw New Exception(ex.Message, ex.InnerException)
End Try
Return CType(res, IDataReader)
End Function

''' <summary>
''' Sends the System.Data.SqlClient.SqlCommand.CommandText to the System.Data.SqlClient.SqlCommand.Connection, and builds a System.Data.SqlClient.SqlDataReader using one of the System.Data.CommandBehavior values.
''' </summary>
''' <param name="spname">The stored procedure to execute at the data source.</param>
''' <param name="returnValue">The returned value from stored procedure.</param>
''' <param name="parameterValues">The parameter values of the stored procedure.</param>
''' <returns>A System.Data.SqlClient.SqlDataReader object.</returns>
Public Function ExecuteReader(ByVal spname As String, ByRef returnValue As Integer, ByVal ParamArray parameterValues() As Object) As IDataReader
Dim connection As SqlConnection = Nothing
Dim command As SqlCommand = Nothing
Dim res As SqlDataReader = Nothing
Try
connection = New SqlConnection(ConnectionString)
command = New SqlCommand(spname, connection)
command.CommandType = CommandType.StoredProcedure
connection.Open()
SqlCommandBuilder.DeriveParameters(command)
Me.AssignParameters(command, parameterValues)
res = command.ExecuteReader(CommandBehavior.CloseConnection)
returnValue = command.Parameters(0).Value
Catch ex As Exception
Throw New Exception(ex.Message, ex.InnerException)
End Try
Return CType(res, IDataReader)
End Function

#End Region

#Region " FillDataset "

''' <summary>
''' Adds or refreshes rows in the System.Data.DataSet to match those in the data source using the System.Data.DataSet name, and creates a System.Data.DataTable named "Table."
''' </summary>
''' <param name="cmd">The Transact-SQL statement or stored procedure to execute at the data source.</param>
''' <param name="cmdType">A value indicating how the System.Data.SqlClient.SqlCommand.CommandText property is to be interpreted.</param>
''' <param name="parameters">The parameters of the Transact-SQL statement or stored procedure.</param>
''' <returns>A System.Data.Dataset object.</returns>
Public Function FillDataset(ByVal cmd As String, ByVal cmdType As CommandType, Optional ByVal parameters() As SqlParameter = Nothing) As DataSet
Dim connection As SqlConnection = Nothing
Dim command As SqlCommand = Nothing
Dim sqlda As SqlDataAdapter = Nothing
Dim res As New DataSet
Try
connection = New SqlConnection(_connectionString)
command = New SqlCommand(cmd, connection)
command.CommandType = cmdType
AssignParameters(command, parameters)
sqlda = New SqlDataAdapter(command)
sqlda.Fill(res)
Catch ex As Exception
Throw New Exception(ex.Message, ex.InnerException)
Finally
If Not (connection Is Nothing) Then connection.Dispose()
If Not (command Is Nothing) Then command.Dispose()
If Not (sqlda Is Nothing) Then sqlda.Dispose()
End Try
Return res
End Function

#End Region

#Region " ExecuteDataset "

''' <summary>
''' Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the System.Data.DataSet with the specified System.Data.DataTable name.
''' </summary>
''' <param name="insertCmd">A command used to insert new records into the data source.</param>
''' <param name="updateCmd">A command used to update records in the data source.</param>
''' <param name="deleteCmd">A command for deleting records from the data set.</param>
''' <param name="ds">The System.Data.DataSet to use to update the data source. </param>
''' <param name="srcTable">The name of the source table to use for table mapping.</param>
''' <returns>The number of rows successfully updated from the System.Data.DataSet.</returns>
Public Function ExecuteDataset(ByVal insertCmd As SqlCommand, ByVal updateCmd As SqlCommand, ByVal deleteCmd As SqlCommand, ByVal ds As DataSet, ByVal srcTable As String) As Integer
Dim connection As SqlConnection = Nothing
Dim sqlda As SqlDataAdapter = Nothing
Dim res As Integer = 0
Try
connection = New SqlConnection(_connectionString)
sqlda = New SqlDataAdapter
If Not (insertCmd Is Nothing) Then insertCmd.Connection = connection : sqlda.InsertCommand = insertCmd
If Not (updateCmd Is Nothing) Then updateCmd.Connection = connection : sqlda.UpdateCommand = updateCmd
If Not (deleteCmd Is Nothing) Then deleteCmd.Connection = connection : sqlda.DeleteCommand = deleteCmd
res = sqlda.Update(ds, srcTable)
Catch ex As Exception
Throw New Exception(ex.Message, ex.InnerException)
Finally
If Not (connection Is Nothing) Then connection.Dispose()
If Not (insertCmd Is Nothing) Then insertCmd.Dispose()
If Not (updateCmd Is Nothing) Then updateCmd.Dispose()
If Not (deleteCmd Is Nothing) Then deleteCmd.Dispose()
If Not (sqlda Is Nothing) Then sqlda.Dispose()
End Try
Return res
End Function

#End Region

#Region " ExecuteScript "

''' <summary>
''' Executes a SQL query file against the connection.
''' </summary>
''' <param name="filename">SQL query file name.</param>
''' <param name="parameters">The parameters of the SQL query file.</param>
Public Sub ExecuteScript(ByVal filename As String, Optional ByVal parameters() As SqlParameter = Nothing)
Dim fStream As FileStream = Nothing
Dim sReader As StreamReader = Nothing
Dim connection As SqlConnection = Nothing
Dim command As SqlCommand = Nothing
Try
fStream = New FileStream(filename, FileMode.Open, FileAccess.Read)
sReader = New StreamReader(fStream)
connection = New SqlConnection(ConnectionString)
command = connection.CreateCommand()
connection.Open()
While (Not sReader.EndOfStream)
Dim sb As New StringBuilder
While (Not sReader.EndOfStream)
Dim s As String = sReader.ReadLine
If (Not String.IsNullOrEmpty(s)) AndAlso (s.ToUpper.Trim = "GO") Then
Exit While
End If
sb.AppendLine(s)
End While
command.CommandText = sb.ToString
command.CommandType = CommandType.Text
AssignParameters(command, parameters)
command.ExecuteNonQuery()
End While
Catch ex As Exception
Throw New Exception(ex.Message, ex.InnerException)
Finally
If (Not IsNothing(connection)) AndAlso (connection.State = ConnectionState.Open) Then connection.Close()
If (Not IsNothing(command)) Then command.Dispose()
If (Not IsNothing(sReader)) Then sReader.Close()
If (Not IsNothing(fStream)) Then fStream.Close()
End Try
End Sub

#End Region


End Class

End Namespace

分享到:
评论

相关推荐

    图书管理系统源代码

    MessageBox.Show("连接不到数据库LibraryMis,请在“数据库访问设置窗体中对数据库访问进行正确的设置”" + ",取消登录后重新启动图书馆管理系统!","警告",MessageBoxButtons.OK,MessageBoxIcon.Warning ); //...

    DatabaseAccess:一种设计为易于重用的系统,可为统一的外观提供简单的接口,并具有简单的接口,可连接多种基础数据库实现范例(noSQL,Relational)和技术

    数据库访问 一种旨在易于重用的系统,可为统一的外观提供简单的界面,并可以将其集成到多个基础数据库实现范例(noSQL,Relational)和技术中。

    JAVA WEB框架,java网站一个模块只用写一个文件

    |___Hyberbin.java 进一步封装了数据库的操作,用户不直接对数据库操作,只要给出实体POJO类,数据可以自动查询、修改、删除、插入 servlet 用户自己的包,完成相应模块的功能。 |___Szdw.java POJO类,对应数据库...

    PassVault:密码记忆申请

    将其命名为有意义的示例,例如:LoginRequest,PasswordList,DatabaseAccess等(不带下划线“ _”,不带连字符“-”,不带空格“”) 布局文件(.xml)的各自活动应具有相同的名称,且大小写和单词之间不能使用...

    asp代码ASP基于WEB个人博客网页设计(源代码+论文+答辩)

    asp代码ASP基于WEB个人博客网页设计(源代码+论文+答辩)本资源系百度网盘分享地址

    三菱PLC例程源码打包机

    三菱PLC例程源码打包机本资源系百度网盘分享地址

    asp代码ASP基于USBKEY文件加密工具-USBkey管理系统(源代码+论文)

    asp代码ASP基于USB KEY文件加密工具——USB key管理系统(源代码+论文)本资源系百度网盘分享地址

    Android开发编码规范

    该文档是《阿里巴巴Java开发手册》的规约条目的延伸信息; 其中包含了对内容的适当扩展和解释。它提供了编码和实现方式的正例,以及需要提防的雷区和错误案例的反例。该文档面向Android开发所有成员,旨在规范化代码风格和编程习惯,并提出了针对软件调优的建议。其中包括Android资源文件命名与使用、Android基本组件、UI与布局、进程、线程与消息等方面的内容

    网络安全-逆向学习路线

    红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线红队蓝军逆向学习路线

    which-2.20.tar.gz

    算、文件操作、数据分析和网络编程等。Python社区提供了大量的第三方库,如NumPy、Pandas和Requests,极大地丰富了Python的应用领域,从数据科学到Web开发。Python库的丰富性是Python成为最受欢迎的编程语言之一的关键原因之一。这些库不仅为初学者提供了快速入门的途径,而且为经验丰富的开发者提供了强大的工具,以高效率、高质量地完成复杂任务。例如,Matplotlib和Seaborn库在数据可视化领域内非常受欢迎,它们提供了广泛的工具和技术,可以创建高度定制化的图表和图形,帮助数据科学家和分析师在数据探索和结果展示中更有效地传达信息。

    asp代码ASP基于bs在线花店系统设计(源代码+论文)

    asp代码ASP基于bs在线花店系统设计(源代码+论文)本资源系百度网盘分享地址

    基于深度学习的轨道交通客流实时分析预测系统 第二版(前端).zip

    人工智能毕业设计&课程设计

    解决端口占用netstat -ano

    解决端口占用netstat -ano

    tensorflow_onmttok_ops-0.1.1-cp35-cp35m-manylinux2014_x86_64.whl

    算、文件操作、数据分析和网络编程等。Python社区提供了大量的第三方库,如NumPy、Pandas和Requests,极大地丰富了Python的应用领域,从数据科学到Web开发。Python库的丰富性是Python成为最受欢迎的编程语言之一的关键原因之一。这些库不仅为初学者提供了快速入门的途径,而且为经验丰富的开发者提供了强大的工具,以高效率、高质量地完成复杂任务。例如,Matplotlib和Seaborn库在数据可视化领域内非常受欢迎,它们提供了广泛的工具和技术,可以创建高度定制化的图表和图形,帮助数据科学家和分析师在数据探索和结果展示中更有效地传达信息。

    基于深度学习框架的图像识别:手势识别。使用到:CaffeTensorFlowCNNopenCVcpppythondesign

    人工智能-深度学习-tensorflow

    Andorid项目源码 驴友社交系统 客户端+ 服务器端 (源码)

    Andorid项目源码 驴友社交系统 客户端+ 服务器端 (源码) Andorid项目源码 驴友社交系统 客户端+ 服务器端 (源码) Andorid项目源码 驴友社交系统 客户端+ 服务器端 (源码) Andorid项目源码 驴友社交系统 客户端+ 服务器端 (源码) Andorid项目源码 驴友社交系统 客户端+ 服务器端 (源码) Andorid项目源码 驴友社交系统 客户端+ 服务器端 (源码) Andorid项目源码 驴友社交系统 客户端+ 服务器端 (源码) Andorid项目源码 驴友社交系统 客户端+ 服务器端 (源码) Andorid项目源码 驴友社交系统 客户端+ 服务器端 (源码) Andorid项目源码 驴友社交系统 客户端+ 服务器端 (源码) Andorid项目源码 驴友社交系统 客户端+ 服务器端 (源码) Andorid项目源码 驴友社交系统 客户端+ 服务器端 (源码) Andorid项目源码 驴友社交系统 客户端+ 服务器端 (源码) Andorid项目源码 驴友社交系统 客户端+ 服务器端 (源码) Andorid项目

    JAVA的GUI实现可视化学生管理系统

    JAVA的GUI实现可视化学生管理系统

    WeRoBot-1.5.0-py3-none-any.whl

    Python库是一组预先编写的代码模块,旨在帮助开发者实现特定的编程任务,无需从零开始编写代码。这些库可以包括各种功能,如数学运算、文件操作、数据分析和网络编程等。Python社区提供了大量的第三方库,如NumPy、Pandas和Requests,极大地丰富了Python的应用领域,从数据科学到Web开发。Python库的丰富性是Python成为最受欢迎的编程语言之一的关键原因之一。这些库不仅为初学者提供了快速入门的途径,而且为经验丰富的开发者提供了强大的工具,以高效率、高质量地完成复杂任务。例如,Matplotlib和Seaborn库在数据可视化领域内非常受欢迎,它们提供了广泛的工具和技术,可以创建高度定制化的图表和图形,帮助数据科学家和分析师在数据探索和结果展示中更有效地传达信息。

    基于ssm小区物业管理系统.zip

    基于ssm小区物业管理系统.zip

    RStudio的15个经典高效快捷操作.pdf

    RStudio的15个经典高效快捷操作

Global site tag (gtag.js) - Google Analytics