Dataset To Excel Export Function

The Function:

Public Sub ExportDatasetToExcel(ByVal ds As DataSet, ByVal strExcelFile As String)

    Dim conn As New OleDbConnection(String.Format("provider=Microsoft.Jet.OLEDB.4.0; Data Source='{0}';" & "Extended Properties='Excel 8.0;HDR=YES;'", strExcelFile))
    conn.Open()

    Dim strTableQ(ds.Tables.Count) As String

    Dim i As Integer = 0

    'making table query
    For i = 0 To ds.Tables.Count - 1

        strTableQ(i) = "CREATE TABLE [" & ds.Tables(i).TableName & "]("

        Dim j As Integer = 0
        For j = 0 To ds.Tables(i).Columns.Count - 1
            Dim dCol As DataColumn
            dCol = ds.Tables(i).Columns(j)
            strTableQ(i) &= " [" & dCol.ColumnName & "] varchar(255) , "
        Next
        strTableQ(i) = strTableQ(i).Substring(0, strTableQ(i).Length - 2)
        strTableQ(i) &= ")"

        Dim cmd As New OleDbCommand(strTableQ(i), conn)
        cmd.ExecuteNonQuery()

    Next

    'making insert query
    Dim strInsertQ(ds.Tables.Count - 1) As String
    For i = 0 To ds.Tables.Count - 1
        strInsertQ(i) = "Insert Into " & ds.Tables(i).TableName & " Values ("
        For k As Integer = 0 To ds.Tables(i).Columns.Count - 1
            strInsertQ(i) &= "@" & ds.Tables(i).Columns(k).ColumnName & " , "
        Next
        strInsertQ(i) = strInsertQ(i).Substring(0, strInsertQ(i).Length - 2)
        strInsertQ(i) &= ")"
    Next

    'Now inserting data
    For i = 0 To ds.Tables.Count - 1
        For j As Integer = 0 To ds.Tables(i).Rows.Count - 1
            Dim cmd As New OleDbCommand(strInsertQ(i), conn)
            For k As Integer = 0 To ds.Tables(i).Columns.Count - 1
                cmd.Parameters.AddWithValue("@" & ds.Tables(i).Columns(k).ColumnName.ToString(), ds.Tables(i).Rows(j)(k).ToString())
            Next
            cmd.ExecuteNonQuery()
            cmd.Parameters.Clear()

        Next
    Next
End Sub

Let’s say i have my.xls in D drive. i want to export a Dataset Name dsFinal to this excel file.
Use:

ExportDatasetToExcel(dsFinal, “d:\my.xls”)

 

Encryption and Decryption Functions In Vb.net

Imports:

Imports System.Security
Imports System.Security.Cryptography
Imports System.IO
Imports System.Runtime.InteropServices
Imports System.Text.RegularExpressions
Imports System.Text

Encryption:

Public Function Encrypt(ByVal plainText As String) As String

Dim passPhrase As String = "yourPassPhrase"
Dim saltValue As String = "mySaltValue"
Dim hashAlgorithm As String = "SHA1"

Dim passwordIterations As Integer = 2
Dim initVector As String = "@1B2c3D4e5F6g7H8"
Dim keySize As Integer = 256

Dim initVectorBytes As Byte() = Encoding.ASCII.GetBytes(initVector)
Dim saltValueBytes As Byte() = Encoding.ASCII.GetBytes(saltValue)

Dim plainTextBytes As Byte() = Encoding.UTF8.GetBytes(plainText)


Dim password As New PasswordDeriveBytes(passPhrase, saltValueBytes, hashAlgorithm, passwordIterations)

Dim keyBytes As Byte() = password.GetBytes(keySize 8)
Dim symmetricKey As New RijndaelManaged()

symmetricKey.Mode = CipherMode.CBC

Dim encryptor As ICryptoTransform = symmetricKey.CreateEncryptor(keyBytes, initVectorBytes)

Dim memoryStream As New MemoryStream()
Dim cryptoStream As New CryptoStream(memoryStream, encryptor, CryptoStreamMode.Write)

cryptoStream.Write(plainTextBytes, 0, plainTextBytes.Length)
cryptoStream.FlushFinalBlock()
Dim cipherTextBytes As Byte() = memoryStream.ToArray()
memoryStream.Close()
cryptoStream.Close()
Dim cipherText As String = Convert.ToBase64String(cipherTextBytes)
Return cipherText
End Function

Decryption

Public Function Decrypt(ByVal cipherText As String) As String
Dim passPhrase As String = "yourPassPhrase"
Dim saltValue As String = "mySaltValue"
Dim hashAlgorithm As String = "SHA1"

Dim passwordIterations As Integer = 2
Dim initVector As String = "@1B2c3D4e5F6g7H8"
Dim keySize As Integer = 256
' Convert strings defining encryption key characteristics into byte
' arrays. Let us assume that strings only contain ASCII codes.
' If strings include Unicode characters, use Unicode, UTF7, or UTF8
' encoding.
Dim initVectorBytes As Byte() = Encoding.ASCII.GetBytes(initVector)
Dim saltValueBytes As Byte() = Encoding.ASCII.GetBytes(saltValue)

' Convert our ciphertext into a byte array.
Dim cipherTextBytes As Byte() = Convert.FromBase64String(cipherText)

' First, we must create a password, from which the key will be
' derived. This password will be generated from the specified
' passphrase and salt value. The password will be created using
' the specified hash algorithm. Password creation can be done in
' several iterations.
Dim password As New PasswordDeriveBytes(passPhrase, saltValueBytes, hashAlgorithm, passwordIterations)

' Use the password to generate pseudo-random bytes for the encryption
' key. Specify the size of the key in bytes (instead of bits).
Dim keyBytes As Byte() = password.GetBytes(keySize 8)

' Create uninitialized Rijndael encryption object.
Dim symmetricKey As New RijndaelManaged()

' It is reasonable to set encryption mode to Cipher Block Chaining
' (CBC). Use default options for other symmetric key parameters.
symmetricKey.Mode = CipherMode.CBC

' Generate decryptor from the existing key bytes and initialization
' vector. Key size will be defined based on the number of the key
' bytes.
Dim decryptor As ICryptoTransform = symmetricKey.CreateDecryptor(keyBytes, initVectorBytes)

' Define memory stream which will be used to hold encrypted data.
Dim memoryStream As New MemoryStream(cipherTextBytes)

' Define cryptographic stream (always use Read mode for encryption).
Dim cryptoStream As New CryptoStream(memoryStream, decryptor, CryptoStreamMode.Read)

' Since at this point we don't know what the size of decrypted data
' will be, allocate the buffer long enough to hold ciphertext;
' plaintext is never longer than ciphertext.
Dim plainTextBytes As Byte() = New Byte(cipherTextBytes.Length - 1) {}

' Start decrypting.
Dim decryptedByteCount As Integer = cryptoStream.Read(plainTextBytes, 0, plainTextBytes.Length)

' Close both streams.
memoryStream.Close()
cryptoStream.Close()

' Convert decrypted data into a string.
' Let us assume that the original plaintext string was UTF8-encoded.
Dim plainText As String = Encoding.UTF8.GetString(plainTextBytes, 0, decryptedByteCount)

' Return decrypted string.
Return plainText
End Function

Example:

Dim strEncryptedText  As String
strEncryptedText = Encrypt("yourEncryptionText")

Dim strDecrptedText As String
strDecrptedText = Decrypt(strEncryptedText)

You can easily set the passphrase, saltvalue and bits to your liking..

For More Info Go to:
http://msdn.microsoft.com/en-us/library/system.security.cryptography.rijndael.aspx

Fill free to Comment ..

 

A Simple Gmail Client

In This Post I will Guide u To making a Simple Gmail Client  to send Your Emails using Gmail Account Using Vb.net

 Let me Show You how final Result will Look Like:



1. For Sending Mail Through Vb.net. This Client Uses  “Imports System.Net.Mail”.
    So First Import it to your Code




Imports System.Net.Mail

2. Layout All the Controls As Shown In the Above Figure

3. There is Progress Bar after the send Button. I have used Backgroud Worker To Send the Mail. So that When Mail Sending is in Progress the Screen Wont Stop Working.

4. Now Here is the Complete Code

First Define This In your Form

Dim msg As New MailMessage 
Dim i As Integer

Form’s Load Event

Me.ProgressBar1.MarqueeAnimationSpeed = 0

Write This to Send Button’s Click Event

msg.To.Add(Me.txtto.Text) 
msg.From = New MailAddress(Me.txtuser.Text)
msg.Subject = Me.txtsubject.Text
msg.Body = Me.txtbody.Text
'' for attachment
For i = 0 To Me.ListBox1.Items.Count - 1
msg.Attachments.Add(New Attachment(Me.ListBox1.Items(i).ToString))
Next
Me.Button1.Enabled = False
Me.Button1.Text = "Sendign.."
Me.ProgressBar1.Visible = True
Me.ProgressBar1.MarqueeAnimationSpeed = 100
Me.BackgroundWorker1.RunWorkerAsync()

Attach Button’s Click Event ( Button beside the listbox)

Dim d As New OpenFileDialog 
d.ShowDialog()
Me.ListBox1.Items.Add(d.FileName.ToString())
Me.ListBox1.Visible = True

Now its the Back Ground Worker …
Background worker’s Do work Event:

Private Sub BackgroundWorker1_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork 

Dim i As Integer = 0
Dim smtp As New SmtpClient
smtp.Host = "smtp.gmail.com"
smtp.EnableSsl = True
smtp.Port = 587
smtp.Timeout = 300000
smtp.Credentials = New Net.NetworkCredential(Me.txtuser.Text, Me.txtpass.Text)
'Dim ms As MailMessage
'ms = e.Argument
Try
' Label5.Text = "Sending Message......."
'Me.Button1.Text = "Sending...."
' Me.Button1.Enabled = False
'Me.BackgroundWorker1.ReportProgress(i,
smtp.Send(msg)
e.Result = "Done"
Catch ex As Exception
'Label5.Text = "Sending Failed........"
MessageBox.Show(ex.ToString())
End Try

Background Worker’s Run work Completed Event:

Private Sub BackgroundWorker1_RunWorkerCompleted(ByVal sender As System.Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted 
' Label5.Text = "Message Sending Complete"
Me.ProgressBar1.MarqueeAnimationSpeed = 0
Me.Button1.Text = "Send"
Me.Button1.Enabled = True
MessageBox.Show("Sent")
End Sub

And It’s Done.

For Complete Project Email me at [email protected]

 
%d bloggers like this: