Thursday, December 30, 2010

WCF Errors with IIS7 with .Net Framework 4.0

WCF Errors with IIS7 with .Net Framework 4.0 

When I start hosting WCF service on the IIS7 then I got following errors and I spend enough time to resolve this, following error could be caused by different reason but I described the reason and solution which I faced. I was using Visual Studio 2010 with .Net framework 4.0 and IIS7.  

Error-1: HTTP Error 404.17 - Not Found
The requested content appears to be script and will not be served by the static file handler.

Cause
WCF service developed on different .NET framework (V4.0) and Application Pool set in different version (V2.0).
Solution
Go to the IIS 7 and select Application Pools à right click on your application and change the .NET Framework version to V4.0. (Change the v2.0 to v4.0 in Application Pool)

Error-2: HTTP Error 500.19 - Internal Server Error

The requested page cannot be accessed because the related configuration data for the page is invalid.


Cause
This error occurs when you application deployed on the different version of .NET and Web.config has different version mentioned.
Like: Application Pool running on .Net 4.0 and Web.Config includes .Net 3.5
Solution
Go to the application project à right click and go to "Property Pages"à Build tab and Set the application Target Framework to .Net Framework 4.0.
Another quick fix, change application pool set to 2.0 as default.
Error-3: No protocol binding matches the given address 'http://localhost/IISHostedService/MyService.svc'. Protocol bindings are configured at the Site level in IIS or WAS configuration.

 <endpoint address="http://localhost/IISHostedService/MyService.svc" binding="wsHttpBinding" contract="IMyService">
Solution
Remove this address URL from Web.config, because this is already configured in IIS.
Go to the web.config and change in the Services section like below
<endpoint address="" binding="wsHttpBinding" contract="IMyService">


Error-4: Metadata publishing for this service is currently disabled.


There are couples of help available on internet and on the site page where this error displays. But for that was not the problem

Cause

Normally this error occurs when "httpGetEnabled" is false in Web.config 
check your web.config <serviceMetadata httpGetEnabled="true">


But for me the problem was, I created one folder explicitly in “C:/inetpub” called ‘MyFolder’ and then from the visual studio I created WCF service on that folder choosing

Web location: File System and File created on “C:/inetpub/MyFolder”.


Now when I deploy my WCF service and try to access then its throws following error “
Metadata publishing for this service is currently disabled.

Solution

DONOT create any folder explicitly on “C:/inetpub” and while creating new WCF service choose following option

Web location: HTTP and File created on “http://localhost/MyFolder”.

Wednesday, October 13, 2010

How to pass Credential to Web Service with Basic Authentication

How to pass Credential to Web Service with Basic Authentication
 You can pass credential to web service in different ways
Ø  Manual:  Passing user Id and Password manually
Ø  Default: Get the System Credential of the application.
Ø  Network: Get the network Credential of the current security.
C#.NET code Sample for Manual
using System.Net;

// MyWebService is the web service reference to your project
MyWebService myService = new MyWebService();
myService.Timeout = -1;
myService.PreAuthenticate = true;
System.Net.CredentialCache userCredentials = new System.Net.CredentialCache();
NetworkCredential netCred = new NetworkCredential("User Id", "Password");
userCredentials.Add(new Uri(myService.Url), "Basic", netCred);
myService.Credentials = userCredentials;

You can also use Default or Network Credentials like below
C#.NET code Sample for Default Credential
// MyWebService is the web service reference to your project
MyWebService myService = new MyWebService();
myService.Timeout = -1;
myService.PreAuthenticate = true;
myService.Credentials = System.Net.CredentialCache.DefaultCredentials;

C#.NET code Sample for Default Network Credential
// MyWebService is the web service reference to your project
MyWebService myService = new MyWebService();
myService.Timeout = -1;
myService.PreAuthenticate = true;
myService.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;

Now you can call methods on the myService to use.

Friday, October 01, 2010

Table Value Parameter with LINQ, (LINQ for Stored Procedure that has datatable as an input parameter)

Table Value Parameter with LINQ, (LINQ for Stored Procedure that has datatable as an input parameter)
If you are using LINQ to SQL with SQL server database then Visual Studio will not allow you to drag your SP into dbml file like you do for other simple SP and also Visual Studio will not able to generate the code for this. There is no other ways to generate the code for this situation (SP has input parameter as a datatable) in the DataContext.
You have to write the code into partial class (given with your .dbml file), now assume you have SP signature like below

Stored Procedure

CREATE PROCEDURE [dbo].[GetResource]
( @ResourceID dbo.Resource READONLY) 

Resource: is datatable type

CREATE TYPE [dbo].[Resource] AS TABLE
( [ResourceId] [int] NOT NULL )

While writing the code in the DataContext we have to manually execute the SP and return as a List, see below code

NameSpace

using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Linq.Expressions;

DataContext Code

public partial class YourDataContext : System.Data.Linq.DataContext
{
public IEnumerable<ResourceData> GetResource(List<int> resourceId)
{
DataTable resourceIdTable = new DataTable();
resourceIdTable.Columns.Add("Resource", typeof(int));
// Fill the datatable from the input List
foreach (int r in resourceId)
resourceIdTable.Rows.Add(r);
// "GetSqlCommand()" is a method will open the connection and return sqlCommand Object
SqlCommand cmd = ConnectionManager.GetSqlCommand("GetResource", connectionString);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter p1 = cmd.Parameters.AddWithValue("@ResourceID", resourceIdTable);
p1.SqlDbType = SqlDbType.Structured;
p1.TypeName = "Resource";
SqlDataReader reader = cmd.ExecuteReader();
return this.Translate<ResourceData>(reader).ToList();
}
}

Call DataContext Method

YourDataContext rdc = new YourDataContext();
IEnumerable<ResourceData> result = rdc.GetResource(resourceId);

foreach (ResourceData res in result)
{ MessageBox.Show("ResourceId : " + res.Id + " Resource Name : " + res.Name);
}

ResourceData: is Entity class like below

public sealed class ResourceData
{
private int _id;
private string _name;
public int Id
{
get { return _id; }
private set { _id = value; }
}
public string Name
{
get { return _name; }
private set { _name = value; }
}
}

LINQ for Stored procedure returning Multiple ResultSets

LINQ for Stored procedure returning Multiple ResultSets

When you have SP which is returning multiple recordset and when you try to drag into dbml file then Visual Studio will not able to generate the code for this in the DataContext class.

For this you have to manually write the code into Partial class (present with the dbml file), I usually drag my SP into dbml file and then copy past generated code into partial class and modify, just replace "ISingleResult" with "IMultipleResults".

Let say you have one SP called "GerResources" which is returning two RecordSets

Stored Procedure

CREATE PROCEDURE [dbo].[GetResource]

( @ResourceID int

)

AS

Begin

Select Id, Value from Employee where ResourceId = @ResourceID

Select Name , Value from Content where ResourceId = @ResourceID

End

NameSpace

using System.Data.Linq;

using System.Data.Linq.Mapping;

using System.Linq;

using System.Linq.Expressions;

DataContext Code

public partial class YourDataContext : System.Data.Linq.DataContext

{

[FunctionAttribute(Name = "dbo.GetResource")]

[ResultType(typeof(Employee))]

[ResultType(typeof(Content))]

public IMultipleResults GetResource(int resourceID)

{

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), resourceID);

return ((IMultipleResults)(result.ReturnValue));

}

}

Content and Employee is Entity class.

public sealed class Employee

{

private int _id;

private string _value;

public int Id

{

get { return _id; }

private set { _id = value; }

}

public string Value

{

get { return _value; }

private set { _value = value; }

}

}

public sealed class Content

{

private int _name;

private string _value;

public int Name

{

get { return _name; }

private set { _name = value; }

}

public string Value

{

get { return _value; }

private set { _value = value; }

}

}

Call DataContext Method

// Create and open the connection

YourDataContext rdc = new YourDataContext();

//Get multiple records from resource (Employee)

List<Employee> emp = result.GetResult<Employee>().ToList();

//Get multiple records from resource (content)

List<Content> cont = result.GetResult<Content>().ToList();

//Read the Data from Employee Object

foreach (Employee e in emp)

{

MessageBox.Show(e.Id + e.Value);

}

//Read the Data from Content Object

foreach (Content c in cont)

{

MessageBox.Show(c.Name + c.Value);

}

To read only Single records from LINQ

Note: If you have single Row returning from SP then use "Single()" method instead of "foreach" loop

//Create and open the connection

YourDataContext rdc = new YourDataContext();

//Get multiple records from resource (Employee)

List<Employee> emp = result.GetResult<Employee>().Single();

//Display the value

MessageBox.Show(e.Id + e.Value);

Wednesday, September 22, 2010

Fiddler is free web debugging tool

Fiddler is a Web Debugging Proxy which logs all HTTP(S) traffic between your computer and the Internet. Fiddler allows you to inspect all HTTP(S) traffic, set breakpoints, and "fiddle" with incoming or outgoing data. Fiddler includes a powerful event-based scripting subsystem, and can be extended using any .NET language.

Fiddler is freeware and can debug traffic from virtually any application, including Internet Explorer, Mozilla Firefox, Opera, and thousands more.

Download from here http://www.fiddler2.com/fiddler2/version.asp


Wednesday, August 25, 2010

Error: Unknown Return Type, The return types for the following stored procedures could not be detected….(LINQ).

Error: Unknown Return Type, The return types for the following stored procedures could not be detected…..

Error:

The return types for the following stored procedures could not be detected. Set the return type for each stored procedure in the Properties window.

Situations

This error usually comes when you are trying to add your Stored Procedure into DBML (LINQ) file.

Cause

This problem occurs whenever the designer cannot figure out the return type of the SP. This usually happens when the stored procedure has multiple results or uses a temp table. The SQL Server feature that attempts to derive the Meta data of the function reports no columns for the result shape, so the designer defaults to thinking it only has the single int return value.

Solutions

· The one way to get these types of stored procedures to work is to edit DBML by hand or write your own method signature for the procedure in a partial class To Handle multiple record set return from stored procedure by LINQ see the link here.

· The second way is to avoid using #temp Table in your stored procedure, instead of you can use Table type variable like below (@TempTable)

Ex:

DECLARE @TempTable TABLE

(

AttributeID INT,

Value NVARCHAR(200)

)


INSERT INTO @TempTable Select * from Attribute

OR

--Execute SP and insert results into @TempTable

INSERT INTO @TempTable Exec GetAttribute @Id

You can do all operation which you was doing with #Temp table like Join, Insert, Select etc.

Thursday, August 19, 2010

Concatenating Row Values into one column in Oracle, SQL Server Database and LINQ

Concatenating Row Values into one column in Oracle, SQL Server Database and LINQ


Concatenating row values is very common requirements in different databases, to write this kind of query is very time consuming, I tried to combine Oracle,SQL Server database and LINQ simple solution into below example. Maybe this is not matched with your requirement but you will get an idea to proceed.


Requirement


Let say you have one Employee table having FK reference with department table like below


Employee table

EMP_ID

DEPT_ID

1

101

2

101

3

102

4

102

5

102

How to write a select query so that it returns two columns DEPT_ID and Comma Separated employees under that department. Example


DEPT_ID

EMPLOYEES

101

1,2

102

3,4,5


Solution (ORACLE DATABASE)

To achieve above requirement there are different solution available in oracle different versions

Oracle 10g: Use COLLECT function like

SELECT DEPT_ID,

CAST (COLLECT (TO_CHAR(EMP_ID)) AS SYS.ODCIVARCHAR2LIST) AS EMPLOYEES

FROM EMPLOYEE

GROUP BY DEPT_ID


Note: If SYS.ODCIVARCHAR2LIST does not work then you have to create one table object, see below example

CREATE OR REPLACE TYPE Collect_Type AS TABLE OF VARCHAR2 (4000);

SELECT DEPT_ID,

CAST (COLLECT (TO_CHAR(EMP_ID)) AS Collect_Type) AS EMPLOYEES

FROM EMPLOYEE

GROUP BY DEPT_ID


Oracle 11g: Use LISTAGG function like

SELECT DEPT_ID,

LISTAGG (TO_CHAR(EMP_ID), ', ') WITHIN GROUP (ORDER BY DEPT_ID)as "EMPLOYEES"

FROM employees;


Ref: http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm


Result:

DEPT_ID

EMPLOYEES

101

1,2

102

3,4,5


Solution (SQL SERVER DATABASE)

SQL Server: SQL server database also you can achieve same result in multiple ways, like XML PATH or Custom Function etc.

Using XML PATH

Select Main.Dept_ID,

Left(Main.Employee,Len(Main.Employee)-1) As "Employees"

From(Select distinct ST2.Dept_ID,

(Select cast(ST1.Emp_ID as varchar(50)) + ',' AS [text()]

From dbo.Employee ST1

Where ST1.Dept_ID= ST2.Dept_ID

ORDER BY ST1.Dept_ID

For XML PATH ('')) [Employee]

From dbo.Employee ST2) [Main]


Using Custom Function

CREATE FUNCTION ConcatEmp(@DeptId varchar(50))

RETURNS VARCHAR(1000) AS

BEGIN

DECLARE @temp VARCHAR(1000)

SELECT @temp = COALESCE(@temp + ', ', '') + cast(Emp_ID as varchar(50))

FROM [Employee]

WHERE Dept_ID = @DeptId

return @temp

END


After creating above function you can simply execute in select statement like below


SELECT Dept_ID

, dbo.ConcatEmp(Dept_ID) AS Employees

FROM Employee

GROUP BY Dept_ID


Result:

DEPT_ID

EMPLOYEES

101

1,2

102

3,4,5


Solution (LINQ QUERY)

LINQ is one of the .NET (Framework 3.0 and above) features to write SQL query in code, if you are trying to achieve same result using LINQ, you can get idea from following example


Here I have created one small console application and write following code to get same results as SQL Server and Oracle Database


using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;


namespace ConsoleApplication1

{

public class Test

{

static void Main()

{

var Employee = new[]

{

new { Dept="101", EmpId="1" },

new { Dept="101", EmpId="2" },

new { Dept="102", EmpId="3" },

new { Dept="102", EmpId="4" },

new { Dept="102", EmpId="5" },

};


var groupedUsers = Employee.GroupBy(Dept => Dept.Dept);


foreach (var group in groupedUsers)

{

Console.Write(" {0} :", group.Key);

foreach (var entry in group)

{

Console.Write(" {0}", entry.EmpId);

}

Console.WriteLine();

}

Console.Read();

}

}

}


Result:

----------

101: 1,2

102: 3,4,5