IT博客-MT'S BLOG

使用DataReader

MT'S BLOG — Thu, 23 Nov 2006 15:00:00 GMT

    ADO.NET提供了三个类:DBConnection, DBCommand 和 DBDataReader，它们与传统的ADO对象十分的相似。但是在.NET应用程序中它们不能被直接使用。能够使用的是从它们继承而来的类，比如ADOConnection, ADOCommand 和 ADODataReader来读取数据，这里的前提是存在一个OLEDB的Provider。如果要操作的对象是SQL Server，那么将使用另一些功能相同的类SQLConnection, SQLCommand 和 SQLDataReader 以便直接通过SQL Server 的 TDS 连接通道来获得更好的工作性能。下面是一个例子（因为在这里C#和VB.NET的代码区别不是很大，所以将只使用VB.NET的实例）：

Imports System.Data
Imports System.Data.ADO
..
Dim cn As New ADOConnection()
cn.ConnectionString = strConnect

Try
   cn.Open()
   Dim cd As New ADOCommand(搒elect * from authors? cn)
   Dim dr As ADODataReader
   cd.Execute(dr)
   While dr.Read()
     listbox1.Items.Add(dr(揳u_lname?)
   End While
Catch err As Exception
   MsgBox(err.ToString)
Finally
   If cn.State = DBObjectState.Open Then cn.Close()
End Try

    在上面你会发现在While Loop语句中没有出现MoveNext方法，因为DataReader的Read方法将自动前移光标，并会在没有数据的时候返回一个False值。这显然减轻了开发者的工作量。

    在两种情况下，DataReader被推荐使用：一、客户界面利用手写代码，或是没有用到数据绑定并且数据的更新是利用手写的SQL语句或是存储过程，这种情况下，DataReader将提供有效的访问相关数据的方法；二、一个需要查看数据库状态的的过程，但不需要实现诸如自动更新的功能。一些比较好的应用实例有向下拉菜单填充数据，以及产品编号的验证。

理解DataSets

    对于一些开发者来说，可能会对DataSets的使用颇有微词，但事实上，掌握它并不难。如果你对连接断开的RecordSet 模式，以及分层的RecordSet 的使用不陌生，并且乐于使用一些XML的话，那么利用DataSets 就没有什么困难了。

    可是有什么必要去花费时间去重新学习一种对象模型呢？
    理由之一是，ADO.NET 力推的断开连接模式（disconnected model）是非常有意义的。断开连接的数据伸缩性更高，因为这种模式对数据服务器的压力比较小。通过平行服务器对中间层进行扩展比对数据层进行扩展要容易的多，因此对于可扩展的应用程序来说，对数据层资源的使用要谨慎。断开连接模式将在这里得到应用。
    理由二，在数据管理方面，断开连接模式有非常广阔的前景。

    如果说有一种技术的发展比ADO还要迅速，这就是XML。XML解决了两个ADO所对的局限。
    1、XML更加适用与跨平台的的数据传输，因为它能够穿越防火墙。
    2、XML比ADO包括它的扩展ADO2.5描述的类型多。一个简单的例子，同样面对扁平的数据，普通的XML将比ADO显示出更高的效率。

    DataSets是XML与ADO结合的产物，它的一个重要的特点是与数据库或SQL无关。它只是简单的对数据表进行操作，交换数据或是将数据绑定到用户界面上。

    向DataSets中加入数据表的方法有两种：
    1、利用DataSetCommand对象，它可以以XML 的形式返回一个数据库查询的结果。在处理数据库更新的时候，这种方法与ADO的客户端光标基本相同，但提供更强的控制。
    2、直接对XML数据进行操作。DataSet 对象有对XML数据和Schemas进行读写的的方法，并且可以和XMLDataDocument对象（该对象是多用途XMLDocument类的继承）紧密的结合起来工作，它具有一些对扁平数据操作的特殊函数因此可以作为从DataSet 到通用XML DOM的桥梁。

    下面是一段使用DataSet的代码：
Dim dc As New ADODataSetCommand( _
   "select au_id, au_fname, au_lname from authors" & _
   " where au_lname = ‘Ringer’", strConnect)
Dim ds As New DataSet()

dc.FillDataSet(ds, "Authors")
MsgBox(ds.XmlSchema, , "XML Schema")
MsgBox(ds.XmlData, , "XML Data")

     以上的代码将一个‘AUTHORS’数据表填充到DataSet 对象实例 ds 中，并显示了XML数据和Schemas，请看两个显示结果：表1 和表2

     向DataSet中添加数据表的操作是非常容易的，请看下面的代码：

dc = New ADODataSetCommand( _
   "select * from titleauthor" & _
   " where title_id like 'PS%'", strConnect)

dc.FillDataSet(ds, "Titles")
MsgBox(ds.XmlData, , "XML Data")

表1显示XML Schema

表2显示DataSet 对象生成的数据

MT'S BLOG 2006-11-23 23:00 发表评论

点击不显示表格

MT'S BLOG — Thu, 23 Nov 2006 02:53:00 GMT

标题1

ABCDEFG

MT'S BLOG 2006-11-23 10:53 发表评论

SQL库连接

MT'S BLOG — Sun, 19 Nov 2006 10:06:00 GMT

Private Sub SQLM()
        Dim myconn As String = "server=(local);UID=sa;PWD=123456; database=tj"
        SqlConnection1 = New SqlClient.SqlConnection(myconn)
        'sqlCommand1 = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

        SqlDataAdapter1 = New SqlClient.SqlDataAdapter("select 分类1,id from 通信录1", SqlConnection1)
        SqlDataAdapter1.Fill(DSLIST11, "通信录1")
        DataGridView1.DataSource = DSLIST11.Tables(0)

End Sub

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
'OleDbDataAdapter1.SelectCommand = New OleDb.OleDbCommand("select top 5 * from 通信录1", OleDbConnection1)

        SqlDataAdapter1.SelectCommand = New SqlClient.SqlCommand("select top 5 * from 通信录1", SqlConnection1)
        DSLIST11.Clear()
        SqlDataAdapter1.Fill(DSLIST11, "通信录1")
    End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Call SQLM()

End Sub

MT'S BLOG 2006-11-19 18:06 发表评论

ASP.NET 初学VB.NET连接SQL数据库！

MT'S BLOG — Sun, 19 Nov 2006 09:49:00 GMT

<%@ Page Language="VB" Debug="true" trace="true" %>
<%@ Import NameSpace="System.Data" %>
<%@ Import NameSpace="System.data.SQLClient" %>

显示SQL数据库

MT'S BLOG 2006-11-19 17:49 发表评论

ADO.Net的Programmer Guide

MT'S BLOG — Sun, 19 Nov 2006 09:45:00 GMT

ADO.Net的Programmer Guide

創稿人	蔡爾榮	創稿日期	2002/11/15
最後修改人	蔡爾榮	最後修改日期	2003/02/03

版本：1.06.00.0007

本文以SQL Server .Net managed data provider為準，若使用OleDB連接Database，可以在Class name用OleDb取代Sql。

本文不會詳述ADO.Net的使用方式，僅提供一brief introduction，詳細說明請自行參閱ADO.Net的OnLine Help。

ADO.Net-System.Data

DataSet

類似SQL Server的Database，DataSet中可以含DataTable，DataRelation。

DataTable

類似SQL Server的Table，DataTable上可以有DataView，DataTable與DataTable間可以有DataRelation。

DataTable可以有PrimaryKey。

當DataTable.NewRow時，DataTable中Columns的Default就會跑至新增DataRow中了。

當DataTable.Rows.Add(row)，ADO.Net會檢查該Row是否違反Constraint。

There are several events that are raised by the DataTable object when a change is occurring in a record:

The ColumnChanging and ColumnChanged events are raised during and after each change to an individual column. The ColumnChanging event is useful when you want to validate changes in specific columns. Information about the proposed change is passed as an argument with the event.
The RowChanging and RowChanged events are raised during and after any change in a row. The RowChanging event is more general, in that it simply indicates that a change is occurring somewhere in the row; you do not know which column has changed.
By default, each change to a column therefore raises four events: first the ColumnChanging and ColumnChanged events for the specific column being changed, and then the RowChanging and RowChanged event. If multiple changes are being made to the row, the events will be raised for each change.

Note The data row's BeginEdit method turns off the RowChanging and RowChanged events after each individual column change. In that case, the event is not raised until the EndEdit method has been called, when the RowChanging and RowChanged events are raised just once.
The event you choose depends on how granular you want the validation to be. If it is important that you catch an error immediately when a column is changed, build validation using the ColumnChanging event. Otherwise, use the RowChanging event, which might result in catching several errors at once. Additionally, if your data is structured in such a way that the value of one column is validated based on the contents of another column then you should perform your validation during the RowChanging event.

DataView

類似SQL Server的View，DataView依附於DataTable，本身不存資料，資料存在DataTable中。

DataRow

DataTable中的一筆資料。

有RowState決定目前Row是新增刪除或修改。

具有double data buffer的功能。

DataRowCollection類似ADO.Recordset

The DataRowVersion informs you what version of a DataRow exists. Versions change under the following circumstances:

After calling the DataRow object's BeginEdit method, if you change the value, the Current and Proposed values become available.
After calling the DataRow object's CancelEdit method, the Proposed value is deleted.
After calling the DataRow object's EndEdit method, the Proposed value becomes the Current value.
After calling the DataRow object's AcceptChanges method, the Original value becomes identical to the Current value.
After calling the DataTable object's AcceptChanges method, the Original value becomes identical to the Current value.
After calling the DataRow object's RejectChanges, the Proposed value is discarded, and the version becomes Current.

DataRowVersion

當DataRow.HasVersion(DataRowVersion.Proposed) == true時，DataRow["DataCol"]會與DataRow["DataCol",DataRowVersion.Proposed]相同。

當DataRow.HasVersion(DataRowVersion.Proposed) == false時，DataRow["DataCol"]會與DataRow["DataCol",DataRowVersion.Current]相同。

DataRowView

DataView中的一筆資料。

DataColumn

記載DataTable中的Column Schema。

有AutoIncrement Column類似SQL Server Identity的功能。

有計算欄位的功能。

當DataColumn被Assign到某Table後，該DataColumn的MaxLength便無法修改。

DataRelation

類似SQL Server的Foreign key constrain，描述著DataTable與DataTable之間的關係。

SQLClient-System.Data.SqlClient

SqlDataAdapter

ADO.Net與Database溝通的Class。可將SQL Server上的Table讀進DataTable，亦可將DataTable的異動寫回Database。

SqlConnection

類似ADO.Connection。

SqlParameter

類似ADO.Parameter。

SqlCommand

類似ADO.Command

在使用SqlCommand.ExecuteScalar(含IDbCommand.ExecuteScalar)時須注意，當SqlCommand.CommandText的語法錯誤時，會throw Exception，但是若僅僅是型別有錯，是不會throw Exception的，但是該SqlConnection會自動被Rollback，且ExecuteScalar return null。

例如：CaseMap.MapID在資料庫的型別為int

sqlConnection1.Open();
IDbCommand SQLCommand = new SqlCommand("select count(*) from CaseMap where MapID='rwerwe'",sqlConnection1);
SQLCommand.ExecuteScalar(); // 不會throw Exception
sqlConnection1.Close();

sqlConnection1.Open();
IDbCommand SQLCommand = new SqlCommand("select count(*) from CaseMap where1 MapID='1'",sqlConnection1);
SQLCommand.ExecuteScalar();　// 會throw Exception
sqlConnection1.Close();

TQuarkLib.Net的TQuark.Lib.Data.DBHelp.ExecuteScalarMustHaveValue提供一Partial Solution。當IDbCommand.ExecuteScalar return null時，會throw exception，但是依然無法恢復Transaction的狀態，因為該Connection已經被sp_reset_connection了。

SqlDataReader

可自SQL Server一筆一筆資料讀取的class。

SqlDataAdapter.Update與Transaction的關係

當呼叫SqlDataAdapter.Update時，若發生Exception，因為Update是Process by Row by row的，理論上已經做完的Row其RowState應該已經AcceptChange。但是若後來SQL Rollback會造成State不符。

Read and Write BLOB Data by Using ADO.NET

待研究

TQuarkLib.Net的支援

DataHelp

提供一些有關DataSet，DataTable，DataView，DataRow間處理的utility。

DBHelp

提供一些有關DataSet，DataTable，DataView，DataRow與IDbCommand的處理utility。

SqlHelp

提供一些有關DataSet，DataTable，DataView，DataRow與SqlClient的處理utility。

DataSetTransactionControl

類似NestedTransactionControl，但是NestedTransactionControl是支援Connection，而DataSetTransactionControl是支援DataSet。

DataSetTransactionMonitor

類似DBTransactionMonitor，但是DBTransactionMonitor是支援Connection，而DataSetTransactionMonitor是支援DataSet。

MT'S BLOG 2006-11-19 17:45 发表评论

VB.NET操作SQL Server完全模块

MT'S BLOG — Sun, 19 Nov 2006 02:46:00 GMT

Module ModSql

Function GetIntByStr(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As String) As Integer

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where " & ParaName & "='" & ParaValue & "'"

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), 0, sqlCommand.ExecuteScalar)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return rowsAffected

