Xiangsheng Visual C # database programming

visual| Programming | data | Database on database programming, Microsoft provides a unified data object access model, in Visual Studio6.0 called ADO, in. NET is unified as ado.net, mastering ado.net is equal to mastering the core of database programming.
Database programming is always an important aspect of programming language, and it is also a difficult task. Database programming is very rich in content, but the most basic programming is a few, such as: Connect the database, get the required data and data records for browsing, delete, modify, insert and other operations. It also focuses on the data from the following data records. This article focuses on the basic programming of Visual C # databases: How to browse records, modify records, delete records, and insert records.

A Design and operation of the environment settings:

(1). Windows 2000 Server Edition

(2). Microsoft Data acess Component version 2.6 (MDAC 2.6)

(3). Net FrameWork SDK Beta 2

For a clearer explanation of the problem, the database is selected using the currently typical database, one is the local database Access 2000, and the other is the remote database SQL Server 2000. Where the local database name is "Db.mdb", in which a data table "person" is defined, and the data structure of the person table is the following table:

Field Name field Type field meaning
ID number ordinal
XM Text Name
XB Text Gender
NL text Age
Zip text zip code

The database server name for the remote database SQL Server 2000 is "Server1", the database name is "Data1", the login ID is "sa", the password is empty, and a "person" table is defined in the database, as shown in the data structure.
Two How to browse Data:

In Visual C # data binding, you've learned how to bind certain fields in a dataset to a property of the WinForm component, so that programmers can customize the form of data display based on the WinForm component. And the WinForm component display at this point can change as the record pointer changes. Thus, the key to browsing data logging is how to change the record pointer. To do this, you use the BindingManagerBase class, whose primary role is to manage objects that are implemented to bind to the same data source. Specifically, you can synchronize components on a Windows Form that have data binding to the same data source. A property "Position" is defined in the BindingManagerBase class to change the data pointer in the BindingManagerBase object. Creating a BindingManagerBase object must use the BindingContext class, in fact, each object that is inherited from the control class has a single BindingContext object. The BindingManagerBase object that implements the data-binding component in most creation forms is obtained using the BindingContext of the form class. The following code is a model of an Access 2000 database that is created with a BindingManagerBase object called "Mybind."

Create a OleDbConnection
String Strcon = "Provider = microsoft.jet.oledb.4.0;" Data Source = Db.mdb ";
OleDbConnection myconn = new OleDbConnection (Strcon);
String strcom = "SELECT * from person";
file://Create a DataSet
myDataSet = new DataSet ();

MyConn.Open ();
file://use OleDbDataAdapter to get a dataset
OleDbDataAdapter mycommand = new OleDbDataAdapter (strcom, myconn);
file://bind the dataset to the Books data table
Mycommand.fill (myDataSet, "person");
file://close this OleDbConnection
Myconn.close ();
Mybind = this. BindingContext [myDataSet, "person"];

The following code is modeled on a SQL Server 2000 database and creates a BindingManagerBase object called "Mybind."

Sets the data connection string, which means that the SQL Server database is opened, the server name is Server1, and the database is Data1
String Strcon = "Provider = sqloledb.1;" Persist security Info = False; User ID = sa; Initial Catalog = data1; Data Source = Server1 ";
OleDbConnection myconn = new OleDbConnection (Strcon);
MyConn.Open ();
String strcom = "SELECT * from person";
file://Create a DataSet
myDataSet = new DataSet ();
file://use OleDbDataAdapter to get a dataset
OleDbDataAdapter mycommand = new OleDbDataAdapter (strcom, myconn);
file://bind the dataset to the person datasheet
Mycommand.fill (myDataSet, "person");
file://close this OleDbConnection
Myconn.close ();
Mybind = this. BindingContext [myDataSet, "person"];

The BindingManagerBase object that is the same data source is obtained, by changing the "Position" property value of this object, so that the data of the component that binds the data changes accordingly, thus realizes the navigation data record.

< I >. Navigation Button "Previous" Implementation method:

protected void Goprevious (object sender, System.EventArgs e)
{
if (mybind.position = 0)
MessageBox.Show ("It's the first record!") "," Information tips! ", MessageBoxButtons.OK, MessageBoxIcon.Information);
Else
Mybind.position-= 1;
}

< II >. Navigation Button "Next" Implementation method:

protected void GoNext (object sender, System.EventArgs e)
{
if (mybind.position = = mybind.count-1)
MessageBox.Show ("It's the last record!") "," Information tips! ", MessageBoxButtons.OK, MessageBoxIcon.Information);
Else
Mybind.position + 1;
}

< III >. Navigation button "to tail" implementation method:

protected void Golast (object sender, System.EventArgs e)
{
Mybind.position = mybind.count-1;
}
< IV >. Navigation Button "to first" implementation method:
protected void Gofirst (object sender, System.EventArgs e)
{
mybind.position = 0;
}

Note: "Count" is another important property of the BindingManagerBase object and is the total number of data set records.
Three To implement a Delete record:

When working with data records, two points must be clear:

One: When you are working on a data record, I think some programmers must have such a doubt that when a dataset is requested on the database server, a "DataSet" object is generated to manage the dataset, so that if there are so many requests to the database server, there will also be a lot of " DataSet object, which is bound to cause the database server to crash at a certain time. This idea is natural, but it does not match the reality, because the DataSet object is not generated on the server side, but on the client. Therefore, the impact on the database server is not very large when confronted with many requests for data.

Second: Remember to write the three-tier data model with Delphi, every time the modification of the database is actually just the first layer of data generated by the changes, to really modify the database, you must also call a different method. While processing a database with ado.net, while the direct object being processed is a database, the content in the DataSet object is not changed, and the data component that is being displayed is derived from the DataSet object, which creates an illusion Is that the modified record has not been modified, the deleted records are not deleted. Therefore, when the data recording operation, after the database is modified, the "DataSet" object to make the necessary changes, so as to ensure that "dataset" object and the database content consistent, synchronized. The following code is the program code that deletes the record displayed by the current bound component, which is based on the Access 2000 database:

protected void Delete_record (object sender, System.EventArgs e)
{
DialogResult r = MessageBox.Show ("Delete current record!") "," delete the current record! ", Messageboxbuttons.yesno, messageboxicon.question);
int ss = (int) r;
if (ss = 6)//press OK button
{
try{
file://Connect to a database
String Strcon = "Provider = microsoft.jet.oledb.4.0;" Data Source = Db.mdb ";
OleDbConnection myconn = new OleDbConnection (Strcon);
MyConn.Open ();
String Strdele = "DELETE from person WHERE id=" + t_id. Text;
OleDbCommand mycommand = new OleDbCommand (Strdele, myconn);
file://deletes the specified record from the database
Mycommand.executenonquery ();
file://deletes the specified record from the dataset
mydataset.tables ["Person"]. Rows [Mybind.position]. Delete ();
mydataset.tables ["Person"]. AcceptChanges ();
Myconn.close ();
}
catch (Exception ed)
{
MessageBox.Show ("Delete logging error message:" + ed.) ToString (), "Error!" " ) ;
}
}
}

Four Insert Data record:

The basic idea for inserting records and deleting records in a database is consistent, by ado.net inserting data into the database first, and then making the necessary modifications to the DataSet object. The following code is the code that modifies the current record with the Access 2000 database model:

protected void Update_record (object sender, System.EventArgs e)
{
int i = mybind.position;
try{
file://Connect to a database
String Strcon = "Provider = microsoft.jet.oledb.4.0;" Data Source = Db.mdb ";
OleDbConnection myconn = new OleDbConnection (Strcon);
MyConn.Open ();
mydataset.tables ["Person"]. Rows [Mybind.position]. BeginEdit ();
file://to modify the specified record from the database
String STRUPDT = "UPDATE person SET xm = '"
+ T_XM. Text + "', XB = '"
+ T_XB. Text + "', NL ="
+ T_NL. Text + ", zip ="
+ T_books. Text + "WHERE id =" + t_id. Text;
OleDbCommand mycommand = new OleDbCommand (STRUPDT, myconn);
Mycommand.executenonquery ();
mydataset.tables ["Person"]. Rows [Mybind.position]. EndEdit ();
mydataset.tables ["Person"]. AcceptChanges ();
Myconn.close ();
}
catch (Exception ed)
{
MessageBox.Show ("Modify the specified record error:" + ed.) ToString (), "Error!" " ) ;
}
Mybind.position = i;
}

Because the difference between the SQL Server 2000 data Logging modification operation and the Access 2000 data record modification operation is only in different data links, the specific code can refer to the code in the "Delete data record", which is not available here.
Five Insert Data record:

In line with the previous two operations, the idea is to first insert data into the database through ado.net, and then make the necessary modifications to the DataSet object. The following code is the code that inserts a data record for the model in an Access 2000 database

