How to properly open SQL connection with a using statement and do basic data validation?

[Automation23]

Hello! I am new to database testing. I am doing mobile automation and I need to validate sent data in the database. I am trying to write a method that will open the db connection, do some implementation, and then close it. I need a method to return a value but I am stuck with it.
So far I have this:

public static int GetMemberId(string username){
    using (SqlConnection connection = new SqlConnection(Config.connectionString)){
        string query = $"select id from memberTable where username like '{username}'";
        using (SqlCommand command = new SqlCommand(query, connection)){
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader()){
                //not sure about the following line
                int i = Int32.Parse(ExecuteQuery(query).Rows[0].ItemsArray[0].ToString());
                //ExecuteQuery() method is a customed method that returns DataTable
            
            }
        }
    }
}

How can I return the first value of the first column from that query and then validate it with the expected output?

Another question, what is the best practical way/strategy to test the sent data in the database? Any advice?

Thank you in advance! Any help would be appreciated.

 

[jmcilhinney]

If you only want to get one value from the database then call ExecuteScalar. If you want to get data from multiple records then you call ExecuteReader, but then you need to call Read to get each record. Read returns true as long as there is a record to read, so you usually call it in a while loop:

public static int GetMemberId(string username)
{
    var query = $"select id from memberTable where username like '{username}'";
    
    using (var connection = new SqlConnection(Config.connectionString))
    using (var command = new SqlCommand(query, connection))
    {
        connection.Open();
        
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                var id = reader.GetInt32(0);
                
                // Use id here.
            }
        }
    }
}

Note that that method doesn't really make sense as it is because it's supposed to return an int but if you're performing a partial match then how can you possibly know that there will only be one match, or any matches for that matter? You need to be clear about what you're actually trying to achieve.

Also, if you have a method that takes a string and returns a DataTable and you want to use it then what is any of this for? You would just pass in your query and get back your DataTable, then get the data out of it. Again, you need to be clear about what you're actually trying to achieve. If you have a DAL already then either use it or don't.

 

[Skydiver]

I am new to database testing. I am doing mobile automation and I need to validate sent data in the database.

This is not the correct way to test if data has been sent to a database.

The correct way to do this kind of unit testing is to write a mock database interface and then assert that the mock's various methods have been called with the correct values. More likely than not, the unit test and mock will be written in C#.

The correct way to do this kind of integration testing is to dump the database tables and checking values. More likely than not, dumping the tables will be done with SQL scripts, and then some Perl, Python, or PowerShell scripts to examine the dumps.

 

[Automation23]

@jmcilhinney Is it possible to use assertions while reading the rows?
Something like that:

        public static void DB()
        {
            using(SqlConnection connection = new SqlConnection(Config.connectionStringBH))
            {
                connection.Open();
                string query = "select staffVerificationDate from hcAppointments where appointmentId = 21226449";
                SqlCommand command = new SqlCommand(query, connection);

                using(SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Assert.IsTrue(String.Format("{0}", reader[0]) != null);
                    }
                }
            }
        }

 

[Automation23]

This is not the correct way to test if data has been sent to a database.

The correct way to do this kind of unit testing is to write a mock database interface and then assert that the mock's various methods have been called with the correct values. More likely than not, the unit test and mock will be written in C#.

The correct way to do this kind of integration testing is to dump the database tables and checking values. More likely than not, dumping the tables will be done with SQL scripts, and then some Perl, Python, or PowerShell scripts to examine the dumps.

Thank you for replying back. I will need to do some research about a mock database cause I am not really familiar with that but I think it would be something I would utilize in the future. Thanks a lot!

 

[Skydiver]

It's not a mock database. It's a class that has the same interface as the repository or data access layer.

Mock a Database Repository using Moq

How to mock a database repository using Moq

www.codeproject.com