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.
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 (
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:
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:
Insert, the syntax is:
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.