System.Data.SqlClient.SqlConnection.Close()

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 7

19 View Source File : invoicedal.cs
License : MIT License
Project Creator : 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 [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@id", u.id);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }

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

            return issucess;
        }

19 View Source File : userdal.cs
License : MIT License
Project Creator : 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 View Source File : Catagoriesdal.cs
License : MIT License
Project Creator : bilalmehrban

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

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

            return issucess;
        }

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

public DataTable Search(string keyword)
        {
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            //TO hold the data from database 
            DataTable dt = new DataTable();
            try
            {
                //SQL Query to search Data from DAtabase
                string query = "SELECT* FROM  tbl_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 View Source File : invoicedal.cs
License : MIT License
Project Creator : bilalmehrban

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

            return issucess;
        }

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

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

19 View Source File : Manage_Productsdal.cs
License : MIT License
Project Creator : 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 [email protected] WHERE [email protected]";

                //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 View Source File : DatabaseOperation.cs
License : MIT License
Project Creator : 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 View Source File : userdal.cs
License : MIT License
Project Creator : 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 [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@id", u.id);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }

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

            return issucess;
        }

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

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

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


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

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

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

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

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

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

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

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

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

			return file.FullName;
		}

19 View Source File : product_detailsdal.cs
License : MIT License
Project Creator : 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 View Source File : logindal.cs
License : MIT License
Project Creator : 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 [email protected]_name AND [email protected] AND [email protected]_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 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

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

19 View Source File : Catagoriesdal.cs
License : MIT License
Project Creator : 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 View Source File : DatabaseOperation.cs
License : MIT License
Project Creator : 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 View Source File : invoicedetailsdal.cs
License : MIT License
Project Creator : 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 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

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

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

public DataTable Search(string keyword)
        {
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            //TO hold the data from database 
            DataTable dt = new DataTable();
            try
            {
                //SQL Query to search Data from DAtabase
                string query = "SELECT* FROM  tbl_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 View Source File : SqlServerHealthCheck.cs
License : GNU Affero General Public License v3.0
Project Creator : 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 View Source File : suppliersdal.cs
License : MIT License
Project Creator : 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 protected] AND [email protected]_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 View Source File : Catagoriesdal.cs
License : MIT License
Project Creator : bilalmehrban

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

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

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

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

19 View Source File : DatabaseOperation.cs
License : MIT License
Project Creator : 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 View Source File : invoicedetailsdal.cs
License : MIT License
Project Creator : bilalmehrban

public DataTable Search(string keyword)
        {
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            //TO hold the data from database 
            DataTable dt = new DataTable();
            try
            {
                //SQL Query to search Data from DAtabase
                string query = "SELECT* FROM  tbl_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 View Source File : MssqlSchema.cs
License : MIT License
Project Creator : aquilahkj

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

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

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

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

19 View Source File : Manage_Productsdal.cs
License : MIT License
Project Creator : 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 [email protected]_name AND [email protected] AND [email protected] AND [email protected]_code AND [email protected]";
                //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 View Source File : Manage_Productsdal.cs
License : MIT License
Project Creator : 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 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

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

19 View Source File : product_detailsdal.cs
License : MIT License
Project Creator : 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 [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@id", u.id);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }

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

            return issucess;
        }

19 View Source File : DatabaseOperation.cs
License : MIT License
Project Creator : 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 View Source File : Catagoriesdal.cs
License : MIT License
Project Creator : 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 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

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

19 View Source File : invoicedal.cs
License : MIT License
Project Creator : 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 View Source File : DesignerContextMenuAddIn.cs
License : GNU General Public License v3.0
Project Creator : anderson-joyle

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

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

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

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

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

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

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

                        conn.Open();

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

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

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

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

                        conn.Close();
                    }

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

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

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

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

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

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

19 View Source File : invoicedal.cs
License : MIT License
Project Creator : 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 [email protected]_no, [email protected]_name,[email protected]_payable,[email protected]_amount,[email protected],[email protected]_amount,[email protected]_amount,[email protected]_by,[email protected]_date WHERE [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@id", u.id);
                cmd.Parameters.AddWithValue("@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 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

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

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

public DataTable Search(string keyword)
        {
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            //TO hold the data from database 
            DataTable dt = new DataTable();
            try
            {
                //SQL Query to search Data from DAtabase
                string query = "SELECT* FROM  tbl_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 View Source File : DatabaseOperation.cs
License : MIT License
Project Creator : 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 View Source File : Manage_Productsdal.cs
License : MIT License
Project Creator : 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 View Source File : DatabaseConnector.cs
License : MIT License
Project Creator : Azure-Samples

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

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

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

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

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

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

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

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

            return true;
        }

19 View Source File : userdal.cs
License : MIT License
Project Creator : 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 [email protected]_name AND [email protected] AND [email protected] AND [email protected]_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 View Source File : SqlServerDataBase.cs
License : Mozilla Public License 2.0
Project Creator : 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 View Source File : product_detailsdal.cs
License : MIT License
Project Creator : 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 View Source File : Catagoriesdal.cs
License : MIT License
Project Creator : bilalmehrban

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

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

            return issucess;
        }

19 View Source File : DatabaseOperation.cs
License : MIT License
Project Creator : 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 [email protected]@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 View Source File : SqlServerIo.cs
License : MIT License
Project Creator : 0ffffffffh

private void Close()
        {
            if (conn != null)
            {
                if (Ready)
                    conn.Close();

                conn.Dispose();
                conn = null;
            }
        }

19 View Source File : SqlServerDataBase.cs
License : Mozilla Public License 2.0
Project Creator : 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 View Source File : DatabaseOperation.cs
License : MIT License
Project Creator : 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 View Source File : SqlServerHelper.cs
License : Apache License 2.0
Project Creator : aryice

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

19 View Source File : Manage_Productsdal.cs
License : MIT License
Project Creator : 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 [email protected]_name,[email protected]_code,[email protected],[email protected],[email protected]_price,[email protected]_price,[email protected],[email protected] WHERE [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@id", u.id);
                cmd.Parameters.AddWithValue("@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;
        }

See More Examples