locked
In CUIT is it possible to automate the queries to be executed in SQL.? RRS feed

  • Question

  • Hi ,

    I am working on CUIT test for a web based project it need background process to be excuted in SQL by queries.Is it possible to automate the queries to be executed in SQL?

    Tuesday, December 11, 2012 10:06 AM

Answers

  • Hello Ramsid,

    I find that we can automate the SQL Server query execution successfully if we don’t save the sqlquery.sql created during recording and close the sql server directly after executing the query script. I have tried the following actions and test can run succeed:

    Click Start->All Programs->Microsoft SQL Server 2008 R2->SQL Server Management Studio->Connect to Server->Click New Query->Input some scripts into the SQLQuery1.sql->Close the Sql Server.

    If you save the sqlquery.sql, you will get the issue stated in my previous reply. New query will be created so that the recorded sqlquery will be found correctly.

    You can try the actions on SQL Server 2005 to check the result.

    In this case, you can try to put those query scripts in a CSV file and add this data source to Coded UI test so that you can execute multiple queries.

    Also you can hand code your own functions for opening SQL Server 2005 and selecting the database to execute the query one by one in coded UI test. You can record some actions on SQL Server and check its objects are created in UIMap.designer.cs. Then you can refer them to hand code your own coded UI test.

    Meanwhile you can refer to this blog that may help you: Hand-coding a Coded UI Test

    Hope my reply can help you.

    Best regards,


    Amanda Zhu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, December 14, 2012 7:37 AM
    Moderator

All replies

  • Hello Ramsid,

    Thank you for your post.

    I am not very sure about “automate the queries to be executed in SQL”.

    Can you provide us a specific scenario about what you want to do in details?

    What application do you do coded UI test for? What will you get when the SQL queries are executed? Can you see the result on the application UI after SQL queries are executed?

    If possible, you can provide us more information so that we can further look at this issue.

    And as far as I know, if you have a HtmlTable control in an web application and some data in the SQL Server database will display in the HtmlTable control when you click a button, you can coded UI test the HtmlTable control such as iterating the HtmlTable, getting the value on each htmlcell and adding assertion for each cell.

    Generally the coded UI test performs actions on the user interface controls for an application and verifies that the correct controls are displayed with the correct values.

    You can refer to this article for detailed information about code ui test:

    Verifying Code by Using Coded User Interface Tests

    If you want to automate the internal process that some queries are executed in SQL, as my personal point of view, I am afraid that we can’t do that using Coded UI test.

    If I have misunderstood anything, please feel free to let me know.

    Best regards,



    Amanda Zhu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.



    Wednesday, December 12, 2012 2:31 AM
    Moderator
  • Hi Am anda,

    I am performing CUIT for web based appilcation.After performing set of operation background process need to be executed manully in SQL.For CUIT, i need to perform automate the background process SQL query to be run SQL server2005.Also i need to save the query result in say as .rpt file and i need the assert the values of query results.

    Wednesday, December 12, 2012 6:29 AM
  • Hello Ramsid,

    Glad to receive your reply.

    If you want to automate the background process SQL query run in SQL server2005 which should be done manually, I am afraid that we can’t accomplish it using Coded UI test.

    Coded UI Test performs some actions on the user interface controls for an application.

    Then Coded UI Test can plays back those actions you have performed on the application UI to verify that the correct controls are displayed with the correct values. It can’t help you perform some operations which should be done by manually to execute some functions.

    If the values of query results display in some controls of the application, and CodedUI can recognize those controls, I think that you can assert the values.

    If I have misunderstood anything, please feel free to let me know.

    Best regards,


    Amanda Zhu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, December 12, 2012 7:48 AM
    Moderator
  • Hi Am anda,

    I want to automate the query to be executed in SQL server 2005 and it has no actions to perform on application UI and checking with database. Once the set of action is performed, i need to open SQL server2005 and perform background query.Once the background process is run all the tables get updated and i can generate the report as the process outcome.

    Wednesday, December 12, 2012 9:46 AM
  • Hello,

    So you can use the basic ways to run a query per your API with c# by including some of the Microsoft.SqlServer.Management dlls in your solution, then use it in you CUIT.

    You can find many examples on the web about this and how to get queries results


    QC Automation Engineer


    Wednesday, December 12, 2012 4:43 PM
  • Hello Ramsid,

    Glad to receive your reply.

    Do you want to know if we can record actions (open the SQL server2005, create new queries, input the queries scripts, and then execute the script) on the SQL server 2005 UI and then play back it?

    You can record these actions. You can try it with SQL server2005.

    I have tried these actions on Sql Server UI, and I record to input some delete/update scripts in SQLQuery1and click “execute”. But when I run coded UI test, the CodedUI plays back to generate SQLQuery 2 rather than SQLQuery 1 after the New Quey button is clicked. In this case, the SQLQuery 1 object can’t be found during playback. So test will fail. I have tried many times and always get this issue. Also I tried the same actions manually and another new SQLQuery will always generate when I click the New Query again. I think that it is desired for SQL Server. But as my personal point of view, this is also a restraint for Coded UI test to automate to execute the query in SQLQuery.

    Also you can refer to this article that may help you:

    http://social.msdn.microsoft.com/Forums/is/vsautotest/thread/f957ce2e-a1c2-4318-81e1-78afc6ad71ff

    Hope my reply can help you.

    If I have misunderstood anything, please feel free to let me know. I will try my best to help you.

    Best regards,


    Amanda Zhu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, December 13, 2012 1:51 AM
    Moderator
  • Hi Am anda,

    As you said  i need to record actions open SQL server 2005 and execute the query.But i need to execute set of queries one after the another by using new query and i need to playback all the set of queries.Is it possible to give the queries as a input file? 

    Thursday, December 13, 2012 7:39 AM
  • Hello Ramsid,

    As I said in my previous reply, there is anything wrong with coded UI test playback since new query always will be create when you click the button again. So as my personal point of view, I am afraid that you can’t accomplish the automation using coded UI test.

    When I work with one query, if I want to make the playback successfully, I need to know which sqlquery will be generate during playback(such as the one is sqlquery2 if the recorded one is sqlquery1) first. Then I need to change the sqlquery object property in the UIMap.uitest and UIMap.Designer.cs. I think that there will be a bit of work to change the UIMap each time if you want to accomplish it with coded UI test. And maybe you will get much trouble.

    If you can avoid that restraint in SQL Server and you need to execute sets of queries, I think that you may do data driven coded UI test. You can put your queries in a XML file or CVS file. Then you get different values for the SQLQuery Edit control from the data source. And coded UI test will run with multiple queries (one query per run iteration).

    For detailed information, please see: How to: Create a Data-Driven Coded UI Test

    Meanwhile if there is no proper way to avoid that restraint of SQL Server, to help you resolve this issue, you could submit this feature request:

    http://visualstudio.uservoice.com/forums/121579-visual-studio

    The Visual Studio product team is listening to user voice there. You can send your ideas/suggestions there and people can vote.

    Thanks for your understanding.

    Hope it can help you.

    Best regards,


    Amanda Zhu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, December 13, 2012 8:22 AM
    Moderator
  • Hi Am anda

    Thanks for your valuable suggestion.I am going to try by data driven method. How the SQL connection is opened and my query starts to execute? do i need to write any coding for SQL server 2005 to open and to select my database to execute the query one by one?

    Friday, December 14, 2012 6:40 AM
  • Hello Ramsid,

    I find that we can automate the SQL Server query execution successfully if we don’t save the sqlquery.sql created during recording and close the sql server directly after executing the query script. I have tried the following actions and test can run succeed:

    Click Start->All Programs->Microsoft SQL Server 2008 R2->SQL Server Management Studio->Connect to Server->Click New Query->Input some scripts into the SQLQuery1.sql->Close the Sql Server.

    If you save the sqlquery.sql, you will get the issue stated in my previous reply. New query will be created so that the recorded sqlquery will be found correctly.

    You can try the actions on SQL Server 2005 to check the result.

    In this case, you can try to put those query scripts in a CSV file and add this data source to Coded UI test so that you can execute multiple queries.

    Also you can hand code your own functions for opening SQL Server 2005 and selecting the database to execute the query one by one in coded UI test. You can record some actions on SQL Server and check its objects are created in UIMap.designer.cs. Then you can refer them to hand code your own coded UI test.

    Meanwhile you can refer to this blog that may help you: Hand-coding a Coded UI Test

    Hope my reply can help you.

    Best regards,


    Amanda Zhu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, December 14, 2012 7:37 AM
    Moderator
  • Hello Ramsid,

    I am marking my reply as answer. If my suggestion can’t help you, you can come back and unmark it as answer. If you have anything unclear, please feel free to come back and ask me.

    Best reagrds,


    Amanda Zhu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, December 17, 2012 12:56 AM
    Moderator