vb.net数据库应用 vbnet操作数据库

使用VB.net实现sql数据库的备份与恢复代码

Private Sub Button _Click(ByVal sender As System Object ByVal e As System EventArgs) Handles Button Click 备份数据库 BACKUP DATABASE Test TO DISK= C:\Backup WITH Format Dim cn As SqlConnection Dim cm As SqlCommand cn = getDB() cm = New SqlCommand( BACKUP DATABASE JXbase TO DISK= C:\Backup WITH Format cn) cn Open() cm ExecuteNonQuery() MsgBox( 备份成功 ) cn Close()

End Sub

恢复 Private Sub Button _Click(ByVal sender As System Object ByVal e As System EventArgs) Handles Button Click Dim cn As New SqlConnection Dim cn As New SqlConnection Dim mydr As SqlDataReader Dim str As String

Dim tmpConnectionString As String = Data Source=JJK;Initial Catalog=master;Integrated Security=True cn ConnectionString = tmpConnectionString cn ConnectionString = tmpConnectionString cn Open() cn Open() Dim cm As SqlCommand = New SqlCommand( select spid from master sysprocesses where dbid=db_id( JXbase ) cn) mydr = cm ExecuteReader()

Dim cm As SqlCommand = New SqlCommand() cm Connection = cn

While (mydr Read()) 杀死进程

str = kill mydr( spid ) ToString() cm CommandText = str cm CommandType = CommandType Text Application DoEvents() cm ExecuteNonQuery() 杀进程 End While mydr Close() cm = New SqlCommand( RESTORE DATABASE JXbase FROM DISK= C:\Backup cn) 恢复备份 cm ExecuteNonQuery() MsgBox( 恢复成功成功 ) cn Close() cn Close()

lishixinzhi/Article/program/net/201311/12901

相关问答

Q1: vb.net 怎么操作数据库

如果楼主熟悉VB6vb.net数据库应用，可以直接在项目中添加ADODB的Com引用，这样vb.net数据库应用你就可以像VB6那样操作数据库vb.net数据库应用了！

另外

.NET

Framework中连接数据库要用到ADO.NET。如果要操作Access数据库，要用到System.Data.OleDb命名空间下的许多类。

比如按楼主所说，“我想在textbox1中显示表一中【一些数据】字段下的第一个内容”：

'首先导入命名空间

Imports

System.Data

Imports

System.Data.OleDb

'然后在某一个事件处理程序中写：

Dim

conn

As

New

OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data

Source=数据库.accdb;Jet

OLEDB:Database

Password=MyDbPassword")

Dim

command

As

New

OleDbCommand("Select

*

From

数据表",

conn)

conn.Open()

'打开数据库连接

Dim

reader

As

OleDbDataReader

=

command.ExecuteReader()

'执行SQL语句，返回OleDbDataReader

对象

Do

While

reader.Read()

'读取一条数据

textbox1.Text

+=

reader("一些数据")

VbCrLf

Loop

reader.Close()

'关闭OleDbDataReader

conn.Close()

'关闭连接

Q2: 关于vb.net上数据库的使用问题

你把数据库中的数据查询出来保存在DataSet 中

DataSet 是脱机的数据集，也即与数据库断开的数据集

把DataSet绑定到GridView上，这时候你在GridView上的操作就都是与数据库断开的

Q3: 我想在vb.net的textbox里调用sql表中的数据，怎么办？

分类: 电脑/网络 程序设计 其他编程语言

解析:

Visual Basic.NET快速开发MIS系统

【摘 要】 本文介绍微软最新技术Visual Basic.NET在数据库开发方面的应用。结合数据库系统开发的知识，介绍了物理表操作的方法，利用Visual Basic.NET的面向对象的特征，利用类的继承知识，简化了数据库系统开发过程。

引言

以前版本的Visual Basic虽然号称自己是一种OOP（面向对象）编程语言，但却不是一个地地道道的OOP编程语言，最多只是半个面向对象的编程语言。但Visual Basic.NET已经是一种完全的面向对象的编程语言。他支持面向对象的所有基本特征：继承、多态和重载。这使得以前在Visual Basic中很难或根本实现不了的问题，在Visual Basic.NET中可以顺利的用简单的方法实现。

自定义数据操作类

定义一个数据访问的基类，并编写有关数据库操作的必要方法。

定义一个数据访问类，类名为CData。定义连接Oracle数据库的方法ConnOracle，获取数据集的方法GetDataSet, 获取物理表的方法GetDataTable, 向物理表中插入一行数据的方法Insert, 向物理表中删除数据的方法Delete, 向物理表中更新数据的方法Update。其实现方法不是本文的重点，在此仅给出代码，不作详细分析。代码如下：

Public Class CDataBase

Dim OleCnnDB As New OleDbConnection()

@#连接Oracle数据库，ServerName:服务器名，UserId:用户名，UserPwd:用户密码

Public Function ConnOracle(ByVal ServerName As String, ByVal UserId As String, ByVal UserPwd As String) As OleDbConnection

Dim OleCnnDB As New OleDbConnection()

With OleCnnDB

.ConnectionString = "Provider=MSDAORA.1;Password=@#" UserPwd "@#;User ID=@#" UserId "@#;Data Source=@#" ServerName "@#"

Try

.Open()

Catch er As Exception

MsgBox(er.ToString)

End Try

End With

mOleCnnDB = OleCnnDB

Return OleCnnDB

End Function

@#获取数据集。TableName：表名，strWhere：条件

Public Overloads Function GetDataSet(ByVal TableName As String, ByVal strWhere As String) As DataSet

Dim strSql As String

Dim myDataSet As New DataSet()

Dim myOleDataAdapter As New OleDbDataAdapter()

myOleDataAdapter.TableMappings.Add(TableName, TableName)

strSql = "SELECT * FROM " TableName " where " strWhere

myOleDataAdapter.SelectCommand = New OleDbCommand(strSql, mOleCnnDB)

Try

myOleDataAdapter.Fill(myDataSet)

Catch er As Exception

MsgBox(er.ToString)

End Try

Return myDataSet

End Function

@#获取物理表。TableName：表名

Public Overloads Function GetDataTable(ByVal TableName As String) As DataTable

Dim myDataSet As New DataSet()

myDataSet = GetDataSet(TableName)

Return myDataSet.Tables(0)

End Function

@#获取物理表。TableName：表名，strWhere：条件

Public Overloads Function GetDataTable(ByVal TableName As String, ByVal strWhere As String) As DataTable

Dim myDataSet As New DataSet()

myDataSet = GetDataSet(TableName, strWhere)

Return myDataSet.Tables(0)

End Function

@#向物理表中插入一行数据。TableName：表名，Value：行数据，BeginColumnIndex：开始列

Public Overloads Function Insert(ByVal TableName As String, ByVal Value As Object, Optional ByVal BeginColumnIndex As Int16 = 0) As Boolean

Dim myDataAdapter As New OleDbDataAdapter()

Dim strSql As String

Dim myDataSet As New DataSet()

Dim dRow As DataRow

Dim i, len As Int16

strSql = "SELECT * FROM " TableName

myDataAdapter.SelectCommand = New OleDbCommand(strSql, mOleCnnDB)

Dim custCB As OleDbCommandBuilder = New OleDbCommandBuilder(myDataAdapter)

myDataSet.Tables.Add(TableName)

myDataAdapter.Fill(myDataSet, TableName)

dRow = myDataSet.Tables(TableName).NewRow

len = Value.Length

For i = BeginColumnIndex To len - 1

If Not (IsDBNull(Value(i)) Or IsNothing(Value(i))) Then

dRow.Item(i) = Value(i)

End If

Next

myDataSet.Tables(TableName).Rows.Add(dRow)

Try

myDataAdapter.Update(myDataSet, TableName)

Catch er As Exception

MsgBox(er.ToString)

Return False

End Try

myDataSet.Tables.Remove(TableName)

Return True

End Function

@#更新物理表的一个字段的值。strSql：查询语句，FieldName_Value：字段及与对应的值

Public Overloads Sub Update(ByVal strSql As String, ByVal FieldName_Value As String)

Dim myDataAdapter As New OleDbDataAdapter()

Dim myDataSet As New DataSet()

Dim dRow As DataRow

Dim TableName, FieldName As String

Dim Value As Object

Dim a() As String

a = strSql.Split(" ")

TableName = a(3)

a = FieldName_Value.Split("=")

FieldName = a(0).Trim

Value = a(1)

myDataAdapter.SelectCommand = New OleDbCommand(strSql, mOleCnnDB)

Dim custCB As OleDbCommandBuilder = New OleDbCommandBuilder(myDataAdapter)

myDataSet.Tables.Add(TableName)

myDataAdapter.Fill(myDataSet, TableName)

dRow = myDataSet.Tables(TableName).Rows(0)

If Value Nothing Then

dRow.Item(FieldName) = Value

End If

Try

myDataAdapter.Update(myDataSet, TableName)

myDataSet.Tables.Remove(TableName)

Catch er As Exception

MsgBox(er.ToString)

End Try

End Sub

@#删除物理表的数据。TableName:表名，strWhere：条件

Public Overloads Sub Delete(ByVal TableName As String, ByVal strWhere As String)

Dim myReader As OleDbDataReader

Dim myCommand As New OleDbCommand()

Dim strSql As String

strSql = "delete FROM " TableName " where " strWhere

myCommand.Connection = mOleCnnDB

myCommand.CommandText = strSql

Try

myReader = myCommand.ExecuteReader()

myReader.Close()

Catch er As Exception

MsgBox(er.ToString)

End Try

End Sub

End Class

定义一操作数据库中物理表的类CData,此类继承CDataBase,即：

Public Class CData：Inherits CDataBase

此类应该由供用户提供所操作的物理表的表名，指定了表名就可取得该表的所有性质。该表主要完成插入、删除、更新功能。定义其属性、方法如下：

申明类CData的变量:

@#所要操作的表名

Private Shared UpdateTableName As String

@#所要操作的表对象

Public Shared UpdateDataTable As New DataTable()

@#对应表的一行数据197

Public Shared ObjFields() As Object

@#表的字段数

Public Shared FieldCount As Int16

@#主关键字。我们假设每个物理表都有一个主关键字字段fSystemID

Public Shared SystemID As String

说明：Shared 关键字指示一个或多个被声明的编程元素将被共享。共享元素不关联于某类或结构的特定实例。可以通过使用类名或结构名称或者类或结构的特定实例的变量名称限定共享元素来访问它们。

申明类CData的属性UpdateTable,当向UpdateTable赋给了一个已知表的表名，就可确定表的字段数，定义出数据行。这里，先打开表，再重新定义数据行.

Public Property UpdateTable() As String

Get

UpdateTable = UpdateTableName

End Get

Set(ByVal Value As String)

UpdateTableName = Value.Trim

UpdateDataTable = DB.GetDataTable(UpdateTableName)

UpdateTableFieldNames = UpdateDataTable.Clone

FieldCount = UpdateDataTable.Columns.Count

ReDim ObjFields(FieldCount - 1)

End Set

End Property

@#删除由主关键值fSystemID指定的数据行

Public Sub Delete()

Dim strSQL As String

strSQL = "Delete from " UpdateTableName " where fSystemID=" SystemID

DB.Delete(strSQL)

UpdateDataTable.Rows.Remove(GetRow)

End Sub

@#向表UpdateTableName中插入一行数据。数据由ObjFields给出

Public Function Insert() As Boolean

DB.Insert(UpdateTableName, ObjFields)

End Function

@#更新表UpdateTableName所指定的行

Public Shadows Sub Update()

Dim SetField As String

Dim i As Int16

For i = 1 To FieldCount - 1

SetField = UpdateTableFieldNames.Columns(i).ColumnName "=" ObjFields(i)

UpdateField(SetField)

Next

End Sub

Public Sub UpdateField(ByVal SetField As String)

Dim StrSQL As String

StrSQL = "select * from " UpdateTableName " where fSystemID= " SystemID

DB.Update(StrSQL, SetField)

End Sub

@#填充网络数据

Public Overloads Sub FillGrid(ByVal GridName As DataGrid)

GridName.DataSource = UpdateDataTable

End Sub

@#把数据网格的当前行数据定写入到输入控件中

Public Sub DataGridToText(ByVal frm As Form)

Dim RowIndex, i As Int16

Dim value

Dim obj As Control

Dim DataGrid As New DataGrid()

If FieldCount = 0 Then Exit Sub

For Each obj In frm.Controls

If obj.GetType.Name = "DataGrid" Then

DataGrid = obj

Exit For

End If

Next

RowIndex = DataGrid.CurrentRowIndex

For i = 1 To FieldCount - 1

value = DataGrid.Item(RowIndex, i)

If IsDBNull(value) = True Then

value = ""

End If

For Each obj In frm.Controls @#

If obj.TabIndex = i Then

obj.Text = value

Exit For

End If

Next

Next

End Sub

Q4: vb.net 中如何使用SQL语句查询数据库中的数据

1、首先打开Visual Studio 2008代码窗口，添加引用。

2、输入以下代码：Public conn1  As SqlConnection = New SqlConnection 。

3、声明关键字 Public；（因为是全局变量，所以用Public 来声明）。

4、如果SQL 数据库就在本机，则用以下代码连接。

5、如果代码太长，影响可读性，可以用空格加"_"后，回车换行即可。

Q5: vb.net数据库操作

参考一下下面这段代码就可以了。

Imports System.Data

'引入数据库操作类命名空间

Imports System.Data.OleDb

'引入ADO.NET操作命名空间

Public Class FrmModifystInfo

Inherits System.Windows.Forms.Form

Public ADOcmd As OleDbDataAdapter

Public ds As DataSet = New DataSet()

'建立DataSet对象

Public mytable As Data.DataTable

'建立表单对象

Public myrow As Data.DataRow

'建立数据行对象

Public rownumber As Integer

'定义一个整型变量来存放当前行数

Public SearchSQL As String

Public cmd As OleDbCommandBuilder

'======================================================

#Region " Windows 窗体设计器生成的代码 "

#End Region

'======================================================

Private Sub FrmModifystInfo_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load

'窗体的载入

TxtSID.Enabled = False

TxtName.Enabled = False

ComboSex.Enabled = False

TxtBornDate.Enabled = False

TxtClassno.Enabled = False

TxtRuDate.Enabled = False

TxtTel.Enabled = False

TxtAddress.Enabled = False

TxtComment.Enabled = False '设置信息为只读

Dim tablename As String = "student_Info "

SearchSQL = "select * from student_Info "

ExecuteSQL(SearchSQL, tablename) '打开数据库

ShowData() '显示记录

End Sub

Private Sub ShowData()

'在窗口中的textbox中显示数据

myrow = mytable.Rows.Item(rownumber)

TxtSID.Text = myrow.Item(0).ToString

TxtName.Text = myrow.Item(1).ToString

ComboSex.Text = myrow.Item(2).ToString

TxtBornDate.Text = Format(myrow.Item(3), "yyyy-MM-dd ")

TxtClassno.Text = myrow.Item(4).ToString

TxtTel.Text = myrow.Item(5).ToString

TxtRuDate.Text = Format(CDate(myrow.Item(6)), "yyyy-MM-dd ")

TxtAddress.Text = myrow.Item(7).ToString

TxtComment.Text = myrow.Item(8).ToString

End Sub

Private Sub BtFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtFirst.Click

'指向第一条数据

rownumber = 0

ShowData()

End Sub

Private Sub BtPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtPrev.Click

'指向上一条数据

BtNext.Enabled = True

rownumber = rownumber - 1

If rownumber 0 Then

rownumber = 0 '如果到达记录的首部，行号设为零

BtPrev.Enabled = False

End If

ShowData()

End Sub

Private Sub BtNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtNext.Click

'指向上一条数据

BtPrev.Enabled = True

rownumber = rownumber + 1

If rownumber mytable.Rows.Count - 1 Then

rownumber = mytable.Rows.Count - 1 '判断是否到达最后一条数据

BtNext.Enabled = False

End If

ShowData()

End Sub

Private Sub BtLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtLast.Click

'指向最后一条数据

rownumber = mytable.Rows.Count - 1

ShowData()

End Sub

Private Sub BtDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtDelete.Click

mytable.Rows.Item(rownumber).Delete() '删除记录

If MsgBox( "确定要删除改记录吗？ ", MsgBoxStyle.OKCancel + vbExclamation, "警告 ") = MsgBoxResult.OK Then

cmd = New OleDbCommandBuilder(ADOcmd)

'使用自动生成的SQL语句

ADOcmd.Update(ds, "student_Info ")

BtNext.PerformClick()

End If

End Sub

Private Sub BtModify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtModify.Click

TxtSID.Enabled = False '关键字段只读

TxtName.Enabled = True '可读写

ComboSex.Enabled = True

TxtBornDate.Enabled = True

TxtClassno.Enabled = True

TxtRuDate.Enabled = True

TxtTel.Enabled = True

TxtAddress.Enabled = True

TxtComment.Enabled = True

End Sub

Private Sub BtUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtUpdate.Click

If Not Testtxt(TxtName.Text) Then

MsgBox( "请输入姓名！ ", vbOKOnly + vbExclamation, "警告 ")

TxtName.Focus()

Exit Sub

End If

If Not Testtxt(ComboSex.Text) Then

MsgBox( "请选择性别！ ", vbOKOnly + vbExclamation, "警告 ")

ComboSex.Focus()

Exit Sub

End If

If Not Testtxt(TxtClassno.Text) Then

MsgBox( "请选择班号！ ", vbOKOnly + vbExclamation, "警告 ")

TxtClassno.Focus()

Exit Sub

End If

If Not Testtxt(TxtTel.Text) Then

MsgBox( "请输入联系电话！ ", vbOKOnly + vbExclamation, "警告 ")

TxtTel.Focus()

Exit Sub

End If

If Not Testtxt(TxtAddress.Text) Then

MsgBox( "请输入家庭住址！ ", vbOKOnly + vbExclamation, "警告 ")

TxtAddress.Focus()

Exit Sub

End If

If Not IsNumeric(Trim(TxtSID.Text)) Then

MsgBox( "请输入数字学号！ ", vbOKOnly + vbExclamation, "警告 ")

Exit Sub

TxtSID.Focus()

End If

If Not IsDate(TxtBornDate.Text) Then

MsgBox( "出生时间应输入日期格式（yyyy-mm-dd）！ ", vbOKOnly + vbExclamation, "警告 ")

Exit Sub

TxtBornDate.Focus()

End If

If Not IsDate(TxtRuDate.Text) Then

MsgBox( "入校时间应输入日期格式（yyyy-mm-dd）！ ", vbOKOnly + vbExclamation, "警告 ")

TxtRuDate.Focus()

Exit Sub

End If

myrow.Item(0) = Trim(TxtSID.Text)

myrow.Item(1) = Trim(TxtName.Text)

myrow.Item(2) = Trim(ComboSex.Text)

myrow.Item(3) = Trim(TxtBornDate.Text)

myrow.Item(4) = Trim(TxtClassno.Text)

myrow.Item(5) = Trim(TxtTel.Text)

myrow.Item(6) = Trim(TxtRuDate.Text)

myrow.Item(7) = Trim(TxtAddress.Text)

myrow.Item(8) = Trim(TxtComment.Text)

mytable.GetChanges()

cmd = New OleDbCommandBuilder(ADOcmd)

'使用自动生成的SQL语句

ADOcmd.Update(ds, "student_Info ")

'对数据库进行更新

MsgBox( "修改学籍信息成功！ ", vbOKOnly + vbExclamation, "警告 ")

TxtName.Enabled = False

ComboSex.Enabled = False

TxtBornDate.Enabled = False

TxtClassno.Enabled = False

TxtRuDate.Enabled = False

TxtTel.Enabled = False

TxtAddress.Enabled = False

TxtComment.Enabled = False '重新设置信息为只读

End Sub

Private Sub BtCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtCancel.Click

TxtSID.Enabled = False

TxtName.Enabled = False

ComboSex.Enabled = False

TxtBornDate.Enabled = False

TxtClassno.Enabled = False

TxtRuDate.Enabled = False

TxtTel.Enabled = False

TxtAddress.Enabled = False

TxtComment.Enabled = False

End Sub

Public Function ExecuteSQL(ByVal SQL As String, ByVal table As String)

Try

'建立ADODataSetCommand对象

'数据库查询函数

ADOcmd = New OleDbDataAdapter(SQL, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\student.mdb ")

'建立ADODataSetCommand对象

ADOcmd.Fill(ds, table) '取得表单

mytable = ds.Tables.Item(0) '取得名为table的表

rownumber = 0 '设置为第一行

myrow = mytable.Rows.Item(rownumber)

'取得第一行数据

Catch

MsgBox(Err.Description)

End Try

End Function

End Class

请采纳。

关于vb.net数据库应用和vbnet操作数据库的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站。