rsqlserver

==========

Sql Server driver database interface (DBI) driver for R. This is a DBI-compliant Sql Server driver based on the The .NET Framework Data Provider for SQL Server (SqlClient) System.Data.SqlClient.

Motivation

The .NET Framework Data Provider for SQL Server (SqlClient) uses its own protocol to communicate with SQL Server. It is lightweight and performs well because it is optimized to access a SQL Server directly without adding an OLE DB or Open Database Connectivity (ODBC) layer.

Prerequisites and package dependencies

The interoperability of R and .NET code relies on the rClr package. You can download an installable R package for windows (zip file). Please Make sure to at least skim through the installation instructions.. Under windows it is better to use the zip package. First you install Visual C++ Redistributable Packages for Visual Studio 2013

The rsqlserver package uses the .NET framework SDK to build a small C# project. Typically if you have on your machine the file "C:\Windows\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe", you can skip this paragraph. Otherwise you need to install the Microsoft Windows SDK for Windows 7 and .NET Framework 4. An overview of list of Microsoft SDKs is available here

Installation

rsqlserver is currently available only on github. It is available for windows user and Linux user.

You can install it from github using the devtools package

require(devtools)
install_github('agstudy/rsqlserver')  ## assuming that you have already installed rClr

Features

rsqlserver presents many features:

• Fast and Easy connection to Sql server.[see benchamrking]
• Use Trusted connection with server. This functionality is only available for Windows users.
• dbSendQuery Querying the data base : low levels functions using sql statement.
• Full DBI compliant: for example Support of Higher level convenient functions :dbReadTable,dbWriteTable,dbRemoveTable,..)
• dbTransaction, dbCommit, dbRollback for Transaction management
• dbCallProc (in development) for Stored procedure call.
• dbBulkCopy using Bulk Copy for quickly bulk copying Big data.frame or large files into SQL server tables or views.
• Many DBI extension like dbGetScalar , dbGetNoQuery , dbBulkCopy
• dbParameter(coming soon) to handle Transact-SQL named parameters. This will provide better type checking and improve performance.

Benchmarking

You can see rsqlserver benchmarking performance versus drivers :RODBC and RJDBC.

Acknowledgements

I want to thank Jean-Michel Perraud the author of rClr package.

if you like this project, give it a star or a donation :)