[Itextsharp.dll Download
Amancio Mccrae
I am developing an application in vb.net sharepoint in which I generate PDF files with buttonsI'm using the itextsharp.dll library for PDF generation, the error is as follows, to make 'Debug' in the app all goes well, but when clicking on a button, the application ('Server Error in' / falls 'Application.')reviewed the 'event viewer' and error me the following message:"Exception message:. Could not load file or assembly 'iTextSharp, Version = 5.5.5.0, Culture = neutral, PublicKeyToken = 8354ae6d2174ddca' or one of Its dependencies The system can not find the file specified."
I have the following: I have the DLL loaded into the 'bin / debug' folder and have added the corresponding referencealso attempt to load the DLL from C: \ windows \ microsoft.NET \ .... and nothing took 3 days researching my problem and still can not find the error :(ps: sorry for my English, I speak Spanish
I've never used iTextSharp before, but in doing some searches online, it looks like you can use the NuGet package manager to load iTextSharp into your project. This should take care of loading the appropriate references into your project.
Microsoft introduced the ability to use .NET CLR stored procedures and functions in SQL Server some time ago, starting with SQL Server 2005. Now more than 8 years later I think many developers are like me: I acknowledge the power of CLR routines, but try to avoid using CLR.
Part of the reason for this avoidance has to do with technical considerations. But truthfully for me, part of the reason also has to do with the increased complexity that CLR introduces into development, deployment, and maintenance of the database.
This article will demonstrate an approach to deploying and managing CLR routines that may be more comfortable for T-SQL developers and DBA's, and one that does not involve use of Visual Studio. This approach also encapsulates everything needed to deploy the CLR assembly within the database, meaning that a database backup will store all needed dependencies.
The basic goal of this exercise is to create a stored procedure that when executed will compile C# code, sign the .DLL, register the assembly in SQL, and create the wrapper SQL objects, all within this stored procedure. In this way, deployment of the CLR assembly is as easy as running a stored procedure. Everything is taken care of, and is all in one place: no independent .DLL 's, Visual Studio projects, or C# source to keep track of.
Additionally, this exercise attempts to follow best practices for deployment, such as signing the assembly and properly securing it in SQL. These are things that often get omitted when in a hurry to set up a CLR assembly in SQL.
As easy as 1, 2, 3...11. And that is part of what I mean about the complexity of deploying and maintaining CLR assemblies in SQL: there are lots of steps to learn how to do (and then remember to do them). These steps need to be done every time you deploy this database to a new server. Being able to do all of these things by executing a single stored procedure simplifies things greatly.
We are trying to avoid storing the C# source in a file because we want everything that is needed to create the assembly to be encapsulated in the database. The source could be stored in a table, or, as I have done here, the source code can be stored as a string literal inside the stored procedure.
What I have done is copy-and-pasted the C# source from Visual Studio, then used search-and-replace to replace single quote characters with two single quote characters, and then assigned this string to a variable which will later get written out to a temporary .cs file.
I fully understand and agree that xp_cmdshell can introduce a number of security problems, and is best avoided in production databases. My approach here is that this stored procedure will enable xp_cmdshell temporarily. It will be enabled just long enough to call a batch file that the procedure will dynamically create.
In my opinion, this use of xp_cmdshell is safe and appropriate: it will only be called at deploy-time by an administrator, will be used to execute carefully scripted statements, and will be immediately disabled.
This CLR assembly requires iTextSharp, an open source library for creating PDF 's (from ). Download, and copy the itextsharp.dll file to c:\ServerEnvironment (or a folder of your choosing, updating the script as needed).
CLR code can do anything, including destructive or malicious things. CLR code that does potentially dangerous things (such as deleting files from the file system) gets flagged as "unsafe ". SQL prevents "unsafe " CLR assemblies from being loaded in an effort to protect the server environment from destructive or malicious things. SQL will allow "unsafe " CLR assemblies if one of two things is true: a) the TRUSTWORTHY database property is enabled, or b) the assembly is signed and tied to a key and login in SQL.
TRUSTWORTHY is a bad idea, because basically it says that ANY "unsafe " assembly can be loaded. We don 't want to open the door to load any and every "unsafe " assembly. If we did, a user could register dangerous or malicious .DLLs without the DBA's knowledge. Also, someone could potentially change the .DLL in the file system without the DBA's knowledge--and SQL would then continue to allow users to call methods in the now-rogue assembly. (Think of TRUSTWORTHY as being SQL deeming the entire physical server and everything on it as being safe or "trustworthy".)
Signing the assembly is a much better idea. It is slightly complicated to do, but the concept isn 't too hard. This involves signing the assembly with a cryptographic signature, creating an asynchronous key in SQL based on this signature, creating a SQL login associated with the key, and granting appropriate rights to this login. This in effect gives us the ability to say that the specified user is allowed to load this specific "unsafe " assembly.
Putting this another way, signing the assembly guarantees the DBA that only assemblies approved by the DBA will be used by SQL. I will not dig into what all is going on in the mechanics of signing the code, but will instead just show you how to do it.
Now we can create the assembly in SQL. This terminology can be a little confusing, as the .NET assembly is already created (i.e. the C# code has already been compiled and the .DLL already exists.) Really what we are doing here is "registering " the assembly for use by SQL, though the SQL command is "CREATE ASSEMBLY ".
NOTE: This particular assembly (that renders PDF documents) requires "unsafe " operations. Some assemblies may not require "unsafe " operations, and can thus have a different setting for PERMISSION_SET.
There are a lot of steps involved in properly deploying a CLR assembly in SQL. But the good news is that once these steps are encapsulated within a stored procedure, the procedure can be executed any time the CLR source code is updated and whenever you need to deploy the CLR assembly to a different machine.
Both the C# source and the script to build, sign and register it are resident in the SQL database--and as such get backed up and restored along with all other SQL objects. The DBA can see exactly what is going on in the assembly--both in terms of the C# source and the various compiler options--all in one place, by simply looking at the source of this stored procedure. Also, the DBA doesn't even need to open Visual Studio: everything can be done from native T-SQL
Visual Studio is a fine development tool, and is useful when developing the C# code. But in my opinion, a deployment script implemented in a SQL stored procedure is a much nicer way for a T-SQL developer or DBA to deploy and update CLR assemblies in SQL.
Will I use CLR for everything? No, definitely not. But now when I need to use a CLR assembly I can now do so with greater safety and greater ease than I could without the techniques described in this article.
(See attached file for full source code. You can download and execute BuildAndRegisterCLRAssembly.sql to create all procedures and functions referenced here, as well as to execute the example shown above.)
795a8134c1