protected void Insert_record (object sender, System.EventArgs e)
{
Try
{
file://to determine if all fields have been added, execute when finished, or pop-up prompts
if (t_id. Text!= "" && t_xm. Text!= "" && t_xb. Text!= "" && t_nl. Text!= "" && t_books. Text!= "")
{
String myConn1 = "Provider = microsoft.jet.oledb.4.0;" Data Source = Db.mdb ";
OleDbConnection myconn = new OleDbConnection (MYCONN1);
MyConn.Open ();
String Strinsert = "INSERT into person (ID, XM, XB, NL, Zip) VALUES (";
Strinsert + = t_id. Text + ", '";
Strinsert + = T_xm. Text + "', '";
Strinsert + = T_xb. Text + "',";
Strinsert + = T_nl. Text + ",";
Strinsert + = T_books. Text + ")";
OleDbCommand Inst = new OleDbCommand (Strinsert, myconn);
Inst. ExecuteNonQuery ();
Myconn.close ();
mydataset.tables ["Person"]. Rows [Mybind.position]. BeginEdit ();
mydataset.tables ["Person"]. Rows [Mybind.position]. EndEdit ();
mydataset.tables ["Person"]. AcceptChanges ();
}
Else
{
MessageBox.Show ("All field values must be filled!") "," the mistake! " ) ;
}
}
catch (Exception ed)
{
MessageBox.Show ("Save data Record Occurrence" + ed.) ToString (), "Error!" " ) ;
}
}

Also, the difference between an Insert record operation for a SQL Server 2000 database and an Access 2000 database insert record is only a different data link, and the code can refer to the code in the "Delete data record", which is not available here.
Six Visual C # Database programming completes the main interface of source code and program running:

With the above points in hand, it's easy to write a complete database programming program, followed by the complete code for database programming in Visual C # (Data01.cs), designed in an Access 2000 database model, as follows:

Using System;
Using System.Drawing;
Using System.ComponentModel;
Using System.Windows.Forms;
Using System.Data.OleDb;
Using System.Data;

