Archive

Posts Tagged ‘parameters’

Table-Valued Parameters: Reuse model collection types

July 31, 2015 Leave a comment

This is the last post of the Table-Valued Parameters (TVP) series. In the previous post we saw we can use TVP with CodeFluent Entities. We started with a single column TVP, then we create a more complex table type. The last thing is that CodeFluent Entities can generate a TVP per entity. Let’s see what does this means:

Database:

CREATE TYPE [dbo].[CustomerType] AS TABLE (
 [Customer_Id] [int] NOT NULL,
 [Customer_FirstName] [nvarchar] (256) NULL,
 [Customer_LastName] [nvarchar] (256) NULL,
 [_rowVersion] [binary] (8) NULL)
GO

CREATE PROCEDURE [dbo].[Category_ProcessCategoriesWithCollection]
(
 @categories [dbo].[CategoryType] READONLY
)
AS
SET NOCOUNT ON
DECLARE @_c_categories int; SELECT @_c_categories= COUNT(*) FROM @categories
SELECT * FROM Category
INNER JOIN @categories AS c
ON [Category].[Category_Name] LIKE c.Category_Name + '%'
RETURN
GO

Business Object Model:

public static void ProcessCategoriesWithCollection(
    Samples.TVP.CategoryCollection categories
)
{
    CodeFluent.Runtime.CodeFluentPersistence persistence =
      CodeFluentContext.Get(
        Samples.TVP.Constants.Samples_TVPStoreName
      ).Persistence;
    persistence.CreateStoredProcedureCommand(
      null,
      "Category",
      "ProcessCategoriesWithCollection");
    persistence.AddArrayParameterObject("@categories", categories);
    ...
}

So we have a strongly typed code generated the model. How do we generate this code?

First we have to set the table type name (name used to create the database type)


Then we indicate the BOM producer to use user defined type (UDT):


Yep, only two simple attributes to set and CodeFluent Entities generates lots of code for you J

Happy Querying,

The R&D Team.

Table-Valued Parameters: Multi-Columns

July 24, 2015 Leave a comment

In previous blog posts we’ve seen how to use Table-Valued Parameters (TVP) that contains one column. Today we’ll see how to use TVP that contains two columns or more.

First we create our model:

CodeFluent Entities cannot create multi-columns TVP, so we simply use “object[]” to declare the parameter and we will create the table type by hand in an SQL file. For much convenience we’ll include the hand-written script in the generation process, so each time you generate the model, the custom script is executed J Persistence producers provide a way to execute a hand-made script before or after a standard generated script. Generated scripts are ran in a specific order, and you can plug-in your own hand-made ones by using the script name prefixed with the desired adjective (before or after). You’ll find a list of possibilities on the documentation page.

Let’s create a new SQL file named “before_<Project default namespace>_procedures.sql”

Then copy these lines into the before script file:

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = object_id(N'[dbo].[Customer_TVPInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Customer_TVPInsert]
GO

IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'CustomerTableType' AND ss.name = N'dbo')
DROP TYPE [dbo].[CustomerTableType]
GO

CREATE TYPE dbo.CustomerTableType AS TABLE
(
	FirstName	nvarchar(256),
	LastName	nvarchar(256)
)

We can now map the method parameter to this Table Type:

Note: If the parameter is not in the list, you can create it by using the Add button.

That’s it, we can use the generated code:

Customer.TVPInsert(new Tuple<string, string>[]
{
    Tuple.Create("John", "Doe"),
    Tuple.Create("Jane", "Doe")
});

Or maybe you prefer a typed version:

public class CustomerTvp
{
    public string FirstName { get; set; }
    public string LastName { get; set; }

    public CustomerTvp(string firstName, string lastName)
    {
        FirstName = firstName;
        LastName = lastName;
    }
}
Customer.TVPInsert(new CustomerTvp[]
{
    new CustomerTvp("John", "Doe"),
    new CustomerTvp("Jane", "Doe")
});

The TVPInsert method has a parameter of type “object[]”. You can change it to a typed one as we’ve seen in a previous blog post.

 

And voilà !

Happy Querying,

The R&D Team.

Categories: .NET Tags: , , , ,

Table-Valued Parameters: Use IEnumerable instead of an array

