Make query with DbParameter and DbCommand

martinsalo

Hello Forum,


 


I wonder how parameterized queries work with SAP. I'm playing with this source, it should retrieve the current SAP username.


But cant get it to work:




public static string QuerySAPUsername() {
try {
SwissAddonFramework.B1Connector B1 = SwissAddonFramework.B1Connector.GetB1Connector();
string SapUserID = B1.UserId.ToString();


DbCommand SapCmd = B1.CreateCommand("SELECT User_Code FROM OUSR WHERE UserID = @SapUserID;");
System.Data.Common.DbParameter Param = B1.CreateParameter("SapUserID", SapUserID);
SapCmd.Parameters.Add(Param);
DbDataReader UsernameDbDataReader = SapCmd.ExecuteReader(System.Data.CommandBehavior.SingleResult);


if (UsernameDbDataReader == null || UsernameDbDataReader.HasRows == false)
throw new Exception("Cannot query SAP Username");


UsernameDbDataReader.Read();
return UsernameDbDataReader["User_Code"].ToString();


} catch (Exception ex) {
string s = ex.ToString();
}
}


The error message is:

ExecuteReader: The connection property is not initialised.

I want to use the default SAP connection to the SAP database, I dont want to store extra connection info for my addin.

 

 

Instead I can use plain SQL queries, but thats not what I want:



System.Data.SqlClient.SqlDataReader SqlReader = SwissAddonFramework.B1Connector.GetB1Connector().ExecuteQuery("SELECT User_Code FROM OUSR WHERE UserID = " + SapUserID + ";");

Does someone know how the parameter object work with the command object?



 


 


 


Thanks


 


Martin


Fiona Shao

Hi Martin,


hope the following code could help you.


System.Data.SqlClient.SqlCommand SalUnit = new System.Data.SqlClient.SqlCommand();

SalUnit.CommandText = @"

SELECT T0.NumInSale

FROM OITM T0

WHERE T0.ItemCode = @itemCode

";

SalUnit.Parameters.AddWithValue("@itemCode", itemCode);

 

best regards,

fiona

0

Comments

0 comments

Please sign in to leave a comment.

Didn't find what you were looking for?

New post