Update the Excel sheet using c#

Some time I got the requiremnt where I need to update the excel sheet corresponding to testcases through the code whether the test case is failed or passed while automation is running and send the updated testcases excel with current status of testcases through the email at the end of automation code.
i hope that this would be very usefull for you.

Below is the code for that purpose :
----------------------------------------------------------------
//write to excel sheet
public void writeToExcelsheet(int rowNumber, string status)
{
try
{
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = Program.excelSheetDeclaration;
using (DbCommand command = connection.CreateCommand())
{
command.CommandText =
"Update [sheet1$] Set Status =\"" + status + "\" WHERE TestCaseID ="+rowNumber;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
}
}
catch (Exception ex)
{
//
}
} //end of writeToExcelsheet method.
----------------------------------------------------
Thanks!!

How to Read data from Excel sheet using DataSet

While coding the Automation the situation came that we need to read data from excel sheet and store at one place to use that data through out the code and storing the data from excel sheet to the data set is a good option. Reading the data one by one from excel sheet would impact the performance of the automation code.
So below is the code which will read the data from excel sheet and would store in to dataset.
--------------------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Data.Common;
using System.Data.Sql;
//
namespace ReadFromExcel
{
//Class to read excel sheet and store in to dataset
class Program
{
static void Main(string[] args)
{
//Local variable to store the excelsheet location
string excelSheetLocation = "c:\\TestCases.xls";
//Local variable to store the connection string to talk with excel sheet
string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelSheetLocation + ";Extended Properties=Excel 8.0";
//Declare a dataset
DataSet myDataSet=new DataSet();
//Declare oledb connection
OleDbConnection con = new OleDbConnection(excelConnectionString);
//Open the connection to communicat with excel sheet
con.Open();
//Create Dataset and fill with imformation from the Excel Spreadsheet for easier reference
OleDbDataAdapter myCommand = new OleDbDataAdapter(" SELECT * FROM [sheet1$]", con);
//filled the dataset with the data of excel sheet
myCommand.Fill(myDataSet);
//close the connection
con.Close();
//show the data on console window from dataset
//Total Number of rows in excel sheet
int totalRow = myDataSet.Tables[0].Rows.Count;
//trace through each rows
for (int i = 0; i < totalRow; i++)
{
//trace through each coloumn
for (int j = 0; j < myDataSet.Tables[0].Columns.Count;j++ )
{
//Show the data on to console window
Console.WriteLine(myDataSet.Tables[0].Rows[i][j].ToString());
} //end of for loop
} //end of for loop
} //end of main
} //end of class
} //end
----------------------------------------------------------------------------------------------
Thanks!!

Blogger Buzz: Blogger integrates with Amazon Associates

Blogger Buzz: Blogger integrates with Amazon Associates

How to get Source code of the target web page

Sometimes during the automation development I faced lots of problem when the source code functionality is disable of any target web page.

Then I thought do we have any alternative to solve this problem?
I searched/goggled somewhere here and there. At last I got some snip of code.

I customize the code in such a way that it helped me in 2 ways.
1. When source code functionally is disable of the target web page/web site.
2. When something failed (no control found) during running of automation code then just get the source code and save it in text file. Latter it would help me to find out why my automation code failed or Check for the controls id/value/text etc on that web page.

Below is the code for that

public void GetSourceCode()
{
try
{
//Target website/web page
string strUrlOfWebPage = "http://jawedm.blogspot.com";
//Location to store source code in text file.
string strTextFileLocation = "c://SourceFile.txt";
System.Net.WebRequest request = System.Net.HttpWebRequest.Create(urlOfWebPage);
System.Net.HttpWebResponse response = (System.Net.HttpWebResponse)
request.GetResponse();
System.IO.Stream responseStream = response.GetResponseStream();
System.IO.StreamReader readerSource = new System.IO.StreamReader
(responseStream);
string strSourceForTextFile = readerSource.ReadToEnd();
readerSource.Close();
System.IO.File.WriteAllText(strTextFileLocation, strSourceForTextFile);

}
catch(Exception ex)
{
Console.WriteLine(ex.Message.ToString());
}
}

Thanks ,
Md. jawed

FXCop an Automated tool for code review

Introduction

Thanks to FXCop teams and Microsoft for presenting FXCop tool to us for analysis of code. This is widely used for code review purpose. And which analyze compiled .NET assemblies for compliance. It is a tool to check your code against the rules or guidelines defined by FXCop engine and/or your own rules. It is freely available from Microsoft. You can download it from here.

FXCop supports assemblies written in C#, VB.NET, C++/CLI, and other managed .NET languages.

Thanks to NITIN for explaining FXCop tool which helped me to go deep inside FXCop functionality, purpose and integration with VS.Net.

FXCop Aims

FXCop aims to make it easy to Act in accordance with someone's rules, commands, or wishes by scanning compiled assemblies/dll/exe and creating a report that listed out the details coding violations and suggests code fixes and improvements.

Modes of Operation

FXCop can be operated in two modes:

1. Through GUI and

2. Through command-line mode.

Behind the screen

FXCop (1.30 and higher) performs analysis through a technique called Introspection. The use of the Introspection engine allows for much faster/ richer analysis infrastructure and supports multithreaded analysis to read the code of your assembly and process against predefined rules categorized as naming conventions, library design, localization, security, performance, portability, interoperability etc.

Every assembly in .NET has metadata, which describes the assembly and all types used in the assembly.

FXCop uses this metadata to learn what is going on in the code. In addition, it examines the Microsoft Intermediate Language (MSIL) generated when code is compiled. The combination of examining the metadata and MSIL provides FXCop plenty of information to gain an understanding of what the code is doing. It compares your code against the rules and generates a message for every instance where code doesn't conform to the rules.

Rules defined by FXCop

1. Design Rules

2. Globalization Rules

3. Interoperability Rules

4. Mobility Rules

5. Naming Rules

6. Performance Rules

7. Portability Rules

8. Security Rules

9. Usage Rules

Last but not the least: The FXCop Introspection engine can be leveraged to develop custom FXCop rules specific to the project needs.

Report generation

The report generated by an FXCop run detail the violations and the files, where they are found; as shown in the below figure. These are then fixed by developers; the code is then committed and FXCop is re-run to check if the code has successfully passed through all the checks.

fig: Report generated by FXCop.

Integrate with Visual studio

In the Visual Studio .NET IDE, navigate to Tools -> External Tools option;

on click of External tool one new dialog box would open.click on Add button to display the new configuration dialog box as below:

fig: Adding FXCop to External toll

On new windows set the parameter as below:
1. ADD: Click on ADD button to add an Fxcop as external tool
2. Title: provide an external toll name (something like FxCop).
3. Command: To set the value of the Command text box, browse to the location where FxCop
is installed, and select FxCopCmd.exe. (in my case it is at
C :\Program Files\Microsoft FxCop 1.36\FxCopCmd.exe)

4. Argument : In the Arguments text box, specify /console (or /c) in addition to any command-

line options you use when running the analysis from the command line. The

value of a command-line option must be enclosed in quotation marks if the value

contains any spaces. in my case i have set the value as :

/c /f:"D:\NWAAutomationCode\bin\Debug" /r:"C:\Program
Files\Microsoft FxCop 1.36\Rules" /consolexsl:"C:\Program
Files\Microsoft FxCop 1.36 \Xml\VSConsoleOutput.xsl"

Some key arguments explained below:

• /c direct FXCop analysis to console or output window in IDE.

• /f: - where indicates the executable (EXE), or Dynamic Link
Library (DLL), or a directory to browse for target assemblies
("D:\NWAAutomationCode\bin\Debug").

• /p: - indicates the FXCop project as the input parameter, and refers to the
Corresponding FXCop project file.

• /s - Indicates FXCop to include the summary report with the informational message. • /r: - Indicates the directory location where FXCop rules library is saved (typically \Microsoft FxCop 1.36\Rules). This will load the entire rules library (.dll files) from the path, during the run.

5. Initial Directory : Set the Initial Directory to the location where FxCopCmd should start.
("C:\Program Files\Microsoft FxCop 1.36")

6. Select the Use Output window check box.

7. Click OK.

at last we have integarted FXCop to our VS.Net.
Now to Run this FXCop for Code Review analysis Click on Tools, under drop down menu select FXCop as shown below:

fig: Run FXcop through VS.Net

On click of FXCop ,the FXCop Engine would start Analysis our code w.r.t selected Rules and The analysis results will be posted in the "Output Window" of VS with the rule name, code file name, and error details as shown below ,The developer can click on the relevant FXCop error and navigate to the source-code to understand the problem and then fix it.

fig: OutPut Report by FXCop

Hope it would be Usefull for all of you.

Comments and suggestion always welcome.
Please feel free to put some comments.

References:
1. http://msdn.microsoft.com/en-gb/magazine/cc188721.aspx
2. http://www.binarycoder.net/fxcop/html/tutorial.html
3. http://www.codeproject.com/KB/dotnet/FxCopIntegrateVSNET.aspx
4. http://articles.techrepublic.com.com/5100-10878_11-6152637.html
Thanks,
Md.Jawed