[C#] 手動設定EF的資料庫連線

在專案加入一個ADO.NET實體資料模型後，

.NET會在Web.config加上以下的連線資訊：

  <connectionStrings>

  <add name="LoLEntities" connectionString="… " providerName="System.Data.EntityClient" />

  </connectionStrings>

如果不想把連線字串寫在Web.config，可以考慮以下做法手動去設定連線。

比較重要的是，Entity的連線 (EntityConnectionStringBuilder) 一定要設定MetaData的位置 (如下圖)

可直接寫成如下的路徑，讓程式自己去尋找即可。(Web.config預設的寫法)

如 "res://*/Import.LoLModel.csdl|res://*/Import.LoLModel.ssdl|res://*/Import.LoLModel.msl"

private LoLEntities _lolEntities;
// Specify the provider name, server and database.
string sProvider = "System.Data.SqlClient";
string sServerName = "[Your Database Server Name/IP]";
string sDatabaseName = "XXX";

// Initialize the connection string builder for the
// underlying provider.
SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();

// Set the properties for the data source.
sqlBuilder.DataSource = sServerName;
sqlBuilder.InitialCatalog = sDatabaseName;
sqlBuilder.IntegratedSecurity = false;
sqlBuilder.PersistSecurityInfo = true;
sqlBuilder.UserID = "[USER ID]";
sqlBuilder.Password = "[USER PASSWORD]";
sqlBuilder.MultipleActiveResultSets = true;

// Initialize the EntityConnectionStringBuilder.
EntityConnectionStringBuilder entityBuilder =new EntityConnectionStringBuilder();

//Set the provider name.
entityBuilder.Provider = sProvider;

// Set the provider-specific connection string.
entityBuilder.ProviderConnectionString = sqlBuilder.ToString();

String sMetadata = string.Format(@"res://*/Import.LoLModel.csdl|res://*/Import.LoLModel.ssdl|res://*/Import.LoLModel.msl");

entityBuilder.Metadata = sMetadata;

//列印連線字串
 Console.WriteLine(entityBuilder.ToString());


//**************************************************
//測試連線
//**************************************************
using (EntityConnection conn =
    new EntityConnection(entityBuilder.ToString()))
{
    try
    {
        conn.Open();
        Console.WriteLine("測試連線");
        conn.Close();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}


// Create an EntityConnection.
_lolEntities = new LoLEntities(new EntityConnection(entityBuilder.ToString()) );