Join the 1-day Testing & QA Summit featuring 15+ Expert Speakers.Register for FREE! Join TestMu Conference

How to use ExecuteSqlCommand method of Ocaramba.Helpers.SqlHelper class

Best Ocaramba code snippet using Ocaramba.Helpers.SqlHelper.ExecuteSqlCommand

SqlHelper.cs

Source:SqlHelper.cs

...52        /// <example>How to use it: <code>53        /// var connectionString = "User ID=sqluser;Password=sqluserpassword;server=servername;";54        /// const string ColumnName = "AccountNumber";55        /// const string SqlQuery = "SELECT  AccountNumber as " + ColumnName + " FROM [AdventureWorks].[Sales].[Customer] where [CustomerID] in (1, 2)";56        /// var result = SqlHelper.ExecuteSqlCommand(SqlQuery, connectionString, ColumnName);57        /// </code></example>58        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "SQL injection is in this case expected.")]59        public static ICollection<string> ExecuteSqlCommand(string command, string connectionString, string column)60        {61            Logger.Trace(CultureInfo.CurrentCulture, "Execute sql query '{0}'", command);62            var resultList = new List<string>();63            using (var connection = new SqlConnection(connectionString))64            {65                connection.Open();66                using (var sqlCommand = new SqlCommand(command, connection))67                {68                    using (var sqlDataReader = sqlCommand.ExecuteReader())69                    {70                        if (!sqlDataReader.HasRows)71                        {72                            Logger.Error(CultureInfo.CurrentCulture, "No result for: {0} \n {1} \n {2}", command, connectionString, column);73                            return resultList;74                        }75                        while (sqlDataReader.Read())76                        {77                            resultList.Add(sqlDataReader[column].ToString());78                        }79                    }80                }81            }82            if (resultList.Count == 0)83            {84                Logger.Error(CultureInfo.CurrentCulture, "No result for: {0} \n {1} \n {2}", command, connectionString, column);85            }86            else87            {88                Logger.Trace(CultureInfo.CurrentCulture, "Sql results: {0}", resultList);89            }90            return resultList;91        }92        /// <summary>93        /// Method is used for execution SQL query (select) and reading each column from row.94        /// </summary>95        /// <param name="command">SQL query.</param>96        /// <param name="connectionString">Server, user, pass.</param>97        /// <param name="columns">Name of columns.</param>98        /// <returns>99        /// Dictionary of each column existed in raw.100        /// </returns>101        /// <example>How to use it: <code>102        /// var connectionString = "User ID=sqluser;Password=sqluserpassword;server=servername;";103        /// ICollection&lt;string&gt; column = new List&lt;string&gt;();104        /// column.Add("NationalIDNumber");105        /// column.Add("ContactID");106        /// const string SqlQuery = "SELECT [NationalIDNumber] as " + column.ElementAt(0) + " , [ContactID] as " + column.ElementAt(1) + " from [AdventureWorks].[HumanResources].[Employee] where EmployeeID=1";107        /// Dictionary&lt;string, string&gt; results = SqlHelper.ExecuteSqlCommand(command, GetConnectionString(server), column);108        /// </code></example>109        /// <exception cref="System.Collections.Generic.KeyNotFoundException">Exception when there is not given column in results from SQL query.</exception>110        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "SQL injection is in this case expected.")]111        public static Dictionary<string, string> ExecuteSqlCommand(string command, string connectionString, IEnumerable<string> columns)112        {113            Logger.Trace(CultureInfo.CurrentCulture, "Execute sql query '{0}'", command);114            var resultList = new Dictionary<string, string>();115            var resultTemp = new Dictionary<string, string>();116            using (var connection = new SqlConnection(connectionString))117            {118                connection.Open();119                using (var sqlCommand = new SqlCommand(command, connection))120                {121                    using (var sqlDataReader = sqlCommand.ExecuteReader())122                    {123                        if (!sqlDataReader.HasRows)124                        {125                            Logger.Error(CultureInfo.CurrentCulture, "No result for: {0} \n {1}", command, connectionString);...

SqlData.cs

Source:SqlData.cs

