Here are the examples of the csharp api System.Data.SqlClient.SqlConnection.Close() taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.
597 Examples
19
Source : SqlServerIo.cs
with MIT License
from 0ffffffffh
with MIT License
from 0ffffffffh
private void Close()
{
if (conn != null)
{
if (Ready)
conn.Close();
conn.Dispose();
conn = null;
}
}
19
Source : SqlServerDataBase.cs
with Mozilla Public License 2.0
from agebullhu
with Mozilla Public License 2.0
from agebullhu
public void Close()
{
if (_connection == null)
{
return;
}
try
{
lock (LockData)
{
if (_connection.State == ConnectionState.Open)
{
//Trace.WriteLine("Close Connection", "SqlServerDataBase");
_connection.Close();
}
//Trace.WriteLine(_count--, "Close");
_connection = null;
}
}
catch (Exception exception)
{
_connection?.Dispose();
Trace.WriteLine("Close Error", "SqlServerDataBase");
LogRecorder.Error(exception.ToString());
}
}
19
Source : SqlServerDataBase.cs
with Mozilla Public License 2.0
from agebullhu
with Mozilla Public License 2.0
from agebullhu
private void Close(SqlConnection connection)
{
//int cnt;
lock (Connections)
{
if (!Connections.Remove(connection))
return;
//cnt = Connections.Count;
}
if (connection == null)
{
return;
}
if (connection.State == ConnectionState.Open)
{
try
{
connection.Close();
}
catch (Exception exception)
{
LogRecorderX.Exception(exception);
}
}
try
{
connection.Dispose();
}
catch (Exception exception)
{
LogRecorderX.Exception(exception);
}
}
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 : SqlServerHealthCheck.cs
with GNU Affero General Public License v3.0
from AnyStatus
with GNU Affero General Public License v3.0
from AnyStatus
public async Task Handle(HealthCheckRequest<SqlServerConnection> request, CancellationToken cancellationToken)
{
var connection = new SqlConnection(request.DataContext.ConnectionString);
try
{
await connection.OpenAsync().ConfigureAwait(false);
request.DataContext.State = State.Ok;
}
catch (SqlException)
{
request.DataContext.State = State.Failed;
}
finally
{
if (connection != null)
connection.Close();
}
}
19
Source : DatabaseOperation.cs
with MIT License
from ap0405140
with MIT License
from ap0405140
public DataTable Query(string sTsql, bool closeconnect = true)
{
DataTable dt;
try
{
RefreshConnect();
sda = new SqlDataAdapter(sTsql, scn);
sda.SelectCommand.CommandTimeout = 0;
sds = new DataSet();
sda.Fill(sds);
dt = (sds != null && sds.Tables.Count > 0 ? sds.Tables[0] : null);
return dt;
}
catch (Exception ex)
{
throw new Exception("Run SQL: \r\n" + sTsql
+ "\r\n\r\n" + "ExceptionSource: " + ex.Source
+ "\r\n\r\n" + "ExceptionMessage: " + ex.Message);
}
finally
{
if (closeconnect == true)
{
if (scn.State == ConnectionState.Open)
{
scn.Close();
}
scn.Dispose();
}
}
}
19
Source : DatabaseOperation.cs
with MIT License
from ap0405140
with MIT License
from ap0405140
public string Query11(string sTsql, bool closeconnect = true)
{
string sReturn;
object oTemp;
try
{
RefreshConnect();
scm = new SqlCommand(sTsql, scn);
oTemp = scm.ExecuteScalar();
sReturn = (oTemp == null ? string.Empty : oTemp.ToString());
}
catch (Exception ex)
{
sReturn = null;
throw new Exception("Run SQL: \r\n" + sTsql
+ "\r\n\r\n" + "ExceptionSource: " + ex.Source
+ "\r\n\r\n" + "ExceptionMessage: " + ex.Message);
}
finally
{
if (closeconnect == true)
{
if (scn.State == ConnectionState.Open)
{
scn.Close();
}
scn.Dispose();
}
}
return sReturn;
}
19
Source : DatabaseOperation.cs
with MIT License
from ap0405140
with MIT License
from ap0405140
public void ExecuteSP_Parameters(string SPname,
ref List<SqlParameter> pParameters)
{
try
{
RefreshConnect();
scm = new SqlCommand(SPname, scn);
scm.CommandType = CommandType.StoredProcedure;
scm.Parameters.AddRange(pParameters.ToArray());
scm.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
if (scn.State == ConnectionState.Open)
{
scn.Close();
}
scn.Dispose();
}
}
19
Source : DatabaseOperation.cs
with MIT License
from ap0405140
with MIT License
from ap0405140
public DataTable ExecuteSP_Datatable(string SPname, ref Dictionary<string, SqlParameter> pParameter)
{
DataTable dt;
dt = null;
try
{
RefreshConnect();
scm = new SqlCommand(SPname, scn);
scm.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter pp in pParameter.Values)
{
scm.Parameters.Add(pp);
}
sda = new SqlDataAdapter(scm);
sda.SelectCommand.CommandTimeout = 0;
sds = new DataSet();
sda.Fill(sds);
if (sds != null && sds.Tables.Count > 0)
{
dt = sds.Tables[0];
}
}
catch (Exception ex)
{
throw new Exception("Run SQL: \r\n" + SPname + " " + string.Join(",", pParameter.Values.Select(p => p.Value.ToString()).ToArray())
+ "\r\n\r\n" + "ExceptionSource: " + ex.Source
+ "\r\n\r\n" + "ExceptionMessage: " + ex.Message);
}
finally
{
if (scn.State == ConnectionState.Open)
{
scn.Close();
}
scn.Dispose();
}
return dt;
}
19
Source : DatabaseOperation.cs
with MIT License
from ap0405140
with MIT License
from ap0405140
public void ExecuteSQL(string sTsql, bool closeconnect = true)
{
try
{
RefreshConnect();
scm = new SqlCommand(sTsql, scn);
scm.CommandTimeout = 0;
scm.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception("Run SQL: \r\n" + sTsql
+ "\r\n\r\n" + "ExceptionSource: " + ex.Source
+ "\r\n\r\n" + "ExceptionMessage: " + ex.Message);
}
finally
{
if (closeconnect == true)
{
if (scn.State == ConnectionState.Open)
{
scn.Close();
}
scn.Dispose();
}
}
}
19
Source : DatabaseOperation.cs
with MIT License
from ap0405140
with MIT License
from ap0405140
public bool ExecuteSQL(string sTsql, out string sMessage, bool bFinallyClose = true)
{
bool y;
try
{
sMessage = string.Empty;
RefreshConnect();
y = false;
scm = new SqlCommand(sTsql, scn);
scm.CommandTimeout = 0;
scm.ExecuteNonQuery();
y = true;
}
catch (Exception ex)
{
y = false;
sMessage = ex.Source + " " + ex.Message;
}
finally
{
if (bFinallyClose == true)
{
if (scn.State == ConnectionState.Open)
{
scn.Close();
}
scn.Dispose();
}
}
return y;
}
19
Source : DatabaseOperation.cs
with MIT License
from ap0405140
with MIT License
from ap0405140
public bool ExecuteSQLWithIdenreplacedyValue(string sTsql, ref string NewID, ref string sMessage)
{
bool s;
object r;
try
{
s = false;
sMessage = string.Empty;
RefreshConnect();
sTsql = sTsql + "select NewID=@@idenreplacedy; ";
scm = new SqlCommand(sTsql, scn);
scm.CommandTimeout = 0;
r = scm.ExecuteScalar();
if (r != null)
{
NewID = Convert.ToInt64(r).ToString();
s = true;
}
else
{
NewID = null;
sMessage = "ExecuteScalar return null.";
}
}
catch (Exception ex)
{
s = false;
NewID = null;
sMessage = "Run SQL: \r\n" + sTsql
+ "\r\n\r\n" + "ExceptionSource: " + ex.Source
+ "\r\n\r\n" + "ExceptionMessage: " + ex.Message;
}
finally
{
if (scn.State == ConnectionState.Open)
{
scn.Close();
}
scn.Dispose();
}
return s;
}
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 : 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 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 : 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 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 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 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 : 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 : 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;
}
19
Source : invoicedetailsdal.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_invoice WHERE customer_name Like '%" + keyword + "%' OR inv_no 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 : logindal.cs
with MIT License
from bilalmehrban
with MIT License
from bilalmehrban
public bool login(loginbll 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 check login
string query = "select * from tbl_users where user_name=@user_name AND preplacedword=@preplacedword AND user_type=@user_type";
//Creating SQL Command to preplaced value
SqlCommand cmd = new SqlCommand(query, conn);
//Preplaceding Values to the Variables
cmd.Parameters.AddWithValue("@user_name", u.user_name);
cmd.Parameters.AddWithValue("@preplacedword", u.preplacedword);
cmd.Parameters.AddWithValue("@user_type", u.user_type);
//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)
{
//Login Sucessful
issuccess = true;
}
else
{
//Login Failed
issuccess = false;
}
}
catch (Exception ex)
{
//Throw Message if any error occurs
MessageBox.Show(ex.Message);
}
finally
{
//Closing Connection
conn.Close();
}
return issuccess;
}
19
Source : Manage_Productsdal.cs
with MIT License
from bilalmehrban
with MIT License
from bilalmehrban
public DataTable select()
{
//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 Get Data From DAtabase
string query = "select* from tbl_stock";
//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 : Manage_Productsdal.cs
with MIT License
from bilalmehrban
with MIT License
from bilalmehrban
public bool update_(Manage_Productsbll 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_stock set product_name=@product_name,colour_code=@colour_code,supplier=@supplier,catagory=@catagory,purchase_price=@purchase_price,retail_price=@retail_price,type=@type,quanreplacedy=@quanreplacedy 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("@product_name", u.product_name);
cmd.Parameters.AddWithValue("@colour_code", u.colour_code);
cmd.Parameters.AddWithValue("@supplier", u.supplier);
cmd.Parameters.AddWithValue("@catagory", u.catagory);
cmd.Parameters.AddWithValue("@purchase_price", u.purchase_price);
cmd.Parameters.AddWithValue("@retail_price", u.retail_price);
cmd.Parameters.AddWithValue("@type", u.type);
cmd.Parameters.AddWithValue("@quanreplacedy", u.quanreplacedy);
//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 : Manage_Productsdal.cs
with MIT License
from bilalmehrban
with MIT License
from bilalmehrban
public bool exist(Manage_Productsbll 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_stock where product_name=@product_name AND supplier=@supplier AND catagory=@catagory AND colour_code=@colour_code AND type=@type";
//For Executing Command
SqlCommand cmd = new SqlCommand(query, conn);
//Preplaceding Values to the Variables
cmd.Parameters.AddWithValue("@type", u.type);
cmd.Parameters.AddWithValue("@product_name", u.product_name);
cmd.Parameters.AddWithValue("@supplier", u.supplier);
cmd.Parameters.AddWithValue("@catagory", u.catagory);
cmd.Parameters.AddWithValue("@colour_code", u.colour_code);
//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 : product_detailsdal.cs
with MIT License
from bilalmehrban
with MIT License
from bilalmehrban
public DataTable MonthlyAndDailyData(string d1, string d2)
{
//MEthod to connect Database
connclreplaced c = new connclreplaced();
SqlConnection conn = new SqlConnection(c.connection);
//TO hold the data from database
DataTable dt = new DataTable();
//string d1 = DateTime.Now.ToString();
try
{
//SQL Query to insert Data in DAtabase
string query = "SELECT* FROM tbl_productdetails WHERE added_date BETWEEN '" + d2 + "' AND '" + d1 + "' ";
//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 dt;
}
19
Source : product_detailsdal.cs
with MIT License
from bilalmehrban
with MIT License
from bilalmehrban
public bool delete(product_detailsbll 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_productdetails 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 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 : sales_analyticsdal.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_productdetails WHERE product_id Like '%" + keyword + "%' OR product_name like '%" + keyword + "%' OR inv_no like '%" + keyword + "%' OR code 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 DataTable select()
{
//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 Get Data From DAtabase
string query = "select* from tbl_catagories";
//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 insert(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 insert Data in DAtabase
string query = "Insert into tbl_catagories(catagory,supplier,added_by,added_date)Values(@catagory,@supplier,@added_by,@added_date)";
//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);
cmd.Parameters.AddWithValue("@added_by", u.added_by);
cmd.Parameters.AddWithValue("@added_date", u.added_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;
}
19
Source : invoicedal.cs
with MIT License
from bilalmehrban
with MIT License
from bilalmehrban
public DataTable Search(string id)
{
//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_invoice WHERE inv_no = " + id ;
//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 : invoicedal.cs
with MIT License
from bilalmehrban
with MIT License
from bilalmehrban
public bool delete(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 delete Data in DAtabase
string query = "DELETE FROM tbl_invoice 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 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 : invoicedal.cs
with MIT License
from bilalmehrban
with MIT License
from bilalmehrban
public bool update(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 = "UPDATE tbl_invoice set inv_no=@inv_no, customer_name=@customer_name,total_payable=@total_payable,paid_amount=@paid_amount,discount=@discount,due_amount=@due_amount,change_amount=@change_amount,added_by=@added_by,sales_date=@sales_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("@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;
}
19
Source : invoicedetailsdal.cs
with MIT License
from bilalmehrban
with MIT License
from bilalmehrban
public DataTable select()
{
//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 Get Data From DAtabase
string query = "select* from tbl_invoice";
//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 : Manage_Productsdal.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_stock WHERE colour_code Like '%" + keyword + "%' OR product_name like '%" + keyword + "%' OR supplier like '%" + keyword + "%' OR catagory like '%" + keyword + "%' OR id 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 : Manage_Productsdal.cs
with MIT License
from bilalmehrban
with MIT License
from bilalmehrban
public bool UpdateQuanreplacedy(int id, decimal quanreplacedy)
{
//Create a Boolean Variable and Set its value to false
bool success = false;
//MEthod to connect Database
connclreplaced c = new connclreplaced();
SqlConnection conn = new SqlConnection(c.connection);
try
{
//Write the SQL Query to Update Qty
string sql = "UPDATE tbl_stock SET quanreplacedy=@quanreplacedy WHERE id=@id";
//Create SQL Command to Preplaced the calue into Queyr
SqlCommand cmd = new SqlCommand(sql, conn);
//Preplaceding the VAlue trhough parameters
cmd.Parameters.AddWithValue("@quanreplacedy", quanreplacedy);
cmd.Parameters.AddWithValue("@id", id);
//Open Database Connection
conn.Open();
//Create Int Variable and Check whether the query is executed Successfully or not
int rows = cmd.ExecuteNonQuery();
//Lets check if the query is executed Successfully or not
if (rows > 0)
{
//Query Executed Successfully
success = true;
}
else
{
//Failed to Execute Query
success = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return success;
}
19
Source : Manage_Productsdal.cs
with MIT License
from bilalmehrban
with MIT License
from bilalmehrban
public Manage_Productsbll GetProductpriceFromId(string id)
{
//First Create an Object of BLL and REturn it
Manage_Productsbll p = new Manage_Productsbll();
//MEthod to connect Database
connclreplaced c = new connclreplaced();
SqlConnection conn = new SqlConnection(c.connection);
//Data TAble to Holdthe data temporarily
DataTable dt = new DataTable();
try
{
//SQL Query to Get id based on Name
string sql = "SELECT purchase_price FROM tbl_stock WHERE id='" + id + "'";
//Create the SQL Data Adapter to Execute the Query
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
conn.Open();
//Preplaceding the VAlue from Adapter to DAtatable
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
//Preplaced the value from dt to variable
p.purchase_price = dt.Rows[0]["purchase_price"].ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return p;
}
19
Source : userdal.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_users WHERE id Like '%" + keyword + "%' OR user_name like '%" + keyword + "%' OR user_type like '%" + keyword + "%' OR adress 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 : userdal.cs
with MIT License
from bilalmehrban
with MIT License
from bilalmehrban
public bool exist(usersbll 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_users where user_name=@user_name AND cnic=@cnic AND email=@email AND phone_no=@phone_no ";
//For Executing Command
SqlCommand cmd = new SqlCommand(query, conn);
//Preplaceding Values to the Variables
cmd.Parameters.AddWithValue("@user_name", u.user_name);
cmd.Parameters.AddWithValue("@cnic", u.cnic);
cmd.Parameters.AddWithValue("@email", u.email);
cmd.Parameters.AddWithValue("@phone_no", u.phone_no);
//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 : userdal.cs
with MIT License
from bilalmehrban
with MIT License
from bilalmehrban
public usersbll GetIDFromUsername(string username)
{
//Connecting user BLL for getting id
usersbll u = new usersbll();
//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 get Data from DAtabase
string sql = "SELECT id FROM tbl_users WHERE user_name='" + username + "'";
//Getting DAta from dAtabase
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
//Database Connection Open
conn.Open();
//Fill Data in our DataTable
adapter.Fill(dt);
//Checking The rows in DataTable
if (dt.Rows.Count > 0)
{
//Getting id from DataTable
u.id = int.Parse(dt.Rows[0]["id"].ToString());
}
}
catch (Exception ex)
{
//Throw Message if any error occurs
MessageBox.Show(ex.Message);
}
finally
{
//Closing Connection
conn.Close();
}
return u;
}
19
Source : product_detailsdal.cs
with MIT License
from bilalmehrban
with MIT License
from bilalmehrban
public DataTable Search(string inv_no)
{
//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_productdetails WHERE inv_no = " + inv_no ;
//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 : suppliersdal.cs
with MIT License
from bilalmehrban
with MIT License
from bilalmehrban
public bool exist(suppliersbll 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_suppliers where email=@email AND phone_no=@phone_no";
//For Executing Command
SqlCommand cmd = new SqlCommand(query, conn);
//Preplaceding Values to the Variables
cmd.Parameters.AddWithValue("@email", u.email);
cmd.Parameters.AddWithValue("@phone_no", u.phone_no);
//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 : userdal.cs
with MIT License
from bilalmehrban
with MIT License
from bilalmehrban
public bool delete(usersbll 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_users 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 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