How to put data into the heart of C # DB2 instance

Usually occasionally because the work need to write some of their own small tools to operate the database, because just need to implement some small functions, also do not involve a multi-type database, perhaps a one-time use. So at this time those chatty database operation framework, it is no longer suitable for me. And this blog post is mainly to record their work in the main use of several ways, summarized and summarized.

First, the purpose.

1, understand the basic steps of database operation.

2. Understand the basic operation of the database using ODBC method.

3, understand the way of using OLE DB for the basic operation of the database.

4. Understand the database operation using database Operation class library provided by database vendor.

Second, the basic principles and processes.

1, generally we operate the database main design of several steps is basically fixed.

1. New Database connection

2. Create DATABASE commands and set related parameters

3. Execute database command to get return result

4. Close the database connection

2. Pre-configured ODBC is required to prepare content (because the DSN that is used after the use of ODBC in the text is involved)

To use ODBC for the DB2 database, you must install the corresponding driver yourself. Microsoft only provides a default installed driver for its own database and Oracle database. Specific driver download and installation can own Baidu.

After filling in a meaningful name for the data source, if you do not have your database in the drop-down box, simply click Add.

DB2 default port number 50000, if you are not sure if you have modified the default port number, you can find your own port information by entering the DB2 get dbm CFG command in the DB2 instance.

3, the basic operation of the database using ODBC method.

The System.Data.Odbc namespace in C # provides a number of well-integrated database operation classes.

The first step is to use OdbcConnection to create a connection to the database.

Create a corresponding database connection by using the specified connection string.

"Driver={sql Server}; server= (local); Trusted_connection=yes;database=adventureworks; "

"Driver={microsoft ODBC for Oracle}; Server=oracle8i7; Persist Security Info=false; Trusted_connection=yes "

"Driver={microsoft Access Driver (*.mdb)};D Bq=c:\bin\northwind.mdb"

"Driver={microsoft Excel Driver (*.xls)};D Bq=c:\bin\book1.xls"

"Driver={microsoft Text Driver (*.txt; *.csv)};D Bq=c:\bin "

"Dsn=dsnname"

Several of the above styles are the corresponding configuration methods given on Microsoft's MSDN. The simplest way is to use the latter one. Because I am using it, I will establish the appropriate DSN.

The second step, create a Database command, and set the relevant parameters.

The most common method is to create an instance of the OdbcCommand class when used, which can execute SQL statements or stored procedures. Instantiate the method of this class by using two parameters.

OdbcCommand (String, OdbcConnection) The first parameter determines the SQL or stored procedure that needs to be executed. The second parameter determines the execution on that database connection.

The third step, execute the database command gets the returned result set, and processes the result machine.

The OdbcCommand instance provides three basic database operation methods.

ExecuteReader (): Executes the result set returned to the query.

ExecuteNonQuery (): Executes the data UPDATE statement. (Insert,delete,update,set)

ExecuteScalar (): Returns the first column of the first row of the query result set from the database.

Using the OdbcDataAdapter class with OdbcCommandBuilder can be used to populate a DataSet.

code example:

   1:  privatebool CONNECTION2DB2WITHODBC ()

   2:   {

   3:              try

   4:              {

   5:                  new OdbcConnection ("Dsn=localdbinst");//Create Connection

   6:                  string"INSERT into Db2inst1. TEST (ID, NAME, age) VALUES (3, ' Candy ', +) ";

   7:                  string"SELECT COUNT (*) from Test";

   8:                  string"select * from Test";

   9:                  new OdbcCommand (COMMANDTEXT3); Create command

  Ten:                  command. Connection = OdbcConnection;

One   :  

  :                  odbcconnection.open ();  Open a database connection

  :  

  :                  //                command. ExecuteNonQuery ();

A   :                  //                int count = (int) command. ExecuteScalar ();

  :                  //                MessageBox.Show ("The Count of table is:" + Count);

  :                  odbcdatareader reader = command. ExecuteReader (); Execute Database command

  :                  string"";

  :  while                 (reader. Read ())

  :                  {

  :                      string id = (string) reader[0];

A   :                      string name = (string) reader[1];

  23°c:                      int age = (int) reader[2];

  :  "                    Id:" "  Name:" "Age  :" + age + Environment.NewLine;

  :                  }

  :                  MessageBox.Show (content);

  :                  odbcconnection.close ();

  :              }

  :              catch (OdbcException exception)

  :              {

To   :                  MessageBox.Show (exception. Message + exception. ErrorCode + exception. Data);

  :                  returnfalse;

  :              }

  :              returntrue;

  :   }

4, the database operation through OLE DB connection method.

The Baidu Encyclopedia defines Oledb:ole db (OLE DB) as a strategic low-level application interface to different data sources. OLE DB includes not only the structured Problem language (SQL) capability of the Microsoft-funded standard data Interface Open Database Connectivity (ODBC), but also the pathways for other non-SQL data types.

Illustrates that OLE DB supports a broader range of support relative to ODBC.

The way you create a connection has specific specifications on MSDN.

"Provider=msdaora; Data Source=oracle8i7; Persist Security info=false;integrated Security=yes "

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\bin\localaccess40.mdb "

"Provider=sqloledb;data source= (local); Integrated Security=sspi"

"PROVIDER=IBMDADB2;" + "Data source=databasename; Uid=username; Pwd=password; "

The rest of the steps are the same as ODBC, except that the instantiated classes are different.

The connection code instance.

   1:  privatebool connection2db2witholedb ()

   2:   {

   3:        try

   4:        {

   5:          new OleDbConnection ("PROVIDER=IBMDADB2;") "Data source=sample; Uid=db2inst1; [email protected]; ");

   6:          con. Open ();

   7:        }

   8:        catch (Exception)

   9:        {

  Ten:           returnfalse;

One   :        }

  :        returntrue;

  :   }

5. Use an assembly provided by IBM. After installing the client, there is a netf11 directory under the Bin directory under the installed directory, and the assembly provided in this directory can easily operate the DB2 database. Features are also relatively complete points. Targeted is also relatively strong.

Using this assembly provided by IBM, you can return a result set in the form of XmlReader, not only by using the methods that ODBC has. The versatility of the data has also been greatly improved. It also provides many convenient methods for assigning parameters that are often used in DB2 programming.

Connection Code instance:

   1:  privatebool connection2db2 ()

   2:  {

   3:      new db2connection ("database=sample; Userid=db2inst1; [Email protected]; server=192.168.1.102 ");

   4:      new Db2command ("INSERT into Db2inst1. TEST (ID, NAME, age) VALUES (2, ' Kasson ', ")");

   5:      mycommand.connection = cn;

   6:      try

   7:      {

   8:          CN. Open ();

   9:          mycommand.executenonquery ();

  Ten:          CN. Close ();

One   :      }

  :      catch (Exception)

  :      {

  :          returnfalse;

  :      }

  :      returntrue;

  :  }

Iii. Summary of use. This blog post mainly introduces several kinds of database connection methods that are used frequently in development, and in the process of using, the individual feels that ODBC and OLE DB are basically not very different, OLE DB supports the content more broadly. The reverse means that ODBC is more specialized than OLE DB. The same is true for vendor-supplied assemblies. Based on the principle of not repeating wheels, it is recommended to choose the assembly provided by the manufacturer. However, for some databases it is necessary to install additional products if you want to use the convenience provided by the manufacturer.

How to put data into the heart of C # DB2 instance