Ado. NET detailed study (iv)--example demonstration DataReader basic operation

Source: Internet
Author: User
Tags constructor tostring
Previous article address in ADO:

Http://dev.csdn.net/develop/article/26/26246.shtm

Http://dev.csdn.net/develop/article/26/26480.shtm

Http://dev.csdn.net/develop/article/26/26481.shtm

This time we demonstrated the basic application of DataReader with an example, including command and the basic operation of connection. Through this example of learning we can deal with the general database system.

WinForm's Personal Address Book (vs.net2003 + SQL server2000)

1. Create a database (the previous article has been discussed)

2. Start Vs.net build contract engineering and design the following interface:




The list of important control properties is as follows:

control type
Text
Name
Other

Listview

Listview
Display mode is details,fullrowselect to Ture

Button
Are you sure
Btnok
Default enable to False

Button
Re-fill
Btnre
Default enable to False

Button
Add contact information
Btnadd


Button
Modify the selected contact information
Btnedit


Button
Delete selected contact information
Btndel


Textbox

Txtname
Default enable to False

Textbox

Txtphone
Default enable to False

Textbox

Txtaddress
The default enable is False,multiline to True



3. Write code:

First we add Liestview to display data events in the form Load event

private void Form1_Load (object sender, System.EventArgs e)

{

GetInfo ();

}

In the GetInfo method we have to read the information inside the database and display it inside the listview. A feasible approach is to use DataReader to read data directly and then display it. But I do not want to do this here, I write a specialized class CONTRACTDB to process the data, this class has its own methods to implement the operation of the database.

Class Contractdb, which encapsulates all operation events on the database for the application

Using System;

Using System.Data;

Using System.Data.SqlClient;



Namespace contract

{

<summary>

Summary description of the CONTRACTDB.

</summary>

public class Contractdb

{

String connstr = "Data Source = joycode;initial Catalog = Contract;user id = Sa;password = 87345587";

String sql;

SqlCommand cmd;



Public Contractdb ()

{

//

TODO: Add constructor logic here

//

}



<summary>

Get all contact information

</summary>

<returns> All contact Information </returns>

Public SqlDataReader Getreader ()

{

String sql = "Select fid,fname,fphone,faddress from Friend";

SqlConnection conn = new SqlConnection (CONNSTR);

Conn. Open ();

SqlCommand cmd = new SqlCommand (sql,conn);

SqlDataReader reader = cmd. ExecuteReader (commandbehavior.closeconnection);

return reader;

}

}

}

My purpose is obvious, I will call the Getreader method to get the DataReader I need, so the GetInfo method code in Form1 is as follows:

private void GetInfo ()

{

Contractdb cdb = new Contractdb ();

Try

{

SqlDataReader reader = Cdb.getreader ();

This.listView.Items.Clear ();

while (reader. Read ())

{

string[] SubItems = new string[]

{

Reader. GetInt32 (0). ToString (),

Reader. GetString (1),

Reader. GetString (2),

Reader. GetString (3)

};

THIS.LISTVIEW.ITEMS.ADD (new ListViewItem (subitems));

}

Reader. Close ();

}

catch (Exception ex)

{

MessageBox.Show (ex. ToString ());

}

}

The above code is simple and does not explain, but note that since we do not consider exceptions in the data access class, we must deal with the possible exceptions here.

Similarly, we add additional methods to the data access class: Add a contact, delete a contact, and modify the information. The entire class file is as follows:

Using System;

Using System.Data;

Using System.Data.SqlClient;



Namespace contract

{

<summary>

Summary description of the CONTRACTDB.

</summary>

public class Contractdb

{

String connstr = "Data Source = bineon;initial Catalog = Contract;user id = Sa;password = 87345587";

String sql;

SqlCommand cmd;



Public Contractdb ()

{

//

TODO: Add constructor logic here

//

}



<summary>

Get all contact information

</summary>

<returns> All contact Information </returns>

Public SqlDataReader Getreader ()

{

String sql = "Select fid,fname,fphone,faddress from Friend";

SqlConnection conn = new SqlConnection (CONNSTR);

Conn. Open ();

SqlCommand cmd = new SqlCommand (sql,conn);

SqlDataReader reader = cmd. ExecuteReader (commandbehavior.closeconnection);

return reader;

}



<summary>

Add New Contact

</summary>

<param name= "_name" > Name </param>

<param name= "_phone" > Tel </param>

<param name= "_address" > Address </param>

public void Addinfo (string _name,string _phone,string _address)

{

You can use command parameter to construct SQL statements for greater efficiency and security

String sql = "INSERT INTO friend (fname,fphone,faddress) values ('";

SQL + + _name + "', '" + _phone + "', '" + _address + "')";

SqlConnection conn = new SqlConnection (CONNSTR);

SqlCommand cmd = new SqlCommand (sql,conn);

Conn. Open ();

Cmd. ExecuteNonQuery ();

Conn. Close ();

}



<summary>

Modify contact information

</summary>

<param name= "_id" > need to modify the id</param> of the contact person

<param name= "_name" ></param>

<param name= "_phone" ></param>

<param name= "_address" ></param>

public void Changeinfo (int _id,string _name,string _phone,string _address)

{

String sql = "Update friend set Fname = '" + _name + "', Fphone = '" + _phone + ", faddress = '" + _address + "'";

SqlConnection conn = new SqlConnection (CONNSTR);

SqlCommand cmd = new SqlCommand (sql,conn);

Conn. Open ();

Cmd. ExecuteNonQuery ();

Conn. Close ();

}



<summary>

Delete the contact information for the specified FID

</summary>

<param name= "Infoid" ></param>

public void Deleteinfo (int infoid)

{

String sql = "Delete friend where Fid =" + infoid;

SqlConnection conn = new SqlConnection (CONNSTR);

Conn. Open ();

SqlCommand cmd = new SqlCommand (sql,conn);

Cmd. ExecuteNonQuery ();

Conn. Close ();

}

}

}

A description of the above class code: You can write a base class on your own, and the class above inherits the base class, and the base class contains methods such as exenonquerystring so that you don't have to write the code for each method to create a connection. In addition, the above SQL statement construct suggests using the command parameter method, which is more efficient and secure.

In addition to the database inside the admin table is not used, this table is used to save login information, you can make a landing note for this program.

Download the whole project code: click to download



Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.