英文:
How can I run multiple SQL commands in one batch using C#?
问题
我有相当多的SQL命令需要运行。我把它们放在一个列表中，然后遍历列表运行SQL命令，然后运行下一个。问题在于，在每次运行时，连接都会打开然后关闭，因此如果有50个命令，就会有50次打开和关闭连接。
我有的命令类型包括创建函数、表等。
在C#中有没有简单的方法可以做到这一点？
英文:
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#?
答案1
得分: 2
打开和关闭连接是廉价的。ADO池化连接。它会将连接保持打开，以供下一个语句使用。所以当你调用 open() 时，你获得对连接的独占使用权，但 close() 只是将其返回到池中。
如果你运行多个语句，更要注意错误处理等事项。你可以在一个 SQL 语句块中运行多个语句，在打开的连接上连续运行多个 SQL 语句块。但如果一个语句失败了，你是否继续执行下一个语句，或者回滚之前的语句，以及你想在 C# 中还是在 SQL 中管理这个问题。还有哪些语句（如果有的话）会返回结果？这些因素应该决定你在何处拆分你的 SQL。
在应用程序代码中创建表格是相当不寻常的。你是否看过数据库项目？
英文:
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 ?
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论