Most IMP Connectivity with DataBase (Ado.NET with Sql Server ) in Connected Mode.....

Connectivity with DataBase (Ado.NET with Sql Server ) in Connected Mode.....

http://kushonline.blogspot.in/2012/07/connectivity-adonet-with-connected-mode.html

1.  Create Database test

   Create database test

2.  Create Table p_customer in test Database
     a.  id  int ,primary key
     b.  fname nvarchar(50)
     c.  lname nvarchar(50)
     d.  dob nvarchar(50)

use test

create table p_customer ( id  int primary key, fname nvarchar(50), lname nvarchar(50), dob nvarchar(50))

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;

//Please using this namespace  for Connectivity  with ADO.NET........................

using System.Data.SqlClient;

namespace p_emp_login

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        Insert data  in Data Tabel ......................................

   private void btnadd_Click(object sender, EventArgs e)

    {

    try

    {

  create  object  of Connection Class..................

   SqlConnection con = new SqlConnection();

  Set Connection String property of Connection object..................

  con.ConnectionString = "Data Source=KUSH-PC;Initial Catalog=test;Integrated Security=True";

  Open Connection..................

   con.Open();

  Create object of Command Class................

  SqlCommand cmd = new SqlCommand();

 //set Connection Property  of  Command object.............

  cmd.Connection = con;

 Set Command type of command object

    1.StoredProcedure

    2.TableDirect

    3.Text   (By Default)

cmd.CommandType = CommandType.Text;

  //Set Command text Property of command object.........

cmd.CommandText = "insert into p_customer(id,fname,lname,dob) values(" +Convert.ToInt32(txtid.Text) + ",'" + txtfn.Text + "','" + txtln.Text + "','" + txtdob.Text + "')";

   Execute command by calling following method................

      1.ExecuteNonQuery()

           It  query using for  insert,delete,update command...........

      2.ExecuteScalar()

           It  query return a single value than one record...................(using only for        select command)

      3.ExecuteReader()

         It  query return one or more than one record....................................

 cmd.ExecuteNonQuery();

  MessageBox.Show("Data Saved");

  txtid.Text = "";

  txtfn.Text = "";

  txtln.Text = "";

  txtdob.Text = "";

            

}

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

           

        }

For Clear TextBox Text.......................

        private void btnclr_Click(object sender, EventArgs e)

        {

            // different method for blank  TextBox Text.......................

            txtid.Text = "";

            txtfn.Clear();

            txtln.Text = String.Empty;

            txtdob.Text = "";

        }

For Close Application..................................

        private void btnclose_Click(object sender, EventArgs e)

        {

            Application.Exit();

        }

For Searching data for database (using ExecuteReader()  and SqlDataReader ..

 private void btnsearch_Click(object sender, EventArgs e)

 {

    SqlConnection con = new SqlConnection();

 con.ConnectionString = "Data Source=KUSH-PC;Initial Catalog=test;Integrated Security=True";

 con.Open();

 SqlCommand cmd = new SqlCommand();

 cmd.CommandText = "select * from p_customer where id='" + Convert.ToInt32(txtid.Text) + "'";

 cmd.Connection = con;

 SqlDataReader dr = cmd.ExecuteReader();

            if (dr.Read())

            {

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

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

                txtdob.Text = dr[3].ToString();

            }

            else

   MessageBox.Show("Record not found", "No record", MessageBoxButtons.OK,    MessageBoxIcon.Information);

            con.Close();

        }

   Getting  for  first record from  DataTable...........................................

        private void btnfirst_Click(object sender, EventArgs e)

        {

            

 SqlConnection con = new SqlConnection();

 con.ConnectionString = "Data Source=KUSH-PC;Initial Catalog=test;Integrated Security=True";

            con.Open();

 SqlCommand cmd = new SqlCommand();

  cmd.CommandText = "select * from p_customer";

   cmd.Connection = con;

  SqlDataReader dr = cmd.ExecuteReader();

            if (dr.Read())

            {

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

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

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

                txtdob.Text = dr[3].ToString();

            }

            else

                MessageBox.Show("Record not found", "No record", MessageBoxButtons.OK,MessageBoxIcon.Information);

            con.Close();

        }

    // Getting  for  last record from  DataTable...........................................

 private void btnlast_Click(object sender, EventArgs e)

  {

   SqlConnection con = new SqlConnection();

  con.ConnectionString = "Data Source=KUSH-PC;Initial Catalog=test;Integrated Security=True";

  con.Open();

 SqlCommand cmd = new SqlCommand();

  cmd.CommandText = "select * from p_customer";

 cmd.Connection = con;

 SqlDataReader dr = cmd.ExecuteReader();

  while(dr.Read())

            {

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

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

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

                txtdob.Text = dr[3].ToString();

            }

           

            con.Close();

        }

Deleting  record from  DataTable...........................................

 private void btndel_Click(object sender, EventArgs e)

  {

      try

{

 SqlConnection con = new SqlConnection();

 con.ConnectionString = "Data Source=KUSH-PC;Initial Catalog=test;Integrated Security=True";

 con.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "delete from p_customer where id='" + Convert.ToInt32(txtid.Text) +"'";

cmd.Connection = con;

cmd.ExecuteNonQuery();

MessageBox.Show("Data Deleted");

}

catch (Exception ex)

  {

    MessageBox.Show(ex.Message);

  }

           

        }

//Updating datavale  from  DataTable...........................................

private void btnupdt_Click(object sender, EventArgs e)

{

 try

 {

SqlConnection con = new SqlConnection();

con.ConnectionString = "Data Source=KUSH-PC;Initial Catalog=test;Integrated Security=True";

con.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "update p_customer set fname='" + txtfn.Text + "',lname='" + txtln.Text + "',dob='" + txtdob.Text + "' where id='" + Convert.ToInt32(txtid.Text) +"'";

 cmd.Connection = con;

 cmd.ExecuteNonQuery();

 MessageBox.Show("Data successfully updated");

con.Close();

}

catch (Exception ex)

{

 MessageBox.Show(ex.Message);

}

}

 Getting  for  previous record from  DataTable...........................................

 private void btnprev_Click(object sender, EventArgs e)

  {

   SqlConnection con = new SqlConnection();

  con.ConnectionString = "Data Source=KUSH-PC;Initial Catalog=test;Integrated Security=True";

  con.Open();

 SqlCommand cmd = new SqlCommand();

 cmd.CommandText = "select * from p_customer where id<'" + Convert.ToInt32(txtid.Text) + "'";

 cmd.Connection = con;

SqlDataReader dr = cmd.ExecuteReader();

 if (dr.Read())

            {

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

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

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

                txtdob.Text = dr[3].ToString();

            }

            else

    MessageBox.Show("No previous record found","",MessageBoxButtons.OK,MessageBoxIcon.Information);

            con.Close();

        }

  Getting  for  next record from  DataTable...........................................

private void btnnext_Click(object sender, EventArgs e)

 {

SqlConnection con = new SqlConnection();

 con.ConnectionString = "Data Source=KUSH-PC;Initial Catalog=test;Integrated Security=True";

con.Open();

SqlCommand cmd = new SqlCommand();

 cmd.CommandText = "select * from p_customer where id>'" + Convert.ToInt32(txtid.Text) + "'";

cmd.Connection = con;

SqlDataReader dr = cmd.ExecuteReader();

 if (dr.Read())

    {

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

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

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

                txtdob.Text = dr[3].ToString();

    }

     else

 MessageBox.Show("No further record found", "", MessageBoxButtons.OK,MessageBoxIcon.Information);

            con.Close();

        }

 Coding For Page Load.........................

     private void Form1_Load(object sender, EventArgs e)

        {

        }

       

    }

   

}