1 Replies - 547 Views - Last Post: 30 January 2015 - 09:27 AM Rate Topic: -----

#1 methuselah90   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 15-December 14

Use the results of an SQL query to create another query in C#

Posted 30 January 2015 - 03:14 AM

I am working on a bidding/allocation system using C# and MySQL, and I am currently having difficulty implementing the “post-allocation” mechanics of the system. Let me explain how it works, before introducing the code:

    STEP 1
  • When the bids are made on the system, they all have the position 0, so the first thing the system does is order them according to priority.

    Posted Image
    (bid table)

    In this case, 5 bidders are interested in what is in plot 15, but we have not assigned them a position yet.

    Posted Image
    (bid table)

    The system then organises the bids according to the order in which they are going to processed.


    STEP 2
  • The bids are then allocated, but unfortunately there are only 3 spaces in plot 15 that can be allocated. As a result, we use the priority listing to determine which bidder gets what.

    Posted Image
    (booking table)


    STEP 3
  • At this stage, I can say that I am stuck, there are two things that I want to do, both of which involve reusing the results of the query at step 2.

    Posted Image
    (bid table)

    - The bidders that have their job allocated should see: their bid.status updated from Queued to Allocated, the bid.position should be set to 0, and their job_id should be set to the booking they have been allocated (i.e. booking.id).
    - The bidders that have not had their jobs allocated should see: their bid.status remain as Queued, and their bid.position should be set to 0 in preparation for the method to run again


The difficulty I am having is in stage 3, how do I determine which jobs have been allocated and which ones have not, and then run the necessary SQL queries in order to make the updates?

My code so far is as follows:

// STEP 1a - SELECT BIDS

string query =
    "SELECT t1.operator_id, t1.datetime, t1.plot_id, t1.position, t2.market_access FROM bid t1 " +
    "JOIN operator t2 ON t1.operator_id = t2.id WHERE t1.status='Queued' AND [email protected] " +
    "ORDER BY t2.market_access ASC, t1.datetime ASC";
var bidList = new List<BidList>();
var cmd = new MySqlCommand(query, _connection);
cmd.Parameters.AddWithValue(("@postcode"), _plot);
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
    var item = new BidList
    {
        OperatorId = dataReader["operator_id"] + "",
        PlotId = dataReader["plot_id"] + "", 
        Position = dataReader["position"] + "",
        Datetime = dataReader["datetime"] + "",
        MarketAccess = dataReader["market_access"] + "",
    };
    bidList.Add(item);
}
dataReader.Close();

// STEP 1b - SET PRIORITIES

for (var i = 0; i < bidList.Count; i++)
{
    var position = i + 1;
    bidList[i].Position = position.ToString();
    query = "UPDATE bid SET [email protected] WHERE status='Queued' AND [email protected] AND [email protected]_id;";
    cmd = new MySqlCommand(query, _connection);
    cmd.Parameters.AddWithValue(("@position"), position);
    cmd.Parameters.AddWithValue(("@postcode"), _plot);
    cmd.Parameters.AddWithValue(("@operator_id"), bidList[i].OperatorId);
    cmd.ExecuteNonQuery();
}
dataReader.Close();

// STEP 2 - ALLOCATE JOBS ACCORDING TO PRIORITY    

foreach (var t in bidList)
{
    query = "SELECT operator_id, plot_id, status FROM booking " +
        "WHERE status='open' AND [email protected] AND operator_id='0'" +
        "ORDER BY datetime ASC;" +
        "UPDATE booking SET [email protected]_id, status='Allocated' " +
        "WHERE ([email protected]_id AND operator_id='0' AND status='Open') LIMIT 1;";
    cmd = new MySqlCommand(query, _connection);
    cmd.Parameters.AddWithValue(("@operator_id"), t.OperatorId);
    cmd.Parameters.AddWithValue(("@postcode"), _plot);
    cmd.Parameters.AddWithValue(("@plot_id"), t.PlotId);
    cmd.ExecuteNonQuery();
}
dataReader.Close();

// STEP 3

// ???

CloseConnection();



Is This A Good Question/Topic? 0
  • +

Replies To: Use the results of an SQL query to create another query in C#

#2 ybadragon   User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: Use the results of an SQL query to create another query in C#

Posted 30 January 2015 - 09:27 AM

It would be a simple update query then. you would update the bid.status to "Allocated" where the operator_id is in a list of id's that got the plots, then update the bid.position to 0 where the operator_id is in a list of id's that didn't get the plots. If you were using Database Context's this would be much easier as you could simply update the objects that are returned, and then use DBContext.Submit()

IN operator for SQL
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1