DLLs that invoke C # writes in stored procedures

Source: Internet
Author: User
Tags command line datetime filetime net command net command line
Stored Procedures

A problem with a transition filetime has been encountered in a recent project. Time is in C # in the Datetime.tofiletime write to the database, which in the stored procedure to determine the year and quarter became a problem, how to convert time into a normal time format? There is no fromfiletime in SQL Server? In fact, it is very simple to write a DLL for conversion time in C # and then call it in the stored procedure.
The classes for converting time are as follows:
[Guid ("729ba6af-3eff-4b75-b43b-d951a190dbe6")]
public class Filetimeconvert
Public Filetimeconvert ()

public string ConvertTime (long filetime)
String retVal = String. Empty;
if (filetime > 0)
DateTime dt = Datetime.fromfiletime (FILETIME);
RetVal = dt. ToString ();
return retVal;
Note that this GUID we reference in the stored procedure to use this GUID.

For convenience it may be necessary to call in multiple places, we specialize in writing a stored procedure
As follows:

CREATE PROCEDURE Spu_convertfiletimetonormal
@ticks BIGINT,
@returnval varchar () output

DECLARE @src varchar (255)
DECLARE @desc varchar (255)

DECLARE @object int
DECLARE @hr int
EXEC @hr = sp_OACreate ' {729ba6af-3eff-4b75-b43b-d951a190dbe6} ', @object out

If @hr = 0--If the creation of the object succeeds
EXEC @hr = sp_OAMethod @object, ' ConvertTime ', @returnval out, @ticks
Print @returnval
Print @hr
if (@hr <> 0)
EXEC sp_OAGetErrorInfo @object, @src out, @desc out
SELECT Hr=convert (varbinary (4), @hr), source= @src, description= @desc


--print @hr
print ' Create Object converttime failed '

EXEC sp_OAGetErrorInfo @object, @src out, @desc out
SELECT Hr=convert (varbinary (4), @hr), source= @src, description= @desc

EXEC @hr = sp_OADestroy @object

IF @hr <> 0
EXEC sp_OAGetErrorInfo @object


This time we're still in the final step. Register DLL
Enter Regasm-codebase path under the. NET command line

OK, now we can call this stored procedure in other stored procedures to return to normal time.
Give a small example:
EXEC Spu_convertfiletimetonormal @CreateTime, @NormalTime out
@CreateTime: FILETIME Format
@NormalTime: Normal time format string (Spu_convertfiletimetonormal stored procedure return value)

This simple little example illustrates how we call a DLL in a stored procedure
Do you have one more choice to solve the problem?

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.