Friday, August 31, 2012

Entity Framework: Executing SQL Strings

I've griped about this before, not sure why everything has to be so complicated with EF at times, but holy hell executing just a simple SQL String "SELECT * FROM Table" is a major feat that takes more than just 1 line of code to do. Anyhow, if you are using EF 4.0 and not EF 4.1 it is slightly more difficult to execute sql strings right from EF. EF 4.1 has some nice methods that make a bit of a difference, but not very much, I still find myself needing the following code below.

I just want to execute my sql string and get a DataTable back for pete's sake? Is that really too much to ask for? Apparently so...

So first thing's first, we need to get a connection string. You would think this was easy... well it isn't unfortunately.

Getting the Connection String in EF 4.0
//You need these libraries
using System.Data.Objects;
using System.Data.EntityClient;

public static string GetConnectionString(this ObjectContext context)
{
 return ((EntityConnection)context.Connection).StoreConnection.ConnectionString;
}

If you are using EF 4.1 check out this link here to see how to do it in 4.1; I haven't tried it so I can't vouch for it. EF 4.1 offers a new property called "Database" which seems to help with some of this mundane code.

Getting a DataTable in EF 4.0
//You need these libraries
using System.Data;
using System.Data.Objects;
using System.Data.SqlClient;

public static DataTable ExecuteDataTable(this ObjectContext context, string sqlString)
{
 DataTable dt = new DataTable("Table1"); //The Table name MUST be set in order to pass this over WCF

 using (IDbConnection con = new SqlConnection(context.GetConnectionString()))
 {
  using (IDbCommand cmd = new SqlCommand(sqlString, (SqlConnection)con))
  {
   cmd.CommandTimeout = 0;

   using (SqlDataAdapter adapter = new SqlDataAdapter())
   {
    adapter.SelectCommand = (SqlCommand)cmd;
    adapter.Fill(dt);
   }
  }
 }

 return dt;
}

Please be aware that if you are going to return this DataTable over the pipe from a WCF service to a client application, you MUST name your DataTable or you will get a bogus WCF error that will only serve as unnecessary confusion (it will be going into my hall of shame soon). At all... Look here for a great question and answer about this.

Getting a DataSet in EF 4.0
//You need these libraries
using System.Data;
using System.Data.Objects;
using System.Data.SqlClient;

public static DataSet ExecuteDataSet(this ObjectContext context, string sqlString)
{
 DataSet ds = new DataSet();

 using (IDbConnection con = new SqlConnection(context.GetConnectionString()))
 {
  using (IDbCommand cmd = new SqlCommand(sqlString, (SqlConnection)con))
  {
   cmd.CommandTimeout = 0;

   using (SqlDataAdapter adapter = new SqlDataAdapter())
   {
    adapter.SelectCommand = (SqlCommand)cmd;
    adapter.Fill(ds);
   }
  }
 }

 return ds;
}  

I haven't tested the DataSet code yet, but it should work since it is exactly the same as the DataTable code except that it is returning a DataSet instead of a DataTable. I tried making a generic version of this method, but it didn't work. If you are going to return the DataSet across the pipe via WCF, you need to change it into a List<DataTable> so that it will be serializable. Just remember to name each table to avoid WCF bogus problems.

Sources
http://stackoverflow.com/questions/6587143/get-the-connection-string-for-entity-framework-using-the-poco-template/
http://stackoverflow.com/questions/10254272/execute-stored-procedure-in-entity-framework-return-listdatatable-or-dataset
http://stackoverflow.com/questions/915329/is-it-possible-to-run-native-sql-with-entity-framework
http://stackoverflow.com/questions/12702/net-returning-datatables-in-wcf

Thursday, August 30, 2012

Entity Framework: Basic CRUD Operations

I like Entity Framework (EF), I think it is a little too rigid sometimes and it would be very nice if there was a very straight forward way to get Plain Old CLR Objects (POCO) out of EF Entities. One can dream, can he not?! Going on a tangent, there are ways to get POCO out of EF Entities, but I haven't gotten them to work yet, I have looked through many tutorials and wasted a full work day trying to find a way around it. That being said, beware if you plan on using EF with WCF, some of your more complicated EF Entities will not go over the pipe in WCF. The reason simply being that there are un-serializable properties in those Entities. Hence the need for POCO.

I would really have liked EF to just have very simplistic syntax for the most basic of operations, those being CRUD operations. Since that is not the case, I am going to just put it here on my blog because I don't think I should have to ever look this stuff up again (I've done this too many times already).

I am going to use everyone's favorite used to death example, yes you guessed it - the Product object.

Read Operations
//Your basic Getter - the "R" of CRUD
public static Product GetProduct(int productID)
{
 using (DBEntities context = new DBEntities())
 {
  //Using Linq or Lamba get your product.
  return context.ProductEFs.Where(p => p.ProductID == productID).FirstOrDefault();
 }
}

//Your basic Get All - also the "R" of CRUD
public static List<Product> GetProducts()
{
 using (DBEntities context = new DBEntities())
 {
  return context.ProductEFs.ToList();
 }
}

Write Operations
//Your rudimentary Crud Operations, this is only the "C", "U" & "D" of CRUD
public enum CrudOperations
{
 Insert = 1, //Create
 Update = 2, //Update
 Delete = 3  //Delete
}

//This is the remaining "C", "U" & "D" of CRUD. 
//This method takes a product object and a CRUD Operation
public void Operation(Product product, CrudOperations op)
{
 try
 {
  using (DBEntities context = new DBEntities())
  {
   switch (op)
   {
    case CrudOperations.Insert:
     context.AddToProductEFs(product); //The most straight forward operation, add to the collection
     break;
    case CrudOperations.Update:
     context.ProductEFs.Attach(product); //Must attach first and change the state to modified
     context.ObjectStateManager.ChangeObjectState(product, EntityState.Modified);
     break;
    case CrudOperations.Delete:
     context.ProductEFs.Attach(product); //Must attach first
     context.DeleteObject(product); //Then you can perform a delete
     break;
   }

   //All of these operations have this in common
   context.SaveChanges();
  }
 }
 catch (Exception ex)
 { 
  //Log this error if you choose to.
  
  //Return something less frightening - PO = Product Operation
  throw new Exception("PO:" + op.ToString() + " - " + ex.Message);
 }
}

Not terribly complicated once you have the code in front of you, but it isn't obvious when starting from scratch. I am debating on making extension methods for all of this so it will simply read: context.Insert(), context.Update(), context.Delete() etc..

Wednesday, August 29, 2012

WCF: Why isn't my object showing up on the client side?

I had a minor head-palm moment a few minutes ago.

Problem
I declared an object in my WCF service with the correct DataContract and DataMember attributes, but my
service reference still didn't acknowledge the object, even after I did an update/refresh on the reference.

Solution
Then it hit me. I didn't use the object in the service itself. Meaning there is no mention or use of said object in the actual Service or Service Contract.

Conclusion
So the answer to "Why isn't my object showing up on the client side?" is: In order to be able to use a WCF provided object, it must be used in the Service/Service Contract (Interface). This is pretty basic, but sometimes you just forget. Less of an obscure problem and more of a gotcha.

Tuesday, August 14, 2012

New cannot be used on a type parameter that does not have a new constraint

This is not such an obscure problem as it is just not straight forward to solve. Simply put you cannot instantiate a new instance of a generic object without explicitly saying that the method/class you are trying to do this inside of must allow for it.

Let's Review That Error One More Time
New cannot be used on a type parameter that does not have a new constraint.

Code that will Cause this Error
Private Sub GenericMethod(Of T)(parameter1 As Integer)
 Dim obj As New T() 'This line won't compile because it is not explicitly allowed
 
 Dim strExample As String = obj.GetType().Name

 'Do more stuff...
End Sub
So What This Really Means Is...
The new keyword cannot be used to instantiate an object of type T if the method/class that it is being used in side of did not explicitly define it as a constraint of the method/class.

Code That Will Compile Happily
'By explicitly stating that this method's generic T can be instantiated solves the problem
Private Sub GenericMethod(Of T As {New})(parameter1 As Integer)
 Dim obj As New T()
 
 Dim strExample As String = obj.GetType().Name

 'Do more stuff...
End Sub

Resources
Constraints on Type Parameters VB.Net
Constraints on Type Parameters C#
https://www.google.com/search?q=constraints+on+type+parameters+vb.net
https://www.google.com/search?q=constraints+on+type+parameters+C#

Tuesday, August 7, 2012

How to get Filename and File Extension from a Column with tSQL

I have written this query like 100 times, but this time I am going to document it so I don't lose it again.

This query specifically assumes that you have a single column that contains a file name with or without an extension, but not the full path. I'm sure modifying this query to accommodate any needs shouldn't be that difficult. I will be the first to say I am not very proud of how inefficient this query is, but the point is it works. If you are going for performance, then you might want to use a while loop or a cursor to iterate through each row while storing results in a variable.

I was using this query to move data from one table to another for a one time fix in my database.

SELECT 
 OriginalFileName,
 FileName = LEFT(OriginalFileName, LEN(OriginalFileName) - CHARINDEX('.', REVERSE(OriginalFileName), 0)),
 Extension = REVERSE(LEFT(REVERSE(OriginalFileName), CHARINDEX('.', REVERSE(OriginalFileName), 0) - 1))
FROM YourTableHere

FileName = LEFT(OriginalFileName, LEN(OriginalFileName) - CHARINDEX('.', REVERSE(OriginalFileName), 0))
This line is grabbing everything to the left of the last period in the string. I am utilizing the REVERSE() function in order to find the location of the last period (.) in the file name just in case the filename looks like this: "books_05.01.12.pdf " which can get confusing very quickly. Think of "CHARINDEX('.', REVERSE(OriginalFileName), 0))" as a LastIndexOf() function.

Extension = REVERSE(LEFT(REVERSE(OriginalFileName), CHARINDEX('.', REVERSE(OriginalFileName), 0) - 1))
This line is grabbing everything to the left of the last period in the reverse of the original string and finally it reverses the whole result to make it face the right way.

Again this methodology is very inefficient because I am repeating the same method calls several times on the exact same column. This could be easily fixed by implementing a loop or a cursor and only calling the repeated function calls once and storing the result in a variable.

This query will not work for SQL Server 2000.