.Net Tips and Solutions: June 2013

Friday, June 7, 2013

HOW TO DELETE ROWS FROM A TABLE WITHOUT USING WHERE CONDITION

If we want to delete top 1 row (or) top 2 (or) top 3 (or) top 5 ... etc

We use the query below

Syntax:: Delete top (no. of rows) from <table name>

example:: delete top (2) from tbEmployees

USE OF LOGGERS IN ASP.NET

The main purpose of creating logger file in our project is that, after deploying our project in to the server we are not able to check the errors or line by line debugging, In that time we have to know , what are the errors are coming when our project is running on the live(available in the website).

In that time if we use the below code in our project, then all the errors will be saved/stored in a text document in our system in a folder.

Why i wrote the path in web.config file means, when we are Hosting(up) our project in to server, in that time we will change only web.config file not the code.
You can save this log files in your database also.
-->We are creating our logger text file in E: Drive.(We can change the drive also)

<appSettings>
<add key="log" value="E:\\Logger.Log"/>
</appSettings>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.IO;

namespace Project
{
public static class logger
{
public static void log(string className, string method, string message)
{
FileStream fs=null; StreamWriter sw=null;
try
{
// Here we are calling the path from web.config file
string str=ConfigurationManager.AppSettings["log"];
fs = new FileStream(str, FileMode.Append);
sw = new StreamWriter(fs);
sw.WriteLine(DateTime.Now.ToString() + "\t" + className+ "\t" + method + "\t" + message);
//Don't forgot to close Stream Writer before File Stream
sw.Close();
fs.Close();
}
catch(Exception ex)
{
throw ex;
}
finally
{
if(sw!=null)
sw.Close();
if(fs!=null)
fs.Close();
}
}
}
}

// how we will use this Logger method in our project

public class MessageDB
{
public void InsertMessage(long UID, MessageType Type, string Message, string Remarks)
{
// logger is the class name and log is the method name
logger.log(" MessageDB","InsertMessage","Begin");
try
{
// our code
logger.log(" MessageDB", "InsertMessage", "End");
}
}
catch (Exception ex)
{
logger.log("MessageDB", "InsertMessage", "Exception" + ex.Message);
throw ex;
}
finally
{

}
}

// how the file is stored in our directory
In the text file format it will store.
Logger.Log // text file(Logger.Log is file name)
the text inside of the file will be stored like below

--> If we wont get any error in our project means the text look like blow
6/8/2013 10:37:15 AM " MessageDB", "InsertMessage", "Begin"
6/8/2013 10:37:15 AM " MessageDB", "InsertMessage", "END"

--> If we got any error in our project means the text look like blow
6/8/2013 10:37:15 AM Login login Start
6/8/2013 10:37:15 AM UserBiz AuthenticateUser start
6/8/2013 10:37:15 AM UserBiz AuthenticateUser end
6/8/2013 10:37:15 AM UserDB AutenticateUser start
6/8/2013 10:37:15 AM UserDB AuthenticateUser MessageCannot open database "EmployeeDataBase" requested by the login. The login failed.

PURPOSE OF STREAM WRITER IN C#.NET:-
StreamWriter writes text files. It enables easy and efficient text output. It is best placed in a using-statement to ensure it is removed from memory when no longer needed. It provides several constructors and many methods.
We first declare and initialize a new StreamWriter instance in a using construct. Please note how the System.IO namespace is included at the top of the file. The keyword using means different things in different places.
Example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace StreamReadWrite
{
    class Program
    {
        static void Main(string[] args)
        {
            // Get the directories currently on the C drive.
            DirectoryInfo[] cDirs = new DirectoryInfo(@"c:\").GetDirectories();

            // Write each directory name to a file. 
            using (StreamWriter sw = new StreamWriter("CDriveDirs.txt"))
            {
                foreach (DirectoryInfo dir in cDirs)
                {
                    sw.WriteLine(dir.Name);

                }
            }

            // Read and show each line from the file. 
            string line = "";
            using (StreamReader sr = new StreamReader("CDriveDirs.txt"))
            {
                while ((line = sr.ReadLine()) != null)
                {
                    Console.WriteLine(line);
                }
            }
        }
    }
}

HOW TO COMPARE TWO EXCEL SHEETS AND DISPLAY THE RESULT IN NEW EXCEL SHEET USING C#.NET?

Consider two excel sheets and specify the data as follows
Excel1.xslx
ID NAME Qualification
1 bindu B-tech
2 Anu Degree
3 Deepthi M-tech
4 indu Mca
5 Amulya Mba
Excel2.xslx
ID NAME Qualification
1 bindu B-tech
2 Anu Degree
3 Deepthi M-tech
Now write the code and it will automatically generates a new Excel sheet as follows
Result.xslx
ID NAME Qualification
4 indu Mca
5 Amulya Mba

Coding:-
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using System.Data.SqlClient;
using System.IO;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelEg
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void BtnBrwse_Click(object sender, EventArgs e)
{
OpenFileDialog DialogA = new OpenFileDialog();
DialogA.CheckFileExists = true;
//DialogA.InitialDirectory = "Desktop"
DialogA.Title = "Select a File";

if (DialogA.ShowDialog() == DialogResult.OK)
{
FileTxt1.Text = DialogA.FileName;
}
else if (FileTxt1.Text == "")
{
MessageBox.Show("Please Select a File");
}
}

private void BtnBrwse2_Click(object sender, EventArgs e)
{
OpenFileDialog DialogB = new OpenFileDialog();
DialogB.CheckFileExists = true;
DialogB.Title = "Select a file";
if (DialogB.ShowDialog() == DialogResult.OK)
{
FileTxt2.Text = DialogB.FileName;
}
else if (FileTxt2.Text == "")
{
MessageBox.Show("Please Select a File");
}
}

private void Btn_cmpare_Click(object sender, EventArgs e)
{
string filename1 = FileTxt1.Text;
string filename2 = FileTxt2.Text;
//string filename3 = FileTxt3.Text;
string file1_sheet = GetExcelSheets(filename1);
string file2_sheet = GetExcelSheets(filename2);
//string file3_sheet = GetExcelSheets(filename3);
String sConnectionString1 = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + filename1 + ";" +
"Extended Properties=Excel 12.0;";
String sConnectionString2 = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + filename2 + ";" +
"Extended Properties=Excel 12.0;";
// Create connection object by using the preceding connection string.
OleDbConnection objConn = new OleDbConnection(sConnectionString1);
// Open connection with the database.
objConn.Open();
// The code to follow uses a SQL SELECT command to display the data from the worksheet.
// Create new OleDbCommand to return data from worksheet.
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + file1_sheet + "$]", objConn);
// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
//Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;
// Create new DataSet to hold information from the worksheet.
DataSet objDataset1 = new DataSet();
objAdapter1.Fill(objDataset1, "XLData");
DataTable dt1 = objDataset1.Tables[0];
objConn.Close();
objConn = new OleDbConnection(sConnectionString2);
// Open connection with the database.
objConn.Open();
// The code to follow uses a SQL SELECT command to display the data from the worksheet.
// Create new OleDbCommand to return data from worksheet.
objCmdSelect = new OleDbCommand("SELECT * FROM [" + file2_sheet + "$]", objConn);
// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
objAdapter1 = new OleDbDataAdapter();
// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;
// Create new DataSet to hold information from the worksheet.
objDataset1 = new DataSet();
// Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "XLData");
DataTable dt2 = objDataset1.Tables[0];
// Clean up objects.
objConn.Close();
DataTable dtresult = new DataTable();
dtresult = Comparedata(dt1, dt2);

}
public DataTable Comparedata(DataTable Firsttb, DataTable Secondtb)
{
Firsttb.TableName = "FirstTable";
Secondtb.TableName = "SecondTable";
//Create Empty Table
DataTable table = new DataTable("Difference");
try
{
//Must use a Dataset to make use of a DataRelation object
using (DataSet ds = new DataSet())
{
//Add tables
ds.Tables.AddRange(new DataTable[]
{
Firsttb.Copy(), Secondtb.Copy()});
DataColumn[] firstcolumns = new DataColumn[ds.Tables[0].Columns.Count];
for (int i = 0; i < firstcolumns.Length; i++)
{
firstcolumns[i] = ds.Tables[0].Columns[i];
}
DataColumn[] secondcolumns = new DataColumn[ds.Tables[1].Columns.Count];
for (int i = 0; i < secondcolumns.Length; i++)
{
secondcolumns[i] = ds.Tables[1].Columns[i];
}
//Create DataRelation
DataRelation r = new DataRelation(string.Empty, firstcolumns, secondcolumns, false);
ds.Relations.Add(r);

for (int i = 0; i < Firsttb.Columns.Count; i++)
{
table.Columns.Add(Firsttb.Columns[i].ColumnName, Firsttb.Columns[i].DataType);
}
//If First Row not in Second, Add to return table.

table.BeginLoadData();

foreach (DataRow parentrow in ds.Tables[0].Rows)
{
DataRow[] childrows = parentrow.GetChildRows(r);
if (childrows == null || childrows.Length == 0)

table.LoadDataRow(parentrow.ItemArray, true);
}
table.EndLoadData();
}

}
catch (Exception ex)
{
}
string fileName = "Result";
ExportDataTableToExcel(table, fileName);
return table;
}

private void ExportDataTableToExcel(DataTable dt, string filename)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;

xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
for (i = 0; i < dt.Columns.Count; i++)
{
xlWorkSheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}

for (i = 0; i < dt.Rows.Count; i++)
{
for (j = 0; j < dt.Columns.Count; j++)
{
xlWorkSheet.Cells[i + 2, j + 1] = dt.Rows[i][j];
}
}
try
{
xlWorkBook.SaveAs(filename + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public string GetExcelSheets(string excelFileName)
{
Microsoft.Office.Interop.Excel.Application excelFileObject = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workBookObject = null;
workBookObject = excelFileObject.Workbooks.Open(excelFileName, 0, true, 5, "", "", false,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"",
true,
false,
0,
true,
false,
false);
Excel.Sheets sheets = workBookObject.Worksheets;

// get the first and only worksheet from the collection of worksheets
Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
MessageBox.Show(worksheet.Name);

return worksheet.Name;
}
}
}

Monday, June 3, 2013

Purpose of DateTime.ParseExact Method

Converts the specified string representation of a date and time to its DateTime equivalent. The format of the string representation must match a specified format exactly.

Converts the specified string representation of a date and time to its DateTime equivalent using the specified format and culture-specific format information. The format of the string representation must match the specified format exactly.

Supported by the .NET Compact Framework.

[Visual Basic] Overloads Public Shared Function ParseExact(String, String, IFormatProvider) As DateTime

[C#] public static DateTime ParseExact(string, string, IFormatProvider);

[C++] public: static DateTime ParseExact(String*, String*, IFormatProvider*);

[JScript] public static function ParseExact(String, String, IFormatProvider) : DateTime;

DateTime.ParseExact("9/1/2009", "M'/'d'/'yyyy", null);

DateTime.ParseExact("9/1/2009", "M/d/yyyy", CultureInfo.InvariantCulture);

DateTime.Parse("9/1/2009", new CultureInfo("en-US"));

The parameters passed to ParseExact are as follows:
1) The actual date, StartDateTime
2) The format the StartDateTime is CURRENTLY in,
3) The culture (or null)

So what you want to try is this:

//
// Get our date
//
string StartDateTime = "11/24/2006 23:59";
//
// Create a datetime object
//
DateTime myDate = DateTime.ParseExact(StartDateTime,"MM/dd/yyyy H:mm",
null);
//
// Capture our desired format in a string
//
string FormattedDate = myDate.ToString("yyyy-MM-dd HH:mm tt");
//
// Now we can use it as we please
//
Console.WriteLine(FormattedDate);

