Transcript Overview of ADO.NET Whidbey

Scalable Development, Inc.
Building systems today that perform tomorrow.
Intro to Sql Server
Yukon for Developers
Wallace
B. McClure
Scalable
Development, Inc.
.NET Resources
– www.asp.net
 AspAdvice – www.aspadvice.com
 Windows Forms – www.windowsforms.net
 Architecture – msdn.microsoft.com/architecture
 .NET News – www.dotnetwire.com
 ASP.NET
Agenda
 Introduction
to Yukon for developers.
 CLR
Integration.
 New .NET Features.
 TSQL.
 Other features.
 Caution:
 Yukon
code is beta 1.
 .NET Framework is Alpha quality.
 Limited Documentation.
What is Yukon?
 Next
version of the MS Sql Server
database.
 Integration of the .NET Framework into
the engine.
 Beta 2 expected sometime in the early
summer.
 Shipment?
Sql Workbench (SWB)
No more Enterprise Manager.
 No more Sql Server Manager (for the moment).
 VS.NET inspired management tool.
 Lots of pieces are disabled at this time.
 Let’s take a look at it…………………

Developer Oriented Featureset

CLR in the database.
 Stored
Procedures.
 Triggers.
 Functions.
Scrollable Server Side Cursors.
 TSQL Enhancements.
 Full-Text Search.
 XML.

CLR in the Database
Yukon database engine hosts the CLR.
 Mechanism to put procedural logic into the
database.
 .NET Whidbey framework will be integrated
into the engine.
 Calls to CLR and TSQL objects will be the
same.
 Similar, but safer than extended stored
procedures.

When to use what?
 CLR
Object:
 Complicated
procedural logic.
 Computationally intensive logic.
 TSQL
 Set
Object:
oriented operations.
 Data oriented operations
New .NET Objects
System.Data.SqlServer.
 SqlContext.

 GetTriggerContext.
 Insert.
 Update.
 Delete.
– access to the current command.
 GetConnection – access to the current connection.
 GetCommand

SqlProcedure attribute.
CLR Stored Procedure Code
[SqlProcedure] public static void sp_Add_URL_DOTNET(string pstrUrl)
{
SqlCommand sqlCm = SqlContext.GetCommand();
string strSql;
string strDomainName = CalculateDomainName(pstrUrl);
string strSearchCode = CalculateSearchCode(pstrUrl);
try
{
strSql = "select count(*) from tblSearchUrl where UrlAddress='" +
SqlEscape(pstrUrl) + "' and DomainName='" + strDomainName + "' and "
+ "SearchCode=" + strSearchCode;
sqlCm.CommandText = strSql;
if ( Convert.ToInt32(sqlCm.ExecuteScalar()) == 0 ) {
CLR Stored Procedure Code (con’t.)
strSql = "insert into tblSearchUrl (UrlAddress, UrlStatus, DomainName,
SearchCode )
values (" +
"'" + pstrUrl + "', 'NEW_URL', '" +
SqlEscape(strDomainName) + "', " + strSearchCode + ")";
sqlCm.CommandText = strSql;
sqlCm.ExecuteNonQuery();
}
}
catch (System.Exception sysExc)
{
EventLog.WriteEntry("dbWebSearch", "Error Message: " +
sysExc.Message, EventLogEntryType.Information);
}
finally
{
sqlCm.Dispose();
}
}
CLR Trigger Code
Option Explicit On
Option Strict On
Imports System.Data.SqlServer
Public Class cTimeSheetSql
Public Shared Sub trTimeSheetProjectEntryUpdate()
Dim sqlTrgCxt As SqlTriggerContext = SqlContext.GetTriggerContext
Dim sqlCm As SqlCommand = SqlContext.GetCommand
Dim strSql As String = "update tblProjectEntry set DateUpdated='" &
DateTime.Now.ToString() &
"' where tblProjectEntryId in (select tblProjectEntryId from inserted)"
'Dim strSql As String = "select count(*) from inserted"
Try
If (sqlTrgCxt.TriggerAction = Sql.TriggerAction.Update) Then
sqlCm.CommandText = strSql
sqlCm.CommandType = CommandType.Text
sqlCm.ExecuteNonQuery()
End If
Finally
sqlCm = Nothing
End Try
End Sub
Trigger Installation
CREATE ASSEMBLY as TimeSheetObjects FROM
'C:\Documents and Settings\Wallym\My Documents\Visual Studio
Projects\Local - TimeSheet Project\Local - TimeSheet
Project\bin\timeSheetObjectsNS.dll'
WITH permission_set = safe
CREATE TRIGGER [dbo].[tr_tblProjectEntry_Update] ON
[dbo].[tblProjectEntry]
AFTER UPDATE AS EXTERNAL name
TimeSheetObjectsNS:[TimeSheetObjectsNS.cTimeSheetSql]::trTimeSheetP
rojectEntryUpdate
Scrollable Server Side Cursors
SqlConnection sqlCn = new SqlConnection(strCn);
SqlCommand sqlCm = new SqlCommand();
SqlResultSet sqlRs;
sqlCm.Connection = sqlCn;
sqlCn.Open();
sqlCm.CommandText = strSql;
sqlCm.CommandType = CommandType.Text;
sqlRs = sqlCm.ExecuteResultSet(ResultSetOptions.Updatable);
if ( sqlRs.HasRows == true )
{
while(sqlRs.Read())
{
//do something.......
sqlRs.Update();
}
}
sqlRs.Close();
T-Sql Enhancements
 PIVOT/UNPIVOT.
 TRY/CATCH.
 Recursive
Queries. Analogous to
Oracle’s CONNECT BY PRIOR.
 Wait.
 Others.
Full-Text Search Enhancements
Seems to be faster than Sql2k FT.
 More scalable than Sql2k FT.
 Can search through multiple columns at one
time using Contains and FreeText commands.

Tons of other enhancements:

New language constructs for
 Security.
 Replication.
 Notification
Services.
 Triggers.
XML Query.
 XML DataType.
 Service Broker.
 User Defined Types.
 Reporting Services.

Scalable Development, Inc.
Building systems today that perform tomorrow.
Questions?
 865-693-3004.
 [email protected]
 Don’t
ask about shipment dates
because I know nothing about them.
END