Tuesday, 17 September 2013

AJAX AUTOCOMPLETED

Code for AJAX AUTOCOMPLETED

----------------.aspx--------------

<asp:TextBox ID="txt_Narration" runat="server" Width="200px" OnTextChanged="txt_Narration_TextChanged"
AutoPostBack="true">
</asp:TextBox>&nbsp;
<div id="div_NARRATION">
</div>
<cc1:AutoCompleteExtender runat="server" ID="AutoComplete_NARRATION" BehaviorID="autoCompleteNARRATION"
TargetControlID="txt_Narration" ServicePath="~/webservices/CommonWebService.asmx"
ServiceMethod="Get_Narration" MinimumPrefixLength="1" CompletionInterval="10"
EnableCaching="true" CompletionSetCount="12" CompletionListCssClass="AutoExtender"
CompletionListItemCssClass="AutoExtenderList" CompletionListHighlightedItemCssClass="AutoExtenderHighlight"
CompletionListElementID="div_NARRATION">
</cc1:AutoCompleteExtender>

.CSS

.AutoCompleteExtender_CompletionList
{
font-size: small;   
visibility:hidden;
padding : 1px;
Height:230px;
width:250px;
z-index:5000;
cursor:default;
margin:0px!important;
border:buttonshadow;
list-style-type:none;
overflow:auto;
text-align:left;
border-width:1px;
border-style:solid;
border-color:Maroon;
}

.AutoExtender
{

font-family: Verdana, Helvetica, sans-serif;
font-size: .8em;
font-weight: normal;
border: solid 1px #006699;
line-height: 20px;
padding: 10px;
background-color: White;
margin-left:0px;
z-index:5000;
}
.AutoExtenderList
{
border-bottom: dotted 1px #006699;
cursor: pointer;
color: Maroon;
z-index:5000;
}
.AutoExtenderHighlight
{
color: White;
background-color: #006699;
cursor: pointer;
z-index:5000;
}
#divwidth
{
z-index:500;
width: 200px !important;  
text-align:Left; 
}
#divwidth div
{
z-index:500;
text-align:Left;
width: 200px !important;  

}


.webservices

using System;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Configuration;
using System.Diagnostics;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Web.Script.Services;
using System.Xml;
using System.Collections.Generic;
/// <summary>
/// Summary description for CommonWebService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]

ConnectionStringSettings css = ConfigurationManager.ConnectionStrings["cnnGPTERP_IBM"];
SqlConnection sqlcnGPTERP;
SqlDataAdapter da;
DataTable dt;








[WebMethod]
public string[] Get_Narration(string prefixText)
{
//int count = 10;
sqlcnGPTERP = new SqlConnection(css.ConnectionString);
string sql = "select  narr_code, narrat_name from narration  where  (narrat_name like @prefixText or narr_code like @prefixText) order by  narrat_name";
SqlDataAdapter da = new SqlDataAdapter(sql, sqlcnGPTERP);
da.SelectCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 50).Value = prefixText + "%";
DataTable dt = new DataTable();
da.Fill(dt);
string[] items = new string[dt.Rows.Count];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
items.SetValue(dr["narrat_name"].ToString() + " { " + dr["narr_code"].ToString() + " }", i);
i++;
}
return items;
    }

JAVASCRIPT AUTO REFRESH PAGE

Response.Expires = 0;
Response.Buffer = true;
Response.Clear();
Response.AddHeader("Refresh", "3");

JAVASCRIPT ALERT ON CLASS PAGE



----------------.aspx--------------
JAVASCRIPT FUNCTION
function alertClick(Details)
{
alert(Details)
return false;
}
.CS    
string alert = "Enter Atleast One Debit Type and One Credit Type Ledger";
RegisterClientScriptBlock("gridcount", "<script Language='javascript'>window.onload = function(){return alertClick('" + alert + "');}</script>");
return;

 ---------------- OR

protected void Page_Load(object sender, EventArgs e)

{

  string message = "Hello! Mudassar.";

  System.Text.StringBuilder sb = new System.Text.StringBuilder();

  sb.Append("<script type = 'text/javascript'>");

  sb.Append("window.onload=function(){");

  sb.Append("alert('");

  sb.Append(message);

  sb.Append("')};");

  sb.Append("</script>");

  ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", sb.ToString());

}
 

Code for AJAX TEXTCHANGE WEBSERVICE CALL



----------------.aspx--------------

   <script language="javascript" type="text/javascript">
       
    function CallParametersPageMethod()
    {
       Branch_Abrv = document.getElementById('txt_search').value;
       CommonWebService.Mai_Login_Branch(Branch_Abrv,onSucceeded,onFailed);
// CommonWebService ? WEBSERVICE NAME
// Mai_Login_Branch ? METHOD NAME FROM WEBSERVICE
    }

    function onSucceeded(result,userContext,methodName)
    {
        if(result!="")
        {
          document.getElementById('txt_search').value= result;
        }   
    }
    function onFailed(error,userContext,methodName)
    {
      alert("An error occurred");
    }

    </script>


.ASPX

<asp:ScriptManager ID="ScriptManager1" runat="server">
<Services>
<asp:ServiceReference InlineScript="true" Path="~/webservices/CommonWebService.asmx" />
</Services>
</asp:ScriptManager>


<asp:TextBox ID="txt_search" runat="server" Style="z-index: 112; left: 145px; position: absolute;
top: 135px;" TabIndex="4" ToolTip="Type Branch Name" AutoCompleteType="Disabled"
Width="143px"></asp:TextBox>


.CS CODE

txt_search.Attributes.Add("onblur", "javascript:return CallParametersPageMethod()");

.WEBSERVICES

[WebMethod]
public string Mai_Login_Branch(string prefixText)
{
if (prefixText.Contains("{") || prefixText.Contains("}"))
{
string[] branch_name = prefixText.Split('{');
prefixText = Convert.ToString(branch_name[1]).Trim();
prefixText = prefixText.Replace("{", "");
prefixText = prefixText.Replace("}", "");
}
sqlcnGPTERP = new SqlConnection(css.ConnectionString);
string sql = "select branch,branch_abrv,pk_branch_key from Branch Where branch_abrv ='" + prefixText.Trim() + "'";
SqlDataAdapter da = new SqlDataAdapter(sql, sqlcnGPTERP);
DataTable dt = new DataTable();
da.Fill(dt);
string[] items = new string[dt.Rows.Count];
int i = 0;
string Primary_Key = "";
foreach (DataRow dr in dt.Rows)
{
Primary_Key = dr["branch_abrv"].ToString() + "{" + dr["Branch"].ToString() + "}";
i++;
}
return Primary_Key;
}

AJAX TAB CONTROL




<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>

<cc1:tabcontainer id="TabContainer1" runat="server" activetabindex="0" style="z-index: 18;
left: 0px; top: 0px" width="100%" cssclass="ajax__myTab" backcolor="#FFFBD6">
<%--ledger details--%>
<cc1:TabPanel ID="Tab_Ledger_Details" runat="server" HeaderText="Voucher Details"
Width="100%" Style="text-align: left" BackColor="#FFFBD6">
<ContentTemplate>
</ContentTemplate>
</cc1:TabPanel>
<%--vourcher details complet--%>
<%--Bill and cost tab start--%>
<cc1:TabPanel ID="Tab_Bill_Cost" runat="server" HeaderText="Bill and Cost Details"
Width="100%" BackColor="#FFFBD6">
<ContentTemplate>

