StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » FillbyPrimaryKey from SPROC

FillbyPrimaryKey from SPROC

Charles R Hankey — Fri, 25 Sep 2009 20:42:24 GMT

I love FillbyPrimaryKey with the overload that accepts a paramarray of keys. But I would like to us a SPROC to get the data on the back end as there are a number of joins I'd prefer to handle there.

What is the syntax for calling using FillbyStoreProcedure or cmd.CommandType = StoredProcedure and having, as a parameter, a list(of T)

( need to do this with both integer keys and system.guid keys )

TIA

RE: FillbyPrimaryKey from SPROC

Charles R Hankey — Fri, 25 Sep 2009 20:42:24 GMT

Just posted my first shot at Generic methods on the Walkthrough referenced here. Basically, it allows me to use one sub in my BaseBO for whatever data type of pk and there is also a refactor of the shared function in my Base.Utility class that converts a List(Of T) to a datatable with one column of type T

I have been working a bit with LINQ and hope to post some samples on really basic stuff, working with SF types soon. Getting my head around some of the Generic stuff is somehow helping with that.

RE: FillbyPrimaryKey from SPROC

Greg McGuffey — Fri, 25 Sep 2009 14:41:30 GMT

Well, this is some really cool stuff. The third article also introduced the hierarchyid data type in SQL which is just bloody awesome! I use hierarchies quite a lot and it was a pain in the...er...posterior. I also notices that it appears that T-SQL now can do OOP type stuff now. I.e. a variable that is a hierarchyid has methods! When did this happen?!?! Anyway, I'm very excited to delve into this more and hope to install SQL Server 2008 soon....Now back to planing for the migration of my companies SQL Server 2000 servers to SQL Server 2005....sigh...

RE: FillbyPrimaryKey from SPROC

Charles R Hankey — Thu, 24 Sep 2009 20:53:45 GMT

Walkthrough is posted on User Contributed Samples

RE: FillbyPrimaryKey from SPROC

Charles R Hankey — Thu, 24 Sep 2009 15:05:48 GMT

Greg and I have already discussed this but I wanted to continue the discussion here for the benefit of other SF developers :

In SQL2008 the Table View Parameter has added an incredibly powerful tool for passing parameters into T-SQL sprocs. An old friend, Kevin Goff, has written some very good stuff for Code Magazine on all this and he sent me some links about the topic in general and TVPs in particular

[url]http://www.setfocus.com/TechnicalArticles/Articles/sql-server-2005-tsql-3.aspx[/url]
(go down to section 5....this converts a CSV to a table variable for subsequent join operations)

[url]http://www.code-magazine.com/Article.aspx?quickid=0709031 [/url]
(listings 12 and 13....this converts an XML string to a table variable)

[url]http://www.code-magazine.com/Article.aspx?quickid=0807041[/url]
Finally, wrote about using the new table type in 2008 to pass a datatable as a parameter
(Tip #5)

This last link shows how to pass a datatable as a parameter into a sproc, where it can be used to do one big honkin' insert.

I am particularly interested in doing this with a table of keys in order to accomplish the same thing as fillbyprimarykey() using the paramarray overload.

NOTE: This is only relevant to SQL2008 and that is sql10 - spent an embarassing amount of time last night wondering why I was getting errors executing scripts, for getting the sql express db I was in ( sql9 - like vb 9 latest version, right? WRONG ) was sql2005.

Anyway, as soon as I get a complete walk-through on this done I'll post it . There is a huge amount of potential here and I'm amazed I haven't run across this solution before, though I'm sure somebody mentioned it but I just didn't get the implications at the time.

RE: FillbyPrimaryKey from SPROC

Greg McGuffey — Thu, 24 Sep 2009 00:58:22 GMT

I haven't used the FillByStoredProcudure, but I use a lot of sprocs, for a lot of things. I don't think that a .NET generic type (List) will mix well with SQL. I know it won't when using SqlCommand.Parameters. Here is what the code might look like.

SQL Sproc Example:
[codesnippet]Create Proc testFill
  @key int
AS
  Select table1.*
  From table1 Left Outer Join table2
  Where id = @key
[/codesnippet]

SF Fill procedure using the above sproc:
[codesnippet]Public Sub FillByKey(ByVal key As Integer)
  Using cmd As New SqlCommand()
    cmd.CommandType = StoredProcedure
    cmd.CommandText = "testFill"
    cmd.Parameters.AddWithValue("@key", key).SqlDataType = SqlDataType.int
    Me.FillDataTable(cmd)
  End Using
End Sub[/codesnippet]

Now, I have a feeling that it is possible that a sproc might not work out as well as you'd like. The reason being that you want to pass a list in. I.e. there will be an unknown number of keys, and as far as I know, SQL server doesn't do that well (if it does, I'd really like to know about it!). You might be able to use a CLR sproc that could be used to parse a string of delimited keys in, but an easier way is this. You simply build the SQL statement with the appropriate number of parameters and then add those parameters, driven by the list. In order to handle the complex joins, you could either do them here or create a view and then query the view (probably the better way to go).

Here is an example. I just hacked this out, so I might have messed up on some of the syntax or made some other mistake. Hopefully you'll get the idea.
[codesnippet]
'''<Summary>
''' Fill the BO with the records that contain the indicated keys.
''' This is generic so one method can handle both integers and Guids.
'''</Summary>
Public Sub FillByKeyList(Of T)(keyList As List(Of T))
  '-- Validate that T of supported type.
  If T.GetType() IsNot GetType(Integer) AndAlso T.GetType() IsNot GetType(Guid) Then
    Throw New ArgumentException("The list must be of either integers or GUIDS.")
  End If

  '-- Define a template that will be used to build the name of the parameter.
  '   {1} is replaced with a delimiter if needed and {0} with
  '   the index of the item in the list.
  Dim paramNameTemplate As String = "{1}@id_{0}"

  '-- Build basic SQL statement. We'll use a view to make life easier.
  Dim sql As String
  sql = "Select * From ComplexJoinView"

  '-- Build an In clause with parameters names.
  Dim keyParamList As String = String.Empty
  For idx As Integer = 0 To keyList.Count - 1
    Dim itemDelimiter As String = ","
    If idx = 0 Then idx = String.Empty
    keyParamList &= String.Format(paramNameTemplate, idx, itemDelimiter)
  Next

  '-- Add In clause to SQL
  sql &= String.Format(" In({0})", keyParamList)

  '-- Build SQL Command
  Using cmd As New SqlCommand()
    cmd.CommandText = sql

    '-- Add the parameters for each key
    For idx As Integer = 0 To keyList.Count - 1
      '-- Determine what the SQL Data Type is Based on generic type.
      Dim sqlType As SqlDataType
      If T.GetType() Is GetType(Integer) Then
        sqlType = SqlDataType.Integer
      ElseIf T.GetType() Is GetType(Guid) Then
        sqlType = SqlDataType.UniqueIdentifier
      End If

      '-- Build name of this parameter
      Dim paramName As String = String.Format(paramNameTemplate, idx, String.Empty)

      '-- Add the parameter
      cmd.Parameters.AddWithValue(paramName, keyList(idx)).SqlDataType = sqlType
    Next

    '-- Fill BO
    Me.FillDataTable(cmd)
  End Using
End Sub[/codesnippet]

Hopefully that will get you going or spark a useful idea!