How to embed/attach SQL Database into Visual C#?

Question

This is the first time I've used SQL and this is probably a stupid question but I've done some research and I don't think I'm finding what I'm looking for.

What I want is a way to make a private SQL database that will be used within my C# program. I've already made a database within SQL Server Express and I've connected that to Visual Studio 2010.

SqlCommand DBAccess = new SqlCommand();
DBAccess.Connection = new SqlConnection(
    "Data Source = localhost;" +
    "Initial Catalog = My_Database;" +
    "Trusted_Connection = True");

• Can I make the Data Source embedded into my program and will be compiled along with the rest of the solution?

Some extra background on the program;

It's a program that needs to search through 6 tables within the database and output the contents of a DataRow when search string matches a particular field.

EG.

Field 1     Field 2
quick       AA
brown       AA
fox         AB
jumps       AB
over        AA
the         AB
lazy        AB
dog         AA

Search_String = AB

Outputs;

fox
jumps
the
lazy

Any help will be much appreciated!!!!!

Thanks

Answer 1

Just for getting a grip (VS 2010):

1. Create a console project
2. Add a reference to System.Data.SqlServerCe (in Program Files\Microsoft SQL Server Compact Edition\v3.5\Desktop\System.Data.SqlServerCe.dll on my computer)
3. Right click the project node in Solution Explorer, select "Add => New Item...", choose "Local Database", name it MyDB
4. A new file MyDB.sdf will be added to the project (a MS SQL Server Compact Database)
5. Right click the new file, click "Open", the database will be open in "Server Explorer"
6. In "Server Explorer" expand MyDB.sdf, right click Tables, "Create Table" (name it MyTable)
7. Add two columns "Field1" and "Field2" (leave them nvarchar(100) for now)
8. Right click the new table, choose "Show Table Data", fill in your data

The code:

using System.Data.SqlServerCe;

namespace ConsoleApplication6
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var cn = new SqlCeConnection("Data Source=MyDB.sdf"))
            {
                cn.Open();
                using (var cmd = cn.CreateCommand())
                {
                    cmd.CommandText = "select * from MyTable where Field2 like '%AB%'";
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine("Field1: {0}", reader[0]);
                        }
                    }
                }
            }
            Console.ReadKey();
        }
    }
}

Will output fox jumps the lazy.

BUT, I would go with SQlite for simple purposes. Wrapper is here: http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki