Microsoft SQL Server 2008 - Tài liệu hướng dẫn giảng dạy dành cho giảng viên

n append, Encoding encoding, Int32 bufferSize)
 at System.IO.StreamWriter..ctor(String path, Boolean append)
 at StoredProcedures.WriteToFile(String fileName, String message)
This error is expected. To ensure the security of the server, Microsoft SQL Server only grants a limited set of permissions to an assembly. The file is a secured resource and by default a stored procedure is not allowed to access secured resources. If an assembly needs additional permissions, it must be specifically granted.
Task 5: Unloading an Assembly and Stored Procedure Manually Using DDL Statements
Type the following two lines of code:
DROP PROCEDURE WriteToFile
DROP ASSEMBLY Permissions
Select the above lines of code.
Press F5.
This will unload the stored procedure and assembly.
Task 6: Loading an Assembly with a Specific Permission Set
Type the following lines of code:
ALTER DATABASE AdventureWorks SET TRUSTWORTHY ONCREATE ASSEMBLY Permissions
FROM 'C:\data\Permissions\Permissions\bin\Permissions.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
Note
Make sure that there are no line breaks in the folder name when typing the code.
An important distinction between this CREATE ASSEMBLY statement and the previous one is that this time you’re specifying the permission set to be used by the assembly. There are three different permission sets: SAFE, EXTERNAL_ACCESS, and UNSAFE.
SAFE is the default permission set and works for the majority of scenarios. When code in an assembly runs under the SAFE permission set, it can only do computation and data access within the server via the in-process managed provider.
EXTERNAL_ACCESS is a code permission set that addresses scenarios where the code needs to access resources outside the server such as the files, network, registry and environment variables. 
UNSAFE code permission is for those situations where an assembly is not verifiably safe or requires additional access to restricted resources, such as the Win32 API.
Highlight the above lines of code so that they are selected.
Press F5.
Running this DDL statement loads the Permissions.dll assembly into Microsoft SQL Server with the EXTERNAL_ACCESS permission set. 
Type the following lines of code again (or highlight the ones you had typed previously):
CREATE PROCEDURE WriteToFile
@FILENAME NVARCHAR(256),
@MESSAGE NVARCHAR(4000)
AS
EXTERNAL NAME [Permissions].[StoredProcedures].WriteToFile
Select the above lines of code.
Press F5.
Type the following line of code:
EXEC WriteToFile 'c:\test.txt', 'This is a test'
Highlight the above line of code so that it is selected.
Press F5.
This time, the command is completed successfully.
In SQL Server Management Studio, select the File | Exit menu command. If prompted to save your changes, click No.
Task 7: Viewing the File
Open Windows Explorer and find the file you just created.
Double-click the file to load it into NotePad.
You can see that the message was written from the managed code into this file.
Close Notepad.
In this exercise, you began working with SQL Server Management Studio, part of Microsoft SQL Server . You learned that .NET assemblies are loaded into Microsoft SQL Server using a giver permission set. By default, assemblies are loaded with limited privileges and cannot access external resources such files. To give an assembly access to external resources, it must be loaded with a different additional permission set such as EXTERNAL_ACCESS. You also learned how to manually load and unload assemblies and stored procedures using DDL.
Exercise 4:Creating User-Defined Aggregates
In this exercise, you will learn how to extend the list of installed aggregate functions by creating new user-defined aggregates whose implementation is in managed code.
Task 1: Testing the User-Defined Aggregate Template
An aggregate function performs a calculation on a set of values and returns a single value. T-SQL already comes with several built-in aggregate functions such as COUNT, AVG and SUM. With CLR integration, you will now be able to create your own aggregates using managed code. For this exercise, you will create a user-defined aggregate that performs string concatenation.
In Visual Studio , select the File | New | Project menu command.
Create a new C# project, using the SQL Server Project template.
In the Name field, enter “UserDefinedAggregates”.
In the Location field, enter "C:\data".
Click OK.
In the Add Database Reference dialog box, select the existing reference to the AdventureWorks database.
Click OK.
In the Solution Explorer, right-click UserDefinedAggregates and select Add | New Item.
In the Add New Item dialog box, click Aggregate.
Type UTSConcatenate as the name
Click Add.
In the Code Editor, at the top of the code window, type the following code:
using System.Text;
The aggregate you are creating will be using the StringBuilder class of the System.Text namespace. Importing System.Text will reduce typing.
Above the struct declaration, just below the serialization flag, you must indicate that you will be performing the serialization yourself. To do this, add the following attribute to the struct
[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 8000)]
You must also implement the IBinarySerialize interface so SQL knows how to retain the values in the Stringbuilder within the struct
public struct UTSConcatenate : IBinarySerialize
In the UTSConcatenate class, before public void Init(), type the following code:
 private StringBuilder sb;
 private bool firstConcat ;
