Implementing Managed code in SQL Server 2005 using Visual Studio.NET 2005 - Creating Managed Code using Visual Studio.NET 2005

Note: Before proceeding further, you need to create a database named "emp" in SQL Server 2005 using SQL Server Management Studio with a simple table "emp" as show in the following figure:

Open Start >> Programs >> Microsoft Visual Studio 2005 Beta >> Microsoft Visual Studio 2005

Using Visual Studio.NET 2005, proceed with following steps:

• Go to File >> New Project.
  
  
• Select Project type as Visual Basic >> Database and template as SQL Server Project.  Name the project as SampleSqlServerProject (shown in Fig2) and click on the OK button.
  
  

  

  
  
  
• Provide Server name, security and database information as shown below (Fig 3) and click OK. Make sure to test the connection before proceeding further.
  
  

  

  
  
  
• Right click on "SampleSqlServerProject" in the solution explorer and go to Add >> user-defined function (Fig 4).
  

  

  
  
  
• You will be presented with an "add new item" wizard.  Select "User-Defined Function" within the templates, give the name as "getEmployee.vb" and click on the "Add" button (Fig 5).
  

  

  
  
  
• Make changes to the code as follows (in bold):

ImportsSystem

ImportsSystem.Data.Sql

ImportsSystem.Data.Sqlserver

ImportsSystem.Data.SqlTypes

 

PartialPublicClass UserDefinedFunctions

    <SqlFunction()> _

    PublicSharedFunction getEmployee(ByValEmpNoAsSqlString)AsSqlString

       ' Add your code here

       DimcmdAsSqlCommand = SqlContext.GetCommand

        cmd.CommandText = "SELECT ename FROM dbo.emp WHERE  EmpNo='" & EmpNo.ToString & "'"

       DimrecAsSqlDataRecord = cmd.ExecuteRow

       Dimename AsString = rec.GetString(0)

       Returnename

    EndFunction

EndClass

• Right click on "SampleSqlServerProject" in the solution explorer and select "rebuild."
  
  
• Right click on "SampleSqlServerProject" in the solution explorer and select "deploy."

Next: Executing the Managed Code from Visual Studio 2005 >>
 

More MS SQL Server Articles
More By Jagadish Chaterjee

Comments On: Implementing Managed code in SQL Server 2005 using Visual Studio.NET 2005

· It is a very good article to start with.The tabular difference between managed and...   >>> Post your comment now!