CODEWITHSUNDEEP
c#mysql
Kasper Juner
Kasper Juner

Reputation: 952

How to altertable if not exist in c#?

I have an API in .NET Core 3.1 which insert some data to my databases, the issue is that i had to add a new column to the column where i'm inserting the data, as then the api is called i'm dynamically connect to the database to which i should do the insert, how could i make an altertable if that column not exist and then insert the new item?

Here is the method where i'm inserting the stuff:

public static IActionResult InsertOrder(string piva, string idNegozio, RiepilogoTakeaway ordine, HttpResponse Response) {
            string connectionString = getConnectionString(piva);
            var query_ordine = @"INSERT INTO `ordini` (`TIPO_OR`, `TAVOLO_OR`, `ORA_OR`, `SENT_OR`, `ID_OR_CFG`, `LOTTERIA_OR`) VALUES (@tipo, @tavolo, NOW(), 0, @id, @lotteria); SELECT LAST_INSERT_ID();";

            using var connection = new MySqlConnection(connectionString);
            using var cmd = new MySqlCommand(query_ordine, connection);
            cmd.Parameters.AddWithValue("@tipo", "STB");
            cmd.Parameters.AddWithValue("@tavolo", 0);
            cmd.Parameters.AddWithValue("@id", idNegozio);
            cmd.Parameters.AddWithValue("@lotteria", ordine.lotteria);
            connection.Open();
            cmd.Prepare();

            string idOrdine = cmd.ExecuteScalar().ToString();

            ...
}

And that's the alter table i should execute:

ALTER TABLE `ordini`
ADD COLUMN `LOTTERIA_OT` VARCHAR(10) NULL AFTER `ID_OR_CFG`;

Which would be the best way to do it?

Upvotes: 0

Views: 51

Answers (1)

user3746240
user3746240

Reputation: 341

Try to deploy the SQL changes before calling it. It's very likely that you'll need higher level of privilege in the database to run the alter or add column, for example, if the SQL user you are using to insert the order doesn't have permissions to change the DB schema. However, if that is still an option, you will need to execute the SQL schema changeusing command.ExecuteNonQuery(). Possibly worth creating a new method "PrepareSqlTable" so you can call before your query runs. Bear in mind that will impact your application performance/scalability because now we'll be checking that on every single query. Another alternative, handle this in the catch by looking into the error message and creating it accordingly (provides more performance benefits than the previous option).

Upvotes: 1

Related Questions