C# Database Basics (2012)
Chapter 5. Data in a Web Service
Writing a web service doesn’t have to be a difficult or frustrating experience. I know many people, including myself, who struggled with writing one the first time. The challenge is more around understanding how different data types are returned and then taking care of the data access. I have written a simple example that will give you a great start toward writing one that will meet your needs.
The first thing that I want to cover is something called an SQL Injection Attack. When you are accepting input from users on the Web (really anywhere, but particularly on the Web), you need to be sure that you are taking precautions to avoid an SQL Injection Attack. In basic terms, if you are building an SQL Query String dynamically based on user input, a user can enter in text, end the first statement, then enter in malicious code (drop a table, overwrite data, etc.), and then put in a comment marker that would make SQL Server ignore the rest of the statement.
So, while you may be tempted to take user input in a variable—which for now we’ll call x—and concatenate it within your string, you should avoid doing that at all times. Here’s what it might look like in that example:
string x = (input from the user)
string sqlstr = "Select * from tbl_Test Where Product = '" + x + "'"
If you did that and someone targeted your site, all your data could be deleted. There are two steps that you can take to keep from being attacked in this manner. The first thing you can do is create a parameterized query. The second thing you need to do is validate the data in that parameter. Working with SQL Server parameters in C# is relatively straightforward. The part that you should keep your eye on is the data validation. Here’s what the SQL string would look like if you changed it to take a parameter:
string sqlstr = "Select * from tbl_Test Where Product = @Product"
Now that we’ve covered that very briefly, I encourage you to read up on this type of attack before you take a site live, because these attacks impact websites all the time. Also, it should be noted that this logic applies to any website, not just a web service. You’ll also see that a lot of people suggest using a stored procedure instead of a query to access data. While I agree with that, it is difficult to write an application that isn’t using some level of dynamic code for data access. Just keep a lookout for where you can use a stored procedure when you are building applications and when you can’t make sure that you are using parameters. In addition, you should use strongly typed parameters so a malicious user or just a regular user in error can’t put unexpected parameters into your query.
Writing a Web Service
Up through .NET Framework 3.5, a Web Service was a type of project that you could create. In .NET Framework 4.0, that option no longer exists. However, if you need to build one in .NET 4.0, you can simply create an Empty .NET Web Application and then add a Web Service as a Project Item.
Let’s start by writing a simple web service, and then we can go through getting it data connected. To do this, open up Visual Studio (either the Full Version, or if you are using the Express editions, you would use Visual Web Developer) and go to File→New→Project, choose Web on the left, and then choose ASP.NET Empty Web Application. You will see a screen that looks similar to the one in Figure 5-1.
Figure 5-1. Setting up an empty web application
In this example, I called the project Chapter5_WebService. From here, go into Solution Explorer and you will see that there aren’t many items in the lists. This is because we’ve started with an empty web application. To add in a web service, go to Project→Add New Item and you will get a screen like you see in Figure 5-2. Choose Web Service and give it a name. I’ve called mine Chapter5_WS.
Figure 5-2. Adding a web service
In the top section of the code that opens, you will need to add references to be able to access data in SQL Server. In addition, you will be using a connection string set in the Web.config file, so you will need to add a reference to be able to access that. You should have the following lines of code at the top of your web service:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Configuration;
From here, you can change the namespace for your web service and add comments. For this example, the only change I made was to make the namespace chapter5_ws. The next piece of code can be added above the HelloWorld example supplied by Visual Studio, or you can replace that example with this code:
[WebMethod]
public System.Collections.ObjectModel.Collection<string> listTest(string thisList)
{
System.Collections.ObjectModel.Collection<string> holder = new System.Collections.ObjectModel.Collection<string>();
switch (thisList)
{
case "First":
holder.Add("one");
holder.Add("two");
holder.Add("three");
return holder;
case "Second":
holder.Add("four");
holder.Add("five");
holder.Add("six");
holder.Add("seven");
return holder;
default:
holder.Add("Invalid");
return holder;
}
}
Breaking down what this web service will do is pretty simple. You are writing a service that will take a string input and return a collection of strings. In this case, if the user passes First as the parameter, the service will return one, two, and three. If the user passes Second as the parameter, the service will return four, five, six, and seven. If the user passes any other parameter, the service will return invalid.
So, take that code and press F5 to launch the web service. The ASP.NET Development server will launch and show you a page with the services available listed as shown in Figure 5-3. Click on listTest and you will see a screen like the one shown in Figure 5-4. In the text box, enter Second and click the Invoke button.
Figure 5-3. Launching a web service
Figure 5-4. Entering parameters to test the service
Your browser will return the following XML:
<?xml version="1.0" encoding="UTF-8"?>
<ArrayOfString xmlns="chapter6_ws" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<string>four</string>
<string>five</string>
<string>six</string>
<string>seven</string>
</ArrayOfString>
This example isn’t very useful on its own, but in a real-world application, you could pass a parameter to a web service and return a list to show on a drop-down list or something along those lines. Again, this one is static, but I’m showing it here so that you can see how a web service is written and called. Now, we need some data.
To accomplish this, go to Solution Explorer and right-click on the project name, then go to Add→Add ASP.NET Folder→App_Data. This will add the App_Data folder, which we will use to put in an SQL Server Database. See Figure 5-5 below.
Figure 5-5. Navigating to App_Data
Next, right-click on the App_Data folder and choose Add→New Item. This will bring up the screen shown in Figure 5-6. Choose SQL Server Database. In this case, I’ve named it WS_Database.
Figure 5-6. Choosing the kind of database
If you choose View→Server Explorer, you will see the SQL Server database that you created. This database’s file will be in the App_Data folder of your application. If you click on the triangle to the left of the database, it will expand the database to show the objects. From there, right-click on Tables and choose Add New Table. You will see a screen like the one shown in Figure 5-7. Add the data fields shown there. Take note that the Product_ID is set to be the Identity Column.
Figure 5-7. Adding a table
Once you have done this, click Save and save the table as tbl_Products. That is the only table that we are going to have in this database. In the example, I added several products, put Fruits or Vegetables as the categories, and put in the names of some fruits and vegetables as the product names. Now that it is saved, right-click on the table name and choose Show Table Data. Enter in a few rows of data. Figure 5-8 shows the rows that I added.
Figure 5-8. Adding some data
The next thing you need to do is add a few lines into your Web.Config file to store the database connection information. Your Web.Config file should look like the code below:
<?xml version="1.0"?>
<!--
For more information on how to configure your ASP.NET application, please visit
http://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<system.web>
<compilation debug="true" targetFramework="4.0" />
</system.web>
<connectionStrings>
<add name="WS_Data"
connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\WS_Database.mdf;User Instance=true"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
The only part that you should have had to add is the part for connectionStrings. This line is saying to use your SQL Server Express database called WS_Database.mdf that is in the App_Data folder. (Using |DataDirectory| lets you reference that folder instead of having to use the physical path.) It is also using Integrated Security. You could also have a database that needs a username and password, but for this example, this will do just fine. Save the changes to the Web.config file and go back to the web service code.
You will be writing a procedure that will return a DataTable of products based on the Category name that a user passes to the procedure. The code will look like the listing below:
[WebMethod]
public DataTable getProducts(string Category)
{
SqlConnection sqlcn = new SqlConnection(ConfigurationManager.ConnectionStrings["WS_Data"].ConnectionString);
DataSet ds = new DataSet();
string sqlstr = "Select Product from tbl_Products where Category = @Category";
SqlDataAdapter ws_sql_adapter = new SqlDataAdapter(sqlstr, sqlcn);
SqlParameter param = ws_sql_adapter.SelectCommand.Parameters.Add("@Category", SqlDbType.Char, 10);
param.Value = Category;
ws_sql_adapter.Fill(ds, "Products");
DataTable dt = ds.Tables["Products"];
return dt;
}
In this code, you are connecting to SQL Server, sending a parameterized select command, and returning records. It sounds easy enough, but it is helpful to break it all down. First, because you have already defined a connection string called WS_Data in the Web.config file and you have set a using reference to System.Configuration, you can reference that connection string in code through the ConfigurationManager object. You could certainly enter in a connection string there, but it is much easier as you add multiple pages in a web application to reference the connection string. Also, if you do it this way and your connection string changes, you can change it in one place and it will be used everywhere.
Next, you are adding a new DataSet object. This should already be familiar to you from the earlier chapters. The connection string that we are using here is selecting the column Product from your table, where Category equals the parameter that is being passed.
In order to create the select command, you need to add an SqlDataAdapter object using the query string that you created and the connection that you created. Once you have done that, you need to add an SqlParameter object to the SqlDataAdapter object. There are several overloads for this object, but we are using the one that passes the name, the data type, and the length. Remember, we are passing this as a strongly typed parameter to avoid getting passed malicious code in a parameter.
Now that you have a parameter object, you set the Value of the parameter equal to the Category variable, which is the parameter that is passed when the web service is called. The final steps are just to use the data adapter to fill the DataSet and then to create a DataTable object and reference it to the table that you just filled in the DataSet. Once this is done, your last line just returns the DataTable to the user. When I first tried to build a web service to return data, I spent a lot of time trying to change the DataTable into a collection, array, XML, etc—basically anything that I thought would get passed to the browser as XML. But the nice part about the web service is that it will do this for you for most objects without you having to do anything extra. All you need to do is return the object that you created and the web service will render it as XML. It is amazing how sometimes you can spend an hour on something and end up with only 12 lines of code that does what you need.
Save your progress so far and press F5 to launch the web service. Once it opens, click getProducts on the web page, enter Fruits in the text box, and click Invoke. You will get the following XML response:
<?xml version="1.0" encoding="UTF-8"?>
<DataTable xmlns="chapter6_ws"><xs:schema xmlns="" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:xs="http://www.w3.org/2001/XMLSchema" id="NewDataSet">
<xs:element msdata:UseCurrentLocale="true" msdata:MainDataTable="Products" msdata:IsDataSet="true" name="NewDataSet">
<xs:complexType>
<xs:choice maxOccurs="unbounded" minOccurs="0">
<xs:element name="Products">
<xs:complexType>
<xs:sequence>
<xs:element name="Product" minOccurs="0" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<NewDataSet xmlns="">
<Products diffgr:id="Products1" msdata:rowOrder="0">
<Product>Apples </Product>
</Products>
<Products diffgr:id="Products2" msdata:rowOrder="1">
<Product>Bananas </Product>
</Products><Products diffgr:id="Products3" msdata:rowOrder="2">
<Product>Melon </Product>
</Products></NewDataSet>
</diffgr:diffgram>
</DataTable>
This returned the data of the products that we put under the category of Fruits. If you close that tab in your browser and try to call it with a category that you don’t have defined, you will notice that it gives you a blank DataTable. This is important, as you will see when you try the next example.
Close out the browser and go back to the code. The next piece of code that you’ll write is going to get an individual category for a product, meaning that if you enter Apples, the system should return the word Fruits. The code to do that follows:
[WebMethod]
public string getCategory(string Product)
{
SqlConnection sqlcn = new SqlConnection(ConfigurationManager.ConnectionStrings["WS_Data"].ConnectionString);
DataSet ds = new DataSet();
string sqlstr = "Select Category from tbl_Products where Product = @Product";
SqlDataAdapter DataCommand = new SqlDataAdapter(sqlstr, sqlcn);
DataCommand.SelectCommand.Parameters.Add("@Product", SqlDbType.Char, 20);
DataCommand.SelectCommand.Parameters[0].Value = Product;
DataCommand.Fill(ds, "Products");
DataTable dt = ds.Tables["Products"];
string category = "No Records";
if (dt.Rows.Count > 0)
{
category = dt.Rows[0].Field<string>(0).ToString();
}
return category;
}
I want to point out a few items for you to notice here. First, we are passing back a single string and not a DataTable. The second thing to notice is that because the product can have more characters in the table than the category (it was setup to have a maximum of 10 characters for the category and 20 characters for the product), you have to state that in the parameter object. The final thing to note is that we need to make sure that the query returns data. If you refer to dt.Rows[0] and there is no data, you will get an error. It is easy enough to check the count of the rows. So, rather than setting additional conditions, you can set the string that you are trying to return to “No Records.” If a record is returned, you will overwrite that variable with the data in the first column of the first row that is returned. If you put a product in more than one category (in this example), this would only return the first record. You could make it return a DataTable, but I wanted to show an example where you would return an individual record. Start the web service and click on getCategory, enter in Apples as the product name, and click Invoke. You will get an XML response like you see below:
<?xml version="1.0" encoding="UTF-8"?>
<string xmlns="chapter6_ws">Fruits </string>
This web service would return “No Records” in place of “Fruits” if you entered a product name that wasn’t in the database. Again, this is a relatively simple procedure, but I think it shows you some of the possibilities that you have and is a good example of how to work with an SQL Server database in a web environment. If you were to try this with your web-hosted site, you would need to make sure that ASP.NET 4.0 was enabled on the site, and you would need to change your connection string, as many web-hosting sites don’t have the SQL Server in the same location. But, your web-hosting site will usually give you the connection string prebuilt, and you can just copy and paste.
There are a lot of APIs out there that you call as a web service. There are many different ways to call them in code. Typically, you would have an http post request and then you would look for the response, which is the XML that is returned. You can also use a SOAP request and response. When you run the web service in your ASP.NET development server and click on an operation, below the text box where you enter in your parameter(s), there are sample requests and responses for each type of request and response.
In a web application that you are building, you can easily call your web service by just creating a reference to it. To try this, go to Project →Add New Item and select Web Form, naming your web form Default.aspx. Enter the following code to make your web page, or you can go to the design view and drag the controls onto the page there:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Chapter6_WebService.Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
Enter the category you would like to see:<br />
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />
<br />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
<br />
The count of the rows returned is:<br />
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
</div>
</form>
</body>
</html>
Then, switch to code view and enter the following:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
namespace Chapter6_WebService
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
Chapter6_WebService.Chapter6_WS wt = new Chapter6_WebService.Chapter6_WS();
DataTable dt = wt.getProducts(this.TextBox1.Text);
this.GridView1.DataSource = dt;
this.GridView1.DataBind();
this.TextBox2.Text = dt.Rows.Count.ToString();
}
}
}
This creates a simple web page that lets you enter in a Category, and when you click the button, it will populate a datagrid with the products. It will also populate a text box with the number of rows returned. Figure 5-9 shows what the webpage looks like when opened.
Figure 5-9. A simple web page for accessing the service
Then enter in the word Vegetables in the top text box and click the button. Once you do that, you’ll see the screen shown in Figure 5-10.
Figure 5-10. A response added to the web page
What is nice about doing this is you don’t need to do anything special to take the response and put it in a DataTable. When the XML is returned, C# recognizes the type as a DataTable and you can bind it right to your datagrid. Also, note that the only addition to the “using” code is using System.Data;—everything else is already there by default.
You could also create a new website and, with your web service project running, go to Website→Add Service Reference, then enter in the address of your web service. In my case, the address bar shows: http://localhost:63741/Chapter6_WS.asmx. Once you enter that in, click Go. It will find that web service and you can expand it to see the operations in the web service. Before you click OK, rename the namespace for the reference to getData and then click OK. If you make the exact same web page as above, you just need to change the backend code to the code below:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using getData;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
getData.Chapter6_WSSoapClient wt = new getData.Chapter6_WSSoapClient();
DataTable dt = wt.getProducts(this.TextBox1.Text);
this.GridView1.DataSource = dt;
this.GridView1.DataBind();
this.TextBox2.Text = dt.Rows.Count.ToString();
}
}
In this case, you are adding a line up top showing using getData;, which is setting a reference to the service that you added. You should notice that the way you create a reference to the service is slightly different in that you are referencing the SoapClient. But, other than those two changes, the rest of the code is identical. Also, please be aware that you have to have both projects running in order for this code to work. Typically, you won’t be referencing your own web services in this manner. But, you will run into situations where you want to consume other web services, and I think it is much easier to add a service reference than it is to write your own SOAP or HTTP POST requests.
The code for the web service used was broken up into multiple pieces, so I’m showing the full code for the web service below. If you have a web service with multiple operations like this one, take notice of how the [WebMethod] line of code needs to appear above each. If you remove that line, there will be no errors in the code; it is just that an operation without [WebMethod] above it won’t show up or be available on the web service.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Configuration;
namespace Chapter6_WebService
{
/// <summary>
/// Summary description for Chapter6_WS
/// </summary>
[WebService(Namespace = "chapter6_ws")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class Chapter6_WS : System.Web.Services.WebService
{
[WebMethod]
public DataTable getProducts(string Category)
{
SqlConnection sqlcn = new SqlConnection(ConfigurationManager.ConnectionStrings["WS_Data"].ConnectionString);
DataSet ds = new DataSet();
string sqlstr = "Select Product from tbl_Products where Category = @Category";
SqlDataAdapter ws_sql_adapter = new SqlDataAdapter(sqlstr, sqlcn);
SqlParameter param = ws_sql_adapter.SelectCommand.Parameters.Add("@Category", SqlDbType.Char, 10);
param.Value = Category;
ws_sql_adapter.Fill(ds, "Products");
DataTable dt = ds.Tables["Products"];
return dt;
}
[WebMethod]
public string getCategory(string Product)
{
SqlConnection sqlcn = new SqlConnection(ConfigurationManager.ConnectionStrings["WS_Data"].ConnectionString);
DataSet ds = new DataSet();
string sqlstr = "Select Category from tbl_Products where Product = @Product";
SqlDataAdapter DataCommand = new SqlDataAdapter(sqlstr, sqlcn);
DataCommand.SelectCommand.Parameters.Add("@Product", SqlDbType.VarChar, 10);
DataCommand.SelectCommand.Parameters[0].Value = Product;
DataCommand.Fill(ds, "Products");
DataTable dt = ds.Tables["Products"];
string category = "No Records";
if (dt.Rows.Count > 0)
{
category = dt.Rows[0].Field<string>(0).ToString();
}
return category;
}
[WebMethod]
public System.Collections.ObjectModel.Collection<string> listTest(string thisList)
{
System.Collections.ObjectModel.Collection<string> holder = new System.Collections.ObjectModel.Collection<string>();
switch (thisList)
{
case "First":
holder.Add("one");
holder.Add("two");
holder.Add("three");
return holder;
case "Second":
holder.Add("four");
holder.Add("five");
holder.Add("six");
holder.Add("seven");
return holder;
default:
holder.Add("Invalid");
return holder;
}
}
}
}
What’s Next
So far, you have had the chance to connect to Access and SQL Server in a desktop environment, and you’ve also connected to SQL Server in a web environment. In the next chapter, you’ll build a simple web interface to edit records in an Access database. There are a lot of great web tutorials out there on using the MVC design pattern for websites, but getting into that depth is beyond the scope of this book. In this next example, we will be building a simple, one-page webforms site just to show how to access and edit data on a web page. We’ll be using the Northwind database.