System.Data.SqlClient.SqlDataReader.Read()

public bool Read()
        {
            if (this.reader == null)
                throw new Exception("Reader not available");

            if (!this.reader.HasRows)
                return false;

            if (!this.reader.Read())
                return false;

            return true;
        }

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

private IEnumerable<Doc> execEnumerable(MsSqlCRUDQueryExecutionContext ctx, SqlCommand cmd, SqlDataReader reader, Schema schema, Schema.FieldDef[] toLoad, Query query)
                    {
                      using(cmd)
                        using(reader)
                        while(reader.Read())
                        {
                          var row = PopulateDoc(ctx, query.ResultDocType, schema, toLoad, reader);
                          yield return row;
                        }
                    }

public static Rowset PopulateRowset(MsSqlCRUDQueryExecutionContext context, SqlDataReader reader, string target, Query query, QuerySource qSource, bool oneDoc)
          {
            Schema.FieldDef[] toLoad;
            Schema schema = GetSchemaForQuery(target, query, reader, qSource, out toLoad);
            var store= context.DataStore;

            var result = new Rowset(schema);
            while(reader.Read())
            {
              var row = PopulateDoc(context, query.ResultDocType, schema, toLoad, reader);

              result.Add( row );
              if (oneDoc) break;
            }

            return result;
          }

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 static IEnumerable<Dictionary<string, object>> Serialize(SqlDataReader reader)
        {
            var results = new List<Dictionary<string, object>>();
            var cols = new List<string>();
            for (var i = 0; i < reader.FieldCount; i++) cols.Add(reader.GetName(i));

            while (reader.Read()) results.Add(SerializeRow(cols, reader));

            return results;
        }

private IDataGrid ReadToGrid
            (
                SqlDataReader reader
            )
        {
            var grid = new DataGrid("QueryResults");

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    var rowValues = new Dictionary<string, object>();
                    
                    for (var i = 0; i < reader.FieldCount; i++)
                    {
                        var fieldName = reader.GetName(i);
                        var fieldValue = reader.GetValue(i);

                        rowValues.Add
                        (
                            fieldName,
                            fieldValue
                        );
                    }

                    grid.AddRow(rowValues);
                }
            }

            return grid;
        }

public static IEnumerable<QueryRow> ToQueryRows
            (
                this SqlDataReader reader,
                ILocaleConfiguration localeConfiguration,
                params QueryColumnInfo[] queryColumns
            )
        {
            Validate.IsNotNull(localeConfiguration);

            var rows = new List<QueryRow>();

            if (reader.HasRows)
            {
                var columnSchemas = queryColumns.Select
                (
                    info => info.Column
                );

                while (reader.Read())
                {
                    var cells = new List<QueryCell>();

                    for (var i = 0; i < reader.FieldCount; i++)
                    {
                        var fieldName = reader.GetName(i);
                        var fieldValue = reader.GetValue(i);

                        var transformer = CulturalTransformerFactory.GetInstance
                        (
                            fieldValue
                        );

                        fieldValue = transformer.Transform
                        (
                            fieldValue,
                            localeConfiguration
                        );

                        var columnSchema = columnSchemas.FirstOrDefault
                        (
                            c => c.Name.Equals(fieldName, StringComparison.OrdinalIgnoreCase)
                        );

                        if (columnSchema == null)
                        {
                            throw new InvalidOperationException
                            (
                                $"The field name '{fieldName}' was not expected."
                            );
                        }

                        cells.Add
                        (
                            new QueryCell
                            (
                                columnSchema,
                                fieldValue
                            )
                        );
                    }
                }
            }

            return rows;
        }

protected void DisplayCurrentSecret()
        {
            StringBuilder html = new StringBuilder();

            string constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

            using (var conn = new SqlConnection(constr))
            {
                conn.Open();

                using (var cmd = new SqlCommand(@" select secret from users where id='" + Session["user_id"] + "' ", conn))
                {
                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr.HasRows && dr.Read())
                    {
                        html.Append(dr["secret"].ToString());
                    }
                }
            }
            CurrentSecretPlace.Controls.Add(new Literal { Text = html.ToString() });
        }

public void DisplayAccountInfo()
        {
            StringBuilder html = new StringBuilder();

            string constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

            using (var conn = new SqlConnection(constr))
            {
                conn.Open();

                using (var cmd = new SqlCommand(@" select * from users where id=@account_id", conn))
                {
                    SqlParameter accountId = new SqlParameter("account_id", SqlDbType.Int);
                    accountId.Value = Request.QueryString["account_id"];
                    cmd.Parameters.Add(accountId);

                    SqlDataReader dr = cmd.ExecuteReader();

                    if (dr.HasRows && dr.Read())
                    {
                        html.Append("Username: " + dr["username"] + "<br/>");
                        html.Append("Secret: " + dr["secret"]);
                    }
                    else
                    {
                        html.Append("<b style='color:red'>Invalid User id</b>");
                    }
                    AccountInfoPage.Controls.Add(new Literal { Text = html.ToString() });
                }
            }
        }

public void DisplayAccountInfo_Fixed()
        {
            StringBuilder html = new StringBuilder();
            string constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

            using (var conn = new SqlConnection(constr))
            {
                conn.Open();

                //Validating Part to make sure the user authorized to access the object:
                using (var cmd = new SqlCommand(@" select * from users where id=@account_id and username=@username", conn))
                {
                    SqlParameter accountId = new SqlParameter("account_id", SqlDbType.Int);
                    accountId.Value = Request.QueryString["account_id"];
                    cmd.Parameters.Add(accountId);

                    SqlParameter usernameParam = new SqlParameter("username", SqlDbType.NVarChar);
                    usernameParam.Value = Session["username"].ToString();
                    cmd.Parameters.Add(usernameParam);

                    SqlDataReader dr = cmd.ExecuteReader();

                    if (!dr.HasRows)
                    {
                        html.Append("<b style='color:red'>You are not authorized</b>");
                        AccountInfoPage.Controls.Add(new Literal { Text = html.ToString() });
                        return;
                    }

                }
            }

            //Getting the Data:
            using (var conn = new SqlConnection(constr))
            {
                conn.Open();

                using (var cmd = new SqlCommand(@" select * from users where id=@account_id and username=@username", conn))
                {
                    SqlParameter accountId = new SqlParameter("account_id", SqlDbType.Int);
                    accountId.Value = Request.QueryString["account_id"];
                    cmd.Parameters.Add(accountId);

                    SqlParameter usernameParam = new SqlParameter("username", SqlDbType.NVarChar);
                    usernameParam.Value = Session["username"].ToString();
                    cmd.Parameters.Add(usernameParam);

                    SqlDataReader dr = cmd.ExecuteReader();

                    if (dr.Read())
                    {
                        html.Append("Username: " + dr["username"] + "<br/>");
                        html.Append("Secret: " + dr["secret"]);
                    }
                    else
                    {
                        html.Append("<b style='color:red'>Invalid User id</b>");
                    }
                    AccountInfoPage.Controls.Add(new Literal { Text = html.ToString() });
                }
            }
        }

protected void DoLogin()
        {
            StringBuilder html = new StringBuilder();

            string constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

            using (var conn = new SqlConnection(constr))
            {
                conn.Open();

                using (var cmd = new SqlCommand(@" select * from users where username='" + Username.Text + "' ", conn))
                {
                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr.HasRows && dr.Read())
                    {
                        String preplacedwordFromDatabase = dr["preplacedword"].ToString();
                        if (String.Equals(preplacedwordFromDatabase, Preplacedword.Text))
                        {
                            Session["isLoggedIn"] = 1;
                            Session["username"] = Username.Text;
                            Session["user_id"] = dr["id"];
                            Response.Write(dr["id"]);
                            RedirectionAfterLogin();
                            //RedirectionAfterLogin_Fixed();
                        }
                        else
                        {
                            html.Append("<b style='color:red'>Invalid credentials</b>");
                            LoginFormPage.Controls.Add(new Literal { Text = html.ToString() });
                            return;
                        }

                    }
                    else
                    {
                        html.Append("<b style='color:red'>Invalid credentials</b>");
                        LoginFormPage.Controls.Add(new Literal { Text = html.ToString() });
                        return;
                    }

                }
            }
        }

protected void DoLogin_BCrypt()
        {
            StringBuilder html = new StringBuilder();

            string constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

            using (var conn = new SqlConnection(constr))
            {
                conn.Open();

                using (var cmd = new SqlCommand(@" select * from users where username='" + Username.Text + "' ", conn))
                {
                    SqlDataReader dr = cmd.ExecuteReader();
                    if(dr.HasRows && dr.Read())
                    {
                        String hashFromDatabase = dr["preplacedword"].ToString();
                        if (BCrypt.Net.BCrypt.Verify(Preplacedword.Text, hashFromDatabase))
                        {
                            Session["isLoggedIn"] = 1;
                            Session["username"] = Username.Text;
                            Session["user_id"] = dr["id"];
                            RedirectionAfterLogin();
                            //RedirectionAfterLogin_Fixed();
                        }
                        else
                        {
                            html.Append("<b style='color:red'>Invalid credentials</b>");
                            LoginFormPage.Controls.Add(new Literal { Text = html.ToString() });
                            return;
                        }

                    }
                    else
                    {
                        html.Append("<b style='color:red'>Invalid credentials</b>");
                        LoginFormPage.Controls.Add(new Literal { Text = html.ToString() });
                        return;
                    }
                    
                }
            }
        }

protected void Display_Posts()
        {
            String userInput = Request.QueryString["id"];
            string constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

            StringBuilder html = new StringBuilder();
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();

                //Vulnerable - Direct user input - Dynamic Query
                SqlCommand cmd = new SqlCommand("SELECT replacedle,content FROM Posts where id=" + userInput, conn);
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        html.Append(String.Format("<h3>{0}</h3>", reader["replacedle"]));
                        html.Append(String.Format("<div clreplaced='post-content'>{0}</div>", reader["content"]));
                    }
                }

            }

            PostsPlace.Controls.Add(new Literal { Text = html.ToString() });
        }

protected void Display_Posts_Fixed()
        {
            String userInput = Request.QueryString["id"];
            string constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
            StringBuilder html = new StringBuilder();
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();

                //Parameterized Query:
                SqlCommand cmd = new SqlCommand("SELECT replacedle,content FROM Posts where id=@id", conn);
                SqlParameter postId = new SqlParameter("id", SqlDbType.Int);
                postId.Value = userInput;
                cmd.Parameters.Add(postId);
                
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        html.Append(String.Format("<h3>{0}</h3>", reader["replacedle"]));
                        html.Append(String.Format("<div clreplaced='post-content'>{0}</div>", reader["content"]));
                    }
                }
            }

            PostsPlace.Controls.Add(new Literal { Text = html.ToString() });
        }

protected void DisplayAppreplacedle()
        {
            StringBuilder html = new StringBuilder();
            string constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

            using (var conn = new SqlConnection(constr))
            {
                conn.Open();

                using (var cmd = new SqlCommand(@" select Value from AppSettings where Name='Appreplacedle'", conn))
                {

                    SqlDataReader dr = cmd.ExecuteReader();

                    if (dr.HasRows && dr.Read())
                    {
                        html.Append("<h3 style='text-align:center'>" + dr["Value"] + "</h3>");
                    }
                    
                }
            }
            Appreplacedle.Controls.Add(new Literal { Text = html.ToString() });
        }

public IList<NotAcceptedConsentData> GetUsersWithUnAcceptedConsents()
        {
            try
            {
                IList<NotAcceptedConsentData> notAcceptedConsentDataList = new List<NotAcceptedConsentData>();

                using (SqlConnection connection = new SqlConnection(_builder.ConnectionString))
                {
                    Console.WriteLine("\nQuery data for unaccepted consents:");
                    Console.WriteLine("=========================================\n");

                    connection.Open();
                    StringBuilder sb = new StringBuilder();
                    sb.Append("SELECT DISTINCT UserId ");
                    sb.Append("FROM UserConsentValues ");
                    sb.Append("WHERE Value = '0'");
                    string sql = sb.ToString();

                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine("{0}", reader.GetGuid(0));
                                notAcceptedConsentDataList.Add(new NotAcceptedConsentData
                                {
                                    UserId = reader.GetGuid(0)
                                });
                            }
                        }
                    }
                }

                return notAcceptedConsentDataList;
            }

            catch(SqlException ex)
            {
                Console.WriteLine(nameof(SqlException) + " occurred when getting information about consents...");
                Console.WriteLine(nameof(SqlException) + " details: " + ex.Message);
                throw;
            }
        }

public List<string> GetAllDataTableName()
      {
         List<string> dtnames = new List<string>();
         if (ConnectionStatus == ConnectionState.Closed) Connect();
         SqlDataReader reader = ExecuteSqlToReader(@"SELECT Name FROM SysObjects Where XType='U' ORDER BY Name");
         while (reader.Read()) dtnames.Add(reader.GetString(0));
         return dtnames;
      }

public bool HasItem(Guid workSessionId) {
            bool hasItem = false;
            using(SqlConnection connection = new SqlConnection(settings.ConnectionString)) {
                string commandText = "SELECT [#WorkSessionId#] FROM [#TableName#] WHERE [#WorkSessionId#] = @WorkSessionId";
                commandText = HibernationTableQueryHelper.PatchSQLCommandText(commandText, settings);
                SqlCommand command = new SqlCommand(commandText, connection);
                command.Parameters.Add(new SqlParameter("WorkSessionId", workSessionId));
                connection.Open();
                using(SqlDataReader reader = command.ExecuteReader()) 
                    hasItem = reader.Read();
                connection.Close();
            }
            return hasItem;
        }

public HibernatedItem GereplacedemByWorkSessionId(Guid workSessionId) {
            HibernatedItem item = null;
            using(SqlConnection connection = new SqlConnection(settings.ConnectionString)) {
                string commandText = "SELECT [#WorkSessionId#], [#DoreplacedentId#], [#HibernationTime#], [#Header#], [#Content#] FROM [#TableName#] WHERE [#WorkSessionId#] = @WorkSessionId";
                commandText = HibernationTableQueryHelper.PatchSQLCommandText(commandText, settings);
                SqlCommand command = new SqlCommand(commandText, connection);
                command.Parameters.Add(new SqlParameter("WorkSessionId", workSessionId));
                connection.Open();
                using(SqlDataReader reader = command.ExecuteReader()) {
                    if(reader.Read()) {
                        item = new HibernatedItem(workSessionId);
                        item.DoreplacedentId = (string)reader[settings.ColumnNames.DoreplacedentId];
                        item.HibernationTime = Convert.ToDateTime(reader[settings.ColumnNames.HibernationTime]);
                        item.Header = (byte[])reader[settings.ColumnNames.Header];
                        item.Content = (byte[])reader[settings.ColumnNames.Content];
                    }
                    connection.Close();
                }
            }
            return item;
        }

public Guid FindWorkSessionId(string doreplacedentId) {
            Guid workSessionId = Guid.Empty;
            using(SqlConnection connection = new SqlConnection(settings.ConnectionString)) {
                string commandText = "SELECT [#WorkSessionId#] FROM [#TableName#] WHERE [#DoreplacedentId#] = @DoreplacedentId";
                commandText = HibernationTableQueryHelper.PatchSQLCommandText(commandText, settings);
                SqlCommand command = new SqlCommand(commandText, connection);
                command.Parameters.Add(new SqlParameter("DoreplacedentId", doreplacedentId));
                connection.Open();
                using(SqlDataReader reader = command.ExecuteReader()) {
                    if(reader.Read()) 
                        workSessionId = reader.GetGuid(0);
                }
                connection.Close();
            }
            return workSessionId;
        }

public DataTable GetFirstDataRow(string collectionName)
        {
            DataTable dataTable = new DataTable("Table1");

            if (expired)
                return dataTable;

            string surveyId = collectionName.Substring(collectionName.IndexOf("{{") + 2, 36);
            using (SqlConnection connection = new SqlConnection(certInfo.ConnectionString))
            {
                connection.Open();
                string commandString = "select top 1 ResponseJson from SurveyResponse where ResponseJson is not null and surveyid = '" + surveyId + "'";
                using (SqlCommand command = new SqlCommand(commandString, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {

                        while (reader.Read())
                        {
                            string json = reader.GetFieldValue<string>(0);
                            dataTable = GetDataTableFromJson(dataTable, json);
                        }
                    }
                }
            }
            return dataTable;
        }

public List<string> GetTableNames()
        {
            List<string> names = new List<string>();

            if (expired)
            {
                Epi.Windows.MsgBox.ShowError("Your certificate file has expired. Please ask your Epi Info administrator for a new certificate file.");
                return names;
            }

			if (!String.IsNullOrEmpty(redcaptablename))
			{
				names.Add(redcaptablename);
			}

			if (certInfo != null)
            {
                using (SqlConnection connection = new SqlConnection(certInfo.ConnectionString))
                {
                    connection.Open();
                    string commandString = "select o.Organization, m.SurveyId, m.SurveyName, m.DateCreated from surveymetadata m inner join organization o on m.OrganizationId = o.OrganizationId where o.OrganizationId = '" + certInfo.OrganizationId + "'";
                    using (SqlCommand command = new SqlCommand(commandString, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {

                            while (reader.Read())
                            {
                                string name = reader.GetFieldValue<string>(2) + " (" + reader.GetFieldValue<DateTime>(3) + ")" + " {{" + reader.GetFieldValue<Guid>(1) + "}}";
                                names.Add(name);
                            }
                        }
                    }
                }
            }

            return names;
        }

private bool CheckLogin()
		{
			//Check if login/preplacedword is valid  
			SqlDataReader dr = null;
			string username	= GetPageRequest(EvoUI.fNameLogin);
			string aSQL;
			SqlConnection cn = new SqlConnection(_SqlConnectionDico);

			if (string.IsNullOrEmpty(ErrorMsg) && !string.IsNullOrEmpty(username))
			{
				aSQL = EvoDB.SQL_EXEC + "EvoDicoSP_Login @login, @preplacedword";
				cn.Open();
				SqlCommand cmd = new SqlCommand(aSQL, cn);
				cmd.Parameters.Add(new SqlParameter("@login", username));
				cmd.Parameters.Add(new SqlParameter("@preplacedword", GetPageRequest(EvoUI.fNamePreplacedword)));
				try
				{
					dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				}
				catch 
				{}
				if (string.IsNullOrEmpty(ErrorMsg) && dr != null)
				{
					if (dr.Read())
						UserID = Convert.ToInt32(dr.GetValue(dr.GetOrdinal("ID")));
					else
						UserID = -1; 
				}
				else
				{
					ErrorMsg = "Invalid login stored procedure.";
					username = string.Empty;
				}
				cmd.Dispose();
				cn.Close();
			}
			return UserID > 0;
		}

static void Main(string[] args)
        {
            if (args.Length != 5)
            {
                Console.WriteLine("[*]ERROR: Please provide the correct number of arguments!");
                Console.WriteLine("[*]Ex: SqlClient.exe <username> <preplacedword> <IP Address> <databasename> <SQL Query>");
                return;
            }
            string connString = @"Server=" + args[2] + ";Database=" + args[3] + ";User ID=" + args[0] + ";Preplacedword=" + args[1];

            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    //retrieve the SQL Server instance version
                    string query = args[4];

                    SqlCommand cmd = new SqlCommand(query, conn);

                    //open connection
                    conn.Open();

                    //execute the SQLCommand
                    SqlDataReader dr = cmd.ExecuteReader();

                    //check if there are records
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            //display retrieved record (first column only/string value)
                            for (int i = 0; i < dr.FieldCount; i++)
                            {
                                Console.WriteLine(dr.GetName(i));
                            }
                            for (int i = 0; i < dr.FieldCount; i++)
                            {
                                Console.WriteLine(dr.GetValue(i));
                            }
                        }
                    }
                    else
                    {
                        Console.WriteLine("No data found.");
                    }
                    dr.Close();
                }
            }
            catch (Exception ex)
            {
                //display error message
                Console.WriteLine("Exception: " + ex.Message);
            }
        }