</ContentTemplate>
</cc1:TabPanel>
<%--Bill and cost tab complete--%>
<%--document ref tab start--%>
<cc1:TabPanel ID="Tab_Doc_Ref" runat="server" HeaderText="Document Reference Details"
Width="100%" BackColor="#FFFBD6">
<ContentTemplate>

</ContentTemplate>
</cc1:TabPanel>
<%--document ref tab complete--%>
</cc1:tabcontainer>

JAVASCRIPT CLEARE HISTORY

<script type="text/javascript" language="javascript">
javascript:window.history.forward(1);
</script>


Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.Cache.SetAllowResponseInBrowserHistory(False)

SQL CONCATE MULTIPAL ROW

DECLARE @CITY NVARCHAR(MAX)
SELECT @CITY = COALESCE(@CITY+',','')+CITY_NAME FROM MST_CITY
SELECT @CITY


-- RESULT

KOLHAPUR,SANGALI,MUMBAI,PUNE

Thursday, 1 August 2013

Function that Splits string in SQL Server / Table Valued Function

CREATE FUNCTION [dbo].[SplitString]
(
@SplitStr nvarchar(1000),
@SplitChar nvarchar(5)
)
RETURNS @RtnValue table
(
Data nvarchar(50)
)
AS
BEGIN
Declare @Count int
Set @Count = 1
While (Charindex(@SplitChar,@SplitStr)>0)
Begin
Insert Into @RtnValue (Data)
Select
Data = ltrim(rtrim(Substring(@SplitStr,1,Charindex(@SplitChar,@SplitStr)-1)))
Set @SplitStr = Substring(@SplitStr,Charindex(@SplitChar,@SplitStr)+1,len(@SplitStr))
Set @Count = @Count + 1
End
Insert Into @RtnValue (Data)
Select Data = ltrim(rtrim(@SplitStr))
Return
END
To Execute above Function use the statement as follows
select * from dbo.SplitString(',abc,defg,hij,klm,nopq,Test,123,', ',')

Friday, 21 June 2013

sql inda all country in database

CREATE TABLE country (
id int NOT NULL ,
country_code varchar(5) NOT NULL default '',
country_name varchar(100) NOT NULL default '',

)
--
INSERT INTO country VALUES (1, 'US', 'United States');
INSERT INTO country VALUES (2, 'CA', 'Canada');
INSERT INTO country VALUES (3, 'AF', 'Afghanistan');
INSERT INTO country VALUES (4, 'AL', 'Albania');
INSERT INTO country VALUES (5, 'DZ', 'Algeria');
INSERT INTO country VALUES (6, 'DS', 'American Samoa');
INSERT INTO country VALUES (7, 'AD', 'Andorra');
INSERT INTO country VALUES (8, 'AO', 'Angola');
INSERT INTO country VALUES (9, 'AI', 'Anguilla');
INSERT INTO country VALUES (10, 'AQ', 'Antarctica');
INSERT INTO country VALUES (11, 'AG', 'Antigua and/or Barbuda');
INSERT INTO country VALUES (12, 'AR', 'Argentina');
INSERT INTO country VALUES (13, 'AM', 'Armenia');
INSERT INTO country VALUES (14, 'AW', 'Aruba');
INSERT INTO country VALUES (15, 'AU', 'Australia');
INSERT INTO country VALUES (16, 'AT', 'Austria');
INSERT INTO country VALUES (17, 'AZ', 'Azerbaijan');
INSERT INTO country VALUES (18, 'BS', 'Bahamas');
INSERT INTO country VALUES (19, 'BH', 'Bahrain');
INSERT INTO country VALUES (20, 'BD', 'Bangladesh');
INSERT INTO country VALUES (21, 'BB', 'Barbados');
INSERT INTO country VALUES (22, 'BY', 'Belarus');
INSERT INTO country VALUES (23, 'BE', 'Belgium');
INSERT INTO country VALUES (24, 'BZ', 'Belize');
INSERT INTO country VALUES (25, 'BJ', 'Benin');
INSERT INTO country VALUES (26, 'BM', 'Bermuda');
INSERT INTO country VALUES (27, 'BT', 'Bhutan');
INSERT INTO country VALUES (28, 'BO', 'Bolivia');
INSERT INTO country VALUES (29, 'BA', 'Bosnia and Herzegovina');
INSERT INTO country VALUES (30, 'BW', 'Botswana');
INSERT INTO country VALUES (31, 'BV', 'Bouvet Island');
INSERT INTO country VALUES (32, 'BR', 'Brazil');
INSERT INTO country VALUES (33, 'IO', 'British lndian Ocean Territory');
INSERT INTO country VALUES (34, 'BN', 'Brunei Darussalam');
INSERT INTO country VALUES (35, 'BG', 'Bulgaria');
INSERT INTO country VALUES (36, 'BF', 'Burkina Faso');
INSERT INTO country VALUES (37, 'BI', 'Burundi');
INSERT INTO country VALUES (38, 'KH', 'Cambodia');
INSERT INTO country VALUES (39, 'CM', 'Cameroon');
INSERT INTO country VALUES (40, 'CV', 'Cape Verde');
INSERT INTO country VALUES (41, 'KY', 'Cayman Islands');
INSERT INTO country VALUES (42, 'CF', 'Central African Republic');
INSERT INTO country VALUES (43, 'TD', 'Chad');
INSERT INTO country VALUES (44, 'CL', 'Chile');
INSERT INTO country VALUES (45, 'CN', 'China');
INSERT INTO country VALUES (46, 'CX', 'Christmas Island');
INSERT INTO country VALUES (47, 'CC', 'Cocos (Keeling) Islands');
INSERT INTO country VALUES (48, 'CO', 'Colombia');
INSERT INTO country VALUES (49, 'KM', 'Comoros');
INSERT INTO country VALUES (50, 'CG', 'Congo');
INSERT INTO country VALUES (51, 'CK', 'Cook Islands');
INSERT INTO country VALUES (52, 'CR', 'Costa Rica');
INSERT INTO country VALUES (53, 'HR', 'Croatia (Hrvatska)');
INSERT INTO country VALUES (54, 'CU', 'Cuba');
INSERT INTO country VALUES (55, 'CY', 'Cyprus');
INSERT INTO country VALUES (56, 'CZ', 'Czech Republic');
INSERT INTO country VALUES (57, 'DK', 'Denmark');
INSERT INTO country VALUES (58, 'DJ', 'Djibouti');
INSERT INTO country VALUES (59, 'DM', 'Dominica');
INSERT INTO country VALUES (60, 'DO', 'Dominican Republic');
INSERT INTO country VALUES (61, 'TP', 'East Timor');
INSERT INTO country VALUES (62, 'EC', 'Ecudaor');
INSERT INTO country VALUES (63, 'EG', 'Egypt');
INSERT INTO country VALUES (64, 'SV', 'El Salvador');
INSERT INTO country VALUES (65, 'GQ', 'Equatorial Guinea');
INSERT INTO country VALUES (66, 'ER', 'Eritrea');
INSERT INTO country VALUES (67, 'EE', 'Estonia');
INSERT INTO country VALUES (68, 'ET', 'Ethiopia');
INSERT INTO country VALUES (69, 'FK', 'Falkland Islands (Malvinas)');
INSERT INTO country VALUES (70, 'FO', 'Faroe Islands');
INSERT INTO country VALUES (71, 'FJ', 'Fiji');
INSERT INTO country VALUES (72, 'FI', 'Finland');
INSERT INTO country VALUES (73, 'FR', 'France');
INSERT INTO country VALUES (74, 'FX', 'France, Metropolitan');
INSERT INTO country VALUES (75, 'GF', 'French Guiana');
INSERT INTO country VALUES (76, 'PF', 'French Polynesia');
INSERT INTO country VALUES (77, 'TF', 'French Southern Territories');
INSERT INTO country VALUES (78, 'GA', 'Gabon');
INSERT INTO country VALUES (79, 'GM', 'Gambia');
INSERT INTO country VALUES (80, 'GE', 'Georgia');
INSERT INTO country VALUES (81, 'DE', 'Germany');
INSERT INTO country VALUES (82, 'GH', 'Ghana');
INSERT INTO country VALUES (83, 'GI', 'Gibraltar');
INSERT INTO country VALUES (84, 'GR', 'Greece');
INSERT INTO country VALUES (85, 'GL', 'Greenland');
INSERT INTO country VALUES (86, 'GD', 'Grenada');
INSERT INTO country VALUES (87, 'GP', 'Guadeloupe');
INSERT INTO country VALUES (88, 'GU', 'Guam');
INSERT INTO country VALUES (89, 'GT', 'Guatemala');
INSERT INTO country VALUES (90, 'GN', 'Guinea');
INSERT INTO country VALUES (91, 'GW', 'Guinea-Bissau');
INSERT INTO country VALUES (92, 'GY', 'Guyana');
INSERT INTO country VALUES (93, 'HT', 'Haiti');
INSERT INTO country VALUES (94, 'HM', 'Heard and Mc Donald Islands');
INSERT INTO country VALUES (95, 'HN', 'Honduras');
INSERT INTO country VALUES (96, 'HK', 'Hong Kong');
INSERT INTO country VALUES (97, 'HU', 'Hungary');
INSERT INTO country VALUES (98, 'IS', 'Iceland');
INSERT INTO country VALUES (99, 'IN', 'India');
INSERT INTO country VALUES (100, 'ID', 'Indonesia');
INSERT INTO country VALUES (101, 'IR', 'Iran (Islamic Republic of)');
INSERT INTO country VALUES (102, 'IQ', 'Iraq');
INSERT INTO country VALUES (103, 'IE', 'Ireland');
INSERT INTO country VALUES (104, 'IL', 'Israel');
INSERT INTO country VALUES (105, 'IT', 'Italy');
INSERT INTO country VALUES (106, 'CI', 'Ivory Coast');
INSERT INTO country VALUES (107, 'JM', 'Jamaica');
INSERT INTO country VALUES (108, 'JP', 'Japan');
INSERT INTO country VALUES (109, 'JO', 'Jordan');
INSERT INTO country VALUES (110, 'KZ', 'Kazakhstan');
INSERT INTO country VALUES (111, 'KE', 'Kenya');
INSERT INTO country VALUES (112, 'KI', 'Kiribati');
INSERT INTO country VALUES (113, 'KP', 'Korea, Democratic People''s Republic of');
INSERT INTO country VALUES (114, 'KR', 'Korea, Republic of');
INSERT INTO country VALUES (115, 'KW', 'Kuwait');
INSERT INTO country VALUES (116, 'KG', 'Kyrgyzstan');
INSERT INTO country VALUES (117, 'LA', 'Lao People''s Democratic Republic');
INSERT INTO country VALUES (118, 'LV', 'Latvia');
INSERT INTO country VALUES (119, 'LB', 'Lebanon');
INSERT INTO country VALUES (120, 'LS', 'Lesotho');
INSERT INTO country VALUES (121, 'LR', 'Liberia');
INSERT INTO country VALUES (122, 'LY', 'Libyan Arab Jamahiriya');
INSERT INTO country VALUES (123, 'LI', 'Liechtenstein');
INSERT INTO country VALUES (124, 'LT', 'Lithuania');
INSERT INTO country VALUES (125, 'LU', 'Luxembourg');
INSERT INTO country VALUES (126, 'MO', 'Macau');
INSERT INTO country VALUES (127, 'MK', 'Macedonia');
INSERT INTO country VALUES (128, 'MG', 'Madagascar');
INSERT INTO country VALUES (129, 'MW', 'Malawi');
INSERT INTO country VALUES (130, 'MY', 'Malaysia');
INSERT INTO country VALUES (131, 'MV', 'Maldives');
INSERT INTO country VALUES (132, 'ML', 'Mali');
INSERT INTO country VALUES (133, 'MT', 'Malta');
INSERT INTO country VALUES (134, 'MH', 'Marshall Islands');
INSERT INTO country VALUES (135, 'MQ', 'Martinique');
INSERT INTO country VALUES (136, 'MR', 'Mauritania');
INSERT INTO country VALUES (137, 'MU', 'Mauritius');
INSERT INTO country VALUES (138, 'TY', 'Mayotte');
INSERT INTO country VALUES (139, 'MX', 'Mexico');
INSERT INTO country VALUES (140, 'FM', 'Micronesia, Federated States of');
INSERT INTO country VALUES (141, 'MD', 'Moldova, Republic of');
INSERT INTO country VALUES (142, 'MC', 'Monaco');
INSERT INTO country VALUES (143, 'MN', 'Mongolia');
INSERT INTO country VALUES (144, 'MS', 'Montserrat');
INSERT INTO country VALUES (145, 'MA', 'Morocco');
INSERT INTO country VALUES (146, 'MZ', 'Mozambique');
INSERT INTO country VALUES (147, 'MM', 'Myanmar');
INSERT INTO country VALUES (148, 'NA', 'Namibia');
INSERT INTO country VALUES (149, 'NR', 'Nauru');
INSERT INTO country VALUES (150, 'NP', 'Nepal');
INSERT INTO country VALUES (151, 'NL', 'Netherlands');
INSERT INTO country VALUES (152, 'AN', 'Netherlands Antilles');
INSERT INTO country VALUES (153, 'NC', 'New Caledonia');
INSERT INTO country VALUES (154, 'NZ', 'New Zealand');
INSERT INTO country VALUES (155, 'NI', 'Nicaragua');
INSERT INTO country VALUES (156, 'NE', 'Niger');
INSERT INTO country VALUES (157, 'NG', 'Nigeria');
INSERT INTO country VALUES (158, 'NU', 'Niue');
INSERT INTO country VALUES (159, 'NF', 'Norfork Island');
INSERT INTO country VALUES (160, 'MP', 'Northern Mariana Islands');
INSERT INTO country VALUES (161, 'NO', 'Norway');
INSERT INTO country VALUES (162, 'OM', 'Oman');
INSERT INTO country VALUES (163, 'PK', 'Pakistan');
INSERT INTO country VALUES (164, 'PW', 'Palau');
INSERT INTO country VALUES (165, 'PA', 'Panama');
INSERT INTO country VALUES (166, 'PG', 'Papua New Guinea');
INSERT INTO country VALUES (167, 'PY', 'Paraguay');
INSERT INTO country VALUES (168, 'PE', 'Peru');
INSERT INTO country VALUES (169, 'PH', 'Philippines');
INSERT INTO country VALUES (170, 'PN', 'Pitcairn');
INSERT INTO country VALUES (171, 'PL', 'Poland');
INSERT INTO country VALUES (172, 'PT', 'Portugal');
INSERT INTO country VALUES (173, 'PR', 'Puerto Rico');
INSERT INTO country VALUES (174, 'QA', 'Qatar');
INSERT INTO country VALUES (175, 'RE', 'Reunion');
INSERT INTO country VALUES (176, 'RO', 'Romania');
INSERT INTO country VALUES (177, 'RU', 'Russian Federation');
INSERT INTO country VALUES (178, 'RW', 'Rwanda');
INSERT INTO country VALUES (179, 'KN', 'Saint Kitts and Nevis');
INSERT INTO country VALUES (180, 'LC', 'Saint Lucia');
INSERT INTO country VALUES (181, 'VC', 'Saint Vincent and the Grenadines');
INSERT INTO country VALUES (182, 'WS', 'Samoa');
INSERT INTO country VALUES (183, 'SM', 'San Marino');
INSERT INTO country VALUES (184, 'ST', 'Sao Tome and Principe');
INSERT INTO country VALUES (185, 'SA', 'Saudi Arabia');
INSERT INTO country VALUES (186, 'SN', 'Senegal');
INSERT INTO country VALUES (187, 'SC', 'Seychelles');
INSERT INTO country VALUES (188, 'SL', 'Sierra Leone');
INSERT INTO country VALUES (189, 'SG', 'Singapore');
INSERT INTO country VALUES (190, 'SK', 'Slovakia');
INSERT INTO country VALUES (191, 'SI', 'Slovenia');
INSERT INTO country VALUES (192, 'SB', 'Solomon Islands');
INSERT INTO country VALUES (193, 'SO', 'Somalia');
INSERT INTO country VALUES (194, 'ZA', 'South Africa');
INSERT INTO country VALUES (195, 'GS', 'South Georgia South Sandwich Islands');
INSERT INTO country VALUES (196, 'ES', 'Spain');
INSERT INTO country VALUES (197, 'LK', 'Sri Lanka');
INSERT INTO country VALUES (198, 'SH', 'St. Helena');
INSERT INTO country VALUES (199, 'PM', 'St. Pierre and Miquelon');
INSERT INTO country VALUES (200, 'SD', 'Sudan');
INSERT INTO country VALUES (201, 'SR', 'Suriname');
INSERT INTO country VALUES (202, 'SJ', 'Svalbarn and Jan Mayen Islands');
INSERT INTO country VALUES (203, 'SZ', 'Swaziland');
INSERT INTO country VALUES (204, 'SE', 'Sweden');
INSERT INTO country VALUES (205, 'CH', 'Switzerland');
INSERT INTO country VALUES (206, 'SY', 'Syrian Arab Republic');
INSERT INTO country VALUES (207, 'TW', 'Taiwan');
INSERT INTO country VALUES (208, 'TJ', 'Tajikistan');
INSERT INTO country VALUES (209, 'TZ', 'Tanzania, United Republic of');
INSERT INTO country VALUES (210, 'TH', 'Thailand');
INSERT INTO country VALUES (211, 'TG', 'Togo');
INSERT INTO country VALUES (212, 'TK', 'Tokelau');
INSERT INTO country VALUES (213, 'TO', 'Tonga');
INSERT INTO country VALUES (214, 'TT', 'Trinidad and Tobago');
INSERT INTO country VALUES (215, 'TN', 'Tunisia');
INSERT INTO country VALUES (216, 'TR', 'Turkey');
INSERT INTO country VALUES (217, 'TM', 'Turkmenistan');
INSERT INTO country VALUES (218, 'TC', 'Turks and Caicos Islands');
INSERT INTO country VALUES (219, 'TV', 'Tuvalu');
INSERT INTO country VALUES (220, 'UG', 'Uganda');
INSERT INTO country VALUES (221, 'UA', 'Ukraine');
INSERT INTO country VALUES (222, 'AE', 'United Arab Emirates');
INSERT INTO country VALUES (223, 'GB', 'United Kingdom');
INSERT INTO country VALUES (224, 'UM', 'United States minor outlying islands');
INSERT INTO country VALUES (225, 'UY', 'Uruguay');
INSERT INTO country VALUES (226, 'UZ', 'Uzbekistan');
INSERT INTO country VALUES (227, 'VU', 'Vanuatu');
INSERT INTO country VALUES (228, 'VA', 'Vatican City State');
INSERT INTO country VALUES (229, 'VE', 'Venezuela');
INSERT INTO country VALUES (230, 'VN', 'Vietnam');
INSERT INTO country VALUES (231, 'VG', 'Virigan Islands (British)');
INSERT INTO country VALUES (232, 'VI', 'Virgin Islands (U.S.)');
INSERT INTO country VALUES (233, 'WF', 'Wallis and Futuna Islands');
INSERT INTO country VALUES (234, 'EH', 'Western Sahara');
INSERT INTO country VALUES (235, 'YE', 'Yemen');
INSERT INTO country VALUES (236, 'YU', 'Yugoslavia');
INSERT INTO country VALUES (237, 'ZR', 'Zaire');
INSERT INTO country VALUES (238, 'ZM', 'Zambia');
INSERT INTO country VALUES (239, 'ZW', 'Zimbabwe');
select * from country

