RSS

Category Archives: LinQ

MSDTC on server … is unavailable

By Sochinda,

Please go to Run: services.msc

and find service’s called Distributed Transaction Coordinator and start

11

 

 
Leave a comment

Posted by on November 11, 2015 in C#, LinQ, SQL Server

 

LINQ and Dynamic Query – Comparing DateTime value

By: lakswin

In one of our development, we end up with the situation that needs to check the DateTime value in Dynamic LINQ query. Trying different ways out there and googling a bit, finally got some useful references that helped us to resolve the same

Hope, you would love reading!

 

Example:

TestEntities te = new TestEntities();

string dateString = “12/01/2010″;

 

Used the following overload for “.Where” :

# 1:

IQueryable<T>.Where(Expression<Func<State, bool>> predicate)

var dateTest = te.SiteLogs

.Where(String.Format(“it.TimeStamp >= DATETIME ‘{0}’”, Convert.ToDateTime(dateString).ToString(“yyyy-MM-dd HH:mm”)))

.Select(“it.IPAddress”);

 

# 2:

IQueryable.Where(string predicate, params object[] values)

var dateTest1 = te.SiteLogs

.Where(“it.TimeStamp >= @0″,  Convert.ToDateTime(dateString))

.Select(“it.IPAddress”);

 

Observations:

In # 1,

  • We need to say the “Time” format also, that is compulsory. Otherwise it would throw an error as well.  And again, the syntax with “DATETIME” is wired 🙂
  • The resultant date format should be “yyyy-MM-dd “

 

In # 2,

Its clean and simple. I like this syntax. Here you can add any number of Params like @0, @1 & so on

 

References:

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

http://msdn.microsoft.com/en-us/library/bb399176.aspx

 

Copy from: https://lakswin.wordpress.com/2011/10/20/linq-and-dynamic-query-%E2%80%93-comparing-datetime-value/

 
Leave a comment

Posted by on March 10, 2015 in C#, LinQ

 

Export data from a DataSet into a real Excel 2007 file

Ever wanted to add an “Export to Excel” function to your ASP.Net, WinForms or WPF application ?

This free C# and VB.Net library lets you export a DataTable or DataSet of data into a “real” Excel 2007 .xlsx file, using one line of code.

CreateExcelFile.CreateExcelDocument(myDataSet, myExcelPathFilename);

…or if you’re using ASP.Net…

CreateExcelFile.CreateExcelDocument(myDataSet, myExcelFilename, Response);

You can download the full source code using the links below, so you can extend it to add Excel formatting, etc.

It uses the OpenXML libraries, rather than Microsoft’s Visual Studio Tools for Office (VSTO) libraries, so you don’t need to have Excel installed on your server.

The CreateExcelFile library

All of the code you’ll need is contained in one class, CreateExcelFile, which I’ve saved in the file CreateExcelFile.cs (or CreateExcelFile.vb for the VB.Net version).

To use this class, you simply call its CreateExcelDocument function, passing it a DataSet variable (which contains the values you want writing into Excel cells), and the path+filename of the Excel file you wish to create.

Each DataTable within your DataSet will be saved into it’s own Excel worksheet.

// Step 1: Create a DataSet, and put some sample data in it
DataSet ds = CreateSampleData();

// Step 2: Create the Excel .xlsx file
try
{
string excelFilename = “C:\\Sample.xlsx”;
CreateExcelFile.CreateExcelDocument(ds, excelFilename);
}
catch (Exception ex)
{
MessageBox.Show(“Couldn’t create Excel file.\r\nException: ” + ex.Message);
return;
}

Demo program

The attached Visual Studio 2010 C# and VB.Net WinForms demo shows how easy it is to use the CreateExcelFile library.

It consists of a simple dialog with one button on it (shown below).

When you click on the button, it’ll create a DataSet and fill it with some sample data, and prompt you for a location to save our example Excel file to. It will then call the CreateExcelDocument function to create an Excel 2007 .xlsx file containing our DataSet’s data, then open the file in Excel for you to admire.

Screenshot from the C# demo Screenshot from the C# demo

