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