Start Free Trial

asked on

Second LINQ-to-SQL data context on the same Web form

I am working on a Web form using MS Visual Studio 2010 Pro in C# with SQL Server 2005. I have to query two separate databases to display data on the same Web form using LINQ-to_SQL. I need advice/help on getting it so the application recognizes both databases (two sepaprate .DBML files).

This is supported right out of the box. Just make sure you put both dbml files in their own folder in the project to prevent naming clashes between the two. An alternative solution is to place each database definition in a separate project and reference that from your web project.

ASKER

First attempts didn't work, so I thought I'd post here for a possible solution. I've put everything into a new Web project. So, let me try this again.

If you have separate .dbml files, you need separate DataContext instances, and then you can reference them separately. I have a project now, with 7 .dbml files, and each one with a DataContext instance to talk to the database.

ASKER

That's what I did . . .

Database1DataContext db = new Database1DataContext();

Database2DataContext db2 = new Database2DataContext();

. . . but for some reason, it wasn't recognizing the tables (entities) in the second .DBML file. Is there anything I should be aware of?

Are you getting any compiler errors? Did it generate the proxy classes in the .designer files? If you have an instance of a DataContext, and there aren't any members, that usually indicates that the proxy class wasn't generated correctly.

ASKER

Then I would say no, I didn't. I am fairly new to LINQ-to-SQL, as well as Visual Studio 2010, so how exactly do I get it to generate each of the proxy classes?

From my experience it is best to place each DBML in a separate folder (namespace) in your project, that way the names in each context won't clash with the other.

Aha, fresh meat!! You have come into my lair, so let me show you around. *BIG GRIN*

I created a blog entry to highlight the steps:

https://www.experts-exchange.com/blogs/TheLearnedOne/B_4669-NET-Object-Relational-Mapping-ORM-LINQ-to-SQL.html

ASKER

Hey, TheLearnedOne, do you agree with ToAoM's separate folder (namespace)? Meat? Yes. Fresh? Not so much. Perhaps I should just go to your blog before asking anymore questions. I mean, it has crossed my mind on whether or not the sequence of events has any effects. For example, on VS2010, should I click Add New Item (to the project) > (under Visual C#) Data > LINQ to SQL Classes to create each of the .dbml files for my project before I do a Build of the project/solution? Ok, going to read your blog.

When you are working with multiple contexts, where it is possible for name conflicts, it is essential that you use separate folders so that the fully-qualified names will be unique when the classes are automatically generated.

ASKER

Good point. Your blog is full of good information, but not entirely related to what I'm doing. I already have two existing SQL Server 2005 databases (on the same server) with relationships that I am using for the Web application/site. So I added both databases by Server Explorer > Connect to Database so each server.database.dbo combination shows under Data Connections. I then used Add New Item > (Visual C#) LINQ to SQL Classes which creates the respective .dbml files. It prompts me that in order to use these files, it recoomends they be placed in the App_Code/ folder. Do you agree with this so far?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

What happens if I and another programmer/analyst have projects within the same solution? My project has always been a Web site, while the other person's is a Web application that consumes a Web Service. Is it possible there may be a conflict between the two if both are within the same solution?

When you work with a Web Site model, the folder is one big site, so you won't have any segregation. That is why you have the concept of well-known folders, so that content can be located.

ASKER

And that is why I've been having a conflict when trying to access the second database. The other programmer/analyst's Web application uses the same database (same model) as the first database I am working with. Ok, I'm gonna have to try this again, but as a Web application this time.

Here is an example structure, if you have multiple solutions that share the same data:

+ Admin Web Site (Web Application)
+ User Web Site (Web Application)
+ Data Access Class Library

You would add a reference to the Data Access Class Library for the separate web application projects.

ASKER

Ok, this is gonna take some time to re-do the site as a Web application, so please bear with me.

ASKER

Are you still with me, TheLearnedOne?

I had to work thru a few errors in converting my project from a Web site to a Web application. I got it to run without any errors, warning or unhandled exceptions. The GridView displays just as it should, but the individual SUMs do not. My code is attached. What am I missing to get it to display the SUMs?

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Caching;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;

namespace NewFeeBookReport
{
    public partial class FeeBookReport : System.Web.UI.Page
    {

        protected void Page_Load(object sender, EventArgs e)
        {
            DateTime beginDate = new DateTime(2011, 02, 28, 8, 0, 0);
            DateTime endDate = new DateTime(2011, 2, 28, 17, 0, 0);

            Recorder_test2DataContext db = new Recorder_test2DataContext();

            var FeeBookEntries =
                from j in db.journals
                join l in db.ledgers on j.id equals l.journal__id
                join a in db.accounts on l.account__id equals a.id
                join acctyp in db.account_types on a.account_type__id equals acctyp.id
                join inst in db.instruments on j.instrument__id equals inst.id
                join ik in db.instrument_kinds on inst.instrument_kind__id equals ik.id into temp
                from x in temp.DefaultIfEmpty()
                where (inst.file_time > Convert.ToDateTime(beginDate))
                && (inst.file_time < Convert.ToDateTime(endDate))
                select new
                {
                    journal_id = j.id,
                    Document = string.Format("{0} {1}", inst.book, inst.page),
                    Filed = inst.file_time.Date,
                    Kind = x.short_name,
                    REC = l.amount == null ? "0.00" : Convert.ToString(l.amount)
                };
            
            GridView1.DataSource = FeeBookEntries;
            GridView1.DataBind();

            var RMA =
                 (from j in db.journals
                  join l in db.ledgers on j.id equals l.journal__id
                  join a in db.accounts on l.account__id equals a.id
                  join inst in db.instruments on j.instrument__id equals inst.id into results
                  from r in results.DefaultIfEmpty()
                  where r.file_time > beginDate
                  && r.file_time < endDate
                  && (l.account__id == 4002)
                  select l.amount).Sum();

            string RMAstg;
            RMAstg = RMA.ToString();
            
            var Ecomm =
                 (from j in db.journals
                  join l in db.ledgers on j.id equals l.journal__id
                  join a in db.accounts on l.account__id equals a.id
                  join inst in db.instruments on j.instrument__id equals inst.id into results
                  from r in results.DefaultIfEmpty()
                  where r.file_time > beginDate
                  && r.file_time < endDate
                  && (l.account__id == 4006)
                  select l.amount).Sum();

            string Ecommstg;
            Ecommstg = Ecomm.ToString();
            
            var Audit =
                 (from j in db.journals
                  join l in db.ledgers on j.id equals l.journal__id
                  join a in db.accounts on l.account__id equals a.id
                  join inst in db.instruments on j.instrument__id equals inst.id into results
                  from r in results.DefaultIfEmpty()
                  where r.file_time > beginDate
                  && r.file_time < endDate
                  && (l.account__id == 4004)
                  select l.amount).Sum();

            string Auditstg;
            Auditstg = Audit.ToString();
            
            var Recording =
                 (from j in db.journals
                  join l in db.ledgers on j.id equals l.journal__id
                  join a in db.accounts on l.account__id equals a.id
                  join inst in db.instruments on j.instrument__id equals inst.id into results
                  from r in results.DefaultIfEmpty()
                  where r.file_time > beginDate
                  && r.file_time < endDate
                  && (l.account__id == 4001)
                  select l.amount).Sum();

            string Recordingstg;
            Recordingstg = Recording.ToString();

            string Total_REstg;
            Total_REstg = (RMAstg + Ecommstg + Auditstg + Recordingstg);
            
            var RMAcharge =
                 (from j in db.journals
                  join l in db.ledgers on j.id equals l.journal__id
                  join a in db.accounts on l.account__id equals a.id
                  join inst in db.instruments on j.instrument__id equals inst.id into results
                  from r in results.DefaultIfEmpty()
                  where r.file_time > beginDate
                  && r.file_time < endDate
                  && (l.account__id == 1102)
                  select l.amount).Sum();

            string RMAchargestg;
            RMAchargestg = RMAcharge.ToString();

            string Grand_Totalstg;
            Grand_Totalstg = RMAcharge.ToString();

            string RMAcashstg;
            RMAcashstg = (RMA - RMAcharge).ToString();
            
            var ECMcharge =
                 (from j in db.journals
                  join l in db.ledgers on j.id equals l.journal__id
                  join a in db.accounts on l.account__id equals a.id
                  join inst in db.instruments on j.instrument__id equals inst.id into results
                  from r in results.DefaultIfEmpty()
                  where r.file_time > beginDate
                  && r.file_time < endDate
                  && (l.account__id == 1103)
                  select l.amount).Sum();

            string ECMchargestg;
            ECMchargestg = ECMcharge.ToString();

            string ECMcashstg;
            ECMcashstg = (Ecomm - ECMcharge).ToString();
            
            var REcharge =
                 (from j in db.journals
                  join l in db.ledgers on j.id equals l.journal__id
                  join a in db.accounts on l.account__id equals a.id
                  join inst in db.instruments on j.instrument__id equals inst.id into results
                  from r in results.DefaultIfEmpty()
                  where r.file_time > beginDate
                  && r.file_time < endDate
                  && (l.account__id == 1101)
                  select l.amount).Sum();

            string REchargestg;
            REchargestg = REcharge.ToString();

            string REcashstg;
            REcashstg = (Recording - REcharge).ToString();
            
            var TTax =
                 (from j in db.journals
                  join l in db.ledgers on j.id equals l.journal__id
                  join a in db.accounts on l.account__id equals a.id
                  join inst in db.instruments on j.instrument__id equals inst.id into results
                  from r in results.DefaultIfEmpty()
                  where r.file_time > beginDate
                  && r.file_time < endDate
                  && (l.account__id == 4003)
                  select l.amount).Sum();

            string TTaxstg;
            TTaxstg = TTax.ToString();

            //Other Fees

            string RevenueStampsstg;
            RevenueStampsstg = TTax.ToString();
            //same number as TTax
            
        }
    }
}

