Advice on automating table/column re-namings in SQL Server and updating Entity Framework mappings in Visual Studio

Hey everyone; I want to build a tool that takes two dictionary objects,

Dictionary<string, string> tableNames, Dictionary<string, string> columnNames

where the key is an old table name string that should be renamed, and the value is the new string for the table name. One diction开发者_如何学Pythonary has table names that need to be updated and the other has column names to be updated. The dictionary containing column names is all that can be used for updating table column names; an associated table the columns are in will be unknown or won't matter since I essentially will be changing the name of a column if ANY table happens to have the particular column name string.

I've been able to do the above steps okay with the following code:

Change table names.

using (SqlCommand cmd = new SqlCommand())
{
    foreach (KeyValuePair<string, string> keyValuePair in tableNames)
    {
        cmd.Connection = connection;
        cmd.Parameters.Clear();
        cmd.CommandText = "sp_RENAME";
        cmd.CommandType = System.Data.CommandType.StoredProcedure;

        cmd.Parameters.Add(new SqlParameter("@objname", keyValuePair.Key));
        cmd.Parameters.Add(new SqlParameter("@newname", keyValuePair.Value));

        try 
        {
            cmd.ExecuteNonQuery(); 
        }
        catch (Exception e) 
        {
            var v = e.Message; 
        }
    }
}

Change column names.

DataTable dt = connection.GetSchema("Tables");

foreach (DataRow dataRow in dt.Rows)
{
    string tableName = dataRow.Field<string>("TABLE_NAME");

    foreach (KeyValuePair<string, string> keyValuePair in columnNames)
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.Connection = connection;
            cmd.Parameters.Clear();
            cmd.CommandText = "sp_RENAME";
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@objname", tableName + "." + keyValuePair.Key));
            cmd.Parameters.Add(new SqlParameter("@newname", keyValuePair.Value));
            cmd.Parameters.Add(new SqlParameter("@objtype", "COLUMN"));

            try 
            {
                cmd.ExecuteNonQuery(); 
            }
            catch (Exception e) 
            { 
                var v = e.Message; 
            }
        }
    }
}

So, that's cool. However, I have about 80+ projects in a solution where the Entity Framework is used, and I have lots of edmx and edmx.Designer.cs files. Whenever a table changes, I go to an edmx file that has the table, delete the table, right-click an empty space, and re-add the table by choosing "Update Model from Database...".

I would like advice on how I can automate these steps so that I can iterate over the 80+ projects and update all the table mappings, either with brute force (delete and re-add all tables in edmx file), or perhaps I can store a list of tables that were changed in my code and only update those tables.

Also, if there is anything I can improve in the code provided above, please let me know. I only have some experience with programming for databases, so advice associated with that is appreciated as well. Thank you!

---

Why do you rename tables or columns which are used by 80 projects??? I can't imagine any reason which would force me to do such silly thing.

Anyway if you want to change just table names and column names you can open all EDMX files as XMLs and modify their SSDL and MSL parts. SSDL part contains description of all tables (+ their columns) and relations used in mapping and MSL part describes the mapping between tables and classes. This will not change names of classes or properties. If you want to change them as well you must first change CSDL (classes description) as well. Then it will consists of a lot of work because renaming class or property in EDMX would require modifying all .cs/.vb/.aspx/etc. files defining or using that. It is like triggering refactoring from your code (it should be possible).