请教连接数据库问题
网上找到一个连接数据库的代码,但不知道怎么运用函数连接
好像这样不行
Dim a As SQLDataBase
a = New SQLDataBase
Public Class SQLDataBase
' 数据库连接字符串
Private cnstr As String = ""
' 数据库执行的SQL脚本
Private sqlstr As String = ""
' 数据库连接对象
Private cn As SqlConnection
' SQl执行语句命令
Private cmd As SqlCommand
''' <summary>
''' 创建SQlDataBase类的实例,并打开以《connectstr》为连接字符串的数据库
''' </summary>
''' <param name="connectstr">连接数据库的字符串;例如:Data Source=IPadress;Initial Catalog=DataBaseName;Integrated Security=false;User ID=用户名;Password=密码;</param>
''' <remarks></remarks>
Public Sub New(ByVal connectstr As String)
' 给数据库连接字符赋值
If connectstr.Length = 0 Then
MsgBox("connectstr为空,创建SQLDataBase失败")
Exit Sub
Else
cnstr = connectstr
End If
' 连接到数据库,并打开
Try
cn = New SqlConnection(cnstr)
cn.Open()
Catch ex As Exception
MsgBox("错误代码为:" & Err.Number & vbCrLf & "错误描述为: " & Err.Description)
Exit Sub
End Try
End Sub
''' <summary>
''' 执行SQL更新语句
''' </summary>
''' <param name="updatecmd"></param>
''' <remarks></remarks>
Public Sub UpdateSQL(ByVal updatecmd As String)
' 给数据库执行脚本sqlstr赋值
If updatecmd.Length = 0 Then
MsgBox("updatecmd为空,无法更新数据库")
Exit Sub
Else
sqlstr = updatecmd
End If
' 给命令对象赋值
cmd = New SqlCommand With {.CommandText = updatecmd, .Connection = cn}
' 定义执行SQL语句收影响的行数
Dim influnrows As Integer
Try
influnrows = cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox("错误代码为:" & Err.Number & vbCrLf & "错误描述为: " & Err.Description)
Exit Sub
End Try
End Sub
''' <summary>
''' 通过Sqlreader来获取服务器数据库里面的值,返回的是二维字符串类型的数组
''' </summary>
''' <param name="CommandText">Transact-SQL语句</param>
''' <returns>返回的是二维字符串类型的数组</returns>
''' <remarks></remarks>
Public Function ExecuteSQLToArray(ByVal CommandText As String) As String(,)
' 定义函数的返回结果
Dim FunRet As String(,) = Nothing
' 定义数据库reader对象
Dim reader As SqlDataReader = Nothing
' 给数据库执行脚本sqlstr赋值
If CommandText.Length = 0 Then
MsgBox("updatecmd为空,无法更新数据库")
Return FunRet
Else
sqlstr = CommandText
End If
' 给命令对象赋值
cmd = New SqlCommand With {.CommandText = CommandText, .Connection = cn}
' 给reader对象赋值
reader = cmd.ExecuteReader()
'判断是否有结果
If reader.HasRows = False Then
Return FunRet
End If
'*********************
'remarks:因为reader是一条一条语句的读取, reader只能获取列数, 不能获取行数, 因此需要将总的记录除以列数皆可以获取行数
'*********************
' 定义函数返回数组的列数和行数
Dim lstReader As New List(Of String)
Dim intColumnCount As Integer
Dim intRowsCount As Integer
'将结果集存到listReader列表
intColumnCount = reader.FieldCount
While reader.Read()
For i = 0 To intColumnCount - 1
lstReader.Add(reader.GetValue(i).ToString) '//如果数据库里面是null,则返回的是空值
Next
End While
'获取结果集的行数
intRowsCount = lstReader.Count / intColumnCount
'将结果转化为二位数组
ReDim FunRet(intRowsCount - 1, intColumnCount - 1)
Dim index As Integer = 0
For j = 0 To UBound(FunRet, 1)
For i = 0 To UBound(FunRet, 2)
FunRet(j, i) = lstReader.Item(index)
index = index + 1
Next
Next
'将函数的结果返回
Return FunRet
End Function
''' <summary>
''' 通过SqlDataAdapter来获取服务器数据库里面的值,返回的是DataTable对象
''' </summary>
''' <param name="SelectCommandText">Transact-SQL语句</param>
''' <returns>返回的是二维字符串类型的数组</returns>
''' <remarks></remarks>
Public Function ExecuteSQLToDataTable(ByVal SelectCommandText As String) As DataTable
' 定义函数的返回结果
Dim FunRet As New DataTable
' 给数据库执行脚本sqlstr赋值
If SelectCommandText.Length = 0 Then
MsgBox("updatecmd为空,无法更新数据库")
Return FunRet
End If
'将结果填充到SqlDataAdapter中
Dim sda As SqlDataAdapter
sda = New SqlDataAdapter(SelectCommandText, cn)
sda.Fill(FunRet)
'返回函数的值
Return FunRet
End Function
''' <summary>
''' 释放资源,关闭数据库连接
''' </summary>
''' <remarks></remarks>
Public Sub close()
'关闭cmd对象
If IsNothing(cmd) = False Then
cmd = Nothing
End If
'断开和数据库的连接
If IsNothing(cn) = False Then
cn.Close()
End If
End Sub
End Class
好像这样不行
Dim a As SQLDataBase
a = New SQLDataBase
Public Class SQLDataBase
' 数据库连接字符串
Private cnstr As String = ""
' 数据库执行的SQL脚本
Private sqlstr As String = ""
' 数据库连接对象
Private cn As SqlConnection
' SQl执行语句命令
Private cmd As SqlCommand
''' <summary>
''' 创建SQlDataBase类的实例,并打开以《connectstr》为连接字符串的数据库
''' </summary>
''' <param name="connectstr">连接数据库的字符串;例如:Data Source=IPadress;Initial Catalog=DataBaseName;Integrated Security=false;User ID=用户名;Password=密码;</param>
''' <remarks></remarks>
Public Sub New(ByVal connectstr As String)
' 给数据库连接字符赋值
If connectstr.Length = 0 Then
MsgBox("connectstr为空,创建SQLDataBase失败")
Exit Sub
Else
cnstr = connectstr
End If
' 连接到数据库,并打开
Try
cn = New SqlConnection(cnstr)
cn.Open()
Catch ex As Exception
MsgBox("错误代码为:" & Err.Number & vbCrLf & "错误描述为: " & Err.Description)
Exit Sub
End Try
End Sub
''' <summary>
''' 执行SQL更新语句
''' </summary>
''' <param name="updatecmd"></param>
''' <remarks></remarks>
Public Sub UpdateSQL(ByVal updatecmd As String)
' 给数据库执行脚本sqlstr赋值
If updatecmd.Length = 0 Then
MsgBox("updatecmd为空,无法更新数据库")
Exit Sub
Else
sqlstr = updatecmd
End If
' 给命令对象赋值
cmd = New SqlCommand With {.CommandText = updatecmd, .Connection = cn}
' 定义执行SQL语句收影响的行数
Dim influnrows As Integer
Try
influnrows = cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox("错误代码为:" & Err.Number & vbCrLf & "错误描述为: " & Err.Description)
Exit Sub
End Try
End Sub
''' <summary>
''' 通过Sqlreader来获取服务器数据库里面的值,返回的是二维字符串类型的数组
''' </summary>
''' <param name="CommandText">Transact-SQL语句</param>
''' <returns>返回的是二维字符串类型的数组</returns>
''' <remarks></remarks>
Public Function ExecuteSQLToArray(ByVal CommandText As String) As String(,)
' 定义函数的返回结果
Dim FunRet As String(,) = Nothing
' 定义数据库reader对象
Dim reader As SqlDataReader = Nothing
' 给数据库执行脚本sqlstr赋值
If CommandText.Length = 0 Then
MsgBox("updatecmd为空,无法更新数据库")
Return FunRet
Else
sqlstr = CommandText
End If
' 给命令对象赋值
cmd = New SqlCommand With {.CommandText = CommandText, .Connection = cn}
' 给reader对象赋值
reader = cmd.ExecuteReader()
'判断是否有结果
If reader.HasRows = False Then
Return FunRet
End If
'*********************
'remarks:因为reader是一条一条语句的读取, reader只能获取列数, 不能获取行数, 因此需要将总的记录除以列数皆可以获取行数
'*********************
' 定义函数返回数组的列数和行数
Dim lstReader As New List(Of String)
Dim intColumnCount As Integer
Dim intRowsCount As Integer
'将结果集存到listReader列表
intColumnCount = reader.FieldCount
While reader.Read()
For i = 0 To intColumnCount - 1
lstReader.Add(reader.GetValue(i).ToString) '//如果数据库里面是null,则返回的是空值
Next
End While
'获取结果集的行数
intRowsCount = lstReader.Count / intColumnCount
'将结果转化为二位数组
ReDim FunRet(intRowsCount - 1, intColumnCount - 1)
Dim index As Integer = 0
For j = 0 To UBound(FunRet, 1)
For i = 0 To UBound(FunRet, 2)
FunRet(j, i) = lstReader.Item(index)
index = index + 1
Next
Next
'将函数的结果返回
Return FunRet
End Function
''' <summary>
''' 通过SqlDataAdapter来获取服务器数据库里面的值,返回的是DataTable对象
''' </summary>
''' <param name="SelectCommandText">Transact-SQL语句</param>
''' <returns>返回的是二维字符串类型的数组</returns>
''' <remarks></remarks>
Public Function ExecuteSQLToDataTable(ByVal SelectCommandText As String) As DataTable
' 定义函数的返回结果
Dim FunRet As New DataTable
' 给数据库执行脚本sqlstr赋值
If SelectCommandText.Length = 0 Then
MsgBox("updatecmd为空,无法更新数据库")
Return FunRet
End If
'将结果填充到SqlDataAdapter中
Dim sda As SqlDataAdapter
sda = New SqlDataAdapter(SelectCommandText, cn)
sda.Fill(FunRet)
'返回函数的值
Return FunRet
End Function
''' <summary>
''' 释放资源,关闭数据库连接
''' </summary>
''' <remarks></remarks>
Public Sub close()
'关闭cmd对象
If IsNothing(cmd) = False Then
cmd = Nothing
End If
'断开和数据库的连接
If IsNothing(cn) = False Then
cn.Close()
End If
End Sub
End Class
...全文
相关推荐
解决Java 连接MySQL数据库的出现的问题
完整解决Java MySQL连接的方法,步骤清晰简明,立即解决
C#连接Informix数据库的问题
主要介绍了C#连接Informix数据库的问题,本文给大家介绍的非常详细,对大家的工作或学习具有一定的参考借鉴价值,需要的朋友可以参考下
PB9.0升级到11.5 后数据库连接问题
由于pb9.0对webservice支持不好,因此将程序升级到pb11.5下面是本人在升级过程中的一些体会,希望对大家有用
解决Django连接db遇到的问题
1、django.db.utils.ConnectionDoesNotExist: The connection default doesn’t exist 解决:第一个连接的命名一定要是default,将EN5Test改为default即可 2、django.core.exceptions.ImproperlyConfigured Requested setting DATABASES, but settings are not configured. You must either define the environment variable DJANGO_SETTINGS_MODUL
dr中数据库连接问题
解决dreameaver中数据库连接中字符串定义连接问题。。。。03版和10版office的不同


