[EF] Enable Entity Framework to support multiple databases

Enable Entity Framework to support multiple databases

Luo chaohui (http://kesalin.cnblogs.com /)

This article follows the "signature-non-commercial use-consistency" creation public agreement  

EF supports SQL Server very well, both code first, model first, and database first, but it is not so friendly to non-Microsoft databases, currently, the only guarantee is that database first supports most databases well. So here, the idea of enabling Entity Framework to support multi-database implementation is based on database first. First, create a database table in each database (here there are a lot of exquisite local governments, the field types must be consistent, you can use the power designer tool to simplify manual work ), create a conceptual model based on a database, store the model and ing relationships, copy and modify the generated storage model file, and match the file with other databases, to obtain support for multiple databases.

This example demonstrates the support for SQL Server 2008 and MySQL 5.5 library, using MySQL connector net 6.3.5. See the previous article to install the relevant software. The procedure is as follows:

1. Create the school database and student table in SQL Server 2008 and MySQL 5.5 respectively (lower case is recommended, MySQL uses lower case by default). The student table only contains three fields: ID (Primary Key ), name and age. Note that the data types of the two tables must be identical!

SQL Server 2008 table:

MySQL table:

 

2. insert different test data into the two tables respectively. You can alsoCodeTo simplify the process, you can directly use the database management tool to insert test data.

3. Create a C # ConsoleProgramEfmutilpledatabase:

 

4. Right-click the project name and add the new item: studentmodel. edmx of the ADO. NET Entity Data Model type to it:

Select generate from database:

Create a new connection. First, use the MySQL database, set the connection to MySQL, and select school as the database table. Then, the wizard will automatically generate the conceptual model, storage model, and ing relationship for us, connection string:

Select the database table to be used:

 

5. Now the Wizard is complete. Let's take a look at the files generated by the wizard for us:

App. config: database connection configuration;

Studentmodel. edmx: the conceptual model, storage model, and ing relationship are automatically generated in this file;

Studentmodel. Designer. CS: automatically generated code. Through these automatically generated data object classes, we can directly operate the database.

 

6. To make it easier to support multiple databases later, we will change the default connection string name to schoolentitiesmysql in the app. config file.

 <?  XML version = "1.0" encoding = "UTF-8"  ?> 
 <  Configuration  > 
 <  Connectionstrings  > 
 <  Add  Name  ="Schoolentitiesmysql" Connectionstring  = "Metadata = Res: // */studentmodel. CSDL | res: // */studentmodel. SSDL | res: // */studentmodel. MSL; provider = MySQL. data. mysqlclient; provider connection string = & quot; server = localhost; user id = root; Password = yourpwd; persist Security info = true; database = School & quot ;"  Providername  = "System. Data. entityclient"   /> 
 </  Connectionstrings  > 
 </  Configuration  > 

7. Let's write the code for testing the connection to MySQL. Add reference: system. configuration to the project, and modify program. CS:

 Using System;
 Using System. Collections. Generic;
 Using System. LINQ;
 Using System. text;
 Using System. Data;
 Using System. Data. objects;
 Using System. configuration;

 Namespace Efmultipledatabase
{
 Class Program
{
 Private   Static   Void Processdatabase ( String Connectionstringname)
{
 String Connectionstring = configurationmanager. connectionstrings [connectionstringname]. connectionstring;
 Using ( VaR Querycontext = New Schoolentities (connectionstring ))
{
Console. writeline ( "  > All STUDENT:  " );

Querycontext. Connection. open ();

 //  Query
  //
   Using ( VaR Transaction = querycontext. Connection. begintransaction ())
{
 VaR People = From P In Querycontext. Student Orderby P. Age Select P;
 Foreach ( VaR P In People)
{
Console. writeline ( "  {0}, age: {1}  " , P. Name, P. Age );
}

Transaction. Commit ();
}

Querycontext. Dispose ();
}
}

 Static   Void Main ( String [] ARGs)
{
Console. writeline ( "  ========= MySQL =====  " );
Processdatabase ( "  Schoolentitiesmysql " );

Console. Readline ();
}
}
}

In the above Code, we read the app. the connection string configuration in config determines which database to connect to. This design allows us to add connection configurations to other databases to support multiple databases.

8. Let's make the program support SQL Server 2008. First, you must manually create a storage model file and create an XML file named studentmodel. sqlserver. SSDL in the project. Right-click studentmodel. edmx and select open with XML (text) Editor! -- SSDL content --> between <edmx: storagemodels>... </Edmx: storagemodels>: copy the content to the newly created studentmodel. sqlserver. SSDL file. The studentmodel. sqlserver. SSDL content should be as follows:

 <?  XML version = "1.0" encoding = "UTF-8"  ?> 
 < Schema  Namespace  = "Schoolmodel. Store"  Alias  = "Self"  Provider  = "Mysql. Data. mysqlclient"  Providermanifesttoken  = "5.1"  Xmlns: Store  = "Http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator"  Xmlns  = "Http://schemas.microsoft.com/ado/2009/02/edm/ssdl"  > 
<  Entitycontainer  Name  = "Schoolmodelstorecontainer"  > 
 <  Entityset  Name  = "Student"  Entitytype  = "Schoolmodel. Store. Student"  Store: Type  = "Tables"  Schema  = "School"   /> 
 </  Entitycontainer  > 
 <  Entitytype  Name  = "Student"  > 
 <  Key  > 
 <  Propertyref  Name  = "ID"   /> 
 </  Key  > 
 <  Property  Name  = "ID"  Type  = "Int"  Nullable  = "False"  Storegeneratedpattern  = "Identity"   /> 
 <  Property Name  = "Name"  Type  = "Varchar"  Maxlength  = "45"   /> 
 <  Property  Name  = "Age"  Type  = "Umediumint"   /> 
 </  Entitytype > 
 </  Schema  > 

9. Next, modify the studentmodel. sqlserver. SSDL storage model file to match the SQL Server 2008 database table. First, we need to change provider and providermanifesttoken:

 
Provider = "system. Data. sqlclient" providermanifesttoken = "2008"
 

This indicates that the storage model is based on the SQL Server 2008 database. Then, change the type of each field to the data type supported by SQL Server 2008. (the data type used by MySQL is certainly not the same as that used by SQL Server 2008 ), put the storage model in the output directory and modify the copy to output directory attribute of the file to copy always .. The modified content should be as follows:

 <?  XML version = "1.0" encoding = "UTF-8"  ?> 
 < Schema  Namespace  = "Schoolmodel. Store"  Alias  = "Self"  Provider  = "System. Data. sqlclient"  Providermanifesttoken  = "2008"  Xmlns: Store  = "Http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator"  Xmlns  = "Http://schemas.microsoft.com/ado/2009/02/edm/ssdl"  > 
<  Entitycontainer  Name  = "Schoolmodelstorecontainer"  > 
 <  Entityset  Name  = "Student"  Entitytype  = "Schoolmodel. Store. Student"  Store: Type  = "Tables"  Schema  = "DBO"   /> 
 </  Entitycontainer  > 
 <  Entitytype  Name  = "Student"  > 
 <  Key  > 
 <  Propertyref  Name  = "ID"   /> 
 </  Key  > 
 <  Property  Name  = "ID"  Type  = "Int"  Nullable  = "False"  /> 
 <  Property  Name  = "Name" Type  = "Nchar"  Maxlength  = "45"   /> 
 <  Property  Name  = "Age"  Type  = "Int"   /> 
 </  Entitytype  > 
 </ Schema  > 

10. Now we have modified the storage model. Next we will add a connection string to the SQL Server 2008 database in APP. config. The modified app. config is as follows:

 <?  XML version = "1.0" encoding = "UTF-8"  ?> 
 <  Configuration  > 
 <  Connectionstrings  > 
 <  Add  Name = "Schoolentitiesmysql"  Connectionstring  = "Metadata = Res: // */studentmodel. CSDL | res: // */studentmodel. SSDL | res: // */studentmodel. MSL; provider = MySQL. data. mysqlclient; provider connection string = & quot; server = localhost; user id = root; Password = yourpwd; persist Security info = true; database = School & quot ;"  Providername  = "System. Data. entityclient"   /> 
 <  Add  Name  ="Schoolentitiessqlserver" Connectionstring  = "Metadata = Res: // */studentmodel. CSDL | studentmodel. sqlserver. SSDL | res: // */studentmodel. MSL; provider = system. data. sqlclient; provider connection string = & quot; Data Source = .; initial catalog = school; persist Security info = true; user id = sa; Password = 

 
Yourpwd
 

 
; Multipleactiveresultsets = true; APP = entityframework & quot ;"Providername= "System. Data. entityclient" />
</Connectionstrings>
</Configuration>
 

11. Now that the configuration is complete, we only need to call the following statement in main () to test the connection to the SQL Server 2008 database:

 
Static VoidMain (String[] ARGs)
{
Console. writeline ("========= MySQL =====");
Processdatabase ("Schoolentitiesmysql");

Console. writeline ("======= SQL Server 2008 ====");
Processdatabase ("Schoolentitiessqlserver");

Console. Readline ();
}
 

The result is as follows:

 

Summary:

The above process is to generate a consistent database table, then generate a conceptual model through a database, store the model and ing relationship, and then copy and modify the storage model to match it with other databases, to support multiple databases.