blocks|key|297290|text|使用SqlCommand。此代码将仅在很短的一段时间内保持连接活动(只要您的查询是可执行的)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|297291|DataTable+results+=+new+DataTable();

using(SqlConnection+conn+=+new+SqlConnection(connString))
++++using(SqlCommand+command+=+new+SqlCommand(query,+conn))
++++++++using+(SqlDataAdapter+dataAdapter+=+new+SqlDataAdapter(command))
+++++++++++dataAdapter.Fill(results);|code-block|syntax|javascript|297292|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Use a SqlCommand. This code will only keep the connection alive for a very short period of time (as long as your query is performant):

<pre><code>DataTable results = new DataTable();

using(SqlConnection conn = new SqlConnection(connString))
 using(SqlCommand command = new SqlCommand(query, conn))
 using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
 dataAdapter.Fill(results);
</code></pre>

blocks|key|3236074|text|我认为SqlCommand是一个明显的赢家，因为您不需要连接不同的进程。一旦完成，您就可以关闭数据库连接。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3236075|然后，您还可以将应用程序分发到没有sqlcmd可用的计算机。|offset|length|style|CODE|3236076|entityMap^0|0|H|6|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

I think SqlCommand is a clear winner because you don't need to wire up a different process. You can close the database connection as soon as you're done with it.

And then you can also distribute the application to machines that don't have <code>sqlcmd</code> available.

blocks|key|3236186|text|那得看情况。如果您不关心查询的结果，派生一个使用sqlcmd的进程可能是可以的。另一方面，如果你需要控制结果，最好使用ADO.NET。为了避免长时间保持连接，请确保通过在连接字符串中添加Pooling=false来禁用ADO.NET+connection+pooling：|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|CODE|entityRanges|data|3236187|using+(var+conn+=+new+SqlConnection("Data+Source=server;Initial+Catalog=somedb;User+Id=foo;Password=secret;Pooling=false"))
using+(var+cmd+=+conn.CreateCommand())
{
++++conn.Open();
++++cmd.CommandText+=+"DELETE+FROM+foo";
++++var+result+=+cmd.ExecuteNonQuery();
}|code-block|syntax|javascript|3236188|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/8xx3tyca(v=VS.100).aspx^0|2L|D|2L|D|31|Q|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@$9|U|A|V|B|C]|$9|W|A|X|B|D]]|E|@$9|Y|A|Z|1|10]]|F|$]]|$1|G|3|H|5|I|7|11|8|@]|E|@]|F|$J|K]]|$1|L|3|-4|5|6|7|12|8|@]|E|@]|F|$]]]|M|$N|$5|O|P|Q|F|$R|S]]]]

It depends. If you don't care about the result of the query forking a process which uses sqlcmd might be OK. If on the other hand you need to control the results it would be better to use ADO.NET. To avoid keeping the connection open for a long time make sure you disable <a href="http://msdn.microsoft.com/en-us/library/8xx3tyca(v=VS.100).aspx" rel="nofollow noreferrer">ADO.NET connection pooling</a> by adding <code>Pooling=false</code> to your connection string:

<pre><code>using (var conn = new SqlConnection("Data Source=server;Initial Catalog=somedb;User Id=foo;Password=secret;Pooling=false"))
using (var cmd = conn.CreateCommand())
{
 conn.Open();
 cmd.CommandText = "DELETE FROM foo";
 var result = cmd.ExecuteNonQuery();
}
</code></pre>

blocks|key|3021648|text|来自MSDN:|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3021649|下面的示例创建一个SqlConnection、一个SqlCommand和一个SqlDataReader。该示例读取数据，并将其写入控制台。最后，该示例在退出Using代码块时先关闭SqlDataReader，然后关闭SqlConnection。|3021650|private+static+void+ReadOrderData(string+connectionString)
{
++++string+queryString+=+
++++++++"SELECT+OrderID,+CustomerID+FROM+dbo.Orders;";
++++using+(SqlConnection+connection+=+new+SqlConnection(
+++++++++++++++connectionString))
++++{
++++++++SqlCommand+command+=+new+SqlCommand(
++++++++++++queryString,+connection);
++++++++connection.Open();
++++++++SqlDataReader+reader+=+command.ExecuteReader();
++++++++try
++++++++{
++++++++++++while+(reader.Read())
++++++++++++{
++++++++++++++++Console.WriteLine(String.Format("{0},+{1}",
++++++++++++++++++++reader[0],+reader[1]));
++++++++++++}
++++++++}
++++++++finally
++++++++{
++++++++++++//+Always+call+Close+when+done+reading.
++++++++++++reader.Close();
++++++++}
++++}
}|code-block|syntax|javascript|3021651|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx^0|2|5|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

From <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx" rel="noreferrer">MSDN:</a>

The following example creates a SqlConnection, a SqlCommand, and a SqlDataReader. The example reads through the data, writing it to the console. Finally, the example closes the SqlDataReader and then the SqlConnection as it exits the Using code blocks.

<pre><code>private static void ReadOrderData(string connectionString)
{
 string queryString = 
 "SELECT OrderID, CustomerID FROM dbo.Orders;";
 using (SqlConnection connection = new SqlConnection(
 connectionString))
 {
 SqlCommand command = new SqlCommand(
 queryString, connection);
 connection.Open();
 SqlDataReader reader = command.ExecuteReader();
 try
 {
 while (reader.Read())
 {
 Console.WriteLine(String.Format("{0}, {1}",
 reader[0], reader[1]));
 }
 }
 finally
 {
 // Always call Close when done reading.
 reader.Close();
 }
 }
}
</code></pre>

blocks|key|299363|text|我认为SqlCommand是一个好主意，但请记住，这个类只有在连接到SQL+Server时才可用。如果您正在处理Oracle或到其他数据库的OleDb连接，那么您将需要一些其他类型的命令/连接类。所有的数据命令对象都继承自DbCommand，因此我将对此进行研究。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|299364|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/system.data.common.dbcommand.aspx^0|33|9|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

I think SqlCommand is a good idea, but keep in mind this class is only available when connecting to SQL Server. You'll need some other type of Command/Connection classes if you're dealing with Oracle, or an OleDb connection to some other database. All of the data command objects inherit from <a href="http://msdn.microsoft.com/en-us/library/system.data.common.dbcommand.aspx" rel="nofollow noreferrer">DbCommand</a>, so I would read up on that.

I need to execute a sql query from within a c# class. I have thought of 2 options

<ol>
<li>Starting a process of sqlcmd.</li>
<li>Using a SqlCommand object.</li>
</ol>

My question is which would be the better way? It's important that the solution only holds a connection to the server for a short time.

I'm open to other ideas if the above aren't good.

Thanks in advance.

SQL/C# - Best method for executing a query

SQL Server

数据库

服务器

我需要在c#类中执行sql查询。我想了两个选择使用SqlCommand对象启动sqlcmd的进程。。我的问题是哪种方式更好？重要的是，该解决方案只在短时间内保持与服务器的连接。如果上面的想法不好，我对其他想法持开放态度。提前谢谢。

问SQL/C# -执行查询的最佳方法
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL/C# -执行查询的最佳方法EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL/C# -执行查询的最佳方法
EN