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