-1

I have quite a few SQL commands that I need to run. I have them in a list and I traverse the list run the SQL command, and then run the next. The issue here is that on every run, the connection is opened and then closed so if there were 50 commands there would be 50 open and close.

The type of command I have are creating functions, tables etc.

Is there an easy way I can do this in C#?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Angela
  • 53
  • 7
  • 1
    One of many possible ways are stored procedures ~ they are defined on SQL server you just call it once from C# code. However I would not recommend this approach as in these days there is EntityFramework and instead of SQL queries you can write just C# code that will do that. – Tatranskymedved May 26 '23 at 04:25
  • 1
    Last, but not least - you do not need to close connection to the SQL server after each command, rather open it once, call all the necessary commands and then close the connection. – Tatranskymedved May 26 '23 at 04:26
  • 1
    I think the second approach is feasible, but its in a foreach loop so i will need to know when the last statement is and then close connection ? – Angela May 26 '23 at 04:36
  • Yes, exactly that. It might be similar to this: https://stackoverflow.com/a/13677409/7167572 – Tatranskymedved May 26 '23 at 04:42
  • Show your code. Then refactor it to allow the caller to control the lifetime of the connection. Then consider concatenating the sql command text too. – Jeremy Lakeman May 26 '23 at 05:00
  • Please post an [mcve] of the problem you are having. – Fildor May 26 '23 at 08:27
  • It seems that you're creating an issue out of something that isn't an issue. What issue have you faced such that you believe that opening/closing a connection 50 times is a problem? Are you using Windows 3.11? – Tu deschizi eu inchid May 26 '23 at 15:32

1 Answers1

2

Opening and closing connections is cheap. ADO pools connections. It keeps them open for the next statement that comes along. So when you call open(), you get exclusive use of the connection, but close() just returns it to the pool.

If you're running multiple statements, concentrate more on things like error handling. You can run multiple statements in one block of SQL, and multiple blocks of SQL one after the other on an open connection. But if one statement fails, do you go on to the next one, or roll back the previous ones, and do you want to manage this in C# or in the SQL. And which statements, if any, return results? These are the factors that should determine where you split your SQL.

It's pretty unusual to create tables in your application code. Have you looked at DB projects ?

bbsimonbb
  • 27,056
  • 15
  • 80
  • 110