Sunday, December 23, 2012

SQL auto generate last column id without auto increment

If we want to get a table autogenerate column id, without auto increment column. we can do it easily.
we can also made it globally for all table. We can create a table name paramaterize store procedure that
will give us table last column ID without using auto increment column.

 CREATE PROCEDURE [dbo].[spSET_GetDB_TablePKID]  
      @tableName [varchar](100),  
      @locationId [int]  
 AS  
 BEGIN  
 declare @newID varchar(20)  
 declare @dbID varchar(20)  
 declare @generateID bigint  
 declare @ReturnValue varchar(100)  
  if exists(select * from DB_TablePKID where LocationID=@locationId and TableName=@tableName)  
   begin  
   --table name exists  
   set @ReturnValue= (select MaxID+1 from DB_TablePKID where LocationID=@locationId and TableName=@tableName)  
   end  
  else --table name is not exists.  
   begin  
   -- generate new first ID for the supplied table  
   select @dbID= DBid from DB where DBLocationID=@locationId  
   set @newID=@dbID+'00000000000001'  
   set @generateID=convert(bigint,@newID)  
   set @ReturnValue=@generateID  
   end   
   select @ReturnValue  
 end  

declar the store procedure in C#

 public long GetTablekeyID(string tableName, int locationId)  
     {  
       long id = 0;  
       // id = _db.spSET_GetDB_TablePKID(tableName, locationId);  
       System.Data.Objects.ObjectResult<string> sid = this.DataContext.spSET_GetDB_TablePKID(tableName, locationId);  
       foreach (string aa in sid)  
       {  
         id = long.Parse(aa.ToString());  
       }  
       return id;  
     }  

Now call from anywhere with your any table name. it will return last id of your table. here locationID is optional.

C# print file on LAN Printer

If you want to print any file on network printer then you can try with this code block // Change Default Printer System.Ma...