|
|
8/13/2009
I happened across a CURSOR based "Running Total" example on the web today.
Everyone with whom I work knows I detest Cursors in TSQL. 
So I coded up (one) alternate solution.
Below is (one variation) of a 'Running Total' solution using a CTE.
http://technet.microsoft.com/en-us/library/ms175972.aspx (CTE Link)
-----------START TSQL
Use Northwind
GO
declare @CustomerID varchar(6)
declare @BeginDate datetime
declare @EndDate datetime
select @CustomerID = (select top 1 CustomerID from dbo.Orders )
select @BeginDate = '01/01/1900'
select @EndDate = '12/31/2010'
;
WITH MyCTE -- http://technet.microsoft.com/en-us/library/ms175972.aspx
( ShipName,ShipAddress,ShipCity,ShipRegion,ShipPostalCode,ShipCountry,CustomerID,CustomerName,[Address],
City ,Region,PostalCode,Country,Salesperson,OrderID,OrderDate,RequiredDate,ShippedDate,ShipperName,
ProductID ,ProductName,UnitPrice,Quantity,Discount,ExtendedPrice,Freight,ROWID) AS
(
SELECT
ShipName ,ShipAddress,ShipCity,ShipRegion,ShipPostalCode,ShipCountry,CustomerID,CustomerName,[Address],
City ,Region,PostalCode,Country,Salesperson,OrderID,OrderDate,RequiredDate,ShippedDate,ShipperName,
ProductID ,ProductName,UnitPrice,Quantity,Discount,ExtendedPrice,Freight
, ROW_NUMBER() OVER ( ORDER BY OrderDate , ProductName ASC ) as ROWID
FROM
dbo .Invoices inv -- "Invoices" is a VIEW, FYI
where
inv .CustomerID = @CustomerID
and (inv.OrderDate between @BeginDate and @EndDate)
)
SELECT
/*
ShipName,ShipAddress,ShipCity,ShipRegion,ShipPostalCode,ShipCountry,CustomerID,CustomerName,[Address],
City,Region,PostalCode,Country,Salesperson,OrderID,OrderDate,RequiredDate,ShippedDate,ShipperName,
ProductID,ProductName,UnitPrice,Quantity,Discount,ExtendedPrice,Freight,
*/
--trim the list down a little for the final output
CustomerID ,Salesperson,OrderID,OrderDate,ProductName,UnitPrice,Quantity,Discount,ExtendedPrice,Freight,
(ExtendedPrice + Freight) as ComputedTotal
--The below line is the "trick". I reference the above CTE, but only get data that is less than or equal to the row that I am on (outerAlias.ROWID)
, (Select SUM (ExtendedPrice + Freight) from MyCTE innerAlias where innerAlias.ROWID <= outerAlias.ROWID ) as RunningTotal
, ROWID as ROWID_SHOWN_FOR_KICKS , OrderDate as OrderDateASecondTimeForConvenience
FROM MyCTE outerAlias
--Two Order By Options
--ORDER BY outerAlias.OrderDate , ProductName -- << Whatever the ORDER BY is here, should match the "ROW_NUMBER() OVER ( ORDER BY ________ ASC )" statement inside the CTE
ORDER BY outerAlias.ROWID -- << Or, to keep is more "trim", ORDER BY the ROWID, which will of course be the same as the "ROW_NUMBER() OVER ( ORDER BY" inside the CTE
7/7/2009
Download example HERE. (<< Right-click. "Save (Target) As" usually works best).
If I haven't mentioned it before, thank you Microsoft for the sqlcmd.exe utility.
If you are new to sqlcmd or new to the concept of repeatability, below is a simple example.
It creates 3 databases that are exactly the same except for the database-name.
Can you say "repeatable" (as in every-single-time-repeatable-and-dependable)?
If you're still using the GUI in Sql Server Management Studio and doing a "Right-Click"/"Add Database" for than anything for casual development, then please stop.
It is not repeatable. It does not promote repeatability for "D-DAY" (that's what I call deployment-day).
My motto:
If you cannot deploy your code, your code and hard-work is essentially worthless.
The demo is simple. A database, one table, and 2 stored procedures. But the skeleton is there.
The example zip file is a demo example of creating a basic “OrganizationDB” (OrganizationDB01, OrganizationDB02, and OrganizationDB03).
Again, this is a sqlcmd.exe example of using $(Variable)’s to deploy databases….which emphasizes repeatability but with configurability as well.
Instructions:
Unzip the files……….read the README.txt file (the top portion contains setup information).
If you have ".\SqlExpress" setup as your development environment, you should be able to just click:
RebuildDatabaseMaster_AllThree.bat
and it should just work.
Look at the gif screenshot (in the zip), and compare to your database.
Then you can look at the README.txt file for explanation about what is happening.
There are also output log files you can look at (mentioned in the README.txt file) which will provide clues if anything does not work.
After you get it down, you’re essentially (always) one copy/paste/edit (the variable file) and a second copy/paste/edit (the .bat file) away from deploying a new database.
ISQLW OSQL << These are the early versions of this tool, FYI.
7/6/2009
We all know a SELECT TOP(N) (ORDER BY) statement will work.
Use Northwind
GO
select top(10) * from dbo.Orders ORDER BY CustomerID , OrderDate
GO
However, if you've tried to do a:
Update TOP (10) ...... ORDER BY X
That does not work.
Here is a workaround example to do an Update TOP(N) (ORDER BY) query with one (cte) statement.
----------START TSQL
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Television]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[Television]
END
GO
CREATE TABLE [dbo].[Television] (
TelevisionUUID [uniqueidentifier] not null default NEWSEQUENTIALID() ,
TelevisionName varchar(64) not null ,
TelevisionKey int not null ,
IsCheckedOut bit default 0
)
GO
ALTER TABLE dbo.Television ADD CONSTRAINT PK_Television_TelevisionUUID
PRIMARY KEY CLUSTERED (TelevisionUUID)
GO
ALTER TABLE dbo.Television ADD CONSTRAINT CK_Television_TelevisionName_UNIQUE
UNIQUE (TelevisionName)
GO
set nocount on
declare @counter int
select @counter = 11000
declare @currentTVName varchar(24)
declare @TopSize int
select @TopSize = 10
while @counter > 10000 -- this loop counter is ONLY here for fake data,....do not use this syntax for production code
begin
select @currentTVName = 'TV: '+ convert(varchar(24) , @counter)
INSERT into dbo.Television ( TelevisionName , TelevisionKey ) values ( @currentTVName , @counter)
select @counter = @counter - 1
end
select count(*) as TV_Total_COUNT from dbo.Television
/*
--Does not Work!
Update TOP (10) dbo.Television
Set IsCheckedOut = 1
FROM
dbo.Television tv
ORDER BY tv.TelevisionKey
*/
declare @AuditTrail table ( TelevisionUUID uniqueidentifier , OldIsCheckedOut bit , NewIsCheckedOut bit )
;
WITH cte1 AS
( SELECT
TOP (@TopSize)
TelevisionUUID , --<<Note, the columns here must be available to the output
IsCheckedOut
FROM
dbo.Television tv
WITH ( UPDLOCK, READPAST , ROWLOCK ) --<<Optional Hints, but helps with concurrency issues
WHERE
IsCheckedOut = 0
ORDER BY
tv.TelevisionKey DESC
)
UPDATE cte1
SET IsCheckedOut = 1
output inserted.TelevisionUUID , deleted.IsCheckedOut , inserted.IsCheckedOut into @AuditTrail ( TelevisionUUID , OldIsCheckedOut , NewIsCheckedOut )
;
print ''
print 'Newly Checked Out Items'
select * from dbo.Television tv where tv.IsCheckedOut <> 0
print 'Output AuditTrail'
select * from @AuditTrail
print 'Not checked out items'
select count(*) as TVCOUNTIsNOTCheckedOut from dbo.Television tv where tv.IsCheckedOut = 0
Threads are now (or soon will be) very "Old School".
Well, that statement may be a little strong, but there will soon be a new option.
I attended a local user group and was pleased to learn about the:
.NET 4 -- System.Threading.Tasks.Task
Here is a URL describing it (better than I can re-hash it)
It's still a little off into the future, but it is good to know nonetheless.
Did I inspire a Sql Server 2008 feature?
You be the judge:
Go here:
http://blogs.msdn.com/sqlcat/archive/2006/02/17/Partition-Elimination-in-SQL-Server-2005.aspx#5276553
And find this post dated on:
October 4, 2007 1:50 PM
(Note, the post I wrote is above this date stamp)..
Now go here to this URL.
http://technet.microsoft.com/en-us/library/ms345599(SQL.100).aspx
(This article is titled: Query Processing Enhancements on Partitioned Tables and Indexes)
Ok, now the heart of the matter.
Find this line in the technet article:
Partition Summary Information
And read the 3-4 paragraphs below it.
Did I inspire a Sql Server 2008 feature?
You make the call!
Well, I don't care how it got there, but the 2008 version is 100 times better than the 2005 version.
========================================================================
Below are the snipplets that I cite above....But I wanted to give the real URL's so this does not look made up.
http://blogs.msdn.com/sqlcat/archive/2006/02/17/Partition-Elimination-in-SQL-Server-2005.aspx#5276553
October 4, 2007 1:50 PM
Man, what would be nice is something like:
set partitions profile on; --<this does not exist
And then it would just show you:
Table PartitionTouches
t1 1
Because it gets cumbersome/confusing sometimes.
I guess I can dream.
But thank you again for the post...I'd be lost/frustrated without it.
http://technet.microsoft.com/en-us/library/ms345599(SQL.100).aspx
Partition Summary Information
In run-time execution plans, partition summary information provides a count of the partitions accessed and the identity of the actual partitions accessed. You can use this information to verify that the correct partitions are accessed in the query and that all other partitions are eliminated from consideration.
The following information is provided: Actual Partition Count, and Partitions Accessed.
Actual Partition Count is the total number of partitions accessed by the query.
Partitions Accessed, in XML Showplan output, is the partition summary information that appears in the new RuntimePartitionSummary element in RelOp node of the operator in which it is defined. The following example shows the contents of the RuntimePartitionSummary element, indicating that two total partitions are accessed (partitions 2 and 3).
<RunTimePartitionSummary>
<PartitionsAccessed PartitionCount="2" >
<PartitionRange Start="2" End="3" />
</PartitionsAccessed>
</RunTimePartitionSummary>
5/6/2009
I recently saw an article on the "One True Lookup Table".
Code Sample Here (Right-click, Save As works best)
The author makes 2 points.
//
In most cases, neither of the above applies, and responsibility for data integrity resides solely in the application code.
Show me such a database, and I’ll show you some data where the code does not match anything in the lookup table.
//
Well, I can provide a solution that uses a UDF (user defined function) and a check constraint.
And the statement "and I’ll show you some data where the code does not match anything in the lookup table" would not be valid if this solution is employed.
The FK exists to keep integrity.<<(This exists in addtion to the constraint)
And the check constraint makes sure that you only can apply values of the correct "CodeCategory".
(Aka, I cannot put in a OrderStatus value into a column wanting a Country or similar).
The solution is Sql Server specific, but the idea is there.
Keep in mind I use this for ~most tables. I still create a few standalone lookup tables when the cookie cutter does not work.
I am not saying this is the best solution in every scenario. There is a performance issue with validating the check constraint for every INSERT/UPDATE action.
But for bulk data inserts (or updates), you can drop the constraint and readd it if that becomes an issue.
1/27/2009 Here is a sample I wrote that someone requested I post.
Right click HERE and do a "Save As" or "Save Target As".
The sample gets an IDataReader on a Source Datastore (Access/Jet database), populates a strong dataset, then after so many records, ships the DataSet.GetXml() to a sql server stored procedure.
This should run faster than doing a row by row get/insert that alot of people code up.
Yes, there are many ways to do bulk inserts/imports with Sql Server. I chose this one because in my real life code, I need to run the rows through some business rules before importing.
Enjoy.
12/10/2008 SqlServer2005/2008 // OUTPUT clause in INSERT/UPDATE/DELETE statements
These types of samples are all over the place on the web, but here is my original example for which I believe is better clarity.
Original Example(s) at:
http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT-clause.aspx
create table PrimaryHolderTable ( i int identity (1001,2) not null primary key, j int not null unique )
create table #OutputResultsHolder ( i int not null, j int not null)
insert into PrimaryHolderTable (j)
output inserted.i, inserted.j into #OutputResultsHolder
select top 10 o.object_id from sys.objects as o order by o.object_id desc --<< from sys.objects is there just to provide some rows
select * from #OutputResultsHolder
drop table #OutputResultsHolder, PrimaryHolderTable;
go
create table dbo.EmployeeTable ( EmpKey int identity(1001,2) , EmpAge int not null );
create table dbo.AuditTable ( EntityKey int not null default -1 , OldValue int null, NewValue int null , Tag varchar(64) );
insert into dbo.EmployeeTable (EmpAge)
output inserted.EmpKey , null , inserted.EmpAge , 'Employee Inserted' into dbo.AuditTable ( EntityKey , OldValue , NewValue , Tag)
values( 18 );
insert into dbo.EmployeeTable (EmpAge)
output inserted.EmpKey , null , inserted.EmpAge , 'Employee Inserted' into dbo.AuditTable ( EntityKey , OldValue , NewValue , Tag)
values( 20 );
insert into dbo.EmployeeTable (EmpAge)
output inserted.EmpKey , null , inserted.EmpAge , 'Employee Inserted' into dbo.AuditTable ( EntityKey , OldValue , NewValue , Tag)
values( 22 );
update dbo.EmployeeTable
set EmpAge = EmpAge + 1
output inserted.EmpKey , deleted.EmpAge, inserted.EmpAge , 'Employee Updated' into dbo.AuditTable ( EntityKey , OldValue , NewValue , Tag)
where EmpAge <=20;
delete from dbo.EmployeeTable
output deleted.EmpKey , deleted.EmpAge, NULL , 'Employee Deleted' into dbo.AuditTable (EntityKey , OldValue , NewValue , Tag)
where EmpAge > 0;--Test multi rows
select * from dbo.EmployeeTable;--<<will be empty at this point
select * from dbo.AuditTable;
drop table dbo.EmployeeTable, dbo.AuditTable;
go
7/24/2008 This is an oldie but a goodie. If you're doing CURSOR development for Sql Server to solve business problems, then STOP. DOWNLOAD this file (right-click and save-as works best) for an example of non cursor based logic solving. 03 and 04 solve the
same business problem, but 2 different ways.
03 is the cursor
model. 04 is the udf model.
Read the comments, and try to make a decent sized database (row count) so you see the massive and significant time difference.
6/13/2008 Usually I don't post quotes, but this one has gotten enough positive feedback, I guess I'll post it.
The conversation context: A VB6 developer moving to C# and posed the question "Where is the Resume Next in C#?".
Here was my reply:
My VB.NET suggestions:
Exception Handling? (formally know as ErrorHandling)
Take the vb6 mentality of exception handling and do the 4 following things:
1. Put it in a brown paper bag.
2. Take it to someone's house you don't like.
3. Set it on fire.
4. Run away from it as fast as you can.
Check here:
http://blogs.msdn.com/kcwalina/archive/2005/03/16/396787.aspx
(A great post by Krzysztof Cwalina on exceptional handling) I was able to attend Krzysztof's presentation on Reusable Frameworks at TechEd2008, and he mentioned a future version (V2) of his book Framework Design Guidelines. Sweet! 3/4/2008
August, 2008 Update.
I added in SqlServerCE support (version 3.1 for desktop).
Now there is SqlServer, Access, Excel, Text and SqlServerCE.
SqlServerCE was a little tricky at first, but I got it running.
This is an early preview, and I don't have alot of comments or explanation yet.
Download the code HERE (Right Click / Save As works best)
One of my first real jobs my company had to support multiple RDBMS (Relational Database Management Systems).
Sql Server 6.5, 7.0, Sybase, and Access/Jet (Although I don't consider Access/Jet a real RDBMS).
Oh, the countless number of "IF gDataBaseType="Access" then" statements. YUCK!
I had been advising people to use the Factory Pattern for multiple RDBMS support, but finally decided to author a demo.
You'll need Sql Server 2000,2005 or 2008. You don't have to have those, but it helps.
Most important instruction. Read the README.txt file before unzipping.
Again, read the README.txt file BEFORE unzipping.
I'll followup with this at some point, but wanted to get the sneak preview out the door.
..
11/29/2007 If you're using the MembershipProvider (actually the concrete SqlMembershipProvider), then you may have run into an issue where you develop all the Users and Roles on your local machine, using the asp_net.mdf in the Express version of Sql Server 2005. All of the sudden, you need to deploy your application and you need a different database to store your information. You'll probably find this resource for deploying on a different version of Sql Server http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx Ok. You run the aspnet_regsql.exe utility, and you have the correct database, but it is empty. What do you do now? I've created this helper script to enable you to move all your applications/users/roles to a new database. This script doesn't actually INSERT the new data, but creates the TSQL code that you can run on the new database. I've posted the code here (as a text file). Hopefully, it will make your life a little easier when you need to copy to a new database all the Membership information. 10/18/2007 I'll provide more info later, but wanted to get this entry up.
I've recently did a sample application for my local user group.
It's a layered and tiered setup. The "traditional" layers are there, but now you can tier this application thanks to WCF.
Also included is an alternate way to avoid creating proxy classes on the client. I call this the "DotNet to DotNet" method.
Ultimately, this life saving blog entry made this possible:
The sample is complete. You'll have to create a simple Sql Server Database, and run a few .tsql files (3) to have a working example.
A IsOneWay method is also included (aka, using WCF with MSMQ). And an error handling (IErrorHandler) example is provided as well, when a "IsOneWay" message fails.
(Naturally, you'll need MSMQ 3.0 or 4.0 installed for this example to work).
Please look at the root directory README.txt files, it answers most questions about the setup, and where I downloaded Juval's excellent ServiceEx project.
If it helps, please leave a comment. Thanks.
I am currently leading the discussion about StockTrader (msdn.microsoft.com/stocktrader) for my local user group ( www.trinug.org). I am also attempting to integrate my sample application into Greg Leake's (stock trader author) Configuration Framework.
If you haven't seen the StockTrader videos on Channel9/Wiki, check them out.
Now load the example which you can download here. (A "right-click" and "save-as" works best). PS If you need a good reference for getting started (aka, the nuts and bolts of getting WCF working on your development machine), try this: http://www.thoughtshapes.com/WCF/GettingStarted.htm DOWNLOAD NOTE:
If you have not installed WF (WorkFlow) onto your system, you will need to make the following changes:
Open up (in notepad.exe) the following file:
\BusinessLogic\GranadaCoder.Applications.TrinugWCFDemoVersion1.BusinessLogic.csproj
Find the lines below and REMOVE them.
(Line 12)
<ProjectTypeGuids>{14822709-B5A1-4724-98CA-57A101D1B079};{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}</ProjectTypeGuids>
(Line 14)
<Import Project="$(MSBuildExtensionsPath)\Microsoft\Windows Workflow Foundation\v3.0\Workflow.Targets" />
Save the file. Close notepad.exe. Find and open the .sln file. That should work.
I realized after the fact that I had a WF reference in there, and not everyone has WF installed.
5/15/2007 We all know you can do an xml to html conversion. Did you know you can do an xml to xml conversion also? Why? If you ever get data as xml, but you cannot control the formatting, you can alter it into another xml document. Why? One way to use this is to think about a DataSet object. (You're using strongly typed datasets and not loosely typed datasets, right?) If you remember that a DataSet is just some fancied up Xml, you can convert your original xml into one that matches your dataset schema. Create a new strongly typed dataset. Call it MyStronglyTypedDS. Add an "Author" table. Add elements : MyUniqueID, LastName, FirstName, TheBookCount. ( string, string, string, int ) Save your file. Now load the example which you can download here. There are 3 files. myxml.xml, myxsl.xsl, runit.vbs. (The vbs is to keep it simple, but you can easily find the DotNet code to do this). Run runit.vbs. It will create a new file called results.xml. Voila! An xml to xml conversion, which outputs to the matching schema of the (strong) dataset you created above. MyStronglyTypedDS ds = new MyStronglyTypedDS(); MyStronglyTypedDS.ReadXml(@"c:\mypath\results.xml"); Or check this MS KB article: http://support.microsoft.com/kb/311566 The original need I had for this method was that I was getting the same data from customers, however, each customer sent the data just a little differently. They were actually sending excel files. I saved off the excel (as xml) programatically. Then I had one xsl file per customer, that translated the data into a common xml schema. And Boom! When a new customer had a new and different format, all I had to do was write 1 xsl file, and I could use their data, without altering any of my original code (It's kind of an OO concept of Open-Closed-Principal (OCP), but with xsl) (<< How's that for a stretch?). Writing an xsl file for excel/xml is a little trickier, but can be done. See a previous post from me and you can see some of that syntax and namespace issues. 6/19/2006
I've been writing IComparer's for a while, and could never figure out a "clean" way to write one which handled mutliple values to compare against.
One time I tried stringing together the multi values, but that was deficient fairly quickly, when you went to datatypes outside of strings.
( If you sort the numbers 1-15 with a string, you get : 1,10,11,12,13,14,15,2,3,4,5,6,7,8,9, which is not what you want). Throw some DateTime's in the mix, and you see that can get ugly very quickly.
Well, no longer. The following sample shows how to have your cake and eat it too.
The EmployeeComparer can handle strings, times, ints (or others). It can handle 1:N number of sort parameters, where N is probably the overall number of properties you have on your object.
And it only does the work needed, aka, it doesn't run 5 comparing operations when 1 will suffice.
The little trick is to nest some comparisons, in case there is a "tie" ( compare value of 0 ).
The other good news is that this solution is not limited to 1, 2 or 3 levels of comparing values.
The limit is your imagination.
However, the solution does not do unnecessary compares either.
I think the solution is pretty elegant.
The example can sort ASC or DESC. However, it sorts ASC and DESC for the entire set of SortColumns.
I'm working on my own solution for this. (Just a wrapper object to hold the SortColumn and the direction).
But I will leave that as an exercise for the reader.
Here is the downloadable example. (Right Click and "Save Target As" is your best bet). This is 1.1 code, fyi. 6/5/2006 I've taken the 1.1 example (Custom Objects and Tiered Development below), and translated it into a 2.0 example, to take advantage of generics.
Most of the translation of my 1.1 example to 2.0 came from knowledge gained from his article.
The link to the original location of the article is below.
There is not that much different between the 1.1 example and 2.0 example, except the use of Generics.
For example:
[Serializable]
public class Customer
{
private string m_customerId;
private string m_contactName;
private string m_city;
private List<BusinessObjects.Order> m_orders = new List<BusinessObjects.Order>();
public Customer()
{
}
public Customer(string custId)
{
this.m_customerId = custId;
}
public Customer(string custId, string contactName, string city)
{
this.m_customerId = custId;
this.m_contactName = contactName;
this.m_city = city;
}
public string CustomerID
{
get { return this.m_customerId; }
set { this.m_customerId = value; }
}
public string ContactName
{
get { return this.m_contactName; }
set { this.m_contactName = value; }
}
public string City
{
get { return this.m_city; }
set { this.m_city = value; }
}
public List<BusinessObjects.Order > AllOrders
{
get { return this.m_orders; }
set { this.m_orders = value; }
}
public int CompareTo(Customer cust )
{
return this.CustomerID.CompareTo(cust.CustomerID );
}
// Special implementation to be called by Customer comparer
public int CompareTo(Customer cust, Comparers.CustomerComparerType comparisonType)
{
switch (comparisonType)
{
case Comparers.CustomerComparerType.CustomerId :
return this.m_customerId.CompareTo(cust.CustomerID );
case Comparers.CustomerComparerType.ContactName :
return this.m_contactName.CompareTo(cust.ContactName);
case Comparers.CustomerComparerType.City :
return this.m_city.CompareTo(cust.City );
}
return 0;
}
}
OrderCollection (1.1) has been replaced with
public List<BusinessObjects.Order >
The code sample can be downloaded Here. (Right Click , Save As probably works best)
References:
Also see the references in the 1.1 example below. 5/24/2006
Recently I was involved in a discussion on creating BusinessObjects, BusinessObject Collections, and where one should keep the code to create the objects.
In a N-tiered system, you want to create seperate tiers. While most people can quote the name of the tiers, understanding them and actually keeping them seperated is the fall short area.
I have created a sample which has a:
Presentation Tier ( a Console App )
Business Tier.
DataLayer Tier.
I am piggy-backing off the NorthWind database for my example.
I have created 2 strong objects (Customer and Order), and 2 Collection objects (CustomerCollection and OrderCollection).
I have also created a CustomerController object to handle the creation of Customer's and Order's.
The CustomerController is a business object, which calls the DataLayer object, and gets an IDataReader to populate the data.
(Sql Server 2000/7.0/2005 scripts are included in the download as well).
I am also using the DAAB 2.0 "SQLHelper" class to assist with the datalayer object. While I use the EnterpriseLibrary project now in my production code, the SQLHelper is good for this demo, as it is Sql Server (2000 usually) specific. It also provides an easier mechanism for implementing the connection string property.
However, it points out the fact that I should be able to move from one DataHelper (SQLHelper) to another (EnterpriseLibrary.Data) and *never* have to touch/recompile the
Presentation Code or the
BusinessLogic Code
In fact, you could switch from Sql Server to Oracle, and never have to recompile these 2 upper tiers (again, Presentation and BusinessLogic should be isolated from the database vendor of choice)
Hopefully the sample will provide an illustration to avoid common mistakes.
Those mistakes are:
1. Intertwined business layer logic and database connectivity.
2. Substituting the SQLHelper class ~in place of~ the application's DataLayer object. The SQLHelper (as the name implies) is there to assist/help the DataLayer object, not replace it.
3. Keeping objects (their properties, methods and sometimes events) seperate from the code which creates them.
I show how to create a sub collection of Orders per Customer, and how to accomplish this with one database call. (IDataReader.NextResult();)
I show how the DataAccessLayer should be simple, and pretty much return only data.
My rule of thumb is that:
The DataLayer object should return:
IDataReader's
DataSet's (typed and untyped)
Scalars ( count(*) is an example here)
void/Nothing (aka, you just call a stored procedure which does something, but does not return anything)
That's it. That's what the DataLayer should return.
The obvious reason is that any RDBMS or datastore should be able to return DataSet's and IDataReaders. That's the goal, to provide data to the business layer without having intricate knowledge of the backend database.
..
As a bonus, I include some time-test code to compare:
Custom Objects/Collections being populated with an IDataReader
Typed DataSet object, with Constraints enabled.
Typed DataSet object, with no Constaints (in the xsd definition)
You can view the results.
In regards to Custom Objects/Collections vs Typed DataSet's vs other methods:
Like in most areas of software development, there are not "blanket statements" for which method to use.
There are pro's and con's, and it depends.
I usually go with the CustomObjects/Collections.
I jokingly refer to DataSet's as the "poor man's business object". However, there are times when they make good sense.
You can download the example code here. ("Right Click"/"Save Target As" is the best approach I think)
I am working up a DotNet 2.0 example also, where I replace the CollectionBase objects with generics. Look for that code soon.
References:
And a reference to read from start to finish, aka, very informative for a bird's eye view:
2/8/2006
How many times have you written something like this:
<appSettings>
<add key="smtpserver" value="smtp.myisp.com"/>
</appSettings>
And then, when you need to send email, you read the value from the config file, and write code to send email.
Perhaps you deploy to a web hosting company, and realize they have a different smtp authentication routine.
Then you go back to your code, and make adjustments, so you can send from your local setup, and then your hosting company as well.
So you put together something for the 1.1 Framework. Then 2.0 comes out, and you repeat using the new email object in 2.0.
1.1 Framework uses the
System.Web.Mail.MailMessage
object.
Then along comes 2.0, and thank goodness they clean up sending emails.
2.0 uses the
System.Net.Mail.MailMessage
object.
After looking at the 2.0 library, I realized it would be much cleaner to seperate the code which instantiates these 2 objects, and the smtp server settings.
I basically have created a custom configuration section handler, to encapsulate most/all of the different settings one might need to send Smtp Emails.
The best part of this custom handler, is that I can reuse it for both 1.1 and 2.0 email sending.
I've also included a 1.1 sample project, and a 2.0 sample project.
Let's take a quick look at the App.config (or Web.config) file.
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="EmailSettingsSectionName" type="GranadaCoder.Email.Settings.EmailSmtpSettingsHandler,GranadaCoder.Email.Settings" />
</configSections>
<EmailSettingsSectionName defaultEmailFrom="donotreply@donotreply.com" portNumber="25">
<!--You need to add a reference to GranadaCoder.Email.Settings.EmailSmtpSettingsHandler.dll since this dll lives outside this (Presentation) assembly-->
<!--Comments can go here-->
<!--SSL example. Google(gmail)Mail is a good (free) example of this. Naturally, you need to provide a legitimate username and password-->
<!--Note, with the 2.0 Framework, my tests show that gmail likes port 587-->
<smtpServer enabled="true" smtpServerName="smtp.gmail.com" defaultEmailFrom="donotreply@gmail.com" portNumber="465" authenicationMode="SSL" smtpUserName="mygmailaddress@gmail.com" smtpUserPassword="mygmailpassword" executeOrder="3"/>
<!--Basic authentication. Passing in a username (and sometimes a password) are used here.-->
<smtpServer enabled="true" smtpServerName="smtp-server.nc.rr.com" defaultEmailFrom="donotreply@rr.com" portNumber="25" authenicationMode="basic" smtpUserName="myemail@rr.com" executeOrder="2"/>
<!--None authentication. Nothing but the smtp-server name is provided-->
<smtpServer enabled="false" smtpServerName="smtp.noauthenticationneeded.com" authenicationMode="none" executeOrder="1"/>
</EmailSettingsSectionName>
</configuration>
I have all 3 scenarios here.
(No) authentication.
Basic authenticaton.
SSL authentication.
You'll also notice I have a Collection of SmtpServers. Why? One way I use this class is to have back-up email senders. In case the first smtp server is down, I use the 2nd (or the 3rd or 4th) email to send the email. If the first one is down, I also send an admin email saying "The primary smtp server was down". This is why I included the 'executeOrder' attribute. But that is outside the scope of this blog.
The config section makes quick work of deploying my solution to a hosting or production server.
By seperating the setup information from the 1.1 or 2.0 code to send emails, your 1.1 or 2.0 code becomes cleaner.
I also like the Collection of SmtpServers idea. Using <app key values> means you get one smtp server to use.
Even with 2.0:
<system.net>
<mailSettings>
<smtp from="your@email.com">
<network host="smtp-server.rr.com" password="" userName="myaccount@rr.com" />
</smtp>
</mailSettings>
</system.net>
It looks like a 1 smtp server setup.
You can download the code HERE. (Right-Click and "Save As" works best)
The code is in 1.1, except for the code in the TestApp_2_0 directory. There are 2 solution files, one in 1.1 (VS 2003) and one in 2.0 (VS 2005).
For 1.1, you will want to open up the:
\Settings\TestApp_1_1\GranadaCoder.Email.Settings.TestApp.sln
solution. (In VS 2003 of course.)
For 2.0, you will want to open up the:
\Settings\TestApp_2_0\TestApp_2_0.sln
solution file. (In VS 2005 of course.)
(Note, the 2.0 solution does not refer to the GranadaCoder.Email.Settings.dll file "By Project", so you may need to re-add the reference (as in... browse/file...add reference) to get it to work.
This solution offers these advantages:
Encapsulation and seperation of the Smtp Server Settings from the code to send an Email through DotNet.
(Optionally) a Collection of SmtpServer, to allow for a backup plan, if the primary smtp server is unavailable.
Easy setup through a config file.
The solution will not work out of the box. You need to put in your smtp server settings. You also need to change the To address in the Console.Application code. And don't forget the <smtpServer enabled="false"> setting, which needs to be "true" if you want that smtp-server to be considered.
Even if you don't need the smtp server, you can use the project to learn how to write a customized Configuration Handler.
Also included are:
IComparer, to handle different attributes/properties to sort by.
CollectionBase object, and a Sort method using your custom IComparer 12/16/2005 While not cutting edge OO programming, I've instructed people so many times how to do this, that I decided to add it to the blog.
MSDE is a "free" and "lite" version of Sql Server 2000. It has a few articial govenors in it, and a few features disabled. However, for most developers, it is a great way to get the Sql Server 2000 engine.
The great thing about it, is that any code (tsql) written for MSDE will work on Sql Server 2000. It is much much better than going from Access to Sql Server.
Go Here to get to the download. Or try www.microsoft.com/sql and search for "MSDE".
You will download the file. Once downloaded, you will run the exe and it will decompress the files.
Let's assume you extract the files to c:\wutemp\msde\
The trick with the install is that running the "setup.exe" file (in c:\wutemp\msde\ folder) won't really do anything.
The program installs from the command line prompt. Here are some short cuts.
Inside the c:\wutemp\msde\ folder, create 2 new files.
InstallMSDEWithMyParameters.bat
MyParameters.INI
The contents of InstallMSDEWithMyParameters.bat will be:
setup /settings "MyParameters.ini" SAPWD="sapassword" /L*v C:/MSDELog.log
The contents of MyParameters.ini will be:
[Options]
DISABLENETWORKPROTOCOLS=0
SECURITYMODE=SQL
You can read more about the options in the ReadmeMSDE2000A.htm file (which is a part of the extraction process)
Now, double click the InstallMSDEWithMyParameters.bat file, and it will be off to the races.
You'll notice that your sa password will be "sapassword". You can alter this if you'd like, but make it something you can remember, because you're dead in the water without it.
I'd reboot after the install, so the MSSQLSERVER services will start up.
Now you have a functional, local, legal development server.
You main tool of trade will be osql.exe. Google it, and you'll find more info.
While MSDE is free, Microsoft makes you jump thru some hoops to use it. They really want you to buy the full version. But the hoops are much better than using Access for any decent sized relational database.
12/1/2005 There are many sites out there showing examples of the Simple Factory Pattern.
This blog entry takes the simple examples a little further by giving 3 different ways to make the decision on which Concrete Object is returned.
While I have experience with all 3, the use of reflection seems to be the most powerful one.
Alot of times, you may be using this pattern (and specifically the #3 option) without really knowing it.
The Enterprise Library from Microsoft is one very well known Framework. The DataAccess objects actually are using reflection to determine which concrete Database object (Microsoft.Practices.EnterpriseLibrary.Data.Database) to use. The Microsoft.Practices.EnterpriseLibrary.Data.Database is an abstract class, and the concrete class is determined by some string settings in the dataConfiguration.config file. (Look closely at the line
<databaseType name="Sql Server" type="Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase, Microsoft.EntLib.Data" />
That's reflection giving back a concrete version of a Microsoft.Practices.EnterpriseLibrary.Data.Database object)
Here is another link, discussing the ASP.NET 2.0 Provider Model.
So what are the 3 methods?
1. Using a "key". Alot of web samples use this method. The example here uses a string letter to give back a concrete IAnimal.
2. Use the Environment. I have to thank a previous colleague for showing me this one. Well, he actually showed me the Factory Pattern to begin with. So there's some dap for him.
This example will return a different concrete class based on Web Environment or a Winforms environment.
3. Using reflection. You can setup the concrete class in a configuration file. The code will use the Interface or Abstract Class, but you can switch out the functionality with just a tweak of a config file.
This is very flexible, and really keeps your options open for future development.
In fact, the concrete class can live in its own assembly, just as long as it implements the Interface or Abstract class. My example shows how you can switch out to a different "RateQuoter" (the mock example uses PostalService and UPS to give different shipping rates). The switch can occur without recompile, and only a few tweaks of a configuration file.
Thanks to all my colleagues with their mad OO skilllzzzz.
Here's some code snipplets, the download will give you a complete C# code sample to show you how to better use the Factory Design Pattern.
As bonus material, you'll also see a:
Web version of the Singleton Pattern.
A configuration handler, for encapsulating information in your App.config or Web.config file.
#1 Key Method
public class AnimalFactory
{
public static IAnimal GetAnimal(string key)
{
switch (key.ToUpper())
{
case "B":
return new Bird();
case "C":
return new Cat();
case "D":
return new Dog();
default:
throw new ArgumentException("The AnimalFactory was given a Bad Key");
}
}
}
#2 Environment Method
public class ObjectHolderFactory
{
private ObjectHolderFactory()
{
}
public static IObjectHolder GetObjectHolder( )
{
//This is a perfect example of a Simple Factory need.
//When in a non web arena, the singleton is a simple
//Hashtable or HybridDictionary object
//When I am in the web arena, I need to piggyback off the
//web Session object, aka, the WebSessionObjectHolder
//uses session variables to store the singleton
//Singleton
if ( null == System.Web.HttpContext.Current ) // Add a reference to System.Web.dll
{ //Non Web Environment
return InMemoryObjectHolder.GetInstance();
}
else
{ //Web Environment
return WebSessionObjectHolder.GetInstance();
}
}
}
#3 Reflection
public class RateQuoterFactory
{
private static readonly string CONFIG_SECTION_NAME = "RateQuotersSectionName";
private RateQuoterFactory()
{
}
public static IRateQuoter GetARateQuoter()
{
//The RateQuoterSettings encapulates the information found in the App.Config file
//The "Handler" reads the xml ... to create a RateQuoterSettings instance.
RateQuoterSettings settings = ((RateQuoterSettings)System.Configuration.ConfigurationSettings.GetConfig(CONFIG_SECTION_NAME));
//Now you have a RateQuoterSettings instance.
//use the assembly and class name from the RateQuoterSettings instance. to dynamically create the object
return CreateInstance( settings.AssemblyName, settings.ClassName , settings.ShippingCompanyHomeState );
}
private static IRateQuoter CreateInstance( string assemblyName, string className , string homeState)
{
IRateQuoter returnObject = null;
Assembly assem = Assembly.Load( assemblyName );
if(null != assem)
{
Type objectType = assem.GetType( className , true , true );
//The use of "homeState" is here... to show how the CreateInstance can have non default constructors.
//Notice the second argument is an array of objects.. the array of objects ... needs to match one of the contructor-method-signatures
returnObject = (IRateQuoter)Activator.CreateInstance( objectType , new object[] {homeState} );
}
return returnObject;
}
}
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="RateQuotersSectionName" type="GranadaCoder.Applications.FactoryPatternExamples.FactoryWithReflection.ConfigurationLib.RateQuoterHandler,GranadaCoder.Applications.FactoryPatternExamples" />
</configSections>
<RateQuotersSectionName>
<rateQuoterObject enabled="true" assemblyName="GranadaCoder.Applications.FactoryPatternExamples.ConcreteObjectsOutsideBaseAssembly"
className="GranadaCoder.Applications.FactoryPatternExamples.ConcreteObjectsOutsideBaseAssembly.ConcreteRateQuoters.UPSRateQuoter" ShippingCompanyHomeState="NC"/>
</configuration>
So there's the summary. Check out the code, and see for yourself.
Right click HERE and click 'Save As'.
|
Tools 
Help
