FR: How to work with DB

Experiences, small talk, and other automation gossip.
User avatar
slavikf
Posts: 104
Joined: Mon Sep 13, 2010 9:07 pm
Location: Toronto, Canada
Contact:

FR: How to work with DB

Post by slavikf » Thu May 19, 2011 1:09 am

Feature Request:
Would be nice, if you publish blog article about validating DB data, using Ranorex

Simple case scenarios would be:
- Preparing data in DB for test
- Running AUT, getting data from DB, Validating data

Also, interesting, if Ranorex can do any data binding with DB

User avatar
sdaly
Posts: 238
Joined: Mon May 10, 2010 11:04 am
Location: Dundee, Scotland

Re: FR: How to work with DB

Post by sdaly » Thu May 19, 2011 3:56 pm

To be honest, this isn't really Ranorex specific, it's more .net. There is loads of information on the internet about how to connect to db's etc. Anyway, as an example here is a class that I wrote for working with mysql. Working with Oracle or MSSQL etc is very similar, just different connection objects are used.

To use this, download and install the .net mysql connector from http://dev.mysql.com/downloads/connector/net/

For setting up test data you could do something like -

Code: Select all

MySQLConnector.UpdateQuery("INSERT INTO TABLE (FIELD1, FIELD2) VALUES ('1','2')");
For validating something has been added you could do -

Code: Select all

Ranorex.Validate.IsTrue(MySQLConnector.CountQuery("SELECT COUNT(*) FROM TABLE WHERE ID='1234'") = 1, "Validating 1 record has been found in DB for ID 1234");
For getting data you can do -
string data = MySQLConnector.RunQuery("SELECT ID FROM TABLE WHERE NAME ='Jim'").Rows[0]["ID"];
Of course you could then do

Code: Select all

 Ranorex.Validate.IsTrue(data = "1234", "Checking data on row zero is 1234");

Code: Select all

public static class MySQLConnector
	{
		static string connectionString = "SERVER=192.168.1.1;" +
			"DATABASE=dbname;" +
			"UID=root;" +
			"PASSWORD=pass;";
		
		/// <summary>
		/// Returns a datatable containing the data the query retrieved.  Use this when you need data returned.
		/// </summary>
		/// <param name="SQL"></param>
		/// <returns></returns>
		public static DataTable RunQuery(string SQL)
		{
			
			MySqlConnection connection = new MySqlConnection(connectionString);
			MySqlDataReader reader = null;
			DataTable dt = new DataTable();
			try{
				MySqlCommand command = connection.CreateCommand();
				command.CommandText = SQL;
				connection.Open();
				reader = command.ExecuteReader();
				//load the mysql reader into a databable - this allows the connection to the db to be closed while still
				//having access to the data that the query returned
				dt.Load(reader);
			}catch(Exception e){
				Console.WriteLine("Exception is MySQLConnector::RunQuery - " + e.ToString());
			}finally{
				reader.Close();
				connection.Close();
			}
			return dt;
		}
		
		/// <summary>
		/// Returns the count of records found for a count query.  Example SELECT COUNT(*) FROM hdlogin.
		/// Use this when you just was a count and don't need any data returned.
		/// </summary>
		/// <param name="SQL"></param>
		/// <returns></returns>
		public static int CountQuery(string SQL)
		{
			MySqlConnection connection = new MySqlConnection(connectionString);
			int count=0;
			try{
				MySqlCommand command = connection.CreateCommand();
				command.CommandText = SQL;
				connection.Open();
				count = Convert.ToInt32(command.ExecuteScalar());
			}
			catch (Exception e){
				Console.WriteLine("Exception is MySQLConnector::CountQuery - " + e.ToString());
			}
			finally{
				connection.Close();
			}
			return count;
		}
		
		/// <summary>
		/// Returns the number of rows affected by the supplied query.  Use this if you need to run an UPDATE or INSERT query.
		/// </summary>
		/// <param name="SQL"></param>
		/// <returns></returns>
		public static int UpdateQuery(string SQL)
		{
			MySqlConnection connection = new MySqlConnection(connectionString);
			int rowsAffected=0;
			try{
				MySqlCommand command = connection.CreateCommand();
				command.CommandText = SQL;
				connection.Open();
				rowsAffected = Convert.ToInt32(command.ExecuteNonQuery());
			}
			catch (Exception e){
				Console.WriteLine("Exception is MySQLConnector::UpdateQuery - " + e.ToString());
			}
			finally{
				connection.Close();
			}
			return rowsAffected;
		}
		
	}

User avatar
Support Team
Site Admin
Site Admin
Posts: 12145
Joined: Fri Jul 07, 2006 4:30 pm
Location: Houston, Texas, USA
Contact:

Re: FR: How to work with DB

Post by Support Team » Thu May 19, 2011 4:00 pm

Besides, you can also use the Ranorex provided data connectors:
http://www.ranorex.com/support/user-gui ... ctors.html

Regards,
Alex
Ranorex Team