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

dim

成都创新互联是一家专注于做网站、网站设计和成都机柜租用的网络公司，有着丰富的建站经验和案例。

myselectquery

as

string

=

"select

*

from

表1

where

姓名='小强'"

dim

mycommand

as

new

sqlcommand

(myselectquery,

conn)

'建立一个command控件，conn是你的sqlconnection对象

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

dim

myreader

as

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

myreader

=

mycommand.executereader()'运行你的查询，结果到myreader

if

myreader.read()

then

'如果查到了数据

msgbox(myreader.getstring(0))

'显示第一个字段

end

if

vb.net 读取sql数据库字段

我是用C#.net写出来的

SqlConnection myConn = new SqlConnection(sqlconnstring);

myConn.Open();

SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT * from 表名", myConn);

thisAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

DataTable thisDataTable = new DataTable();

thisAdapter.Fill(thisDataTable);

DataTableReader thisReader = new DataTableReader(thisDataTable);

DataTable schemaTable = thisReader.GetSchemaTable();

foreach (DataRow dr in schemaTable.Rows)

{

MessageBox.Show(dr["ColumnName"].ToString());获取字段名称(f1 f2 f3)

MessageBox.Show(dr["ColumnSize"].ToString());获取字段长度(2 2 2)

MessageBox.Show(dr["DataType"].ToString());获取字段类(str str int)

}

刚刚入手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在数据库开发方面的应用。结合数据库系统开发的知识，介绍了物理表操作的方法，利用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