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