Month: February 2010

Email DataTable as Excel Attachment

The following code allows a developer to convert a DataTable into a MemoryStream so that it can be attached to an e-mail. It accepts a DataTable and binds it to a GridView. Next, it renders the GridView into a StringWriter object. To convert a string into a byte array I used the Encoding object and wrote the byte array into the MemoryStream. Attaching the MemoryStream to an email is easy. Read the usage snippet below.

Usage:

DirectExcel excel = new DirectExcel();

System.IO.MemoryStream ms = excel.ExportToStream(myDataTable);

Attachment attachFile = new Attachment(ms, “filename.xls”, “application/vnd.ms-excel”);

MailMessage mail = new MailMessage();

mail.Attachments.Add(attachFile);

 

 

In order for the above snippet to work, you will need to copy the ExportToStream method below and paste it in your DirectExcel Class. If you don’t already have a DirectExcel Class click here to copy it from my previous post.

 

public System.IO.MemoryStream ExportToStream(DataTable objDt)

{

    //remove any columns specified.

    foreach (string colName in ColumnsToRemove)

    {

        objDt.Columns.Remove(colName);

    }

 

    this.DataSource = objDt;

    this.DataBind();

 

    System.IO.StringWriter sw = new System.IO.StringWriter();

    System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(sw);

    this.RenderControl(hw);

    string content = sw.ToString();

    byte[] byteData = Encoding.Default.GetBytes(content);

 

    System.IO.MemoryStream mem = new System.IO.MemoryStream();

    mem.Write(byteData, 0, byteData.Length);

    mem.Flush();

    mem.Position = 0; //reset position to the begining of the stream

    return mem;

} 

SQL Reporting Services 2005 – Print Report (Server-Side)

Before I begin this article I want to make sure it’s clear that I created this code a few years ago based on research I found on Google and MSDN (so I can’t take full credit for the implementation). This version of the code is customized and encapsulated in a standalone class. I believe this to be extremely useful for many developers out there.

 

If you’ve used SQL Server Reporting Services you know that printing a report programmatically isn’t as easy as calling ReportServer.Print(). This is why I put together a class that provides this functionality. The amount of code you have to write to print a report via the SQL Reporting Services Web Service can be a mind blowing task. Before we go any further, here is a link to the RSPrintManager class.

 

The first thing you’ll want to do is Add a Web Reference to your project or create a proxy class. The URL you should add a web reference to is as follows (replace <ReportServerName> with the name of you SQL Reporting Server):

 

·         http://<ReportServerName>/ReportServer/ReportExecution2005.asmx

 

Optionally, you can update the hardcoded URL so you don’t have to set it when you use the RSPrintManager:

private string m_url = “http://<ReportServerName>/ReportServer/ReportExecution2005.asmx”;

 

If you use the default constructor, you windows credentials will be used for Report Server Authentication. Otherwise, you can use the overloaded constructor to pass use different credentials. Let’s take a look at the use of this class:

 

// Create an instance of RSPrintManager w/the default windows credentials

RSPrintManager rsPintManager = new RSPrintManager();           

// this property doesn’t have to be set if you changed the hardcorded value of

// the “m_url” member in RSPrintManager

rsPintManager.Url = “http://<ReportServerName>/ReportServer/ReportExecution2005.asmx”;

// call the PrintReport method and pass the report path

rsPintManager.PrintReport(@“/CustomerReports/OpenWork”);

 

As you can see the use of this object is extremely easy to use. If your report contained parameters you’d write the code as follows:

// Create an instance of RSPrintManager w/the default windows credentials

RSPrintManager rsPintManager = new RSPrintManager();           

// this property doesn’t have to be set if you changed the hardcorded value of

// the “m_url” member in RSPrintManager

rsPintManager.Url = “http://<ReportServerName>/ReportServer/ReportExecution2005.asmx”;

// declare an array of ParameterValue objects based on the number of parameters in your report…

// in this case I have the parameters in a datagridview so I use the Count property to set the size

ReportExecution2005.ParameterValue[] @params = new ReportExecution2005.ParameterValue[DataGridView1.Rows.Count];

// Loop through each record in the datagridview and set the parameters accordingly

for (int i = 0; i <= @params.Length – 1; i++)

{

      @params[i] = new ReportExecution2005.ParameterValue();

      @params[i].Name = DataGridView1.Rows[i].Cells[“ParameterName”].Value.ToString();

      @params[i].Value = DataGridView1.Rows[i].Cells[“ParameterValue”].Value.ToString();

}

// call the PrintReport method and pass the report path

rsPintManager.PrintReport(@“/CustomerReports/OpenWork”, @params);

 

// Optionally you can use the overload to print a specific range of pages

// rsPintManager.PrintReport(@”/CustomerReports/OpenWork”, @params, 1, 5);

 

 

To get a list of parameters for a specific report  in the report server and populate a DataGridView, you’ll need to add a web reference or create a proxy class for the following URL:

 

·         http://<ReportServerName>/ReportServer/ReportService2005.asmx

 

 

// create an instance ReportingService2005  

ReportService2005.ReportingService2005 rs = new ReportService2005.ReportingService2005();

// set the URL

rs.Url = “http://<ReportServerName>/ReportServer/ReportService2005.asmx”;

// get a list of parameters

ReportService2005.ReportParameter[] items = rs.GetReportParameters(@“/CustomerReports/OpenWork”, null, false, null, null);

// Add the parameters to the DataGridView with blank values

foreach (ReportService2005.ReportParameter p in items)

{

      DataGridView1.Rows.Add(p.Name, “”);

}

SQL Server IMAGE DataType to PictureBox

The following sample is great if you want to load an Employee or signature image from your database. I tried to comment it as much as possible so I think the code will be self explanatory.

 

// Load an image into a PictureBox from a SQL Server image datatype

public void LoadImage(PictureBox pic)

{

    // TODO: change connection string

    SqlConnection con = new SqlConnection(“Data Source=<server>; Initial Catalog=<YouDatabaseName>; Integrated Security=SSPI;”);

    // TODO: change query to pull form your own tables

    SqlCommand cmd = new SqlCommand(“select top 1 signature_jpeg from SignOffForm where order_id = @order_id”, con);

    SqlDataReader rdr = null;

    byte[] imgData = null;

    try

    {

        int orderID = 10000;

        cmd.Parameters.AddWithValue(“@order_id”, orderID);

 

        con.Open();

        rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

        if (rdr.Read())

        {

            // make sure our byte array is big enough to store the image

            imgData = new byte[rdr.GetBytes(0, 0, null, 0, int.MaxValue)];

            // retrieve the image from the datareader and store it in a byte array

            rdr.GetBytes(0, 0, imgData, 0, imgData.Length);

            // use a memorystream to read the image

            MemoryStream ms = new MemoryStream(imgData);

            // set the image to the picturebox

            pic.Image = Image.FromStream(ms);

        }

        else

        {

            MessageBox.Show(“No records found in the database.”, “Warning”);

        }

    }

    catch (Exception ex)

    {

        MessageBox.Show(ex.ToString(), “Error”);

    }

    finally

    {

        if (con.State == ConnectionState.Open) con.Close();

    }

}

Use WMI to get your Service Tag

WMI is a powerful tool provided by Microsoft that provides component information and notificaiton. Below is a snippet on how you can query WMI to retrieve the Dell Service Tag of a single computer. You can easily extend this sample via Directory Service (LDAP) to query all PCs in your network.

Please Note:  Make sure you add a reference to System.Management and place using System.Management; to the top of your code file.

We start by setting the computerName string to you PC’s name. You can press and hold window key on your keyboard then press the Pause/Break key to load the System Properties . If Windows XP you’ll want to click on the Computer Name tab. On Windows 7/Vista machines you will see the computer name right on System window that pops up somewhere near in the middle of the screen.

string computerName = “<Your PC Name>”;

Next you’ll want to create a ManagementScope object by passing in the Management Path to the constructor (which concatenates the PC name).

ManagementScope scope = new ManagementScope(@”\\” + computerName  + @”\root\cimv2″);

scope.Connect();

 

Next, you create an ObjectQuery object which accepts a query that looks very much like a standard T-SQL query. Click here to view reference list that’ll help you build your own queries.

 

ObjectQuery query = new ObjectQuery(“Select SerialNumber From Win32_SystemEnclosure”);

 

Next you’ll create a ManagementObjectSearcher object that will be used to actually execute the query.

 

ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query);

 

The Get() method returns the results into the ManagementObjectCollection object.

 

ManagementObjectCollection objColl = searcher.Get();

 

The rest is simple, you loop through each element in the collection and retrieve the SerialNumber since this is where Dell Stores their Service Tags.

 

foreach (ManagementObject o in objColl)

{

Console.WriteLine(computerName + “:” + o[“SerialNumber”].ToString());

}

 

Folder/Directory Sizes Utility

A co-worker (Rob Yacullo) and I wrote the following utility to help us figure out which directories consumed the most drive space. With this tool you can easily figure out which directories are consuming high amounts of disk space without having to drill down each individual directory in Windows Explorer.

The following link is the executable only:

The following link contains the full source code:

This applicaton was built on top of the .NET Framework 3.5.

Enjoy! Wink

-Nuno

Direct Excel Export

Often in the web world developers are asked to add Excel exporting capabilities to their web application or web services. The following demonstration shows you how you can implement a decent solution that is both quick and easy to implement in you own code. I built a class named DirectExcel that inherits the ASP.NET GridView control. The reason it inherits the GridView is because this exporting technique requires that our data be databound to a grid. First lets take a look at a snippet of code on how we can use this class.

 

Usage Snippet:

SqlCommand oCmd = new SqlCommand(“usp_get_data “);

oCmd.CommandType = CommandType.StoredProcedure;

 // The report name will also be used as the filename when saving.

DirectExcel directExcel = new  DirectExcel(“Report Name”); 

// oCmd is a SqlCommand that contains the Query you want executed against the database

 directExcel .Export(oCmd);

 

You will need to add a connectionString key to your web.config named “DBConnectionString“ and make sure the query/SP in the SqlCommand above is valid. Take a quick look at the Export method in code below. You will notice that we are first pulling the data from the database using a SqlDataAdapter and filling a DataTable with that data. Since our class inherits the GridView Class we bind the data to this.DataSource. Next we set some properties to the current context by clearing the response, change the content type, and setting the MsoFormats. Notice how the MsoFormat are embedded in the Style tag; This is because we are going to render the GridView to HTML and we can use these classes to format the columns by hooking into the RowCreated event (but I’ll get into details on this later). Next, we create a StringWriter and pass it into a HtlmTextWriter object. When we call the this.RenderControl() method, we pass in the HtmlTextWriter object which is used to write the grid HTML generated by the RenderControl() method. At this point, the StringWriter object should hold the HTML generated based on the GridView. Now it’s just a matter of writing the StringWriter to the response and the user will be prompted with a Open or Save dialog.

 

 public class DirectExcel : System.Web.UI.WebControls.GridView

{

//MsoFormats should be all on one line, it’s used to format columns

public string MsoFormats = “<style>.TextFormat { mso-number-format:\\@; } .DateFormat { mso-number-format:’mm\\/dd\\/yy’ } .CurrencyFormat { mso-number-format:\”\\0022$\\0022\\#\\,\\#\\#0\\.00\” } .NumberFormat {mso-number-format:0;} .FixedNumberFormat{mso-number-format:Fixed;} .PercentFormat{mso-number-format:0%;} .PercentWithDecimalsFormat{mso-number-format:Percent;}</style>”;

 

                // default filename used when saving

                public string ReportName;

                // columns to remove from exported view

                public List<string> ColumnsToRemove = new List<string>();

 

                public DirectExcel(string strReportName)

                {

                                ReportName = strReportName;

                                // default empty data text to display when grid has zero records

                                this.EmptyDataText = “No data record(s) found for criteria.”;

                }

 

                public DirectExcel(string strReportName, string strEmptyDataText)

                {

                                ReportName = strReportName;

                                this.EmptyDataText = strEmptyDataText;

                }

 

                public void Export(System.Data.SqlClient.SqlCommand oCmd)

                {

                                SqlConnection objConn = new SqlConnection(ConfigurationManager.AppSettings[“DBConnectionString”].ToString());

                                SqlDataAdapter objAdapter = new System.Data.SqlClient.SqlDataAdapter();

                                DataTable objDt = new DataTable();

 

                                oCmd.Connection = objConn;

                                oCmd.CommandTimeout = 300;

 

                                objAdapter.SelectCommand = oCmd;

                                objAdapter.Fill(objDt);

 

                                //remove any columns specified.

                                foreach (string colName in ColumnsToRemove)

                                {

                                                objDt.Columns.Remove(colName);

                                }

 

                                this.DataSource = objDt;

                                this.DataBind();

 

                                //clear the reponse and change content type

                                HttpContext.Current.Response.Clear();

                                HttpContext.Current.Response.AddHeader(“Content-Disposition”, “attachment; filename=\”” + ReportName + “.xls\””);

                                HttpContext.Current.Response.Charset = “”;

                                HttpContext.Current.Response.ContentType = “application/vnd.ms-excel”;

                                //add the mso classes that can be used to format columns

                                HttpContext.Current.Response.Write(this. MsoFormats);

 

                                System.IO.StringWriter tw = new System.IO.StringWriter();

                                System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);

                                this.RenderControl(hw);

                                HttpContext.Current.Response.Write(tw.ToString());

                                HttpContext.Current.Response.End();

                }

 

                  // Used to Format Direct Excel Export

                protected override void OnRowCreated(GridViewRowEventArgs e)

                {

                                base.OnRowCreated(e);

                                if (e.Row.RowType == DataControlRowType.DataRow)

                                {

                                                e.Row.VerticalAlign = VerticalAlign.Top;

                                }

                }

}