How to preview various documents in Silverlight Application:

I had to display various documents for preview in my Silverlight application. All the file data is stored in database. We do not want to store these files in File System. This is to make sure that this will not come and byte us when we choose Cloud. I spent enough time in googling. Finally I found out below two solutions:
   1. IFrame or DIV in silverlight application
   2. Use RadHtmlPlaceHolder telerik control.
  
I found RadHtmlPlaceHolder control more flexibile and easy to implement.

Here are the steps for my POC:
I had to display various documents in my Silverlight application. All the file data is stored in database. We do not want to store these files in File System. This is to make sure that this will not come and byte us when we choose to use in Cloud. I spent enought time in googling. Finally I found out below two solutions:
   1. IFrame or DIV in silverlight application
   2. Use RadHtmlPlaceHolder telerik control.
  
I found RadHtmlPlaceHolder control more flexibile and easy to implement.
Here are the steps for my POC:

Step #1:Create a table in SQL server which will hold file data.

CREATE TABLE [dbo].[FileUpload](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [FileName] [varchar](50) NULL,
 [FileContent] [varbinary](max) NULL,
 [ContentType] [varchar](50) NULL,
 CONSTRAINT [PK_FileUpload] PRIMARY KEY CLUSTERED
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

• File Name: File name
• File Content: Stored file content in VarBinary. For front end(C#) code it would be array of bytes.
• Content Type: Store type of the file. Example: PDF, JPG, PNG, etc.

Note: Write a tool to save some sample files in database. This code is out of scope for this blog. You may google for this.

Step #2:
Create a stored procedure to read File data from database.
CREATE Procedure [dbo].[GetFile]
 @Id int
AS
 Begin
  Select top 1 FileContent from  [FileUpload] where id=@id
End
The above stored procedure will read a document based on document id.

Step #3:
Create an .ASPX page for preview document on the screen. This page will be used as source for RadHtmlPlaceHolder control.
Write a method to read File content from database:
 public byte[] GetDocument(int docId)
        {
            byte[] result = null;
            using (SqlConnection aspnetDbConnection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = aspnetDbConnection.CreateCommand();
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "GetFile";
                SqlParameter paramFileName = new SqlParameter("@Id ", docId);
                command.Parameters.Add(paramFileName);
                aspnetDbConnection.Open();
                result = (byte[]) command.ExecuteScalar();
            }
            return result;
        }
Create below two methods in .ASPX code behind.
  // Declare this variable to hold document Id
  int docId;

  //This is Page Load event of ASPX page
        protected void Page_Load(object sender, EventArgs e)
        {
   // Read querystring of document id. This querystring is passed from Silverlight page
            docId = Convert.ToInt32(Request.QueryString["docid"]);

   LoadDocumentPDF();
           
        }

        void LoadDocumentPDF()
        {
            DocumentsService service = new DocumentsService();
   // Call GetDocument method to read byte[] of the required document
            byte[] buffer = service.GetDocument(docId);

   // Provide content type information here. In this case I know the content type is PDF. This content type can be many types. For example JPG, DOC, PNG etc
            Response.ContentType = "application/pdf";
            Response.AddHeader("content-length", buffer.Length.ToString());
            Response.BinaryWrite(buffer);

        }

Step #4:
Now its time to write some code in Silverlight.
Add RadHtmlPlaceholder control in XAML file:

< telerik:RadHtmlPlaceholder x:Name="TheHtmlPlaceHolder" Grid.Row="1" RespectSilverlightLayoutMeasure="True" >< /telerik:RadHtmlPlaceholder >

In code behind, paste below two lines to display required document. At this moment, I have hard coded Document Id to "2". In read document Id should be dynamic.

 Uri uri = new Uri("http://localhost:2233/DocumentViewer.aspx?docid=2", UriKind.RelativeOrAbsolute);
TheHtmlPlaceHolder.SourceUrl = uri;

ADO.Net Batch Update

Lately I had to write a windows service which has more number of insert/undate statements. During this process we faced performance challanges. To over come this we have taken ASP.Net batch update technique. It worked really well for us. In this approach, we can execute a chunk of DML (Insert, Update, Delete) statements at once. I set batch size to 10,000 update statements. It worked flawlessly. To achvie this I have used ADO.Net in data layer. SqlDataAdapter class plays major role in ths process.


Enough of theory. Let us get into sample on how to do this.

Example: Update employee Grade of all employees in an organization. In this example we will update 10,0000 employees grades


Step #1: Create datatable which holds all employees.


DataTable dt = new DataTable("Employees");
DataColumn dcEmployeeId = new DataColumn("EmployeeId");
DataColumn dcGradeId = new DataColumn("Grade");
dt.Columns.Add(dcEmployeeId);
dt.Columns.Add(dcGradeId);
// employee is a collection of employees. Assume that it has 10,000 employees in it.
foreach (var emp in employees)
{
var row = dt.NewRow();
row["EmployeeId"] = emp.EmployeeId;
row["Grade"] = emp.Grade;
dt.Rows.Add(row);
// Since we are planning to update this data, below three statements
row.AcceptChanges();
row.BeginEdit();
row.EndEdit();
}


Step #2: Execute batch from Data Access Layer:

SqlCommand sqlCommand = null;
SqlDataAdapter adapter = null;
SqlConnection testConnection = null;
using (testConnection = new SqlConnection(<< Connection String >>))
{
adapter = new SqlDataAdapter();
var updateSQL = string.Format(@"UPDATE emp SET emp.Grade = @Grade
                      FROM Employees emp
                           WHERE emp.EmployeeId = @EmployeeId");
sqlCommand = new SqlCommand(updateSQL, testConnection);
adapter.UpdateCommand = sqlCommand;
adapter.UpdateCommand.Parameters.Add("@EmployeeId", SqlDbType.Decimal, 4, "EmployeeId");
adapter.UpdateCommand.Parameters.Add("@Grade", SqlDbType.Int, 4, "Grade");
adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
adapter.UpdateBatchSize = 0;
// Execute the update. Below database is the table created in step #1
adapter.Update(dt);
}

Saperating coma separated values in a string using T-SQL ; SQL Server

If you do lot of application development, you often come across to a situation where we need to split values of a string with a delimiter. The delimiter could be coma. Here is the T-SQL script on how to do it.

Declare @OrderList varchar(500) = '1,2,3,4,78,987'

DECLARE @OrderId varchar(10), @Pos int


SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)

IF REPLACE(@OrderList, ',', '') <> ''
 BEGIN
   WHILE @Pos > 0
     BEGIN
         SET @OrderId = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
            IF @OrderId <> ''
                  BEGIN
                   -- Here we can write necessary code with OrderId
                   print @OrderId
               END
          SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
          SET @Pos = CHARINDEX(',', @OrderList, 1)
   END
END

Recompile all Stored Procedures in database.

If you make some performance turning on existing database, all the change may not reflect in existing stored procedures. Your existing SPs may not take all changes to schema, indexes, table partitioning etc. SPs may not give you the performance results you are expecting. So it is wise to recompile all the stored procedures. We can use sp_recompile system SP to recompile one stored procedure. However  if you want recompile all SPs in database then we need a script. Below is the script you can execute to recompile all SPs in your database.


-- Use <>
DECLARE @SPName AS VARCHAR(255)
-- Cursor fo reading all Stored Procedures in database
DECLARE cursor_AllSPs CURSOR FOR
SELECT [name]
FROM sysobjects
WHERE xtype = 'P'


OPEN cursor_AllSPs


FETCH NEXT FROM cursor_AllSPs into @SPName


WHILE (@@FETCH_STATUS <> -1)
 BEGIN
  FETCH NEXT FROM cursor_AllSPs INTO @SPName
  -- sp_recompile will mark SP for recompile
  EXEC sp_recompile @SPName
 END
CLOSE cursor_AllSPs
DEALLOCATE cursor_AllSPs
 
The above script will mark all stored procedures for compilation. When we execute SPs next time, those will be compiled.

Searching Near By Addresses on Map using Haversine formula in SQL Server

In one of my projects, we have a requirement to search near by locations. Our application front is Silverlight and backend is SQL Server 2008. In this article I am sharing my solution from SQL Server point of view. I have used Haversine formula to solve the problem. Click here to see more details on Haversine formula.

I have Locations table. This table has below fields:

• LocationId
• LocationName
• StreetAddress
• City
• Zip
• State
• Country
• Description
• Logitude
• Latidue

In this application we have used Bing Map Provider. Click here to see more details on Bing Map Provider. While saving data in Locations, the application checks for valid address. And stores Latitude and Longitude of the address.

In the Nearby search screen, allow user to enter full address of location where he/she wants to search for nearby locations. If it is valid address then we extract latitude and longitude of the address user entered.

Now front end passes latitude, and longitude to business layer. Let us see on how we can extract nearby locations of given address (latitude and longitude). SQL Server Stored proedure accepts maximum distance of search radius. You can make this configurable item (web.config entry) in web service.

Step #1: Create a function in SQL Server. The details of the function are:

Function Name: IsLocationFallInRadius
Purpose: This function takes latitude and longitude of two locations. Also it takes allowed maximun distance between these two locations. It retuns 1(true) if two locations fall within maximum distance. Returns 0 (false) if if two locations does not fall within maximum distance.

create function dbo.IsLocationFallInRadius
(
@Latitude1 float,
@Longitude1 float,
@Latitude2 float,
@Longitude2 float,
@MaxDistance float
)
returns bit
as
/*
Function: IsLocationFallInRadius
Computes the Great Circle distance in miles
between two points on the Earth using the
Haversine formula distance calculation.


Input Parameters:
@Longitude1 - Longitude in degrees of point 1
@Latitude1 - Latitude in degrees of point 1
@Longitude2 - Longitude in degrees of point 2
@Latitude2 - Latitude in degrees of point 2
*/
begin
declare @radius float
declare @lon1 float
declare @lon2 float
declare @lat1 float
declare @lat2 float
declare @a float
declare @distance float
declare @IsInRadius bit


-- Sets average radius of Earth in miles
set @radius = 3959.0E
-- Convert degrees to radians
set @lon1 = radians( @Longitude1 )
set @lon2 = radians( @Longitude2 )
set @lat1 = radians( @Latitude1 )
set @lat2 = radians( @Latitude2 )


set @a = sqrt(square(sin((@lat2-@lat1)/2.0E)) +  (cos(@lat1) * cos(@lat2) * square(sin((@lon2-@lon1)/2.0E))) )


set @distance = @radius * ( 2.0E *asin(case when 1.0E < @a then 1.0E else @a end ))
if(@distance > @MaxDistance)
Begin
 SET @IsInRadius = 0
End
Else
Begin
 SET @IsInRadius = 1
End
return @IsInRadius
end

Step #2: Create Stored Procedure to retrive records from Locations table.
Stored Procedure Name: SearchLocationsNearBy
Purpose: It takes location latitude and longitude of search input. It also takes maximum distance of search. It returns locations which fall within maximum distance (within radius).

CREATE PROCEDURE [dbo].[SearchLocationsNearBy]

@Latitude float,
@Longitude float,
@MaxDistance float
AS
Begin
 select * from Locations
   Where  dbo.IsLocationFallInRadius(@Latitude,@Longitude, Latitude, Longitude,@MaxDistance) = 1
End

In the where clause of query we I have called IsLocationFallInRadius function to vaidate if the records falls within the given rage. Business layer can invoke SearchLocationsNearBy stored procedure for searching.

Print Mulitple pages using Silverlight

When you have a single page to print in silverlight, it is straight forward code. However if you want to print multiple pages, developer need to add some logic to the code. We should let printer know how each page is spiltted.


Single Page Print: Let us write a sample code to print single page. For this purpose, create a simple silverlight page.

• Place a button on silverlight page.
• Set content (label of button) to Print.
• Trap click event of button.

Button Width="65" Content="Print" Height="25" Click="PrintButton_Click" HorizontalAlignment="Right" Margin="3"/

Handle click event of Print Button in code behind.


private void PrintButton_Click(object sender, RoutedEventArgs e)
{
 PrintDocument docToPrint = new PrintDocument();
 docToPrint.PrintPage += new EventHandler(docToPrint_PrintPage);
 docToPrint.EndPrint += new EventHandler(docToPrint_EndPrint);
 docToPrint.Print("Remittance Notice");
}

• Create an object of PrintDocument class.
• Trap PrintPage and EndPrint events of PrintDocument object.
• PrintPage will trigger when user invokes print functionality.
• EndPrint will be triggered after printing completes.
• Call Print() method of PrintDocument object. This will send print request to printer. Print method takes document name as parameter.

int indexValue = 0; // declare a global variable for print line count.

void docToPrint_PrintPage(object sender, PrintPageEventArgs e)
{
 StackPanel itemHost = new StackPanel();
 while(indexValue < 40) // Loop throught for Fourty Lines.
 {
  TextBlock tb = new TextBlock();
  tb.Text = "This is my line number " + indexValue.ToString();
  itemHost.Children.Add(tb); // Add TextBlock to Stack Panel.
  indexValue++;
 }
  e.PageVisual = itemHost; // Set Stackpanel to PageVisual of    PrintPageEventArgs.
}


Above method is called then PrintPage event of PrintDocument class invoked. In this method,

• Create an object of StackPanel. This will be the print document.
• Add all the content to StackPanel which is required for printing.
• In the above case we are printing Fourty lines

void docToPrint_EndPrint(object sender, EndPrintEventArgs e)
{
 // todo: if required write needed after printing document.
}
Above method is called after printing is completed.


Multiple Page Print: The above code works if it is printing only one page. In the above case we are printint fourty lines of document. In case if user wants to print 500 lines of document, above code will fail. The above code will print first page only and ignores remaning pages. To achived that we need to write below code.

int indexValue = 0;
void docToPrint_PrintPage(object sender, PrintPageEventArgs e)
{
 StackPanel itemHost = new StackPanel();
 while(indexValue < 500) // Loop throught for Five Hundered Lines.
 {
  TextBlock tb = new TextBlock();
  tb.Text = "This is my line number " + indexValue.ToString();
  itemHost.Children.Add(tb);
  itemHost.Measure(new Size(e.PrintableArea.Width,   double.PositiveInfinity)); // To update designed size of StackPanel
  if (itemHost.DesiredSize.Height > e.PrintableArea.Height) // Check if Stack   Panel Height is more than printable area height
 {
  itemHost.Children.Remove(tb);
  e.HasMorePages = true; // Set HasMorePages to true. This will split(page break) the page.
  break; // Come out of while loop.
 }
   indexValue++;
}
 e.PageVisual = itemHost;
}


So the trick to print multiple pages is: Spilt print document after it reaches maximum height of a given page height. Then set HasMorePages of PrintPageEventArgs object to true.