MS SQL Server
  1. MS SQL Server
  3. Déterminez si vous souhaitez générer ExecuteNonQuery ou ExecuteReader à partir du schéma
Intereting Posts
Rejoindre plusieurs tables dans SQL Server Casting varchar comme date Sélectionner les lignes de base sur le sous-set Comment get le time passé à l'intérieur et à l'extérieur dans SQL Requête renvoyant la list de toutes les procédures stockées dans une database SQL Server avec varchar en tant que paramètre server sql si la déclaration ne fonctionne pas Pourquoi les tags disparaissent-ils lors de l'ajout de '; 'au champ qui est combiné avec' pour le path xml '? NHibernate: Comment actualiser correctement une entité, y compris les collections enfants? Meilleure façon de gérer plusieurs instructions de suppression Impossible de se connecter à Yii avec SQL Server local 2012 Comment comparer la performance des requêtes via LINQ-to-SQL et SQL-Server Utilisation de la procédure stockée dans Select (T-SQL) Comment faire pour supprimer la contrainte non nulle dans le server SQL à l'aide de la requête Conversion de types de données d'Oracle vers SQL Server dans un projet ASP.NET Comment transposer des colonnes de jeu d'loggings en lignes

Déterminez si vous souhaitez générer ExecuteNonQuery ou ExecuteReader à partir du schéma

Je tente de créer un mécanisme de mappage de procédure stockée / ADO.NET où une procédure stockée avec des parameters devient 

object MyStoredProcedure.Execute(out returnValue, param1, param2, ...)

Le problème vient lorsque vous essayez de générer la méthode de récupération de données réelle. Je peux facilement get la plupart des informations de schéma dont j'ai besoin dans les vues Schéma d'informations, mais je ne peux pas find de manière fiable quel type de return (paramètre de sortie par rapport à SELECT / SqlDataReader vs deux) devrait provenir de la procédure et appeler ExecuteNonQuery ou ExecuteReader.

Dans le pire des cas, je peux probablement parsingr le text de la procédure, mais il y a toutes sortes de choses géniales qui pourraient mal tourner là-bas.

La raison de la génération de code est que la database d'application contient plusieurs centaines de procédures stockées. Et nous avons hérité de ce projet, donc il n'y a aucun moyen de nous envelopper de ces nombreux process que nous n'avons pas créés.

En fait, j'ai deux objectives principaux pour la génération ADO.NET:

1) Supprimer tous les littéraux de string (noms de proc stockés dans la création de SqlCommand, noms de parameters dans la création de SqlParameter, etc.) à partir de l'application. De cette façon, quand une procédure ou un schéma de database change, nous pouvons régénérer les wrappers ADO.NET, et toutes les erreurs résultant de ces changements seront capturées au moment de la compilation.

2) Supprimer le besoin de creuser un proc pour déterminer les parameters, returnne les types, etc Donc, fondamentalement, la database elle-même devient une API, avec tous les détails de la procédure stockée interne abstraite.

Ouaip; ce n'est pas facile. Pour les cas simples , vous pouvez essayer d'exécuter le sp (awooga!) En passant des valeurs nulles pour tous les parameters et en utilisant SET FMTONLY ON – un peu risqué (les sprocs étendus sont exécutés, par exemple) et pas robuste puisque le TSQL . Mais une option.

Les "out" doivent être disponibles via des métadonnées; le "vieux" moyen serait syscolumns (il y a probablement une alternative d'info-schéma pour le faire correctement).

Juste comme une mise à jour; Si vous voulez que la database se décrive elle-même en tant qu'API, pensez peut-être aux fonctions UDF pour les sélections. avantages:

  • les métadonnées pour les valeurs de return sont rigides et faciles à interroger
  • il est composable à l'appelant

Ou; il suffit d'utiliser un ORM. LINQ-to-SQL fonctionnera volontiers avec ce type d'installation (y compris la composabilité); Entity Framework fera certainement tout ce dur travail pour vous pour les procédures stockées. Ou l'un des autres; NHibernate , LLBLGen Pro , etc. Ils ont tous abordé ce problème. Ce n'est pas sortingvial; pourquoi le réinventer?

Je ne pense pas que vous devriez être inquiet à ce sujet.
Ne serait-il pas préférable de fournir des surcharges et laisser l'user décider de la méthode à appeler?

Faites-moi savoir, si je n'ai pas bien compris la question.

En supposant que toutes vos procédures stockées sont cohérentes, c'est-à-dire qu'elles returnnent au plus un set de résultats, avec le même set de colonnes indépendamment des valeurs de paramètre ou de l'état de données de la database, tous les parameters OUTPUT , ..

Et en supposant aussi qu'il s'agit d'un développement ou au plus tard d'un outil de construction (et non d' un outil d'exécution) et que vous ayez un certain contrôle sur le contenu de Srored Procedure, ALORS voici ce que je vais essayer:

  1. Installez une norme de procédure stockée rétroactive qui exige que tous les sProcs aient un commentaire du formulaire suivant, qui doit fonctionner correctement dans la database de test (ou de développement?):

    'TEST: EXEC spThisProc [, …]

  2. Ecrivez votre outil pour extraire la list des procédures stockées, leurs parameters, types de données et parameters de sortie à partir des catalogues système (vous pouvez utiliser les tables INFORMATION_SCHEMA ROUTINES et PARAMETERS pour cela).

  3. Récupérez également le script pour tous les sProcs (à partir de la colonne ROUTINE_DEFINITON de la table INFORMATION_SCHEMA.ROUTINES, pour un location), puis extrayez le text de la command "'TEST:" (ci-dessus) et ExecuteReader contre la database Test. Ensuite, testez le Resultset returnné pour voir s'il contient des datasets (ou est-ce "Tables"?). Si ce n'est pas le cas, marquez sProc comme ayant besoin de ExecuteNonQuery, sinon, extrayez les définitions de colonne de l'set de données renvoyé et utilisez plutôt que de générer le code ExecuteReader correspondant pour vos définitions de class.

espère que cela pourrait aider à récupérer le type de parameters 

  select s.id, s.name, t.name as [type], t.length,s.isoutparam from syscolumns s inner join systypes t on s.xtype = t.xtype where id = (select id from sysobjects where name = 'SP NAME')

Et consultez cet excellent lien – get la list des colonnes renvoyées à partir de la sélection SQL

c'est un exemple de requête 

 USE tempdb GO IF OBJECT_ID('dbo.TestSP') IS NOT NULL DROP PROCEDURE dbo.TestSP; GO IF NOT EXISTS (SELECT * FROM sys.servers WHERE name = 'Loopback') BEGIN EXEC sp_addlinkedserver @server = 'Loopback', @srvproduct = '', @provider = 'SQLOLEDB', @datasrc = @@servername END GO CREATE PROC dbo.TestSP AS SELECT 1 as [ID], 'Name' as [Name], 'Boston' as [City] GO DECLARE @MyXML XML SELECT @MyXML = (SELECT * FROM (SELECT * FROM OPENQUERY(Loopback,'SET FMTONLY ON; EXEC tempdb.dbo.TestSP; SET FMTONLY OFF')) vw FOR XML AUTO, XMLSCHEMA) SELECT @MyXML

SET FMTONLY -> Il vous permettra d'exécuter un SP, mais seulement récupérer les métadonnées

En outre, vous pouvez essayer de consulter SqlServer SMO http://msdn.microsoft.com/en-us/library/ms162169.aspx pour interroger des informations sur votre database sans avoir à écrire des requêtes directement sur le schéma d'informations.