ADO.NET stands for ActiveX Data Object. ADO.NET is a set of classes and methods for connecting to .Net application (window form, web form, etc.) to database for applying CRUD (create, read, update and delete) operation on database.

  In database connection is use to connection string for connecting to database. The connection string use in .net programming for connecting to .net application with database. This connection string passes in configuration file use the connectionstring tag in config file. This connection string tag take ADD tag for adding the attribute in connection string. We can add more than on connection string, the application use this string at runtime for fetching database value for database.

 In ADO.NET has two architecture

 Every time connection stablish with database. This architecture request for data multiple time.

But doesn’t store in temporary.

    The application automatically connect and disconnect during the processing. The application store temporary data store in application using Dataset.

That use ADO.NET to connect to database (SQL Server, Oracle server, MySQL, DB Brower).

Classes in ADO.NET for database connection and CRUD operation

 The connection string use in .net programming for connecting to .net application with database. This connection string passes in web.config and app.config file using the connectionstring tag in config file. This connection string tag take ADD tag for adding the attribute in connection string. We can add more than on connection string, the application use this string at runtime for fetching database value for database.

In ADD tag have attributes for adding the connection details

• Name

• Connection string

• Provider name

In the connection string attribute we can give

• Server

• Database name

• Initial catalog

• Connection User id

• Connection Password

• Connection Time out

• Integrated security

• Connection pool size

• Encryption

<?xml version='1.0' encoding='utf-8'?>

<configuration>

  <connectionStrings>

     <add name='myConnection' connectionString='server=localhost;database=mydatabase;' />

  </connectionStrings>

</configuration>

Fetching the connection string is done using the ConfigurationManager in class program.

string conn = ConfigurationManager.ConnectionStrings['myConnection'].ConnectionString;

Example of database connection

using System;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace WindowsApplication1

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)

        {

            string connetionString = null;

            SqlConnection cnn ;

   connetionString = 'Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password'

            cnn = new SqlConnection(connetionString);

            try

            {

                cnn.Open();

                MessageBox.Show ('Connection Open ! ');

                cnn.Close();

            }

            catch (Exception ex)

            {

                MessageBox.Show('Can not open connection ! ');

            }

        }

    }

}

Server Connection

<connectionStrings>

  <add

    name='sqlServer'

    providerName='System.Data.SqlClient'

    connectionString='Data Source=localhost;Initial Catalog=MyDatabase;Integrated Security=True;' />

</connectionStrings>

Connection timeout

<connectionStrings>

  <add

    name='sqlServer'

    providerName='System.Data.SqlClient'

    connectionString='Data Source=localhost;Initial Catalog=MyDatabase;Integrated Security=True;Connection Timeout=60' />

</connectionStrings>

Username and password

<connectionStrings>

  <add

    name='sqlServer'

    providerName='System.Data.SqlClient'

    connectionString='Data Source=localhost;Initial Catalog=MyDatabase;User Id=user;Password=pwd;' />

</connectionStrings>

Mysql

<connectionStrings>

  <add

    name='mySql'

    providerName='MySql.Data.MySqlClient'

    connectionString='Server=localhost;Database=MyDatabase;Uid=user;Pwd=pwd;' />

</connectionStrings>

Integrity security

<connectionStrings>

  <add

    name='mySql'

    providerName='MySql.Data.MySqlClient'

    connectionString='Server=localhost;Database=MyDatabase;IntegratedSecurity=yes;Uid=auth_windows;' />

</connectionStrings>

Connection pool

<connectionStrings>

  <add

    name='mySql'

    providerName='MySql.Data.MySqlClient'

    connectionString='...;MinimumPoolSize=10;maximumpoolsize=50;' />

</connectionStrings>