VB.NET中如何用SQL語(yǔ)句建表（復(fù)制表）？謝謝了！

分類: 電腦/網(wǎng)絡(luò) 程序設(shè)計(jì) 其他編程語(yǔ)言

我們提供的服務(wù)有：成都做網(wǎng)站、成都網(wǎng)站制作、微信公眾號(hào)開(kāi)發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、陽(yáng)泉ssl等。為近千家企事業(yè)單位解決了網(wǎng)站和推廣的問(wèn)題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的陽(yáng)泉網(wǎng)站制作公司

問(wèn)題描述:

在SQL數(shù)據(jù)庫(kù)里已有一個(gè)現(xiàn)成的空數(shù)據(jù)表（只有字段、沒(méi)有數(shù)據(jù)），請(qǐng)問(wèn)高手，怎樣在VB.NET中用SQL語(yǔ)句把該數(shù)據(jù)庫(kù)中的那個(gè)表復(fù)制一個(gè)到該數(shù)據(jù)庫(kù)（字段不變、數(shù)據(jù)為空）只是把數(shù)據(jù)表的名改了？

謝謝！謝謝！

解析:

select * into 新表 from 舊表

使用 SELECT INTO 插入行

SELECT INTO 語(yǔ)句創(chuàng)建一個(gè)新表，并用 SELECT 的結(jié)果集填充該表。新表的結(jié)構(gòu)由選擇列表中表達(dá)式的特性定義，例如：

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 可將幾個(gè)表或視圖中的數(shù)據(jù)組合成一個(gè)表。也可用于創(chuàng)建一個(gè)包含選自鏈接服務(wù)器的數(shù)據(jù)的新表。

我想在vb.net的textbox里調(diào)用sql表中的數(shù)據(jù)，怎么辦？

分類: 電腦/網(wǎng)絡(luò) 程序設(shè)計(jì) 其他編程語(yǔ)言

解析:

Visual Basic.NET快速開(kāi)發(fā)MIS系統(tǒng)

【摘 要】 本文介紹微軟最新技術(shù)Visual Basic.NET在數(shù)據(jù)庫(kù)開(kāi)發(fā)方面的應(yīng)用。結(jié)合數(shù)據(jù)庫(kù)系統(tǒng)開(kāi)發(fā)的知識(shí)，介紹了物理表操作的方法，利用Visual Basic.NET的面向?qū)ο蟮奶卣?，利用類的繼承知識(shí)，簡(jiǎn)化了數(shù)據(jù)庫(kù)系統(tǒng)開(kāi)發(fā)過(guò)程。

引言

以前版本的Visual Basic雖然號(hào)稱自己是一種OOP（面向?qū)ο螅┚幊陶Z(yǔ)言，但卻不是一個(gè)地地道道的OOP編程語(yǔ)言，最多只是半個(gè)面向?qū)ο蟮木幊陶Z(yǔ)言。但Visual Basic.NET已經(jīng)是一種完全的面向?qū)ο蟮木幊陶Z(yǔ)言。他支持面向?qū)ο蟮乃谢咎卣鳎豪^承、多態(tài)和重載。這使得以前在Visual Basic中很難或根本實(shí)現(xiàn)不了的問(wèn)題，在Visual Basic.NET中可以順利的用簡(jiǎn)單的方法實(shí)現(xiàn)。

自定義數(shù)據(jù)操作類

定義一個(gè)數(shù)據(jù)訪問(wèn)的基類，并編寫(xiě)有關(guān)數(shù)據(jù)庫(kù)操作的必要方法。

定義一個(gè)數(shù)據(jù)訪問(wèn)類，類名為CData。定義連接Oracle數(shù)據(jù)庫(kù)的方法ConnOracle，獲取數(shù)據(jù)集的方法GetDataSet, 獲取物理表的方法GetDataTable, 向物理表中插入一行數(shù)據(jù)的方法Insert, 向物理表中刪除數(shù)據(jù)的方法Delete, 向物理表中更新數(shù)據(jù)的方法Update。其實(shí)現(xiàn)方法不是本文的重點(diǎn)，在此僅給出代碼，不作詳細(xì)分析。代碼如下：

