Write Data to SQL Server Tables Using SqlBulkCopy

SqlBulkCopy is a new feature in ADO .NET 2.0 that speed up a copy operation for a large amount of data from your .NET application to SQL Server tables.

The SqlBulkCopy class can be used to write data only to SQL Server tables from different types of data source, as long as the data can be loaded to a DataTable instance or read with an IDataReader instance.

SqlBulkCopy contains an instance method WriteToServer, which is used to transfer data from a source to the destination table. SqlBulkCopy uses a collection of SqlBulkCopyColumnMapping objects to map a column in a data source to a field in the destination table.

There are some essential properties of SqlBulkCopy that you should be aware of:
  § BatchSize: Number of rows SqlBulkCopy will copy from data source to the destination table.
  § BulkCopyTimeOut: The number of seconds that system should wait to complete the copy operation.
  § ColumnMappings: You can use its Add() method to add in a new SqlBulkCopyColumnMapping object to its collection.
  § DestinationTableName
  § NotifyAfter: SqlRowsCopied event handler will be triggered when the number of rows specified has been copied. This is very helpful if you want to be aware the progress of a copy operation, showing completed % in a progress bar, for example.


Sample Codes
Here, I will demonstrate how to import data from CSV source file into database.

…
using System.Data.SqlClient;
…
…
private void bulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e) {
    MessageBox.Show(String.Format("{0} rows have been copied.", e.RowsCopied.ToString()));
}

private void CopyDataToDestinationTable()
{

    string strconn = "
server=localhost;Database=Transaction;User Id=sa;Password=123456;";
    OleDbConnection conn = new OleDbConnection();
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\;Extended Properties=\"text;HDR=Yes;FMT=CSVDelimited\"";

    try {
        conn.Open();

        OleDbCommand myCommand = conn.CreateCommand();

        string commandString = "select * from source.csv";
        myCommand.CommandType = CommandType.Text;
        myCommand.CommandText = commandString;

        OleDbDataReader dataReader = myCommand.ExecuteReader();
        dataReader.Read();

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(strconn)) {

            bulkCopy.DestinationTableName = "Products";
            bulkCopy.BulkCopyTimeout = 100000;
            bulkCopy.NotifyAfter = 1000;
            bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
            bulkCopy.ColumnMappings.Add("Brand", "Brand");
            bulkCopy.ColumnMappings.Add("Model", "Model");
            bulkCopy.ColumnMappings.Add("ProductName", "ProductName");

            // call WriteToServer which starts import
            bulkCopy.WriteToServer(dataReader);
        }
    }
    catch (Exception ex) {
        MessageBox.Show(ex.Message);
    }
    finally {
        conn.Close();
    }
}

Well, that’s it. Feel free to ask if there is any problem. Cheers!

BULK INSERT in MS SQL SERVER

In IT environment, it is always a necessary to import data from data files into database.

And recently, I have to deal with import a huge load of data (around 200 MB for 1 CSV input file) from CSV files into MS SQL SERVER. So what I had done to achieve this is by using BULK INSERT.

Firstly, prepare a CSV data file with the following content and save as test.csv.
July,Singapore,25
James,Australia,50
May,China,29

And then run scripts as following to load all the data from the CSV file into database.

--Temp table to store the data
CREATE TABLE CSVTest
(
   Name VARCHAR(500),
   Country VARCHAR(500),
   Age INT
)
GO

--Bulk insert into CSVTest
BULK INSERT CSVTest
FROM 'C:\test.csv'
WITH
(
   FIELDTERMINATOR=',',
   ROWTERMINATOR = '\n'
)
GO

--Get all data from CSVTest
SELECT *
FROM CSVTest
GO

--Drop the temp table
DROP TABLE CSVTest
GO

Kill Excel.exe Process in C# .NET

I am trying to get my C# .NET codes to generate reports into excel files. In the Export function, it creates the excel object, adds in the data and then saves the file.

All of this works fine. But for some reason, the excel.exe process is still running when I check from task manager.

I had tried to quit the excel application. I had tried to use InteropServices.Marshal.ReleaseComObject to get rid of them in .NET. But unfortunately, the excel.exe is still sitting in memory.

Finally, I found a way to kill the process in C# .NET. Take a look on the codes below:


using System.Diagnostics;
using System.Collections;
…
…
…

Hashtable myHashtable;
private void btnExport_Click(object sender, EventArgs e)
{
  // get process ids before running the excel codes
  CheckExcellProcesses();

  // export to excel
  ExportDataToExcel();

  // kill the right process after export completed
  KillExcel();
}

private void ExportDataToExcel()
{
  // your export process is here...
}

private void CheckExcellProcesses()
{
  Process[] AllProcesses = Process.GetProcessesByName("excel");
  myHashtable = new Hashtable();
  int iCount = 0;

  foreach ( Process ExcelProcess in AllProcesses) {
    myHashtable.Add(ExcelProcess.Id, iCount);
    iCount = iCount + 1;
  }
}

private void KillExcel()
{
  Process[] AllProcesses = Process.GetProcessesByName("excel");

   // check to kill the right process
  foreach ( Process ExcelProcess in AllProcesses) {
    if (myHashtable.ContainsKey(ExcelProcess.Id) == false)
       ExcelProcess.Kill();
  }

  AllProcesses = null;
}


Hope this helps.

