Title ADO.NET Detailed Study (4) - Example Demo DataReader Basic Operation Bineon [Original] Keyword ADO.NET Detailed Study (4) - Example Demo DataReader Basic Operation Operating Article Address:
http://dev.9cbs.net/develop/Article/26/26246.shtm
http://dev.9cbs.net/develop/Article/26/26480.shtm
http://dev.9cbs.net/develop/Article/26/26481.shtm
This time we use an instance to demonstrate the basic application of DataReader, of course, contains the basic operations of Command and Connection. Through this example, we can handle a general database system.
WinForm's personal address book (vs.net2003 sql server2000)
1. Establish a database (previous article has been discussed)
2. Start VS.NET to establish a Contract project and design the following interface:
The list of important control attributes is as follows:
Control type
TEXT
Name
other
Listview
Listview
The display mode is details, fullrowselect is TURE
Button
determine
BTNOK
Default enable for false
Button
Refill
Btnre
Default enable for false
Button
Add contact information
btnadd
Button
Modify the contact information
btnedit
Button
Delete Check Contact Information
BTNDEL
Textbox
TXTNAME
Default enable for false
Textbox
TXTPHONE
Default enable for false
Textbox
TXTADDRESS
Default enable is false, multiline is true
3. Write code:
First we add a LiestView display data event in the form loading event.
Private Void Form1_Load (Object Sender, System.EventArgs E)
{
GetInfo ();
}
In the GetInfo method we must read the information in the database and display it in ListView. At this time, a feasible method is to read the data directly using DataReader and then display. But I don't want to do this here. I write a special class ContractDB to handle data. There are some other methods in this class to implement the operation of the database.
// Class ContractDB, package application all the operational events for the database
Using system;
Using system.data;
Using system.data.sqlclient;
Namespace Contract
{
///
/// ContractDB summary description.
///
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
//
}
///
/// get all contact information
///
///
{
String SQL = "SELECT FID, FNAME, FPHONE, FADDRESS from Friend";
SqlConnection conn = new SqlConnection (connStr);
Cn.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 ();
THISTVIEW.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.totring ());
}
}
The above code is simple, not explaining, but pay attention to the abnormality of our data access class, then we must handle an exception that may occur here.
Similar to we add additional methods in the data access class: Add contacts, delete contacts, modify information. The entire class file is as follows:
Using system;
Using system.data;
Using system.data.sqlclient;
Namespace Contract
{
///
/// ContractDB summary description.
///
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
//
}
///
/// get all contact information
///
///
Public SqlDataReader getReader ()
{
String SQL = "SELECT FID, FNAME, FPHONE, FADDRESS from Friend";
SqlConnection conn = new SqlConnection (connStr);
Cn.open ();
Sqlcommand cmd = new SQLCOMMAND (SQL, Conn); SqlDataReader Reader = cmd.executeReader (Commandbehavior.CloseConnection);
Return Reader;
}
///
/// Add a new contact
///
/// Name
/// Phone
/// address
Public void addinfo (String _Name, String _phone, string _address)
{
// You can use Command Parameter to construct a SQL statement to get better efficiency and higher security.
String SQL = "INSERT INTO Friend (F6one, Faddress) VALUES ('"
SQL = _Name "','" _phone ", '" _address ")";
SqlConnection conn = new SqlConnection (connStr);
Sqlcommand cmd = new SQLCOMMAND (SQL, CONN);
Cn.open ();
cmd.executenonquery ();
CONN.CLOSE ();
}
///
// / Modify contact information
///
/// contact ID required to modify
///
///
///
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);
Cn.open ();
cmd.executenonquery ();
CONN.CLOSE ();
}
///
/// Remove contact information of the specified FID
///
///
Public void deleteInfo (int infoid) {
String SQL = "Delete Friend Where Fid =" InfoID;
SqlConnection conn = new SqlConnection (connStr);
Cn.open ();
Sqlcommand cmd = new SQLCOMMAND (SQL, CONN);
cmd.executenonquery ();
CONN.CLOSE ();
}
}
}
Description of the above code: You can write a base class yourself, then the class is inherited above, the base class contains ExenonQueryString and other methods so you don't have to write the code such as the connection. In addition, the SQL statement constructs are recommended to use Command Parameter, such efficiency and security are relatively high.
In addition, the admin table in the database is not used, this table is used to save login information, you can make a login prompt for this program.
Whole project code download: Click to download
