自学WEB开发第五天-基于VB和ASP.NET：使用ADO，数据库的增删改查

  • 使用`Command`对象和`DataReader`对象
  • 使用`DataAdapter`对象和`DataSet`对象
  • ADO.NET数据库操作总结
  • 接下来这里MARK一些操作方法：
  • 继续MARK，关于数据行状态问题

数据库连接建立后，就是最基础的操作，增删改查了。
这里，我重新建立个新的项目，用来测试。然后建立数据库连接： MyCon。代码如下： 

        Dim connstr As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\xxx\xxx\database\test.mdf;Integrated Security=True;Connect Timeout=30"
        Dim MyCon As New SqlConnection(connstr)
        MyCon.Open()

接下来研究许久，发现增删改查有两种方式，一种是直接运行SQL命令，然后读取数据库，即使用Command对象和DataReader对象。另一种是将数据搬运填充至本地表，然后进行操作，再返写回数据库，即使用DataAdapter对象和DataSet对象。

使用Command对象和DataReader对象

 '新建数据库命令对象（Command） cmd 和数据库操作命令(SQL 语句) sqlcmd
Dim sqlcmd As String = "select * from tablename"        '这里建立SQL查询语句
Dim cmd As New SqlCommand(sqlcmd, MyCon)    '在数据库连接MyCon里执行SQL语句
'新建数据读取对象（DataReader）dr
Dim dr As SqlDataReader         '不能new,应用程序不直接创建IDataReader接口的实例，而是创建继承 IDataReader的类的实例
dr = cmd.ExecuteReader()		'ExecuteReader方法返回一个DataReader对象

至此，对数据库MyCon的查询就建立完成了，并返回了查询结果到DataReader对象中。可以通过Read方法读取，每次Read读取一条记录，即每次一行。comunls1到comunls4为列名称

		Dim strDisplay As String 	'设置个显示的字符串
        While dr.Read()		'读取第一行数据
            Response.Write("<br>")	'换个行
            strDisplay = ""
            strDisplay = strDisplay & dr("columns1").ToString & Space(5)
            strDisplay = strDisplay & dr("columns2").ToString & Space(5)
            strDisplay = strDisplay & dr("columns3").ToString & Space(5)
            strDisplay = strDisplay & dr("columns4").ToString & Space(5)
            Response.Write(strDisplay)  '显示输出结果
        End While
        MyCon.Close() '因为DataReader对象具有独占性，在已经打开DataReader的情况下，将不能对Connection进行任何操作，所以用完了它就应及时调用Close()方法关闭。

调试一下，看看结果，OK，通过。

这个是最简单的查询，一个结果集可能有多个记录，每个记录可能有多个列值，用ExecuteReader方法返回DataReader对象，就是返回所有结果。如果ExecuteReader方法未查询到数据，则DataReader.Read()方法会返回False。

如果SQL查询是用于聚合运算，比如求有多少记录，总计数是多少之类，结果只返回一个值，可以使用ExecuteScalar方法，返回首行首列的一个值，其他忽略。如果ExecuteScalar方法在数据库里未查询到，则返回Nothing。如果查询到，但是数据库中的值是Null，则返回DBNull.Value。

		MyCon.Open()	'之前因为DataReader对象的独占性原因关闭了，这里重新建立连接。
        Dim strValue As String 	'定义个显示输出的变量，只是在名称上区别于strDisplay
        strValue = cmd.ExecuteScalar().ToString	'ExecuteScalar方法返回第一个值
        Response.Write("<br>")		'换个行
        Response.Write(strValue)		’显示输出结果

以上是增删改查里的查，接下来就是增删改了。增删改和查大体类似，就是利用Command对象的CommandText属性，来改变相应的SQL语句就行。只是没有返回结果集。这里使用ExecuteNonQuery方法。ExecuteNonQuery方法是非查询命令，主要用来执行insert,update,delete和那些没有返回结果集的SQL语句，并返回执行命令后影响的行数。如果delete,update对应的记录不存在，则返回0；如果出错，则返回-1。注意：因为这个用户不知道成功与否，所以要加入一些提示或再次显示原数据库，以便了解执行情况。

            cmd.CommandText = "update tablename set columns1 = 33 where columns2 like N'%赵%'"	'更改Command对象执行的SQL语句
            cmd.ExecuteNonQuery()	'执行Command

这里在调试时候有个要注意的地方：在SQL语句中使用中文，刚开始时候直接就是’%赵%'这样，可是结果不对，应该是找不到这个值。网上查了下，在单引号前加个大写的N就可以了，即N%赵%这样。原因貌似应该是中文字符集的问题。

通过Command对象的CommandText属性，使用不同的SQL语句，配合ExecuteReader、ExecuteScalar、ExecuteNonQuery方法，就可以实现数据库的增删改查。

我们可以事务处理，一次处理多条命令。在ADO.NET中，事务是个批处理过程，即即要么全部都成功执行，要么全部失败。（SQL自动会控制它）。

