Search code examples
c#xmloopdata-access-layerstrong-typing

Designing Object Oriented database calls rather than string based

related

Design Strongly typed object from XML

Currently we run stored procedures based on "SQL XML" where in we send xml string as input to stored procedures and get the response from stored procedures as xml. For a simple demonstration of user logging into application here is how it is done

<Request Type="" CRUD="C/R/U/D">`
<Users>
<UserName></UserName>
<Password></Password>
</Users>
</Request>

don't mind about the response from database because that we can change to anything we desired to. It is this construction of xml that we find too intimidating. Below is code we follow

StringBuilder _sbXml = new StringBuilder();
_sbXml.AppendLine("<Request Type='' CRUD=''>");
_sbXml.AppendLine("<Users>");
_sbXml.AppendLine("<UserName>"+ usernameVariable +"</UserName>");
_sbXml.AppendLine("<Password>"+ passwordVariable +"</Password>");
_sbXml.AppendLine("</Users>");
DataTier.BeginRequest(_sbXml.ToString());

we tried abstracting things into methods but again we never solved the problem that we wanted to, just hid them somewhere.

So we concluded that strong typing is necessary for each request to avoid any typo's,undesired behavior, avoid hand coded xml and maintainable

. Hence the question

  • How can i abstract away this form of building xml's

  • XSD tool can generate class modelling xml data(Related post), is the class that is generated using the tool adaptive for long run?

  • Abstracting string xml to Typed classes is advisable? Has anyone had success doing something that i am trying to do now?Which did you feel comfortable with?

More

Above is just a single database call, we got same for all the database calls. We are into big mess maintaining for sure.

Extra

  1. Using C# 2.0

  2. .NET 2.0

  3. SQL Server 2005

  4. Visual Studio 2005

Solution

  • There are two problems here:

    • You're passing parameters to the stored procedures using XML, when stored procedures can take parameters in a perfectly normal way without using an intermediate layer
    • You're building your XML by hand. Please don't do that - .NET has plenty of good XML APIs, even though using .NET 2.0 means you can't use LINQ to XML.

    So, I would suggest:

    • Get rid of the XML layer if you possibly can. I realize this may not be feasible.
    • Build the XML using APIs such as XmlDocument etc
    • You don't want the XML-building code littering your code, certainly - whether you need several different types for this is unclear from your post; it will depend on how much your requests vary.

    (It's not really clear what you meant by "we concluded that a class would solve things" or "I saw the related post and generated class" so it's possible that I'm missing the point. If so, please edit your question to clarify.)