Line Break in SQL Server 2005

I have created a stored procedure and include line break logic

Create Procedure GeneratePieChartEnforceblePie  
@ClientId varchar(50),  
@FromDate varchar(50),  
@Todate varchar(50)  
   
As  
DECLARE @fromD DATETIME
DECLARE @toD DATETIME
DECLARE @NewLineChar AS CHAR(2) --This is used for Line Break and set its value
SET @NewLineChar = CHAR(13) + CHAR(10)                                
SET @fromD = CONVERT(datetime,@FromDate,103)                                  
SET @toD = CONVERT(datetime,@Todate,103)      
Begin  

 
select   ('Rec '+DispCode +'-'+ShortDesc+'-'+'Number of Cases ' +Cast(count(fullref) AS varchar(50))+  @NewLineChar + 'Cash value of Segment £ '++Cast(sum(Amt) AS varchar(50)) ) as 'Code',count(cid) as Cnt, DispCode, ShortDesc , sum(Amt) as Amt from vw_EnfPie
 where cclientid=@ClientId  and convert(datetime, entrydate, 103) between convert(datetime, @FromDate, 103) and convert(datetime, @Todate, 103)
 group by DispCode, ShortDesc order by cast(DispCode as numeric)
End

Pie Chart in asp.net

1.Download the Chart control framework with SP1
http://archive.msdn.microsoft.com/mschart
and run exec for available of chart control in Visual studio
2. In aspx page add chart control

<asp:Chart ID="Chart1" runat="server" Height="336px" Width="580px">
                 <Titles>
      <asp:Title Name="Title1" Text="Whole Pie"
         Alignment="TopCenter" Font="Verdana, 12pt, style=Bold" ForeColor="Purple">
      </asp:Title>
   </Titles>
                <Series>
                <asp:Series Name="Series1" ChartType="Pie"
                       
                        CustomProperties="PieLabelStyle=Disabled, PieDrawingStyle=SoftEdge, CollectedThresholdUsePercent=False, PieLineColor=Purple"
                        Legend="Legend1">
                </asp:Series>
             
                </Series>
                <Legends >
                    <asp:Legend Name="Legend1">
                    </asp:Legend>
                 
                    </Legends>
                <ChartAreas>
                <asp:ChartArea Name="ChartArea1">
                </asp:ChartArea>
                </ChartAreas>
                </asp:Chart>



3. In Code behind page include the namespace


using System.Drawing;
using System.Drawing.Imaging;
using System.Drawing.Drawing2D;
using System.Web.UI.DataVisualization.Charting;

4. Now bind data in chart control,
I have use class and here i develope method as PieChart()


  public DataSet PieChart()
        {
            SqlConnection _con = new SqlConnection(ConString);
            SqlCommand _cmd = new SqlCommand("GeneratePieChart", _con);
            _cmd.CommandType = CommandType.StoredProcedure;
            _cmd.Parameters.Add("@ClientId", SqlDbType.VarChar, 50).Value = ClientId;
            _cmd.Parameters.Add("@FromDate", SqlDbType.VarChar, 50).Value = fromdate;
            _cmd.Parameters.Add("@Todate", SqlDbType.VarChar, 50).Value = ToDate;
            DataSet dsClients = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = _cmd;
            da.Fill(dsClients);
            return dsClients;

        }

5. Now create the object of class and use method which return dataset to bind with Pie Chart

 Client ObjCls = new Client();

     Dataset  DsClient = new DataSet();
        DsClient = ObjCls.PieChart;

        DataView dv = DsClient.Tables[0].DefaultView;

        Chart1.Series["Series1"].Points.DataBindXY(dv, "status_code", dv, "Cnt");
        this.Chart1.Series[0]["PieLabelStyle"] = "Outside";

      //Set border width so that labels are shown on the outside

        this.Chart1.Series[0].BorderWidth = 1;

        this.Chart1.Series[0].BorderColor = System.Drawing.Color.FromArgb(26, 59, 105);

        // Add a legend to the chart and dock it to the bottom-center

        //  this.Chart2.Legends.Add("Legend1");

        this.Chart1.Legends[0].Enabled = true;

        this.Chart1.Legends[0].Docking = Docking.Bottom;

        this.Chart1.Legends[0].Alignment = System.Drawing.StringAlignment.Center;

        // Set the legend to display pie chart values as percentages
        // Again, the P2 indicates a precision of 2 decimals

        this.Chart1.Series[0].LegendText = "#PERCENT{P2}";
        // By sorting the data points, they show up in proper ascending order in the legend

        this.Chart1.DataManipulator.Sort(PointSortOrder.Descending, Chart1.Series[0]);

6. Now run the page and you will get output

Use of Foreach loop with Dataset in asp.net

DataSet ds = new DataSet();
foreach(DataRow dr in ds.Tables[0].Rows)
{
Console.WriteLine(dr["ColName"].ToString());
}

Creating Proxy Object Or Class for WCF Service

