Delete data in sql table before running test

Ask general questions here.
jaredmatthews
Posts: 16
Joined: Fri Apr 18, 2014 1:06 pm

Delete data in sql table before running test

Post by jaredmatthews » Mon Sep 08, 2014 9:49 pm

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
Regards,
Jared

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

Re: Delete data in sql table before running test

Post by jaredmatthews » Tue Sep 09, 2014 1:18 pm

Ceige or sdaly can either of you Ranorex studs help me
Regards,
Jared

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

Re: Delete data in sql table before running test

Post by krstcs » Tue Sep 09, 2014 1:21 pm

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

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

Re: Delete data in sql table before running test

Post by jaredmatthews » Tue Sep 09, 2014 1:29 pm

Awesome thanks krtcs for the quick reply. that helps me on my way :)
Regards,
Jared

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

Re: Delete data in sql table before running test

Post by krstcs » Tue Sep 09, 2014 1:31 pm

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

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

Re: Delete data in sql table before running test

Post by krstcs » Tue Sep 09, 2014 1:32 pm

You are most welcome! Hope it helps!
Shortcuts usually aren't...

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

Re: Delete data in sql table before running test

Post by jaredmatthews » Tue Sep 09, 2014 7:40 pm

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.
Regards,
Jared

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

Re: Delete data in sql table before running test

Post by krstcs » Tue Sep 09, 2014 7:58 pm

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! :D
Shortcuts usually aren't...

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

Re: Delete data in sql table before running test

Post by jaredmatthews » Tue Sep 09, 2014 8:10 pm

Thanks again man appreciate your time :)
Regards,
Jared