Kenan
Assistant Engineer
Assistant Engineer
  • UID621
  • Fans1
  • Follows0
  • Posts55
Reads:1948Replies:0

[SQLServer Development]Best practices of SQL Server - develop database applications for .NET CORE-based Linux version

Created#
More Posted time:Oct 25, 2016 17:31 PM
Title: Best practices of SQL Server - develop database applications for .NET CORE-based Linux version
Background
Recently some customers connected database applications in .NET CORE-based Linux versions of SQL Server. During development, some issues arose and the customers mistakenly blamed our SQL Server for the issues. We have received some tickets regarding this. Therefore, we need to enhance our capacity in this regard in a planned manner and guide the users to use SQL Server correctly.
Deploy the environment
1. Server version: Ubuntu 14.04
2. .NET CORE version: 1.0
3. Installation process
3.1 Add dotnet apt-get feed
sudo sh -c 'echo "deb [arch=amd64] https://apt-mo.trafficmanager.net/repos/dotnet-release/ trusty main" > /etc/apt/sources.list.d/dotnetdev.list'
sudo apt-key adv --keyserver apt-mo.trafficmanager.net --recv-keys 417A0893
sudo apt-get update


3.2 Install .NET Core SDK
sudo apt-get install dotnet-dev-1.0.0-preview2-003131
https://www.microsoft.com/net/core#ubuntu


Develop the first database application
1. Create a folder
mkdir SqlConnect

2. Create the source code file and compile the content
Here the C# language is used. The connected database is RDS for SQL SERVER 2008 R2. Some articles on GitHub suggested that SP3 is required for connecting SQL SERVER 2008 R2, which is not true. This may cause misunderstandings for customers.
touch DbApp.cs
vim DbApp.cs
chmod +755 DbApp.cs
----------------------------------------------------------------------------------------------
using System;
using System.Data.Common;
using System.Data.SqlClient;

namespace ConsoleApplication
{
    public class Program
    {
        public static void Main(string[] args)
        {    
        String sqlConnStr="Data Source=myServer,3433;User ID=zhangshan;Password=123;Initial Catalog=master";
        String sqlStr="select spid,waittype  from sys.sysprocesses";

        SqlConnection sqlConn= new SqlConnection(sqlConnStr);
        SqlCommand sqlCmd = new SqlCommand(sqlStr,sqlConn);
        try
        {
            string spid=String.Empty;
            string program_name=String.Empty;

            sqlConn.Open();
            SqlDataReader reader = sqlCmd.ExecuteReader();
            while(reader.Read())
            {
                spid=reader[0].ToString();
                program_name=reader[1].ToString();
                Console.WriteLine("spid="+spid+" "+"waittype="+program_name);
            }
            //Console.WriteLine("The server is:"+svrName);
        }
        catch(SqlException e)
        {  
            Console.WriteLine(e.Message);
        }
        finally
        {
            sqlConn.Close();
        }      
        //Console.Read();
        }
    }
}


3. Create the configuration file
By default, the system.data.* package does not exist (.Nuget/package), so you need to add the package dependency.
"System.Data.Common": "4.1.0",
"System.Data.SqlClient" : "4.1.0",
"System.Runtime": "4.1.0"
touch project.json
vim project.json
----------------------------------------------------------------------------------------------
{
    "version": "1.0.0-*",
    "buildOptions": {
        "debugType": "portable",
        "emitEntryPoint": true
    },
    "dependencies": {
        "System.Data.Common": "4.1.0",
        "System.Data.SqlClient" :  "4.1.0",
        "System.Runtime": "4.1.0"
    },
  "frameworks": {
    "netcoreapp1.0": {
      "dependencies": {
        "Microsoft.NETCore.App": {
          "type": "platform",
          "version": "1.0.1"
        }
      },
      "imports": "dnxcore50"
    }
  }
}


4. Restore the dependent libraries of the entire project
Find the project file (project.json) in the current directory, and restore the dependent libraries of the entire project using the NuGet Gallery. Then traverse every directory, generate the project file and continue to restore the dependent items in the project file.
dotnet restore



5. Compile the application

dotnet build



6. Run and use the application
dotnet run



Okay. The first simple database application has been completed. The development is very easy for those who are familiar with C#.
Guest