晕
手头做的一个东西,要求在DataGrid中,每输入完一行后,就保存一次,因为数据库中表字段变动比较频繁,所以匆忙地考虑过后,决定用非类型化的数据集来实现。
下手时,因为人比较懒,不想自己手太多的Sql代码(字段有十几个),想着每次配置数据适配器很累,于是决定使用SqlCommandBuilder来根据select语句生成CURD所需的语句,谁知,在这里,我犯了一个愚蠢的错误。
几下子弄完后,一测试之下,眼睛都瞪出来了,删除一条数据时,数据库中居然反而会多出几条重复数据。
经分析之下,才突然想起SqlCommandBuider生成的代码弱智,因为这是ID的问题造成的,于是采用类似如下的代码:
但生成了程序之后,相同的问题又出现了。
后来还是直接用vs.net2003的配置工具弄的。
可惜的是,这个工具只能在支持窗体,组件,控件这类有UI界面的地步使用,在类中,却无法配置,显得太重了。
看了看源码,觉得要重新写一遍很花时间,还是等有空时,再说吧。
下手时,因为人比较懒,不想自己手太多的Sql代码(字段有十几个),想着每次配置数据适配器很累,于是决定使用SqlCommandBuilder来根据select语句生成CURD所需的语句,谁知,在这里,我犯了一个愚蠢的错误。
几下子弄完后,一测试之下,眼睛都瞪出来了,删除一条数据时,数据库中居然反而会多出几条重复数据。
经分析之下,才突然想起SqlCommandBuider生成的代码弱智,因为这是ID的问题造成的,于是采用类似如下的代码:
rivate Sub AutoIncrInsertEventSql(ByVal sServer As String, _
ByVal sDB As String)
Cursor.Current = Cursors.WaitCursor
sqlConnect.ConnectionString = "SERVER=" & _
sServer & ";INTEGRATED SECURITY=True;DATABASE=" & sDB
Dim sqlDA As SqlDataAdapter = New SqlDataAdapter( _
"SELECT * FROM Employees ORDER BY EmployeeID", sqlConnect)
[END CALLOUT A]
[BEGIN CALLOUT B]
sqlDA.InsertCommand = New SqlCommand("INSERT INTO Employees" & _
"(LastName, FirstName, Title, Address, City, Region, " & _
"PostalCode) VALUES (@LastName, @FirstName, @Title, " & _
"@Address, @City, @Region, @PostalCode)", sqlConnect)
sqlDA.InsertCommand.CommandType = CommandType.Text
[END CALLOUT B]
[BEGIN CALLOUT C]
Dim ds = New DataSet
Try
sqlConnect.Open()
sqlDA.Fill(ds, "Employees")
[END CALLOUT C]
[BEGIN CALLOUT D]
Dim sqlParm = New SqlParameter
sqlParm = sqlDA.InsertCommand.Parameters.Add( _
New SqlParameter("@LastName", SqlDbType.NChar, 20, _
"LastName"))
sqlParm = sqlDA.InsertCommand.Parameters.Add( _
New SqlParameter("@FirstName", SqlDbType.NChar, 10, _
"FirstName"))
sqlParm = sqlDA.InsertCommand.Parameters.Add( _
New SqlParameter("@Title", SqlDbType.NChar, 30, _
"Title"))
sqlParm = sqlDA.InsertCommand.Parameters.Add( _
New SqlParameter("@Address", SqlDbType.NChar, 60, _
"Address"))
sqlParm = sqlDA.InsertCommand.Parameters.Add( _
New SqlParameter("@City", SqlDbType.NChar, 15, _
"City"))
sqlParm = sqlDA.InsertCommand.Parameters.Add( _
New SqlParameter("@Region", SqlDbType.NChar, 15, _
"Region"))
sqlParm = sqlDA.InsertCommand.Parameters.Add( _
New SqlParameter("@PostalCode", SqlDbType.NChar, 10, _
"PostalCode"))
[END CALLOUT D]
[BEGIN CALLOUT E]
Dim sqlDR = ds.Tables("Employees").NewRow()
sqlDR("LastName") = "Jones"
sqlDR("FirstName") = "James"
sqlDR("Title") = "Sales Temp"
sqlDR("Address") = "2111 First St"
sqlDR("City") = "Portland"
sqlDR("Region") = "OR"
sqlDR("PostalCode") = "97234"
ds.Tables("Employees").Rows.Add(sqlDR)
[END CALLOUT E]
[BEGIN CALLOUT F]
AddHandler sqlDA.RowUpdated, New _
SqlRowUpdatedEventHandler(AddressOf OnRowUpdated)
sqlDA.Update(ds, "Employees")
sqlConnect.Close()
Cursor.Current = Cursors.Default
Catch e As Exception
Cursor.Current = Cursors.Default
MsgBox(e.Message)
End Try
End Sub
[END CALLOUT F]
ByVal sDB As String)
Cursor.Current = Cursors.WaitCursor
sqlConnect.ConnectionString = "SERVER=" & _
sServer & ";INTEGRATED SECURITY=True;DATABASE=" & sDB
Dim sqlDA As SqlDataAdapter = New SqlDataAdapter( _
"SELECT * FROM Employees ORDER BY EmployeeID", sqlConnect)
[END CALLOUT A]
[BEGIN CALLOUT B]
sqlDA.InsertCommand = New SqlCommand("INSERT INTO Employees" & _
"(LastName, FirstName, Title, Address, City, Region, " & _
"PostalCode) VALUES (@LastName, @FirstName, @Title, " & _
"@Address, @City, @Region, @PostalCode)", sqlConnect)
sqlDA.InsertCommand.CommandType = CommandType.Text
[END CALLOUT B]
[BEGIN CALLOUT C]
Dim ds = New DataSet
Try
sqlConnect.Open()
sqlDA.Fill(ds, "Employees")
[END CALLOUT C]
[BEGIN CALLOUT D]
Dim sqlParm = New SqlParameter
sqlParm = sqlDA.InsertCommand.Parameters.Add( _
New SqlParameter("@LastName", SqlDbType.NChar, 20, _
"LastName"))
sqlParm = sqlDA.InsertCommand.Parameters.Add( _
New SqlParameter("@FirstName", SqlDbType.NChar, 10, _
"FirstName"))
sqlParm = sqlDA.InsertCommand.Parameters.Add( _
New SqlParameter("@Title", SqlDbType.NChar, 30, _
"Title"))
sqlParm = sqlDA.InsertCommand.Parameters.Add( _
New SqlParameter("@Address", SqlDbType.NChar, 60, _
"Address"))
sqlParm = sqlDA.InsertCommand.Parameters.Add( _
New SqlParameter("@City", SqlDbType.NChar, 15, _
"City"))
sqlParm = sqlDA.InsertCommand.Parameters.Add( _
New SqlParameter("@Region", SqlDbType.NChar, 15, _
"Region"))
sqlParm = sqlDA.InsertCommand.Parameters.Add( _
New SqlParameter("@PostalCode", SqlDbType.NChar, 10, _
"PostalCode"))
[END CALLOUT D]
[BEGIN CALLOUT E]
Dim sqlDR = ds.Tables("Employees").NewRow()
sqlDR("LastName") = "Jones"
sqlDR("FirstName") = "James"
sqlDR("Title") = "Sales Temp"
sqlDR("Address") = "2111 First St"
sqlDR("City") = "Portland"
sqlDR("Region") = "OR"
sqlDR("PostalCode") = "97234"
ds.Tables("Employees").Rows.Add(sqlDR)
[END CALLOUT E]
[BEGIN CALLOUT F]
AddHandler sqlDA.RowUpdated, New _
SqlRowUpdatedEventHandler(AddressOf OnRowUpdated)
sqlDA.Update(ds, "Employees")
sqlConnect.Close()
Cursor.Current = Cursors.Default
Catch e As Exception
Cursor.Current = Cursors.Default
MsgBox(e.Message)
End Try
End Sub
[END CALLOUT F]
但生成了程序之后,相同的问题又出现了。
后来还是直接用vs.net2003的配置工具弄的。
可惜的是,这个工具只能在支持窗体,组件,控件这类有UI界面的地步使用,在类中,却无法配置,显得太重了。
看了看源码,觉得要重新写一遍很花时间,还是等有空时,再说吧。