DOT NET WORLD: 11/01/2012

Wednesday, November 7, 2012

MySQL DATE_FORMAT() Function

Definition and Usage

The DATE_FORMAT() function is used to display date/time data in different formats.

Syntax

DATE_FORMAT(date,format)
Where date is a valid date and format specifies the output format for the date/time.

The formats that can be used are:

Format Description
%a Abbreviated weekday name
%b Abbreviated month name
%c Month, numeric
%D Day of month with English suffix
%d Day of month, numeric (00-31)
%e Day of month, numeric (0-31)
%f Microseconds
%H Hour (00-23)
%h Hour (01-12)
%I Hour (01-12)
%i Minutes, numeric (00-59)
%j Day of year (001-366)
%k Hour (0-23)
%l Hour (1-12)
%M Month name
%m Month, numeric (00-12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss AM or PM)
%S Seconds (00-59)
%s Seconds (00-59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00-53) where Sunday is the first day of week
%u Week (00-53) where Monday is the first day of week
%V Week (01-53) where Sunday is the first day of week, used with %X
%v Week (01-53) where Monday is the first day of week, used with %x
%W Weekday name
%w Day of the week (0=Sunday, 6=Saturday)
%X Year of the week where Sunday is the first day of week, four digits, used with %V
%x Year of the week where Monday is the first day of week, four digits, used with %v
%Y Year, four digits
%y Year, two digits

Example

The following script uses the DATE_FORMAT() function to display different formats. We will use the NOW() function to get the current date/time:

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
DATE_FORMAT(NOW(),'%m-%d-%Y')
DATE_FORMAT(NOW(),'%d %b %y')
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')
The result would look something like this:

Nov 04 2008 11:45 PM
11-04-2008
04 Nov 08
04 Nov 2008 11:45:34:243

Monday, November 5, 2012

Save Image to Database as Binary using FileUpload in asp.net

if (fupimage.HasFile)
{
//getting length of uploaded file
int length = fupimage.PostedFile.ContentLength;
//create a byte array to store the binary image data
imgbyte = new byte[length];
//store the currently selected file in memeory
HttpPostedFile img = fupimage.PostedFile;
//set the binary data
img.InputStream.Read(imgbyte, 0, length);
string imagename = fupimage.PostedFile.FileName;
//use the web.config to store the connection string
}
else
{

string fpath = Server.MapPath("~//images//face.gif");
System.IO.FileInfo imageInfo = new System.IO.FileInfo(fpath);
imgbyte = new byte[imageInfo.Length];
System.IO.FileStream imagestream = imageInfo.OpenRead();
imagestream.Read(imgbyte, 0, imgbyte.Length);
imagestream.Close();

}

cmd.Parameters.Add("@photo", SqlDbType.Image).Value = imgbyte;

Mail Sending in asp.net using System.Net.Mail

This summary is not available. Please click here to view the post.

Retrieve image, font and color settings from Database in C# windows application

private void btnpreview_Click(object sender, EventArgs e)
{
try
{
dt = cs.getTable("select * from [View] where hall='Receiption'", con.con.ConnectionString.ToString());
if (dt.Rows.Count > 0)
{
Image im = Bitmap.FromFile(dt.Rows[0]["image"].ToString());
gbLabels.BackgroundImage = im;

TypeConverter converter = TypeDescriptor.GetConverter(typeof(Font));
// Saving Font object as a string
string fontString = dt.Rows[0]["font"].ToString();
string scrollfontString = dt.Rows[0]["scrollfont"].ToString();
// Load an instance of Font from a string
Font fon = (Font)converter.ConvertFromString(fontString);
Font scrollfon = (Font)converter.ConvertFromString(fontString);
string color = dt.Rows[0]["color"].ToString();
string scrollcolor = dt.Rows[0]["scrollcolor"].ToString();
Color col = System.Drawing.ColorTranslator.FromHtml("#" + color);
Color scrollcol = System.Drawing.ColorTranslator.FromHtml("#" + scrollcolor);

lblEventName.Font = fon;
lblEventName.ForeColor = col;
lblnotes.Font = fon;
lbltime.Font = fon;
label2.Font = fon;
label1.Font = fon;
lblnotes.ForeColor = col;
lbltime.ForeColor = col;
label2.ForeColor = col;
label1.ForeColor = col;
dougScrollingTextCtrl1.DougScrollingTextColor1 = scrollcol;
dougScrollingTextCtrl1.Font = scrollfon;
gbLabels.Visible = true;

}
}
catch (Exception ex)
{
Utils.LogHandler.HandleError(ex);
}
}

Color Settings save to Database in C# windows application

private void btnColor_Click(object sender, EventArgs e)
{
try
{
if (colorDialog1.ShowDialog() == DialogResult.OK)
{
string color = colorDialog1.Color.ToArgb().ToString("x");
//color = color.Substring(2, 6);
//color = "#" + color;

txtColor.Text = color;
}
}
catch (Exception ex)
{
Utils.LogHandler.HandleError(ex);
}
}

Font Settings Save to Database in C# windows application

private void btnfont_Click(object sender, EventArgs e)
{
try
{
if (fontDialog1.ShowDialog() == DialogResult.OK)
{
Font font = fontDialog1.Font;

TypeConverter tc = TypeDescriptor.GetConverter(typeof(Font));
string fontString = tc.ConvertToString(font);

txtFont.Text = fontString;
}
}
catch (Exception ex)
{
Utils.LogHandler.HandleError(ex);
}
}

Images upload and save to folder in C# windows application

private void btnBrowseimage_Click(object sender, EventArgs e)
{
try
{

btnSubmit.Enabled = false;
gbLabels.Visible = false;
gbLabels.BackgroundImage = null;

OpenFileDialog fDialog = new OpenFileDialog();
fDialog.AddExtension = true;
fDialog.CheckFileExists = true;
fDialog.CheckPathExists = true;
fDialog.Title = "Open Image Files";
fDialog.Filter = "All files (*.*)|*.*|JPEG Files|*.jpeg|GIF Files|*.gif";
fDialog.InitialDirectory = @"C:\";
fDialog.Multiselect = true;
if (fDialog.ShowDialog() == DialogResult.OK)
{

imageFiles = fDialog.FileNames;
Files = new string[imageFiles.Length];
for (int j = 0; j <= imageFiles.Length - 1; j++)
{
System.Drawing.Image im = System.Drawing.Image.FromFile(imageFiles[j]);
int ActualWidth = im.Width;
int ActualHeight = im.Height;
im.Dispose();
if (ActualWidth > 3600 && ActualHeight > 2400)
{
MessageBox.Show("Please upload image with size less than 3600X2400", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{

txtImageName.Text = imageFiles[j];
FileStream fssource = new FileStream(txtImageName.Text, FileMode.Open);

var root1 = txtImageName.Text;
var temp1 = root1.Split('\\');
var image = temp1[temp1.Length - 1];

var root = Application.ExecutablePath;

var temp = root.Split('\\');
temp[temp.Length - 1] = "";
temp[temp.Length - 2] = "Images";
var newpath = string.Join("\\", temp);

bool folderExists = Directory.Exists(newpath);
if (!folderExists)
Directory.CreateDirectory(newpath);

newpath = newpath + "_" + DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second + "ReceiptionBack" + "." + image.Split('.')[1];

txtImageName.Text = newpath;
Files[j] = txtImageName.Text;

FileStream fsdest = new FileStream(newpath, FileMode.Create);
while (true)
{
int i;
i = fssource.ReadByte();
if (i == -1)
break;
fsdest.WriteByte(Convert.ToByte(i));
}
fsdest.Close();
}

}

}
btnSubmit.Enabled = true;

}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}

SlideShow in C# windows application using picturbox

string[] Files;

var root = Application.ExecutablePath;

var temp = root.Split('\\');
temp[temp.Length - 1] = "";
temp[temp.Length - 2] = "Images";
var newpath = string.Join("\\", temp);
Files = Directory.GetFiles(newpath);

/// <summary>
/// Show the image in the PictureBox.
/// </summary>
public static void ShowImage(string path, PictureBox pct)
{
pct.ImageLocation = path;
}

/// <summary>
/// Show the next image.
/// </summary>
private void ShowNextImage()
{
ShowImage(this.Files[(++this.selected) % this.Files.Length], this.pictureBox);
}

Windows Media Player integrate in C# windows application

videourl = dt.Rows[0]["videourl"].ToString();
axWindowsMediaPlayer1.URL = videourl;
axWindowsMediaPlayer1.settings.rate = 1.0;
axWindowsMediaPlayer1.Ctlcontrols.play();
//axWindowsMediaPlayer1.settings.setMode("loop", true);

private void axWindowsMediaPlayer1_PlayStateChange(object sender, AxWMPLib._WMPOCXEvents_PlayStateChangeEvent e)
{
try
{
if (axWindowsMediaPlayer1.playState == WMPLib.WMPPlayState.wmppsMediaEnded)
{
ShowImage(Files[selected], pictureBox);
timer2.Enabled = true;
pictureBox.Visible = true;
axWindowsMediaPlayer1.Visible = false;
axWindowsMediaPlayer1.Ctlcontrols.stop();
}
else if (axWindowsMediaPlayer1.playState == WMPLib.WMPPlayState.wmppsStopped)
{
}
}
catch (Exception ex)
{
Utils.LogHandler.HandleError(ex);
}
}

RowHeader display in VB.NET windows application

Private Sub dgvSubscriptions_RowPostPaint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgvSubscriptions.RowPostPaint
Try
' Dim b As SolidBrush = New SolidBrush(dgvSubscriptions.RowHeadersDefaultCellStyle.ForeColor)

'e.Graphics.DrawString((e.RowIndex + 1).ToString(), e.InheritedRowStyle.Font, b, e.RowBounds.Location.X + 10, e.RowBounds.Location.Y + 4)
Dim dg As DataGridView = DirectCast(sender, DataGridView)
' Current row record

Dim rowNumber As String = String.Empty
rowNumber = (e.RowIndex + 1).ToString()

' Format row based on number of records displayed by using leading zeros
While rowNumber.Length < dg.RowCount.ToString().Length
rowNumber = "0" & rowNumber
End While

' Position text
Dim size As SizeF = e.Graphics.MeasureString(rowNumber, Me.Font)
If dg.RowHeadersWidth < CInt(size.Width + 20) Then
dg.RowHeadersWidth = CInt(size.Width + 20)
End If

' Use default system text brush
Dim b As Brush = SystemBrushes.ControlText

' Draw row number
e.Graphics.DrawString(rowNumber, dg.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
Catch ex As Exception
Utils.LogHandler.HandleError(ex)
End Try
End Sub

CompressFile in VB.NET using ICSharpCode.SharpZipLib.dll

Public Shared Sub CompressFile(ByVal sourceFilePath As String, ByVal destinationFilePath As String)
Dim outFileInfo As IO.FileInfo = New IO.FileInfo(destinationFilePath)
Dim inFileInfo As IO.FileInfo = New IO.FileInfo(sourceFilePath)

'Throw error if source file doesn't exist
If Not inFileInfo.Exists Then
Throw New Exception("Source File not found!!")
End If
' Create the output directory if it does not exist
If Not IO.Directory.Exists(outFileInfo.Directory.FullName) Then IO.Directory.CreateDirectory(outFileInfo.Directory.FullName)

Using fsOut As IO.FileStream = IO.File.Create(destinationFilePath)
Using zipStream As ICSharpCode.SharpZipLib.Zip.ZipOutputStream = New ICSharpCode.SharpZipLib.Zip.ZipOutputStream(fsOut)
zipStream.SetLevel(4)
Dim newEntry As ICSharpCode.SharpZipLib.Zip.ZipEntry = New ICSharpCode.SharpZipLib.Zip.ZipEntry(inFileInfo.Name)
newEntry.DateTime = DateTime.UtcNow
zipStream.PutNextEntry(newEntry)
Dim buffer(4096) As Byte
Using streamReader As IO.FileStream = IO.File.OpenRead(sourceFilePath)
ICSharpCode.SharpZipLib.Core.StreamUtils.Copy(streamReader, zipStream, buffer)
End Using
zipStream.CloseEntry()
zipStream.IsStreamOwner = True
zipStream.Close()
End Using
End Using
End Sub

Friday, November 2, 2012

DataTable to Excel using VB.NET

Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.AppendHeader("content-disposition", "attachment; filename=Dashboard.xls")
Response.Charset = ""
Response.Write(DataTable2ExcelString(dtInternalsTemp, dtSuppliersTemp, dtCustomersTemp, "Dashboard"))
Response.End()

Response.ContentType = "application/vnd.ms-excel"

Private Function DataTable2ExcelString(ByVal dt1 As DataTable, ByVal dt2 As DataTable, ByVal dt3 As DataTable, ByVal strfrom As String) As String
Dim sbTop As New Text.StringBuilder()
sbTop.AppendLine("<?xml version=""1.0""?>")
sbTop.AppendLine("<?mso-application progid=""Excel.Sheet""?>")
sbTop.AppendLine("<Workbook")
sbTop.AppendLine(" xmlns=""urn:schemas-microsoft-com:office:spreadsheet""")
sbTop.AppendLine(" xmlns:o=""urn:schemas-microsoft-com:office:office""")
sbTop.AppendLine(" xmlns:x=""urn:schemas-microsoft-com:office:excel""")
sbTop.AppendLine(" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""")
sbTop.AppendLine(" xmlns:html=""http://www.w3.org/TR/REC-html40"">")
sbTop.AppendLine(" <DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">")
sbTop.AppendLine(" <Author>Surgere</Author>")
sbTop.AppendLine(" <LastAuthor></LastAuthor>")
sbTop.AppendLine(" <Created></Created>")
sbTop.AppendLine(" <Company>Surgere</Company>")
sbTop.AppendLine(" <Version>11.8036</Version>")
sbTop.AppendLine(" </DocumentProperties>")
sbTop.AppendLine(" <ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel"">")
sbTop.AppendLine(" <WindowHeight>6795</WindowHeight>")
sbTop.AppendLine(" <WindowWidth>8460</WindowWidth>")
sbTop.AppendLine(" <WindowTopX>120</WindowTopX>")
sbTop.AppendLine(" <WindowTopY>15</WindowTopY>")
sbTop.AppendLine(" <ProtectStructure>False</ProtectStructure>")
sbTop.AppendLine(" <ProtectWindows>False</ProtectWindows>")
sbTop.AppendLine(" </ExcelWorkbook>")
sbTop.AppendLine(" <Styles>")
sbTop.AppendLine(" <Style ss:ID=""Default"" ss:Name=""Normal"">")
sbTop.AppendLine(" <Alignment ss:Vertical=""Bottom"" />")
sbTop.AppendLine(" <Borders />")
sbTop.AppendLine(" <Font />")
sbTop.AppendLine(" <Interior />")
sbTop.AppendLine(" <NumberFormat />")
sbTop.AppendLine(" <Protection />")
sbTop.AppendLine(" </Style>")
sbTop.AppendLine(" <Style ss:ID=""s21"">")
sbTop.AppendLine(" <Font x:Family=""Swiss"" ss:Bold=""1"" />")
sbTop.AppendLine(" </Style>")
sbTop.AppendLine(" </Styles>")

If strfrom = "Dashboard" Then

If Not dt1 Is Nothing Then

sbTop.AppendLine(" <Worksheet ss:Name=""Internal"">")
sbTop.AppendLine(" <Table>")

sbTop.AppendLine("<Row>")
Dim i As Integer
For i = 0 To dt1.Columns.Count - 1
sbTop.AppendLine("<Cell><Data ss:Type=""String"">" & (dt1.Columns(i).ColumnName & "").Replace("<", "<").Replace(">", ">") & "</Data></Cell>")
Next
sbTop.AppendLine("</Row>")

'Items
Dim x As Integer
Dim sType As String
For x = 0 To dt1.Rows.Count - 1
sbTop.Append("<Row>")
For i = 0 To dt1.Columns.Count - 1
If dt1.Columns(i).DataType Is GetType(Decimal) Then
sType = "Number"
Else
sType = "String"
End If
sbTop.Append("<Cell><Data ss:Type=""" & sType & """>" & (dt1.Rows(x)(i) & "").Replace("<", "<").Replace(">", ">").Replace(vbNewLine, "
") & "</Data></Cell>")
Next
sbTop.Append("</Row>")
Next

sbTop.AppendLine(" </Table>")
sbTop.AppendLine(" </Worksheet>")
End If
If Not dt2 Is Nothing Then

sbTop.AppendLine(" <Worksheet ss:Name=""Supplier"">")
sbTop.AppendLine(" <Table>")

sbTop.AppendLine("<Row>")
'Dim i As Integer
For i = 0 To dt2.Columns.Count - 1
sbTop.AppendLine("<Cell><Data ss:Type=""String"">" & (dt2.Columns(i).ColumnName & "").Replace("<", "<").Replace(">", ">") & "</Data></Cell>")
Next
sbTop.AppendLine("</Row>")

'Items
Dim x As Integer
Dim sType As String
sType = String.Empty
For x = 0 To dt2.Rows.Count - 1
sbTop.Append("<Row>")
For i = 0 To dt2.Columns.Count - 1
If dt2.Columns(i).DataType Is GetType(Decimal) Then
sType = "Number"
Else
sType = "String"
End If
sbTop.Append("<Cell><Data ss:Type=""" & sType & """>" & (dt2.Rows(x)(i) & "").Replace("<", "<").Replace(">", ">").Replace(vbNewLine, "
") & "</Data></Cell>")
Next
sbTop.Append("</Row>")
Next

sbTop.AppendLine(" </Table>")
sbTop.AppendLine(" </Worksheet>")
End If

If Not dt3 Is Nothing Then
sbTop.AppendLine(" <Worksheet ss:Name=""Customer"">")
sbTop.AppendLine(" <Table>")

sbTop.AppendLine("<Row>")
Dim i As Integer
For i = 0 To dt3.Columns.Count - 1
sbTop.AppendLine("<Cell><Data ss:Type=""String"">" & (dt3.Columns(i).ColumnName & "").Replace("<", "<").Replace(">", ">") & "</Data></Cell>")
Next
sbTop.AppendLine("</Row>")

'Items
Dim x As Integer
Dim sType As String
For x = 0 To dt3.Rows.Count - 1
sbTop.Append("<Row>")
For i = 0 To dt3.Columns.Count - 1
If dt3.Columns(i).DataType Is GetType(Decimal) Then
sType = "Number"
Else
sType = "String"
End If
sbTop.Append("<Cell><Data ss:Type=""" & sType & """>" & (dt3.Rows(x)(i) & "").Replace("<", "<").Replace(">", ">").Replace(vbNewLine, "
") & "</Data></Cell>")
Next
sbTop.Append("</Row>")
Next

sbTop.AppendLine(" </Table>")
sbTop.AppendLine(" </Worksheet>")
End If
ElseIf strfrom = "DashboardAsset" Then

If Not dt1 Is Nothing Then

sbTop.AppendLine(" <Worksheet ss:Name=""AssetList"">")
sbTop.AppendLine(" <Table>")

sbTop.AppendLine("<Row>")
Dim i As Integer
For i = 0 To dt1.Columns.Count - 1
sbTop.AppendLine("<Cell><Data ss:Type=""String"">" & (dt1.Columns(i).ColumnName & "").Replace("<", "<").Replace(">", ">") & "</Data></Cell>")
Next
sbTop.AppendLine("</Row>")

'Items
Dim x As Integer
Dim sType As String
For x = 0 To dt1.Rows.Count - 1
sbTop.Append("<Row>")
For i = 0 To dt1.Columns.Count - 1
If dt1.Columns(i).DataType Is GetType(Decimal) Then
sType = "Number"
Else
sType = "String"
End If
sbTop.Append("<Cell><Data ss:Type=""" & sType & """>" & (dt1.Rows(x)(i) & "").Replace("<", "<").Replace(">", ">").Replace(vbNewLine, "
") & "</Data></Cell>")
Next
sbTop.Append("</Row>")
Next

sbTop.AppendLine(" </Table>")
sbTop.AppendLine(" </Worksheet>")
End If
ElseIf strfrom = "LaneAlert" Then
If Not dt1 Is Nothing Then

sbTop.AppendLine(" <Worksheet ss:Name=""Unidentified Asset Assignment"">")
sbTop.AppendLine(" <Table>")

sbTop.AppendLine("<Row>")
Dim i As Integer
For i = 0 To dt1.Columns.Count - 1
sbTop.AppendLine("<Cell><Data ss:Type=""String"">" & (dt1.Columns(i).ColumnName & "").Replace("<", "<").Replace(">", ">") & "</Data></Cell>")
Next
sbTop.AppendLine("</Row>")

'Items
Dim x As Integer
Dim sType As String
For x = 0 To dt1.Rows.Count - 1
sbTop.Append("<Row>")
For i = 0 To dt1.Columns.Count - 1
If dt1.Columns(i).DataType Is GetType(Decimal) Then
sType = "Number"
Else
sType = "String"
End If
sbTop.Append("<Cell><Data ss:Type=""" & sType & """>" & (dt1.Rows(x)(i) & "").Replace("<", "<").Replace(">", ">").Replace(vbNewLine, "
") & "</Data></Cell>")
Next
sbTop.Append("</Row>")
Next

sbTop.AppendLine(" </Table>")
sbTop.AppendLine(" </Worksheet>")
End If

If Not dt2 Is Nothing Then

sbTop.AppendLine(" <Worksheet ss:Name=""Unidentified Lane Movement"">")
sbTop.AppendLine(" <Table>")

sbTop.AppendLine("<Row>")
'Dim i As Integer
For i = 0 To dt2.Columns.Count - 1
sbTop.AppendLine("<Cell><Data ss:Type=""String"">" & (dt2.Columns(i).ColumnName & "").Replace("<", "<").Replace(">", ">") & "</Data></Cell>")
Next
sbTop.AppendLine("</Row>")

'Items
Dim x As Integer
Dim sType As String
sType = String.Empty
For x = 0 To dt2.Rows.Count - 1
sbTop.Append("<Row>")
For i = 0 To dt2.Columns.Count - 1
If dt2.Columns(i).DataType Is GetType(Decimal) Then
sType = "Number"
Else
sType = "String"
End If
sbTop.Append("<Cell><Data ss:Type=""" & sType & """>" & (dt2.Rows(x)(i) & "").Replace("<", "<").Replace(">", ">").Replace(vbNewLine, "
") & "</Data></Cell>")
Next
sbTop.Append("</Row>")
Next

sbTop.AppendLine(" </Table>")
sbTop.AppendLine(" </Worksheet>")
End If

End If

sbTop.AppendLine("</Workbook>")

Return sbTop.ToString()
End Function

DOT NET WORLD