ASP .NET Validators don’t work properly with Update Panel

Recently I am working on a project ASP .NET AJAX. Of course it is very common to have some validators in some forms to control the input values, and unfortunately I experienced problem with validators inside the Update Panel. The validators just don’t work properly as I am expected.

After google searching and here is the solution that I had found: ASP .NET AJAX Validators.

Firstly, downloads the validators.zip file and adds in the DLL into the /bin directory of your project. After that, include the following codes into the pages section of the web.config.

<tagMapping>
  <add tagType="System.Web.UI.WebControls.CompareValidator" mappedTagType="Sample.Web.UI.Compatibility.CompareValidator, Validators, Version=1.0.0.0"/>
  <add tagType="System.Web.UI.WebControls.CustomValidator" mappedTagType="Sample.Web.UI.Compatibility.CustomValidator, Validators, Version=1.0.0.0"/>
  <add tagType="System.Web.UI.WebControls.RangeValidator" mappedTagType="Sample.Web.UI.Compatibility.RangeValidator, Validators, Version=1.0.0.0"/>
  <add tagType="System.Web.UI.WebControls.RegularExpressionValidator" mappedTagType="Sample.Web.UI.Compatibility.RegularExpressionValidator, Validators, Version=1.0.0.0"/>
  <add tagType="System.Web.UI.WebControls.RequiredFieldValidator" mappedTagType="Sample.Web.UI.Compatibility.RequiredFieldValidator, Validators, Version=1.0.0.0"/>
  <add tagType="System.Web.UI.WebControls.ValidationSummary" mappedTagType="Sample.Web.UI.Compatibility.ValidationSummary, Validators, Version=1.0.0.0"/>
</tagMapping>

And the problem solved. Cheers!

OrElse and AndAlso operators in VB .NET

OrElse and AndAlso are two new logical operators in VB .NET and they have some properties that can enhance your codes in two general categories:

1. Avoid executing part of a logical expression.
2. Optimize code by not executing any more of a compound expression than required.

OrElse and AndAlso are quite similar with the And and Or except that VB .NET supports short-circuiting with the OrElse and AndAlso operators. This means that the expressions will only be executed when necessary. Anyway, the And and Or are still present in VB .NET.

For example:
// Assume that str1 = 5,
// x = 1 and y = 1

If x > str1 And y < str1 Then
' code
End If

When performing an And operator in VB .NET, it actually evaluates both of the expressions to get the final outcome. Even the first condition (x greater than str1) returns as FALSE, it still continues to look at the second argument even though it doesn’t need to.

Let’s see how AndAlso evaluates the codes below.

If x > str1 AndAlso y < str1 Then
' code
End If

When using AndAlso, VB .NET knows that the expression will not succeed once it is determined that the first condition (x greater than str1) is FALSE. So it stops evaluating the expression right away without checking the second condition.

The difference of Or and OrElse are also similar to And and AndAlso operator, which Or will check all the conditions and OrElse won’t checking the remaining expression, if it found any of the previous condition is TRUE.

How to get Windows’ Serial Number and Windows Logon User Name in .NET

By using the namespace System.Management, we can easily retrieve the serial number of your Windows OS and windows logon user name.

First of all, you need to add a reference of System.Management into your application. To add a reference into your .NET application, you can right-click on References in the solution explorer and select Add Reference.


From the Add References dialog box, select System.Management and click OK.


The sample codes below written in both C# and VB .NET show you how to read the Windows’ serial number and logon user name.

C# .NET
using System.Management;

public static string getSerialNumber() {
  string _serialNo = string.Empty;

  ManagementObjectSearcher objMOS = new ManagementObjectSearcher("Select * from Win32_OperatingSystem");
  ManagementObjectCollection objMOC;

  objMOC = objMOS.Get();

  foreach (ManagementObject objMO in objMOC) {
    _serialNo = objMO["SerialNumber"].ToString();
  }

  return _serialNo;
}

public static string getlogonUser(){
  string _user = string.Empty;

  ManagementObjectSearcher objMOS = new ManagementObjectSearcher("SELECT * FROM Win32_ComputerSystem");
  ManagementObjectCollection objMOC;

  objMOC = objMOS.Get();

  foreach (ManagementObject objMO in objMOC) {
    _user = objMO ["UserName"].ToString();
  }

  return _user;
}


VB .NET
Imports System.Management

Public Shared Function getSerialNumber() As String
  Dim _serialNo As String = String.Empty

  Dim objMOS As New ManagementObjectSearcher("Select * from Win32_OperatingSystem")
  Dim objMOC As ManagementObjectCollection

  objMOC = objMOS.Get

  For Each objMO As ManagementObject In objMOC
    _serialNo = objMO("SerialNumber").ToString()
  Next

  Return _serialNo
End Function

Public Shared Function getlogonUser() As String
  Dim _user As String = String.Empty

  Dim objMOS As New ManagementObjectSearcher("SELECT * FROM Win32_ComputerSystem")
  Dim objMOC As ManagementObjectCollection

  objMOC = objMOS.Get

  For Each objMO As ManagementObject In objMOC
    _user = objMO("UserName").ToString()
  Next

  Return _user
End Function

Cheers.

Export Data to Excel (Not HTML Table) in C# .NET

In my previous post - Export Data to Excel (Not HTML Tables), I had shown you how to export to excel in VB .NET.

In this post, I will use a C# .NET example instead.

Of cause first thing that you need to do is to add Excel.dll (Microsoft excel 11.0 Object Library) into your .NET project references.

Here is the codes:

private void ExportToExcelFile(System.Data.DataTable dt)
{
   Excel.Application xlsApp = new Excel.ApplicationClass();
   Excel.Workbook xlsWorkbook;
   Excel.Worksheet xlsWorksheet;
   string strhdr;
   int row;
   string strFile = "file1.xls";
   string filename = Server.MapPath(strFile);

   if (dt.Rows.Count > 0)
   {
      //Create new workbook
      xlsWorkbook = xlsApp.Workbooks.Add(true);

      //Get the first worksheet
      xlsWorksheet = (Excel.Worksheet)(xlsWorkbook.Worksheets[1]);

      //Activate current worksheet
      xlsWorksheet.Activate();

      //Set header row to row 1
      row = 1;

      //Add table headers to worksheet
      xlsWorksheet.Cells[row, 1] = "Name";
      xlsWorksheet.Cells[row, 2] = "Gender";
      xlsWorksheet.Cells[row, 3] = "Age";

      //Format header row (bold, extra row height, autofit width)
      xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).Font.Bold = true;
      xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).Rows.RowHeight = 1.5 * xlsWorksheet.StandardHeight;
      xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).EntireRow.AutoFit();

      //Freeze the columm headers
      xlsWorksheet.get_Range("A" + (row + 1).ToString(), "C" + (row + 1).ToString()).Select();
      xlsApp.ActiveWindow.FreezePanes = true;

      //Write data to Excel worksheet
      foreach (DataRow dr in dt.Rows)
      {
         row += 1;
         if (dr["Name"] != null)
            xlsWorksheet.Cells[row, 1] = dr["Name"];
         if (dr["Gender"] != null)
            xlsWorksheet.Cells[row, 2] = dr["Gender"];
         if (dr["Age"] != null)
            xlsWorksheet.Cells[row, 3] = dr["Age"];
      }

      //Format data rows (align to center and left, autofit width and height)
      xlsWorksheet.get_Range("A2", "C" + row.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
      xlsWorksheet.get_Range("A2", "C" + row.ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
      xlsWorksheet.get_Range("A2", "c" + row.ToString()).EntireColumn.AutoFit();
      xlsWorksheet.get_Range("A2", "c" + row.ToString()).EntireRow.AutoFit();

      //Make excel workbook visible to user after all data has been added to worksheet.
      xlsApp.DisplayAlerts = false;
      xlsWorkbook.Close(true, filename, null);

      //Export data to client machine
      strhdr = "attachment;filename=" + strFile;
      Response.Clear();
      Response.ContentType = "application/vnd.ms-excel";
      Response.ContentEncoding = System.Text.Encoding.Default;
      Response.AppendHeader("Content-Disposition",strhdr);
      Response.WriteFile(filename);
      Response.Flush();
      Response.Clear();
      Response.Close();
   }
}

Cheers!

How to Insert Value into an Identity Column

In database tables, normally identity columns are used as primary keys that automatically generates numeric values for every new inserted row.

For this identity (AutoNumber) column, you are not allow to insert your own value. So what if you really want to insert your own value into the column?

A very easy way to do this. I will show you how to do this.

Firstly, create a table Sample by using the script below into your database.

CREATE TABLE Sample (
Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(50) NOT NULL )
GO

Normally if we want to insert a new row into this table, you will have to only insert the value of Name, as script below:

INSERT INTO Sample(Name) VALUES ('July')

Now, we try to insert a value into the Id column.

INSERT INTO Sample(Id, Name) VALUES (11, 'July')

When this query is executed, an error will be generated:

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Sample' when IDENTITY_INSERT is set to OFF.

To insert this record into table without any error, you just need to enable the IDENTITY_INSERT. It should look like this:

SET IDENTITY_INSERT Sample ON

INSERT INTO Sample(Id, Name) VALUES (11, 'July')

SET IDENTITY_INSERT Sample OFF

But there are some points that you need to be aware of about IDENTITY_INSERT.

1.   Only 1 table can have the IDENTITY_INSERT property set to ON at a time. If you try to enable IDENTITY_INSERT to ON for a second table while a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server will return an error message.
2.    If the value that you have inserted is greater than the current identity seed in your table, the new inserted value will be set as the current identity seed.

For example, table Sample has its current identity value 1 and you want to insert a new row with the identity value 11. When you insert another new row, your identity value will start from 12, instead of 1.

SET IDENTITY_INSERT Sample ON

INSERT INTO Sample(Id, Name) VALUES (11, 'July')

SET IDENTITY_INSERT Sample OFF

INSERT INTO Sample(Name) VALUES ('August')

Result:

Id Name
11 July
12 August

Cheers.

How to Call Remote Web Services from Client Side JavaScript

Web Services is a collection of universally available functions, which allows different applications from different sources to share among each other without time-consuming custom coding by using the XML, SOAP, WSDL and UDDI open standards across the Internet. You can go to here for more detail explanation on it.

Recently, I have been doing some research into how to call a web service from client side JavaScript. ASP .NET AJAX enables you to call a web service from the browser by using client script, namely the page can call server-based methods without a postback and refreshing the whole web page.

Here, I will show you how to call Web Services within the same project and also how to call Web Services remotely.

