Best practices for SQL data access

Hi,

i am newbie in ASP.net world. i am using 3 tier application architechture for my web based application. data base is sql server 2000. i have looked at object and sql datasource objects but i think they are not suitable for my requirements. so i am planning to directly use ado.net to access data from database.( i.e. creating connection, then creating commands n executing them)

now what i am looking for is the best known practices for the above task. i have following solutions in my mind please let me know if i am missing some or which could be the best aproach.

careate one class which will handle all the database requests so that all the pages and business objects request that class to to do all the db related stuff. (creating connection, command n execution)

have a class which will return connection to your page or business object and then u can use that connection to do db related stuff.

some thing in between that you create a sqlcommand and pass it to a class which will take care of connections and execute you request.

what i am worried about more is the connections to database and the connection pooling n sharing stuff. i dont have any idea how they works.

please help me in this regard

thanks

Have you looked at the Data Access Application Block?

http://msdn2.microsoft.com/en-us/library/aa480458.aspx

|||

thanks i had a look through it, n it sound goods.

any other thoughs or ideas???

|||

aakbar:

thanks i had a look through it, n it sound goods.

any other thoughs or ideas???

Yes, follow those suggestions there...

|||

Outside of that, if was using ADO, I'd create a Business Logic class for each of the tables in my database using this class to generate all of the business logic which in turn would then call the ADO function within the DAL which would be your Data Application Blocks.

Best Practice for Counts/Distinct Count Measures for Filtered Data

Let's say the Count/Distinct Count measure is based on filtered data.

When the situation allows, is it better to do a CASE statement with 1s and 0s from the table in the DSV and then do a SUM on this field? Or is it better to create a new Named Query in the DSV filtering out the data through the WHERE clause based off of the original table and then do a DISTINCT COUNT measure on the newly created Named Query?

If there's no clear cut answer as to what is the "better" approach, please offer up the advantages and disadvantages for both. My preference is best performance.

An Example:

Measure: Active SKU Count
Products dimension has product_key, SKU and Status

In the DSV for the Products dimension, should I add a CASE statement:

CASE WHEN [Status] = 'Active' THEN 1 ELSE 0 END AS [Active Status]

Then create a new SUM measure based on the Active Status column.

OR

Create a new Named Query in the DSV:

SELECT * from Products WHERE Status = 'Active'

Then create a new DISTINCT COUNT measure based on the SKU column from the new Named Query?

These 2 alternatives do not produce the same results. If a given SKU has fact records in Jan, Feb, and Mar, the first approach using SUM would return a value of 3 for Calendar Quarter 1. Then second approach based on a distinct count measure would return 1 (which is probably what you want)

Distinct count measures should be implemented in their own measure group anyway, so if you only ever need the active count then filtering out the non-active records is a good option.

If you need to get distinct counts of both active and inactive SKUs then you would be better off adding the Active flag as an attribute to the product dimension and implementing it as a slowly changing dimension.

|||Darren,

I had a feeling that these 2 alternatives would not produce the same results when I was typing up the question. Perhaps my example scenario wasn't a good one, because the limited testing with my applicable situation yielded the same results.

In any event, I will just go ahead and go forward with the separate measure group with the new Named Query approach.

Thanks!

Best parctices in SSRS 2000 reports development

Any ideas or known approaches on how to better develop reports. I have
about 30 Crystal Reports based on stored procedures. Now I need to develop
SSRS 2000 reports. What is the best way to achieve this in terms of layout
development? Is there any kind of a style sheet or templates in SSRS 2000 ?
( many reports look alike, and most of them are just tables, with no charts
or any other complicated structures). From what Iâ've known so far ( I
developed quite a few reports in RS), Iâ'd develop it one by one , changing
all color/font settings for each report individually. Is it possible to do
it in a more sophisticated way?
Any response/ideas would be really appreciated
LenaI'd reccomend figuring out how to do this with SQL Server 2005 instead
of SQL Server 2000.
2005 is a very nice product
Lena wrote:
> Any ideas or known approaches on how to better develop reports. I have
> about 30 Crystal Reports based on stored procedures. Now I need to develop
> SSRS 2000 reports. What is the best way to achieve this in terms of layout
> development? Is there any kind of a style sheet or templates in SSRS 2000 ?
> ( many reports look alike, and most of them are just tables, with no charts
> or any other complicated structures). From what I've known so far ( I
> developed quite a few reports in RS), I'd develop it one by one , changing
> all color/font settings for each report individually. Is it possible to do
> it in a more sophisticated way?
> Any response/ideas would be really appreciated
> Lena

Beginning Balance Query