Friday, 17 May 2013

SQL NEW INSERT STORE PROCEDURE DYNAMICALLY

IF EXISTS (
        SELECT *
        FROM dbo.sysobjects
        WHERE id = object_id(N'[dbo].[test_create_procedure]')
            AND OBJECTPROPERTY(id, N'IsProcedure') = 1
        )
    DROP PROCEDURE [dbo].[test_create_procedure]
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS OFF
GO

ALTER PROCEDURE create_procedure @table VARCHAR(200),
    @DeveloperName VARCHAR(200),
    @Createtable VARCHAR(20)
    --create_procedure 'Products','VISHAL','Products'
AS
SET NOCOUNT ON

DECLARE @testTable VARCHAR(8000)
DECLARE @testTable2 VARCHAR(8000)
DECLARE @testTable3 VARCHAR(8000)
DECLARE @opration VARCHAR(8000)
DECLARE @testTable_UPDATE VARCHAR(8000)
DECLARE @final VARCHAR(8000)
DECLARE @OP VARCHAR(100)

SET @testTable = ''
SET @testTable2 = ''
SET @final = ''
SET @testTable3 = ''
SET @testTable_UPDATE = ''
SET @opration = ''

DECLARE @Datetime VARCHAR(50)

SET @Datetime = getdate()

SELECT @testTable = @testTable + ',
            ' + column_name
FROM information_schema.columns
WHERE table_name = @table
    AND (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 0)

SELECT @testTable2 = @testTable2 + ',
@' + column_name + '  ' + data_type + '(' + cast(character_maximum_length AS VARCHAR(10)) + ')' + CASE is_nullable
        WHEN 'no'
            THEN ' '
        WHEN 'yes'
            THEN '=null'
        END
FROM information_schema.columns
WHERE table_name = @table
    AND character_maximum_length <> NULL
    AND (column_default IS NULL)
    AND data_type <> 'text'

SELECT @testTable2 = @testTable2 + ',
@' + column_name + '  ' + data_type
FROM information_schema.columns
WHERE table_name = @table
    AND (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 0)
    AND (
        character_maximum_length = NULL
        OR data_type = 'text'
        )

SELECT @testTable3 = @testTable3 + ',
            @' + column_name
FROM information_schema.columns
WHERE table_name = @table
    AND (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 0)

SELECT @testTable_UPDATE = @testTable_UPDATE + ',
            ' + column_name + ' =@' + column_name
FROM information_schema.columns
WHERE table_name = @table
    AND (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 0)

DECLARE @testTable_UPDATE_IDENTITY NVARCHAR(MAX)

