Connecting to Sql Server2008

Ask general questions here.
kumarchowg
Posts: 19
Joined: Mon Jan 23, 2012 7:53 am

Connecting to Sql Server2008

Post by kumarchowg » Wed Feb 01, 2012 6:17 am

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

Stian
Posts: 25
Joined: Mon Jan 30, 2012 9:38 am

Re: Connecting to Sql Server2008

Post by Stian » Wed Feb 01, 2012 8:11 am

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.

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

Re: Connecting to Sql Server2008

Post by sdaly » Wed Feb 01, 2012 9:09 am

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 :wink:

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;
		}
	
	}
}

omayer
Posts: 458
Joined: Thu Oct 28, 2010 6:14 pm

Re: Connecting to Sql Server2008

Post by omayer » Thu Dec 13, 2012 9:51 pm

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
Tipu

User avatar
Support Team
Site Admin
Site Admin
Posts: 11709
Joined: Fri Jul 07, 2006 4:30 pm
Location: Graz, Austria

Re: Connecting to Sql Server2008

Post by Support Team » Thu Dec 27, 2012 11:50 am

Hello,

Please follow this forum post.

Regards,
Markus (T)
.
Image

jaredmatthews
Posts: 16
Joined: Fri Apr 18, 2014 1:06 pm

Re: Connecting to Sql Server2008

Post by jaredmatthews » Tue Sep 09, 2014 3:28 pm

Hi Sdaly,

Do you have a sample project where i can see how you tie in this user module?

Thanks,
Jared
Regards,
Jared

krstcs
Ranorex Guru
Posts: 2683
Joined: Tue Feb 07, 2012 4:14 pm
Location: Austin, Texas, USA

Re: Connecting to Sql Server2008

Post by krstcs » Tue Sep 09, 2014 4:42 pm

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();
Shortcuts usually aren't...