tailieunhanh - Microsoft SQL Server 2005 Developer’s Guide- Part 12

Microsoft SQL Server 2005 Developer’s Guide- P12:SQL Server 2005 is a feature-rich release that provides a host of new tools and technologies for the database developer. This book is written to help database developers and DBAs become productive immediately with the new features and capabilities found in SQL Server 2005. | Chapter 3 Developing CLR Database Objects 89 capture any errors that may occur during the file import process. Within the Try-Catch loop a new StreamReader named stmReader is created that will be used to read the file from the operating system. The name of the file that will be read is passed into the StreamReader s instantiation call. Then the stmReader s ReadToEnd method is used to read the entire contents of the file into the sContent string variable. After the contents of the file have been read the stmReader StreamReader is closed and the contents of the sContents variable are assigned to the SQL Server column. If any errors occur while the input file is being read then the code in the Catch portion of the Try-Catch structure is executed. Within the Catch block a SqlPipe object named sp is created and then used to send those errors back to the caller of the stored procedure. This code block uses the SqlPipe object which represents a conduit that passes information between the CLR and the calling code. Here the SqlPipe object enables the stored procedure to pass error information to the external caller. Setting the Stored Procedure Security At this point the code is finished for the stored procedure but because of security concerns it still can t execute. By default SQL Server CLR objects can only access database resources and they cannot access external resources. In the case of the usp_ImportFile example the stored procedure needs to access the file system so the default security settings need to be changed. To enable external access you need to open the project s properties and click the Database tab. Then in the Permissions Level drop-down you need to change the value from Safe to External. More information about the CLR security options is presented later in this chapter. Deploying the Stored Procedure After the CLR stored procedure source code has been compiled into an assembly you can then add that assembly to the database and create the CLR stored .