public ItemData<short> ReadInt16(DeviceAddress address)
        {
            ItemData<short> data = new ItemData<short>();
            var dataReader = ExecuteProcedureReader("ReadValueByID",
               new SqlParameter("@ID", SqlDbType.SmallInt) { Value = address.CacheIndex },
               new SqlParameter("@DATATYPE", SqlDbType.TinyInt) { Value = address.VarType });
            if (dataReader == null)
            {
                data.Quality = QUALITIES.QUALITY_BAD;
                return data;
            }
            while (dataReader.Read())
            {
                data.Value = dataReader.GetInt16(0);
                data.Quality = QUALITIES.QUALITY_GOOD;
            }
            dataReader.Close();
            return data;
        }

public ItemData<byte> ReadByte(DeviceAddress address)
        {
            ItemData<byte> data = new ItemData<byte>();
            var dataReader = ExecuteProcedureReader("ReadValueByID",
                  new SqlParameter("@ID", SqlDbType.SmallInt) { Value = address.CacheIndex },
                  new SqlParameter("@DATATYPE", SqlDbType.TinyInt) { Value = address.VarType });
            if (dataReader == null)
            {
                data.Quality = QUALITIES.QUALITY_BAD;
                return data;
            }
            while (dataReader.Read())
            {
                data.Value = dataReader.GetByte(0);
                data.Quality = QUALITIES.QUALITY_GOOD;
            }
            dataReader.Close();
            return data;
        }

public ItemData<string> ReadString(DeviceAddress address, ushort size)
        {
            ItemData<string> data = new ItemData<string>();
            var dataReader = ExecuteProcedureReader("ReadValueByID",
                          new SqlParameter("@ID", SqlDbType.SmallInt) { Value = address.CacheIndex },
                          new SqlParameter("@DATATYPE", SqlDbType.TinyInt) { Value = address.VarType });
            if (dataReader == null)
            {
                data.Quality = QUALITIES.QUALITY_BAD;
                return data;
            }
            while (dataReader.Read())
            {
                data.Value = dataReader.GetString(0);
                data.Quality = QUALITIES.QUALITY_GOOD;
            }
            dataReader.Close();
            return data;
        }

public ItemData<float> ReadFloat(DeviceAddress address)
        {
            ItemData<float> data = new ItemData<float>();
            var dataReader = ExecuteProcedureReader("ReadValueByID",
                  new SqlParameter("@ID", SqlDbType.SmallInt) { Value = address.CacheIndex },
                  new SqlParameter("@DATATYPE", SqlDbType.TinyInt) { Value = address.VarType });
            if (dataReader == null)
            {
                data.Quality = QUALITIES.QUALITY_BAD;
                return data;
            }
            while (dataReader.Read())
            {
                data.Value = dataReader.GetFloat(0);
                data.Quality = QUALITIES.QUALITY_GOOD;
            }
            dataReader.Close();
            return data;
        }

public ItemData<bool> ReadBit(DeviceAddress address)
        {
            ItemData<bool> data = new ItemData<bool>();
            var dataReader = ExecuteProcedureReader("ReadValueByID",
                 new SqlParameter("@ID", SqlDbType.SmallInt) { Value = address.CacheIndex },
                   new SqlParameter("@DATATYPE", SqlDbType.TinyInt) { Value = address.VarType });
            if (dataReader == null)
            {
                data.Quality = QUALITIES.QUALITY_BAD;
                return data;
            }
            while (dataReader.Read())
            {
                data.Value = dataReader.GetBoolean(0);
                data.Quality = QUALITIES.QUALITY_GOOD;
            }
            dataReader.Close();
            return data;
        }

public ItemData<object> ReadValue(DeviceAddress address)
        {
            ItemData<object> data = new ItemData<object>();
            var dataReader = ExecuteProcedureReader("ReadValueByID",
                new SqlParameter("@ID", SqlDbType.SmallInt) { Value = address.CacheIndex },
                  new SqlParameter("@DATATYPE", SqlDbType.TinyInt) { Value = address.VarType });
            if (dataReader == null)
            {
                data.Quality = QUALITIES.QUALITY_BAD;
                return data;
            }
            while (dataReader.Read())
            {
                data.Value = dataReader.GetValue(0);
                data.Quality = QUALITIES.QUALITY_GOOD;
            }
            dataReader.Close();
            return data;
        }

