Config File Connection String Syntax for SQL Express Databases

When creating an application that uses a SQL Express database, you may choose to use a database that exists locally within your application (i.e. the SQL Express database resides in the App_Data directory of your application) or you may prefer to use a database that has been registered with the SQLExpress Server (i.e. you have attached the database to your SQLExpress Server from within Microsoft SQL Server Management Studio – aka SSMS).

Depending on where your database exists, you must use the correct Connection String for your application to make a successful connection to your database. Typically (and preferably), I like to place my Connection String information in the App.Config or Web.Config file. Here is the correct Connection String XML tag syntax for the two different database locations that were previously mentioned.

<configuration>
<connectionStrings>
    <!-- This is an example of a connection string for a SQL Express database that lives in the application's App_Data directory -->
    <add name="LocalConnection" providerName="System.Data.SqlClient" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|Database1.mdf;Integrated Security=True;User Instance=True" />

    <!-- This is an example of a connection string for a SQL Express database that lives in the SQL Express Server's list of registered (or attached) databases. Attaching a database to the SQL Server is typically done via SQL Server Management Studio (SSMS) -->
    <add name="ServerConnection" providerName="System.Data.SqlClient" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=DATABASE1;Integrated Security=True" />
</connectionStrings>
</configuration>

C# Code Examples of using the different Connection Strings

public class DAL
    {
        public static List&lt;UserModel&gt; GetUsers()
        {
            List<UserModel>; users = new List<UserModel>();
            
            // This example uses the connection string that points to the database that resides in the local App_Data directory of this application
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalConnection"].ToString()))
            {
                SqlCommand cmd = new SqlCommand("SELECT * FROM [User]", conn);
                conn.Open();
                SqlDataReader sdr = cmd.ExecuteReader();

                while (sdr.Read())
                {
                    UserModel user = new UserModel();
                    user.EmailAddress = sdr["Email"].ToString();
                    user.Password = sdr["Password"].ToString();
                    user.UserId = (int)sdr["UserId"];
                    user.Username = sdr["Username"].ToString();
                    users.Add(user);
                }
            }

            return users;
        }
    }
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s