DATAGRIDVIEW ROW FORMATING

SAMPLE:

Private Sub dgvData_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgvData.CellFormatting

        If dgvData.DataSource IsNot Nothing Then
            Dim drv As DataRowView
            Dim dt As DataTable = CType(dgvData.DataSource, DataSet).Tables(0)

            If e.RowIndex >= 0 Then
                If e.RowIndex <= dt.Rows.Count - 1 Then
                    drv = dt.DefaultView.Item(e.RowIndex)
                    Dim dfm As Date
                    Dim dto  As Date
                    dfm = drv.Item("Period From")
                    dto  = drv.Item("Period Upto")                      
                    If Now.Date >= dfm And Now.Date <= dto Then
                        e.CellStyle.ForeColor = dgvData.DefaultCellStyle.ForeColor
                    Else
                        e.CellStyle.ForeColor = SystemColors.GrayText
                    End If
                End If
            End If
        End If
    End Sub

SUM OF A DATAGRIDVIEW CELL USING LINQ

SAMPLE:1

Dim qry0 = From theRow As DataGridViewRow In dgvData.Rows _
                      Select theRow
Dim t = qry0.Sum(Function(x As DataGridViewRow) x.Cells("Amount").Value)

** dgvData - Datagridview name "Anount" is column name

-----------------------------------------------------------------

SAMPLE:2

Dim qry1 = From theRow As DataGridViewRow In dgvData.Rows _
              Where theRow.Cells("Year").Value > 2010 _
              Select theRow

Dim t = qry1.Sum(Function(x As DataGridViewRow) x.Cells("Amount").Value)

LINQ to Untyped DataSet Samples

SAMPLE:1

VB.NET

Dim query = From em In ds.Tables("Employees").AsEnumerable() _
            Where em.Field(Of String)("FirstName") = "Bijo" _

            Select em

'To display the results
   For Each em In query
       Console.WriteLine("Name = {1} {2}", _
                em.Field(Of String)("FirstName"), _
                em.Field(Of String)("LastName"))

        Next


C#

var query = from em in ds.Tables("Employees").AsEnumerable() _
            where em.Field<string>("FirstName") == "Bijo"

            select em.Field<string>(“FirstName”);



C# METHOD-2

var query = ds.Tables("Employees").AsEnumerable()
    .Where(dr => dr.Field<string>("FirstName") == "Bijo")

    .Select(dr => dr.Field<string>("FirstName"));

READING XML DOCUMENTS USING XDOCUMENT

READING XML DOCUMENTS USING XDOCUMENT

<employees>
    <department name="HR" >
        <employee ID="1" Name="Alex" passcode="100"/>
        <employee ID="2" Name="Anil" passcode="1545"/>
        <employee ID="3" Name="Vinod" passcode="856"/>
        <employee ID="4" Name="Sibi" passcode="1254"/>
    </department>
    <department name="Sales" >
        <employee ID="1" Name="Jeen" passcode="700"/>
        <employee ID="2" Name="Anil" passcode="545"/>
        <employee ID="3" Name="Kichu" passcode="86"/>
    </department>
</employees>


TO READ THE ABOVE XML USING XDOCUMENT OBJECT

Dim xdoc As XDocument = XDocument.Parse(<<xml string>>)
Dim deptList = From dl In xdoc.Descendants("department") Select dl

For Each dl In deptList
    '//PROCESS EACH DEPT.
    'ie dl="<department name="HR">
               <employee ID="1" Name="Alex" passcode="100"/>
               <employee ID="2" Name="Anil" passcode="1545"/>
               <employee ID="3" Name="Vinod" passcode="856" />
               <employee ID="4" Name="Sibi" passcode="1254" /> 
            </department>"

     'to read attributes use dl.Attribute("name").Value
 
     For Each em In dl.Elements("employee")
         '//PROCESS AN EMPLOYEE
         'ie em="<employee ID="1" Name="Alex" passcode="100" />"
     Next

Next

READING XML DOCUMENTS

READING XML DOCUMENTS

EXAMPLE-1

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
     <generalsettings>
          <billheader>
                <title>Title Name</title>
                <phone> 0+91 xxxx xxxx</phone>
                <email> xxxxxx@xyz.com</email>
                <web address="hotelxyz.com"/>
          </billheader>
    </generalsettings>


To load the xml into an xml document object:

  Dim xmldoc As New XmlDocument
  xmldoc.LoadXml(<<xmlString>>)

To select a specific node from xml document:
     Dim titleNode As XmlNode = xmldoc.SelectSingleNode("/generalsettings/billheader/title")
     Dim titleText =   titleNode.InnerText

To read attribute values:
     Dim webNode As XmlNode = xmldoc.SelectSingleNode("/generalsettings/billheader/web ")
     Dim webAddress =   webNode.Attributes("address").Value



EXAMPLE-2

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<waiterList>
    <waiter ID="1" Name="Alex" passcode="100"/>
    <waiter ID="2" Name="Anil" passcode="1545"/>
    <waiter ID="3" Name="Vinod" passcode="856"/>
    <waiter ID="4" Name="Sibi" passcode="1254"/>
</waiterList>

To Read All Waiter Elements:

nodelist = doc.GetElementsByTagName("waiter")
For Each node As XmlElement In nodelist
   //process nodes//
Next

STORE IMAGES IN DATABASE

STORE IMAGES IN DATABASE 

‘To show an image in picture box

PictureBox1.Image = Image.FromFile(<<file name with full path>>)

‘To Store an image in database table (sql server)  (In sql server use data type ‘Image’)

Dim picBytes As Byte() = Nothing

Dim memStream As New IO.MemoryStream

PictureBox1.Image.Save(memStream, Drawing.Imaging.ImageFormat.Jpeg)

picBytes = memStream.ToArray()

SqlCmd.CommandText = "Insert Into ImageTable(Picture) Values (@pic)”

SqlCmd.Parameters.AddWithValue("@pic ", picBytes)

SqlCmd.ExecuteNonQuery()

‘To Retrieve an image from sql server database and shown in a Picture box

Dim picBytes As Byte() = CType(DataRow.Item("Picture"), Byte())

Dim memStream As IO.MemoryStream = New IO.MemoryStream(picBytes)

PictureBox1.Image = Image.FromStream(memStream)

Coding Standerds And Guidelines

http://msdn.microsoft.com/en-us/library/ms229002%28v=vs.100%29.aspx

TO BACKUP SQL SERVER DATABASE USING ADO.NET

Imports System.Data.SqlClient

Public Sub GenerateBackup()
 Using xCon As New SqlConnection(<<ConnectionString>>), _
            Cmd As New SqlCommand
                xCon.Open()
                Cmd.Connection = xCon
                Cmd.CommandText = "BACKUP DATABASE @dbFile TO DISK= @Target WITH FORMAT"
       
        sqlCmd.Parameters.AddWithValue("@dbFile ","<<DatabaseFile>>")
        '//<<DatabaseFile>> -- IF AN ATTACHED DATABASE FILE, SPECIFY THE FULL PATH AND FILE NAME [ie 'C:\MyDbSpace\db.mdf']
                       OTHERWISE SPECIFY THE DATABASE NAME
         sqlCmd.Parameters.AddWithValue("@Target  ","<<BackupFilePath & FileName>>")
                Cmd.ExecuteNonQuery()
                xCon.Close()
End Using

COMBO BOX DATA BINDING

Public Sub PopulateComboBox(ByRef Combo As ComboBox, ByVal SQl As String)

        Try
            Dim DS As New DataSet
            Dim DA As New SqlClient.SqlDataAdapter
            Dim TableName As String

            TableName = Trim(Mid(SQl, InStr(SQl, " FROM ", CompareMethod.Text) + 6)) & " "
            TableName = Mid(TableName, 1, InStr(TableName, " ", CompareMethod.Text))

            Using xCon As New SqlClient.SqlConnection(conn_string)
                xCon.Open()
                DA.SelectCommand = New SqlClient.SqlCommand(SQl)
                DA.SelectCommand.Connection = xCon
                DA.Fill(DS, TableName)
                xCon.Close()
            End Using
            Combo.DataSource = DS.Tables(TableName)
            Combo.DisplayMember = DS.Tables(TableName).Columns(1).Caption
            Combo.ValueMember = DS.Tables(TableName).Columns(0).Caption
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try

    End Sub


Usage
PopulateComboBox(cmbProperty, "Select Id,Title,SurveyNo From Properties")

To Retrive Value from Combox
cmbProperty.SelectedValue -- to get Id
cmbProperty.SelectedItem -- to get DataRowView -
[Conversion Needed, ie CType(cmbProperty.SelectedItem,DataRowView)]

Set DataGridView Row Height

Dim xRow as DataGridViewRow = DataGridView1.Rows(DataGridView1.Rows.Add())
'//after assigning cell values use following statement
xRow.Height = xRow.GetPreferredHeight(xRow.Index, DataGridViewAutoSizeRowMode.AllCells, False)

Delegate - Sample -1

'// Assumes  Form-1 shows a list of records and Form2 to create a new record. The purpose of this delegate is to update the list view in Form-1 after saving the record.

FORM-1
Public Class Form1

    Private Sub AddOrUpdateRecordallbackFn(ByVal xRec As AuctionInword)
    //Arg - 'AuctionInword' user defined type
    //METHOD IMPLIMENTATION
    End Sub

   Private Sub Button1_Click()
       '// CALL FORM 2
      Dim xfm As New Form2
      '// Point the function
       xfm.AddOrUpdateAICallBack = AddressOf AddOrUpdateRecordallbackFn
       xfm.Show()
     End Sub

End Class


FORM-2
Public Class Form2
    '//Delegate to Form1 function AddOrUpdateRecordallbackFn
    Public Delegate Sub AddOrUpdateRecord(ByVal xRecord As AuctionInword)   
   
    '//variable to point the function
    Public AddOrUpdateAICallBack As AddOrUpdateRecord

Private Sub SaveRecord()
        //Save record implementation
    Dim _CurrentRecord = New <<User Type>> '//To store new record
    -----------------------
    -----------------------
       
    //After Saving, call the Form1 function 'AddOrUpdateRecordallbackFn' using delegate
    If AddOrUpdateAICallBack IsNot Nothing Then AddOrUpdateAICallBack(_CurrentRecord)
End Sub

End Class