In my latest contract, the DBA has opted to use SQL Server stored procedures for all data access. Preventing direct table access may ensure maximum security and performance - but it sure reduces the fun for the front-end guy (me).
I’d been doing my screen prototypes with Entity Framework 1 and getting pretty good at manipulating the mapped objects. Arrrgh.
Just for fun, I decided to test out EF 4 against the first stored procedure I received – Inserting a contact. It’s not difficult but you want to watch for a couple of gotchas.
When you choose the database objects in EF, you can add stored procedures. No surprise about that. What’s strange is that you’re just making the designer aware of the SPs, not actually doing anything with them. (This is unlike tables where the OR mapping is automatic.)
For stored procedures, you need to go into the Model Browser, drill down to the Function Imports area, right-click and select Add Function Import.
I got messed up when using the Function Import because I didn’t read the dialogue box text carefully enough. My stored procedure adds a contact to the database and returns the ContactID and a returnCode. So, I selected Scalars and an Int32 type as the return. Oops. The label says “Returns a Collection Of”. After some trial and error, I learned that the correct choice was None.
BTW, the preceding dialogue box would be better if the combo box were at the top so you choose the stored procedure name and then the Function Import Name could default to the name of the SP you just selected.
With the mapping of the stored procedure in place, the ASP.NET 4 code is straightforward to call the InsertContact stored procedure. The ContactID comes back inside the ObjectParameter object as its Value property:
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles Me.Load
Dim ent As New WebApplication1.DBEntities1
Dim objparam As New System.Data.Objects.ObjectParameter _
("contactID", DBNull.Value)
Dim status As Integer = 0
status = ent.InsertContact(objparam, "Billy Bob", _
"Thornton", "Goof", "These are notes")
Label1.Text = "Status: " & status.ToString() & " , ContactID: " _
& objparam.Value.ToString()
End Sub
Maybe tomorrow night the DBA will have progressed far enough so I’ll have an SP that returns a bunch of data to EF 4.
Ken