End Function

Function GetIntByInt(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As Integer) As Integer

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where " & ParaName & "=" & ParaValue & ""

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), 0, sqlCommand.ExecuteScalar)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return rowsAffected

End Function

Function GetStrByStr(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As String) As String

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where " & ParaName & "='" & ParaValue & "'"

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As String = ""

sqlConnection.Open()

Try

rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), "", sqlCommand.ExecuteScalar)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return Trim(rowsAffected)

End Function

Function GetStrByInt(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As Integer) As String

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where " & ParaName & "=" & ParaValue & ""

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As String = ""

sqlConnection.Open()

Try

rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), "", sqlCommand.ExecuteScalar)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return Trim(rowsAffected)

End Function

Function GetBitByStr(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As String) As Boolean

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where " & ParaName & "='" & ParaValue & "'"

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As Boolean = False

sqlConnection.Open()

Try

rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), False, sqlCommand.ExecuteScalar)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return rowsAffected

End Function

Function GetBitByInt(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As Integer) As Boolean

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where " & ParaName & "=" & ParaValue & ""

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As Boolean = False

sqlConnection.Open()

Try

rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), False, sqlCommand.ExecuteScalar)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return rowsAffected

End Function

Function GetDateByInt(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As Integer) As DateTime

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where " & ParaName & "=" & ParaValue & ""

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As DateTime