SELECT @testTable_UPDATE_IDENTITY = '
            ' + column_name + ' =@' + column_name
FROM information_schema.columns
WHERE table_name = @table
    AND (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 1)

SET @testTable = SUBSTRING(@testTable, 2, len(@testTable))
SET @testTable2 = SUBSTRING(@testTable2, 4, len(@testTable2))
SET @testTable3 = SUBSTRING(@testTable3, 2, len(@testTable3))
SET @testTable_UPDATE = SUBSTRING(@testTable, 2, len(@testTable_UPDATE))
SET @opration = ' insert into [' + @table + ']
            (
            ' + @testTable + '
            )
        values
            (
            ' + @testTable3 + '
            )'

DECLARE @OPERATION_UPDATE NVARCHAR(MAX)

SET @OPERATION_UPDATE = 'UPDATE [' + @table + ']
            SET ' + @testTable_UPDATE + ' WHERE [' + @testTable_UPDATE_IDENTITY + ']'

--PRINT @OPERATION_UPDATE

SET @OP = 'InsertNew' + @table
SET @final = '/*
----------------------------------------------------------------------------------------
Store Procedure Name :  SP__' + @OP + '
----------------------------------------------------------------------------------------
1- Creation Date :' + convert(VARCHAR, getdate(), 103) + '
2- Last Update   :' + convert(VARCHAR, getdate(), 103) + '
3- Parametars No:6
4- Creation By :' + @DeveloperName + '
5- Last Update By :' + @DeveloperName + '
6- Return Value : Dataset

---------------------------------------------------------------------------------------
*/
Create  PROCEDURE  SP__' + @OP + '
(
  ' + @testTable2 + '
)
AS
 set nocount on

' + @opration + '

---------------------------------------------------------------------------------------


' + @OPERATION_UPDATE + '

Select * from   [' + @table + ']'

--exec (@final)
PRINT @final
GO

SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS ON
GO

--create_procedure 'Products','VISHAL','Products'

DYNAMIC SQL INSERT STATEMENT

create table #tmp (
SQLText varchar(8000) )

create table #tmp2 (
Id int identity,
SQLText varchar(8000) )

set nocount on

delete #tmp
delete #tmp2

declare @vsSQL varchar(8000),
@vsCols varchar(8000),
@vsTableName varchar(40)

declare csrTables cursor for
select name
from sysobjects
where type in ('u')
and name in ('Customers')
order by name


open csrTables
fetch next from csrTables into @vsTableName

while (@@fetch_status = 0)
begin

