Question: I need to run a custom query in SalesLogix Web v7.5 and I don’t know how to run custom SQLs and manipulate record sets in a C# code snippet. How can I do this?
Answer: You can use ADO.Net (System.Data, System.Data.OleDb, etc.) to do your SQL tasks. Here is an example of a scalar value:
//Get connection string
string connStr = service.GetConnectionString();
using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connStr))
using (System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand())
//Build and execute SQL
sql = “SELECT USERID FROM USERPROFILE WHERE USERID = ‘” + curUserID + “‘”;
cmd.Connection = conn;
cmd.CommandText = sql;
//If the statements executes then there is a value
yourVal = cmd.ExecuteScalar().ToString();
//SQL returned a null.
Notice that the SalesLogix Connection String (SLX OLE DB Provider) is being retrieved via the service GetConnectionString() method, with is a SalesLogix-specific method. Being that this is an OLE DB provider, methods from the System.Data.OLE DB namespace are being used which is from the standard .Net class libraries. If you look at Microsoft’s .Net SDK documentation, you can see there are useful classes such as OLE DBCommand, OLE DBDataReader, etc.