Differences Between SQL Server 2000 and 2005

Feature	SQL Server 2000	SQL Server 2005
Server Programming Extensions	Limited to extended stored procedures, which are difficult to write and can impact the server stability.	The incorporation of the CLR into the relational engine allows managed code written in .NET languages to run. Different levels of security can protect the server from poorly written code.
T-SQL Error Handling	Limited to checking @@error, no much flexibility.	Addition of TRY/CATCH allows more mature error handling. More error_xx functions can gather additional information about errors.
T-SQL Language	SQL Language enhanced from previous versions providing strong data manipulation capabilities.	All the power of SQL Server 2000 with the addition of CTEs for complex, recursive problems, enhanced TOP capabilities, PIVOT/APPLY/Ranking functions, and ROW_NUMBER
Auditing	Limited support using triggers to audit changes.	Robust event handling with EVENT NOTIFICATIONS, the OUTPUT clauses, and DDL triggers.
Large Data Types	Limited to 8k for normal data without moving to TEXT datatypes. TEXT is hard to work with in programming environments.	Includes the new varchar(max) types that can store up to 2GB of data in a single column/row.
XML	Limited to transforming relational data into XML with SELECT statements, and some simple query work with transformed documents.	Native XML datatype, support for schemas and full XPATH/XQUERY querying of data.
ADO.NET	v1.1 of ADO.NET included enhancements for client development.	v2 has more features, including automatic failover for database mirroring, support for multiple active result sets (MARS), tracing of calls, statistics, new isolation levels and more.
Messaging	No messaging built into SQL Server.	Includes Service Broker, a full-featured asynchronous messaging system that has evolved from Microsoft Message Queue (MSMQ), which is integrated into Windows.
Reporting Services	An extremely powerful reporting environment, but a 1.0 product.	Numerous enhancements, run-time sorting, direct printing, viewer controls and an enhanced developer experience.
ETL	DTS is a very easy to use and intuitive tool. Limited capabilities for sources and transformations. Some constructs, such as loops, were very difficult to implement.	Integration Services is a true programming environment allowing almost any source of data to be used and many more types of transformations to occur. Very complex environment that is difficult for non-DBAs to use. Requires programming skills.
Full-Text Search	Workable solution, but limited in its capabilities. Cumbersome to work with in many situations.	More open architecture, allowing integration and plug-ins of third party extensions. Much more flexible in search capabilities.

Sql Split function

CREATE FUNCTION Split

(

     @StringToSplit varchar(2048),

     @Separator varchar(128)

)

RETURNS TABLE AS RETURN

WITH CTE AS

(

     SELECT 0 StartIndex, 1 EndIndex

     UNION ALL

     SELECT EndIndex, CHARINDEX(@Separator, @StringToSplit, EndIndex) + LEN(@Separator)

     FROM CTE

     WHERE EndIndex > StartIndex

)

SELECT

     SUBSTRING(@StringToSplit

                        , StartIndex

                        , CASE WHEN EndIndex > LEN(@Separator)

                                  THEN EndIndex-StartIndex-LEN(@Separator)

                                  ELSE LEN(@StringToSplit) - StartIndex + 1

                         END) String

     ,StartIndex StartIndex     

FROM CTE

WHERE StartIndex > 0

How to export GridView to PDF (ASP.NET)

How to export GridView to PDF (ASP.NET)

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 = newSystem.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 = newiTextSharp.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.

Import Data from Excel File to sql datatable

Import Data from Excel File to sql datatableTo Export Data Form an Excel file, there is one drawback lying behind them. The Sheet1 from the query actually is the name of the spreadsheet in your excel file. What if there is no Sheet1 inside the excel file? What if you don’t even know the sheet name? 

Yes. I found this error when I tried to upload an excel file with different sheet name. And here is the solution for that.

To get the sheet name in your excel file, firstly, Microsoft DAO 3.5 Library is needed. Go to Project -> Reference -> Add Reference, select Microsoft DOA 3.5 Library from the list and add to your project. Here is the example code of how to get the first sheet in the excel file.

Dim strConn As String
Dim da As OleDbDataAdapter
Dim ds As New DataSet
Dim dao_dbE As dao.DBEngine
Dim dao_DB As DAO.Database
Dim strSheet As String

dao_dbE = New dao.DBEngine
dao_DB = dao_dbE.OpenDatabase("C:\test.xls", False, True, "Excel 8.0;")
strSheet = dao_DB.TableDefs(0).Name
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
"Data Source=C:\test.xls;Extended Properties=""Excel 8.0;"""

da = New OleDbDataAdapter("SELECT * FROM [" & _
strSheet & "]", strConn)
da.TableMappings.Add("Table", "Excel")
da.Fill(ds)

Import Data from Excel File to sql datatable

Import Data from Excel File to sql datatable

As I has stated in my previous post regarding to Export Data Form an Excel file, there is one drawback lying behind them. The Sheet1 from the query actually is the name of the spreadsheet in your excel file. What if there is no Sheet1 inside the excel file? What if you don’t even know the sheet name? 

Yes. I found this error when I tried to upload an excel file with different sheet name. And here is the solution for that.

To get the sheet name in your excel file, firstly, Microsoft DAO 3.5 Library is needed. Go to Project -> Reference -> Add Reference, select Microsoft DOA 3.5 Library from the list and add to your project. Here is the example code of how to get the first sheet in the excel file.

Dim strConn As String
Dim da As OleDbDataAdapter
Dim ds As New DataSet
Dim dao_dbE As dao.DBEngine
Dim dao_DB As DAO.Database
Dim strSheet As String

dao_dbE = New dao.DBEngine
dao_DB = dao_dbE.OpenDatabase("C:\test.xls", False, True, "Excel 8.0;")
strSheet = dao_DB.TableDefs(0).Name
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
"Data Source=C:\test.xls;Extended Properties=""Excel 8.0;"""

da = New OleDbDataAdapter("SELECT * FROM [" & _
strSheet & "]", strConn)
da.TableMappings.Add("Table", "Excel")
da.Fill(ds)

Export Datatable to Excel

Export Datatable to Excel(Not HTML Tables)

Recently I working with export data to an excel file, but had faced a lot of problems. The main problem of it: The exported file is saved as HTML tables with XLS extension, not the actual XLS format. In this way, I can’t re-import the data using the OleDbConnection.

I have been searching around the net, but no exact solution that suit my situation. Finally, I had found another way to export data into the actual XLS format, by using Excel.Application.

First thing that you need to do is to add Excel dll (Microsoft Excel 11.0 Object Library) as a reference.

Here are the codes.

Private Function ExportToExcel(ByVal dt As System.Data.DataTable)
    Dim xlsApp As New Excel.Application
    Dim xlsWorkbook As Excel.Workbook
    Dim xlsWorksheets As Excel.Worksheets
    Dim xlsWorksheet As Excel.Worksheet
    Dim strhdr As String
    Dim row As Integer
    Dim drow As DataRow
    Dim strFile As String = "test.xls"
    Dim filename As String = Server.MapPath("Doc") & "\" & strFile

    If dt.Rows.Count > 0 Then
        'Create new workbook
        xlsWorkbook =
        xlsApp.Workbooks.Add

        'Get the first worksheet
        xlsWorksheet = CType(xlsWorkbook.Worksheets(1), Excel.Worksheet)

        'Activate current worksheet
        xlsWorksheet.Activate()

        'Set header row to row 1
        row = 1

        'Add table headers to worksheet
        xlsWorksheet.Cells(row,1).Value = "NAME"
        xlsWorksheet.Cells(row, 2).Value = "JOB POSITION"
        xlsWorksheet.Cells(row, 3).Value = "ORIGIN"

        'Format header row (bold, extra row height, autofit
width)
        With xlsWorksheet.Range("A" & row, "C" & row)
            .Font.Bold = True
            .Rows(row).RowHeight = 1.5 * xlsWorksheet.StandardHeight
            .EntireRow.AutoFit()
        End With

        'Freeze the column headers
        With xlsWorksheet.Range("A" & row + 1, "C" & row + 1).Select
            xlsApp.ActiveWindow.FreezePanes = True
        End With

        'Write data to Excel worksheet
        For Each drow In dt.Rows
            row += 1
            If Not IsDBNull(dr.Item("NAME")) Then xlsWorksheet.Cells(row, 1).Value = dr.Item("NAME")
            If Not IsDBNull(dr.Item("JOB POSITION")) Then xlsWorksheet.Cells(row, 2).Value = dr.Item("JOB POSITION")
            If Not IsDBNull(dr.Item("ORIGIN")) Then xlsWorksheet.Cells(row, 3).Value =
dr.Item("ORIGIN")
        Next

        'Format data rows (align to top, autofit width and height)
        With xlsWorksheet.Range("A2", "C" & row)
            .VerticalAlignment = CType(XlVAlign.xlVAlignCenter, Excel.XlVAlign)
            .HorizontalAlignment = CType(XlHAlign.xlHAlignLeft, Excel.XlHAlign)
            .EntireColumn.AutoFit()
            .EntireRow.AutoFit()
        End With

        'Make excel workbook visible to user after all data has been added to worksheet
        xlsApp.DisplayAlerts = False
        xlsWorkbook.Close(True, filename)

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

Difference between NVARCHAR and VARCHAR in SQL Server

Difference between NVARCHAR and VARCHAR in SQL Server

The broad range of data types in SQL Server can sometimes throw people through a loop, especially when the data types seem to be highly interchangeable. Two in particular that constantly spark questions areVARCHAR and NVARCHAR: what's the difference between the two, and how important is the difference?

VARCHAR is an abbreviation for variable-length character string. It's a string of text characters that can be as large as the page size for the database table holding the column in question. The size for a table page is 8,196 bytes, and no one row in a table can be more than 8,060 characters. This in turn limits the maximum size of a VARCHAR to 8,000 bytes.

The "N" in NVARCHAR means uNicode. Essentially, NVARCHAR is nothing more than a VARCHAR that supports two-byte characters. The most common use for this sort of thing is to store character data that is a mixture of English and non-English symbols — in my case, English and Japanese.

The key difference between the two data types is how they're stored. VARCHAR is stored as regular 8-bit data. But NVARCHAR strings are stored in the database as UTF-16 — 16 bits or two bytes per character, all the time — and converted to whatever codepage is being used by the database connection on output (typically UTF-8). That said, NVARCHAR strings have the same length restrictions as their VARCHAR cousins — 8,000 bytes. However, since NVARCHARs use two bytes for each character, that means a given NVARCHAR can only hold 4,000 characters (not bytes) maximum. So, the amount of storage needed for NVARCHAR entities is going to be twice whatever you'd allocate for a plain old VARCHAR.

Because of this, some people may not want to use NVARCHAR universally, and may want to fall back on VARCHAR — which takes up less space per row — whenever possible.

Here's an example of how to mix and match the use of the two types. Let's say we have a community website where people log in with a username, but can also set a public "friendly" name to be more easily identified by other users. The login name can be a VARCHAR, which means it must be 8-bit ASCII (and it can be constrained further to conventional alphanumerics with a little more work, typically on the front end). The friendly name can be an NVARCHAR to allow Unicode entities. This way you're allowing support for Unicode, but only in the place where it matters most — both for the users, and where the extra storage space is going to be put to the best possible use.

VARCHAR and NVARCHAR in SQL Server 2005

One fairly major change to both VARCHAR and NVARCHAR in SQL Server 2005 is the creation of the VARCHAR(MAX) and NVARCHAR(MAX) data types. If you create a VARCHAR(MAX) column, it can hold up to 2^31 bytes of data, or 2,147,483,648 characters; NVARCHAR(MAX) can hold 2^30 bytes, or 1,073,741,823 characters.

These new data types are essentially replacements for the Large Object or LOB data types such as TEXT and NTEXT, which have a lot of restrictions. They can't be passed as variables in a stored procedure, for instance. The(MAX) types don't have those restrictions; they just work like very large string types. Consequently, if you're in the process of re-engineering an existing data design for SQL Server 2005, it might make sense to migrate some (although not all!) TEXT / NTEXT fields to VARCHAR(MAX) / NVARCHAR(MAX) types when appropriate.

The big difference between VARCHAR and NVARCHAR is a matter of need. If you need Unicode support for a given data type, either now or soon enough, go with NVARCHAR. If you're sticking with 8-bit data for design or storage reasons, go with VARCHAR. Note that you can always migrate from VARCHAR to NVARCHAR at the cost of some room -- but you can't go the other way 'round. Also, because NVARCHAR involves fetching that much more data, it may prove to be slower depending on how many table pages must be retrieved for any given operation.