System.Data.SqlClient.SqlCommand.ExecuteReader()

public bool Execute(bool nonQuery, string queryStringFormat, params object[] args)
        {
            bool result = false;
            SqlTransaction sqlTran = null;
            SqlCommand cmd = null;
            string query;

            ExecPerf perf = new ExecPerf();

            affected = 0;

            if (!Ready)
                return false;

            try
            {
                if (nonQuery)
                    sqlTran = conn.BeginTransaction();

                query = string.Format(queryStringFormat, args);

                cmd = new SqlCommand(query, this.conn, sqlTran);

                if (nonQuery)
                {
                    perf.Begin();
                    affected = cmd.ExecuteNonQuery();
                    perf.Time("SQL execution", TimeSpan.FromSeconds(3));
                }
                else
                {
                    CloseReader();

                    perf.Begin();
                    reader = cmd.ExecuteReader();
                    perf.Time("sql execution",TimeSpan.FromSeconds(8));
                    affected = reader.RecordsAffected;
                }

                if (sqlTran != null)
                    sqlTran.Commit();

                result = true;
            }
            catch (Exception e)
            {
                Log.Error("Sql exec error: {0}", e.Message);

                if (sqlTran != null)
                    sqlTran.Rollback();
            }

            return result;
        }

public override void OnClick(AddinDesignerEventArgs e)
        {
            try
            {
                StringBuilder result;
                INamedElement namedElement = e.SelectedElement as INamedElement;

                if (namedElement is Tables.ITable)
                {
                    result = this.GenerateFromTable(namedElement as Tables.ITable, true);
                }
                else if (namedElement is Tables.ITableExtension)
                {
                    result = this.GenerateFromTableExtension(namedElement as Tables.ITableExtension, true);
                }
                else if (namedElement is Tables.IRelation)
                {
                    result = this.GenerateFromTableRelations(e.SelectedElements.OfType<Tables.IRelation>());

                    var selectedRelations = e.SelectedElements.OfType<Tables.IRelation>();
                    if (selectedRelations.Any())
                    {
                        result = this.GenerateFromTableRelations(selectedRelations);
                    }
                }
                else if (namedElement is DataEnreplacedies.IDataEnreplacedyView)
                {
                    result = this.GenerateFromDataEnreplacedy(namedElement as DataEnreplacedies.IDataEnreplacedyView, true);
                }
                else
                {
                    throw new NotImplementedException($"Element {e.SelectedElement.ToString()} is not supported.");
                }

                if (result != null)
                {
                    string message = string.Empty;
                    int counter = 0;

                    message += $"Counting for " + tableName;
                    message += "\n\n";

                    using (SqlConnection conn = new SqlConnection(BusinessDatabaseConnectionString))
                    {
                        var query = result.ToString();

                        query = query.Replace("\n", " ");
                        query = query.Replace("\r", " ");
                        query = query.Replace(" GO ", "");

                        conn.Open();

                        using (SqlCommand command = new SqlCommand(query, conn))
                        {
                            using (SqlDataReader reader = command.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    if (reader.FieldCount == 1)
                                    {
                                        string counterLocStr = reader["COUNTER"].ToString();

                                        counter += int.Parse(counterLocStr);
                                    }
                                    else
                                    {
                                        string dataAreaId = reader["DATAAREAID"].ToString();
                                        string counterLocStr = reader["COUNTER"].ToString();

                                        message += $"{dataAreaId.ToUpper()}: {int.Parse(counterLocStr)}";

                                        counter += int.Parse(counterLocStr);
                                    }
                                }
                            }
                        }

                        conn.Close();
                    }

                    message += "\n\n";
                    message += $"Total: {counter}";
                    message += "\n\n";
                    message += "==================  USED QUERY  ===================\n";
                    message += result.ToString();
                    message += "===============================================";
                    message += "\n\n";
                    message += "Do you want to open this query in MSSQL Management Studio?";

                    if (CoreUtility.PromptYesNo(message, "Counting records"))
                    {
                        // Save the SQL file and open it in SQL management studio. 
                        string temporaryFileName = Path.GetTempFileName();

                        // Rename and move
                        var sqlFileName = temporaryFileName.Replace(".tmp", ".sql");
                        File.Move(temporaryFileName, sqlFileName);

                        // Store the script in the file
                        File.AppendAllText(sqlFileName, result.ToString());

                        //var dte = CoreUtility.ServiceProvider.GetService(typeof(EnvDTE.DTE)) as EnvDTE.DTE;
                        //dte.ExecuteCommand("File.OpenFile", sqlFileName);

                        Process sqlManagementStudio = new Process();
                        sqlManagementStudio.StartInfo.FileName = sqlFileName;
                        sqlManagementStudio.StartInfo.UseShellExecute = true;
                        sqlManagementStudio.StartInfo.WindowStyle = ProcessWindowStyle.Normal;
                        sqlManagementStudio.Start();
                    }
                }
            }
            catch (Exception ex)
            {
                CoreUtility.HandleExceptionWithErrorMessage(ex);
            }
        }

[MethodImpl(MethodImplOptions.NoInlining)]
        private static void Test()
        {
            ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["Database"];
            using (SqlConnection connection = new SqlConnection(connectionString.ConnectionString))
            {
                connection.Open();
                const string commandText = "SELECT * FROM [dbo].[Table]";
                SqlCommand command = new SqlCommand(commandText, connection);
                Console.WriteLine(command.ToString());
                int count = 0;
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        count++;
                    }
                }
                Console.Write(count);
            }
        }

private static List<SalesDTO> GetDataFromSQL(string connectionStr)
        {
            var ret = new List<SalesDTO>();
            // lets connect to the AdventureWorks sample database for some data
            using (SqlConnection sqlConn = new SqlConnection(connectionStr))
            {
                sqlConn.Open();
                using (SqlCommand sqlCmd = new SqlCommand("select replacedle, FirstName, MiddleName, LastName, SubTotal, OrderDate, TaxAmt, Freight, TotalDue  from Sales.SalesOrderHeader s inner join HumanResources.Employee h on s.SalesPersonID = h.BusinessEnreplacedyID inner join Person.Person c on c.BusinessEnreplacedyID = h.BusinessEnreplacedyID order by LastName, FirstName, MiddleName;", sqlConn))
                {
                    using (SqlDataReader sqlReader = sqlCmd.ExecuteReader())
                    {
                        //Get the data and fill rows 5 onwards
                        while (sqlReader.Read())
                        {
                            ret.Add(new SalesDTO
                            {
                                replacedle = sqlReader["replacedle"].ToString(),
                                FirstName=sqlReader["FirstName"].ToString(),
                                MiddleName=sqlReader["MiddleName"].ToString(),
                                LastName=sqlReader["LastName"].ToString(),
                                OrderDate = (DateTime)sqlReader["OrderDate"],
                                SubTotal = (decimal)sqlReader["SubTotal"],
                                Tax=(decimal)sqlReader["TaxAmt"],
                                Freight=(decimal)sqlReader["Freight"]
                            });
                        }
                    }
                }
            }
            return ret;
        }

public static string RunSample3(string connectionString)
		{
            var file = Utils.GetFileInfo("Sample3.xlsx");
            // ok, we can run the real code of the sample now
            using (ExcelPackage xlPackage = new ExcelPackage(file))
            {
                // get handle to the existing worksheet
                ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Sales");
                var namedStyle = xlPackage.Workbook.Styles.CreateNamedStyle("HyperLink");   //This one is language dependent
                namedStyle.Style.Font.UnderLine = true;
                namedStyle.Style.Font.Color.SetColor(Color.Blue);
                if (worksheet != null)
                {
                    const int startRow = 5;
                    int row = startRow;
                    //Create Headers and format them 
                    worksheet.Cells["A1"].Value = "AdventureWorks Inc.";
                    using (ExcelRange r = worksheet.Cells["A1:G1"])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("Britannic Bold", 22, FontStyle.Italic));
                        r.Style.Font.Color.SetColor(Color.White);
                        r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
                        r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                        r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
                    }
                    worksheet.Cells["A2"].Value = "Year-End Sales Report";
                    using (ExcelRange r = worksheet.Cells["A2:G2"])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("Britannic Bold", 18, FontStyle.Italic));
                        r.Style.Font.Color.SetColor(Color.Black);
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
                        r.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
                    }

                    worksheet.Cells["A4"].Value = "Name";
                    worksheet.Cells["B4"].Value = "Job replacedle";
                    worksheet.Cells["C4"].Value = "Region";
                    worksheet.Cells["D4"].Value = "Monthly Quota";
                    worksheet.Cells["E4"].Value = "Quota YTD";
                    worksheet.Cells["F4"].Value = "Sales YTD";
                    worksheet.Cells["G4"].Value = "Quota %";
                    worksheet.Cells["A4:G4"].Style.Fill.PatternType = ExcelFillStyle.Solid;
                    worksheet.Cells["A4:G4"].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
                    worksheet.Cells["A4:G4"].Style.Font.Bold = true;


                    // lets connect to the AdventureWorks sample database for some data
                    using (SqlConnection sqlConn = new SqlConnection(connectionString))
                    {
                        sqlConn.Open();
                        using (SqlCommand sqlCmd = new SqlCommand("select LastName + ', ' + FirstName AS [Name], EmailAddress, Jobreplacedle, CountryRegionName, ISNULL(SalesQuota,0) AS SalesQuota, ISNULL(SalesQuota,0)*12 AS YearlyQuota, SalesYTD from Sales.vSalesPerson ORDER BY SalesYTD desc", sqlConn))
                        {
                            using (SqlDataReader sqlReader = sqlCmd.ExecuteReader())
                            {
                                // get the data and fill rows 5 onwards
                                while (sqlReader.Read())
                                {
                                    int col = 1;
                                    // our query has the columns in the right order, so simply
                                    // iterate through the columns
                                    for (int i = 0; i < sqlReader.FieldCount; i++)
                                    {
                                        // use the email address as a hyperlink for column 1
                                        if (sqlReader.GetName(i) == "EmailAddress")
                                        {
                                            // insert the email address as a hyperlink for the name
                                            string hyperlink = "mailto:" + sqlReader.GetValue(i).ToString();
                                            worksheet.Cells[row, 1].Hyperlink = new Uri(hyperlink, UriKind.Absolute);
                                        }
                                        else
                                        {
                                            // do not bother filling cell with blank data (also useful if we have a formula in a cell)
                                            if (sqlReader.GetValue(i) != null)
                                                worksheet.Cells[row, col].Value = sqlReader.GetValue(i);
                                            col++;
                                        }
                                    }
                                    row++;
                                }
                                sqlReader.Close();

                                worksheet.Cells[startRow, 1, row - 1, 1].StyleName = "HyperLink";
                                worksheet.Cells[startRow, 4, row - 1, 6].Style.Numberformat.Format = "[$$-409]#,##0";
                                worksheet.Cells[startRow, 7, row - 1, 7].Style.Numberformat.Format = "0%";

                                worksheet.Cells[startRow, 7, row - 1, 7].FormulaR1C1 = "=IF(RC[-2]=0,0,RC[-1]/RC[-2])";

                                //Set column width
                                worksheet.Column(1).Width = 25;
                                worksheet.Column(2).Width = 28;
                                worksheet.Column(3).Width = 18;
                                worksheet.Column(4).Width = 12;
                                worksheet.Column(5).Width = 10;
                                worksheet.Column(6).Width = 10;
                                worksheet.Column(7).Width = 12;
                            }
                        }
                        sqlConn.Close();
                    }

                    // lets set the header text 
                    worksheet.HeaderFooter.OddHeader.CenteredText = "AdventureWorks Inc. Sales Report";
                    // add the page number to the footer plus the total number of pages
                    worksheet.HeaderFooter.OddFooter.RightAlignedText =
                        string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
                    // add the sheet name to the footer
                    worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
                    // add the file path to the footer
                    worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;
                }
                // we had better add some doreplacedent properties to the spreadsheet 

                // set some core property values
                xlPackage.Workbook.Properties.replacedle = "Sample 3";
                xlPackage.Workbook.Properties.Author = "John Tunnicliffe";
                xlPackage.Workbook.Properties.Subject = "ExcelPackage Samples";
                xlPackage.Workbook.Properties.Keywords = "Office Open XML";
                xlPackage.Workbook.Properties.Category = "ExcelPackage Samples";
                xlPackage.Workbook.Properties.Comments = "This sample demonstrates how to create an Excel 2007 file from scratch using the Packaging API and Office Open XML";

                // set some extended property values
                xlPackage.Workbook.Properties.Company = "AdventureWorks Inc.";
                xlPackage.Workbook.Properties.HyperlinkBase = new Uri("http://www.codeplex.com/MSFTDBProdSamples");

                // set some custom property values
                xlPackage.Workbook.Properties.SetCustomPropertyValue("Checked by", "John Tunnicliffe");
                xlPackage.Workbook.Properties.SetCustomPropertyValue("EmployeeID", "1147");
                xlPackage.Workbook.Properties.SetCustomPropertyValue("replacedemblyName", "ExcelPackage");

                // save the new spreadsheet
                xlPackage.Save();
            }

			return file.FullName;
		}

