Need Help in displaying the SQL Result

Best practices, code snippets for common functionality, examples, and guidelines.
ravihraj
Posts: 6
Joined: Fri Aug 26, 2016 4:48 pm

Need Help in displaying the SQL Result

Post by ravihraj » Wed Sep 28, 2016 6:06 pm

Hi team,

I have created the SQL connection and i am not able to display the result of the SQL Query. I am pasting my query below:

/*
* Created by Ranorex
* User: HAM5768
* Date: 9/28/2016
* Time: 11:37 AM
*
* To change this template use Tools | Options | Coding | Edit Standard Headers.
*/
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 Ranorex;
using Ranorex.Core;
using Ranorex.Core.Testing;

namespace MyFirstProject.RepoFiles
{
/// <summary>
/// Description of sqlconn.
/// </summary>
[TestModule("E05AB488-75B7-46D3-9F5D-A25205DD1443", ModuleType.UserCode, 1)]
public class sqlconn : ITestModule
{
/// <summary>
/// Constructs a new instance.
/// </summary>
public sqlconn()
{
// Do not delete - a parameterless constructor is required!
}

/// <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 sqlConn = new SqlConnection();
sqlConn.ConnectionString = "server=NTDBTH7575M00;database=MARKITEDM_DEMO_DX;Integrated Security=True;Max Pool Size=200;Connect Timeout=60";
SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = sqlConn;
sqlConn.Open();
//sqlComm.CommandText= "Select Top 20 CCXRefID from [TableName] where [ColumnName] = '" + varParm1 + "' and [ColumnName] = '" + varParm2 + "';

sqlComm.CommandText = "Select Top 20 [RoleId] FROM [MARKITEDM_DEMO_DX].[dbo].[RefRoles]";
////to return only 1 value:
object result = sqlComm.ExecuteScalar();


sqlConn.Close();





}
}
}


Help needed to display the result of my Query in the code . How do i do it ? please help me

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

Re: Need Help in displaying the SQL Result

Post by krstcs » Thu Sep 29, 2016 3:17 pm

First, you should not be using ExecuteScalar as it only returns a SINGLE VALUE (Scalar). You need to return a table, in this case with only one column, but up to 20 entries (TOP 20). So you should probably be using a .NET DataTable and filling it with the result of the query. There is a lot of information on doing this if you search the web for ".NET fill datatable from sql".

Second, once you have the datatable, you can use a foreach loop on the Rows collection of the table and get the values from each row.

Code: Select all

foreach (System.Data.Row r in myDataTable.Rows) {
    Report.Info(r.Values[0].ToString());
}
NOTE: You will need to adjust this code to your specific use case.
Shortcuts usually aren't...