[转]在.NET中调用Oracle存储过程经验总结(5)

 

前台调用代码如下，有点繁琐，请耐心阅读：

     string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";

     string queryString = "TestPackage.SelectRecords";

     OracleConnection cn = new OracleConnection(connectionString);

     OracleCommand cmd = new OracleCommand(queryString,cn);

     cmd.CommandType = CommandType.StoredProcedure;

     cmd.Parameters.Add("ret_cursor",OracleType.Cursor);

     cmd.Parameters["ret_cursor"].Direction = ParameterDirection.Output;

    

     try

     {

          cn.Open();

          OracleDataAdapter da = new OracleDataAdapter(cmd);

          DataSet ds = new DataSet();

          da.Fill(ds,"TestTable");

          cn.Close();

          int count = ds.Tables["TestTable"].Rows.Count;

         

          /*打印原始记录*/

          Console.WriteLine("Old Records is:");

          for( int i = 0;i <= count - 1;i++ )

         {

              string id = ds.Tables["TestTable"].Rows[i]["ID"].ToString();

              string name = ds.Tables["TestTable"].Rows[i]["NAME"].ToString();

              string age = ds.Tables["TestTable"].Rows[i]["AGE"].ToString();

 

              Console.WriteLine("Record {0}:",i+1);

              Console.WriteLine("ID:{0}"tName:{1}"tAge:{2}"n",id,name,age);

         }

        

         da.SelectCommand = cmd;     //为DataAdapter指定SelectCommand

 

         OracleCommand updateCmd = new OracleCommand("TestPackage.UpdateRecords",cn);

         updateCmd.CommandType = CommandType.StoredProcedure;

         updateCmd.Parameters.Add("id_in",OracleType.Number,3,"ID");

         updateCmd.Parameters.Add("newName",OracleType.VarChar,20,"NAME");

         updateCmd.Parameters.Add("newAge",OracleType.Number,3,"AGE");

         da.UpdateCommand = updateCmd;        //为DataAdapter指定UpdateCommand

 

         OracleCommand deleteCmd = new OracleCommand("TestPackage.DeleteRecords",cn);

         deleteCmd.CommandType = CommandType.StoredProcedure;

         deleteCmd.Parameters.Add("id_in",OracleType.Number,3,"ID");

         da.DeleteCommand = deleteCmd;        //为DataAdapter指定DeleteCommand

 

         OracleCommand insertCmd = new OracleCommand("TestPackage.InsertRecords",cn);

          insertCmd.CommandType = CommandType.StoredProcedure;

         insertCmd.Parameters.Add("name_in",OracleType.VarChar,20,"NAME");

         insertCmd.Parameters.Add("age_in",OracleType.Number,3,"AGE");

         da.InsertCommand = insertCmd;        //为DataAdapter指定InsertCommand

 

         DataTable newTable = ds.Tables["TestTable"];

        

         /*修改第一条记录*/

         newTable.Rows[0]["AGE"] = 22;

         newTable.Rows[0]["NAME"] = "John";

        

         /*删除一条记录*/

         newTable.Rows[2].Delete();

 

         /*插入一条记录*/

         DataRow newRow = newTable.NewRow();

         newRow["NAME"] = "Bob";

         newRow["AGE"] = 99;

         newTable.Rows.Add(newRow);

 

         cn.Open();

         da.Update(newTable);       //将更改更新到数据库

         newTable.Clear();           //清空DataTable

         da.Fill(newTable);           //获取更改后的数据

         cn.Close();

 

         /*打印新记录*/

         Console.WriteLine("New Records is:");

         for( int i = 0;i <= newTable.Rows.Count - 1;i++ )

         {

              string id = newTable.Rows[i]["ID"].ToString();

              string name = newTable.Rows[i]["NAME"].ToString();

              string age = newTable.Rows[i]["AGE"].ToString();

 

              Console.WriteLine("Record {0}:",i+1);

              Console.WriteLine("ID:{0}"tName:{1}"tAge:{2}"n",id,name,age);

         }

     }

     catch( OracleException ex )

     {

          Console.WriteLine("Exception occurred!");

          Console.WriteLine("The exception message is:{0}",ex.Message.ToString());

     }

     finally

     {

         Console.WriteLine("------------------End-------------------");

}

转载于:https://www.cnblogs.com/seven911/archive/2008/10/20/1315519.html