Notice how our demo application created a DataSet containing three DataTables in it, called Drivers, Vehicles & Vehicle Owners. The CreateExcelFile library then created an Excel Worksheet for each of these DataTable names, and wrote each DataTable’s data into it.

Using the “Export to Excel” library in your own application

The CreateExcelFile library has two dependences:

  • DocumentFormat.OpenXml.dll
    From the Microsoft Open XML SDK library
  • WindowsBase.dll
    From the Microsoft .Net Framework library

I have included a copy of these two files in the source code’s lib folder, but ideally, you should download the most recent version from the Microsoft website.

To use the library in your own code, simply add these two files, and the CreateExcelFile.cs file to your Visual Studio project, then add the two .dll files to the References section of your project.

Also, you need to make sure that these two files have the “Copy Local” field set to true. Without this, when you deploy your app, it won’t copy the .dlls to your bindirectory, and IIS will complain that it can’t find the OpenXML libraries.

Then just call the static CreateExcelDocument function, as shown above.

CreateExcelFile.CreateExcelDocument(ds, excelFilename);

I have deliberately left this library’s source code available for you to view and alter, as desired.

Exporting from a List<> or DataTable

With a little help from a discussion on CodeGuru, I added two extra functions, so you can now call the CreateExcelDocument function in three ways:

public static bool CreateExcelDocument<T>(List<T> list, string xlsxFilePath)
public static bool CreateExcelDocument(DataTable dt, string xlsxFilePath)
public static bool CreateExcelDocument(DataSet ds, string xlsxFilePath)

..so you should find it really easy to export your data to Excel from any of these formats.

Using the library in an ASP.Net application

November 2013: With help from my fellow developers, I have made the C# library even easier to use in an ASP.Net application. The ExportToExcel class now lets you create an Excel file without writing to a temporary file first. You just need to pass the “Response” as a parameter.

// In this example, I have a defined a List of my Employee objects.
class Employee;
List<Employee> listOfEmployees = new List<Employee>();

// The following ASP.Net code gets run when I click on my “Export to Excel” button.
protected void btnExportToExcel_Click(object sender, EventArgs e)
{
// It doesn’t get much easier than this…
CreateExcelFile.CreateExcelDocument(listOfEmployees, “Employees.xlsx”, Response);
}

By default, this functionality is disabled in the C# file, so that non-ASP.Net developers don’t get build errors when attempting to use the library. To enable the functionality, you need to manually make two small changes:

First, uncomment the first line of code in the CreateExcelFile.cs file, so it looks like this:

#define INCLUDE_WEB_FUNCTIONS

Then add a new Reference, and select the System.Web library.

Add Reference..

And that’s it. You can now use the three new web-friendly functions for exporting to Excel.

public static bool CreateExcelDocument<T>(List<T> list, string filename, System.Web.HttpResponse Response)
public static bool CreateExcelDocument(DataTable dt, string filename, System.Web.HttpResponse Response)
public static bool CreateExcelDocument(DataSet ds, string filename, System.Web.HttpResponse Response)

Cool, hey ?

You’re welcome to use and adapt this code as you like, but – please – if you like it, leave me a comment below.

Setting the Excel column widths

By default, the CreateExcelFile class just creates a valid Excel 2007 with no formatting, colors, borders, etc.

If you wanted to change this code to set the widths of the columns in your Excel file, below are the changes you’d need to make.

First, you need to add a few lines to the CreateParts function (shown in red):