July 17, 2015 Leave a comment

We’ve seen that CodeFluent Entities supports Table-Valued Parameters. The generated .NET method has a parameter of type array:

public static CustomerCollection LoadByIds(System.Guid[] ids)

What you may not know is that you can IEnumerable, IEnumerable<T>, DataTable or IDataReader instead of the array!

Select your parameter and set Type name to the desired type name:

After generating the code, the signature of the method is

public static SampleAdvancedTvp.CustomerCollection LoadByIds(
    System.Collections.IEnumerable ids
)

Happy Querying,
THe R&D Team.

Table-Valued Parameters: CFQL operators

July 10, 2015 Leave a comment

CodeFluent Query Language (CFQL) allows to quickly create simple methods. CFQL provides support for common operations with Table Value Parameters.

To show result of queries we will use the following data:

IN

 

Using the IN operator you can exclude values that aren’t included in a list. For instance:

load(guid[] ids) WHERE Id IN (@ids)

The generated SQL procedure:

CREATE PROCEDURE [dbo].[Customer_LoadByIdsIn]
(
 @ids [dbo].[cf_type_Customer_LoadByIdsIn_0] READONLY
)
AS
SET NOCOUNT ON
DECLARE @_c_ids int; SELECT @_c_ids= COUNT(*) FROM @ids
SELECT DISTINCT
    [Customer].[Customer_Id],
    [Customer].[Customer_Name],
    [Customer].[Customer_DateOfBirth]
FROM [Customer]
WHERE [Customer].[Customer_Id] IN (((SELECT * FROM @ids)))

RETURN
GO

 

 // John and Jane
CustomerCollection.LoadByIds(new int[] { 1, 2 });
 // Empty result set
CustomerCollection.LoadByIds(new int[0]);

Equals (=)

 

Using the equals (=) operator you can exclude values that aren’t included in a list, but unlike the IN operator, when the list is empty no filter is applied. For instance:

load(guid[] ids) WHERE Id = @ids

The generated SQL procedure:

CREATE PROCEDURE [dbo].[Customer_LoadByIdsEquals]
(
 @ids [dbo].[cf_type_Customer_LoadByIdsEquals_0] READONLY
)
AS
SET NOCOUNT ON
DECLARE @_c_ids int; SELECT @_c_ids= COUNT(*) FROM @ids
SELECT DISTINCT
    [Customer].[Customer_Id],
    [Customer].[Customer_Name],
    [Customer].[Customer_DateOfBirth]
FROM [Customer] LEFT OUTER JOIN @ids AS _t_ids ON ((@_c_ids = 0)
OR (([Customer].[Customer_Id] = _t_ids.Item)))
WHERE ((@_c_ids = 0) OR (([Customer].[Customer_Id] = _t_ids.Item)))

RETURN
GO

 

 // John and Jane
CustomerCollection.LoadByIds(new int[] { 1, 2 });
 // John, Jane and Jimmy
CustomerCollection.LoadByIds(new int[0]);

Comparison operators: Like, StartsWith, greater than, freetext, etc.

 

You can use comparison operators between a single value and a TVP:

load(string[] names) WHERE Name STARTSWITH @names

The generated SQL procedure:

CREATE PROCEDURE [dbo].[Customer_LoadByNamesStartsWith]
(
 @names [dbo].[cf_type_Customer_LoadByNamesStartsWith_0] READONLY
)
AS
SET NOCOUNT ON
DECLARE @_c_names int; SELECT @_c_names= COUNT(*) FROM @names
SELECT DISTINCT
    [Customer].[Customer_Id],
    [Customer].[Customer_Name],
    [Customer].[Customer_DateOfBirth]
FROM [Customer] LEFT OUTER JOIN @names AS _t_names ON ((@_c_names = 0)
OR (([Customer].[Customer_Name] LIKE (_t_names.Item + '%'))))
WHERE ((@_c_names = 0)
OR (([Customer].[Customer_Name] LIKE (_t_names.Item + '%'))))

RETURN
GO

 

 // Jane, John
CustomerCollection.LoadByNamesStartsWith(new string[] { "Ja", "Jo" });
 // Jane, John and Jimmy
CustomerCollection.LoadByNamesStartsWith(new string[0]);


Custom

 

You can use RAW methods with Table Value Parameter. Here’s an example with inline SQL:

LOAD(string[] names)
WHERE [EXISTS (SELECT * FROM @names AS n WHERE $Name$ = n.Item)]

The generated SQL procedure:

CREATE PROCEDURE [dbo].[Customer_LoadCustom]
(
 @names [dbo].[cf_type_Customer_LoadCustom_0] READONLY
)
AS
SET NOCOUNT ON
DECLARE @_c_names int; SELECT @_c_names= COUNT(*) FROM @names
SELECT DISTINCT
    [Customer].[Customer_Id],
    [Customer].[Customer_Name],
    [Customer].[Customer_DateOfBirth]
FROM [Customer]
WHERE EXISTS (
SELECT * FROM @names AS n WHERE [Customer].[Customer_Name] = n.Item
)

RETURN
GO

 

 // Jane, John
CustomerCollection.LoadCustom(new string[] { "Jane", "John" });
 // Empty result set
CustomerCollection.LoadCustom(new string[0]);

Happy Querying,

The R&D Team.

Table-Valued Parameters: Basics

March 24, 2015 Leave a comment

You can use table-valued parameters (TVP) to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

Great news, you can use TVP with CodeFluent Entities! Let’s see how you can use TVP to bulk load rows based on a list of id.

Create a new CFQL method:

Note the usage of “[]” after the type name. The generated method is:

public static CustomerCollection LoadByIds(System.Guid[] ids)

And the generated stored procedure:

CREATE TYPE [dbo].[cf_type_Customer_LoadByIds_0] AS TABLE (
 [Item] [uniqueidentifier] NULL)
GO
CREATE PROCEDURE [dbo].[Customer_LoadByIds]
(
 @ids [dbo].[cf_type_Customer_LoadByIds_0] READONLY
)
AS
SET NOCOUNT ON
DECLARE @_c_ids int; SELECT @_c_ids= COUNT(*) FROM @ids
SELECT DISTINCT [Customer].[Customer_Id], [Customer].[Customer_FirstName], [Customer].[Customer_LastName], [Customer].[Customer_DateOfBirth]
    FROM [Customer]
    WHERE [Customer].[Customer_Id] IN (((SELECT * FROM @ids)))

RETURN
GO

Table-Valued Parameters require SQL Server 2008. Don’t forget to change the target of the SQL Server producer to use at least this version.

Additionally set Legacy String Array Mode to false :

Happy Coding,

The R&D Team.

References: http://www.softfluent.com/documentation/Methods_WorkingWithArrays.html

Table-Valued Parameters: Basics

February 11, 2015 Leave a comment

First, what is Table-Valued Parameters ?

Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data.

You can use table-valued parameters (TVP) to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

Great news, you can use TVP with CodeFluent Entities! Let’s see how you can use TVP to bulk load rows based on a list of id.

Create a new CFQL method:

Note the usage of “[]” after the type name. The generated method is:

public static CustomerCollection LoadByIds(System.Guid[] ids)

And the generated stored procedure:

CREATE TYPE [dbo].[cf_type_Customer_LoadByIds_0] AS TABLE (
 [Item] [uniqueidentifier] NULL)
GO
CREATE PROCEDURE [dbo].[Customer_LoadByIds]
(
 @ids [dbo].[cf_type_Customer_LoadByIds_0] READONLY
)
AS
SET NOCOUNT ON
DECLARE @_c_ids int; SELECT @_c_ids= COUNT(*) FROM @ids
SELECT DISTINCT [Customer].[Customer_Id], [Customer].[Customer_FirstName], [Customer].[Customer_LastName], [Customer].[Customer_DateOfBirth]
    FROM [Customer]
    WHERE [Customer].[Customer_Id] IN (((SELECT * FROM @ids)))

RETURN
GO

Table-Valued Parameters require at least SQL Server 2008. Don’t forget to change the target of the SQL Server producer to use at least this version. Additionally set Legacy String Array Mode to false.

Additionally set Legacy String Array Mode to false.


References: http://www.softfluent.com/documentation/Methods_WorkingWithArrays.html

Happy coding,

The R&D Team.