SqlDataReader 读入 List<string>

SqlDataReader to read into Listlt;stringgt;(SqlDataReader 读入 Listlt;stringgt;)

问题描述

我正在用 C# 编写一个方法来从 WCF 服务查询 SQL Server Express 数据库.我必须使用 ADO.NET 来执行此操作(然后稍后使用 LINQ 重写它).

I am writing a method in C# to query a SQL Server Express database from a WCF service. I have to use ADO.NET to do this (then rewrite it with LINQ later on).

该方法接受两个字符串(fname, lname)，然后从匹配的记录中返回一个Health Insurance NO"属性.我想把它读入一个列表(还有一些其他的属性要检索).

The method takes two strings (fname, lname) then returns a "Health Insurance NO" attribute from the matching record. I want to read this into a list (there are some other attribs to retrieve as well).

当前代码返回一个空列表.我哪里错了?

The current code returns an empty list. Where am I going wrong?

public List<string> GetPatientInfo(string fname, string lname)
{
    string connString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\xxxx\Documents\Visual Studio 2010\Projects\ADOWebApp\ADOWebApp\App_Data\ADODatabase.mdf;Integrated Security=True;User Instance=True";

    SqlConnection conn = new SqlConnection(connString);

    string sqlquery = "SELECT Patient.* FROM Patient WHERE ([First Name] = '"+fname+"') AND ([Last Name] = '"+lname+"')";
    SqlCommand command = new SqlCommand(sqlquery, conn);
    DataTable dt = new DataTable();

    List<string> result = new List<string>();

    using (conn)
    {
        conn.Open();

        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader != null && reader.Read())
            {
               dt.Load(reader);
               result.Add(Convert.ToString(reader["Health Insurance NO"]));
            }
        }
     }

     return result;
}

推荐答案

您正试图通过 DataTable.Load >循环加载一个 DataTable.你只需要一次.您还在循环中使用 reader.Read() .SqlDataReader.Read()将阅读器推进到下一条记录而不使用它.如果你打算使用 DataTable.Load 你不需要先阅读阅读器.所以你只需要完全删除循环来加载表格.

You are trying to load a DataTable via DataTable.Load >in a loop<. You just need that once. You're also using reader.Read() in the loop. SqlDataReader.Read() advances the reader to the next record without to consume it. If you're going to use DataTable.Load you don't need to read the reader first. So you just have to remove the loop completely to load the table.

但既然你想返回一个列表，你根本不需要 DataTable，只需循环阅读器:

But since you want to return a list you don't need the DataTable at all, just loop the reader:

List<string> result = new List<string>();
using (conn)
{
    conn.Open();
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while(reader.Read())
        {
            result.Add(Convert.ToString(reader["Health Insurance NO"]));
        }
    }
}

除此之外，您可以在没有 sql 参数的情况下进行 sql 注入.

Apart from that, you are open for sql-injection without sql-parameters.

这篇关于SqlDataReader 读入 List&lt;string&gt;的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！