sqlConnection.Open()

Try

rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), DateAdd(DateInterval.Year, -100, Date.Now), sqlCommand.ExecuteScalar)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return rowsAffected

End Function

Function GetDateByStr(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As String) As DateTime

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where " & ParaName & "='" & ParaValue & "'"

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As DateTime

sqlConnection.Open()

Try

rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), DateAdd(DateInterval.Year, -100, Date.Now), sqlCommand.ExecuteScalar)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return rowsAffected

End Function

Function DelByInt(ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As Integer) As Integer

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "DELETE FROM " & TableName & " WHERE " & ParaName & " =" & ParaValue

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteNonQuery

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return rowsAffected

End Function

Function DelByStr(ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As String) As Integer

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "DELETE FROM " & TableName & " WHERE " & ParaName & "='" & ParaValue & "'"

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteNonQuery

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return rowsAffected

End Function

Function DelBySQL(ByVal StrSQL As String) As Integer

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = StrSQL

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteNonQuery

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return rowsAffected

End Function

Function UpdateBitBySQL(ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As Boolean, ByVal ParaSQL As String) As String

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "UPDATE " & TableName & " SET " & DataFieldName & " = " & IIf(DataFieldValue = True, 1, 0) & " WHERE " & ParaSQL

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

sqlConnection.Open()

