Introduction
Xml File used to store the queries and stored procedures .The Xml file below has two main tags SqlQuery and StoredProc
You can associate the query with a tagname stored desired query in in the Query Attribute if query has any parameters then Make that many no of child nodes and Specify the ParameterName to Parameter attribute and sqldatatype to DataType Attribute of childtag
(Note this xml file was created for queries using provider sqlclient and targetting database sqlserver 2000, if provider used is oledb the queries can use "?" instead of explictly specifying parameter name followed by @)
<pre>
<?
xml version="1.0" encoding="utf-8"?>
<
Queries>
<SqlQuery>
<LoginClient Query="select chrid,chrname from yourtable where UserName=@UserName and pwd =@Password">
<LoginClientParam Parameter="@UserName" DataType="SqlDbType.VarChar">
</LoginClientParam>
<LoginClientParam Parameter="@Password" DataType="SqlDbType.VarChar">
</LoginClientParam>
</LoginClient>
<LoginSubscriber Query="select chrid,chrname from yourtable where UserName=@UserName and pwd =@Password">
<LoginSubscriberParam Parameter="@UserName" DataType="SqlDbType.VarChar">
</LoginSubscriberParam>
<LoginSubscriberParam Parameter="@Password" DataType="SqlDbType.VarChar">
</LoginSubscriberParam>
</LoginSubscriber>
<LoginRM Query="Select usr_id from yourtable where Usr_Name = @UserName and pwd =@Password">
<LoginRMParam Parameter="@UserName" DataType="SqlDbType.VarChar">
</LoginRMParam>
<LoginRMParam Parameter="@Password" DataType="SqlDbType.VarChar">
</LoginRMParam>
</LoginRM>
<LoginBackUser Query="abc">
</LoginBackUser>
<TradeRegStockNameData Query="select distinct chrDesc,chrDesc as chrDesc2 from yourtable where bitdelete=0">
</TradeRegStockNameData>
</SqlQuery>
<StoredProc>
<TradeReg Query="YourStoredProcedure">
<TradeRegParam Parameter="@mSub_Ac_id" DataType="SqlDbType.Char" DataLen="10">
</TradeRegParam>
<TradeRegParam Parameter="@mClient_id" DataType="SqlDbType.Char" DataLen="10">
</TradeRegParam>
<TradeRegParam Parameter="@mSettType" DataType="SqlDbType.Char" DataLen="255">
</TradeRegParam>
<TradeRegParam Parameter="@mFromDate" DataType="SqlDbType.Char" DataLen="10">
</TradeRegParam>
<TradeRegParam Parameter="@mToDate" DataType="SqlDbType.Char" DataLen="10">
</TradeRegParam>
<TradeRegParam Parameter="@mSettFromDate" DataType="SqlDbType.Char" DataLen="10">
</TradeRegParam>
<TradeRegParam Parameter="@mSettToDate" DataType="SqlDbType.Char" DataLen="10">
</TradeRegParam>
<TradeRegParam Parameter="@mStockName" DataType="SqlDbType.Char" DataLen="255">
</TradeRegParam>
<TradeRegParam Parameter="@mTradeType" DataType="SqlDbType.Char" DataLen="1">
</TradeRegParam>
<TradeRegParam Parameter="@mBuySell" DataType="SqlDbType.Char" DataLen="1">
</TradeRegParam>
<TradeRegParam Parameter="@mUser_Ac_id" DataType="SqlDbType.Char" DataLen="10">
</TradeRegParam>
<TradeRegParam Parameter="@UsrType" DataType="SqlDbType.Int">
</TradeRegParam>
</TradeReg>
</StoredProc>
</
Queries>
</pre>
The supporting class that uses the xml file
<pre>
using
System;
using
System.Data;
using
System.Data.SqlClient;
using
System.Configuration;
using System.Collections;
using
System.Drawing;
using
System.Xml;
using
System.Web;
namespace
kmukWebFII
{
/// <summary>
/// Summary description for FetchDataSqlClient.
/// </summary>
public class FetchDataSqlClient
{
public
static string GetFastMultipleParamTrial(string xmlTagName,params string[] val)
{
XmlDocument _xdoc =
new XmlDocument();
_xdoc.Load( HttpContext.Current.Server.MapPath("KmukQueries.xml"));
XmlNodeList xtemp = _xdoc.GetElementsByTagName(xmlTagName);
SqlConnection dbconn =
new SqlConnection(ConfigurationSettings.AppSettings["Kmukconn2"]);
//string sqlCommand = xtemp[0].Attributes["Query"].Value;
SqlCommand dbcmd =
new SqlCommand();
dbcmd.Connection = dbconn;
dbcmd.CommandText = xtemp[0].Attributes["Query"].Value;
//This piece of code would be executed in case of parameters exists in the code
if(val[0]!="null")
{
int icount = 0;
foreach(XmlNode xnodtemp in xtemp[0].ChildNodes )
{
string paramname = xnodtemp.Attributes["Parameter"].Value;
string paramtype = xnodtemp.Attributes["DataType"].Value;
dbcmd.Parameters.Add(paramname,paramtype);
dbcmd.Parameters[paramname].Value = val[icount];
icount++;
}
}
SqlDataReader rset=
null;
bool errorocc = true;
try
{
dbconn.Open();
rset = dbcmd.ExecuteReader();
}
catch(Exception e)
{
e.ToString();
errorocc =
false;
}
finally
{
dbconn.Close();
}
// catch(SqlException se)
// {
// //if(rset!="")
// // dbconn.Close();
//
// if(NUM_TRIES<MAX_TRIES)
// {
// rset = dbcmd.ExecuteScalar().ToString();
// NUM_TRIES+=1;
// }
// else
// throw se;
//
// }
if(errorocc!=false)
return rset;
else
return "-1";
}
</pre>
The sample example of how to use it in the code
Lets say we use this static class to return a datareader object
//code behind of the aspx page
datareader drtemp;
drtemp = FetchDataSqlClient.GetFastSingleParamTrial("LoginClient",dbconn,txtusernamec.Text,txtpasswordc.Text);
signature of above function ("string xmlTagName",oledbconnection object,parameters )
this is my first contribution towards code project ..
Hope to comeback with some improvement in the code with better functionality very soon
