Stored Procedures as coded step

[Amardeep]

Hi,

I want use the stored procedure as a Setup coded step where I want to attach database from server location to machine where I am running the test and upgrade it. After that I want to run my recorded steps.

How can I implement that?

Regards
A

using System.Data.SqlClient;
CREATE PROCEDURE SP_UpgradeDatabaseTest @RegressionRunID int
AS

BEGIN
    DECLARE @CMDSQL             varchar(1000),
            @DatabaseID         int,
            @DatabaseName       varchar(250),
            @CurrentLocation    varchar(250),
            @TargetLocation     varchar(250),
            @Result             int


    SET     @TargetLocation = 'C:\UpgradeDatabaseTest\'

    -- Tidy up previous run... detach databases
    DECLARE Database_cursor CURSOR
    FOR
    SELECT name FROM sys.databases
    JOIN DatabasesForUpgrading ON name = DatabaseName

    OPEN Database_cursor

    FETCH NEXT FROM Database_cursor
    INTO    @DatabaseName
    WHILE @@FETCH_STATUS = 0
    BEGIN

        EXEC sp_detach_db @DatabaseName

        FETCH NEXT FROM Database_cursor INTO    @DatabaseName

    END

    CLOSE Database_cursor
    DEALLOCATE Database_cursor

    EXEC master..xp_cmdshell 'Del C:\UpgradeDatabaseTest\*.mdf'
    EXEC master..xp_cmdshell 'Del C:\UpgradeDatabaseTest\*.ldf'

    -- Get list of databases to upgrade, copy, attach, and upgrade
    DECLARE Database_cursor CURSOR FOR SELECT DatabaseID, DatabaseName, Location FROM DatabasesForUpgrading WHERE Upgrade =1
    OPEN Database_cursor

    FETCH NEXT FROM Database_cursor
    INTO    @DatabaseID,
            @DatabaseName,
            @CurrentLocation

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- copy database to expected location
        DECLARE @PassedVariable VARCHAR(100)

        SET @CurrentLocation = @CurrentLocation + RTRIM(@DatabaseName) + '*.*'

        SET @CMDSQL = 'C:\NR_UpgradeDatabaseDBRepository\CopyFiles.bat ' + @CurrentLocation + ' ' + @TargetLocation

        EXEC master..xp_cmdshell @CMDSQL

        -- Attach Database to Server
        SELECT @CMDSQL = 'CREATE DATABASE ' +  @DatabaseName +
        ' ON (FILENAME = ''' + @TargetLocation + @DatabaseName + '.mdf''' + '),
        (FILENAME = ''' + @TargetLocation + @DatabaseName + '_Log.ldf'')
        FOR ATTACH'

        EXECUTE (@CMDSQL)

        -- Run Upgrade Database
        SET @CMDSQL = '"c:\program files (x86)\Athene\Data Management\Upgrade Database" DW-E5530,' + @DatabaseName
        EXEC master..xp_cmdshell @CMDSQL

        --Check Result of upgrade
        SET @CMDSQL = 'findstr /c:"The database has been updated" c:\programdata\metron\logs\*' + @DatabaseName + '*.log'
        EXEC @Result = master..xp_cmdshell @CMDSQL

        IF @Result = 0
            -- Success
            INSERT RR_UpgradeDatabase (RegressionRunID, DatabaseID, Result)
            VALUES (@RegressionRunID, @DatabaseID, 1)
        ELSE
            -- Failure
            INSERT RR_UpgradeDatabase (RegressionRunID, DatabaseID, Result)
            VALUES (@RegressionRunID, @DatabaseID, 0)

        FETCH NEXT FROM Database_cursor
        INTO    @DatabaseID,
                @DatabaseName,
                @CurrentLocation

    END

        CLOSE Database_cursor
        DEALLOCATE Database_cursor

END

[Boyan Boev]

Hi Amardeep,

Thank you for contacting us.

Here is how to access database via coded step.

Hope this helps.

Regards,
Boyan Boev
Telerik

[Bryan]

Or, set up a test with no steps and bind it to your stored procedure. Then use the "Setup" test as a "Test as Step" in the first step of your dependent test.

[Boyan Boev]

Hello Bryan,

Yes, you can put this coded step in a test as step.

Thank you for your input Bryan we appreciate it.

Regards,
Boyan Boev
Telerik

 

[Bryan]

Boyan,

The approach I suggested does not require the use of a coded step.

[Boyan Boev]

Hi Bryan,

The coded step is needed for executing queries in the data base or modifying it.

Thank you.

Regards,
Boyan Boev
Telerik

 

[Bryan]

Boyan,

I believe you are mistaken. We are currently using a test with NO steps with a binding to a stored procedure that performs multiple deletes and creates to set up our environment. Please make sure that this functionality is not removed from Telerik as it is crucial to our testing efforts and we should not have to create a coded step to execute a stored procedure.

[Boyan Boev]

Hi Bryan,

Please excuse me for misunderstanding.

You are absolutely correct and this functionality is not removed from Test Studio.

Regards,
Boyan Boev
Telerik

 

[Bryan]

Boyan,

I tried using the coded step as you suggested, but it times out before the stored procedure finishes running and forcefully closes the connection. Using the timeout in the connection string also had no effect.

​

////Define a new SQL connection with a connection string.
////The connection string will be different depending on your environment and the name of the database, table, etc.
////See http://www.connectionstrings.com for connection string examples.
SqlConnection thisConnection = new SqlConnection("Server=st_db;Database=QualityAssurance;"
                                             + " User Id=xxxxxxxxx; Password=xxxxxxxxx;"
                                             + " Connection Timeout=600");
thisConnection.Open();

////Write the name of the database to the log.
//Log.WriteLine(thisConnection.Database);

//Create a SQL command to insert a new value into the "City" column.
SqlCommand thisCommand = thisConnection.CreateCommand();
thisCommand.CommandText = "EXECUTE [QualityAssurance].[dbo].[CreateWPIData] "
                                   + "  @ApplicationName = 'Just' "
                                   + "  ,@ApplicationVersion = 'Delete' "
                                    + " ,@TestPlanName = 'It' "
                                    + " ,@TestSetName = 'All' "
                                    + " ,@DeleteIntegrationRecords  = 'Y' "
                                    + " ,@DeleteWrExpertDataClaims  = 'Y' "
                                    + " ,@ResetIntegrationCompany   = 'Y' "
                                + " ; ";
thisCommand.ExecuteNonQuery();

[Boyan Boev]

Hi Bryan,

You don't need to use code when it works without a single row of code as you described in your initial post.

Could you please elaborate a bit more why do you use coded step?

Hope to hear from you soon.

Regards,
Boyan Boev
Telerik

 

[Bryan]

Boyan,

I get the timeout issue when it is used as a coded step and when it is used as a bound data set. All evidence points to Telerik forcefully closing the connection.

[Boyan Boev]

Hello Bryan,

Could you please send us the application log so we can debug this further. Please clear it first and then reproduce the issue.

Hope to hear from you soon.

Regards,
Boyan Boev
Telerik

 

[Bryan]

Boyan,

I have included both the application log and the test log in the attached zip file.
Additionally, I created a new procedure that intentionally takes 5 minutes to complete to duplicate the issue in any database you desire. The code for this stored procedure is below.

​

-- =======================================
-- Author:      Bryan Boutwell
-- Create date: 2015-02-02
-- Description: Sample stored procedure that intentionally takes
--              5 minutes to complete to demonstrate Telerik
--              Test Studio issue with executing long running
--              stored procedures.
-- =======================================

CREATE PROCEDURE [dbo].TELERIK_TIMEOUT
AS
BEGIN
    WAITFOR DELAY '0:5:00';
END
GO

[Boyan Boev]

Hi Bryan,

Thank you for the logs and the sample.

I will consult this with our developers and will update this forum thread accordingly.

Thank you for your cooperation.

Regards,
Boyan Boev
Telerik

 

[Boyan Boev]

Hello Bryan,

Please excuse us for the delayed reply.

Since this exception is not coming from Test Studio (System.Data.SqlClient.SqlConnection.OnError) could you please send us a sample test project where we can reproduce behavior. 

How do you configure exactly the project?

What code you have in the coded step?

A Jing video would also help us.

Hope to hear from you soon.

Regards,
Boyan Boev
Telerik

 

[Bryan]

Boyan,

To clarify, the error is actually NOT being thrown by SQL. The connection is being forcibly closed by Telerik. My assumption is that there is a timeout configuration in Telerik that is causing the connection to be forcibly closed even though the stored procedure is still running.

A sample project is attached. You will need to perform the following database steps on your end to make it run.
1 - create the stored procedure "TELERIK_TIMEOUT" in the database you want to run the test against.

-- =======================================

-- Author:      Bryan Boutwell

-- Create date: 2015-02-02

-- Description: Sample stored procedure that intentionally takes

--              5 minutes to complete to demonstrate Telerik

--              Test Studio issue with executing long running

--              stored procedures.

-- =======================================



CREATE PROCEDURE [dbo].TELERIK_TIMEOUT

AS

BEGIN

    WAITFOR DELAY '0:5:00';

END

GO

2 - update the connection string in the coded step to connect to the correct database.

[Boyan Boev]

Hi Bryan,

Thank you for the project.

I was able to reproduce the issue and have logged a bug report about this. You can track and vote for it here.

I hope this will be fixed as soon as possible.

Thank you for you feedback.

Regards,
Boyan Boev
Telerik

 

[Boyan Boev]

Hi Bryan,

After further discussion about the issue we have discovered what causes it.

This is not a Test Studio bug. The .NET framework defaults to a 30 second timeout. You have to increase the timeout in the SqlCommand. See this article.

You should add this row after creating the command:

thisCommand.CommandTimeout = 500;

Let me know if you need further assistance.

Regards,
Boyan Boev
Telerik