Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Changes to SqlClient for the RC1 release

Cross-Platform SqlClient

System.Data.SqlClient is cross-platform! This means you can now take advantage of its APIs to interact with a SQL Server database across OS X, Linux, and Windows.

Windows 7 and Windows Server 2008 R2 Users

To use the library, you will need to first download and install the Visual C++ Redistributable for Visual Studio 2012 Update 4 found here.

OSX and Linux Users

The previous version of SqlClient used its own networking layer implemented on top of Windows. Because .NET Core is cross-platform, SqlClient was changed to use the .NET networking layer to communicate with SQL Server. However, it is not feature-complete for OSX or Linux.

To use SqlClient on OSX or Linux, Multiple Active Result Sets (MARS) must be disabled in the connection string (it is enabled by default). Set it to false by including MultipleActiveResultSets=False; in your connectionString.

Additionally, connectivity to SQL Server is only possible via TCP.

Communication over Named Pipes, Shared Memory, or LocalDB is not supported yet.

Example connection string:

connectionString="Data Source=my-data-source;Initial Catalog=mydataBase;MultipleActiveResultSets=False;User Id=myUserName; Password=myPassword;"

Sample

Note: the following will compile cross-platform, but won't execute due to a dependency on a database which doesn't exist. You can swap out the connection string to point the data source, username, and password to something which matches your infrastructure.

C#:

using System;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace SqlClientTest
{
	public class Program
	{
		public void Main(string[] args)
		{
			string connectionString = 
			    "Data Source=<data_source_goes_here>;" +
			    "Initial Catalog=mydataBase;" +
			    "MultipleActiveResultSets=False;" + // Disables MARS
			    "User Id=myUserName; Password=myPassword;";
			    
			string sql = "select * from Dogs";
			
			List<Dog> dogs = new List<Dog>();
			
			using (var conn = new SqlConnection(connectionString))
			{
				conn.Open();
				
				using (var command = new SqlCommand(sql, conn))
				{
					var reader = command.ExecuteReader();
					while (reader.Read())
					{
						// Collect the values from the row in the Dog table!
						
						// Name is the first column 
						string name = reader.GetString(0);
						
						// IsShortHaired is the second column
						bool isShortHaired = reader.GetBoolean(1);
						
						// NumberOfLegs is the third column
						int numberOfLegs = reader.GetInt32(2);
						
						dogs.Add(new Dog(name, isShortHaired, numberOfLegs));
					}
				}
			}
			
			foreach (var dog in dogs)
			{
			    if (dog.IsShortHaired)
			    {
				    Console.WriteLine($"{dog.name} is short-haired and has {dog.NumberofLegs} legs!");
				}
			}
		}
	}
	
	public class Dog
	{
		public Dog(string name, bool isShortHaired, int numberOfLegs)
		{
			Name = name;
			IsShortHaired = isShortHaired;
			NumberOfLegs = numberOfLegs;
		}
		
		public string Name { get; set; }
		public bool IsShortHaired { get; set; }
		public int NumberOfLegs { get; set; }
	}
}

project.json:

{
	"dependencies": {
		"System.Runtime": "4.0.0-rc1-*",
		"System.Data.SqlClient": "4.0.0-beta-23409",
		"System.Collections":"4.0.0-rc1-*",
		"System.Console": "4.0.0-beta-23409"
	},
	"frameworks": {
		"dotnet55":{}
	}
}

To build:

$ dnu restore
$ dnu build
@natemcmaster

This comment has been minimized.

Copy link

natemcmaster commented Nov 18, 2015

@cartermp Network Library=DBMSSOCN; shouldn't be necessary.

@cartermp

This comment has been minimized.

Copy link
Owner Author

cartermp commented Nov 18, 2015

Updated. Thanks!

@saurabh500

This comment has been minimized.

Copy link

saurabh500 commented Nov 18, 2015

@cartermp Windows Server 2008 RC2 should be Windows Server 2008 R2

@politician

This comment has been minimized.

Copy link

politician commented Nov 18, 2015

@cartermp @natemcmaster The .NET Core and ASP.NET 5 RC announcement explicitly calls out Network Library=DBMSSOCN as something that's required and include a link to this gist. You might want to straighten that out, or explain why the announcement is wrong.

@cartermp

This comment has been minimized.

Copy link
Owner Author

cartermp commented Nov 18, 2015

@saurabh500 Updated. Thanks!

@politician The blog has been updated to include the updates from this gist.

@ferozed

This comment has been minimized.

Copy link

ferozed commented Nov 18, 2015

Are table valued parameters supported? @cartermp ?

@Alendar

This comment has been minimized.

Copy link

Alendar commented Nov 21, 2015

Are you sure MARS is supposed to be the default? I'm using the SQL driver in 4.6, and I have to set MARS to true to run multiple commands through the same connection. Is this a change in default behavior?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.