Esempio n. 1
0
        public decimal dItemPrice(SqlConnection oConn, string sItemCode, int iListnum, ref int iErr)
        {
            // routine to get an Item price based on the Price List Number

            iErr = -1;
            if (iListnum < 1)
            {
                return(0.0M);
            }

            try
            {
                string SQL = "";
                SQL  = SQL + "SELECT Price FROM ITM1 WHERE ItemCode = '" + sItemCode + "' AND PriceList ='" + iListnum.ToString() + "'";
                sTmp = sGetDBField(ref oConn, SQL);
                if (sTmp == "")
                {
                    return(0.0M);
                }
                iErr = 0;
                return(Convert.ToDecimal(sTmp));
            }
            catch (Exception ex)
            {
                DImsg.MessageERR(ref ex);
                return(0.0M);
            }
            finally
            {
                GC.Collect();
            }
        }
Esempio n. 2
0
        public SAPbobsCOM.Company Connect(string pServer, string pDBName, string pDBUser, string pDBPassword, SAPbobsCOM.BoDataServerTypes pServerType,
                                          string pSAPUser, string pSAPPassword, ref int iErr)
        {
            if (pDBName == "")
            {
                iErr = 1;
            }

            if (iErr != 0)
            {
                return(null);
            }

            //oConnTrg = SQLDirect.oConnectToSql(txbDBServerTrg.Text, cmbSAPDBTrg.Text.Substring(0, cmbSAPDBTrg.Text.IndexOf(" - ")), txbDBUserTrg.Text, txbDBPassTrg.Text, true, bErr);
            oComp.Server = pServer;
            if (pServer == "")
            {
                oComp.Server = scServer;
            }

            oComp.DbUserName = pDBUser;
            if (pDBUser == "")
            {
                oComp.DbUserName = scSQLName;
            }

            oComp.DbPassword = pDBPassword;
            if (pDBPassword == "")
            {
                oComp.DbPassword = scSQLPass;
            }

            oComp.DbServerType = pServerType;
            //if (pServerType == null) oComp.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2008;

            oComp.CompanyDB = pDBName;

            oComp.UserName = pSAPUser;
            if (pSAPUser == "")
            {
                oComp.UserName = scSAPName;
            }

            oComp.Password = pSAPPassword;
            if (pSAPPassword == "")
            {
                oComp.Password = scSAPPass;
            }

            iErr = oComp.Connect();
            if (iErr == 0)
            {
                return(oComp);
            }
            else
            {
                DImsg.MessageERR(iErr, oComp.GetLastErrorDescription());
                return(null);
            }
        }
Esempio n. 3
0
        public string sGetDBfield(ref SAPbobsCOM.Company oComp, string sSQL, ref bool BubbleEvent)
        {
            string functionReturnValue = null;

            // routine to execut the sql command and return a value

            functionReturnValue = "";
            if (BubbleEvent == false)
            {
                return(functionReturnValue);
            }

            SAPbobsCOM.Recordset oRS1 = null;

            try
            {
                oRS1 = rsGetDBRecSet(ref oComp, sSQL, ref BubbleEvent);
                if (oRS1.RecordCount > 0)
                {
                    functionReturnValue = oRS1.Fields.Item(0).Value.ToString();
                }
            }
            catch (Exception ex)
            {
                BubbleEvent = false;
                DImsg.MessageERR(ref ex);
            }

            if ((oRS1 != null))
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oRS1);
            }
            GC.Collect();
            return(functionReturnValue);
        }
Esempio n. 4
0
        public string sGetDocEntry(SqlConnection oConn, string sTable, string sDocNum)
        {
            // get the document ENTRY for the specified DocNum.

            string sRtn = "";
            string SQL  = "";

            try
            {
                // get the corresponding DocNum
                SQL  = "SELECT DocEntry FROM " + sTable + " WHERE DocNum = '" + sDocNum + "'";
                sRtn = sGetDBField(ref oConn, SQL);

                return(sRtn);
            }
            catch (Exception ex)
            {
                DImsg.MessageERR(ref ex);
                return(sRtn);
            }
            finally
            {
                GC.Collect();
            }
        }
Esempio n. 5
0
        public string sGetDBField(ref SqlConnection oConn, string Query)
        {
            // get the first element -
            //  if nothing if found return empty.


            string sReturn = "";

            string[][] RecordData;
            if (oConn != null)
            {
                RecordData = ReturnArrayQueryData(oConn, Query);
                if (RecordData == null)
                {
                    return(sReturn);
                }
                if (RecordData.Length <= 0)
                {
                    return(sReturn);
                }
                try
                {
                    sReturn = RecordData[0][0];
                }
                catch (Exception ex)
                {
                    DImsg.MessageERR(ref ex);
                }
            }
            return(sReturn);
        }
Esempio n. 6
0
        public SAPbobsCOM.Recordset rsGetDBRecSet(ref SAPbobsCOM.Company oComp, string sSQL, ref bool BubbleEvent)
        {
            SAPbobsCOM.Recordset functionReturnValue = default(SAPbobsCOM.Recordset);

            // routine to execut the sql command and return a record set

            functionReturnValue = null;
            if (BubbleEvent == false)
            {
                return(functionReturnValue);
            }

            //Dim oRS1 As SAPbobsCOM.Recordset = Nothing

            try
            {
                //oRS1 = oComp.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
                //oRS1.DoQuery(sSQL)
                //rsGetDBRecSet = oRS1
                functionReturnValue = (SAPbobsCOM.Recordset)oComp.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
                functionReturnValue.DoQuery(sSQL);
                iTmp = functionReturnValue.RecordCount;
            }
            catch (Exception ex)
            {
                BubbleEvent = false;
                DImsg.MessageERR(ref ex);
            }

            //If Not oRS1 Is Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(oRS1)
            GC.Collect();
            return(functionReturnValue);
        }
Esempio n. 7
0
        public object ExecuteScalar(SqlConnection oConn, string sCommand)
        {
            object value = null;

            try
            {
                if (oConn != null)
                {
                    SqlCommand sSQLCmd;
                    using (sSQLCmd = new SqlCommand(sCommand, oConn))
                    {
                        // ExecuteNonQuery returns the number of rows accessed.
                        value = sSQLCmd.ExecuteScalar();
                    }
                }
            }
            catch (Exception ex)
            {
                DImsg.MessageERR(ref ex);
            }
            finally
            {
            }
            return(value);
        }
Esempio n. 8
0
        public DataTable LoadDataTable(SqlConnection oConn, string sQuery, string sTableName)
        {
            if (oConn == null)
            {
                return(null);
            }
            if (sQuery.Trim().Length == 0)
            {
                return(null);
            }
            if (sTableName.Trim().Length == 0)
            {
                return(null);
            }

            try
            {
                DataSet        ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(sQuery, oConn);
                ds = new DataSet(sTableName);
                da.Fill(ds, sTableName);
                return(ds.Tables[sTableName]);
            }
            catch (Exception ex)
            {
                DImsg.MessageERR(ref ex);
                return(null);
            }
            finally
            {
                GC.Collect();
            }
        }
