Share via

Calling a Stored Procedure from your WPF app

  • Article

Recently I needed to get data from a database by calling a stored procedure. This seemed like it should be easy to do but it ended taking me 2 days to find the right feature that would work. My knowledge of databases is extremely limited and I ended up winding along a not very helpful path. Hopefully this blog post will help you avoid the same pitfalls.

I started off thinking I needed something from LINQ to SQL so I searched for that and came up with the LINQ to SQL ORM. This seemed like it would be SUPER easy, but for reasons I still don’t understand (I found this forum post which I think might be related) I could never get the return type to auto-generate correctly so I could run the query but I couldn’t get the data out. I have since discovered that the Entity Framework provides similar (seemingly better) functionality. In fact MSDN states “For new applications, however, Microsoft recommends using the the Entity Framework that can accommodate both direct mapping and a higher-level conceptual mapping between the application object model and the relational model of the database.” But for some reason this still doesn’t work for me. If I were starting out again (with a stored procedure that had a defined return type), I would start with the Entity Framework. Try these docs as it took me a while to figure out what I was doing: Entity Data Model Wizard and How to: Import a Stored Procedure (Entity Data Model Tools).

Instead I finally found the ADO .NET docs which had exactly what I was looking for and it did turn out to be super easy. Go to this page and scroll down to the Using Parameters with a SqlCommand and a Stored Procedure section.

In my example, I read the data and write it to an XElement.

Here’s some code to retrieve the data:

//Get all data

connection.Open();

XElement data = new XElement("Data");

try

{

SqlDataReader reader = command.ExecuteReader();

int numCol = reader.FieldCount;

while (reader.Read())

{

int i;

XElement temp = new XElement("Topic");

for (i = 0; i < numCol; i++)

{

temp.Add(new XAttribute(reader.GetName(i),

                           reader[i].ToString()));

}

data.Add(temp);

}

}

catch (Exception e)

{

MessageBox.Show(e.Message);

}

connection.Close();

Then I had to define each column and bind it to the XElement data. See my post on A DataGrid sample using XML data for example code.  I happen to like LINQ a lot so I use XElement all the time, but if you are not a big fan, you could put your data into a DataTable or any of the Binding Sources available for WPF apps.

That’s it. Happy coding,

Margaret

Comments

  • Anonymous
    May 04, 2010
    It really hurts that this is the advice from then "WPF User Education". I feel sorry for the newbie taking your advice. The only thing you need to do to extract the data using Linq to SQL (or EF) is to create a dummy class to match the result of the Stored Procedure Public Class ResultData    Public Property ProductID as integer    Public Property ProductName as String    Public Property Quantity as integer end class Or you can drop the SP in the DataContext design surface and let the ORM create the class for you. http://msdn.microsoft.com/en-us/library/bb386975.aspx

  • Anonymous
    May 05, 2010
    I totally agree that using EF or LINQ to SQL ORM would have been the easiest route. I mentioned at the top that I tried doing that but in my case, the SPROC return value is a bool (0 for success and 1 for failure) and then somehow returns the data which you can only get through a reader. I tried what you suggested above when I was starting out but it doesn't work because of the way the SPROC returns the data. I don't own the DB and I haven't been able to see the SQL that creates the SPROC so I'm not sure what they are doing. If there is a way to make EF handle this case, I'd love to know about it, but I haven't been able to find it.  I was hoping to help people find all the different ways to go about this in case they end up in the situation I was in. So, thanks for adding more tips to this and I welcome other suggestions. Margaret

  • Anonymous
    August 19, 2010
    I was able to use the Function Import technique. This brings the data into a Complex Type which can then be access through a CollectionViewSource. jalpesh.blogspot.com/.../entity-framework-40-bind-stored.html