vb.net 操作Access数据库帮助类
发布时间:2020-12-17 00:15:50 所属栏目:大数据 来源:网络整理
导读:VB.net 操作Access数据库的一些常用方法,代码如下 Imports SystemImports System.Data.SqlClientImports System.ConfigurationImports System.Collections.GenericImports System.TextImports System.DataImports System.Configuration.ConfigurationSettin
VB.net 操作Access数据库的一些常用方法,代码如下 Imports System Imports System.Data.SqlClient Imports System.Configuration Imports System.Collections.Generic Imports System.Text Imports System.Data Imports System.Configuration.ConfigurationSettings Imports System.Data.OleDb Public Class DB Private Shared com As OleDb.OleDbCommand Private Shared reader As OleDb.OleDbDataReader Private Shared adapter As OleDb.OleDbDataAdapter Private Shared conn As OleDb.OleDbConnection ''' <summary> ''' 获取数据库连接 ''' </summary> ''' <value></value> ''' <returns></returns> ''' <remarks></remarks> Public Shared ReadOnly Property NewConn() As OleDbConnection Get Dim connectionString As String 'connectionString = System.Configuration.ConfigurationSettings.GetConfig("Supermarket") '连接2010数据库 'connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:GCU.accdb" '连接03-07数据库 connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:JLGCU.MDB" '应该在这里先判断conn是否为Nothing If conn Is Nothing Then conn = New OleDb.OleDbConnection(connectionString) End If If conn.State <> ConnectionState.Open Then conn.Open() End If Return conn End Get End Property ''' <summary> ''' 执行增删改(无参) ''' </summary> ''' <param name="sql">执行的Sql语句</param> ''' <returns></returns> ''' <remarks></remarks> Public Shared Function ExecuteNonQuery(ByVal sql As String) As Integer com = New OleDb.OleDbCommand(sql,NewConn()) Return com.ExecuteNonQuery() End Function ''' <summary> ''' 执行增删改(有参) ''' </summary> ''' <param name="sql"></param> ''' <param name="para"></param> ''' <returns></returns> ''' <remarks></remarks> Public Shared Function ExecuteNonQuery(ByVal sql As String,ByVal para As OleDbParameter()) As Integer com = New OleDb.OleDbCommand(sql,NewConn) com.Parameters.AddRange(para) Return com.ExecuteNonQuery() End Function ''' <summary> ''' 执行增删改的存储过程 ''' </summary> ''' <param name="para"></param> ''' <param name="ProcedureName"></param> ''' <returns></returns> ''' <remarks></remarks> Public Shared Function ExecuteNonQuery(ByVal para As SqlParameter(),ByVal ProcedureName As String) As Integer Dim cmd As OleDb.OleDbCommand cmd = New OleDb.OleDbCommand() cmd.Connection = NewConn() cmd.CommandText = ProcedureName cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddRange(para) Return com.ExecuteNonQuery End Function ''' <summary> ''' 执行查询(返回一个结果集,无参) ''' </summary> ''' <param name="sql"></param> ''' <returns></returns> ''' <remarks></remarks> Public Shared Function GetScalar(ByVal sql As String) As String Dim dataset As DataSet dataset = New DataSet() com = New OleDb.OleDbCommand(sql,NewConn) adapter = New OleDbDataAdapter(com) adapter.Fill(dataset) If dataset.Tables.Count > 0 And dataset.Tables(0).Rows.Count > 0 Then Return dataset.Tables(0).Rows(0)(0).ToString() End If Return "Null" End Function ''' <summary> ''' 执行查询(返回一个结果集,有参) ''' </summary> ''' <param name="sql"></param> ''' <param name="para"></param> ''' <returns></returns> ''' <remarks></remarks> Public Shared Function GetScalar(ByVal sql As String,ByVal para As SqlParameter()) As Integer com = New OleDb.OleDbCommand(sql,NewConn) com.Parameters.AddRange(para) Return Convert.ToInt32(com.ExecuteScalar()) End Function ''' <summary> ''' 执行查询(返回一行数据,无参) ''' </summary> ''' <param name="sql"></param> ''' <returns></returns> ''' <remarks></remarks> Public Shared Function GetReader(ByVal sql As String) As OleDbDataReader com = New OleDb.OleDbCommand(sql,NewConn) reader = com.ExecuteReader() Return reader End Function ''' <summary> ''' 执行查询(返回一行数据,有参) ''' </summary> ''' <param name="sql"></param> ''' <param name="para"></param> ''' <returns></returns> ''' <remarks></remarks> Public Shared Function GetReader(ByVal sql As String,ByVal para As SqlParameter()) As OleDbDataReader com = New OleDb.OleDbCommand(sql,NewConn) com.Parameters.AddRange(para) reader = com.ExecuteReader() Return reader End Function ''' <summary> ''' 执行查询(返回一个数据集,无参) ''' </summary> ''' <param name="sql"></param> ''' <returns></returns> ''' <remarks></remarks> Public Shared Function GetDataSet(ByVal sql As String) As DataTable Dim dataset As DataSet dataset = New DataSet() com = New OleDb.OleDbCommand(sql,NewConn) adapter = New OleDbDataAdapter(com) adapter.Fill(dataset) Return dataset.Tables(0) End Function ''' <summary> ''' 执行查询(返回一个数据集,有参) ''' </summary> ''' <param name="sql"></param> ''' <param name="para"></param> ''' <returns></returns> ''' <remarks></remarks> Public Shared Function GetDataSet(ByVal sql As String,ByVal para As SqlParameter()) As DataTable Dim dataset As DataSet dataset = New DataSet() com = New OleDbCommand(sql,NewConn) com.Parameters.AddRange(para) adapter = New OleDbDataAdapter(com) adapter.Fill(dataset) Return dataset.Tables(0) End Function End Class (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |