Workflow - Component - Developer Guide - SQL

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