Best way to access a SQL Server database using C# .Net

Here is an example using EF with code generation from the database (for a real app you probably want to generate your DB from the code, instead):

1. Right click on your project >> Add >> New Item >> ADO.NET Entity Data Model.
2. Pick a name for your entities i.e. MyEntities.edmx, click Next
3. Select "Generate from database"
4. Configure a 'New Connection' if there is not one already there. Next.
5. Select the Tables, Views, and SPROCs you want included in the entities. Finish.

You will see a file MyEntities.edmx added to your project. You can open it in design view to see a diagram of your entities and relationships. Note that each entity should have a primary key - the easiest way to do this is to add an ID - auto increment field to each table, or a GUID column. Anyway now you can query your db like this:

// assuming a "Product" table, which has an entity pluralized to "Products"

MyEntities db = new MyEntities();

var cheapProducts = db.Products.Where(p => p.Price > 30); // var is IEnumerable<Product>

In my opinion the best solution is to create intermediate class between the db and application (some type of data layer), which a number of methods for manage queries. Description below base on SQLite as an analogy to SQLServer (ADO connector)

Mentioned class can by singleton, and you can call it instance wherever you want in your application - for SQLite it can looks like that:

private static SQLite instance;

public static SQLite getInstance()
{
   if(instance == null)
   {
    instance = new SQLite();
    thread = Thread.CurrentThread.ManagedThreadId;
   }
   return instance;
}

You can get the instance this way:

SQLite db = SQLite.getInstance();

This class can contain multiple methods for data manipulation for example:

public SQLiteCommand prepareQuery(string sql)
{
   cmd = new SQLiteCommand(sql, conn);
   return cmd;
}

public SQLiteDataReader executeReader()
{   
    return cmd.ExecuteReader();
}

public int executeInt()
{
   object obj = cmd.ExecuteScalar();
   return (int)obj;
}

but also transaction manage and diagnostoc methods. So now - you can use this-like class in you application if you have other db sources or even db types, you can create next data-layer (for example for Oracle or MSSQL, MySQL ...) each of which has implements the same interface for example:

IDataBase

and now, you have some sort of facade, which you can replace as needed dynamicly. From this time using db in application is concentrated in one place, and it is pure pleasure for programmer to use it - that's my suggestion.