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...

Getting started with Metro Style applications.

Hi All,
          Last days i got a chance to attend a Microsoft training for windows 8 development so i decided to share my learning with the world, In this blog ill start from installation of software and finally lead you to make a sample application.

         It’s better to install Windows 8 Consumer preview in a virtual environment as down gradation is not yet officially announced by Microsoft (or some complex method is there). Restore disk before installation or some factory setting might work. You need to install Virtual Box or VM ware workstation 8.0 to run in virtual environment.  The setup of VM ware workstation can be downloaded from www.vmware.com.

      A detailed link of making the virtual machine for windows 8 can be found here.

     You can get windows 8 developer preview from Windows 8 Consumer Preview as well as Visual Studio 11 Beta Express for Windows 8. 


     Once windows 8 and VS 2011 is installed lets start the development process step by step. 



     Start Visual Studio and use the File - New Project command to create a new Visual C# project named “ContosoCookbook.” Be sure to select “Windows Metro Style” from the list of Visual C# templates, and to select “Grid Application” from the list of template types

 

Select Start Debugging from the Debug menu (or simply press F5) to launch the application in the debugger. The application will start and you’ll see the screen shown in Figure 2. This is the application’s home page or start page

After this you can click on items and categories and grid style application will handle all for you. after this lets move to the application manifest file and to do that double-click Package.appxmanifest to open the application manifest.

 

Here you can set the application icons, titles and the deceives that your application might require. 

You will find a folder name DataModel in the solution explorer, there you can have you own data source or you can modify the sample data source, which can fetch data from any JSON/XML services or data file. 

Open the SampleDataSource class from DataModel folder and in constructor you can fetch the JSON/XML file to populate it, below are two functions to set the source from a local file and a remote location. 

  

 public async void LoadRemoteDataAsync()  
     {  
       // Retrieve data from remote location  
       var client = new HttpClient();  
       client.MaxResponseContentBufferSize = 1024 * 1024; // Read up to 1 MB of data  
       var response = await client.GetAsync(new Uri("URL to fetch the JSON/XML data"));  
       var result = await response.Content.ReadAsStringAsync();  
       // Parse the JSON recipe data  
       var recipes = JsonArray.Parse(result.Substring(1, result.Length - 1));  
       // ADD to Datasource Group after parsing as sample data source did.   
     }  
     public async void LoadLocalDataAsync()  
     {  
       // Retrieve data from JSON.txt  
       var file = await Package.Current.InstalledLocation.GetFileAsync("Data\\JSON.txt");  
       var stream = await file.OpenReadAsync();  
       var input = stream.GetInputStreamAt(0);  
       var reader = new DataReader(input);  
       uint count = await reader.LoadAsync((uint)stream.Size);        
       var result = reader.ReadString(count);  
       // Parse the JSON recipe data  
       var recipes = JsonArray.Parse(result.Substring(1, result.Length - 1));  
       // ADD to Datasource Group after parsing as sample data source did.   
     }  

If you have created a new Data source then you need to modify the calls in following classes accordingly.

In GroupDetailPage.xaml.cs update the var group conversion with you own.

     protected override void OnNavigatedTo(NavigationEventArgs e)  
     {  
       var group = (YOURDataGroup)e.Parameter;  
       this.DefaultViewModel["Group"] = group;  
       this.DefaultViewModel["Items"] = group.Items;  
     }  

Also apply the same to GroupedItemsPage.xaml.cs and ItemDetailPage.xaml.cs, which would lead you to the development of you first sample application.

hope you had a great time working with windows 8 Metro style applications.

Happy coding.