CODEWITHSUNDEEP
c#linqlinq-to-sqlado.net
Taryn
Taryn

Reputation: 247680

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

Upvotes: 17

Views: 8546

Answers (3)

Atul K.
Atul K.

Reputation: 101

When we drop a Table or StoredProcedure in our .dbml file it creates its class which communicates with the data layer and our business logic.

In Linq to SQL we have to have the StoredProcedures or Tables present in the .dbml file otherwise there is no way to call a generic method in Linq to SQL for calling a stored procedure by passing its name to a method.

But in ADO.Net we can do it (like you know)

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

Upvotes: 0

Peter
Peter

Reputation: 9712

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.

Upvotes: 18

CodingGorilla
CodingGorilla

Reputation: 19842

The simplest answer to your question is that you can grab the Connection property of your MyDataContext and create and execute your own SqlCommands just like you would in straight up ADO.Net. I'm not sure if that will serve your purposes, especially if you want to retrieve entities from your LINQ to SQL model.

If you want to return entities from the model, then have a look at the DataContext.ExecuteCommand method.

Upvotes: 2

Related Questions