Intro to Sql Server Yukon for Developers

1. Scalable Development, Inc. Building systems today that perform tomorrow. Intro to Sql ServerYukon for Developers • Wallace B. McClure • Scalable Development, Inc.

2. .NET Resources • ASP.NET – www.asp.net • AspAdvice – www.aspadvice.com • Windows Forms – www.windowsforms.net • Architecture – msdn.microsoft.com/architecture • .NET News – www.dotnetwire.com

3. 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.

4. 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?

5. 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…………………

6. Developer Oriented Featureset • CLR in the database. • Stored Procedures. • Triggers. • Functions. • Scrollable Server Side Cursors. • TSQL Enhancements. • Full-Text Search. • XML.

7. 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.

8. When to use what? • CLR Object: • Complicated procedural logic. • Computationally intensive logic. • TSQL Object: • Set oriented operations. • Data oriented operations

9. New .NET Objects • System.Data.SqlServer. • SqlContext. • GetTriggerContext. • Insert. • Update. • Delete. • GetCommand – access to the current command. • GetConnection – access to the current connection. • SqlProcedure attribute.

10. 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 ) {

11. 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(); } }

12. 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

13. 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]::trTimeSheetProjectEntryUpdate

14. 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();

15. T-Sql Enhancements • PIVOT/UNPIVOT. • TRY/CATCH. • Recursive Queries. Analogous to Oracle’s CONNECT BY PRIOR. • Wait. • Others.

16. 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.

17. Tons of other enhancements: • New language constructs for • Security. • Replication. • Notification Services. • Triggers. • XML Query. • XML DataType. • Service Broker. • User Defined Types. • Reporting Services.

18. Scalable Development, Inc. Building systems today that perform tomorrow. Questions? • 865-693-3004. • wallym@scalabledevelopment.com • Don’t ask about shipment dates because I know nothing about them. END