How to use SqlHelper class of Ocaramba.Helpers package

Best Ocaramba code snippet using Ocaramba.Helpers.SqlHelper

SqlHelper.cs

Source:SqlHelper.cs

1ï»¿// <copyright file="SqlHelper.cs" company="Objectivity Bespoke Software Specialists">2// Copyright (c) Objectivity Bespoke Software Specialists. All rights reserved.3// </copyright>4// <license>5//     The MIT License (MIT)6//     Permission is hereby granted, free of charge, to any person obtaining a copy7//     of this software and associated documentation files (the "Software"), to deal8//     in the Software without restriction, including without limitation the rights9//     to use, copy, modify, merge, publish, distribute, sublicense, and/or sell10//     copies of the Software, and to permit persons to whom the Software is11//     furnished to do so, subject to the following conditions:12//     The above copyright notice and this permission notice shall be included in all13//     copies or substantial portions of the Software.14//     THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR15//     IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,16//     FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE17//     AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER18//     LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,19//     OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE20//     SOFTWARE.21// </license>22namespace Ocaramba.Helpers23{24    using System.Collections.Generic;25    using System.Data.SqlClient;26    using System.Diagnostics.CodeAnalysis;27    using System.Globalization;28    using NLog;29    /// <summary>30    /// Class is used for execution SQL queries and reading data from database.31    /// </summary>32    public static class SqlHelper33    {34        /// <summary>35        /// NLog logger handle.36        /// </summary>37#if net4738        private static readonly NLog.Logger Logger = LogManager.GetCurrentClassLogger();39#endif40#if netcoreapp2_241        private static readonly NLog.Logger Logger = NLog.Web.NLogBuilder.ConfigureNLog("nlog.config").GetCurrentClassLogger();42#endif43        /// <summary>44        /// Method is used for execution SQL query (select) and reading each row from column.45        /// </summary>46        /// <param name="command">SQL query.</param>47        /// <param name="connectionString">Server, user, pass.</param>48        /// <param name="column">Name of column.</param>49        /// <returns>50        /// Collection of each row existed in column.51        /// </returns>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())...

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        }...

SqlHelper

Using AI Code Generation

