locked
LINQtoSQL DBML file changable connectionstring RRS feed

  • Question

  • User-1329022387 posted

    I am new to LINQ to SQL. I added a LINQ to SQL Class data item and connected via the Server Explorer. 

    The issue I am having is that the code I am writing connects to one database server for testing and another for production. Normally, I just update the ConnectionString in the web.config file to the correct connection. However, in the class' .dbml file, the server info is hardcoded in. 

    Is there a way to make this more changeable?

    <Connection Mode="WebSettings" ConnectionString="IPAddress;Initial Catalog=DatabaseName;Persist Security Info=True;User ID=username" SettingsObjectName="System.Configuration.ConfigurationManager.ConnectionStrings" SettingsPropertyName="ConnectionStringName" Provider="System.Data.SqlClient" />
    Thursday, January 7, 2021 11:25 PM

All replies

  • User-943250815 posted

    DBML is for development only, it is used to create DataContext class that should be published.
    If you open DataContext Class you will see a Default Initializer pointing to Connection String name saved on web.config file (when you created Linq To SQL), there is also another Class Initializer where you can call using your own Connection String, in this case you can hard code it or load from web.config.

    To get a ConnectionString from web.config you can use:

    string myConStr = ConfigurationManager.ConnectionStrings["myConnectionName"].ConnectionString;
    

    or

    Dim MyConStr as string = ConfigurationManager.ConnectionStrings("myConnectionName").ConnectionString

    Replace "myConnectionName" by name you had set in web.config
    And yes, you can set as many Connection Strings as you want on web.config file

    Friday, January 8, 2021 12:55 AM
  • User-1329022387 posted

    Thanks for the quick response. Hmmmm.. well shoot looks like I made an assumption. When I test my DataContext class with the original forming database server it effectively populates the data. When I change the ConnectionString to point to the next database server, none of the data populates. Any ideas why?

    Friday, January 8, 2021 1:02 AM
  • User-821857111 posted

    LINQ to SQL was deprecated in favour of Entity Framework many years ago. I wouldn't recommend wasting any time learning it.

    https://www.learnentityframeworkcore.com/

    Friday, January 8, 2021 7:47 AM
  • User-943250815 posted

    Considering both DB have same structure (tables, fields, types, permissions) it should work.
    Other than you have an Exception that tells you what´s incorrect.

    As Mikesdotnetting, perhaps better you turn to Entity Framework, Linq To SQL is restricted to SQL Server, with Entity Framework you can work not only with SQL Server, with many other different Database Servers.

    But if you are limited to or preffer go ahead with Linq To SQL, give us some code so we can try help you

    Friday, January 8, 2021 12:13 PM
  • User-1329022387 posted

    EntityFramework unfortunately is not an option for this project. 

    I am not quite sure what code to show you, but here goes. 

    My DataContext where the table is mapped to (Auto-Generated):

    [global::System.Data.Linq.Mapping.DatabaseAttribute(Name="SiteTableName")]
    	public partial class Max_Cell_Qty_vs_PartDataContext : System.Data.Linq.DataContext
    	{
    		
    		private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();
    		
        #region Extensibility Method Definitions
        partial void OnCreated();
        #endregion
    		
    		public Max_Cell_Qty_vs_PartDataContext() : 
    				base(global::System.Configuration.ConfigurationManager.ConnectionStrings["SiteConnectionString"].ConnectionString, mappingSource)
    		{
    			OnCreated();
    		}

    My web.config ConnectionStrings syntax:

    <add name="TestConnectionString" connectionString="Data Source=11.11.11.111;Initial Catalog=TestDBName;Persist Security Info=True;User ID=TestUser;Password=TestPassword"
          providerName="System.Data.SqlClient" />
    
    <add name="SiteConnectionString" connectionString="Data Source=10.10.10.110;Initial Catalog=SiteDBName;User ID=SiteUser;Password=SitePassword"
          providerName="System.Data.SqlClient" />

    Friday, January 8, 2021 6:58 PM
  • User-943250815 posted

    As you can see Default Connection String in DataContext is "SiteConnectionString".
    Here a sample to demonstrate how use DataContext with Default Connection String as a Defined Connection String. Assuming both connection strings are fine.
    MyTest.aspx (webform)

    <%@ Page Language="C#" AutoEventWireup="true" CodeFile="MyTest.aspx.cs" Inherits="MyTest" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
      <title></title>
    </head>
    <body>
      <form id="form1" runat="server">
        <div>
          <asp:Button ID="Button1" runat="server" Text="Test DBContext & Connection String" OnClick="Button1_Click" />
          <h3>Using Default connection string</h3>
          <asp:GridView ID="GridView1" runat="server"></asp:GridView>
          <h3>Using Defined connection string</h3>
          <asp:GridView ID="GridView2" runat="server"></asp:GridView>
        </div>
      </form>
    </body>
    </html>
    

    MyTest.cs (code-behind)

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    
    public partial class MyTest : System.Web.UI.Page
    {
      protected void Page_Load(object sender, EventArgs e)
      {
    
      }
    
      protected void Button1_Click(object sender, EventArgs e)
      {
        // Using Datacontext with Default Connection String
        Max_Cell_Qty_vs_PartDataContext MyDB1 = new Max_Cell_Qty_vs_PartDataContext();
        var MyList1 = (from p in MyDB1.HereUseTableName select p).Take(5).ToList(); // Replace "HereUseTableName" by table name, Intellisense show you tables available in context
        GridView1.DataSource = MyList1;
        GridView1.DataBind();
    
        // Using DataContext with Defined Connection String
        string myConStr = System.Configuration.ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString; // Get Connection String by name from web.config
        Max_Cell_Qty_vs_PartDataContext MyDB2 = new Max_Cell_Qty_vs_PartDataContext(myConStr); // Using TestConnectionString
        var MyList2 = (from p in MyDB2.HereUseTableName select p).Take(5).ToList(); // Replace "HereUseTableName" by table name, Intellisense show you tables available in context
        GridView2.DataSource = MyList2;
        GridView2.DataBind();   
      }
    }

    With this sample you can see up to 5 records coming from each Server\Database.
    As I understand your main issue is how to deal with Connection String between Development & Production.
    A simple way (but not too academic) would have both Connection Strings on web.config and just leave one commented and another active. This way you can even maintain same name for both.
    With some more work, you can check if is in Development Environment before intialize Datacontext

    Max_Cell_Qty_vs_PartDataContext MyDB;
    if (System.Web.Hosting.HostingEnvironment.IsDevelopmentEnvironment == true)
     {
      MyDB = new Max_Cell_Qty_vs_PartDataContext();
     }
    else
     {
      string myConStr = System.Configuration.ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString;
      MyDB = new Max_Cell_Qty_vs_PartDataContext(myConStr); 
      }

    Friday, January 8, 2021 8:08 PM
  • User-1329022387 posted

    Ooo I like your check for DevelopmentEnvironment. I tried the un-academic way of just commenting out the unused connectionstring and named them the same thing. Each string works perfectly IF it was the connection that was originally created via LINQtoSQL. However, if I comment out the one that originally created the DataContext it doesn't work meaning that there are no errors, it compiles and deploys great but the data doesn't populate EVEN though that same connection string did populate the data when it was the connectionstring creating the datacontext..... ‍

    Friday, January 8, 2021 10:23 PM
  • User-943250815 posted

    So, did you proof that you can play multiple ways to deal with Connection String.
    About Production Server, as I told, usually on error an Exception is raised, so place your DataBaseContext.SubmitChanges inside a Try/Catch block  and write Exception Message to a label on page.
    You can also Enable Debug on web.config check "Configure debugging in the web.config file" topic here https://docs.microsoft.com/en-us/visualstudio/debugger/how-to-enable-debugging-for-aspnet-applications?view=vs-2019 then later remove Debug set

    I´m assuming you have some data on both DBs and with my sample you could see 2 grids with data from both DBs.
    Remember, both DB should have same structure, a field name, type or even a PK, make difference when Insert/Update
    By the way, make both Connection Strings similar you don't want "Persist Security Info=True"

    Just in case, I have a production site running with Linq To SQL, without DBML file published

    Friday, January 8, 2021 11:46 PM