Creating Linked Server through Query in SQL Server

Creating Linked Server through Query in SQL Server

-- DROPPING IF EXISTS
IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0
                                                                                      AND srv.name =@ServerName)
BEGIN
            EXEC master.dbo.sp_dropserver @server=@ServerName, @droplogins='droplogins'
END

-- CREATING LINKED SERVERS
EXEC master.dbo.sp_addlinkedserver @server = @ServerName, @srvproduct=N'SQL Server'

--The linked server remote logins
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@ServerName,@useself=N'False',@locallogin=NULL,@rmtuser=@UserID,@rmtpassword=@Pwd
--GO

EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'collation compatible',           
                                                                                                                    @optvalue=N'false'
 --GO

 EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'data access', 
                                                                                                                  @optvalue=N'true'
 --GO

 EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'dist', @optvalue=N'false'
 --GO

 EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'pub', @optvalue=N'false'
 --GO

 EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'rpc', @optvalue=N'true'
 --GO

 EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'rpc out', @optvalue=N'true'
 --GO

 EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'sub', @optvalue=N'false'
 --GO

 EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'connect timeout',       
                                                                                                @optvalue=N'0'
 --GO

 EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'collation name',
                                                                                                @optvalue=null
 --GO

 EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'lazy schema validation',
                                                                                                @optvalue=N'false'
--GO

EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'query timeout',
                                                                                               @optvalue=N'0'
--GO

EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'use remote collation', 
                                                                                               @optvalue=N'true'
--GO

EXEC master.dbo.sp_serveroption @server=@ServerName,
                                                      @optname=N'remote proc transaction promotion', @optvalue=N'true'
--GO

Closing Cursor by Checking if exists in SQL Server

Closing Cursor by Checking if exists in SQL Server

IF (SELECT CURSOR_STATUS('global','Cur_Name')) >=0
    BEGIN
        DEALLOCATE    Cur_UDD
    END

Ex :

BEGIN TRANSACTION
   
BEGIN TRY

DECLARE Cur_Name CURSOR FOR SELECT COL1,COL2 FROM TABLE

OPEN Cur_Name

FETCH NEXT FROM Cur_Name INTO @Col1,@Col2
WHILE @@FETCH_STATUS=0
BEGIN


    FETCH NEXT FROM Cur_Name INTO @Col1,@Col2

END

CLOSE        Cur_Name
DEALLOCATE    Cur_Name


COMMIT TRANSACTION
   
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
       
    IF (SELECT CURSOR_STATUS('global','Cur_UDD')) >=0
    BEGIN
        DEALLOCATE    Cur_UDD
    END
   
    DECLARE @Err_Msg NVARCHAR(MAX)
    DECLARE @Err_Sev INT
    SELECT  @Err_Msg=ERROR_MESSAGE(),@Err_Sev=ERROR_SEVERITY()   
    RAISERROR(@Err_Msg,16,1)
END CATCH

Common SP to Change/Alter User Defined Datatype's(UDDs) Size and Datatype even it has references of function,view,constraint and etc..

Common SP to Change/Alter User Defined Datatype's(UDDs) Size and Datatype even it has references of function,view,constraint and etc..

