Tuesday, February 1, 2011

Insert Parameter Array in .NET

Here's a snippet to insert a parameter array in .NET:
   1:    ' add a class ex. Patient  
   2:      Public Class Patient  
   3:        Public name As String  
   4:        Public code As Integer  
   5:      End Class  
   6:      ' create a delegate   
   7:      Delegate Function myConn() As String  
   8:      'function to return connstring  
   9:      Private Function connectionstring() As String  
  10:        Dim conn As String = "your_connection_string here"  
  11:        Return conn  
  12:      End Function  
  13:      '--Main Sub  
  14:      ' execute insert array    
  15:      Dim objCon As myConn  
  16:        objCon = New myConn(AddressOf connectionstring)  
  17:        Dim str As String = objCon.Invoke()  
  18:        Dim connection As New SqlConnection(str)  
  19:        Try  
  20:          Dim patients As New List(Of Patient)  
  21:          For i As Integer = 0 To max_count_variable  
  22:            Dim patient As New Patient()  
  23:            patient.name = "greg nelson" + i.ToString  
  24:            patient.code = i * 2  
  25:            patients.Add(patient)  
  26:          Next  
  27:          connection.Open()  
  28:          Dim query As New StringBuilder  
  29:          Dim cmd As New SqlCommand  
  30:          For j As Integer = 0 To patients.Count - 1  
  31:            query.Append(String.Format("Insert into patients  
  32:               (PatientName,PatientSexCode)   
  33:               values(@names{0},@code{1});", j, j))  
  34:            'declare the parameters  
  35:            cmd.Parameters.AddWithValue(String.Format  
  36:             ("@names{0}", j), patients(j).name)  
  37:            cmd.Parameters.AddWithValue(String.Format  
  38:             ("@code{0}", j), patients(j).code)  
  39:          Next  
  40:          cmd.Connection = connection  
  41:          cmd.CommandText = query.ToString()  
  42:          cmd.ExecuteNonQuery()  
  43:        Catch ex As ApplicationException  
  44:          Throw New Exception(ex.Message)  
  45:        End Try  
  46:        connection.Close()  
  47:        Console.Write("Array successfully saved")  
  48:        Console.ReadLine()  
  49:      '--end main sub 

0 comments:

Post a Comment