...41            if (orderColumnName == null)42            {43                string sqlQuery = "SELECT " + columnName + " FROM " + dbName + "." + tableName + " WHERE " + columnNameFilter + " IN " + "(" + filterValue + ")";44                Logger.Info(CultureInfo.CurrentCulture, "SQL query: {0}", sqlQuery);45                var result = SqlHelper.ExecuteSqlCommand(sqlQuery, ProjectBaseConfiguration.ConnectionString, columnName);46                foreach (var rows in result)47                {48                    Logger.Info(CultureInfo.CurrentCulture, "SQL query result: {0}", rows);49                }50                return result;51            }52            else53            {54                string sqlQuery = "SELECT " + columnName + " FROM " + dbName + "." + tableName + " WHERE " + columnNameFilter + " IN " + "(" + filterValue + ")" + " ORDER BY " + orderColumnName;55                Logger.Info(CultureInfo.CurrentCulture, "SQL query: {0}", sqlQuery);56                var result = SqlHelper.ExecuteSqlCommand(sqlQuery, ProjectBaseConfiguration.ConnectionString, columnName);57                foreach (var rows in result)58                {59                    Logger.Info(CultureInfo.CurrentCulture, "SQL query result: {0}", rows);60                }61                return result;62            }63        }64        /// <summary>65        /// Gets four columns (one row).66        /// </summary>67        /// <param name="columnFirst">Selected first column name.</param>68        /// <param name="columnSecond">Selected second column name.</param>69        /// <param name="columnThird">Selected third column name.</param>70        /// <param name="columnFourth">Selected fourth column name.</param>71        /// <param name="dbName">The DB name.</param>72        /// <param name="tableName">The table name.</param>73        /// <param name="columnNameFilter">Column name filter.</param>74        /// <param name="filterValue">Filter value.</param>75        /// <returns>Returns four columns from DB. </returns>76        public static Dictionary<string, string> GetGetFourColumnsFromDB(string columnFirst, string columnSecond, string columnThird, string columnFourth, string dbName, string tableName, string columnNameFilter, string filterValue)77        {78            ICollection<string> column = new List<string>();79            column.Add(columnFirst);80            column.Add(columnSecond);81            column.Add(columnThird);82            column.Add(columnFourth);83            string sqlQuery = "SELECT " + column.ElementAt(0) + "," + column.ElementAt(1) + "," + column.ElementAt(2) + "," + column.ElementAt(3) + " FROM " + dbName + "." + tableName + " WHERE " + columnNameFilter + " = " + filterValue;84            Logger.Info(CultureInfo.CurrentCulture, "SQL query: {0}", sqlQuery);85            Dictionary<string, string> results = SqlHelper.ExecuteSqlCommand(sqlQuery, ProjectBaseConfiguration.ConnectionString, column);86            foreach (var row in results)87            {88                Logger.Info(CultureInfo.CurrentCulture, "SQL query result: {0}", row);89            }90            return results;91        }92        /// <summary>93        /// Gets four columns (one row).94        /// </summary>95        /// <param name="columnFirst">Selected first column name.</param>96        /// <param name="columnSecond">Selected second column name.</param>97        /// <param name="columnThird">Selected third column name.</param>98        /// <param name="columnFourth">Selected fourth column name.</param>99        /// <param name="dbName">The DB name.</param>100        /// <param name="tableName">The table name.</param>101        /// <param name="columnNameFilter">Column name filter.</param>102        /// <param name="filterValue">Filter value.</param>103        /// <param name="fileName">Saved file name.</param>104        public static void WriteToFile(string columnFirst, string columnSecond, string columnThird, string columnFourth, string dbName, string tableName, string columnNameFilter, string filterValue, string fileName)105        {106            try107            {108                using StreamWriter file = new StreamWriter(ProjectBaseConfiguration.DownloadFolderPath + "\\TestFilesSql\\" + fileName, false);109                {110                    ICollection<string> column = new List<string>();111                    column.Add(columnFirst);112                    column.Add(columnSecond);113                    column.Add(columnThird);114                    column.Add(columnFourth);115                    string sqlQuery = "SELECT " + column.ElementAt(0) + "," + column.ElementAt(1) + "," + column.ElementAt(2) + "," + column.ElementAt(3) + " FROM " + dbName + "." + tableName + " WHERE " + columnNameFilter + " = " + filterValue;116                    Logger.Info(CultureInfo.CurrentCulture, "SQL query: {0}", sqlQuery);117                    Dictionary<string, string> results = SqlHelper.ExecuteSqlCommand(sqlQuery, ProjectBaseConfiguration.ConnectionString, column);118                    file.WriteLine(column.ElementAt(0) + ";" + column.ElementAt(1) + ";" + column.ElementAt(2) + ";" + column.ElementAt(3) + ";");119                    foreach (var row in results)120                    {121                        file.Write(row.Value + ";");122                        Logger.Info(CultureInfo.CurrentCulture, "Save to file: {0}", row.Value);123                    }124                    Logger.Info(CultureInfo.CurrentCulture, "File path: {0}", ProjectBaseConfiguration.DownloadFolderPath + "\\TestFilesSql\\" + fileName);125                }126            }127            catch (Exception ex)128            {129                throw new ApplicationException("Ups, something went wrong:", ex);130            }131        }...

