六種添加記錄方法學(xué)習(xí)筆記圖例源碼說明

1、VBNET2008六種添加記錄方法學(xué)習(xí)筆記（圖例、源碼、說明）（本文代碼VB.NET2008 + SQL Server 2005下測試通過。）VB6.0向VB.NET過渡后，很多編程愛好者對(duì)VB.NET數(shù)據(jù)庫編程感到迷惑，本文通過對(duì)國內(nèi)教材、國外教材、老教材、新教材的研究，將作者看到過的6種（insert語句、參數(shù)化3種、行提交、存儲(chǔ)過程、）添加（增加）記錄的方法提煉出來，并分析了各種添加記錄的優(yōu)點(diǎn)、缺點(diǎn)及是否常用等做了簡單敘述。讀者可以根據(jù)這些方法編寫出對(duì)應(yīng)的增刪改查（CRUD）代碼，使自己的程序編寫的更加容易理解，提高編程效率，提升程序的運(yùn)行速度。百度文庫首發(fā)，侵權(quán)必究。 一、前期準(zhǔn)備工作

2、（圖例）1、引用數(shù)據(jù)庫命名空間使用數(shù)據(jù)庫對(duì)象時(shí)要首先導(dǎo)入數(shù)據(jù)庫命名空間，數(shù)據(jù)庫對(duì)象的命名空間根據(jù)你所使用的數(shù)據(jù)庫不同，命名空間也不一樣。本文使用的是SQL Server的命名空間。 Imports2、界面設(shè)計(jì)1個(gè)Datagridview控件,5個(gè)TextBox控件，1個(gè)DateTimePicker控件，5個(gè)按鈕控件。具體控件示例如下： 3、在Datagridview表格中預(yù)覽數(shù)據(jù)庫記錄簡單的查詢了這個(gè)表的所有記錄。 Private Sub dataView() Dim objConn As New SqlClient.SqlConnection Dim objDa As New SqlClie

3、nt.SqlDataAdapter Dim objComm As New SqlClient.SqlCommand Dim objDs As New DataSet objConn.ConnectionString = "Data Source=49;Initial Catalog=ldg;User ID=wink;Pwd=wcp6810662" objComm.CommandText = "select * from wink" objComm.Connection = objConn objDa.SelectCommand =

4、objComm objConn.Open() objDa.Fill(objDs, "wink") objConn.Close() DataGridView1.DataSource = objDs.Tables("wink") End Sub4、窗體加載時(shí)調(diào)用dataView()過程預(yù)覽數(shù)據(jù)庫記錄窗體的 Form2_Load方法在本文的程序代碼段中多次被調(diào)用以刷新表格，讀者可以直接調(diào)用dataView()過程刷新表格。 Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As Syst

5、em.EventArgs) Handles MyBase.Load Call dataView() End Sub二、添加記錄的六種方法及簡要分析（源碼及說明）請(qǐng)注意代碼段使用時(shí)注意控件名稱，否則復(fù)制粘貼后容易出現(xiàn)錯(cuò)誤。1、使用INSERT語句添加記錄方法：直接使用SQL的INSERT語句在數(shù)據(jù)庫中添加記錄，這種方法大家會(huì)經(jīng)常見到，此種方法較為初級(jí)，數(shù)據(jù)庫字段較多時(shí)不便于維護(hù)，但較為直觀，很多初學(xué)者使用此方法。按鈕2代碼如下： Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs

6、) Handles Button2.Click Dim objConn As New SqlClient.SqlConnection Dim objComm As New SqlClient.SqlCommand objConn.ConnectionString = "Data Source=49;Initial Catalog=ldg;User ID=wink;Pwd=wcp6810662" objComm.Connection = objConn Dim sql As String sql = "insert into wink (id,

7、name,sex,age,address,birth) values('" & TextBox1.Text.ToString().Trim() & "','" & TextBox2.Text.ToString().Trim() & " ','" & TextBox3.Text.ToString().Trim() & " ','" & TextBox4.Text.ToString().Trim() & &qu

8、ot; ','" & TextBox5.Text.ToString().Trim() & " ','" & DateTimePicker1.Value & "') " objComm.CommandText = sql objConn.Open() Try objComm.ExecuteNonQuery() Catch sqlExceptionErr As SqlClient.SqlException MessageBox.Show(sqlExceptionErr.Mess

9、age) Finally objConn.Close() End Try Call Form2_Load(sender, e) MsgBox("添加成功!", MsgBoxStyle.Information, "提示") End Sub2、參數(shù)化方式增加數(shù)據(jù)庫記錄方法（VB.NET2008之前）：這種添加記錄的方法VB.NET2008之前較為常用，2008版本之后SqlClient.SqlCommand.Add方法被覆寫為SqlClient.SqlCommand.AddWithValue方法。該方法的好處是可以在數(shù)據(jù)記錄進(jìn)入數(shù)據(jù)庫之前控制各個(gè)字段的類型，

10、缺點(diǎn)是編程時(shí)寫代碼較為麻煩。按鈕3代碼如下： Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Dim objConn As New SqlClient.SqlConnection Dim objComm As New SqlClient.SqlCommand Dim insert As String insert = "insert into wink (id,name,sex,age,address,birth)&q

11、uot; insert = insert & "values (id,name,sex,age,address,birth)" objConn.ConnectionString = "Data Source=49;Initial Catalog=ldg;User ID=wink;Pwd=wcp6810662" objComm.Connection = objConn objComm.CommandText = insert objComm.Parameters.Add("id", SqlDbType.In

12、t).Value = Trim(TextBox1.Text) objComm.Parameters.Add("name", SqlDbType.VarChar).Value = Trim(TextBox2.Text) objComm.Parameters.Add("sex", SqlDbType.VarChar).Value = Trim(TextBox3.Text) objComm.Parameters.Add("age", SqlDbType.Int).Value = Trim(TextBox4.Text) objComm.Par

13、ameters.Add("address", SqlDbType.VarChar).Value = Trim(TextBox5.Text) objComm.Parameters.Add("birth", SqlDbType.DateTime).Value = DateValue(Me.DateTimePicker1.Text) objConn.Open() Try objComm.ExecuteNonQuery() Catch sqlExceptionErr As SqlClient.SqlException MessageBox.Show(sqlExc

14、eptionErr.Message) Finally objConn.Close() End Try Call Form2_Load(sender, e) MsgBox("添加成功!", MsgBoxStyle.Information, "提示") End Sub3、參數(shù)化方式增加數(shù)據(jù)庫記錄方法（VB.NET2008及以后）：這種添加記錄的方法使用SqlClient.SqlCommand.AddWithValue的屬性實(shí)現(xiàn)以參數(shù)化方式增加記錄。以參數(shù)化方式增加數(shù)據(jù)庫記錄的明顯優(yōu)勢是字段關(guān)系清晰，并且避免了SQLServer本身的一些漏洞，如注釋語句產(chǎn)生的

15、問題。當(dāng)以參數(shù)化的方式添加記錄時(shí)要考慮SqlCommand類的CommandText屬性，需要在SQL語句中使用“”指明參數(shù)；SqlCommand類Parameter屬性是SqlParameter參數(shù)集合，需要使用Add或AddWithValue方法向集合中添加參數(shù)，然后才能使用具體參數(shù)的Value屬性進(jìn)行賦值操作。按鈕4代碼如下： Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click Dim objConn As New SqlCl

16、ient.SqlConnection Dim objComm As New SqlClient.SqlCommand objConn.ConnectionString = "Data Source=49;Initial Catalog=ldg;User ID=wink;Pwd=wcp6810662" objComm.Connection = objConn objComm.CommandText = "insert into wink (id,name,sex,age,address,birth) values (id,name,sex,ag

17、e,address,birth)" objComm.Parameters.AddWithValue("id", TextBox1.Text) objComm.Parameters.AddWithValue("name", TextBox2.Text) objComm.Parameters.AddWithValue("sex", TextBox3.Text) objComm.Parameters.AddWithValue("age", TextBox4.Text) objComm.Parameters.Ad

18、dWithValue("address", TextBox5.Text) objComm.Parameters.AddWithValue("birth", DateValue(DateTimePicker1.Value) objConn.Open() Try objComm.ExecuteNonQuery() Catch sqlExceptionErr As SqlClient.SqlException MessageBox.Show(sqlExceptionErr.Message) Finally objConn.Close() End Try Cal

19、l Form2_Load(sender, e) MsgBox("添加成功!", MsgBoxStyle.Information, "提示") End Sub4、以行提交的方式插入數(shù)據(jù)庫記錄方法：使用SqlCommand命令對(duì)象與數(shù)據(jù)庫交互，并插入新記錄。當(dāng)應(yīng)用程序使用數(shù)據(jù)集存儲(chǔ)記錄時(shí)，可以使用TableAdapter類的Update()方法，該方法會(huì)將所有更改（包括更新、插入及刪除）發(fā)送到數(shù)據(jù)庫中。這種方法在國產(chǎn)的教材、教程、書籍中較為常見。按鈕5代碼如下： Private Sub Button5_Click(ByVal sender As System

20、.Object, ByVal e As System.EventArgs) Handles Button5.Click Dim strSql As String Dim strConn As String Dim objDA As SqlClient.SqlDataAdapter Dim objds As New Data.DataSet Dim objcb As SqlClient.SqlCommandBuilder Dim objrow As Data.DataRow strConn = "Data Source=49;Initial Catalog=ldg

21、;User ID=wink;Pwd=wcp6810662" strSql = "select * from wink" objDA = New SqlClient.SqlDataAdapter(strSql, strConn) objcb = New SqlClient.SqlCommandBuilder(objDA) objDA.Fill(objds, "wink") objrow = objds.Tables("wink").NewRow objrow("id") = Trim(TextBox1.Te

22、xt) objrow("name") = Trim(TextBox2.Text) objrow("sex") = Trim(TextBox3.Text) objrow("age") = Trim(TextBox4.Text) objrow("address") = Trim(TextBox5.Text) objrow("birth") = DateValue(Me.DateTimePicker1.Text) objds.Tables("wink").Rows.Add(objr

23、ow) objDA.Update(objds, "wink") Call Form2_Load(sender, e) MsgBox("添加成功!", MsgBoxStyle.Information, "提示") End SubEnd Class5、使用存儲(chǔ)過程添加記錄的方法：由于時(shí)間關(guān)系此處用到的數(shù)據(jù)庫是Oracle數(shù)據(jù)庫，但與SQLServer基本相似。是一種比較偏向于企業(yè)級(jí)的添加記錄的方法。該方法減少了本地處理時(shí)間，但是增加了數(shù)據(jù)庫的負(fù)擔(dān)。并且可以通過存儲(chǔ)過程的編程更加靈活地添加記錄。按鈕代碼如下： Private Sub But

24、ton4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click Dim objCmd As New OracleClient.OracleCommand() objCmd.Connection = New OracleClient.OracleConnection(strConn) objCmd.CommandText = "bzk22.addtest" objCmd.CommandType = CommandType.StoredProcedure o

25、bjCmd.Parameters.Add("Pro_A", OracleClient.OracleType.VarChar).Value = TextBox1.Text objCmd.Parameters.Add("Pro_B", OracleClient.OracleType.VarChar).Value = TextBox2.Text objCmd.Parameters.Add("Pro_C", OracleClient.OracleType.VarChar).Value = TextBox3.Text objCmd.Parame

26、ters.Add("Pro_D", OracleClient.OracleType.VarChar).Value = TextBox4.Text objCmd.Parameters.Add("Pro_S" 'cmd.Parameters.Add("io_cursor", OracleType.Cursor).Direction = ParameterDirection.Output Dim objDA As New OracleClient.OracleDataAdapter(objCmd) Dim objDs As New

27、DataSet() objDA.Fill(objDs, "test") Call GridView() End Sub存儲(chǔ)過程代碼如下：create or replace procedure addTest( Pro_A TEST.A%type, Pro_B TEST.B%type, Pro_C TEST.C%type, Pro_D TEST.D%type, Pro_S TEST.S%type)asbegininsert into TEST (A,B,C,D,S) values(Pro_A,Pro_B,Pro_C,Pro_D,Pro_S);-4個(gè)參數(shù) commit;end;

28、6、參數(shù)化及占位符方式增加數(shù)據(jù)庫記錄方法：該方法與JAVA中使用SQL添加記錄的方法較為相似，是方法3的一種變化，是較新的方法。該方法使用“？”進(jìn)行站位，并且按照數(shù)據(jù)庫中字段的順序進(jìn)行賦值。使用時(shí)要注意“？”的個(gè)數(shù)應(yīng)與字段個(gè)數(shù)一致，否則將出現(xiàn)錯(cuò)誤。按鈕1代碼如下： Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim objConn As New SqlClient.SqlConnection Dim objComm As N

29、ew SqlClient.SqlCommand objConn.ConnectionString = "Data Source=49;Initial Catalog=ldg;User ID=wink;Pwd=wcp6810662" objComm.Connection = objConn objComm.CommandText = "insert into wink (id,name,sex,age,address,birth) values (?, ?, ?, ?, ?, ?)" objComm.Parameters.AddWithValue("id"