https päring SQL serveri CLR protseduurist

Igasugu asju võimaldatakse ka andmebaasis teha CLR-iga aga kui vaja teha sertifikaadikindel https postitus võib seda teha nii.

Et serveri sertifikaatide veateadetest lahti saada tuleb kasutada
ServicePointManager.ServerCertificateValidationCallback meetodi ülekirjutamist

using System.Security.Cryptography.X509Certificates;
using System.Net.Security;
using System.Net;
using System.IO;


private static bool ValidateRemoteCertificate( object sender, X509Certificate certificate, X509Chain chain, SslPolicyErrors policyErrors )
{
//siia võib mingi mõistliku veatöötluse juurde arendada, praegu annab alati true, ehk kõikidest vigadest läheb mööda.
return true;
}

CLR protseduur ise

[Microsoft.SqlServer.Server.SqlProcedure]
public static void VeebiParing(SqlString url, SqlString andmed, SqlString kellelt, out SqlString outt)
{
try
{
//see on sertifikaadi veast möödahiilimiseks
ServicePointManager.ServerCertificateValidationCallback += new RemoteCertificateValidationCallback(ValidateRemoteCertificate);

HttpWebRequest myRequest=(HttpWebRequest)System.Net.WebRequest.Create((string)url);

myRequest.Method = "POST";
myRequest.ContentType = "application/x-www-form-urlencoded";


//paneme sisu kokku
StringBuilder postData = new StringBuilder();
//siin paneb andmed külge
postData.Append("Saadame="+(string)kellelt+"&andmeid="+(string)andmed);


//päringu andmete sisu tuleb läbi System.Uri.EscapeUriString lasta, muidu ei lähe läbi veebi.
string data = System.Text.Encoding.GetEncoding("UTF-8").GetBytes(System.Uri.EscapeUriString(postData.ToString()));
myRequest.ContentLength = data.Length;

// Striim veebi saatmiseks

Stream newStream = myRequest.GetRequestStream();
// Saadame minema
newStream.Write(data, 0, data.Length);
newStream.Close();

//loeme vastuse

HttpWebResponse loWebResponse = (HttpWebResponse)myRequest.GetResponse();

Encoding enc = System.Text.Encoding.GetEncoding("UTF-8");
StreamReader loResponseStream = new StreamReader(loWebResponse.GetResponseStream(), enc);
//kuna on protseduur siis nii saab sisulistvastust tagastada
outt = loResponseStream.ReadToEnd();
loResponseStream.Close();
loWebResponse.Close();

}
catch ( WebException ex)
{
outt = "WebException: " + ex.Message;
}
catch (SystemException ex)
{
outt="Error: "+ex.Message;
}
return;

}

Kui andmebaas hakkab CLR alusel protseduuri looma siis NVARCHAR(4000) sisendparameetri võid muuta NVARCHAR(MAX) peale ja ASSEMBLY tuleb teha UNSAFE märgendiga, mis annab CLR protseduurile turvalisuse seisukohalt laiad võimalused ehk kaaluda tasub muude võimaluste kasutamist

BULK INSERT tekstifailist CLR-i kasutades

Sai kogemata SQLSERVER 2008 Web Edition maha müüdud ja siis avastatud, et Integration Services selle versiooni sees ei olegi. Kokkuvõttes tuleb asjad ise teha ja loeme nüüd BULK INSERT käsuga otse tekstifailist:


using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void HansaImport(SqlString fail, SqlString kataloog, SqlString sihttabel, SqlInt32 kustutafail)
{
//teeme sisendparameetrite kontrolli ja lõikame lõpud maha
string siht = Convert.ToString(sihttabel);
siht=siht.Substring(0, (siht.Length > 30 ? 30 : siht.Length));
string kat = Convert.ToString(kataloog);

kat = kat.Substring(0, (kat.Length > 30 ? 30 : kat.Length));
string fai = Convert.ToString(fail);
fai = fai.Substring(0, (fai.Length > 20 ? 20 : fai.Length));
string impafail = kat + fai;
if (System.IO.File.Exists(impafail))
{
using (SqlConnection konn = new SqlConnection("Context connection=true"))
{
SqlCommand komm = new SqlCommand("TRUNCATE TABLE " + siht + "; BULK INSERT " + siht + " FROM '" + impafail + "' WITH (FIELDTERMINATOR ='\t',ROWTERMINATOR ='\n', BATCHSIZE = 10000)", konn);
konn.Open();
komm.ExecuteNonQuery();
konn.Close();
if (kustutafail == 1)
{
System.IO.File.Delete(impafail);
}
}
}
else
{
throw new SystemException("CLR Error !! Missing file: " + fai + " in directory: " + kat);
}
}
}


