Connecting to data sources with web services in Visual C#.NET

Generation QR Code JIS X 0510 in Visual C#.NET Connecting to data sources with web services

(String)dr["ContactTitle"]; customers.Address = dr["Address"] is DBNull string.Empty : (String)dr["Address"]; customers.City = dr["City"] is DBNull string.Empty : (String)dr["City"]; customers.Region = dr["Region"] is DBNull string.Empty : (String)dr["Region"]; customers.PostalCode = dr["PostalCode"] is DBNull string.Empty : (String)dr["PostalCode"]; customers.Country = dr["Country"] is DBNull string.Empty : (String)dr["Country"]; customers.Phone = dr["Phone"] is DBNull string.Empty : (String)dr["Phone"]; customers.Fax = dr["Fax"] is DBNull string.Empty : (String)dr["Fax"];

customersList.Add(customers); } } return customersList; }

After you ve created your finder, we ll need a specific finder so that a single row can be returned. This is used in places such as a Business Data column. The web method takes a parameter of CustomerID, which happens to be the unique identifier for the Customers entity, as shown in listing C.2.

public Customers GetCustomersSpecificFinder(String customerid) { Customers customer= new Customers(); string sqlQuery = "Select * From dbo.[Customers] Where ([CustomerID]=@CustomerID)";

using (SqlConnection sqlConnection = new SqlConnection(connectionString)) { sqlConnection.Open(); SqlCommand comm = new SqlCommand(sqlQuery, sqlConnection); comm.Parameters.AddWithValue("@CustomerID", customerid); DataSet dataSet = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(comm); adapter.Fill(dataSet, "dbo.[Customers]"); if(dataSet.Tables[0].Rows.Count > 0) { DataRow dr = dataSet.Tables[0].Rows[0]; customers.CustomerID = dr["CustomerID"] is DBNull string.Empty : (String)dr["CustomerID"]; customers.CompanyName = dr["CompanyName"] is DBNull string.Empty :

(String)dr["CompanyName"]; customers.ContactName = dr["ContactName"] is DBNull string.Empty : (String)dr["ContactName"]; customers.ContactTitle = dr["ContactTitle"] is DBNull string.Empty : (String)dr["ContactTitle"]; customers.Address = dr["Address"] is DBNull string.Empty : (String)dr["Address"]; customers.City = dr["City"] is DBNull string.Empty : (String)dr["City"]; customers.Region = dr["Region"] is DBNull string.Empty : (String)dr["Region"]; customers.PostalCode = dr["PostalCode"] is DBNull string.Empty : (String)dr["PostalCode"]; customers.Country = dr["Country"] is DBNull string.Empty : (String)dr["Country"]; customers.Phone = dr["Phone"] is DBNull string.Empty : (String)dr["Phone"]; customers.Fax = dr["Fax"] is DBNull string.Empty : (String)dr["Fax"];

} } return customer }

Finally, each entity should also have an IDEnumerator so that the data can be indexed and used in the MOSS Search. Listing C.3 shows a web method that simply returns all of the identifiers.

public List<String> GetCustomersIdEnumerator() { List<String> ids = new List<String>(); string sqlQuery = "Select CustomerID from dbo.[Customers]"; using (SqlConnection sqlConnection = new SqlConnection(connectionString)) { sqlConnection.Open(); SqlCommand comm = new SqlCommand(sqlQuery, sqlConnection); DataSet dataSet = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(comm); adapter.Fill(dataSet, "dbo.[Customers]"); foreach (DataRow dr in dataSet.Tables[0].Rows) { ids.Add(dr["CustomerID"] is DBNull string.Empty : (String)dr["CustomerID"]); } } return ids; }

The Customer class, shown in listing C.4, is simple and basically returns a property for each column within the entity. The data type is specified for each column. The class is then used as the return type for each of the previous methods.

[ServiceContract (Namespace = "devguide")] public interface INorthwindWCFService { [OperationContract] List<Customers> GetCustomersFinder(); [OperationContract] Customers GetCustomersSpecificFinder(String customerid); [OperationContract] List<String> GetCustomersIdEnumerator(); } [DataContract] public class Customers { private String _CustomerID; [DataMember] public String CustomerID { get { return _CustomerID; } set { _CustomerID = value; } } private String _CompanyName; [DataMember] public String CompanyName { get { return _CompanyName; } set { _CompanyName = value; } } private String _ContactName; [DataMember] public String ContactName

{ get { return _ContactName; } set { _ContactName = value; } } private String _ContactTitle; [DataMember] public String ContactTitle { get { return _ContactTitle; } set { _ContactTitle = value; } } private String _Address; [DataMember] public String Address { get { return _Address; } set { _Address = value; } } private String _City; [DataMember] public String City { get { return _City; } set { _City = value; } } private String _Region; [DataMember] public String Region { get { return _Region; } set {

_Region = value; } } private String _PostalCode; [DataMember] public String PostalCode { get { return _PostalCode; } set { _PostalCode = value; } } private String _Country; [DataMember] public String Country { get { return _Country; } set { _Country = value; } } private String _Phone; [DataMember] public String Phone { get { return _Phone; } set { _Phone = value; } } private String _Fax; [DataMember] public String Fax { get { return _Fax; } set { _Fax = value; } } }