Entity Framework call stored proc with code

If you need to call a stored procedure from Entity framework and you can’t add it to the designer (like if it doesn’t return an entity)  you can do with just code.
This way you can use the same connection as you model.

Here is some example code to use.

 using (cctEntities ccte = new cctEntities ())
                {
                    DbConnection connection = ((EntityConnection)ccte.Connection).StoreConnection;
                    bool opening = (connection.State == ConnectionState.Closed);
                    if (opening)
                    {
                        connection.Open();
                    }

                    DbCommand cmd = connection.CreateCommand();

                    cmd.CommandText = "proc_SelectMarketInfo";
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@MarketID", MarketID));

                    try
                    {
                        using (DbDataReader dReader = cmd.ExecuteReader())
                        {
                            //Read sproc results
                            while (dReader.Read())
                            {
                                UserRole U = new UserRole();
                                U.userFirst = String.IsNullOrEmpty(dReader["FirstName"].ToString()) ? string.Empty : dReader["FirstName"].ToString();
                                U.userLast = String.IsNullOrEmpty(dReader["LastName"].ToString()) ? string.Empty : dReader["LastName"].ToString();
                                U.username = String.IsNullOrEmpty(dReader["Username"].ToString()) ? string.Empty : dReader["Username"].ToString();
                                U.userRole = String.IsNullOrEmpty(dReader["Role"].ToString()) ? string.Empty : dReader["Role"].ToString();
                                markets.Add(U);
                            }
                        }
                    }
                    finally
                    {
                        if (opening && connection.State == ConnectionState.Open)
                        {
                            connection.Close();

                        }
                    }
                }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Advertisements

Got something to add?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s