CREATE PROCEDURE  Change_UDD_SP
(
    @Find_UDD_Name        NVARCHAR(MAX),
    @Replace_UDD_Type    NVARCHAR(MAX)
)
AS
BEGIN
   
    SET NOCOUNT ON

    DECLARE @Replace_UDD_Name    NVARCHAR(MAX)
    DECLARE @Udd_Count            INT
    DECLARE @CreateUDDQuery        NVARCHAR(MAX)

    SELECT    @Udd_Count            = 1
    SELECT    @Replace_UDD_Name    = @Find_UDD_Name + CONVERT(NVARCHAR(10),@Udd_Count)

   
    SELECT @Find_UDD_Name=LTRIM(RTRIM(@Find_UDD_Name))
    SELECT @Replace_UDD_Type=LTRIM(RTRIM(@Replace_UDD_Type))
   
    -- TO VALIDATE DATA
    IF ISNULL(@Find_UDD_Name,'')=''
    BEGIN
        RAISERROR('ENTER VALUE FOR @Find_UDD_Name',16,1)
        RETURN
    END
   
    IF ISNULL(@Replace_UDD_Type,'')=''
    BEGIN
        RAISERROR('ENTER VALUE FOR @Replace_UDD_Type',16,1)
        RETURN
    END

    -- TO CHECK FIND UDD IS EXISTS
    IF NOT EXISTS(SELECT 'X' FROM sys.types where name=@Find_UDD_Name)
    BEGIN
        RAISERROR('NO SUCH UDD : %s',16,1,@Find_UDD_Name)
        RETURN
    END
   
   
    --IF NOT EXISTS(SELECT    'X'    FROM    SYS.OBJECTS O,
    --                                    SYS.ALL_COLUMNS C,
    --                                    SYS.TYPES T
    --                            WHERE    o.object_id        =    c.object_id 
    --                            AND        c.user_type_id    =    t.user_type_id 
    --                            AND        t.name            =    @Find_UDD_Name)
    --BEGIN
    --    RAISERROR('NO REFERENCES TO THIS UDD : %s',16,1,@Find_UDD_Name)
    --    RETURN
    --END
   

    -- FIND RENAMING UDDs
    WHILE EXISTS(SELECT 'X' FROM SYS.TYPES WHERE name=@Replace_UDD_Name)
    BEGIN
        SELECT    @Udd_Count            = @Udd_Count +1
        SELECT    @Replace_UDD_Name    = @Find_UDD_Name + CONVERT(NVARCHAR(10),@Udd_Count)   
    END
   
   
    BEGIN TRANSACTION
   
    BEGIN TRY

        -- RENAMING UDDs
        EXEC SP_RENAME @Find_UDD_Name,@Replace_UDD_Name
       
        -- CREATING NEW UDD
        SELECT @CreateUddQuery    = N'CREATE TYPE ' + @Find_UDD_Name + ' FROM ' + @Replace_UDD_Type
        EXEC SP_EXECUTESQL @CreateUDDQuery
        --PRINT @CreateUDDQuery


        DECLARE @Table_Name        NVARCHAR(MAX)
        DECLARE @Table_Type        NVARCHAR(MAX)
        DECLARE @Column_Name    NVARCHAR(MAX)
        DECLARE @Is_Nullable    INT
        DECLARE @Is_Identity    INT
        DECLARE @Type_Name        NVARCHAR(MAX)
        DECLARE @Alter_Qry        NVARCHAR(MAX)
        DECLARE @Condition_Qry    NVARCHAR(MAX)
        DECLARE @Slno            INT
        DECLARE @Old_Table_Name    NVARCHAR(MAX)
        DECLARE @Constrn_Name    NVARCHAR(MAX)
        DECLARE @Constrn_Cols    NVARCHAR(MAX)
        DECLARE @Constrn_Qry    NVARCHAR(MAX)
        SELECT    @Old_Table_Name    =    ''
        SELECT    @Constrn_Name    =    ''
        SELECT    @Constrn_Cols    =    ''

        DECLARE Cur_UDD CURSOR FOR    SELECT    o.name,o.type ,c.name,c.is_nullable,c.is_identity,t.name,CASE WHEN o.type='U' THEN 1 ELSE 2 END AS Slno 
                                    FROM    SYS.OBJECTS O,
                                            SYS.ALL_COLUMNS C,
                                            SYS.TYPES T
                                    WHERE    o.object_id        =    c.object_id 
                                    AND        c.user_type_id    =    t.user_type_id 
                                    AND        t.name            =    @Replace_UDD_Name
                                    ORDER BY Slno,o.type,o.name 
                                   

        OPEN Cur_UDD

        FETCH NEXT FROM Cur_UDD INTO @Table_Name,@Table_Type,@Column_Name,@Is_Nullable,@Is_Identity,@Type_Name,@Slno
        WHILE @@FETCH_STATUS=0
        BEGIN
           
            IF @Table_Name<>@Old_Table_Name AND UPPER(@Table_Type)='U'
            BEGIN
                SELECT    @Constrn_Name    =    ''
                SELECT    @Constrn_Cols    =    ''
                IF EXISTS(SELECT 'X'    FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE k,INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
                                        WHERE    k.CONSTRAINT_CATALOG    =    c.CONSTRAINT_CATALOG
                                        AND        k.CONSTRAINT_NAME        =    c.CONSTRAINT_NAME
                                        AND        k.TABLE_CATALOG            =    c.TABLE_CATALOG
                                        AND        k.TABLE_NAME            =    c.TABLE_NAME
                                        AND        c.CONSTRAINT_TYPE        =    'PRIMARY KEY'      
                                        AND        k.TABLE_NAME            =    @Table_Name
                                        AND        k.COLUMN_NAME            =    @Column_Name)
                BEGIN
                    SELECT    @Constrn_Name            =    k.CONSTRAINT_NAME
                    FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE k,
                            INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
                    WHERE    k.CONSTRAINT_CATALOG    =    c.CONSTRAINT_CATALOG
                    AND        k.CONSTRAINT_NAME        =    c.CONSTRAINT_NAME
                    AND        k.TABLE_CATALOG            =    c.TABLE_CATALOG
                    AND        k.TABLE_NAME            =    c.TABLE_NAME
                    AND        c.CONSTRAINT_TYPE        =    'PRIMARY KEY'      
                    AND        k.TABLE_NAME            =    @Table_Name
                    AND        k.COLUMN_NAME            =    @Column_Name
                   
                    SELECT    @Constrn_Cols    =    STUFF(CONVERT(NVARCHAR(MAX),(    SELECT    ','+COLUMN_NAME  
                                                                                FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                                                                                WHERE    CONSTRAINT_NAME    =    @Constrn_Name
                                                                                FOR        XML PATH(''))),1,1,'')
                    SELECT    @Constrn_Qry=N'ALTER TABLE ' +      @Table_Name + ' DROP CONSTRAINT ' + @Constrn_Name                                           
                    --PRINT @Constrn_Qry
                    EXEC    SP_EXECUTESQL @Constrn_Qry
                    SELECT    @Old_Table_Name    =    @Table_Name
                END
            END
           
            SELECT @Alter_Qry        =''
            SELECT @Condition_Qry    =''
           
            IF UPPER(@Table_Type)='U'
            BEGIN
                IF @Is_Nullable=0
                BEGIN
                    SELECT @Condition_Qry = ' NOT NULL '
                END
                SELECT @Alter_Qry=N'ALTER TABLE ' +  @Table_Name + ' ALTER COLUMN ' +  @Column_Name + ' ' + @Find_UDD_Name + ' ' + @Condition_Qry
               
                --PRINT @Alter_Qry
                EXEC SP_EXECUTESQL @Alter_Qry
               
            END
            ELSE
            BEGIN
                -- TO REFRESH TABLE FUNCTION AND VIEWS
                EXEC sp_refreshsqlmodule @Table_Name
               
            END
           
           
            FETCH NEXT FROM Cur_UDD INTO @Table_Name,@Table_Type,@Column_Name,@Is_Nullable,@Is_Identity,@Type_Name,@Slno
            -- CHECKING NEXT RECORD
            IF (@Table_Name<>@Old_Table_Name OR @@FETCH_STATUS<>0)  AND @Constrn_Name<>''
            BEGIN
                SELECT    @Constrn_Qry=N'ALTER TABLE ' + @Old_Table_Name + ' ADD CONSTRAINT ' + @Constrn_Name + ' PRIMARY KEY('+@Constrn_Cols+')'
                --PRINT @Constrn_Qry
                EXEC SP_EXECUTESQL @Constrn_Qry
                SELECT    @Constrn_Name    =    ''
                SELECT    @Constrn_Cols    =    ''
            END
        END

        CLOSE        Cur_UDD
        DEALLOCATE    Cur_UDD
       
        -- CHECKING OTHER REF FOR THIS UDD
        DECLARE @Ref_Name    NVARCHAR(MAX)
        DECLARE Cur_Ref CURSOR FOR SELECT DISTINCT SPECIFIC_NAME FROM INFORMATION_SCHEMA.PARAMETERS WHERE USER_DEFINED_TYPE_NAME    =    @Replace_UDD_Name
        OPEN    Cur_Ref
        FETCH NEXT FROM Cur_Ref INTO @Ref_Name
        WHILE @@FETCH_STATUS=0
        BEGIN
            EXEC sp_refreshsqlmodule @Ref_Name
            FETCH NEXT FROM Cur_Ref INTO @Ref_Name
        END
        CLOSE        Cur_Ref
        DEALLOCATE    Cur_Ref
       
       
        -- DROPPING DUMMY UDD
        SELECT @CreateUddQuery    = N'DROP TYPE ' + @Replace_UDD_Name
        EXEC SP_EXECUTESQL @CreateUDDQuery
        COMMIT TRANSACTION
   
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
       
        IF (SELECT CURSOR_STATUS('global','Cur_UDD')) >=0
        BEGIN
            DEALLOCATE    Cur_UDD
        END
       
        IF(SELECT CURSOR_STATUS('global','Cur_Ref'))>=0
        BEGIN
            DEALLOCATE Cur_Ref
        END
       
        DECLARE @Err_Msg NVARCHAR(MAX)
        DECLARE @Err_Sev INT
        SELECT  @Err_Msg=ERROR_MESSAGE(),@Err_Sev=ERROR_SEVERITY()   
        RAISERROR(@Err_Msg,16,1)
    END CATCH
   
    SET NOCOUNT OFF

END

Comparing 2 databases to find Objects existing in Database1 and not existing in Database2

Comparing 2 databases to find Objects existing in Database1 and not existing in Database2.

To Get UDD Differences :

-- NEW UDDs
select t.name,t.max_length,t.precision,t.scale,t.is_nullable,t1.name from Database1.sys.types t,Database1.sys.types t1 where t.is_user_defined=1 and t.name not in (select ti.name from Database2.sys.types ti where ti.is_user_defined=1)
AND t.system_type_id=t1.user_type_id

-- CHANGED UDDs
select t.name,tsys.name,t.max_length,t.precision,t.scale,t.is_nullable,t.default_object_id
,ttsys.name,tt.max_length,tt.precision,tt.scale,tt.is_nullable,tt.default_object_id
from Database1.sys.types t,Database2.sys.types tt,Database1.sys.types tsys,Database2.sys.types ttsys
where t.is_user_defined=1 and t.name=tt.name and t.system_type_id= tsys.user_type_id and tt.system_type_id=ttsys.user_type_id   
and t.name+'_'+CONVERT(NVARCHAR,t.system_type_id)+'_'+CONVERT(NVARCHAR,t.max_length)+'_'+CONVERT(NVARCHAR,t.precision)+'_'+CONVERT(NVARCHAR,t.scale)+'_'+CONVERT(NVARCHAR,t.is_nullable)+'_'+CONVERT(NVARCHAR,t.default_object_id)
not in (select ti.name+'_'+CONVERT(NVARCHAR,ti.system_type_id)+'_'+CONVERT(NVARCHAR,ti.max_length)+'_'+CONVERT(NVARCHAR,ti.precision)+'_'+CONVERT(NVARCHAR,ti.scale)+'_'+CONVERT(NVARCHAR,ti.is_nullable)+'_'+CONVERT(NVARCHAR,ti.default_object_id) from Database2.sys.types ti where ti.is_user_defined=1)
and t.name  not in (select n.name from Database1.sys.types n where n.is_user_defined=1 and n.name not in (select ni.name from Database2.sys.types ni where ni.is_user_defined=1) )

To Get Table Differences :

-- NEW TABLES
SELECT o.name FROM Database1.sys.objects o
WHERE o.type='U' and o.name not in (SELECT i.name FROM Database2.sys.objects i WHERE i.type='U')  order by o.name 

-- CHANGED COLUMNS
SELECT o.object_id,o.name,c.column_id,c.name,o.type,c.is_nullable,c.is_identity,t.name  
FROM Database1.sys.objects o,Database1.sys.columns c,Database1.sys.types t WHERE o.type ='U' and o.object_id=c.object_id and c.user_type_id=t.user_type_id 
AND o.name+'_'+  c.name NOT IN(SELECT uo.name+'_'+  uc.name FROM Database2.sys.objects uo,Database2.sys.columns uc WHERE uo.type ='U' and uo.object_id=uc.object_id) 
AND o.name not in (SELECT distinct n.name FROM Database1.sys.objects n
WHERE n.type='U' and n.name not in (SELECT ni.name FROM Database2.sys.objects ni WHERE ni.type='U'))
ORDER BY o.name,c.column_id  

