The issue first came up in a project last semester, and again it haunts us:
when you place your .mdf file in one project (the DAL) and execute the application from another project (the UI) evil things happen. Namely, you get an exception:
System.Data.SqlClient.SqlException was unhandled by user code
Message="An attempt to attach an auto-named database for file blah blah blah\PL\myDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."
As I say, this is evil. The file myDB.mdf is right where I put it; when I go into Settings for my DAL project and open the nifty dialog box for my connection string, the "Test Connection" button works just fine.
But just you take a closer look at that error. When I compile my application, the system starts looking for my DB in the directory that contains my PL, not my DAL. What happened?
When I open the nifty dialog box, the path for my DB looks like this:
blah blah blah\DAL\myDB.mdf
The actual value of the setting in my DAL\Settings.settings file looks like this:
Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\myDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True
Ha ha! Foul play! I can spend the whole day setting the location of the DB in the nifty dialog box, but in the bitter end the Settings.settings file translates the path of the parent project to a variable called |DataDirectory|, and at compile time it replaces my DAL project's path with the startup (UI) project's path for the value of that variable.
What's the right workaround?
1. The wrongheaded way would be to elbow my way into the Settings.settings and set the path absolutely to the location of my DB. Wrongheaded, because if I move my project around (even on my own machine), I have to elbow back in and update the location.
2. The easy way out would be to use SQL Server instead of a local data file. Which is of course what we're doing on our project.
3. I thought to try to place the DB in the parent folder of the projects - ie, in the solution folder. But that induced Settings.settings to hard-code the location. Not much better than solution 1.
4. Finally I found what I was looking for! by jumping from here to here to (at last!)
https://blogs.msdn.com/smartclientdata/archive/2005/08/26/456886.aspx
So I added the following line of code to a function that is executed before I try to access my data:
"AppDomain.CurrentDomain.SetData("DataDirectory", AppDomain.CurrentDomain.BaseDirectory.Replace("GUI", "DAL") );"
where GUI is the directory of my UI and DAL is the directory of my DAL and they are in the same parent (solution) directory.
But really DBs were meant to be on servers, and anyway 3 layers are 2 layers too many. So claims Microsoft.
Now considering how much trouble this has caused a good number of people since 2005, I think it's time for Microsoft to rethink its nifty features.
09 July 2009
02 July 2009
Pile on the layers
Here's a quote from MSDN:
"Most ASP.NET data source controls, such as the SqlDataSource, are used in a two-tier application architecture where the presentation layer (the ASP.NET Web page) communicates directly with the data tier (the database, an XML file, and so on)."
This is a cute way of saying that all those nice data-bound ASP.net controls - the ones that are supposed to take a data source and then do all the work of Selecting, Inserting, Updating, and Deleting (ie, CRUD) for you - will not work in a 3-layered structure.
That was the bad news, except that we knew it all along.
Here comes the good news:
"The ObjectDataSource works with a middle-tier business object to select, insert, update, delete, page, sort, cache, and filter data declaratively without extensive code."
http://msdn.microsoft.com/en-us/library/9a4kyhcx.aspx
So what do we need to do in order to get the data-sourced controls to work with an ObjectDataSource and, by extension, a BL and DAL?
Creating an ObjectDataSource Control Source Object has some answers. We need to define a stateless class (no non-static members) to provide CRUD logic for the data to populate the data-bound control on our form. Optionally, this class can also provide functions to filter the data and sort it. Then, using the (very handy) wizard provided by the ObjectDataSource, we select the class with the CRUD functions and identify the parameters it needs to preform Select.
Very well, but what about the parameters for Insert, Update, and Delete?
We can define another class to represent a record/row in our data schema. (The TypeName property of the ObjectDataSource must be set to this class.) All properties of the class will be polled (by reflection, one presumes) in order to fill the data-sourced control, and will be filled in return for the Update function. By setting the ConflictDetection property of the ObjectDataSource, we can even decide how updates should be done:
OverwriteChanges will simply fill an object with the new values and pass it to the Update function.
CompareAllValues will fill two objects: the first with the old values, the second with the new, and pass both to the Update function.
Two items of note:
1. The wizard will only work if your latest working build contains all the classes and functions you want to use! In other words, Build your project before running the wizard.
2. The CRUD functions I'm describing here are in my BL. They themselves do NOT do the actual persistence to the DB; rather they massage the data as appropriate and pass it in the appropriate format to the DAL, which does the real work.
"Most ASP.NET data source controls, such as the SqlDataSource, are used in a two-tier application architecture where the presentation layer (the ASP.NET Web page) communicates directly with the data tier (the database, an XML file, and so on)."
This is a cute way of saying that all those nice data-bound ASP.net controls - the ones that are supposed to take a data source and then do all the work of Selecting, Inserting, Updating, and Deleting (ie, CRUD) for you - will not work in a 3-layered structure.
That was the bad news, except that we knew it all along.
Here comes the good news:
"The ObjectDataSource works with a middle-tier business object to select, insert, update, delete, page, sort, cache, and filter data declaratively without extensive code."
http://msdn.microsoft.com/en-us/library/9a4kyhcx.aspx
So what do we need to do in order to get the data-sourced controls to work with an ObjectDataSource and, by extension, a BL and DAL?
Creating an ObjectDataSource Control Source Object has some answers. We need to define a stateless class (no non-static members) to provide CRUD logic for the data to populate the data-bound control on our form. Optionally, this class can also provide functions to filter the data and sort it. Then, using the (very handy) wizard provided by the ObjectDataSource, we select the class with the CRUD functions and identify the parameters it needs to preform Select.
Very well, but what about the parameters for Insert, Update, and Delete?
We can define another class to represent a record/row in our data schema. (The TypeName property of the ObjectDataSource must be set to this class.) All properties of the class will be polled (by reflection, one presumes) in order to fill the data-sourced control, and will be filled in return for the Update function. By setting the ConflictDetection property of the ObjectDataSource, we can even decide how updates should be done:
OverwriteChanges will simply fill an object with the new values and pass it to the Update function.
CompareAllValues will fill two objects: the first with the old values, the second with the new, and pass both to the Update function.
Two items of note:
1. The wizard will only work if your latest working build contains all the classes and functions you want to use! In other words, Build your project before running the wizard.
2. The CRUD functions I'm describing here are in my BL. They themselves do NOT do the actual persistence to the DB; rather they massage the data as appropriate and pass it in the appropriate format to the DAL, which does the real work.
Subscribe to:
Posts (Atom)
