Page 1 of 1
Connecting to Sql Server2008
Posted: Wed Feb 01, 2012 6:17 am
by kumarchowg
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
Re: Connecting to Sql Server2008
Posted: Wed Feb 01, 2012 8:11 am
by Stian
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
You can do anything that C#/VB and the .NET framework can do - if you are able to write code for it.
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
Posted: Wed Feb 01, 2012 9:09 am
by sdaly
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
Posted: Thu Dec 13, 2012 9:51 pm
by omayer
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
Re: Connecting to Sql Server2008
Posted: Thu Dec 27, 2012 11:50 am
by Support Team
Hello,
Please follow this forum
post.
Regards,
Markus (T)
Re: Connecting to Sql Server2008
Posted: Tue Sep 09, 2014 3:28 pm
by jaredmatthews
Hi Sdaly,
Do you have a sample project where i can see how you tie in this user module?
Thanks,
Jared
Re: Connecting to Sql Server2008
Posted: Tue Sep 09, 2014 4:42 pm
by krstcs
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:
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;
}
To use it:
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();