当前位置：文江博客话题详情

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

发布于 2024-09-19 09:31:58 字数 190 浏览 6 评论 0原文

我需要从 ac# 类中执行 sql 查询。我想到了 2 个选项

启动 sqlcmd 进程。
使用 SqlCommand 对象。

我的问题是哪种方法更好？重要的是，该解决方案只能在短时间内保持与服务器的连接。

如果上述想法不好，我愿意接受其他想法。

提前致谢。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

少女七分熟 2024-09-26 09:31:58

使用 SqlCommand。此代码只会在很短的时间内保持连接处于活动状态（只要您的查询是高性能的）：

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);

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):

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);

回复收藏 0 原文

世态炎凉 2024-09-26 09:31:58

来自 MSDN：

以下示例创建一个SqlConnection、SqlCommand 和 SqlDataReader。该示例读取数据，并将其写入控制台。最后，该示例在退出Using 代码块时关闭SqlDataReader，然后关闭SqlConnection。

using System.Data.SqlClient

...

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]));
            }
        }
        catch (SqlException e)
        {
            Console.WriteLine(e.StackTrace);
            Console.WriteLine(e.Message);
        }
        finally
        {
            // Always call Close when done reading.
            reader.Close();
        }
    }
}

From MSDN:

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.

using System.Data.SqlClient

...

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]));
            }
        }
        catch (SqlException e)
        {
            Console.WriteLine(e.StackTrace);
            Console.WriteLine(e.Message);
        }
        finally
        {
            // Always call Close when done reading.
            reader.Close();
        }
    }
}

回复收藏 0 原文

兮颜 2024-09-26 09:31:58

这取决于。如果您不关心查询分叉使用 sqlcmd 的进程的结果可能没问题。另一方面，如果您需要控制结果，那么最好使用 ADO.NET。为了避免长时间保持连接打开状态，请确保禁用 ADO.NET 连接池，通过将 Pooling=false 添加到连接字符串中：

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();
}

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 ADO.NET connection pooling by adding Pooling=false to your connection string:

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();
}

回复收藏 0 原文