Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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
Edited 13-Jan-13 11:02am
v2
Comments
David_Wimbley at 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 at 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 at 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 at 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 at 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 at 13-Jan-13 18:24pm
   
I appreciate ur help , tahnks !
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Here is sample in sql management objects on creating a database/copying schema and data of a source db.
 
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.
  Permalink  
Comments
JamesB28 at 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 at 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 at 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 :)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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.
 
//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
 
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.
  Permalink  
v2

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Sergey Alexandrovich Kryukov 397
1 OriginalGriff 350
2 thatraja 205
3 Emre Ataseven 165
4 Shai Vashdi 150
0 Sergey Alexandrovich Kryukov 8,894
1 OriginalGriff 5,200
2 Peter Leow 3,955
3 Maciej Los 3,535
4 Abhinav S 3,208


Advertise | Privacy | Mobile
Web02 | 2.8.140415.2 | Last Updated 13 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid