C# is a language. The classes defined in the class libraries are used to communicate with the database and we are using C# language to interact with the database. In Ado.Net components we have number of classes that are responsible to communicate with the database.

Introducing ADO.NET

Ado.Net is an Object-oriented set of libraries that allows you to interact with data sources. Following are the data providers in ado.net.

1. System.Data.SqlClient:- contains classes that communicate with Ms SQLServer.


2. System.Data.OracleClient:- communicate with Oracle.


3. System.Data.Oledb:- communicate to a data source that has an OLEDB provider.


4. System.Data.Odbc:- communicate to a data source that has an ODBC provider

ADO.NET OBJECTS

Ado.net includes many objects you can use to work with data. Some of the primary objects are as follows:-

1. SqlConnection:- To interact with a database, you must have a connection to it. The connection objects helps identifying the database server, database name, user name and password. The connection object is used by command objects.


2. SqlCommand:- You can use this object to send SQL statements to the database. This can represent a SQL Statement or stored procedure.


3. SqlDataReader:- The data reader object allows you to obtain the results of a Select statement from a command object. The data returned from a data reader is a fast forward-only stream of data. This fetch data in a sequential manner.


4. DataSet:- You can imagine dataset as a database. This contain multiple datatable objects, which further contains columns and rows objects. We can also define relationship among tables in the dataset. This is disconnected approach.


5. SqlDataAdapter:- The SqlDataAdapter is used to fill dataset object when reading the data and writes in a single batch when persisting changes back to the database. A data adapter contains a reference to the connection object and opens and closes the connection automatically when reading from or writing to the data base. The data adapter contains 4 command objects i.e. SELECT, UPDATE, DELETE, and INSERT

Example: Demonstrate Database Interaction

This example will provide the interaction with the database. The user will enter his credentials, if the user is valid and the credentials matched with the database, he is able to view all the other users' credentials that are valid.

Form20.cs

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace cSHARPEXAMPLES

{

  public partial class Form20 : Form

    {

    public Form20()

        {

            InitializeComponent();

        }

    private void btnLogin_Click( object sender, EventArgs e)

        { //Login

      try

            {

                SqlConnection con = null ;

                string conStr = "" ;

                int _ExistFlag = 0;

                conStr = "Data Source=localhost;initial catalog=TestDB;uid=sa;pwd=sa" ;

        con = new SqlConnection (conStr);

        _ExistFlag = fnUserExists(con,txtName.Text,txtPassword.Text);

            if (_ExistFlag == 0)

                { //Not A Valid User

          MessageBox .Show( "Not A Valid User" );

                }

        else

                { // If Valid user Than Display all the Valid User that are there in the DataBase

          MessageBox .Show( "Valid User :: Display All the Valid User" );

                    fillgridWithValidUser(con);

                }

            }

      catch ( Exception ex)

            {

        MessageBox .Show( "Exception Occurs.........." +ex.Message);

            }

      finally

            {

        txtName.Text = "" ;

        txtPassword.Text = "" ;

            }

        }

    /*

    * Function: Whether a Valid User or Not

    * Depending on the creadentials enter by user

    */

    private int fnUserExists( SqlConnection con, string strUserName, string strPassword)

        {

          SqlCommand cmd = null ;

          int _ExistFlag = 0;

          cmd = new SqlCommand ( "select count(*) from tblLogin where UserName=@UserName and password=@Password " , con);

          cmd.Parameters.Add( "@UserName" , SqlDbType.NVarChar).Value = strUserName;

          cmd.Parameters.Add( "@Password" , SqlDbType.NVarChar).Value = strPassword;

          cmd.CommandType = CommandType .Text;

          con.Open();

      _ExistFlag = Int16 .Parse(cmd.ExecuteScalar().ToString());

          con.Close();

      return _ExistFlag;

          }

    /*

    * if the Credentials Enter By user is Valid:

    * Then Display List Of all The Other Valid Users.

    */

    private void fillgridWithValidUser( SqlConnection con)

        {

          SqlDataAdapter ada = null ;

          DataSet dst = null ;

          ada = new SqlDataAdapter ( "select * from tblLogin" , con);

          dst = new DataSet ();

          ada.Fill(dst, "Login" );

        dataGridView1.DataSource = dst.Tables[0].DefaultView;

        }

    }

}

Output:

On Click: Login Button