public ItemData<Storage>[] ReadMultiple(DeviceAddress[] addrsArr)
        {
            int len = addrsArr.Length;
            SqlParameter param = new SqlParameter("@IDARRAY", SqlDbType.VarChar);
            string str = string.Empty;
            for (int i = 0; i < len; i++)
            {
                str += addrsArr[i].CacheIndex + '|' + (int)addrsArr[i].VarType + ',';
            }
            param.Value = str;
            var dataReader = ExecuteProcedureReader("BatchReadByID", param);//可以采用IN 操作符+,
            if (dataReader == null)
                return null;
            else
            {
                ItemData<Storage>[] itemArr = new ItemData<Storage>[len];
                int i = 0;
                while (dataReader.Read())
                {
                    switch (addrsArr[i].VarType)
                    {
                        case DataType.BOOL:
                            itemArr[i].Value.Boolean = dataReader.GetBoolean(0);
                            break;
                        case DataType.BYTE:
                            itemArr[i].Value.Byte = dataReader.GetByte(0);
                            break;
                        case DataType.WORD:
                            itemArr[i].Value.Word = (ushort)dataReader.GetInt16(0);
                            break;
                        case DataType.SHORT:
                            itemArr[i].Value.Int16 = dataReader.GetInt16(0);
                            break;
                        case DataType.DWORD:
                            itemArr[i].Value.DWord = (uint)dataReader.GetInt32(0);
                            break;
                        case DataType.INT:
                            itemArr[i].Value.Int32 = dataReader.GetInt32(0);
                            break;
                        case DataType.FLOAT:
                            itemArr[i].Value.Single = dataReader.GetFloat(0);
                            break;
                    }
                    i++;
                }
                dataReader.Close();
                return itemArr;
            }
        }

public FileData[] ReadAll(short groupId)
        {
            FileData[] list = null;
            using (var reader = ExecuteProcedureReader("ReadAll", new SqlParameter("@GroupId", groupId)))
            {
                if (reader == null) return null;
                if (reader.Read())
                {
                    int count = reader.GetInt32(0);
                    list = new FileData[count];
                    reader.NextResult();
                    int i = 0;
                    while (reader.Read())
                    {
                        if (i < count)
                        {
                            list[i].ID = reader.GetInt16(0);
                            switch ((DataType)reader.GetByte(1))
                            {
                                case DataType.BOOL:
                                    list[i].Value.Boolean = Convert.ToBoolean(reader.GetValue(2));
                                    break;
                                case DataType.BYTE:
                                    list[i].Value.Byte = Convert.ToByte(reader.GetValue(2));
                                    break;
                                case DataType.WORD:
                                    list[i].Value.Word = Convert.ToUInt16(reader.GetValue(2));
                                    break;
                                case DataType.SHORT:
                                    list[i].Value.Int16 = Convert.ToInt16(reader.GetValue(2));
                                    break;
                                case DataType.DWORD:
                                    list[i].Value.DWord = Convert.ToUInt32(reader.GetValue(2));
                                    break;
                                case DataType.INT:
                                    list[i].Value.Int32 = Convert.ToInt32(reader.GetValue(2));
                                    break;
                                case DataType.FLOAT:
                                    list[i].Value.Single = Convert.ToSingle(reader.GetValue(2));
                                    break;
                                case DataType.STR:
                                    list[i].Text = Convert.ToString(reader.GetValue(2));
                                    //如何传送字符串？
                                    break;
                            }
                            i++;
                        }
                    }
                }
            }
            return list;
        }

public byte[] ReadBytes(DeviceAddress address, ushort size)
        {
            using (var dataReader = ExecuteProcedureReader("ReadValueByID",
              new SqlParameter("@ID", SqlDbType.SmallInt) { Value = address.CacheIndex },
              new SqlParameter("@DATATYPE", SqlDbType.TinyInt) { Value = address.VarType }))
            {
                if (dataReader != null)
                {
                    while (dataReader.Read())
                    {
                        return dataReader[0] as byte[];
                    }
                }
            }
            return null;
        }

public ItemData<int> ReadInt32(DeviceAddress address)
        {
            ItemData<int> data = new ItemData<int>();
            var dataReader = ExecuteProcedureReader("ReadValueByID",
              new SqlParameter("@ID", SqlDbType.SmallInt) { Value = address.CacheIndex },
              new SqlParameter("@DATATYPE", SqlDbType.TinyInt) { Value = address.VarType });
            if (dataReader == null)
            {
                data.Quality = QUALITIES.QUALITY_BAD;
                return data;
            }
            while (dataReader.Read())
            {
                data.Value = dataReader.GetInt32(0);
                data.Quality = QUALITIES.QUALITY_GOOD;
            }
            dataReader.Close();
            return data;
        }

public static T ExecuteSelectObject<T>(SqlConnection conn, string cmdStr) where T : ISqlDataType, new()
        {
            using (SqlCommand cmd = new SqlCommand(cmdStr, conn))
            {
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    if (sdr.Read())
                    {
                        T item = new T();
                        item.LoadBySqlDataReader(sdr);
                        return item;
                    }
                    else
                    {
                        return default(T);
                    }
                }
            }
        }

public static List<T> ExecuteSelectEnumerable<T>(SqlConnection conn, string cmdStr) where T : ISqlDataType, new()
        {
            using (SqlCommand cmd = new SqlCommand(cmdStr, conn))
            {
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    List<T> list = new List<T>();
                    while (sdr.Read())
                    {
                        T item = new T();
                        item.LoadBySqlDataReader(sdr);
                        list.Add(item);
                    }
                    return list;
                }
            }
        }

