C# .NET & RegEx

Create a new Windows Application project in Visual Studio .NET. Open the Form Designer and add the following controls:

1. Button

a. Name: btnTestRegEx

b. Text: Test RegEx

2. TextBox

a. Name: tbxRegEx

b. Dock: Left

c. MultiLine: True

d. Text: Site Name:(?’SiteName’.*)Site Address:(?’SiteAddress’.*)Site Contact:(?’SiteContact’.*)

3. TextBox

a. Name: tbxInput

b. Dock: Left

c. MultiLine: True

d. Text:

Site Name: www.NunoSolutions.com

Site Address:

100 Main Street

MyCity, NJ 012345-1234

Site Contact: Nuno F. Pereira

4. TextBox

a. Name: tbxSiteName

b. MultiLine: False

c. Text: Site Name

5. TextBox

a. Name: tbxSiteAddress

b. MultiLine: False

c. Text: Site Address

6. TextBox

a. Name: tbxSiteContact

b. MultiLine: False

c. Text: Site Contact

You want to make sure your form design view looks something like the following:

Next, double click on the Test RegEx button to hook the Click event. You should now be in the code view for your form and VS.Net should present you with an event declaration similar to the following:

private void btnTestRegEx_Click(object sender, EventArgs e)

{

}

Let’s add the following code to the button Click event. Copy and Paste the following within the scope of btnTestRegEx_Click event. Your code should now look like the following :

private void btnTestRegEx_Click(object sender, EventArgs e)

{

string SiteName = “”, SiteAddress = “”, SiteContact = “”;

Match regExMatch = Regex.Match(textBox1.Text,

myRegExpression, RegexOptions.Singleline);

if (regExMatch.Success)

{

SiteName = regExMatch.Groups[“SiteName”].Value.Trim().ToUpper();

SiteAddress = regExMatch.Groups[“SiteAddress”].Value.Trim().ToUpper();

SiteContact = regExMatch.Groups[“SiteContact”].Value.Trim().ToUpper();

}

tbxSiteName.Text = SiteName;

tbxSiteAddress.Text = SiteAddress;

tbxSiteContact.Text = SiteContact;

}

When you run your application and click the Test RegEx button, the values should properly get parsed and set to their respective textboxes.

 

Here’s how it should look:

Force SSL in IIS7 (via URL Rewrite Module)

In order for this to work you’ll need to install the URL Rewrite module on your machine first.

Just know that after installing the module on your machine might force you to restart IIS and/or the machine itself.

Add the following to your web.config:

<system.webServer>

 

  <rewrite>

    <rules>

      <clear />

      <rule name=Redirect to https stopProcessing=true>

        <match url=(.*) />

        <conditions>

          <add input={HTTPS} pattern=off ignoreCase=true />

        </conditions>

        <action type=Redirect url=https://{HTTP_HOST}{REQUEST_URI} redirectType=Permanent />

      </rule>

    </rules>

  </rewrite>

 

</system.webServer>

 

IIS 7 AppPool NTFS Permissions

Grant Default AppPool identity NTFS permissions

1. Right click the directory you want to grant modfiy permissions to then click Properties

2. Click the Edit button

3. Click Add button

4. Click the Locations button and select the computer name (should be the first node in the treeview) then click OK

5. Enter “IIS AppPool\DefaultAppPool” in the Select User or Groups textbox then click Check Names

6. The value should automatically change to DefaultAppPool

 

Reporting Services RDL Report

The following video shows how you can create a simple RDL report, deploy it to the Report Server, and view it from the Report Manager in SQL Server 2008 Reporting Services:

Video 1 – Create a simple server-side report in Reporting Services.

Video 2 – Export a server-side RDL report from a .NET Windows Application.

Report.cs (6.76 kb) to download Report Class that’s used in video 2.

Click NunoSolutions.7z (69.20 kb) to download the sample project used in the video 2.

One more thing, make sure to update the user credentials with an user that has access to run reports.

Export SRS 2008 Report from Windows Applications

The following code was written and tested against SQL Server 2008 R2 Reporting Services. The helper classes help reduce and simplify the amount of code you’ll have to write (and remember) when exporting reports from SRS. I included the ReportExecutionService proxy class that I generated using the WSDL.exe tool. However, you can add a web reference to it using the GUI tools in VS.NET. For those of you who are interested in the command I used:

Open the Visual Studio 2010 Command Prompt (it should be located in Start>All Programs>Visual Studio 2010>Visual Studio Tools>Visual Studio Command Prompt) and enter the following:

 

·         C:\wsdl http://localhost/reportserver/reportexecution2005.asmx

 

 

Here’s what the code will look like to export a report:

NunoSolutions.Report rpt = new NunoSolutions.Report();

rpt.ReportServerUrl = http://localhost/ReportServer; // Change localhost in this path to your server’s name

rpt.ReportTitle = “My Northwind Customers”; // This value is used as the file name

rpt.ReportFormat = NunoSolutions.REPORT_FORMAT.PDF;

rpt.ReportPath = “/Northwind Reports/Northwind Customers”; // The “/Northwind Reports” part is the folder name in Report Server/Manager

// The credential set below must have access to the report server

NunoSolutions.ReportHelper.UserName = “<YourUserName>”;

NunoSolutions.ReportHelper.Password = “<YourPassword>”;

NunoSolutions.ReportHelper.Domain = string.Empty; // In corporate environments you’ll need to set this

// You can change this method declaration to support a file path input parameter.

// For demo purposes, I have a Folder Browser Dialog popup to prompt you where

// you’d like to save the report file.

NunoSolutions.ReportHelper.Export(rpt);

 

Download the sample source code here:

NunoSolutionsWindows.7z (61.39 kb)

Cool NEW Feauture in SQL Server 2005 – OUTPUT Clause

 

A new Clause was introduced in SQL Server 2005 called “OUTPUT” that enables you to do some pretty cool stuff. For example, let’s say you wanted to create an audit table and wanted it updated each time a query executes an INSERT or UPDATE statement (works with DELETE too) but you don’t want to create a Trigger in your table. This is the perfect scenario where you could use the OUTPUT clause. In the example below, I created a table named employee and an audit table named emp_audit that I’ll be using to store a copy of successfully affected row values from the employee table anytime an INSERT and/or UPDATE is executed:

 

 

CREATE TABLE employee(

      emp_id int IDENTITY(1,1),

      first_name varchar(20),

      last_name varchar(25),

      address1 varchar(50),

      Primary Key (emp_id)

);

 

CREATE TABLE emp_audit(

      emp_id int,

      first_name varchar(20),

      last_name varchar(25),

      address1 varchar(50),

      insert_date datetime

);

 

 

DECLARE @emp_id int

 

INSERT INTO employee(first_name, last_name, address1)

OUTPUT      inserted.emp_id, inserted.first_name, inserted.last_name,

            inserted.address1, getdate() INTO emp_audit

VALUES (‘bobby’, ‘jones’, ‘1234 main st’)

 

/*

The OUTPUT clause will cause the SCOPE_IDENTITY() function to return the IDENTITY value of the emp_audit table (instead of employee) if the emp_audit table has an identity seed column.

*/

SELECT @emp_id = SCOPE_IDENTITY()

 

UPDATE employee

SET first_name = ‘BOB’, address1 = ‘300 broad st’

OUTPUT      inserted.emp_id, inserted.first_name, inserted.last_name,

            inserted.address1, getdate() INTO emp_audit

WHERE emp_id = @emp_id

 

SELECT *

FROM employee

WHERE emp_id = @emp_id

 

SELECT *

FROM emp_audit

WHERE emp_id = @emp_id

 

Another reason to use the OUTPUT clause is to retrieve a list of Identities for an INSERT, UPDATE, or DELETE statement that affects one or more records (especially useful when more than one record gets affected IMO).

 

CREATE TABLE employee2

(

      emp_id int identity(1,1),

      first_name varchar(20),

      last_name varchar(25),

      address1 varchar(50),

      PRIMARY KEY (emp_id)

);

 

CREATE TABLE vendors2

(

      vendor_id int identity(1,1),

      company_name varchar(55),

      address1 varchar(50),

      Primary Key (vendor_id)

);

 

INSERT INTO employee2 VALUES(‘michael’, ‘jackson’, ‘111’)

INSERT INTO employee2 VALUES(‘jane’, ‘doe’, ‘222’)

INSERT INTO employee2 VALUES(‘johnny’, ‘bravo’, ‘333’)

 

 

— Variable Table that will store all the inserted vendor_id values

DECLARE @InsertedVendorIDs TABLE(vendor_id int)

 

INSERT INTO vendors2 (company_name, address1)

 — The line below adds the inserted IDs to a variable table and is the recommended approach by Microsoft in SQL 2005 to retrieve identities since both Scope_Identity()and @@Identity could return incorrect identity values when a parallel query plan is generated (yup, it’s a bug in SQL Server click here to view the MS KB article)

OUTPUT inserted.vendor_id INTO @InsertedVendorIDs

SELECT      first_name + ‘ ‘ + last_name AS company_name, address1

FROM employee2

EXCEPT  — <– another cool query feature that I like but isn’t new. insert only records employees2 that don’t exist in vendors2

SELECT company_name, address1

from vendors2

 

— retrieves all the inserted identity values that were created by the previous query

SELECT * FROM @InsertedVendorIDs

 

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());

}