Introduction
I would like to share something in SQL which might be useful in some circumstances. The SQL procedure which I have uploaded depends on an input parameter (XML), converts it to a query, and executes it.
Background
I once got a requirement in ASP.NET (C#) in which I had multiple GridViews with editable textboxes in each row. When the user enters a valid amount in each one of them and hits the Save button, all the valid things are saved in the database.
Now the usual solution for this (there would be a lot of solutions, but being a novice I speak in their perspective) would be to generate queries (Insert/Updates) and execute them using ADO.NET. But I thought of doing something else to resolve this in a better fashion as I am a fan of StoredProcedures rather than generating inline queries. So I developed this XmlToQuery converter which is uploaded with this post.
Using the Code
This is a simple idea of just constructing XML syntaxes (type: String in C# .NET) and passing it as a parameter to the procedure which will parse through it and form the necessary queries and execute them in one shot. Now this logic is customizable to our needs. Initially, I had built it for the purpose of constructing Update queries, but later I extended it to handle Inserts as well (so you would be seeing update based variable declarations inside..
).
Using the procedure:
EXECUTE Proc_xmltoqueryconverter
'<DataSet><tableDetails>TableName,(ColA;ColB),-1,(ValueA;ValueB),-1,INSERT</tableDetails>
<tableDetails>TableName,PKFieldName,PKFieldValue,
TobeUpdatedFieldName,TobeUpdatedFieldValue,UPDATE</tableDetails>
</DataSet>'
The main tag is the <DataSet> tag, and <tabledetails> provides the info. For individual queries, the last word in the tag represents whether it is an Insert/Update.
Update: For an Update, the syntax is:
<tableDetails>TableName,PKFieldName,PKFieldValue,TobeUpdatedFieldName,
TobeUpdatedFieldValue,UPDATE</tableDetails>
Insert: For Insert, the syntax is:
<tableDetails>TableName,(ColA;ColB),-1,(ValueA;ValueB),-1,INSERT</tableDetails>
where the column list is provided by (ColA;ColB) and the value list is provided by (ValueA;ValueB), both of which can be multiple but equal in numbers.
Like this, we can have multiple <tabledetails> in this (pardon my namings) and we can execute these queries with atomicity and have the advantages of a stored procedure.
