Wednesday, March 25, 2009

asp.net insert/update code

It occured to me that I didn't have a place any longer to keep vital snips and code for doing development on the multitudes of different projects that I have and I need someplace to organize my thoughts so why not out in public.

So we use App_Code and SQLProviders for updates to DNN modules on our site.

This is a sample simple update insert. (Will fix if buggy but wanted to save and color as soon as I can)


Public Overrides Function InsertAddUsers(ByVal ID As Integer, ByVal First_Name As String, ByVal Last_Name As String, ByVal Email As String) As Boolean
Dim conDNN As New SqlConnection(DNNConnectionString)
Dim scAddUsers As String
scAddUsers = "IF EXISTS " _
& "(SELECT 1 FROM WebinarUsers WHERE WebinarRegistrationID = @ID and email = @email) " _
& "BEGIN " _
& "Update WebinarUsers SET WebinarRegistrationID = @ID, " _
& "firstName = @First_Name, lastName = @Last_Name, Email = @email " _
& "where WebinarRegistrationID = @ID and email = @email " _
& "END " _
& "ELSE " _
& "BEGIN " _
& "Insert Into WebinarUsers (WebinarRegistrationID, FirstName, LastName, Email) " _
& "Values (@ID, @First_name, @Last_Name, @Email) " _
& "END"

Try
conDNN.Open()
Using insertUsers As New SqlCommand(scAddUsers, conDNN)

insertUsers.Parameters.Add("@ID", SqlDbType.Int).Value = ID
insertUsers.Parameters.Add("@First_Name", SqlDbType.VarChar).Value = First_Name
insertUsers.Parameters.Add("@Last_name", SqlDbType.VarChar).Value = Last_Name
insertUsers.Parameters.Add("@Email", SqlDbType.VarChar).Value = Email
insertUsers.ExecuteNonQuery()

Return True
End Using

Catch ex As Exception
'Log error
Return False
Finally
conDNN.Close()
End Try
End Function