[C #] Some experiences of SQL Server Express LocalDb (SQLLOCALDB)

Really feel that their knowledge is still relatively narrow, before, incredibly still do not know sqllocaldb.

What's sqllocaldb? In fact, simplifying the local database of SQL Server, you can say that SQL Server can be either remote or local.

And sqllocaldb can only be used locally. To be direct, SQLLOCALDB is a lightweight, local-based, T-SQL database, full name: SQL Server Express LocalDb.

Well, don't say much nonsense.

First, how to install this sqllocaldb?

1) If visual Studio 2015 or 2017 is installed, it is generally installed on the computer.

In Visual Studio 2017, for example, you can see that the component has been installed in the Single component page in Visual Studio Installer (the Red box section):

2) or install the component into the visual Studio 2017 installation package:

64-bit:

32-bit:

3) Download SQL Server express:https://download.microsoft.com/download/5/e/9/to SQL Server Download Center 5e9b18cc-8fd5-467e-b5bf-bade39c51f73/sqlserver2017-ssei-expr.exe

After downloading, open the reference and click on "Download Media":

On the popup page, select LocalDB (third) and click Next to download Sqllocaldb.msi

Note: SQL Server Express localdb Microsoft is no longer available, currently the latest, that is, the above 2017 version.

4) Baidu Network disk Download: https://pan.baidu.com/s/12uKJ7IEE_45P0chOI4b39w

Install different SQLLOCALDB depending on the system type.

Second, connect sqllocaldb

1. Open Visual Studio 2017, click View (menu bar), Server Explorer, and the Server Explorer window will open:

2. Right click on "Data Connection", select "Add Connection" in the popup context menu, and set "server name" to (LOCALDB) \mssqllocaldb in the popup window as shown in:

Then attach a database file called Musicdbcontext.mdf (the file can be defined by itself) and click OK, at this point, Musicdbcontext. Database as the default database, we can select other existing databases in SQLLOCALDB.

The resulting connection string is as follows:

Data source= (LocalDb) \mssqllocaldb; Attachdbfilename=c:\users\cnc\desktop\musicdbcontext.mdf;initial catalog=musicdbcontext;integrated Security=True

We can change the path of the MDF file to a relative path:

Data source= (LocalDb) \mssqllocaldb; attachdbfilename=| Datadirectory|\musicdbcontext.mdf;initial catalog=musicdbcontext;integrated Security=true

Third, write a program to read the existing data

As shown in the following code:

 
 
using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = @"Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=MusicDBContext;Integrated Security=True";
            SqlConnection connection = new SqlConnection(connectionString);
            SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Musics",connection);
            DataTable table = new DataTable();
            adapter.Fill(table);
            var result = (from row 
                          in table.Rows.Cast<DataRow>()
                          select
                          new { ID = (int)row[0], Title = (string)row[1], ReleaseDate = (DateTime)row[2] });
            result.ToList().ForEach(x => Console.WriteLine($"{x.ID},{x.Title},{x.ReleaseDate}"));
        }
    }
}

The results of the operation are as follows:

Note: Musicdbcontext.mdf can be downloaded in Baidu Network disk, address: Https://pan.baidu.com/s/1zgkPLcetTo-XMNEH3-8FoQ

Iv. Some reference materials on sqllocaldb

https://docs.microsoft.com/zh-cn/sql/database-engine/configure-windows/sql-server-2016-express-localdb?view= sql-server-2017
https://docs.microsoft.com/zh-cn/sql/2014/relational-databases/express-localdb-instance-apis/ command-line-management-tool-sqllocaldb-exe?view=sql-server-2017
https://docs.microsoft.com/zh-cn/sql/2014/tools/sqllocaldb-utility?view=sql-server-2017
https://technet.microsoft.com/zh-cn/hh510202 (v=sql.105)

[C #] Some experiences of SQL Server Express LocalDb (SQLLOCALDB)