Home > DeveloperSection > Articles > INSERTING DATA AND FETCHING RECORDS FROM DATABASE IN C# .NET

INSERTING DATA AND FETCHING RECORDS FROM DATABASE IN C# .NET


ADO.Net ADO.Net 
Ratings:
0 Comment(s)
 23966  View(s)
Rate this:

INSERTING DATA AND FETCHING RECORDS FROM DATABASE IN C# .NET

First Step is to open a new project in Microsoft visual studio, then open a Design view in the form and place a various label as well as text field and Buttons in the form. As we have taken five Labels  like 

Employee_id,Employee_Name,Employee_Address,Employee_Phoneno,Employee_Age along with textbox with each of the labels, next we have to take four Buttons as insert Button, previous (<<),Next (>>),and Clear Button we have taken with the help of Toolbox. As shown below.

After setting the above properties to the forms and controls, your form will be shown like below:


INSERTING DATA AND FETCHING RECORDS FROM DATABASE IN C# .NET

Property of Form1 can be set as follows;

Property Name

Property Value

txtid

Employee_id

txname

Employee_Name

txtAddress

Employee_Address

txtphoneno

Employee_phoneno

txtAge

Employee_Age

 

Next Create a database in the Microsoft SQL Server, with a name mind and add a table name Employee with respected fields. Add fields to the table Employees with the specified data types as given below:

Column Name

Data Types

Employee_id

Int

Employee_Name

Varchar(50)

Employee_Address

Varchar(50)

Employee_phoneno

Varchar(50)

Employee_Age

Varchar(50)

 

The snapshots of the database are shown as:

SQL Server

 

//Place a namespace before writing a code

 using System.Data.SqlClient;

//CODE

 

private void Form1_Load(object sender, EventArgs e)

        {

            try

            {

//this lines are for creating a new connection by making a new sql connection object,we are passing a coonection string of this object which includes server name that coonect to the server database,userid,password,and name of the database will be same as we have given like mind.

 

                con = new SqlConnection("server=uttam-pc1\\sqlexpress; uid=sa; password=sa; database=mind");

//this is for open a coonection

                con.Open();

//create a new dataAdapter

                ad = new SqlDataAdapter("select * from Employee", con);

//sqlcommandbuilder is used for changes made in a dataset in a database

                SqlCommandBuilder builder = new SqlCommandBuilder(ad);

//Create a dataset Object

                ds = new DataSet();

//use dataAdapter object to fill the dataset

                ad.Fill(ds, "Employee");

//create a datatype objectin a table

                dt = ds.Tables["Employee"];

               

               

                //populating text fields with 1st record from database.

                txtID.Text = dt.Rows[counter][0].ToString();

                txtName.Text = dt.Rows[counter][1].ToString();

                txtAddress.Text = dt.Rows[counter][2].ToString();

                txtPhoneno.Text = dt.Rows[counter][3].ToString();

                txtAge.Text = dt.Rows[counter][4].ToString();

               

            }

            catch(Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }

 

//Next place a piece of code under clear button as below.

private void btnClear_Click(object sender, EventArgs e)

        {

//it will clear all the text field.

            txtID.Text= "";

            txtName.Text = "";

            txtAddress.Text = "";

            txtPhoneno.Text = "";

            txtAge.Text = "";

 

        }

//Double click inside the insert button and write a highlighted code this code will insert a data in a database

private void btnInsert_Click(object sender, EventArgs e)

        {

           

           //created datarow object as dr

 

                   DataRow dr;

//Create a data table object and add a new row


                    dr = dt.NewRow();

                    dr[0] = txtID.Text;

                    dr[1] = txtName.Text;

                    dr[2] = txtAddress.Text;

                    dr[3] = txtPhoneno.Text;

                    dr[4] = txtAge.Text;

                    dt.Rows.Add(dr);

//it will modify data in a database

                    ad.Update(dt);

                       }

 

//Next double click on the next button (>>),and place a code.

   private void btnNext_Click(object sender, EventArgs e)

        {

           

            if (counter < dt.Rows.Count-1)//this condition checks number of rows and counter checks counter is less than number of rows or not.//if this conditions true it will increase the counter by one

            {

                counter++;

//checking number of rows in a datable.

                txtID.Text = dt.Rows[counter][0].ToString();

                txtName.Text = dt.Rows[counter][1].ToString();

                txtAddress.Text = dt.Rows[counter][2].ToString();

                txtPhoneno.Text = dt.Rows[counter][3].ToString();

                txtAge.Text = dt.Rows[counter][4].ToString();

            }

       }

 

//Next double click on the Previous button (<<),and place a code.

private void btnPrevious_Click(object sender, EventArgs e)

        {

            try

            {

 

                if (counter > 0)//check wether counter is greater then 0.If the condition satisfy then counter will be decreases.

                {

                    counter--;

 

                    txtID.Text = dt.Rows[counter][0].ToString();

                    txtName.Text = dt.Rows[counter][1].ToString();

                    txtAddress.Text = dt.Rows[counter][2].ToString();

                    txtPhoneno.Text = dt.Rows[counter][3].ToString();

                    txtAge.Text = dt.Rows[counter][4].ToString();

                }

 

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

 

        }

//In the above code,to get the previous record,we are first decrementing the //value of counter by one.

//Next double click on the close button and place a code.

private void btnClose_Click(object sender, EventArgs e)

        {

            this.Close();//simply close the form.

        }

//After writing all these code we have to run the application with the help of F5.the desired output look likes.

INSERTING DATA AND FETCHING RECORDS FROM DATABASE IN C# .NET


Don't want to miss updates? Please click the below button!

Follow MindStick