Workflow and ServiceDesk Community

In this Article I'm going to explain how to talk to SQL.

• List
• Setup
• Simple Component
• Deploy
• Help File
• Logging
• Inputs
  • Checkbox
  • Dropdown / Combo
• Inspecting other Components
• Creating Globals
• Creating Project Properties
• Working with SQL (this)
• Working with a Web Service

Build a component like previous Articles have shown.

Now we can create a method that gets some information from SQL.

I would add a new input to the Component that's a SQL Connection String, this makes it easier to manage and configure.

The following could be added to your Run method.

We need a SQL Connection first.

using (SqlConnection DbConn = new SqlConnection(dbConnStr))
{
}

Where dbConnStr is retrieved from the Property.

Now declare your SQL string.

string sql = "SELECT DISTINCT vC.[Guid] [ComputerGuid] ,vC.[Name] [ComputerName] ,vC.[IP Address] [ComputerIPAddress] ,TC.[Subnet Mask] [ComputerSubnetMask] FROM vComputer vC JOIN Inv_AeX_AC_TCPIP TC on vC.Guid = TC._ResourceGuid WHERE Name = @ComputerName";

Your SQL could be anything but here's a string to get some information from a Computer in the SMP.

SELECT DISTINCT 
  vC.[Guid] [ComputerGuid] 
  ,vC.[Name] [ComputerName] 
  ,vC.[IP Address] [ComputerIPAddress] 
  ,TC.[Subnet Mask] [ComputerSubnetMask] 
FROM 
  vComputer vC 
  JOIN Inv_AeX_AC_TCPIP TC on vC.Guid = TC._ResourceGuid 
WHERE 
  Name = @ComputerName

Next we need a SQLCommand, pass both the sql string and the Connection (DbConn)

using (SqlCommand sqlCommand = new SqlCommand(sql, DbConn))
{
}

Set the appropriate properties

sqlCommand.CommandText = sql.ToString();
sqlCommand.Connection = DbConn;
sqlCommand.CommandType = CommandType.Text;

If you have any parameters you can set them here too

I've passed in the computerName as a parameter.

sqlCommand.Parameters.Add("@ComputerName", SqlDbType.NVarChar);
sqlCommand.Parameters["@ComputerName"].Value = computerName;

Finally we need to Open the connection and Execute the command.

DbConn.Open();
SqlDataReader dataReader = sqlCommand.ExecuteReader();

If we've opened it we need to close it, good use for a try/catch/finally statement.

try
{
}
catch (Exception ex)
{
}
finally
{
	dataReader.Close();
}

Inside your try statement let's check for data/records and do something with them.

if (dataReader.HasRows)
{
    while (dataReader.Read())
    {
        Guid computerGuid = dataReader.GetGuid(0);
        string strComputerName = dataReader.GetString(1);
        string strComputerIPAddress = dataReader.GetString(2);
        string strComputerSubnetMask = dataReader.GetString(3);
        
        //Do something
    }
}

Full code

private void GetComputerInfo(IData data, string dbConnStr, string computerName, bool logAllErrors)
{
	using (SqlConnection packageServerDbConn = new SqlConnection(dbConnStr))
	{
		string sql = "SELECT DISTINCT vC.[Guid] [ComputerGuid] ,vC.[Name] [ComputerName] ,vC.[IP Address] [ComputerIPAddress] ,TC.[Subnet Mask] [ComputerSubnetMask] FROM vComputer vC JOIN Inv_AeX_AC_TCPIP TC on vC.Guid = TC._ResourceGuid WHERE Name = @ComputerName";
		
		using (SqlCommand getComputerInfoCommand = new SqlCommand(sql, packageServerDbConn))
		{
			getComputerInfoCommand.CommandText = sql.ToString();
			getComputerInfoCommand.Connection = packageServerDbConn;
			getComputerInfoCommand.CommandType = CommandType.Text;

			getComputerInfoCommand.Parameters.Add("@ComputerName", SqlDbType.NVarChar);
			getComputerInfoCommand.Parameters["@ComputerName"].Value = computerName;

			packageServerDbConn.Open();

			SqlDataReader dataReader = getComputerInfoCommand.ExecuteReader();

			try
			{
				//get data
				if (dataReader.HasRows)
				{
					while (dataReader.Read())
					{
						//ComputerGuid	                        ComputerName	ComputerIPAddress	ComputerSubnetMask
						//3582B4B5-E3BC-41E1-8E18-07A0AA6E848D	PROTIRUS026	    10.10.100.156	    255.255.255.0
						Guid computerGuid = dataReader.GetGuid(0);
						string strComputerName = dataReader.GetString(1);
						string strComputerIPAddress = dataReader.GetString(2);
						string strComputerSubnetMask = dataReader.GetString(3);
						
						ComputerType c = new ComputerType();
						c.Id = computerGuid;
						c.IpAddress = strComputerIPAddress;
						c.Subnet = strComputerSubnetMask;
						c.Name = strComputerName;
						this.computers.Add(c);
					}
				}
				else
				{
					base.LogError(data, "No rows found.");
				}
			}
			catch (Exception ex)
			{
				if (logAllErrors)
				{
					base.LogError(data, ex.Message);
				}
			}
			finally
			{
				dataReader.Close();
			}
		}
	}
}