Publish date: 2013-10-17

System.Data.OleDbDataAdapter and query «INSERT … RETURNING» (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
using System;
using System.Data;
using System.Data.OleDb;
 
namespace Sample_0001{
////////////////////////////////////////////////////////////////////////////////
//class Program
 
class Program
{
 private const int c_nRows=5;
 
 private const string c_cn_str
  ="provider=LCPI.IBProvider.3;"
  +"location=localhost:d:\\database\\ibp_test_fb25_d3.gdb;"
  +"user id=gamer;"
  +"password=vermut;";
 
 //Table:
 // TEST_SEQUENTIAL_MOVE
 //
 //Columns:
 // ID BIGINT [NOT NULL] [PRIMARY KEY]
 //
 //Triggers
 // BEFORE INSERT. IF NEW.ID is NULL THEN NEW.ID=GEN_ID(...,1);
 
 static int Main()
 {
  int resultCode=0;
 
  try // [catch]
  {
   OleDbConnection  cn=null;
   OleDbTransaction tr=null;
   OleDbDataAdapter da=null;
   DataTable        table=null;
 
   try // [finally]
   {
    cn=new OleDbConnection(c_cn_str);
 
    cn.Open();
 
    //--------------------------------------
    tr=cn.BeginTransaction(IsolationLevel.RepeatableRead);
 
    //-------------------------------------- create data_adapter
    da=new OleDbDataAdapter();
 
    //-------------------------------------- prepare insert command
 
    //use explicit marker for OUT-parameter
    da.InsertCommand=new OleDbCommand("insert into TEST_SEQUENTIAL_MOVE (ID) values (?)\n"
                                      +"returning ID into ?",
                                      cn,tr);
    {
     var cmd_params=da.InsertCommand.Parameters;
 
     cmd_params.Add(Helper__CreateParam(ParameterDirection.Input,
                                        OleDbType.BigInt,
                                        "ID"));
 
     cmd_params.Add(Helper__CreateParam(ParameterDirection.Output,
                                        OleDbType.BigInt,
                                        "ID"));
    }//local
 
    //-------------------------------------- build table
    table=new DataTable();
 
    table.Columns.Add("ID",typeof(Int64));
 
    //-------------------------------------- fill table
    for(int i=0;i<c_nRows;++i)
     table.Rows.Add(table.NewRow());
 
    Helper__PrintTable(table);
 
    //-------------------------------------- update
    Console.WriteLine("");
    Console.WriteLine("Update");
 
    da.Update(table);
 
    //--------------------------------------
    Console.WriteLine("");
    Helper__PrintTable(table);
 
    //-------------------------------------- commit
    tr.Commit();
   }
   finally
   {
    Helper__Dispose(table);
    Helper__Dispose(da);
    Helper__Dispose(tr);
    Helper__Dispose(cn);
   }//finally
  }
  catch(Exception e)
  {
   resultCode=1;
 
   Console.WriteLine("");
   Console.WriteLine("ERROR: {0} - {1}",e.Source,e.Message);
  }//catch
 
  return resultCode;
 }//Main
 
 //-----------------------------------------------------------------------
 private static void Helper__PrintTable(DataTable table)
 {
  for(int i=0,_c=table.Rows.Count;i!=_c;++i)
  {
   Console.WriteLine("Rows[{0}]={1} [{2}]",
                     i,
                     Helper__CStrNE(table.Rows[i][0]),
                     table.Rows[i].RowState.ToString());
  }//for i
 }//Helper__PrintTable
 
 //-----------------------------------------------------------------------
 private static OleDbParameter Helper__CreateParam(ParameterDirection direction,
                                                   OleDbType          type,
                                                   string             source)
 {
  var param=new OleDbParameter();
 
  param.Direction   =direction;
  param.OleDbType   =type;
  param.SourceColumn=source;
 
  return param;
 }//Helper__CreateParam
 
 //-----------------------------------------------------------------------
 private static string Helper__CStrNE(object v)
 {
  if(Object.ReferenceEquals(v,null))
   return "#NULL";
 
  if(DBNull.Value.Equals(v))
   return "#DBNULL";
 
  return v.ToString();
 }//Helper__CStrNE
 
 //-----------------------------------------------------------------------
 private static void Helper__Dispose(IDisposable obj)
 {
  if(!Object.ReferenceEquals(obj,null))
   obj.Dispose();
 }//Helper__Dispose
 
 //-----------------------------------------------------------------------
 private static void Helper__Dispose(OleDbDataAdapter da)
 {
  if(Object.ReferenceEquals(da,null))
   return;
 
  Helper__Dispose(da.InsertCommand);
 
  da.Dispose();
 }//Helper__Dispose
};//class Program
 
////////////////////////////////////////////////////////////////////////////////
}//namespace Sample_0001


Output of sample.
Output.
All examples