1 * Microsoft SQL Server Provider
4 <li>ADO.NET Provider for Microsoft SQL Server 7/2000 databases</li>
6 <li>Exists in namespace System.Data.SqlClient and assembly System.Data</li>
8 <li>Created by Tim Coleman</li>
10 <li>Used the <a href="http://www.freetds.org/">FreeTDS</a> and
11 <a href="http://jtds.sourceforge.net/">jTDS</a> projects as resources.</li>
13 <li>Implemented in 100% C#</li>
15 <li>Is similar to the Mono.Data.TdsClient and Mono.Data.SybaseClient providers.</li>
17 <li>Requires the assembly Mono.Data.Tds.dll which implements the TDS protocol in 100% C#.</li>
19 <li>Uses TDS Protocol Version 7.0</li>
21 <li>Bugs with Mono or the data provider should be reported
22 in Mono's Bugzilla <a href="http://bugzilla.ximian.com/">here</a>. If you
23 do not have Bugzilla user account, it is free
25 create one <a href="http://bugzilla.ximian.com/createaccount.cgi">here</a>.</li>
34 <li>Thanks to Sebastien Pouliot, we now have NT Authentication (Integrated Security) support. This support was
35 added after the release of Mono 0.29. So, you either get it from cvs or wait for the Mono 0.30 release.</li>
37 <li>Able to connect to Microsoft SQL Server 7/2000 databases via SQL Server authentication and NT Authentication.</li>
39 <li>Connection pooling works.</li>
41 <li>Stored Procedures work.</li>
43 <li>Parameters work.</li>
45 <li>Prepare works.</li>
47 <li>SQL commands can be executed
48 via ExecuteNonQuery() of a SqlCommand.</li>
50 <li>SQL aggregates can be executed and a single row and single column
51 result can be retrieved via ExecuteScalar() of a SqlCommand</li>
53 <li>SQL queries can be executed via ExecuteReader() and results
54 can be retrieved via SqlDataReader.</li>
56 <li>a DataTable with schema info about a result can be gotten via GetSchemaTable()
57 in a SqlDataReader</li>
59 <li>XML can be read via ExecuteXmlReader in a SqlCommand.</li>
61 <li>Data can be filled in a DataTable in a DataSet via a SqlDataAdapter</li>
63 <li>Uses TDS Protocol Version 7.0</li>
65 <li>Works in the SQL# command-line and GTK# GUI version</li>
71 <li>Connection timeouts is being developed now</li>
73 <li>Needs more testing</li>
75 <li>Start work on TDS Protocol Version 8.0 support</li>
77 <li>Add support for .NET 1.2 and 2.0 and Microsoft SQL Server (Yukon) support</li>
84 <li>Have a working mono and mcs installed</li>
86 <li>Have access to a Microsoft SQL Server database
87 or either download it:
89 <li><a href="http://www.microsoft.com/sql/default.asp">Microsoft SQL Server</a></li>
93 <li><b>IMPORTANT:</b> If using Microsoft SQL Server 2000, make sure
94 you are using at least Service Pack 3 for Microsoft SQL Server 2000. If using
95 MSDE 2000, make sure you have the special Service Pack 3 for MSDE 2000. You
96 can get it from <a href="http://www.microsoft.com/sql/downloads/2000/sp3.asp">here</a></li>
98 <li>For those that only have MSDE installed. You can change the authentication mode
\r
99 from Windows Only Authentication to SQL Server and Windows Authentications (also knows as Mixed-mode authentication)
\r
100 via the <a href="http://support.microsoft.com/default.aspx?scid=kb;en-us;Q322336&sd=tech#4">registry</a></li>. It is
\r
101 the LoginMode you need to change. By default,
\r
102 MSDE is installed with Windows Only Authentication. For SqlClient to work with MSDE, you will
\r
103 need to change the setting.</a>
\r
105 <li>If using MSDE, you might need to create a new user with password. Give
\r
106 this user access to various databases in this MSDE instance. Also, for each
\r
107 database, give this new user at least SELECT access to the various tables you want
\r
108 to retrieve data from.</li>
\r
110 <li>If you have Enterprise Manager, you can easily change the authentication mode
\r
111 for both MSDE and Microsoft SQL Server. To change the authentication mode in
\r
112 Enterprise Mananger, select the instance, right-click on it, and select properites.
\r
113 The SQL Server properties dialog for that instance will pop up. Choose the Security
\r
114 tab. Change the authentication from Windows Only to SQL Server and Windows. If
\r
115 the instance of your database does not show up in Enterprise Manager, Register first
\r
116 by selecting the Action menu and choosing New SQL Server Registration.</li>
\r
118 <li>Located at mcs/class/System.Data/Test is a test for System.Data.SqlClient
119 named SqlTest.cs and you could use this as a basis for your test.</li>
121 <li>Mono's SqlClient does not support trusted connections
122 nor integrated security. You can not use this when connecting. You need
123 to explicitly use a User ID and Password
124 authenticated by SQL Server.</li>
126 <li>Has a connection string format for SQL Server Authentication:
128 Server=hostname;Database=databaseName;User ID=userid;Password=password
131 <li>Has a connection string format for NT Authentication:
133 Server=hostname;Database=databaseName;User ID=windowsUserid;Password=windowsPassword;Integrated Security=SSPI
137 <li>The Server part can be used three ways:
141 <td><b>Server Definition</b></td> <td><b>Example</b></td>
145 <td>hostname</td> <td>Server=MYHOST</td>
149 <td>hostname,port</td> <td>Server=MYHOST,1433</td>
153 <td>hostname\\instance</td> <td>Server=MYHOST\\NETSDK</td>
162 using System.Data.SqlClient;
166 public static void Main(string[] args)
168 string connectionString =
169 "Server=localhost;" +
171 "User ID=myuserid;" +
172 "Password=mypassword;";
174 dbcon = new SqlConnection(connectionString);
176 IDbCommand dbcmd = dbcon.CreateCommand();
178 "SELECT fname, lname " +
180 dbcmd.CommandText = sql;
181 IDataReader reader = dbcmd.ExecuteReader();
182 while(reader.Read()) {
183 string FirstName = (string) reader["fname"];
184 string LastName = (string) reader["lname"];
185 Console.WriteLine("Name: " +
186 FirstName + " " + LastName);
199 <li>Building C# Example:
201 <li>Save the example to a file, such as, TestExample.cs</li>
204 mcs TestExample.cs -r System.Data.dll
207 <li>Build on Windows via Cygwin:
209 mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe \
210 TestExample.cs -r System.Data.dll
215 <li>Running the Example: