Ranorex Forum

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

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();