Friday, October 01, 2010

Table Value Parameter with LINQ, (LINQ for Stored Procedure that has datatable as an input parameter)

Table Value Parameter with LINQ, (LINQ for Stored Procedure that has datatable as an input parameter)
If you are using LINQ to SQL with SQL server database then Visual Studio will not allow you to drag your SP into dbml file like you do for other simple SP and also Visual Studio will not able to generate the code for this. There is no other ways to generate the code for this situation (SP has input parameter as a datatable) in the DataContext.
You have to write the code into partial class (given with your .dbml file), now assume you have SP signature like below

Stored Procedure

CREATE PROCEDURE [dbo].[GetResource]
( @ResourceID dbo.Resource READONLY) 

Resource: is datatable type

CREATE TYPE [dbo].[Resource] AS TABLE
( [ResourceId] [int] NOT NULL )

While writing the code in the DataContext we have to manually execute the SP and return as a List, see below code

NameSpace

using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Linq.Expressions;

DataContext Code

public partial class YourDataContext : System.Data.Linq.DataContext
{
public IEnumerable<ResourceData> GetResource(List<int> resourceId)
{
DataTable resourceIdTable = new DataTable();
resourceIdTable.Columns.Add("Resource", typeof(int));
// Fill the datatable from the input List
foreach (int r in resourceId)
resourceIdTable.Rows.Add(r);
// "GetSqlCommand()" is a method will open the connection and return sqlCommand Object
SqlCommand cmd = ConnectionManager.GetSqlCommand("GetResource", connectionString);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter p1 = cmd.Parameters.AddWithValue("@ResourceID", resourceIdTable);
p1.SqlDbType = SqlDbType.Structured;
p1.TypeName = "Resource";
SqlDataReader reader = cmd.ExecuteReader();
return this.Translate<ResourceData>(reader).ToList();
}
}

Call DataContext Method

YourDataContext rdc = new YourDataContext();
IEnumerable<ResourceData> result = rdc.GetResource(resourceId);

foreach (ResourceData res in result)
{ MessageBox.Show("ResourceId : " + res.Id + " Resource Name : " + res.Name);
}

ResourceData: is Entity class like below

public sealed class ResourceData
{
private int _id;
private string _name;
public int Id
{
get { return _id; }
private set { _id = value; }
}
public string Name
{
get { return _name; }
private set { _name = value; }
}
}

2 comments:

Edgar Flores said...

Very well Done man, i like your blog, cuz u have interesting things... i have one question have u ever send oracle array to a procedure? do one post about that pleaseee.

Ritesh_Kesharwani said...

Hi,

Please see below link have idea about sending Array to oracle SP

http://riteshk.blogspot.com/2009/03/how-to-send-array-as-input-parameter-to.html