Last post Feb 22, 2007 04:29 PM by smphelan
Feb 15, 2007 09:04 AM|smphelan|LINK
I'm newbie to ado and asp development and am perplexed how to proceed with my application... Based on which user is logged in I want to decide which databases are used and I'm not sure of the best way to move forward. I would like to use 1...n number of
databases where the structure of each database( and corresponding tables) is the same only the data differs.
So my first question is what is the prefered method to accomplish this... simply use connection strings stored in a database and then use that to connect to the data. Or alternatively would it be better to simply duplicate the tables within one database
using some unique identifier in the name of each table.
The second question is that I would like to use things like the TableAdapter/objectdatasource/gridview controls but they seem to want to hardcode in which database they connect when you select the tableadapter to use in the objectdatasource so how would
you load the correct data.
Thanks for your help
Feb 16, 2007 05:09 AM|justSomeone|LINK
If the structure of youe DBs are the same and only the data differs, why dont you have a table that userID and use this to filter the data rather than going to a 2nd DB
Feb 16, 2007 11:11 AM|smphelan|LINK
Thanks for your reply. I thought about doing it that way(and I still might), but it didn't quite seem right because each of my users is actually a different customer who themselves have customers on the site and so there data is private to them. And the
customers come and go, not frequently, but regularly so it seemed like a good reason to split the data among different databases so that when one leaves you just delete that database and poof they are gone. Then the system doesn't have to hash through each
and every database removing entries for the deleted customer.
It also lets me do certain operations on a database knowing that it only affects one client, but maybe thats just not worth the hassel of implementing a site with multiple databases?
Feb 17, 2007 06:34 AM|justSomeone|LINK
Feb 19, 2007 12:10 PM|smphelan|LINK
No, I don't think that will work(Not that it wouldn't work but it doesn't sound like what I want to do). I want to use the same code for all the customers so I don't want to have a bunch of instances of the same code on the server which would make changes
and updates difficult. I also want to make the adding of a customer as easy as clicking a few buttons and filling in a few text boxes for my customer.
So, is connecting to mulitple databases at runtime just not done? If so what are the reasons?
I guess what I'm worried about is that having to do a where AccountNumber='12345' in my code everywhere will become cumbersome versus just using a different database that only contains that accounts data. Does that help any?
Thanks for you help!!
Feb 19, 2007 11:50 PM|Kevin Yu - MSFT|LINK
Actually, there are many approaches to achieve this.
1. You can split the information to several databases.
2. You can assign the database Table to different users. There are users in the SQL Server database, and you can make tables like username.TableName.
3. User your application to control the accessability of certain data. All the data for different users are kept in the same table. But each record has a column to indicate which user it belongs to. When query, use a WHERE clause to filter them out.
HTH. If this does not answer you question, please feel free to mark the post as Not Answered and post your reply. Thanks!
Feb 20, 2007 08:56 AM|smphelan|LINK
Thanks for your help.
In regards to #2. Where you have tables with different names is it possible to create a stored procedure that accepts something like an Account number as an argument and then uses that to select say accountNumber.TableName. If so, how would one do that
because I've tried and been unsuccessful trying to use a parameter in the database name?
Feb 20, 2007 11:52 PM|Kevin Yu - MSFT|LINK
You can have a VARCHAR parameter as the username. In the stored procedure, you can use this username to form a SELECT query. The query is concatenated as a string. Then just execute it with EXEC.
Feb 21, 2007 09:17 AM|smphelan|LINK
So what would the syntax be for this select statement. I guess thats what I'm struggling with. Say I have two tables one is Tasks_123 the other is Tasks_321.
So I would want to do something like
From Tasks_ @AcctNumber
but of course that doesn't work... so what is the correct syntax to do that?
Feb 22, 2007 02:20 AM|Kevin Yu - MSFT|LINK
Try to use something like
DECLARE @AcctNumber as NVarChar(50)
DECLARE @SQL AS NVarChar(50)
SET @SQL='SELECT * FROM Tasks_' + @AcctNumber
Feb 22, 2007 04:29 PM|smphelan|LINK
I tried creating a stored procedure(below) to do what you suggested Kevin. I was able to create the sp but when I tried to run the sp I get error:
Could not find stored procedure 'SELECT TaskID,Name,Description FROM Tasks_123'.
why is it trying to run that line as a stored procedure call rather than just the SQL?
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE GetTasksByAccount
-- Add the parameters for the stored procedure here
@AcctNumber nvarchar(50) = 1
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SQL AS NVarChar(50)
-- Insert statements for procedure here
SET @SQL = 'SELECT TaskID,Name,Description FROM Tasks_' + @AcctNumber