Hello all,
I would like a total (beginning balance) field in a query be the total for
the year up to the month based on another field within the query, so that
when the fiscal_date = '5/1/2006' that I would have a field contain totals
from '1/1/2006' thru '4/30/2006'. I would like to do this in one query, I am
trying to populate a table with 6 years of data that would have a beginning
balance field and then todays current amount based off of a Fiscal_Date fiel
d.
My problem is I cannot figure out how to do a sum of prior data based off of
another fields current date. Am I trying to do too much in one query to
populate the table?Lyners wrote:
> Hello all,
> I would like a total (beginning balance) field in a query be the total for
> the year up to the month based on another field within the query, so that
> when the fiscal_date = '5/1/2006' that I would have a field contain totals
> from '1/1/2006' thru '4/30/2006'. I would like to do this in one query, I
am
> trying to populate a table with 6 years of data that would have a beginnin
g
> balance field and then todays current amount based off of a Fiscal_Date fi
eld.
> My problem is I cannot figure out how to do a sum of prior data based off
of
> another fields current date. Am I trying to do too much in one query to
> populate the table?
>
Without DDL, I don't know your actual table/field names, but something
like this should be close:
SELECT
Table2.FiscalDate,
(SELECT SUM(Amount) FROM Table1 WHERE DATEPART(year, DateField) =
DATEPART(year, Table2.FiscalDate) AND DATEPART(month, DateField) <
DATEPART(month, Table2.FiscalDate)) AS FiscalAmount
FROM Table2|||Thank you Tracy,
Yes, I did something close to this. What I ended up doing was create 2
tablkes that were identical, and then used a cross join where one of my
criteria is that a.date < b.date, then in the select statement I added a
sum(a.amount). This way I sum all a.amount that is less than b.date.
This way I have all of the b table fields and the total of the amount from
the a table all together.
Thank you,
Lyners
"Tracy McKibben" wrote:

> Lyners wrote:
> Without DDL, I don't know your actual table/field names, but something
> like this should be close:
> SELECT
> Table2.FiscalDate,
> (SELECT SUM(Amount) FROM Table1 WHERE DATEPART(year, DateField) =
> DATEPART(year, Table2.FiscalDate) AND DATEPART(month, DateField) <
> DATEPART(month, Table2.FiscalDate)) AS FiscalAmount
> FROM Table2
>|||Why would you create a second identical table when a VIEW would most likely
have worked -without the issues of keeping 2 tables syncronized?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Lyners" <Lyners@.discussions.microsoft.com> wrote in message
news:7FA08953-C425-43CD-A198-3CBC08EA229E@.microsoft.com...
> Thank you Tracy,
> Yes, I did something close to this. What I ended up doing was create 2
> tablkes that were identical, and then used a cross join where one of my
> criteria is that a.date < b.date, then in the select statement I added a
> sum(a.amount). This way I sum all a.amount that is less than b.date.
> This way I have all of the b table fields and the total of the amount from
> the a table all together.
> Thank you,
> Lyners
> "Tracy McKibben" wrote:
>|||Good point Arnie. I actually just created a large query that has 2 identical
select statements. The second select statement I total the amount field base
d
on the cross join where clause. I am in the design phase of this report, so
in the future, I will most likely create a view (faster) of the select query
and just reference that instead of requerying everytime.
Thanks for the performance pointer.
"Arnie Rowland" wrote:

> Why would you create a second identical table when a VIEW would most likel
y
> have worked -without the issues of keeping 2 tables syncronized?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Lyners" <Lyners@.discussions.microsoft.com> wrote in message
> news:7FA08953-C425-43CD-A198-3CBC08EA229E@.microsoft.com...
>
>

Beginner question Searching table

I have a stored procedure that takes 5 parameters. Based on what parameters are passed i want to return a result set to the calling code. Not all of the parameters are required so there will be some combinations on the entered parameters.

Do i need to dynamic build the query with the IF statements to check if the input parameter is NULL or not ?

Thanks

You can use the isnull function like so:

-- Using the northwind database in sql server 2k

declare @.categoryname varchar(100)
declare @.categoryID varchar(100)

--set @.categoryname = 'Produce'
set @.CategoryID = 4

select * from categories
where isnull(@.categoryname, categoryname) = categoryname
and isnull(@.categoryID, categoryID) = categoryID

|||For additional reading on this subject, check this article for a full description of was to do this: http://www.sommarskog.se/dyn-search.html|||

Hi,

Instead of running the below one

select * from categories
where isnull(@.categoryname, categoryname) = categoryname
and isnull(@.categoryID, categoryID) = categoryID

Running this one may be faster

select * from categories
where (@.categoryname is null or categoryname = @.categoryname)
and (@.categoryID is null or categoryID = @.categoryID)

Since during the execution plan creation the engine may decide a better way since it knows that "@.categoryname is null" is true for every record. But a similar approach is also valid for the first one too. Actually this is related with how wise the sql server engine is.

Eralper

http://www.kodyaz.com

|||

Thanks all for helping. It works great now and it runs fast for me. Thanks so much for you help.

Guess i need to start reading up on my T SQL.

|||

The query optimizer doesn't quite optimize the conditions using the multiple variables like you described. The problem is that the value of only @.categoryname or @.categoryid will be fixed at compile time depending on the first execution and the plan primed into the cache will not be that efficient for the other invocations. Erland's article that Louis posted goes through lot of techniques and their various pros/cons. The recompilation whitepaper below is also a good read on how plan caching works:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Before Delete Trigger?

