SQL Server Express Local Database Server

[jussij]

SQL Server Express makes it easy to create a local database server for SQL development.

The SQL Server Express installation includes the SqlLocalDB.exe utility which can be used to create a database server instance using the batch commands shown below:

Code: Select all

REM Create an instance of local database instance called LocalDBApp1
SqlLocalDB.exe create LocalDBApp1

REM Start the local database instance 
SqlLocalDB.exe start LocalDBApp1

REM Gather information about the local database instance 
SqlLocalDB.exe info LocalDBApp1

Using SSMS you can connect to that database server using this server connection:

Code: Select all

(localdb)\LocalDBApp1

And you can connect to a database found on that sever using a connection string. For example, connecting to the master database the following connection string can be used:

Code: Select all

Data Source=(localdb)\LocalDBApp1;Database=master;Integrated Security=True

And the following C# code shows how to use that connection string to retrieve data from that master database :

Code: Select all

using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;

namespace DatabaseApplication
{
    static class Program
    {
        [STAThread]
        static void Main(string[] args)
        {
            try
            {
                var server = @"(LocalDB)\LocalDBApp1";
                var database = "master";
                var connectionString = $"Data Source={server};Database={database};Integrated Security=True";

                Console.WriteLine($"ConnectionString: {connectionString}");

                using (var con = new SqlConnection(connectionString))
                {
                    var query = "SELECT TOP (10) [object_id] " +
                                     ",[principal_id] "        +
                                     ",[schema_id] "           +
                                     ",[parent_object_id] "    +
                                     ",[create_date] "         +
                                     ",[modify_date] "         +
                                     ",[is_ms_shipped] "       +
                                     ",[is_published] "        +
                                     ",[is_schema_published] " +
                                     ",[type] "                +
                                     ",[type_desc] "           +
                                     ",[name] "                +
                                "FROM [sys].[all_objects]";

                    var cmd= new SqlCommand(query, con);

                    cmd.CommandType = CommandType.Text;
                    con.Open();

                    var sb = new StringBuilder();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                sb.Length = 0;
                                for (int i = 0; i < reader.FieldCount; ++i)
                                {
                                    var value = reader[i].ToString();
                                    sb.Append((i < 10) ? value : value.PadRight(25, ' '));
                                    sb.Append(" ");
                                }
                                Console.WriteLine(sb.ToString());
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                string message = ex.Message + "\n\n" + ex.StackTrace;
                Console.WriteLine(message);
            }
        }
    }
}

Which will produces this output:

Code: Select all

ConnectionString: Data Source=(LocalDB)\LocalDBApp1;Database=master;Integrated Security=True
-1073624922  4 0 19/07/2021 4:09:23 PM 19/07/2021 4:09:23 PM True False False P  SQL_STORED_PROCEDURE      sp_MSalreadyhavegeneration
-1072815163  4 0 19/07/2021 4:09:15 PM 19/07/2021 4:09:29 PM True False False P  SQL_STORED_PROCEDURE      sp_MSwritemergeperfcounter
-1072667163  4 0 19/07/2021 4:08:34 PM 19/07/2021 4:08:34 PM True False False X  EXTENDED_STORED_PROCEDURE sp_drop_trusted_assembly
-1072372588  3 0 19/07/2021 4:08:58 PM 19/07/2021 4:08:58 PM True False False V  VIEW                      TABLE_PRIVILEGES
-1071944761  4 0 19/07/2021 4:09:06 PM 19/07/2021 4:09:06 PM True False False X  EXTENDED_STORED_PROCEDURE sp_replsetsyncstatus
-1070913306  4 0 19/07/2021 4:09:11 PM 19/07/2021 4:09:13 PM True False False P  SQL_STORED_PROCEDURE      sp_replshowcmds
-1070573756  4 0 19/07/2021 4:09:07 PM 19/07/2021 4:09:08 PM True False False P  SQL_STORED_PROCEDURE      sp_publishdb
-1070030802  4 0 19/07/2021 4:09:08 PM 19/07/2021 4:09:25 PM True False False FN SQL_SCALAR_FUNCTION       fn_MSrepl_getsrvidfromdistdb
-1068937686  4 0 19/07/2021 4:09:07 PM 19/07/2021 4:09:08 PM True False False FN SQL_SCALAR_FUNCTION       fn_getserverportfromproviderstring
-1068897509  4 0 19/07/2021 4:09:04 PM 19/07/2021 4:09:26 PM True False False P  SQL_STORED_PROCEDURE      sp_addqueued_artinfo