SQL query result assertion

Question

I have put together the following method:

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

I'd like to be able to run an assertion like this one:

var a = DbQueryToArry();         
Assert.IsTrue(a.Contains("some value"));

Given reader [0]

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

I only get the first column (CLIENTINFO) into the array and not the second (ACCOUNT_Purpose). How should I modify the code to get both ?

In addition, the returned values could be either a String or Int so would my current code version should be handling both ?

Thanks in advance.

Answer 1

If we switch from obsolete ArrayList to something like IEnumerable<T>:

public static IEnumerable<IDataRecord> DbQueryToArray(string sql) {
  if (null == sql)
    throw new ArgumentNullException(nameof(sql));

  //TODO: do not hardcode connetcion string but read it (say, from Settings)
  string SqlCString = "connString";

  //DONE: Wrap IDisposable into using
  using (SqlConnection connection = new SqlConnection(SqlCString)) {
    connection.Open();

    //DONE: Wrap IDisposable into using
    using (SqlCommand command = new SqlCommand(sql, connection)) {
      //DONE: Wrap IDisposable into using
      using (SqlDataReader reader = command.ExecuteReader()) {
        while (reader.Read()) {
          yield return reader as IDataRecord;
        }
      }
    }
  }
}

then you can use Linq in order to query the result:

 var a = DbQueryToArray("Select CLIENTNO, ACCOUNT_Purpose from audit.ACCOUNTS_AUDIT");

 Assert.IsTrue(a.Any(record => 
   Convert.ToString(record["CLIENTNO"]) == "some value")); 

 Assert.IsTrue(a.Any(record => 
   Convert.ToString(record["ACCOUNT_Purpose"]) == "some other value")); 

If you don't want execute query several times, you can materialize the results:

 var a = DbQueryToArray("Select CLIENTNO, ACCOUNT_Purpose from audit.ACCOUNTS_AUDIT")
   .ToList();

 Assert.IsTrue(a.Any(record => Convert.ToString(record[0]) == "some value")); 

 Assert.IsTrue(a.Any(record => Convert.ToString(record[1]) == "some other value"));

Finally (see comments below), if we want to test if any field in any record has the value:

  var a = DbQueryToArray("Select CLIENTNO, ACCOUNT_Purpose from audit.ACCOUNTS_AUDIT")
    .SelectMany(line => {
      // Flatten the cursor into IEnumerable<String>
      string[] result = new string[line.FieldCount];

      for (int i = 0; i < result.Length; ++i)
        result[i] = Convert.ToString(line[i]);

      return result;
    });

  a.Any(item => item == "some value");

Answer 2

Use DataTable and SqlDataAdapter to get query result in form of table. Something like this:

string connString = @"your connection string here";
string query = "select * from table";
DataTable dataTable = new DataTable();
SqlConnection conn = new SqlConnection(connString);        
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
// create data adapter
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query your database and return the result to your datatable
da.Fill(dataTable);
conn.Close();
da.Dispose();

Then you can use dataTable object to see if particular values exist.

Answer 3

best practice is to check first if the reader has rows

reader.HasRows

then close the reader and the connection

your code should look like this:

public static ArrayList DbQueryToArry()
    {
        string SqlCString = "connString";
        SqlConnection connection = null;

        ArrayList valuesList = new ArrayList();

        connection = new SqlConnection(SqlCString);
        using (connection)
        {
            connection.Open();

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

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    valuesList.Add(Convert.ToString(reader[0]));
                    valuesList.Add(Convert.ToString(reader[1])); // add to valuelist
                }
            }

            reader.Close(); // close reader

        } //dispose connection

        return valuesList;
    }

Answer 4

The other answers are good, However some concerns

1. Lets stop using ArrayList, and use a List<T> instead
2. Lets use a using statement where you can

_{Note : I have used a ValueTuple to return more than 1 field}

Example

public static List<(string clientNo, string account)> DbQueryToArray()
{
   const string SqlCString = "connString";

   var valuesList = new List<(string clientNo, string account)>();

   using (var connection = new SqlConnection(SqlCString))
   {
      connection.Open();

      using (var command = new SqlCommand("Select CLIENTNO, ACCOUNT_Purpose from audit.ACCOUNTS_AUDIT", connection))
      {
         var reader = command.ExecuteReader();

         while (reader.Read())
            valuesList.Add(((string)reader[0],(string)reader[1]) );
      }
   }
   return valuesList;
}

Usage

var results = DbQueryToArray();
Assert.IsTrue(results.Any(x => x.clientNo == someValue || x.account == someValue));

Answer 5

That's cause you are getting only the first column. You can do something like below by specifying the column name

        while (reader.Read())
        {
            valuesList.Add(Convert.ToString(reader["CLIENTNO"]));
            valuesList.Add(Convert.ToString(reader["ACCOUNT_Purpose"]));
        }

Moreover, since you are converting all the columns to string; I would suggest to use a strongly typed collection like List<string> rather then ArrayList valuesList = new ArrayList();

Answer 6

It is because you only read the first value of the reader. Reader.Read() read each row one by one and Convert.ToString(reader[0])) means that you want to read the first column as string.