ADO.NET many Sessions or many Databases with the same structure in one application and the "Save as " menue

Question

4.50/5 (2 votes)

See more:

Hi guys ! Im developing a desktop application ( sales management software )using Vb.net , ADO.NET and SQL Server 2008 .

Well I have 2 Problems :

Problem 1-

Im using one database but My client needs to create many 'FOLDERS' or ' SESSIONS ' , that means MANY EMPTY DATABASES with the same structure of the main Database with different names .

Here is a quick scenario : The user opens the software ,then he is asked to choose a folder in witch he wants to work on if it is already created. Else he should create a new folder with new name and password . so here he will start working on a new empty copy of our database .HE could then create as many FOLDDERS as he needs And So on .. many Folders or could be called SESSIONS would be created . Its seems like creating Sessions in The MS Windows operating system . So How can I do that ?

Problem 2-

In the same Context , Now I want Get a meune named '' SAVE AS ... '' witch gives me of course to choose a Directory and name of the file . I know how to do it , But whats the code to get a copy Of my current Database , so I could save many different copies when I Update my Database .

Please ~~Its URGENT~~ I need your help , I will appreciate your recommendation I will give more information if you will . Thaks alot !

Posted 13-Jan-13 10:58am

JamesB28

Updated 13-Jan-13 11:02am

Abhishek Pant

v2

Add a Solution

Comments

David_Wimbley 13-Jan-13 17:04pm

Your going to need to take a look at SQL management objects in .net to create databases like your saying for one.

JamesB28 13-Jan-13 17:15pm

Hi David , Thanks for your answer . So in this case shall I change the whole code of my application ? or just adding some imports and replacing some codes here and there ? Cause I have never used the SQL Management Objects.

David_Wimbley 13-Jan-13 17:25pm

Can you provide some code samples so i can see what your working with and then i can be in a better position to provide a recommendation. I merely said SQL management objects since that is fairly easy to create databases at will and seems to be more geared to what it seems you are attempting to do.

JamesB28 13-Jan-13 17:37pm

Well I think my Question wasn't Clear . Well I will Ask it in an other way : How can I make a Copy of the structure of my database (empty database) or even a copy of this database with inserted data VIA ado.net . Because my Client will not create a new database using MS SQL Server and then provide it to my Desktop Application . I want make it programaticcaly in my application not manually . Th e question could be also : How to create a database using ADO.NET ? I hope Its clear now , thanks

David_Wimbley 13-Jan-13 18:21pm

Ok i think i have an idea, ill throw together something and submit it as solution once done...itll be a minute or two

JamesB28 13-Jan-13 18:24pm

I appreciate ur help , tahnks !

2 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)

David_Wimbley · Accepted Answer · 2013-01-13T13:10:00

Solution 2

Here is sample in sql management objects on creating a database/copying schema and data of a source db.

C#

Server srvr = new Server("(local)");
//Set your source database
Database db = srvr.Databases["BugTracker"];
//Create transfer class for source DB
Transfer transfer = new Transfer(db);
transfer.CopyAllObjects = true;
transfer.DropDestinationObjectsFirst = true;
transfer.CopySchema = true;
transfer.CopyData = true;
//Set Destination server in case its not same as source
transfer.DestinationServer = "(local)";
transfer.CreateTargetDatabase = true;
//Set destination database
Database destDB = new Database(srvr, "BugTrackerCopy2");
destDB.Create();
transfer.DestinationDatabase = "BugTrackerCopy2";
transfer.Options.IncludeIfNotExists = true;
transfer.TransferData();

Once done you should see your new database created with all tables and data from source database. This is to answer part 2 of your question.

Posted 13-Jan-13 13:10pm

David_Wimbley

Comments

JamesB28 13-Jan-13 20:01pm

Hi David. Thanks for the two solutions . Now I see what you were talking about when you mentioned SQL management objects. I see clear now ,you gave me the motivation to go learn more about it , because I use only ADO.NET when I deal with databases.
In the first Solution in ADO.NET Code you gave the query : string query = "CREATE DATABASE YourDatabaseHere"; This gives an Empty Database without tables . Ten Could I copy all the script That I used to create my tables and their Relationships and All in following queries ? I think Its logically possible but Just to be Sure. Anyways I liked the sql management objects Part But I'm Not experimented in this . I will have more questions, Now I consider your solutions and I go to work on it .. thanks again

David_Wimbley 13-Jan-13 20:13pm

No problem, happy to help.

Here is a good link for SMO: http://msdn.microsoft.com/en-us/library/ms162169.aspx

TO answer your question yes, if you had all the necessary scripts you needed in sql you could use ado.net to create/update and move whatever data you may need.

Without seeing your database im assuming you either have or know how to generate the necessary scripts to populate the database after you run the "CREATE DATABASE YourDatabaseHere".

In my opinion i think trying to do what your doing using scripts is going to be more cumbersome than SMO but it just matters what your comfortable with.

JamesB28 13-Jan-13 20:42pm

Yes , I think so , I'm going to take my time learning about SMO ; I will need it in my next projects. So thanks for the link its helpful. Thanks for your time I appreciate it. See u soon then with an other problem or an extension of the same question :)

David_Wimbley · Accepted Answer · 2013-01-13T12:35:00

In order to create a database in c# you can use sql management objects

Youll need the following DLLs referenced in your project for the below snippet.

Microsoft.SqlServer.Management.Smo

This is dll can be found here: C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies

Here is a very basic create databse using SMO.

C#

//This uses my default (local) instance, specify a server in the Server constructor
Server srv = new Server();
Database db = new Database(srv, "DavidsDB");
db.Create();

Or for ADO.NET you should be able to do the following

C#

string ConnectionString = String.Format(@"Data Source = {0};User Id={1}; password={2}; Initial Catalog = {3};",
                            ConfigurationManager.AppSettings["DB_Server"],
                            ConfigurationManager.AppSettings["DB_User"],
                            ConfigurationManager.AppSettings["DB_Pass"],
                            ConfigurationManager.AppSettings["DB_Name"]);
 
string query = "CREATE DATABASE YourDatabaseHere";
 
SqlConnection connection = new SqlConnection(ConnectionString);
connection.Open();
SqlCommand queryCMD = new SqlCommand(query, connection);
queryCMD.Connection.Open();
queryCMD.ExecuteNonQuery();

Let me know if you have any other questions.

Also i see in your question that you want to be able to take a backup copy of your database everytime you update it. You have a lot of options here, i guess the question is how are you wanting to do this. You could use powershell scripts, sql management objects or some pre-determined sql scripts that you just run every time you update.