public static ColumnInfoCollection GetColumnData(this SqlConnection connection, string tableName, string schema = "dbo")
        {
            const string CMD_COLUMN_INFO_FORMAT =
@"SELECT
    COLUMN_NAME,
    COLUMN_DEFAULT,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    IS_NULLABLE,
    ORDINAL_POSITION,
    NUMERIC_PRECISION,
    NUMERIC_SCALE,
    COLUMNPROPERTY(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdenreplacedy') AS 'IsIdenreplacedy'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND TABLE_SCHEMA = @SchemaName";

            const string CMD_IS_PRIMARY_KEY_FORMAT =
@"SELECT CU.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU
WHERE CU.CONSTRAINT_NAME = T.Constraint_Name
AND CU.TABLE_NAME = T.TABLE_NAME
AND T.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND CU.TABLE_NAME = @TableName
AND T.CONSTRAINT_SCHEMA = @SchemaName";

            var list = new ColumnInfoCollection();

            bool alreadyOpen = (connection.State != ConnectionState.Closed);

            try
            {
                var foreignKeyColumns = connection.GetForeignKeyData(tableName, schema);

                if (!alreadyOpen)
                {
                    connection.Open();
                }

                using (var command = new SqlCommand(CMD_COLUMN_INFO_FORMAT, connection))
                {
                    command.CommandType = CommandType.Text;

                    command.Parameters.Add(new SqlParameter
                    {
                        Direction = ParameterDirection.Input,
                        DbType = DbType.String,
                        ParameterName = "@TableName",
                        Value = tableName
                    });

                    command.Parameters.Add(new SqlParameter
                    {
                        Direction = ParameterDirection.Input,
                        DbType = DbType.String,
                        ParameterName = "@SchemaName",
                        Value = schema
                    });

                    using (var reader = command.ExecuteReader())
                    {
                        ColumnInfo columnInfo = null;

                        while (reader.Read())
                        {
                            columnInfo = new ColumnInfo();

                            if (!reader.IsDBNull(0))
                            { columnInfo.ColumnName = reader.GetString(0); }

                            if (!reader.IsDBNull(1))
                            { columnInfo.DefaultValue = reader.GetString(1); }
                            else
                            { columnInfo.DefaultValue = string.Empty; }

                            if (foreignKeyColumns.Contains(columnInfo.ColumnName))
                            {
                                columnInfo.KeyType = KeyType.ForeignKey;
                            }

                            //else
                            //{
                            try
                            {
                                string type = reader.GetString(2);
                                columnInfo.DataTypeNative = type;
                                columnInfo.DataType = DataTypeConvertor.GetDbType(type.ToEnum<SqlDbType>(true));
                            }
                            catch (ArgumentNullException)
                            {
                                columnInfo.DataType = DbType.Object;
                            }
                            catch (ArgumentException)
                            {
                                columnInfo.DataType = DbType.Object;
                            }

                            //}

                            if (!reader.IsDBNull(3))
                            { columnInfo.MaximumLength = reader.GetInt32(3); }

                            if (!reader.IsDBNull(4))
                            {
                                if (reader.GetString(4).ToUpperInvariant().Equals("NO"))
                                { columnInfo.IsNullable = false; }
                                else
                                { columnInfo.IsNullable = true; }
                            }

                            if (!reader.IsDBNull(5))
                            { columnInfo.OrdinalPosition = reader.GetInt32(5); }

                            if (!reader.IsDBNull(6))
                            { columnInfo.Precision = reader.GetByte(6); }

                            if (!reader.IsDBNull(7))
                            { columnInfo.Scale = reader.GetInt32(7); }

                            if (!reader.IsDBNull(8))
                            { columnInfo.IsAutoIncremented = reader.GetInt32(8) == 1 ? true : false; }

                            list.Add(columnInfo);
                        }
                    }
                }
            }
            finally
            {
                if (!alreadyOpen && connection.State != ConnectionState.Closed)
                { connection.Close(); }
            }

            #region Primary Keys

            using (var command = connection.CreateCommand())
            {
                command.CommandType = CommandType.Text;
                command.CommandText = CMD_IS_PRIMARY_KEY_FORMAT;

                command.Parameters.Add(new SqlParameter
                {
                    Direction = ParameterDirection.Input,
                    DbType = DbType.String,
                    ParameterName = "@TableName",
                    Value = tableName
                });

                command.Parameters.Add(new SqlParameter
                {
                    Direction = ParameterDirection.Input,
                    DbType = DbType.String,
                    ParameterName = "@SchemaName",
                    Value = schema
                });

                alreadyOpen = (connection.State != ConnectionState.Closed);

                if (!alreadyOpen)
                {
                    connection.Open();
                }

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string pkColumn = reader.GetString(0);
                        ColumnInfo match = list[pkColumn];
                        if (match != null)
                        {
                            match.KeyType = KeyType.PrimaryKey;
                        }
                    }
                }

                if (!alreadyOpen)
                {
                    connection.Close();
                }
            }

            #endregion Primary Keys

            return list;
        }

