CODEWITHSUNDEEP
sqlsql-servervisual-studio-2010c#
Tony Wu
Tony Wu

Reputation: 329

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

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");

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

Upvotes: 9

Views: 10512

Answers (4)

Sorin Comanescu
Sorin Comanescu

Reputation: 4867

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

Upvotes: 8

Denis Biondic
Denis Biondic

Reputation: 8201

SQLite is great to use too, fast, lightweight ...

Upvotes: 3

Stanislav Berkov
Stanislav Berkov

Reputation: 6287

Yes, there is http://en.wikipedia.org/wiki/SQL_Server_Compact for such purpose.

Upvotes: 3

spender
spender

Reputation: 120380

You'll want to look at SQL Server Compact Edition

Upvotes: 2

Related Questions