System.Data.SqlClient.SqlConnection.Open()

Here are the examples of the csharp api System.Data.SqlClient.SqlConnection.Open() taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.

1316 Examples 7

19 View Source File : ConnectionTester.cs
License : MIT License
Project Creator : afaniuolo

public static bool IsServerConnected(string connectionString)
		{
			if (connectionString.ToLower().StartsWith("mongodb"))
			{
				try
				{
					var databaseName = MongoUrl.Create(connectionString).DatabaseName;
					var client = new MongoClient(connectionString).GetDatabase(databaseName);
					var collection = client.GetCollection<FormData>("FormData").CountDoreplacedents(new BsonDoreplacedent());
					return true;
				}
				catch (Exception ex)
				{
					return false;
				}
			}
			else
			{
				using (SqlConnection connection = new SqlConnection(connectionString))
				{
					try
					{
						connection.Open();
						return true;
					}
					catch (SqlException)
					{
						return false;
					}
				}
			}
		}

19 View Source File : SqlServerDataBase.cs
License : Mozilla Public License 2.0
Project Creator : agebullhu

public bool Open()
        {
            //if (_isClosed)
            //{
            //    //throw new Exception("已关闭的数据库对象不能再次使用");
            //}
            bool result = false;
            if (_connection == null)
            {
                result = true;
                _connection = new SqlConnection(ConnectionString);
                //Trace.WriteLine("Create Connection", "SqlServerDataBase");
            }
            else if (string.IsNullOrEmpty(_connection.ConnectionString))
            {
                result = true;
                //Trace.WriteLine("Set ConnectionString", "SqlServerDataBase");
                _connection.ConnectionString = ConnectionString;
            }
            if (_connection.State == ConnectionState.Open)
            {
                return result;
            }
            //Trace.WriteLine(_count++, "Open");
            //Trace.WriteLine("Opened Connection", "SqlServerDataBase");
            _connection.Open();
            return true;
        }

19 View Source File : SqlServerDataBase.cs
License : Mozilla Public License 2.0
Project Creator : agebullhu

private SqlConnection InitConnection()
        {
            var connection = new SqlConnection(ConnectionString);
            IocScope.DisposeFunc.Add(() => Close(connection));
            int cnt;
            lock (Connections)
            {
                Connections.Add(connection);
                cnt = Connections.Count;
            }
            LogRecorderX.MonitorTrace($"打开连接数:{cnt}");
            //Trace.WriteLine(_count++, "Open");
            //Trace.WriteLine("Opened _connection", "SqlServerDataBase");
            connection.Open();
            return connection;
        }

19 View Source File : SqlServerDataBase.cs
License : Mozilla Public License 2.0
Project Creator : agebullhu

public bool Open()
        {
            if (_connection != null && _connection.State == ConnectionState.Open)
            {
                return false;
            }
            if (_connection == null)
            {
                _connection = InitConnection();
                return true;
                //Trace.WriteLine("Create _connection", "SqlServerDataBase");
            }
            if (string.IsNullOrEmpty(_connection.ConnectionString))
            {
                //Trace.WriteLine("Set ConnectionString", "SqlServerDataBase");
                _connection.ConnectionString = ConnectionString;
            }
            //Trace.WriteLine(_count++, "Open");
            //Trace.WriteLine("Opened _connection", "SqlServerDataBase");
            _connection.Open();
            return true;
        }

19 View Source File : DesignerContextMenuAddIn.cs
License : GNU General Public License v3.0
Project Creator : anderson-joyle

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

19 View Source File : Program.cs
License : GNU General Public License v3.0
Project Creator : AndreiFedarets

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

19 View Source File : Database.cs
License : GNU General Public License v3.0
Project Creator : andysal

