关于vb.net操作sql类的信息
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
标题名称:关于vb.net操作sql类的信息
本文地址:http://scyanting.com/article/doohjdh.html