XML - Query Engine

This is the xml file which consist the Select Query in xml format,


<query>
<select> <column><field>Uid <alias>UserId <column><field>Pwd <alias>Password <column> <field>City <alias>City</select>
<from>
<table>Users <tbody></tbody></table></from>
<where>
<condetion>
<field>Uid</field>
<operator>!=</operator>

<parameterbind type="NVarchar">@userid</parameterbind>
</condetion>
<boolean operator="AND"></boolean>
<condetion>
<field>City</field>
<operator>Like</operator>
<value>'S%'</value>
</condetion>
<boolean>
</where>
<sort>
<condetion>
<field>Uid</field>
<order>Asc</order>
</condetion>
</sort>
</query>"


TO Genrate Query @ runtime,
create following Classes.


namespace XMLEngine
{
class Query // This Class Object would return by Query Engine
{
private string _queryString;
private Dictionary<string,> _paramters;
List<sqlparameter> ReturnParams = new List<sqlparameter>();


public Query()
{
_queryString = "";
_paramters = new Dictionary<string,>();
}
public string QueryString
{
get { return _queryString; }
set { _queryString = value; }
}

public Dictionary<string,> Paramters
{
get { return _paramters; }
set { _paramters = value; }
}

public void BindParameters(string key,string paramValue)
{
SqlParameter sp = _paramters[key];
sp.Value = paramValue;
_paramters[key] = sp;
}

public bool HasParametrs
{
get
{
return (_paramters.Count > 0);
}
}

public Int32 CountParametrs
{
get {return _paramters.Count; }
}

public SqlParameter[] GetParameters
{
get
{
foreach (SqlParameter sp in _paramters.Values)
{
ReturnParams.Add(sp);
}
return ReturnParams.ToArray();
}
}
}
}



And Then create Engine which Create Query and Parameters List,


