Calling a Stored Procedure from your WPF app

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