public Image<Rgba32> GetCategoryThumbnail(int categoryId)
        {
            byte[] imageRawData = null;
            var connectionString = this.ConnectionString;
            using(var cn = new SqlConnection(connectionString))
            using(IDbCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = $"SELECT Picture FROM Categories WHERE CategoryID={categoryId}";
                cmd.Connection = cn;
                cn.Open();
                using IDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (myReader.Read())
                {
                    imageRawData = (byte[])myReader.GetValue(0);
                }
            }
            return ByteArrayToImage(imageRawData, true);
        }

19 View Source File : Database.cs
License : GNU General Public License v3.0
Project Creator : andysal

public Image<Rgba32> GetProductThumbnail(int productId)
        {
            var connectionString = this.ConnectionString;
            using (var cn = new SqlConnection(connectionString))
            using (IDbCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = $"SELECT CategoryID FROM Products WHERE ProductID={productId}";
                cmd.Connection = cn;
                cn.Open();
                var categoryId = (int) cmd.ExecuteScalar();
                return GetCategoryThumbnail(categoryId);
            }
        }

19 View Source File : Database.cs
License : GNU General Public License v3.0
Project Creator : andysal

public Image GetCategoryThumbnail(int categoryId)
        {
            byte[] imageRawData = null;
            var cn = (SqlConnection)this.Context.Database.Connection;
            using (IDbCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = "SELECT Picture FROM Categories WHERE CategoryID=" + categoryId;
                cmd.Connection = cn;
                cn.Open();
                using (IDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (myReader.Read())
                    {
                        imageRawData = (byte[])myReader.GetValue(0);
                    }
                }
            }
            return byteArrayToImage(imageRawData, true);
        }

19 View Source File : NskReadModelFacadeImpl.cs
License : GNU General Public License v3.0
Project Creator : andysal

public System.Drawing.Image GetThumbnailByCategory(int categoryId)
        {
            byte[] imageRawData = null;
            var cn = (SqlConnection) this.Database.Connection;
            using (var cmd = new SqlCommand("SELECT Picture FROM Categories WHERE CategoryID=" + categoryId, cn))
            {
                cn.Open();
                using (IDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (myReader.Read())
                    {
                        imageRawData = (byte[])myReader.GetValue(0);
                    }
                }
            }
            return byteArrayToImage(imageRawData, true);
        }

19 View Source File : NskReadModelFacadeImpl.cs
License : GNU General Public License v3.0
Project Creator : andysal

public System.Drawing.Image GetThumbnailByCategory(int categoryId)
        {
            byte[] imageRawData = null;
            var cn = (SqlConnection)this.Database.Connection;
            using (IDbCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = "SELECT Picture FROM Categories WHERE CategoryID=" + categoryId;
                cmd.Connection = cn;
                cn.Open();
                using (IDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (myReader.Read())
                    {
                        imageRawData = (byte[])myReader.GetValue(0);
                    }
                }    
            }
            return byteArrayToImage(imageRawData, true);
        }

19 View Source File : DatabaseOperation.cs
License : MIT License
Project Creator : ap0405140

