VB.NET Database Insert Tutorial

Insert data into a database - VB version

Adding data into a database is called an INSERT operation. The SQL code is called INSERT, but using Ado.net you can actually do this a number of different ways. This page attempts to show how to insert using some best practices.

What does this sample show?

VistaDB VB.net sample demonstrating how to use SQL Parameters to insert data into a database table named MyTable. This sample includes a database with a single table that contains 3 columns (ID, Data1, Data2). The INSERT statement is built manually to demonstrate how to do it, and retrieves the IDENTITY value after the insert in a single statement.

VB.NET Sample SQL Code

All inserts using ADO.Net should really be done with Parameterized Queries. Using a parameter object on a DBCommand is the safest way to insert data because you don't have to worry about SQL Injection Attacks. All data is automatically quoted for you through our provider. Another benefit of using parameterized queries is when you have data that is localization sensitive, such as datetime objects. By using a parameterized query the datetime is automatically converted to a format that the engine will handle, you don't have to worry about the users locale.

Public Sub RunDemoInsertSQL()
    Dim sw As New Stopwatch
    sw.Start()
    ' The Data Source is the name of the database.
     The local file path is used meaning the database is in the same directory as the EXE.
    ' Mode is exclusive mode (not shared with other apps)
    ' Pooling is for connection pooling
    Using conn As VistaDBConnection = New
        VistaDBConnection( "Data Source=NewDatabase.vdb4;Mode=ExclusiveReadWrite;Pooling=false")
            ' Open the database
            conn.Open()
            ' Insert a new row into the database using parameterized data
           Dim sqlInsertCommand As String =
               "INSERT INTO [MyTable](Data1, Data2) VALUES (@Data1, @Data2); SELECT
                   @@IDENTITY"
           Using cmd As VistaDBCommand = New VistaDBCommand(sqlInsertCommand, conn)
               ' Add the new command parameters and their name
           cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "@data1"
           cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "@data2"
           Dim i As Integer
           ' Do this in a loop only changing the command parameter values per loop
           For i = 0 To 30000
               cmd.Parameters.Item("@Data1").Value = ("SQL
                   Entry: " & i.ToString)
               cmd.Parameters.Item("@Data2").Value = DateTime.Now.Millisecond
               cmd.ExecuteReader()
                   Next i
               End Using
           End Using
     sw.Stop()
     Console.WriteLine(("SQL Insert : " & sw.ElapsedMilliseconds & " ms "))
End Sub

Sample VB.NET DDA Code

This portion of the code is the same sample, but written for Direct Data Access (DDA) instead. In DDA you must first tell the table you are adding a new record with an insert operation, then you modify the columns, and the Post the new record to the database. Column data does not have to be quoted or passed by a parameterized query because DDA always handles .Net datatypes natively.

Public Sub RunDemoInsertDDA()
    Dim sw As New Stopwatch
    sw.Start()
    ' This interface requires Importing the VistaDB.DDA namespace
    ' Nothing for the password means the database is not encrypted
    Using db As IVistaDBDatabase =
    VistaDBEngine.Connections.OpenDDA.OpenDatabase(
        "NewDatabase.vdb4",
        VistaDB.VistaDBDatabaseOpenMode.ExclusiveReadWrite,
        Nothing)
        ' Open the table
        Using table As IVistaDBTable = db.OpenTable("MyTable", True, False)
        Dim i As Integer
        For i = 0 To 30000
            ' Insert a new record
            table.Insert()
            ' Add the values to the columns
            table.PutString("data1", ("Entry: " & i.ToString))
            table.PutInt32("data2", DateTime.Now.Millisecond)
            ' Post the new record to the table
            table.Post()
        Next i
    End Using
End Using

sw.Stop()
Console.WriteLine(("DDA Insert : " & sw.ElapsedMilliseconds & " ms "))
End Sub

C# Insert Sample

You may also want to view the C# insert rows sample also.