Stuck accessing SQL Server in code

Class library usage, coding and language questions.
Zee357
Posts: 8
Joined: Tue Jan 05, 2016 7:43 pm

Stuck accessing SQL Server in code

Post by Zee357 » Thu Mar 10, 2016 9:12 pm

I'm trying to get a SQLClient database connector initialized and open then to run embedded SQL queries in my Ranorex user code. it is Failing on initialization of the connection string.

Any code that is working for anyone out there, I'd love to take a look at it and how you are structuring the code and your solution to handle database operations properly. The doc and examples I've seen are not helping me.

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

Re: Stuck accessing SQL Server in code

Post by krstcs » Thu Mar 10, 2016 9:47 pm

Can you post the error you are getting, along with the code you are using? Maybe there's just something off in your code?

This is basically what I use (although I have a library setup):

Code: Select all

using System.Data;
using System.Data.SqlClient;

...

string sqlConnString = "Server={0};Database={1};User Id={2};Password={3};Connection Timeout={4};"; //notice the ending ';' inside the string...
string dbServer = @"myServer\sqlexpress"; //if you use a full version use this "myServer" without @ and \
string dbName = "myDBName";
string dbUsername = "username";
string dbPassword = "password";
int dbConnectionTimeoutInSeconds = 10; //or whatever you want the connection to timeout at

SqlConnection sqlConn = new SqlConnection();
sqlConn.ConnectionString = string.Format(sqlConnString, dbServer, dbName, dbUsername, dbPassword, dbConnectionTimeoutInSeconds);

sqlConn.Open();

SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = sqlConn;

sqlComm.CommandText = "UPDATE myTable SET Column1='True' WHERE ID = 3";
sqlComm.ExecuteNonQuery(); //use this for sql that doesn't return a table

sqlComm.CommandText = "SELECT TOP 1 ID FROM myTable ORDER BY OrderDate DESC";
int id = sqlComm.ExecuteScalar(); //use scalar for single value returns, the command will return row 1, column 1

For returning a table value (multiple rows and/or columns) you should use a .NET DataAdapter and Fill() it with the query. You can read more on how to do that and how it works on MSDN (I suggest you look there anyway, it's a great help for .NET).

Code: Select all

sqlComm.CommandText = "SELECT * FROM myTable";

SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = sqlComm;

DataTable myData = new DataTable();

da.Fill(myData);

sqlConn.Close();  // ALWAYS CLOSE THE CONNECTION AFTER YOU RUN YOUR COMMAND(S)...  You sometimes don't have to, but it's always good practice
myData will now contain everything from the SQL statement.
Shortcuts usually aren't...

barkha
Posts: 18
Joined: Tue May 16, 2017 9:41 pm
Location: Ames, IA
Contact:

Re: Stuck accessing SQL Server in code

Post by barkha » Tue Aug 15, 2017 7:26 pm

Hello,
I have also being using the same method to run commands on the back end. I want to know how we can get the result to show up from the back end in the report. I mean sometimes the query fails I want to display it in the report or fail the test based on the query status.

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

Re: Stuck accessing SQL Server in code

Post by krstcs » Tue Aug 15, 2017 9:26 pm

That very much depends on your setup and what data you want to put in the report. Generally anything you want to put in there can be added with the Report class.

As far as errors, you would probably be best off using if statements and throwing an exception if the data you expect isn't returned. You can use row counts or look for specific data items, but again that depends on what your setup and needs are.

Please provide more information about what you are trying to accomplish and your current setup, including the code you are using as this will make it easier to give feedback and potentially working solutions.
Shortcuts usually aren't...

barkha
Posts: 18
Joined: Tue May 16, 2017 9:41 pm
Location: Ames, IA
Contact:

Re: Stuck accessing SQL Server in code

Post by barkha » Fri Sep 01, 2017 6:12 pm

Hello,
So for example I am trying to delete some data from specific table in my database. Now sometimes due to access constraints my data might not be deleted. Sql will throw an error . However, My ranorex report say query executed successfully. So i don't know if data actually got deleted from database.
I want to somehow monitor the execution of back end or get the success or error message thrown by sql to verify that data actually got deleted.
If the query throw an error my result should fail.

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

Re: Stuck accessing SQL Server in code

Post by krstcs » Tue Sep 05, 2017 1:51 pm

You could try adding a query after the delete operation. Have the query look for the information you just tried to delete, if it is found throw an exception.

Without seeing your code it's hard to know why Ranorex isn't seeing what you're doing as an error.
Shortcuts usually aren't...

User avatar
odklizec
Ranorex Guru
Ranorex Guru
Posts: 3936
Joined: Mon Aug 13, 2012 9:54 am
Location: Zilina, Slovakia

Re: Stuck accessing SQL Server in code

Post by odklizec » Wed Sep 06, 2017 7:43 am

Hi,

I think you just need to create a condition, in which you obtain a query result from reader (the one which confirms successful delete) and try...catch block, if reader returns exception. Here is a small sample from one of my tests I recently cooked up...

Code: Select all

            SqlConnection conn = new SqlConnection("Data Source=something;Initial Catalog=somethingelse;Integrated Security=True");
			SqlDataReader rdr = null;
			try
			{
				// 2. Open the connection
				conn.Open();
				
				// 3. Pass the connection to a command object
				SqlCommand cmd = new SqlCommand("SELECT RoutePlannerStatus FROM somethingelse.dbo.PO_CarrierDispatchScheduling WHERE ContactName ='JOHN WAYNE (" + replaceString + ")'", conn);
	
				// 4. Use the connection
				// get query results
				rdr = cmd.ExecuteReader();
				
				//wait for the appearance of status in DB				
				while (!rdr.HasRows) 
				{
					Report.Debug("Info","Waiting for status to appear in DB..."); 
					System.Threading.Thread.Sleep(500); 
					try 
					{
						rdr.Close();
						rdr = cmd.ExecuteReader();
					} 
					catch (Exception ex)
					{
						throw new Ranorex.RanorexException(ex.ToString());
					}
				}
	 			
				// validate status 
				while (rdr.Read())
				{
					string actualStatusFromDB = Regex.Replace(rdr[0].ToString(), @"^\s+|\s+$", "");
					Validate.AreEqual(actualStatusFromDB, expectedStatus);
				}
			}
			finally
			{
				// close the reader
				if (rdr != null)
				{
					rdr.Close();
				}
	
				// 5. Close the connection
				if (conn != null)
				{
					conn.Close();
				}
			}  
In the above sample, I'm trying to obtain a value from RoutePlannerStatus. The thing is, that it may not be ready/in DB at a time of starting the test. So I'm evaluating rdr.HasRows. If it returns false, it means that the RoutePlannerStatus is most probably not in DB yet. So I'm closing the reader, and starting it again (with some delay) and I repeat this until the rdr.HasRow is not true. I think something similar should help you?
Pavel Kudrys
Ranorex explorer at Descartes Systems

Please add these details to your questions:
  • Ranorex Snapshot. Learn how to create one >here<
  • Ranorex xPath of problematic element(s)
  • Ranorex version
  • OS version
  • HW configuration

barkha
Posts: 18
Joined: Tue May 16, 2017 9:41 pm
Location: Ames, IA
Contact:

Re: Stuck accessing SQL Server in code

Post by barkha » Fri Sep 08, 2017 3:23 pm

Thank You ! Exactly what I was looking for.