public void RefreshConnect()
        {
            try
            {
                if (scn == null || scn.State != ConnectionState.Open)
                {
                    scn = new SqlConnection();
                    scn.ConnectionString = ConnectString;
                    scn.Open();
                }
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

19 View Source File : Sample12.cs
License : Apache License 2.0
Project Creator : Appdynamics

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

19 View Source File : Sample3.cs
License : Apache License 2.0
Project Creator : Appdynamics

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

19 View Source File : Sample4.cs
License : Apache License 2.0
Project Creator : Appdynamics

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

19 View Source File : Sample4.cs
License : Apache License 2.0
Project Creator : Appdynamics

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

19 View Source File : MssqlSchema.cs
License : MIT License
Project Creator : aquilahkj

public List<Table> GetTables()
        {
            const string tableCommandText = @"SELECT A.name as TableName,A.object_id as TableCode, C.value as CommentText FROM sys.tables A left JOIN sys.extended_properties C ON C.major_id = A.object_id  and minor_id=0 WHERE A.name = N'{0}'";

            const string columnCommandText = @"
with indexcte as(
	select
		ic.column_id,
		ic.index_column_id,
		ic.object_id
	from
		{0}.sys.indexes idx
	inner join {0}.sys.index_columns ic on idx.index_id = ic.index_id
	and idx.object_id = ic.object_id
	where
		idx.object_id = object_id('{0}.dbo.{1}')
	and idx.is_primary_key = 1
) select
	colm.column_id ColumnId,
	cast(
		case
		when indexcte.column_id is null then
			0
		else
			1
		end as bit
	) ColumnKey,
	cast(colm.max_length as int) bytelength,
	(
		case
		when systype.name = 'nvarchar'
		and colm.max_length > 0 then
			colm.max_length / 2
		when systype.name = 'nchar'
		and colm.max_length > 0 then
			colm.max_length / 2
		when systype.name = 'ntext'
		and colm.max_length > 0 then
			colm.max_length / 2
		else
			colm.max_length
		end
	) MaxLength,
	colm.name ColumnName,
	systype.name DataType,
	colm.is_idenreplacedy IsIdenreplacedy,
	colm.is_nullable AllowNull,
	cast(colm.precision as int) Precision,
	cast(colm.scale as int) Scale,
	prop.value ColumnComment
from
	{0}.sys.columns colm
inner join {0}.sys.types systype on colm.system_type_id = systype.system_type_id
and colm.user_type_id = systype.user_type_id
left join {0}.sys.extended_properties prop on colm.object_id = prop.major_id
and colm.column_id = prop.minor_id
left join indexcte on colm.column_id = indexcte.column_id
and colm.object_id = indexcte.object_id
where
	colm.object_id = object_id('{0}.dbo.{1}')
order by
	colm.column_id";

            List<Table> tables = new List<Table>();
            foreach (TableNameSet tableNameSet in DbSetting.GetTables()) {
                string tableCommandStr = String.Format(tableCommandText, tableNameSet.TableName);
                SqlConnection tableConn = new SqlConnection(_connectionString);
                tableConn.Open();
                SqlCommand tableCommand = new SqlCommand(tableCommandStr, tableConn);
                SqlDataAdapter tableAd = new SqlDataAdapter(tableCommand);
                DataSet tableDs = new DataSet();
                tableAd.Fill(tableDs);
                DataTable tableColumns = tableDs.Tables[0];
                tableConn.Close();
                if (tableColumns.Rows.Count == 0) {
                    continue;
                }
                string tableComment = Convert.ToString(tableColumns.Rows[0]["CommentText"]);
                string tableCode = Convert.ToString(tableColumns.Rows[0]["TableCode"]);
                string columnCommandStr = String.Format(columnCommandText, this._dataBaseName, tableNameSet.TableName);
                SqlConnection columnConn = new SqlConnection(_connectionString);
                columnConn.Open();
                SqlCommand columnCommand = new SqlCommand(columnCommandStr, tableConn);
                SqlDataAdapter columnAd = new SqlDataAdapter(columnCommand);
                DataSet columnDs = new DataSet();
                columnAd.Fill(columnDs);
                DataTable columnColumns = columnDs.Tables[0];
                tableConn.Close();

                Table table = new Table(tableNameSet.AliasName, tableNameSet.TableName);
                if (String.IsNullOrEmpty(tableComment)) {
                    tableComment = tableNameSet.TableName;
                }
                table.CommentText = tableComment;
                foreach (DataRow item in columnColumns.Rows) {
                    Column column = CreateColumn(table, item);
                    if (column != null) {
                        table.SetColumn(column);
                    }
                }
                tables.Add(table);
            }
            return tables;
        }

19 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

public override int ExecuteSqlTran(string connectionString, ArrayList SQLStringList, List<SqlParameter[]> ParamList)
        {
            int val = 0;
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    var cmd = new SqlCommand();
                    try
                    {
                        for (int n = 0; n < SQLStringList.Count; n++)
                        {
                            string strsql = SQLStringList[n].ToString();
                            var cmdParam = ParamList[n];
                            PrepareCommand(cmd, conn, trans, strsql, cmdParam);
                            val += cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return val;
        }

19 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

public override int ExecuteSql(string connectionString, string SQLString, string content)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(SQLString, connection);
                System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
                myParameter.Value = content;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

19 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

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

19 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

public override DataSet RunProcedure(string connectionString, string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                var dataSet = new DataSet();
                connection.Open();
                var sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }

19 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

public override int RunProcedure(string connectionString, string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                int result;
                connection.Open();
                var command = BuildIntCommand(connection, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                result = (int)command.Parameters["ReturnValue"].Value;
                return result;
            }
        }

19 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

public override string RunProcedure(string connectionString, string storedProcName, IDataParameter[] parameters, out string rowsAffected)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                string result;
                connection.Open();
                var command = BuildIntCommand(connection, storedProcName, parameters);
                command.ExecuteNonQuery();
                result = command.Parameters["ReturnValue"].Value.ToString();
                rowsAffected = result;
                return result;
            }
        }

19 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

public override int ExecuteSqlTran(string connectionString, List<String> SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch
                {
                    tx.Rollback();
                    return 0;
                }
            }
        }

19 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

public override int ExecuteSqlTran(string connectionString, string SQLString)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    string strsql = SQLString;
                    if (strsql.Trim().Length > 1)
                    {
                        cmd.CommandText = strsql;
                        count += cmd.ExecuteNonQuery();
                    }
                    tx.Commit();
                    return count;
                }
                catch
                {
                    tx.Rollback();
                    return 0;
                }
            }
        }

19 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

public override bool ConnectionIsUse(string connectionString)
        {
            bool revalue = false;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    connection.Close();
                    revalue = true;
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    connection.Close();
                }
            }
            return revalue;
        }

19 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

public override int ExecuteSql(string connectionString, string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        connection.Close();
                        throw new Exception(E.Message);
                    }
                }
            }
        }

19 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

public override void ExecuteSqlTran(string connectionString, ArrayList SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }

19 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

public override int ExecuteSqlInsertImg(string connectionString, string strSQL, byte[] fs)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(strSQL, connection);
                System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
                myParameter.Value = fs;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

19 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

public override object GetSingle(string connectionString, string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        connection.Close();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                    }
                }
            }
            return null;
        }

19 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

public override SqlDataReader ExecuteReader(string connectionString, string strSQL)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(strSQL, connection);
            try
            {
                connection.Open();
                SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
        }

19 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

public override DataSet Query(string connectionString, string strSQLString)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                var ds = new DataSet();
                try
                {
                    connection.Open();
                    var command = new SqlDataAdapter(strSQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }

19 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

public override DataTable GetDataDable(string connectionString, string strSQLString)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                var ds = new DataTable();
                try
                {
                    connection.Open();
                    var command = new SqlDataAdapter(strSQLString, connection);
                    command.Fill(ds);
                }
                catch (SqlException ex)
                {
                    ;
                }
                return ds;
            }
        }

19 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

public override void ExecuteSqlTran(string connectionString, Hashtable SQLStringList)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    var cmd = new SqlCommand();
                    try
                    {
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            var cmdParms = (SqlParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

19 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

public override int ExecuteSqlTran1(string connectionString, Hashtable SQLStringList)
        {
            int val = 0;
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    var cmd = new SqlCommand();
                    try
                    {
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            var cmdParms = (SqlParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            val += cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return val;
        }

19 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

19 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

public override void WriteBlockDataToDB(string connectionString, DataTable dt)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
                    {
                        bulkCopy.DestinationTableName = dt.TableName;
                        foreach (DataColumn col in dt.Columns)
                        {
                            bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                        }
                        bulkCopy.WriteToServer(dt);
                        bulkCopy.Close();
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    connection.Close();
                }
            }
        }

19 View Source File : InteractWithSqlServerDatabase.cs
License : MIT License
Project Creator : aspose-gis

public static void RemoveSqlServerTable(string connectionString)
        {
            // ExStart: RemoveSqlServerTable
            // First, we create the connection to the SQL Server.
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                // Then, we preplaced this connection to Dataset.Open method in order to access tables in the SQL Server.
                using (var ds = Dataset.Open(connection, Drivers.SqlServer))
                {
                    // remove table with the name "features_table".
                    // It possible remove a table with geospatial data only.
                    // An exception isn't thrown if the table doesn't exist.
                    ds.RemoveLayer("features_table");
                }
            }
            // ExEnd: RemoveSqlServerTable
        }

19 View Source File : InteractWithSqlServerDatabase.cs
License : MIT License
Project Creator : aspose-gis

public static void CreateSqlServerTable(string connectionString)
        {
            // ExStart: CreateSqlServerTable
            // First, we create the connection to the SQL Server.
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                // Then, we preplaced this connection to Dataset.Open method in order to access tables in the SQL Server.
                using (var ds = Dataset.Open(connection, Drivers.SqlServer))
                {
                    // create table with the name "features_table" and fill it with data.
                    using (var layer = ds.CreateLayer("features_table"))
                    {
                        layer.Attributes.Add(new FeatureAttribute("name", AttributeDataType.String) { Width = 50 });

                        var feature = layer.ConstructFeature();
                        feature.SetValue("name", "Name1");
                        feature.Geometry = Geometry.FromText("POINT (10 20 30)");
                        layer.Add(feature);

                        feature = layer.ConstructFeature();
                        feature.SetValue("name", "Name2");
                        feature.Geometry = Geometry.FromText("POINT (-10 -20 -30)");
                        layer.Add(feature);
                    }
                }
            }
            // ExEnd: CreateSqlServerTable
        }

19 View Source File : InteractWithSqlServerDatabase.cs
License : MIT License
Project Creator : aspose-gis

public static void ListSqlServerTables(string connectionString)
        {
            // ExStart: ListSqlServerTables
            // First, we create the connection to the SQL Server.
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                // Then, we preplaced this connection to Dataset.Open method in order to access tables in the SQL Server.
                using (var ds = Dataset.Open(connection, Drivers.SqlServer))
                {
                    // Only spatial tables are exported as layers, so the following code will list all tables
                    // that have geometry column.
                    for (int i = 0; i < ds.LayersCount; ++i)
                    {
                        Console.WriteLine(ds.GetLayerName(i));
                    }
                }
            }
            // ExEnd: ListSqlServerTables
        }

19 View Source File : InteractWithSqlServerDatabase.cs
License : MIT License
Project Creator : aspose-gis

public static void ExportSqlServerTable(string connectionString)
        {
            // ExStart: ExportSqlServerTable
            var outputPath = Path.Combine(RunExamples.GetDataDir(), "sql_server_out.kml");

            // First, we create the connection to the SQL Server.
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                // Then, we preplaced this connection to Dataset.Open method in order to access tables in the SQL Server.
                using (var ds = Dataset.Open(connection, Drivers.SqlServer))
                {
                    // open table with the name "features_table" in SQL Server database and save it to the Kml.
                    using (var table = ds.OpenLayer("features_table"))
                    {
                        table.SaveTo(outputPath, Drivers.Kml);
                    }
                    Console.WriteLine("\nExport complete: " + outputPath);
                }
            }
            // ExEnd: ExportSqlServerTable
        }

19 View Source File : MsSqlRpcHandler.cs
License : MIT License
Project Creator : azist

protected virtual SqlConnection GetSqlConnection(JsonDataMap headers)
    {
      var result = new SqlConnection(ConnectString);
      result.Open();
      return result;
    }

19 View Source File : MsSqlTests.cs
License : MIT License
Project Creator : azist

private void clearAllTables()
        {
          using(var cnn = new SqlConnection(CONNECT_STRING))
          {
              cnn.Open();
              using(var cmd = cnn.CreateCommand())
              {
                cmd.CommandText = "delete from TBL_TUPLE; delete from TBL_PATIENT; delete from TBL_DOCTOR; delete from TBL_TYPES; delete from TBL_FULLGDID;";
                cmd.ExecuteNonQuery();
              }
          }

        }

