How to use connection/disconnection objects?

Connection objects 

We have 2 types of object which are used to communicate with database.

1.      Connection Oriented Objects :       SqlCommand

2.      DisConnected Oriented Objects:   SqlDataAdapter

                                                                     Connection Oriented Objects:

Connection Oriented Objects are Provider specific objects means

For SqlServer --àsystem.Data.SqlConnection   ----àSqlCommand

For Oracle-à System.Data.OracleConnection----àOracleCommand.

DisConnected objects:

System.Data.SqlClient--------------àSqlDataAdapter

System.Data.OracleClient---------- > OracleDataAdapter

Connection Oriented architecture

SqlCommand Object :

 SqlCommand is Connection Oriented object which needs a valid opened connection to communicate with database.

Which can’t open the connection implicitly.so required one valid connection to the database.

System.Data.SqlClient----------------SqlCommand

System.Data.OracleClient----------OracleCommand

System.Data.Oledd--------------------OledbCommand

SqlCommand object can perform any type of operations like dml,ddl,tcl, operations. It have methods and properties. While interacting with database.

Methods: ExecuteNonQuery,ExecuteReader,ExecuteSclar….ect

Properties: Connectoion,CommandType,Container…ect

Ex:

using SqlCommand Inserting a record into database.

1^st Model:

           string str = @"Server=.\SQLEXPRESS;Database=PropercodeDB;Trusted_Connection=True;";

            SqlConnection con = new SqlConnection(str);

            con.Open();

            string stext = "insert into customer values('" + txtCustName.Text + "','" + txtClocation.Text + "')";

            DbCommand/SqlCommand cmd = new SqlCommand(stext, con);

            int i=cmd.ExecuteNonQuery();

            MessageBox.Show("Inserted Succecully");

In rightside of sqlcommand we can use DbCommand also why because Sqlcommand derived from DbCommand

2^nd Model:

using stored procedure

create procedure ProCuster_Insert

@cname nvarchar(20),@cloc nvarchar(20)

as

begin

 insert into Customer values(@cname,@cloc)

end

Ex:

    DbCommand/SqlCommand cmd = new SqlCommand("ProCuster_Insert", con);         

            cmd.Parameters.Add(new SqlParameter("@cname", txtCustName.Text));

            cmd.Parameters.Add(new SqlParameter("@cloc", txtClocation.Text));

            cmd.CommandType = CommandType.StoredProcedure;         /odel

     ccecullydureand we can use DbCommand also why because Sqlcommand derived from DbCommandd classes.

cate with database.          

            int i=cmd.ExecuteNonQuery();       

           MessageBox.Show("Inserted Succecully");

Note: Parameter names should be same in database as well as in application also--@cname,@cloc

3^rd Model:

string str = @"Server=.\SQLEXPRESS;Database=PropercodeDB;Trusted_Connection=True;";

            SqlConnection con = new SqlConnection(str);

            con.Open();

            DbCommand cmd = con.CreateCommand(); // wcf createChannel

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = "ProCuster_Insert";

            DbParameter cname = cmd.CreateParameter();

            cname.ParameterName = "@cname";

            cname.Value = txtCustName.Text;

            cmd.Parameters.Add(cname);

            DbParameter cloc = cmd.CreateParameter();

            cloc.ParameterName = "@cloc";

            cloc.Value = txtClocation.Text;

            cmd.Parameters.Add(cloc);

            int i=cmd.ExecuteNonQuery();           

            MessageBox.Show("Inserted Succefully….");

SqlDataReader:

This object used to read the data from the result return by the database query.

When query for data retrival means select command this object is very useful.

Methods: Read,HasRows,IsClosed,…..ect

string str = @"Server=.\SQLEXPRESS;Database=PropercodeDB;Trusted_Connection=True;";

            SqlConnection con = new SqlConnection(str);

            con.Open();

            SqlCommand cmd = new SqlCommand();

            cmd.Connection = con;

            cmd.CommandText = "select * from Customer";

            IDataReader/DbDataReader/SqlDataReader  dr=cmd.ExecuteReader();

            while (dr.Read()==true)

            {

               

                txtCustName.Text = dr[1].ToString();

                txtClocation.Text = dr[2].ToString();

           

            }

            con.Close();

            MessageBox.Show("Retrived succefully");

If condition retrives First record , and while condition retrives last record from the result.

Using DataReader read total data to an object like datatable,dataset.

LIKE

string str = @"Server=.\SQLEXPRESS;Database=PropercodeDB;Trusted_Connection=True;";

            SqlConnection con = new SqlConnection(str);

            con.Open();

            SqlCommand cmd = new SqlCommand();

            cmd.Connection = con;

            cmd.CommandText = "select * from Customer";

            IDataReader  dr=cmd.ExecuteReader();

            DataTable dt=new DataTable();

            dt.Load(dr, LoadOption.Upsert);

            GridView1.DataSource = dt;

            GridView1.DataBind();

            con.Close();

ExecuteSclar()-------------retrive single value from return result. Which useful when a cenerio user exist or not  return type is OBJECT TYPE.

Ex: if user exists in the database procedure returns ture then use ExecuteSclar then ExecuteReader();

                                                  -----------------------Factory Pattern---------------------------------


create procedure ProCuster_Insert
@cname nvarchar(20),@cloc nvarchar(20)
as
begin
 insert into customer values(@cname,@cloc)
end

string conn=ConfigurationManager.ConnectionStrings["con"].ConnectionString.ToString();

    DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
   DataSet ds;

    protected void Page_Load(object sender, EventArgs e)

    {

       

    }

    protected void btnSubmit_Click(object sender, EventArgs e)

    {

      

        DbConnection con = factory.CreateConnection();

        con.ConnectionString = conn;

        con.Open();

        DbCommand cmd = con.CreateCommand(); // wcf createChannel

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.CommandText = "ProCuster_Insert";

        DbParameter cname = cmd.CreateParameter();

        cname.ParameterName = "@cname";

        cname.Value = txtCustName.Text;

        cmd.Parameters.Add(cname);

        DbParameter cloc = cmd.CreateParameter();

        cloc.ParameterName = "@cloc";

        cloc.Value = txtClocation.Text;

        cmd.Parameters.Add(cloc);

        int i = cmd.ExecuteNonQuery();

        Label1.Text = "inserted succefully...."+i.ToString();

         

    }


create procedure ProCuster_AllCustomers
as
begin
select * from customer
end

  protected void GetAllcustomers_Click(object sender, EventArgs e)

    {

        DbConnection con = factory.CreateConnection();

        con.ConnectionString = conn;            

        DbDataAdapter da = factory.CreateDataAdapter(); 

        DbCommand cmd = con.CreateCommand(); // wcf createChannel

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.CommandText = "ProCuster_AllCustomers";

        da.SelectCommand = cmd;

        ds = new DataSet();

        da.Fill(ds);       

        GridView1.DataSource = ds;

        GridView1.DataBind();

        Label1.Text = "Available Customers...";

    }