Intoduction of ADO.NET with Examples of Database Connectivity (Continue...)

Continue from previous post

Now I am giving a practical approach of ADO.NET

There are 2 ways to storing data into database.

1.     Connection Oriented Architecture

2.     Disconnected Oriented Architecture (Implicitly using ADO.NET)

-         Now I explain 2 examples  in each example there are 4 buttons (Create, Retrieve, Update, and Delete)

-         For every program I used my ‘Connection String ‘you have to use your ‘Connection String’.

-         For getting that string

1.     Create your database and table in sql server.

2.     Open ‘SQL Server Object Explorer’ of Visual Studio and add database in your application using ‘SQL Server Object Explorer’

3.     In ‘SQL Server Object Explorer’ right click on your database and go to Properties and copy and use that ‘Connection String’.

 

·        Following table schema I have used.

 

In any programing language there are 5 steps for database operation

1.     Establish the connection

2.     Open that connection

3.     Execute your query

4.     Get the result(if any)

5.     Close the connection

  1. Connection Oriented Architecture

Design:

Explanation: 

1.     This application store your data in your database

2.     For that in code section open the connection, execute your sql query  and close the connection

3.     For manipulating your data you have to open and close database connection (Maintain the connection) otherwise it will through exception.

4.     In this example your application directly connected with your database

5.     For manipulation your data not in memory, manipulation directly done on database.

Code:

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace AdoCrudOperation

{

    public partial class WebForm2 : System.Web.UI.Page

    {

        // This program demonstrate CRUD operation in connected access using asp.net in C#

        const String conStr = @"Your Database Connection String";

        SqlConnection con;

        protected void Page_Load(object sender, EventArgs e)

        {

        }

        protected void Button1_Click(object sender, EventArgs e)

        {

            //inserting record into DB

            con = new SqlConnection(conStr);

          

            int id = Convert.ToInt32(TextBox1.Text);

            string name = TextBox2.Text;

            int mob_no = Convert.ToInt32(TextBox3.Text);

            string strQuery = "insert into Employee(ID,Name,Mob_No)values("+id+",'"+name+"',"+mob_no+")";

            SqlCommand cmd = new SqlCommand(strQuery,con);

            con.Open();

            cmd.ExecuteNonQuery();

            con.Close();

        }

        protected void Button2_Click(object sender, EventArgs e)

        {

            //retriving record from DB

            con = new SqlConnection(conStr);

            int id = Convert.ToInt32(TextBox1.Text);

            string strQuery = "Select * from Employee Where ID="+id+"";

            SqlCommand cmd = new SqlCommand(strQuery, con);

            con.Open();

            SqlDataReader dr = cmd.ExecuteReader();

            if(dr.Read())

            {

                TextBox2.Text=dr["Name"].ToString();

                TextBox3.Text=dr["Mob_No"].ToString();

            }

            con.Close();

        }

        protected void Button3_Click(object sender, EventArgs e)

        {

            //updating record into DB

            con = new SqlConnection(conStr);

            int id = Convert.ToInt32(TextBox1.Text);

            string name = TextBox2.Text;

            int mob_no = Convert.ToInt32(TextBox3.Text);

            string strQuery = "update Employee set Name='"+name+"',Mob_No="+mob_no+" where ID="+id+"";

            SqlCommand cmd = new SqlCommand(strQuery, con);

            con.Open();

            cmd.ExecuteNonQuery();

            con.Close();

        }

        protected void Button4_Click(object sender, EventArgs e)

        {

            //deleting record from DB

            con = new SqlConnection(conStr);

            int id = Convert.ToInt32(TextBox1.Text);

            string strQuery = "delete from Employee where ID=" + id + "";

            SqlCommand cmd = new SqlCommand(strQuery, con);

            con.Open();

            cmd.ExecuteNonQuery();

            con.Close();

        }

    }

}

 2. Disconnected Oriented Architecture (Implicitly using ADO.NET)

  What is a Disconnected Oriented Architecture

1.     Disconnected mode is disconnected connection oriented.

2.     In Disconnection mode, we read data from a database by using a DataSet object.

3.     Disconnection mode get low in speed and performance.

4.     Disconnection mode can hold multiple tables of data.

5.     We can perform all option as like update, insert, delete etc.

v Why we use Disconnected Oriented Architecture
          In Disconnected mode, we are used DataSet for retrieving data from database. So we are not need to maintaining the connection also.  We can be performed all the operations with the data. It won’t cause traffic problem" while working with database.

Design:

 

 

Explanation:

1.     This application store your data in your database

2.     For that there is no need to open and close database connection in code section.

3.     Only send your query to DataAdapter and call fill() method of DataAdapter.

4.     Fill() method will take care of maintaining the connection.

5.      In this example your application indirectly connected with your database(through    DataAdapter)

6.     While manipulation your data in memory (DataSet), so manipulation directly done on DataSet and update() method of DataAdapter stores that data into database.

Code:

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace AdoCrudOperation

{

// This program demonstrate CRUD operation in dissconeced(implicit) access using asp.net in C# 

    public partial class WebForm3 : System.Web.UI.Page

    {

        const String conStr = @"Your Database Connection String";

        SqlConnection con;

        protected void Page_Load(object sender, EventArgs e)

        {

        }

        protected void Button1_Click(object sender, EventArgs e)

        {

            //Insert a record into DB

            int id = Convert.ToInt32(TextBox1.Text);

            string name = TextBox2.Text;

            int mob_no = Convert.ToInt32(TextBox3.Text);

            string strQuery = "insert into Employee values(" + id + ",'" + name + "','" + mob_no + "'); ";

             con = new SqlConnection(conStr);

            SqlDataAdapter adp = new SqlDataAdapter(strQuery, con);

            DataTable dt = new DataTable();

            adp.Fill(dt);

            adp.Update(dt);

           

        }

        protected void Button2_Click(object sender, EventArgs e)

        {

            //Retriving record from DB

            int id = Convert.ToInt32(TextBox1.Text);

            string strQuery = "select * from Employee where ID=" + id + "";

            SqlConnection con = new SqlConnection(conStr);

            SqlDataAdapter adp = new SqlDataAdapter(strQuery, con);

            DataSet ds = new DataSet();

            adp.Fill(ds, "MyEmployee");

            TextBox2.Text = ds.Tables["MyEmployee"].Rows[0]["Name"].ToString();

            TextBox3.Text = ds.Tables["MyEmployee"].Rows[0]["Mob_No"].ToString();

        }

        protected void Button3_Click(object sender, EventArgs e)

        {

            //updating record into DB

            int id = Convert.ToInt32(TextBox1.Text);

            string name = TextBox2.Text;

            int mob_no = Convert.ToInt32(TextBox3.Text);

            string strQuery = "update  Employee set Name='"+name+"',Mob_No="+mob_no+" where ID="+id+"";

            SqlConnection con = new SqlConnection(conStr);

            SqlDataAdapter adp = new SqlDataAdapter(strQuery, con);

            DataTable dt = new DataTable();

            adp.Fill(dt);

            adp.Update(dt);

     

        }

        protected void Button4_Click(object sender, EventArgs e)

        {

            //deleting record from DB

            int id = Convert.ToInt32(TextBox1.Text);

            string strQuery = "delete from Employee where ID=" + id + "";

            SqlConnection con = new SqlConnection(conStr);

            SqlDataAdapter adp = new SqlDataAdapter(strQuery, con);

            DataTable ds = new DataTable();

            adp.Fill(ds);

            adp.Update(ds);

        }

    }

}

Web References:

1.     en.wikipedia.org

2.     msdn.microsoft.com

3.     dotnethearts.blogspot.in

4.     tutorialspoint.com

5.       c-sharpcorner.com

                                                                            

                                                                     -Created By Ashutosh Jagtap,         

         For any grievance kindly mail us on

           ashujagtap333@gmail.com