C-Sharp Database Parameterized Insert Tutorial

Insert data into a database

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?

C# 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.

C-Sharp 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 static void RunDemoInsertSQL( )
{
    Stopwatch sw = new Stopwatch();
    sw.Start();
    using (VistaDBConnection conn = new VistaDB.Provider.VistaDBConnection(
        "Data Source=NewDatabase.vdb4;Mode=ExclusiveReadWrite;Pooling=false")
        )
    {
        conn.Open(); string sqlInsertCommand =
            "INSERT INTO [MyTable](Data1, Data2) VALUES (@Data1, @Data2); SELECT @@IDENTITY";
        using( VistaDBCommand cmd = new VistaDBCommand(sqlInsertCommand, conn ))
        {
            // Get your parameters ready
            cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@data1";
            cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@data2";
            // Now do your for loop
            Int64 retval;
            for (int i = 0; i < 30000; i++)
            {
                cmd.Parameters["@Data1"].Value = "SQL Entry: "
                    + i.ToString();
                cmd.Parameters["@Data2"].Value = DateTime.Now.Millisecond;
                cmd.ExecuteReader();
            }
        }
    }
    sw.Stop();
    Console.WriteLine("SQL Insert : " + sw.ElapsedMilliseconds + " ms ");
}

Sample C-Sharp 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 static void RunDemoInsertDDA( )
{
    Stopwatch sw = new Stopwatch();
    sw.Start();
    IVistaDBDDA DDAObj = VistaDBEngine.Connections.OpenDDA();
    using (IVistaDBDatabase db = DDAObj.OpenDatabase(
        "NewDatabase.vdb4",
        VistaDBDatabaseOpenMode.ExclusiveReadWrite,
        null)
        )
{
    using (IVistaDBTable table = db.OpenTable("MyTable", true, false))
    {
        for (int i = 0; i < 30000; i++)
        {
            table.Insert(); table.PutString("data1", "Entry: " + i.ToString());
            table.PutInt32("data2", DateTime.Now.Millisecond);
            table.Post();
         }
    }
}
sw.Stop();
Console.WriteLine("DDA Insert : " + sw.ElapsedMilliseconds + " ms ");
}

VB.Net Example

See the VB.Net version also.