Monday 26 January 2009

Export SQL Server tables to Excel in c#

This piece of code executes a sql query on a sql server database and returns the result into a excel workbook (with column headings included).

Remember to add the following references (Project > Add Reference > COM)

Microsoft Excel 12.0 Object Library (or an older version)


using System;
using System.Collections.Generic;

using System.Linq;
using System.Text;
using System.Data.SqlClient;

using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace ExportDBTablesToExcel
{
class ExportToExcel
{

private Excel.Application app ;

private Excel.Workbook workbook;
private Excel.Worksheet previousWorksheet;

private Excel.Range workSheet_range;

private string folder;

private static string CONNECTION_STR = "Data Source=(local);Database=DATABASE_NAME;"
+ "Integrated Security=SSPI;";


public ExportToExcel(string folder)
{

this.folder = folder;

this.app = null;
this.workbook = null;

this.previousWorksheet = null;
this.workSheet_range = null;



createDoc();
}

private void createDoc()

{
try
{
app = new Excel.Application();

app.Visible = false;
workbook = app.Workbooks.Add(1);


}
catch (Exception e)
{
Console.Write(e.ToString());

}
finally
{
}
}

public void shutDown()

{
try
{
workbook = null;

app.Quit();
}
catch (Exception e)

{
Console.Write(e.ToString());
}

finally
{
}
}

public void ExportTable(string query,string sheetName)

{
SqlConnection myConnection = new SqlConnection(CONNECTION_STR);

SqlDataReader myReader = null;

try
{

Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets.Add(Missing.Value, Missing.Value, 1, Excel.XlSheetType.xlWorksheet);

worksheet.Name = sheetName;

previousWorksheet = worksheet;



myConnection.Open();


SqlCommand myCommand = new SqlCommand(query,
myConnection);

myReader = myCommand.ExecuteReader();

int columnCount = myReader.FieldCount;

for (int n = 0; n < columnCount; n++)

{
Console.Write(myReader.GetName(n) + "\t");

createHeaders(worksheet, 1, n + 1, myReader.GetName(n));

}

int rowCounter = 2;
while (myReader.Read())

{
for (int n = 0; n < columnCount; n++)

{
Console.WriteLine();
Console.Write(myReader[myReader.GetName(n)].ToString() + "\t");

addData(worksheet, rowCounter, n + 1, myReader[myReader.GetName(n)].ToString());

}
rowCounter++;
}


}

catch (Exception e)
{
Console.WriteLine(e.ToString());

}
finally
{
if (myReader!=null && !myReader.IsClosed)

{
myReader.Close();
}

if (myConnection != null)

{
myConnection.Close();
}

myReader = null;
myConnection = null;
}

}


public void createHeaders(Excel.Worksheet worksheet,int row, int col, string htext)

{
worksheet.Cells[row, col] = htext;


}

public void addData(Excel.Worksheet worksheet,int row, int col, string data)

{
worksheet.Cells[row, col] = data;

}


public void SaveWorkbook(){

String folderPath = "C:\\My Files\\" + this.folder ;

if (!System.IO.Directory.Exists(folderPath)) {

System.IO.Directory.CreateDirectory(folderPath);

}

string fileNameBase = "db" ;
String fileName = fileNameBase;
string ext = ".xlsx" ;
int counter = 1 ;

while (System.IO.File.Exists(folderPath+fileName+ext)){

fileName = fileNameBase + counter;
counter++ ;
}

fileName = fileName +ext ;

string filePath = folderPath + fileName ;

try
{
workbook.SaveAs(filePath, Excel.XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

}
catch (Exception e)
{
Console.WriteLine(e.ToString());

}
}


static void Main(string[] args)

{

ExportToExcel export = new ExportToExcel(args[0]);

export.ExportTable("SELECT * FROM t_table1","t_table1");
export.ExportTable("SELECT * FROM t_table2","t_table2");
export.ExportTable("SELECT * FROM t_table3","t_table3");
export.SaveWorkbook() ;

export.shutDown();
}





}

}


This code is based on the following links:

http://www.codeproject.com/KB/cs/Excel_and_C_.aspx

and
http://www.codeproject.com/KB/database/sql_in_csharp.aspx?fid=16002&df=90&mpp=25&noise=3&sort=Position&view=Quick&fr=26&select=1490011

Wednesday 21 January 2009

Read an Excel document and Send an email from outlook in c# .net

This is a handy example if you need to read an excel document and/or send an email from outlook in some c# code.

Remember to add the following references (Project > Add Reference > COM)

Microsoft Excel 12.0 Object Library
Microsoft Office 12.0 Object Library
Microsoft Outlook 12.0 Object Library

if you have older versions of office , they should work too.

using System;
using System.Collections.Generic;

using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;

using Outlook = Microsoft.Office.Interop.Outlook;
using Microsoft.Office.Core;

namespace StatusNotifier
{
class StatusNotifier
{
static void Main(string[] args)

{
string Path = @"D:\MyExcelFile.xls";
// initialize the Excel Application class

Excel.ApplicationClass app = new Excel.ApplicationClass();

// create the workbook object by opening the excel file.
Excel.Workbook workBook = app.Workbooks.Open(Path,
0,
true,
5,
"",
"",
true,
Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);

// get the active worksheet using sheet name or active sheet
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];

int index = 0;
// This row,column index should be changed as per your need.
// i.e. which cell in the excel you are interesting to read.
object rowIndex = 2;


object colA = 1;
object colB = 2 ;


StringBuilder message = new StringBuilder();

message.AppendLine("My Message");

message.AppendLine("--------------");
message.AppendLine("");


//----------------------------

//READ EXCEL WORKBOOK
//------------------------------

try
{
while (((Excel.Range)workSheet.Cells[rowIndex, colDateRaised]).Value2 != null)

{
rowIndex = 2 + index;
DateTime dateRaised = DateTime.FromOADate(double.Parse(((Excel.Range)workSheet.Cells[rowIndex, colA]).Value2.ToString()));

string description = ((Excel.Range)workSheet.Cells[rowIndex, colB]).Value2.ToString();



message.AppendLine(rowIndex + " DateRaised :" + dateRaised.ToShortDateString() + "Description: " + description) ;



index++;
}
}
catch (Exception ex)

{
app.Quit();
Console.WriteLine(ex.Message);

}

message.AppendLine("For more inforamtion please see:");
message.AppendLine(Path);

Console.WriteLine("Message:\n " + message);

//----------------------------
//CREATE OUTLOOK MESSAGE
//------------------------------

try
{
// Create the Outlook application by using inline initialization.

Outlook.Application oApp = new Outlook.Application();

//Create the new message by using the simplest approach.
Outlook.MailItem oMsg = (Outlook.MailItem)oApp.CreateItem(Outlook.OlItemType.olMailItem);

//Add a recipient.
// TODO: Change the following recipient where appropriate.
Outlook.Recipient oRecip = (Outlook.Recipient)oMsg.Recipients.Add("someone@somewhere.com");

oRecip.Resolve();

//Set the basic properties.
oMsg.Subject = "Status Update";

oMsg.Body = message.ToString();

//Add an attachment.

// TODO: change file path where appropriate
String sSource = "C:\\setupxlg.txt";
String sDisplayName = "MyFirstAttachment";

int iPosition = (int)oMsg.Body.Length + 1;

int iAttachType = (int)Outlook.OlAttachmentType.olByValue;

Outlook.Attachment oAttach = oMsg.Attachments.Add(sSource, iAttachType, iPosition, sDisplayName);

// If you want to, display the message.
// oMsg.Display(true); //modal

//Send the message.
oMsg.Save();

oMsg.Send();

//Explicitly release objects.
oRecip = null;

oAttach = null;
oMsg = null;
oApp = null;

}

// Simple error handler.
catch (Exception e)
{

Console.WriteLine("{0} Exception caught: ", e);
}



}
}
}

This code is based upon examples from the following links:

Excel: http://www.codeproject.com/KB/cs/Excel_Application_in_C_.aspx
Outlook: http://support.microsoft.com/kb/310263

Tuesday 20 January 2009

Clearcase update from command line showing dialog and windows

If you like doing things from the command line but you want the update progress bar , etc to pop up, then this is quite useful.

c:
cd "C:\Program Files\Rational\ClearCase\bin"
"clearviewupdate" -quick -window 1f00f4 -windowmsg c14e -ws "Path Here"


If you don't want the update dialog to pop up you can use:

cleartool update "Path Here"

Thursday 15 January 2009

Use Beyond Compare in ClearCase

If you use any kind of source control system, one of the things you need to do is compare differences or "do diffs" or different versions of a file. However the the compare tool in clearcase is crap.

Beyond Compare is probably the the best file (and folder) comparison I have come across. It can even compare zip files.

If you are tired of using the shitty compare tool that is a default in clearcase there is indeed a way to switch this to use beyond compare instead

1. Go into the "C:\Program Files\Rational\ClearCase\lib\mgrs" directory.

2. Create a backup of the file named "map".

3. Edit "map" and change the line:
text_file_delta  xcompare   ..\..\bin\cleardiffmrg.exe

to:
text_file_delta  xcompare   C:\Progra~1\Beyond~1\bc2.exe