1.Create a folder to store proxy object or Class(here I created folder with name WCFProxy in d drive)

2.Host the Web Service Or WCF in server or Create virtual directory

3.Open Visual studio command prompt

4: Use the follwoing command as per your Service Host location

C:\Program Files\Microsoft Visual Studio 90.\VC>  http://localhost/WCFService /Out:/D:/WCFProxy//Myservice.cs  /config:d:/WCFProxy/MyService.config


5. Now How to use it in Client application(Called application)
 Put the Created Proxy calss ( Myservice.cs) in App_code folder and add namespace to this class.

6.In page add namespace like Using WCFServenamespace

7. Create the Proxy class object Like

ServiceClient Obj=new ServiceClient();

Now you will get all the metheds list availabe in WCF Service

Sting Test=Obj.GetClientName();

This all about how to Create and use proxy Class in application.






.

Month wise Data retriving using SQL query and how to validate correct data

SELECT  YEAR(EntryDate) AS 'Year', MONTH(EntryDate) AS 'Month', COUNT(*) AS monthly_Case_count
FROM dbo.SP_Case WHERE YEAR(EntryDate)=2011
GROUP BY YEAR(EntryDate), MONTH(EntryDate)
ORDER BY YEAR(EntryDate), MONTH(EntryDate);

SELECT COUNT(*) FROM dbo.SP_Case
WHERE YEAR(EntryDate)=2011 AND MONTH(EntryDate)=2

Using HyperLink Field of GridView in asp.net

Supposer we have gridview as:


<ASP:GRIDVIEW id=GridView1 runat="server" autogeneratecolumns="False" datakeynames="CustomerID" datasourceid="SqlDataSource1">    
    <COLUMNS>    
        <ASP:HYPERLINKFIELD text="Detail" datanavigateurlfields="CustomerID" datanavigateurlformatstring="CustomerDetails.aspx?customerId={0}"></ASP:HYPERLINKFIELD>        
        <ASP:BOUNDFIELD datafield="CustomerID" headertext="CustomerID" readonly="True" sortexpression="CustomerID"></ASP:BOUNDFIELD>        
        <ASP:BOUNDFIELD datafield="CompanyName" headertext="CompanyName" sortexpression="CompanyName"></ASP:BOUNDFIELD>        
        <ASP:BOUNDFIELD datafield="ContactName" headertext="ContactName" sortexpression="ContactName"></ASP:BOUNDFIELD>    
    </COLUMNS>
</ASP:GRIDVIEW>

<ASP:SQLDATASOURCE id=SqlDataSource1 runat="server" connectionstring="<%$ ConnectionStrings:NORTHWNDConnectionString %>"
selectcommand="SELECT [CustomerID], [CompanyName], [ContactName] FROM [Customers]">
</ASP:SQLDATASOURCE>


1.

<asp:TemplateField>
                <ItemTemplate>
                <asp:HyperLink ID="hlDetails1" Text="Details" runat="server"
                NavigateUrl='<%# "CustomerDetails.aspx?customer=" + Eval("CustomerID") + "&CompanyName=" + Server.UrlEncode(Eval("CompanyName").ToString())%>' />
                </ItemTemplate>
    </asp:TemplateField>

2. Seting NavigateUrl property of HyperLink by Calling method in code-behind:

<asp:TemplateField> 

    <ItemTemplate> 

        <asp:HyperLink id="hlDetails2" Text="Details" Runat="server" 

        NavigateUrl='<%# GetUrl(Eval("CustomerID"),Eval("CompanyName"))%>' /> 

    </ItemTemplate> 

</asp:TemplateField>

public string GetUrl(object id, object companyname) 

 { 

 //here you can do validation e.g. if companyname is not null or something 

 //Also you can do some customization based on your logged-in user 

 //You can get the Page location dynamically from say web.config

 string url = 

"~/CustomerDetails.aspx?customerid=" + id.ToString() + "&companyname=" + 

Server.UrlEncode(companyname.ToString()); 

 return url; 

 }

3.

 Seting  NavigateUrl in RowDataBound event

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CustomerID" 
DataSourceID="SqlDataSource1" onrowdatabound="GridView1_RowDataBound">
...
...
</asp:GridView>

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) 
{ 
 if (e.Row.RowType == DataControlRowType.DataRow) 
 { 
    HyperLink hl = (HyperLink)e.Row.FindControl("hlDetails2"); 
    if (hl != null) 
    { 
        DataRowView drv = (DataRowView)e.Row.DataItem; 
        string id = drv["CustomerID"].ToString(); 
        string companyname = drv["CompanyName"].ToString(); 
        hl.NavigateUrl = "~/CustomerDetails.aspx?customerid=" + id.ToString() + "&companyname=" + Server.UrlEncode(companyname.ToString()); 
    } 
 } 
}

Crack to access server using mstsc even after exceeding user access limit

mstsc /v ServerName /admin /public