Creating Insert update procedure and C# caller function for SQL table.

Hi All,

           I was just wondering that how many of you wants to get rid of minor repeated things that we need to do in every day code, most of you might have your own tools to do that but today ill be sharing two simple procedure that will help you to create not only the insert update procedure but also the C# caller function for that procedure so all you need to do is to create a table and you are done with all caller functions.

           Also ill keep on adding the procedures which will generate the class objects and properties for table, right now i am using code smith code generator for that ;)

           Lets start with a procedure which will generate the insert and update procedure for any table in SQL.

/****** Object:  StoredProcedure [dbo].[DynamicInsertUpdate]    Script Date: 09/10/2012 19:19:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[DynamicInsertUpdate] 
@tablename varchar(800)
--,@DbName nvarchar(200)
AS

declare @vtblcolumns varchar(max)
declare @vcolumns1 varchar(max)
declare @vcolumns2 varchar(max)
declare @vtblDtypes varchar(max)
Declare @vstr varchar(max) 
declare @vColumns varchar(max)
Declare @vPKcol varchar(250)
declare @Sid varchar(200)
  SELECT @sid =  TABLE_NAME FROM information_schema.tables WHERE table_name = @tablename AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') =1

  Select @vPKcol = c.COLUMN_NAME 
    from     INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
            where     pk.TABLE_NAME = @tableName
            and    CONSTRAINT_TYPE = 'PRIMARY KEY'
            and    c.TABLE_NAME = pk.TABLE_NAME
            and    c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

  select @vtblcolumns =  COALESCE(@vtblcolumns+',','')+ COLUMN_NAME + '='+'@P'+ COLUMN_NAME + ' 'from information_schema.columns where TABLE_NAME= @tablename  AND COLUMN_NAME NOT IN (@vPKcol)
  
  if @sid = null 
    select @vcolumns1 =    COALESCE(@vcolumns1+',','')+ COLUMN_NAME,@vColumns2 = COALESCE(@vColumns2+',','') + '@p'+ Column_NAME from information_schema.columns where TABLE_NAME= @tablename 
  else 
 select @vcolumns1 =    COALESCE(@vcolumns1+',','')+ COLUMN_NAME,@vColumns2 = COALESCE(@vColumns2+',','') + '@p'+ Column_NAME from information_schema.columns where TABLE_NAME= @tablename and COLUMN_NAME <> @vPKcol
--    Select  from information_schema.columns where TABLE_NAME =@tablename

   
      
      Select @vColumns = COALESCE(@vColumns+',','') + 
        '@p'+ Column_NAME + ' ' + 
        Data_Type  + 
        case when Character_Maximum_length > 0 then
            '(' + cast(Character_Maximum_length as varchar(10)) + ')'
        else ''
        end from information_schema.columns where TABLE_NAME =@tablename
  


Select @vstr = 
'
IF Object_ID ( ''UP_'+@tablename+'_InsertUpdate '') IS NOT NULL
    DROP Procedure UP_'+@tablename+'_InsertUpdate
GO

SET ANSI_NULLS ON  
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        '+system_user+'
-- Create date: '+convert (varchar (50),getdate(),103)+'
-- Description:    This is Auto Generated Code for '+@tablename+' Insert and Update Record for Single Primary Key Table
-- =============================================
CREATE PROCEDURE UP_'+@tablename+'_InsertUpdate 
('+@vColumns+ '    
)
As
BEGIN
SET NOCOUNT ON;

    IF EXISTS (Select '+@vpkcol+' from '+@tablename+ ' where '+@vpkcol+'='+'@p'+@vpkcol+ ')
    BEGIN
    UPDATE '+@tablename+' 
    SET '+@vtblcolumns+' 
    WHERE '+ @vPKcol + '='+'@p'+@vpkcol+ '
    END
    ELSE
    BEGIN
    INSERT INTO '+@tablename+ '('+@vcolumns1+') 
                        VALUES ('+@vColumns2+')

    SET ' +'@p'+@vPKcol+' = Scope_Identity()
    END

RETURN  ' +'@p'+@vPKcol+'

End
GO
'
Print @vstr 

And now comes the calling part, just use this procedure with DB manager class available on internet.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- UP_CSCallBuilder 'UP_Admin_InsertUpdate' , 'admin', 'Admin'
ALTER     PROCEDURE [dbo].[UP_CSCallBuilder]
(
@objName nvarchar(100), 
@passObject nvarchar(100),
@passClass nvarchar(100)
)
AS
/*
___________________________________________________________________
Name:  CS Call Builder
Author:  Usman Shabbir
___________________________________________________________________
*/


SET NOCOUNT ON

DECLARE @parameterCount int
DECLARE @errMsg varchar(100)
DECLARE @parameterAt varchar(1)
DECLARE @connName varchar(100)
--Change the following variable to the name of your connection instance
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 ' public static int Insert'+ @passClass +'('+ @passClass +' '+ @passObject +')'
 PRINT '   {'
  PRINT 'try'
  PRINT '   {'
  print 'using (DbManager db = DbManager.GetDbManager())'
  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) + '] = db.MakeInParam("' + @parameterAt + @col_name + '", SqlDbType.' + @col_redef + ','+ cast(@col_len as varchar) +','+ @passObject +'.'+ substring(@col_name,3, len(@col_name)  - 2) +');'

   --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 = '+ @passObject +'.'+ replace(@col_name,'p','') +' ;'
      -- 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 '   return db.RunProc("' + @objName + '", paramsToStore);'
  PRINT '   }'
  PRINT '   }'
  PRINT 'catch(Exception ex)'
  PRINT '   {'
  print '  new SqlLog().InsertSqlLog(0, "'+ @passClass +'.Insert'+ @passClass +'", ex);'
  PRINT '   }'
  PRINT '   return 0;'
  PRINT '   }'  
  CLOSE cur
  DEALLOCATE cur
 END

if(LEN(@errMsg)>0) PRINT @errMsg
DROP TABLE #t_obj
SET NOCOUNT ON

Happy Coding...