How To Pass Lists of data from C# to MySQL Stored Procedures


Problem :

I am writing a MySQL stored procedure that is called from a C# MVC project, that will take in a number of parameters to make a hotel reservation.

The problem I've come across is that for MySQL Stored Procedures what is the best approach to pass lists of objects.

One idea I had was before calling the stored procedure, I could pre-save the required data in some temp tables, and use a unique key that can be called in the Stored Procedure making the hotel reservation where it can grabs all the data from temp tables.

You might ask, why don't I just call multiple stored procedures, but for integrity purposes, I will be using MySQL Transactions.



Solution :

You might ask, why don't I just call multiple stored procedures, but for integrity purposes, I will be using MySQL Transactions.

That's an interesting reason. Consider the following code:

using (MySqlConnection c = new MySqlConnection("cstring"))
{
    c.Open();
    var t = c.BeginTransaction();

    try
    {
        foreach (var o in list)
        {
            using (MySqlCommand cmd = new MySqlCommand("EXECUTE sp @field1, @field2", c, t))
            {
                cmd.Parameters.AddWithValue("@field1", o.field1);
                cmd.Parameters.AddWithValue("@field2", o.field2);

                cmd.ExecuteNonQuery();
            }
        }

        t.Commit();
    }
    catch (Exception ex)
    {
        t.Rollback();
        // do something with ex
    }
}

that is one contiguous transaction with multiple trips to the database. Though I don't fully understand your application, load, or requirements, it seems to me that if you simply do something like this it will work just fine for your needs. The only time the number of round-trips start to matter is when you're dealing with millions of transactions.


Mysql Tutorials

Mysql Howto..

Basic MySQL question: how to get rows from one table that are not in another table?

How to reverse the functioning of regexp matching in mysql? [closed]

How to simplify embedded mysql query into a JOIN?

How do I install Zend on my website and how do I test if it works?

How to store 'query-able' encrypted data in MySQL?

How to access MySQL from a remote computer (not localhost)?

How to select from two tables in MySQL even if not all rows in one table have corespondents in the other?

How to display special characters from MySQL database to html?

How do I account for missing xPaths and keep my data uniform when scraping a website using DOMXPath query method?

How do I add the first of the week for every week from 2011-01-02 until 2100-01-01 to a mysql table via a while loop?

Pagination: how to know where a comment of page belongs

How to make xml file from php and mysql

How to print out a table from mySQL database inside a html/php page

mysql---how to design the guestbook database?

How to count rows in one table based on another table in mysql 2

how to change CSS styles dynamically using php/Mysql..i want to create themes that are unique to each user?

How to save a rank column in Mysql

How do i select distinct values from multiple columns in SQL?

How to copy data from one table to another new table in MySQL?

How to renumber primary index

How I can Import data from CSV to MySQL?

How to use mysqli_query() in PHP?

How to substring a MySQL table column

Mysql: How to retrieve data on weekly basis

how to select date as an integer in mysql [duplicate]

How to connect mvc with mysql?

how to protect access to my company website to only employees [closed]

how do i use mysql connector with godady account?

How to return mysql data using table aliases with left join and duplicate columns

How to store field Id in a row?