Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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>
Posted 7-Sep-11 1:39am
Edited 7-Sep-11 11:07am
v3

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

On understanding the code, I noticed your datacontract is defined in 'Item' object, which is used through out web service and database operations.
 
If you don't want to use DAL layer, you can use LInQ to Entity framework which is well proven for DB operations. You can map the auto generated 'Item' object into Entity object. Then you are done.
  Permalink  
Comments
HansHammel at 9-Sep-11 2:13am
   
Well, but how can I do that. I found several examples doing things just the other way round, mapping an entity that comes from an existing database. in my case it comes from the webservice and there is no database to map to.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 6,045
1 DamithSL 4,601
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,260


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 8 Sep 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100