Delete data in sql table before running test

[jaredmatthews]

Hi

I want to delete all data in a sql table before running a test. I can connect to my datasource in Manage Data Sources and in the Query box i have to query i need to delete the particular data. I have the checkbox checked to auto load data when suite opens but i think its expecting my query to return data when I actually want it to delete data.

Can anyone help me here. I do not have coding experience but am open to trying a code module with some help.

Thanks,
Jared

[jaredmatthews]

Ceige or sdaly can either of you Ranorex studs help me

[krstcs]

Ranorex uses the ExecuteReader() method of the SqlCommand class to get data from the database. That method should only be used to retrieve rows of data. The Ranorex software expects the dataset to be static at runtime (as it should for raw test data). In addition, the Ranorex data connectors are not designed to be used in the manner you are trying to use them and there could be undesired side-effects.

If you need to manipulate data (such as needing to update rows in a table due to runtime monitoring, which is what I assume you are needing to do), you should create your own code module that does that, using the SqlCommand.ExecuteNonQuery() method. This will allow you to place the code module wherever you need it in your test instead of having to tie it to a test case.

[jaredmatthews]

Awesome thanks krtcs for the quick reply. that helps me on my way

[krstcs]

One thing you could do is put your delete statement in a stored procedure and call that stored procedure in your data connector.

However, this should be avoided and you should use the method I described above if possible.



Ideally, you would use stored procedures for all interactions with the DB and call them from the connectors, or your data manipulator modules. This helps keep data in the DB and code in Ranorex. Ranorex only needs to know about the SP, so if you need to change the SP's returned fields, you only have to change the SP, not every place it is used in code.

SP call looks like this:

Code: Select all

exec <StoredProcedureName> <[@Param1=]Value><, [@Param2=]Value>...<, [@ParamN=]Value>

[krstcs]

You are most welcome! Hope it helps!

[jaredmatthews]

Hi Krstcs,

Thanks for all your help in the multiple posts. THis is what i came up with:

Code: Select all

using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using System.Drawing;
using System.Threading;
using WinForms = System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;
using System.Xml;


using Ranorex;
using Ranorex.Core;
using Ranorex.Core.Testing;

namespace sqlexample
{
    /// <summary>
    /// Description of Sql.
    /// </summary>
    [TestModule("3E830763-4E92-415A-A875-EC3E57A15EA8", ModuleType.UserCode, 1)]
    public class Sql : ITestModule
    {
        /// <summary>
        /// Constructs a new instance.
        /// </summary>
        public Sql()
        {           
        }
         
        /// <summary>
        /// Performs the playback of actions in this module.
        /// </summary>
        /// <remarks>You should not call this method directly, instead pass the module
        /// instance to the <see cref="TestModuleRunner.Run(ITestModule)"/> method
        /// that will in turn invoke this method.</remarks>
        void ITestModule.Run()
        {
            Mouse.DefaultMoveTime = 300;
            Keyboard.DefaultKeyPressTime = 100;
            Delay.SpeedFactor = 1.0;
         	SqlConnection connection = new SqlConnection(@"User ID=user;Password=pass;Persist Security Info=true;Initial Catalog=table;Data Source=dbname");
            SqlCommand cmd = new SqlCommand("Delete from SystemData where fieldID='UpgradeVersion'", connection);
       

            try
            {
               connection.Open();
               if (connection.State == System.Data.ConnectionState.Open) {
                  Report.Log(ReportLevel.Success, "User", "DB Connected");
                  cmd.ExecuteNonQuery();
                  Report.Log(ReportLevel.Success, "User", "Deleted Upgrade Version from DB");
               }
               
            }
               
            catch (Exception e)
            {
            	Report.Log(ReportLevel.Success, "User", "DB Error Found");
                throw e;
               
            }
            finally
            {
                if (connection.State == System.Data.ConnectionState.Open)
                    connection.Close();
                    Report.Log(ReportLevel.Success, "User", "DB Closed");
            }
        }
            
      
}
}

Since I dont know c# at all the hardest part was figuring out where to put this.

[krstcs]

Just a note on the if() blocks:

If you don't include curly-brackets "{...}" around a set of lines of code then the IF will only execute the very next line...

Code: Select all

if (myBool)
  int newValue = 2; // this will be executed only if myBool is true...
  newValue = 3;  //this will be executed all the time because it isn't enclosed in a block "{...}"


if (myBool) { //notice the bracket here to start the block for the if...
  int newValue = 2; // this will be executed only if the myBool is true...
  newValue = 3;  //this will be executed only if myBool is true as well, because it is in the block.
} // and the closing bracket here to show the if where to stop...

Also, I would highly recommend that you pick up a C# book ("C# 5.0 All-in-One for Dummies" is an excellent place to start) and start learning to code. It will be invaluable in your future with Ranorex and any other object-oriented software, even those built on Java or C/C++ because you will understand the syntax better.


And, as always, ask questions!

[jaredmatthews]

Thanks again man appreciate your time