namespace XMLEngine
{
class QueryEngine
{
static StringBuilder sbQuery;
static XmlDocument doc;

//List<sqlparameter> params_;
Dictionary<string,sqlparameter> params_;


public QueryReader()
{
sbQuery = new StringBuilder();
doc = new XmlDocument();
params_ = new Dictionary<string,>();
}

public Query GetQuery(string XmlDocPath)
{
Query qry = new Query();
doc.Load(XmlDocPath);

WriteSelectClause();
WriteFromClause();
WriteWhereClause();
WriteOrderByClause();

qry.Paramters = params_;
qry.QueryString=sbQuery.ToString();
//Console.WriteLine(sbQuery.ToString());
//Console.ReadKey();
return qry;
}

public void GetQueryOld(string XmlDocPath)
{
XmlDocument doc = new XmlDocument();
doc.Load(XmlDocPath);

XmlNodeList Views = doc.GetElementsByTagName("View");

foreach (XmlNode Columns in Views[0])
{
XmlElement xl = (XmlElement)Columns;
//XmlNodeList ColmnList = xl.GetElementsByTagName("Column");
Console.WriteLine(xl.GetElementsByTagName("Column")[0].InnerText + "." + xl.GetElementsByTagName("Column")[1].InnerText + "." + xl.GetElementsByTagName("Column")[2].InnerText);
//foreach (XmlNode Cols in Columns.ChildNodes)
//{
// XmlElement Desc = (XmlElement)Cols;
// Console.WriteLine(Desc.InnerText);
//}
}
Console.ReadKey();
}

public void WriteSelectClause()
{
sbQuery.Append("Select ");
XmlNodeList XSelectNodeList = doc.GetElementsByTagName(Constants.NODE_NAME_VIEW);
//XmlNode XSelectNode = doc.SelectSingleNode("/Select");
foreach (XmlNode xnColName in XSelectNodeList[0])
{
XmlElement Column = (XmlElement)xnColName;
sbQuery.Append(Column.GetElementsByTagName(Constants.NODE_NAME_FIELD)[0].InnerText);
if (Column.GetElementsByTagName(Constants.NODE_NAME_FIELD)[0].InnerText != "*")
{
sbQuery.Append(" As " + Column.GetElementsByTagName(Constants.NODE_NAME_ALIAS)[0].InnerText);
}
sbQuery.Append(",");
}
sbQuery.Remove(sbQuery.Length - 1, 1);
}

public void WriteFromClause()
{
sbQuery.Append(" From ");

XmlNodeList XFromNodeList = doc.GetElementsByTagName(Constants.NODE_NAME_FROM);

foreach (XmlNode XFromNode in XFromNodeList)
{
XmlElement From = (XmlElement)XFromNode;
sbQuery.Append(From.GetElementsByTagName(Constants.NODE_NAME_TABLE)[0].InnerText);
}
sbQuery.Append(" ");
}

public void WriteWhereClause()
{
sbQuery.Append(" Where ");
XmlNodeList XWhereNodeList = doc.GetElementsByTagName(Constants.NODE_NAME_FILTER);
foreach (XmlNode xnCondetion in XWhereNodeList[0])
{
XmlElement Condetion = (XmlElement)xnCondetion;

if (Condetion.Name ==Constants.NODE_NAME_BOOLEAN)
{
sbQuery.Append(" "+Condetion.GetAttribute(Constants.NODE_NAME_OPERATOR)+" ");
}
else
{
foreach (XmlNode xnCondTags in Condetion.ChildNodes)
{
if (xnCondTags.Name == Constants.NODE_NAME_FIELD)
{
sbQuery.Append(" " + Condetion.GetElementsByTagName(Constants.NODE_NAME_FIELD)[0].InnerText);
}
else if (xnCondTags.Name == Constants.NODE_NAME_OPERATOR)
{
sbQuery.Append(" " + Condetion.GetElementsByTagName(Constants.NODE_NAME_OPERATOR)[0].InnerText);
}
else if (xnCondTags.Name == Constants.NODE_NAME_VALUE)
{
sbQuery.Append(" " + Condetion.GetElementsByTagName(Constants.NODE_NAME_VALUE)[0].InnerText);
}
else if (xnCondTags.Name == Constants.NODE_NAME_PARAMETERBIND)
{
XmlElement xParam=(XmlElement)xnCondTags;
sbQuery.Append(" " + Condetion.GetElementsByTagName(Constants.NODE_NAME_PARAMETERBIND)[0].InnerText);
SqlParameter sp = new SqlParameter(Condetion.GetElementsByTagName(Constants.NODE_NAME_PARAMETERBIND)[0].InnerText, SqlDbType.NVarChar);
sp.ResetSqlDbType();
//sp.UdtTypeName = xParam.GetAttribute("type");
this.params_.Add(Condetion.GetElementsByTagName(Constants.NODE_NAME_FIELD)[0].InnerText, sp);
}
}
}



}

}

public void WriteOrderByClause()
{
sbQuery.Append(" Order By ");
XmlNodeList XSortNodeList = doc.GetElementsByTagName(Constants.NODE_NAME_SORT);

foreach (XmlNode xnCondetion in XSortNodeList[0])
{
XmlElement Condetion = (XmlElement)xnCondetion;
//sbQuery.Append(Condetion.InnerText+"-----");
sbQuery.Append(" " + Condetion.GetElementsByTagName(Constants.NODE_NAME_FIELD)[0].InnerText);
sbQuery.Append(" " + Condetion.GetElementsByTagName(Constants.NODE_NAME_ORDER)[0].InnerText);
sbQuery.Append(",");
}
sbQuery.Remove(sbQuery.Length - 1, 1);
}

}
}

And All used Constant Value would fetch from This Static Class.

