How to switch Entity Framework database connected to

If you have a connection to a database through entity framework and you need to switch it to another database (with the exact same structure) you just need to set the Connection.ConnectionString (as seen below).
I had an application where we created a copy of the Master database when setting up a new client.  So using Entity Framework I switched from the master database to the client (depending on what the admin was doing).

using (MasterEntities aEntities = new MasterEntities())
{//Switch db connected to

 

aEntities.Database.Connection.ConnectionString = aEntities.Database.Connection.ConnectionString.Replace("OldDatabaseName", "NewDatabaseName");

      //Some Query

}

[tweetmeme only_single=”false”]
Advertisements

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; }

Entity Framework Model won’t update

So you have a Entity Framework model all laid out and you make major changes to a table (rename fields and add fields).

Now you need to update your model (you don’t want to delete it and recreate it because you have added stored procs, etc).

If you delete your table off the model and try updating your model from database.  There are some scenarios where it won’t see that you need to add back the table you deleted (don’t ask me what they are).  The table won’t show in the designer, but you won’t be able to add it back.

To get around this problem, right click on the edmx file and open with xml editor.(It will ask you if you want to close it, if you have it open, click yes)

Warning: I would recommend you back up your files before editing your xml.

Now search for your table name and delete any reference to it.  Save the xml and close it.  When you open your edmx file you will not having any trouble adding back you changed table.

Entity Framework model won’t open

The entity framework creates a model of your database, a .edmx file.  If you open the file and close it, then you can’t open it again until you close visual studio and reopen it.  That no fun.

Here’s a work around, right click you edmx file and choose Open With…
image
You will get a choice of what you want to open the model with.  If you choose XML Editor you will be able to open just the xml.  (If may warn you that your model is already open and will ask if it can close it, choose yes).
Now it’s open, but you see all the XML.
Right click on the edmx file again and choose Open.

Your model will open back in the familiar view that your used to.