Business Intelligence with Syncfusion

These days Business Intelligence(BI) is a pretty decent topic in all kind of organizations. One of the nice features of BI would be ability to create Ad-Hoc reports. Ad-Hoc reporting is the process of creating reports on the fly with little or no training of reporting. This has been a major advantage of BI, because end users can create their own reports without taking much time.

Today I am going to write about one of the nice BI tools/controls which I have came across. Syncfusion Essential Studio Business Intelligence Edition is a part of Syncfusion product suite. Syncfusion main product suite which is Syncfusion Essential Studio provides some nice controls to work with various areas in Microsoft .NET framework. Since I am mainly focusing on Business Intelligence, I will be only talking about Syncfusion Essential Studio Business Intelligence Edition here.

Syncfusion Essential Studio Business Intelligence Edition has some nice controls for following areas.

1. Business Intelligence Edition for ASP.NET
2. Business Intelligence Edition for ASP.NET MVC
3. Business Intelligence Edition for Silverlight
4. Business Intelligence Edition for WPF
5. Business Intelligence Edition for Windows Forms

My favorite control is Syncfusion Olap Client which supports Ad-Hoc reporting. What you only have to do is simply pass in the connection string to an SQL Server Analysis Services(SSAS) database or any XML for Analysis(XMLA) provider and the database name, and the Olap Client has the ability to retrieve all the cubes available within that database, the different dimensions, measures, and KPIs that are in the cube, and display them in an easily navigable tree.

Because of the Syncfusion Essential Studio Business Intelligence Edition's features, it comes with a price. And that is the only bad thing I have seen in this product until now.

Happy Coding.

Regards,
Jaliya

jQuery UI - Modal Message

I was always not good in designing nice web sites, but this jQuery UI JavaScript library is there to help me on that. Today I am going to write about how to show a nice message box, which is a modal using jQuery UI.

First I need to have jQuery and jQuery UI libraries installed on my web application. I can easily use NuGet to do the installation.

Installing jQuery library

Installing jQuery UI library

Then I have selected a theme from jQuery UI Themes. There are a lot of themes available and even you can customize the themes. I have downloaded a theme and put it into themes folder under Content folder.

Adding a theme

Then I have added following lines to the master page.

    <script src="Scripts/jquery-1.7.2.js" type="text/javascript"></script>
    <script src="Scripts/jquery-ui-1.8.20.js" type="text/javascript"></script>
    <link href="Content/themes/redmond/jquery-ui-1.8.22.custom.css" rel="stylesheet" type="text/css" />

I am adding a div to my page, and it will be the modal.

    <div id="dialog-alert" title="Message">

    </div>

I am writing a method in the code behind file which will contain the JavaScript function.

        private void ShowJqueryMessage(string message)
        {
            try
            {
                //Initialize the stringbuilder object to append javascript
                StringBuilder sb = new StringBuilder();
                sb.Append("$(function() { ");
                sb.Append("$('#dialog-alert').empty();");
                sb.Append("$('#dialog-alert').append('" + message + "');");
                sb.Append(" $('#dialog-alert').dialog({");
                sb.Append("    width: 350,");
                sb.Append("buttons: {");
                sb.Append("'Close': function () {");
                sb.Append("$('#dialog-alert').dialog('close');");
                sb.Append("}");
                sb.Append("   }");
                sb.Append(" });");
                sb.Append("});");
                //Register the script on page startup
                ClientScript.RegisterStartupScript(typeof(Page), "myscript", sb.ToString(), true);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

Then in my button click event I am calling the method.

        protected void Button1_Click(object sender, EventArgs e)
        {
            ShowJqueryMessage("Message.");
        }

Message in the modal

That's it. Hope this helps.

Happy Coding.

Regards,
Jaliya

SQL Server Transactions

Transactions can be really important when we are working with databases. For example let’s say you need to execute two or more SQL commands sequentially and if any statement fails, all other statements should not be executed. To achieve this, we can use Transactions.

Mainly transactions has these four properties which are abbreviated as ACID.

1. Atomic
   • Atomic means that all the work in the transaction is treated as a single unit. That is either all statements in the unit must execute, or no statement in the unit must execute.
2. Consistent
   • Consistent means that a completed or roll backed transaction leaves the database in a consistent internal state. A transaction will convert the database from a known starting state to a known ending state. If the transaction commits, the database will be at the known ending state. If the transaction fails, the database will be at the known starting state.
3. Isolated
   • Isolations means that the transaction sees the database in a consistent state. If two transactions try to update the same table, one will go first and then the other will follow. Transactions are committed independently from each other and they are transparent to each other.
4. Durability
   • Durability means that the results of the transaction are permanently stored in the system and ensures that the result or effect of a committed transaction persists in case of a system failure.

There are couple of transaction types.

1. Autocommit Transactions
   • Autocommit mode is the default transaction management mode of the SQL Server Database Engine. Every Transact-SQL statement is committed or rolled back when it completes. If a statement completes successfully, it is committed; if it encounters any error, it is rolled back.
   • But here there is something to note. That is sometimes Database Engine has rolled back an entire batch instead of just one SQL statement. This happens if the error encountered is a compile error, not a run-time error. A compile error prevents the Database Engine from building an execution plan, so nothing in the batch is executed. Let’s the following examples.
   • In this example, there is a compile time error in the third line. So the error prevented anything in the batch from being executed.

     INSERT INTO TestTable VALUES (1, 'aaa');

     
     

     INSERT INTO TestTable VALUES (2, 'bbb');

     
     

     INSERT INTO TestTable VALUSE (3, 'ccc');  -- Syntax error. 

     
     
     

     SELECT * FROM TestTable ;  -- Returns no rows.

   • In this example, there is a run time duplicate primary key error in he third line. The first two INSERT statements are successful and committed, so they remain after the run-time error.
     INSERT INTO TestTable VALUES (1, 'aaa');
     

     
     

     INSERT INTO TestTable VALUES (2, 'bbb');

     
     

     INSERT INTO TestTable VALUES (1, 'ccc');  -- Duplicate key error.

     
     
     

     SELECT * FROM TestTable -- Returns rows 1 and 2.

2. Explicit Transactions
   • An explicit transaction is one in which you explicitly define both the start and end of the transaction. DB-Library applications and Transact-SQL scripts use the BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK Transact-SQL statements to define explicit transactions.
     • BEGIN TRANSACTION
       Marks the starting point of an explicit transaction for a connection.
     • COMMIT TRANSACTION or COMMIT WORK
       Used to end a transaction successfully if no errors were encountered. All data modifications made in the transaction become a permanent part of the database. Resources held by the transaction are freed.
     • ROLLBACK TRANSACTION or ROLLBACK WORK
       Used to erase a transaction in which errors are encountered. All data modified by the transaction is returned to the starting state which it was in at the start of the transaction. Resources held by the transaction are freed.
3. Implicit Transactions
   • When a connection is operating in implicit transaction mode, the instance of the SQL Server Database Engine automatically starts a new transaction after the current transaction is committed or rolled back. If there is an INSERT statement running as an Implicit Transaction, then a separate transaction is created for that. If it was an explicit transaction, several INSERTs would be wrapped together in one transaction. Also for an Implicit Transaction, SQL Server needs to write the transaction log to disk every time . The session running the Implicit Transaction will remain open until a COMMIT/ROLLBACK transaction command is issued.
4. Distributed Transactions
   • At times you might have to execute transactions that span more than one server. These transactions can only be executed if all servers involved have MS Distributed Transaction Coordinator (MS DTC) installed and running. Distributed transactions are also very difficult to debug, so use them sparingly.

Hope this helps.

Happy Coding.

Regards,
Jaliya

jQuery - Open another page inside the modal window

Sometimes you might face a situation where you want to open another page inside your modal window. This is how you can achieve it.

Add this part to your master page.

    <script src="Scripts/jquery-1.7.2.js" type="text/javascript"></script>
    <script src="Scripts/jquery-ui-1.8.20.js" type="text/javascript"></script>

    <script type="text/javascript">
        $(function () {
            $("#dialog-page").dialog("destroy");
        });

        function showDialog(uniqueName) {
            $("#dialog-page").load('../NewPage.aspx').dialog({
                autoOpen: false,
                resizable: true,
                height: 400,
                width: 600,
                modal: true
            });

            $('#dialog-page').dialog('open');
            return false;
        }
    </script>

In your page which will have the the control to open the modal, add this div.

    <div id='dialog-page' title="Modal">
        
    </div>

I am using Link Button to open the modal. This is how you can trigger the javascript function.

<asp:LinkButton ID = "lbtnModal" runat="server" Text="Open" OnClientClick="javascript:return showDialog(this.name);" ></asp:LinkButton>

New page inside the modal

Hope this helps.

Happy Coding.

Regards,
Jaliya

jQuery UI Tabs - Maintain same tab after postback

I think this is a common issue if you are using jQuery UI Tabs. The issue is when you click a submit button inside a any tab other than the first tab, you will be landed on the first tab after postback. It can really be a trouble if you want to stay on the same tab after postback.
Here is a simple workaround to solve that problem.

• Add a hidden field inside the tabs div.

    <div id="tabs">
        <asp:HiddenField ID="tab_index" Value="0" runat="server" />
        <ul>
            <li><a href="#tabs-1">Tab 1</a></li>
            <li><a href="#tabs-2">Tab 2</a></li>
            <li><a href="#tabs-3">Tab 3</a></li>
        </ul>

        <div id="tabs-1">
            <asp:Button ID="Button1" runat="server" Text="Submit" OnClick="Button1_Click"/>
        </div>

        <div id="tabs-2">
            <asp:Button ID="Button2" runat="server" Text="Submit" OnClick="Button2_Click"/>
        </div>

        <div id="tabs-3">
            <asp:Button ID="Button3" runat="server" Text="Submit" OnClick="Button3_Click"/>
        </div>
    </div>

• Add this script to your master page.

    <script type="text/javascript">
        $(document).ready(function () {
            var iSelectedTab = $(this).find("input[id*='tab_index']").val();
            if (iSelectedTab == null)
                iSelectedTab = 0;
            $('[id$=tabs]').tabs({ selected: iSelectedTab });
        });
    </script>

• In your code behind file, set the current tab in the postback event.

protected void Button1_Click(object sender, EventArgs e)
{
    tab_index.Value = "0";
}

protected void Button2_Click(object sender, EventArgs e)
{
    tab_index.Value = "1";
}

protected void Button3_Click(object sender, EventArgs e)
{
    tab_index.Value = "2";
}

Hope this helps.

Happy Coding.

Regards,
Jaliya

Microsoft SharePoint Server 2013 Preview is Released

This week Microsoft has unveiled SharePoint Server 2013 Preview along with 2013 Preview versions of Exchange, Lync, Office Web Apps Server, Project, Visio and Office Professional Plus. All these previews brings a Metro-like feel, as well as revamped touch tools intended to make it easier to use the desktop applications on a tablet.

According to Microsoft SharePoint Team Blog, here is a list of new features which are available in this new release.

1. New SharePoint Experience - Metro Style User Experience
2. Pervasive Social Networking - Yammer and SharePoint
3. SkyDrive Pro - Sync and use documents offline in the Windows Explorer
4. SharePoint Sites - View sites in one place
5. Projects for Everyone - Task management
6. FAST Search and Recommendations - Search with FAST
7. Cloud App Model and Marketplace - build, buy, deploy and manage applications via the new Cloud App Model for the Office 2013
8. Dynamic Publishing - Multi-device, Multi-lingual intranet and internet sites
9. In-Place Governance and Compliance - New unified policy management

I have installed SharePoint 2013 and the user experience is some what different when comparing to SharePoint 2010, but it is great. It’s like Windows 8 user experience. I think it will take some time to get familiar with this new release and I am pretty sure it is worth it.

For more information on all the new 2013 product preview releases, visit Try more products page.

Happy Coding.

Regards,
Jaliya

Writing blog posts with Windows Live Writer

For all this time, I was using Blogger post editor to write my posts. It is a nice editor, but it has some difficulties when writing source codes. Today one of my good friends, Melick told me about this Windows Live Writer which comes with Windows Live Essentials.

I have Windows Live Essentials in my laptop for sometime and the funny thing is I did not know about this nice tool until today. Just now I started using it and I must say, it is the nicest blog editor I have ever seen. The capabilities of Windows Live Writer is really amazing and it has the ability to connect to almost any kind of blog and to do all the things related to a posts without touching the browser. And there are a lot of nice free plug-ins available to make your post writing time more enjoyable.

For those who haven’t started using Windows Live Writer, why not give it a try. I am sure you will like it. And Thank you so much Melick for showing me this nice tool.

Happy Coding.

Regards,
Jaliya

Connecting to an Instance of SQL Server by Using SQL Server Authentication in C#

I am pretty sure that you can find many articles about how to connect to a specific database on a SQL Server instance in C#. But you won't be able to find many about how to connect to a specific SQL Server instance in C#. So thought to write a post about how to connect to a SQL Server Instance in C#.

Before starting off with the topic, I think it's better to refresh the knowledge in SQL Server instances.

Mainly there are two types of SQL Server instances. First is the Default Instance which is identified solely by the name of the computer on which the instance is running, it does not have a separate instance name. The second is the Named Instance. All instances of the database engine other than the default instance are identified by an instance name specified during installation of the instance. We can only have one default instance, but we can have many named instances and each SQL Server instance has its own copy of the server files, databases and security credentials.

Now let's move into the topic. We can connect to a SQL Server instance using SQL Server Management Objects (SMO). SMO is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.

So this is how you can connect to the Default, Named and Remote Server Instances using SQL Server Authentication. First you need to add references to following dll's which can be found on "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies" folder.

1. Microsoft.SqlServer.Smo.dll
2. Microsoft.SqlServer.ConnectionInfo.dll
3. Microsoft.SqlServer.Management.Sdk.Sfc.dll

using System;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace ConnectToSQLInstance
{
    class Program
    {
        static void Main(string[] args)
        {
            string username = "sa";
            string password = "sa";
            string instanceName = "instance_name";
            string remoteSrvName = "remote_server_name";

            /*----------------connect to default instance----------------*/
            Server oServer = new Server();
            // set to true for Windows Authentication
            oServer.ConnectionContext.LoginSecure = false;
            oServer.ConnectionContext.Login = username;
            oServer.ConnectionContext.Password = password;
            // connection is established
            Console.WriteLine(oServer.Information.Version);

            /*----------------connect to named instance----------------*/
            ServerConnection oServerConnection = new ServerConnection();
            // connects to named instance
            oServerConnection.ServerInstance = @".\" + instanceName;
            oServerConnection.LoginSecure = false;
            oServerConnection.Login = username;
            oServerConnection.Password = password;
            Server oServerNamed = new Server(oServerConnection);
            // connection is established
            Console.WriteLine(oServerNamed.Information.Version);

            /*----------------connect to default instance----------------*/
            // In here remote server name / ServerInstance needs to be specified
            ServerConnection oRemoteServerConnection = new ServerConnection(remoteSrvName);
            oRemoteServerConnection.LoginSecure = false;
            oRemoteServerConnection.Login = username;
            oRemoteServerConnection.Password = password;
            Server oServerRemote = new Server(oRemoteServerConnection);
            // connection is established
            Console.WriteLine(oServerRemote.Information.Version);
        }
    }
}

Hope this helps.

Happy Coding.

Regards,
Jaliya

MS SQL Server table browser and a Stored Procedure Generator

Isn't it nice to have some simple tool which will allow you to easily browse through your connected MS SQL Servers' all databases and tables? And then view the table information in design mode such as column names, column types, column sizes and primary keys etc? And finally to create "INSERT", "UPDATE", "DELETE" and "SELECT" stored procedures for a selected table in a single button click?

Since I am little bit lazy in doing things related to MS SQL, I wanted an application to make my life easier when writing Stored Procedures and all. So I have wrote this simple tool, in which it's main task is to generate Stored Procedures which ultimately ended up as a "MS SQL Server table browser and a Stored Procedure Generator".

This tool has the ability to connect to default, named or remote instances of SQL Server database engine. (Please note that, only the SQL Server Authentication is supported in this version.)  Then it can be used to browse databases and tables of connected SQL Server instance and then to view tables design in an easier manner. And finally it has the capability to generate  "INSERT", "UPDATE", "DELETE" and "SELECT" stored procedures for a selected table.

Connect To Server

Database and Table Browser

Table Design Viewer

Stored Procedure Generator

I have uploaded the installer to my skydrive, so anyone can help themselves with this tool. I have ran couple of tests with generated stored procedures and the results were very much satisfying. But if you come across with any kind of bug, please let me know as I am planning to enhance this tool further. It is really appreciated.

     Download

Happy Coding.

Regards,
Jaliya

How to open a new browser window from the code behind file - ASP.NET & C#

Sometimes we face this situation where we want to fill some values into some variables and we want to send those values to a web page where it will open in a new window. If you want to do all this in a single button click, there is something you should think about.

In javascript, there is window.open() method, which you  can use to open the new window in any way you prefer. But since that would be a client side event, it will run before the server side events. Which means if you write all the session variable assigning part in your server side click event which is,

OnClick

it will fire after executing client side click event which is,

OnClientClick

So as a solution to that, this is the code snippet to open a new window from the code behind file under the server side click event.

ClientScript.RegisterStartupScript(this.GetType(), "PopupWindow", "<script language='javascript'>window.open('YourPage.aspx','Title','width=600,height=300')</script>");

Happy Coding.

Regards,
Jaliya