[Hướng dẫn] Các thao tác thêm, xóa sửa, tìm kiếm cơ bản với sql server 2000

Tutorial
Unit two
(Part 1: Do with Sql Server 2000)
This tutorial for Programmer. This tells about to access Database in Visual Studio (Csharp ). Use DataGridView with action Connect to data about as: add, delete, update, save and search basic.
1. With Sql Server 2000
Note: I used Sql Server 2000 with server name =(local), user =sa; password= sa. You change info your server to run the application.
The first you design the application Windows Form like :

* And you make a database like as:

Database’s Name = Mydata
The tables : Demo
This Demo have 4 record : ID, Name, Pass, Noteone
There in ID is the Keyword

* More information you database

Now, We code for button Show – this use to show data in your date into dataGridView.
//

Code:


   
                // show data from Database ( Mydata) to DataGridView
                // Connect to server
 string connection = "server=(local);database=Mydata;user=sa;password=sa";
                SqlConnection con = new SqlConnection(connection);
                con.Open();
                // Make command to use
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                string show = "select*from Demo";
                cmd.CommandText = show;
                //
                SqlDataAdapter da = new SqlDataAdapter(show, con);
                DataSet ds = new DataSet();
                // Fill date to ds
                da.Fill(ds);
                // Fill Dato from DataSet to DataGridView
                dataGridView1.DataSource = ds.Tables[0];
                // Closed Connect
                con.Dispose();
                da.Dispose();
             cmd.Dispose();


b. Next – We will code for button ADD.
You click to button2 ( this has name ADD in the your application ) then copy and paste this code underhearth.

Code:


// Connect to server
                string connection = "server=(local);database=Mydata;user=sa;password=sa";
                SqlConnection con = new SqlConnection(connection);
                con.Open();
                // Make command to use
             
           
                //*************** code here for all ****************************//
                string ADD = "insert into De mo(ID,Name,Pass,Noteone) values('"+textBox1.Text+"','"+textBox2.Text+"','"+textBox3.Text+"','"+textBox4.Text+"')";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandText = ADD;
                cmd.ExecuteNonQuery();
                MessageBox.Show("Succesfull, good do", "Ok. you add Info to you data");
                //***********************
             
                // Closed Connect
                con.Dispose();
                cmd.Dispose();
                // clearn textbox;


c. Delete the record in data. Delete at record ID

Code:


// Delete info you data which has ID= textBox1.Text

                // show data from Database ( Mydata) to DataGridView
                // Connect to server
                string connection = "server=(local);database=Mydata;user=sa;password=sa";
                SqlConnection con = new SqlConnection(connection);
                con.Open();
                // Make command to use


                //*************** code here for all ****************************//
                string Delete = "delete from Demo where ID='"+textBox1.Text+"'";

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandText = Delete;
                cmd.ExecuteNonQuery();
                MessageBox.Show("Succesfull, good do. You delete ID= "+textBox1.Text+" in you data", "Ok. you Delete Info to you data");
                //***********************

                // Closed Connect
                con.Dispose();
                cmd.Dispose();
                // clearn textbox;


d. UPDATE data following ID

Code:


// update info which has ID = textBox1;
                // show data from Database ( Mydata) to DataGridView
                // Connect to server
                string connection = "server=(local);database=Mydata;user=sa;password=sa";
                SqlConnection con = new SqlConnection(connection);
                con.Open();
                // Make command to use


                //*************** code here for all ****************************//
                string updatedata = "update Demo set Name='" + textBox2.Text + "',Pass='"+textBox3.Text+"',Noteone='"+textBox4.Text+"' where ID='"+textBox1.Text+"'";

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandText =updatedata;
                cmd.ExecuteNonQuery();
                MessageBox.Show("Succesfull, good do. You update ID= " + textBox1.Text + " in you data", "Ok. you Updatedata Info to you data");
                //***********************

                // Closed Connect
                con.Dispose();
                cmd.Dispose();


e. Find data which two types
e.1. Fill fidelity at textbox key

Code:

 // Find info in you data
                // show data from Database ( Mydata) to DataGridView
                // Connect to server
                string connection = "server=(local);database=Mydata;user=sa;password=sa";
                SqlConnection con = new SqlConnection(connection);
                con.Open();
                // Make command to use


                //*************** code here for all ****************************//
                string finddata = "select*from Demo where Name='" + textBox5.Text + "'";

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandText = finddata;
                cmd.ExecuteNonQuery();
                MessageBox.Show("Succesfull, good do. You find info about Name= " + textBox5.Text + " in you data", "Ok. you find Info to you data");
                //***********************
                // Show data find
                SqlDataAdapter da = new SqlDataAdapter(finddata, con);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];

                // Closed Connect
                con.Dispose();
                cmd.Dispose();
                ds.Dispose();
                da.Dispose();


e.2. Find relative at the key word

Code:


// Connect to server
                string connection = "server=(local);database=Mydata;user=sa;password=sa";
                SqlConnection con = new SqlConnection(connection);
                con.Open();
                // Make command to use


                //*************** code here for all ****************************//
                string finddata = "select*from Demo where Name like '%" + textBox5.Text + "%'";

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandText = finddata;
                cmd.ExecuteNonQuery();
                MessageBox.Show("Succesfull, good do. You find info about like  " + textBox5.Text + " in you data", "Ok. you find Info to you data");
                //***********************
                // Show data find
                SqlDataAdapter da = new SqlDataAdapter(finddata, con);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];

                // Closed Connect
                con.Dispose();
                cmd.Dispose();
                ds.Dispose();
                da.Dispose();
                // clearn textbox;


Source code full and Demo here:
http://www.mediafire.com/?sharekey=3402828850897022e7c82ed4b8f0c380788f7c7e4b8c67c4a543906a5faff527
2. With Access 2003 ( the last)
3. Other (the last)
4. End ( the last).



*********
About : http://hoclaptrinhcanban.tk
Author: Tesulakata@yahoo.com.vn
Phone : 0972.096.906