Tags

, ,

My scope was to create a WCF service to get data from any database table, table name being pass as parameter. So, first think I did is to create a table called “TEST” with the following structure:

[First Name] nvarchar(50)
[Last Name]  nvarchar(50)
[ID Number]  numeric(18, 0)

The expected XML response was supposed to look like this (of course will look different if I choose another table with another structure, but you get the idea).

<?xml version="1.0"?>
<GetTables.TableRows xmlns="http://schemas.datacontract.org/2004/07/MVC.DataServices" xmlns:i="http://www.w3.org/2001/XMLSchemainstance">
<GetTables.TableRow>
<TableField>
<FieldName>First Name</FieldName>
<FieldType>String</FieldType>
<FieldValue>test</FieldValue>
</TableField>
<TableField>
<FieldName>Last Name]</FieldName>
<FieldType>String</FieldType>
<FieldValue>test</FieldValue>
</TableField>
<TableField>
<FieldName>ID Number</FieldName>
<FieldType>Decimal</FieldType>
<FieldValue/>
</TableField>
</GetTables.TableRow>
<GetTables.TableRow>
<TableField>
<FieldName>First Name</FieldName>
<FieldType>String</FieldType>
<FieldValue>test 1</FieldValue>
</TableField>
<TableField>
<FieldName>Last Name]</FieldName>
<FieldType>String</FieldType>
<FieldValue>test 1</FieldValue>
</TableField>
<TableField>
<FieldName>ID Number</FieldName>
<FieldType>Decimal</FieldType>
<FieldValue/>
</TableField>
</GetTables.TableRow>
<GetTables.TableRow>
<TableField>
<FieldName>First Name</FieldName>
<FieldType>String</FieldType>
<FieldValue>test 2</FieldValue>
</TableField>
<TableField>
<FieldName>Last Name]</FieldName>
<FieldType>String</FieldType>
<FieldValue>test 2</FieldValue>
</TableField>
<TableField>
<FieldName>ID Number</FieldName>
<FieldType>Decimal</FieldType>
<FieldValue/>
</TableField>
</GetTables.TableRow>
</GetTables.TableRows>

Below is the code, which did this for me:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.ServiceModel.Web;
using System.Text;


namespace MVC.DataServices
{
    [ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class GetTables
    {
    
        /// <summary>
        /// Get data from database table
        [OperationContract]
        [WebGet(ResponseFormat = WebMessageFormat.Xml)]
        public TableRows GetTableData(string tableName)
        {
            TableRows results = new TableRows();
            WebOperationContext.Current.OutgoingResponse.ContentType = "text/xml";
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand("SELECT * FROM " + tableName,connection);
                SqlDataReader reader = command.ExecuteReader();
                while(reader.Read() == true)
                {
                    TableRow row = new TableRow();
                    for(int  i = 0; i < reader.FieldCount; i++)
                    {
                        TableField field = new TableField();
                        field.FieldName = Convert.ToString(reader.GetName(i));
                        field.FieldType = Convert.ToString(reader.GetFieldType(i).Name);
                        field.FieldValue = Convert.ToString(reader.GetValue(i));
                        row.Add(field);
                    }
                    results.Add(row);
                }
             }

            return results;
        }

        [DataContract(Name="TableField")]
        public class TableField
        {
            [DataMember]
            public string FieldName { get; set; }
            [DataMember]
            public string FieldType { get; set; }
            [DataMember]
            public string FieldValue { get; set; }
        }


        [CollectionDataContract]
        public class TableRow : List<TableField>
        {
            public TableRow()
            {
            }
        }

        [CollectionDataContract]
        public class TableRows : List<TableRow>
        {
            public TableRows()
            {

            }
        }
        
    }
}

I just needed to pass TableName parameter (http://localhost/DataServices/GetTables.svc/GetTableData?TableName=TEST) to the web service and it gets all the data from the specified table and display it into an XML format.

Advertisements