Unique Program of saving, search, delete and show data from the database in Ado.net.
Hi friend , I want to tell you about a very Important and Interesting Program that help you Show all data in the text box and you can Insert the data in SQL Database with the help of Text boxes and save it in SQL and if you want to search/retrieve data from database then you can find the data with help of ID and you can delete the date you not keep it in database with the help of single ID .
So I hope you enjoy this nice coding of ado.net page. here I describe step by step so that you can not have to face the problem. So take care only database name and table name. You should have also take care of text boxes here. because a special coding for each text boxes here, so take care and change as you required text name, change the label name as you wish.
Step 1. first of all open Visual Studio 2005 or 2008. and click on Create project, Then it opens a new form on the page.
Now design the page as you wish
Here I design with following:-
Step 2:- create a table in your SQL give the location and remember that In which Database your table is created?
here I created seven column with a unique SId column.
open Toolbox from the view menu
Following Tool is required for Following see below and take care
=>To Show All Data from the database
Drag a Data Grid View Control on the form
=> Save the data in the database
Drag the Label and text boxes as you mention column in your SQL table and change it's properties name as you Mention In your column name in the table.
and a button (Change text property Save)
=> Drag the text boxes as you created the column in the table (suppose you create 6 column the drag 6 text boxes on the form)
Note: take care of text numbers and button numbers otherwise it became wrong in the program.
=> For search Command
One button (Name Search) one text box and one Label (change text properties, Search by Id)
=> For update
if suppose that if you want to update city column then you need tow text box one for entering Id where you want to delete and other for new city name and a button (update) and a label name
=> For delete Command
One label (Enter Sid where you want to delete values)and a text box and one button delete
Important Instruction ;- here I explain the enter sid, for search update and delete command we have used text box 1 everywhere, you can take another text box for a separate command only change the name of the text box, take care of it.
Now: - I describe here one by one command
1 => For See the all values from the table
Note:- here I add some extra namespace in it, For this double click on the form and add some Namespace as below: It is a must for it,. these are the following:-
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
Note => if name space is not available in the list then you can add it by following way -
Go to Website menu and click on Add Reference and choose above Name space one by one and ok. Now come to the page and u can easily add these name space as above mention : (if you not clear still then you can send Mail me amitsaxena0503@gmail.com for help, You can send message me contact with my sidebar)
Now Double Click On Button(which change properties text name see all values of the database)
Note: I want to tell you about my table which I created in SQL
Table Name Amit and column Name are Sid, Name, Age, City, Phone, (you can create as you wish)
you should Insert minimum 10 records in a table to see better
Now Double click on Show Button and write the Following code on Button Event Page
SqlConnection sc = new SqlConnection("Data Source = .; Initial Catalog = AmitSaxena; Integrated Security = true;");
sc.Open();
SqlDataAdapter sda = new SqlDataAdapter("Select*from Amit", sc);
DataSet ds = new DataSet();
sda.Fill(ds);
GridView1.DataSource = ds.Tables[0].DefaultView;
GridView1.DataBind();
Note: you can connect with your database as you wish, I created here for our local machine so write as above.
you can run it simply press F5 Button and debug the program
but I want to describe here all command so Now I want to describe to search command, here I enter the only Sid in the text box and we will see the all its related values, for this write the following code
Double click on search Button and write the following code in button event section.
SqlConnection sc = new SqlConnection("Data Source = .; Initial Catalog = AmitSaxena; Integrated Security = true;");
sc.Open();
SqlDataAdapter sda = new SqlDataAdapter("Select*from Amit where sid = '"+TextBox1.Text+"'", sc);
DataSet ds = new DataSet();
sda.Fill(ds);
GridView1.DataSource = ds.Tables[0].DefaultView;
GridView1.DataBind();
Now I am going to explain How to insert data in Database table
You design as above instruction as Drag text boxes 6/7 or as your requirement and label and change it' text properties
Now double Click on Save / Insert Button and write the following code in Button section Event
SqlConnection sc = new SqlConnection("Data Source = .; Initial Catalog = AmitSaxena; Integrated Security = true;");
sc.Open();
SqlCommand scom = new SqlCommand("Insert into Amit values ('" + TextBox2.Text + "', '" + TextBox3.Text + "', '" + TextBox4.Text + "', '" + TextBox5.Text + "', '" + TextBox6.Text + "')", sc);
scom.ExecuteNonQuery();
sc.Close();
TextBox2.Text = "";
TextBox3.Text = "";
TextBox4.Text = "";
TextBox5.Text = "";
TextBox6.Text = "";
Now I explain How to delete data from the table
Double click on Delete button and write the following code in Button event section
SqlConnection sc = new SqlConnection("Data Source = .; Initial Catalog = AmitSaxena; Integrated Security = true;");
sc.Open();
SqlDataAdapter sda = new SqlDataAdapter("delete Amit where sid = '" + TextBox7.Text + "'", sc);
DataSet ds = new DataSet();
sda.Fill(ds);
//GridView1.DataSource = ds.Tables[0].DefaultView;
//GridView1.DataBind();
Now you Run this nice coding and enjoy the best of luck.
See all The Coding In together it shows like below in the whole program: if are able to edit as your requirement then it's very nice otherwise you can take help with above step-by-step Coding. Here all Coding for all:
using System;
using System. Data;
using System. Configuration;
using System. Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default: System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection sc = new SqlConnection("Data Source = .; Initial Catalog = AmitSaxena; Integrated Security = true;");
sc.Open();
SqlDataAdapter sda = new SqlDataAdapter("Select*from Amit", sc);
DataSet ds = new DataSet();
sda.Fill(ds);
GridView1.DataSource = ds.Tables[0].DefaultView;
GridView1.DataBind();
}
protected void Button2_Click(object sender, EventArgs e)
{
SqlConnection sc = new SqlConnection("Data Source = .; Initial Catalog = AmitSaxena; Integrated Security = true;");
sc.Open();
SqlDataAdapter sda = new SqlDataAdapter("Select*from Amit where sid = '"+TextBox1.Text+"'", sc);
DataSet ds = new DataSet();
sda.Fill(ds);
GridView1.DataSource = ds.Tables[0].DefaultView;
GridView1.DataBind();
}
protected void Button3_Click(object sender, EventArgs e)
{
SqlConnection sc = new SqlConnection("Data Source = .; Initial Catalog = AmitSaxena; Integrated Security = true;");
sc.Open();
SqlCommand scom = new SqlCommand("Insert into Amit values ('" + TextBox2.Text + "', '" + TextBox3.Text + "', '" + TextBox4.Text + "', '" + TextBox5.Text + "', '" + TextBox6.Text + "')", sc);
scom.ExecuteNonQuery();
sc.Close();
TextBox2.Text = "";
TextBox3.Text = "";
TextBox4.Text = "";
TextBox5.Text = "";
TextBox6.Text = "";
}
protected void Button4_Click(object sender, EventArgs e)
{
SqlConnection sc = new SqlConnection("Data Source = .; Initial Catalog = AmitSaxena; Integrated Security = true;");
sc.Open();
SqlDataAdapter sda = new SqlDataAdapter("delete Amit where sid = '" + TextBox7.Text + "'", sc);
DataSet ds = new DataSet();
sda.Fill(ds);
//GridView1.DataSource = ds.Tables[0].DefaultView;
//GridView1.DataBind();
}
}
Now Enjoy this Nice coding. Thanks a lot to visit my site.