C# PsqlConnection.Open方法代码示例

        private void cmdSaveOutWorkshop_Click(object sender, EventArgs e)
        {
            using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr))
            {
                oConn.Open();
                for (int i = 0; i < dgTakeOutWorkshop.Rows.Count; i++)
                {
                    if (dgTakeOutWorkshop.Rows[i].Cells["chkSelectOut"].Value == chkSelectOut.TrueValue)
                    {

                        string sSql = "Update Inventory Set ";
                        sSql += "UserDefText01 = '',";
                        sSql += "UserDefText03 = '',";
                        sSql += "UserDefText02 = '' ";
                        sSql += "where ItemCode = '" + this.dgTakeOutWorkshop.Rows[i].Cells[0].Value + "'";

                        int iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();
                        if (iReturn <= 0)
                        {
                            MessageBox.Show("An error occured with the movement of item " + this.dgTakeOutWorkshop.Rows[i].Cells[0].Value + " to the workshop");

                        }

                    }
                }

                cmdSaveOutWorkshop.Enabled = false;
                oConn.Dispose();
            }
            dgPutInWorkshop.Rows.Clear();
            currentlyOutWorkshopGridLoad();

            dgTakeOutWorkshop.Rows.Clear();
            currentlyInWorkshopGridLoad();
        }

        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 void Connect(string dsn, string host)
 {
     Console.Error.Write("Connecting to {0}...", host);
     DBConn = new PsqlConnection(string.Format("Server DSN={0}; Host={1}", dsn, host));
     DBConn.Open();
     Console.Error.WriteLine("connected");
 }

        private void loadUserGrid()
        {
            dgUserZoom.Rows.Clear();
            PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr);
            oConn.Open();

            string sSql = "Select * from SOLUS order by UserName";

            PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteReader();

            while (rdReader.Read())
            {
                int n = dgUserZoom.Rows.Add();
                dgUserZoom.Rows[n].Cells["clUserCode"].Value = rdReader["Code"].ToString();
                dgUserZoom.Rows[n].Cells["clDescription"].Value = rdReader["Description"].ToString();
                dgUserZoom.Rows[n].Cells["UserName"].Value = rdReader["UserName"].ToString();
                if (rdReader["UserType"].ToString() == "0")
                    dgUserZoom.Rows[n].Cells["UserType"].Value = "Front Desk";
                if (rdReader["UserType"].ToString() == "1")
                    dgUserZoom.Rows[n].Cells["UserType"].Value = "Administrator";
                if (rdReader["UserType"].ToString() == "2")
                    dgUserZoom.Rows[n].Cells["UserType"].Value = "Asset Maintenance";
                dgUserZoom.Rows[n].Cells["Password"].Value = rdReader["Psswrd"].ToString();
                dgUserZoom.Rows[n].Cells["clCreditInvoice"].Value = rdReader["CreditInvoice"].ToString();
                dgUserZoom.Rows[n].Cells["clCancelReturn"].Value = rdReader["CancelReturnItem"].ToString();
                dgUserZoom.Rows[n].Cells["clCloseOrder"].Value = rdReader["CloseOrder"].ToString();
                dgUserZoom.Rows[n].Cells["clShortName"].Value = rdReader["ShortName"].ToString();
                dgUserZoom.Rows[n].Cells["clTelephoneNumber"].Value = rdReader["TelephoneNumber"].ToString();
                dgUserZoom.Rows[n].Cells["clLockOrder"].Value = rdReader["LockOrder"].ToString();
                dgUserZoom.Rows[n].Cells["clReturnItem"].Value = rdReader["ReturnItem"].ToString();
            }
            rdReader.Close();
            oConn.Dispose();
            dgUserZoom.Focus();
        }

        public void DBLoadInventoryGrid()
        {
            try
            {
                dgvInventory.AutoGenerateColumns = false;
                dgvInventory.DataSource = null;

                string sSql = "";

                using (PsqlConnection pasConn = new PsqlConnection(Connect.sPastelConnStr))
                {
                    pasConn.Open();

                    sSql = "SELECT RTRIM(Category) As Category, RTRIM(ItemCode) As ItemCode, RTRIM(Description) AS Description, RTRIM(UnitSize) AS UnitSize, RTRIM(ICDesc) AS ICDesc";
                    sSql += " FROM Inventory";
                    sSql += " LEFT JOIN InventoryCategory ON ICCode = Category ";

                    dsInventory = Connect.getDataSet(sSql, "Inventory", pasConn);

                    bsInventory = new BindingSource();
                    bsInventory.DataSource = dsInventory;
                    bsInventory.DataMember = dsInventory.Tables["Inventory"].TableName;

                    dgvInventory.DataSource = bsInventory;
                    dgvInventory.ClearSelection();

                    pasConn.Close();
                }

            }
            catch(Exception ex)
            {
                MessageBox.Show("Error Info: " + ex.Message, "Exception Occurred", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string sConnString = "Server Name=localhost;Database Name=LSYNC;";
            using (PsqlConnection oPastelConn = new PsqlConnection(sConnString))
            {
                oPastelConn.Open();

                Cursor = System.Windows.Forms.Cursors.WaitCursor;
                solPastelSDK clsPastelSDK = new solPastelSDK();

                string[] aLines = new string[2];
                aLines[0] = "0|1|105.28|120|DAY |1|0|0|023PHIREDRILL|DRILL HILTI TE16                        |4|001|     |";
              //              aLines[0] = "0|1|162.28|162.28|DAY |0|0|0|039PHIREGENERA|6KVA GENERATOR HONDA MGS6000            |4|001|     |";
                aLines[1] = "0|1|0.02|0.02|    |0|0|0|1000020        |Rounding                                |6|001|     |";

                string sHeader = "|||ABS001|05/02/2010|IN100159|Y|0|||||||||00001|0||2010/02/05 12:00:00 AM||Blondie||1|||||||||26477506|0";

                string sDataPath = "C:\\Pastel09\\FAIREGLEN";
                string sResult = clsPastelSDK.CreatePastelDocument(sHeader, aLines, 106, sDataPath, Global.iPastelSdkUser, false, oPastelConn, 1);

                MessageBox.Show(sResult);
                //CreatePastelDocument(sHeader, aLines, 106, sDataPath, Global.iPastelSdkUser, Global.bLogCreateDocument);

                //txtSdkResults.Text = sResult;

                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 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 DBLoadRawBatchGrid()
        {
            try
            {
                dgvRawBatches.AutoGenerateColumns = false;
                dgvRawBatches.DataSource = null;

                string sSql = "";

                using (PsqlConnection liqConn = new PsqlConnection(Connect.sConnStr))
                {
                    liqConn.Open();

                    sSql = "SELECT * FROM SOLSIL";
                    sSql += " WHERE DocNumber = '" +sDocNumFilter + "'" ;

                    dsRawBatchInfo = Connect.getDataSet(sSql, "RawBatch", liqConn);

                    bsRawBatch = new BindingSource();
                    bsRawBatch.DataSource = dsRawBatchInfo;
                    bsRawBatch.DataMember = dsRawBatchInfo.Tables["RawBatch"].TableName;

                    dgvRawBatches.DataSource = bsRawBatch;

                    liqConn.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error Info: " + ex.Message, "Exception Occurred", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        public static void FetchVersionDetails()
        {
            using (PsqlConnection oConn = new PsqlConnection(Connect.sConnStr))
            {
                try
                {
                    oConn.Open();

                }
                catch (Exception Ex)
                {
                    MessageBox.Show("Error connecting to Liquid Database:" + Ex.ToString(),"Connection Error");
                    Application.Exit();
                }

                string sSql = "SELECT * FROM SOLUPD";
                PsqlDataReader rdReader = Connect.getDataCommand(sSql, oConn).ExecuteReader();
                while (rdReader.Read())
                {
                    iCurrVersion = Convert.ToInt32(rdReader["CurrentVersion"].ToString().Trim());
                }

                rdReader.Close();
            }
        }

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

        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 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;
            string sUserName = "";
            string sUserCode = "";
            string sUserType = "";

            if (txtUserName.Text == "")
            {
                MessageBox.Show("Please supply username field");
                return;
            }
            using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
            {
                oConn.Open();
                string sSql = "Select UserName,Psswrd,Code,UserType, CreditInvoice 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;
                        sUserName = rdReader["UserName"].ToString().Trim();
                        sUserCode = rdReader["Code"].ToString().Trim();
                        sUserType = rdReader["UserType"].ToString().Trim();
                        Global.iCreditInvoice = Convert.ToInt32(rdReader["CreditInvoice"]);
                    }
                }

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

            if (bMatch == true)
            {

                this.Visible = false;
                Cursor = System.Windows.Forms.Cursors.WaitCursor;
                Main frmMain = new Solsage_Process_Management_System.Main();

                Global.sLogedInUserName = sUserName;
                Global.sLogedInUserCode = sUserCode;
                Global.sLogedInUserType = sUserType;
                Global.frmLogin = this;

                Global.bUseBackground = chkUseBackground.Checked;

                Global.frmMain = frmMain;
                frmMain.Show();

                Cursor = System.Windows.Forms.Cursors.Default;
            }
            else
            {
                MessageBox.Show("Login Failed");
                cmdClearFields();
            }
        }