本人是根据自己的经历,将有基于VB.Net的数据库的操作整理如下,或许不是很完整,后面有机会再补充。现将代码里面的大概内容叙述一下:
-
New(connectstr)方法是创建带参数的SQLDataBase的实例;
-
UpdateSQL(updatecmd)方法是执行updatecmd数据库语句,更新数据库;
-
ExecuteSQLToArray(CommandText)函数是通过Sqlreader来获取服务器数据库里面的值,返回的是二维字符串类型的数组;
-
ExecuteSQLToDataTable(SelectCommandText)函数通过SqlDataAdapter来获取服务器数据库里面的值,返回的是DataTable对象,方便后期的处理;
-
close()方法是释放资源,关闭数据库连接。
-
'********************************
-
-
-
-
-
'*******************************
-
Imports System.Data.SqlClient
-
-
-
Private cnstr As String = ""
-
-
Private sqlstr As String = ""
-
-
Private cn As SqlConnection
-
-
Private cmd As SqlCommand
-
-
-
''' 创建SQlDataBase类的实例,并打开以《connectstr》为连接字符串的数据库
-
-
''' <param name="connectstr">连接数据库的字符串;例如:Data Source=IPadress;Initial Catalog=DataBaseName;Integrated Security=false;User ID=用户名;Password=密码;</param>
-
-
Public Sub New(ByVal connectstr As String)
-
-
If connectstr.Length = 0 Then
-
MsgBox("connectstr为空,创建SQLDataBase失败")
-
-
-
-
-
-
-
-
cn = New SqlConnection(cnstr)
-
-
-
MsgBox("错误代码为:" & Err.Number & vbCrLf & "错误描述为: " & Err.Description)
-
-
-
-
-
-
-
-
''' <param name="updatecmd"></param>
-
-
Public Sub UpdateSQL(ByVal updatecmd As String)
-
-
-
If updatecmd.Length = 0 Then
-
MsgBox("updatecmd为空,无法更新数据库")
-
-
-
-
-
-
-
cmd = New SqlCommand With {.CommandText = updatecmd, .Connection = cn}
-
-
-
Dim influnrows As Integer
-
-
influnrows = cmd.ExecuteNonQuery()
-
-
MsgBox("错误代码为:" & Err.Number & vbCrLf & "错误描述为: " & Err.Description)
-
-
-
-
-
-
''' 通过Sqlreader来获取服务器数据库里面的值,返回的是二维字符串类型的数组
-
-
''' <param name="CommandText">Transact-SQL语句</param>
-
''' <returns>返回的是二维字符串类型的数组</returns>
-
-
Public Function ExecuteSQLToArray(ByVal CommandText As String) As String(,)
-
-
-
Dim FunRet As String(,) = Nothing
-
-
Dim reader As SqlDataReader = Nothing
-
-
-
If CommandText.Length = 0 Then
-
MsgBox("updatecmd为空,无法更新数据库")
-
-
-
-
-
-
-
cmd = New SqlCommand With {.CommandText = CommandText, .Connection = cn}
-
-
reader = cmd.ExecuteReader()
-
-
-
If reader.HasRows = False Then
-
-
-
-
-
-
'remarks:因为reader是一条一条语句的读取, reader只能获取列数, 不能获取行数, 因此需要将总的记录除以列数皆可以获取行数
-
-
-
-
Dim lstReader As New List(Of String)
-
Dim intColumnCount As Integer
-
Dim intRowsCount As Integer
-
-
-
intColumnCount = reader.FieldCount
-
-
For i = 0 To intColumnCount - 1
-
lstReader.Add(reader.GetValue(i).ToString) '//如果数据库里面是null,则返回的是空值
-
-
-
-
-
intRowsCount = lstReader.Count / intColumnCount
-
-
-
ReDim FunRet(intRowsCount - 1, intColumnCount - 1)
-
-
For j = 0 To UBound(FunRet, 1)
-
For i = 0 To UBound(FunRet, 2)
-
FunRet(j, i) = lstReader.Item(index)
-
-
-
-
-
-
-
-
-
-
''' 通过SqlDataAdapter来获取服务器数据库里面的值,返回的是DataTable对象
-
-
''' <param name="SelectCommandText">Transact-SQL语句</param>
-
''' <returns>返回的是二维字符串类型的数组</returns>
-
-
Public Function ExecuteSQLToDataTable(ByVal SelectCommandText As String) As DataTable
-
-
-
Dim FunRet As New DataTable
-
-
-
If SelectCommandText.Length = 0 Then
-
MsgBox("updatecmd为空,无法更新数据库")
-
-
-
-
-
Dim sda As SqlDataAdapter
-
sda = New SqlDataAdapter(SelectCommandText, cn)
-
-
-
-
-
-
-
-
-
-
-
-
-
-
If IsNothing(cmd) = False Then
-
-
-
-
-
If IsNothing(cn) = False Then
-
-
-
-