System.Data.SqlClient.SqlCommand

/// <summary>
/// Definitions (and values) for SQL Server Signature queries
/// </summary>
class SigSigningProvider_SQLServer_Server
{
    #define.SystemDataSqlDbType('System.Data.SqlDbType')
    #define.NVarChar('NVarChar')
    #define.DateTime('DateTime')
    #define.VarBinary('VarBinary')
    #define.Int('Int')
    #define.SystemConvert('System.Convert')
    #define.ToBase64String('ToBase64String')
    #define.FromBase64String('FromBase64String')

    #define.ElementNameSignatureList('signaturelist')
    #define.ElementNameSignature('signature')
    #define.AttrNameStart('start')
    #define.AttrNameLength('length')

    #define.PosStart(1)
    #define.PosLength(2)
    #define.PosSignature(3)

    System.Data.SqlClient.SqlConnection conn;
}
/// Verify if there is a valid certificate for the <paramref name="_signeeId" />
/// </summary>
/// <param name="_signeeId">
/// Certificate signee
/// </param>
/// <returns type="boolean">
/// true, if a vaild certificate is found for signee; otherwise false
/// </returns>
/// <exception cref="Exception::Error">
/// SQL Server was unable to validate certificate
/// </exception>
public boolean hasValidCertificateSQL(SIGSQLCertSubject _signeeId)
{
    #LocalMacro.SelectStmt
        ' SELECT COUNT(*) FROM sys.certificates '
        + ' WHERE subject = @s'
        + ' AND start_date <= @f'
        + ' AND expiry_date > @t'
        + ' AND pvt_key_encryption_type = N\'PW\''
    #EndMacro
    #define.ParmNameSubject('@s')
    #define.ParmNameStartDate('@f')
    #define.ParmNameExpiryDate('@t')

    System.Data.SqlClient.SqlCommand                cmd;
    System.Data.SqlClient.SqlParameter              param;
    System.Data.SqlDbType                           type;
    System.Data.SqlClient.SqlParameterCollection    params;
    str                                             stmtText;
    int                                             intRes;
    boolean                                         ret = false;
    utcdatetime                                     utcToday;
    str                                             errMsg;
    ;

    utcToday = DateTimeUtil::newDateTime(systemdateget(),0);
    stmtText = #SelectStmt;

    try
    {
        new InteropPermission(InteropKind::ClrInterop).assert();
        conn.Open();
        cmd = conn.CreateCommand();
        cmd.set_CommandText(stmtText);

        params = cmd.get_Parameters();
        type = CLRInterop::parseClrEnum(#SystemDataSqlDbType,#NVarChar);
        param = params.Add(#ParmNameSubject,type);
        param.set_Value(_signeeId);

        type = CLRInterop::parseClrEnum(#SystemDataSqlDbType,#DateTime);
        param = params.Add(#ParmNameStartDate,type);
        param.set_Value(utcToday);

        param = params.Add(#ParmNameExpiryDate,type);
        param.set_Value(utcToday);

        intRes = cmd.ExecuteScalar();
        ret = (intRes > 0);
        conn.Close();
    }
    catch
    {
        errMsg = AifUtil::getClrErrorMessage();
        try
        {
            conn.Close();
        }
        catch
        {
            ret = false;
        }
        throw error(errMsg ? errMsg : "@SYP1558");
    }
    return ret;
}
//---------------------------------------
/// <summary>
/// Revoke permission to certificate
/// </summary>
/// <param name="_id">
/// Certificate Id for certificate to have permissions revoked
/// </param>
public void revokeCertificateSQL(SIGCertificateId _id)
{
    #LocalMacro.RevokeStmt
        'ALTER CERTIFICATE \%1 REMOVE PRIVATE KEY'
    #EndMacro
    System.Data.SqlClient.SqlCommand    cmd;
    str                                 stmtText;
    str                                 name;
    str                                 subject;
    ;

    name = this.getNameForId(_id);
    if (!name)
    {
        return;
    }
    subject = this.getSubjectForId(_id);
    if (subject != curuserid()
        && (!hasSecuritykeyAccess(securitykeynum(AdminSetup), AccessType::Delete)))
    {
        return;
    }
    stmtText = strfmt(#RevokeStmt,name);

    try
    {
        new InteropPermission(InteropKind::ClrInterop).assert();
        conn.Open();
        cmd = conn.CreateCommand();
        cmd.set_CommandText(stmtText);

        cmd.ExecuteNonQuery();
        conn.Close();
    }
    catch
    {
        try
        {
            conn.Close();
        }
        catch
        {
            stmtText = '';
        }
    }
}
//----------------------------------------------
/// <summary>
/// Retrieve certificate name base on certificate Id (<paramref name="_id" />
/// </summary>
/// <param name="_id">
/// Certificate Id
/// </param>
/// <returns type="str">
/// Certificate name
/// </returns>
public str getSubjectForId(SIGCertificateId _id)
{
    #LocalMacro.SelectStmt
        ' SELECT subject'
        + ' FROM sys.certificates '
        + ' WHERE certificate_id = @id'
    #EndMacro
    #define.ParmNameId('@id')

    System.Data.SqlClient.SqlCommand                cmd;
    System.Data.SqlClient.SqlParameter              param;
    System.Data.SqlDbType                           type;
    System.Data.SqlClient.SqlParameterCollection    params;
    str                                             stmtText;
    str                                             subject;
    ;

    stmtText = #SelectStmt;

    try
    {
        new InteropPermission(InteropKind::ClrInterop).assert();
        conn.Open();
        cmd = conn.CreateCommand();
        cmd.set_CommandText(stmtText);

        params = cmd.get_Parameters();
        type = CLRInterop::parseClrEnum(#SystemDataSqlDbType,#Int);
        param = params.Add(#ParmNameId,type);
        param.set_Value(_id);

        subject = cmd.ExecuteScalar();
        conn.Close();
    }
    catch
    {
        try
        {
            conn.Close();
        }
        catch
        {
            subject = '';
        }
    }
    return subject;
}
//------------------------------------------------
/// <summary>
/// Revoke permission to certificate
/// </summary>
/// <param name="_id">
/// Certificate Id for certificate to have permissions revoked
/// </param>
public void revokeCertificateSQL(SIGCertificateId _id)
{
    #LocalMacro.RevokeStmt
        'ALTER CERTIFICATE \%1 REMOVE PRIVATE KEY'
    #EndMacro
    System.Data.SqlClient.SqlCommand    cmd;
    str                                 stmtText;
    str                                 name;
    str                                 subject;
    ;

    name = this.getNameForId(_id);
    if (!name)
    {
        return;
    }
    subject = this.getSubjectForId(_id);
    if (subject != curuserid()
        && (!hasSecuritykeyAccess(securitykeynum(AdminSetup), AccessType::Delete)))
    {
        return;
    }
    stmtText = strfmt(#RevokeStmt,name);

    try
    {
        new InteropPermission(InteropKind::ClrInterop).assert();
        conn.Open();
        cmd = conn.CreateCommand();
        cmd.set_CommandText(stmtText);

        cmd.ExecuteNonQuery();
        conn.Close();
    }
    catch
    {
        try
        {
            conn.Close();
        }
        catch
        {
            stmtText = '';
        }
    }
}

转载于:https://my.oschina.net/fandyx/blog/374800