Kui nüüd värk käivitada Management Studio alt siis kõik OK aga kui panna jooksma kui andmebaasi töö tuleb selline viga

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row

Tekstifailis on kuupäev kujul 23.10.2009

Et asi andmebaasi tööna jooksma saada anname temale kuupäeva formaadi ette
SET DATEFORMAT DMY
EXECUTE [dbo].[HansaImport]
@fail='arved.txt'
,@kataloog='D:\HANSA_IMPORT\'
,@sihttabel='[dbo].[HANSA_ARVED_TEMP]'
,@kustutafail=0

SQL SERVER Agent kasutab SQL SERVERi enda kuupäeva seadeid asjadest aru saamiseks

Räsi arvutav CLR funktsioon MS SQL SERVERILE

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Cryptography;


public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static byte[] ArvutaRasi(SqlString sona) //STRINGINA ANNAB teksti sisse
    {
        System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
        byte[] data = encoding.GetBytes((string)sona); ;
        byte[] result;
        SHA512 shaM = new SHA512Managed(); //SHA512 või mõni muu
        result = shaM.ComputeHash(data);
        return result; //bitijada tagasi
    }
};

Blogged with the Flock Browser

SQL2005 CLR meetodi dünaamiline väljakutsumine teisest CLR meetodist

Kuna kulus kaks päeva teadasaamiseks, kuidas asju teha ei saa nüüd siis nüüd puust ja punaselt

CLR tegemine SQL2005 tuleb teha .NET Framework Versiooniga 2.0 kuna LINQ-i SQL2005 veel ei tunne

Ehk probleem, et teeme ühe ASSEMBLY ja selles sees olevast meetodist on vaja pöörduda teise ASSEMBLY meetodite poole.
SQL2005 lubab välja kutsuda ainult SQLSERVER-i enda sisse laetud ASSEMBLY-sid. Võimalus tegelikult ka mujalt , näiteks GAC-ist aga
võib pista kisama. Need DLL-id mida saab SQL2005 ASSEMBLYKS teha on piiratud ja muid DLL-e saab sisse laadida UNSAFE suvandiga.
Aga näide selline, et
ASSEMBLY TurukampusSqlPdf ja selle sees meetod ArvePdf mis pöördub ASSEMBLY Laad meetodite KasLaeb ja Laeb poole

ASSEMBLY Laad meetodid sellised

public partial class Protseduurid //See peab sellise nimega olema, selle kaudu leitakse ülesse
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void KasLaeb( ref String nimi) //siit loeb ka ref parameetri tagasi
{
string s="Tere ";
nimi = s+nimi;
}

[Microsoft.SqlServer.Server.SqlProcedure]
public static void Laeb()
{
string s = "Tere ";
s += "Kere";
}
}


ASSEMLBLY TurukampusSqlPdf meetod ArvePdf tahab nüüd neid kätte saada


using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Reflection.Emit;
using System.Reflection;
using System.Diagnostics;
using System.IO;


public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ArvePdf(SqlString raportkataloog, SqlInt32 invoice_form, SqlInt32 keel, SqlInt64 arvenr,
SqlInt32 arvetyyp, SqlInt32 tenant, SqlString fail)
{
try
{

// assembly laadimine tuleb teha täisnimega
Assembly repf = Assembly.Load("Laad, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null");
Type MyType = repf.GetType("Protseduurid"); //siin on see klassi nimi oluline
if (MyType != null)
{
object MyObj = Activator.CreateInstance(MyType);
//kutsume välja meetodi
MyType.InvokeMember("Laeb", BindingFlags.Default BindingFlags.InvokeMethod, null, null, new object[] { });
//kutsume välja meetodi koos parameetritega
object[] MyParameter = new object[] { "Kuido" };
object tar = new object();
MyType.InvokeMember("KasLaeb", BindingFlags.Default BindingFlags.InvokeMethod, null, tar, MyParameter);
// MyParameter[0].ToString() on nüüd see, mille KasLaeb meetodist parameetrina tagasi loeme

//kirjutame selle bitijadana faili
System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
byte[] sisu = encoding.GetBytes(tar.GetType().FullName + " " + tar.ToString() + " " + MyParameter[0].ToString());
FileInfo fil = new FileInfo((string)fail);
FileStream f = fil.OpenWrite();
f.Write(sisu, 0, sisu.Length);
f.Close();
}

}
catch (SystemException ex)
{
throw ex;
}

}
}

Blogged with the Flock Browser