I have a database that will be used as the back end of a distributed application that holds information based on application users. The application needs to log on and see if there are updates for the user. My current thoughts on this are that the application will log in and check a Date column named [modified] in the users table (I am not worried about what individual changes have occured, but more if anything has changed). To implement this I have now put Insert, Update triggers that use the tables relationships to track which users need to be updated on the tables that need to be watched... they look something like this:

CREATE TRIGGER MOD_UP_INS_GROUPS
ON dbo.Groups
FOR INSERT, UPDATE
AS
SET NOCOUNT ON
DECLARE @.IDVar1 as int
SET @.IDVar1 = (SELECT GroupID FROM inserted)
UPDATE Users
SET Modified = GetDate()
WHERE (AccountName IN
(SELECT DISTINCT dbo.Users.AccountName
FROM dbo.Groups INNER JOIN
dbo.GroupUserDetail ON dbo.Groups.GroupID = dbo.GroupUserDetail.GroupID INNER JOIN
dbo.Users ON dbo.GroupUserDetail.AccountName = dbo.Users.AccountName
WHERE (dbo.Groups.GroupID = @.IDVar1)))

This appears to be working great... however the Delete Trigger is where my problems start... I can not use the above trigger (with deleted in place of inserted) because it appears the delete action takes place prior to the Delete Trigger and with the referential deletes, etc. The path to the user is lost before I can track it with the delete trigger. Is there a way to make a BEFORE DELETE TRIGGER... or any other thoughts would be helpfull.

Thank You,
KentDo Instead of Trigger.|||Not sure I'm following...but you can code an INSTEAD OF trigger...look it up in BOL...|||I tried a Instead of Trigger but I had the following error:

Cannot ALTER INSTEAD OF DELETE or UPDATE TRIGGER 'MY TRIGGER NAME' on table 'dbo.Group' because the table has a FOREIGN KEY with cascaded DELETE or UPDATE.

Doing a little reading I have found I can't define this trigger on tables with foreign key relationships with cascading deletes.

The other problem I thought of with this type of trigger... is how do you let an outside programs sql delete requests continue with this trigger?|||Well...can you simply explain what your goal is, in business terms...

I'm having a hard time seeing what you're trying to do..

Also, you got other problems

SET @.IDVar1 = (SELECT GroupID FROM inserted)

You do know that inserted may have many rows...so doing that will give you the last value in the result set...|||First of all thanks for your replies:

The Database is the backend of a Client-Server Application that assigns Startup Scripts for Company Programs to individual users within the company (Based on Windows Login Names that are stored in the users table). It does this in a method similar to the Windows Server Environment where you assign individual Company Programs to a Group then assign a group to a user or a user to a group.

The front end that the users see needs to be able to connect to the database and determine if anything has changed, and if so, update itself with the new settings whether it be the user has been added\deleted from a group or if an actual Company Program Startup Script has changed.

My attempted solution to this problem was to create a modified column in the users table. When a table is modified that effects a user or users the modified column for the user or users in question would be updated with the current date (getdate()). The front end then accessess the database and compares it's last updated date with the date in the users.modified column to determine if it needs to update. With the update, Insert Triggers I have been able to accomplish this very nice. However, the delete trigger causes problems because lets say a group is deleted... the group is deleted then referential updates delete the users who where assigned to that group in a groupdetails table then I am unable to track which users need to be modified...

The front end does not contain a database... but rather stores items in an ini file and the registry, therefore replication is not an option. The other thing is clients are not always connected to the network so the settings are stored on the local machine for the individual users.

I hope this is clearer... thanks again.

BDE Connection via ODBC to MS SQL Server fails

Hi,

I have a strange problem accessing a MS SQL Database from my network.
I'm using an application that is based on BDE which uses ODBC to connect to a MS SQL DB.
When I tried to connect from the network with BDE,I got this error message:

BDE Error : 13059
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).

On the server side I'm using W2003 EE ,MS SQL 2000 Standard with SP4, BDE5.11.
On the client side it's an XP SP2 with MS SQL Client installed, same BDE 5.11
When I create an alias using ODBC and mapping to my server, it connects and the communication with the Database Server succeds:
TESTS COMPLETED SUCCESSFULLY!
When I'm trying to connect using BDE Admin and double clicking on the + sign of the alias I created usind ODBC admin I get the message I listed above.
Any help would be appreciated,

Thanks.May check this http://support.plato.com/kb/tip.asp?psid=23252 link fyi.|||Thank you for replying me...in the meantime i solved the problem.
It was the incorrect settings in the registry for BDE. After erasing the keys from the registry and having reinstalled the BDE everything worked fine.
There are some files used by BDE when connecting to SQL server which are not installed when using Delphi 5 or later :(

However thank you for the usefull link.

best regards,
Sanyi|||Glad the issue is resolved and appreciate your feedback on posting the solution, that helps.