To Get Constraint Differences :

-- DEFAULT CONSTRAINTS
select ob.name,o.name,c.name from Database1.sys.objects o,Database1.sys.columns c,Database1.sys.objects ob where o.type='D' and o.object_id=c.default_object_id and o.parent_object_id=ob.object_id  
and  ob.name+'_'+o.name+'_'+c.name not in (select obi.name+'_'+oi.name+'_'+ci.name from Database2.sys.objects oi,Database2.sys.columns ci,Database2.sys.objects obi where oi.type='D' and oi.object_id=ci.default_object_id and oi.parent_object_id=obi.object_id)  


-- PRIMARY KEY
select * from Database1.sys.objects where type='PK' and name not in(select name from Database2.sys.objects where type='PK')

Backup all databases in a Server except system DBs in Sql Server

Backup all databases in a Server except system DBs in Sql Server

Please compile this SP on Sql Server and whenever you want to take the backup of all existing databases, plz Execute this SP once.  It will automatically take backup and it will place in specified location.  

CREATE PROCEDURE [dbo].[DataBase_BackUp_SP](@OutputPath NVARCHAR(4000)='\\Server1\Shared Docs\Others\')   
AS   
BEGIN   
 SET NOCOUNT ON   
    
 DECLARE @BackFileName NVARCHAR(4000)    
 DECLARE @BackPath  NVARCHAR(4000)    
 DECLARE @BackFullPath NVARCHAR(4000)    
 DECLARE @DBName   NVARCHAR(4000)    
 DECLARE @ServerName  NVARCHAR(4000)    
   
   
 -- GETTING SERVER NAME   
 SELECT @ServerName = Name    
 FROM SYS.SERVERS    
 WHERE Server_id = 0    
   
    
 DECLARE DB_Cur CURSOR FOR SELECT NAME FROM SYS.DATABASES WHERE Database_Id>4   
 OPEN DB_Cur   
 FETCH NEXT FROM DB_Cur INTO @DBName   
 WHILE @@FETCH_STATUS=0   
 BEGIN   
     
  SELECT @BackPath=@OutputPath   
  SELECT @BackFileName='Backup_'+ @DBName +'_'+  @ServerName +'_' + CONVERT(NVARCHAR(10),GetDate(),105) + '.bak'   
  SELECT @BackFullPath =@BackPath +  @BackFileName   
     
  BACKUP DATABASE @DBName   
  TO DISK=@BackFullPath   
  WITH INIT   
     
  FETCH NEXT FROM DB_Cur INTO @DBName   
 END   
    
 CLOSE  DB_Cur   
 DEALLOCATE DB_Cur   
    
 SET NOCOUNT OFF   
END

Finding Missing Numbers in SQL Server

Finding Missing/Skipped Numbers in table  in SQL Server  

Declare @tbl table(slno int)

insert into @tbl(slno) values(1)
insert into @tbl(slno) values(3)
insert into @tbl(slno) values(4)
insert into @tbl(slno) values(5)
insert into @tbl(slno) values(11)
insert into @tbl(slno) values(13)
insert into @tbl(slno) values(18)
insert into @tbl(slno) values(8008)

select * from @tbl

select c.slno from
(select a.slno,(select b.slno from (select ROW_NUMBER() OVER(ORDER BY Slno) as Actslno, * from @tbl) b where b.ActSlNo=a.ActSlNo+1) as nextSlno from (select ROW_NUMBER() OVER(ORDER BY Slno) as Actslno, * from @tbl) a) c
where ISNULL(c.nextSlno,0)-ISNULL(c.slno,0)>1

Avoid Flickering of Datagridview in .NET

Avoid Flickering of Datagridview in .NET

Private Sub SetDoubleBuffering(ByVal pDataGridview As DataGridView)

        GetType(Control).InvokeMember("DoubleBuffered", Reflection.BindingFlags.SetProperty Or 

        Reflection.BindingFlags.Instance Or Reflection.BindingFlags.NonPublic, Nothing, 

        pDataGridview, New Object() {True})

End Sub

Writing Control Validation in common place without writing on every control's event

Writing Control Validation in common place without writing on every control's event

here i'm going to allowing only numeric validation, i'm going to write this code in ControlValidation Class. its my own class.

Public Sub Allow_Control_Numeric(ByRef Cntrl As Control, ByVal pMsgTitle As String, Optional ByVal pNoOfDecimal As Integer = 0, Optional ByVal AllowNegative As Boolean = False, Optional ByVal AllowBlank As Boolean = True, Optional ByVal isCorrectFormatRequired As Boolean = True, Optional ByVal AllowZero As Boolean = True, Optional ByVal pIsQtyColumn As Boolean = False, Optional ByVal pTxtValidationCntrl As Control = Nothing, Optional ByVal pIsReadOnlyCntrl As Boolean = False, Optional ByVal pTxtValidationCntrlFlag As ValidationColumnFlag = ValidationColumnFlag.PartNo, Optional ByVal pIsPriceColumn As Boolean = False)
        Dim _controlValidation = New ControlValidation
        _controlValidation._Txt_Cntrl = Cntrl
        _controlValidation._MsgTitle = pMsgTitle
        _controlValidation._NoOfDecimal = pNoOfDecimal
        _controlValidation._AllowNegative = AllowNegative
        _controlValidation._AllowBlank = AllowBlank
        _controlValidation._isCorrectFormatRequired = isCorrectFormatRequired
        _controlValidation._AllowZero = AllowZero
        _controlValidation._IsQtyColumn = pIsQtyColumn
        _controlValidation._Txt_Validation_Cntrl = pTxtValidationCntrl
        _controlValidation._validationFlag = pTxtValidationCntrlFlag
        _controlValidation._IsPriceColumn = pIsPriceColumn

        If _gQtyValidationClasses.Contains(_stackTrace.GetFrames(1).GetMethod.ReflectedType.Name) Or _gQtyValidationClasses.Contains("*") Then
            _controlValidation._QtyAllowClass = True
        Else
            _controlValidation._QtyAllowClass = False
        End If

        '' To Set max Length for all Qty Controls
        If pIsQtyColumn Then
            If TypeOf Cntrl Is TextBox Then
                If _controlValidation._QtyAllowClass = True Then
                    CType(Cntrl, TextBox).MaxLength = _gQtyTotalSize
                Else
                    CType(Cntrl, TextBox).MaxLength = _gQtyTotalSizeOthers
                End If
            End If
        ElseIf pIsPriceColumn Then
            If TypeOf Cntrl Is TextBox Then
                CType(Cntrl, TextBox).MaxLength = _gPriceNumberSize + _controlValidation._NoOfDecimal + 1
            End If
        End If

        RemoveHandler Cntrl.KeyUp, AddressOf _controlValidation.Control_KeyUp
        RemoveHandler Cntrl.Validated, AddressOf _controlValidation.Control_Validated
        AddHandler Cntrl.KeyUp, AddressOf _controlValidation.Control_KeyUp
        AddHandler Cntrl.Validated, AddressOf _controlValidation.Control_Validated
    End Sub

   

    Private Sub Control_KeyUp()
        If _IsQtyColumn Then
            If _QtyAllowClass Then
                _NoOfDecimal = Get_Part_UOM_Result(_Txt_Validation_Cntrl.Text, _validationFlag)
            Else
                _NoOfDecimal = _gQtyDecimalSizeOthers
            End If
        End If
        _Txt_Cntrl.Text = _validation.Check_Numeric(_Txt_Cntrl.Text, _MsgTitle, _NoOfDecimal, _AllowNegative, _AllowBlank, True, False, _AllowZero, _IsQtyColumn, _QtyAllowClass, _IsPriceColumn)
    End Sub

 Private Sub Control_Validated()
        If _IsQtyColumn Then
            If _QtyAllowClass Then
                _NoOfDecimal = Get_Part_UOM_Result(_Txt_Validation_Cntrl.Text, _validationFlag)
            Else
                _NoOfDecimal = _gQtyDecimalSizeOthers
            End If
        End If
        _Txt_Cntrl.Text = _validation.Check_Numeric(_Txt_Cntrl.Text, _MsgTitle, _NoOfDecimal, _AllowNegative, _AllowBlank, True, _isCorrectFormatRequired, _AllowZero, _IsQtyColumn, _QtyAllowClass, _IsPriceColumn)
    End Sub

In validation class,

Public Function Check_Numeric(ByVal pValue As String, ByVal pMsgTitle As String, Optional ByVal pNoOfDecimal As Integer = 0, Optional ByVal AllowNegative As Boolean = False, Optional ByVal AllowBlank As Boolean = True, Optional ByVal pErrMsgRequired As Boolean = True, Optional ByVal isCorrectFormatRequired As Boolean = True, Optional ByVal AllowZero As Boolean = True, Optional ByVal pIsQty As Boolean = False, Optional ByVal pQtyAllowClass As Boolean = False, Optional ByVal pIsPrice As Boolean = False) As String  ',  ByVal pMsgBoxTitle As String, Optional ByVal pFrac As Boolean = False) As Char
        ' Checking Fractions value it must be >= 0
        If Not (pNoOfDecimal >= 0 And pNoOfDecimal <= 99) Then
            If pErrMsgRequired Then
                MessageBox.Show(My.Resources.E709, pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
            Return ""
        End If


        '' Checking Numerics
        If IsNumeric(pValue) Or (pValue = "-" And AllowNegative = True) Or (pValue = "." And pNoOfDecimal > 0) Then

            If pIsQty Then   ' Qty Validation
                Dim _QtyDecValue As String = ""
                Dim _QtyNoValue As String = ""
                'Dim _RetValue As String = ""

                If Len(pValue) > IIf(pQtyAllowClass = True, _gQtyTotalSize, _gQtyTotalSizeOthers) Then
                    MessageBox.Show(My.Resources.E1606 & " " & IIf(pQtyAllowClass = True, _gQtyTotalSize, _gQtyTotalSizeOthers), pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Return ""
                End If

                _QtyNoValue = Mid(pValue, 1, IIf(InStr(pValue, ".") > 0, InStr(pValue, ".") - 1, Len(pValue)))
                _QtyDecValue = IIf(InStr(pValue, ".") > 0, Mid(pValue, InStr(pValue, ".") + 1), "")

                If Len(_QtyNoValue) > IIf(pQtyAllowClass = True, _gQtyNumberSize, _gQtyNumberSizeOthers) Or Len(_QtyDecValue) > pNoOfDecimal Then
                    If Not pQtyAllowClass Then
                        MessageBox.Show(My.Resources.E1607 & " (" & IIf(pQtyAllowClass = True, _gQtyNumberSize, _gQtyNumberSizeOthers) & " , " & pNoOfDecimal & " ) ", pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Else
                        If pNoOfDecimal <= 0 Then
                            MessageBox.Show(My.Resources.E1607 & " (" & IIf(pQtyAllowClass = True, _gQtyNumberSize, _gQtyNumberSizeOthers) & " , " & pNoOfDecimal & " ) " & My.Resources.E1608, pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                        Else
                            MessageBox.Show(My.Resources.E1607 & " (" & IIf(pQtyAllowClass = True, _gQtyNumberSize, _gQtyNumberSizeOthers) & " , " & pNoOfDecimal & " ) " & My.Resources.E1609, pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                        End If
                    End If
                    '_RetValue = Mid(_QtyNoValue, 1, _gQtyNumberSize)
                    'If Len(_QtyDecValue) > pNoOfDecimal Then
                    '    If pNoOfDecimal > 0 Then
                    '        _RetValue = _RetValue & "." & Mid(_QtyDecValue, 1, pNoOfDecimal)
                    '    End If
                    'End If
                    Return ""
                End If
            End If

            If pIsPrice Then
                'Dim _PriceDecValue As String = ""
                Dim _PriceNoValue As String = ""

                _PriceNoValue = Mid(pValue, 1, IIf(InStr(pValue, ".") > 0, InStr(pValue, ".") - 1, Len(pValue)))
                '_PriceDecValue = IIf(InStr(pValue, ".") > 0, Mid(pValue, InStr(pValue, ".") + 1), "")
                If Len(_PriceNoValue) > _gPriceNumberSize Then
                    MessageBox.Show(My.Resources.E1610 & " (" & _gPriceNumberSize & " , " & pNoOfDecimal & " ) ", pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Return ""
                End If
            End If


            If AllowNegative = False Then  ' checking if allownegative is false it should be positive values
                If Val(pValue) < 0 Then 'Or Val(FormatNumber(pValue, pNoOfDecimal, TriState.True, , TriState.False)) < 0 Then
                    If pErrMsgRequired Then
                        MessageBox.Show(My.Resources.E710, pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                    End If
                    Return "" ''Math.Abs(Val(pValue)) ''Commented by Ravi on 12-09-2011. just clear,no need to correct the format. 
                End If
                If IsNumeric(pValue) Then
                    If Val(FormatNumber(pValue, pNoOfDecimal, TriState.True, , TriState.False)) < 0 Then
                        If pErrMsgRequired Then
                            MessageBox.Show(My.Resources.E710, pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                        End If
                        Return ""
                    End If
                End If
            End If




            ' To get first . (dot) position
            Dim _DotExists As Boolean = False
            Dim _DotPosition = InStr(pValue, ".")

            If _DotPosition = 0 Then
                _DotPosition = Len(pValue)
            Else
                _DotExists = True
            End If

            ' getting Decimal values after .
            Dim _DecimalValue = Mid(pValue, _DotPosition + 1, Len(pValue))
            'Dim _IntValue As Integer

            'If _DotExists Then
            '    _IntValue = Mid(pValue, 1, _DotPosition - 1)
            'Else
            '    _IntValue = Mid(pValue, 1, _DotPosition)
            'End If

            If Not AllowZero Then
                If pValue <> "-" And pValue <> "." Then
                    If Val(pValue) = 0 Then
                        MessageBox.Show(My.Resources.E1611, pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                        Return ""
                    End If
                End If
            End If

            If (Len(_DecimalValue) > pNoOfDecimal) Or (pNoOfDecimal = 0 And _DotExists = True) Then
                ' careful focus when changing this conditions
                If pErrMsgRequired Then
                    If pNoOfDecimal = 0 Then
                        MessageBox.Show(My.Resources.E711, pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Else
                        MessageBox.Show(My.Resources.E712 & pNoOfDecimal, pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                    End If
                End If
                If pValue = "-" Or pValue = "." Then
                    Return pValue
                Else
                    If isCorrectFormatRequired Then
                        Return "" 'FormatNumber(pValue, pNoOfDecimal, TriState.True, , TriState.False)  '' No need to round off
                    Else
                        Return ""
                    End If
                End If

            End If


            '' when no error , it comes here..
            '' Convert valid numeric value to correct format
            If isCorrectFormatRequired Then
                If pValue = "." Or pValue = "-" Then
                    Return ""
                Else
                    Return FormatNumber(pValue, pNoOfDecimal, TriState.True, , TriState.False)
                End If
            Else
                Return pValue
            End If
        Else
            If Trim(pValue) = "" And AllowBlank = True Then
                '' to skip when numeric values can allow blank values
            Else
                If pErrMsgRequired Then
                    MessageBox.Show(My.Resources.E713, pMsgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error)
                End If
                Return ""
            End If
        End If
        Return pValue

    End Function

Callling this function :

_controlValidation.Allow_Control_Numeric(_Value, MsgTitle, _2, False, True, False)

Writing into File Using StreamWriter in .NET

Writing into File Using StreamWriter in .NET

Dim Sw = New System.IO.StreamWriter(_fname)

For each _Rowdata in WholeData
    Dim _Value=_Rowdata

    Sw.WriteLine(_header)
    Sw.Flush()
Next

If Not IsNothing(Sw) Then Sw.Dispose()

Reading File Using StreamReader in .NET

Reading File Using StreamReader in .NET

Dim Sr As New System.IO.StreamReader(FileName)

While Not Sr.EndOfStream

         Dim LineValue = Sr.ReadLine

End While

Sr.Close()

Reading Excel Data in .NET

Reading Excel Data in .NET

Try
xla = CreateObject("Excel.Application")
xlw = GetObject(FileName)
xls = xlw.sheets(SheetNo)

For i = StartingRow To xls.Rows.Count
    For j = StartingCol To MaxCol
        If Trim(xls.Cells(i, StartingCol).Text) = "" Then
                    GetExcelToGrid = True
                        GoTo EndPart
                End If
       
        Dim _Value = Trim(xls.Cells(i, j).Text)
    Next j
Next i

EndPart:
 DGV.AllowUserToAddRows = False
 xlw.close()
 xla = Nothing
 xlw = Nothing
 xls = Nothing
 If Not IsNothing(oldCI) Then System.Threading.Thread.CurrentThread.CurrentCulture = oldCI
Catch ex As Exception
     GoTo EndPart
End Try

Using Hashtable in .NET

Using Hashtable in .NET



Dim _Col As New Hashtable

Adding values

_Col.Add(Key,Value)
_Col.Add(Key,Value)
_Col.Add(Key,Value)



Getting Values from hash table

Dim Value= _Col.Item(Key)

Retaining Last Column Resized in Datagridview

Retaining Last Grid Column Resized in Datagridview 

Initially insert formname and its gridname in MyProject->Settings.Settings file.

Setting file :

1. Open Settings.Setting file
2. Insert name like gridName_formname
3. choose System.COllection.Specialized.StringCollection
4. choose scope. if u want to user specific then choose User else Application.
5. Paste this code as dummy in Value Column
    <?xml version="1.0" encoding="utf-16"?>
    <ArrayOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <string>100~0</string>
    </ArrayOfString>



Writing Last Resized Grid Column in Form UnLoad :

In form Unload  Call this function as 

Set_User_Settings(me)

function :

Public Sub Set_User_Settings(ByVal Objfrm As Object)
        Dim ExitFlag As Boolean = False
        For Each Cnt As Object In Objfrm.Controls
            Set_GridControls(Objfrm, Cnt, ExitFlag)
        Next
End Sub


Private Sub Set_GridControls(ByVal Objfrm As Object, ByVal Cntrl As Object, ByRef canExit As Boolean)
        If canExit = True Then Exit Sub
        If Cntrl.HasChildren = True Then
            If TypeOf Cntrl Is DataGridView Then
                Dim _Key As String = Cntrl.name & "_" & Objfrm.name
                With Cntrl
                    Try
                        My.Settings.Item(_Key) = New System.Collections.Specialized.StringCollection()
                    Catch ex As Exception
                        canExit = True
                        Exit Sub
                    End Try
                    For i = 0 To Cntrl.Columns.Count - 1
                        Dim setting = .Columns(i).Width.ToString + "~" + .Columns(i).DisplayIndex.ToString
                        My.Settings.Item(_Key).Add(setting)
                    Next
                    My.Settings.Save()
                End With
            Else
                For Each chldcntrl In Cntrl.Controls
                    Set_GridControls(Objfrm, chldcntrl, canExit)
                Next
            End If
        End If
 End Sub


On Form Load, Assign last resized column :
call as Get_User_Settings(Me)

and its function is

Public Sub Get_User_Settings(ByVal Objfrm As Object)
        Dim ExitFlag As Boolean = False
        For Each Cnt As Object In Objfrm.Controls
            Get_GridControls(Objfrm, Cnt, ExitFlag)
        Next
        If ExitFlag = True Then Exit Sub
End Sub


Private Sub Get_GridControls(ByVal Objfrm As Object, ByVal Cntrl As Object, ByRef canExit As Boolean)
        If canExit = True Then Exit Sub
        If Cntrl.HasChildren = True Then
            If TypeOf Cntrl Is DataGridView Then
                Dim _Key As String = Cntrl.name & "_" & Objfrm.name
                With Cntrl
                    Try
                        If My.Settings.Item(_Key).Count > 0 Then
                            For i = 0 To My.Settings.Item(_Key).Count - 1
                                Dim setting = My.Settings.Item(_Key).Item(i).ToString
                                Dim settings() = setting.Split("~")
                                .Columns(i).Width = Int(settings(0))
                                .Columns(i).DisplayIndex = Int(settings(1))
                            Next
                        End If
                    Catch ex As Exception
                        canExit = True
                        Exit Sub
                    End Try
                End With
            Else
                For Each chldcntrl In Cntrl.Controls
                    Get_GridControls(Objfrm, chldcntrl, canExit)
                Next
            End If
        End If
    End Sub

 

Using SqlBulkCopy from datatable in .NET

Using SqlBulkCopy from datatable in .NET

Using copy As New SqlBulkCopy(Conn.Connection, SqlBulkCopyOptions.Default, Conn.Transaction)
                copy.ColumnMappings.Add("Column2", "PMO_PartNo")
                copy.ColumnMappings.Add("Column4", "PMO_Part_Name")

                copy.DestinationTableName = "PMS_MIG_Part_Master_Upload_Original"
                copy.WriteToServer(dt1)
End Using

Reading Excel Data by OLEDB Connection in .NET

Reading Excel Data by OLEDB Connection in .NET

Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim DtSet As System.Data.DataSet

        Try
            pSheetNo = pSheetNo - 1
            If UCase(fileextension) = ".XLS" Then

                ''''''' Fetch Data from Excel

                Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

                MyConnection = New 

               System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; ;data 

                source='" & PrmPathExcelFile & " '; Extended Properties=""Excel 8.0;IMEX=1;""")
                MyConnection.Open()

                ' Select the data from Sheet1 of the workbook.
                Dim myTableName = MyConnection.GetSchema("Tables").Rows(pSheetNo)

                ("TABLE_NAME")

                MyCommand = New 

                System.Data.OleDb.OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", 

                 myTableName), MyConnection)
                MyCommand.TableMappings.Add("Table", "FO")
                DtSet = New System.Data.DataSet
                MyCommand.Fill(DtSet)
                '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                MyConnection.Close()
                Return DtSet
            Else
                ''''''' Fetch Data from Excel

                Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

                MyConnection = New 

                System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0; ;data 

                source='" & PrmPathExcelFile & " '; Extended Properties=""Excel 12.0;IMEX=1;""")
                MyConnection.Open()

                ' Select the data from Sheet1 of the workbook.
                Dim myTableName = MyConnection.GetSchema("Tables").Rows(pSheetNo)

                ("TABLE_NAME")

                MyCommand = New 

                System.Data.OleDb.OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", 

                myTableName), MyConnection)
                MyCommand.TableMappings.Add("Table", "FO")
                DtSet = New System.Data.DataSet
                MyCommand.Fill(DtSet)
                '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                MyConnection.Close()
                Return DtSet
            End If
        Catch ex As Exception
            MyConnection.Close()
            System.Windows.Forms.MessageBox.Show(ex.Message, pmsgTitle, 

            System.Windows.Forms.MessageBoxButtons.OK, 

            System.Windows.Forms.MessageBoxIcon.Error)
        End Try

Opening Excel Application ,Modify,Save and Close in .NET

Opening Excel Application ,Modify,Save and Close in .NET

Public Function Change_Excel_Cell_Formatting(ByVal pFilePath As String, ByVal pSheetNo As Integer, ByVal pRowIndex As Integer, ByVal pMsgTitle As String) As Boolean
        Dim xla As Object
        Dim xlw As Object = Nothing
        Dim xls As Object
        Try
            xla = CreateObject("Excel.Application")
            xlw = xla.WorkBooks.Open(pFilePath)  ' GetObject(pFilePath)
            xls = xlw.sheets(pSheetNo)
            'xls.Rows(pRowIndex & ":" & pRowIndex + 1).Select()   '' Prob with this line that got error  

            ''as Too many fields, so limiting only 255 cols
            xls.Range(xls.cells(pRowIndex, 1), xls.Cells(pRowIndex + 1, 255)).Select()
            xla.Selection.NumberFormat = "@"

            xls.Range(xls.Columns(256), xls.Columns(xls.columns.count)).Select()
            xla.Selection.Delete()

            xla.DisplayAlerts = False
            xlw.save()
            Return True
        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show(ex.Message, pMsgTitle, 

            Windows.Forms.MessageBoxButtons.OK, Windows.Forms.MessageBoxIcon.Error)
            Return False
        Finally
            If Not IsNothing(xla) Then xla.DisplayAlerts = False
            If Not IsNothing(xlw) Then xlw.close()
            xls = Nothing
            xlw = Nothing
            If Not IsNothing(xla) Then xla.Quit()
            If Not IsNothing(xla) Then 

            System.Runtime.InteropServices.Marshal.ReleaseComObject(xla)
            xla = Nothing
            GC.Collect()
        End Try
    End Function

Getting Installed Version of Microsoft Office in .NET

Getting Installed Version of Microsoft Office in .NET

Private Function iCheck_Excel_Version_Installed(ByVal pMsgTitle As String) As Integer

        'The subkey's string value we check is like
        'Excel.Application.<version>, i e Excel.Application.10

        'The subkey we are interested of is located under the
        'HKEY_CLASSES_ROOT class.
        Const stXL_SUBKEY As String = "\Excel.Application\CurVer"

        Dim rkVersionKey As RegistryKey = Nothing
        Dim stVersion As String = String.Empty
        Dim stXLVersion As String = String.Empty

        'A very simple regular expression where:
        '[8-9] means look for the numbers 8 and 9
        'and start in the end of the expression.
        'Dim stRegExpr As String = "[8-9]$"

        'If we need to make sure that for instance Excel 2003 (11) or
        'later is installed then the above expression can be modified
        'to:
        'Dim stRegExpr As String = "[8-9]$|[1]0$"
        Dim stRegExpr As String = "[8-9]$|[1][0-1]$"

        Dim iVersion As Integer = Nothing

        Try
            'Here we try to open the subkey.
            rkVersionKey = Registry.ClassesRoot.OpenSubKey(name:=stXL_SUBKEY, _
                                                           writable:=False)

            'If it does not exist it means that Excel is not installed at all.
            If rkVersionKey Is Nothing Then
                iVersion = 0
                Return iVersion
            End If

            'OK, Excel is installed let's find out which version is available.
            stXLVersion = CStr(rkVersionKey.GetValue(name:=stVersion))

            'Here we match the retrieved value with our created regular
            'expression.
            If Regex.IsMatch(input:=stXLVersion, pattern:=stRegExpr) Then
                'Either Excel 97 or Excel 2000 is installed.
                iVersion = 1
                Return iVersion
            Else
                'Excel 2002 or later is available.
                iVersion = 2
                Return iVersion
            End If
        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show(ex.Message, pMsgTitle,   

             Windows.Forms.MessageBoxButtons.OK, Windows.Forms.MessageBoxIcon.Error)
            Return Nothing
        Finally
            If Not rkVersionKey Is Nothing Then rkVersionKey.Close()
        End Try
    End Function