Grand Total and SubTotal using Gouping
In this example, we calculated the the customer wise total, Project wise total and Grant total.

First we create a below table, it is having the 4 colums. Here NoOfDocuments means, each user(DoaminID) worked document count as per customer project.

CREATE TABLE #tmpTestGrouping (
Customer VARCHAR(100),
Project VARCHAR(100),
DomainID VARCHAR(100),
[NoOfDocuments] INT
)

INSERT INTO #tmpTestGrouping
SELECT 'UHC', 'ACE - Adjudication','nsuresh',9 UNION ALL
SELECT 'UHC', 'ACE - Adjudication','rkbabu',8 UNION ALL
SELECT 'UHC', 'ACE - Adjudication','vijayans',5.5 UNION ALL
SELECT 'UHC', 'ACE - Adjudication','venkatramanp',7.5 UNION ALL
SELECT 'UHC', 'HN West','veeramainp',9.5 UNION ALL
SELECT 'UHC', 'HN West','jeyavania',2.5 UNION ALL
SELECT 'UMR', 'HN West','gpsuba',1.5 UNION ALL
SELECT 'UHC', 'UBH-PDM','kalai',30 UNION ALL
SELECT 'UHC', 'UBH-PDM','rajkumar',20 UNION ALL
SELECT 'UHC', 'UBH-PDM','deepu',11 UNION ALL
SELECT 'UHC', 'UBH-PDM','prakeerthvijayan',6.5

Select the Table now,

SELECT * FROM #tmpTestGrouping


Now we gets the all results of Grouping with the DocumentsTotal.

SELECT
         GROUPING(Customer) GroupingCustomer,
    GROUPING(Project) GroupingProject,
    GROUPING(DomainID) GroupingDoaminID,
         Customer,
         [Project],
         DomainID,
         SUM ([NoOfDocuments]) AS [NoOfDocuments]
FROM   #tmpTestGrouping
GROUP BY Customer,[Project],DomainID WITH cube




In the above fig, GroupingCustomer, GroupingProject, GroupingDoaminID values only having 0 or 1 that is a boolean. If its 1 means that columns grouped as per the aggregate function which is given by us.


No we need to get the Total, based on the below condition we will get the result as per the columns or clustered columns or the Grand total.
        
SELECT  
CASE WHEN GROUPING(Customer) = 1 AND GROUPING([Project]) = 1 AND
GROUPING(DomainID) = 1 THEN 'Grand Total'
WHEN GROUPING(Customer) = 0  AND GROUPING([Project]) = 0 AND
GROUPING(DomainID) = 1 THEN ''
WHEN GROUPING([Project]) = 1 AND GROUPING(DomainID) = 1 THEN ''
           ELSE Customer END AS Customer,
   
CASE WHEN GROUPING(Customer) = 0 AND GROUPING([Project]) = 1 AND      
     GROUPING(DomainID) = 1 THEN 'Customer Total'
     WHEN GROUPING(DomainID) = 1 AND GROUPING(Customer) = 0 THEN ''
     WHEN GROUPING(Customer) = 1 THEN '' ELSE
CAST([Project] AS VARCHAR) END AS [Project],

CASE WHEN GROUPING(Customer) = 1 AND GROUPING([Project]) = 1 AND
GROUPING(DomainID) = 1 THEN ''
     WHEN GROUPING(Customer) = 0 AND GROUPING([Project]) = 0 AND
GROUPING(DomainID) = 1 THEN 'Project Total'
     WHEN GROUPING(Customer) = 0 AND GROUPING([Project]) = 1 AND
GROUPING(DomainID) = 1 THEN ''
    ELSE DomainID END AS DomainID,SUM ([NoOfDocuments]) AS [NoOfDocuments]
FROM
#tmpTestGrouping
GROUP BY Customer,[Project],DomainID WITH ROLLUP     

The output is like below,




This helps to calculate the totals instead of calculate from our application.
01:35 | Category: | 0 comments

Multilingual in asp.net
Multilingual is a concept of changing the language dynmaically based on country. Here I have explained simple example of using multilingual in our application.

First we need to add the App_GlobalResources  folder and then add resource files in our application like below,





Here, the Test.resx file is need for default, then only it works fine.

Then we will assing the key and values as per our requirement.The keys are the pre defined messages, error messages, lables and etc.,

In default.aspx, contains the dropdown with two different inputs. The dropdown selection, we will change the language of the page.

<div>
Language :
<asp:DropDownList ID="ddlLanguage" runat="server"
OnSelectedIndexChanged="ddlLanguage_SelectedIndexChanged" AutoPostBack="True">
<asp:ListItem Text="English" Value="en-US" Selected="True"></asp:ListItem>
<asp:ListItem Text="French" Value="fr-FR"></asp:ListItem>
</asp:DropDownList>
&nbsp;--&gt;
<asp:Label ID="lblLocality" runat="server" SkinID="002lblNormal" Text="Locality :"></asp:Label>
&nbsp;<input id="txtLocality" runat="server" class="ctlTextBox" maxlength="50" type="text" />&nbsp;&nbsp;&nbsp;
<asp:Label ID="lblPhone" runat="server" SkinID="002lblNormal" Text="Phone :"></asp:Label>
<input id="txtPhone1" runat="server" class="ctlTextBox" maxlength="15" onkeypress="return ValidateTextBox('txtPhone1');"
type="text" />
</div>


Then we need to allocate the language by using CultureInfo. See the below example,

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo(ddlLanguage.SelectedValue);
Thread.CurrentThread.CurrentUICulture = Thread.CurrentThread.CurrentCulture;
LoadLanguageSelection();
}
}
protected void ddlLanguage_SelectedIndexChanged(object sender, EventArgs e)
{
Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo(ddlLanguage.SelectedValue);
Thread.CurrentThread.CurrentUICulture = Thread.CurrentThread.CurrentCulture;
LoadLanguageSelection();

}

//Purpose : Load the Language
private void LoadLanguageSelection()
{
lblLocality.Text = Convert.ToString(GetGlobalResourceObject("Test", "Locality"));
lblPhone.Text = Convert.ToString(GetGlobalResourceObject("Test", "Phone"));
}



The LoadLanguageSelection() method is used to load the page. Within the GetGlobalResourceObject we have given the default Resource file name(this is the identification of resource file with culture name, see the solution explorer in the above figure) and key name.



Now we see the dropdown selection, the page will get change the language itself like below.


06:47 | Category: | 0 comments

How to assign XML variable value in SP using sql 2008

In this example, first we need to declare a XML variable for assigning, it is like a normal value assignment using SET but we need to put a parenthesis of xml select clause. See below,

DECLARE @PFPRun XML
SET @PFPRun =
(           SELECT
            [vikingid], 
            [System], 
            [totalstrokes], 
            [TotalDocuments], 
            [Quality]
FROM
            tempdb..#tmpOverAllPFP       
FOR
XML PATH('PFPEmployees'),TYPE
)


After assingment, we have sent a bulk table as single parameter from one SP to another SP. See the below,

EXEC USP_PFPReportViewJob  @LOC,@DATE, @CreatedBy,@PFPRun

We also possible to use a Typed parameter or Table Variable instead of using xml
05:45 | Category: | 0 comments

Get Column name using SQL Query
SELECT
column_name 'Column Name',
data_type 'Data Type',
character_maximum_length 'Maximum Length'
FROM
information_schema.columns
WHERE
table_name = 'Your Table Name'
04:58 | Category: | 0 comments

Oops - Can't reference to child Class object refer to the parent Class object
In this case, Most of the people says, “Parent's can hold to children but children cannot”

Let’s see the below example,


class Parent
    {
        public void display1()
        {
            Console.WriteLine("Parent");
        }
    }

class Child : Parent//Class Child extends Parent(in case of Java Lang.)
    {
        public void display()
        {
            Console.WriteLine("Child");
        }

    }

    class Program
    {
        static void Main(string[] args)
        {
            Child aChild = new Child();
            Parent aParent = new Parent();
            aParent = aChild;// its valid.
            aChild = aParent;// its not valid. Why?
        }
    }

Reason:

-          Inheritance is "type of" not "contains". So the Child is Type Of Parent, But the Parent is not a type of Child. In this way the above example working.

-          Technically, Object is a logical reference (heap memory) of physical things (Signatures/Codes – stored into Stack memory). In Child class having the heap reference of Parent class so that we can assign. But the Parent class object does not have the Child reference so we can’t.

-          Reason of this situation: This is basically used, the child already created one object. But we know the use is exactly Parent class. So just we assign the reference, no need to create a reference and assign

-          Example:

int I ;
int J ;

I =  2 * 3 / 6;
J = I ;

 Here no need to calculate the value again. Just assign that value. The same way, we no need to create a object again, just point that created object(child class object).
07:44 | Category: | 0 comments

Bulk Insert Using Sql Type – User Defined Table Type
Another one of the Bulk sending Method is SQL Type Method. In this Method we will create the User-Defined Table Type in SQL Server 2008 and send it as a single parameter.

The following example will describe,

BulkCopyUsingType.cs



