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 query and executes it.
I once got a requirement in ASP.NET(C#) in which I have multiple
GridViews with editable textboxes in each row of it. When the user enters a valid amount in each one of them and hits the save button, all the valid things are to be 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 (
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 biased variable declarations inside.. " ).
Using the procedure:
The main tag is the <DataSet> tag, and the <tabledetails> provide 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:
Insert, the syntax is:
where the column list is provided by (
ColA;ColB) and 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.