1using Ocaramba;2using Ocaramba.Helpers;3using Ocaramba.Types;4using NUnit.Framework;5using System;6using System.Collections.Generic;7using System.Linq;8using System.Text;9using System.Threading.Tasks;10{11    {12        public void TestSqlHelper()13        {14            var connectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True;";15            var query = "SELECT COUNT(*) FROM [dbo].[TestTable]";16            var result = SqlHelper.ExecuteScalar(connectionString, query);17            Assert.AreEqual(1, result);18        }19    }20}21Ocaramba (in Ocaramba.dll) Version:

SqlHelper

Using AI Code Generation

1using Ocaramba;2using Ocaramba.Helpers;3using Ocaramba.Types;4using NUnit.Framework;5using System;6using System.Collections.Generic;7using System.Linq;8using System.Text;9using System.Threading.Tasks;10{11    {12        public void TestMethod1()13        {14            var sqlHelper = new SqlHelper("Server=.;Database=TestDB;Trusted_Connection=True;");15            var result = sqlHelper.ExecuteQuery("Select * from Users");16            Console.WriteLine(result.Count());17            foreach (var item in result)18            {19                Console.WriteLine(item["UserName"]);20            }21        }22    }23}

SqlHelper

Using AI Code Generation

1using Ocaramba;2using Ocaramba.Helpers;3using Ocaramba.Types;4using System;5using System.Collections.Generic;6using System.Data;7using System.Linq;8using System.Text;9using System.Threading.Tasks;10using NUnit.Framework;11using NUnit.Framework.Interfaces;12using Ocaramba.Extensions;13using Ocaramba.UITests;14using Ocaramba.UITests.PageObjects;15using Ocaramba.UITests.TestingFramework;16using Ocaramba.UITests.TestingFramework.Attributes;17using Ocaramba.UITests.TestingFramework.Controls;18using Ocaramba.UITests.TestingFramework.Enums;19using Ocaramba.UITests.TestingFramework.Extensions;20using Ocaramba.UITests.TestingFramework.Logging;21using Ocaramba.UITests.TestingFramework.TestRunner;22using Ocaramba.UITests.TestingFramework.Attributes;23using Ocaramba.UITests.TestingFramework.Controls;24using Ocaramba.UITests.TestingFramework.Enums;25using Ocaramba.UITests.TestingFramework.Extensions;26using Ocaramba.UITests.TestingFramework.Logging;27using Ocaramba.UITests.TestingFramework.TestRunner;28{29    [TestFixtureSource(typeof(ParallelConfig), "BrowserToRunWith")]30    {31        public ParallelTest2(DriverContext driverContext)32            : base(driverContext)33        {34        }35        public void TestMethod()36        {37            var sqlHelper = new SqlHelper();38            var data = sqlHelper.ExecuteQuery("SELECT * FROM [dbo].[TestTable]");39            foreach (DataRow row in data.Rows)40            {41                Logger.Info(row["ID"].ToString());42                Logger.Info(row["Name"].ToString());43            }44        }45    }46}

SqlHelper

Using AI Code Generation

1using Ocaramba;2using Ocaramba.Helpers;3using Ocaramba.Types;4using OpenQA.Selenium;5using System;6using System.Collections.Generic;7using System.Linq;8using System.Text;9using System.Threading.Tasks;10using NUnit.Framework;11{12    {13        public void Test1()14        {15            string connString = SqlHelper.GetConnectionString("DefaultConnection");16            string query = "SELECT * FROM dbo.Students";17            var data = SqlHelper.GetDataTable(connString, query);18            var data1 = SqlHelper.GetDataTable(connString, query, 2);19        }20    }21}

SqlHelper

Using AI Code Generation

1using Ocaramba;2using Ocaramba.Helpers;3using Ocaramba.Tests.NUnit;4using NUnit.Framework;5{6    {7        public void SqlHelperTest()8        {9            var sql = "SELECT * FROM [Table]";10            var sqlHelper = new SqlHelper();11            var dataTable = sqlHelper.ExecuteQuery(sql);12            Assert.IsNotNull(dataTable);13        }14    }15}16using Ocaramba;17using Ocaramba.Helpers;18using Ocaramba.Tests.NUnit;19using NUnit.Framework;20{21    {22        public void SqlHelperTest()23        {24            var sql = "SELECT * FROM [Table]";25            var sqlHelper = new SqlHelper();26            var dataTable = sqlHelper.ExecuteQuery(sql);27            Assert.IsNotNull(dataTable);28        }29    }30}31using Ocaramba;32using Ocaramba.Helpers;33using Ocaramba.Tests.NUnit;34using NUnit.Framework;35{36    {37        public void SqlHelperTest()38        {39            var sql = "SELECT * FROM [Table]";40            var sqlHelper = new SqlHelper();41            var dataTable = sqlHelper.ExecuteQuery(sql);42            Assert.IsNotNull(dataTable);43        }44    }45}46using Ocaramba;47using Ocaramba.Helpers;48using Ocaramba.Tests.NUnit;49using NUnit.Framework;50{51    {52        public void SqlHelperTest()53        {54            var sql = "SELECT * FROM [Table]";55            var sqlHelper = new SqlHelper();56            var dataTable = sqlHelper.ExecuteQuery(sql);57            Assert.IsNotNull(dataTable);58        }59    }60}

SqlHelper

Using AI Code Generation

1using Ocaramba;2using Ocaramba.Helpers;3using Ocaramba.Types;4{5    {6        public void Test()7        {8            SqlHelper sqlHelper = new SqlHelper("Server=.;Database=Test;Trusted_Connection=True;");9            string query = "select * from table1";10            var result = sqlHelper.ExecuteQuery(query);11        }12    }13}

SqlHelper

Using AI Code Generation

1public void TestMethod1()2{3    var sqlHelper = new SqlHelper("connectionString");4    var result = sqlHelper.ExecuteScalar("select * from table");5}6public void TestMethod1()7{8    var sqlHelper = new SqlHelper("connectionString");9    var result = sqlHelper.ExecuteScalar("select * from table");10}11public void TestMethod1()12{13    var sqlHelper = new SqlHelper("connectionString");14    var result = sqlHelper.ExecuteScalar("select * from table");15}16public void TestMethod1()17{18    var sqlHelper = new SqlHelper("connectionString");19    var result = sqlHelper.ExecuteScalar("select * from table");20}21public void TestMethod1()22{23    var sqlHelper = new SqlHelper("connectionString");24    var result = sqlHelper.ExecuteScalar("select * from table");25}26public void TestMethod1()27{28    var sqlHelper = new SqlHelper("connectionString");29    var result = sqlHelper.ExecuteScalar("select * from table");30}31public void TestMethod1()32{33    var sqlHelper = new SqlHelper("connectionString");34    var result = sqlHelper.ExecuteScalar("select * from table");35}36public void TestMethod1()37{38    var sqlHelper = new SqlHelper("connectionString");39    var result = sqlHelper.ExecuteScalar("select

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.