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

Работа с массивами в таблице базы данных. (FB, C#)

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
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
////////////////////////////////////////////////////////////////////////////////
//Samples for LCPI ADO.NET Data provider for OLEDB.
//                                                                   10.01.2015.
using System;
using System.Data;
using System.Diagnostics;
using lcpi.data.oledb;
 
using structure_lib=lcpi.lib.structure;
 
namespace Sample_0019{
////////////////////////////////////////////////////////////////////////////////
//class Program
 
class Program
{
 private const string c_cn_str
   ="provider=LCPI.IBProvider.3;"
   +"location=localhost:d:\\database\\ibp_test_fb25_d3.gdb;"
   +"user id=gamer;"
   +"password=vermut;";
 
 //-----------------------------------------------------------------------
 static int Main()
 {
  int resultCode=0;
 
  try //[catch]
  {
   Helper__Work();
  }
  catch(Exception exc)
  {
   resultCode=1;
 
   Console.WriteLine("ERROR: {0} - {1}",exc.Source,exc.Message);
  }//catch
 
  return resultCode;
 }//Main
 
 //-----------------------------------------------------------------------
 private static void Helper__Work()
 {
  OleDbConnection  cn=null;
  OleDbTransaction tr=null;
  OleDbCommand     cmd=null;
  OleDbCommand     cmd_s=null;
  OleDbDataReader  reader=null;
 
  try // [finally with dispose]
  {
   cn=new OleDbConnection(c_cn_str);
 
   cn.Open();
 
   tr=cn.BeginTransaction(IsolationLevel.RepeatableRead);
 
   cmd=new OleDbCommand(null,cn,tr);
   cmd_s=new OleDbCommand(null,cn,tr);
 
   //-------------------
   if(cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                             new object[]{null,null,"NET_SMPL_0019"}).Rows.Count==0)
   {
    Console.WriteLine("create test table ...");
 
    cmd.CommandText
     ="create generator GEN_ID_NET_SMPL_0019;\n"
     +"create table NET_SMPL_0019(\n"
     +" ID INTEGER NOT NULL PRIMARY KEY,\n"
     +" ARR VARCHAR(128) [0:2,1:2]);\n"
     +"create trigger BI_NET_SMPL_0019 for NET_SMPL_0019\n"
     +" before insert\n"
     +" as\n"
     +" begin if(new.ID is NULL) then new.ID=GEN_ID(GEN_ID_NET_SMPL_0019,1); end;";
 
    cmd.ExecuteNonQuery();
 
    tr.CommitRetaining();
   }//if
 
   //-------------------
   Console.WriteLine("insert new array...");
 
   {
    var arr=Array.CreateInstance(typeof(string),
                                 /*lengths*/new int[]{3,2},
                                 /*lowerBounds*/new int[]{0,1});
 
 
    arr.SetValue("firebird"  ,0,1);
    arr.SetValue("2.5.3 SU1" ,0,2);
 
    arr.SetValue("interbase" ,1,1);
    arr.SetValue("XE7"       ,1,2);
 
    arr.SetValue("yaffil"    ,2,1);
    arr.SetValue("He flew away, but promised to return",2,2);
 
    cmd.CommandText
     ="insert into NET_SMPL_0019 (ARR) values(:arr) returning ID into :id;";
 
    cmd["arr"].Value=arr;
 
    cmd.ExecuteNonQuery();
   }//local
 
   var recID=cmd["id"].Value;
 
   Console.WriteLine("recID: {0}",recID);
 
   //-------------------
   Console.WriteLine("");
   Console.WriteLine("select array [ExecuteScalar] ...");
 
   cmd_s.CommandText="select ARR from NET_SMPL_0019 where ID=:id";
 
   cmd_s["id"].Value=recID;
 
   var Arr2=(Array)cmd_s.ExecuteScalar();
 
   Helper__PrintArr(Arr2);
 
   //-------------------
   Console.WriteLine("");
   Console.WriteLine("update array ...");
 
   Arr2.SetValue("FIREBIRD"  ,0,1);
   Arr2.SetValue("INTERBASE" ,1,1);
   Arr2.SetValue("YAFFIL"    ,2,1);
 
   cmd.CommandText="update NET_SMPL_0019 set ARR=:arr2 where ID=:recID";
 
   cmd["arr2"].Value=Arr2;
   cmd["recID"].Value=recID;
 
   var rowsAffected=cmd.ExecuteNonQuery();
 
   Console.WriteLine("rowsAffected: {0}",rowsAffected);
 
   //-------------------
   Console.WriteLine("");
   Console.WriteLine("select updated array [ExecuteReader]...");
 
   reader=cmd_s.ExecuteReader(CommandBehavior.SingleResult);
 
   if(!reader.Read())
    throw new ApplicationException("Test record not found!");
 
   Helper__PrintArr(reader.GetArray(0 /*ARR*/));
 
   reader.Close();
 
   //-------------------
   Console.WriteLine("");
 
   tr.Commit();
  }
  finally
  {
   structure_lib.DisposeUtils.Exec(ref reader);
   structure_lib.DisposeUtils.Exec(ref cmd);
   structure_lib.DisposeUtils.Exec(ref cmd_s);
   structure_lib.DisposeUtils.Exec(ref tr);
   structure_lib.DisposeUtils.Exec(ref cn);
  }//finally
 }//Helper__Work
 
 //-----------------------------------------------------------------------
 private static void Helper__PrintArr(Array Arr)
 {
  Debug.Assert(!Object.ReferenceEquals(Arr,null));
 
  Helper__PrintArrElem(Arr,0,1);
  Helper__PrintArrElem(Arr,0,2);
  Helper__PrintArrElem(Arr,1,1);
  Helper__PrintArrElem(Arr,1,2);
  Helper__PrintArrElem(Arr,2,1);
  Helper__PrintArrElem(Arr,2,2);
 }//Helper__PrintArr
 
 //-----------------------------------------------------------------------
 private static void Helper__PrintArrElem(Array Arr,int i1,int i2)
 {
  Debug.Assert(!Object.ReferenceEquals(Arr,null));
 
  Console.WriteLine("[{0},{1}]=\"{2}\"",i1,i2,Arr.GetValue(i1,i2));
 }//Helper__PrintArrElem
};//class Program
 
////////////////////////////////////////////////////////////////////////////////
}//nms Sample_0019


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