DB2 9 应用开发（733 考试）认证指南，第 6 部分: .NET 编程(4)-sdccf-ChinaUnix博客

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241111) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241110) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241109) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241108) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241107) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241106) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241105) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241104) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241103) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241102) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241101) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241100) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241099) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241098) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241097) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241096) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241095) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241094) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241093) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241092) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241091) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241090) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241089) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241088) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241087) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241086) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241085) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241084) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241083) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241082) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241081) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241080) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241079) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241078) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241077) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241076) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241075) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241074) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

用 ADO.NET 读取和更新数据

在本节中，您将学习如何在 .NET 应用程序中使用 ADO.NET DB2Command 对象来执行 SQL 语句。读完本节后，您将学会：

什么是 DB2Command 对象
如何使用 ExecuteReader() 方法从 DB2 中读数据
如何使用 ExecuteNonQuery() 方法插入和删除数据
如何使用 ExecuteScalar() 方法返回单个值

DB2Command 对象允许您指定与 DB2 进行交互的方式。例如，可以对 DB2 中的数据执行 SELECT、INSERT、MODIFY 和 DELETE 命令。

DB2Command 对象可以使用 DB2Connection.CreateCommand() 方法创建。下面是一个例子：

DB2Command cmd;
...
//assume conn is a DB2Connection object
cmd = conn.createCommand();

若要从 DB2Command 对象中执行 INSERT、UPDATE 或 DELETE SQL 语句，可将 CommandContext 设置为相应的 SQL 语句，并调用 cmd.ExecuteNonQuery() 方法。例如：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) 
VALUES (401, 'Peter', 99,'Mgr')";      
cmd.ExecuteNonQuery();

若要从 DB2Command 对象中执行一个查询，可将 CommandContext 设置为查询，并调用 cmd.ExecuteReader() 方法：

                    
// assume a DB2Connection conn 
DB2Command cmd = conn.CreateCommand(); 
DB2Transaction trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99"; 
DB2DataReader reader = cmd.ExecuteReader();

// assume a DB2Transaction object trans
trans.Rollback(); 
... 
trans.Commit();

                    
// assume a DB2DataReader reader 
Int16 id = 0; 
String name = ""; 
string job = "";

// Output the results of the query 
while(reader.Read()) 
{ 
    id = reader.GetInt16(0); 
    name = reader.GetString(1); 
    job = reader.GetString(2);
         
    Console.WriteLine(" " + id + " " + name + " " + job); 
} 

reader.Close();

回页首

对于动态 SQL，需要添加参数到 DB2Command 对象中，并设置这些参数的值，例如：

                    
String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};

//Assume conn is a DB2Connection object
DB2Command cmd = conn.CreateCommand(); 
trans = conn.BeginTransaction(); 
cmd.Transaction = trans; 
cmd.CommandText = insertStmt;
cmd.Prepare(); 

// Declare the parameters for the statement 
cmd.Parameters.Add("@id", DB2Type.SmallInt);
cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
//Set their values and then insert
for (int i = 0; i < 3; i++) 
{     	
  cmd.Parameters["@id"].Value = 401 + i;
  cmd.Parameters["@name"].Value = staff[i,0];
  cmd.Parameters["@job"].Value = staff[i,1];
        
  cmd.ExecuteNonQuery();
}

回页首

清单 9 演示了本节中讨论的所有概念，包括：

创建 DB2Command 对象
执行 INSERT、UPDATE 或 DELETE 语句
执行 SQL 查询
解析结果集对象

                    

using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;


class AccessSampDb
{
  public static void Main(String[] args)
  {
    DB2Connection conn = null;
    DB2Transaction trans = null;
    DB2DataReader reader = null;
     
    String insertStmt = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?, ?, 99, ?)";
    String [,] staff =  {{"Smyth","LW"},{"Hesky","RW"},{"Peter","CLK"}};
  
    try
    {
      String dbName = "SAMPLE";
      String connectString = "Database=" + dbName; 
      
      conn = new DB2Connection(connectString); 
      
      //Connecting to SAMPLE database
      Console.Writeline("Connecting to SAMPLE database");
      conn.Open(); 

      DB2Command cmd = conn.CreateCommand(); 
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = insertStmt;
      cmd.Prepare(); 
      
      // Declare the parameters for the statement and set their value
      cmd.Parameters.Add("@id", DB2Type.SmallInt);
      cmd.Parameters.Add("@name", DB2Type.VarChar, 9);
      cmd.Parameters.Add("@job", DB2Type.Char, 5);
           
      //Insert into STAFF table
      for (int i = 0; i < 3; i++) 
      { 
        //Set the values to the parameters	
        cmd.Parameters["@id"].Value = 401 + i;
        cmd.Parameters["@name"].Value = staff[i,0];
        cmd.Parameters["@job"].Value = staff[i,1];
        
        cmd.ExecuteNonQuery();
      }
            
      trans.Commit();

      //Query data in table STAFF
      trans = conn.BeginTransaction(); 
      cmd.Transaction = trans; 
      cmd.CommandText = "SELECT ID, NAME, JOB FROM STAFF WHERE DEPT = 99";
      reader = cmd.ExecuteReader();     
 
      Int16 id = 0; 
      String name = ""; 
      String job = "";

      // Output the results of the query 
      Console.WriteLine("Employe #  Employee Name   Job");
      while(reader.Read()) 
      { 
         id = reader.GetInt16(0); 
         name = reader.GetString(1); 
         job = reader.GetString(2);
         
         Console.WriteLine(" " + id + "        " + name + "          " + job); 
      } 

      reader.Close();
      trans.Commit();     
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.Message); 
    }
    finally
    {
    	if (reader != null)
    	{
          reader.Close();
    	}
    	
    	if (conn != null)
    	{
            conn.Close();
    	}
    } 
  }
}

若要编译该代码，可将该程序放入包含脚本 bldapp.bat 的 %DB2PATH%\samples\.NET\cs 目录中。然后发出以下命令：

 bldapp AccessSamplDb

若要运行该应用程序，可发出以下命令：

 AccessSamplDb

这个应用程序的输出应类似下面的内容：

Employe #  Employee Name   Job
 401        Smyth          LW
 402        Hesky          RW
 403        Peter          CLK

阅读(241073) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们