Try

sqlCommand.ExecuteNonQuery()

Return ""

Catch ex As Exception

Return ex.Message

Finally

sqlConnection.Close()

End Try

End Function

Function UpdateStrByInt(ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As String, ByVal Para As String, ByVal ParaValue As Integer) As Integer

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "UPDATE " & TableName & " SET " & DataFieldName & " = '" & DataFieldValue & "' WHERE " & Para & " = " & ParaValue

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteNonQuery

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return rowsAffected

End Function

Function UpdateStrByStr(ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As String, ByVal Para As String, ByVal ParaValue As String) As Integer

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "UPDATE " & TableName & " SET " & DataFieldName & " = '" & DataFieldValue & "' WHERE " & Para & " = '" & ParaValue & "'"

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteNonQuery

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return rowsAffected

End Function

Function UpdateIntByInt(ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As Integer, ByVal Para As String, ByVal ParaValue As String) As Integer

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "UPDATE " & TableName & " SET " & DataFieldName & " = " & DataFieldValue & " WHERE " & Para & " = " & ParaValue

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteNonQuery

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return rowsAffected

End Function

Function UpdateIntByStr(ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As Integer, ByVal Para As String, ByVal ParaValue As String) As Integer

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "UPDATE " & TableName & " SET " & DataFieldName & " = " & DataFieldValue & " WHERE " & Para & " = '" & ParaValue & "'"

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteNonQuery

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return rowsAffected

