query - stored procedure return c#




Quali sono i pro e i contro per mantenere SQL in Processi memorizzati rispetto al codice (20)

Vantaggi per il codice:

  • Più semplice da gestire: non è necessario eseguire uno script SQL per aggiornare le query
  • Più semplice da portare su un altro DB: nessun proc di porta

In realtà, penso che tu sia così arretrato. IMHO, SQL nel codice è doloroso da mantenere perché:

  • finisci per ripeterti nei relativi blocchi di codice
  • SQL non è supportato come lingua in molti IDE, quindi hai solo una serie di stringhe di verifica non verificate che eseguono attività per te
  • le modifiche apportate a un tipo di dati, a un nome di tabella o a un vincolo sono molto più prevalenti rispetto allo swapping di un intero database per uno nuovo
  • il tuo livello di difficoltà aumenta man mano che la tua richiesta diventa più complessa
  • e testare una query in linea richiede la costruzione del progetto

Pensa ai Procored Stored come metodi che invochi dall'oggetto del database: sono molto più facili da riutilizzare, c'è solo un posto da modificare e nel caso in cui tu cambi i provider di DB, le modifiche avvengono nei Proceed Stored e non nel tuo codice .

Detto questo, il guadagno di prestazioni dei proc memorizzati è minimo, come Stu ha detto prima di me e non è possibile inserire un punto di interruzione in una stored procedure (ancora).

Quali sono i vantaggi / svantaggi di mantenere SQL nel codice sorgente C # o in Processi memorizzati? Ne ho discusso con un amico su un progetto open source su cui stiamo lavorando (C # ASP.NET Forum). Al momento, la maggior parte dell'accesso al database viene eseguita creando SQL inline in C # e chiamando al DB di SQL Server. Quindi sto cercando di stabilire quale, per questo particolare progetto, sarebbe il migliore.

Finora ho:

Vantaggi per il codice:

  • Più semplice da gestire: non è necessario eseguire uno script SQL per aggiornare le query
  • Più semplice da portare su un altro DB: nessun proc di porta

Vantaggi per Procored Stored:

  • Prestazione
  • Sicurezza

@Keith

Sicurezza? Perché gli sproc sono più sicuri?

Come suggerito da Komradekatz, è possibile disabilitare l'accesso alle tabelle (per la combinazione di nome utente / password che si collega al DB) e consentire solo l'accesso SP. In questo modo, se qualcuno ottiene il nome utente e la password nel database, possono eseguire SP ma non possono accedere alle tabelle o a qualsiasi altra parte del DB.

(Naturalmente l'esecuzione di sproc può fornire loro tutti i dati di cui hanno bisogno, ma ciò dipenderebbe dagli sproc che erano disponibili. Dando loro l'accesso alle tabelle dà loro accesso a tutto.)


Bene, ovviamente, l'utilizzo di stored procedure presenta diversi vantaggi rispetto alla creazione di SQL nel codice.

  1. L'implementazione del codice e SQL diventano indipendenti l'uno dall'altro.
  2. Il codice è più facile da leggere.
  3. Scrivi una volta usa più volte.
  4. Modifica una volta
  5. Non è necessario fornire dettagli interni al programmatore sul database. ecc. ecc.

Cado dal codice . Costruiamo il livello di accesso ai dati che viene utilizzato da tutte le app (sia web che client), quindi è ASCIUTTO da quella prospettiva. Semplifica la distribuzione del database perché dobbiamo solo assicurarci che gli schemi della tabella siano corretti. Semplifica la manutenzione del codice perché non è necessario consultare il codice sorgente e il database.

Non ho molti problemi con l'accoppiamento stretto con il modello di dati, perché non vedo dove sia possibile rompere realmente quell'accoppiamento. Un'applicazione e i suoi dati sono intrinsecamente accoppiati.


Generalmente scrivo il codice OO. Sospetto che la maggior parte di voi probabilmente lo faccia anche lei. In quel contesto, mi sembra ovvio che tutta la logica aziendale - comprese le query SQL - appartiene alle definizioni di classe. Suddividere la logica in modo tale che una parte di essa risieda nel modello di oggetto e la parte sia nel database non è meglio che inserire la logica di business nell'interfaccia utente.

Molto è stato detto in precedenti risposte sui vantaggi di sicurezza dei proc memorizzati. Questi rientrano in due grandi categorie:

1) Limitazione dell'accesso diretto ai dati. Questo è sicuramente importante in alcuni casi e, quando ne incontri uno, i proc memorizzati sono praticamente la tua unica opzione. Nella mia esperienza, tuttavia, tali casi sono l'eccezione piuttosto che la regola.

2) query SQL / parametrizzate. Questa obiezione è un'aringa rossa. Inline SQL - anche inline SQL generato dinamicamente - può essere parametrizzato come qualsiasi proc memorizzato e può essere eseguito con la stessa facilità in qualsiasi linguaggio moderno che valga la pena. Non c'è nessun vantaggio neanche qui. ("Gli sviluppatori pigri potrebbero non preoccuparsi dell'uso dei parametri" non è un'obiezione valida. Se hai sviluppatori nel tuo team che preferiscono semplicemente concatenare i dati utente nel loro SQL invece di usare i parametri, prova prima ad educarli, poi li licenzi se questo non funziona, proprio come faresti con gli sviluppatori che hanno altre cattive abitudini, palesemente dannose).


Il vantaggio prestazionale per le stored procedure è spesso trascurabile.

Più vantaggi per le stored procedure:

  • Prevenire il reverse engineering (se creato con Encryption, ovviamente)
  • Migliore centralizzazione dell'accesso al database
  • Possibilità di modificare il modello di dati in modo trasparente (senza dover distribuire nuovi client); particolarmente utile se più programmi accedono allo stesso modello di dati

Le stored procedure sono più mantenibili perché:

  • Non è necessario ricompilare l'app C # ogni volta che si desidera modificare qualche SQL
  • Si finisce per riutilizzare il codice SQL.

La ripetizione del codice è la cosa peggiore che puoi fare quando stai cercando di creare un'applicazione gestibile!

Cosa succede quando trovi un errore logico che deve essere corretto in più punti? Sei più incline a dimenticare di cambiare l'ultimo punto in cui copi e incolli il codice.

A mio parere, i guadagni in termini di prestazioni e sicurezza sono un ulteriore vantaggio. È comunque possibile scrivere procedure SQL memorizzate non sicure / inefficienti.

Più semplice da portare su un altro DB: nessun proc di porta

Non è molto difficile codificare tutte le stored procedure per la creazione in un altro DB. In effetti, è più facile che esportare le tue tabelle perché non ci sono chiavi primarie / straniere di cui preoccuparsi.


Mi piacciono i proc memorizzati, non so quante volte sono stato in grado di apportare modifiche a un'applicazione utilizzando una stored procedure che non ha prodotto alcun tempo di inattività per l'applicazione.

Grande fan di Transact SQL, l'ottimizzazione delle query di grandi dimensioni si è dimostrata molto utile per me. Non ho scritto alcun inline SQL in circa 6 anni!


Non sono un grande fan delle stored procedure, ma le uso in una condizione:

Quando la query è abbastanza grande, è meglio memorizzarla nel database come stored procedure invece di inviarla dal codice. In questo modo, invece di inviare enormi quantità di caratteri di stringa dal server delle applicazioni al database, verrà inviato solo il comando "EXEC SPNAME" .

Questo è eccessivo quando il server di database e il server Web non si trovano sulla stessa rete (ad esempio, comunicazione Internet). E anche se questo non è il caso, troppo stress significa molto spreco di banda.

Ma amico, sono così terribili da gestire. Li evito il più possibile.


Pensare in questo modo

Hai 4 server web e un sacco di applicazioni Windows che usano lo stesso codice SQL Ora hai capito che c'è un piccolo problema con il codice SQl, quindi preferisci cambiare il proc in 1 posto o spingere il codice a tutti i server web, reinstallare tutte le app desktop (clickonce potrebbe aiutare) su tutte le finestre di Windows

Preferisco i proc memorizzati

È anche più facile eseguire test delle prestazioni su un proc, inserirli in statistiche di analisi query set io / ora sul set showplan_text on e voilà

non è necessario eseguire il profiler per vedere esattamente ciò che viene chiamato

solo i miei 2 centesimi


Procedura di archiviazione.

Se un errore scivola o la logica cambia un po ', non è necessario ricompilare il progetto. Inoltre, consente l'accesso da diverse fonti, non solo l'unico posto in cui hai codificato la query nel tuo progetto.

Non penso sia più difficile mantenere le stored procedure, non dovresti codificarle direttamente nel database ma prima in file separati, quindi puoi semplicemente eseguirle su qualunque DB devi configurare.


Qualcosa che non ho visto menzionato finora: le persone che conoscono meglio il database non sono sempre le persone che scrivono il codice dell'applicazione. Le stored procedure danno ai membri del database un modo per interfacciarsi con programmatori che non vogliono davvero imparare molto su SQL. I database di grandi dimensioni, e soprattutto quelli legacy, non sono le cose più facili da comprendere, quindi i programmatori potrebbero preferire un'interfaccia semplice che fornisca loro ciò di cui hanno bisogno: lascia che i DBA comprendano come unire le 17 tabelle per farlo accadere.

Detto questo, i linguaggi utilizzati per scrivere stored procedure (PL / SQL è un noto esempio) sono piuttosto brutali. In genere non offrono alcuna delle sottigliezze che si vedono nelle odierne lingue imperative, OOP o funzionali popolari. Pensa COBOL.

Quindi, attenersi alle stored procedure che si limitano ad astrarre i dettagli relazionali piuttosto che quelli che contengono la logica di business.


Questo è in discussione su alcuni altri thread qui al momento. Sono un sostenitore costante delle stored procedure, sebbene vengano presentati alcuni buoni argomenti per Linq to Sql.

L'incorporamento di query nel codice ti accoppia strettamente al tuo modello di dati. Le stored procedure sono una buona forma di programmazione contrattuale, il che significa che un DBA ha la libertà di modificare il modello di dati e il codice nella procedura, purché il contratto rappresentato dagli input e output della stored procedure sia mantenuto.

L'ottimizzazione dei database di produzione può essere estremamente difficile quando le query sono nascoste nel codice e non in una posizione centrale facile da gestire.

[Modifica] Ecco un'altra discussione corrente


Secondo me non è possibile votare sì o no su questa domanda. Dipende totalmente dal design della tua applicazione.

Io voto totalmente contro l'uso di SP in un ambiente a 3 livelli, in cui hai davanti un server delle applicazioni. In questo tipo di ambiente il server delle applicazioni è lì per eseguire la logica aziendale. Se usi SP ulteriormente, inizi a distribuire l'implementazione della logica di business su tutto il tuo sistema e diventerà poco chiaro chi è responsabile di cosa. Alla fine ti ritroverai con un application server che fondamentalmente non farà altro che il seguente:

(Pseudocode)

Function createOrder(Order yourOrder) 
Begin
  Call SP_createOrder(yourOrder)
End

Quindi, alla fine, hai il tuo livello intermedio in esecuzione su questo cluster 4 Server molto potente, ciascuno dotato di 16 CPU e in realtà non farà nulla! Che spreco!

Se hai un grosso client gui che si collega direttamente al tuo DB o forse più applicazioni è una storia diversa. In questa situazione, gli SP possono fungere da una sorta di pseudo livello intermedio che disaccoppia l'applicazione dal modello dati e offre un accesso controllabile.


Sono fermamente dalla parte dei proc memorizzati supponendo che non si imbrogliano e si usi l'SQL dinamico nel proc memorizzato. Innanzitutto, l'utilizzo di stored procs consente al dba di impostare le autorizzazioni a livello di processo memorizzato e non a livello di tabella. Questo è fondamentale non solo per combattere gli attacchi di SQL injection, ma anche per impedire agli addetti ai lavori di accedere direttamente al database e modificare le cose. Questo è un modo per aiutare a prevenire le frodi. Nessun database che contenga informazioni personali (SSN, numeri di carte di credito, ecc.) O che in ogni caso crei transazioni finanziarie dovrebbe mai essere accessibile tranne che attraverso procedure strored. Se usi un altro metodo, lasci il tuo database aperto a tutti gli individui dell'azienda per creare transazioni finanziarie false o rubare dati che possono essere utilizzati per il furto di identità.

I proc memorizzati sono anche molto più facili da gestire e ottimizzare le prestazioni rispetto all'SQL inviato dall'app. Consentono inoltre al dba di vedere quale sarà l'impatto di un cambiamento strutturale del database sul modo in cui i dati sono accessibili. Non ho mai incontrato un buon dba che consentisse l'accesso dinamico al database.


Sono un grande sostenitore del codice su SPROC. La ragione numero uno è mantenere il codice strettamente accoppiato, quindi un secondo vicino è la facilità del controllo del codice sorgente senza un sacco di utility personalizzate per farlo entrare.

Nel nostro DAL se abbiamo istruzioni SQL molto complesse, generalmente le includiamo come file di risorse e li aggiorniamo come necessario (potrebbe anche essere un assembly separato, e scambiato per db, ecc ...).

Ciò mantiene il nostro codice e le nostre chiamate sql memorizzate nello stesso controllo di versione, senza "dimenticare" di eseguire alcune applicazioni esterne per l'aggiornamento.


Uno dei suggerimenti di una sessione Microsoft TechEd sulla sicurezza a cui ho partecipato, per effettuare tutte le chiamate tramite stored proc e negare l'accesso direttamente alle tabelle. Questo approccio è stato definito come una sicurezza aggiuntiva. Non sono sicuro che ne valga la pena solo per motivi di sicurezza, ma se stai già utilizzando stored procs, non potrebbe far male.


Usa il tuo codice app come fa meglio: gestire la logica.
Utilizza il tuo database per ciò che sa fare meglio: archivia i dati.

È possibile eseguire il debug di stored procedure ma è più facile eseguire il debug e la logica di mantenimento del codice. Di solito finisci di ricompilare il codice ogni volta che cambi il modello del database.

Anche le stored procedure con parametri di ricerca facoltativi sono molto carenti perché è necessario specificare in anticipo tutti i parametri possibili e le ricerche complesse a volte non sono possibili perché non si può prevedere quante volte un parametro verrà ripetuto nella seach.


CON

Trovo che fare molta elaborazione all'interno delle stored procedure renderebbe il tuo server DB un unico punto di inflessibilità, quando si tratta di ridimensionare la tua azione.

Tuttavia, fare tutto ciò che scricchiola nel tuo programma rispetto al server SQL, ti consente di scalare di più se hai più server che eseguono il tuo codice. Ovviamente questo non si applica ai proc memorizzati che eseguono solo il normale recupero o aggiornamento, ma a quelli che eseguono più elaborazioni come il loop su dataset.

PROFESSIONISTI

  1. Prestazioni per ciò che può valere (evita l'analisi della query tramite il driver DB / ricreazione del piano, ecc.)
  2. La manipolazione dei dati non è incorporata nel codice C / C ++ / C #, il che significa che ho un codice di livello inferiore da esaminare. SQL è meno dettagliato e più semplice da consultare quando elencato separatamente.
  3. A causa della separazione, la gente è in grado di trovare e riutilizzare il codice SQL molto più facilmente.
  4. È più facile cambiare le cose quando lo schema cambia: devi solo dare lo stesso output al codice e funzionerà perfettamente
  5. Più facile da portare su un altro database.
  6. Posso elencare le autorizzazioni individuali sulle mie stored procedure e controllare l'accesso anche a quel livello.
  7. Posso profilare il mio codice di query / persistenza dei dati separato dal mio codice di trasformazione dei dati.
  8. Posso implementare condizioni mutevoli nella mia procedura memorizzata e sarebbe facile personalizzarlo sul sito di un cliente.
  9. Diventa più facile usare alcuni strumenti automatici per convertire insieme schemi e istruzioni piuttosto che quando sono incorporati nel mio codice in cui dovrei cercarli.
  10. Garantire le best practice per l'accesso ai dati è più semplice quando si ha tutto il codice di accesso ai dati all'interno di un singolo file: posso controllare le query che accedono alla tabella non performante o quella che utilizza un livello più alto di serializzazione o selezionare * nel codice ecc. .
  11. Diventa più facile trovare modifiche alla logica dello schema / modifica della manipolazione dei dati quando tutto è elencato in un file.
  12. Diventa più facile eseguire ricerche e sostituire le modifiche su SQL quando si trovano nello stesso posto, ad es. Modifica / aggiunta delle istruzioni di isolamento della transazione per tutti i proc memorizzati.
  13. Io e il DBA scopriamo che avere un file SQL separato è più facile / conveniente quando il DBA deve rivedere le mie cose SQL.
  14. Infine, non devi preoccuparti degli attacchi di SQL injection perché alcuni membri pigri del tuo team non hanno utilizzato le query parametrizzate quando utilizzano sql incorporati.

Tronchi più piccoli

Un altro pro minore per le stored procedure che non è stato menzionato: quando si tratta di traffico SQL, l'accesso ai dati basato su sp genera molto meno traffico. Questo diventa importante quando si monitora il traffico per l'analisi e la profilazione: i log saranno molto più piccoli e leggibili.





stored-procedures