webservice data -> sql - best practice?

See more:

C#

XML

SQL

.NET

Amazon

webservice

Hey guys,

I need some advice on how to put a data of a webservice response the most efficent way into a database (structure and values). It should be quick and may be dirty (e.g. no DAL, etc.). At the moment i am just playing around with some webservices, comparing data quality before implementing a final solution. My first approach was to do some mapping in the generated Reference.cs directly. For a pitty i figured out, that the data i am interested in is spread widely in subclasses of the Simple.AmazonECS.Item (further called Item) properties. While my solution works on basic types in the Item class (see the uncommented [Attributes]), it is no good for these complex types. For a webservice may change and i have to do this with several services, what would be the best way to get the respons into a database. I looked at some projects like xsd2db, but none worked for the webservices schema (extracted from wsdl). There must be an easy way to get from a webservice xml schema to a database schema...

PS: this may be part of the problem: all classes are inherited from object! i think this is not supported by the entity framework, right?

Any suggestions?

// This ist the auto generated reference.cs of the Amazon Webservice 
// http://webservices.amazon.com/AWSECommerceService/AWSECommerceService.wsdl

namespace Simple.Amazon.ECS
{
    // [...] Enumerations, Classes and other stuff
    
    [System.CodeDom.Compiler.GeneratedCodeAttribute("System.Xml", "4.0.30319.233")]
    [System.SerializableAttribute]
    [System.Diagnostics.DebuggerStepThroughAttribute]
    [System.ComponentModel.DesignerCategoryAttribute("code")]
    [System.Xml.Serialization.XmlTypeAttribute(Namespace="http://webservices.amazon.com/AWSECommerceService/2009-07-01")]
    public partial class Image : object, System.ComponentModel.INotifyPropertyChanged
    {
        #region Fields

        private string uRLField;
        //[...]

        #endregion Fields

        #region Events

        public event System.ComponentModel.PropertyChangedEventHandler PropertyChanged;

        #endregion Events

        #region Properties

        [System.Xml.Serialization.XmlElementAttribute(Order=0)]
        [Column(Name = "URL", Storage = "uRLField", AutoSync = AutoSync.OnInsert, IsPrimaryKey= true)]
        [DataMember(Order = 0)]
        public string URL
        {
            get {
                return this.uRLField;
            }
            set {
                this.uRLField = value;
                this.RaisePropertyChanged("URL");
            }
        }
        //[...]

        #endregion Properties

        #region Methods
        
        protected void RaisePropertyChanged(string propertyName)
		//[...]

        #endregion Methods
    }
    
    [System.CodeDom.Compiler.GeneratedCodeAttribute("System.Xml", "4.0.30319.233")]
    [System.SerializableAttribute]
    [System.Diagnostics.DebuggerStepThroughAttribute]
    [System.ComponentModel.DesignerCategoryAttribute("code")]
    [System.Xml.Serialization.XmlTypeAttribute(AnonymousType=true, Namespace="http://webservices.amazon.com/AWSECommerceService/2009-07-01")]
    // my approach
    //[Table(Name = "dbo.AmazonItems")]
    //[DataContract()]
    public partial class Item : object, System.ComponentModel.INotifyPropertyChanged
    {
        #region Fields

        private string aSINField;
        private Image largeImageField;
        private ItemAttributes itemAttributesField;
		//[...]

        #endregion Fields

        #region Events

        public event System.ComponentModel.PropertyChangedEventHandler PropertyChanged;

        #endregion Events

        #region Properties

        [System.Xml.Serialization.XmlElementAttribute(Order=0)]
        // my approach - this works for the basic type
        //[Column(Name = "ASIN",Storage="aSINField", AutoSync = AutoSync.OnInsert, IsPrimaryKey = true)]
        //[DataMember(Order=0)]
        public string ASIN
        {
            get {
                return this.aSINField;
            }
            set {
                this.aSINField = value;
                this.RaisePropertyChanged("ASIN");
            }
        }

        [System.Xml.Serialization.XmlElementAttribute(Order=10)]
        // my approach - this crashes - unknown sql type - the data i am inteseted in
        //[Column(Name = "ItemAttributes", Storage = "itemAttributesField", AutoSync = AutoSync.OnInsert, DbType="SQL_VARIANT")]
        //[DataMember(Order = 10)]
        public ItemAttributes ItemAttributes
        {
            get {
                return this.itemAttributesField;
            }
            set {
                this.itemAttributesField = value;
                this.RaisePropertyChanged("ItemAttributes");
            }
        }

        [System.Xml.Serialization.XmlElementAttribute(Order=8)]
        // my approach - this crashed to - complex type
        //[Column(Name = "LargeImage", Storage = "largeImageField", AutoSync = AutoSync.OnInsert)]
        //[DataMember(Order = 8)]
        public Image LargeImage
        {
            get {
                return this.largeImageField;
            }
            set {
                this.largeImageField = value;
                this.RaisePropertyChanged("LargeImage");
            }
        }

        //[...] more Classes, Events, ...



// well, here is what i am doing in Main()

// create a WCF Amazon ECS client
BasicHttpBinding binding		= new BasicHttpBinding(BasicHttpSecurityMode.Transport);
binding.MaxReceivedMessageSize	= int.MaxValue;
AWSECommerceServicePortTypeClient client = new AWSECommerceServicePortTypeClient(
    binding,
    new EndpointAddress("https://webservices.amazon.com/onca/soap?Service=AWSECommerceService"));

// add authentication to the ECS client
client.ChannelFactory.Endpoint.Behaviors.Add(new AmazonSigningEndpointBehavior(accessKeyId, secretKey));

// prepare an ItemSearch request
ItemSearchRequest request1	= new ItemSearchRequest();

request1.SearchIndex			= "All";
request1.DeliveryMethod = DeliveryMethod.Ship;
request1.MerchantId = "All";
request1.Condition = Condition.All;
request1.ResponseGroup = new string[] {"Large"};
request1.Keywords = "test";

// prepare the search
ItemSearch itemSearch = new ItemSearch();
itemSearch.AssociateTag = "";
// batch the two requests together
itemSearch.Request = new ItemSearchRequest[] { request1, request2 };
itemSearch.AWSAccessKeyId = accessKeyId;            
            
// issue the ItemSearch request
ItemSearchResponse response = client.ItemSearch(itemSearch);

// issue the DataContext on SQL 2008 R2 server
DataContext db = new DataContext(@"Data Source=./;Database=test;Integrated Security=SSPI;");

// a Table of the Simple.ECS.Amazon.Item class
Table<item> it = db.GetTable<item>();

// drop the test DB
db.DeleteDatabase();
// and recreate the structure it to see if it works
// this ist where errors occure 
// for unknown sql types (complex types like Simple.Amazon.ECS.Image)
db.CreateDatabase();

foreach (var responses in response.Items)
{
    foreach (var item in responses.Item)
    {
        it.InsertOnSubmit(item);
    }
}
db.SubmitChanges();
db.Connection.Close();                                                
</item></item>