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

Automation23

Member
Joined
Jul 13, 2022
Messages
7
Programming Experience
1-3
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:

C#:
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

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
4,643
Location
Sydney, Australia
Programming Experience
10+
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:
C#:
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

Staff member
Joined
Apr 6, 2019
Messages
5,698
Location
Chesapeake, VA
Programming Experience
10+
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

Member
Joined
Jul 13, 2022
Messages
7
Programming Experience
1-3
@jmcilhinney Is it possible to use assertions while reading the rows?
Something like that:
C#:
        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

Member
Joined
Jul 13, 2022
Messages
7
Programming Experience
1-3
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

Staff member
Joined
Apr 6, 2019
Messages
5,698
Location
Chesapeake, VA
Programming Experience
10+
It's not a mock database. It's a class that has the same interface as the repository or data access layer.

 
Top Bottom