After implementing the function to Update the  Computerinformation in MDT as explained in the last post, I found more and more settings from the MDT Database I wanted to get or set during the execution of the scripts. So instead of reading the complete set of settings for an entry I started creating similar functions to get/set the Computername, the UDDir, etc. But just after a couple of settings it was obvious that this couldn't be the final solution. The MDT settings table contains 84 different settings (so far) creating a function for each of them would make it confusing and hard to maintain.

So what I wanted to have was a function which I just could give the name of the setting I wanted to get/set and if necessary the type (Computer, Location, etc.). Almost all columns within the settings table contains nvarchar values with either 50 or 255 character length. Only OSDINSTALLSILENT is a Char value. The first thing we would need is again two stored procedures. One to set a supplied value, one to read. Actually, they are quite similar. I will just go through the interesting parts of the get stored procedure. Find the necessary SQL scripts to create both Procedures at the end of this post.

So let's get into the code. The Stored Procedure will first check if the given setting exists.

IF EXISTS (SELECT 1 FROM syscolumns c INNER JOIN sysobjects o ON c.id = o.id WHERE o.name LIKE 'Settings' AND c.name LIKE @Setting)

If it exists, we prepare a dynamic select statement based on the supplied values

DECLARE @Query as nvarchar(500)
SET @Query = 'SELECT ' + @Setting + ' FROM dbo.Settings WHERE [Type] = ''' + @Type + ''' AND ID = @MDTID'

You will probably recognize that the @MDTID variable has not been concatenated. We just supplied it as part of the statement. But why? If you try to concatenate it like 'SELECT ... ID = ' + @MDTID you would get a conversion error. Then you could try to use the CAST Function to convert the int value into a nvarchar value. This would enable you to create the select statement, but then the value supplied is not an integer anymore so can't be used to compare it against the ID. We would need to cast it again back into Int. So the easiest solution for this is a system stored procedure called sp_executesql which gives us the possibility to supply a parameter definition and let it take care about this problem.

DECLARE @ParameterDefinition AS NVARCHAR(100)
SET @ParameterDefinition = '@MDTID INT'
EXECUTE sp_executesql @Query, @ParameterDefinition, @MDTID

Now we have a stored procedure we can call to get or set any setting by name. As with every stored procedure, you can use it directly within the customsettings.ini as explained in the last post or directly from a script. But again we also wanted to use this via our custom webservice. So we just add the following two functions to our class:

    <WebMethod()> _
    Public Function GetSetting(ByVal MDTID As Integer, ByVal Setting As String, ByVal Type As String) As String
        Dim Result As String = ""

        Dim connection As New SqlClient.SqlConnection(Me._ConnectionString)

        Try
            ' Create connection to MDT Database
            connection.Open()

            'Prepare command to execute Stored Procedure
            Dim command As New SqlClient.SqlCommand("GetSetting", connection)
            command.CommandType = CommandType.StoredProcedure
            command.Parameters.Add("@MDTID", SqlDbType.Int).Value = MDTID
            command.Parameters.Add("@Setting", SqlDbType.NVarChar, 50).Value = Setting
            command.Parameters.Add("@Type", SqlDbType.NVarChar, 20).Value = Type

            'Execute Stored Procedure
            Trace.WriteLine("GetSetting: About to execute Stored Procedure 'GetSetting'")
            Result = command.ExecuteScalar

        Catch ex As Exception
            Trace.WriteLine("GetSetting: Error " & Err.Number & ": " & Err.Description)
            Trace.WriteLine("GetSetting: Source: " & Err.Source)
        Finally
            connection.Close()
        End Try

        Return Result
    End Function

    <WebMethod()> _
    Public Function SetSetting(ByVal MDTID As Integer, ByVal Setting As String, ByVal Value As String, ByVal Type As String) As Integer
        Dim Result As Integer

        Dim connection As New SqlClient.SqlConnection(Me._ConnectionString)

        Try
            ' Create connection to MDT Database
            connection.Open()

            'Prepare command to execute Stored Procedure
            Dim command As New SqlClient.SqlCommand("SetSetting", connection)
            command.CommandType = CommandType.StoredProcedure
            command.Parameters.Add("@MDTID", SqlDbType.Int).Value = MDTID
            command.Parameters.Add("@Setting", SqlDbType.NVarChar, 50).Value = Setting
            command.Parameters.Add("@Value", SqlDbType.NVarChar, 255).Value = Value
            command.Parameters.Add("@Type", SqlDbType.NVarChar, 20).Value = Type
           
            'Execute Stored Procedure
            Trace.WriteLine("SetSetting: About to execute Stored Procedure 'SetSetting'")
            Result = command.ExecuteScalar

        Catch ex As Exception
            Trace.WriteLine("SetSetting: Error " & Err.Number & ": " & Err.Description)
            Trace.WriteLine("SetSetting: Source: " & Err.Source)
        Finally
            connection.Close()
        End Try

        Return Result
    End Function

The content is pretty much the same as of the function we used to Update the Computer information. You see as soon as we have our fundament, it's easy to extend. Again you can download all source files mentioned in this post.