C# PsqlConnection.Dispose方法代码示例

        private void BusinessUnitTracking_Load(object sender, EventArgs e)
        {
            this.WindowState = FormWindowState.Maximized;
            this.ControlBox = false;
            using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
            {
                oConn.Open();
                string sSql = "select UnitId, concat(concat(UnitName,' - '),UnitDescription) 'Name'  from SOLMBS order by UnitID";
                DataSet dsUnits = Solsage_Process_Management_System.Classes.Connect.getDataSet(sSql, "Unit", oConn);
                selUnits.DataSource = dsUnits.Tables["Unit"];
                selUnits.DisplayMember = "Name";
                this.txtUnitID.DataBindings.Add(new Binding("Text", dsUnits.Tables["Unit"], "UnitID", true));
                if (loadOperators(oConn))
                {
                    selOperators_SelectedIndexChanged(sender, e);
                    viewSheet(oConn);
                }
                oConn.Dispose();
            }
            DataGridViewCellStyle dgCellStyle = new DataGridViewCellStyle();
            dgCellStyle.BackColor = Color.LightGray;
            dgInput.Columns[0].DefaultCellStyle = dgCellStyle;
            dgInput.Columns[1].DefaultCellStyle = dgCellStyle;
            dgInput.Columns[2].DefaultCellStyle = dgCellStyle;
            dgInput.Columns["cl24"].DefaultCellStyle = dgCellStyle;
            dgInput.Columns["clEntryId"].Width = 0;

            this.txtUnitID.TextChanged += new System.EventHandler(this.txtUnitID_TextChanged);
            this.selOperators.SelectedIndexChanged += new System.EventHandler(this.selOperators_SelectedIndexChanged);
        }

        private void cmdDeleteKit_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("This will permanently delete the current selected Kit Items. Are you sure?", "Delete Kit Items", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.Yes)
            {
                using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
                {
                    oConn.Open();
                    foreach (DataGridViewRow dgRow in dgKitMain.Rows)
                    {
                        // Main Kit code
                        if (dgRow.Cells[0].Value != null)
                        {
                            string sDelSql = "delete From SOLKIT where KitName = '" + txtKitName.Text.Trim() + "'";
                            int delRet = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sDelSql, oConn).ExecuteNonQuery();

                            //Kit Details
                            string sDelSql2 = "delete From SOLKITDET where FkMainItemCode = '" + dgRow.Cells["clMainItemCode"].Value.ToString() + "'";
                            int delRet2 = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sDelSql2, oConn).ExecuteNonQuery();
                        }
                    }
                    oConn.Dispose();
                }
                dgKitDetails.Rows.Clear();
                dgKitMain.Rows.Clear();
                txtKitName.Text = "";
            }
            dgKitDetails.Rows.Add();
            dgKitMain.Rows.Add();
        }

        private void cmdFinish_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("This will add selected stock to store. Continue?", "Recieve Stock", MessageBoxButtons.OKCancel, MessageBoxIcon.Information) == DialogResult.OK)
            {
                using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
                {
                    oConn.Open();

                    foreach (DataGridViewRow dgRow in dgReciveOldStock.Rows)
                    {

                        string sSql = "Insert into SOLMSM ";
                        sSql += "(GeneralItemCode,GenItemDescription,QuantityScanned,UserName,ItemDate)";
                        sSql += "Values ('" + dgRow.Cells["clItemCode"].Value.ToString().Trim() + "','" + dgRow.Cells["clItemDescription"].Value.ToString().Trim() + "','" + dgRow.Cells["clQuantity"].Value.ToString().Trim() + "', '" + Solsage_Process_Management_System.Classes.Global.sLogedInUserName.ToString() + "', '" + lblDateTime.Text + "')";
                        int iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();

                        sSql = "Insert into SOLMROS ";
                        sSql += "(ItemCode,ItemDescription,Quantity,UserName,Date)";
                        sSql += "Values ('" + dgRow.Cells["clItemCode"].Value.ToString().Trim() + "','" + dgRow.Cells["clItemDescription"].Value.ToString().Trim() + "','" + dgRow.Cells["clQuantity"].Value.ToString().Trim() + "', '" + Solsage_Process_Management_System.Classes.Global.sLogedInUserName.ToString() + "', '" + lblDateTime.Text + "') ";
                        iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();

                    }

                    oConn.Dispose();
                }
                this.Close();
            }
        }

        private void cmdlogin_Click(object sender, EventArgs e)
        {
            bool bMatch = false;

            using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
            {
                oConn.Open();
                string sSql = "Select UserName,Psswrd,Code,UserType from SOLUS where UserName = '" + txtUserName.Text + "'";
                PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteReader();
                while (rdReader.Read())
                {
                    if (txtPassword.Text == rdReader["Psswrd"].ToString().Trim())
                    {
                        bMatch = true;
                        sUserCode = rdReader["Code"].ToString().Trim();
                    }
                }
                rdReader.Close();
                oConn.Dispose();
            }

             if (bMatch == true)
            {
                this.DialogResult = DialogResult.OK;
                this.Close();
            }
            else
            {
                MessageBox.Show("Username and Password does not match.  Please try again.");
                cmdClearFields();
                this.ActiveControl = txtUserName;
            }
        }

        public static Decimal CalculateCommission(string sCommissionType, string sCommissionCategory, Decimal dInvoiceValue)
        {
            decimal dTotalCommission = 0;
            decimal dCategory = 0;
            decimal dCommissionType = 0;
            //get category percentage
            using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
            {
                oConn.Open();
                String sSql = "Select CategoryPercentage from SOLMARKCAT where CategoryName = '" + sCommissionCategory + "'";
                string sPercentage = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteScalar().ToString();
                dCategory = Convert.ToDecimal(sPercentage) / 100;

                try
                {
                    sSql = "Select CommissionPercentage From SOLMARKCOMTIPE where CommissionTipe = '" + sCommissionType + "'";
                    string sComPercentage = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteScalar().ToString();
                    dCommissionType = Convert.ToDecimal(sComPercentage) / 100;
                }
                catch
                {
                    dCommissionType = 1;
                }

                oConn.Dispose();
            }
            dTotalCommission = dInvoiceValue * dCategory * dCommissionType;

            return dTotalCommission;
        }

        private void LoadAccountDetails()
        {
            Cursor = System.Windows.Forms.Cursors.WaitCursor;
            dgAccountDetails.Rows.Clear();

            using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
            {
                oConn.Open();
                String sSql = "Select Category, CommissionType, InvoiceValue, CustomerCode, DocumentNumber From SOLMARKTRANS where MarketerCode = '" + txtMarketer.Text.Trim() + "' order by CustomerCode";
                PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteReader();
                while (rdReader.Read())
                {
                    int n = dgAccountDetails.Rows.Add();
                    dgAccountDetails.Rows[n].Cells["clCustomerCode"].Value = rdReader[3].ToString();
                    dgAccountDetails.Rows[n].Cells["clDocumentNumber"].Value = rdReader[4].ToString();
                    dgAccountDetails.Rows[n].Cells["clCategory"].Value = rdReader[0].ToString();
                    dgAccountDetails.Rows[n].Cells["clCommissionType"].Value = rdReader[1].ToString();
                    dgAccountDetails.Rows[n].Cells["clInvoiceValue"].Value = rdReader[2].ToString();
                    dgAccountDetails.Rows[n].Cells["clCommissionAmount"].Value = Classes.Functions.CalculateCommission(rdReader[1].ToString(), rdReader[0].ToString(), Convert.ToDecimal(rdReader[2].ToString())).ToString("N2");

                }
                rdReader.Dispose();
                oConn.Dispose();
            }
            AddTotals();
            Cursor = System.Windows.Forms.Cursors.Default;
        }

        private void LoadAssetGrid()
        {
            dgAllAssetZoom.Rows.Clear();
            string sSelectedStoreCode = "All";
            using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr))
            {
                oConn.Open();
                string sSql = "SELECT distinct  Inventory.ItemCode, Description, UserDefText01, UserDefText02, UserDefText03 from Inventory ";
                sSql += " inner join MultiStoreTrn on Inventory.ItemCode = MultiStoreTrn.ItemCode ";
                sSql += " where (StoreCode = '" + sSelectedStoreCode + "' or '" + sSelectedStoreCode + "' = 'All')and (Inventory.ItemCode like '%" + txtItemCodeFilter.Text + "%' or '" + txtItemCodeFilter.Text + "' = '')";
                sSql += " and UserDefNum01 = 1 ";
                //sSql += " and UserDefText01 = 'WORKSHOP'";
                sSql += " order by Inventory.UserDefText01, Inventory.ItemCode ";

                PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteReader();
                while (rdReader.Read())
                {
                    int n = dgAllAssetZoom.Rows.Add();
                    dgAllAssetZoom.Rows[n].Cells["clItemCode"].Value = rdReader["ItemCode"].ToString();
                    dgAllAssetZoom.Rows[n].Cells["clDescription"].Value = rdReader["Description"].ToString();
                }
                oConn.Dispose();
                rdReader.Close();
            }
        }

        private void cmdReNumber_Click(object sender, EventArgs e)
        {
            int iNextDocNum = 1;
            string sOutput = "";
            using (PsqlConnection PoConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr))
            {
                PoConn.Open();
                using (PsqlConnection LoConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
                {
                    LoConn.Open();

                    foreach (DataGridViewRow oRow in dgRenumberDocs.Rows)
                    {
                        string sNewDocNum = iNextDocNum.ToString("00000000");

                        //UPDATE HISTORY HEADER
                        string sSql = "update HistoryHeader set DocumentNumber = '" + sNewDocNum + "' ";
                        sSql += "where DocumentNumber = '" + oRow.Cells["clDocumentNumber"].Value.ToString() + "' ";
                        int iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, PoConn).ExecuteNonQuery();

                        //UPDATE HISTORY LINES
                        sSql = "update HistoryLines set DocumentNumber = '" + sNewDocNum + "' ";
                        sSql += "where DocumentNumber = '" + oRow.Cells["clDocumentNumber"].Value.ToString() + "' ";
                        iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, PoConn).ExecuteNonQuery();

                        sOutput += oRow.Cells["clDocumentNumber"].Value.ToString() + ", " + sNewDocNum + "\r\n";

                        //UPDATE SOLHH
                        sSql = "update SOLHH set DocNumber = '" + sNewDocNum + "' ";
                        sSql += "where DocNumber = '" + oRow.Cells["clDocumentNumber"].Value.ToString() + "' ";
                        iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, LoConn).ExecuteNonQuery();

                        //UPDATE SOLHL
                        sSql = "update SOLHL set Header = '" + sNewDocNum + "' ";
                        sSql += "where Header = '" + oRow.Cells["clDocumentNumber"].Value.ToString() + "' ";
                        iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, LoConn).ExecuteNonQuery();

                        iNextDocNum++;
                    }

                    string sFinalPath = "C:\\Pastel09\\ReNumberLog.txt";

                    using (FileStream fs = File.Create(sFinalPath))
                    {
                        byte[] info = new UTF8Encoding(true).GetBytes(sOutput);
                        fs.Write(info, 0, info.Length);
                        fs.Close();
                    }

                    LoConn.Dispose();
                }
                PoConn.Dispose();
            }
        }

 private void BuildDataset()
 {
     dsEstimatedTurnover = new DataSet();
     using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr))
     {
         string sSql = "Select CustomerCode,CustomerDesc, LinkWeb as Balance ";
         sSql += " from CustomerMaster";
         dsEstimatedTurnover = Solsage_Process_Management_System.Classes.Connect.getDataSet(sSql, "dtEstimatedTurnover", oConn);
         oConn.Dispose();
     }
 }

        private void cmdDelete_Click(object sender, EventArgs e)
        {
            using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
            {
                oConn.Open();
                string delSql = "delete From SOLMARKDET where MarketerCode = '" + txtName.Text.Trim().Replace("'", "''") + "'";
                int delRet = Solsage_Process_Management_System.Classes.Connect.getDataCommand(delSql, oConn).ExecuteNonQuery();

                oConn.Dispose();
            }
            cmdNew_Click(null, null);
        }

        private void cmdReturn_Click(object sender, EventArgs e)
        {
            string[] aPastelUpdateLine = new string[0];
            using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
            {
                oConn.Open();
                for (int i = 0; i < dgItemGrid.Rows.Count; i++)
                {
                    if (dgItemGrid.Rows[i].Cells["clSelect"].Value == clSelect.TrueValue)
                    {
                        string sSql = "Update SOLAL set status = 1, UserCodeReturn = '" + txtSalesCode.Text + "' ";
                        sSql += "where DocumentNumber = '" + dgItemGrid.Rows[i].Cells["clDocNumber"].Value + "' ";
                        sSql += "and AssetNumber = '" + txtAssetCode.Text.Trim() + "' ";
                        sSql += "and ItemCode = '" + dgItemGrid.Rows[i].Cells["clItemCode"].Value + "' ";

                        int iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();
                        if (iReturn == 0)
                        {
                            MessageBox.Show("An erros occured during the update of SOLAL", "SOLAL Update Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                    }

                    Array.Resize<string>(ref aPastelUpdateLine, aPastelUpdateLine.Length + 1);
                    aPastelUpdateLine[aPastelUpdateLine.Length - 1] = dgItemGrid.Rows[i].Cells["clItemCode"].Value.ToString();

                }

                oConn.Dispose();
            }

            if (aPastelUpdateLine.Length > 0)
            {
                using (PsqlConnection oConnPastel = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr))
                {
                    oConnPastel.Open();
                    for (int i = 0; i < aPastelUpdateLine.Length; i++)
                    {
                        string sSql = "Update Inventory set ";
                        sSql += "UserDefText01 = '' ";
                        sSql += ",UserDefText02 = '' ";
                        sSql += "where ItemCode = '" + aPastelUpdateLine[0].ToString().Trim() + "'";

                        int iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConnPastel).ExecuteNonQuery();
                    }
                    oConnPastel.Dispose();
                }
            }

            LoadAssetGrid();
        }

        private void lbBlockedItems_DoubleClick(object sender, EventArgs e)
        {
            lbActiveItems.Items.Add(lbBlockedItems.SelectedItem);

            using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
            {
                oConn.Open();
                //insert item into SOLIM
                string sSql = "Delete from SOLIM where ItemCode = '" + lbBlockedItems.SelectedItem.ToString().Trim() + "'";
                int iRet2 = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();
                oConn.Dispose();
            }
            lbBlockedItems.Items.Remove(lbBlockedItems.SelectedItem);
        }

 private void BuildDataSet()
 {
     dsCustomersOverLimit = new DataSet();
     using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr))
     {
         string sSql = "Select CustomerCode,CustomerDesc,(Round(CurrBalanceThis01,2) + Round(CurrBalanceThis02,2)";
         sSql += " + Round(CurrBalanceThis03,2) + Round(CurrBalanceThis04,2) + Round(CurrBalanceThis05,2) + Round(CurrBalanceThis06,2) + Round(CurrBalanceThis07,2) + Round(CurrBalanceThis08,2)";
         sSql += " + Round(CurrBalanceThis09,2) + Round(CurrBalanceThis10,2) + Round(CurrBalanceThis11,2) + Round(CurrBalanceThis12,2) + Round(CurrBalanceThis13,2) + Round(CurrBalanceLast01,2)";
         sSql += " + Round(CurrBalanceLast02,2) + Round(CurrBalanceLast03,2) + Round(CurrBalanceLast04,2) + Round(CurrBalanceLast05,2) + Round(CurrBalanceLast06,2) + Round(CurrBalanceLast07,2)";
         sSql += " + Round(CurrBalanceLast08,2) + Round(CurrBalanceLast09,2) + Round(CurrBalanceLast10,2) + Round(CurrBalanceLast11,2) + Round(CurrBalanceLast12,2) + Round(CurrBalanceLast13,2))";
               sSql += " CurrentBalance, CreditLimit from CustomerMaster where Category <> '2'";
         dsCustomersOverLimit = Solsage_Process_Management_System.Classes.Connect.getDataSet(sSql,"dtCustomersOverLimit", oConn);
         oConn.Dispose();
     }
 }

        private void cmdAddGroup_Click(object sender, EventArgs e)
        {
            if (txtPublicHolidayName.Text == "")
            {
                MessageBox.Show("Please Fill In Name of Public Holiday");
                return;
            }

            using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
                {
                    oConn.Open();
                    if (txtPhId.Text == "")//new item
                    {
                        string sSql = "Insert into SOLPH (PublicHolidayName, PublicHolidayDate) VALUES ";
                        sSql += "(";
                        sSql += "'" + txtPublicHolidayName.Text + "'";
                        sSql += ",'" + dtPublicHolidayDate.Value.ToString("MM-dd-yyyy") + "'";
                        sSql += ")";
                        int iRet = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();
                        sSql = "SELECT @@IDENTITY FROM SOLPH";
                        txtPhId.Text = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteScalar().ToString();
                    }
                    else
                    {
                        string sSql = "Update SOLPH set ";
                        sSql += " PublicHolidayName  = '" + txtPublicHolidayName.Text + "'";
                        sSql += ", PublicHolidayDate = '" + dtPublicHolidayDate.Text + "'";
                        sSql += " where id = " + txtPhId.Text;

                        int iRet = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();
                    }
                    oConn.Dispose();
                }

                loadPublicHolidays();
                cmdDeleteGroup.Enabled = true;
                int iRowIndex = 0;

                foreach (DataGridViewRow dgRow in dgPublicHolidays.Rows)
                {
                    if (dgRow.Cells["clPhId"].Value.ToString() == txtPhId.Text.Trim())
                    {
                        iRowIndex = dgRow.Index;
                    }
                }

                dgPublicHolidays.CurrentCell = dgPublicHolidays.Rows[iRowIndex].Cells[0];
        }

        private void cmdDeleteNote_Click(object sender, EventArgs e)
        {
            using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
            {
                oConn.Open();

                if (MessageBox.Show("This will delete Customer Note. Do you want to continue? ","Delete Customer Note",MessageBoxButtons.OKCancel,MessageBoxIcon.Information) == DialogResult.OK)
                {
                string sSql = "delete from SOLCN where IDNumber = '" + txtID.Text + "' And CustomerCode = '" + txtAcountCode.Text + "'";
                int iRet = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();
                oConn.Dispose();
                this.Close();
                }

            }
        }