Execute plain SQL in script

Question

Hi, 
 I have an SQL query that i very complicate and at the end it returns just 1 or 0. 
 I need to run it into a script and let me return just the number. 
 I can achieve that? 
 I didn't find any object in vb dell to do that. 
 The sqlcommand need an sqlconnection object and I don't know how to fill it from identity manger.

Barry Jackson · Accepted Answer

OK try this code snippet .... put your complicated SQL where the highlighted code is: 
 ConnectionString = Connection.GetSingleProperty("dialogDatabase", "connectionstring", "") 
 Dim conData As ConnectData = DbApp.Instance.Connect(ConnectionString) 
 Using conData.Connection 
 Dim cSQL As SqlExecutor = conData.Connection.CreateSqlExecutor(conData.PublicKey) 
 returnValue = CStr(cSQL.SqlExecuteNonQuery(String.Format("update tps_user set tps_deleted = '{0}' where tps_name = '{1}'", "0", dbsUnsAccount.GetValue("AccountName").String))) 
 End Using 
 If returnValue = "1" Then 
 <some code here> 
 End If

Barry Jackson · Answer

Hi, 
 
You can get the connection string of the database using: 
 
ConnectionString = Connection.GetSingleProperty("dialogDatabase", "connectionstring", "") 
 
Then you could create a connection using: 
 
Dim conData as ConnectData = DbApp.Instance.Connect(ConnectionString) 
 
Then take it from there ..... does that help? 
 
If your database is encrypted you'll have to pass the coonection string in as a parameter that the job service hides and decrypts. 
 
HTH, Barry.