Subscribe to Interesting Programming Methods        RSS Feed
-----

Insert to database all fields pramaeters

Icon Leave Comment
I was working on an issue at work where we have a database that has ~55 columns. I had to create an insert statement that would insert values into these columns, and I have to get the values from a text file that is "","" delimited. I always use parameters in my sql statements, because they are more secure that way. The issue is I didn't know all the datatypes I needed for each column, so when trying to inserta string into a Date or Number field it came up with an error. My solution was to get a listing of the columns, and assign a Type object to the current columns datatype, then attempt to Cast my string into that datatype. This is what I cam up with. Follow the comments they explain what the code is doing. If you have questions feel free to ask :)

' get a list of all the columns
Dim query As New OleDbCommand 
query.CommandText = "SELECT * FROM Table_Name"
        Dim da As OleDbDataAdapter = New OleDbDataAdapter(query.CommandText, con)
        Dim ds As New DataSet
        Dim columns As New List(Of DataColumn)
        Dim curLine As String = ""
        da.Fill(ds)
        For Each dc As DataColumn In ds.Tables(0).Columns
            columns.Add(dc)
        Next
' start reading the file
Dim sr As New StreamReader(fi.FullName)
Using sr
While Not sr.EndOfStream
curLine = sr.ReadLine()
' split the curent line on the delimiter
Dim strings() As String = curLine.Split({""","""}, StringsSplitOptions.None)
' begin ccreating the sql statemnt
For i As Integer = 0 to strings.Count - 2
query.CommandText = "INSERT INTO Table_Name VALUES("
' create a type that will change based on the current column
Dim myType As Type = columns(i).DataType
' replace any extra double quotes
strings(i) = strings(i).Replace(Chr(34), "")
' this handles objects trying to convert "" to integer or something similar
If strings(i).Trim = "" And Not myType.Name = "String" Then
strings(i) = Nothing
ElseIf strings(i).Trim = "" And myType.Name = "String" Then
string(i) = " "
End If
Dim value As Object = Nothing
' this is where the conversion actually happens
If Not strings(i) Is Nothing Then
value = CTypeDynamic(strings(i), myType)
Else
value = DBNull.Value
End If
' add the parameter to the query object
query.Parameters.Add(New OleDbParameter(columns(i).ColumnName, value))
Next
' finish the query out
For i As Integer = 0 to query.Parameters.Count - 1
query.CommandText &= "@" & query.Parameters(i).ParameterName & ","
Next
' remove the last "," and place the closing ")"
query.CommandText = Mid(query.CommandText, 1, query.CommandText.Length - 1) & ")"
' open the connection
connection.Open()
' execute the query
query.ExecuteNonQuery()
' close the connection
connection.Close()
' empty the parameters for the next line in the file
query.Parameters.Clear()
End While
End Using

0 Comments On This Entry

 

Trackbacks for this entry [ Trackback URL ]

There are no Trackbacks for this entry

August 2014

S M T W T F S
      1 2
3456789
10111213141516
17181920212223
24252627282930
31      

Tags

    Recent Entries

    Search My Blog

    0 user(s) viewing

    0 Guests
    0 member(s)
    0 anonymous member(s)

    Categories