Дата публикации: 14.10.2013

Создание и работа с PACKAGE (C#, FB3)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
////////////////////////////////////////////////////////////////////////////////
//Samples for LCPI ADO.NET Data provider for OLEDB.
//                                                                    14.10.2013
using System;
using System.Diagnostics;
using System.Data;
using System.Data.Common;
using System.Text.RegularExpressions;
using lcpi.data.oledb;
 
namespace Sample_0010{
////////////////////////////////////////////////////////////////////////////////
//class Program
 
class Program
{
 //----------------------------------------- connection string
 private const string c_cn_str
  ="provider=LCPI.IBProvider.3;"
  +"location=vxp-fb30:e:\\database\\ibp_test_fb30_d3.gdb;"
  +"user id=gamer;"
  +"password=vermut;"
  +"exec_sp_named_param=true;" //use named parameters in "exec SP"
  +"dbclient_library=fbclient_30.dll";
 
 //----------------------------------------- DDL for test package
 private const string c_sql_package_ddl
  ="recreate package MATH as\n"
  +"begin\n"
  +" procedure sp_add(a integer,b integer) returns (r integer);\n"
  +" procedure sp_sub(a integer,b integer) returns (r integer);\n"
  +"end;\n"
  +"\n"
  +"recreate package body MATH as\n"
  +"begin\n"
  +" procedure sp_add(a integer,b integer) returns (r integer) as\n"
  +" begin\n"
  +"  r=a+b;\n"
  +" end\n"
  +" procedure sp_sub(a integer,b integer) returns (r integer) as\n"
  +" begin\n"
  +"  r=a-b;\n"
  +" end\n"
  +"end;";
 
 //----------------------------------------------------------------------
 static int Main(string[] args)
 {
  int resultCode=0;
 
  try
  {
   using(var cn=new OleDbConnection(c_cn_str))
   {
    Console.WriteLine("Connect to database ...");
    cn.Open();
 
    {
     //check server name and version
     var ds_info=cn.GetSchema(DbMetaDataCollectionNames.DataSourceInformation);
 
     var dbms_name=ds_info.Rows[0][DbMetaDataColumnNames.DataSourceProductName];
     var dbms_verN=ds_info.Rows[0][DbMetaDataColumnNames.DataSourceProductVersionNormalized];
 
     Console.WriteLine("DBMS Name    : {0}",Helper_DBValueToStr(dbms_name));
     Console.WriteLine("DBMS Version : {0}",Helper_DBValueToStr(dbms_verN));
 
     if(!(dbms_name is string))
      throw new ApplicationException("DBMS name is not avaliable");
 
     if(((string)dbms_name)!="Firebird")
      throw new ApplicationException("Not supported DBMS");
 
     if(!(dbms_verN is string))
      throw new ApplicationException("Normalized version of DBMS is not available");
 
     //check the DBMS version format [I am RegExp Hero! :)]
     if(!Regex.IsMatch((string)dbms_verN,@"^([0-9]{2}\.){3}[0-9]{6}$"))
      throw new ApplicationException("Wrong DBMS version format");
 
     if(System.StringComparer.Ordinal.Compare(((string)dbms_verN),"03")<0)
      throw new ApplicationException("Not supported DBMS version");
    }//local
 
    //-------------------------------------- RECREATE PACKAGE
    Console.WriteLine("");
    Console.WriteLine("Start transaction ...");
    using(var tr=cn.BeginTransaction(IsolationLevel.RepeatableRead))
    {
     using(var cmd=new OleDbCommand(c_sql_package_ddl,cn,tr))
     {
      Console.WriteLine("Recreate test package ...");
 
      cmd.ExecuteNonQuery();
     }//using cmd
 
     Console.WriteLine("Commit...");
     tr.Commit();
    }//using tr
 
    //--------------------------------------- WORK
    Console.WriteLine("");
    Console.WriteLine("Start transaction ...");
    using(var tr=cn.BeginTransaction(IsolationLevel.RepeatableRead))
    {
     using(var cmd=new OleDbCommand("",cn,tr))
     {
      //------------------------------------ ADD
      cmd.CommandText="exec MATH.SP_ADD";
 
      cmd["a"].Value=2;
      cmd["b"].Value=3;
 
      cmd.ExecuteNonQuery();
 
      Console.WriteLine("{0}+{1}={2}",
                        Helper_DBValueToStr(cmd["a"].Value),
                        Helper_DBValueToStr(cmd["b"].Value),
                        Helper_DBValueToStr(cmd["r"].Value));
 
      //------------------------------------ SUB
      cmd.CommandText="exec MATH.SP_SUB";
 
      cmd["a"].Value=2;
      cmd["b"].Value=3;
 
      cmd.ExecuteNonQuery();
 
      Console.WriteLine("{0}-{1}={2}",
                        Helper_DBValueToStr(cmd["a"].Value),
                        Helper_DBValueToStr(cmd["b"].Value),
                        Helper_DBValueToStr(cmd["r"].Value));
     }//using cmd
 
     Console.WriteLine("Commit...");
     tr.Commit();
    }//using tr
   }//using cn
  }
  catch(Exception e)
  {
   ++resultCode;
 
   Console.WriteLine("");
   Console.WriteLine("ERROR: {0} - {1}",e.Source,e.Message);
  }//catch
 
  return resultCode;
 }//Main
 
  //Helper methods -------------------------------------------------------
 private static string Helper_DBValueToStr(object v)
 {
  if(Object.ReferenceEquals(v,null))
   return "#NULL";
 
  if(DBNull.Value.Equals(v))
   return "#DBNULL";
 
  return v.ToString();
 }//Helper_DBValueToStr
};//class Program
 
////////////////////////////////////////////////////////////////////////////////
}//namespace Sample_0010


Output of sample.
Вывод.
Все примеры