微軟愛Linux有一段時間了，SQL Server on Linux這目標也看到光了，
隨者.Net Core 2.0 Announcing，今天我就透過GDD和SOD方式動手實作
(我沒很了解.netCore，但這兩種方式對我來說有快速學習的效果~哈)，
看看.Net Core 2.0是否讓我有不同的開發體驗。
Runing a SQL Server of Linux in a Docker
我透過docker快速建立我的開發環境
docker search mssql
可以看到 microsoft/mssql-server-linux 高達377顆星，
所以我等等會使用該docker image來建立我第一個SQL Server of Linux。
docker pull microsoft/mssql-server-linux
使用該image前，請注意一下必要條件如下
docker run -e “ACCEPT_EULA=Y” -e “SA_PASSWORD=XXXXXXX” -ti -p 1533:1433 microsoft/mssql-server-linux
完成必要條件後，就可在Linux順利啟動SQL Server。
使用SSMS連接SQL Server of Linux
該image所包的是SQL Server 2017(RC2) 14.0.900.75版本。
下面我將建立一個測試資料庫、資料表和SP，方便後面我使用 Dapper的.net core版本進行資料存取。
Show current data file path
可以看到目前系統資料庫檔案都存放在/var/opt/mssql/data，但使用者資料庫我想存放在/opt/mssql/data
#Use the docker exec -it command to start an interactive bash shell inside your running container
docker exec -it 858775108f6e "bash"
note:
docker ps –a –show container
docker rm <CONTAINER ID> --remove container
docker start <CONTAINER ID> --start container
docker stop <CONTAINER ID> --stop container
#Display files of location /var/opt/mssql/data
ls -lrt /var/opt/mssql/data
#create a directory named data under /opt/mssql/
mkdir /opt/mssql/data
ls -lrt /opt/mssql/data
--Create database via ssms
create database ricolinux on primary
(
name='ricolinux',filename='/opt/mssql/data/ricolinux.mdf',size=100mb,maxsize=unlimited,filegrowth=10mb
)
log on
(
name='ricolinux_Log',filename='/opt/mssql/data/ricolinux_Log.ldf',size=100mb,maxsize=unlimited,filegrowth=10mb
)
Check ricolinux database file path
相關檔案已經放置我想要的路徑了。
use ricolinux
go
create table Employee
(
serial int not null
,cname nvarchar(30) not null
,ename varchar(30) not null
,birthday date not null
,storeon datetime not null default(getdate())
)
go
create proc usp_GetEmployee(@serial int)
as
set nocount on
select * from dbo.Employee where serial=@serial
Use .Net core to access SQL Server of Linux
看了官方文件，需要安裝Microsoft.Extensions.Configuration和Microsoft.Extensions.Configuration.Json，
安裝完後我們可以透過編輯*.csproj再次確認相依檔案。
appsettings.json
另外我將使用Dapper來存取SQL Server of Linux
class Employee
{
public int Serial { get; set; }
public string Cname { get; set; }
public string Ename { get; set; }
public DateTime Birthday { get; set; }
public DateTime Storeon { get; set; }
}
internal class BaseRepository
{
public string ConnectionString {
get
{
//read appsettings.json
var builder = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json", optional: true, reloadOnChange: true);
//create IConfigurationRoot object
var Configuration = builder.Build();
return Configuration["ConnectionStrings:linuxsql"];
}
}
public IDbConnection Connection=> new SqlConnection(ConnectionString);
}
internal sealed class EmployeeRepository: BaseRepository
{
private readonly string _connectionString;
private readonly IDbConnection _connection;
public EmployeeRepository()
{
_connectionString = base.ConnectionString;
_connection = base.Connection;
}
public IEnumerable<Employee> GetAll()
{
using (_connection)
{
_connection.Open();
return _connection.Query<Employee>("SELECT * FROM dbo.Employee");
}
}
public IEnumerable<Employee> GetEmployeesWithSP(int serial)
{
using (_connection)
{
_connection.Open();
return _connection.Query<Employee>("usp_GetEmployee", new { serial = serial },
commandType: CommandType.StoredProcedure);
}
}
public int Add(Employee prod)
{
using (_connection)
{
string sQuery = @"INSERT INTO dbo.Employee (Serial, Cname, Ename, Birthday, Storeon)
VALUES(@Serial, @Cname, @Ename, @Birthday, getdate())";
_connection.Open();
return _connection.Execute(sQuery, prod);
}
}
public int Delete(int serial)
{
using (_connection)
{
string sQuery = @"DELETE FROM dbo.Employee WHERE serial = @serial";
_connection.Open();
return _connection.Execute(sQuery, new { serial = serial });
}
}
public int Update(Employee employee)
{
using (_connection)
{
try
{
string sQuery = @"UPDATE dbo.Employee SET Cname = @Cname,
Ename = @Ename, Birthday= @Birthday, Storeon=getdate()
WHERE Serial = @Serial";
_connection.Open();
return _connection.Execute(sQuery, employee);
}
catch (Exception e)
{
Console.WriteLine(e);
return 0;
}
}
}
}
Main Entry
class Program
{
private static EmployeeRepository _employeeRepository;
static void Main(string[] args)
{
Console.WriteLine("\nWhat is your statement? ");
var sqlLine = Console.ReadLine();
string[] inputValues;
int result = 0;
_employeeRepository =new EmployeeRepository();
switch (sqlLine.ToUpper().Substring(0, Math.Min(3, sqlLine.Length)))
{
case "GET":
_employeeRepository.GetAll().ToList().ForEach(
s => Console.WriteLine($"Serial:{s.Serial}, Cname:{s.Cname}, Ename:{s.Ename}, Birthday:{s.Birthday}, Storeon:{s.Storeon}")
);
break;
case "INS":
inputValues = sqlLine.Substring(6).Split(',');
var employeeadd = new Employee()
{
Serial = int.Parse(inputValues[0]),
Cname= inputValues[1],
Ename= inputValues[2],
Birthday =DateTime.Parse(inputValues[3])
};
result= _employeeRepository.Add(employeeadd);
Console.WriteLine( (result > 0) ? "successed": "failed");
break;
case "DEL":
inputValues = sqlLine.Substring(6).Split(',');
result = _employeeRepository.Delete(int.Parse(inputValues[0]));
Console.WriteLine((result > 0) ? "successed" : "failed");
break;
case "UPD":
inputValues = sqlLine.Substring(6).Split(',');
var employeeupd = new Employee()
{
Serial = int.Parse(inputValues[0]),
Cname = inputValues[1],
Ename = inputValues[2],
Birthday = DateTime.Parse(inputValues[3])
};
result = _employeeRepository.Update(employeeupd);
Console.WriteLine((result > 0) ? "successed" : "failed");
break;
case "EXE":
inputValues = sqlLine.Substring(6).Split(',');
_employeeRepository.GetEmployeesWithSP(int.Parse(inputValues[0])).ToList().ForEach(
s => Console.WriteLine($"Serial:{s.Serial}, Cname:{s.Cname}, Ename:{s.Ename}, Birthday:{s.Birthday}, Storeon:{s.Storeon}")
);
break;
default:
break;
}
Console.Write("\nPress any key to exit...");
Console.ReadKey(true);
}
}
Install .net core 2.0 on linux
apt-get install curl
curl https://packages.microsoft.com/keys/microsoft.asc | gpg --dearmor > microsoft.gpg
mv microsoft.gpg /etc/apt/trusted.gpg.d/microsoft.gpg
cat /etc/issue #determine Ubuntu version number
sh -c 'echo "deb [arch=amd64] https://packages.microsoft.com/repos/microsoft-ubuntu-xenial-prod xenial main" > /etc/apt/sources.list.d/dotnetdev.list'
apt-key adv --keyserver apt-mo.trafficmanager.net --recv-keys 417A0893
apt-get update
apt-get upgrade
apt-get install dotnet-sdk-2.0.0
#Confirm you have the correct version installed
dotnet --version
Copy myapp files to linux container
mkdir docker-netcoreapp
#copy all of .net core files to /docker-netcoreapp/
docker cp D:\riconetcore\AccessLinuxSQL\. 858775108f6e:/docker-netcoreapp/.
note:我設定local的D分享給container
Start myapp on Ubuntu
Cd docker-netcoreapp
dotnet AccessSQLofLinux.dll
insert
Getall
Update
Execsp
透過呼叫SP確認剛剛資料已被正確更新。
Delete
stop container
docker commit 858775108f6e microsoft/mssql-server-linux
docker stop 858775108f6e
感想
我寫這隻簡單.netCore app存取 linux sql server，花較多時間在Liunx command，
至於安裝SQL Server on Linux，透過docker可說輕鬆省事毫不費力，
寫code過程差異較大就是組態設定檔案的存取，以及檔案相依性問題排除，
後面有空再來玩玩web api。
參考
Quickstart: Run the SQL Server 2017 container image with Docker
Build a C# Hello World application with .NET Core in Visual Studio 2017
How to Change Default Data and Log file directory for SQL Server running on Linux
Run the SQL Server 2017 container image with Docker
.NET Core 2.0 Changes – 4 Key Things to Know