public class Data:form
{
Private System.ComponentModel.Container components = null;
Private Button Lastrec;
Private Button Nextrec;
Private Button Previousrec;
Private Button Firstrec;
Private TextBox t_books;
Private TextBox T_nl;
Private ComboBox T_XB;
Private TextBox T_XM;
Private TextBox t_id;
Private Label l_books;
Private Label L_nl;
Private Label L_XB;
Private Label L_XM;
Private Label l_id;
Private Label Label1;
Private DataSet myDataSet;
Private Button button1;
Private Button button2;
Private Button Button3;
Private Button Button4;
Private BindingManagerBase Mybind;

Public Data ()
{
file://Connect to a database
Getconnected ();
Initialize the content that is required in the form
InitializeComponent ();
}
file://clear the resources used in the program
protected override void Dispose (bool disposing)
{
if (disposing)
{
if (Components!= null)
{
Components. Dispose ();
}
}
Base. Dispose (disposing);
}
public static void Main ()
{
Application.Run (New Data ());
}
public void getconnected ()
{
Try
{
file://Create a OleDbConnection
String Strcon = "Provider = microsoft.jet.oledb.4.0;" Data Source = Db.mdb ";
OleDbConnection myconn = new OleDbConnection (Strcon);
String strcom = "SELECT * from person";
file://Create a DataSet
myDataSet = new DataSet ();

MyConn.Open ();
file://use OleDbDataAdapter to get a dataset
OleDbDataAdapter mycommand = new OleDbDataAdapter (strcom, myconn);
file://bind the dataset to the Books data table
Mycommand.fill (myDataSet, "person");
file://close this OleDbConnection
Myconn.close ();
}
catch (Exception e)
{
MessageBox.Show ("Connection Error!" + e.tostring (), "error");
}
}
private void InitializeComponent ()
{

file://add controls, slightly

This. Name = "Data";
This. Text = "Database programming for Visual C #!" " ;
This. ResumeLayout (FALSE);
Mybind = this. BindingContext [myDataSet, "person"];
}
protected void New_record (object sender, System.EventArgs e)
{

t_id. Text = (Mybind.count + 1). ToString ();
T_xm. Text = "";
T_xb. Text = "";
T_nl. Text = "";
T_books. Text = "";

}
protected void Insert_record (object sender, System.EventArgs e)
{
Try
{
file://to determine if all fields have been added, execute when finished, or pop-up prompts
if (t_id. Text!= "" && t_xm. Text!= "" && t_xb. Text!= "" && t_nl. Text!= "" && t_books. Text!= "")
{
String myConn1 = "Provider = microsoft.jet.oledb.4.0;" Data Source = Db.mdb ";
OleDbConnection myconn = new OleDbConnection (MYCONN1);
MyConn.Open ();
String Strinsert = "INSERT into person (ID, XM, XB, NL, Zip) VALUES (";
Strinsert + = t_id. Text + ", '";
Strinsert + = T_xm. Text + "', '";
Strinsert + = T_xb. Text + "',";
Strinsert + = T_nl. Text + ",";
Strinsert + = T_books. Text + ")";
OleDbCommand Inst = new OleDbCommand (Strinsert, myconn);
Inst. ExecuteNonQuery ();
Myconn.close ();
mydataset.tables ["Person"]. Rows [Mybind.position]. BeginEdit ();
mydataset.tables ["Person"]. Rows [Mybind.position]. EndEdit ();
mydataset.tables ["Person"]. AcceptChanges ();
}
Else
{
MessageBox.Show ("All field values must be filled!") "," the mistake! " ) ;
}
}
catch (Exception ed)
{
MessageBox.Show ("Save data Record Occurrence" + ed.) ToString (), "Error!" " ) ;
}
}

protected void Update_record (object sender, System.EventArgs e)
{
int i = mybind.position;
try{
file://Connect to a database
String Strcon = "Provider = microsoft.jet.oledb.4.0;" Data Source = Db.mdb ";
OleDbConnection myconn = new OleDbConnection (Strcon);
MyConn.Open ();
mydataset.tables ["Person"]. Rows [Mybind.position]. BeginEdit ();
file://to modify the specified record from the database
String STRUPDT = "UPDATE person SET xm = '"
+ T_XM. Text + "', XB = '"
+ T_XB. Text + "', NL ="
+ T_NL. Text + ", zip ="
+ T_books. Text + "WHERE id =" + t_id. Text;
OleDbCommand mycommand = new OleDbCommand (STRUPDT, myconn);
Mycommand.executenonquery ();
mydataset.tables ["Person"]. Rows [Mybind.position]. EndEdit ();
mydataset.tables ["Person"]. AcceptChanges ();
Myconn.close ();
}
catch (Exception ed)
{
MessageBox.Show ("Modify the specified record error:" + ed.) ToString (), "Error!" " ) ;
}
Mybind.position = i;
}


protected void Delete_record (object sender, System.EventArgs e)
{
DialogResult r = MessageBox.Show ("Delete current record!") "," delete the current record! ", Messageboxbuttons.yesno, messageboxicon.question);
int ss = (int) r;
if (ss = 6)//press OK button
{
try{
file://Connect to a database
String Strcon = "Provider = microsoft.jet.oledb.4.0;" Data Source = Db.mdb ";
OleDbConnection myconn = new OleDbConnection (Strcon);
MyConn.Open ();
String Strdele = "DELETE from person WHERE id=" + t_id. Text;
OleDbCommand mycommand = new OleDbCommand (Strdele, myconn);
file://deletes the specified record from the database
Mycommand.executenonquery ();
file://deletes the specified record from the dataset
mydataset.tables ["Person"]. Rows [Mybind.position]. Delete ();
mydataset.tables ["Person"]. AcceptChanges ();
Myconn.close ();
}
catch (Exception ed)
{
MessageBox.Show ("Delete logging error message:" + ed.) ToString (), "Error!" " ) ;
}
}
}

file://button "Tail Record" object event program
protected void Golast (object sender, System.EventArgs e)
{
Mybind.position = mybind.count-1;
}

file://button "Next" Object event program
protected void GoNext (object sender, System.EventArgs e)
{
if (mybind.position = = mybind.count-1)
MessageBox.Show ("It's the last record!") "," Information tips! ", MessageBoxButtons.OK, MessageBoxIcon.Information);
Else
Mybind.position + 1;
}
file://button "Previous" Object event program
protected void Goprevious (object sender, System.EventArgs e)
{
if (mybind.position = 0)
MessageBox.Show ("It's the first record!") "," Information tips! ", MessageBoxButtons.OK, MessageBoxIcon.Information);
Else
Mybind.position-= 1;
}
file://button "First Record" object event program
protected void Gofirst (object sender, System.EventArgs e)
{
mybind.position = 0;
}
}

For program code that is modeled on a SQL Server 2000 database, simply link the data in the Data01.cs, that is:
String myConn1 = "Provider = microsoft.jet.oledb.4.0;" Data Source = Db.mdb ";


Change to:


String Strcon = "Provider = sqloledb.1;" Persist security Info = False; User ID = sa; Initial Catalog = data1; Data Source = Server1 ";


Note: This data link means: Open the SQL Server database, the server name is Server1, the database is Data1
You can get the complete source code that Visual C # has programmed for the SQL Server 2000 database for templates. So this article is no longer available.

Seven Summarize:

Database programming is always a key and difficult point in program programming content. These operations are the most basic and important content of database programming. Those complex programming is nothing more than a number of these processes superimposed.