Consuming a Local Web Service
Simple example of Web Service
I assume that you had installed the ASP .NET AJAX framework. So let us start with create a new ASP .NET AJAX-Enabled Web Application created.

I will firstly show you how to call a web service within the same project, so let’s add new Web Service called webService.asmx to the project (Right-click your project on your solution explorer | Add New Item | Select Web Service and name it | Click Add.).

Let us create a very simple web service that returns current date and time.

In order to make a web service accessible from script, the web service class must be qualified with the ScriptServiceAttribute. See the example below:

...
using System.Web.Script.Services;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ScriptService]
public class WebService : System.Web.Services.WebService
{
  ...
  [WebMethod]
  public string GetCurrentDateTime() {
    string curTime = String.Format("Current datetime is {0}.", DateTime.Now);
    return curTime;
  }
}

And then save it.

Exposing Web Services to Client Script in .aspx Web Page
To enable a web service to be called from client script in your .aspx file, make sure that we have one instance of ScriptManager object.

Then add the Services collection to the ScriptManager object, reference the web service by adding asp:ServiceReference child element to the Services collection and sets its Path attribute to point to the web service. Below is how it is looks like:

<body>
  <form id="form1" runat="server">
    <asp:ScriptManager ID="ScriptManager1" runat="server">
      <Services>
      <asp:ServiceReference Path="WebService.asmx" />
      </Services>
    </asp:ScriptManager>
  </form>
</body>

Client-side Function, Button and Label
Next we need to add in a client-side function to call server-side web service, a button to trigger the web service, and a label to display the returned result. Below is the sample of the web page:

<html>
<head runat="server">
<title>Calling Web Services</title>
<script language="javascript" type="text/javascript">
function GetTime() {
  WebService.GetCurrentDateTime(ShowResult);
}

function ShowResult(result) {
  document.getElementById("lblResult").innerHTML = result.value;
}
</script>
</head>
<body>
  <form id="form1" runat="server">
    ...
    <asp:Label id="lblResult" runat="server">Result will be displayed here.</asp:Label>
    <br/>
    <button id="btn" name="btn" onclick="GetTime()">Get Time</button&gt;
    <br/>
  </form>
</body>
</html>

TIPS: If you want to call the JavaScript function from a separate .js file, copy the functions into the .js file, and the add in Scripts collection to the ScriptManager object, reference the JavaScript function by adding asp:ScriptReference child element to the Scripts collection and sets its Path attribute to point to the JavaScript, as below:

<Scripts>
<asp:ScriptReference Path="CallWebServiceMethods.js" />
</Scripts>

That’s it for consuming web service, which located in the same project.


Consuming Web Services Remotely
WebService Behavior (HTC file)
The first thing that you need to do is to create a separate ASP .NET Web Service application and copy the web service that we created as above into it. I will add another method here to show you how to call a web service and pass parameters to its method through JavaScript.

[WebMethod]
public string EchoYourWord(string yourword) {
  return "You have said that: " + yourword;
}

Save and run it. So now you got your web service ready to test.

WebService behavior enables us to make a method call to a remote web service using a scripted language. It is a reusable DHTML component that uses web services by handling the communication of the SOAP data packets between the browser and the web services.

The following are 3 important methods supported by the web service behavior:

• createUseOptions – Allows us to preserve authentication information across remote method invocations. Can be very useful when using SSL to communicate with the remote web service

• callService – Allows us to invoke the remote web service method asynchronously

• useService – Allows us to establish a friendly name for the web service that can be used while invoking the web service

You can download the HTC file at here. Copy into the same folder as your project.

How to use Web Service Behavior in .aspx Page
I will recommend you that to create another new .aspx page to test on consuming remote web service.

To invoke the GetCurrentDateTime() method, we have to attach the HTC file to a valid element, such as DIV or BODY.

<div id="service" style="BEHAVIOR:url(webservice.htc)" ></div>

Once this behavior is attached, we may need to add some code to the web page to enable use of WebService behavior and make calls to the web service.

Add in this JavaScript function, as below:

function Init() {
  // initialize and create a name "webService" for Web service
  // Note: the "service.use..." is the DIV or BODY element’s id as created just now.
  service.useService("http://localhost/My_WebServices/WebService.asmx?wsdl", "webService");
}

Next, we will need to invoke the useService method in the onload event to map the web service before any methods on the web service are invoked.

After that, we can add in another JavaScript function and use the callService method to invoke the remote web service method asynchronously.

Your web page should look like this when you are done:

<html>
<head runat="server">
<title>Calling Web Services</title>
<script language="javascript" type="text/javascript">
function Init() {
  // initialize and create a shortcut name "webService" for Web service
  // Note: the "service.use..." is the DIV or BODY element’s id as created just now.
  service.useService("http://localhost/My_WebServices/WebService.asmx?wsdl", "webService");
}

function GetTime() {
  // Note: the "service.webService..." is the shortcut name of your web service that you had initialize in the Init().
  service.webService.callService(ShowResult, "GetCurrentDateTime");
}

// sample of passing a parameter to the web service
var myword;
function Echo() {
  myword = document.form1.SayOutLoud.value;
  service.webService.callService(ShowResult, "EchoYourWord", myword);
}

function ShowResult(result) {
  document.getElementById("lblResult").innerHTML = result.value;
}
</script>
</head>
<body onload="Init()">
  <form id="form1" runat="server">
    <div id="service" style="BEHAVIOR:url(webservice.htc)" ></div>
    <asp:Label id="lblResult" runat="server">Result will be displayed here.</asp:Label>
    <br/><br/>
    Your words : <input type="text" name="SayOutLoud"/><br/><br/>
    <button id="btn" name="btn" onclick="GetTime()">Get Time</button><br />
    <button id="btn1" name="btn1" onclick="Echo()">Echo Your Word</button><br/>
    <br/>
  </form>
</body>
</html>

Note: Of course, if you are using a separate .js file, you still need to add a reference in a ScriptManager object, as I had mentioned above.

That’s it for calling a remote web service. Any question please feels free to ask.

Cheers!

Displaying Date and time in VB Script with ASP Classic

To display the Date and Time in your ASP page with certain format is very simple indeed. Take a look at the examples as below:

1. <%=time()%>

   - Get current time

   - e.g. 9:52:30 AM

   
   
2. <%=date()%>

   - Get current date

   - e.g. 7/11/2008

   
   
3. <%=now()%>

   - Get current date and time

   - e.g. 7/11/2008 9:54:03 AM

   
   
4. <%=FormatDateTime(now,0)%>

   - e.g. 7/11/2008 9:55:41 AM

   
   
5. <%=FormatDateTime(now,1)%>

   - e.g. Friday, July 11, 2008

   
   
6. <%=FormatDateTime(now,2)%>

   - e.g. 7/11/2008

   
   
7. <%=FormatDateTime(now,3)%>

   - e.g. 9:57:49 AM

   
   
8. <%=FormatDateTime(now,4)%>

   - e.g. 09:58

   
   
9. <%=WeekDay(now)%>

   - Day of the week

   - e.g. 6

   
   
10. <%=WeekDayName(WeekDay(now))%>

    - e.g. Friday

    
    
11. <%=Day(date)%>

    - Day of the month

    - e.g. 11

    
    
12. <%=Month(date)%>

    - Month of the year

    - e.g. 7

    
    
13. <%=MonthName(Month(date))%>

    - e.g. July

    
    
14. <%=Year(date)%>

    - Current year

    - e.g. 2008

    
    
15. <%=Right(Year(date),2)%>

    - Current year

    e.g. 08

    
    
16. <%=Hour(now)%>

    - Hour part

    - e.g. 10

    
    
17. <%=Minute(now)%>

    - Minute part

    - e.g. 11

    
    
18. <%=Second(now)%>

    - Second part

    - e.g. 2

Hope you will find this helpful.

Couldn’t find ConfigurationManager in System.Configuration?

I am trying to pull an appSettings key from my web.config. In .NET 1.1, we usually use:

System.Configuration.ConfigurationSettings.AppSettings["strConnection"].ToString();

However, in .NET2.0, using this code will cause the following error:

'System.Configuration.ConfigurationSettings.AppSettings' is obsolete: 'This method is obsolete, it has been replaced by System.Configuration!System.Configuration.ConfigurationManager.AppSettings'

So I just change my code to the following, according to the error above. But the problem is I can’t find ConfigurationManager in the System.Configuration.

The reason is because the original System.Configuration.ConfigurationSettings class is point to the System.dll assembly. There is another new the System.Configuration.dll assembly with the new ConfigurationManager class. In this case, you need to add in the System.Configuration.dll as a reference to your application before you can use it.

Hope this will help someone.

Export GridView to PDF

Recently I have been trying to generate some reports to PDF file format from ASP. NET2.0 application. There are a lot of open source PDF libraries out there that you can use to export to PDF such as iTextSharp, Gios PDF .NET Library and PDFSharp. You can go to this link to find out more open source PDF libraries in C#.

Later I will show you a working solution on how to export GridView to PDF by using one of the free libraries – iTextSharp.

ITextSharp is a port of the iText open source java library written entirely in C# for the .NET platform. It is a library that allows developers to extend the capabilities of their web server applications with dynamic PDF document generation and generate PDF file on the fly.

Before that, you need to download the iTextSharp library. Here is the download link.

Add in the iTextSharp.dll as a reference into your web application.

Here is my sample of code:

…
using iTextSharp.text;
using iTextSharp.text.pdf;

protected void Page_Load(object sender, EventArgs e)
{
  ExportToPDF();
}

private void ExportToPDF()
{
  Document document = new Document(PageSize.A4, 0, 0, 50, 50);
  System.IO.MemoryStream msReport = new System.IO.MemoryStream();

  try {
    // creation of the different writers
    PdfWriter writer = PdfWriter.GetInstance(document, msReport);

    // we add some meta information to the document
    document.AddAuthor("eJuly");
    document.AddSubject("Export to PDF");

    document.Open();

    iTextSharp.text.Table datatable = new iTextSharp.text.Table(7);

    datatable.Padding = 2;
    datatable.Spacing = 0;

    float[] headerwidths = { 6, 20, 32, 18, 8, 8, 8 };
    datatable.Widths = headerwidths;

    // the first cell spans 7 columns
    Cell cell = new Cell(new Phrase("System Users Report", FontFactory.GetFont(FontFactory.HELVETICA, 16, Font.BOLD)));
    cell.HorizontalAlignment = Element.ALIGN_CENTER;
    cell.Leading = 30;
    cell.Colspan = 7;
    cell.Border = Rectangle.NO_BORDER;
    cell.BackgroundColor = new iTextSharp.text.Color(System.Drawing.Color.Gray);
    datatable.AddCell(cell);

    // These cells span 2 rows
    datatable.DefaultCellBorderWidth = 1;
    datatable.DefaultHorizontalAlignment = 1;
    datatable.DefaultRowspan = 2;
    datatable.AddCell("No.");
    datatable.AddCell(new Phrase("Full Name", FontFactory.GetFont(FontFactory.HELVETICA, 14, Font.NORMAL)));
    datatable.AddCell("Address");
    datatable.AddCell("Telephone No.");

    // This cell spans the remaining 3 columns in 1 row
    datatable.DefaultRowspan = 1;
    datatable.DefaultColspan = 3;
    datatable.AddCell("Just Put Anything");

    // These cells span 1 row and 1 column
    datatable.DefaultColspan = 1;
    datatable.AddCell("Col 1");
    datatable.AddCell("Col 2");
    datatable.AddCell("Col 3");

    datatable.DefaultCellBorderWidth = 1;
    datatable.DefaultRowspan = 1;

    for (int i = 1; i < 20; i++) {
      datatable.DefaultHorizontalAlignment = Element.ALIGN_LEFT;
      datatable.AddCell(i.ToString());
      datatable.AddCell("This is my name.");
      datatable.AddCell("I have a very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very long long address.");
      datatable.AddCell("0123456789");

      datatable.DefaultHorizontalAlignment = Element.ALIGN_CENTER;
      datatable.AddCell("No");
      datatable.AddCell("Yes");
      datatable.AddCell("No");
    }

    document.Add(datatable);
  }
  catch (Exception e) {
    Console.Error.WriteLine(e.Message);
  }

  // we close the document
  document.Close();

  Response.Clear();
  Response.AddHeader("content-disposition", "attachment;filename=Export.pdf");
  Response.ContentType = "application/pdf";
  Response.BinaryWrite(msReport.ToArray());
  Response.End();
}

Hope these codes can help those people who are new to asp.net developing and save some time on their searching solutions. You can also find the tutorial of iTextSharp at here.

Happy Programming!

Error “There was an error opening this document. The file is damaged and could not be repaired”

Few days ago, I was trying to export report in Excel, Word and PDF format.

The code that I used works perfect for the Excel and Word file, however, when I tried to do the same on PDF file, I got the following error in Adobe Acrobat Reader version 7 and Internet Explorer 6.0:

“There was an error opening this document. The file is damaged and could not be repaired.”

I had been searched the solution for hours. I had tried various other things such as Response.BinaryWrite, Response.ClearContent, Response.ClearHeaders, and also some other header settings, but no luck.

Finally, I got this code that works fine.

Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=Export.pdf");
Response.ContentType = "application/pdf";
Response.BinaryWrite(Report.ToArray());
Response.End();

Hope this can hope those facing the same problem. Cheers!

Difference between String and string

If you notice in your codes, String, which typed, with an uppercase S is colored in light blue. If it is typed in the lowercase string, it is colored in dark blue. What is the difference between String and string actually?

Nothing really, string is a C# language keyword, which is an alias for System.String in .NET framework. So do int = System.Int32, bool = System.Boolean and short = System.Int16. The only subtle difference is that string can be used without a using System; directive, while String needs it, otherwise you will need to specify System.String in full.

Hope this can provides you a clearer understanding about string and String.

JavaScript not firing with UpdatePanel

Yesterday when I am dealt with textbox inside an UpdatePanel, I noticed that the JavaScript is not fire when the TextChanged handler is triggered.

I had the code lines as below to fire my JavaScript function.

string strScript = "javascript:alert('Testing')";
ClientScript.RegisterStartupScript(typeof(Page),"clientScript", strScript);

After working around with the bug, finally I found the cause. The ClientScript.RegisterStartupScript is just cannot working within an UpdatePanel. To work around the problem there are related static methods in the class ScriptManager that should be used when the control is used inside an UpdatePanel, as the following:

string strScript = "javascript: alert('Testing')";
ScriptManager.RegisterStartupScript(this.gridPanel, this.GetType(), "strScript", strScript, true);

In short, when you are using UpdatePanel in your form, don’t use ClientScript.RegisterStartupScript. More information please refers to:
http://asp.net/AJAX/Documentation/Live/mref/O_T_System_Web_UI_ScriptManager_RegisterStartupScript.aspx http://asp.net/AJAX/Documentation/Live/mref/M_System_Web_UI_ScriptManager_RegisterStartupScript_5_d03cd23f.aspx

Hope this can help you anyway. Cheers.

How to call Server Side Methods from Client Side Script in ASP .NET

Basically, you cannot call server side methods from client side script directly. Server side methods will execute at server side, client side script at client side, they live at two different worlds.

However, to call a server side method from client side, you can pass some information to the server as a request for an action, which can trigger a code behind method.

One simple solution to communicate with server from client side is to provide a hidden input field to store some flag or information and to read them on the server side.

To achieve this, put a hidden field in your HTML on the page, which use to store some flag. And in the code behind, you can call to the correct method that is needed according to the provided information.

<input id="hiddenVal" type="hidden" value="0" name="hiddenVal" runat="server"/>

Alternatively, you can put a hidden input button in your HTML on the page and then use script to call the button’s click method, as shown below:

<input type="button" id="btnHidden" style="DISPLAY:none" runat="server" onserverclick="server_side_handler"/>

Here is the sample of script to make the button clicks.

var btnHide = document.getElementById("btnHidden");
btnHide.click();

Another way to call a server side method from client side script is by using ASP .NET AJAX Extensions. I assume that you already installed ASP .NET AJAX Extension, and then create a new ASP .NET AJAX-Enabled Web Site. The steps below will show you how this can be done.

Drag and drop a button into the page. The markup will look familiar to the following:

<form id="form1" runat="server">
    <asp:ScriptManager ID="ScriptManager1" runat="server"/>
    <div>
        <asp:Button ID="btnGetMe" runat="server" Text="Button" />
    </div>
</form>

Add the attribute EnablePageMethods="true" to the ScriptManager as shown below:

<asp:ScriptManager ID="ScriptManager1" runat="server" EnablePageMethods="true"/>

Note: This is very important or else you won’t be able to call the server side methods.

After that, add in a STATIC method to code behind of the page. This method then can be called from client side script easily. Mark the method as a WebMethod to expose the method, as below:

[System.Web.Services.WebMethod]
public static string HelloWorld(string value)
{
    return value;
}

Next step that you need to do is to call the page method. To call the method, you can specify a client-side method. The returned value from the server side will be passed as an argument to the client-side method.

<script type="text/javascript">
function CallMethod()
{
    PageMethods.HelloWorld("Hello, how are you today?", OnSucceeded);
}

function OnSucceeded(result)
{
    alert(result);
}
</script>

To invoke the method whenever the button is clicked, add these few lines of code in the Page_Load() event.

if (!Page.IsPostBack)
{
    btnGetMe.Attributes.Add("onclick", "javascript:CallMyPageMethod()");
}

Try to run your website now.

@@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT in Transact-SQL

In Transact-SQL, you can use @@IDENTITY keyword to retrieve the value of the identity column whenever an INSERT, SELECT INTO or bulk copy statement is completed. It contains the last value that is generated by the statement.

For example, the query below inserts a new record into a table and returns a result set containing the ID of the inserted record.

INSERT INTO myTable (myName) VALUES (‘E-JULY’)

SELECT @@IDENTITY AS [@@IDENTITY] --Last inserted identity value will be inserted

However, beware of a potential for a subtle bug in some circumstances. For example, if you use @@IDENTITY and insert into a table (myTable) that run a trigger, and if the trigger inserts another record into another table (mySubTable), which happens to have an identity column, the @@IDENTITY will contain the identity value of table mySubTable instead of the identity value of myTable.

One option to prevent this from happening, we can use SCOPE_IDENTITY() function instead which will return the last inserted identity value in the current scope, in this case the returned value will be the identity value of myTable. Every trigger, function, procedure and batch has its own scope. SCOPE_IDENTITY shows the last inserted identity value in the current scope, which ignores any triggers that might fire.

INSERT INTO myTable (myName) VALUES (‘E-JULY’)

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]

IDENT_CURRENT is limited to a specified table. It returns the last identity value generated for a specific table or view in any session and any scope.

INSERT INTO myTable (myName) VALUES (‘E-JULY’)

SELECT IDENT_CURRENT('myTable') AS [IDENT_CURR]

However, please note that SCOPE_IDENTITY and IDENT_CURRENT are only available in SQL Server 2000 or higher version.

Global.asax in ASP .NET

The Global.asax, which also known as ASP .NET application file, is located in the root directory of an ASP .NET application. This file contains codes that are executed in response to application-level and session-level events rose by ASP .NET or by HTTP modules.

There are a few things that needs to be take note about Global.asax, as below:
 The Global.asax is an optional file. You can remove the file if it is unnecessary.
 Any direct URL request to retrieve this file will be rejected automatically. External users are restricted from download or view the code written within it.
 If there are any changes on an active Global.asax file, the ASP .NET page framework will firstly complete all current requests for the application, sends the Application_End event to any listeners, and then restarts the application domain. In effect, this reboots the application, closing all browser sessions and flushing all state information. When the next incoming request from a browser arrives, the ASP.NET page framework re-parses and recompiles the Global.asax file and raises the Application_Start event.

In Visual Studio 2005, the Global.asax is not created by default. To create a Global.asax in your web project, right click your web site or web project in your solution explorer, choose Add New Item from the context menu. From the Visual Studio Installed Template, select Global Application Class and you can see something similar to the figure below:



Application and Session Events in Global.asax

Application Handlers:
Application_Start – invoked when the application first starts
Application_Init – invoked after Application_Start and is used for initializing code
Application_Disposed – invoked before destroying an instance of an application
Application_Error – invoked when an exception occurs
Application_End – invoked when the application ends and used to clean up variables and memory

Request Handlers:
Application_BeginRequest – invoked when an user makes a request for the application
Application_EndRequest – invoked at the end of each request
Application_PreRequestHandlerExecute – invoked before ASP .NET executes an event handler
Application_PostRequestHandlerExecute – invoked after ASP .NET handler has finished its execution
Application_PreSendRequestHeaders – invoked before ASP .NET sends HTTP header to the client
Application_PreSendRequestContent – invoked before ASP .NET sends contents to the client
Application_AuthenticateRequest – invoked before a user credentials are authenticated
Application_AuthorizeRequest – invoked on successful authentication of a user credentials. You can create your own user authorization to access the resources of an application
Application_ResolveRequestCache – invoked on successful completion of the authorization request
Application_AcquireRequestState – invoked when ASP .NET acquires the current state associated with the current request
Application_ReleaseRequestState – invoked before current state data in the session collection is serialized
Application_UpdateRequestCache – invoked before information is added to output cache of the page

