How to add a database table by code in VB.net

Question

0.00/5 (No votes)

See more:

Hi All,

I have developed a project on VB.net with SQL server compact edition 4.0 as a database and deployed it in many machines, but now I am going to provide an update to the application which has more tables in database.

The issue here is that my clients have their data in database and i dont want them to loose it. But if i kept the same database in backend so my new update will not work on that database i need to add table to their existing database to allow my application to work on their system.

I am willing to add new tables to database in setup project, means when my client runs the setup the tables are added to their existing database.

I think so the question above is well clear and understandable, so please help me.

Thank you in advance.

Posted 22-Feb-15 3:29am

Kashif Alvi

Updated 22-Feb-15 3:33am

v2

Add a Solution

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)

Wendelius · Answer 1 · 2015-02-22T03:35:00

Solution 1

If I understood correctly, one possibility is that your app adds the missing tables when starting. To add a table is basically the same as for example updating a row, you simply execute a SqlCommand. Only this time you have a CREATE TABLE statement in the CommandText.

The person who is issuing the command just needs to have proper privileges in order to create the table (dbo for example).

Posted 22-Feb-15 3:35am

Wendelius

Comments

Kashif Alvi 22-Feb-15 9:38am

Yes i thought about that solution but i want to add a table while installing the application. Is there any way to do this by editing my setup project.

Wendelius 22-Feb-15 9:42am

What setup mechanism/program you use?

Kashif Alvi 22-Feb-15 9:45am

Visual studio setup project

Wendelius 22-Feb-15 9:52am

In that case have a look at Extending Visual Studio Setup Project[^]

OriginalGriff · Answer 2 · 2015-02-22T03:38:00

Since you are using Compact edition, the way I'd go is to provide a complete new database, and transfer their data over, rather than trying to add anything to the existing file. That way, if there is any problem in the update, the customer doesn't lose anything.

In theory, adding a table programmatically to a DB shouldn't cause any problems anyway: it's just a case of issuing the CREATE TABLE command:

C#

string sql = "CREATE TABLE NewTable (LastName NVARCHAR(40) NOT NULL, FirstName NVARCHAR(40) NOT NULL, EmailAddress NVARCHAR(256))";

And that won't affect existing data - but Databases can be delicate, as can the people who depend on them! I'd create a new DB completely, and make sure they are kept happy.