CODEWITHSUNDEEP
c#asp.netsqlsql-server
Shaggy
Shaggy

Reputation: 5790

Retrieve data from all database with same table schema

I have created ASP.NET C# Web Application (with Database as SQL Server 2008). My Application Connects to Different databases (Say Count is 10). they all are have same structures i.e. Same Table Name,Stored Procedure Name,SP Argument Count & Type,Table Schema all are same.

But data inside is different.

I have stored procedure inside every database dbo.usp_getData1 which accepts some parameters do some table scans and retrieve data with respect to only that database.

My Question is How Can i Retrieve Data from all my Database using only one of the stored Procedure (As Parameters/Table Structure is same) ?

I can achieve same either by SQL Server or C# Coding.

Views will be helpful for me ? 

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] 
[ WITH <view_attribute> [ ,...n ] ] 
AS select_statement 
[ WITH CHECK OPTION ] [ ; ]

<view_attribute> ::= 
{
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]     }

Upvotes: 1

Views: 1226

Answers (2)

Frederiek
Frederiek

Reputation: 1613

You could use linq to sql and generate a dbml file.

And then you can acces every database with adjusting the connectionstring when initializing the datacontext:

MyDataContext firstDB = new MyDataContext(someConnectionstring);
MyDataContext secondDB = new MyDataContext(someOtherConnectionstring);

Upvotes: 0

Andre Loker
Andre Loker

Reputation: 8408

You can select from multiple databases (on the same server) if you fully qualify the table name like Database.Schema.Table.

See e.g. http://www.sqlteam.com/article/selecting-data-from-different-databases

If you want to pass the database names as a parameter to your stored procedure, you'll probably have to resort to dynamic SQL, although this could potentially open a whole new can of worms.

See e.g. SQL 2000 Table Name as variable in stored procedure and http://www.sommarskog.se/dynamic_sql.html

Simple example:

CREATE PROCEDURE SelectEverything
    @tableName NVARCHAR(250)
AS
BEGIN
    DECLARE @sql NVARCHAR(500)
    SET @sql = 'SELECT * FROM ' + @tableName;
    EXEC(@sql);
END
GO

If you execute this proc:

EXEC dbo.SelectEverything 'SomeOtherDatabase.dbo.SomeTable'

It will select everything of table SomeTable in schema dbo of database SomeOtherDatabase.

But please read the link about dynamic SQL.

Upvotes: 1

Related Questions