protected void Page_Load(object sender, EventArgs e)
{

DataTable dtDataTable = new DataTable("UserDetailes");
DataRow drDataRow = null;

dtDataTable.Columns.Add("System", typeof(string));
dtDataTable.Columns.Add("NoOfEmployess", typeof(int));
drDataRow = dtDataTable.NewRow();
drDataRow["System"] = "FireFly";
drDataRow["NoOfEmployess"] = "1000";
dtDataTable.Rows.Add(drDataRow);

drDataRow = dtDataTable.NewRow();
drDataRow["System"] = "Viking";
drDataRow["NoOfEmployess"] = "2421";
dtDataTable.Rows.Add(drDataRow);

using (SqlConnection objCon = new SqlConnection("pwd=pfp123;UID=pfp;database=usrdb;server=10.20.36.61"))
{
objCon.Open();
using (SqlCommand objCmd = new SqlCommand())
{

objCmd.CommandText = "usp_TypedParamInsert";
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Connection = objCon;
SqlParameter param1 = new SqlParameter("@LocationID", SqlDbType.Int);
param1.Value = 1;
objCmd.Parameters.Add(param1);

SqlParameter param = new SqlParameter("@UsersCollection", SqlDbType.Structured);
param.Value = dtDataTable; //Directly send the Bulk data as a table.
objCmd.Parameters.Add(param);
objCmd.ExecuteNonQuery();

}
}

}



In DB, we need to do the followings,

--Create Destination Table
CREATE TABLE [dbo].[tblUserDefinedTypedData](
      [LocationID] [tinyint] NOT NULL,
      [System] [varchar](20) NOT NULL,
      [NoOfEmployess] [int] NOT NULL
) ON [PRIMARY]


-- Create the Type of User-Defined Table structure
CREATE TYPE [dbo].tblPFPSystem AS TABLE
(
      [System] [varchar](20) NOT NULL,
      [NoOfEmployess] [int] NOT NULL
)
GO

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Now we create the SP for insert operation
CREATE PROCEDURE [dbo].[usp_TypedParamInsert]
(
      @LocationID INT,
      @UsersCollection AS [dbo].[tblPFPSystem] READONLY
)
AS
BEGIN

SET NOCOUNT ON;
--This row performs bulk copy of object DataTable to Table in SQL

INSERT INTO [tblUserDefinedTypedData] SELECT @LocationID,* FROM @UsersCollection

END


Now, we will run the application, the DataTable Values are getting inserted into DB. In this example I have send a DataTable as hard coded one, instead of this you may use your data tables according to your needs. Now the Results shows like below,






03:36 | Category: | 1 comments

Query with Remote server / OpenQuery
There may be times when you'll want to run an ad hoc query that retrieves data from a remote data source or bulk loads data into a SQL Server table. In such cases, you can use the OpenQuery,OpenRowSet or OpenDataSource function in Transact-SQL

This queries are connects to the destination server and runs the query on that server and returns the resultset. Whereas, in case of linked server query is executed on the local server 


openquery is much faster. Only one problem is the query string is limited to 8k only

See the below examples…

Format:

SELECT * FROM OPENQUERY(remotelinkedservername,Query)

Example:

SELECT * FROM OPENQUERY(GUNBATS,'select * from [BRETRACKER].[dbo].[ViQuality]' )

Format:

SELECT * FROM OPENDATASOURCE ( provider_name,
Connectionstring ).dbname.schemaname.TableName

Example:

SELECT * FROM OPENDATASOURCE('SQLOLEDB',
'Data Source=10.20.36.61;User ID=pfp;Password=xxxx').usrdb.dbo.T_pfpamount

Format:

SELECT * FROM OPENROWSET ( 'provider_name', { 'datasource' ; 'user_id' ; 'password' |
'Connectionstring' } , {[catalog. ] [ schema. ] object | 'query' } )

Example:

SELECT A.* FROM OPENROWSET ('SQLOLEDB','10.20.36.61' ; 'PFP' ;
'xxxx','select * from [usrdb].[dbo].[T_pfpamount]' 
) as a

When you try to execute queries involving OPENROWSET on SQL Server you get the error in some
places because of Ad Hoc Distributed Queries is disabled.

Following are the steps to enable Ad Hoc Distributed Queries on SQL Server:

sp_configure 'show advanced options',1
reconfigure
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

Ad Hoc Distributed Queries are now enabled. If you run the query with OPENROWSET
now you do not get the error again.


Linked Server

The Linked Servers option allows you to connect to another instance of SQL Server running
on a different machine

When you execute a distributed query (query a remote database) against a linked server, you
must include a fully qualified, four-part table name for each data source to query

i.e    linked_server_name.catalog.schema.object_name.

Example

SELECT * FROM [GUNBATS].[BRETRACKER].[dbo].[ViQuality] wher Quality = ‘Y’
05:33 | Category: | 0 comments