1 / 18

Intro to Sql Server Yukon for Developers

Scalable Development, Inc. Building systems today that perform tomorrow. Intro to Sql Server Yukon for Developers. Wallace B. McClure Scalable Development, Inc. .NET Resources. ASP.NET – www.asp.net AspAdvice – www.aspadvice.com Windows Forms – www.windowsforms.net

zarek
Download Presentation

Intro to Sql Server Yukon for Developers

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related