LICT .NET Training | Kamal Protap Sir | Part 2

07.11.17 Vids
DataAdapater vs DataReader:

SqlDataReader row by row executes from the table

ExecuteReader() always executes the query to the data reader object.

Using data reader:

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


namespace WindowsFormsApp1
{
    public partial class DataAdaptervsDatareader : Form
    {
        public DataAdaptervsDatareader()
        {
            InitializeComponent();
        }

        private void DataAdaptervsDatareader_Load(object sender, EventArgs e)
        {
            string ConString = "Server=ZAKIHP-PC\\ZAKISQL;Database=PracticeByMe;Trusted_Connection=Yes";
            SqlConnection conn = new SqlConnection(ConString);
            string querystring = "select * from tbl_ProductInfo";
            conn.Open();
            SqlCommand cmd=new SqlCommand(querystring,conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                MessageBox.Show("Name: " + reader["name"] + "Price: "+ reader["price"]+"Dates: " + reader["dates"]);
                //MessageBox.Show(reader[0] + reader[1] + reader[2]);
            }
            conn.Close();

        }
    }
}

Using data adapter:

Difference between DataReader and DataAdapter:

c# – SqlDataAdapter vs SqlDataReader – Stack Overflow

stackoverflow.com

Using data adapter:
First we call data adapter then data set

  SqlDataAdapter Da = new SqlDataAdapter(cmd);
  DataSet ds=new DataSet();

DataSet is a disconnected architecture
DataReader is a connected architecture

DataRow()

With DataAdapter:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace ADONETPRGS
{
    public partial class Dataadaptervsdatareader : Form
    {
        public Dataadaptervsdatareader()
        {
            InitializeComponent();
        }

        private void Dataadaptervsdatareader_Load(object sender, EventArgs e)
        {
            string ConString = "Data Source=DESKTOP-0MSCGUP;Initial Catalog=Lict_Dotnet;User ID=sa;Password=sa123";
            SqlConnection con = new SqlConnection(ConString);
            string querystring = "Select * from tbl_Product";
            con.Open();
            SqlCommand cmd = new SqlCommand(querystring, con);

            SqlDataAdapter Da = new SqlDataAdapter(cmd);
            
            DataSet Ds = new DataSet();
            Da.Fill(Ds);
            //SqlDataReader reader = cmd.ExecuteReader();
            // con.Close();
            foreach (DataRow Objrow in Ds.Tables[0].Rows)
            {
                MessageBox.Show("Name:" + Objrow["Name"] + " " + "Price:" + Objrow["Price"]);
            }


            //while (reader.Read())
            //{
            //    MessageBox.Show(reader["Name"] + " : " + reader["Price"]);
            //}
            // con.Close();

        }
    }
}

Whole datagridview has been binded with the data.

Stored procedure in Our Application:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApp1
{
    public partial class Storedprocedure : Form
    {
        public Storedprocedure()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection connection;
            SqlDataAdapter adapter;

            DataSet ds=new DataSet();
            int i = 0;
            string connectionString = "Server=ZAKIHP-PC\\ZAKISQL;Database=PracticeByMe;Trusted_Connection=Yes";
            connection = new SqlConnection(connectionString);
            SqlCommand cmd=new SqlCommand("SelectProduct", connection);
            cmd.CommandType = CommandType.StoredProcedure;
 
            adapter =new SqlDataAdapter(cmd);
            adapter.Fill(ds);

           for (i=0;i<=ds.Tables[0].Rows.Count-1;i++)
           {
               MessageBox.Show(ds.Tables[0].Rows[i][0].ToString() + " " + ds.Tables[0].Rows[i][1].ToString());
           }
        }
    }
}