Open in new window

ASKER

...and here's most of the form from the .aspx file.

<%@ Page Language="C#" AutoEventWireup="True" CodeBehind="FeeBookReport.aspx.cs" Inherits="NewFeeBookReport.FeeBookReport" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Recorders: Fee Book</title>
    <style type="text/css">
	p { font-family:Arial; font-size:12pt; font-weight:bold; }
	div { font-family:Arial; font-size:12pt; font-weight:bold; }
	</style>

</head>
<body>
    <form id="ReportViewer" runat="server">
    <div>
        <center>Fee Book ()</center>
        Run Time: <%= System.DateTime.Now.ToLongDateString()+" "+System.DateTime.Now.ToLongTimeString() %><br /><br />
    </div>
    <div>
        <asp:GridView ID="GridView1" runat="server" Height="900px" Width="600px" AllowPaging="True" 
            AutoGenerateEditButton="True" AutoGenerateSelectButton="True" GridLines="None" 
            BorderColor="White" BorderStyle="None" Font-Names="Arial" Font-Size="12pt" 
            HorizontalAlign="Center" PageSize="39">
            <AlternatingRowStyle BackColor="#CCCCCC" Font-Bold="False" Font-Names="Arial" 
                Font-Size="12pt" ForeColor="Black" HorizontalAlign="Left" 
                VerticalAlign="Middle" Wrap="False" />
            <EditRowStyle BorderStyle="None" Font-Bold="True" Font-Names="Arial" 
                Font-Size="12pt" HorizontalAlign="Left" VerticalAlign="Middle" Wrap="False" />
            <EmptyDataTemplate>
                You have generated no results . . .
            </EmptyDataTemplate>
            <HeaderStyle BorderStyle="None" Font-Bold="True" Font-Names="Arial" 
                Font-Size="12pt" HorizontalAlign="Left" VerticalAlign="Middle" Wrap="False" />
            <PagerStyle BorderStyle="None" Font-Names="Gill Sans MT" Font-Size="Small" 
                HorizontalAlign="Center" VerticalAlign="Bottom" Wrap="False" />
            <RowStyle BorderStyle="None" Font-Names="Arial" Font-Size="Medium" 
                HorizontalAlign="Left" VerticalAlign="Middle" Wrap="False" />
            <SelectedRowStyle BackColor="#99CCFF" BorderStyle="None" Font-Bold="True" 
                Font-Names="Arial" Font-Size="12pt" ForeColor="Black" HorizontalAlign="Left" 
                VerticalAlign="Middle" Wrap="False" />
        </asp:GridView>
    </div>
    <div>
        <p style="page-break-before:always; text-align:center" >Fee Book&nbsp;<%= System.DateTime.Now.ToLongDateString() %></p>
        <p>Run Time: <%= System.DateTime.Now.ToLongDateString()+" "+System.DateTime.Now.ToLongTimeString() %></p>
        <center><asp:Table ID="Table1" runat="server" BorderStyle="None" CellPadding="0" Width="600px" Font-Size="12pt">
            <asp:TableRow HorizontalAlign="Center"><asp:TableCell ColumnSpan="3"></asp:TableCell></asp:TableRow>
            <asp:TableRow><asp:TableCell Width="290px" VerticalAlign="Top">
                
                <asp:Table ID="Table2" runat="server" BorderStyle="None" CellPadding="0" Width="290px">
                    <asp:TableRow HorizontalAlign="Center" TableSection="TableHeader" Font-Bold="True" Font-Size="12pt">
                        <asp:TableCell ColumnSpan="2" Width="290px" Text="Recording Fees"></asp:TableCell></asp:TableRow>
                    <asp:TableRow><asp:TableCell HorizontalAlign="Left">RMA</asp:TableCell><asp:TableCell HorizontalAlign="Right" runat="server" Text="<%= RMAstg %>"></asp:TableCell></asp:TableRow>
                    <asp:TableRow><asp:TableCell HorizontalAlign="Left">E-Commerce</asp:TableCell><asp:TableCell HorizontalAlign="Right" Text="<%= Ecommstg %>"></asp:TableCell></asp:TableRow>
                    <asp:TableRow><asp:TableCell HorizontalAlign="Left">Audit</asp:TableCell><asp:TableCell HorizontalAlign="Right" Text="<%= Auditstg %>"></asp:TableCell></asp:TableRow>
                    <asp:TableRow><asp:TableCell HorizontalAlign="Left">Recording</asp:TableCell><asp:TableCell HorizontalAlign="Right" Text="<%= Recordingstg %>"></asp:TableCell></asp:TableRow>
                    <asp:TableRow><asp:TableCell ColumnSpan="2">&nbsp;</asp:TableCell></asp:TableRow>
                    <asp:TableRow><asp:TableCell HorizontalAlign="Left">Total RE</asp:TableCell><asp:TableCell HorizontalAlign="Right" Text="<%= Total_REstg %>"></asp:TableCell></asp:TableRow>
                    <asp:TableRow><asp:TableCell HorizontalAlign="Left">&nbsp;</asp:TableCell><asp:TableCell HorizontalAlign="Right">__________</asp:TableCell></asp:TableRow>
                    <asp:TableRow><asp:TableCell HorizontalAlign="Left">Grand Total</asp:TableCell><asp:TableCell HorizontalAlign="Right" Text="<%= Grand_Totalstg %>"></asp:TableCell></asp:TableRow>
                </asp:Table>
            
            </asp:TableCell><asp:TableCell Width="20">&nbsp;</asp:TableCell><asp:TableCell Width="290px" VerticalAlign="Top">
                
                <asp:Table ID="Table3" runat="server" BorderStyle="None" CellPadding="0" Width="290px">
                    <asp:TableRow HorizontalAlign="Center" TableSection="TableHeader" Font-Bold="True" Font-Size="12pt">
                        <asp:TableCell ColumnSpan="2" Width="290px" Text="RE CASH/Charge Summary"></asp:TableCell></asp:TableRow>
                    <asp:TableRow><asp:TableCell HorizontalAlign="Left">RMA Cash</asp:TableCell><asp:TableCell HorizontalAlign="Right" Text="<%= RMAcashstg %>"></asp:TableCell></asp:TableRow>
                    <asp:TableRow><asp:TableCell HorizontalAlign="Left">RMA Charge</asp:TableCell><asp:TableCell HorizontalAlign="Right" Text="<%= RMAchargestg %>"></asp:TableCell></asp:TableRow>
                    <asp:TableRow><asp:TableCell HorizontalAlign="Left">ECM Cash</asp:TableCell><asp:TableCell HorizontalAlign="Right" Text="<%= ECMcashstg %>"></asp:TableCell></asp:TableRow>
                    <asp:TableRow><asp:TableCell HorizontalAlign="Left">ECM Charge</asp:TableCell><asp:TableCell HorizontalAlign="Right" Text="<%= ECMchargestg %>"></asp:TableCell></asp:TableRow>
                    <asp:TableRow><asp:TableCell HorizontalAlign="Left">RE Cash</asp:TableCell><asp:TableCell HorizontalAlign="Right" Text="<%= REcashstg %>"></asp:TableCell></asp:TableRow>
                    <asp:TableRow><asp:TableCell HorizontalAlign="Left">RE Charge</asp:TableCell><asp:TableCell HorizontalAlign="Right" Text="<%= REchargestg %>"></asp:TableCell></asp:TableRow>
                    <asp:TableRow><asp:TableCell HorizontalAlign="Left">&nbsp;</asp:TableCell><asp:TableCell HorizontalAlign="Right">&nbsp;</asp:TableCell></asp:TableRow>
                    <asp:TableRow><asp:TableCell HorizontalAlign="Left">Transfer Tax</asp:TableCell><asp:TableCell HorizontalAlign="Right" Text="<%= TTaxstg %>"></asp:TableCell></asp:TableRow>
                    <asp:TableRow><asp:TableCell ColumnSpan="2">&nbsp;</asp:TableCell></asp:TableRow>
                    <asp:TableRow><asp:TableCell ColumnSpan="2">&nbsp;</asp:TableCell></asp:TableRow>
                </asp:Table>
            
            </asp:TableCell></asp:TableRow>
            <asp:TableRow><asp:TableCell Width="290px" VerticalAlign="Top">
            
                <asp:Table ID="Table4" runat="server" BorderStyle="None" CellPadding="0" Width="290px">
                    <asp:TableRow HorizontalAlign="Center" TableSection="TableHeader" Font-Bold="True" Font-Size="12pt">
                        <asp:TableCell ColumnSpan="2" Width="290px" HorizontalAlign="Center" Text="Other Fees"></asp:TableCell></asp:TableRow>
                    <asp:TableRow><asp:TableCell HorizontalAlign="Left">Revenue Stamps</asp:TableCell><asp:TableCell HorizontalAlign="Right" Text="<%= RevenueStampsstg %>"></asp:TableCell></asp:TableRow>