public static string RunSample4(string connectionString, FileInfo template)
        {
            using (ExcelPackage p = new ExcelPackage(template, true))
            {
                //Set up the headers
                ExcelWorksheet ws = p.Workbook.Worksheets[1];
                ws.Cells["A20"].Value = "Date";
                ws.Cells["B20"].Value = "EOD Rate";
                ws.Cells["B20:D20"].Merge = true;
                ws.Cells["E20"].Value = "Change";
                ws.Cells["E20:G20"].Merge = true;
                ws.Cells["B20:E20"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
                using (ExcelRange row = ws.Cells["A20:G20"]) 
                {
                    row.Style.Fill.PatternType = ExcelFillStyle.Solid;
                    row.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23,55,93));
                    row.Style.Font.Color.SetColor(Color.White);
                    row.Style.Font.Bold = true;
                }
                ws.Cells["B21"].Value = "USD/JPY";
                ws.Cells["C21"].Value = "USD/EUR";
                ws.Cells["D21"].Value = "USD/GBP";
                ws.Cells["E21"].Value = "USD/JPY";
                ws.Cells["F21"].Value = "USD/EUR";
                ws.Cells["G21"].Value = "USD/GBP";
                using (ExcelRange row = ws.Cells["A21:G21"])
                {
                    row.Style.Fill.PatternType = ExcelFillStyle.Solid;
                    row.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
                    row.Style.Font.Color.SetColor(Color.Black);
                    row.Style.Font.Bold = true;
                }

                int startRow = 22;
                //Connect to the database and fill the data
                using (SqlConnection sqlConn = new SqlConnection(connectionString))
                {
                    int row = startRow;
                    sqlConn.Open();
                    using (SqlCommand sqlCmd = new SqlCommand("SELECT CurrencyRateDate, SUM(Case when ToCurrencyCode = 'JPY' Then EndOfDayRate Else 0 END) AS [JPY], SUM(Case when ToCurrencyCode = 'EUR' Then EndOfDayRate Else 0 END) AS [EUR], SUM(Case when ToCurrencyCode = 'GBP' Then EndOfDayRate Else 0 END) AS [GBP] FROM [Sales].[CurrencyRate]  where [FromCurrencyCode]='USD' AND ToCurrencyCode in ('JPY', 'EUR', 'GBP') GROUP BY CurrencyRateDate  ORDER BY CurrencyRateDate", sqlConn))
                    {
                        using (SqlDataReader sqlReader = sqlCmd.ExecuteReader())
                        {                            
                            // get the data and fill rows 22 onwards
                            while (sqlReader.Read())
                            {
                                ws.Cells[row, 1].Value = sqlReader[0];
                                ws.Cells[row, 2].Value = sqlReader[1];
                                ws.Cells[row, 3].Value = sqlReader[2];
                                ws.Cells[row, 4].Value = sqlReader[3];
                                row++;
                            }
                        }
                        //Set the numberformat
                        ws.Cells[startRow, 1, row - 1, 1].Style.Numberformat.Format = "yyyy-mm-dd";
                        ws.Cells[startRow, 2, row - 1, 4].Style.Numberformat.Format = "#,##0.0000";
                        //Set the Formulas 
                        ws.Cells[startRow + 1, 5, row - 1, 7].Formula = string.Format("B${0}/B{1}-1", startRow, startRow + 1);
                        ws.Cells[startRow, 5, row - 1, 7].Style.Numberformat.Format = "0.00%";
                    }

                    //Set the series for the chart. The series must exist in the template or the program will crash.
                    ExcelChart chart = ((ExcelChart)ws.Drawings["SampleChart"]); 
                    chart.replacedle.Text = "Exchange rate %";
                    chart.Series[0].Header = "USD/JPY";
                    chart.Series[0].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow+1, 1, row - 1, 1);
                    chart.Series[0].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 5, row - 1, 5);

                    chart.Series[1].Header = "USD/EUR";
                    chart.Series[1].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
                    chart.Series[1].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 6, row - 1, 6);

                    chart.Series[2].Header = "USD/GBP";
                    chart.Series[2].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
                    chart.Series[2].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 7, row - 1, 7);
                }
                
                //Get the doreplacedet as a byte array from the stream and save it to disk.  (This is useful in a webapplication) ... 
                Byte[] bin = p.GetAsByteArray();

                FileInfo file = Utils.GetFileInfo("sample4.xlsx");
                File.WriteAllBytes(file.FullName, bin);
                return file.FullName;
            }
        }

