锐客网

  1. 首页 > 生活百科 > it技术 > >

vb.net操作sql的简单介绍

IT技术TableName

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
【vb.net操作sql的简单介绍】 Value = https://www.04ip.com/post/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 ValueNothing 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 = https://www.04ip.com/post/DataGrid.Item(RowIndex, i)
If IsDBNull(value) = True Then
valuehttps://www.04ip.com/post/= ""
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 = killmydr( 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的信息别忘了在本站进行查找喔 。

    推荐阅读


    上一篇：家庭酒店如何营销促销，酒店最简单的营销方案

    下一篇：mc服务器箱子，mc服务器ban物品