Thats it, next time you compare two versions of a file in the version tree or you compare with previous version, beyond compare will be used instead.

Alternately if you want to use winmerge (which is opensource and free) , then it gives you an option to replace the default viewer when you install it.

Tuesday 13 January 2009

25 most dangerous programming errors

A group of software people from various firms brought together by the NSA have sat around and come up with a list of the most dangerous 25 programming errors . If you been a developer for more than 5 minutes you have probably come across half of these, but this can be used as a handy checklist.

In the words of Paul Kurtz, executive director of a group called the Software Assurance Forum for Excellence in Code "Now, with the Top 25, we can spend less time working with police after the house has been robbed and instead focus on getting locks on the doors before it happens"

Here's my cheap and cheerful run down of through the list.

Each heading links to the site where there is more detail on each , which includes discussion, prevention and mitagations, attack patterns against the error.

15-Jan-08: I haven't had time to comment on each error in the list, but I will do at some point

1. Improper Input Validation
e.g. Alpha characters aren't allowed to be entered in numeric fields, stuff like that
2. Improper Encoding or Escaping of Output
Using stored procs and using correct character encoders
3. Failure to Preserve SQL Query Structure (aka 'SQL Injection')
Make sure a user can't enter sql into a text field that will
4. Failure to Preserve Web Page Structure (aka 'Cross-site Scripting')
5. Failure to Preserve OS Command Structure (aka 'OS Command Injection')
6. Cleartext Transmission of Sensitive Information
7. Cross-Site Request Forgery (CSRF)
8. Race Condition
9. Error Message Information Leak
10. Failure to Constrain Operations within the Bounds of a Memory Buffer
11. External Control of Critical State Data
12. External Control of File Name or Path
13. Untrusted Search Path
14. Failure to Control Generation of Code (aka 'Code Injection')
15. Download of Code Without Integrity Check
16. Improper Resource Shutdown or Release
17. Improper Initialization
18. Incorrect Calculation
19. Improper Access Control (Authorization)
20. Use of a Broken or Risky Cryptographic Algorithm
21. Hard-Coded Password
22. Insecure Permission Assignment for Critical Resource
23. Use of Insufficiently Random Values
24. Execution with Unnecessary Privileges
25. Client-Side Enforcement of Server-Side Security

Tuesday 6 January 2009

Remote Debugging a .NET app with Visual Studio 2008 (9)

This is where you need to debug an application in visual studio, but the process is running on some other machine (i.e. not on your local box). A good example is when a tester is running something and you want to connect to their box to debug it.

This in fact turned out to be a pretty painless process.

First on the machine where the app is running that you need to debug you need to run msvsmon.exe . I found this in

Program Files
\Microsoft Visual Studio 9.0\Common7\IDE\Remote Debugger\x86 .

This will bring up the "Visual Studio Remote Debugging Monitor".

More than likely you will be debugging a machine on your network. So goto Tools>Permissions, and add the user of the machine where visual studio is running.

Now run the application you intened to debug

Now in Visual Studio goto Debug>Attach to Process . In the box "Qualifier" , enter the machine name of where your application is running and hit enter.

A list of processes will appear in the "Available Processes" box below. On the other machine in the remote debugging monitor you will see a log that you have connected.

Select the process which corresponds to the app you want to debug from Available processes and click on attach. And that's it you are now in debug mode and you can set your breakpoints and whatever and debug your app in the usual way!

For more info look at this article from microsoft :

http://support.microsoft.com/kb/910448