public static string RunSample4(string connectionString, FileInfo template)
        {
            using (ExcelPackage p = new ExcelPackage(template, true))
            {
                //Set up the headers
                ExcelWorksheet ws = p.Workbook.Worksheets[0];
                ws.Cells["A20"].Value = "Date";
                ws.Cells["B20"].Value = "EOD Rate";
                ws.Cells["B20:D20"].Merge = true;
                ws.Cells["E20"].Value = "Change";
                ws.Cells["E20:G20"].Merge = true;
                ws.Cells["B20:E20"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
                using (ExcelRange row = ws.Cells["A20:G20"]) 
                {
                    row.Style.Fill.PatternType = ExcelFillStyle.Solid;
                    row.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23,55,93));
                    row.Style.Font.Color.SetColor(Color.White);
                    row.Style.Font.Bold = true;
                }
                ws.Cells["B21"].Value = "USD/JPY";
                ws.Cells["C21"].Value = "USD/EUR";
                ws.Cells["D21"].Value = "USD/GBP";
                ws.Cells["E21"].Value = "USD/JPY";
                ws.Cells["F21"].Value = "USD/EUR";
                ws.Cells["G21"].Value = "USD/GBP";
                using (ExcelRange row = ws.Cells["A21:G21"])
                {
                    row.Style.Fill.PatternType = ExcelFillStyle.Solid;
                    row.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
                    row.Style.Font.Color.SetColor(Color.Black);
                    row.Style.Font.Bold = true;
                }

                int startRow = 22;
                //Connect to the database and fill the data
                using (SqlConnection sqlConn = new SqlConnection(connectionString))
                {
                    int row = startRow;
                    sqlConn.Open();
                    using (SqlCommand sqlCmd = new SqlCommand("SELECT CurrencyRateDate, SUM(Case when ToCurrencyCode = 'JPY' Then EndOfDayRate Else 0 END) AS [JPY], SUM(Case when ToCurrencyCode = 'EUR' Then EndOfDayRate Else 0 END) AS [EUR], SUM(Case when ToCurrencyCode = 'GBP' Then EndOfDayRate Else 0 END) AS [GBP] FROM [Sales].[CurrencyRate]  where [FromCurrencyCode]='USD' AND ToCurrencyCode in ('JPY', 'EUR', 'GBP') GROUP BY CurrencyRateDate  ORDER BY CurrencyRateDate", sqlConn))
                    {
                        using (SqlDataReader sqlReader = sqlCmd.ExecuteReader())
                        {                            
                            // get the data and fill rows 22 onwards
                            while (sqlReader.Read())
                            {
                                ws.Cells[row, 1].Value = sqlReader[0];
                                ws.Cells[row, 2].Value = sqlReader[1];
                                ws.Cells[row, 3].Value = sqlReader[2];
                                ws.Cells[row, 4].Value = sqlReader[3];
                                row++;
                            }
                        }
                        //Set the numberformat
                        ws.Cells[startRow, 1, row - 1, 1].Style.Numberformat.Format = "yyyy-mm-dd";
                        ws.Cells[startRow, 2, row - 1, 4].Style.Numberformat.Format = "#,##0.0000";
                        //Set the Formulas 
                        ws.Cells[startRow + 1, 5, row - 1, 7].Formula = string.Format("B${0}/B{1}-1", startRow, startRow + 1);
                        ws.Cells[startRow, 5, row - 1, 7].Style.Numberformat.Format = "0.00%";
                    }

                    //Set the series for the chart. The series must exist in the template or the program will crash.
                    ExcelChart chart = ((ExcelChart)ws.Drawings["SampleChart"]); 
                    chart.replacedle.Text = "Exchange rate %";
                    chart.Series[0].Header = "USD/JPY";
                    chart.Series[0].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow+1, 1, row - 1, 1);
                    chart.Series[0].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 5, row - 1, 5);

                    chart.Series[1].Header = "USD/EUR";
                    chart.Series[1].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
                    chart.Series[1].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 6, row - 1, 6);

                    chart.Series[2].Header = "USD/GBP";
                    chart.Series[2].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
                    chart.Series[2].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 7, row - 1, 7);
                }
                
                //Get the doreplacedet as a byte array from the stream and save it to disk.  (This is useful in a webapplication) ... 
                Byte[] bin = p.GetAsByteArray();

                FileInfo file = Utils.GetFileInfo("sample4.xlsx");
                File.WriteAllBytes(file.FullName, bin);
                return file.FullName;
            }
        }

public override SqlDataReader RunProcedure(string connectionString, string storedProcName, IDataParameter[] parameters)
        {
            var connection = new SqlConnection(connectionString);
            SqlDataReader returnReader;
            connection.Open();
            var command = BuildQueryCommand(connection, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            returnReader = command.ExecuteReader();
            return returnReader;
        }

public new SqlDataReader ExecuteReader()
        {
            return Intercept(() => InnerSqlCommand.ExecuteReader());
        }

public bool fillcombo()
        {
            bool issucess = false;
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                string query = "select* from tbl_suppliers";
                SqlCommand cmd = new SqlCommand(query, conn);
                SqlDataReader myreader;
                conn.Open();
                myreader = cmd.ExecuteReader();
                while (myreader.Read())
                {
                    string sname = myreader.GetString(1);
                    txtsupplier.Items.Add(sname);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);

            }
            finally
            {
                conn.Close();
            }
            return issucess;
        }

public bool fillcombo1()
        {
            bool issucess = false;
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                string query = "select* from tbl_catagories";
                SqlCommand cmd = new SqlCommand(query, conn);
                SqlDataReader myreader;
                conn.Open();
                myreader = cmd.ExecuteReader();
                while (myreader.Read())
                {
                    string sname = myreader.GetString(1);
                    txtcatagory.Items.Add(sname);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);

            }
            finally
            {
                conn.Close();
            }
            return issucess;
        }