private static void CreateParts(DataSet ds, SpreadsheetDocument spreadsheet)
{
// Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
uint worksheetNumber = 1;
foreach (DataTable dt in ds.Tables)
{
// For each worksheet you want to create
string workSheetID = “rId” + worksheetNumber.ToString();
string worksheetName = dt.TableName;

WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();

// If you want to define the Column Widths for a Worksheet, you need to do this *before* appending the SheetData
// http://social.msdn.microsoft.com/Forums/en-US/oxmlsdk/thread/1d93eca8-2949-4d12-8dd9-15cc24128b10/
//
// The following example sets the column width, for all columns where we have data, to 20 pixels.

int columnWidthSize = 20;
Columns columns = new Columns();

for (int colInx = 0; colInx < dt.Columns.Count; colInx++)
{
Column column = CustomColumnWidth(colInx, columnWidthSize);
columns.Append(column);
}
newWorksheetPart.Worksheet.Append(columns);

// create sheet data
newWorksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData());

// save worksheet
WriteDataTableToExcelWorksheet(dt, newWorksheetPart);
newWorksheetPart.Worksheet.Save();

Then, add the following function just below the WriteDataTableToExcelWorksheet function:

private static Column CustomColumnWidth(int columnIndex, double columnWidth)
{
// This creates a Column variable for a zero-based column-index (eg 0 = Excel Column A), with a particular column width.
Column column = new Column();
column.Min = (UInt32)columnIndex + 1;
column.Max = (UInt32)columnIndex + 1;
column.Width = columnWidth;
column.CustomWidth = true;
return column;
}

If you wanted to calculate the column widths, based on the data in each column, it’s not easy.
Have a read of this article:
http://polymathprogrammer.com/2010/01/11/custom-column-widths-in-excel-open-xml/

Downloads

C# VB.Net Filename Description
ExportToExcel.zip The full Excel demo, showing how to use the CreateExcelFile library code, and the Open XML libary files.
CreateExcelFile.cs / .vb Just the C#/VB.Net source code file for the CreateExcelFile class.
ExpotToExcelExe.zip The executable (.exe) file of the Demo, if you just want to see what the application looks like.
Note: This zip file contains a copy of the .exe file created by the Visual Studio project which you can download using the link above. This .zip file is safe, but your anti-virus software might complain about it or block it, as it contains an executable file.

Copy from: http://mikesknowledgebase.azurewebsites.net/pages/CSharp/ExportToExcel.htm

 
Leave a comment

Posted by on August 8, 2014 in C#, LinQ

 

Linq to SQL with WCF Services

I was interested to see how I could use Linq to SQL with WCF Services to load and save data using a Silverlight project.  In this post I will expand upon the database I created in my Linq to SQL Tutorial and the console application I wrote for my Set inheritance modifiers with SQLMetal post.

The first step is to enable serialisation on my Linq entities so that they can be sent over the wire. To do this in the O/R Designer you can select the white space of the designer and view the DataContext properties.  Set the property called Serialization Mode to Unidirectional:

Linq Serialization Mode Property

 

If using SQLMetal you can use the serialization command line argument:

1
SQLMetal.exe"/server:localhost /database:University /dbml:University.dbml <strong>/serialization:unidirectional</strong> /namespace:Entities /context:UniversityDataContext /pluralize

Enabling unidirectional serialization in either of these two ways adds the necessaryDataContract and DataMember attributes to the generated entities and properties:

1
2
3
4
5
6
7
8
9
10
11
12
13
[Table(Name="dbo.Student")]
[DataContract()]
public partial class Student : EntityBase, INotifyPropertyChanging, INotifyPropertyChanged
{
    ....
    [Column(Storage="_Forename", DbType="NVarChar(50) NOT NULL", CanBeNull=false, UpdateCheck=UpdateCheck.Never)]
    [DataMember(Order=3)]
    public string Forename
    {
        ....
    }
}

The entities are now in a state where they can be serialised and sent down the wire.  In my WCF service I have a method that returns a list of my Linq to SQL Student entity:

1
2
3
4
5
6
7
public List GetStudents()
{
    using (_context)
    {
        return _context.Students.ToList();
    }
}

These entities can then be easily used by the client, in this case the Silverlight application:

1
2
3
4
5
6
7
8
9
10
11
12
UniversityContractClient _proxy = new UniversityContractClient();
private void PopulateStudents()
{
    _proxy.GetStudentsCompleted += new EventHandler(proxy_GetStudentsCompleted);
    _proxy.GetStudentsAsync();
}
void proxy_GetStudentsCompleted(object sender, GetStudentsCompletedEventArgs e)
{
    dgStudents.ItemsSource = e.Result;
}

Here I am using the list to populate a DataGrid:

Linq WCF Datagrid

This is all very straight forward, but the next step to update the data it a little more complex.  Here is my service method to save a Student entity created or updated by the client:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
public void SaveStudent(Student student)
{
    using (_context)
    {
        if (student.IsNew)
        {
            _context.Students.InsertOnSubmit(student);
        }
        else
        {
            _context.Students.Attach(student, true);
        }
              
        _context.SubmitChanges();
    }
}

Here I am using the IsNew property I created in my Set inheritance modifiers with SQLMetalpost to check if the entity is to be inserted or updated.  The insert code is simple enough, but for the update we have to attach the entity to the DataContext as it has been modified outside of the DataContext’s scope.  I’m at doing this using the Attach method of the Student table, passing true for the asModified parameter to state that the entity has been updated.

In my Silverlight application I have a DataForm which calls this method passing the updated Student entity:

Linq WCF DataForm

At this point inserting data will work, but when I try to update an entity the service method will throw the following error when trying to attach the entity:

An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy.

This occurs because the entity was modified outside of the scope of the DataContext, so Linq to SQL doesn’t know what has changed about the entity and what to update.  To overcome this we can use a Timestamp column.  The Timestamp is a byte array which is used for versioning.  Linq to SQL knows to check this column to see if an object has been updated.  In my database I have changed the Student table so that it has a field called Timestamp, of type timestamp which doesn’t allow NULLs:

Linq WCF Timestamp Field

When adding the new column, the O/R Designer automatically knows this is a timetamp column and sets the Time Stamp and Auto Generated Value properties to true:

Linq WCF Timestamp Properties

 

SQLMetal will also detect a column with the timestamp type and set the necessary attributes.

With this timestamp column set up it will now be possible to successfully update an entity that was changed by the client.

In my example if I try to update the entity twice it will throw the following exception when trying to submit the changes:

Row not found or changed.

This is because the client doesn’t have the entity with the updated timestamp.  Also when adding a new entity the entity at the client won’t have the updated ID identity column so trying to update this will create another entity.  To resolve this I can change my SaveStudent service method to return the updated Student entity:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
public Student SaveStudent(Student student)
{
    using (_context)
    {
        if (student.IsNew)
        {
            _context.Students.InsertOnSubmit(student);
        }
        else
        {
            _context.Students.Attach(student, true);
        }
              
        _context.SubmitChanges();
    }
    return student;
}

In my Silverlight application I then pass the hash code for the object as the userState when calling the asyncronus service method:

1
_proxy.SaveStudentAsync(student, student.GetHashCode());

This user state can then be obtained in the callback EventArgs class using e.UserState.  Using this I get the correct object from my collection, update it and reassign the source for my DataGrid and DataForm:

1
2
3
4
5
6
7
8
9
10
11
12
void _proxy_SaveStudentCompleted(object sender, SaveStudentCompletedEventArgs e)
{
    ObservableCollection students = (ObservableCollection)dgStudents.ItemsSource;
    Student student = students.Where(s => s.GetHashCode() == Convert.ToInt32(e.UserState)).First();
    if (student.ID == 0)
    {
        student.ID = e.Result.ID;
    }
    student.Timestamp = e.Result.Timestamp;
    dgStudents.ItemsSource = students;
    dfStudent.ItemsSource = students;
}

This is all well and good and works as expected but what I really wanted to do was have an UpdateDate column which holds the date of the last update which could be used as a timestamp.  I replaced my current Timestamp column with an UpdateDate column:

Linq WCF UpdateDate Field

The default for the new column is set to getdate() to automatically populate with the current date when creating a new record:

Linq WCF UpdateDate Default

Using the O/R Designer this field can be set to a timestamp by setting the Time Stamp property to True, which will automatically set Auto Generated Value to True.

As I am using SQLMetal I can update the console application I wrote in my Set inheritance modifiers with SQLMetal post to add an IsVersion attribute to the DBML XML as well as the Modifier attribute:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
.... code omitted ....
//Find the column node
if (child.Name.Equals("Column"))
{
    if (child.Attributes["Name"].Value.Equals("ID"))
    {
        //Create the Modifier attribute to add to ID column
        XmlAttribute modifierAttribute = xmlDoc.CreateAttribute("Modifier");
        modifierAttribute.Value = "Override";
        child.Attributes.Append(modifierAttribute);
    }
    else if (child.Attributes["Name"].Value.Equals("UpdateDate"))
    {
        //Create the IsVersion attribute to add to UpdateDate column
        XmlAttribute versionAttribute = xmlDoc.CreateAttribute("IsVersion");
        versionAttribute.Value = "True";
        child.Attributes.Append(versionAttribute);
    }
}
.... code omitted ....

Doing this adds the following values to the Column attribute on the UpdateDate property in the Student entity.  You can see IsVersion=true which tells Linq to SQL this property is the timestamp.

1
[Column(Storage="_UpdateDate", AutoSync=AutoSync.Always, DbType="DateTime NOT NULL", IsDbGenerated=true, IsVersion=true, UpdateCheck=UpdateCheck.Never)]

At this point everything works okay, but the UpdateDate is not refreshed on update.  To fix this add a trigger that sets the date on update:

1
2
3
4
5
6
7
ALTER TRIGGER trg_UpdateDate
ON dbo.Student
FOR UPDATE
AS
    UPDATE      Student
    SET         UpdateDate = getdate()
    WHERE       (ID IN (SELECT ID FROM Inserted))

The UpdateDate is now set for each update and is used by Linq to SQL as the timestamp.

Referenced by: http://www.joe-stevens.com/2009/07/10/linq-to-sql-with-wcf-services/

 
Leave a comment

Posted by on January 19, 2012 in C#, LinQ, WCF

 

Dynamic LINQ

LINQ to SQL is an extension of LINQ that allows developers to write “queries” in .NET to retrieve and manipulate data from a SQL Server database. This gives the ability to access relational database objects as normal .Net objects.

 

LINQ to SQL integrates SQL based schema definitions to the common language runtime (CLR) type system. This provides strong typing, syntax checking, intellisense over SQL objects, while retaining the expressive power of the relational model and the performance of query evaluation directly in the underlying database.

LINQ to SQL enables us to write type-safe queries (static SQL Queries ) against an IQueryable objects (SQL data tables). But some times we need to construct the queries dynamically on the fly. Normally we build dynamic SQL queries by concatenating strings together. The same can be achieved in LINQ to SQL using the Dynamic Expression API which extends the core LINQ API capabilities through dynamic construction of expression trees using the classes in the System.Linq.Expressions namespace.

Normally LINQ queries constructed by using language operators or type-safe lambda extension methods. but the dynamic query library provides us with string based extension methods, where we will pass the expressions in string format.

The Dynamic Expression API is present in the System.Linq.Dynamic namespace. This API provides classes:

– Responsible for string-based querying by using IQueryable extension methods.

– Responsible for parsing strings and producing the equivalent expression trees.

– Responsible for generating new classes which represent the Select columns in the dynamically constructed query.

The Dynamic Expression API provides the following IQueryable Extension Methods as part of System.Linq.Dynamic.DynamicQueryable class for dynamically querying objects.

public static IQueryable Where(this IQueryable source,  string predicate, params object[] values);
public static IQueryable<T> Where<T>(this IQueryable<T> source,   string predicate, params object[] values);
public static IQueryable Select(this IQueryable source,    string selector, params object[] values);
public static IQueryable OrderBy(this IQueryable source,    string ordering, params object[] values);
public static IQueryable<T> OrderBy<T>(this IQueryable<T> source,  string ordering, params object[] values);
public static IQueryable Take(this IQueryable source, int count);
public static IQueryable Skip(this IQueryable source, int count);
public static IQueryable GroupBy(this IQueryable source,    string keySelector, string elementSelector, params object[] values);
public static bool Any(this IQueryable source);
public static int Count(this IQueryable source);

These methods operate on IQueryable and use strings instead of lambda expressions to express predicates, selectors, and orderings.

Constructing Dynamic Queries using IQueryable extension Methods

DataClassesDataContext db=new DataClassesDataContext();
var products = from p in db.Products
where p.Model == “SD1000” && p.onsale==true
select p;

We can construct the above query using the IQueryable extension method Where() like below:

DataClassesDataContext db=new  DataClassesDataContext();
var products = db.Products
.Where(“Model==@0 && onsale==@1″,”SD1000”, true) ;

In the above query Where method is the IQueryable extension method provided in the Dynamic Expression API and takes the input string and produces an equivalent expression tree dynamically to query the products table. Even the string passed to Where method can be constructed dynamically like below

DataClassesDataContext db=new  DataClassesDataContext();
string condition = “Model==\”SD1000\” && onsale==true “;
var products = db.Products.Where(condition );

Like the above example we can use the other IQueryable extension methods to construct the queries dynamically according to our programming needs. Some more examples are:

DataClassesDataContext db=new DataClassesDataContext();
var product = from p in db.Products
where p.Model == “SD1000” && p.onsale == true
orderby p.Name
select new { p.ItemId, p.Name, p.Model1, p.onsale };

That can be converted like below:

DataClassesDataContext db = new DataClassesDataContext();
string condition = “Model==\”SD1000\” && onsale==true “;
var products = db.Products
.Where(condition)
.OrderBy(“Name”)
.Select(” new (ItemId,Name,Model,onsale)”);

In the above code the OrderBy extension method is used for ordering the results by Name column and the Select extension method is used for creating the classes dynamically . Observe that the syntax is using ‘(‘,’)’ instead of ‘{‘,’}’ while constructing new classes dynamically as opposed to static class constuction.

The ParseException Class

Normally when constructing dynamic queries by concatenating strings to form an expression string there is a chance of getting parsing errors. The Dynamic Expression API provides ParseException class which can be used to catch the parsing errors. The Position property gives the character index in the expression string at which the parsing error occurred and the message gives the error message.

try
{
DataClassesDataContext db=new DataClassesDataContext();
string condition = “Model==\”SD1000\” && onsale==true “;
var products = db.Products
.Where(condition)
.OrderBy(“Name”)
.Select(” new (ItemId,Name,Model,onsale)”);
}
catch (ParseException ex)
{
Response.Write(ex.Position + ex.Message);
}

The Take() extension method is used to select a no of rows from the query result and Skip() extension method is used to skip no of rows from the query result.

DataClassesDataContext db=new DataClassesDataContext();
string condition = “Model==\”SD1000\” && onsale==true “;
var product2 = db.Products.Where(condition).OrderBy(“Name”).Select(” new (ItemId,Name,Model,onsale)”).Skip(2).Take(3);

The Count extension method is used to count the no of rows returned as the query result.

DataClassesDataContext db = new DataClassesDataContext();
string condition = “Model==\”SD1000\” && onsale==true “;
var count = db.Products.Where(condition).Count();

The GroupBy extension method is used to group the returned results in to IEnumerable groups.

DataClassesDataContext db = new DataClassesDataContext();
var Models= db.Products.GroupBy(“Model”, “new (Model)”);

 

JOINS in LINQ

There is no direct support for Joins using the extension methods provided in the Dynamic Expression API. However one can use joins indirectly by using inner queries, where the inner queries can be constructed using Dynamic Expression API like below.

DataClassesDataContext db=new DataClassesDataContext();
string condition = “Model==\”SD1000\” && onsale==true “;
var products = from m in (db.Models.Where(“Model1==@0”, “SD1000”))
join p in (db.Products.Where(condition).OrderBy(“Name”))
on m.Model1 equals p.Model
select new { p.ItemId, p.Name, p.Model, m.company, p.Price, p.onsale, p.discount, p.Description, m.features };

While using joins on inner queries, which are constructed using the Dynamic Expression API the Select extension method can’t be used on the dynamic queries.

This tutorial is written by RELIANCE CONSULTING.

 

Referenced by: http://www.beansoftware.com/ASP.NET-Tutorials/Dynamic-LINQ.aspx

 
Leave a comment

Posted by on February 15, 2011 in C#, LinQ

 

Elegant Way of Executing a Stored Procedure using LINQ?

I was working in a project today and I was trying to create a generic way of using a DataContext class to execute a stored procedure.  Of course there’s the way of dragging a stored procedure onto the designer, etc… but I’m not interested in this at the moment.  TBQH, I haven’t even been doing the ORM mapping in this manner lately after using the System.Data.Linq.Mapping namespace to map a POCO (Plain ‘Ole CLR Object) to an actual SQL table (as well as using ColumnAttribute‘s to map the properties to columns.  This is extremely helpful for larger 2.0 projects that were recently converted to 3.5.   Some might argue that this isn’t how LINQ was supposed to be used or that it makes it more complicated.   First of all, I don’t think that there is any right or wrong way to use LINQ.  Secondly, I find it can be a pain to have to remove the table from the designer, reconnect via Server Explorer, refresh the database, re-drag onto designer over-and-over when making changes to tables, views, procedures, etc…  This is also problematic if you don’t have access to a production database from your dev environment.  Most web servers I experience don’t have Visual Studio 2008 installed so it’s not like I can quickly generate a newer set of LINQ-to-SQL classes.  If a table column name changes, I simply refactor the corresponding property in my library and I’m good to go.

[Table(Name = “dbo.FooTable”)]
public class Foo
{
#region Table Columns / Properties

[Column]
public long FooId { get; set; }

[Column]
public string FooName { get; set; }

[Column]
public string FooDescription { get; set; }

#endregion Table Columns / Properties
}

Easy.

public class Database<TDataContext, TEntity>
{
// …
}

Take the previous data access helper class.  Let’s say I want to create just a generic function to execute a stored procedure against my database.  Let’s call it “ExecuteCommand”.

public static void ExecuteCommand(string command, object[] parameters)
{
if (string.IsNullOrEmpty(command))
throw new ArgumentNullException(“command”);

using (var database = new TDataContext())
database.ExecuteCommand(command, parameters);
}

Basic.  The implementation is pretty mindless as well.

Database<MyDataContext, TFoo>.ExecuteCommand(“CommandName”, null);

This sucks when you have parameters because I haven’t found any documentation on how to execute a procedure elegantly using this approach.  It appears that the DataContext.ExecuteCommand approach is best-suited for on-the-fly dynamic SQL…. gross!  I also read (need to find the link again) that some super-sleuthing uncovered that parameter substitution is silently ignored.  (I’ll have to check back on that)

I did find a way to implement using a stored procedure….

string command = string.Format(“exec Command ‘{0}’, {1}”, “string”, 1);
Database<MyDataContext, TFoo>.ExecuteCommand(command, null);

That is disgusting.  Not a huge fan.  What if you have a procedure that has 5, 10, 20 parameters? Ouch.  What if your input parameters contain ‘ ‘s? You would then have to replace all ‘ with a ”.

Any suggestions?  I’m hoping that there is a much more elegant / succinct way of doing this that I haven’t found yet.  I’ve tried to “RTFM” as they say but a couple thousand pages of LINQ reference books and I’ve got nothing.

Here’s a little 2.0 throwback of what I’m trying to accomplish.  Like I said before, there has to bge a more elegant way of doing this with the 3.5 Framework / LINQ / DataContext that doesn’t involve dragging-and-dropping procedures onto the designer.

public static int ExecuteProcedure(List<SqlParameter> parameters, string command)
{
try
{
using (DBManager manager = …)
{
manager.Open();
manager.CreateParameters(parameters.Count);

for (int i = 0; i < parameters.Count; ++i)
manager.AddParameters(i, parameters[i].ParameterName, parameters[i].Value);

return (int) manager.ExecuteScalar(CommandType.StoredProcedure, command);
}
}
catch (SqlException)
{
return 0;
}

}

Help me from the following please!!

StringBuilder query = new StringBuilder(“exec Command “);
query.AppendFormat(“{0},”, foo.FooProperty1);
query.AppendFormat(“‘{0}’,”, foo.FooProperty2.Replace(“‘”, “””));
query.AppendFormat(“‘{0}’,”, foo.FooProperty3.Replace(“‘”, “””));
query.AppendFormat(“‘{0}’,”, foo.FooProperty4.Replace(“‘”, “””));
query.AppendFormat(“‘{0}’,”, foo.FooProperty5.Replace(“‘”, “””));
query.AppendFormat(“‘{0}’,”, foo.FooProperty6.Replace(“‘”, “””));
query.AppendFormat(“‘{0}'”, foo.FooProperty7.Replace(“‘”, “””));

Database<MyDataContext, Foo>.ExecuteCommand(query.ToString(), null);

C#, LINQ

Referenced by: http://www.willasrari.com/blog/elegant-way-of-executing-a-stored-procedure-using-linq/000312.aspx

 
Leave a comment

Posted by on November 22, 2010 in C#, LinQ