ExecuteSqlCommand

Using AI Code Generation

1using System;2using System.Collections.Generic;3using System.Linq;4using System.Text;5using System.Threading.Tasks;6using Ocaramba;7using Ocaramba.Helpers;8using Ocaramba.Types;9using NUnit.Framework;10using OpenQA.Selenium;11{12    {13        public void TestMethod()14        {15            SqlHelper.ExecuteSqlCommand("UPDATE [dbo].[Orders] SET [ShipName] = 'Test' WHERE [ShipName] = 'Test'");16        }17    }18}19using System;20using System.Collections.Generic;21using System.Linq;22using System.Text;23using System.Threading.Tasks;24using Ocaramba;25using Ocaramba.Helpers;26using Ocaramba.Types;27using NUnit.Framework;28using OpenQA.Selenium;29{30    {31        public void TestMethod()32        {33            SqlHelper.ExecuteSqlCommand("UPDATE [dbo].[Orders] SET [ShipName] = 'Test' WHERE [ShipName] = 'Test'");34        }35    }36}37using System;38using System.Collections.Generic;39using System.Linq;40using System.Text;41using System.Threading.Tasks;42using Ocaramba;43using Ocaramba.Helpers;44using Ocaramba.Types;45using NUnit.Framework;46using OpenQA.Selenium;47{48    {49        public void TestMethod()50        {51            SqlHelper.ExecuteSqlCommand("UPDATE [dbo].[Orders] SET [ShipName] = 'Test' WHERE [ShipName] = 'Test'");52        }53    }54}55using System;56using System.Collections.Generic;57using System.Linq;58using System.Text;59using System.Threading.Tasks;60using Ocaramba;61using Ocaramba.Helpers;62using Ocaramba.Types;63using NUnit.Framework;64using OpenQA.Selenium;65{66    {

ExecuteSqlCommand

Using AI Code Generation

1using System;2using System.Collections.Generic;3using System.Linq;4using System.Text;5using System.Threading.Tasks;6using Ocaramba;7using Ocaramba.Helpers;8using Ocaramba.Types;9using NUnit.Framework;10using System.Data.SqlClient;11using System.Data;12{13    {14        public void ExecuteSqlTest()15        {16            SqlConnection sqlConnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True");17            sqlConnection.Open();18            SqlCommand sqlCommand = new SqlCommand("SELECT * FROM Customers", sqlConnection);19            sqlCommand.ExecuteNonQuery();20            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);21            DataTable dataTable = new DataTable();22            sqlDataAdapter.Fill(dataTable);23            sqlConnection.Close();24            Console.WriteLine(dataTable.Rows.Count);25        }26    }27}28using System;29using System.Collections.Generic;30using System.Linq;31using System.Text;32using System.Threading.Tasks;33using Ocaramba;34using Ocaramba.Helpers;35using Ocaramba.Types;36using NUnit.Framework;37using System.Data.SqlClient;38using System.Data;39{40    {41        public void ExecuteSqlTest()42        {43            SqlConnection sqlConnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True");44            sqlConnection.Open();45            SqlCommand sqlCommand = new SqlCommand("SELECT * FROM Customers", sqlConnection);46            sqlCommand.ExecuteNonQuery();47            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);48            DataTable dataTable = new DataTable();

ExecuteSqlCommand

Using AI Code Generation

1using Ocaramba;2using Ocaramba.Helpers;3using NUnit.Framework;4{5    {6        public void TestMethod()7        {8            SqlHelper.ExecuteSqlCommand("SELECT * FROM [dbo].[Employee]", "Server=.;Database=TestDB;Trusted_Connection=True;");9        }10    }11}12using Ocaramba;13using Ocaramba.Helpers;14using NUnit.Framework;15{16    {17        public void TestMethod()18        {19            var result = SqlHelper.ExecuteSqlQuery("SELECT * FROM [dbo].[Employee]", "Server=.;Database=TestDB;Trusted_Connection=True;");20        }21    }22}

ExecuteSqlCommand

Using AI Code Generation

1using Ocaramba;2using Ocaramba.Helpers;3using NUnit.Framework;4{5    {6        public void SqlHelperTest()7        {8            var sqlHelper = new SqlHelper();9            sqlHelper.ExecuteSqlCommand(@"INSERT INTO [dbo].[People] ([FirstName], [LastName])10                                    VALUES ('John', 'Doe')");11            sqlHelper.ExecuteSqlCommand(@"DELETE FROM [dbo].[People] WHERE [FirstName] = 'John' AND [LastName] = 'Doe'");12        }13    }14}15Ocaramba.Helpers.SqlHelper.ExecuteSqlCommand method (String, SqlParameter)

ExecuteSqlCommand

Using AI Code Generation

1using Ocaramba;2using Ocaramba.Helpers;3using System.Data.SqlClient;4using TechTalk.SpecFlow;5{6    {7        private readonly DriverContext driverContext;8        private readonly ScenarioContext scenarioContext;9        public TestSteps(DriverContext driverContext, ScenarioContext scenarioContext)10        {11            this.driverContext = driverContext;12            this.scenarioContext = scenarioContext;13        }14        [Given(@"I have a SQL Server connection")]15        public void GivenIHaveASQLServerConnection()16        {17            var sqlConnection = new SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=True");18            sqlConnection.Open();19            scenarioContext.Set(sqlConnection);20        }21        [When(@"I execute a SQL query")]22        public void WhenIExecuteASQLQuery()23        {24            var sqlConnection = scenarioContext.Get<SqlConnection>();25            var sqlCommand = new SqlCommand("SELECT * FROM Customers", sqlConnection);26            var sqlDataReader = sqlCommand.ExecuteReader();27            scenarioContext.Set(sqlDataReader);28        }29        [Then(@"I should see the results")]30        public void ThenIShouldSeeTheResults()31        {32            var sqlDataReader = scenarioContext.Get<SqlDataReader>();33            while (sqlDataReader.Read())34            {35                var name = sqlDataReader["CompanyName"].ToString();36                System.Console.WriteLine(name);37            }38        }39        [Given(@"I have a SQL Server connection and a SQL query")]40        public void GivenIHaveASQLServerConnectionAndASQLQuery()41        {42            var sqlConnection = new SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=True");43            sqlConnection.Open();44            scenarioContext.Set(sqlConnection);45            var sqlCommand = new SqlCommand("SELECT * FROM Customers", sqlConnection);46            scenarioContext.Set(sqlCommand);47        }48        [When(@"I execute the SQL query")]49        public void WhenIExecuteTheSQLQuery()50        {51            var sqlCommand = scenarioContext.Get<SqlCommand>();52            var sqlDataReader = sqlCommand.ExecuteReader();53            scenarioContext.Set(sqlDataReader);54        }55        [Given(@"I have a SQL Server connection and a SQL query with parameters")]56        public void GivenIHaveASQLServerConnectionAndASQLQueryWithParameters()57        {58            var sqlConnection = new SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=True");59            sqlConnection.Open();60            scenarioContext.Set(sqlConnection);

ExecuteSqlCommand

Using AI Code Generation

1var sqlHelper = new SqlHelper();2sqlHelper.ExecuteSqlCommand("INSERT INTO testtable VALUES ('test')");3var sqlHelper = new SqlHelper();4sqlHelper.ExecuteSqlCommand("INSERT INTO testtable VALUES ('test')");5var sqlHelper = new SqlHelper();6sqlHelper.ExecuteSqlCommand("INSERT INTO testtable VALUES ('test')");7var sqlHelper = new SqlHelper();8sqlHelper.ExecuteSqlCommand("INSERT INTO testtable VALUES ('test')");9var sqlHelper = new SqlHelper();10sqlHelper.ExecuteSqlCommand("INSERT INTO testtable VALUES ('test')");11var sqlHelper = new SqlHelper();12sqlHelper.ExecuteSqlCommand("INSERT INTO testtable VALUES ('test')");13var sqlHelper = new SqlHelper();14sqlHelper.ExecuteSqlCommand("INSERT INTO testtable VALUES ('test')");15var sqlHelper = new SqlHelper();16sqlHelper.ExecuteSqlCommand("INSERT INTO testtable VALUES ('test')");17var sqlHelper = new SqlHelper();18sqlHelper.ExecuteSqlCommand("INSERT INTO testtable VALUES ('test')");19var sqlHelper = new SqlHelper();20sqlHelper.ExecuteSqlCommand("INSERT INTO testtable VALUES ('test')");21var sqlHelper = new SqlHelper();22sqlHelper.ExecuteSqlCommand("INSERT INTO testtable VALUES ('test')");

ExecuteSqlCommand

Using AI Code Generation

1using System;2using System.Collections.Generic;3using System.Linq;4using System.Text;5using System.Threading.Tasks;6using Ocaramba;7using Ocaramba.Extensions;8using Ocaramba.Helpers;9using Ocaramba.Types;10using NUnit.Framework;11using OpenQA.Selenium;12using OpenQA.Selenium.Remote;13{14    {15        public InsertRecord(DriverContext driverContext) : base(driverContext)16        {17        }18        public void InsertRecordTest()19        {20            string sql = "INSERT INTO [dbo].[Employees] ([FirstName], [LastName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone], [Extension], [Photo], [Notes], [ReportsTo], [PhotoPath]) VALUES ('John', 'Doe', 'Vice President, Sales', 'Mr.', '1968-07-23', '2002-05-01', '507 - 20th Ave. E. Apt. 2A', 'Seattle', 'WA', '98122', 'USA', '(206) 555-9857', '5467', NULL, NULL, NULL, 'images/employees/2.bmp')";21            SqlHelper.ExecuteSqlCommand(sql, this.DriverContext);22            sql = "SELECT COUNT(*) FROM [dbo].[Employees] WHERE FirstName = 'John' AND LastName = 'Doe'";23            int count = (int)SqlHelper.ExecuteScalarSqlCommand(sql, this.DriverContext);24            Assert.AreEqual(1, count);25        }26    }27}28using System;29using System.Collections.Generic;30using System.Linq;31using System.Text;32using System.Threading.Tasks;33using Ocaramba;34using Ocaramba.Extensions;

ExecuteSqlCommand

Using AI Code Generation

1var sqlHelper = new SqlHelper();2sqlHelper.ExecuteSqlCommand(@"C:\Users\user\Downloads\2.sql");3var sqlHelper = new SqlHelper();4sqlHelper.ExecuteSqlCommand(@"C:\Users\user\Downloads\3.sql");5var sqlHelper = new SqlHelper();6sqlHelper.ExecuteSqlCommand(@"C:\Users\user\Downloads\4.sql");7var sqlHelper = new SqlHelper();8sqlHelper.ExecuteSqlCommand(@"C:\Users\user\Downloads\5.sql");9var sqlHelper = new SqlHelper();10sqlHelper.ExecuteSqlCommand(@"C:\Users\user\Downloads\6.sql");11var sqlHelper = new SqlHelper();12sqlHelper.ExecuteSqlCommand(@"C:\Users\user\Downloads\7.sql");13var sqlHelper = new SqlHelper();14sqlHelper.ExecuteSqlCommand(@"C:\Users\user\Downloads\8.sql");15var sqlHelper = new SqlHelper();16sqlHelper.ExecuteSqlCommand(@"C:\Users\user\Downloads\9.sql");17var sqlHelper = new SqlHelper();18sqlHelper.ExecuteSqlCommand(@"C:\Users\user\Downloads\10.sql");

Automation Testing Tutorials

Learn to execute automation testing from scratch with LambdaTest Learning Hub. Right from setting up the prerequisites to run your first automation test, to following best practices and diving deeper into advanced test scenarios. LambdaTest Learning Hubs compile a list of step-by-step guides to help you be proficient with different test automation frameworks i.e. Selenium, Cypress, TestNG etc.