Export (0) Print
Expand All

DbConnection, DbCommand and DbException

Once you have created a DbProviderFactory and a DbConnection, you can then work with commands and data readers to retrieve data from the data source.

This example takes a DbConnection object as an argument. A DbCommand is created to select data from the Categories table by setting the CommandText to a SQL SELECT statement. The code assumes that the Categories table exists at the data source. The connection is opened and the data is retrieved using a DbDataReader.

// Takes a DbConnection and creates a DbCommand to retrieve data 
// from the Categories table by executing a DbDataReader.  
static void DbCommandSelect(DbConnection connection)
{
    string queryString =
        "SELECT CategoryID, CategoryName FROM Categories";

    // Check for valid DbConnection. 
    if (connection != null)
    {
        using (connection)
        {
            try
            {
                // Create the command.
                DbCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                command.CommandType = CommandType.Text;

                // Open the connection.
                connection.Open();

                // Retrieve the data.
                DbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine("{0}. {1}", reader[0], reader[1]);
                }
            }

            catch (Exception ex)
            {
                Console.WriteLine("Exception.Message: {0}", ex.Message);
            }
        }
    }
    else
    {
        Console.WriteLine("Failed: DbConnection is null.");
    }
}

This example takes a DbConnection object as an argument. If the DbConnection is valid, the connection is opened and a DbCommand is created and executed. The CommandText is set to a SQL INSERT statement that performs an insert to the Categories table in the Northwind database. The code assumes that the Northwind database exists at the data source, and that the SQL syntax used in the INSERT statement is valid for the specified provider. Errors occurring at the data source are handled by the DbException code block, and all other exceptions are handled in the Exception block.

// Takes a DbConnection, creates and executes a DbCommand.  
// Assumes SQL INSERT syntax is supported by provider. 
static void ExecuteDbCommand(DbConnection connection)
{
    // Check for valid DbConnection object. 
    if (connection != null)
    {
        using (connection)
        {
            try
            {
                // Open the connection.
                connection.Open();

                // Create and execute the DbCommand.
                DbCommand command = connection.CreateCommand();
                command.CommandText =
                    "INSERT INTO Categories (CategoryName) VALUES ('Low Carb')";
                int rows = command.ExecuteNonQuery();

                // Display number of rows inserted.
                Console.WriteLine("Inserted {0} rows.", rows);
            }
                // Handle data errors. 
            catch (DbException exDb)
            {
                Console.WriteLine("DbException.GetType: {0}", exDb.GetType());
                Console.WriteLine("DbException.Source: {0}", exDb.Source);
                Console.WriteLine("DbException.ErrorCode: {0}", exDb.ErrorCode);
                Console.WriteLine("DbException.Message: {0}", exDb.Message);
            }
                // Handle all other exceptions. 
            catch (Exception ex)
            {
                Console.WriteLine("Exception.Message: {0}", ex.Message);
            }
        }
    }
    else
    {
        Console.WriteLine("Failed: DbConnection is null.");
    }
}

The DbException class is the base class for all exceptions thrown on behalf of a data source. You can use it in your exception handling code to handle exceptions thrown by different providers without having to reference a specific exception class. The following code fragment demonstrates how to use DbException to display error information returned by the data source using GetType, Source, ErrorCode, and Message properties. The output will display the type of error, the source indicating the provider name, an error code, and the message associated with the error.

    try
    {
        // Do work here.
    }
    catch (DbException ex)
    {
        // Display information about the exception.
        Console.WriteLine("GetType: {0}", ex.GetType());
        Console.WriteLine("Source: {0}", ex.Source);
        Console.WriteLine("ErrorCode: {0}", ex.ErrorCode);
        Console.WriteLine("Message: {0}", ex.Message);
    }
    finally
    {
        // Perform cleanup here.
    }
Show:
© 2014 Microsoft