namespace XMLEngine
{
public static class Constants
{
public const string
NODE_NAME_DOCUMENT_ELEMENT = "Query",
NODE_NAME_VIEW = "Select",
NODE_NAME_FROM = "From",
NODE_NAME_FILTER = "Where",
NODE_NAME_PARAMETERBIND = "ParameterBind",
NODE_NAME_SORT = "Sort",
NODE_NAME_CONDITION = "Condition",
NODE_NAME_TABLE = "Table",
NODE_NAME_FIELD = "Field",
NODE_NAME_ALIAS = "Alias",
NODE_NAME_COLUMN = "Column",
NODE_NAME_BOOLEAN = "Boolean",
NODE_NAME_OPERATOR = "Operator",
NODE_NAME_GROUPING = "Grouping",
NODE_NAME_GROUP = "Group",
NODE_NAME_ASCENDING = "Asc",
NODE_NAME_DESCENDING = "Desc",
NODE_NAME_VALUE = "Value",
NODE_NAME_AND = "And",
NODE_NAME_OR = "Or",
NODE_NAME_NOT = "Not",
NODE_NAME_ORDER = "Order";

public const string
DEFAULT_NAME_ROW_INDEX_FIELD = "rowIndex";

public const int
DEFAULT_PAGE_SIZE = 10;
}
}

genrate dynamic query from dataset

we can gerate dynamic query from datset by following function,

but remember the passing dataset should have same table name as Database,
we can give the Dataset table name at fill time,eg. adapter.fill(Ds,"Users");

public string GetCommandText(DataSet Ds1)
{
bool esc = true;
string tabname = Ds1.Tables[0].TableName;
StringBuilder QueryText = new StringBuilder("Select ");

foreach (DataColumn dc in Ds1.Tables[0].Columns)
{
if (esc)
{
QueryText.Append(dc.ColumnName);
}
else
{
QueryText.Append("," + dc.ColumnName);
}

esc = false;
}
QueryText.Append(" from ");
QueryText.Append(tabname);

string Pk = Ds1.Tables[0].PrimaryKey[0].ColumnName;
StringBuilder sbWhere = new StringBuilder();
sbWhere.Append(" Where ");
sbWhere.Append(Pk);
sbWhere.Append(" In (");

foreach (DataRow dr in Ds1.Tables[0].Rows)
{
sbWhere.Append(dr[Pk]);
sbWhere.Append(",");
}
sbWhere.Remove(sbWhere.Length - 1, 1);
sbWhere.Append(")");

QueryText.Append(sbWhere.ToString());
Console.WriteLine(QueryText.ToString());
return QueryText.ToString();
}

Basics of Merging Datasets

Hi, Friends ,
This is my first blog. so please ignore mistakes if i have done in this article.

So When i got this task mean merging the datasets and Update Database , i got nervous but now i m feeling its too easy.

Now Explaning with follwing code,

For Create Dataset 1

SqlConnection con = new SqlConnection(connString);
SqlDataAdapter sda = new SqlDataAdapter("Select * from Users", con);
DataSet Ds = new DataSet();
SqlCommandBuilder sbd = new SqlCommandBuilder(sda);
sbd.ConflictOption = ConflictOption.OverwriteChanges;

sda.AcceptChangesDuringFill = false; //This will maintain Row State-Added

sda.AcceptChangesDuringUpdate = false; //This will maintain Row State-Added

sda.ContinueUpdateOnError = true; // This will contineu update source

sda.FillLoadOption = LoadOption.OverwriteChanges;
//this is must to get Row State Unchanged

sda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// To get Primary key & others constraint


sda.Fill(Ds); //finally fill the Dataset1


For Create Dataset 2

SqlConnection con1 = new SqlConnection(connString2)
SqlDataAdapter sda1 = new SqlDataAdapter("Select * from Users", con1);
DataSet Ds1 = new DataSet();
sda1.AcceptChangesDuringFill = false;
sda1.AcceptChangesDuringUpdate = false;

sda1.FillLoadOption = LoadOption.Upsert;//This will fill rows with State - Added

SqlCommandBuilder sbd1 = new SqlCommandBuilder(sda1);
sbd1.ConflictOption = ConflictOption.OverwriteChanges;
sda1.ContinueUpdateOnError = true;
sda1.MissingSchemaAction = MissingSchemaAction.AddWithKey;

sda1.Fill(Ds1); //finally fill the Dataset 2

//Ds1.AcceptChanges(); Do not Write this Coz this will change row state

Ds.Merge(Ds1, false, MissingSchemaAction.AddWithKey);
sda.Update(Ds);

In this Code no where is code for Row state but whole code depend on Row state.
So be carefull on Row State