Starting a Visual Studio 2008 SQL Server Project
Once you have decided to program a CLR routine, the first step is to start Visual Studio 2008 and create a new project. Visual Studio includes a project template for SQL Server projects, which automatically creates all of the necessary references and can create appropriate empty classes for all of the SQL Server CLR routine types. Although you could use a Class Library template instead and do all of this manually, that’s not an especially efficient use of time. So we definitely recommend that you use the SQL Server Project template when developing CLR routines.
The final step in adding the new stored procedure is to name it. The Stored Procedure template is selected, and the procedure has been named GetSalesPerTerritoryByMonth. Developers should remember that, just as in naming T-SQL stored procedures, descriptive, self-documenting names go a long way toward making development and maintenance easier.
Anatomy of a Stored Procedure
After the new stored procedure has been added the project, the following code will be appear in the editing window:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetSalesPerTerritoryByMonth()
{
// Put your code here
}
};
Notice that the Microsoft.SqlServer.Server and System.Data.SqlTypes namespaces have been automatically included in this project. Both of these namespaces have very specific purposes within a routine and will be necessary within most SQL Server CLR projects.
The Microsoft.SqlServer.Server namespace is necessary, as previously mentioned, for the attributes that must decorate all routines to be hosted within SQL Server. In this case, the GetSalesPerTerritoryByMonth method has been decorated with the SqlProcedure attribute. This indicates that the method is a stored procedure. The method has also been defined as static. Since this method will be called without an object instantiation, it would not be available if not defined as static. The Microsoft.SqlServer.Server namespace is also included in order to provide access to the calling context, for data access and returning data.
The System.Data.SqlTypes namespace provides datatypes that correspond to each of the SQL Server datatypes. For instance, the equivalent of SQL Server’s INTEGER datatype isn’t .NET’s System.Int32 datatype. Instead, it’s SqlTypes.SqlInt32. Although these types can be cast between each other freely, not all types have direct equivalents. Many of the SQL Server types have slightly different implementations than what would seem to be their .NET siblings. For that reason, and to provide some insulation in case of future underlying structural changes, it’s important to use these types instead of the native .NET types when dealing with data returned from SQL Server, including both parameters to the routine and data read using a SqlDataReader or DataSet object.
Aside from the included namespaces, note that the return type of the GetSalesPerTerritoryByMonth method is void. SQL Server stored procedures can return either 32-bit integers or nothing at all. In this case, the stored procedure won’t have a return value. That’s generally a good idea, because SQL Server will override the return value should an error occur within the stored procedure; so output parameters are considered to be a better option for returning scalar values to a client. However, should a developer want to implement a return value from this stored procedure, the allowed datatypes are SqlInt32 and SqlInt16.
Adding Parameters
Most stored procedures will have one or more parameters to allow users to pass in arguments that can tell the stored procedure which data to return. In the case of this particular stored procedure, two parameters will be added to facilitate getting data using a date range. These parameters will be called StartDate and EndDate, and each will be defined as type SqlDateTime.
These two parameters are added to the method definition, just like parameters to any C# method:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetSalesPerTerritoryByMonth( SqlDateTime StartDate,
SqlDateTime EndDate)
{
// Put your code here
}
In this case, these parameters are required input parameters. Output parameters can be defined by using the C# ref (reference) keyword before the datatype. This will then allow developers to use SQL Server’s OUTPUT keyword in order to get back scalar values from the stored procedure. Unfortunately, neither optional parameters nor default parameter values are currently supported by CLR stored procedures.
Source of Information : Apress Accelerated SQL Server
Once you have decided to program a CLR routine, the first step is to start Visual Studio 2008 and create a new project. Visual Studio includes a project template for SQL Server projects, which automatically creates all of the necessary references and can create appropriate empty classes for all of the SQL Server CLR routine types. Although you could use a Class Library template instead and do all of this manually, that’s not an especially efficient use of time. So we definitely recommend that you use the SQL Server Project template when developing CLR routines.
The final step in adding the new stored procedure is to name it. The Stored Procedure template is selected, and the procedure has been named GetSalesPerTerritoryByMonth. Developers should remember that, just as in naming T-SQL stored procedures, descriptive, self-documenting names go a long way toward making development and maintenance easier.
Anatomy of a Stored Procedure
After the new stored procedure has been added the project, the following code will be appear in the editing window:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetSalesPerTerritoryByMonth()
{
// Put your code here
}
};
Notice that the Microsoft.SqlServer.Server and System.Data.SqlTypes namespaces have been automatically included in this project. Both of these namespaces have very specific purposes within a routine and will be necessary within most SQL Server CLR projects.
The Microsoft.SqlServer.Server namespace is necessary, as previously mentioned, for the attributes that must decorate all routines to be hosted within SQL Server. In this case, the GetSalesPerTerritoryByMonth method has been decorated with the SqlProcedure attribute. This indicates that the method is a stored procedure. The method has also been defined as static. Since this method will be called without an object instantiation, it would not be available if not defined as static. The Microsoft.SqlServer.Server namespace is also included in order to provide access to the calling context, for data access and returning data.
The System.Data.SqlTypes namespace provides datatypes that correspond to each of the SQL Server datatypes. For instance, the equivalent of SQL Server’s INTEGER datatype isn’t .NET’s System.Int32 datatype. Instead, it’s SqlTypes.SqlInt32. Although these types can be cast between each other freely, not all types have direct equivalents. Many of the SQL Server types have slightly different implementations than what would seem to be their .NET siblings. For that reason, and to provide some insulation in case of future underlying structural changes, it’s important to use these types instead of the native .NET types when dealing with data returned from SQL Server, including both parameters to the routine and data read using a SqlDataReader or DataSet object.
Aside from the included namespaces, note that the return type of the GetSalesPerTerritoryByMonth method is void. SQL Server stored procedures can return either 32-bit integers or nothing at all. In this case, the stored procedure won’t have a return value. That’s generally a good idea, because SQL Server will override the return value should an error occur within the stored procedure; so output parameters are considered to be a better option for returning scalar values to a client. However, should a developer want to implement a return value from this stored procedure, the allowed datatypes are SqlInt32 and SqlInt16.
Adding Parameters
Most stored procedures will have one or more parameters to allow users to pass in arguments that can tell the stored procedure which data to return. In the case of this particular stored procedure, two parameters will be added to facilitate getting data using a date range. These parameters will be called StartDate and EndDate, and each will be defined as type SqlDateTime.
These two parameters are added to the method definition, just like parameters to any C# method:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetSalesPerTerritoryByMonth( SqlDateTime StartDate,
SqlDateTime EndDate)
{
// Put your code here
}
In this case, these parameters are required input parameters. Output parameters can be defined by using the C# ref (reference) keyword before the datatype. This will then allow developers to use SQL Server’s OUTPUT keyword in order to get back scalar values from the stored procedure. Unfortunately, neither optional parameters nor default parameter values are currently supported by CLR stored procedures.
Source of Information : Apress Accelerated SQL Server
|
|
0 comments
Post a Comment