.net core on linux,[netCore]SQL Server on Linux and netCore2.0 开发初体验-优快云博客

8种机械键盘轴体对比

本人程序员，要买一个写代码的键盘，请问红轴和茶轴怎么选？

微软爱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 --remove container

docker start --start container

docker stop --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

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())

)

create proc usp_GetEmployee(@serial int)

set nocount on

select * from dbo.Employee where [email protected]

Use .Net core to access SQL Server of Linux

看了官方文档，需要安装Microsoft.Extensions.Configuration和Microsoft.Extensions.Configuration.Json，

安装完后我们可以透过编辑*.csproj再次确认相依文件。

appsettings.json

另外我将使用Dapper来存取SQL Server of Linuxclass 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 IEnumerableGetAll()

{

using (_connection)

{

_connection.Open();

return _connection.Query("SELECT * FROM dbo.Employee");

}

public IEnumerableGetEmployeesWithSP(int serial)

{

using (_connection)

{

_connection.Open();

return _connection.Query("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 Entryclass 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 linuxapt-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