WebServices using ODBC

So for the past month I have been working hard on pulling data from a odbc driver/connection. That was easy. Then I was tasked to pass a parameter to the blessed thing and clean up the XML transaction. Ok Here is how it works:

So earlier I posted how to get all data from a web method and then filter it with another web method.
Oh Tedious thy name is vain. Huh?
Moving forward. There is three examples of a web service that uses XML doc type as a string to allow **Key Word Here**
PARAMETERS. Parameters as in passing a int and or string into a string…
Mmm might need to rewrite this to sound cooler. Pay attention to the code will you…?
The first example pulls all the data from the source given a select statement and coverts it handily to xml.
Watch!
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Xml.Schema;
using System.Xml;

[WebService(Namespace = "http://localhost")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public classYOURDSNService : System.Web.Services.WebService
{
public YOURDSNService ()
{
}
[WebMethod]
public XmlDocument GetAllYourTable()
{
XmlDocument doc = new XmlDocument();
DataSet YourTableDataSet = new DataSet("YourTable");
using (OdbcConnection conn = new OdbcConnection("Dsn=YOURDSN;uid=Hello;pwd=Moto"))
{
OdbcDataAdapter adapter = new OdbcDataAdapter("Select * from PUB.YourTable WHERE Brand ='Hello_kitty' AND Shipped = 0", conn);
adapter.Fill(YourTableDataSet, "YourTable");
}
doc.LoadXml(YourTableDataSet.GetXml());
return doc;
}

That was interesting but not very Filtered.

Now for the More interesting Part.(Remember This is C# and Kids do try this at home.)

[WebMethod]
public XmlDocument GetStringbyStringName(string x)
{
XmlDocument doc = new XmlDocument();
DataSetYourTableDataSet = new DataSet("YourTable");
using (OdbcConnection conn = new OdbcConnection("Dsn=YourDSN;uid=Hello;pwd=moto"))
{
OdbcDataAdapter adapter1 = new OdbcDataAdapter("Select * from PUB.YourTable Where fieldname = '" + x + "'", conn);
adapter1.Fill(YourTableDataSet, "YourTable");
}
doc.LoadXml(YourTableDataSet.GetXml());
return doc;
}
[WebMethod]
public XmlDocument Getfieldfromstring(string y)
{
XmlDocument doc = new XmlDocument();
DataSet YourTableDataSet = new DataSet("YourTable");
using (OdbcConnection conn = new OdbcConnection("Dsn=YOURDSN;uid=hello;pwd=moto"))
{
OdbcDataAdapter adapter1 = new OdbcDataAdapter("Select * from PUB.YourTable Where field = '" + y + "'", conn);
adapter1.Fill(YourTableDataSet, "YourTable");
}
doc.LoadXml(YourTableDataSet.GetXml());
return doc;
}
}

Wowie wow! That is it…. Ah the fun and love of it.

And if you want to speed up your slooow webservice take a look at what you can do to your webmethod to make it kinda fast.
[System.Web.Services.WebMethod(EnableSession = false, CacheDuration =30, BufferResponse = true)]

More turorials and love later as we play more and more with Web Services. This was brought to you by uh me.

This entry was posted in ASP.NET, Interesting, SQL/Database, Web Development, Web Services. Bookmark the permalink.

Leave a Reply