Working with datareader concepts 

1)datareader holds a collection of records 

2)executeReader() returns datareader objects 

3)datareader is Readonly method 

4)datareader is an obstract class 

5)datareader is forward only

 

Example on datareader with oracle: 

Open windows forms app project 

Start->programs->Microsoft visual studio 2010->Microsoft Visual studio 2010->file menu->new-> 

project->select visual c# from installed templates->select windows forms application project 

place 3 labels & 3 textboxs 2 buttons with text connect  and nextrecord 

using System.Data.OleDb;

 

using System; 

using System.Collections.Generic; 

using System.ComponentModel; 

using System.Data; 

using System.Drawing; 

using System.Linq; 

using System.Text; 

using System.Windows.Forms; 

using System.Data.OleDb;

 

namespace WindowsFormsApplication19 

{ 

    public partial class Form1 : Form 

    { 

        public Form1()

        { 

            InitializeComponent(); 

        }

 

        static OleDbConnection cn = new OleDbConnection("userid=scoott;password=tiger;provider=msdara.1"); 

        OleDbCommand cmd = new OleDbCommand("select * from emp244", cn); 

        OleDbDataReader dr; 

        // in C# the object declared in general declaration, in case if that  

        //object need to be used again in general declaration, then that object need to be declared as static

 

        private void Form1_Load(object sender, EventArgs e) 

        {// code for form_1 load 

            button2.Enabled = false; 

        } 

       

        private void button1_Click(object sender, EventArgs e) 

        {// code for connect button 

            if (cn.State == ConnectionState.Closed) 

            { 

                cn.Open(); 

                dr = cmd.ExecuteReader(); 

                // display column names in labels 

                label1.Text = dr.GetName(0); 

                label2.Text = dr.GetName(1); 

                label3.Text = dr.GetName(2); 

                button2.Enabled = true; 

            } 

            else 

                MessageBox.Show("conn is already opend"); 

        } 

     } 

    }

 

     private void button2_Click(object sender, EventArgs e) 

     { // code for next record button  

            if (dr.Read() == true) 

            { 

                textBox1.Text = dr[0].ToString(); 

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

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

            } 

            else 

                MessageBox.Show("no more records"); 

        }

      

Example 2 on data reader 

Developing a dynamic form, which should work with any type of table with sql server

 

Open windows forms app project 

Start->programs->Microsoft visual studio 2010->Microsoft Visual studio 2010->file menu->new-> 

project->select visual c# from installed templates->select windows forms application project  

place a combobox, 3 buttons with text get, next, clear 

using System.Data.SqlClient;

 

using System; 

using System.Collections.Generic; 

using System.ComponentModel; 

using System.Data; 

using System.Drawing; 

using System.Linq; 

using System.Text; 

using System.Windows.Forms; 

using System.Data.SqlClient;

 

namespace WindowsFormsApplication20 

{ 

    public partial class Form1 : Form 

    { 

        public Form1() 

        { 

            InitializeComponent(); 

        } 

        SqlConnection cn = new SqlConnection("userid=sa;password=123;database=northwind"); 

        SqlCommand cmb = new SqlCommand(); 

        SqlDataReader dr; 

        Label[] L; 

        TextBox[] T; 

        int x = 50;

 

        private void Form1_Load(object sender, EventArgs e) 

        { 

            cn.Open(); 

            // get the table names into combobox 

            SqlCommand sc=new SqlCommand("Select * from Sys.tables",cn); 

            SqlDataReader sdr=sc.ExecuteReader(); 

            while (sdr.Read() == true) 

            { 

                comboBox1.Items.Add(sdr[0].ToString()); 

            } 

            cn.Close(); 

       }

 

        private void button1_Click(object sender, EventArgs e) 

        { 

            if (comboBox1.SelectedIndex == -1) MessageBox.Show("select a table"); 

            else

            { 

                string s = "select * from" + comboBox1.SelectedItem.ToString(); 

                cmb.CommandText = s; 

                cmb.Connection = cn; 

                cn.Open(); 

                dr = cmb.ExecuteReader(); 

                MessageBox.Show("no of cols:" + dr.FieldCount); 

                L = new Label[dr.FieldCount]; 

                T = new TextBox[dr.FieldCount];

 

                for (int i = 0; i < dr.FieldCount; i++) 

                { 

                    L[i] = new Label(); 

                    L[i].Text = dr.GetName(i); 

                    L[i].Location = new Point(100, x); 

                    this.Controls.Add(T[i]); 

                    x = x + 30; 

                } 

                button1.Enabled = false; 

            }

        }

 

        private void button2_Click(object sender, EventArgs e) 

        { 

            if (dr.Read() == true) 

            { 

                for (int i = 0; i < dr.FieldCount; i++) 

                { 

                    T[i].Text = dr[i].ToString(); 

                } 

            } 

            else 

                MessageBox.Show("no more records"); 

        }

 

        private void button3_Click(object sender, EventArgs e) 

        { 

            for (int i = 0; i < dr.FieldCount; i++) 

            { 

                this.Controls.Remove(L[i]); 

                this.Controls.Remove(T[i]); 

            } 

            cn.Close(); 

            x = 50; 

            button1.Enabled = true; 

        } 

    } 

} 

 

Oservations:

1)datareader works only when connection mode is open 

2)if connection is closed, then datareader will be closed automatically i.e to work with datareader connection must be lways open mode. Hence datareader is called as connection oriented