What is the most straightforward way to retrieve data from a local SQL Server database?

Question

I don't need/want any security measures (username/password, etc.) - I just want to create a simple example of retrieving data from the AdventureWorks "lite" database (AdventureWorksLT2012_Data.mdf), storing that data in a generic list.

I have the following code to query an MS Access database. Would it be basically the same except for the connection string and query statement?

public SQLServerPOCRepository()
{
    using (var conn = new OleDbConnection(
        @"Provider=Microsoft.ACE.OLEDB.12.0;User ID=User;Password=Pass;Data Source=C:\SWin\DATA\SDAT42.MDB;Jet OLEDB:System database=C:\SWin\Data\wgeg.mdw"))
    {
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = "SELECT td_duckbill_accounts.dept_no, IIF(ISNULL(t_accounts.name),'No Name provided',t_accounts.name) AS name FROM t_accounts INNER JOIN td_duckbill_accounts ON t_accounts.account_no = td_duckbill_accounts.account_no ORDER BY td_duckbill_accounts.dept_no";
            cmd.CommandType = CommandType.Text;
            conn.Open();
            int i = 1;
            using (OleDbDataReader oleDbD8aReader = cmd.ExecuteReader())
            {
                while (oleDbD8aReader != null && oleDbD8aReader.Read())
                {
                    int duckbillNum = oleDbD8aReader.GetInt16(0);
                    string duckbillName = oleDbD8aReader.GetString(1);
                    Add(new Platypus { Id = i, dbillNum = duckbillNum, Name = duckbillName });
                    i++;
                }
            }
        }
    }
}

The Add() method populates a generic list with an instance of the model Platypus class.

Answer 1

You need to update your connection string to use the System.Data.SqlClient provider, along with Integrated Security.

<add name="AdvWorksLT_ConnectionString" 
    connectionString="AttachDBFilename=C:\MyApplication\AdventureWorks.MDF;Integrated Security=True;User Instance=true" 
    providerName="System.Data.SqlClient"/>

Also, you need to use a SqlConnection, SqlCommand and SqlDataReader instead of the OleDbConnection, OleDbCommand, and OleDbDataReader.