vb.net操作sql的简单介绍

VB.NET中如何用SQL语句建表（复制表）？谢谢了！

分类: 电脑/网络 程序设计 其vb.net操作sql他编程语言

问题描述:

在SQL数据库里已有一个现成vb.net操作sql的空数据表（只有字段、没有数据）vb.net操作sql，请问高手，怎样在VB.NET中用SQL语句把该数据库中vb.net操作sql的那个表复制一个到该数据库（字段不变、数据为空）只是把数据表的名改了vb.net操作sql？

谢谢！谢谢！

解析:

select * into 新表 from 旧表

使用 SELECT INTO 插入行

SELECT INTO 语句创建一个新表，并用 SELECT 的结果集填充该表。新表的结构由选择列表中表达式的特性定义，例如：

SELECT Shippers.*, Link.Address, Link.City,

Link.Region, Link.PostalCode

INTO NewShippers

FROM Shippers

JOIN LinkServer.DB.dbo.Shippers AS Link

ON (Shippers.ShipperID = Link.ShipperID)

SELECT INTO 可将几个表或视图中的数据组合成一个表。也可用于创建一个包含选自链接服务器的数据的新表。

刚刚入手VB.NET,对于SQL数据库操作遇到些小问题,请知情者帮忙.在线等~~~

你干嘛把程序发两次阿

访问数据库简单来说，先建立一个数据库SqlClient.SqlConnection

连接，再建立一个数据命令SqlClient.SqlCommand，然后打开连接执行命令得到结果并关闭连接。就这么简单。给你个例子吧。

Public

Function

SqlAccess()

{

strSqlConn

As

String

=

"

连接字符串";

//连接字符串

strSqlCmd

As

string

=

"sql查询字符串";

//sql查询字符串

sqlConn

As

SqlClient.SqlConnection;

//申明一个连接

sqlCmd

As

SqlClient.SqlCommand;

//申明一个SqlCommand

Try

sqlConn

=

New

SqlClient.SqlConnection;

//建立连接

sqlConn.ConnectionString

=

strSqlConn;

sqlCmd

=

New

SqlClient.SqlCommand(strSqlCmd

,sqlConn);//建立查询

sqlConn.Open()

//打开连接

sqlCmd.ExcuteScalar()

//执行查询，并返回结果，这里你可以设一个变量来获取结果，比如

tmp

=

sqlCmd.ExcuteScalar()，我这里省略了

Catch

ex

As

Exception

MsbBox(ex.ToString)

//显示错误信息

Finally

sqlConn.Close()

//关闭连接

End

Try

}

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

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

解析:

Visual Basic.NET快速开发MIS系统

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

引言

以前版本的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

@#主关键字。vb.net操作sql我们假设每个物理表都有一个主关键字字段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

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

dim

myselectquery

as

string

=

"select

*

from

表1

where

姓名='小强'"

dim

mycommand

as

new

sqlcommand

(myselectquery,

conn)

'建立一个command控件，conn是vb.net操作sql你vb.net操作sql的sqlconnection对象

conn.open()'打开数据连接

dim

myreader

as

sqldatareader'定义一个reader用来读数据

myreader

=

mycommand.executereader()'运行vb.net操作sql你的查询，结果到myreader

if

myreader.read()

then

'如果查到了数据

msgbox(myreader.getstring(0))

'显示第一个字段

end

if

使用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

vb.net操作sql的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于、vb.net操作sql的信息别忘了在本站进行查找喔。