å è½½äº DataSet åï¼æ¨å¯ä»¥ä¿®æ¹æ°æ®ï¼DataSet å°è·è¸ªä¿®æ¹ãå¯å° DataSet 对象è§ä¸ºä»æ°æ®åºä¸æ£ç´¢åºçç¼åäºå
åä¸çæ°æ®ï¼ç±è¡¨éåãå
³ç³»å约æç»æã
è¥è¦æ´æ° DataSet 并å°è¿äºæ´æ°ååæ°æ®åºï¼è¯·æç
§ä¸åæ¥éª¤æä½ï¼
æå¼ Microsoft Visual Studio .NETã
å¨ Visual Basic .NET ä¸æ°å»ºæ§å¶å°åºç¨ç¨åºãé»è®¤æ
åµä¸ï¼Visual Studio å建ä¸ä¸ªâéæ模åâåä¸ä¸ªç©ºç Main() è¿ç¨ã
ç¡®ä¿é¡¹ç®å
æ¬ä¸ä¸ªå¯¹ System å System.Data å½å空é´çå¼ç¨ãå¨ SystemãSystem.Data å System.Data.SqlClient å½å空é´ä¸ä½¿ç¨ Imports è¯å¥ï¼è¿æ ·ï¼å¨åé¢ç代ç ä¸å°±ä¸éè¦ä»è¿äºå½å空é´ä¸éå®å£°æãå¿
é¡»å¨ä»»ä½å
¶ä»å£°æä¹å使ç¨è¿äºè¯å¥ã
Imports System
Imports System.Data
Imports System.Data.SqlClient
å¨ä¿®æ¹æ°æ®å¹¶å°æ´æ¹ååæ°æ®åºä¹åï¼å¿
é¡»å°è¯¥ä¿¡æ¯å è½½å° DataSet ä¸ãæå
³è¯¦ç»è¿ç¨ï¼è¯·åè§ 301216. 为é¿å
éå¤ï¼å°ä¸è¯¦ç»æä¾è¯¥æ¥éª¤ä¸ç代ç ã
ä¸é¢ä»£ç ä¸çè¿æ¥å符串æåä½äºæ¬å°è®¡ç®æºï¼æè¿è¡è¿äºä»£ç ç计ç®æºï¼ä¸ç SQL Serverãå¦æéè¦çè¯ï¼è¯·ç¨æ¨èªå·±ç设置æ¿æ¢è¯¥å符串ãæ»ä¹ï¼å
å建è¿æ¥ï¼ç¶åå建æ°æ®éé
å¨ï¼è¯¥éé
å¨ç¨äºå°æ°æ®å¡«å
å° DataSet ä¸ã
注æï¼å¨æ¬ææä¾ç示ä¾ä»£ç ä¸ï¼æ¨å¿
é¡»å° UID=UserName å Password=StrongPassword æ´æ¹ä¸ºæ£ç¡®çå¼ã请确ä¿è¯¥ç¨æ· ID å
·æå¨æ°æ®åºä¸æ§è¡æ¤æä½æéçéå½æéã
Dim sConnectionString As String
' Modify the following code to correctly connect to your SQL Server.
sConnectionString = "Password=StrongPassword;User ID=UserName;" & _
"Initial Catalog=pubs;" & _
"Data Source=(local)"
Dim objConn As New SqlConnection(sConnectionString)
objConn.Open()
' Create an instance of a DataAdapter.
Dim daAuthors As _
New SqlDataAdapter("Select * From Authors", objConn)
' Create an instance of a DataSet, and retrieve data from the Authors table.
Dim dsPubs As New DataSet("Pubs")
daAuthors.FillSchema(dsPubs, SchemaType.Source, "Authors")
daAuthors.Fill(dsPubs, "Authors")
ç°å¨å·²ç»å è½½äºæ°æ®ï¼æ¨å¯ä»¥å¯¹å
¶è¿è¡ä¿®æ¹ãæ·»å è¡ï¼æè®°å½ï¼æå¤ç§æ¹æ³ã该代ç 示ä¾ä½¿ç¨ä¸ä¸ªä¸æ¥å¼è¿ç¨ï¼
å°ä»¥ä¸ä»£ç ç²è´´å°ç¬¬ 4 æ¥ä¸ç代ç ä¹åï¼
'*****************
'BEGIN ADD CODE
' Create a new instance of a DataTable.
Dim tblAuthors As DataTable
tblAuthors = dsPubs.Tables("Authors")
Dim drCurrent As DataRow
' Obtain a new DataRow object from the DataTable.
drCurrent = tblAuthors.NewRow()
' Set the DataRow field values as necessary.
drCurrent("au_id") = "993-21-3427"
drCurrent("au_fname") = "George"
drCurrent("au_lname") = "Johnson"
drCurrent("phone") = "800 226-0752"
drCurrent("address") = "1956 Arlington Pl."
drCurrent("city") = "Winnipeg"
drCurrent("state") = "MB"
drCurrent("contract") = 1
'Pass that new object into the Add method of the DataTable.Rows collection.
tblAuthors.Rows.Add(drCurrent)
MsgBox("Add was successful.")
'END ADD CODE
ä» DataTable è·åæ°ç DataRow 对象ã
æ ¹æ®éè¦è®¾ç½® DataRow å段å¼ã
å°æ°çå¯¹è±¡ä¼ éç» DataTable.Rows éåç Add æ¹æ³ã
è¥è¦ç¼è¾ç°æè¡ï¼è¯·è·åç¸åºç DataRow 对象ï¼ç¶å为ä¸åæå¤åæä¾æ°å¼ãå¿
é¡»å
æ¾å°æ£ç¡®çè¡ï¼ç±äºæ¨å è½½äºè¡¨çæ¶æåæ°æ®ï¼å¨ç¬¬ 4 æ¥ä¸å¯¹ FillSchema çè°ç¨ï¼ï¼å æ¤è¿ä¸è¿ç¨é常ç®åãæäºæ¶æï¼è¡¨å°±ç¥éåªä¸ªåæ¯å®ç主é®ï¼åæ¶Rows éåç Find æ¹æ³ä¹å°±å¯ç¨äºã
Find æ¹æ³è¿å DataRow 对象ï¼å¹¶ä¸å
¶ä¸»é®ä¸æäºä¸ä¸ªå
·ä½çå¼ï¼å¨æ¬ä¾ä¸ä¸º au_idï¼ãå¨æäº DataRow ä¹åï¼å¯å¯¹åè¿è¡ä¿®æ¹ãæ¨ä¸å¿
å
è£
BeginEdit å EndEdit ä¸çä¿®æ¹ï¼ä½å
è£
å¯ç®å DataSet å¿
é¡»å®æçå·¥ä½ï¼å¹¶è®© DataSet å¯ä»¥å¨è°ç¨ EndEdit çåæ¶æ§è¡å
¶éªè¯æ£æ¥ãå°ä»¥ä¸ä»£ç ç²è´´å° ADD 代ç ä¹åï¼
'*****************
'BEGIN EDIT CODE
drCurrent = tblAuthors.Rows.Find("213-46-8915")
drCurrent.BeginEdit()
drCurrent("phone") = "342" & drCurrent("phone").ToString.Substring(3)
drCurrent.EndEdit()
MsgBox("Record edited successfully")
'END EDIT CODE
è¦ç¨ææè¿äºæ´æ¹æ¥æ´æ°åå§æ°æ®åºï¼å¯å° DataSet ä¼ éå° DataAdapter 对象ç Update æ¹æ³ã
ä¸è¿ï¼å¨è°ç¨ Update ä¹åï¼å¿
é¡»å
设置 DataAdapter 对象ç InsertCommandãUpdateCommand åDeleteCommand å±æ§ãå¯æå¨ç¼å SQL 并ç¨ç¸åºç SqlCommand 对象填å
è¿ä¸ä¸ªå±æ§ï¼ä½ä¹å¯ä»¥ä½¿ç¨ Visual Studio .NET èªå¨çæè¿ä¸ä¸ªå½ä»¤ã
è¥è¦å¨éè¦æ¶çææéçå½ä»¤ï¼å¿
é¡»å建 SqlCommandBuilder 对象çå®ä¾å¹¶ä½¿ç¨è¯¥æé å½æ°ä¸ç DataAdapterãå¦ææ³ä½¿ç¨æ¤æ¹æ³ï¼å¨ä»¥ä¸ä»£ç 示ä¾ä¸ééï¼ï¼æ¨ç表å¿
é¡»æ主é®ä¿¡æ¯ãè¦è®¿é®ä¸»é®ä¿¡æ¯ï¼å¯è°ç¨ FillSchemaï¼ç¶åå°DataAdapter ç MissingSchemaAction å±æ§è®¾ç½®ä¸º AddWithKeyï¼æå¨ä»£ç ä¸æå¨è®¾ç½®ä¸»é®ãå°ä»¥ä¸ä»£ç ç²è´´å° EDIT 代ç ä¹åï¼
'*****************
'BEGIN SEND CHANGES TO SQL SERVER
Dim objCommandBuilder As New SqlCommandBuilder(daAuthors)
daAuthors.Update(dsPubs, "Authors")
MsgBox("SQL Server updated successfully" & chr(13) & "Check Server explorer to see changes")
' END SEND CHANGES TO SQL SERVER
è¦å®å
¨å é¤ä¸è¡ï¼å¯ä½¿ç¨ DataRow 对象ç Delete æ¹æ³ã请注æï¼Rows éåå
å« Remove å RemoveAt 两个æ¹æ³ï¼å®ä»¬ä¼¼ä¹å é¤äºè¡ï¼ä½å®é
ä¸åªæ¯å°è¡ä»éåä¸ç§»é¤ãåªæ Delete æ¹æ³æä¼å°å é¤ç»æååæºæ°æ®åºä¸ãå°ä»¥ä¸ä»£ç ç²è´´å° SEND CHANGES TO SQL SERVER 代ç ä¹åï¼
'*****************
'BEGIN DELETE CODE
drCurrent = tblAuthors.Rows.Find("993-21-3427")
drCurrent.Delete()
MsgBox("Record deleted successfully")
'END DELETE CODE
å°è¿äºæ´æ¹åéå° SQL Server 以移é¤æ©å
æ·»å çè®°å½ãå°ä»¥ä¸ä»£ç ç²è´´å° DELETE 代ç ä¹åï¼
'*****************
' CLEAN UP SQL SERVER
daAuthors.Update(dsPubs, "Authors")
MsgBox("SQL Server updated successfully" & Chr(13) & Chr(13) & "Check Server Explorer to see changes")
ä¿å项ç®ã
å¨è°è¯èåä¸åå»å¯å¨è¿è¡è¯¥é¡¹ç®ã请注æï¼å°åºç°å 个æ¶æ¯æ¡ï¼å®ä»¬æ示代ç çæ§è¡è¿åº¦å¹¶è®©æ¨è½å¤å¨æ§è¡è¿ç¨ä¸æ¥çæ°æ®çå½åç¶æã
å®æ´ä»£ç å表
Imports System
Imports System.Data
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim sConnectionString As String
' Modify the following code to correctly connect to your SQL Server.
sConnectionString = "Password=StrongPassword;User ID=UserName;" & _
"Initial Catalog=pubs;" & _
"Data Source=(local)"
Dim objConn As New SqlConnection(sConnectionString)
objConn.Open()
' Create an instance of a DataAdapter.
Dim daAuthors As _
New SqlDataAdapter("Select * From Authors", objConn)
' Create an instance of a DataSet, and retrieve data from the Authors table.
Dim dsPubs As New DataSet("Pubs")
daAuthors.FillSchema(dsPubs, SchemaType.Source, "Authors")
daAuthors.Fill(dsPubs, "Authors")
'*****************
'BEGIN ADD CODE
' Create a new instance of a DataTable
Dim tblAuthors As DataTable
tblAuthors = dsPubs.Tables("Authors")
Dim drCurrent As DataRow
' Obtain a new DataRow object from the DataTable.
drCurrent = tblAuthors.NewRow()
' Set the DataRow field values as necessary.
drCurrent("au_id") = "993-21-3427"
drCurrent("au_fname") = "George"
drCurrent("au_lname") = "Johnson"
drCurrent("phone") = "800 226-0752"
drCurrent("address") = "1956 Arlington Pl."
drCurrent("city") = "Winnipeg"
drCurrent("state") = "MB"
drCurrent("contract") = 1
'Pass that new object into the Add method of the DataTable.Rows collection.
tblAuthors.Rows.Add(drCurrent)
MsgBox("Add was successful.")
'END ADD CODE
'*****************
'BEGIN EDIT CODE
drCurrent = tblAuthors.Rows.Find("213-46-8915")
drCurrent.BeginEdit()
drCurrent("phone") = "342" & drCurrent("phone").ToString.Substring(3)
drCurrent.EndEdit()
MsgBox("Record edited successfully")
'END EDIT CODE
'*****************
'BEGIN SEND CHANGES TO SQL SERVER
Dim objCommandBuilder As New SqlCommandBuilder(daAuthors)
daAuthors.Update(dsPubs, "Authors")
MsgBox("SQL Server updated successfully" & chr(13) & "Check Server explorer to see changes")
' END SEND CHANGES TO SQL SERVER
'*****************
'BEGIN DELETE CODE
drCurrent = tblAuthors.Rows.Find("993-21-3427")
drCurrent.Delete()
MsgBox("Record deleted successfully")
'END DELETE CODE
'*****************
' CLEAN UP SQL SERVER
daAuthors.Update(dsPubs, "Authors")
MsgBox("SQL Server updated successfully" & Chr(13) & Chr(13) & "Check Server Explorer to see changes")
End Sub
End Module
温馨提示:答案为网友推荐,仅供参考