C-Sharp Database CRUD Examples

CRUD is the basis for all database operations

CRUD (CREATE, READ, UPDATE, DELETE) operations in a database are the most basic level of operation you can perform. These four functions give you the basis of how to Create a new row, Read a row, Update a row, and Delete a row. Each of these operations are performed through SQL code and called through ADO.NET.

Execute a SQL Command

VistaDB database operations are performed by calling one of the Execute functions on the VistaDBCommand object. This sample page includes a quick example of each CRUD operation being performed against a local VistaDB database. All of the operations have almost identical functions from the C# code side, only the SQL commands themselves change.

Connect to the database using a |DataDirectory|

All database operations first require a connection to the database. This is accomplished by using the VistaDBConnection class. A static variable of the database connection string is created globally in the program to allow all of the routines to use the same string.

The |DataDirectory| part of the connection string is a macro telling the database engine to use the current working directory of the executable. It eliminates that need to hard code a path to the file, and allows the debugger to use a copy in the bin\debug folder.

A note on using blocks in your code

Using is a best practice for objects that implement iDisposable, and need to be quickly cleaned up. Putting a using block around an object ensures that the GC will call .Close() and .Dispose() on the object as soon as the using block is complete. Doing this helps to ensure your managed code is cleaned up quickly without a lot of extra syntax in the code or try/catch/finally blocks. Even if an error occurs the object will still be cleaned up properly.

The InsertName function in the code below is used to build and execute a SQL INSERT statement. This function will first build and open the database connection, then build up a VistaDBCommand with the SQL syntax.  The objects are all nested in Using blocks to ensure that they are cleaned up as quickly as possible.

ExecuteNonQuery

Each of the SQL statements below are run against the current database by calling ExecuteNonQuery.  This means the database will excecute the SQL code, but you don't really care about the result of the operation.  In most real world applications you would probably not do this, but instead run ExecuteQuery and check the number of rows affected by the SQL statement.  If you expected a single row to be modified, but more than that were returned then you probably have a bug in your SQL Statement logic.

Parameterized Queries

Hard coding your SQL syntax like this is not a best practice.  It can lead to SQL injection attacks by malicious users entering SQL into your command. 

Imagine you have an input box asking for a username.  If the user puts  'username'; select 1;' in that box the code below would execute select 1 as a new SQL statement.  By putting each parameter as a formal part of the VistaDBCommand object the engine takes care of escaping that syntax and will prevent a SQL injection attack.

See the C-Sharp Insert Rows example for an example using parameterized queries instead of string formatting for the parameters of the SQL statement.

C-Sharp Sample SQL Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.Common;
using VistaDB.Provider;

namespace Basics
{
class Program
{
    static string connectionString = @"DataSource='|DataDirectory|\BasicDB.vdb4'";

    static void Main(string[] args)
    {
        string originalName = "Roger";
        string newName = "Kyle";

        InsertName(originalName);
        UpdateName(originalName, newName);
        DeleteName(newName);
    }

    public static void InsertName(string name)
    {
        using (VistaDBConnection connection = new VistaDBConnection(connectionString))
        {
            connection.Open();
            using (VistaDBCommand command = new VistaDBCommand())
            {
                command.CommandText = 
                    string.Format("INSERT INTO Names (Name)
                        VALUES ('{0}')", name);
                command.Connection = connection;
                command.ExecuteNonQuery();
            }
        }
    }

    public static void DeleteName(string name)
    {
        using (VistaDBConnection connection = new VistaDBConnection(connectionString))
        {
            connection.Open();
            using(VistaDBCommand command = new VistaDBCommand())
            {
                command.CommandText = 
                    string.Format("DELETE FROM Names WHERE Name
                        = '{0}'", name);
                command.Connection = connection;
                command.ExecuteNonQuery();
            }
        }
    }

    public static void UpdateName(string name1, string name2)
    {
        using (VistaDBConnection connection = new VistaDBConnection(connectionString))
        {
            connection.Open();
            using (VistaDBCommand command = new VistaDBCommand())
            {
                command.CommandText = 
                    string.Format("UPDATE Names SET Name = '{0}'
                        WHERE Name = '{1}'",
		  name2, name1);
                command.Connection = connection;
                command.ExecuteNonQuery();
            }
        }
    }
}
}