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.
Stuck accessing SQL Server in code
Re: Stuck accessing SQL Server in code
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):
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).
myData will now contain everything from the SQL statement.
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
Shortcuts usually aren't...
Re: Stuck accessing SQL Server in code
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.
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.
Re: Stuck accessing SQL Server in code
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.
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...
Re: Stuck accessing SQL Server in code
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.
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.
Re: Stuck accessing SQL Server in code
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.
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...
Re: Stuck accessing SQL Server in code
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...
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?
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();
}
}
Pavel Kudrys
Ranorex explorer at Descartes Systems
Please add these details to your questions:
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
Re: Stuck accessing SQL Server in code
Thank You ! Exactly what I was looking for.