Public Class CDataBase

Dim OleCnnDB As New OleDbConnection()

@#連接Oracle數(shù)據(jù)庫(kù)，ServerName:服務(wù)器名，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

@#獲取數(shù)據(jù)集。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

@#向物理表中插入一行數(shù)據(jù)。TableName：表名，Value：行數(shù)據(jù)，BeginColumnIndex：開(kāi)始列

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

@#更新物理表的一個(gè)字段的值。strSql：查詢語(yǔ)句，F(xiàn)ieldName_Value：字段及與對(duì)應(yīng)的值

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

@#刪除物理表的數(shù)據(jù)。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

定義一操作數(shù)據(jù)庫(kù)中物理表的類CData,此類繼承CDataBase,即：

Public Class CData：Inherits CDataBase

此類應(yīng)該由供用戶提供所操作的物理表的表名，指定了表名就可取得該表的所有性質(zhì)。該表主要完成插入、刪除、更新功能。定義其屬性、方法如下：

申明類CData的變量:

@#所要操作的表名

Private Shared UpdateTableName As String

@#所要操作的表對(duì)象

Public Shared UpdateDataTable As New DataTable()

@#對(duì)應(yīng)表的一行數(shù)據(jù)197

Public Shared ObjFields() As Object

@#表的字段數(shù)

Public Shared FieldCount As Int16

@#主關(guān)鍵字。我們假設(shè)每個(gè)物理表都有一個(gè)主關(guān)鍵字字段fSystemID

Public Shared SystemID As String

說(shuō)明：Shared 關(guān)鍵字指示一個(gè)或多個(gè)被聲明的編程元素將被共享。共享元素不關(guān)聯(lián)于某類或結(jié)構(gòu)的特定實(shí)例。可以通過(guò)使用類名或結(jié)構(gòu)名稱或者類或結(jié)構(gòu)的特定實(shí)例的變量名稱限定共享元素來(lái)訪問(wèn)它們。

申明類CData的屬性UpdateTable,當(dāng)向UpdateTable賦給了一個(gè)已知表的表名，就可確定表的字段數(shù)，定義出數(shù)據(jù)行。這里，先打開(kāi)表，再重新定義數(shù)據(jù)行.

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

@#刪除由主關(guān)鍵值fSystemID指定的數(shù)據(jù)行

Public Sub Delete()

Dim strSQL As String

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

DB.Delete(strSQL)

UpdateDataTable.Rows.Remove(GetRow)

End Sub

@#向表UpdateTableName中插入一行數(shù)據(jù)。數(shù)據(jù)由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

@#填充網(wǎng)絡(luò)數(shù)據(jù)

Public Overloads Sub FillGrid(ByVal GridName As DataGrid)

GridName.DataSource = UpdateDataTable

End Sub

@#把數(shù)據(jù)網(wǎng)格的當(dāng)前行數(shù)據(jù)定寫(xiě)入到輸入控件中

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 server的表格邏輯值問(wèn)題

Dim?CheckColumn?As?New?DataGridViewCheckBoxColumn

CheckColumn.Name?=?"State"

CheckColumn.HeaderText?=?"State"

CheckColumn.ThreeState?=?True

DataGridView1.Columns.Add(CheckColumn)

DataGridView1.AllowUserToAddRows?=?False

DataGridView1.Rows.Add(False)

DataGridView1.Rows.Add(DBNull.Value)

DataGridView1.Rows.Add(True)

vb.net 中如何使用SQL語(yǔ)句查詢數(shù)據(jù)庫(kù)中的數(shù)據(jù)

1、首先打開(kāi)Visual Studio 2008代碼窗口，添加引用。

2、輸入以下代碼：Public conn1 ?As SqlConnection = New SqlConnection 。

3、聲明關(guān)鍵字 Public；（因?yàn)槭侨肿兞?，所以用Public 來(lái)聲明）。

4、如果SQL 數(shù)據(jù)庫(kù)就在本機(jī)，則用以下代碼連接。

5、如果代碼太長(zhǎng)，影響可讀性，可以用空格加"_"后，回車換行即可。