Session Methods:
Session_Start – invoked when session starts on each user who requesting a page
Session_End – invoked when the session of a user times out or ends

Web.config for VS2005 is rejected by IIS as Badly Formed

I created a very simple website using VS .NET 2005. When I am trying to bring up my web page, I keep getting the following error message as shown below.

Server Error in '/APS_WebSetup' Application. ________________________________________
Configuration Error
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message: Unrecognized configuration section 'xhtmlConformance'

Source Error:

Line 89: -->
Line 90: <globalization requestEncoding="utf-8" responseEncoding="utf-8"/>
Line 91: <xhtmlConformance mode="Legacy"/>
Line 92: <pages>
Line 93: <namespaces>

Source File: C:\inetpub\wwwroot\APS_WebSetup\web.config Line: 91
________________________________________
Version Information: Microsoft .NET Framework Version: 1.1.4322.2407; ASP.NET Version: 1.1.4322.2407

************************************************************************************

Server Error in '/APS_WebSetup' Application. ________________________________________
Configuration Error
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message: Child nodes are not allowed.

Source Error:

Line 91:
Line 92: <pages>
Line 93: <namespaces>
Line 94: <add namespace="Microsoft.VisualBasic"/>
Line 95: <add namespace="System.Data"/>

Source File: C:\inetpub\wwwroot\APS_WebSetup\web.config Line: 93
________________________________________
Version Information: Microsoft .NET Framework Version: 1.1.4322.2407; ASP.NET Version: 1.1.4322.2407

***********************************************************************************

These errors will occurred when I attempt to run an application developed using .NET framework 2.0 but Microsoft IIS web server defaults to an earlier version of the .NET framework, in my case is .NET 1.1.

To solve these errors, follow the steps as below:
1. Install .NET framework 2.0 (if it is not installed in your system).
2. Go to your IIS -> Default Web Site and select your virtual directory.
3. Right-click the virtual directory and click Properties.


4. Go to ASP .NET tab. Change the ASP .NET version to 2.0. Click Apply and OK.


Try to run the application again. The errors may solve now.

Hope this can help you all. Cheers!

PlaceHolder control in ASP .NET

PlaceHolder control acts as a container to store other web controls to a web page dynamically. It does not produce any output; its main function is used as a container for other controls. You can use the Controls.Controls collection to add, insert or remove controls from a PlaceHolder control.

Here is an example of how to add a web control to PlaceHolder control.

HTML
<form id="Form1" method="post" runat="server">
<asp:placeholder id="placeHolder" runat="server"> </asp:placeholder>
</form>

CODE BEHIND
Dim newLbl As New Label
newLbl.ID = "Label1"
newLbl.Text = "This is a label control. "
placeHolder.Controls.Add(newLbl)

Dim newBtn As New Button
newBtn.ID = "Button1"
newBtn.Text = "This is a button control. "
placeHolder.Controls.Add(newBtn)

' To remove the controls
placeHolder.Controls.Remove("Label1")
placeHolder.Controls.Remove("Button1")

Cheers!

Authentication and Authorization in ASP .NET

Authentication and authorization are two tightly-coupled concepts to form the core of security for .NET applications.

Authentication is the process of determining and verifying the identity of users based on the users’ credentials. Authorization is the process of determining what level of access an authenticated identity should be granted to a given resource.

Whenever a user logs on to a system, he/she will be authenticated first before he/she is authorized.

Authentication
There are three types of authentication in ASP .NET:
1. Form authentication
2. Windows authentication
3. Passport authentication

Form Authentication
You can handle authentication using your own custom logic depends on code written in your .NET application. After a user is authenticated, the credentials will be stored in a cookie to handle subsequent processes.

Windows Authentication
Windows authentication is the default authentication mode in ASP .NET. By using this mode, a user is authenticated based on his/her Windows account. There are four types of Windows authentication methods:
1. Anonymous Authentication – IIS allows any user
2. Basic Authentication – windows username and password (credentials) has to be sent across the network in plain text format, which is insecure.
3. Digest Authentication – same as Basic Authentication but the credentials are encrypted.
4. Integrated Windows Authentication – Depend on Kerberos technology, with strong credential encryption.

Passport Authentication
Passport authentication uses Microsoft’s passport service to authenticate users in a .NET application. Passport uses an encrypted cookies mechanism to identify an authenticated user. If a user had signed in the passport when they browse to your application, he/she will be considered as authenticated by ASP .NET. Else they will be redirected to the passport login page.

Authorization
There are two types of authorization in ASP .NET:
1. URL authorization – specify authorization rules in web.config for different web URLs
2. File authorization - relying on the NTFS system for granting permission

Both authentication and authorization is specified in the web.config of the application.

Example in web.config

<configuration>
    <system.web>

        <authentication mode="[Windows/Forms/Passport/None]" >
        </authentication>

        <authorization>
            <allow users="July"/>
            <deny users="August"/>
            <deny users="?"/>
        </authorization>

    </system.web>
</configuration>