VistaDB 6
VistaDB / Getting Started / Using VistaDB with ADO.NET / Using VistaDB with ADO.NET - Common Operations using VistaDB Exclusively

In This Topic
    Using VistaDB with ADO.NET - Common Operations using VistaDB Exclusively
    In This Topic

    The following code examples show how to perform common data operations using ADO.NET in a VistaDB-exclusive manner; Developers are encouraged to use the alternate approach that uses Database Factory objects since it will work for VistaDB and SQL Server without any code changes.

    This code assumes that the appropriate VistaDB Provider has been referenced by your project and the following namespace has been imported:

    VistaDB.Provider

     Connecting To A Database

    This code sample shows the basic step of opening a VistaDB database.

    using (VistaDBConnection connection = new VistaDBConnection())
    {
        connection.ConnectionString = @"Data Source=C:\mydatabase.vdb6";
        connection.Open();
    }
    
    Using connection As New VistaDBConnection()
         connection.ConnectionString = "Data Source=C:\mydatabase.vdb6"
         connection.Open()
    End Using
    
     Creating A Command

    Most database operations require creating a DbCommand object, configuring it for your operation, and associating it with a database connection to work with.  This example shows creating the connection and command and associating them together.

    using (VistaDBConnection connection = new VistaDBConnection())
    {
        connection.ConnectionString = @"Data Source=C:\mydatabase.vdb6";
        connection.Open();
    
        using (VistaDBCommand command = new VistaDBCommand())
        {
            command.Connection = connection;
        }
    }
    
    Using connection As New VistaDBConnection()
         connection.ConnectionString = "Data Source=C:\mydatabase.vdb6"
         connection.Open()
    
         Using command As New VistaDBCommand()
              command.Connection = connection
         End Using
    End Using
    
     Inserting Data Using a Command

    In this example the command is being used to execute a simple TSQL insert.  Since no data set is expected in return the ExecuteNonQuery() method is used to execute the command.

    using (VistaDBConnection connection = new VistaDBConnection())
    {
        connection.ConnectionString =@"Data Source=C:\mydatabase.vdb6";
        connection.Open();
    
        using (VistaDBCommand command = new VistaDBCommand())
        {
            command.Connection = connection;
            command.CommandText = "INSERT INTO MyTable (MyColumn) VALUES ('Test')";
            command.ExecuteNonQuery();
        }
    }
    
    Using connection As New VistaDBConnection()
         connection.ConnectionString = "Data Source=C:\mydatabase.vdb6"
         connection.Open()
    
         Using command As New VistaDBCommand()
              command.Connection = connection
              command.CommandText = "INSERT INTO MyTable (MyColumn) VALUES ('Test')"
              command.ExecuteNonQuery()
         End Using
    End Using
    
     Inserting Data Using a Parameterized Command

    In this example the command is being used to execute a simple TSQL insert.  It improves on the previous example by using a parameterized TSQL statement and ADO.NET Parameters to pass data.  It is highly recommended that parameters always be used to pass data from your application to ADO.NET for reliability, consistency, and to ensure there is no possibility of a SQL Injection Attack against your code.

    using (VistaDBConnection connection = new VistaDBConnection())
    {
        connection.ConnectionString = @"Data Source=C:\mydatabase.vdb6";
        connection.Open();
    
        using (VistaDBCommand command = new VistaDBCommand())
        {
            int Age = 21;
    
            command.Connection = connection;
            command.CommandText = "INSERT INTO MyTable (MyColumn) VALUES (@age)";
            command.Parameters.Add("@age", Age);
            command.ExecuteNonQuery();
        }
    }
    
    Using connection As New VistaDBConnection()
         connection.ConnectionString = "Data Source=C:\mydatabase.vdb6"
         connection.Open()
    
         Using command As New VistaDBCommand()
              Dim Age As Integer = 21
    
              command.Connection = connection
              command.CommandText = "INSERT INTO MyTable (MyColumn) VALUES (@age)"
              command.Parameters.Add("@age", Age)
              command.ExecuteNonQuery()
         End Using
    End Using
    
     Querying Data and Filling a Data Table

    To retrieve multiple rows of data from a database ADO.NET uses a TableAdapter to fill a DataTable.  The DataTable can have exactly one set of rows with a common schema.

    using (VistaDBConnection connection = new VistaDBConnection())
    {
        connection.ConnectionString = @"Data Source=C:\mydatabase.vdb6";
        connection.Open();
    
        DataTable table = new DataTable();
    
        using (VistaDBCommand command = new VistaDBCommand())
        {
            command.Connection = connection;
            command.CommandText = "SELECT * FROM MyTable";
    
            using (VistaDBDataAdapter adapter = new VistaDBDataAdapter())
            {
                adapter.SelectCommand = command;
                adapter.Fill(table);
            }
        }                    
    }
    
    Using connection As New VistaDBConnection()
         connection.ConnectionString = "Data Source=C:\mydatabase.vdb6"
         connection.Open()
    
         Dim table As New DataTable()
    
         Using command As New VistaDBCommand()
              command.Connection = connection
              command.CommandText = "SELECT * FROM MyTable"
    
              Using adapter As New VistaDBDataAdapter()
                   adapter.SelectCommand = command
                   adapter.Fill(table)
              End Using
         End Using
    End Using
    
     Calling Stored Procedures

    Like Microsoft SQL Server, VistaDB supports Stored Procedures.  In this example the stored procedure "CalcSalesTax" is being called with several parameters.  To prevent SQL Injection attacks parameterized commands should be used to pass data to stored procedures instead of attempting to assemble the query as a string.

    using (VistaDBConnection connection = new VistaDBConnection())
    {
        connection.ConnectionString = @"Data Source=C:\mydatabase.vdb6";
        connection.Open();
    
        using(VistaDBCommand command = new VistaDBCommand("CalcSalesTax", connection))
        {
           command.CommandType = CommandType.StoredProcedure;
    
           VistaDBParameter param = command.Parameters.AddWithValue("@SalesTotal", 100.00);
           param.Direction = ParameterDirection.Input;
           param.DbType = System.Data.DbType.Currency;
    
           VistaDBParameter outparam = command.Parameters.Add("@OrderTotal", VistaDBType.Money);
           outparam.Direction = ParameterDirection.Output;
       
           command.ExecuteNonQuery();
        }                                        
    }
    
    Using connection As New VistaDBConnection()
         connection.ConnectionString = "Data Source=C:\mydatabase.vdb6"
         connection.Open()
    
         Using command As New VistaDBCommand("CalcSalesTax", connection)
              command.CommandType = CommandType.StoredProcedure
    
              Dim param As VistaDBParameter = command.Parameters.AddWithValue("@SalesTotal", 100.0)
              param.Direction = ParameterDirection.Input
              param.DbType = System.Data.DbType.Currency
    
              Dim outparam As VistaDBParameter = command.Parameters.Add("@OrderTotal", VistaDBType.Money)
              outparam.Direction = ParameterDirection.Output
    
              command.ExecuteNonQuery()
         End Using
    End Using
    
    See Also