Connection String in ADO.NET

When your application connects to a database or a data file you let ADO or ADO.Net utilize a provider to do the job for you
  • 2837

Introduction

When your application connects to a database or a data file you let ADO or ADO.Net utilize a provider to do the job for you. The connection string contains the information that the provider need to know to be able to establish a connection to the database or the data file. Because there are different providers and each providers have multiple ways to make a connection there are many different ways to write a connection string. The connection string is really just a string inside your application. A connection string contains initialization information that is passed as a parameter from a data provider to a data source. The syntax depends on the data provider and the connection string is parsed during the attempt to open a connection. Syntax errors generate a run-time exception but other errors occur only after the data source receives connection information. Connection strings are used when the SQLDriverConnect and SQLBrowseConnect ODBC API functions are called. They may specify as little as a data source name or as much as an entire data source configuration. The SQLDriverConnect function will interactively complete a connection string, if necessary and if the calling program indicates that it wants this behavior by prompting the user for the missing information.

In ADO.NET you create and manage connections using connection objects

  • SqlConnection : An object that manages a connection to a SQL Server version 7.0 or later. It is optimized for use with SQL Server 7.0 or later by  bypassing the OLE DB layer.
  •  OleDbConnection : An object that manages a connection to any data store accessible via OLE DB.
  • OdbcConnection : An object that manages a connection to a data source created by using a connection string or ODBC data source name (DSN).
  • OracleConnection : An object that manages a connection to Oracle databases.

Example : Simple example of a connection string in ADO.NET.

Code

using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
    static void Main()
    {
        string connectionString =
            "Data Source=(local);Initial Catalog=manish;"
            + "Integrated Security=true";
        string queryString =
            "SELECT ProductID, UnitPrice, ProductName from dbo.products "
                + "WHERE UnitPrice > @pricePoint "
                + "ORDER BY UnitPrice DESC;";
        int paramValue = 5;
        using (SqlConnection connection =
            new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(queryString, connection);
            command.Parameters.AddWithValue("@pricePoint", paramValue);
            try
            {
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine("\t{0}\t{1}\t{2}",
                        reader[0], reader[1], reader[2]);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.ReadLine();
        }
    }

}

© 2020 DotNetHeaven. All rights reserved.