Implementing DB2 database Programming in Visual C #

visual| Programming | data | Database access to non-SQL Server databases such as IBM DB2 in Visual Studio.NET Beta 1 is usually a ado.net ODBC method, while in Beta 2 the database is accessed using OLE DB mode instead.

The connection string in BETA1 is primarily required to declare the ODBC DNS name of the database, but the string of OLE DB connections in Beta 2 is more complex and the classes used are not the same. Because the database such as DB2 is used widely in the application of mainframe. NET will naturally support this kind of programming development, and for this, the DB2 development of OLE DB will be elaborated in the following step.

OLE DB for DB2 driver
First, the DB2 OLE DB driver is not directly available in Visual Studio.NET and Windows Server, so download the Microsoft Host Integration Server 2000 software, which contains OLE DB Provider and ODBC driver for DB2, download the server-side package and choose to install the data integration at installation time, and you can use its OLE DB DB2 drive when the installation is complete.

setting up a database connection
First, we need to configure the DB2 client settings, open the Server Explorer in the View Select item, add a connection to its data connection, select Microsoft OLE DB Provider for DB2 when specifying the program options, and complete the following information in the next connection option:

Data source:qcdb (please change as appropriate)
Network:tcp/ip
IP Address: Database server addresses
Network PORT:DB2 Service Port
Username: User name for database access rights
Password: This user password
Database Initial CATALOG:QCDB
Package collection:qcdb
Default Schema:userid
The next advanced option is to select the corresponding code page and CCSID in the respective national languages, and finally press the confirm key to complete the connection.

Suppose qcdb This DB2 database has a new data table for address, it contains four fields: name, email, age, and address, we can use Server Explorer to open the connection and the data table, and enter a number of Chinese records to determine whether the characters are displayed correctly.

to add a database connection to a project
To create a new ASP.net project with Visual Studio, activate the Server Explorer above connection, and drag the connection to the new Web form, the connection character parameters required by OLE DB are automatically written to the Web Form program, and the following connection generates the statement:

this.oleDbConnection1.ConnectionString = @ "PROVIDER=DB2OLEDB;
Cache Authentication=false;
Integrated security= "" "" ";
PASSWORD=FREEBSD9;
Persist Security info=true;
User id=userid;initial catalog=qcdb;
Data source=qcdb; Mode=readwrite;
Extended properties= "" "" ";
APPC Remote LU alias= "" ";
APPC local LU alias= "" ";
APPC Mode Name=qpcsupp;
Network transport LIBRARY=TCPIP;
Host ccsid=37; PC Code page=1282;
Network address=172.23.169.1;
Network port=4444; Package collection=qcdb;
Default Schema=userid; Alternate TP name= "" ";
Process Binary as Character=false;
Units of Work=ruw "
Building a connection with the Server Explorer tool avoids writing various parameters incorrectly, enabling you to quickly implement a database connection.

Web Form displays database data
Add an object OleDbDataAdapter1 to the OleDbDataAdapter class for Web form. When selecting a data connection for it, select the connection established above such as: Qcdb.qcdb.userid, choose to use SQL statements, generate SQL statements such as: Selectfrom address, finally prompted to complete. Add a new dataset to the Web form and name it the data that DataSet1 uses to place the query. Add a DataGrid named DataGird1 to display the data, and then add the following code in the Page_Init section of the program section of the Web form:

Oledbconnection1.open ();
Open a database connection
Oledbdataadapter1.fill (DataSet1, "address");
Fill in the dataset with the data you have obtained
Datagrid1.databind ();
Binding Data
Oledbconnection1.close ();
Close connection
After the compilation runs, the contents of the Visible Address table are displayed in the Databrid of the Web form. OleDbConnection1 property settings are in the generated Web Form design code.

Increase Database Data
Add a TextBox with the number of corresponding fields on the Web form, and a button to add the Click Response event code for the key:

This.oleDbInsertCommand1.CommandText = "INSERT into address (NAME, EMAIL, age, Address) VALUES (' +textbox1.text+" ', ' "+ textbox2.text+ "', '" "+textbox3.text+", "" +textbox4.text+ ";
OleDbInsertCommand1.Connection.Open ();
Open connection
Oledbinsertcommand1.executenonquery ();
Execute the SQL statement
OleDbInsertCommand1.Connection.Close ();
Close connection
In practical programming, we need to pay attention to the problem of single quotes in SQL statements as well as numbers and string handling problems.

Delete database Data
Add a TextBox5 and a button to the Web form, and in TextBox5, fill in the value of the name field of the record you want to delete, and then press the key to perform the deletion. The key code is as follows:

System.Data.OleDb.OleDbCommand oleDeleteCommand1 = new System.Data.OleDb.OleDbCommand ();
This.oleDbDataAdapter1.DeleteCommand = OleDeleteCommand1;
Declared as OLE DB command
Oledeletecommand1.commandtext= "DELETE from address WHERE name= '" +textbox5.text+ "";
Oledeletecommand1.connection = This.oledbconnection1;
Indicate connection
OleDeleteCommand1.Connection.Open ();
Open connection
Oledeletecommand1.executenonquery ();
Execute SQL statement
OleDeleteCommand1.Connection.Close ();
Close connection
For a DataGrid refresh after an Add, update, and delete operation, you can perform a similar select SQL statement.

The above code is available for all kinds of non-SQL Server database programming reference and use, Microsoft provides such as OLE DB Provider for Oracle, as/400 and VSAM, and many other drivers, OLE DB mode does provide a more extensive data access scope than the ODBC way, For access to data in Access libraries, data in messaging systems, text and graphics on the Web, directory services, and so on, ODBC-compliant data sources are subsets of data stores that conform to OLE DB standards, and OLE DB APIs are compliant with COM standards and object-based APIs. These are significant improvements over the original ODBC approach, providing broader support for integrated data integration processing.

Through the above explanation, the author hopes to make the reader preliminary understanding in the Visual Studio.NET, DB2 is the background database when the application system development step, also hoped that the reader extrapolate, understands the thought and the method, in order to better apply to own system development.

These programs are compiled and run correctly in the Chinese Windows Server, IBM DB2, and Visual Studio.NET Beta 2 environments.