select @vsSQL = '',
@vsCols = ''
select @vsSQL = @vsSQL +
CASE when sc.type in (39,47,61,111) then
'''''''''+' + 'isnull(rtrim(replace('+ sc.name + ','''''''','''''''''''')),'''')' + '+'''''',''+'
when sc.type = 35 then
'''''''''+' + 'isnull(rtrim(replace(substring('+ sc.name + ',1,1000),'''''''','''''''''''')),'''')' + '+'''''',''+'
else
'isnull(convert(varchar,' + sc.name + '),''null'')+'',''+'
end
from syscolumns sc
where sc.id = object_id(@vsTableName)
order by ColID

select @vsCols = @vsCols + sc.name + ','
from syscolumns sc
where sc.id = object_id(@vsTableName)
order by ColID

select @vsSQL = substring(@vsSQL,1,datalength(@vsSQL)-1)

select @vsCols = substring(@vsCols,1,datalength(@vsCols)-1)

insert #tmp
exec ('select ' + @vsSQL + ' from ' + @vsTableName)

update #tmp
set sqltext = 'insert ' + @vsTableName + '(' + @vsCols + ') values(' + substring(sqltext,1,datalength(sqltext)-1) + ')'

insert #tmp2
select 'DELETE from ' + @vsTableName

insert #tmp2 values ('GO')

if (select count(id) from syscolumns where id = object_id(@vsTableName) and ((status & 128) = 128) ) = 1
begin
insert #tmp2
select 'set identity_insert ' + @vsTableName + ' on'
end

insert #tmp2
select * from #tmp

if (select count(id) from syscolumns where id = object_id(@vsTableName) and ((status & 128) = 128) ) = 1
begin
insert #tmp2
select 'set identity_insert ' + @vsTableName + ' off'
end

insert #tmp2 values ('GO')

insert #tmp2
select 'update statistics ' + @vsTableName

insert #tmp2 values ('GO')

delete #tmp

fetch next from csrTables into @vsTableName

end

close csrTables
deallocate csrTables

update #tmp2
set sqltext = substring(sqltext,1,charindex(',)',sqltext)-1) + ',NULL)'
where not(charindex(',)',sqltext) = 0)

update #tmp2
set sqltext = replace(sqltext, ',''''',',null')
where not (charindex(',''''',sqltext) = 0)

update #tmp2
set sqltext = replace(sqltext, '(''''',',null')
where not (charindex('(''''',sqltext) = 0)

set nocount off

select sqltext from #tmp2 order by id

go

drop table #tmp
drop table #tmp2

Sunday, 12 May 2013

datatable group by



private DataTable GetGroupedBy(DataTable dt, string columnNamesInDt, string groupByColumnNames, string typeOfCalculation)

{

//Return its own if the column names are empty

if (columnNamesInDt == string.Empty || groupByColumnNames == string.Empty)

{

return dt;

}




//Once the columns are added find the distinct rows and group it bu the numbet

DataTable _dt = dt.DefaultView.ToTable(true, groupByColumnNames);




//The column names in data table

string[] _columnNamesInDt = columnNamesInDt.Split(',');




for (int i = 0; i < _columnNamesInDt.Length; i = i + 1)

{

if (_columnNamesInDt[i] != groupByColumnNames)

{

_dt.Columns.Add(_columnNamesInDt[i]);

}

}







//Gets the collection and send it back

for (int i = 0; i < _dt.Rows.Count; i = i + 1)

{

for (int j = 0; j < _columnNamesInDt.Length; j = j + 1)

{

if (_columnNamesInDt[j] != groupByColumnNames)

{

_dt.Rows[i][j] = dt.Compute(typeOfCalculation + "(" + _columnNamesInDt[j] + ")", groupByColumnNames + " = '" + _dt.Rows[i][groupByColumnNames].ToString() + "'");

}

}

}




return _dt;

}



-----------------------------------



how to use

//The way to call the Group By Function //Argument 1 = Data table in IMAGE 1 //Argument 2 = The fields you want for the data table returned //Argument 3 = The field you want to group by //Argument 4 = The function you want to do It can be either SUM, COUNT, AVG etc. DataTable dtGroupedBy = GetGroupedBy(dt, "CodeName,Quantity,Current", "CodeName", "Sum");


Friday, 26 April 2013

ASP.NET DATE FORMAT CUSTOME

string SSS = DateTime.Now.ToString("dddd, d MMMM, yyyy", CultureInfo.CreateSpecificCulture("en-US"));

DateTime date1 = new DateTime(2008, 8, 29, 19, 27, 15);

Console.WriteLine(date1.ToString("ddd d MMM", 
                  CultureInfo.CreateSpecificCulture("en-US")));
// Displays Fri 29 Aug
Console.WriteLine(date1.ToString("ddd d MMM", 
                  CultureInfo.CreateSpecificCulture("fr-FR")));
// Displays ven. 29 août 
 
 
DateTime date1 = new DateTime(2008, 8, 29, 19, 27, 15, 18);
CultureInfo ci = CultureInfo.InvariantCulture;

Console.WriteLine(date1.ToString("hh:mm:ss.f", ci));
// Displays 07:27:15.0
Console.WriteLine(date1.ToString("hh:mm:ss.F", ci));
// Displays 07:27:15
Console.WriteLine(date1.ToString("hh:mm:ss.ff", ci));
// Displays 07:27:15.01
Console.WriteLine(date1.ToString("hh:mm:ss.FF", ci));
// Displays 07:27:15.01
Console.WriteLine(date1.ToString("hh:mm:ss.fff", ci));
// Displays 07:27:15.018
Console.WriteLine(date1.ToString("hh:mm:ss.FFF", ci));
// Displays 07:27:15.018
 
 

          
Format specifier
Description
Examples
"d"
The day of the month, from 1 through 31.
More information: The "d" Custom Format Specifier.
6/1/2009 1:45:30 PM -> 1
6/15/2009 1:45:30 PM -> 15
"dd"
The day of the month, from 01 through 31.
More information: The "dd" Custom Format Specifier.
6/1/2009 1:45:30 PM -> 01
6/15/2009 1:45:30 PM -> 15
"ddd"
The abbreviated name of the day of the week.
More information: The "ddd" Custom Format Specifier.
6/15/2009 1:45:30 PM -> Mon (en-US)
6/15/2009 1:45:30 PM -> Пн (ru-RU)
6/15/2009 1:45:30 PM -> lun. (fr-FR)
"dddd"
The full name of the day of the week.
More information: The "dddd" Custom Format Specifier.
6/15/2009 1:45:30 PM -> Monday (en-US)
6/15/2009 1:45:30 PM -> понедельник (ru-RU)
6/15/2009 1:45:30 PM -> lundi (fr-FR)
"f"
The tenths of a second in a date and time value.
More information: The "f" Custom Format Specifier.
6/15/2009 13:45:30.617 -> 6
6/15/2009 13:45:30.050 -> 0
"ff"
The hundredths of a second in a date and time value.
More information: The "ff" Custom Format Specifier.
6/15/2009 13:45:30.617 -> 61
6/15/2009 13:45:30.005 -> 00
"fff"
The milliseconds in a date and time value.
More information: The "fff" Custom Format Specifier.
6/15/2009 13:45:30.617 -> 617
6/15/2009 13:45:30.0005 -> 000
"ffff"
The ten thousandths of a second in a date and time value.
More information: The "ffff" Custom Format Specifier.
6/15/2009 13:45:30.6175 -> 6175
6/15/2009 13:45:30.00005 -> 0000
"fffff"
The hundred thousandths of a second in a date and time value.
More information: The "fffff" Custom Format Specifier.
6/15/2009 13:45:30.61754 -> 61754
6/15/2009 13:45:30.000005 -> 00000
"ffffff"
The millionths of a second in a date and time value.
More information: The "ffffff" Custom Format Specifier.
6/15/2009 13:45:30.617542 -> 617542
6/15/2009 13:45:30.0000005 -> 000000
"fffffff"
The ten millionths of a second in a date and time value.
More information: The "fffffff" Custom Format Specifier.
6/15/2009 13:45:30.6175425 -> 6175425
6/15/2009 13:45:30.0001150 -> 0001150
"F"
If non-zero, the tenths of a second in a date and time value.
More information: The "F" Custom Format Specifier.
6/15/2009 13:45:30.617 -> 6
6/15/2009 13:45:30.050 -> (no output)
"FF"
If non-zero, the hundredths of a second in a date and time value.
More information: The "FF" Custom Format Specifier.
6/15/2009 13:45:30.617 -> 61
6/15/2009 13:45:30.005 -> (no output)
"FFF"
If non-zero, the milliseconds in a date and time value.
More information: The "FFF" Custom Format Specifier.
6/15/2009 13:45:30.617 -> 617
6/15/2009 13:45:30.0005 -> (no output)
"FFFF"
If non-zero, the ten thousandths of a second in a date and time value.
More information: The "FFFF" Custom Format Specifier.
6/1/2009 13:45:30.5275 -> 5275
6/15/2009 13:45:30.00005 -> (no output)
"FFFFF"
If non-zero, the hundred thousandths of a second in a date and time value.
More information: The "FFFFF" Custom Format Specifier.
6/15/2009 13:45:30.61754 -> 61754
6/15/2009 13:45:30.000005 -> (no output)
"FFFFFF"
If non-zero, the millionths of a second in a date and time value.
More information: The "FFFFFF" Custom Format Specifier.
6/15/2009 13:45:30.617542 -> 617542
6/15/2009 13:45:30.0000005 -> (no output)
"FFFFFFF"
If non-zero, the ten millionths of a second in a date and time value.
More information: The "FFFFFFF" Custom Format Specifier.
6/15/2009 13:45:30.6175425 -> 6175425
6/15/2009 13:45:30.0001150 -> 000115
"g", "gg"
The period or era.
More information: The "g" or "gg" Custom Format Specifier.
6/15/2009 1:45:30 PM -> A.D.
"h"
The hour, using a 12-hour clock from 1 to 12.
More information: The "h" Custom Format Specifier.
6/15/2009 1:45:30 AM -> 1
6/15/2009 1:45:30 PM -> 1
"hh"
The hour, using a 12-hour clock from 01 to 12.
More information: The "hh" Custom Format Specifier.
6/15/2009 1:45:30 AM -> 01
6/15/2009 1:45:30 PM -> 01
"H"
The hour, using a 24-hour clock from 0 to 23.
More information: The "H" Custom Format Specifier.
6/15/2009 1:45:30 AM -> 1
6/15/2009 1:45:30 PM -> 13
"HH"
The hour, using a 24-hour clock from 00 to 23.
More information: The "HH" Custom Format Specifier.
6/15/2009 1:45:30 AM -> 01
6/15/2009 1:45:30 PM -> 13
"K"
Time zone information.
More information: The "K" Custom Format Specifier.
With DateTime values:
6/15/2009 1:45:30 PM, Kind Unspecified ->
6/15/2009 1:45:30 PM, Kind Utc -> Z
6/15/2009 1:45:30 PM, Kind Local -> -07:00 (depends on local computer settings)
With DateTimeOffset values:
6/15/2009 1:45:30 AM -07:00 --> -07:00
6/15/2009 8:45:30 AM +00:00 --> +00:00
"m"
The minute, from 0 through 59.
More information: The "m" Custom Format Specifier.
6/15/2009 1:09:30 AM -> 9
6/15/2009 1:09:30 PM -> 9
"mm"
The minute, from 00 through 59.
More information: The "mm" Custom Format Specifier.
6/15/2009 1:09:30 AM -> 09
6/15/2009 1:09:30 PM -> 09
"M"
The month, from 1 through 12.
More information: The "M" Custom Format Specifier.
6/15/2009 1:45:30 PM -> 6
"MM"
The month, from 01 through 12.
More information: The "MM" Custom Format Specifier.
6/15/2009 1:45:30 PM -> 06
"MMM"
The abbreviated name of the month.
More information: The "MMM" Custom Format Specifier.
6/15/2009 1:45:30 PM -> Jun (en-US)
6/15/2009 1:45:30 PM -> juin (fr-FR)
6/15/2009 1:45:30 PM -> Jun (zu-ZA)
"MMMM"
The full name of the month.
More information: The "MMMM" Custom Format Specifier.
6/15/2009 1:45:30 PM -> June (en-US)
6/15/2009 1:45:30 PM -> juni (da-DK)
6/15/2009 1:45:30 PM -> uJuni (zu-ZA)
"s"
The second, from 0 through 59.
More information: The "s" Custom Format Specifier.
6/15/2009 1:45:09 PM -> 9
"ss"
The second, from 00 through 59.
More information: The "ss" Custom Format Specifier.
6/15/2009 1:45:09 PM -> 09
"t"
The first character of the AM/PM designator.
More information: The "t" Custom Format Specifier.
6/15/2009 1:45:30 PM -> P (en-US)
6/15/2009 1:45:30 PM -> 午 (ja-JP)
6/15/2009 1:45:30 PM -> (fr-FR)
"tt"
The AM/PM designator.
More information: The "tt" Custom Format Specifier.
6/15/2009 1:45:30 PM -> PM (en-US)
6/15/2009 1:45:30 PM -> 午後 (ja-JP)
6/15/2009 1:45:30 PM -> (fr-FR)
"y"
The year, from 0 to 99.
More information: The "y" Custom Format Specifier.
1/1/0001 12:00:00 AM -> 1
1/1/0900 12:00:00 AM -> 0
1/1/1900 12:00:00 AM -> 0
6/15/2009 1:45:30 PM -> 9
"yy"
The year, from 00 to 99.
More information: The "yy" Custom Format Specifier.
1/1/0001 12:00:00 AM -> 01
1/1/0900 12:00:00 AM -> 00
1/1/1900 12:00:00 AM -> 00
6/15/2009 1:45:30 PM -> 09
"yyy"
The year, with a minimum of three digits.
More information: The "yyy" Custom Format Specifier.
1/1/0001 12:00:00 AM -> 001
1/1/0900 12:00:00 AM -> 900
1/1/1900 12:00:00 AM -> 1900
6/15/2009 1:45:30 PM -> 2009
"yyyy"
The year as a four-digit number.
More information: The "yyyy" Custom Format Specifier.
1/1/0001 12:00:00 AM -> 0001
1/1/0900 12:00:00 AM -> 0900
1/1/1900 12:00:00 AM -> 1900
6/15/2009 1:45:30 PM -> 2009
"yyyyy"
The year as a five-digit number.
More information: The "yyyyy" Custom Format Specifier.
1/1/0001 12:00:00 AM -> 00001
6/15/2009 1:45:30 PM -> 02009
"z"
Hours offset from UTC, with no leading zeros.
More information: The "z" Custom Format Specifier.
6/15/2009 1:45:30 PM -07:00 -> -7
"zz"
Hours offset from UTC, with a leading zero for a single-digit value.
More information: The "zz" Custom Format Specifier.
6/15/2009 1:45:30 PM -07:00 -> -07
"zzz"
Hours and minutes offset from UTC.
More information: The "zzz" Custom Format Specifier.
6/15/2009 1:45:30 PM -07:00 -> -07:00
":"
The time separator.
More information: The ":" Custom Format Specifier.
6/15/2009 1:45:30 PM -> : (en-US)
6/15/2009 1:45:30 PM -> . (it-IT)
6/15/2009 1:45:30 PM -> : (ja-JP)
"/"
The date separator.
More Information: The "/" Custom Format Specifier.
6/15/2009 1:45:30 PM -> / (en-US)
6/15/2009 1:45:30 PM -> - (ar-DZ)
6/15/2009 1:45:30 PM -> . (tr-TR)
"string"
'string'
Literal string delimiter.
6/15/2009 1:45:30 PM ("arr:" h:m t) -> arr: 1:45 P
6/15/2009 1:45:30 PM ('arr:' h:m t) -> arr: 1:45 P
%
Defines the following character as a custom format specifier.
More information: Using Single Custom Format Specifiers.
6/15/2009 1:45:30 PM (%h) -> 1
\
The escape character.
6/15/2009 1:45:30 PM (h \h) -> 1 h
Any other character
The character is copied to the result string unchanged.
More information: Using the Escape Character.
6/15/2009 1:45:30 AM (arr hh:mm t) -> arr 01:45 A
  

Tuesday, 23 April 2013

SQL PARENT CHILD TREE STRUCTURE

-- DECLARE
DECLARE @Company AS TABLE(EmpID INT, ParentID INT, PersonName VARCHAR(100));

-- Insert Temp Records
INSERT INTO @Company(EmpID, ParentID, PersonName)
VALUES(1 , NULL , 'Maulik Dhorajia')
    , (2 , NULL , 'Bhavesh Gohel')
    , (3 , NULL , 'Dinesh Padhiyar')
    , (4 , 2 , 'Vijay Kumar')
    , (5 , 1 , 'Jitendra Makwana')
    , (6 , 4 , 'Jayesh Dhobi')
    , (7 , 1 , 'Shivpalsinh Jhala')
    , (8 , 5 , 'Amit Patel')
    , (9 , 3 , 'Abidali Suthar')

-- Default data

SELECT * FROM @Company;


-- Incorrect result which we usually find on Internet
;WITH CTECompany
AS
(
    SELECT EmpID, ParentID, PersonName , 0 AS HLevel
    FROM @Company
    WHERE ParentID IS NULL
   
    UNION ALL
   
    SELECT C.EmpID, C.ParentID, C.PersonName , (CTE.HLevel + 1) AS HLevel
    FROM @Company C
    INNER JOIN CTECompany CTE ON CTE.EmpID = C.ParentID
    WHERE C.ParentID IS NOT NULL
)

-- Misleading SQL
SELECT * FROM
(
    SELECT
        EmpID
        , ParentID
        , HLevel
        , (REPLICATE( '----' , HLevel ) + PersonName) AS Person
    FROM CTECompany
) AS P
ORDER BY HLevel;


-- Working Example
;WITH CTECompany
AS
(
    SELECT
        EmpID,
        ParentID,
        PersonName ,
        0 AS HLevel,
        CAST(RIGHT(REPLICATE('_',5) +  CONVERT(VARCHAR(20),EmpID),20) AS VARCHAR(MAX)) AS OrderByField
    FROM @Company
    WHERE ParentID IS NULL
   
    UNION ALL
   
    SELECT
        C.EmpID,
        C.ParentID,
        C.PersonName ,
        (CTE.HLevel + 1) AS HLevel,
        CTE.OrderByField + CAST(RIGHT(REPLICATE('_',5) +  CONVERT(VARCHAR(20),C.EmpID),20) AS VARCHAR(MAX)) AS OrderByField
    FROM @Company C
    INNER JOIN CTECompany CTE ON CTE.EmpID = C.ParentID
    WHERE C.ParentID IS NOT NULL
)

-- Working Example
SELECT
    EmpID
    , ParentID
    , HLevel
    , PersonName
    , (REPLICATE( '----' , HLevel ) + PersonName) AS Person
FROM CTECompany
ORDER BY OrderByField,PersonName;

Tuesday, 5 March 2013

Creating Mailing Labels in SQL Server Reporting Services


Most word processing applications (Word, WordPerfect, and so on) provide the capability to create a “mail merge” from which to generate mailing labels in different formats and layouts. Mailing labels are an automated way to generate the address labels for a large number of envelopes or parcels that need to be mailed.
Reporting Services provides a few features that allow you to create mailing labels in different formats - the only thing you need to know are the exact dimensions of the label template you are targeting when printing. A common mailing label format is to use multiple columns (newspaper layout) in order to maximize the number of labels printed.
This recipe shows you how to leverage Reporting Services’ multi-column layout features to create basic mailing labels, while explaining certain limitations in the rendering engine.
Product Versions
  • All versions (examples provided in Reporting Services 2008)
    What You’ll Need
  • AdventureWorksDW2008 database (or your own database and query that provides name and address data to the report)
  • The exact template size for the labels you will use when printing (including all margins and column widths)
  • A PDF reader or Image viewer (Windows provides an image viewer that supports TIFF, JPG, GIF, and PNG)
Designing the Report
The final outcome of this recipe should be a multi-column report “perfectly” sized to fit the print layout of a mailing label template, as shown in Figure P6-7.
SQL Server Reporting Services mailing label report
Figure 6-7
For the purposes of this recipe, you will utilize the Avery 5160 label template, which contains the following dimensions:
  • Length: 2.5935”
  • Height: 1.0000”
  • Margins: Top 0.5”, Bottom 0.5”, Left 0.21975”, Right 0.21975”
  • Horizontal Spacing (gutter): 0.14000”
  • Vertical Spacing (gutter): 0”
  • 30 labels per sheet of letter size (8.5” x 11”) paper
1. Begin by creating a new report in Report Builder, and removing all default items and the page footer from the report. You need to have a blank design surface - the report dimensions need to be very exact in order to match the labels when printing.
2. Add a new data source to the report and set its connection string to the SQL Server where the AdventureWorksDW2008 database is stored. If you are using your own database, simply choose the server and database for your own data, so you can provide your own query in the next step.
3. Add a new dataset for the data source created in the previous step. Set the query type to Text, and type the following SQL query in the command text window:
SELECT
       c.Title, c.FirstName, c.MiddleName, c.LastName
       , c.AddressLine1, c.AddressLine2
       , g.City, g.StateProvinceCode, g.PostalCode
       , g.EnglishCountryRegionName
FROM
       dbo.DimCustomer c
LEFT OUTER JOIN
       dbo.DimGeography g
       ON g.GeographyKey = c.GeographyKey
WHERE
       g.EnglishCountryRegionName = @Country
ORDER BY
       g.StateProvinceCode
       , g.City
       , c.LastName
Notice that this includes a parameter for the Country field—this will allow you to filter down the dataset to a specific country. Also, you order by State/Province, then by City, and finally, by Last Name. That seems like a reasonable way to order your labels and keep your mail person happy when processing a large case of envelopes or parcels. You should have a blank report with a dataset as shown in Figure P6-8.
blank SQL Server Reporting Services report
Figure P6-8
4. The label template will contain three columns on a letter-size sheet of paper, so you need to set up the report size and layout for multiple columns:
a. In the Report Properties, set the Orientation to Portrait, and Paper Size to Letter (8.5in x 11in).
b. Set the Left and Right Margins to 0.21975in
c. Set the Top and Bottom Margins to 0.5in
Click the OK button to save changes. The report properties should look as shown in Figure P6-9.
SQL Server Reporting Services report properties
Figure P6-9
5. Next, you need to set up multiple columns. The Columns and ColumnSpacing properties of the Report are not exposed via the Report Properties dialog shown in Figure P6-9. Instead, you must edit them in the Properties page for the report (if you don’t see it in Report Builder, choose the View menu from the ribbon and check the Properties box to display it).
Expand the Columns node from the Properties page, and make the following edits (see Figure P6-10):
a. Change the Columns property to 3
b. Change the ColumnSpacing property to 0.14in. This is the size of our label template’s Horizontal Spacing gutter - the spacing between columns on the page.
SQL Server Reporting Services properties page columns node
Figure P6-10
Notice that the report body has been “duplicated” by the number of columns specified in the Columns property, even though you only get to work on the leftmost body template (the other ones are simply placeholders to show the designer that multiple columns will be rendered at runtime).
Since you already specified the dimensions for your label template, you might be wondering why the report is so wide, making you scroll to the right to see the multiple columns. There still are a few dimensions that you must set for the body of the report.
It’s important to understand how report page sizes, body sizes, margins and column spacing relate to each other in the report. Figure P6-11 illustrates how these dimensions fit together.
SQL Server Reporting Services report page sizes, body sizes, margins and column spacing
Figure 6-11
From the diagram, you can then infer that the labels themselves will be the body, while the sheet of paper will be the report page. With that in mind you will set the body dimensions according to the label size specified previously.
6. Click on the Body element, and change the following properties in the Properties window:
a. Expand the Size node and set the Width to 2.5935in
b. Set the Height to 1in
Your report body should now look like the diagram in Figure P6-12.
SQL Server Reporting Services report body
Figure P6-12
7. Finally, you add a data region to the body of the report, attach it to your dataset and drag data fields in for the mailing data.
In Reporting Services 2008, you can use either a List or a Table data region (both use the underlying tablix). However, if you are using an earlier version, I have found the table layout to yield more consistent results and provide a better design surface to control your formatting. I’ll let you, as the report developer, decide what best fits your needs according to the requirements of your mailing label design.
I’ll simply use a table with a single column and detail row, and rely on the Reporting Services 2008 rich text features of the textbox that allow you to drag and drop multiple dataset fields onto the table cell. Make sure your data region (table or list) stretches to fill 100% of the body size, without expanding it. In other words, the width and height of the data region should match that of the body. The easiest way to do this is by drawing the data region on the design surface instead of dragging it from the menu. After adding the data region to the report, double-check that your report body was not modified by the data region.
If you are using a previous version of Reporting Services, you will either use string concatenation expressions (not recommended) or use a rectangle in the cell to make the cell a free-form container for your textboxes - then you can use multiple textboxes for the dataset field, each positioned absolutely within the cell.
Here’s a trick to ensure that your labels are positioned correctly within the cell: select the cell textbox and set its vertical alignment to “Middle”. For some reason, Reporting Services will duplicate the data cell otherwise.
Assuming your mailing labels will require First and Last Name, Address 1 and 2, City, State, Postal Code, and Country name to be displayed, the table cell layout should look similar to Figure P6-13.
SQL Server Reporting Services mailing label
Figure P6-13
While previewing the report, keep in mind that the report viewer used in the preview of the report designer uses the Graphics Device Interface (GDI) to render the report to the screen, and because we are using multi-columns, a feature only supported in the print-oriented renderers for Reporting Services, you will only get to see the expected outcome if you click the “Print Layout” button in the preview window. This leverages the print-preview renderer and not the regular preview renderer. Also, because of this limitation, a multi-column report layout is only supported in print-oriented formats: PDF, TIFF (Image), Print, and Print Preview. You cannot export and save your report to Word, so your best option is to use PDF.
The final outcome of the report in Print Layout preview is shown in Figure P6-14 (I added light gray borders to my textbox, so you can see the size of the labels).
SQL Server Reporting Services mailing label print layout
Figure P6-14
Final Thoughts
Reporting Services provides developers with several features to help create insightful analytical reports, as well as print-ready reports. With its multicolumn capabilities, we are able to drastically change the layout of reports that target a print layout such as PDF.
This recipe showed you how to leverage the multicolumn feature to create mailing labels akin to those found in Microsoft Word’s mail merge feature. The compelling story, however, lies within the integration and automation possibilities. As most developers would agree, Office automation is rather complex and can be a bit frustrating at times. However, with Reporting Services’ web services API, its extensibility, and rich subscription model, the task of automating the creation of mailing labels becomes much simpler.
Credits and Related References
Mailing label dimensions provided by Worldlabel.Com, Inc. (www.worldlabel.com).