Last post Jan 08, 2021 11:46 PM by jzero
Jan 07, 2021 11:25 PM|CW Code|LINK
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" />
Jan 08, 2021 12:55 AM|jzero|LINK
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;
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
Jan 08, 2021 01:02 AM|CW Code|LINK
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?
Jan 08, 2021 07:47 AM|Mikesdotnetting|LINK
LINQ to SQL was deprecated in favour of Entity Framework many years ago. I wouldn't recommend wasting any time learning it.
Jan 08, 2021 12:13 PM|jzero|LINK
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
Jan 08, 2021 06:58 PM|CW Code|LINK
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):
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();
public Max_Cell_Qty_vs_PartDataContext() :
My web.config ConnectionStrings syntax:
<add name="TestConnectionString" connectionString="Data Source=18.104.22.168;Initial Catalog=TestDBName;Persist Security Info=True;User ID=TestUser;Password=TestPassword"
<add name="SiteConnectionString" connectionString="Data Source=10.10.10.110;Initial Catalog=SiteDBName;User ID=SiteUser;Password=SitePassword"
Jan 08, 2021 08:08 PM|jzero|LINK
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.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="MyTest.aspx.cs" Inherits="MyTest" %>
<form id="form1" runat="server">
<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>
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;
// 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;
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
if (System.Web.Hosting.HostingEnvironment.IsDevelopmentEnvironment == true)
MyDB = new Max_Cell_Qty_vs_PartDataContext();
string myConStr = System.Configuration.ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString;
MyDB = new Max_Cell_Qty_vs_PartDataContext(myConStr);
Jan 08, 2021 10:23 PM|CW Code|LINK
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.....
Jan 08, 2021 11:46 PM|jzero|LINK
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