public static IEnumerable<string> GetDatabaseNames(this SqlConnection connection)
        {
            const string CMD_SELECT_DATABASE_NAMES = "SELECT NAME FROM SYS.DATABASES ORDER BY NAME";
            var databaseNames = new List<string>();

            bool alreadyOpen = (connection.State != ConnectionState.Closed);

            if (!alreadyOpen)
            {
                connection.Open();
            }

            using (var command = connection.CreateCommand())
            {
                command.CommandType = CommandType.Text;
                command.CommandText = CMD_SELECT_DATABASE_NAMES;

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        databaseNames.Add(reader.GetString(0));
                    }
                }
            }

            if (!alreadyOpen)
            {
                connection.Close();
            }

            return databaseNames;
        }

public static ForeignKeyInfoCollection GetForeignKeyData(this SqlConnection connection, string tableName, string schema = "dbo")
        {
            const string CMD_FOREIGN_KEYS_FORMAT =
@"SELECT FK_Table = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
	PK_Table = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME,
	Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
	SELECT i1.TABLE_NAME, i2.COLUMN_NAME
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
	INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
		i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
	WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE FK.TABLE_NAME = @TableName
AND C.CONSTRAINT_SCHEMA = @SchemaName
ORDER BY 1,2,3,4";

            var foreignKeyData = new ForeignKeyInfoCollection();
            bool alreadyOpen = (connection.State != ConnectionState.Closed);

            if (!alreadyOpen)
            {
                connection.Open();
            }

            using (var command = new SqlCommand(CMD_FOREIGN_KEYS_FORMAT, connection))
            {
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new SqlParameter
                {
                    Direction = ParameterDirection.Input,
                    DbType = DbType.String,
                    ParameterName = "@TableName",
                    Value = tableName
                });

                command.Parameters.Add(new SqlParameter
                {
                    Direction = ParameterDirection.Input,
                    DbType = DbType.String,
                    ParameterName = "@SchemaName",
                    Value = schema
                });

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        foreignKeyData.Add(new ForeignKeyInfo(
                            reader.IsDBNull(0) ? null : reader.GetString(0),
                            reader.IsDBNull(1) ? null : reader.GetString(1),
                            reader.IsDBNull(2) ? null : reader.GetString(2),
                            reader.IsDBNull(3) ? null : reader.GetString(3),
                            string.Empty,
                            reader.IsDBNull(4) ? null : reader.GetString(4)));
                    }
                }
            }

            if (!alreadyOpen)
            {
                connection.Close();
            }

            return foreignKeyData;
        }

public static IEnumerable<string> GetTableNames(this SqlConnection connection, string databaseName, bool includeViews = false, string schema = "dbo")
        {
            var commandBuilder = new SqlCommandBuilder();

            string query;
            if (includeViews)
            {
                query =
$@"USE {commandBuilder.QuoteIdentifier(databaseName)};
SELECT [name]
FROM sys.Tables
WHERE [name] <> 'sysdiagrams'
AND SCHEMA_NAME([schema_id]) = @SchemaName
UNION
SELECT [name]
FROM sys.Views
WHERE [name] <> 'sysdiagrams'
AND SCHEMA_NAME([schema_id]) = @SchemaName
ORDER BY [name]";
            }
            else
            {
                query =
$@"USE {commandBuilder.QuoteIdentifier(databaseName)};
SELECT [name]
FROM sys.Tables
WHERE [name] <> 'sysdiagrams'
AND SCHEMA_NAME([schema_id]) = @SchemaName
ORDER BY [name]";
            }

            var tables = new List<string>();

            bool alreadyOpen = (connection.State != ConnectionState.Closed);

            if (!alreadyOpen)
            {
                connection.Open();
            }

            using (var command = connection.CreateCommand())
            {
                command.CommandType = CommandType.Text;
                command.CommandText = query;

                command.Parameters.Add(new SqlParameter
                {
                    Direction = ParameterDirection.Input,
                    DbType = DbType.String,
                    ParameterName = "@SchemaName",
                    Value = schema
                });

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        tables.Add(reader.GetString(0));
                    }
                }
            }

            if (!alreadyOpen)
            {
                connection.Close();
            }

            return tables;
        }

public static IEnumerable<string> GetViewNames(this SqlConnection connection, string databaseName, string schema = "dbo")
        {
            var commandBuilder = new SqlCommandBuilder();

            string query =
$@"USE {commandBuilder.QuoteIdentifier(databaseName)};
SELECT [name]
FROM sys.Views
WHERE [name] <> 'sysdiagrams'
AND SCHEMA_NAME([schema_id]) = @SchemaName
ORDER BY [name]";

            var views = new List<string>();

            bool alreadyOpen = (connection.State != ConnectionState.Closed);

            if (!alreadyOpen)
            {
                connection.Open();
            }

            using (var command = connection.CreateCommand())
            {
                command.CommandType = CommandType.Text;
                command.CommandText = query;

                command.Parameters.Add(new SqlParameter
                {
                    Direction = ParameterDirection.Input,
                    DbType = DbType.String,
                    ParameterName = "@SchemaName",
                    Value = schema
                });

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        views.Add(reader.GetString(0));
                    }
                }
            }

            if (!alreadyOpen)
            {
                connection.Close();
            }

            return views;
        }