public bool ToGetInvoiceID()
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;

            int a;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to insert Data in DAtabase
                string query = "Select Max (inv_no) From tbl_invoice";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Database Connection Open
                conn.Open();
                //To execute reader
                SqlDataReader dr = cmd.ExecuteReader();
                
                if (dr.Read())
                {
                    string val = dr[0].ToString();
                    if (val == "")
                    {
                        lblinvoiceno.Text = "1";
                    }
                    else
                    {
                        a = Convert.ToInt32(dr[0].ToString());
                        a = a + 1;
                        lblinvoiceno.Text = a.ToString();
                    }

                }
            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }

public IList<WeatherForecast> GetForecast()
        {
            var result = new List<WeatherForecast>();

            using (var sqlConnection = new SqlConnection(_configuration["ConnectionString"]))
            {
                sqlConnection.Open();

                using (var command = sqlConnection.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM " + TableName;
                    command.CommandType = CommandType.Text;

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                result.Add(new WeatherForecast
                                {
                                    City = reader.GetString(reader.GetOrdinal("City")),
                                    Temperature = reader.GetInt32(reader.GetOrdinal("Temperature"))
                                });
                            }
                        }
                    }
                }
            }

            return result;
        }

public IDataGrid ExecuteQuery
            (
                string connectionString,
                string sql
            )
        {
            Validate.IsNotEmpty(connectionString);
            Validate.IsNotEmpty(sql);

            using (var connection = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand(sql, connection))
                {
                    command.CommandType = CommandType.Text;
                    connection.Open();

                    using (var reader = command.ExecuteReader())
                    {
                        return ReadToGrid(reader);
                    }
                }
            }
        }

public IDataGrid ExecuteStoredProcedure
            (
                string connectionString,
                string procedureName,
                Dictionary<string, object> parameters
            )
        {
            Validate.IsNotEmpty(connectionString);
            Validate.IsNotEmpty(procedureName);
            Validate.IsNotNull(parameters);

            using (var connection = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand(procedureName, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    connection.Open();
                    
                    foreach (var parameter in parameters)
                    {
                        command.Parameters.Add
                        (
                            new SqlParameter
                            (
                                parameter.Key,
                                parameter.Value
                            )
                        );
                    }
                    
                    using (var reader = command.ExecuteReader())
                    {
                        return ReadToGrid(reader);
                    }
                }
            }
        }

private void ResolveColumns()
        {
            _columns = new List<QueryColumnInfo>();

            var connectionString = GetConnectionString();
            var procedureName = this.StoredProcedureName;

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (var command = new SqlCommand(procedureName, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    
                    var reader = command.ExecuteReader();
                    var schema = reader.GetSchemaTable();
                    
                    foreach (DataColumn column in schema.Columns)
                    {
                        var columnName = column.ColumnName;
                        var valueType = column.DataType;

                        var tableSchema = this.DataSource.GetSchemaTable
                        (
                            column.Table.TableName
                        );

                        var columnSchema = new DataColumnSchema
                        (
                            columnName,
                            valueType
                        );

                        _columns.Add
                        (
                            new QueryColumnInfo
                            (
                                tableSchema,
                                columnSchema
                            )
                        );
                    }
                }
            }
        }

private void LoadCollection(SqlCommand cmd)
        {
            using (var dr = new SafeDataReader(cmd.ExecuteReader()))
            {
                Fetch(dr);
                if (this.Count > 0)
                    this[0].FetchChildren(dr);
            }
        }

private void Fetch(SqlCommand cmd)
        {
            using (var dr = new SafeDataReader(cmd.ExecuteReader()))
            {
                if (dr.Read())
                {
                    Fetch(dr);
                    FetchChildren(dr);
                }
                BusinessRules.CheckRules();
            }
        }

private void Fetch(SqlCommand cmd)
        {
            using (var dr = new SafeDataReader(cmd.ExecuteReader()))
            {
                if (dr.Read())
                {
                    Fetch(dr);
                }
            }
        }

private void Fetch(SqlCommand cmd)
        {
            using (var dr = new SafeDataReader(cmd.ExecuteReader()))
            {
                if (dr.Read())
                {
                    Fetch(dr);
                }
                BusinessRules.CheckRules();
            }
        }

private void LoadCollection(SqlCommand cmd)
        {
            using (var dr = new SafeDataReader(cmd.ExecuteReader()))
            {
                Fetch(dr);
            }
        }

private void LoadCollection(SqlCommand cmd)
        {
            IsReadOnly = false;
            var rlce = RaiseListChangedEvents;
            RaiseListChangedEvents = false;
            using (var dr = new SafeDataReader(cmd.ExecuteReader()))
            {
                while (dr.Read())
                {
                    Add(new NameValuePair(
                        dr.GetInt32("RoleID"),
                        dr.GetString("RoleName")));
                }
            }
            RaiseListChangedEvents = rlce;
            IsReadOnly = true;
        }

private void LoadCollection(SqlCommand cmd)
        {
            IsReadOnly = false;
            var rlce = RaiseListChangedEvents;
            RaiseListChangedEvents = false;
            using (var dr = new SafeDataReader(cmd.ExecuteReader()))
            {
                while (dr.Read())
                {
                    Add(new NameValuePair(
                        dr.GetInt32("UserID"),
                        dr.GetString("Name")));
                }
            }
            RaiseListChangedEvents = rlce;
            IsReadOnly = true;
        }

private void LoadCollection(SqlCommand cmd)
        {
            IsReadOnly = false;
            var rlce = RaiseListChangedEvents;
            RaiseListChangedEvents = false;
            using (var dr = new SafeDataReader(cmd.ExecuteReader()))
            {
                while (dr.Read())
                {
                    Add(new NameValuePair(
                        dr.GetInt32("CircTypeID"),
                        dr.GetString("CircTypeTag")));
                }
            }
            RaiseListChangedEvents = rlce;
            IsReadOnly = true;
        }

private void LoadCollection(SqlCommand cmd)
        {
            IsReadOnly = false;
            var rlce = RaiseListChangedEvents;
            RaiseListChangedEvents = false;
            using (var dr = new SafeDataReader(cmd.ExecuteReader()))
            {
                while (dr.Read())
                {
                    Add(new NameValuePair(
                        dr.GetInt32("DecisionTypeID"),
                        dr.GetString("DecisionTypeTag")));
                }
            }
            RaiseListChangedEvents = rlce;
            IsReadOnly = true;
        }

private void Fetch(SqlCommand cmd)
        {
            using (var dr = new SafeDataReader(cmd.ExecuteReader()))
            {
                if (dr.Read())
                {
                    Fetch(dr);
                    FetchChildren(dr);
                }
            }
        }

private void LoadCollection(SqlCommand cmd)
        {
            IsReadOnly = false;
            var rlce = RaiseListChangedEvents;
            RaiseListChangedEvents = false;
            using (var dr = new SafeDataReader(cmd.ExecuteReader()))
            {
                while (dr.Read())
                {
                    Add(new NameValuePair(
                        dr.GetInt32("DocClreplacedID"),
                        dr.GetString("DocClreplacedName")));
                }
            }
            RaiseListChangedEvents = rlce;
            IsReadOnly = true;
        }

private void LoadCollection(SqlCommand cmd)
        {
            IsReadOnly = false;
            var rlce = RaiseListChangedEvents;
            RaiseListChangedEvents = false;
            using (var dr = new SafeDataReader(cmd.ExecuteReader()))
            {
                while (dr.Read())
                {
                    Add(new NameValuePair(
                        dr.GetInt32("DocTypeID"),
                        dr.GetString("DocTypeName")));
                }
            }
            RaiseListChangedEvents = rlce;
            IsReadOnly = true;
        }

private void LoadCollection(SqlCommand cmd)
        {
            IsReadOnly = false;
            var rlce = RaiseListChangedEvents;
            RaiseListChangedEvents = false;
            using (var dr = new SafeDataReader(cmd.ExecuteReader()))
            {
                while (dr.Read())
                {
                    Add(new NameValuePair(
                        dr.GetInt32("FolderTypeID"),
                        dr.GetString("FolderTypeName")));
                }
            }
            RaiseListChangedEvents = rlce;
            IsReadOnly = true;
        }

public IDataReader Fetch(int registerId)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("InterwayDocs"))
            {
                using (var cmd = new SqlCommand("dbo.GetDeliveryRegister", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@RegisterId", registerId).DbType = DbType.Int32;
                    return cmd.ExecuteReader();
                }
            }
        }

public IncomingRegisterDto Fetch(int registerId)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("InterwayDocs"))
            {
                using (var cmd = new SqlCommand("dbo.GetIncomingRegister", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@RegisterId", registerId).DbType = DbType.Int32;
                    var dr = cmd.ExecuteReader();
                    return Fetch(dr);
                }
            }
        }

private void LoadCollection(SqlCommand cmd)
        {
            IsReadOnly = false;
            var rlce = RaiseListChangedEvents;
            RaiseListChangedEvents = false;
            using (var dr = new SafeDataReader(cmd.ExecuteReader()))
            {
                while (dr.Read())
                {
                    Add(new NameValuePair(
                        dr.GetInt32("DocStatusID"),
                        dr.GetString("DocStatusName")));
                }
            }
            RaiseListChangedEvents = rlce;
            IsReadOnly = true;
        }