End Function

Function UpdateIntBySQL(ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As Integer, ByVal ParaSQL As String) As Integer

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "UPDATE " & TableName & " SET " & DataFieldName & " = " & DataFieldValue & " WHERE " & ParaSQL

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteNonQuery

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return rowsAffected

End Function

Function UpdateStrBySQL(ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As String, ByVal ParaSQL As String) As Integer

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "UPDATE " & TableName & " SET " & DataFieldName & " = '" & DataFieldValue & "' WHERE " & ParaSQL

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteNonQuery

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return rowsAffected

End Function

Function UpdateBySQL(ByVal TableName As String, ByVal SetSQL As String, ByVal ParaSQL As String) As Integer

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "UPDATE " & TableName & " SET " & SetSQL & " WHERE " & ParaSQL

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteNonQuery

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return rowsAffected

End Function

Function GetDataTableByFormat(ByVal vRecordNumber As Integer, ByVal ItemSQL As String, ByVal TableName As String, ByVal ParaSQL As String) As System.Data.DataTable

Dim RecordNumber As String

If vRecordNumber = 0 Then

RecordNumber = ""

Else

RecordNumber = "TOP " & vRecordNumber

End If

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "SELECT " & RecordNumber & " " & ItemSQL & " FROM " & TableName & " where " & ParaSQL

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim dataAdapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(sqlCommand)

Dim dataSet As System.Data.DataSet = New System.Data.DataSet

Try

dataAdapter.Fill(dataSet)

Return dataSet.Tables(0)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

End Function

Function GetDataTableBySQL(ByVal SuperSQL As String) As System.Data.DataTable

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(SuperSQL, sqlConnection)

Dim dataAdapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(sqlCommand)

Dim dataSet As System.Data.DataSet = New System.Data.DataSet

Try

dataAdapter.Fill(dataSet)

Return dataSet.Tables(0)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

End Function

Function GetMaxField(ByVal TableName As String, ByVal FieldName As String) As Integer

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "SELECT MAX(" & FieldName & ") AS MaxField FROM " & TableName

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), 0, sqlCommand.ExecuteScalar)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return rowsAffected

End Function

Function GetMinField(ByVal TableName As String, ByVal FieldName As String) As Integer

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "SELECT Min(" & FieldName & ") AS MaxField FROM " & TableName

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As Integer = 0

sqlConnection.Open()

Try

rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), 0, sqlCommand.ExecuteScalar)

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return rowsAffected

End Function

Function GetFieldCount(ByVal TableName As String, ByVal ParaSQL As String) As Integer

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "SELECT COUNT(*) AS FieldCount FROM " & TableName & " where " & ParaSQL

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As Integer

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteScalar

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return rowsAffected

End Function

Function GetFieldSumByInt(ByVal TableName As String, ByVal FieldName As String, ByVal ParaSQL As String) As Integer

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "SELECT isnull(sum(" & FieldName & "),0) AS CountNumber FROM " & TableName & " where " & ParaSQL

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As Integer

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteScalar

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return rowsAffected

End Function

Function GetFieldSumByDec(ByVal TableName As String, ByVal FieldName As String, ByVal ParaSQL As String) As Decimal

Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

Dim queryString As String = "SELECT isnull(sum(" & FieldName & "),0) AS CountNumber FROM " & TableName & " where " & ParaSQL

Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

Dim rowsAffected As Decimal

sqlConnection.Open()

Try

rowsAffected = sqlCommand.ExecuteScalar

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

Finally

sqlConnection.Close()

End Try

Return rowsAffected

End Function

Function ChgFieldByLeftWord(ByVal TableName As String, ByVal FieldName As String, ByVal ParaWord As String, ByVal NewWord As String) As Integer

Try

Return UpdateBySQL(TableName, FieldName & "='" & NewWord & "'+right(" & FieldName & ",len(" & FieldName & ")-" & Len(ParaWord) & ")", "Len(" & FieldName & ")>=" & Len(ParaWord) & " and left(" & FieldName & "," & Len(ParaWord) & ")='" & ParaWord & "'")

Catch ex As Exception

Throw New NotSupportedException(ex.Message)

End Try

End Function

'数据库连接串

Private Function GetConn() As String

Return "server=localhost;database=pubs;uid=sa;pwd="

End Function

End Module

MT'S BLOG 2006-11-19 10:46 发表评论

网上抄的ACCESS连接模块，未验证

MT'S BLOG — Sun, 19 Nov 2006 02:40:00 GMT

Imports System
Imports System.Data
Imports System.Drawing
Imports System.Data.OleDb

Module ConnToDB

Public StartPath As String = Application.StartupPath & "\LiberySystem"
Public Connstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;"
Public ConnectStr As String = Connstr & "Data Source=" & StartPath
Public DataAdapter As OleDbDataAdapter
Public DataConnection As OleDbConnection
Public DataSet As DataSet

Public Function GetDataFromDB(ByVal sqlstr As String) As DataSet
Try
DataConnection = New OleDbConnection
DataConnection.ConnectionString = ConnectStr
DataAdapter = New OleDbDataAdapter(sqlstr, DataConnection)
DataSet = New DataSet
DataSet.Clear()
DataAdapter.Fill(DataSet)
DataConnection.Close()

Catch
MsgBox(Err.Description)
End Try
If DataSet.Tables(0).Rows.Count > 0 Then
Return DataSet
Else
Return Nothing
End If
End Function

Public Function UpdateDataBase(ByVal sqlstr As String) As Boolean
Dim sqlconn As OleDbConnection
Try
Dim cmdTable As OleDbCommand = New OleDbCommand(sqlstr, sqlconn)
cmdTable.CommandType = CommandType.Text
sqlconn.Open()
cmdTable.ExecuteNonQuery()
sqlconn.Close()

Catch
MessageBox.Show(Err.Description)

End Try
Return True
End Function

Public Class Dictionary
Public ISBN As String
Public FineBase As Double
Public FineMulti As Double
Public FineFactor As Double
End Class

Public Structure OnceLends
Public ReadStyle As String
Public LendBooks As Integer
End Structure
Public Structure BorrowType
Public Type As String
Public Days As Integer
End Structure

End Module

MT'S BLOG 2006-11-19 10:40 发表评论

简单手工连接数据库

MT'S BLOG — Sun, 12 Nov 2006 13:16:00 GMT

Imports System.Data.OleDb

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
DSLIST11.Clear()

OleDbDataAdapter1.Fill(DSLIST11, "通信录1")

