如何設定NULL為SqlParameter初始值
在使用ADO.NET連接資料庫進行CRUD時，搭配SqlParameter作為參數，不論是否指定Value屬性在ADO.NET轉成sp_executesql敘述時，給定的變數內容都會設定為default。
Using conn As New SqlConnection(ConnString)
Using cmd As New SqlCommand With {.Connection = conn}
cmd.CommandText = "INSERT INTO t1(c1,c2,c3,c4,c5) VALUES (@c1,@c2,@c3,@c4,GETDATE())"
Dim c1 As New SqlParameter With {.ParameterName = "@c1", .SqlDbType = SqlDbType.Char, .Size = 8, .Value = ConfDate2}
Dim c2 As New SqlParameter With {.ParameterName = "@c2", .SqlDbType = SqlDbType.Char, .Size = 8, .Value = Mtxtc2.Text}
Dim c3 As New SqlParameter With {.ParameterName = "@c3", .SqlDbType = SqlDbType.Char, .Size = 8, .Value = Nohting, .IsNullable = True}
Dim c4 As New SqlParameter With {.ParameterName = "@c4", .SqlDbType = SqlDbType.Int, .Value = Nothing, .IsNullable = True}
cmd.Parameters.AddRange(New SqlParameter() {c1, c2, c3, c4})
Try
If conn.State = ConnectionState.Closed Then conn.Open()
cmd.ExecuteNonQuery()
Catch ex As Exception
End Try
End Using
nd Using
Using conn As New SqlConnection(ConnString)
Using cmd As New SqlCommand With {.Connection = conn}
cmd.CommandText = "INSERT INTO t1(c1,c2,c3,c4,c5) VALUES (@c1,@c2,@c3,@c4,GETDATE())"
Dim c1 As New SqlParameter With {.ParameterName = "@c1", .SqlDbType = SqlDbType.Char, .Size = 8, .Value = ConfDate2}
Dim c2 As New SqlParameter With {.ParameterName = "@c2", .SqlDbType = SqlDbType.Char, .Size = 8, .Value = Mtxtc2.Text}
Dim c3 As New SqlParameter With {.ParameterName = "@c3", .SqlDbType = SqlDbType.Char, .Size = 8 , .IsNullable = True}
Dim c4 As New SqlParameter With {.ParameterName = "@c4", .SqlDbType = SqlDbType.Int, .IsNullable = True}
cmd.Parameters.AddRange(New SqlParameter() {c1, c2, c3, c4})
Try
If conn.State = ConnectionState.Closed Then conn.Open()
cmd.ExecuteNonQuery()
Catch ex As Exception
End Try
End Using
nd Usin
上述程式碼會被轉成下列的SQL命令。
exec sp_executesql N'INSERT INTO t1(c1,c2,c3,c4,c5)
VALUES (@c1,@c2,@c3,@c4,GETDATE())',N'@c1 char(8)
,@c2 char(8),@c3 char(8),@c4 int'
,@c1='11111111',@c2='22222222',@c3=default,@c4=default
此時若資料表t1的c3、c4未設定初始值，則會造成Insert失敗，解決方式為設定為DBNull類別，就能正確設定Null給TSQL命令。
Using conn As New SqlConnection(ConnString)
Using cmd As New SqlCommand With {.Connection = conn}
cmd.CommandText = "INSERT INTO t1(c1,c2,c3,c4,c5) VALUES (@c1,@c2,@c3,@c4,GETDATE())"
Dim c1 As New SqlParameter With {.ParameterName = "@c1", .SqlDbType = SqlDbType.Char, .Size = 8, .Value = ConfDate2}
Dim c2 As New SqlParameter With {.ParameterName = "@c2", .SqlDbType = SqlDbType.Char, .Size = 8, .Value = Mtxtc2.Text}
Dim c3 As New SqlParameter With {.ParameterName = "@c3", .SqlDbType = SqlDbType.Char, .Size = 8, .Value = DBNull.Value, .IsNullable = True}
Dim c4 As New SqlParameter With {.ParameterName = "@c4", .SqlDbType = SqlDbType.Int, .Value = DBNull.Value, .IsNullable = True}
cmd.Parameters.AddRange(New SqlParameter() {c1, c2, c3, c4})
Try
If conn.State = ConnectionState.Closed Then conn.Open()
cmd.ExecuteNonQuery()
Catch ex As Exception
End Try
End Using
nd Using
使用DBNull後，即得到下列的結果。
exec sp_executesql N'INSERT INTO t1(c1,c2,c3,c4,c5)
VALUES (@c1,@c2,@c3,@c4,GETDATE())',N'@c1 char(8)
,@c2 char(8),@c3 char(8),@c4 int'
,@c1='11111111',@c2='22222222',@c3=null,@c4=null