path: root/web/sqlclient

* Microsoft SQL Server Provider

<ul>
	<li>ADO.NET Provider for Microsoft SQL Server 7/2000 databases</li>

	<li>Exists in namespace System.Data.SqlClient and assembly System.Data</li>
	
	<li>Created by Tim Coleman</li>
	
	<li>Used the <a href="http://www.freetds.org/">FreeTDS</a> and 
	<a href="http://jtds.sourceforge.net/">jTDS</a> projects as resources.</li>
	
	<li>Implemented in 100% C#</li>
	
	<li>Is similar to the Mono.Data.TdsClient and Mono.Data.SybaseClient providers.</li>
	
	<li>Requires the assembly Mono.Data.Tds.dll which implements the TDS protocol in 100% C#.</li>
	
	<li>Uses TDS Protocol Version 7.0</li>
	
	<li>Bugs with Mono or the data provider should be reported 
	in Mono's Bugzilla <a href="http://bugzilla.ximian.com/">here</a>.  If you
	do not have Bugzilla user account, it is free 
	and easy to 
	create one <a href="http://bugzilla.ximian.com/createaccount.cgi">here</a>.</li>
	
</ul>


** Current Status


<ul>

	<li>Connect to Microsoft SQL Server 7/2000 databases via SQL Server authentication and NT Authentication.</li>
	
	<li>Connection pooling works.</li>
	
	<li>Stored Procedures work.</li>
	
	<li>Parameters work.</li>
	
	<li>Prepare works.</li>
	
	<li>SQL commands can be executed
	via ExecuteNonQuery() of a SqlCommand.</li>
	
	<li>SQL aggregates can be executed and a single row and single column
	result can be retrieved via ExecuteScalar() of a SqlCommand</li>
	
	<li>SQL queries can be executed via ExecuteReader() and results 
	can be retrieved via SqlDataReader.</li>
	
	<li>a DataTable with schema info about a result can be gotten via GetSchemaTable()
	in a SqlDataReader</li>
	
	<li>XML can be read via ExecuteXmlReader in a SqlCommand.</li>
	
	<li>Data can be filled in a DataTable in a DataSet via a SqlDataAdapter</li>
	
	<li>Works in the SQL# command-line and GTK# GUI version</li>
</ul>

** Action plan

<ul>
	
	<li>Needs more testing and fixing bugs</li>
	
	<li>Start work on TDS Protocol Version 8.0 support</li>
	
	<li>Add support for the .NET Framework 2.0 (Whidbey)</li>
	
	<li>Add support for Microsoft SQL Server 2005 (Yukon) support</li>

</ul>

** Testing

<ul>
	<li>Have a working mono and mcs installed</li>
	
	<li>Have access to a Microsoft SQL Server database 
	or either download it:
		<ul>
			<li><a href="http://www.microsoft.com/sql/default.asp">Microsoft SQL Server</a></li>
		</ul>
	</li>
	
	<li><b>IMPORTANT:</b> If using Microsoft SQL Server 2000, make sure
	you are using at least Service Pack 3 for Microsoft SQL Server 2000.  If using
	MSDE 2000, make sure you have the special Service Pack 3 for MSDE 2000.  You
	can get it from <a href="http://www.microsoft.com/sql/downloads/2000/sp3.asp">here</a></li>
	
	<li>For those that only have MSDE installed.  You can change the authentication mode 
	from Windows Only Authentication to SQL Server and Windows Authentications (also knows as Mixed-mode authentication)
	via the <a href="http://support.microsoft.com/default.aspx?scid=kb;en-us;Q322336&sd=tech#4">registry</a></li>.  It is
	the LoginMode you need to change.  By default,
	MSDE is installed with Windows Only Authentication. If you want SqlClient to work with MSDE via SQL Server authentication, you will
	need to change the setting. Otherwise, you wil have to use NT Authentication.</a>
	
	<li>If using MSDE, you might need to create a new user with password.  Give
    this user access to various databases in this MSDE instance.  Also, for each
    database, give this new user at least SELECT access to the various tables you want
    to retrieve data from.</li>
	
	<li>If you have Enterprise Manager, you can easily change the authentication mode
	for both MSDE and Microsoft SQL Server.  To change the authentication mode in 
	Enterprise Mananger, select the instance, right-click on it, and select properites.
	The SQL Server properties dialog for that instance will pop up.  Choose the Security
	tab.  Change the Authentication from Windows Only to SQL Server and Windows.  If
	the instance of your database does not show up in Enterprise Manager, Register first
	by selecting the Action menu and choosing New SQL Server Registration.</li>

	<li>Located at mcs/class/System.Data/Test is a test for System.Data.SqlClient
	named SqlTest.cs and you could use this as a basis for your test.</li>
	
	<li>If you want to use Integrated Security (aka NT Authentication aka Trusted Connection aka Domain Login), you
	will need to specify the Domain User ID and Password.  This is because Mono is not integrated with Windows
	nor SQL Server.</li>
	
	<li>Has a connection string format for SQL Server Authentication:
<pre>
 Server=hostname;
 Database=databaseName;
 User ID=sqlServerUserid;
 Password=sqlServerPassword
</pre>
	</li>
	<li>Has a connection string format for NT Authentication:
<pre>
 Server=hostname;
 Database=databaseName;
 User ID=windowsDomain\windowsUserid;
 Password=windowsPassword;
 Integrated Security=SSPI
</pre>
	</li>

	<li>The Server part can be used three ways:
	
		<table border=1>
			<tr>
				<td><b>Server Definition</b></td> <td><b>Example</b></td>
			</tr>	
		
			<tr>
				<td>hostname</td> <td>Server=MYHOST</td>
			</tr>
			
			<tr>
				<td>hostname,port</td> <td>Server=MYHOST,1433</td>
			</tr>
			
			<tr>
				<td>hostname\instance</td> <td>Server=MYHOST\NETSDK</td>
			</tr>
		</table>
	</li>
	
	<li>C# Example using SQL Server Authentication:
<pre>
 using System;
 using System.Data;
 using System.Data.SqlClient;
 
 public class Test 
 {
    public static void Main(string[] args)
    {
       string connectionString = 
          "Server=MyServer;" +
          "Database=pubs;" +
          "User ID=MySqlServerUserId;" +
          "Password=MySqlServerPassword;";
       IDbConnection dbcon;
       dbcon = new SqlConnection(connectionString);
       dbcon.Open();
       IDbCommand dbcmd = dbcon.CreateCommand();
       string sql = 
           "SELECT fname, lname " +
           "FROM employee";
       dbcmd.CommandText = sql;
       IDataReader reader = dbcmd.ExecuteReader();
       while(reader.Read()) {
            string FirstName = (string) reader["fname"];
            string LastName = (string) reader["lname"];
            Console.WriteLine("Name: " + 
                 FirstName + " " + LastName);
       }
       // clean up
       reader.Close();
       reader = null;
       dbcmd.Dispose();
       dbcmd = null;
       dbcon.Close();
       dbcon = null;
    }
 }
</pre>
	</li>

	<li>C# Example using NT Authentication (Integrated Security)
<pre>
 using System;
 using System.Data;
 using System.Data.SqlClient;
 
 public class Test 
 {
    public static void Main(string[] args)
    {
       string connectionString = 
          "Server=MyServer;" +
          "Database=pubs;" +
          "User ID=MyWindowsDomain\\MyWindowsUserid;" +
          "Password=MyWindowsPassword;" +
          "Integrated Security=SSPI";
       IDbConnection dbcon;
       dbcon = new SqlConnection(connectionString);
       dbcon.Open();
       IDbCommand dbcmd = dbcon.CreateCommand();
       string sql = 
           "SELECT fname, lname " +
           "FROM employee";
       dbcmd.CommandText = sql;
       IDataReader reader = dbcmd.ExecuteReader();
       while(reader.Read()) {
            string FirstName = (string) reader["fname"];
            string LastName = (string) reader["lname"];
            Console.WriteLine("Name: " + 
                 FirstName + " " + LastName);
       }
       // clean up
       reader.Close();
       reader = null;
       dbcmd.Dispose();
       dbcmd = null;
       dbcon.Close();
       dbcon = null;
    }
 }
</pre>
	</li>

	<li>Building C# Example:
	<ul>
		<li>Save the example to a file, such as, TestExample.cs</li>
		<li>Build on Linux:
<pre>
	mcs TestExample.cs -r System.Data.dll
</pre>
		</li>
	</ul>
	</li>
	<li>Running the Example:
<pre>
mono TestExample.exe
</pre>
	</li>

</ul>