Shulou(Shulou.com)06/02 Report--

Editor to share with you how to automatically convert stored procedures into C # source code, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article. Let's go and learn about it!

The following is the source code for the stored procedure:

CREATE PROCEDURE dbo.tools_CS_SPROC_Builder (@ objName nvarchar (100)) AS / * _ Name: CS SPROC Builder Version: 1 Date: 20 Author 06 2004 Author: Paul McKenzie Description: Call this stored procedue passing the name of your database object that you wish to insert/update from .NET (C #) and the code returns code to copy and paste into your application. This version is for use with "Microsoft Data Application Block". Sample: EXEC tools_CS_SPROC_Builder 'InsertSQL' * / SET NOCOUNT ON DECLARE @ parameterCount int DECLARE @ errMsg varchar DECLARE @ parameterAt varchar DECLARE @ connName varchar SET @ connName='conn.Connection' SET @ parameterAt='' SELECT dbo.sysobjects.name AS ObjName, dbo.sysobjects.xtype AS ObjType, dbo.syscolumns.name AS ColName, dbo.syscolumns.colorder AS ColOrder, dbo.syscolumns.length AS ColLen, dbo.syscolumns.colstat AS ColKey Dbo.systypes.xtype INTO # t_obj FROM dbo.syscolumns INNER JOIN dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id INNER JOIN dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype WHERE (dbo.sysobjects.name = @ objName) AND (dbo.systypes.status

< >

1) ORDER BY dbo.sysobjects.name, dbo.syscolumns.colorder SET @ parameterCount= (SELECT count (*) FROM # t_obj) IF (@ parameterCount

< 1) SET @errMsg='No Parameters/Fields found for ' + @objName IF(@errMsg is null) BEGIN PRINT 'try' PRINT ' {' PRINT ' SqlParameter[] paramsToStore = new SqlParameter[' + cast(@parameterCount as varchar) + '];' PRINT '' DECLARE @source_name nvarchar,@source_type varchar,@col_name nvarchar(100),@col_order int,@col_type varchar(20),@col_len int,@col_key int,@col_xtype int,@col_redef varchar(20) DECLARE cur CURSOR FOR SELECT * FROM #t_obj OPEN cur -- Perform the first fetch. FETCH NEXT FROM cur INTO @source_name,@source_type,@col_name,@col_order,@col_len,@col_key,@col_xtype if(@source_type=N'U') SET @parameterAt='@' -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN SET @col_redef=(SELECT CASE @col_xtype WHEN 34 THEN 'Image' WHEN 35 THEN 'Text' WHEN 48 THEN 'TinyInt' WHEN 52 THEN 'SmallInt' WHEN 56 THEN 'Int' WHEN 58 THEN 'SmallDateTime' WHEN 59 THEN 'Real' WHEN 60 THEN 'Money' WHEN 61 THEN 'DateTime' WHEN 62 THEN 'Float' WHEN 99 THEN 'NText' WHEN 104 THEN 'Bit' WHEN 106 THEN 'Decimal' WHEN 122 THEN 'SmallMoney' WHEN 127 THEN 'BigInt' WHEN 165 THEN 'VarBinary' WHEN 167 THEN 'VarChar' WHEN 173 THEN 'Binary' WHEN 175 THEN 'Char' WHEN 231 THEN 'NVarChar' WHEN 239 THEN 'NChar' ELSE '!MISSING' END AS C) --Write out the parameter PRINT ' paramsToStore[' + cast(@col_order-1 as varchar) + '] = new SqlParameter("' + @parameterAt + @col_name + '", SqlDbType.' + @col_redef + ');' --If the type is a string then output the size declaration IF(@col_xtype=231)OR(@col_xtype=167)OR(@col_xtype=175)OR(@col_xtype=99)OR(@col_xtype=35) BEGIN PRINT ' paramsToStore[' + cast(@col_order-1 as varchar) + '].Size=' + cast(@col_len as varchar) + ';' END PRINT ' paramsToStore['+ cast(@col_order-1 as varchar) + '].Value = ;' -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM cur INTO @source_name,@source_type,@col_name,@col_order,@col_len,@col_key,@col_xtype END PRINT '' PRINT ' SqlHelper.ExecuteNonQuery(' + @connName + ', CommandType.StoredProcedure,"' + @objName + '", paramsToStore);' PRINT ' }' PRINT 'catch(Exception excp)' PRINT ' {' PRINT ' }' PRINT 'finally' PRINT ' {' PRINT ' ' + @connName + '.Dispose();' PRINT ' ' + @connName + '.Close();' PRINT ' }' CLOSE cur DEALLOCATE cur END if(LEN(@errMsg)>

0) PRINT @ errMsg DROP TABLE # t_obj SET NOCOUNT ON GO

Example: stored procedure name '1percent Processing adminstration publish'

Exec dbo.tools_CS_SPROC_Builder '1Processing adminstration publish'

The display result is as follows (C # source code):

Try {SqlParameter [] paramsToStore = new SqlParameter [4]; paramsToStore [0] = new SqlParameter ("@ memberName", SqlDbType.VarChar); paramsToStore [0] .size = 60; paramsToStore [0] .value =; paramsToStore [1] = new SqlParameter ("@ type", SqlDbType.Int); paramsToStore [1] .value =; paramsToStore [2] = new SqlParameter ("@ static", SqlDbType.Int); paramsToStore [2] .Value = ParamsToStore [3] = new SqlParameter ("@ returnType", SqlDbType.Int); paramsToStore [3] .value =; SqlHelper.ExecuteNonQuery (conn.Connection, CommandType.StoredProcedure, "1_Proc_admin_publish", paramsToStore);} catch (Exception excp) {} finally {conn.Connection.Dispose (); conn.Connection.Close () } is all the contents of the article "how to automatically convert stored procedures to C # source code". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel! The above is all the contents of the article "how to automatically convert stored procedures to C # source code". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.