Create an SQL database programmatically c#

Question

Is it possible to create an SQLite database programmatically in C# - by coding.

I was using an XML to store information I needed in my application but it has gotten to the stage where the amount of data being read from the XML is such that the performance of the application is suffering.

As I understand it SQLite databases can be read from and added to faster using the provided System.data.Sqlite functionality.

Is this correct?

[edit] In addition I should mention i'm doing all this in unity 3d with Monodevelop.

Why SQLite and not SQL CE or SQL Server Express? Why programmatically? — JotaBe
– JotaBe, Commented Mar 30, 2012 at 12:54
Programmatically because the user needs to be able to create and add through the applications GUI. SQLite because it was what I came across when seeking an alternative to XML. Do you recommend one of the other choices? — Dave
– Dave, Commented Mar 30, 2012 at 13:00
SQL CE is free, very lightweiht and supported by MS. Some systems, like Umbraco, use it as backend DB. Making a DB is as easy as making it with SQLite. — JotaBe
– JotaBe, Commented Mar 30, 2012 at 19:11

user1096188 · Accepted Answer · 2012-03-30 13:12:44Z

2

I believe you don't even need to explicitly create an SQLite database to work with it, just put its name in a connection string and it will be automatically created when you create a table in it.

answered Mar 30, 2012 at 13:12

user1096188

1,84912 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dave Over a year ago

I see, at the moment when trying this I am told that I am using an invalid connection string. I've tried googling but nothing seems to work. Can you advise?

Dave Over a year ago

fixed it. Unity hadn't linked properly so copied the dll files i needed into asset folder.

JotaBe Over a year ago

If you want to have a good support for .NET Framework technologies, and a ligthweight DB, and are used to T-SQL, managing DBs with SSMS, using technologies like Linq To SQL or Entity Framework, you should use SQL CE. If you want a DB not very well supported by MS, for which you nedd to find tools for managing and using those technologies, then use SQLite.

sblom Over a year ago

@JotaBe, I don't think he's going to be able to run SQL CE everywhere Unity3d runs...

JotaBe Over a year ago

According to his new edit, of course he can't use SQL CE because it's not managed code, but native. In this case, SQLite is the way to go.

Massimiliano Peluso · Accepted Answer · 2012-03-30 12:54:30Z

1

you could use code-first feature in Entity framework: the 4.0 capable providers also add DeleteDatabase/CreateDatabase/DatabaseExists functionality.

more info at:

http://blogs.msdn.com/b/adonet/archive/2010/12/14/ef-feature-ctp5-code-first-walkthrough.aspx

answered Mar 30, 2012 at 12:54

Massimiliano Peluso

26.8k6 gold badges65 silver badges75 bronze badges

Comments

JotaBe · Accepted Answer · 2012-03-30 23:10:31Z

-1

Use SQL Server CE.

You can create a database file like this:

Create a SQL Server Compact Edition Database with C#

I'd use this because is "official" MS database. So it will work fine with MS technologies (Linq to SQL, Entity framework, etc.) It's also a very lightweight DB. Take into acount that there's also available SQL Server Express, but that's not such a lightweight database.

Code to create database:

  string connectionString;
  string fileName = “ArcaneCode.sdf”;
  string password = “arcanecode”;

  connectionString = string.Format(
    “DataSource=\”{0}\”; Password=’{1}’”, fileName, password);

  SqlCeEngine en = new SqlCeEngine(connectionString);
  en.CreateDatabase();

Distributing it only requires copying a few small assemblies (for example the installer for SQL Server CE 2005 it's a 1.7Mb .msi file which results in 7 assemblies) Zero configuration. And there are versions which can be used with .NET framework 2.0, 3.0, 3.5, 4.0 (version SQL Server 2005 up to SQL CE 4.0).

If someone can't see the adavantage of being supported by MS, so that using it with C# and otherMS technologies, and having a good documentation on MSDN.

I suppose that I got downvotes because someone is allergic to MS. If not, let me know why.

edited Mar 30, 2012 at 23:10

answered Mar 30, 2012 at 19:09

JotaBe

39.3k9 gold badges102 silver badges121 bronze badges

2 Comments

reiniero Over a year ago

The OP asked for creating SQLite dbs, not MS SQL CE dbs. Using sqlite may even be a requirement for interoperability with other programs, or the only feasible option when using it on OSX/Linux etc.

JotaBe Over a year ago

The title of the question asks for the way of creating a "SQL database". Fully generic question. The OP proposes the use of SQLite as a solution, but SQL Server CE was a good, lightweight and easy to use option until he added he is using Monodevelop. So, well, the answer doesn't apply any more after his edit to the question, but the content of this answer still applies to the title, to the original question, and is well explained and a working solution. Anyway, that's my opinion and you can do whatever you want with your negative votes.

Collectives™ on Stack Overflow

Create an SQL database programmatically c#

3 Answers 3

5 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related