ADO中，使用Connection和Transaction对象来控制事务，事务顺序包括：
一、调用Connecton对象的BeginTransaction方法来标记事务的开始。
二、将Transaction对象分配给要执行的Command的Transaction属性。
三、执行所需的SQL命令。
四、调用Transaction对象的Commit方法来完成事务，或调用RollBack方法来取消事务。

        Dim trans As SqlTransaction     '建立事务对象，这里不能New
        trans = MyCon.BeginTransaction      '第一步，标记事务的开始。这句必须在下句前，相当于实例事务对象
        cmd.Transaction = trans         '第二步，分配给Command对象的Transaction属性
        cmd.Connection = MyCon		'Command对象要操作的数据库连接，其实前面已经有了，
        
        Try
        	'第三步，事务内容，即需要执行的SQL命令，都有：
            cmd.CommandText = "update tablename set columns1 = 33 where columns2 like N'%赵%'"	'第一条SQL指令
            cmd.ExecuteNonQuery()	'执行指令
            cmd.CommandText = "insert into tablename values (3,N'张三',N'上海',9000)"	'第二条SQL指令
            cmd.ExecuteNonQuery()	'执行指令
            cmd.CommandText = "delete from tablename where columns2 = N'李四'"	'第三条SQL指令
            cmd.ExecuteNonQuery()	'执行指令
            '以上是事务的内容
            trans.Commit()         '第四步，完成事务，就是执行的操作
            Response.Write("<br>事务提交执行成功")
        Catch ex As Exception
            trans.Rollback()       '第四步，回滚事务，上面若有失败，则全部恢复执行事务前的状态。
            Response.Write("<br>事务提交失败，所有任务恢复原状。")
        End Try

以上就是使用Command对象和DataReader对象进行增删改查

使用DataAdapter对象和DataSet对象

使用Command对象和DataReade对象是通过SQL指令直接操作数据库，对数据库的改动不够直观，所以可以使用DataAdapter对象和DataSet对象来进行数据库操作。

DataAdapter对象是数据适配器，从数据源将需要的数据搬运到本地内存中来填充DataSet对象，或者从本地内中数据搬运回到数据源中。这两者的数据可以是相同，也可是不同，DataAdapter会自动匹配。从数据源到本地内存，然后再填充就用Fill方法。从本地内存到数据源，进行数据库的更新用Update方法。DataAdapter对象要配合DataSet对象或者DataTable对象使用。

        Dim da As New SqlDataAdapter("select * from tablename", MyCon)   '建立DataAdapter对象，即确定需要搬运的数据
        Dim ds As New DataSet()     '新建本地DataSet对象（不是Sql对象），准备装入DataAdapter搬运过来的数据
        da.Fill(ds, "mytable")      '将数据填入DataSet对象ds中的"mytable"表内，注：是新填充，如果DataSet中的表内有数据，则填充部分在原有数据后。

这时候，DataSet对象就相当于一个本地内存中的数据库，内容就是DataAdapter对象搬运过来的数据。也有用DataTable对象的，语法为：

DataAdapter.Fill(DataTable)

DataSet对象和DataTable对象的区别在于，DataTable对象相当于一个数据表，而DataSet对象则可以存储多个数据表、视图等。接下来，对DataSet对象里的数据进行操作就行了。

新增数据：

		'新增数据
        Dim Drow As DataRow     '定义行变量
        Drow = ds.Tables("mytable").NewRow   '行变量是新的一个行
        '将新行中各列赋值
		Drow("columns1") = 10
		Drow("columns2") = "王五"
		Drow("columns3") = "北京"
		Drow("columns4") = 15000
		'将改过的行新加至DataSet对象的数据表中
		ds.Tables("mytable").Rows.Add(Drow)  '新行内容加入到表中，也可以写成ds.Tables("mytable").Rows.InsertAt(Drow,2)，即在第2行后插入行。
		'更新数据库
        Try
            Dim cmdb As New SqlCommandBuilder(da)  '和数据库通信，本地内存有数据要更新回去
            da.Update(ds, "mytable")               '完成通信，本地内存里的数据更新回数据库
            Response.Write("<br>数据更新完成")
        Catch ex As Exception
            Response.Write("<br>" + ex.ToString)
        End Try

这里要注意的是，在使用Dataset对象进行增删改查时，实际上是对DataSet对象的4个属性分别定义相应的SQL语句来完成的。比如，对SelectCommand对象定义Select语句，对DeleteCommand对象定义Delete语句等。这样十分麻烦，可以使用CommandBuilder对象来代替。所以在更新数据库之前建立CommandBuilder对象，对DataAdapter对象进行操作。

