Designing Object Oriented database calls rather than string based

Question

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

Answer 1

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.)

Answer 2

Personally I do not recommend using too much xml at the database; that is the least scalable part of your system (you can't scale it "out" cheaply), so rather than spending all its time processing xml, I would just use data in, data out - simple regular TSQL parameters and grids.

---

However!

That approach of building xml is pretty buggy actually - the values should be xml-escaped. But to answer the question - I would use LINQ-to-XML (XElement) or XmlSerializer. Probably the latter, i.e.

public class Request {
    [XmlAttribute] public string Type {get;set;}
    [XmlAttribute] public string CRUD {get;set;} // although I'd prefer an enum

    public RequestUser Users {get;set;}
}
public class RequestUser {
    public string UserName {get;set;}
    public string Password {get;set;} // please use salted hash instead
}

then:

var req = new Request { Type = "", CRUD = "",
          Users = new RequestUser { UserName = username, Password = password } };
var ser = new XmlSerializer(typeof(Request));
StringWriter sw = new StringWriter();
ser.Serialize(sw, req);
string xml = sw.ToString();

The main advantage I'd see here with XmlSerializer (over LINQ-to-XML) is that you can also use this to read back xml in that format, via Deserialize. However, LINQ-to-XML will also work nicely here:

var req = new XElement("Request",
             new XAttribute("Type", ""),
             new XAttribute("CRUD", ""),
             new XElement("Users", 
                new XElement("UserName", username),
                new XElement("Password", password)
             )
          );
var xml = req.ToString();