HI,
i just want to know whether we can connect to Database using Ranorex, if yes could you help us in doing it,
Thanks
Kumar
Connecting to Sql Server2008
Re: Connecting to Sql Server2008
You can do anything that C#/VB and the .NET framework can do - if you are able to write code for it.kumarchowg wrote:HI,
i just want to know whether we can connect to Database using Ranorex, if yes could you help us in doing it,
Thanks
Kumar
Whether or not Ranorex Studio provides some means to hook up to a database without coding, I don't know.
Re: Connecting to Sql Server2008
Since I'm such a lovely chap, here is a MSSQLConnector class that I use in our automation framework - if using RxStudio then maybe you could make a code module

Code: Select all
using System;
using System.Data;
using System.Data.SqlClient;
namespace AutoFramework
{
public class MSSQLConnector
{
private string server, database, uid, password, connectionString;
public MSSQLConnector(string _server, string _database, string _username, string _password)
{
this.server = _server;
this.database = _database;
this.uid = _username;
this.password = _password;
this.connectionString = "user id="+ this.uid +";" +
"password="+ this.password +";" +
@"server="+ this.server + ";" +
"database="+ this.database + "; " +
"connection timeout=30";
}
/// <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 DataTable RunQuery(string SQL)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlDataReader reader = null;
DataTable dt = new DataTable();
try{
SqlCommand 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 MSSQLConnector::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 tablename.
/// Use this when you just was a count and don't need any data returned.
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
public int CountQuery(string SQL)
{
SqlConnection connection = new SqlConnection(connectionString);
int count=0;
try{
SqlCommand command = connection.CreateCommand();
command.CommandText = SQL;
connection.Open();
count = Convert.ToInt32(command.ExecuteScalar());
}
catch (Exception e){
Console.WriteLine("Exception is MSSQLConnector::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 int UpdateQuery(string SQL)
{
SqlConnection connection = new SqlConnection(connectionString);
int rowsAffected=0;
try{
SqlCommand command = connection.CreateCommand();
command.CommandText = SQL;
connection.Open();
rowsAffected = Convert.ToInt32(command.ExecuteNonQuery());
}
catch (Exception e){
Console.WriteLine("Exception is MSSQLConnector::UpdateQuery - " + e.ToString());
}
finally{
connection.Close();
}
return rowsAffected;
}
}
}
Re: Connecting to Sql Server2008
Hi Sdaly, Thank you for you code snippet,
C#-
i have a table name candidate with 10 columns fname,lname,address,..., there are 20 rows.
I need to create 5 candidates using data from 1st 5 rows (fname,lname,addres...).
How do i get the data and pass the data to the caller. Thank you in advance
C#-
i have a table name candidate with 10 columns fname,lname,address,..., there are 20 rows.
I need to create 5 candidates using data from 1st 5 rows (fname,lname,addres...).
How do i get the data and pass the data to the caller. Thank you in advance
Tipu
- Support Team
- Site Admin
- Posts: 12145
- Joined: Fri Jul 07, 2006 4:30 pm
- Location: Houston, Texas, USA
- Contact:
-
- Posts: 16
- Joined: Fri Apr 18, 2014 1:06 pm
Re: Connecting to Sql Server2008
Hi Sdaly,
Do you have a sample project where i can see how you tie in this user module?
Thanks,
Jared
Do you have a sample project where i can see how you tie in this user module?
Thanks,
Jared
Regards,
Jared
Jared
Re: Connecting to Sql Server2008
Jared, these threads are pretty old (2+ years), and Sdaly hasn't been on the site since February 2013.
Here is the code I use, but it is in different places (in libraries) so I may miss some things...
To setup the DB connection:
To use it:
Here is the code I use, but it is in different places (in libraries) so I may miss some things...
To setup the DB connection:
Code: Select all
using System.Data;
using System.Data.SqlClient;
//...
public static SqlConnection sqlConn = new SqlConnection();
public static SqlCommand sqlComm = new SqlCommand();
public static string sqlConnString = "Server={0};Database={1};User Id={2};Password={3};";
public static SqlDataAdapter sqlDA = new SqlDataAdapter();
public static void SetupSqlDataAdapterSELECT() {
sqlDA.SelectCommand = sqlComm;
}
public static void SetupSqlConnection(string dbServer, string dbName, string dbUserID, string dbPassword) {
sqlConn.ConnectionString = string.Format(sqlConnString, dbServer, dbName, dbUserID, dbPassword);
}
public static void SetupSqlCommand(string command) {
sqlComm.Connection = sqlConn;
sqlComm.CommandText = command;
}
Code: Select all
TCS_LIB.Data.SetupSqlConnection(@"<ServerName>\SQLEXPRESS", "<Schema>", "<UserName>", "<Password>");
TCS_LIB.Data.SetupSqlCommand("<sql statement - must be NON QUERY for the exec to work, can be call to stored procedure>");
TCS_LIB.Data.sqlConn.Open();
TCS_LIB.Data.sqlComm.ExecuteNonQuery();
TCS_LIB.Data.sqlConn.Close();
Shortcuts usually aren't...