|
xxx
C# - Web Service Sample
by Charles Carroll
Here is the Web Service Test Page
filename=/experiments/webservicebasics/whatever.asmx
<%@ assembly src="searcher_better_lib.cs"%>
<%@ webService Class="lib_db.Helper" %>
Here is the library the Web is built on
filename=/experiments/webservicebasics/searcher_better_lib.cs
using System;
using System.Data;
using System.Data.OleDb;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Reflection;
using System.Web.Services;
[assembly:AssemblyVersionAttribute("0.8.1.*")]
namespace lib_db
{
public class Helper: WebService
{
public static string ConnDB()
{
return("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" + System.Web.HttpContext.Current.Server.MapPath("/experiments/data/biblio.mdb") + ";");
} // end ConnDB
public static string DBScalar(string strConn, string strSQL)
{
OleDbConnection Conn=null;
string strTempReturn="-error-";
try
{
Conn=new OleDbConnection(strConn);
OleDbCommand Cmd=new OleDbCommand(strSQL,Conn);
Conn.Open();
strTempReturn=Cmd.ExecuteScalar().ToString();
} // end try
catch (Exception exc1)
{
Page pageCurrent=new Page();
pageCurrent=(Page) System.Web.HttpContext.Current.Handler;
pageCurrent.Controls.Add(
new LiteralControl("Error: " + exc1.ToString() + "<br>"));
} // end catch
finally
{
if (Conn != null)
{
if (Conn.State==System.Data.ConnectionState.Open) Conn.Close();
} // end if
} // end finally
return(strTempReturn);
} // end DBScalar
[WebMethod] public DataSet GetCities()
{
string strSQL="select distinct city from publishers";
string strConn=ConnDB();
DataSet ds1=new DataSet();
DataTable dt1=new DataTable();
dt1=DataTableMaker(strConn,strSQL);
ds1.Tables.Add(dt1);
return(ds1);
} // end GetCities
[WebMethod] public DataSet GetStates()
{
string strSQL="select distinct state from publishers";
string strConn=ConnDB();
DataSet ds1=new DataSet();
DataTable dt1=new DataTable();
dt1=DataTableMaker(strConn,strSQL);
ds1.Tables.Add(dt1);
return(ds1);
} // end GetStates
[WebMethod] public DataSet GetZip()
{
string strSQL="select distinct zip from publishers";
string strConn=ConnDB();
DataSet ds1=new DataSet();
DataTable dt1=new DataTable();
dt1=DataTableMaker(strConn,strSQL);
ds1.Tables.Add(dt1);
return(ds1);
} // end GetZip
[WebMethod] public DataSet GetPublishersByState(string strState)
{
string strSQL="select * from publishers where state='" + strState + "'";
string strConn=ConnDB();
DataSet ds1=new DataSet();
DataTable dt1=new DataTable();
dt1=DataTableMaker(strConn,strSQL);
ds1.Tables.Add(dt1);
return(ds1);
} // end GetPublishersByState
[WebMethod] public DataSet GetPublishersByCity(string strCity)
{
string strSQL="select * from publishers where city='" + strCity + "'";
string strConn=ConnDB();
DataSet ds1=new DataSet();
DataTable dt1=new DataTable();
dt1=DataTableMaker(strConn,strSQL);
ds1.Tables.Add(dt1);
return(ds1);
} // end GetPublishersByCity
public static DataTable DataTableMaker(string strConn,string strSQL)
{
DataTable dt1=new DataTable("temp");
OleDbConnection conn=new OleDbConnection(strConn);
OleDbDataAdapter Adapter= new OleDbDataAdapter(strSQL, conn);
Adapter.Fill(dt1);
return(dt1);
} // end DataTableMaker
/// <summary>
/// System.Web.UI.WebControls can be filled from a OLEDB connection string and a sql statement
/// </summary>
/// <param name="pConn">Connection string</param>
/// <param name="pSQL">SQL string</param>
/// <param name="pObj">Web Control to Fill</param>
public static void FillWebControl(string pConn,string pSQL,Object pObj)
{
OleDbConnection Conn=null;
OleDbDataReader Rdr=null;
try
{
Conn=new OleDbConnection(pConn);
OleDbCommand Cmd=new OleDbCommand(pSQL,Conn);
Conn.Open();
Rdr=Cmd.ExecuteReader();
// -or- Rdr=Cmd.ExecuteReader(system.Data.CommandBehavior.CloseConnection)
switch(pObj.GetType().ToString())
{
case "System.Web.UI.WebControls.DropDownList":
((DropDownList) pObj).DataSource=Rdr;
((DropDownList) pObj).DataBind();
break;
case "System.Web.UI.WebControls.DataGrid":
((DataGrid) pObj).DataSource=Rdr;
((DataGrid) pObj).DataBind();
break;
default:
/*
If we encounter a control that is unknown
at least barf up its type to the page
also never forget the syntax trick if (x==y)?100:1000
when you want to show off
*/
Page pageCurrent=new Page();
pageCurrent=(Page) System.Web.HttpContext.Current.Handler;
pageCurrent.Controls.Add(
new LiteralControl("Unknown Type Encountered: " +
pObj.GetType().ToString() + "<br>"));
break;
} // end switch
} // end try
catch (Exception exc1)
{
Page pageCurrent=new Page();
pageCurrent=(Page) System.Web.HttpContext.Current.Handler;
pageCurrent.Controls.Add(
new LiteralControl("Error: " + exc1.ToString() + "<br>"));
} // end catch
finally
{
if (Rdr != null)
{
if (!Rdr.IsClosed) Rdr.Close();
}
if (Conn != null)
{
if (Conn.State==System.Data.ConnectionState.Open) Conn.Close();
}
} // end finally
} // end FillWebControl
} // end class
} // end namespace
Here is the Web Page also using that library:
filename=/experiments/webservicebasics/searcher_better.aspx
<%@ trace="true"%>
<%@ Assembly src="searcher_better_lib.cs" %>
<script language="C#" runat="server">
string strConn;
protected void Page_Load(Object S, EventArgs E)
{
strConn="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=";
strConn += Server.MapPath(@"/experiments/data/biblio.mdb") + ";" ;
searchagain.Visible=false;
if (!Page.IsPostBack)
{
string strSQL;
strSQL="select distinct city from publishers";
lib_db.Helper.FillWebControl(strConn,strSQL,cy);
strSQL="select distinct state from publishers";
lib_db.Helper.FillWebControl(strConn,strSQL,st);
strSQL="select distinct zip from publishers";
lib_db.Helper.FillWebControl(strConn,strSQL,zp);
strSQL="select count(*) as citycount From (select distinct City from publishers)";
litCityCount.Text=lib_db.Helper.DBScalar(strConn,strSQL);
strSQL="select count(*) as statecount From (select distinct State from publishers)";
litStateCount.Text=lib_db.Helper.DBScalar(strConn,strSQL);
strSQL="select count(*) as zipcount From (select distinct Zip from publishers)";
litZipCount.Text=lib_db.Helper.DBScalar(strConn,strSQL);
}
}
protected void trace_search()
{
Trace.Write("city",cy.SelectedItem.Text);
Trace.Write("state",st.SelectedItem.Text);
Trace.Write("zip",zp.SelectedItem.Text);
// Trace.Write("cityinc",cyinclude.Checked.ToString());
// Trace.Write("stateinc",stinclude.Checked.ToString());
// Trace.Write("zipinc",zpinclude.Checked.ToString());
}
protected void search_show()
{
searchcriteria.Visible=false;
search.Visible=false;
searchagain.Visible=true;
searchresults.Visible=true;
}
string search_conditions(string strFieldName,CheckBox chk1,
DropDownList drop1,ref string strParm)
{
string strTempReturn;
if (chk1.Checked)
{
strTempReturn=" " + strFieldName + "='" + drop1.SelectedItem.Text + "' ";
strParm=" AND ";
return(strTempReturn);
}
return("");
}
protected void search_click(object S, EventArgs E)
{
trace_search();
search_show();
string strSQL;
string strPrefix="";
strSQL="select * from publishers where ";
if (!(cyinclude.Checked || stinclude.Checked || zpinclude.Checked))
{
message.Text="You did not choose any categories -- we can't Search";
searchresults.Visible=false;
return;
}
else
{
message.Text="";
}
strSQL += search_conditions("city",cyinclude,cy,ref strPrefix);
strSQL += search_conditions("state",stinclude,st,ref strPrefix);
strSQL += search_conditions("zip",zpinclude,zp,ref strPrefix);
Trace.Write("strSQL",strSQL);
lib_db.Helper.FillWebControl(strConn,strSQL,searchresults);
}
public void searchagain_click(object S, EventArgs E)
{
searchcriteria.Visible=true;
searchresults.Visible=false;
searchagain.Visible=false;
search.Visible=true;
}
</script>
<html><head>
<title>Searcher</title>
</head>
<body bgcolor="#FFFFFF">
<form runat="server">
<asp:literal id="message" runat="server"/>
<asp:Table id="searchcriteria" GridLines="both" BorderWidth="1px" runat="server">
<asp:TableRow>
<asp:TableCell>City (<asp:Literal id="litCityCount" runat="server"/>)</asp:TableCell>
<asp:TableCell>
<ASP:DropDownList id="cy" datatextfield="city" runat="server"/>
</asp:TableCell>
<asp:TableCell>
<ASP:checkbox id="cyinclude" text="include in Search?" runat="server"/>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>State(<asp:Literal id="litStateCount" runat="server"/>)</asp:TableCell>
<asp:TableCell>
<ASP:DropDownList id="st" datatextfield="state" runat="server"/>
</asp:TableCell>
<asp:TableCell>
<ASP:checkbox id="stinclude" text="include in Search?" runat="server"/>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>Zip(<asp:Literal id="litZipCount" runat="server"/>)</asp:TableCell>
<asp:TableCell>
<ASP:DropDownList id="zp" datatextfield="zip" runat="server"/>
</asp:TableCell>
<asp:TableCell>
<ASP:checkbox id="zpinclude" text="include in Search?" runat="server"/>
</asp:TableCell>
</asp:TableRow>
</asp:Table>
<asp:button id="search" text="search for data"
onclick="search_click" runat="server"/><br>
<asp:button id="searchagain" text="search for data again"
onclick="searchagain_click" runat="server"/>
<ASP:DataGrid id="searchresults" runat="server"
Width="100%"
BackColor="white"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
Headerstyle-BackColor="lightblue"
Headerstyle-Font-Size="10pt"
Headerstyle-Font-Style="bold"
MaintainState="false"
/>
</form>
</body></html>
|