不要关闭SQL连接，直到它完成读取

本文关键字：读取 SQL 连接 | 更新日期: 2023-09-27 18:10:57

我有下面的代码，它工作，但只读取DB的顶部行，然后终止。数组应该包含3个数据，但是它只保存了一个。

我想这是因为它没有循环。

你怎么说，代码继续运行，直到它没有更多的数据读取?

SqlConnection conn1 = new SqlConnection(ssConnectionString);
conn1.Open();
SqlCommand command1 = conn1.CreateCommand();
command1.CommandText = "SELECT FeedURL FROM [dbo].[Feeds]";
rssFeeds.Add(command1.ExecuteScalar());
conn1.Close();

不要关闭SQL连接，直到它完成读取

默认情况下，ExecuteScalar()只返回一个值。您需要创建一个DataReader，然后使用command1.ExecuteReader()

循环遍历结果
conn1.Open();
string query = "select feedurl from [dbo].[feeds]";
DataSet DS = new DataSet();
SqlDataAdapter adapt = new SqlDataAdapter(query,conn1);
adapt.Fill(DS);
if (DS != null)
{
     if (DS.Tables[0].rows.Count > 0 )
    {
        foreach(DataRow DR in DS.Tables[0].Rows)
        {
            string temp = DR['columnname'];
        }
    }
{

您可以使用ExecuteReader来解决您的问题。在这个例子中，我从MSDN使用using语句消费连接，因为SqlConnection类有一些非托管资源。如果你有更多关于使用和终结器的问题，也可以点击这里。

如何使用ExecuteReader，你可以在这里查看:

static void HasRows(SqlConnection connection)
{
    using (connection)
    {
        SqlCommand command = new SqlCommand(
          "SELECT CategoryID, CategoryName FROM Categories;",
          connection);
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("{0}'t{1}", reader.GetInt32(0),
                    reader.GetString(1));
            }
        }
        else
        {
            Console.WriteLine("No rows found.");
        }
        reader.Close();
    }
}

试试这个:

//method to make this code reusable
//the returned data set is accessible even if the underlying connection is closed
//NB: that means data's held in memory; so beware of your resource usage
    public DataSet ExecuteSQLToDataSet(string ssConnectionString, string query, string name)
    {
        DataSet ds = new DataSet("Tables");
        using (SqlConnection conn1 = new SqlConnection(ssConnectionString))
        {
            conn1.Open();
            SqlDataAdapter sda = new SqlDataAdapter(query, objConn);
            sda.FillSchema(ds, SchemaType.Source, name);
            sda.Fill(ds, name);
        } //using statement will close and dispose the connection for you
        return ds;
    }

//example usage
    DataSet ds = ExecuteSQLToDataSet(ssConnectionString, "SELECT FeedURL FROM [dbo].[Feeds]", "Feeds"); //nb: name doesn't have to match table name; you can call it what you like; naming is useful if you wanted to add other result sets to the same data set
    //DataTable tblFeeds = ds.Tables["Feeds"]; //if you want to access the above query by name
    foreach (DataTable tbl in ds.Tables)
    {
        foreach (DataRow dr in tbl.Rows) //tblFeeds.Rows if you did that instead of looping through all tables
        {
            //Console.WriteLine(dr["FeedURL"].ToString()); //you can specify a named column
            Console.WriteLine(dr[0].ToString()); //or just use the index
        }
    }
    Console.WriteLine("Done");
    Console.ReadLine();

详细信息:http://support.microsoft.com/kb/314145

相关文章：