使用系统表根据存储过程名字生成ADO.NET数据库访问代码

本是古典何须时尚 2022-03-28 15:38 95阅读 0赞

下面的存储过程可以在查询管理器里面生成ADO.NET 存储过程调用代码，省去很多的Coding功夫。

![Image 1][] SET  ANSI\_NULLS  ON   
![Image 1][] GO   
![Image 1][] SET  QUOTED\_IDENTIFIER  ON   
![Image 1][] GO   
![Image 1][]  
![Image 1][]   
![Image 1][]  
![Image 1][]  
![Image 1][]  
![Image 1][]   
![Image 1][]  
![Image 1][] ALTER     PROCEDURE   \[ dbo \] . \[ sqltoolforexcuteandadapter \]   
![Image 1][]  
![Image 1][](  
![Image 1][]  
![Image 1][] @objName   nvarchar ( **100** ), \-- 存储过程名称   
![Image 1][]   
![Image 1][] @isexcute   int   \-- 是否为execute 或者是sqladapter  0是execute,1是sqladapter   
![Image 1][]   
![Image 1][])  
![Image 1][]  
![Image 1][] AS   
![Image 1][]  
![Image 1][]  
![Image 1][]  
![Image 1][]  
![Image 1][] SET  NOCOUNT  ON   
![Image 1][]  
![Image 1][] DECLARE   @parameterCount   int   
![Image 1][]  
![Image 1][] DECLARE   @errMsg   varchar ( **100** )  
![Image 1][]  
![Image 1][] DECLARE   @parameterAt   varchar ( **1** )  
![Image 1][]  
![Image 1][] DECLARE   @connName   varchar ( **100** )  
![Image 1][]  
![Image 1][] DECLARE   @outputValues   varchar ( **100** )  
![Image 1][]  
![Image 1][] \-- Change the following variable to the name of your connection instance   
![Image 1][]   
![Image 1][] SET   @connName = ' conn.Connection '   
![Image 1][]  
![Image 1][] SET   @parameterAt = ''   
![Image 1][]  
![Image 1][] SET   @outputValues = ''   
![Image 1][]  
![Image 1][] SELECT   
![Image 1][]  
![Image 1][]        dbo.sysobjects.name  AS  ObjName,  
![Image 1][]  
![Image 1][]        dbo.sysobjects.xtype  AS  ObjType,  
![Image 1][]  
![Image 1][]        dbo.syscolumns.name  AS  ColName,  
![Image 1][]  
![Image 1][]        dbo.syscolumns.colorder  AS  ColOrder,  
![Image 1][]  
![Image 1][]        dbo.syscolumns.length  AS  ColLen,  
![Image 1][]  
![Image 1][]        dbo.syscolumns.colstat  AS  ColKey,  
![Image 1][]  
![Image 1][]        dbo.syscolumns.isoutparam  AS  ColIsOut,  
![Image 1][]  
![Image 1][]        dbo.systypes.xtype  
![Image 1][]  
![Image 1][] INTO  \#t\_obj  
![Image 1][]  
![Image 1][] FROM   
![Image 1][]  
![Image 1][]        dbo.syscolumns  INNER   JOIN   
![Image 1][]  
![Image 1][]        dbo.sysobjects  ON  dbo.syscolumns.id  =  dbo.sysobjects.id  INNER   JOIN   
![Image 1][]  
![Image 1][]        dbo.systypes  ON  dbo.syscolumns.xtype  =  dbo.systypes.xtype  
![Image 1][]  
![Image 1][] WHERE   
![Image 1][]  
![Image 1][]        (dbo.sysobjects.name  =   @objName )  
![Image 1][]  
![Image 1][]         AND   
![Image 1][]  
![Image 1][]        (dbo.systypes.status  <>   **1** )    
![Image 1][]   
![Image 1][] ORDER   BY   
![Image 1][]  
![Image 1][]        dbo.sysobjects.name,  
![Image 1][]  
![Image 1][]        dbo.syscolumns.colorder  
![Image 1][]  
![Image 1][]   
![Image 1][]  
![Image 1][] SET   @parameterCount = ( SELECT   count ( \* )  FROM  \#t\_obj)  
![Image 1][]  
![Image 1][] IF ( @parameterCount < **1** )  SET   @errMsg = ' No Parameters/Fields found for  '   \+   @objName   
![Image 1][]  
![Image 1][] IF ( @errMsg   is   null )  
![Image 1][]  
![Image 1][]         BEGIN   
![Image 1][]  
![Image 1][]                 print   ' SqlConnection conn = new SqlConnection("");  
![Image 1][]  
![Image 1][]            SqlCommand com = new SqlCommand(" ' \+ @objName \+ ' ", conn); '   
![Image 1][]  
![Image 1][]                 print   ' com.CommandType = CommandType.StoredProcedure; '   
![Image 1][]  
![Image 1][]                 PRINT   '    SqlParameter\[\] Parameters = new SqlParameter\[ '   \+   
![Image 1][]  
![Image 1][] cast ( @parameterCount   as   varchar )  \+   ' \]; '   
![Image 1][]  
![Image 1][]                 PRINT   ''   
![Image 1][]  
![Image 1][]   
![Image 1][]  
![Image 1][]                 DECLARE   @source\_name   nvarchar ,  
![Image 1][]  
![Image 1][]                                 @source\_type   varchar ,  
![Image 1][]  
![Image 1][]                         @col\_name   nvarchar ( **100** ),  
![Image 1][]  
![Image 1][]                                 @col\_order   int ,  
![Image 1][]  
![Image 1][]                                 @col\_type   varchar ( **20** ),  
![Image 1][]  
![Image 1][]                         @col\_len   int ,  
![Image 1][]  
![Image 1][]                                 @col\_key   int ,  
![Image 1][]  
![Image 1][]                                 @col\_xtype   int ,  
![Image 1][]  
![Image 1][]                                 @col\_redef   varchar ( **20** ),  
![Image 1][]  
![Image 1][]                                 @col\_isout   tinyint   
![Image 1][]  
![Image 1][]   
![Image 1][]  
![Image 1][]                 DECLARE  cur  CURSOR   FOR   
![Image 1][]  
![Image 1][]                 SELECT   \*   FROM  \#t\_obj  
![Image 1][]  
![Image 1][]                 OPEN  cur  
![Image 1][]  
![Image 1][]                 \--  Perform the first fetch.   
![Image 1][]   
![Image 1][]                 FETCH   NEXT   FROM  cur  INTO   
![Image 1][]  
![Image 1][] @source\_name , @source\_type , @col\_name , @col\_order , @col\_len , @col\_key , @col\_isout , @col\_xtype   
![Image 1][]  
![Image 1][]   
![Image 1][]  
![Image 1][]                         if ( @source\_type = N ' U ' )  SET   @parameterAt = ' @ '   
![Image 1][]  
![Image 1][]                         \--  Check @@FETCH\_STATUS to see if there are any more rows to fetch.   
![Image 1][]   
![Image 1][]                         WHILE   **@@FETCH\_STATUS**   =   **0**   
![Image 1][]  
![Image 1][]                                 BEGIN   
![Image 1][]  
![Image 1][]                                 SET   @col\_redef = ( SELECT   CASE   @col\_xtype   
![Image 1][]  
![Image 1][]                                         WHEN   **34**   THEN   ' Image '   
![Image 1][]  
![Image 1][]                                         WHEN   **35**   THEN   ' Text '   
![Image 1][]  
![Image 1][]                                         WHEN   **36**   THEN   ' UniqueIdentifier '   
![Image 1][]  
![Image 1][]                                         WHEN   **48**   THEN   ' TinyInt '   
![Image 1][]  
![Image 1][]                                         WHEN   **52**   THEN   ' SmallInt '   
![Image 1][]  
![Image 1][]                                         WHEN   **56**   THEN   ' Int '   
![Image 1][]  
![Image 1][]                                         WHEN   **58**   THEN   ' SmallDateTime '   
![Image 1][]  
![Image 1][]                                         WHEN   **59**   THEN   ' Real '   
![Image 1][]  
![Image 1][]                                         WHEN   **60**   THEN   ' Money '   
![Image 1][]  
![Image 1][]                                         WHEN   **61**   THEN   ' DateTime '   
![Image 1][]  
![Image 1][]                                         WHEN   **62**   THEN   ' Float '   
![Image 1][]  
![Image 1][]                                         WHEN   **99**   THEN   ' NText '   
![Image 1][]  
![Image 1][]                                         WHEN   **104**   THEN   ' Bit '   
![Image 1][]  
![Image 1][]                                         WHEN   **106**   THEN   ' Decimal '   
![Image 1][]  
![Image 1][]                                         WHEN   **122**   THEN   ' SmallMoney '   
![Image 1][]  
![Image 1][]                                         WHEN   **127**   THEN   ' BigInt '   
![Image 1][]  
![Image 1][]                                         WHEN   **165**   THEN   ' VarBinary '   
![Image 1][]  
![Image 1][]                                         WHEN   **167**   THEN   ' VarChar '   
![Image 1][]  
![Image 1][]                                         WHEN   **173**   THEN   ' Binary '   
![Image 1][]  
![Image 1][]                                         WHEN   **175**   THEN   ' Char '   
![Image 1][]  
![Image 1][]                                         WHEN   **231**   THEN   ' NVarChar '   
![Image 1][]  
![Image 1][]                                         WHEN   **239**   THEN   ' NChar '   
![Image 1][]  
![Image 1][]                                         ELSE   ' !MISSING '   
![Image 1][]  
![Image 1][]                                         END   AS  C)  
![Image 1][]  
![Image 1][]   
![Image 1][]  
![Image 1][]                                 \-- Write out the parameter   
![Image 1][]   
![Image 1][]                                 PRINT   '    Parameters\[ '   \+   cast ( @col\_order \- **1**   as   varchar )  
![Image 1][]  
![Image 1][]                                     \+   ' \] = new SqlParameter(" '   \+   @parameterAt   \+   @col\_name   
![Image 1][]  
![Image 1][]                                     \+   ' ", SqlDbType. '   \+   @col\_redef   
![Image 1][]  
![Image 1][]                                     \+   ' ); '   
![Image 1][]  
![Image 1][]   
![Image 1][]  
![Image 1][]                                 \-- Write out the parameter direction it is output   
![Image 1][]   
![Image 1][]                                 IF ( @col\_isout = **1** )  
![Image 1][]  
![Image 1][]                                         BEGIN   
![Image 1][]  
![Image 1][]                                                 PRINT   '    Parameters\[ ' \+   cast ( @col\_order \- **1**   as   varchar )  
![Image 1][]  
![Image 1][] \+ ' \].Direction=ParameterDirection.Output; '   
![Image 1][]  
![Image 1][]                                                 SET   @outputValues = @outputValues \+ '    ?=Parameters\[ ' \+   
![Image 1][]  
![Image 1][] cast ( @col\_order \- **1**   as   varchar )  \+ ' \].Value; '   
![Image 1][]  
![Image 1][]                                         END   
![Image 1][]  
![Image 1][]                                         ELSE   
![Image 1][]  
![Image 1][]                                         BEGIN   
![Image 1][]  
![Image 1][]                                                 \-- Write out the parameter value line   
![Image 1][]   
![Image 1][]                                                 PRINT   '    Parameters\[ ' \+   cast ( @col\_order \- **1**   as   varchar )  \+   ' \].Value = ?; '   
![Image 1][]  
![Image 1][]                                         END   
![Image 1][]  
![Image 1][]                                 \-- If the type is a string then output the size declaration   
![Image 1][]   
![Image 1][]                                 IF ( @col\_xtype = **231** ) OR ( @col\_xtype = **167** ) OR ( @col\_xtype = **175** ) OR ( @col\_xtype = **99** ) OR ( @col\_xtype = **35** )  
![Image 1][]  
![Image 1][]                                         BEGIN   
![Image 1][]  
![Image 1][]                                                 PRINT   '    Parameters\[ '   \+   cast ( @col\_order \- **1**   as   varchar )  \+   
![Image 1][]  
![Image 1][] ' \].Size= '   \+   cast ( @col\_len   as   varchar )  \+   ' ; '   
![Image 1][]  
![Image 1][]                                         END   
![Image 1][]  
![Image 1][]   
![Image 1][]  
![Image 1][]                                  \--  This is executed as long as the previous fetch succeeds.   
![Image 1][]   
![Image 1][]                         FETCH   NEXT   FROM  cur  INTO   
![Image 1][]  
![Image 1][] @source\_name , @source\_type , @col\_name , @col\_order ,  
![Image 1][]  
![Image 1][] @col\_len , @col\_key , @col\_isout , @col\_xtype   
![Image 1][]  
![Image 1][]         END   
![Image 1][]  
![Image 1][]   PRINT   ''   
![Image 1][]  
![Image 1][]   print   '        com.Parameters.AddRange(Parameters); '   
![Image 1][]  
![Image 1][] if        @isexcute   =   **0**   \-- 使用的execute方法执行sql语句   
![Image 1][]   
![Image 1][] begin   
![Image 1][]  
![Image 1][]   
![Image 1][]  
![Image 1][]       print   ' try  
![Image 1][]  
![Image 1][]            \{  
![Image 1][]  
![Image 1][]                conn.Open();  
![Image 1][]  
![Image 1][]                com.ExecuteNonQuery();  
![Image 1][]  
![Image 1][]            \}  
![Image 1][]  
![Image 1][]            catch (Exception ee)  
![Image 1][]  
![Image 1][]            \{  
![Image 1][]  
![Image 1][]                throw ee;  
![Image 1][]  
![Image 1][]            \}  
![Image 1][]  
![Image 1][]            finally  
![Image 1][]  
![Image 1][]            \{  
![Image 1][]  
![Image 1][]                conn.Close();  
![Image 1][]  
![Image 1][]            \} '   
![Image 1][]  
![Image 1][] end   
![Image 1][]  
![Image 1][] else   if   @isexcute   =   **1** \-- 需要返回数据集的话使用这个   
![Image 1][]   
![Image 1][] begin   
![Image 1][]  
![Image 1][]         print   ' try  
![Image 1][]  
![Image 1][]            \{  
![Image 1][]  
![Image 1][]                da.Fill(ds);  
![Image 1][]  
![Image 1][]            \}  
![Image 1][]  
![Image 1][]            catch (Exception ee)  
![Image 1][]  
![Image 1][]            \{  
![Image 1][]  
![Image 1][]                throw ee;  
![Image 1][]  
![Image 1][]            \}  
![Image 1][]  
![Image 1][]            finally  
![Image 1][]  
![Image 1][]            \{  
![Image 1][]  
![Image 1][]               //do what you want to do or dispose resoures.  
![Image 1][]  
![Image 1][]            \} '   
![Image 1][]  
![Image 1][] end   
![Image 1][]  
![Image 1][]   
![Image 1][]  
![Image 1][]   CLOSE  cur  
![Image 1][]  
![Image 1][]   DEALLOCATE  cur  
![Image 1][]  
![Image 1][]  END   
![Image 1][]  
![Image 1][] if ( LEN ( @errMsg ) > **0** )  PRINT   @errMsg   
![Image 1][]  
![Image 1][] DROP   TABLE  \#t\_obj  
![Image 1][]  
![Image 1][] SET  NOCOUNT  ON

测试代码：

![Image 1][] sqltoolforexcuteandadapter  ' YourProcName ' , **1** \-- or 0

显示出啦的结果是：

![Image 1][] SqlConnection conn  =   new  SqlConnection( "" );  
![Image 1][]  
![Image 1][]            SqlCommand com  =   new  SqlCommand( " YourProcName " , conn);  
![Image 1][]com.CommandType  =  CommandType.StoredProcedure;  
![Image 1][]   SqlParameter\[\] Parameters  =   new  SqlParameter\[ 1 \];  
![Image 1][]   
![Image 1][]   Parameters\[ 0 \]  =   new  SqlParameter( " @yourparam " , SqlDbType.VarChar);  
![Image 1][]   Parameters\[ 0 \].Value  =   ? ;  
![Image 1][]   Parameters\[ 0 \].Size = 6 ;  
![Image 1][]   
![Image 1][]       com.Parameters.AddRange(Parameters);  
![Image 1][] try   
![Image 1][]  
![Image 1][]![Image 1][]             ![dot.gif][] \{  
![Image 1][]  
![Image 1][]                da.Fill(ds);  
![Image 1][]  
![Image 1][]            \}   
![Image 1][]  
![Image 1][]             catch  (Exception ee)  
![Image 1][]  
![Image 1][]![Image 1][]             ![dot.gif][] \{  
![Image 1][]  
![Image 1][]                throw ee;  
![Image 1][]  
![Image 1][]            \}   
![Image 1][]  
![Image 1][]             finally   
![Image 1][]  
![Image 1][]![Image 1][]             ![dot.gif][] \{  
![Image 1][]  
![Image 1][]               //do what you want to do or dispose resoures.  
![Image 1][]  
![Image 1][]            \}

这样非常方便，而且不用以后手写非常多的参数了，参数指定了精确的长度和类型，速度更快。  
  
参考自codeproject

[Image 1]: 
[dot.gif]: /images/20220328/caa6971a345246c3a02206f01167c153.png