Генератор кода C# для хранимых процедур

Автор: McCodeJunky, перевод выполнил kpumuk специально для Realcoding.NET
оригинал  http://codeproject.com/cs/database/CSCodeBuilder.asp

Эта хранимая процедура сгенерирует весь код C# для вызова любой хранимой процедуры 

Скачать примеры к статье - 1,69 кб

Введение

Вы съели собаку на создании кода для связывания всех параметров для хранимых процедур? Я съел. Итак, я написал эту хранимую процедуру для создания кода для себя. (Я люблю код, который пишет код). Она была написана для использования вместе с "Microsoft Data Application Block" (Блок данных для приложений от компании Microsoft) (SQLHelper.cs), однако он может быть легко исправлен так, чтобы писал код без использования MDAB, или даже код на VB. Скрипт управляет как входными, так и выходными параметрами, устанавливая размеры текстовых типов, вы даже можете использовать его для прямого доступа к представлениям или таблицам.

Использование кода

Для использования, просто скопируйте код в Query Analyzer и запустите. В результате будет создана SPROC с названием "tools_CS_SPROC_Builder".

Для использования, просто выполните SPROC, передав имя SPROC, для которой хотите создать код (см. ниже). Примечание: не используйте никаких префиксов владельца и т.п. (dbo.).

EXEC tools_CS_SPROC_Builder 'mySprocsNameHere'

В окне сообщений Query Analyzer выведет весь код, требуемый для вашего класса или code behind. Есть переменная в "tools_CS_SPROC_Builder" с именем "@connName", которая позволяет установить имя для экземпляра вашего подключения. По умолчанию она установлена в "conn.Connection" - просто измените ее в соответствии с вашими соглашениями об именовании в проекте.

Код

CREATE     PROCEDURE tools_CS_SPROC_Builder
(
@objName nvarchar(100)
)
AS
/*
___________________________________________________________________
Название: CS SPROC Builder
Версия:   1
Дата:     10/09/2004
Автор:    Paul McKenzie
Описание: Вызовите эту процедуру, передав имя вашего объекта базы данных, который вы хотите 
  вставлять/обновлять
  из .NET (C#) и процедура вернет код,
  который следует скопировать и вставить в ваше приложение. 
  Эта версия предназначена
  для использования с "Microsoft Data Application Block".
  
  
*/
SET NOCOUNT ON

DECLARE @parameterCount int
DECLARE @errMsg varchar(100)
DECLARE @parameterAt varchar(1)
DECLARE @connName varchar(100)
// Измените следующую переменную на имя вашего экземпляра соединения
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
  -- Извлечь первую строку.
  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='@'
  -- Проверить @@FETCH_STATUS, чтобы определить, если ли еще строки для получения.
  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) 
   -- Вывести параметр
   PRINT '   paramsToStore[' + cast(@col_order-1 as varchar) 
    + '] = new SqlParameter("' + @parameterAt + @col_name
    + '", SqlDbType.' + @col_redef
    + ');'

   -- Если тип - строка,- вывести размер
   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 =  ;'
      -- Это выполняется, пока предыдущее получение было успешным.
      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
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Пример вывода

try
   {
   SqlParameter[] paramsToStore = new SqlParameter[9];
 
   paramsToStore[0] = new SqlParameter("@organisationid", SqlDbType.BigInt);
   paramsToStore[0].Value =  ;
   paramsToStore[1] = new SqlParameter("@DisplayName", SqlDbType.NVarChar);
   paramsToStore[1].Size=100;
   paramsToStore[1].Value =  ;
   paramsToStore[2] = new SqlParameter("@DefaultCurrencyFID", SqlDbType.Int);
   paramsToStore[2].Value =  ;
   paramsToStore[3] = new SqlParameter("@TaxCode", SqlDbType.NVarChar);
   paramsToStore[3].Size=60;
   paramsToStore[3].Value =  ;
   paramsToStore[4] = new SqlParameter("@UserFID", SqlDbType.BigInt);
   paramsToStore[4].Value =  ;
   paramsToStore[5] = new SqlParameter("@IsClient", SqlDbType.Bit);
   paramsToStore[5].Value =  ;
   paramsToStore[6] = new SqlParameter("@IsContractor", SqlDbType.Bit);
   paramsToStore[6].Value =  ;
   paramsToStore[7] = new SqlParameter("@IsSupplier", SqlDbType.Bit);
   paramsToStore[7].Value =  ;
   paramsToStore[8] = new SqlParameter("@IsDesigner", SqlDbType.Bit);
   paramsToStore[8].Value =  ;
 
   SqlHelper.ExecuteNonQuery(conn.Connection, 
CommandType.StoredProcedure,"usp_Insert_Organisation", paramsToStore);
   }
catch(Exception excp)
   {
   }
finally
   {
   conn.Connection.Dispose();
   conn.Connection.Close();
   }

Все, что вам нужно,- скопировать, вставить и заполнить значения, которые вы хотите передать, а также отловить исключения (Да, вам придется написать кое-какой код!).

Интересующие моменты

Для того, чтобы обработать переменную @col_xtype из sysobjects, я запустил множество тестов на таблице, которую создал с переменными разных типов в обоих направлениях. Процедура, несомненно, работает отлично на всех используемых типах данных, но я не имел возможности протестировать все типы из .NET, поэтому если вы найдете баг - дайте мне знать. Наслаждайтесь!