删除数据：

        Dim RowD As DataRow     '定义行变量
        For i As Int16 = 0 To ds.Tables("mytable").Rows.Count - 1	'DataSet对象的行号是从0开始
            RowD = ds.Tables("mytable").Rows(i)		'将DataSet对象里的第i行数据给DataRow
            If RowD("id"） = 3 Then				'DataRow里的某列符合要求
                ds.Tables("mytable").Rows(i).Delete()	'删除对应行
            End If
        Next i

这样就完成了对DataSet对象中的数据行的删除工作。需要注意的是，使用了Delete方法删除行，而不是使用Remove方法，因为使用Remove方法处理数据是直接删除，而使用Delete方法则并没有直接删除数据，而是将其状态标记成了“Deleteed”，等待删除。同样，使用Add方法增加时，也是标记了“Added”。使用Update时没有标记的（或者说标记还是未修改UnChanged）不进行操作。如果想将标记的数据进行更新应用（UnChanged），可以使用DataSet的AcceptChanges方法。

以上数据删除后，可以进行更新数据库操作，和新增数据时的更新数据一样。需要注意的是，DataSet对象是本地内存中的数据，而再次使用Fill方法进行更新前，需要使用DataSet.Tables().Clear()方法进行清空数据，否则Fill方法就是将填充的数据附加在现有数据之后。

修改数据和查询数据
修改和查询类似于新增的代码，都是对数据集（DataSet）中的数据表（DataTable）里的数据行（DataRow）进行操作。

至此，数据库基本的增删改查就结束了。

ADO.NET数据库操作总结

数据库操作首先建立数据连接Connection,然后使用Open方法打开连接。接下来对数据库连接的操作分为利用Command进行直接操作和利用DataAdapter将数据搬运到本地操作再更新数据库两种方法。

Command对象需要执行的SQL命令和目标数据库连接，使用ExecuteReader、ExecuteScalar、ExecuteNonQuery方法执行，并将执行结果返回到结果集DataReader或者字符串里。可以使用Read方法将查询结果从结果集中一次读出一行。可以使用Transaction建立事务，批处理多条SQL命令。建立Transation是，使用Connection的BeginTransaction方法标记任务的开始。通过更改Command的CommandText属性执行不同的SQL命令，并通过Execute相关方法确定执行命令并返回结果。确定所有的批处理事务后，使用Transation的Commit方法执行批处理。如果有执行失败的指令，则通过RollBack方法回滚全部事务。

DataAdapter直接确定需要查询的SQL命令和目标数据库连接。确定好数据后，搬运至本地，并通过Fill方法填充至DataSet或DataTable中，再进行增删改查的操作。操作完后，通过Update方法更新数据库。需注意的有：1、 Fill方法是将DataAdapter的数据以附加方式填充至DataSet，即在原有数据后增加。2、 Update方法更新时，是按照数据行的RowState属性进行更新的。

接下来这里MARK一些操作方法：

一、ROWS.ADD和ROWS.INSERTAT方法
用法为：DataTable.Rows.Add(DataRow)和DataTbale.Rows.InsertAt(DataRow , pos)
Add和InsertAt方法都可以把新行添加到DataTable，区别就是Add是加到最后，InsertAt是插入到指定数据行号(pos)之后。另，参数里的DataRow不能是属于另一张表的，例如dt2.add(dt1.rows(0))会报错，如需要则参考ImportRow的用法。

二、DATATABLE.COPY和DATATABLE.CLONE方法
用法为：DataTable dt2 = DataTable dt1.Copy()和DataTable dt2 = DataTable dt1.Clone()
Copy和Clone都是复制表，区别在于Copy连带表中的数据（行）一起复制，而Clone只复制数据结构（列）。

三、DATATABLE.IMPORTROW方法
用法为：DataTable dt2.ImportRow(DataRow dt1.row)
即将表dt1中的行复制到dt2中最后。这里一次复制一行，需要复制多条数据需使用循环。另外可以使用DataTable dt2.Rows.Add(DataRow dt1.row.ItemArray)。区别在于使用ImportRow方法需要两表结构一致，否则复制过来的数据将是null，而Add方法直接调用数组，并不管表结构，但是需注意的是使用Add调用数组时，dt2的列数不能比dt1少，即不能有溢出。

另使用ItemArry属性复制时，实际上只是指针操作，并没有复制DataRow实例，所以在新、旧表中对此行进行操作效果是一样的。而使用ImportRow方法进行复制则是复制实例。

使用ImportRow时，保留任何属性设置以及初始值和当前值。但是当DataRow的RowState属性为Detached时无法复制

继续MARK，关于数据行状态问题

Datatable中Rows都一个RowState属性，共有如下几种：
1,Detached该行已被创建，但不属于任何DataRowCollection。DataRow在以下情况下立即处于此状态：创建之后添加到集合中之前（.NewRow()），或从集合中移除之后。
2,Unchanged该行自上次调用AcceptChanges以来尚未更改。
3,Added该行已添加到DataRowCollection中，AcceptChanges尚未调用。
4,Deleted该行已通过DataRow的Delete方法被删除，AcceptChanges尚未调用。
5,Modified该行已被修改，AcceptChanges尚未调用。

更多推荐

自学WEB开发第五天-基于VB和ASP.NET：使用ADO.net,数据库的增删改查