19 View Source File : DatabaseConnector.cs
License : MIT License
Project Creator : Azure-Samples

public async Task<bool> StoreTranscriptionAsync(
            Guid transcriptionId,
            string locale,
            string fileName,
            float approximateCost,
            SpeechTranscript speechTranscript)
        {
            if (speechTranscript == null)
            {
                throw new ArgumentNullException(nameof(speechTranscript));
            }

            try
            {
                Connection = new SqlConnection(DBConnectionString);
                Connection.Open();

                var query = "INSERT INTO dbo.Transcriptions (ID, Locale, Name, Source, Timestamp, Duration, DurationInSeconds, NumberOfChannels, ApproximateCost)" +
                    " VALUES (@id, @locale, @name, @source, @timestamp, @duration, @durationInSeconds, @numberOfChannels, @approximateCost)";

                using (var command = new SqlCommand(query, Connection))
                {
                    command.Parameters.AddWithValue("@id", transcriptionId);
                    command.Parameters.AddWithValue("@locale", locale);
                    command.Parameters.AddWithValue("@name", fileName);
                    command.Parameters.AddWithValue("@source", speechTranscript.Source);
                    command.Parameters.AddWithValue("@timestamp", speechTranscript.Timestamp);
                    command.Parameters.AddWithValue("@duration", speechTranscript.Duration ?? string.Empty);
                    command.Parameters.AddWithValue("@durationInSeconds", TimeSpan.FromTicks(speechTranscript.DurationInTicks).TotalSeconds);
                    command.Parameters.AddWithValue("@numberOfChannels", speechTranscript.CombinedRecognizedPhrases.Count());
                    command.Parameters.AddWithValue("@approximateCost", approximateCost);

                    var result = await command.ExecuteNonQueryAsync().ConfigureAwait(false);

                    if (result < 0)
                    {
                        Logger.LogInformation("Did not store json in Db, command did not update table");
                    }
                    else
                    {
                        var phrasesByChannel = speechTranscript.RecognizedPhrases.GroupBy(t => t.Channel);

                        foreach (var phrases in phrasesByChannel)
                        {
                            var channel = phrases.Key;
                            await StoreCombinedRecognizedPhrasesAsync(transcriptionId, channel, speechTranscript, phrases).ConfigureAwait(false);
                        }
                    }
                }

                Connection.Close();
            }
            catch (SqlException e)
            {
                Logger.LogInformation(e.ToString());
                return false;
            }

            return true;
        }

19 View Source File : Program.cs
License : MIT License
Project Creator : BeginTry

private static void PopulateDataSet()
        {
            using (SqlConnection conn = new SqlConnection(SqlConnection.ConnectionString))
            {
                conn.Open();

                #region Iterate through SQL scripts
                foreach (FileInfo fi in ScriptsFolder.GetFiles("*.sql"))
                {
                    Console.ForegroundColor = ConsoleColor.Yellow;
                    Console.WriteLine(fi.Name);

                    foreach (string batch in GetBatches(fi))
                    {
                        if (string.IsNullOrEmpty(batch.Trim()))
                        {
                            continue;
                        }

                        using (SqlCommand cmd = new SqlCommand())
                        {
                            cmd.Connection = conn;
                            cmd.CommandType = System.Data.CommandType.Text;
                            cmd.CommandText = batch;
                            cmd.CommandTimeout = 0;

                            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                            {
                                using (System.Data.DataSet ds = new System.Data.DataSet())
                                {
                                    try
                                    {
                                        da.Fill(ds);
                                    }
                                    catch
                                    {
                                        Console.ForegroundColor = ConsoleColor.Cyan;
                                        Console.Write("\t");
                                        Console.WriteLine("Script failed: " + fi.Name);
                                    }

                                    foreach (DataTable dt in ds.Tables)
                                    {
                                        DataTable dtCopy = dt.Copy();
                                        dtCopy.TableName = fi.Name.Replace(fi.Extension, "");

                                        dtCopy.TableName = dtCopy.TableName.Substring(0, dtCopy.TableName.Length > 31 ? 31 : dtCopy.TableName.Length);
                                        AllDatatablesForExcel.Tables.Add(dtCopy);
                                    }
                                }
                            }
                        }
                    }
                }
                #endregion
            }
        }

19 View Source File : Catagoriesdal.cs
License : MIT License
Project Creator : bilalmehrban

public bool update(Catagoriesbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to update Data in DAtabase
                string query = "UPDATE tbl_catagories set [email protected],[email protected],[email protected]_by,[email protected]_date WHERE [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@id", u.id);
                cmd.Parameters.AddWithValue("@catagory", u.catagory);
                cmd.Parameters.AddWithValue("@supplier", u.supplier);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }

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

            return issucess;
        }

19 View Source File : Catagoriesdal.cs
License : MIT License
Project Creator : bilalmehrban

public bool delete(Catagoriesbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to delete Data in DAtabase
                string query = "DELETE FROM tbl_catagories WHERE [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@id", u.id);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Sucessfull
                    issucess = false;
                }
            }

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

            return issucess;
        }

19 View Source File : Catagoriesdal.cs
License : MIT License
Project Creator : bilalmehrban

public DataTable Search(string keyword)
        {
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            //TO hold the data from database
            DataTable dt = new DataTable();
            try
            {
                //SQL Query to search Data from DAtabase
                string query = "SELECT* FROM  tbl_catagories WHERE id Like '%" + keyword + "%' OR catagory like '%" + keyword + "%' OR supplier like '%" + keyword + "%' OR company like '%" + keyword + "%'";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Getting DAta from dAtabase
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //Database Connection Open
                conn.Open();
                //Fill Data in our DataTable
                adapter.Fill(dt);

            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }
            //Return the value in DataTable
            return dt;
        }

19 View Source File : Catagoriesdal.cs
License : MIT License
Project Creator : bilalmehrban

public bool exist(Catagoriesbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issuccess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to selecte Data from DAtabase
                string query = "select * from tbl_catagories where [email protected] AND [email protected] AND [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@catagory", u.catagory);
                cmd.Parameters.AddWithValue("@supplier", u.supplier);
                //Getting DAta from dAtabase
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //TO hold the data from database
                DataTable dt = new DataTable();
                //Database Connection Open
                conn.Open();
                //Fill Data in our DataTable
                adapter.Fill(dt);
                //Checking The rows in DataTable
                if (dt.Rows.Count > 0)
                {
                    //Data Exist
                    issuccess = true;
                }
                else
                {
                    //Data not exist
                    issuccess = false;
                }
            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }
            return issuccess;
        }

19 View Source File : invoicedal.cs
License : MIT License
Project Creator : bilalmehrban

public bool insert(invoicebll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to insert Data in DAtabase
                string query = "Insert into tbl_invoice(inv_no,customer_name,total_payable,paid_amount,discount,due_amount,change_amount,added_by,sales_date)Values(@inv_no,@customer_name,@total_payable,@paid_amount,@discount,@due_amount,@change_amount,@added_by,@sales_date)";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@inv_no", u.inv_no);
                cmd.Parameters.AddWithValue("@customer_name", u.customer_name);
                cmd.Parameters.AddWithValue("@total_payable", u.total_payable);
                cmd.Parameters.AddWithValue("@paid_amount", u.paid_amount);
                cmd.Parameters.AddWithValue("@discount", u.discount);
                cmd.Parameters.AddWithValue("@due_amount", u.due_amount);
                cmd.Parameters.AddWithValue("@change_amount", u.change_amount);
                cmd.Parameters.AddWithValue("@added_by", u.added_by);
                cmd.Parameters.AddWithValue("@sales_date", u.sales_date);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }

See More Examples