asked on

ADO.Net Entity Framework: Map Store Procedure

Hi guys!

Need some help with mapping a store procedure to this model..

I tried using TPT inheritence but with no luck.

If this is not possible, any other solutions?

(The store procedure I have takes values for all for all three tables.)
tables.JPG

ASKER CERTIFIED SOLUTION

Carl Tawn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ulf-jzl

ASKER

If I use TPT and have one to one relationship it would work, the problem is the one to many relationship i have in the first two tables.

any tips how to change the architecture to solve this?

ulf-jzl

ASKER

it sould be solved by using the Entity Framework Extensions, but I can't figure out how to do that.
Any tips on that?

Carl Tawn

Not something i've used myself i'm afraid. From what i have read it supports pulling back multiple entities from a single procedure, but i haven't seen anything about going the other way.

ulf-jzl

ASKER

ok, do you have any tips how to solve it. leave EF4 for regular ADO.Net to solve this?

Carl Tawn

You can do it easily enough with ADO.Net. You would need to manually create a parameter for each property of your enitites, but other than that its all straightforward stuff. Let su know if you need any help with it.

ulf-jzl

ASKER

something like this then?

Or can I use somehting form the model to make this better?

public static void InsertDeviceTestResults(string deviceId)
        {
            // create and open a connection 
            using (SqlConnection conn = new SqlConnection("Server=(local);DataBase=FBI.Data;Integrated Security=SSPI"))
            {
                // Open connection
                conn.Open();

                // Setup command
                SqlCommand cmd = new SqlCommand("InsertDeviceTestResults", conn) { CommandType = CommandType.StoredProcedure };

                // Add parameter to the stored procedure
                cmd.Parameters.Add(
                    new SqlParameter("@DeviceId", deviceId));

                // Execute the sproc
                cmd.ExecuteReader();
            }
        }

Open in new window

Carl Tawn

Yes, only you'll need to repeat the "cmd.Parameters.Add" line for each property you need to pass to the SP.

Carl Tawn

Except you will use "ExecuteNonQuery()" instead of "ExecuteReader()", if you don't return any data back.