Click here to Skip to main content
Click here to Skip to main content

Automate query execution by using xml and helper class

, 26 Jun 2006
Rate this:
Please Sign up or sign in to vote.
The generalized class has static functions that return dataset and datareader objects , It accepts string(name of xml tag that stores the query) and value to be supplied in case query accepts parameters
<!-- Article Starts - DO NOT ADD HTML/BODY START TAGS-->

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

 

 

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

ashishinfra

United States United States
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140821.2 | Last Updated 26 Jun 2006
Article Copyright 2006 by ashishinfra
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid