Wednesday, February 15, 2012

Web Service or WCF authertication using Soap header

1. This is how do we authenticate web service for secure data exchange;It is done using Soap Header.we have to set username and password here in soap header.

Use the code like in web service:


using System;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Diagnostics;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;


/// <summary>
/// Summary description for WebService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]

public class WebService : System.Web.Services.WebService
{
    public class AuthHeader : SoapHeader
    {
        public string Username;
        public string Password;
    }

    public AuthHeader Authentication;
    [SoapHeader("Authentication", Required = true)]
    [WebMethod(Description = "Returns some sample data")]
    public DataSet SensitiveData()
    {
        DataSet data = new DataSet();

        //Do our authentication
        //this can be via a database or whatever
        if (Authentication.Username == "test" && Authentication.Password == "test")
        {
            //they are allowed access to our sensitive data

            //just create some dummy data
            DataTable dtTable1 = new DataTable();
            DataColumn drCol1 = new DataColumn("Data", System.Type.GetType("System.String"));
            dtTable1.Columns.Add(drCol1);

            DataRow drRow = dtTable1.NewRow();
            drRow["Data"] = "Sensitive Data";
            dtTable1.Rows.Add(drRow);
            dtTable1.AcceptChanges();

            data.Tables.Add(dtTable1);



        }
        else
        {
            data = null;
        }

        return data;
    }
   

}


2.Now in client application refer the web service and create its object and then pass username and password as required by web service and authenticated then data will return otherwise nothing will return.

code is as follows:


 protected void Page_Load(object sender, EventArgs e)
        {
            //simple client page
            AuthWebService.WebService webService = new AuthWebService.WebService();
            AuthWebService.AuthHeader authentication = new AuthWebService.AuthHeader();
            authentication.Username = "test";
            authentication.Password = "test";
            webService.AuthHeaderValue = authentication;
            DataSet ds = new DataSet();
            ds = webService.SensitiveData();
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }


Now run and enjoyee web service authentication and same way we can do for WCf....



Monday, February 13, 2012

procedure expects parameter statement of type ntext nchar nvarchar sql server

If you have such error in dynamic SQL query,

Please use NVARCHAR in declation intead of VARCHAR like below example



ALTER PROCEDURE [dbo].[SPC_GetClientFinancialDetail]  
@FullRef varchar(36)  
   
AS
declare @SourceName as varchar(10)
declare @strSourceName as varchar(50)
declare @SQLQuery1 as Nvarchar(2000)
declare @SQLQuery2 as Nvarchar(2000)
Begin

Select @SourceName= ISNULL(PDC_CRITERIA,'M') FROM WARRANT_DEFAULTS
IF(@SourceName<>'')
Begin

IF(@SourceName='M')
Begin
             set @strSourceName='VWCURRENT_WARRPAYMENTS_MONTHBASIS'
End
else
Begin
            set @strSourceName='VWCURRENT_WARRPAYMENTS_DAYBASIS'
End
 End
  else
Begin
    set @strSourceName='VWCURRENT_WARRPAYMENTS_MONTHBASIS'
End



   
Set @SQLQuery1='SELECT  isnull(sum(PAYMENT_AMT),0) as Tot    
FROM VWWARRANT_PAYMENTS    
WHERE WARRANT_ID = '''+@FullRef+''' AND DIRECT_PAYMENT =''Y'' and payment_dishonoured=''N'' and payment_qc_done=''Y''  
union  SELECT  isnull(sum(PAYMENT_AMT),0) as Tot  
FROM  '+ @strSourceName+'  
WHERE WARRANT_ID = '''+@FullRef+''' AND DIRECT_PAYMENT =''N''  and payment_dishonoured=''N'' and payment_qc_done=''Y'''
Print @SQLQuery1
EXECUTE sp_executeSQL    @SQLQuery1

   

SELECT AMOUNT_REMITTED as  PAYMENT_AMT, convert(varchar(22),payment_remitted_on,103) as REMITDATE    
FROM AUDIT_WARRANT_PAYMENTS  WHERE cast(WARRANT_ID as varbinary) = cast(@FullRef as varbinary)  
AND ACTION_PERFORMED='REMIT' AND ACTION_SOURCE='REMIT_PAYMENT'    
ORDER BY CONVERT(DATETIME, payment_remitted_on,103) ASC    
   
Set @SQLQuery2='SELECT ''Creditor'' as WhoTo,  PAYMENT_AMT,  
PAYMENT_DATE , payment_month , payment_year , payment_no  
FROM VWWARRANT_PAYMENTS    
WHERE WARRANT_ID = '''+@FullRef+''' AND DIRECT_PAYMENT =''Y'' and payment_dishonoured=''N'' and payment_qc_done=''Y''    
union all SELECT ''Sherforce'' as WhoTo , PAYMENT_AMT,  
PAYMENT_DATE , payment_month , payment_year , payment_no  
FROM '+ @strSourceName+'  
WHERE WARRANT_ID = '''+@FullRef+''' AND DIRECT_PAYMENT =''N'' and payment_dishonoured=''N'' and payment_qc_done=''Y''    
ORDER BY  payment_year ,payment_month , payment_no'
Print @SQLQuery2
 EXECUTE sp_executeSQL    @SQLQuery2


End





Thursday, February 9, 2012

Line Break in C#

Environment.NewLine

string strLabel =string.empty;

strLabel = "Rec " + dr["DispCode"].ToString() + "- " + dr["ShortDesc"].ToString() + " Number of Cases " + dr["Cnt"].ToString() + Environment.NewLine + "Cash value of Segment £ " + dr["Amt"].ToString() +  "#";

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




Wednesday, February 8, 2012

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());
}

Monday, February 6, 2012

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.






.