changing database file at runtime in c# and MSSQL

Question

2.33/5 (3 votes)

See more:

SQL-Server-2005

Hi Sergey Alexandrovich

I think i am not able to explain. let me put it in this way

i have a financial application developed in c#.net and MSSQL server. now suppose my database file name is dbfa2012.mdf. ihe client uses this database for year 2012 and after one year i make a fresh blank database dbfa2013.mdf for year 2013. so i want to have a interface where the client can select database dbfa2012 or dbfa2013 of his choice and work

thanks in advance

Pradeep

Posted 2-Aug-13 18:11pm

PRADEaZX

Updated 4-Aug-13 9:13am

v2

Add a Solution

Comments

Zoltán Zörgő 4-Aug-13 15:44pm

Why are you addressing yourself to SA? Are you not interested in other people's opinion?

PRADEaZX 5-Aug-13 21:24pm

its not like that. you will understand if you read SA;s comment

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sergey Alexandrovich Kryukov · Answer 1 · 2013-08-02T18:47:00

Solution 2

Bad, really bad idea. Even if you don't need any integrity now, in future you may run into problem. Don't work with separate databases. Just don't.

—SA

Posted 2-Aug-13 18:47pm

Sergey Alexandrovich Kryukov

Comments

MuhammadUSman1 3-Aug-13 1:11am

I think Best suggestion.
—SA

Sergey Alexandrovich Kryukov 3-Aug-13 1:15am

Thank you, Muhammad.
—SA

Zoltán Zörgő 4-Aug-13 15:43pm

I would agree in general, but I since we speak about SQL Server - having specific mechanisms under the hood - I have to disagree. See my answer.

Sergey Alexandrovich Kryukov 4-Aug-13 17:16pm

May I ask you what difference can it make, exactly?
—SA

Zoltán Zörgő 5-Aug-13 14:52pm

Of course. But let me highlight one single aspect: financial year. A regular business activity is highly bound to the financial year. For the sake of simplicity let us assume, that in year 2013 the company will want to access the 2013 year's data in 90% of the cases, the 2012 years's data in 7% of the cases, and 2% the 2011 year, and only 1% the years before 2011. But you have to keep 10 years of data as archive because of the regulation. Why keeping all the data of the previous years in the database? Of course, you can export it in some format, and import if you need it, or keep it and use the selection to see only what you need. Yes, even SQL 2005 could deal with quite large databases, so theoretically, should not be any problem to have terabytes of data. But as it grows, it will be harder to maintain, even harder to fulfill regulatory requirements. And a financial year is a closed something. It is better to keep it as it is, without interfering with other years's data. So, if you can take your 2012 year data as a closed entity and put it on a DVD (or anything else), and than simply attach it if you need it, you gain a lot.
Of course, there are drawbacks one have to deal with: like what if you need to query several years? Of course, this can be also handled.
But my point is: you can choose to have a single database, but if you like, you can, you may use several databases for the one application. And SQL Server will support you in this, even better it's architecture is made to support such scenarios. The instance is made to be allocated to an application, so if you can make use of this, why shouldn't you? It is not that bad as you pointed out.
But I wouldn't do this with across SQL Server instances or with MySQL for example, even if both are also supported: juts because it is out of the conceptual scope of the respective RDBMS.

Sergey Alexandrovich Kryukov 5-Aug-13 16:01pm

Harder to maintain and so on?... Well, then it comes to the question if the previous years of data are stored at all or discarded forever. If you have to store them, you store them anyway. Where? Why not in the database. More exactly, the final decision depends on ultimate purpose and requirements. However, I can see you point now.

I only though that you are going to tell about difference between SQL Server and other systems, "specific mechanisms under the hood". What are they?

—SA

Zoltán Zörgő 5-Aug-13 16:37pm

Ah, those. Well, I could but I can't tell you about low level things, but all the conceptual view of SQL Server is based on the instances. The databases/catalogs are just a level of abstraction below the instance, and this was kept from versions 2000 on. An instance is not just a bunch of databases, it has several things bundled: server side automations, external links, security objects, and so on. These are all made to support a single application (in it's whole extent).
It is not standard, I admit, but the algorithms are just built to support the transparency and performance one needs if working within such a mixture of objects.
In Oracle for example, the instance has some other level of concerns, thus doing cross database statements necessitates links and the other party is handled as "unsafe, uncontrollable", even if they are "siblings" on the same server. So cross-database statements are reparsed, it needs more resources, is considerably slower, and so on.
It is juts that people are treating SQL server architecture as other RDBMS servers, mixing several applications under the same instance, and thus loosing the benefits. The licensing is also made to support that: you don't license instances, you license clients/machines/cores.

Sergey Alexandrovich Kryukov 5-Aug-13 17:15pm

I see... Thank you for your useful considerations.
—SA

**Zoltán Zörgő** · Answer 2 · 2013-08-04T09:41:00

Actually the idea itself is not that bad - but depends on the application. Ever wondered what is the model database is all about? Well, Microsoft is using an unique view about the database server. Many RDBMS use the database as top level concept related to an application. Microsoft has an ever higher level: the instance. An instance of an SQL Server is intended to be dedicated to an application. The model database[^] is the template for newly created databases. So practically, if you create a good template and dedicate the instance to the application, you can follow your current concept, and simply use SMO to enumerate databases[^].
But I have to emphasize, that this is a very special way to do it, based on Microsoft's own vision. In SQL server it is nothing bad in distributing your data between databases (catalogs) of the same server instance, they work transparently. It is better using this approach than allowing some tables to grow beyond a usability limits - even indexing has it's limitations.
If you take this path, I suggest you create a separate database for the data that is not year-related, like partner data and so on.

An other approach, you can use to partition your year related tables, take a look here: http://databases.about.com/od/sqlserver/a/partitioning.htm[^].

Choosing the best approach necessitates deeper knowledge about the application architecture, data model and business logic.

Code-Hunt · Answer 3 · 2013-08-02T18:14:00

Solution 1

configuring-connection-string-in-app-config-file-during-runt/[^]

how to change connection string at run time in c#[^]

Check the links..hope it will help...

Posted 2-Aug-13 18:14pm

Code-Hunt

Comments

PRADEaZX 3-Aug-13 0:24am

Thanks Code-hunt
will check and get back

[no name] 3-Aug-13 0:25am

Sure..and if it helps then accept the answer so that it will be helpful for others also..

changing database file at runtime in c# and MSSQL

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us