private void LoadCollection(SqlCommand cmd)
        {
            IsReadOnly = false;
            var rlce = RaiseListChangedEvents;
            RaiseListChangedEvents = false;
            using (var dr = new SafeDataReader(cmd.ExecuteReader()))
            {
                while (dr.Read())
                {
                    Add(new NameValuePair(
                        dr.GetInt32("FolderStatusID"),
                        dr.GetString("FolderStatusName")));
                }
            }
            RaiseListChangedEvents = rlce;
            IsReadOnly = true;
        }

public IDataReader Fetch(int registerId)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("InterwayDocs"))
            {
                using (var cmd = new SqlCommand("dbo.GetIncomingRegister", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@RegisterId", registerId).DbType = DbType.Int32;
                    return cmd.ExecuteReader();
                }
            }
        }

public IDataReader Fetch(int registerId)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("InterwayDocs"))
            {
                using (var cmd = new SqlCommand("dbo.GetOutgoingRegister", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@RegisterId", registerId).DbType = DbType.Int32;
                    return cmd.ExecuteReader();
                }
            }
        }

private void LoadCollection(SqlCommand cmd)
        {
            IsReadOnly = false;
            var rlce = RaiseListChangedEvents;
            RaiseListChangedEvents = false;
            using (var dr = new SafeDataReader(cmd.ExecuteReader()))
            {
                while (dr.Read())
                {
                    Add(new NameValuePair(
                        dr.GetInt32("ProductTypeId"),
                        dr.GetString("Name")));
                }
            }
            RaiseListChangedEvents = rlce;
            IsReadOnly = true;
        }

public DeliveryRegisterDto Fetch(int registerId)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("InterwayDocs"))
            {
                using (var cmd = new SqlCommand("dbo.GetDeliveryRegister", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@RegisterId", registerId).DbType = DbType.Int32;
                    var dr = cmd.ExecuteReader();
                    return Fetch(dr);
                }
            }
        }

public OutgoingRegisterDto Fetch(int registerId)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("InterwayDocs"))
            {
                using (var cmd = new SqlCommand("dbo.GetOutgoingRegister", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@RegisterId", registerId).DbType = DbType.Int32;
                    var dr = cmd.ExecuteReader();
                    return Fetch(dr);
                }
            }
        }

public IDataReader Fetch()
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("Invoices"))
            {
                using (var cmd = new SqlCommand("dbo.GetInvoiceList", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    return cmd.ExecuteReader();
                }
            }
        }

public IDataReader Fetch(Guid invoiceId)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("Invoices"))
            {
                using (var cmd = new SqlCommand("dbo.GetInvoiceView", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@InvoiceId", invoiceId).DbType = DbType.Guid;
                    return cmd.ExecuteReader();
                }
            }
        }

public IDataReader Fetch(Guid productId)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("Invoices"))
            {
                using (var cmd = new SqlCommand("dbo.GetProductEdit", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ProductId", productId).DbType = DbType.Guid;
                    return cmd.ExecuteReader();
                }
            }
        }

public IDataReader Fetch()
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("Invoices"))
            {
                using (var cmd = new SqlCommand("dbo.GetProductList", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    return cmd.ExecuteReader();
                }
            }
        }

public IDataReader Fetch()
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("Invoices"))
            {
                using (var cmd = new SqlCommand("dbo.GetProductTypeCachedList", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    return cmd.ExecuteReader();
                }
            }
        }

public IDataReader Fetch()
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("Invoices"))
            {
                using (var cmd = new SqlCommand("dbo.GetProductTypeCachedNVL", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    return cmd.ExecuteReader();
                }
            }
        }

public IDataReader Fetch()
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("Invoices"))
            {
                using (var cmd = new SqlCommand("dbo.GetProductTypeColl", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    return cmd.ExecuteReader();
                }
            }
        }

public IDataReader Fetch()
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("Invoices"))
            {
                using (var cmd = new SqlCommand("dbo.GetProductTypeDynaColl", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    return cmd.ExecuteReader();
                }
            }
        }

public IDataReader Fetch(int productTypeId)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("Invoices"))
            {
                using (var cmd = new SqlCommand("dbo.GetProductTypeEdit", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ProductTypeId", productTypeId).DbType = DbType.Int32;
                    return cmd.ExecuteReader();
                }
            }
        }

public IDataReader Fetch()
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("Invoices"))
            {
                using (var cmd = new SqlCommand("dbo.GetProductTypeList", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    return cmd.ExecuteReader();
                }
            }
        }

public IDataReader Fetch()
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("Invoices"))
            {
                using (var cmd = new SqlCommand("dbo.GetProductTypeNVL", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    return cmd.ExecuteReader();
                }
            }
        }

public IDataReader Fetch(int productTypeId)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("Invoices"))
            {
                using (var cmd = new SqlCommand("dbo.GetProductTypeRO", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ProductTypeId", productTypeId).DbType = DbType.Int32;
                    return cmd.ExecuteReader();
                }
            }
        }