VistaDB 5
Gibraltar VistaDB / Getting Started / Using VistaDB with ADO.NET / Using VistaDB with ADO.NET - Common Operations using Any ADO.NET Provider





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

    The following code examples show how to perform common data operations using ADO.NET in a provider neutral matter. This means you can switch between SQL Server and VistaDB (or even other databases) just by changing the provider in the connection string.  Follow the instructions in ADO.NET Factory Objects in your app.config or web.config to set up your application to use factory objects.

    As you read these examples you'll see a lot of repetitive code.  In your application you can dramatically reduce this by creating a function for getting a connection (with your connection string and the current provider) and for creating parameters.  At that point the code is about the same number of lines as provider-specific code.

     Connecting To A Database

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

    ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["YourAppConnection"];
    DbProviderFactory factory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
    using (DbConnection connection = factory.CreateConnection())
    {
        connection.ConnectionString = connectionStringSettings.ConnectionString;
        connection.Open();
    }
    
    Dim connectionStringSettings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("YourAppConnection")
    Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName)
    Using connection As DbConnection = factory.CreateConnection()
         connection.ConnectionString = connectionStringSettings.ConnectionString
         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.

    ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["YourAppConnection"];
    DbProviderFactory factory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
    using (DbConnection connection = factory.CreateConnection())
    {
        connection.ConnectionString = connectionStringSettings.ConnectionString;
        connection.Open();
    
        using (DbCommand command = connection.CreateCommand())
        {
            //now we can use the command to do things, it's already associated with the connection.
        }
    }
    
    Dim connectionStringSettings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("YourAppConnection")
    Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName)
    Using connection As DbConnection = factory.CreateConnection()
         connection.ConnectionString = connectionStringSettings.ConnectionString
         connection.Open()
    
         Using command As DbCommand = connection.CreateCommand()
              'now we can use the command to do things, it's already associated with the 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.

    ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["YourAppConnection"];
    DbProviderFactory factory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
    using (DbConnection connection = factory.CreateConnection())
    {
        connection.ConnectionString = connectionStringSettings.ConnectionString;
        connection.Open();
    
        using (DbCommand command = connection.CreateCommand())
        {
            command.CommandText = "INSERT INTO MyTable (MyColumn) VALUES (12)";
            command.ExecuteNonQuery();
        }
    }
    
    Dim connectionStringSettings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("YourAppConnection")
    Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName)
    Using connection As DbConnection = factory.CreateConnection()
          connection.ConnectionString = connectionStringSettings.ConnectionString
          connection.Open()
    
          Using command As DbCommand = connection.CreateCommand()
                command.CommandText = "INSERT INTO MyTable (MyColumn) VALUES (12)"
                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.

    ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["YourAppConnection"];
    DbProviderFactory factory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
    using (DbConnection connection = factory.CreateConnection())
    {
        connection.ConnectionString = connectionStringSettings.ConnectionString;
        connection.Open();
    
        using (DbCommand command = connection.CreateCommand())
        {
            int Age = 21;
    
            command.CommandText = "INSERT INTO MyTable (MyColumn) VALUES (@age)";
            DbParameter newParameter = command.CreateParameter();
            newParameter.DbType = DbType.Int32;
            newParameter.ParameterName = "@age";
            newParameter.Value = Age;
            command.Parameters.Add(newParameter);
            command.ExecuteNonQuery();
        }
    }
    
    Dim connectionStringSettings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("YourAppConnection")
    Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName)
    Using connection As DbConnection = factory.CreateConnection()
          connection.ConnectionString = connectionStringSettings.ConnectionString
          connection.Open()
    
          Using command As DbCommand = connection.CreateCommand()
                Dim Age As Integer = 21
    
                command.CommandText = "INSERT INTO MyTable (MyColumn) VALUES (@age)"
                Dim newParameter As DbParameter = command.CreateParameter()
                newParameter.DbType = DbType.Int32
                newParameter.ParameterName = "@age"
                newParameter.Value = Age
                command.Parameters.Add(newParameter)
                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.

    ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["YourAppConnection"];
    DbProviderFactory factory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
    using (DbConnection connection = factory.CreateConnection())
    {
        connection.ConnectionString = connectionStringSettings.ConnectionString;
        connection.Open();
    
        DataTable table = new DataTable();
    
        using (DbCommand command = connection.CreateCommand())
        {
            command.Connection = connection;
            command.CommandText = "SELECT * FROM MyTable";
    
            using (DbDataAdapter adapter = factory.CreateDataAdapter())
            {
                adapter.SelectCommand = command;
                adapter.Fill(table);
            }
        }
    }
    
    Dim connectionStringSettings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("YourAppConnection")
    Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName)
    Using connection As DbConnection = factory.CreateConnection()
          connection.ConnectionString = connectionStringSettings.ConnectionString
          connection.Open()
    
          Dim table As New DataTable()
    
          Using command As DbCommand = connection.CreateCommand()
                command.Connection = connection
                command.CommandText = "SELECT * FROM MyTable"
    
                Using adapter As DbDataAdapter = factory.CreateDataAdapter()
                      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.

    ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["YourAppConnection"];
    DbProviderFactory factory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
    using (DbConnection connection = factory.CreateConnection())
    {
        connection.ConnectionString = connectionStringSettings.ConnectionString;
        connection.Open();
    
        using (DbCommand command = connection.CreateCommand())
        {
            command.CommandText = "CalcSalesTax";
            command.CommandType = CommandType.StoredProcedure;
                        
            DbParameter newParameter = command.CreateParameter();
            newParameter.DbType = DbType.Currency;
            newParameter.Direction = ParameterDirection.Input;
            newParameter.ParameterName = "@SalesTotal";
            newParameter.Value = 100.00;
            command.Parameters.Add(newParameter);
            command.ExecuteNonQuery();
        }
    }
    
    Dim connectionStringSettings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("YourAppConnection")
    Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName)
    Using connection As DbConnection = factory.CreateConnection()
          connection.ConnectionString = connectionStringSettings.ConnectionString
          connection.Open()
    
          Using command As DbCommand = connection.CreateCommand()
                command.CommandText = "CalcSalesTax"
                command.CommandType = CommandType.StoredProcedure
    
                Dim newParameter As DbParameter = command.CreateParameter()
                newParameter.DbType = DbType.Currency
                newParameter.Direction = ParameterDirection.Input
                newParameter.ParameterName = "@SalesTotal"
                newParameter.Value = 100.0
                command.Parameters.Add(newParameter)
                command.ExecuteNonQuery()
          End Using
    End Using
    
    See Also