--- ADO.NET --- Proceduri stocate

procedură stocată este un bloc numit de cod SQL stocat într-o bază de date. Procedurile stocate pot fi create pentru a returna un set de rânduri sau tipuri de date scalare și pentru a efectua altele acțiunile necesare(de exemplu, inserări, actualizări sau ștergeri); pot lua orice număr de parametri opționali. Rezultatul este o unitate de lucru care se comportă ca o funcție tipică, doar că rezidă în depozitul de date și nu în obiectul de afaceri binar.

Apelarea unei proceduri stocate cu un obiect de comandă implică specificarea numelui procedurii stocate, adăugarea definiției fiecărui parametru de procedură și executarea comenzii folosind una dintre metodele ExecuteXXX.

Pe această etapă Baza de date AutoLot are o singură procedură stocată numită GetPetName care are următorul format:

Să adăugăm un apel de procedură stocată la ansamblul AutoLotDAL.dll discutat în articolul anterior:

Șir public LookUpPetName(int carId) ( șir carPetName = șir.Empty; // Setați numele procedurii stocate folosind (SqlCommand cmd = new SqlCommand("GetPetName", this.connect)) ( cmd.CommandType = CommandType.StoredProcedure; // Introduceți param.SqlParameter param = nou SqlParameter(); param.ParameterName = „@carID”; param.SqlDbType = SqlDbType.Int; param.Value = carId; //Parametrii sunt considerați introduși în mod implicit, dar totuși pentru claritate: param. Direction = ParameterDirection.Input; cmd.Parameters.Add(param); // Parametru de ieșire param = nou SqlParameter(); param.ParameterName = "@petName"; param.SqlDbType = SqlDbType.Char; param.Size = 10; param .Direction = ParameterDirection.Output; cmd.Parameters.Add(param); // Execută procedura stocată cmd.ExecuteNonQuery(); // Returnează parametrul de ieșire carPetName = ((șir)cmd.Parameters["@petName"]. Valoare) .Trim(); ) return carPetName; )

Un aspect important despre apelarea procedurilor stocate: amintiți-vă că un obiect de comandă poate reprezenta Declarație SQL(implicit) sau numele procedurii stocate. Dacă doriți să spuneți obiectului comandă că ar trebui să apeleze o procedură stocată, trebuie să treceți numele acelei proceduri (prin un argument de constructor sau folosind proprietatea CommandText) și să setați proprietatea CommandType la CommandType.StoredProcedure (în caz contrar, veți obține o excepție de rulare, deoarece în mod implicit, obiectul de comandă așteaptă o instrucțiune SQL).

Apoi, observați că proprietatea Direcție a obiectului parametru vă permite să specificați direcția de acțiune pentru fiecare parametru transmis procedurii stocate (de exemplu, un parametru de intrare, un parametru de ieșire, un parametru de intrare/ieșire sau o valoare returnată) . Ca și înainte, toate obiectele opțiuni sunt adăugate la colecția de opțiuni pentru obiectul de comandă dat.

După ce procedura stocată s-a încheiat cu un apel la ExecuteNonQuery(), valoarea unui parametru de ieșire poate fi preluată prin răsfoirea colecției de parametri pentru obiectul de comandă dat și folosind cast-ul corespunzător.

fundal

Pentru a crea aplicații de afaceri bazate pe SQL Server 2000, este necesar să se programeze logica de afaceri folosind limbajul intern pentru lucrul cu Interogări T-SQL. Dar în virtutea ei funcționalitate limitată de multe ori crearea unei astfel de logici necesită mult timp și efort de depanare. Odată cu apariția SQL Server 2005, situația se schimbă radical: această versiune declară capacitatea de a utiliza limbaje de nivel superior, și anume limbaje CLR (Visual Basic.NET și C#) la programarea procedurilor stocate și a diferitelor funcții definite de utilizator.

Căutând pe internet materiale despre integrarea SQL Server 2005 și CLR în limba rusă, am găsit doar un articol care oferă exemple despre utilizarea CLR în SQL Server 2005 ( http://www.osp.ru/win2000/2005/08/034.htm). După ce a citit-o, a devenit clar că autorul a folosit o versiune destul de specifică a SQL Server 2005 și, prin urmare, nu a fost posibilă lansarea corectă a unora dintre măsurători.

După ce am studiat câteva dintre exemplele furnizate cu MS SQL Server 2005 și am citit MSDN și SQL Server Books Online, am fost convins că unele dintre exemple nu au funcționat.

După aceea, am decis să încerc să înțeleg și eu această problemă, iar acest articol este rezultatul muncii mele.

Pregătirea pentru muncă

După instalare Microsoft SQL Server2005, trebuie să faci ceva setari aditionale pentru a putea lucra cu CLR.

Pentru a face acest lucru, deschideți SQL Server Management Studio și rulați următorul script:

sp_configure „afișează opțiuni avansate”, 1; GO RECONFIGURĂ; GO sp_configure „clr activat”, 1; GO RECONFIGURĂ; MERGE

Rezultatul scenariului:

Opțiunea de configurare „afișare opțiuni avansate” a fost schimbată de la 1 la 1. Rulați instrucțiunea RECONFIGURE pentru a instala. Opțiunea de configurare „clr enabled” a fost schimbată de la 1 la 1. Rulați instrucțiunea RECONFIGURE pentru a instala.

Această acțiune necesar pentru a permite SQL Server 2005 să ruleze versiuni personalizate.

Creați o bază de date de testare

Pentru a putea testa a creat ansambluri, trebuie mai întâi să creați o bază de date de testare (să se numească Test2005).

Această bază de date va avea un singur tabel numit Users, care conține 4 coloane:

Script pentru a crea un tabel:

CREATE TABLE .( IDENTITY(1,1) NOT NULL, (50) COLLATE Cyrillic_General_CI_AS NOT NULL, NOT NULL, NULL, CONSTRAINT CHEIE PRIMARĂ CLUSTERED (ASC)CU (IGNORE_DUP_KEY = OFF) ON ) ON

Lăsați tabelul creat să conțină următoarele date:

După parcurgerea acestor pași, puteți începe să creați ansamblul.

Crearea unui proiect pentru a lucra cu o bază de date

Pentru a începe crearea unui ansamblu, trebuie să creați un proiect, pentru asta vom executa următoarele acțiuni:

Să începem Microsoft Visual Studio 2005

Să creăm proiect nou cu titlul Testarea CLR selectând stub-ul Proiectului SQL Server în secțiunea Bază de date limba dorită(Fig. 1).

Poza 1

În fereastra care apare, ni se solicită să creăm o conexiune la baza de date (Fig. 2)

Figura 2

Apăsați butonul Add New Reference și în fereastra care apare, selectați SQL Server utilizat pentru testare, cont baza de date creată și faceți clic pe OK.

Proiectul creat are următoarea structură (Fig. 3):

Figura 3

Inițial, proiectul conține fișierul Test.sql, fisierul dat folosit pentru a testa ansamblul generat.

După crearea proiectului, puteți trece la crearea primei proceduri stocate folosind CLR.

Crearea unei proceduri stocate în CLR

Pentru a crea o procedură stocată, faceți clic Click dreapta faceți clic pe proiecte și selectați sub-elementul New Item, element Add. După aceea, pe ecran va apărea un dialog pentru crearea fișierului (Fig. 4)

Figura 4

Să alegem asta Vitrina Procedură, introduceți un nume Creați HTMLLogși faceți clic pe OK.

Procedura stocată generată va crea un document HTML cu date despre utilizatorii a căror dată de deconectare este mai mare sau egală cu data transmisă acestuia ca parametru.

Sursă clasă:

Utilizarea sistemului; folosind System.Data; folosind System.Data.SqlClient; folosind System.Data.SqlTypes; folosind Microsoft.SqlServer.Server; clasă parțială publică StoredProcedures ( //O etichetă specială care marchează procedurile stocate public static void CreateLog(SqlDateTime Value) //Procedure crearea HTML fișier ( DataTable tbl = new DataTable(); //Rezultatele interogării vor fi stocate aici SqlPipe Pipe = SqlContext.Pipe; //Obțineți obiectul SQLPipe din SQLContext //Obiectul Pipe ne va permite să lucrăm cu rezultatele returnat de procedura stocată SqlDataRecord record = new SqlDataRecord (new SqlMetaData("HTML", SqlDbType.Text));//Creare a Record //Pentru a trimite date, trebuie să specificați metadate, care este numele coloanei și tipul acesteia System. Text.StringBuilder st = new System.Text.StringBuilder() ;//Creați un obiect StringBuilder folosind (SqlConnection connection = new SqlConnection("context connection=true")) //Creați o conexiune la baza de date //context connection=true indică faptul că va fi folosită conexiunea curentă ( connection.Open(); / /Open connection SqlCommand command = new SqlCommand ("SELECT ID,Name,DateBegin,DateEnd FROM Users WHERE isnull(DateEnd,"20500101") >= @Date", connection );// Executați comanda de interogare a bazei de date.Parameters.AddWithValue ("@D ate", Value);//Definește parametrul @Date tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection)); //Încărcați rezultatul în DataTable ) //Creați document HTML st.append(""); st.append("

Importuri System Imports System.Data Import System.Data.SqlClient Import System.Data.SqlTypes Import Microsoft.SqlServer.Server Partial Public Class StoredProcedures _ Public Shared Sub CreateLog(ByVal Value As SqlDateTime) Dim tbl As New DataTable() „Rezultatele interogării vor fi stocate aici. Dim Pipe As SqlPipe = SqlContext.Pipe „Obțineți obiectul SQLPipe din SQLContext „Obiectul Pipe ne va permite pentru a lucra cu rezultatele returnate de procedura stocată Dim record As New SqlDataRecord(New SqlMetaData("HTML", SqlDbType.Text)) "Crearea unei înregistrări "Pentru a trimite date, trebuie să specificați metadate, care este Numele coloanei și tipul acesteia Dim st As New System.Text.StringBuilder() " Creați un obiect StringBuilder Folosind conexiunea ca nou SqlConnection("context connection=true") "Creați o conexiune la bază de date "context connection=true indică faptul că conexiunea curentă va fi utilizată connection.Open () „Deschide o conexiune Dim command As New SqlCommand(”SELECT ID,Name,DateBegin,DateEnd FROM Users WHERE isnull(DateEnd,”20500101”) >= @Date”, conexiune) „Perform database interogare comanda.Parameters.AddWithValue( „@Data”, Valoare) „Op definiți parametrul @Date tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection)) „Încărcați rezultatul în DataTable End folosind „Creați document HTML st.Append(”") st.Append("

Pentru a testa procedura stocată creată, să adăugăm o linie la fișierul Test.sql, care apelează procedura creată de noi:

Să începem proiectul pentru execuție, iar în fereastra Output puteți vedea rezultatele procedurii stocate create.

Acum să executăm aceeași linie în SQL Server Management Studio pe care am adăugat-o la Test.sql, iar rezultatul execuției va fi cam așa (Fig. 5):

Figura 5

Figura 6

Codul sursă al procedurii stocate

SQLContext este un obiect abstract care este un context de apelare care permite accesul la obiecte precum SQL Pipe, SQLTriggerContextși Identitatea Windows.

SQL Pipe - un obiect care este un fel de tunel care vă permite să transferați informatie necesara context de apelare.

SQLTriggerContext – un obiect care conține informații despre ce a provocat declanșarea declanșatorului.

Identitatea Windows - un obiect reprezentând utilizatorul în sistem Windows

Crearea unei funcții personalizate care returnează o valoare scalară

Adaugă la proiect fișier nou, pentru a face acest lucru, în dialogul de creare a fișierului (Fig. 4), selectați Funcție definită de utilizator și denumiți funcția CantitateCuvânt.

Această funcție va număra numărul anumitor caractere din text. Textul sursă al funcției:

Utilizarea sistemului; folosind System.Data; folosind System.Data.SqlClient; folosind System.Data.SqlTypes; folosind Microsoft.SqlServer.Server; clasă parțială publică UserDefinedFunctions ( //Eticheta pentru marcarea că aceasta este o funcție //2 parametri sunt trecuți acestei funcție: primul - unde căutăm, al doilea - ce căutăm //Rezultatul funcției va fi: Numărul de apariții ale cuvântului în text, sau NULL dacă text sau cuvânt - NULL public static SqlInt32 QuantityWord(SqlString text, SqlString word) ( bool flag = false; //Define loop exit flag int i = -1; / /Define căutare offset int count = 0; //Număr de apariții dacă (!text .IsNull && !word.IsNull) //Dacă ceva este NULL, atunci returnează NULL ( șir t = text.Value; //Obțineți șirul din parametrul text șir w = cuvânt.Valoare; //Obțineți șirul de la parametru cuvânt while (flag == false) //Începe ciclul de numărare a numărului de apariții ( i = t.IndexOf(w, i + 1); // Căutați o apariție dacă (i >= 0) count++; else flag = adevărat; ) returnează nou SqlInt32(număr); //Return numărul de apariții ) else ( return new SqlInt32(); //Return NULL ) ) )

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class UserDefinedFunctions „2 parametri sunt trecuți acestei funcție: primul - unde căutăm, al doilea - ce căutăm" Rezultatul funcției va fi: Numărul de apariții ale cuvântului în text, sau NULL dacă textul sau cuvântul este NULL _ Public Shared Function QuantityWord(ByVal text As SqlString, ByVal word As SqlString) As SqlInt32 Dim flag As Boolean = False "Define loop exit flag Dim i As Integer = -1 "Define search offset Dim count As Integer = 0 "Cantitate apariții Dacă (Nu text.IsNull și nu cuvânt.IsNull) Apoi „Dacă ceva este NULL, returnează NULL Dim t As String = text.Value „(!LANG:Get string from parameter text Dim w As String = word.Value"Получаем строку из параметра word While (flag = False) "Запускаем цикл подсчета кол-ва вхождений i = t.IndexOf(w, i + 1) "Ищем вхождение If (i >= 0) Then count += 1 Else flag = True End If End While Return New SqlInt32(count) "Возвращаем кол-во вхождений Else Return New SqlInt32() "Возвращаем NULL End If End Function End Class !}

Pentru a verifica, trebuie să modificați fișierul Test.sql, acum ar trebui să conțină următoarea linie:

SELECT Name, dbo.QuantityWord(Name,"po") AS Count FROM Users --dbo.QuantityWord("Unde să cauți", "Ce să cauți")

Dacă setați un punct de întrerupere oriunde în codul sursă al funcției și rulați proiectul în modul „Start Debugging”, atunci când executați cererea, puteți executa pas cu pas funcția creată de noi.

Rezultatul muncii cererea dată, dacă rulează în SQL Server Management Studio, va avea următoarea vedere(Fig. 7):

Figura 7

Creați o funcție personalizată care returnează un tabel

Separat, am decis să iau în considerare funcțiile definite de utilizator care returnează un tabel ca rezultat al muncii lor, deoarece nu funcționează în exemplele furnizate cu SQL Server 2005.

Să adăugăm un fișier nou la proiect, pentru aceasta, în dialogul de creare a fișierului (Fig. 4), selectați Funcție definită de utilizator și denumiți-o ÎntoarcereScrisoare.

Această funcție va returna un tabel în care trecută în funcție cuvânt.

Cod sursă funcție:

Utilizarea sistemului; folosind System.Data; folosind System.Data.SqlClient; folosind System.Data.SqlTypes; folosind Microsoft.SqlServer.Server; clasă parțială publică UserDefinedFunctions ( //O etichetă care definește ce funcţie dată returnează un tabel public static System.Collections.IEnumerable ReturnLetter(SqlString text) //Funcției i se transmite 1 parametru - șir (valoare șir; //Variabilă pentru a stoca șirul dacă (text.IsNull) //Dacă este transmis NULL la funcția, apoi returnează un șir gol value = ""; else value = text.Value; return value.ToCharArray(); //Return o matrice de octeți ) //Funcție pentru a umple tabelul public static void FillRow(Object obj, out string stringElement) ( stringElement = obj.ToString() ;//Returnează un rând în tabel ) )

Importă System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Clasa publică parțială UserDefinedFunctions „O etichetă care specifică că această funcție returnează un tabel _ Public Shared Function ReturnLetter(ByVal text As SqlString) As System.Collections.IEnumerable "Funcția este transmisă 1 parametru - un șir Valoare Dim As String "O variabilă pentru a stoca șirul If text.IsNull Then "Dacă NULL este transmis la funcția, apoi returnează un șir gol value = "" Else value = text.Value End If Return value.ToCharArray() "Return o matrice de octeți End Function "Filll table function Public Shared Sub FillRow(ByVal obj As Object, ByRef stringElement As String) stringElement = obj.ToString() "Returnează un șir în tabelul End Sub End Class

Pentru a testa funcția creată, trebuie să modificați fișierul Test.sql, acesta ar trebui să conțină următoarea linie:

După ce proiectul este lansat pentru execuție, fereastra de ieșire va afișa rezultatele funcției.

Rezultatul interogării, dacă este rulat în SQL Server Management Studio, va arăta astfel (Fig. 8):

Figura 8

Codul sursă al funcției Explicații

Vreau să fiu atent la eticheta care marchează funcțiile:

FillRowMethodName – Acest parametru specifică numele funcției pentru completarea tabelului.

Tabel Definiție – Un parametru responsabil pentru metadatele returnate utilizatorului (în mod ciudat, acest parametru nu este prezent în exemplele C#, dar am putut găsi cum să-l folosesc în SQL Server Books Online (BOL).

Valoarea parametrului Tabel Definiție egal " testatint,testnamenvarchar(4000)" arată că această funcție returnează un tabel format din 2 coloane. Prima coloană numită testatși tip intși al doilea - testname cu tip nvarchar(4000).

Aș dori să atrag atenția asupra definiției funcției FillRowîn limbajul VB.NET

Sub FillRow public partajat(ByVal obj ca obiect, ByRef șirElement ca șir)

În exemplele livrate cu SQL Server 2005, parametrii de ieșire nu sunt marcați ca Out(), deși BOL recomandă insistent acest lucru (am încercat să ruleze atât cu cât și fără ea, nu am observat nicio modificare). Dar cred ca e mai bine sa urmaresti ce scrie in BOL.

Dacă funcția dvs. returnează un tabel cu mai multe coloane, atunci parametrii Out sunt separați prin virgulă:

Importă System.Runtime.InteropServices Public Shared Sub FillRow(ByVal obj ca obiect, ByRef TestID ca șir, ByRef TestName ca șir)

Crearea unei funcții de agregare personalizată

Funcțiile agregate sau funcțiile agregate sunt funcții care operează pe un subset de valori și returnează valoare scalară. Cele mai clare exemple de astfel de funcții sunt funcțiile de însumare sumăși numărul de linii Numara. În acest exemplu, vom construi o funcție care va concatena șiruri și, ca rezultat, va returna un șir în care sunt listate toate numele de utilizator primite pentru luna, separate prin virgule.

Cod sursă funcție:

Utilizarea sistemului; folosind System.Data; folosind System.Data.SqlClient; folosind System.Data.SqlTypes; folosind Microsoft.SqlServer.Server; // Această clasă va fi serializat //Formatul de serializare va fi definit de utilizator //Numele funcției de agregare va fi SumText // Dimensiunea maxima valoare returnată 8000 de octeți //Această clasă acceptă interfața IBinarySerialize public struct MonthSum: IBinarySerialize ( șir privat Names; //variabilă locală unde se acumulează valorile public void Init() //Această metodă este apelată când începe procesul de agregare ( Names) = "" ; //Resetați valorile acumulate ​​) public void Accumulate(SqlString Value) //Această metodă este apelată pentru fiecare valoare din grup ( Names += Value.Value + ","; //Acumulați numele separându-le cu o virgulă ) public void Merge(MonthSum Group) //Această metodă este folosită pentru a îmbina calcule parțiale complexe, este folosită pentru a grupa grupări ( Names += Group.Names; //Obține numele din valoarea transmisă ) public SqlString Terminate( ) // Returnează rezultatul grupării ( dacă (Names.Length > 0) //Dacă nu a fost grupat nimic, atunci returnează NULL return new SqlString(Names.Substring(0, Names.Length - 1)); //Elimină ultima virgulă altfel returnează SQL nou şir(); //Return NULL ) #region IBinarySerialize metode de interfață public void Read(System.IO.BinaryReader r) ( Names = r.ReadString(); //Read the Names values) public void Write(System.IO.BinaryWriter w) ( w . Scrie(Nume); //Scrie valorile Nume ) #endregion )

Importuri System Imports System.Data Import System.Data.SqlClient Import System.Data.SqlTypes Imports Microsoft.SqlServer.Server „Această clasă va fi serializată „Formatul de serializare va fi UserDefined „Numele funcției de agregare va fi SumText „Maximul dimensiunea valorii returnate este de 8000 de octeți" Această clasă acceptă interfața IBinarySerialize _ _ Public Structure MonthSum implementează IBinarySerialize Private Names As String „Variabila locală în care se acumulează valorile Public Sub Init() „Această metodă este apelată când începe procesul de agregare Names = „” „Resetează valorile acumulate la zero End Sub Public Sub Accumulate(ByVal value As SqlString ) "Această metodă este apelată pentru fiecare valoare din grupul Names += value.Value + "," "Acumulați numele separate prin virgule End Sub Public Sub Merge(ByVal Group As MonthSum) "Această metodă este utilizată pentru a fuziona calcule parțiale complexe, este folosit pentru gruparea grupărilor Nume += Grup.Nume "Obține numele din grupul trecut End Sub Public Function Terminate() As SqlString "Returează rezultatul grupării Dacă (Nume.Lungime > 0) Apoi „Dacă nu a fost grupat nimic, atunci returnează NULL Return New SqlString( Names.Substring(0, Names.Length - 1)) „Elimină ultima virgulă Else Return New SqlString() „Return NULL End If End Function #Region” Metode de interfață IBinarySerialize" Public Sub Read(ByVal r As System.IO.BinaryReader) Implementează Microsoft.SqlServer.Server.IBinarySerialize.Read Names = r.ReadString() "Read Values ​​​​Nume End Sub Public Sub Write (ByVal w As System) .IO .BinaryWriter) Implementează Microsoft.SqlServer.Server.IBinarySerialize.Write w.Write(Names) „Scrie valori Nume End Sub #End Region End Structure

Pentru a testa funcția de agregare creată, trebuie să faceți modificări în fișierul Test.sql, acest fișier ar trebui să conțină următorul text:

După pornirea proiectului pentru execuție, rezultatele lucrării pot fi văzute în fereastra Ieșire.

Dacă interogarea din fișierul Test.sql este executată în SQL Server Management Studio, atunci rezultatul va fi cam așa (Fig. 9):

Figura 9

Explicații despre codul sursă al funcțiilor agregate

Vă rugăm să rețineți că pentru a crea o funcție de agregare, nu este folosită o clasă, ci o structură marcată cu o etichetă de serializare și o etichetă specială SqlUserDefinedAggregate.

Atribute suplimentare (opționale). SqlUserDefinedAggregate:

IsInvariantToDuplicates – Setați la true dacă funcția de agregare nu este sensibilă la repetări (de exemplu, MAXși MIN insensibil şi SUMĂ sensibil).

IsInvariantToNulls – Setați la adevărat dacă funcția este invariabilă NUL(de exemplu, MINși SUMĂ utilizare atribut dat, A NUMARA nu foloseste)

IsInvariantToOrder – Atributul este rezervat pentru utilizare ulterioară.

IsNullIfEmpty – Atributul este folosit pentru a marca faptul că agregatul poate reveni NUL dacă nu s-a acumulat nicio valoare.

Crearea declanșatoarelor

Ultima problemă pe care aș dori să o iau în considerare este crearea declanșatorilor folosind CLR.

Să creăm un declanșator care verifică data introdusă într-o coloană Data Început. Dacă data este mai mică decât 1 mai 2006, atunci declanșatorul anulează tranzacția.

Cod sursă de declanșare:

Utilizarea sistemului; folosind System.Data; folosind System.Data.SqlClient; folosind Microsoft.SqlServer.Server; folosind System.Data.SqlTypes; Declanșatoare de clasă parțială publică ( //O etichetă care marchează ce aceasta metoda este un declanșator //Nume - Numele declanșatorului //Target - Tabelul pentru care este creat declanșatorul //Eveniment - Pentru ce evenimente este utilizat declanșatorul SqlTriggerContext triggContext = SqlContext.TriggerContext; //Obțineți contextul declanșatorului folosind ( SqlConnection connection = new SqlConnection("context connection = true")) //Creează o conexiune la bază de date ( cititor SqlDataReader; //Definește o variabilă pentru citirea datelor SqlDateTime valoare = new SqlDateTime(); //Definește o variabilă în care vom salva rezultatul citirii int columnNumber = -1;//Column Number connection.Open(); //Deschide conexiunea Comanda SqlCommand = connection.CreateCommand(); //Creează un obiect SqlCommand pentru comanda conexiunii curente.CommandText = "SELECT * din " + "inserat"; //Scrie o interogare în SqlCommand reader = command.ExecuteReader(); //Execută o interogare pentru a prelua valori din tabelul din sertedreader.Read(); //Citiți datele pentru (int i = 0; i< triggContext.ColumnCount; i++) //Как пример цикла по всем столбцам { if (reader.GetName(i) == "DateBegin" && triggContext.IsUpdatedColumn(i) == true) //Ищем колонку с название DateBegin и проверяем изменилась она или нет { columnNumber = i; //Если она изменилась, сохраняем ее номер break; //Останавливаем цикл } } if (columnNumber != -1) //Если столбец DateBegin изменялся проверяем значение которое в него хотят вставить { value = reader.GetSqlDateTime(columnNumber); //Получаем вставляемое значение } reader.Close(); //Прекращаем чтение if (!value.IsNull) //Если прочитанное значение null но ничего не делаем { if (value.Value <= new DateTime(2006, 5, 1)) //Если дата меньше 1 мая 2006 года, тогда не разрешаем вводить ее { throw new Exception("Дата должная быть больше 1 мая 2006 года"); //Создаем исключение для отмены транзакции } } } } }

Importă System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class Triggers „O etichetă care marchează această metodă ca declanșator „Nume - Numele declanșatorului „Target - The tabel pentru care declanșatorul este creat „Eveniment - Pentru ce evenimente este utilizat declanșatorul _ Public Shared Sub DateTrigger() Dim pipe As SqlPipe = SqlContext.Pipe „Obținerea unui „pipe” pentru ieșirea rezultatelor Dim triggContext As SqlTriggerContext = SqlContext.TriggerContext „Obținerea contextului declanșatorului Folosind conexiunea As New SqlConnection(”context connection = true”) „Creează o conexiune la baza de date Dim reader As SqlDataReader „Definirea unei variabile pentru citirea datelor Dim value As New SqlDateTime() „Definirea unei variabile în care vom salva rezultatul citirii Dim columnNumber As Integer = -1 „Column Number connection.Open () "Deschiderea conexiunii Dim comanda As SqlCommand = connection.CreateCommand() "Creați un obiect SqlCommand pentru comanda conexiunii curente.CommandText = "SELECT * din " + "inserted" "Scrie o interogare la SqlCommand reader = command.ExecuteReader( ) „Execută o interogare pentru a prelua valori din cititorul de tabel inserat.Read() „Citirea datelor pentru i Ca Integer = 0 Pentru a triggContext.ColumnCount - 1 „Ca exemplu de buclă peste toate coloanele If (reader.GetName(i ) = „DateBegin” Și triggContext.IsUpdatedColumn(i) = True) Apoi „Căutați o coloană numită DateBegin și verificați dacă s-a schimbat sau nu columnNumber = i „Dacă s-a schimbat, salvați numărul Ieșire pentru „Opriți bucla Încheierea dacă urmează If (Not columnNumber = -1) Apoi „Dacă coloana DateBegin s-a schimbat, verificați valoarea pe care doresc să o introducă în ea value = reader.GetSqlDateTime(columnNumber) „Obțineți valoarea inserată End If reader.Close() „Opriți citirea dacă (Nu value.IsNull) Apoi „Dacă valoarea citită este nulă, dar nu faceți nimic Dacă (value.Value)<= New DateTime(2006, 5, 1)) Then "Если дата меньше 1 мая 2006 года, тогда не разрешаем вводить ее Throw New Exception("Дата должная быть больше 1 мая 2006 года") "Создаем исключение для отмены транзакции End If End If End Using End Sub End Class

Pentru a verifica funcționarea declanșatorului, vom adăuga următorul text în fișierul Test.sql:

După ce proiectul este lansat pentru execuție, rezultatele interogării din fișierul Test.sql pot fi văzute în fereastra de ieșire.

Dacă această interogare de actualizare este rulată în SQL Server Management Studio, atunci rezultatul muncii sale va fi următorul (Fig. 10):

Figura 10

Explicații despre codul sursă declanșator

În acest exemplu, bucla prin coloane este făcută pentru cea mai bună înțelegere a lucrării.CLR declanșează.

Folosirea declanșatorilor mi-a dat multe probleme. Nu există informații în BOL și MSDN despre cum să anulați o tranzacție, cum să preveniți inserarea datelor incorecte într-un tabel. Exemplul din articolul lui Vinod Kumar folosește metode care nu sunt acceptate de versiunea curentă a SQL Server 2005.

După ce am săpat în BOL, am constatat că nu există nicio modalitate de a anula o tranzacție în care sunt executate funcții CLR. Funcțiile CLR pot anula doar tranzacțiile care sunt definite în codul lor.

După ce am experimentat cu diferite opțiuni pentru anularea tranzacțiilor, putem concluziona că există o singură modalitate de a face acest lucru - aruncați o excepție în funcția CLR. De asemenea, puteți transmite ca rezultat utilizatorului RAISEROARE, dar cel mai important lucru este să nu includeți execuția acestei interogări în Încerca...Captură.

Command.CommandText = "RAISERROR("Data trebuie să fie mai mare decât 1 mai 2006",16,1)"; pipe.ExecuteAndSend(comandă); //throw new Exception("Data trebuie să fie mai mare decât 1 mai 2006"); //Creează o excepție pentru a anula tranzacția

Command.CommandText = "RAISERROR("Data trebuie să fie mai mare decât 1 mai 2006",16,1)" pipe.ExecuteAndSend(command) "Throw New Exception("Data trebuie să fie mai mare decât 1 mai 2006") "Creați o anularea tranzacțiilor excepționale

Dacă urmați această schemă, atunci rezultatul inserării datelor incorecte în tabel va arăta astfel (Fig. 11):

Figura 11

Acum aș dori să clarific întrebarea referitoare la clasă SQLTriggerContext.

Această clasă conține următoarele proprietăți și metode:

Număr de coloane – numărul de coloane cuprinse în tabel pentru care este apelat declanșatorul

Datele evenimentului – Date XML despre ce a cauzat declanșarea declanșatorului

TriggerAction – O proprietate care indică acțiunea care a determinat declanșarea declanșatorului

IsUpdatedColumn – O metodă pentru a verifica dacă datele dintr-o anumită coloană s-au modificat.

Principalele metode și proprietăți ale clasei SQL Pipe:

IsSendingResults – O proprietate care indică faptul că SQLPipe este în modul de trimitere a unui singur set de rezultate către client

ExecuteAndSend – Execută o comandă și trimite imediat rezultatul către client

trimite – O metodă supraîncărcată care vă permite să trimiteți o singură linie de rezultat, mai multe linii de rezultat sau un mesaj către client.

SendResultsStart – Folosit pentru a trimite metadate clientului despre ce date va primi.

SendResultsRow – Trimiterea unei linii de date către client

SendResultsEnd – Marchează sfârșitul trimiterii datelor și readuce SQLPipe la starea inițială.

Atașarea manuală a unui ansamblu la SQL Server 2005

Această secțiune va analiza metodele pentru conectarea manuală a unui ansamblu la SQL Server 2005, precum și prezentarea codului sursă pentru procedurile stocate, funcțiile și declanșatoarele care sunt necesare pentru a funcționa cu CLR.

Conectarea ansamblului la baza de date:

După ce porniți SQL Server Management Studio, mergeți la secțiunea Assemblys din baza de date creată Test2005 (Fig. 12)

Figura 12

În meniul de clic dreapta, selectați elementul New Assembly, iar în fereastra care apare, apăsați butonul Browse (Fig. 13)

Figura 13

După aceea, selectați fișierul TestingCLR.dll aflat în folderul bin\Debug al proiectului creat și apăsați butonul OK.

Acum să creăm o procedură stocată care va apela procedura CLR pe care am creat-o. Pentru a face acest lucru, în SQL Server Management Studio de pe bara de instrumente, faceți clic pe butonul New Query și introduceți următorul text în fereastra care se deschide:

CREATE PROCEDURE @Value datetime --Parametru care este trecut procedurii CA NUME EXTERN .. --Referire la ansamblu și la funcția care va fi executată atunci când această procedură stocată este apelată

Și îl lansăm spre execuție.

Ca urmare, va apărea o procedură în secțiunea Programabilitate / Proceduri de stocare CreateLog.

Rezultatele conexiunii pot fi verificate rulând interogarea:

Acum să creăm un wrapper pentru funcție CantitateCuvânt, pentru aceasta, în fereastra de execuție a interogării, introduceți textul:

CREATE FUNCTION (@text nvarchar(4000), --Function parameter (Ce text să căutați) @word nvarchar(4000) --Function parameter (ce text să căutați)) RETURNE CA NUME EXTERN ..

După executarea acestei comenzi, va apărea o funcție în secțiunea Programabilitate / Funcție scalară CantitateCuvânt, pentru a o verifica, executați următoarea interogare:

SELECT Name, dbo.QuantityWord(Name,"ro") AS Count FROM Users --dbo.QuantityWord("Ce căutăm", "Ce căutăm")

Creați un wrapper pentru o funcție ÎntoarcereScrisoare, în fereastra de execuție a interogării introducem textul:

CREATE FUNCTION (@text nvarchar(4000)--Function input parameter) RETURNS TABLE(Chars nchar(1)) --Structura tabelului returnat CA NUME EXTERN ..

Rulați această comandă și verificați dacă funcționează corect:

SELECT * FROM dbo.ReturnLetter(„Utilizarea Microsoft SQL Server 2005”)

Funcția de agregare este conectată în același mod:

CREATE AGGREGATE (@value nvarchar(4000) -- Valoare de transmis la agregat) RETURNS nvarchar(4000) -- Returnează valoarea EXTERNAL NAME .

Examinare:

SELECT dbo.SumText_vb(Nume) AS SumText, DateBegin FROM Users GROUP BY DateBegin

Declanșează conexiunea:

CREATE TRIGGER ON --Trigger on Users table FOR UPDATE, INSERT --UPDATE și INSERT AS EXTERNAL NAME ..

Examinare:

UPDATE Users SET DateBegin = "20050101" --20050101 - Data 1 ianuarie 2005 în SQL Server Universal Format WHERE ID = 1

După parcurgerea tuturor acestor pași, structura bazei de date ar trebui să arate cam așa (Fig. 14):

Figura 14

Concluzie

În acest articol, am încercat să acopăr elementele de bază ale utilizării Common Language Runtime în Microsoft SQL Server 2005. Acest articol nu acoperă problema creării unui tip definit de utilizator folosind CLR. Această problemă va fi tratată într-un alt articol despre integrarea SQL Server 2005 cu .NET Framework.

Pentru ce sunt toate acestea?

Ce oferă integrarea .NET și SQL Server 2005 Acum, pentru a crea proceduri stocate, funcții definite de utilizator și declanșatoare care implementează logica de afaceri, puteți utiliza astfel de limbaje de programare CLR precum Visual Basic .NET și C#. De asemenea, a devenit posibil să vă creați propriile funcții agregate (de exemplu, scrieți un înlocuitor pentru funcția SUM sau MAX). Acum puteți utiliza cea mai mare parte a funcționalității .NET, cum ar fi expresiile regulate, lucrul cu servicii web, calcule matematice complexe. Mulți vor spune că o astfel de funcționalitate ar putea fi implementată și în T-SQL, dar încercați să implementați funcția de agregare a datelor text creată în acest articol, încercați să scrieți o verificare pentru E-mail-ul introdus de utilizator în T-SQL. Și veți înțelege că utilizarea simplă a expresiei regulate „\ [email protected]+\.(2,4)\b", nu numai că va crește productivitatea muncii, dar va afecta semnificativ și capabilitățile logicii de afaceri implementate. De asemenea, acum, nu numai oamenii care cunosc cu încredere T-SQL, ci și programatorii VB.NET și C# pot fi implicați în procesul de dezvoltare a logicii de afaceri primare. Pentru a afla mai multe despre cum funcționează SQL Server 2005 și .NET, consultați articolul lui Vinod Kumar „Obținerea maximă a CLR” ( http://www.osp.ru/win2000/2005/08/034.htm).

Surse folosite

1. Kumar V. Cum să profitați la maximum de CLR - WINDOWS IT Pro Magazine #08, 2005 ( http://www.osp.ru/win2000/2005/08/034.htm)
2. Cărți online Microsoft SQL Server 2005
3. Bibliotecă MSDN pentru Visual Studio 2005

Mulțumiri speciale lui Tumanov Anatoly Alexandrovich de la autorul Egorov Nikita (C…R…a…S…H)

Ultima actualizare: 31.10.2015

Procedurile stocate sunt o altă formă de interogare a unei baze de date. Dar în comparație cu interogările discutate anterior care sunt trimise din aplicație în baza de date, procedurile stocate sunt definite pe server și oferă performanțe mai bune și sunt mai sigure.

Obiectul SqlCommand are suport încorporat pentru procedurile stocate. În special, are o proprietate CommandType definită care ia ca valoare o valoare din enumerarea System.Data.CommandType. Și valoarea System.Data.CommandType.StoredProcedure indică doar că va fi utilizată o procedură stocată.

Dar pentru a folosi procedurile stocate, trebuie mai întâi să le creăm. Pentru a face acest lucru, accesați SQL Server Management Studio în baza noastră de date usersdb, extindeți-i nodul și apoi selectați Programabilitate-> Proceduri stocate. Faceți clic dreapta pe acest nod și selectați Procedura stocată... din meniul contextual:

În partea centrală a programului se deschide codul de procedură, care este generat implicit. Să înlocuim acest cod cu următorul:

CREATE PROCEDURA . @name nvarchar(50), @age int AS INSERT INTO Utilizatori (Nume, Vârstă) VALUES (@name, @age) SELECT SCOPE_IDENTITY() GO

Această procedură realizează adăugarea de date. Instrucțiunea CREATE PROCEDURE este urmată de numele procedurii. Procedura se numește „sp_InsertUser”, iar cu acest nume o vom numi în codul C#. Numele procedurii poate fi orice.

Procedura are doi parametri de intrare: @name și @age. Valorile pentru numele utilizatorului și vârsta vor fi trecute prin acești parametri. În corpul procedurii, după expresia AS, există o expresie SQL standard INSERT, care realizează adăugarea datelor. Și în final, folosind instrucțiunea SELECT, rezultatul este returnat. Expresia SCOPE_IDENTITY() returnează id-ul înregistrării adăugate, așa că la ieșirea din procedură vom obține id-ul noii înregistrări. Și procedura se termină cu cuvântul cheie GO.

Și apoi faceți clic pe butonul Execute. Procedura stocată este apoi adăugată la baza de date.

În mod similar, să adăugăm o altă procedură care va returna obiecte:

CREATE PROCEDURA . AS SELECT * FROM Users GO

Și, de asemenea, pentru a-l adăuga, faceți clic pe butonul Execute.

Acum să trecem la codul C# și să definim următorul program:

Program de clasă ( static șir connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True"; static void Main(string args) ( Console.Write("Introduceți numele utilizatorului:"); șir nume = Console.ReadLine(); Console.Write("Introduceți vârsta utilizatorului:"); int age = Int32.Parse(Console.ReadLine()); AddUser(nume, vârstă); Console.WriteLine(); GetUsers(); Console .Read(); ) // adăugarea unui utilizator privat static void AddUser(nume șir, vârstă int) ( // nume de procedură șir sqlExpression = "sp_InsertUser"; folosind (SqlConnection connection = new SqlConnection(connectionString)) ( connection.Open( ) ; Comanda SqlCommand = new SqlCommand(sqlExpression, connection); // indică faptul că comanda este o procedură stocată command.CommandType = System.Data.CommandType.StoredProcedure; // parametru pentru introducerea numelui SqlParameter nameParam = nou SqlParameter ( ParameterName = „@nume”, Valoare = nume); // adaugă parametrul command.Parameter s.Add(nameParam); // parametru pentru introducerea vârstei SqlParameter ageParam = new SqlParameter ( ParameterName = "@age", Value = age ); command.Parameters.Add(ageParam); var rezultat = comanda.ExecuteScalar(); // dacă nu trebuie să returnăm id //var rezultat = command.ExecuteNonQuery(); Console.WriteLine("Id-ul obiectului adăugat: (0)", rezultat); ) ) // afișează toți utilizatorii private static void GetUsers() ( // șirul numelui procedurii sqlExpression = "sp_GetUsers"; folosind (SqlConnection connection = new SqlConnection(connectionString)) ( connection.Open(); SqlCommand command = new SqlCommand(sqlExpression) , conexiune); // indică faptul că comanda este o procedură stocată command.CommandType = System.Data.CommandType.StoredProcedure; var reader = command.ExecuteReader(); if (reader.HasRows) ( Console.WriteLine ("(0)) \ t(1)\t(2)", reader.GetName(0), reader.GetName(1), reader.GetName(2)); în timp ce (reader.Read()) ( int id = reader.GetInt32( 0); nume șir = reader.GetString(1); int age = reader.GetInt32(2); Console.WriteLine ("(0) \t(1) \t(2)", id, nume, vârstă); ) ) cititor.Închide (); ) ) )

Pentru a simplifica codul, apelurile la proceduri sunt mutate în metode separate aici. Procedura sp_InsertUser este apelată în metoda AddUser. Numele său este transmis constructorului obiectului SqlCommand în același mod ca o expresie sql obișnuită. Și folosind expresia command.CommandType = System.Data.CommandType.StoredProcedure, se stabilește că sistemul va considera această expresie ca o procedură stocată.

Deoarece procedura primește date prin parametri, trebuie să definim acești parametri folosind obiecte SqlParameter. I se transmite numele și valoarea parametrului. Numele parametrilor trebuie să se potrivească cu numele pe care le-am definit în codul de procedură.

Metoda command.Parameters.Add() adaugă parametri unei proceduri. Și apoi are loc execuția.

Deoarece în codul procedurii de adăugare am definit returnarea id-ului noii înregistrări, adică returnarea unei valori scalare, putem folosi metoda ExecuteScalar() pentru a executa comanda și a o obține. Dar putem folosi și metoda ExecuteNonOuery(), doar că va returna numărul de intrări adăugate, nu id-ul.

În cazul celei de-a doua metode, totul este și mai simplu: obiectului comandă i se trece pur și simplu numele procedurii și, deoarece procedura execută efectiv instrucțiunea SELECT și returnează un set de date, putem folosi metoda ExecuteReader() pentru a executa comanda. Și cu ajutorul cititorului pentru a obține toate datele.

Rulați programul și introduceți câteva date de adăugat.