The StringBuilder object sb will be used to perform fast string concatenations. The Boolean flag firstConcat indicates whether a concatenation is the first concatenation.
In the Init procedure, before the closing bracket, type the following code:
 sb = new StringBuilder();
 firstConcat = true;
This will initialize the StringBuilder object when the aggregate is invoked.
In the Accumulate procedure, before closing bracket, type the following code:
 public void Accumulate(SqlString value)
 {
 if (firstConcat)
 {
 sb.Append(value);
 firstConcat = false;
 }
 else
 {
 sb.Append(",");
 sb.Append(value);
 }
 }
These lines of code perform the actual string concatenation. Accumulate is called for each row in the result set from which values will be aggregated.
In the Merge procedure, before the closing bracket, type the following code:
	Accumulate(Group.sb.ToString());
The Merge method is used by the query processor to merge another instance of this aggregate class with another instance as part of partial computations.
In the Terminate procedure, delete the following lines of code:
 ’ Put your code here
 return new SqlString("");
In the Terminate() routine, before the closing bracket, type the following code:
 return sb.ToString();
This line of code converts the StringBuilder object to a regular String object and returns that String back to the calling code. Terminate is invoked when the entire result set has been processed.
As the final step, we will need to add a Read and Write procedure required by the IBinarySerialize interface. This is used to Serialize and De-Serialize the StringBuilder object so that it can be passed as a parameter into the Merge procedure.Add the following procedures to the class before the closing bracket.
 public void Read(System.IO.BinaryReader r)
 {
 sb = new StringBuilder(r.ReadString());
 }
 public void Write(System.IO.BinaryWriter w)
 {
 w.Write(sb.ToString());
 } 
The completed UTSConcatenate class should now look like the following:
using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 8000)]
public struct UTSConcatenate : IBinarySerialize
{
 private StringBuilder sb;
 private bool firstConcat ;
 public void Init()
 {
 sb = new StringBuilder();
 firstConcat = true;
 }
 public void Accumulate(SqlString value)
 {
 if (firstConcat)
 {
 sb.Append(value);
 firstConcat = false;
 }
 else
 {
 sb.Append(",");
 sb.Append(value);
 }
 }
 public void Merge(UTSConcatenate Group)
 {
 Accumulate(Group.sb.ToString());
 }
 public SqlString Terminate()
 {
 return sb.ToString();
 }
 public void Read(System.IO.BinaryReader r)
 {
 sb = new StringBuilder(r.ReadString());
 }
 public void Write(System.IO.BinaryWriter w)
 {
 w.Write(sb.ToString());
 } 
}
In the Solution Explorer, right-click UserDefinedAggregates and select Rebuild.
Task 4: Deploying a User-Defined Function
Switch to SQL Server Management Studio.
Switch to Visual Studio .
In the Solution Explorer, right-click UserDefinedAggregates, and select Deploy.
Task 5: Testing the User-Defined Aggregate
In Microsoft SQL Server Management Studio, in the existing query, type the following lines of code:
SELECT dbo.UTSConcatenate(Name) FROM production.location
Select the above lines of code.
Press F5.
The results should be:
Debur and Polish,Final Assembly,Finished Goods Storage,Frame Forming,Frame Welding,Metal Storage,Miscellaneous Storage,Paint,Paint Shop,Paint Storage,Sheet Metal Racks,Specialized Paint,Subassembly,Tool Crib
Select the File | Exit menu command. If prompted to save changes, click No.
In Visual Studio , select the File | Close Solution menu command. 
This exercise demonstrated how to create a user-defined aggregate using managed code.
Lab Summary
This lab showed you how Microsoft SQL Server and Visual Studio combine to provide a new level of power and flexibility when developing database and data-centric applications. You learned that the .NET Common Language Runtime is now hosted in Microsoft SQL Server so that you can stored procedures, user-defined functions and aggregates using managed code with languages such as Visual Basic .NET and Visual C#. Although managed code does not replace T-SQL, the ability to use managed code is an important new tool in the database developer's toolbox.