Geeks With Blogs

News This is the *old* blog. The new one is at blog.sixeyed.com
Elton Stoneman
This is the *old* blog. The new one is at blog.sixeyed.com

[Source: http://geekswithblogs.net/EltonStoneman]

Following on from my post on Using the WCF SQL Adapter in .NET: Calling Stored Procedures (see that post for download and installation instructions for the WCF LOB adapter pack), this one looks at using the adapter to execute SQL statements on database objects.

Walkthrough: Executing SQL Statements

Add Adapter Service Reference generates separate entity and client classes for each table you select, and separate request and response classes for each table operation. To generate proxies for executing SQL statements against a table, choose the operations from the Tables view of the hierarchy:

Selecting the Insert and Select operations against the BikeTypes table will generate the following class structure:

The client for connecting to SQL Server is a standard WCF client class, inheriting from ClientBase and specifying the ServiceContract as its channel – in this case the interface is TableOp_dbo_BikeTypes which has operation contracts representing the Insert and Select statements. The entity representing the database table implements IExtensibleDataObject and provides DataMember-flagged properties for each table column; an array of entities is used as the input for the Insert operation, and the return for the Select operation (wrapping the underlying use of generated Request and Response classes).

The Request and Response classes are flagged with MessageContract attributes. Message contracts are less commonly seen than DataContract and ServiceContract, but they allow you finer control over the messages sent and received by the adapter, including the ability to specify whether data is to be serialized in the header or body of the message (see Using Message Contracts). In the generated classes, MessageContract is used to specify the wrapper name and namespace, which puts the message payload within a defined element in the SOAP body. The following attribute:

[System.ServiceModel.MessageContractAttribute(WrapperName="Select", WrapperNamespace="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/BikeTypes", IsWrapped=true)]

- generates a SOAP message for the SelectRequest class which looks like this:

<s:Envelope xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:s="http://www.w3.org/2003/05/soap-envelope">

<s:Header>

<a:Action s:mustUnderstand="1">TableOp/Select/dbo/BikeTypes</a:Action>

<a:MessageID>urn:uuid:9a5fe359-e988-4504-96a9-b9b721d00502</a:MessageID>

<a:ReplyTo>

<a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address>

</a:ReplyTo>

</s:Header>

<s:Body>

<Select xmlns="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/BikeTypes">

<Columns>*</Columns>

<Query>WHERE BikeTypeCode LIKE '%R%'</Query>

</Select>

</s:Body>

</s:Envelope>

 

Note that the SOAP action is the Node Id for the operation from Add Adapter Service Reference. The request message contains Columns and Query elements, which are used in the call to refine the size and content of the resultset. In code you specify the Columns property with "*" to return all, or a comma-separated list of column names (which should be listed in the same order as defined in the table). Query can be null, empty or contain a WHERE clause to restrict the results:

TableOp_dbo_BikeTypesClient client = new TableOp_dbo_BikeTypesClient();

client.Open();

BikeTypes[] allBikeTypes = client.Select("*", null);

BikeTypes[] bikeTypeDescriptions = client.Select("BikeTypeDescription", string.Empty);

BikeTypes[] likeRBikeTypes = client.Select("*", "WHERE BikeTypeCode LIKE '%R%'");

A populated Query property will limit the number of items returned. A populated Columns property will limit the number of populated elements in the response, so the typed object will have null values for any unmapped columns.

For the insert, you pass an array of populated entities to the call:

List<BikeTypes> bikeTypes = new List<BikeTypes>();

bikeTypes.Add(new BikeTypes());

bikeTypes[0].BikeTypeCode = "NEW";

bikeTypes[0].BikeTypeDescription = "New Type";

 

TableOp_dbo_BikeTypesClient client = new TableOp_dbo_BikeTypesClient();

client.Open();

client.Insert(bikeTypes.ToArray());

If you want to write identity values for tables which have an identity column, you can specify AllowIdentityInsert in the binding configuration. Otherwise, any specified columns have the value from the entity inserted; null values have NULL inserted. The return contains an array of long values containing the identity of the inserted rows – unless the table does not have an identity column, in which case the return is null.

Similarly the adapter can generate request and response classes for Update and Delete statements, which follow the same pattern. The generated stack has the same benefits as with the stored procedure calls – the code is very light and it uses the standard WCF stack, so if you'd rather generate or hand-craft your own connections, that's a definite option. I'll explore it in a later post.

Posted on Wednesday, March 4, 2009 7:21 PM WCF , SQL Server | Back to top


Comments on this post: Using the WCF SQL Adapter in .NET: Executing SQL Statements

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
Yeah,cool code, thanks for share,nice,great!Data Recovery Software|Guard Process
Left by robust on Mar 04, 2010 5:17 AM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
Yes,I agree it, thanks for share, you are great!Restore Deleted Files|进程保护
Left by Hark on Mar 04, 2010 5:18 AM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
Subject of this post is very interested.I am pretty much pleased with your good work..Please keep on sharing your views.surely will visit your blog again later.
Left by neways-85 on Apr 28, 2010 5:40 AM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
BizTalk Server no longer supports the SQL Adapter. We recommend you use Microsoft BizTalk Adapter for SQL Server to achieve the same functionality.
Left by firewall audit on Oct 25, 2010 1:20 PM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
It really worked for me. But i am facing only one problem. I have set the CommandTimeout property of my command to various values like 60,90,120,130 etc. After this the page waits exactly for number of seconds of CommandTimeout and then gives me the error !

What to do ? Please give me some ideas...
Left by nutritionals on Dec 02, 2010 5:41 AM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
Thanks for such a guidance. I easily execute my statement using this adapter. I like sql very much and now i know i will not face any problem.
Left by driving instructors in Milton Ke on Feb 26, 2011 10:52 AM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
I was just browsing for relevant blog posts for my project research and I happened to discover yours..thanks a lot !!
Left by Buy Anabolic Steroids on Feb 28, 2011 1:03 PM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
I was just browsing for relevant blog posts for my project research and I happened to discover yours. Thanks for the useful information..good going man..
Left by drag racing on Mar 04, 2011 12:58 PM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
Nice information, valuable and excellent design, as share good stuff with good ideas and concepts, lots of great information and inspiration, both of which we all need, thanks for all the enthusiasm to offer such helpful information here.
Left by Sacramento Carpet Repair on Mar 12, 2011 6:24 PM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
It is an awesome blog where we discuss many things with others. I am happy to be a part of this blog. thanks again from the core of my heart.
Left by electric garage heaters on Mar 13, 2011 11:34 AM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
Excellent post, one of the few articles I’ve read today that said something unique! One new subscriber here,great work
Left by bachelor party ideas on Mar 21, 2011 10:09 AM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
You have beautifully presented your thought in this blog post. I admire the time and effort you put into your blog and detailed information you offer.
Left by Fort Worth Carpet Repair on Mar 27, 2011 5:27 AM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
The power of the internet can not be put into words, the world is surely becoming a much smaller place... Indian escorts London
Left by London Escorts on Jun 05, 2011 12:19 AM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
I am facing a problem. I put my property CommandTimeout command to different values ​​as 60,90,120,130 etc. After that the page looked exactly the number of seconds CommandTimeout and then gives me the error!
Just Dreams Reviews
Left by Ian on Jun 14, 2011 11:18 AM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
Spot on with this write-up, I truly think this website needs much more consideration. I’ll probably be again to read much more, thanks for that info.
Estudiar ingles en el extranjero
Left by effren on Jun 18, 2011 4:03 AM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
very nice post
please keep posting these kind of most informative posts
Thanks
Left by freetress on Jun 18, 2011 9:42 AM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
Thanks for sharing your thoughts and ideas on this one. Please keep posting about such articles as they really spread useful information. Thanks for this particular sharing. I hope it stays updated, take care.
Left by personal loans on Aug 28, 2011 7:53 PM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
Please email me with any hints & tips about how you made your website look this good, I would appreciate it.
Left by Car Racing on Nov 08, 2011 11:21 AM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
Excellent post, thanks for sharing. I'm going to try this and see what comes through :-)
Left by Computer Repair Austin on Nov 16, 2011 1:40 AM

# Very Useful
Requesting Gravatar...
Executing the SQL statement was very helpful, thanks for sharing mate.
Left by Web design and web development on Dec 03, 2011 6:07 AM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
what is this sql adapter in next executing sql statements? Well I guess this is going to be so good also and I will be learning a lot from it.
Left by furnaces indiana on Dec 16, 2011 10:45 AM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
yeah I agree with this too "Following on from my post on Using the WCF SQL Adapter in .NET: Calling Stored Procedures (see that post for download and installation instructions for the WCF LOB adapter pack), this one looks at using the adapter to execute SQL statements on database objects. "
Left by kids clothes on Dec 16, 2011 10:52 AM

# Buy Stromectol Online
Requesting Gravatar...
Thank you for sharing to us.there are many person searching about that now they will find enough resources by your post.I would like to join your blog anyway so please continue sharing with us.
Left by Buy Generic Lyrica on Dec 27, 2011 1:54 PM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
That is an interesting theory, but a theory nontheless
Left by skirts for women on May 13, 2012 2:00 PM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
Thanks for this awesome tutorial, it helped me a lot!
Left by Podryw on May 28, 2012 7:52 PM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
MessageContract is used to specify the wrapper name and namespace, which puts the message payload within a defined element in the SOAP body.
Figured.
Left by Polo Shirts Online on Sep 05, 2012 9:00 AM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
white-colored, purple, and also turquoise—the colours associated with individuals serapes
Left by ralph lauren racing polo on Oct 15, 2012 6:12 PM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
Thanks for this awesome tutorial, it helped me a lot achieve what I was aiming for.
Left by bane on Dec 09, 2012 10:14 PM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
wrapping the underlying use of generated Request and Response classes.
Sure about that?
Left by stylish polo on Mar 22, 2013 2:24 AM

# re: Using the WCF SQL Adapter in .NET: Executing SQL Statements
Requesting Gravatar...
Students can use now paraphrasing tool online free for thier projects or papers. This is the best services you can have ever.
Left by norim on Dec 02, 2015 2:26 PM

Your comment:
 (will show your gravatar)


Copyright © Elton Stoneman | Powered by: GeeksWithBlogs.net