Esempio n. 9
0
        public int SQLCommand(SqlConnection oConn, string sCommand)
        {
            int iRtn = -1;

            try
            {
                if (oConn != null)
                {
                    SqlCommand sSQLCmd;
                    using (sSQLCmd = new SqlCommand(sCommand, oConn))
                    {
                        // ExecuteNonQuery returns the number of rows accessed.
                        iRtn = sSQLCmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                iRtn = -1;
                DImsg.MessageERR(ref ex);
            }
            finally
            {
            }
            return(iRtn);
        }
Esempio n. 10
0
        public string sGetLastDocNumAdded(SqlConnection oConn, string sTable, string sBP)
        {
            // get the last docement added for the specified BP.

            string sRtn = "";
            string SQL  = "";

            try
            {
                // get the Doc Entry
                sRtn = sGetLastDocEntryAdded(oConn, sTable, sBP);

                // get the corresponding DocNum
                SQL  = "SELECT DocNum FROM " + sTable + " WHERE DocEntry = '" + sRtn + "'";
                sRtn = sGetDBField(ref oConn, SQL);

                return(sRtn);
            }
            catch (Exception ex)
            {
                DImsg.MessageERR(ref ex);
                return(sRtn);
            }
            finally
            {
                GC.Collect();
            }
        }
Esempio n. 11
0
        public SqlConnection oConnectToSql(string sServer, string sDataBase, string sUser, string sPassword, bool bDisplayErrMsg, ref int iErr)
        {
            SqlConnection functionReturnValue = default(SqlConnection);

            functionReturnValue = null;

            if (iErr != 0)
            {
                return(functionReturnValue);
            }

            SqlConnection oConn    = new SqlConnection();
            string        sConnStr = "";

            sConnStr = sConnStr + "integrated security=False;";
            sConnStr = sConnStr + "data source=" + sServer + ";";
            sConnStr = sConnStr + "persist security info=False;";
            sConnStr = sConnStr + "initial catalog=" + sDataBase + ";";
            sConnStr = sConnStr + "User ID=" + sUser + ";";
            sConnStr = sConnStr + "Password="******";";

            oConn.ConnectionString = sConnStr;

            try
            {
                oConn.Open();
            }
            catch (SqlException ex)
            {
                iErr = ex.Number;

                // don't display error message if the user is addin user is invalid
                if (ex.Number == 18456 & sUser == scSQLName & sPassword == scSQLPass)
                {
                    return(null);                                                                     //"Login failed for user '" + sUser + "'."
                }
                if (bDisplayErrMsg)
                {
                    DImsg.MessageERR(ex.Number, ex.Message);
                }
                return(null);
            }
            catch (Exception ex)
            {
                iErr = -1;
                if (bDisplayErrMsg)
                {
                    DImsg.MessageERR(ref ex);
                }
                return(null);
            }

            return(oConn);
            //return functionReturnValue;

            //oConn.InfoMessage += New SqlInfoMessageEventHandler(OnInfoMessage)
            //oConn.StateChange += New StateChangeEventHandler(OnStateChange)
        }
Esempio n. 12
0
        public void SetConfig(SqlConnection oConn, string sName, string sVal, string sGrp, string sCmt)
        {
            string sCode = "";

            try
            {
                SQL   = "SELECT Code FROM " + scConfig + " WITH (NOLOCK) WHERE Name = N'" + sName + "'";
                sCode = cSQL.sGetDBField(ref oConn, SQL);

                // entry does not exist, add it
                if (string.IsNullOrEmpty(sCode))
                {
                    // first find the next code
                    SQL   = "SELECT MAX(Code)+10 FROM " + scConfig + " WITH (NOLOCK)";
                    sCode = cSQL.sGetDBField(ref oConn, SQL);
                    if (sCode == "0")
                    {
                        sCode = "1000";
                    }

                    SQL = "INSERT " + scConfig + " VALUES('" + sCode + "','" + sName + "','" + sVal + "','" + sGrp + "','" + sCmt + "'";


                    if (cSQL.SQLCommand(oConn, SQL) != 1)
                    {
                        //DImsg.MessageERR(iErr, sErr); there is an error message in SQLCommand
                    }
                    return;
                }

                SQL = "";
                SQL = SQL + "UPDATE " + scConfig + " SET U_Value = '" + sVal + "'";
                if (sGrp != null)
                {
                    SQL = SQL + " , U_Group = '" + sGrp + "'";
                }
                if (sCmt != null)
                {
                    SQL = SQL + " , U_Comment = '" + sCmt + "'";
                }
                SQL = SQL + " WHERE Name = '" + sName + "'";

                cSQL.SQLCommand(oConn, SQL);
            }
            catch (Exception ex)
            {
                DImsg.MessageERR(ref ex);
            }
            finally
            {
                GC.Collect();
            }
        }
Esempio n. 13
0
        private SqlDataReader ReturnRecordSet(SqlConnection oConn, string sQuery)
        {
            // olny one datareader per connection

            SqlCommand oSqlCommand = null;

            try
            {
                oSqlCommand = new SqlCommand(sQuery, oConn);
                SqlDataReader oRecordSet = oSqlCommand.ExecuteReader();
                return(oRecordSet);
            }
            catch (Exception ex)
            {
                DImsg.MessageERR(ref ex);
                return(null);
            }
        }
Esempio n. 14
0
 public string sGetConfig(SqlConnection oConn, string sName)
 {
     try
     {
         // get the entry
         string sRec = cSQL.sGetDBField(ref oConn, "SELECT U_Value FROM " + scConfig + " WITH (NOLOCK) WHERE Name = '" + sName + "'");
         return(sRec);
     }
     catch (Exception ex)
     {
         DImsg.MessageERR(ref ex);
         return(null);
     }
     finally
     {
         GC.Collect();
     }
 }
Esempio n. 15
0
        public string[] sArrayOfRow(SqlConnection oConn, string sQuery, ref int iErr)
        {
            // return an array of returned values for one Row

            if (iErr != 0)
            {
                return(null);
            }

            try
            {
                string[][] sData = null;

                sData = ReturnArrayQueryData(oConn, sQuery);

                if (sData == null)
                {
                    return(null);
                }

                int j = sData[0].Length - 1;

                string[] sRtn = null;
                sRtn = new string[j + 1];

                for (int i = 0; i <= j; i++)
                {
                    sRtn[i] = sData[0][i];
                }

                return(sRtn);
            }
            catch (Exception ex)
            {
                iErr = 1;
                DImsg.MessageERR(ex.Message);
                return(null);
            }
            finally
            {
            }
        }
Esempio n. 16
0
        public decimal[] dArrayOfColumn(SqlConnection oConn, string sQuery, ref int iErr)
        {
            // return an array of values for one Column  - DECIMAL

            if (iErr != 0)
            {
                return(null);
            }

            try
            {
                string[][] sData = null;

                sData = ReturnArrayQueryData(oConn, sQuery);

                if (sData == null)
                {
                    return(null);
                }

                int j = sData[0].Length - 1;

                decimal[] dRtn = null;
                dRtn = new decimal[j + 1];

                for (int i = 0; i <= j; i++)
                {
                    dRtn[i] = Convert.ToDecimal(sData[i][0]);
                }

                return(dRtn);
            }
            catch (Exception ex)
            {
                iErr = 1;
                DImsg.MessageERR(ex.Message);
                return(null);
            }
            finally
            {
            }
        }
Esempio n. 17
0
        public string[] sArrayOfColumn(SqlConnection oConn, string sQuery, ref int iErr)
        {
            // return an array of values for one Column

            if (iErr != 0)
            {
                return(null);
            }

            try
            {
                string[][] sData = null;

                sData = ReturnArrayQueryData(oConn, sQuery);

                if (sData == null)
                {
                    return(null);
                }

                int j = sData.GetUpperBound(0);

                string[] sRtn = null;
                sRtn = new string[j + 1];

                for (int i = 0; i <= j; i++)
                {
                    sRtn[i] = sData[i][0];
                }

                return(sRtn);
            }
            catch (Exception ex)
            {
                iErr = 1;
                DImsg.MessageERR(ex.Message);
                return(null);
            }
            finally
            {
            }
        }
Esempio n. 18
0
        public decimal dGetDBfield(ref SAPbobsCOM.Company oComp, string sSQL, ref int iRecCnt, ref bool BubbleEvent)
        {
            decimal functionReturnValue = default(decimal);

            // routine to execut the sql command and return a value

            functionReturnValue = 0m;
            if (BubbleEvent == false)
            {
                return(functionReturnValue);
            }

            SAPbobsCOM.Recordset oRS1 = null;

            try
            {
                oRS1    = rsGetDBRecSet(ref oComp, sSQL, ref BubbleEvent);
                iRecCnt = oRS1.RecordCount;
                if (oRS1.RecordCount > 0)
                {
                    functionReturnValue = Convert.ToDecimal(oRS1.Fields.Item(0).Value);
                }
            }
            catch (Exception ex)
            {
                BubbleEvent = false;
                DImsg.MessageERR(ref ex);
            }

            if (oRS1 != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oRS1);
            }
            GC.Collect();
            return(functionReturnValue);
        }
Esempio n. 19
0
        public string sGetLastDocEntryAdded(SqlConnection oConn, string sTable, string sBP)
        {
            // get the last docement Entry added for the specified BP.

            string sRtn = "";
            string SQL  = "";

            try
            {
                SQL  = "SELECT MAX(DocEntry) FROM " + sTable + " WHERE CardCode = '" + sBP + "'";
                sRtn = sGetDBField(ref oConn, SQL);

                return(sRtn);
            }
            catch (Exception ex)
            {
                DImsg.MessageERR(ref ex);
                return(sRtn);
            }
            finally
            {
                GC.Collect();
            }
        }
Esempio n. 20
0
        public void CreateSQL_User(string sServer, string sDatabase, string saUser, string saPassword,
                                   string AddonUserName, string AddonUserPassword, int connectiontrys, ref int iErr)
        {
            //select count(*) From master.sysxlogins WHERE NAME = 'myUsername' // see if user exists

            if (iErr != 0)
            {
                return;
            }

            SqlConnection oConn = oConnectToSql(sServer, sDatabase, AddonUserName, AddonUserPassword, false, ref iErr);

            if (oConn != null)
            {
                goto ExitSub;
            }

tryagain:
            SQLAddUserPrompt oForm = new SQLAddUserPrompt(sServer, sDatabase);

            oForm.GetUserAndPassowrd(ref saUser, ref saPassword);
            if ((oForm.CancelWasPressed == true))
            {
                goto ExitSub;
            }
            oConn = oConnectToSql(sServer, sDatabase, saUser, saPassword, false, ref iErr);
            if (oConn == null)
            {
                iErr = 0;
                goto tryagain;
            }
            //  add the user to the database
            iErr = 1;
            try
            {
                SqlCommand c = new SqlCommand(("CREATE LOGIN "
                                               + (sql_fix(AddonUserName) + (" WITH PASSWORD = \'"
                                                                            + (sql_fix(AddonUserPassword) + "\' , CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF ")))), oConn);
                int inttemp = c.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                DImsg.MessageERR("CREATE LOGIN creation failed" + Environment.NewLine + ex.Message);
                goto ExitSub;
            }
            try
            {
                SqlCommand c2 = new SqlCommand(("EXEC sys.sp_addsrvrolemember @loginame ="
                                                + (sql_fix(AddonUserName) + ", @rolename = N\'sysadmin\'")), oConn);
                int inttemp2 = c2.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                DImsg.MessageERR("sys.sp_addsrvrolemember creation failed" + Environment.NewLine + ex.Message);
                goto ExitSub;
            }
            iErr = 0;

ExitSub:
            if (oConn != null)
            {
                oConn.Close();
            }
            GC.Collect();
        }