Loading...
Searching...
No Matches
Transport9.cs
1using System;
2using System.Collections.Generic;
3using System.Text;
4using System.IO;
5using GAMS;
6using System.Data.OleDb;
7
8
9namespace TransportSeq
10{
19 class Transport9
20 {
21 static void Main(string[] args)
22 {
23 GAMSWorkspace ws;
24 if (Environment.GetCommandLineArgs().Length > 1)
25 ws = new GAMSWorkspace(systemDirectory: Environment.GetCommandLineArgs()[1]);
26 else
27 ws = new GAMSWorkspace();
28 // fill GAMSDatabase by reading from Access
29 GAMSDatabase db = ReadFromAccess(ws);
30
31 // run job
32 using (GAMSOptions opt = ws.AddOptions())
33 {
34 GAMSJob t9 = ws.AddJobFromString(GetModelText());
35 opt.Defines.Add("gdxincname", db.Name);
36 opt.AllModelTypes = "xpress";
37 t9.Run(opt, db);
38 foreach (GAMSVariableRecord rec in t9.OutDB.GetVariable("x"))
39 Console.WriteLine("x(" + rec.Key(0) + "," + rec.Key(1) + "): level=" + rec.Level + " marginal=" + rec.Marginal);
40 // write results into Access file
41 WriteToAccess(ws, t9.OutDB);
42 }
43
44 }
45
46 static void ReadSet(OleDbConnection connect, GAMSDatabase db, string strAccessSelect, string setName, int setDim, string setExp = "")
47 {
48 try
49 {
50 OleDbCommand cmd = new OleDbCommand(strAccessSelect, connect);
51 connect.Open();
52
53 OleDbDataReader reader = cmd.ExecuteReader();
54
55 if (reader.FieldCount != setDim)
56 {
57 Console.WriteLine("Number of fields in select statement does not match setDim");
58 Environment.Exit(1);
59 }
60
61 GAMSSet i = db.AddSet(setName, setDim, setExp);
62
63 string[] keys = new string[setDim];
64 while (reader.Read())
65 {
66 for (int idx = 0; idx < setDim; idx++)
67 keys[idx] = reader.GetString(idx);
68
69 i.AddRecord(keys);
70 }
71 }
72 catch (Exception ex)
73 {
74 Console.WriteLine("Error: Failed to retrieve the required data from the database.\n{0}", ex.Message);
75 Environment.Exit(1);
76 }
77 finally
78 {
79 connect.Close();
80 }
81 }
82
83 static void ReadParameter(OleDbConnection connect, GAMSDatabase db, string strAccessSelect, string parName, int parDim, string parExp = "")
84 {
85 try
86 {
87 OleDbCommand cmd = new OleDbCommand(strAccessSelect, connect);
88 connect.Open();
89
90 OleDbDataReader reader = cmd.ExecuteReader();
91
92 if (reader.FieldCount != parDim+1)
93 {
94 Console.WriteLine("Number of fields in select statement does not match parDim+1");
95 Environment.Exit(1);
96 }
97
98 GAMSParameter a = db.AddParameter(parName, parDim, parExp);
99
100 string[] keys = new string[parDim];
101 while (reader.Read())
102 {
103 for (int idx = 0; idx < parDim; idx++)
104 keys[idx] = reader.GetString(idx);
105
106 a.AddRecord(keys).Value = Convert.ToDouble(reader.GetValue(parDim));
107 }
108 }
109 catch (Exception ex)
110 {
111 Console.WriteLine("Error: Failed to retrieve the required data from the database.\n{0}", ex.Message);
112 Environment.Exit(1);
113 }
114 finally
115 {
116 connect.Close();
117 }
118 }
119
120 static GAMSDatabase ReadFromAccess(GAMSWorkspace ws)
121 {
122
123 GAMSDatabase db = ws.AddDatabase();
124
125 // connect to database
126 string strAccessConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.Combine(ws.SystemDirectory, @"apifiles/Data/transport.accdb");
127 OleDbConnection connection = null;
128 try
129 {
130 connection = new OleDbConnection(strAccessConn);
131 }
132 catch (Exception ex)
133 {
134 Console.WriteLine("Error: Failed to create a database connection. \n{0}", ex.Message);
135 Environment.Exit(1);
136 }
137
138 // read GAMS sets
139 ReadSet(connection, db, "SELECT Plant FROM Plant", "i", 1, "canning plants");
140 ReadSet(connection, db, "SELECT Market FROM Market", "j", 1, "markets");
141
142 // read GAMS parameters
143 ReadParameter(connection, db, "SELECT Plant,Capacity FROM Plant", "a", 1, "capacity of plant i in cases");
144 ReadParameter(connection, db, "SELECT Market,Demand FROM Market", "b", 1, "demand at market j in cases");
145 ReadParameter(connection, db, "SELECT Plant,Market,Distance FROM Distance", "d", 2, "distance in thousands of miles");
146
147 return db;
148 }
149
150 static void WriteVariable(OleDbConnection connect, GAMSDatabase db, string varName, params string[] Domains)
151 {
152 try
153 {
154 GAMSVariable var = db.GetVariable(varName);
155 if(Domains.Length != var.Dim)
156 {
157 Console.WriteLine("Number of column names does not match the dimension of the variable.");
158 Environment.Exit(1);
159 }
160
161 connect.Open();
162
163 // delete table varName if it exists already
164 OleDbCommand cmd = new OleDbCommand("drop table " + varName, connect);
165 try
166 {
167 cmd.ExecuteNonQuery();
168 }
169 catch
170 {
171 }
172
173 String query = "create table " + varName + "(";
174 foreach (string dom in Domains)
175 query += dom + " varchar(64), ";
176 query += "lvl double)";
177
178 cmd.CommandText = query;
179 cmd.ExecuteNonQuery();
180
181 foreach (GAMSVariableRecord rec in var)
182 {
183 query = "insert into " + varName + "(";
184 foreach (string dom in Domains)
185 query += dom + ", ";
186 query += "lvl) values (";
187 foreach (string key in rec.Keys)
188 query += "'" + key + "', ";
189 query += rec.Level + ")";
190 cmd.CommandText = query;
191 cmd.ExecuteNonQuery();
192 }
193 }
194 catch (Exception ex)
195 {
196 Console.WriteLine("Error: Failed to write variable to the database.\n{0}", ex.Message);
197 Environment.Exit(1);
198 }
199 finally
200 {
201 connect.Close();
202 }
203 }
204
205 static void WriteToAccess(GAMSWorkspace ws, GAMSDatabase db)
206 {
207 // connect to database
208 string strAccessConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.Combine(ws.SystemDirectory, @"apifiles/Data/transport.accdb");
209 OleDbConnection connection = null;
210 try
211 {
212 connection = new OleDbConnection(strAccessConn);
213 }
214 catch (Exception ex)
215 {
216 Console.WriteLine("Error: Failed to create a database connection. \n{0}", ex.Message);
217 Environment.Exit(1);
218 }
219
220 // write levels of variable x
221 WriteVariable(connection, db, "x", "i", "j");
222 }
223
224
225 static String GetModelText()
226 {
227 String model = @"
228 Sets
229 i canning plants
230 j markets
231
232 Parameters
233 a(i) capacity of plant i in cases
234 b(j) demand at market j in cases
235 d(i,j) distance in thousands of miles
236 Scalar f freight in dollars per case per thousand miles /90/;
237
238$if not set gdxincname $abort 'no include file name for data file provided'
239$gdxin %gdxincname%
240$load i j a b d
241$gdxin
242
243 Parameter c(i,j) transport cost in thousands of dollars per case ;
244
245 c(i,j) = f * d(i,j) / 1000 ;
246
247 Variables
248 x(i,j) shipment quantities in cases
249 z total transportation costs in thousands of dollars ;
250
251 Positive Variable x ;
252
253 Equations
254 cost define objective function
255 supply(i) observe supply limit at plant i
256 demand(j) satisfy demand at market j ;
257
258 cost .. z =e= sum((i,j), c(i,j)*x(i,j)) ;
259
260 supply(i) .. sum(j, x(i,j)) =l= a(i) ;
261
262 demand(j) .. sum(i, x(i,j)) =g= b(j) ;
263
264 Model transport /all/ ;
265
266 Solve transport using lp minimizing z ;
267
268 Display x.l, x.m ;
269";
270
271 return model;
272 }
273
274 }
275}
GAMS::GAMSDatabase::GetVariable
GAMSVariable GetVariable(string variableIdentifier)
GAMS::GAMSDatabase::AddSet
GAMSSet AddSet(string identifier, int dimension, string explanatoryText="", SetType setType=SetType.multi)
GAMS::GAMSDatabase::AddParameter
GAMSParameter AddParameter(string identifier, int dimension, string explanatoryText="")
GAMS::GAMSJob::Run
void Run(GAMSOptions gamsOptions=null, GAMSCheckpoint checkpoint=null, TextWriter output=null, Boolean createOutDB=true)
GAMS::GAMSOptions::Defines
Dictionary< string, string > Defines
GAMS::GAMSParameter::AddRecord
new GAMSParameterRecord AddRecord(params string[] keys)
GAMS::GAMSSet::AddRecord
new GAMSSetRecord AddRecord(params string[] keys)
GAMS::GAMSSymbolRecord::Key
string Key(int index)
GAMS::GAMSWorkspace::AddJobFromString
GAMSJob AddJobFromString(string gamsSource, GAMSCheckpoint checkpoint=null, string jobName=null)
GAMS::GAMSWorkspace::AddDatabase
GAMSDatabase AddDatabase(string databaseName=null, string inModelName=null)
GAMS::GAMSWorkspace::AddOptions
GAMSOptions AddOptions(GAMSOptions optFrom=null)
TransportSeq.Transport9
This is the 9th model in a series of tutorial examples. Here we show: How to fill a GAMSDatabase by r...
Definition: Transport9.cs:20