CLR String TitleCase Function in SQL Server 2005 (CLR Integration)


With new feature of CLR integration we can provide function with in our assemblies which can be accessed by the user in the TSQL statements. Let us now try to create one title case function. First create one library project in visual studio 2005. Then create one class with one function inside that.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace AssemblyFunctions
{

public partial class MySQLFunctions    
{
[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static string TitleCase(string data)
{
string caseData = data.ToString().Substring(0, 1).ToUpper()
+ data.ToString().Substring(1, data.ToString().Length -1).ToLower();

return caseData;
}
}
}

Now we can compile this assembly and keep it at one location. Then move to MSSQL Management Studio and there in query window. execute following commands one bye one
First we need to enable the CLR integration for that we have to use the sp_configure store procedure in the following way

--Configure the clr enabled state
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

Now to access the assembly into our MSSQL Server we have to register it first

--REGISTER CLASS LIBARIES IN SQL SERVER
CREATE ASSEMBLY ASSEMBLYFUNCTIONS 
FROM 'E:\personal\projects\AssemblyFunctions\AssemblyFunctions\bin\Debug\AssemblyFunctions.dll'
WITH PERMISSION_SET = SAFE
GO

To drop that assembly use the following commands

--DROP/UN-REGISTER THE ASSEMBLY 
DROP ASSEMBLY ASSEMBLYFUNCTIONS
GO

Now to access our functions we have to first register our functions, procedures or what ever is there

--REGISTER USER DEFINED FUNCTION IN SQL SERVER 
CREATE FUNCTION TITLECASE(@DATA NVARCHAR(MAX)) RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME AssemblyFunctions.[AssemblyFunctions.MySQLFunctions].TitleCase  GO

Since now all the things are done you can call your respective function

Select dbo.TitleCase('sample')
go


Also this assembly and function will be added to the mssql server. see the image below



'Dev by INNO > C#' 카테고리의 다른 글

[C#] netstat in c#  (0) 2010.05.11
[C#]NETSTAT 구하기  (0) 2010.05.10
[C#] textbox 에서 ctrl + a 했을때 전체선택  (0) 2010.02.11

[C#] netstat in c# 


using System;

using System.Net;

using System.Net.NetworkInformation;


namespace MyNetstat

{

class Program    

{

static void Main(string[] args)

{

IPGlobalProperties ipProperties = IPGlobalProperties.GetIPGlobalProperties();


IPEndPoint[] endPoints = ipProperties.GetActiveTcpListeners();

TcpConnectionInformation[] tcpConnections = ipProperties.GetActiveTcpConnections();


foreach (TcpConnectionInformation info in tcpConnections)

{

Console.WriteLine("Local : " + info.LocalEndPoint.Address.ToString()

+ ":" + info.LocalEndPoint.Port.ToString()

+ "\nRemote : " + info.RemoteEndPoint.Address.ToString()

+ ":" + info.RemoteEndPoint.Port.ToString()

+ "\nState : " + info.State.ToString() + "\n\n");

}

Console.ReadLine();

}

}

}

[C#] NETSTAT 구하기


Process p = new Process();          
StreamReader sr;          
ProcessStartInfo startInfo = new ProcessStartInfo("netstat");
startInfo.Arguments = "-s";
startInfo.UseShellExecute = false;           
startInfo.RedirectStandardOutput = true;          
startInfo.CreateNoWindow = true;
p.StartInfo = startInfo;
p.Start();
sr = p.StandardOutput;
System.Windows.Forms.MessageBox.Show(sr.ReadToEnd());    



[C#] textbox 에서 ctrl + a 했을때 전체선택


c#에서 textbox 에서 ctrl + a 했을때 전체선택되게 하는 소스이다.

 

textbox1.selectall();

 

같은것도 있지만...

 

키이벤트도 처리해줘야하고 번거로움이 있다..

 

그래서 그걸 해결하는게.. 아래의 소스!!

 

소스코드에 아래의 소스만 추가하면 된다.

 

수정 같은거 안해도 된다... 그냥 추가만 하자!

 

protected override bool ProcessDialogKey(Keys keyData)

  {

   switch (keyData)

   {

    case Keys.A | Keys.Control:

     if (this.ActiveControl is TextBox)

     {

      TextBox txt = (TextBox)this.ActiveControl;

      txt.SelectionStart = 0;

      txt.SelectionLength = txt.Text.Length;

      return true;

     }

     break;

   }

   return base.ProcessDialogKey(keyData);

  }

'Dev by INNO > C#' 카테고리의 다른 글

CLR String TitleCase Function in SQL Server 2005 (CLR Integration)  (0) 2010.05.12
[C#] netstat in c#  (0) 2010.05.11
[C#]NETSTAT 구하기  (0) 2010.05.10

+ Recent posts