Microsoft Exam 70-487 Study Notes

Estimate of Number of Exam Questions per Module

Module Qs 1:Overview of Service and Cloud Technologies 1
2: Querying and Manipulating Data Using Entity Framework 4
3: Creating and Consuming ASP.NET Web API Services 3
4: Extending and Securing ASP.NET Web API Services 3
5: Creating WCF Services 7
6: Hosting Services 3
7: Windows Azure Service Bus 1
8: Deploying Services 4
9: Windows Azure Storage 2
10: Monitoring and Diagnostics 1
12: Scaling Services 1
A: Designing and Extending WCF Services 4
B: Implementing Security in WCF Services 2
C: “Classic” XML and ADO.NET 4
D: LINQ 2

Total questions in exam 42 Continue reading “Microsoft Exam 70-487 Study Notes”

401 Unauthorized Request workaround when using HttpWebRequest

If you ever encountered the dreaded “401 unauthorized” error when trying to get the HTML code from a URL on the same domain due to the user from the application pool being different from the user logged in (windows authentication), this short snippet will show you how to work around this issue:

System.Net.HttpWebRequest request = (System.Net.HttpWebRequest)System.Net.WebRequest.Create(url);
                request.Credentials = System.Net.CredentialCache.DefaultCredentials;
                request.AuthenticationLevel = System.Net.Security.AuthenticationLevel.MutualAuthRequested;
                System.Net.HttpWebResponse response  = (System.Net.HttpWebResponse)request.GetResponse();

                if (response.StatusCode == System.Net.HttpStatusCode.OK)
                {
                    System.IO.Stream receiveStream = response.GetResponseStream();
                    System.IO.StreamReader readStream = null;

                    if (response.CharacterSet == null)
                    {
                        readStream = new System.IO.StreamReader(receiveStream);
                    }
                    else
                    {
                        readStream = new System.IO.StreamReader(receiveStream, Encoding.GetEncoding(response.CharacterSet));
                    }

                    string data = readStream.ReadToEnd();
                }

"The file you are trying to open, '[filename]', is in a different format" Excel Error

On a recent project, the client asked me to allow some reports to be exported to Excel (XLS.) I’ve had such a request 1000 times over the years and quickly went to work rendering the grid as HTML, sending its HTML as a response and setting the Response.Header to the Excel MIME type… Simple.

Or so I thought. Turns out that Office 2007 doesn’t like that much. When you open such a spreadsheet in Excel 2007 you get an error like:

“The file you are trying to open, ‘[filename]’, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?”
(Yes | No | Help)

After searching for hours, I finally ran into this document from MS that essentially says this is a “feature” of the new Excel and no matter how much everyone hates it, they won’t fix it. Here’s an excerpt from that document:

“The current design does not allow you to open HTML content from a web site in Excel… So ASP pages that return HTML and set the MIME type to something like XLS to try to force the HTML to open in Excel instead of the web browser (as expected) will always get the security alert… If you use an HTML MIME type, then the web browser will open the content instead of Excel. So there is no good workaround for this case because of the lack of a special MIME type for HTML/MHTML that is Excel specific. You can add your own MIME type if you control both the web server and the client desktops that need access to it, but otherwise the best option is to use a different file format or alert your users of the warning and tell them to select Yes to the dialog.” [Emphasis added]

In other words, give up because there’s no good solution. Here’s some bad solutions you could try, though:

  • Display a message that says something like, “If you are using Office 2007, please select “Yes” from the resulting dialog.”
  • Include a registry script that the user can optionally run to change their HKCUSoftwareMicrosoftOffice12.0ExcelSecurityExtensionHardening DWord to 0, disabling this useless prompt
  • Only export to CSV, not Excel. Sure, it’s not as pretty but it’ll work.
  • Instead of using this much simpler spreadsheet generation method, instead open a template spreadsheet on the server as a data-source, write to it (using SQL), and save it with a unique file name for the user to download. Of course you’ll then have to do stuff like ensuring your saved files have unique file names (perhaps using GUID) and deleting the old spreadsheets from the File System.

How to insert data into an Excel File using VB.NET

excel-logo

Declaring the connection

If you are using Excel 2007 and above, create the file where you would like to save your data and add the following to your web.config

<appSettings>
<add value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:File.xlsx;Extended Properties='Excel 12.0;ReadOnly=False;HDR=YES;'" key="ExcelLink" />
</appSettings>

Read from an Excel file

First, we initialize the connection to the Excel File:

'connection details
Dim _connectionString As String = System.Configuration.ConfigurationManager.AppSettings("ExcelLink")
'get last value of the id in excel file
Dim factory As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.OleDb")
Dim strErrorsFound As New StringBuilder
Dim blnErrorFound As Boolean = False
Using connection As DbConnection = factory.CreateConnection()
connection.ConnectionString = _connectionString

Then, we create the command that will select information from the sheet:

Using command As DbCommand = connection.CreateCommand()
'Get the RefNo for the latest entry
command.CommandText = "SELECT TOP 1 ID FROM [Feedback$] ORDER BY ID DESC"

We will put our code in a Try-Catch block to prevent any errors bleeding into the Client-Side.

Try
connection.Open()

Using dr As DbDataReader = command.ExecuteReader()
If dr.HasRows Then

While dr.Read()
‘store last ref_number value
lastID = If(dr(“ID”).ToString() <> “”, Int32.Parse(dr(“ID”).ToString()), 0)
End While
End If
End Using

‘clean up- close first connection to the excel file
connection.Dispose()
connection.Close()

Catch ex As Exception
PanelServerError.Visible = True
Exit Sub
End Try

Don’t forget to close the Using Tags

End Using
End Using

Get data from SQL Server and save it into Excel

Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("CN").ConnectionString)

Dim myCommand As New SqlCommand("[Your SP]", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Try

myConnection.Open()

Dim myReader As SqlDataReader = myCommand.ExecuteReader()

While myReader.Read()

IDNum = myReader.Item("ID")
contactName = myReader.Item("ContactName")
contactCompany = myReader.Item("ContactCompany")
contactPosition = myReader.Item("ContactPosition")
contactEmail = myReader.Item("ContactEmail")
project = myReader.Item("Project")

'insert into excel file
Dim factory2 As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.OleDb")

Dim connection2 As DbConnection = factory2.CreateConnection()
connection2.ConnectionString = _connectionString

Dim command2 As DbCommand = connection2.CreateCommand()

'Build SQL string
Dim SQLQuery As New StringBuilder()
SQLQuery.Append("INSERT INTO [Feedback$] ")
SQLQuery.Append("(ID, Name, Company, Position_, Email, Project, ")

SQLQuery.Append("VALUES ('" & IDNum & "', '" & contactName & "','" & contactCompany & "','" & contactPosition & "','" & contactEmail & "','" & project & "','")

Try
connection2.Open()

'insert data into excel spreadsheet
command2.CommandText = SQLQuery.ToString()
command2.ExecuteNonQuery()

Catch ex As SystemException
blnErrorFound = True
strErrorsFound.AppendLine("Line / ID " + IDNum)
strErrorsFound.AppendLine(ex.Message)
strErrorsFound.AppendLine("Query: " + SQLQuery.ToString())
strErrorsFound.AppendLine("******************************************************************************")
Finally
connection2.Dispose()
connection2.Close()
End Try

'****************excel insert code *******************

End While

myReader.Close()

Catch ex As SystemException
blnErrorFound = True
strErrorsFound.AppendLine("Generic error: " + ex.Message)
strErrorsFound.AppendLine("******************************************************************************")

Finally

'clean up
myConnection.Close()
myConnection.Dispose()
End Try

Errors

Operation must use an updateable query.

Resolution

Granting access to the file being written to (IISUSR User or the IIS App Pool User) solved this issue for me.

The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

Resolution

While importing from an Excel spread sheet you see the following error message “[Microsoft][ODBC Excel Driver] The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data”. This is problem with the Excel ODBC driver which can often be worked around by reordering the records in the spreadsheet..
Fundamentally, Excel is NOT a database and the columns in a spreadsheet do not have a data type associated with them in the same way that that a database does. The Excel ODBC driver has to make an assumption about the data type for a given column. The driver does this by reading ahead 8 records and looking at the data found. It then makes a decision about the data type based upon what it has read. Problems occur when the assumption about data type is proven wrong by subsequent records. Consider two scenarios….
A column in an Excel spreadsheet has a column in it which contains string data. The first 8 records contain short strings, (let’s say 20-30 characters). The Excel ODBC driver reads this data and assumes that a short string data type will be appropriate for this column. If a subsequent record contains a longer string, (let’s say 300 characters). The data type may prove inappropriate and unable to store the longer string and the error above is raised by the Excel ODBC driver. Moving the record with the long string to the beginning of the dataset will allow the Excel ODBC to select a more appropriate data type for the column which will apply to all records in the spreadsheet.
A column in a spreadsheet has numeric strings for the first 8 records, For example “123”, “456” etc. Excel decides that this column has numeric data. A subsequent record contains a string which is not numeric data, for example “Hello World”. The assumption made by the Excel ODBC driver will prove incorrect and the above error message will be raised by the ODBC driver. The problem can be worked around by reconsidering the order of the records. If the record containing “hello world” is placed within the first 8 records. The Excel ODBC driver will determine that this column contains string data and hopefully a string data type will be selected which will be appropriate for all data records.
In all cases the technique is to arrange the order of the records such that the Excel ODBC driver is allowed to make the correct selection of data type.

Generate Word documents from ASP.NET

Overview

Ever needed to generate Word documents from your ASP.NET applications? There are a couple of components which will help to generate Word documents, but using these components may have some overhead such as Component Registration, setting permissions, licenses, etc., Sometimes, it may even become difficult to understand their features and use them. Generating word document in ASP.NET with HTML is incredibly easy.

The following sample demonstrates how to place Heading and Table dynamically in the word document in ASP.NET web applications using HTML.

Requirements

Microsoft Visual Web Developer 2005+ Express Edition

Create the Web Application project

Add the new Web Site Application project (with name as Generate Word Document) and place the button (with name as btnGenerateDocument) in the Default.aspx web form as shown below.

Figure 1

Double click the highlighted code behind file from the Solution Explorer and add the following namespaces.

Listing 1

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;

Finally, paste the following code in the button click event

Listing 2

HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Charset ="";

HttpContext.Current.Response.ContentType ="application/msword";

string strFileName = "GenerateDocument"+ ".doc";
HttpContext.Current.Response.AddHeader("Content-Disposition",
  "inline;filename=" + strFileName);

StringBuilder strHTMLContent = newStringBuilder();

strHTMLContent.Append(
  " <h1 title='Heading' align='Center'
  style='font-family: verdana; font -size: 80 % ;
 color: black'><u>Document Heading</u> <  / h1 > ".ToString());

strHTMLContent.Append("<br>".ToString()); strHTMLContent.Append(
  "<table align='Center'>".ToString());

// Row with Column headers
strHTMLContent.Append("<tr>".ToString()); strHTMLContent.Append(
  "<td style='width:100px; background:
# 99CC00'><b>Column
1 <  / b >  <  / td >".ToString());

strHTMLContent.Append("<td style='width:100px;background:
# 99CC00'><b>Column
2 <  / b >  <  / td >".ToString());

strHTMLContent.Append("<tdstyle='width:100px; background:
# 99CC00'><b>Column 3</b>
 <  / td >".ToString());strHTMLContent.Append(" <  / tr > ".ToString());

// First Row Data
strHTMLContent.Append("<tr>".ToString()); strHTMLContent.Append(
  "<td style='width:100px'>a</td>" .ToString()); strHTMLContent.Append(
  "<td style='width:100px'>b</td>" .ToString()); strHTMLContent.Append(
  "<td style='width:100px'>c</td>" .ToString());strHTMLContent.Append("</tr>"
  .ToString());

// Second Row Data
strHTMLContent.Append("<tr>".ToString()); strHTMLContent.Append(
  "<td style='width:100px'>d</td>" .ToString()); strHTMLContent.Append(
  "<td style='width:100px'>e</td>" .ToString()); strHTMLContent.Append(
  "<td style='width:100px'>f</td>" .ToString()); strHTMLContent.Append("</tr>"
  .ToString());

strHTMLContent.Append("</table>".ToString());

strHTMLContent.Append("<br><br>".ToString()); strHTMLContent.Append(
  "<p align='Center'> Note : This is adynamically generated 
  word document  </p> ".ToString());
HttpContext.Current.Response.Write(strHTMLContent);
HttpContext.Current.Response.End();
HttpContext.Current.Response.Flush();

In the above listing, first the Response object is cleared,charset is set to empty and then Content type is set to MS Word.

Filename is defined for the document which will be generated through this code and this is set to the “Content-Disposition” header of the Response object.

Now to the StringBuilder variable — it is an HTML representation of the heading and table with three columns and two rows along with column headers and data appended. This content will be displayed in the
Word document when it is generated.

Once the contents to be displayed in the document are set, the Response object’s Write, End and Flush methods send the output to the browser, where the user’s copy of Microsoft Word will open the content and render it as a Word document.

Run the Web Application

Once the application is run, the following screen is displayed in the browser.

Figure 2

Click the Generate Document button in the above screen upon which the following prompt will be displayed to open or save the document.

Figure 3

Now save the document to some location in the system and then open it to see the contents in the document. Document contents will be as follows:

Figure 4

Downloads

[Download Sample]

Conclusion

In this article, we have seen how easy it is to dynamically generate Word reports in ASP.NET using HTML.