Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Create Reusable Linq To SQL For Stored Procedures

I am working on a new project that needs to use Linq To SQL. I have been asked to create a generic or reusable Linq to SQL class that can be used to execute stored procedures.

In ADO.Net I knew how to do this by just passing in a string of what I wanted to execute and I could pass in different strings for each query I need to run:

SqlCommand cmd = new SqlCommand("myStoredProc", conn); // etc, etc

I am struggling with how to create something similar in Linq To SQL, if it is even possible. I have created a .dbml file and added my stored procedure to it. As a result, I can return the results using the code below:

public List<myResultsStoreProc> GetData(string connectName)
{
   MyDataContext db = new MyDataContext (GetConnectionString(connectName));
   var query = db.myResultsStoreProc();

   return query.ToList();
}

The code works but they want me to create one method that will return whatever stored procedure I tell it to run. I have searched online and talked to colleagues about this and have been unsuccessful in finding a way to create reusable stored proc class.

So is there a way to create a reusable Linq to SQL class to execute stored procs?

Edit:

What I am looking for is if there is a way to do something like the following?

public List<string> GetData(string connectName, string procedureName)
{
   MyDataContext db = new MyDataContext (GetConnectionString(connectName));
   var query = db.procedureName();

   return query.ToList();
}

I have reviewed the MSDN docs on Linq To Sql and these are showing the table in the IEnumerable:

IEnumerable<Customer> results = db.ExecuteQuery<Customer>(
   @"select c1.custid as CustomerID, c2.custName as ContactName
      from customer1 as c1, customer2 as c2
      where c1.custid = c2.custid"
);

I am looking for something very generic, where I can send in a string value of the stored proc that I want to execute. If this is not possible, is there any documentation on why it cannot be done this way? I need to prove why we cannot pass a string value of the name of the procedure to execute in Linq To Sql

like image 667
Taryn Avatar asked May 30 '12 19:05

Taryn


People also ask

Can we use LINQ with stored procedure?

In LINQ to SQL, we can use stored procedures with or without parameters to get the required data from database tables. Before we start using LINQ to SQL with a stored procedure, we need to create a database with required tables and map those tables to LINQ to SQL file (. dbml).

Is LINQ better than stored procedure?

Stored procedures are faster as compared to LINQ query since they have a predictable execution plan and can take the full advantage of SQL features. Hence, when a stored procedure is being executed next time, the database used the cached execution plan to execute that stored procedure.

Is LINQ to SQL still supported?

LINQ to SQL was the first object-relational mapping technology released by Microsoft. It works well in basic scenarios and continues to be supported in Visual Studio, but it's no longer under active development.


1 Answers

DataContext.ExecuteCommand is not quite what you are looking for, as it only returns an int value. What you want instead is DataContext.ExecuteQuery, which is capable of executing a stored procedure and returning a dataset.

I would create a partial class for your DBML in which to store this function.

public List<T> GetDataNoParams(string procname)
{
   var query = this.ExecuteQuery<T>("Exec " + procname);

   return query.ToList();
}

public List<T> GetDataParams(string procname, Object[] parameters)
{
   var query = this.ExecuteQuery<T>("Exec " + procname, parameters);

   return query.ToList();
}

To call a stored procedure you would do:

GetDataNoParams("myprocedurename");

or

GetDataParams("myotherprocedure {0}, {1}, {2}", DateTime.Now, "sometextValue", 12345);

or

GetDataParams("myotherprocedure var1={0}, var2={1}, var3={2}", DateTime.Now, "sometextValue", 12345);

If you want to call procedures with no return value that is easy enough too, as I'm sure you can see, by creating a new method that doesn't store/return anything.

The inspiration came from http://msdn.microsoft.com/en-us/library/bb361109(v=vs.90).aspx.

like image 125
Peter Avatar answered Sep 20 '22 09:09

Peter