C# Local database, I need some examples

Question

Hello , I'm making an app that will be installed and run on multiple computers, my target is to make an empty local database file that is installed with the app and when user uses the app his database to be filled with the data from the app . can you provide me with the following examples :

1. what do I need to do so my app can connect to its local database

2. how to execute a query with variables from the app for example how would you add to the database the following thing

   String abc = "ABC"; String BBB = "Something longer than abc "

and etc Edit :: I am using a "local database" created from " add > new item > Local database" so how would i connect to that ? Sorry for the dumb question .. i have never used databases in .net

Thanks in advance , Nikola

Answer 1

For 1)

The easiest way to provide this functionality is through SQL Server Express User Instances. SQL Server Express is free, so your user does not have to pay additional license for SQL Server, and the User Instance is file-based, which suits your requirement.

For 2)

This is a big topic. You may want to go through some of the tutorials from Microsoft to get the feeling of how to connect to DB, etc.

Answer 2

Seems like you're:

• -Making a C# app that will be installed and run on multiple computers
• -That needs a local database (I'm assuming an RDBMS)
• -You need to generate a blank database at installation
• -You then need to be able to connect to the database and populate it when the app runs.

In general, it seems that you need to read up on using a small database engine for applications. I'd start by checking out SQLite, especially if you need multi-OS capability (eg., your C# program will run on Microsoft's .NET Framework and Novell's Mono). There are C# wrappers for accessing the SQLite database.

Answer 3

What are you considering as a database? From what little you've provided in your question, I'd suggest SQLite.

You can get sample code from their site Sqlite.NET

Answer 4

Not sure I fully understand what you're asking but Sqlite is a good option for lightweight, locally deployed database persistence. Have a look here:

http://www.sqlite.org/

and here for an ADO.NET provider..

http://sqlite.phxsoftware.com/

Answer 5

I'm not seeing anybody suggesting SQL Compact; it's similar to SQLite in that it doesn't require installation and tailors to the low-end database. It grew out of SQL Mobile and as such has a small footprint and limited feature-set, but if you're familiar with Microsoft's SQL offerings it should have some familiarity.

SQL Express is another option, but be aware that it requires a standalone installation and is a bit beefier than you might need for an applciation's local cache. That said it's also quite a bit more powerful than SQL Compact or SQLite.

Answer 6

Depending on the needs you could also consider Sql CE. I'm sure that if you spesified the database you're thinking of using, or your requirements if you're usure you would get proper and real examples of connection strings etc.

Edit: Here's code for SqlCe / Sql Compact

    public void ConnectListAndSaveSQLCompactExample()
    {
        // Create a connection to the file datafile.sdf in the program folder
        string dbfile = new System.IO.FileInfo(System.Reflection.Assembly.GetExecutingAssembly().Location).DirectoryName + "\\datafile.sdf";
        SqlCeConnection connection = new SqlCeConnection("datasource=" + dbfile);

        // Read all rows from the table test_table into a dataset (note, the adapter automatically opens the connection)
        SqlCeDataAdapter adapter = new SqlCeDataAdapter("select * from test_table", connection);
        DataSet data = new DataSet();
        adapter.Fill(data);

        // Add a row to the test_table (assume that table consists of a text column)
        data.Tables[0].Rows.Add(new object[] { "New row added by code" });

        // Save data back to the databasefile
        adapter.Update(data);

        // Close 
        connection.Close();
    }

Remember to add a reference to System.Data.SqlServerCe