1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
|
* 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>Able to connect to Microsoft SQL Server 7/2000 databases</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>Uses TDS Protocol Version 7.0</li>
<li><a href="http://www.go-mono.com/tds-providers.html">Design of the Microsoft SQL Server, Sybase, and TDS Providers in Mono</a></li>
<li>Works in the SQL# command-line and GTK# GUI version</li>
</ul>
** Action plan
<ul>
<li>Connection timeouts is being developed now</li>
<li>Needs more testing</li>
<li>Would like to figure out how to connect via Trusted_Connection or Integrated Security</li>
<li>Start work on TDS Protocol Version 8.0 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>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.</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. For SqlClient to work with MSDE, you will
need to change the setting.</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>Mono's SqlClient does not support trusted connections
nor integrated security. You can not use this when connecting. You need
to explicitly use a User ID and Password
authenticated by SQL Server.</li>
<li>Has a connection string format:
<pre>
Server=hostname;Database=databaseName;User ID=userid;Password=password
</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:
<pre>
using System;
using System.Data;
using System.Data.SqlClient;
public class Test
{
public static void Main(string[] args)
{
string connectionString =
"Server=localhost;" +
"Database=pubs;" +
"User ID=myuserid;" +
"Password=mypassword;";
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 = reader["fname"];
string LastName = 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>
<li>Build on Windows via Cygwin:
<pre>
mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe \
TestExample.cs -r System.Data.dll
</pre>
</li>
</ul>
</li>
<li>Running the Example:
<pre>
mono TestExample.exe
</pre>
</li>
</ul>
|
