Connection Object - Connecting to Database using C# ADO.Net

What is Connection Object in ADO.Net?

Connection Object is used for connecting your application to data source or database. It carries required authentic information like username and password in the connection string and opens a connection. You need to different type of connection object for different type of data providers. For example:

What is Connection String?

Connection String combines all the required authentic information that is used for connecting to a Data Source, like Server Name, Database Name, User Name, Password etc. It is just a single line string that is used by connection object to connect to the database. A connection string looks like this.

Data Source=.\SQLEXPRESS;Initial Catalog=TestDB;User ID=sa;Password=System123;Pooling=False

How to Store Connection String in web.config file?

In order to connect with Database, it is mandatory to keep connection string in a safe and centralized location. It is not recommended to writing connection string in each and every connection. You can store the connection string in Web.config file, app.config file or into a class file.

Web.config File – Add and Retrieve Connection String

If you are developing ASP.Net Project or ASP Web Project then you can store the connection string in Web.config file.

1. Open Web.config file from solution Explorer
2. Paste following code Just Before </Configuration>

 <connectionStrings>
    <add name="StudentConn" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=StudentDB;Integrated Security=True;Pooling=False"/>
  </connectionStrings>

Access Connection String from web.config

You can access this connection string in ASP.NET MVC, like this

using System.Configuration;

namespace ConnectionString_Example.Controllers
{
    public class conString
    {
        public string getConString()

        {
            string constring = ConfigurationManager.ConnectionStrings["studentconn"].ToString();
            return constring;
        }
    }
}

App.config File – Add and Retrieve Connection String

If you are working on the windows form, you can save connection string in App.config file.

Add Connection String into app.config

  <connectionStrings>
    <add name="StudentConn" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TestDB;User ID=sa;Password=System123;Pooling=False"/>
  </connectionStrings>

Retrieve Connection String from app.config

using System;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace FirstForm
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            var ConString = ConfigurationManager.ConnectionStrings["StudentConn"].ConnectionString;
            SqlConnection con = new SqlConnection(ConString);
            con.Open();
        }
    }
}

If ConfigurationManager Object does not appear or giving reference error, then you should add reference to ConfigurationManager Object like this.

1. Right Click on References in Solution Explorer and select Add Reference…

2. Reference Manager window will appear. Here, Choose Framework in left panel and then find and check System.Configuration in Middle Panel.

3. Click OK to add Reference.

Connect to DataSource

Example

SqlConnection con = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=TestDB;User ID=sa;Password=System123;Pooling=False";            );           
  con.Open();
  // Update, Insert Delete Job in Table
  con.Close();

Summary

In this chapter, I tried to make you understand Connection Object and Connection String in ADO.Net. You also learned how to store and retrieve connection string from web.config file or App.config file. In the next chapter you will learn how to Create, Select, Delete or Update database using C# ADO.Net.