How to convert Numeric values into Roman numbers

The simple way for converting numeric values to Roman numbers :

public static string NumberToRoman(int number)
{
//taking string builder here, so we can append roman numbers to this string builder
StringBuilder result = new StringBuilder();
if (number <= 0 || number > 3999)
{
//throw new ArgumentException("Value must be in the range 0 – 3,999.");
Console.WriteLine("Value must be in the range 0 to 3,999....!");
Console.ReadLine();
}
else
{
int[] values = new int[] { 1000, 900, 500, 400, 100, 90, 50, 40, 10, 9, 5, 4, 1 };
string[] numerals = new string[] { "M", "CM", "D", "CD", "C", "XC", "L", "XL", "X", "IX", "V", "IV", "I" };

// Loop through each of the values to diminish the number
for (int i = 0; i < values.Length; i++)
{
// If the number being converted is less than the test value, append the corresponding numeral or numeral pair to the resultant string(string builder)
while (number >= values[i])
{
number -= values[i];
result.Append(numerals[i]);
}
}
Console.WriteLine(result);
Console.ReadLine();
}
return result.ToString();
}

Saturday, June 1, 2013

How to print only the screen without taskbar

Press "Alt+PrintScreen" instead of print screen inorder to get only the screen without Taskbar.

How to Import data from grid to pdf and excel in VB.Net

Import data from grid to pdf :To import data from grid to pdf we can use third part control namely "iTextSharp" so we have to download this "iTextSharp.dll" and add to to your project

http://sourceforge.net/projects/itextsharp/ you can download the iTextSharp.dll from this link

Imports iTextSharp
Imports iTextSharp.text
Imports iTextSharp.text.pdf

Imports iTextSharp.text.html.simpleparser

Protected Sub imgexportpdf_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles imgexportpdf.Click
Using sw As New StringWriter()
Using hw As New HtmlTextWriter(sw)
'To Export all pages
gvCustomer.AllowPaging = False
BindCustomerDetails()

gvCustomer.RenderControl(hw)
Dim sr As New StringReader(sw.ToString())
Dim pdfDoc As New Document(PageSize.A2, 10.0F, 10.0F, 10.0F, 0.0F)
Dim htmlparser As New HTMLWorker(pdfDoc)
PdfWriter.GetInstance(pdfDoc, Response.OutputStream)
pdfDoc.Open()
htmlparser.Parse(sr)
pdfDoc.Close()

Response.ContentType = "application/pdf"
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.pdf")
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.Write(pdfDoc)
Response.[End]()
End Using
End Using
End Sub

Import data from grid to Excel:

Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Drawing

Imports System.Data.OleDb

Protected Sub imgexportexcel_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles imgexportexcel.Click
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition", "attachment;filename=CustomerDetails.xls")
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"
Using sw As New StringWriter()
Dim hw As New HtmlTextWriter(sw)

'To Export all pages
gvCustomer.AllowPaging = False
BindCustomerDetails()

gvCustomer.HeaderRow.BackColor = Drawing.Color.GreenYellow

For Each cell As TableCell In gvCustomer.HeaderRow.Cells
cell.BackColor = Drawing.Color.GreenYellow
Next
For Each row As GridViewRow In gvCustomer.Rows
row.BackColor = Drawing.Color.White
For Each cell As TableCell In row.Cells
If row.RowIndex Mod 2 = 0 Then
cell.BackColor = gvCustomer.AlternatingRowStyle.BackColor
Else
cell.BackColor = gvCustomer.RowStyle.BackColor
End If
cell.CssClass = "textmode"
Next
Next

gvCustomer.RenderControl(hw)
'style to format numbers to string
Dim style As String = "<style> .textmode { } </style>"
Response.Write(style)
Response.Output.Write(sw.ToString())
Response.Flush()
Response.[End]()
End Using

End Sub