SQL查询结果断言

Question

我整理了以下方法：

public static ArrayList DbQueryToArry()

        {

            string SqlCString = "connString";

            SqlConnection connection = null;

            ArrayList valuesList = new ArrayList();

            connection = new SqlConnection(SqlCString);

            connection.Open();

            SqlCommand command = new SqlCommand("Select CLIENTNO, ACCOUNT_Purpose from audit.ACCOUNTS_AUDIT", connection);

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())

            {

                valuesList.Add(Convert.ToString(reader[0]));

            }

            return valuesList;

        }

我希望能够运行这样的断言：

var a = DbQueryToArry();         

Assert.IsTrue(a.Contains("some value"));

给定读者 [0]

valuesList.Add(Convert.ToString(reader[0]));

我只将第一列 ( CLIENTINFO) 放入数组中，而不是第二列 (ACCOUNT_Purpose)。我应该如何修改代码以获得两者？

此外，返回值可以是 String 或 Int，那么我当前的代码版本是否应该同时处理这两者？

提前致谢。

Answer 1

如果我们从过时 ArrayList的切换到类似的东西IEnumerable<T>：public static IEnumerable<IDataRecord> DbQueryToArray(string sql) {&nbsp; if (null == sql)&nbsp; &nbsp; throw new ArgumentNullException(nameof(sql));&nbsp; //TODO: do not hardcode connetcion string but read it (say, from Settings)&nbsp; string SqlCString = "connString";&nbsp; //DONE: Wrap IDisposable into using&nbsp; using (SqlConnection connection = new SqlConnection(SqlCString)) {&nbsp; &nbsp; connection.Open();&nbsp; &nbsp; //DONE: Wrap IDisposable into using&nbsp; &nbsp; using (SqlCommand command = new SqlCommand(sql, connection)) {&nbsp; &nbsp; &nbsp; //DONE: Wrap IDisposable into using&nbsp; &nbsp; &nbsp; using (SqlDataReader reader = command.ExecuteReader()) {&nbsp; &nbsp; &nbsp; &nbsp; while (reader.Read()) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; yield return reader as IDataRecord;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; }}那么您可以使用Linq来查询result：&nbsp;var a = DbQueryToArray("Select CLIENTNO, ACCOUNT_Purpose from audit.ACCOUNTS_AUDIT");&nbsp;Assert.IsTrue(a.Any(record =>&nbsp;&nbsp; &nbsp;Convert.ToString(record["CLIENTNO"]) == "some value"));&nbsp;&nbsp;Assert.IsTrue(a.Any(record =>&nbsp;&nbsp; &nbsp;Convert.ToString(record["ACCOUNT_Purpose"]) == "some other value"));&nbsp;如果你不想多次执行查询，你可以具体化结果：&nbsp;var a = DbQueryToArray("Select CLIENTNO, ACCOUNT_Purpose from audit.ACCOUNTS_AUDIT")&nbsp; &nbsp;.ToList();&nbsp;Assert.IsTrue(a.Any(record => Convert.ToString(record[0]) == "some value"));&nbsp;&nbsp;Assert.IsTrue(a.Any(record => Convert.ToString(record[1]) == "some other value"));最后（见下面的评论），如果我们想测试任何记录中的任何字段是否具有该值：&nbsp; var a = DbQueryToArray("Select CLIENTNO, ACCOUNT_Purpose from audit.ACCOUNTS_AUDIT")&nbsp; &nbsp; .SelectMany(line => {&nbsp; &nbsp; &nbsp; // Flatten the cursor into IEnumerable<String>&nbsp; &nbsp; &nbsp; string[] result = new string[line.FieldCount];&nbsp; &nbsp; &nbsp; for (int i = 0; i < result.Length; ++i)&nbsp; &nbsp; &nbsp; &nbsp; result[i] = Convert.ToString(line[i]);&nbsp; &nbsp; &nbsp; return result;&nbsp; &nbsp; });&nbsp; a.Any(item => item == "some value");

Answer 2

这是因为你只读了读者的第一个值。Reader.Read()逐行读取每一行，这Convert.ToString(reader[0]))意味着您要将第一列作为字符串读取。

Answer 3

使用DataTableandSqlDataAdapter以表格形式获取查询结果。是这样的：string connString = @"your connection string here";string query = "select * from table";DataTable dataTable = new DataTable();SqlConnection conn = new SqlConnection(connString);&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;SqlCommand cmd = new SqlCommand(query, conn);conn.Open();// create data adapterSqlDataAdapter da = new SqlDataAdapter(cmd);// this will query your database and return the result to your datatableda.Fill(dataTable);conn.Close();da.Dispose();然后您可以使用dataTable对象来查看特定值是否存在。

Answer 4

最佳做法是首先检查阅读器是否有行reader.HasRows然后关闭阅读器和连接你的代码应该是这样的：public static ArrayList DbQueryToArry()&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; string SqlCString = "connString";&nbsp; &nbsp; &nbsp; &nbsp; SqlConnection connection = null;&nbsp; &nbsp; &nbsp; &nbsp; ArrayList valuesList = new ArrayList();&nbsp; &nbsp; &nbsp; &nbsp; connection = new SqlConnection(SqlCString);&nbsp; &nbsp; &nbsp; &nbsp; using (connection)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; connection.Open();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SqlCommand command = new SqlCommand("Select CLIENTNO, ACCOUNT_Purpose from audit.ACCOUNTS_AUDIT", connection);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SqlDataReader reader = command.ExecuteReader();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (reader.HasRows)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; while (reader.Read())&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; valuesList.Add(Convert.ToString(reader[0]));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; valuesList.Add(Convert.ToString(reader[1])); // add to valuelist&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; reader.Close(); // close reader&nbsp; &nbsp; &nbsp; &nbsp; } //dispose connection&nbsp; &nbsp; &nbsp; &nbsp; return valuesList;&nbsp; &nbsp; }