CLR To Return Full Gregorian Date By Passed Um Alqura Month And Year

In this article I will show how to create new CLR to be registered in SQL database.

This CLR includes function to convert passed Um Alqura month and year, then returns full Gregorian date.

(I mean by full, the first day of the Um Alqura date with month and year passed converted to Gregorian)

By creating new SQL server project, in visual studio 2012:

Right click on project, add new SQL CLR C# user-defined function:

The beginning of the month: 
Just add the following code:

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static DateTime ConvertToGregorianStartDate (int Year, int Month)
    {
        System.Globalization.UmAlQuraCalendar UMQ_Calender = new System.Globalization.UmAlQuraCalendar();
        return UMQ_Calender.ToDateTime(Year, Month, 1, 0,0,0,0);
    }
}


The end of the month:
Just add the following code:

[Microsoft.SqlServer.Server.SqlFunction]
    public static DateTime ConvertToGregorianEndDate(int Year, int Month)
    {
        System.Globalization.UmAlQuraCalendar UMQ_Calender = new System.Globalization.UmAlQuraCalendar();
        DateTime firstDayOfTheMonth = UMQ_Calender.ToDateTime(Year, Month, 1, 0, 0, 0, 0);
        return firstDayOfTheMonth.AddMonths(1).AddDays(-1);
    }

Build the project and publish it to your DB.

if you execute the function created, you will get the following result:

Change Label From Persian Gulf To Arabian Gulf In Google Maps

By default, Google select Persian Gulf to be displayed as label on the gulf:

 To change it to Arabian Gulf, just add the following query string to Google map API URL:
region=SA

https://maps.googleapis.com/maps/api/js?sensor=false&language=SA&region=SA

Dealing With Google Maps - ASP.NET - Part (3)

In this article, I will show you how to store and retrieve data from SQL Server DB to Google map and vise versa.

Save data from Google map to SQL table:
Create table to store the following information:
  - Latitude
  - Logitude
  - Description of the location

our interface should be like the following:

After click SAVE button, information in text boxes should be saved to our table.

Retrieve data from SQL table to Google map:

Retrieve the data from SQL table by your own way, and define three public properties and assign value to them, those properties will called in HTML.

public string Longitude{get; set;}
public string Latitude{get; set;}
public string Desc{get; set;}

To change marker position to be like values stored in table, just change location properties from:
myCoordinates = new google.maps.LatLng( 24, 47);
to
myCoordinates = new google.maps.LatLng('<%= Latitude %>', '<%= Logitude %>');

now, we want to display location description if user click to the marker.
First change draggable property of the marker from true to false, to be read only.
Then, add the following to script:

// define new info window and assign location description
var infoWindow = new google.maps.InfoWindow({
            content: '<%= Desc %>'
        });

// add click listener to marker
google.maps.event.addListener(marker, 'click', function () {
            infoWindow.open(map, marker);
        });

and if you run the page, and click to marker, you will get the following result:

Enjoy ..

Dealing With Google Maps - ASP.NET - Part (2)

After showing how to load Google map in your asp.net page in this article, we will move to dealing with map marker and its properties.

Marker used to specify location using coordinates, called Longitude and Latitude.

to load marker in your map, just add the following script:

function initialize() {

        // initial location of marker

        var myCoordinates;

        var marker;

        var mapOptions = {

            // 24, 47 is saudi arabia coordinates

            center: new google.maps.LatLng(24, 47),

            zoom: 5,

            mapTypeId: google.maps.MapTypeId.ROADMAP

        };

        var map = new google.maps.Map(document.getElementById("googleMap"), mapOptions);

        // initial location of marker

        myCoordinates = new google.maps.LatLng(24, 47);

        // set marker properties

        marker = new google.maps.Marker({

            position: myCoordinates,

            map: map,

            draggable: true

        });

    }

    google.maps.event.addDomListener(window, 'load', initialize); 

if you play this page, you will get draggable marker in your map:

if you want marker read-only, just change the property of marker:
draggable : false

Now, we want to show coordinates( longitude and latitude ) in text boxes. Just add the following texts inside html body:

<input id="txtLat" type="text" runat="server" />
<input id="txtLong" type="text" runat="server" /> 

and add the following listener to your script (inside initialize() method):

google.maps.event.addListener(marker, 'drag', function () {

   document.getElementById("<% =txtLat.ClientID %>").value = marker.position.lat().toString();

   document.getElementById("<% =txtLong.ClientID %>").value = marker.position.lng().toString();

});

then play your page, and try to move marker, you will get the following:

there is a lot of events of marker, one of them 'dragend', which will called after marker dragged, 'click' .. etc.

Dealing With Google Maps - ASP.NET - Part (1)

In this article, I'll show you how to load Google map in your page. Then, we will move to dealing with markers, how to customize it and apply data which retrieved from SQL database to Google map.

First Step:

create new web application, using visual studio, then register the following reference to web page, or to your master page.

<script type="text/javascript" src="https://maps.googleapis.com/maps/api/js?sensor=false"/>

Second Step:
just copy the following script and put it in your page in order to display Google map.

<script>
function initialize() { var mapOptions = {
       // 28, 47 is saudi arabia coordinates 

       center: new google.maps.LatLng(28, 47), 

       zoom: 5, mapTypeId: google.maps.MapTypeId.ROADMAP }; 

       var map = new google.maps.Map(document.getElementById("googleMap"), mapOptions); 

 }

// this listener will call the method initialize() when page load
google.maps.event.addDomListener(window, 'load', initialize); 

</script> 

Third Step:
inside your page body, set the following div

<div id="googleMap" style="width:500px;height:380px;"></div>

then run the page, you will see the map in your page.

if you want to change map language to Arabic or another language, just pass Language code via query string like the following:

<script type="text/javascript" src="https://maps.googleapis.com/maps/api/js?sensor=false&Language=SA"/>

SQL Error: no process is on the other end of the pipe

If you get this error "no process is on the other end of the pipe" when you want to login to sql server management studio, just go to:

START -> ALL Programs -> Microsoft SQL Server (Version ) -> Configuration Tools -> SQL Server Configuration Manager 

from SQL Server Network Configuration, just enable Named Pipe protocol as the following picture:

then, restart your SQL Server Services and it will work.

How to install Anslysis Service, Tabular Mode

During the installation of SQL Server 2012, there is step to select your Analysis service mode. you can only select one of those modes, Tabular or Multidimensional mode, in specific SQL Server instance.

Note that, Tabular mode is not available in other SQL Server versions. Its only in 2012.

You can create new instance of SQL Server to install Analysis Service, Tabular Mode:

Enjoy Analyzing.

SSAS Tabular Mode Error: The Workspace Database Server 'localhost' Is Not Running In Tabular Mode

when you try to create new tabular model or multidimensional model using SSDT. You may face this error:
"the workspace database server 'localhost' is not running in tabular mode" when you try to connect to local host analysis server.

to solve this error, make sure you already install analysis service + make sure to connect with correct workspace server, if its not 'localhost'. Final solution, try to connect with localhost\SqlServerInstanceName because analysis service takes default sql instance name in installation step.

How To Localize Asp.Net Page Title

As we know, we use GetLocalResourceObject("ResourceName") to get local resource value from local resource file.
Or GetGlobalResourceObject("FileName", "Key") to get resource from global resource file.

In order to localize asp.net page title we should use meta:resourceKey like the following:
<%@ Page meta:resourcekey="PageTitle" %>
and in the resource file, we set PageTitle.Title to access Title value in the page.
and we cann't use GetLocalResourceObject("ResourceName") or GetGlobalResourceObject("FileName", "key") to localize page title in HTML, but you can use it in Code behinde.

How to remove last character from string in C#

Suppose you have the following sentence:

"Riyadh - Jeddah - Dammam -"

which comes from list of cities and separated by (-) after each city.

you need to remove last separator.

just use the following code:

// remove last separator

 if (lblInputRegions.Text.EndsWith("-"))

         lblInputRegions.Text = lblInputRegions.Text.Remove((lblInputRegions.Text.Length - 1), 1);

SSRS - Working With Map Control (1)

In this article, I will explain how we can use MAP control in SSRS.

MAP controls is new control added in new version of SSRS. You can present maps of the world, country, buildings, areas, roads ... etc.

I'm going to separate this article to multiple parts.

In this part, I'm going to show you how to use SSDT and create new SSRS project. Then, we will move to MAP control and describe its features and usage.

If you download SQL Server 2012, SSDT (SQL Server Data Tools) and Visual Studio 2010 will installed in your machine automatically.
If not installed, you can download it and install it in your machine
through this link: Click Me.  and for more information about versions and installation read this: Click Me

Now, open SSDT:

From File menu, create new project and set solution name "TestingMaps_SSRS", for example.

Create new report without data source, then drag and drop MAP control from Toolbox menu:

Now see the first window of the configuration:

Let me explain how map works, the first option is default option which shows MAP for USA.
you can select USA by state, USA by state exploded and USA by state inset.
In addition you can select specific state map for USA under States by Country folder.
The different between them is the data. I mean the map, the information of state ...etc.

The second option is ESRI shapefile. its file shows information about selected map.
for example you can download ESRI shapefile for KSA which shows KSA map, administrative areas, area codes, area phone code .... etc.

But the question is, where can I find the ESRI file?
There are a lot of sites provide ESRI files for FREE! and the following some of them:
- www.diva-gis.org
- www.vdstech.com
- www.gadm.org

The last option is from SQL Special Query. which means ESRI file inserted in SQL table.

In this part in may article, we will focus the first option.

Select "USA by state exploded" then press Next button:

In the second screen, you can change map appearance, quality and connect your map with Microsoft Bing Maps:

The third screen shows map visualization. You can select Basic Map, Color Analytical  Map or Bubble Map.

The Basic Map shows map as it is.
Color and Bubble maps should be connected with analysis service or data source to describe more data.
For example, State be Sales, State by Census, State by Customers ... etc.

We will select Basic Map:

The last screen select map theme. there are multiple themes select your favorite one.
There is check box called "Display Labels" check it and select "#STUSPS" from data field drop down list.
The "#STUSPS" will display state name shortcut in the map.
Press Finish and browse the report, you will see the following result:

Finally, you can change MAP TITLE from map properties.

SSRS - Presenting Dynamic Images From Database To Report

I will explain how to represent images stored in SQL DB into report.

I expected you already save image in SQL DB as varbinary.



STEP -1
Create project SSRS project.


STEP -2
Create shared data source and report.

STEP -3
Create data source and select your image binaries, I supposed the image binary column named FileContent.






STEP -4
Now, in report body, add new table then add new image from toolbox in the first second row of the table and set the following properties:


Then press OK. The result will be like the following:

SSRS - Localize Microsoft Report Viewer

Scenario:
We have multi-language site. This site contains a lot of reports presented in different languages. But we have problem. The report viewer, the control which shows report, is English. We need to localize this control. Can you do it?

Answer:
Yes, its so simple.
there is interface called 'IReportViewerMessages'. Via this interface you can change report viewer internal controls value as the following steps:

STEP -1:
Add a class named, for example, 'ReportViewerMessages' which implement 'IReportViewerMessages'.

STEP -2:
Create GlobalResource named 'ReportViewerResource'.

STEP- 3:
Add the following line to your web.config:
<add key="ReportViewerMessages" value="YourClass, YourAssembly" />

Build and run your project and the result will be like the following: