MindKid

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