Open in new window

ASKER

Ok, perhaps the proper way to put my varibales in the codebehind for the table, something like this:
string RMAstg;
RMAstg = RMA.ToString();
TableRow tblrow;
TableCell tblcell;
tblrow = Table2.Rows(0);
tblcell = tblrow.Cells(0);
tblcell.Text = RMAstg;
but I'm getting the "non-invocable member 'system.web.ui.webcontrols.table.rows' cannot be used like a method" error.

Wow, information overload (whoa doggies)!!

Let's start with--what elements are you using for the SUMs? I am assuming a column footer, but I don't really want to pick out the wheat from the chaff (needle-in-a-haystack problem).

:) I had the same reaction. One thing I notice is that you're joining your objects, but in Linq2Sql you can just do Journal.Ledger.Account and Linq2Sql will do the joins for you as long as you have the proper foreign keys defined. That should make your expressions a lot shorter and easier to read.

But without the data model and a bit of sample data it's very hard to rewrite the queries without breaking anything.

ASKER

Sorry about the data overload. Ok, shall we look at getting the variables (e.g., RMAstg) to display withing the table cells on the page. The separate LINQ-to-SQL queries used for the SUM are not of concern - they all worked before I started down the path of converting from a Web site to a Web application. That is, they worked because they use the same data context (.dbml):
Recorder_test2DataContext db = new Recorder_test2DataContext();

Are you saying that you need to join across different data contexts? LINQ supports local joins, but I don't believe that LINQ-to-SQL does...

ASKER

Allow me to slow down a bit. No, I am not doing joins across different data contexts. So, that a good thing hearing LINQ-to-SQL doesn't do that (I would put the results/records of each data context into a new/temp table, then pull from there) . I have a number of LINQ-to-SQL statements that are using the same data context. The first one, var FeeBookEntries = , is used to bind to a GridView. That works fine. Here is the second LINQ-to-SQL statement:

var RMA =
(from j in db.journals
join l in db.ledgers on j.id equals l.journal__id
join a in db.accounts on l.account__id equals a.id
join inst in db.instruments on j.instrument__id equals inst.id into results
from r in results.DefaultIfEmpty()
where r.file_time > beginDate
&& r.file_time < endDate
&& (l.account__id == 4002)
select l.amount).Sum();

string RMAstg;
RMAstg = RMA.ToString();

...uses the same data context as the first statement. The only difficulty I've run into is displaying the resultant variable (RMAstg) into the HTML table. I believe it's something like this:

Table2.Rows[1].Cells[1].Text = RMAstg;

Hmmm...I don't see anything wrong there, so what is the difficulty that you are having?

ASKER

Just tested what I sent ( Table2.Rows[1].Cells[1].Text = RMAstg; ). It works just fine. Let me finish the others using the same data context and go into the second data context (from a different database on the same server).

ASKER

All good so far. Now when I add the second data context (different databade, same server) . . .

Recboats_newDataContext db2 = new Recboats_newDataContext();

. . . VS2010 intellisense likes it because I put the second .dbml in before I compiled. But, as soon as I try to query the second data context . . .

var BoatCode =
from b in db.BoatCodes
join rbrd in db.rbrdalys on b.DateUsed.Date equals rbrd.activity_date.Date
where (b.DateUsed.Date == beginDate.Date)
select new
{
BoatCode = b.code,
DateUsed = b.DateUsed
};

. . . I get an error for each of the second database tables (db.BoatCodes and db.rbrdalys) - "[2nd data context] does not contain a definition for 'BoatCodes' and no extension method 'BoatCodes' accepting a first argument of type [2nd data context] could be found (are you missing a using directive or an assembly reference?). Is there something I need to do/put between my last use of the 1st data context and the second?

Let's be clear about your implementation...are you defining a single data context (.dbml), and then creating 2 different instances, or do you have 2 separate .dbml files?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

TheLearnedOne: 2 separate data contexts (2 separate .dbml files).

ToAoM: Yes, I can't call the 2nd context 'db' because that was the 1st one. Ah, yes, 'let me swap it to 'db2'.

ASKER

Thanks, ToAoM, for that "ah-ha" moment. Here's how I got it to work:

var BoatCode =
from b in db2.BoatCodes
join rbrd in db2.rbrdalies on b.DateUsed equals rbrd.activity_date
where (b.DateUsed == beginDate.Date)
select new
{
BoatCode = b.Code,
DateUsed = b.DateUsed
};

ASKER

Sure will Microsoft would have mentioned anywhere in their documentation about the limit to one database on the Web site solution. It would have helped save a lot of time and effort. And this is why I turned to the experts!