Solver Foundation in C#. Net
In order to solve the above described instance of a Transporation problem we only need a C# Console Application with a few lines of code. So start your IDE (SharpDevelop or Visual Studio [Express Edition will work either]) and create a new console application project. Then add a reference to the Microsoft.Solver.Foundation.dll under „References“. This DLL should be found after the successful installation of MSF in „C:\Program Files\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\“. As we are using Data (DataTables, DataSets) you also need to reference „System.Data“.
The agenda for the current article is the following:
- load the OML model into Solver Foundation Context
- retrieve data from our sample Access Database
- bind data to the model
- solve the problem
- print out minimized total result and the amount of flow of each arc
So here we go with the first section of our class.
using System;
using System.IO;
using System.Data;
using System.Data.OleDb;
using Microsoft.SolverFoundation.Services;
namespace GSharpBlog.Optimization.TP_Example
{
class Program
{
/// Gets results of a SQL query through a OLE-DB connection.
private static DataSet getDataFromDB(string connectionStr, string query)
{
DataSet ds = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbConnection conn = new OleDbConnection(connectionStr);
try {
conn.Open();
adapter.SelectCommand = new OleDbCommand(query, conn);
adapter.Fill(ds);
return ds;
}
catch (Exception ex){ throw ex; }
finally{ conn.Close(); }
}
First we state a helper method for retrieving data from our Access Database, that simply takes the Connection String and the SQL query and returns a DataSet. After that we can go on with the automatically generated Main() method in which all code is written for the sake of simplicity.
In the following lines of code we define the above stated OML model as string and read it into the Solver Foundation Context. Then the structure of the supply, demand and arcs table (or OD-Matrix) are defined as DataTables.
// Access (2007) DB ConnectionString
string connectionStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\tp.accdb";
// Transportation Problem as OML model
string strModel = @"Model[
Parameters[Sets,Source,Sink],
Parameters[Reals,Supply[Source],Demand[Sink],Cost[Source,Sink]],
Decisions[Reals[0,Infinity],flow[Source,Sink],TotalCost],
Constraints[
TotalCost == Sum[{i,Source},{j,Sink},Cost[i,j]*flow[i,j]],
Foreach[{i,Source}, Sum[{j,Sink},flow[i,j]]<=Supply[i]],
Foreach[{i,Source}, Sum[{j,Sink},flow[i,j]]>=Demand[j]]],
Goals[Minimize[TotalCost]] ]";
// Load OML-Model
SolverContext context = SolverContext.GetContext();
context.LoadModel(FileFormat.OML, new StringReader(strModel));
context.CurrentModel.Name = "Transportation Problem";
// Create Tables
// Supply table
DataTable pSupply = new DataTable();
pSupply.Columns.Add("SupplyNode",Type.GetType("System.String"));
pSupply.Columns.Add("Supply",Type.GetType("System.Int32"));
// Demand table
DataTable pDemand = new DataTable();
pDemand.Columns.Add("DemandNode",Type.GetType("System.String"));
pDemand.Columns.Add("Demand",Type.GetType("System.Int32"));
// OD-Matrix
DataTable pCost = new DataTable();
pCost.Columns.Add("SupplyNode",Type.GetType("System.String"));
pCost.Columns.Add("DemandNode",Type.GetType("System.String"));
pCost.Columns.Add("Cost",Type.GetType("System.Double"));
Then data is pulled out of our Access Database and loaded into the DataTables. Now we can use the helper method getDataFromDB().
//// Fill tables
// 1. Fill Supply
string query = String.Empty;
DataSet accessDS = new DataSet();
query = "SELECT SupplyNode, Supply FROM Supply ORDER BY SupplyNode";
accessDS = getDataFromDB(connectionStr, query);
foreach (DataRow row in accessDS.Tables[0].Rows) {
pSupply.Rows.Add(row[0].ToString(), row[1]);
}
// Clear
query = String.Empty;
accessDS.Clear();
// 2.Fill Demand
query = "SELECT DemandNode, Demand FROM Demand ORDER BY DemandNode";
accessDS = getDataFromDB(connectionStr, query);
foreach (DataRow row in accessDS.Tables[0].Rows) {
pDemand.Rows.Add(row[0].ToString(), row[1]);
}
// Clear
query = String.Empty;
accessDS.Clear();
// 3. Fill Arcs (or OD-Matrix)
query = "SELECT SupplyNode, DemandNode, Cost FROM Arcs ORDER BY SupplyNode";
accessDS = getDataFromDB(connectionStr, query);
foreach (DataRow row in accessDS.Tables[0].Rows) {
pCost.Rows.Add(row[0].ToString(), row[1].ToString(), row[2]);
}
Now we bind the data to the OML model that we have defined earlier. Microsoft Solver Foundation has an advanced binding mechanism that is pretty straight forward. With the following lines, we bind the data from our tables to the OML model: loop through the parameters in the model and simply call the method SetBinding([DataTable].AsEnumerable(), [ValueColumnName], [IDColumnName]) for each parameter.
// Bind values from tables to parameter of the OML model
foreach (Parameter p in context.CurrentModel.Parameters)
{
switch (p.Name)
{
case "Supply":
p.SetBinding(pSupply.AsEnumerable(), "Supply", "SupplyNode");
break;
case "Demand":
p.SetBinding(pDemand.AsEnumerable(), "Demand", "DemandNode");
break;
case "Cost":
p.SetBinding(pCost.AsEnumerable(), "Cost", "SupplyNode", "DemandNode");
break;
}
}
Then we just have to call the Solve() method and fetch the result. Now we loop through the decisions of the model, look for the decision „TotalCost“ and fetch the value of the optimized result.
// Call solver
solution = context.Solve();
// Fetch results: minimized total costs
Report report = solution.GetReport();
double cost = 0;
foreach (Decision desc in solution.Decisions) {
if (desc.Name == "TotalCost")
{
foreach (object[] value in desc.GetValues()) {
cost = Convert.ToDouble(value[0]);
}
}
}
Because we want to know not only the optimized total costs but also the amount of flow between each supply node and demand node we have to query the solution more detailed. We get information about the source/sink-pairs about the amount of flow from MSF – but we lack the distance (or cost) between each pair. So we need to lookup the distances in the original arcs table.
// Print out optimized results
string result = String.Empty;
double totalFlow = 0.0;
foreach (Decision desc in solution.Decisions) {
// flow as variable
if (desc.Name == "flow")
{
foreach (object[] value in desc.GetValues()) {
string source = value[1].ToString();
string sink = value[2].ToString();
double flow = Convert.ToDouble(value[0]);
// lookup km from arcs table
DataRow[] rows = new DataRow[1];
rows = pCost.Select("SupplyNode ='"+source+"' AND DemandNode ='"+sink+"'");
double km = Convert.ToDouble(rows[0]["Cost"]);
string sourceSink = String.Format("{0}_{1}", source, sink);
if(flow != 0)
{
totalFlow += flow;
result = result + "\n" + String.Format("\"{0}\";\"{1}\";\"{2}\";{3};{4}",
sourceSink, source, sink, flow, km);
}
}
Console.WriteLine(result);
}
}
The optimized result is 46. Our solver application prints out the following results.
Total result: 46
**********************
„S1_D1″;“S1″;“D1“;5;3
„S2_D1″;“S2″;“D1“;2;4
„S2_D2″;“S2″;“D2“;3;2
„S2_D3″;“S2″;“D3“;2;4
„S3_D3″;“S3″;“D3“;3;3