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
FR: How to work with DB
Re: FR: How to work with DB
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 -
For validating something has been added you could do -
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
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')");
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");
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;
}
}
- Support Team
- Site Admin
- Posts: 12145
- Joined: Fri Jul 07, 2006 4:30 pm
- Location: Houston, Texas, USA
- Contact:
Re: FR: How to work with DB
Besides, you can also use the Ranorex provided data connectors:
http://www.ranorex.com/support/user-gui ... ctors.html
Regards,
Alex
Ranorex Team
http://www.ranorex.com/support/user-gui ... ctors.html
Regards,
Alex
Ranorex Team