End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.BindingContext(DSLIST11, "通信录1").Position -= 1
        showp()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.BindingContext(DSLIST11, "通信录1").Position += 1
        showp()
    End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim myconn As String = "Provider=Microsoft.Jet.OLEDB.4.0;data source=d:\mydoc\Visual Studio 2005\Projects\PRJ_dbCONN\PRJ_dbCONN\test1.mdb;"

        OleDbConnection1 = New OleDb.OleDbConnection(myconn) '¹

        OleDbDataAdapter1 = New OleDbDataAdapter("select 分类1,id from 通信录1", OleDbConnection1) '2

        OleDbDataAdapter1.Fill(DSLIST11, "通信录1") '3

        DataGridView1.DataSource = DSLIST11.Tables(0) '4

'TextBox2.Text = Me.Name

    End Sub
    Private Sub showp()
        Dim iCnt As Integer
        Dim iPos As Integer
        iCnt = Me.BindingContext(DSLIST11, "通信录1").Count
        If iCnt = 0 Then
            TextBox2.Text = "(No records)"
        Else
            iPos = Me.BindingContext(DSLIST11, "通信录1").Position + 1
            TextBox2.Text = iPos.ToString & " of " & iCnt.ToString
        End If

End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        OleDbDataAdapter1.SelectCommand = New OleDb.OleDbCommand("select top 5 * from 通信录1", OleDbConnection1)
        DSLIST11.Clear()
        OleDbDataAdapter1.Fill(DSLIST11, "通信录1")
    End Sub

Private Sub OleDbConnection1_InfoMessage(ByVal sender As System.Object, ByVal e As System.Data.OleDb.OleDbInfoMessageEventArgs) Handles OleDbConnection1.InfoMessage

End Sub
End Class

MT'S BLOG 2006-11-12 21:16 发表评论

Fill: SelectCommand.Connection 属性尚未初始化

MT'S BLOG — Sun, 12 Nov 2006 07:33:00 GMT

Public Class Form1
    Inherits System.Windows.Forms.Form
    'Dim myconn As String = "Provider=Microsoft.Jet.OLEDB.4.0;data source=D:\work\gdC\test.mdb;"
    'Dim ADC As OleDb.OleDbConnection = New OleDb.OleDbConnection(myconn)

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        DslisT11.Clear()
        OleDbDataAdapter1.Fill(DslisT11, "通信录") 'ERROR -->Fill: SelectCommand.Connection 属性尚未初始化

End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.BindingContext(DslisT11, "通信录").Position -= 1
        showp()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.BindingContext(DslisT11, "通信录").Position += 1
        showp()
    End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
TextBox2.Text = Me.Name

    End Sub
    Private Sub showp()
        Dim iCnt As Integer
        Dim iPos As Integer
        iCnt = Me.BindingContext(DslisT11, "通信录").Count
        If iCnt = 0 Then
            TextBox2.Text = "(No records)"
        Else
            iPos = Me.BindingContext(DslisT11, "通信录").Position + 1
            TextBox2.Text = iPos.ToString & " of " & iCnt.ToString
        End If

End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        OleDbDataAdapter1.SelectCommand = New OleDb.OleDbCommand("select top 5 * from 通信录", OleDbConnection1)
        DslisT11.Clear()
        OleDbDataAdapter1.Fill(DslisT11, "通信录")
    End Sub
End Class

HOW TO
refer to http://doc.readmen.com/1/141059.shtml as the follow

Dim mySelectText As String = "SELECT CustomerID, CompanyName FROM CUSTOMERS"

Dim myConnString As String = "Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind"

Dim custDA As OleDbDataAdapter = New OleDbDataAdapter(mySelectText, myConnString)

Solution: add the command string for OleDbDataAdapter1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        TextBox2.Text = Me.Name
        OleDbDataAdapter1 = New OleDb.OleDbDataAdapter("select * from 通信录", OleDbConnection1)
    End Sub

[FINISH]

MT'S BLOG 2006-11-12 15:33 发表评论