Developing Managed Code and Executing Within Microsoft SQL Server 2000 - Understanding the concept
(Page 5 of 5 )
All the previous sections focused on how to execute the .NET component. But they didn't really explain the concept behind it. Let us go through some of the most important technical issues.
First of all, you can observe that I created an interface something like the following:
Public Interface ISample
Function getSum(ByVal a As Integer, ByVal b As Integer) As Integer
End Interface
Is the interface really necessary (as I am working with only a single method)? Even though it is not logically necessary, it is technically necessary. The COM architecture is designed with the beginning point as the “interface” itself. So, it is the most suggested way to start, when we deal with COM components.
The method within the interface is being “implemented” in the class “CSample” as follows:
Public Class CSample
Implements ISample
Public Function getSum(ByVal a As Integer, ByVal b As Integer) As Integer Implements ISample.getSum
Return a + b
End Function
End Class
You should observe the ones which are highlighted in red. That gives you a clear idea of how to implement the above-defined interface.
After completing everything, I used the following “extended stored procedures” to deal with external processes:
Sp_OACreate
Sp_OADestroy
Sp_OAGetErrorInfo
Sp_OAMethod
“Sp_OACreate” is specially used to create an object, based on an existing COM component. “Sp_OADestroy” does just the opposite. It removes the COM object created by “Sp_OAcreate” from the memory (freeing the resources). Before destroying the COM object, we need to work with the COM object. We can execute any method of the COM object using “Sp_OAMethod”. If any problem (or failure) occurs (during interaction with the COM component), we can fetch the error information using “Sp_OAGetErrorInfo”. I didn’t use “sp_OASetProperty” as the class “CSample” doesn’t have any properties to be set.
Remarks
This article focused mainly on SQL Server 2000 and not at all on SQL Server 2005 (or SQL Server Yukon). It is not at all suggested to use this type of implementation in SQL Server 2005 as it has its own CLR implementation within the SQL Server engine itself. You can directly use .NET programming within SQL Server 2005 without referring to (or working with) any COM related issues.
I am trying to contribute few more real scenarios using the same concept. Keep watching for the new articles. Any comments, suggestions, bugs, errors, feedback etc. are highly appreciated at jag_chat@yahoo.com.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
