Skip to main content

Posts

Showing posts from September, 2005

Sending custom resultset -- SQL Server 2005

This article would explain in detail (with complete code sample) the way to return custom resultsets to the end user using CLR in SQL Server 2005. Things to know before we get started: 1) SqlPipe() -- Its the job of the SqlPipe object to send results back to the stored proc or for that matter UDF etc., 2) SqlDataRecord -- If we want to return resultset (more than one record) make use of this new object which is introduced in ASP.NET 2.0. For that we need to first create a schema using SqlMetaData objects. With this small introduction lets get our hands dirty by writing a small SP which would return more than records. 1. Open VS.NET 2005 2. Create C# based Database project 3. Right click on the solution and choose Add >> Stored proceedure >> name the file as SPReturningResultSet 4. Copy paste the below code into that file using System; using System.Data; using System.Data.Sql; using System.Data.SqlServer; using System.Data.SqlTypes; using System.Data.SqlClient; public part

SP to rename a directory using CLR in SQL Server 2005

This article would explain in detail about the way to rename a physical directory using CLR in SQL Server 2005. The complete source code is attached with this article. 1. Open VS.NET 2005 2. Create C# based Database project 3. Right click on the solution and choose Add >> Stored proceedure 4. Copy paste the below code into that file using System; using System.Data; using System.Data.Sql; using System.Data.SqlServer; using System.Data.SqlTypes; using System.IO; public partial class StoredProcedures { [SqlProcedure] public static void RenameDirectory(String strOldName, String strNewName) { SqlPipe objPipe; objPipe = SqlContext.GetPipe(); try { //Check for directory existance //Note: Since it is for demo purpose I haven't done the complete validation if (Directory.Exists(strNewName)) { objPipe.Send("Specified directory already exists."); } else // Rename the Directory { //Directory.CreateDirectory(strNewName); Directory.Move(strOldName, strN