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
19
Source : ConnectionTester.cs
with MIT License
from afaniuolo
with MIT License
from 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
Source : SqlServerDataBase.cs
with Mozilla Public License 2.0
from agebullhu
with Mozilla Public License 2.0
from 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
Source : SqlServerDataBase.cs
with Mozilla Public License 2.0
from agebullhu
with Mozilla Public License 2.0
from 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
Source : SqlServerDataBase.cs
with Mozilla Public License 2.0
from agebullhu
with Mozilla Public License 2.0
from 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
Source : DesignerContextMenuAddIn.cs
with GNU General Public License v3.0
from anderson-joyle
with GNU General Public License v3.0
from 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
Source : Program.cs
with GNU General Public License v3.0
from AndreiFedarets
with GNU General Public License v3.0
from 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
Source : Database.cs
with GNU General Public License v3.0
from andysal
with GNU General Public License v3.0
from 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
Source : Database.cs
with GNU General Public License v3.0
from andysal
with GNU General Public License v3.0
from 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
Source : Database.cs
with GNU General Public License v3.0
from andysal
with GNU General Public License v3.0
from 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
Source : NskReadModelFacadeImpl.cs
with GNU General Public License v3.0
from andysal
with GNU General Public License v3.0
from 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
Source : NskReadModelFacadeImpl.cs
with GNU General Public License v3.0
from andysal
with GNU General Public License v3.0
from 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
Source : DatabaseOperation.cs
with MIT License
from ap0405140
with MIT License
from 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
Source : Sample12.cs
with Apache License 2.0
from Appdynamics
with Apache License 2.0
from 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
Source : Sample3.cs
with Apache License 2.0
from Appdynamics
with Apache License 2.0
from 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
Source : Sample4.cs
with Apache License 2.0
from Appdynamics
with Apache License 2.0
from 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
Source : Sample4.cs
with Apache License 2.0
from Appdynamics
with Apache License 2.0
from 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
Source : MssqlSchema.cs
with MIT License
from aquilahkj
with MIT License
from 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
Source : SqlServerHelper.cs
with Apache License 2.0
from aryice
with Apache License 2.0
from 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
Source : SqlServerHelper.cs
with Apache License 2.0
from aryice
with Apache License 2.0
from 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
Source : SqlServerHelper.cs
with Apache License 2.0
from aryice
with Apache License 2.0
from 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
Source : SqlServerHelper.cs
with Apache License 2.0
from aryice
with Apache License 2.0
from 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
Source : SqlServerHelper.cs
with Apache License 2.0
from aryice
with Apache License 2.0
from 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
Source : SqlServerHelper.cs
with Apache License 2.0
from aryice
with Apache License 2.0
from 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
Source : SqlServerHelper.cs
with Apache License 2.0
from aryice
with Apache License 2.0
from 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
Source : SqlServerHelper.cs
with Apache License 2.0
from aryice
with Apache License 2.0
from 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
Source : SqlServerHelper.cs
with Apache License 2.0
from aryice
with Apache License 2.0
from 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
Source : SqlServerHelper.cs
with Apache License 2.0
from aryice
with Apache License 2.0
from 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
Source : SqlServerHelper.cs
with Apache License 2.0
from aryice
with Apache License 2.0
from 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
Source : SqlServerHelper.cs
with Apache License 2.0
from aryice
with Apache License 2.0
from 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
Source : SqlServerHelper.cs
with Apache License 2.0
from aryice
with Apache License 2.0
from 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
Source : SqlServerHelper.cs
with Apache License 2.0
from aryice
with Apache License 2.0
from 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
Source : SqlServerHelper.cs
with Apache License 2.0
from aryice
with Apache License 2.0
from 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
Source : SqlServerHelper.cs
with Apache License 2.0
from aryice
with Apache License 2.0
from 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
Source : SqlServerHelper.cs
with Apache License 2.0
from aryice
with Apache License 2.0
from 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
Source : SqlServerHelper.cs
with Apache License 2.0
from aryice
with Apache License 2.0
from 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
Source : SqlServerHelper.cs
with Apache License 2.0
from aryice
with Apache License 2.0
from 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
Source : SqlServerHelper.cs
with Apache License 2.0
from aryice
with Apache License 2.0
from 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
Source : InteractWithSqlServerDatabase.cs
with MIT License
from aspose-gis
with MIT License
from 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
Source : InteractWithSqlServerDatabase.cs
with MIT License
from aspose-gis
with MIT License
from 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
Source : InteractWithSqlServerDatabase.cs
with MIT License
from aspose-gis
with MIT License
from 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
Source : InteractWithSqlServerDatabase.cs
with MIT License
from aspose-gis
with MIT License
from 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
Source : MsSqlRpcHandler.cs
with MIT License
from azist
with MIT License
from azist
protected virtual SqlConnection GetSqlConnection(JsonDataMap headers)
{
var result = new SqlConnection(ConnectString);
result.Open();
return result;
}
19
Source : MsSqlTests.cs
with MIT License
from azist
with MIT License
from 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
Source : DatabaseConnector.cs
with MIT License
from Azure-Samples
with MIT License
from 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
Source : Program.cs
with MIT License
from BeginTry
with MIT License
from 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
Source : Catagoriesdal.cs
with MIT License
from bilalmehrban
with MIT License
from 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 catagory=@catagory,supplier=@supplier,added_by=@added_by,added_date=@added_date WHERE id=@id";
//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
Source : Catagoriesdal.cs
with MIT License
from bilalmehrban
with MIT License
from 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 id=@id";
//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
Source : Catagoriesdal.cs
with MIT License
from bilalmehrban
with MIT License
from 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
Source : Catagoriesdal.cs
with MIT License
from bilalmehrban
with MIT License
from 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 catagory=@catagory AND supplier=@supplier AND company=@company";
//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
Source : invoicedal.cs
with MIT License
from bilalmehrban
with MIT License
from 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