Skytec

warning: Creating default object from empty value in /home/pulsar/webroot/htdocs/modules/taxonomy/taxonomy.pages.inc on line 33.

Using native SQL in nHibernate

Today I ran into a situation in a nHibernate based project where I needed to execute a very specialized query. Using HQL was rather not practical since I had to query tables which were not "directly" mapped to entities.

So, if you need execute queries behind nHibernate's back and map the results to a Bean / Object not specified in the mappings, You might come up with a solution like this:

  1. string someComplexQuery = @"select ... from ...";
  2.  
  3. IQuery sqlQuery =
  4. sessionFactory.GetCurrentSession().CreateSQLQuery(someComplexQuery).
  5. AddScalar("Id", NHibernateUtil.Int32).
  6. AddScalar("Firstname", NHibernateUtil.String).
  7. AddScalar("Lastname", NHibernateUtil.String).SetResultTransformer(
  8. Transformers.AliasToBean(typeof(Person)));
  9.  
  10. IList<Person> people = sqlQuery.List<Person>();
  11. foreach (Person person in people)
  12. {
  13. // ...
  14. }

For a good measure, here is the Person class:
  1. class Person
  2. {
  3. private long id;
  4. private string firstname;
  5. private string lastname;
  6.  
  7. public long Id
  8. {
  9. get { return id; }
  10. set { id = value; }
  11. }
  12.  
  13. public string Firstname
  14. {
  15. get { return firstname; }
  16. set { firstname = value; }
  17. }
  18.  
  19. public string Lastname
  20. {
  21. get { return lastname; }
  22. set { lastname = value; }
  23. }
  24. }

Works like a breeze, /me likes!

Using Spring.NET's PropertyPlaceholderConfigurer

I found the documentation on this topic rather confusing, so here is my cheat-sheet:

Choice 1:

If you store the Configuration-Keys in the Web.config:

File Web.config / App.config

  1. <configuration>
  2.  
  3. <configSections>
  4. <section name="MyConfigSection"
  5. type="System.Configuration.NameValueFileSectionHandler, System,Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
  6. </configSection>
  7.  
  8. <MyConfigSection>
  9. <add key="ParameterName" value="ParameterValue"/>
  10. </MyConfigSection>
  11.  
  12. </configuration>

File spring-whatever.xml

  1. <objects ...>
  2.  
  3. <object type="Spring.Objects.Factory.Config.PropertyPlaceholderConfigurer, Spring.Core">
  4. <property name="ConfigSections" value="MyConfigSection"/>
  5. </object>
  6.  
  7. <object id="SomePOJO" type="SomeType">
  8. <property name="Foo" value="${ParameterName}"/>
  9. </object>
  10.  
  11. </objects>

If you use more than one config section in the App.config / Web.config file, you will need to register all the sections in the Spring's PropertyPlaceholderConfigurer object by adding them to its ConfigSection property. The values should be separated by a comma.

Choice 2:

Don't use web.config at all, define everything in in spring's object xml only:

File: spring-whatever.xml

  1. <objects ...>
  2.  
  3. <object id="appPropertyConfigurer"
  4. type="Spring.Objects.Factory.Config.PropertyPlaceholderConfigurer, Spring.Core">
  5. <property name="MyConfigSection">
  6. <name-values>
  7. <add key="Foo" value="ParameterValue"/>
  8. </name-values>
  9. </property>
  10. </object>
  11.  
  12. <object id="SomePOJO" type="SomeType">
  13. <property name="Foo" value="${ParameterName}"/>
  14. </object>
  15.  
  16. </objects>

WCF, XStream, Serialization Issues and Debugging

Maybe you are familiar with the XStream Serializer which was initially available for Java and ported later on to .NET. If you aren't - its worth checking out. Its a very simple XML serializer able to handle complex objects with cyclic dependencies. It has some minor issues though. If you are aware of them, you most probably won't shoot yourself into the foot. ... Yeah, most probably ... I did!

Lets take one step back. I've been consuming a method (lets call it GetFoo()) exposed over WCF Service by an ASP.NET Client. The method would look like this:

  1. public SomeComplexObject GetFoo(long id)
  2. {
  3. String xml = DAO.GetSerializedObjectById(id);
  4. return (SomeComplexObject) XStream.fromXML(xml);
  5. }

SomeComplexObject would be a class properly annotated with the DataContract and DataMember Attributes. Calling this Method over the WCF Service would bail out a generic error message on the ASP client, basically telling you that the remote side of the connection has quit abruptly.

I must admit that I am still new to the WCF / ASP.NET development so I had no clue where to look for the exception generated by the WCF layer. After hitting Google with this question, I have figured out how to use Microsoft Service Trace Viewer to investigate the SOAP Messages passed around- and more importantly the Exception which occured somewhere deep inside the WCF stack.

The Exception type has been "System.Runtime.Serialization.InvalidDataContractException" and basically said that ".xsdyn~SomeComplexObject" has not been annotated with the DataContract Attribute and thus could not be used in a WCF Call.

.xsdyn? It turns out that XStream will create a fake assembly and create a dynamic instance of the "SomeComplexObject" in case one would not provide a default constructor for the class to be deserialized. Gee! Thats dumb! I mean, not only the way of handing the lack of an default constructor, but also my own dumbness making this mistake.

To sum it up, if you are going to use XStream for .NET, pay attention to include a) the default constructor in each class you wish to deserialize, b) use protected and not private modifiers to class variables if you are going to derive from that class. Otherwise you will be missing the fields in the serialized output. It does not matter if the properties would be public or not.

Oh, and by the way... Setting up the WCF logging was fun. The trace log generated is pretty useful if you need to inspect the SOAP messages / headers and the call history. But if you just need to check the Exception generated by the WCF Stack - take a look into the Windows EventViewer - way more convenient!

How to access Excel files via ADO.NET

The C# snippet below will dump ALL sheets of a given Excel file (c:\test.xls) onto the console. It took me a while to figure out how to access sheets within a Excel file which names I don't know - so here it is:

  1. String connectionString =
  2. "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\test.xls;Extended Properties=\"Excel 8.0;HDR=YES;\"";
  3. DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
  4. try
  5. {
  6. using (OleDbConnection connection = new OleDbConnection(connectionString))
  7. {
  8. connection.Open();
  9. DataRowCollection rows = connection.GetSchema("Tables").Rows;
  10. foreach (DataRow row in rows)
  11. {
  12. string sheetName = row["TABLE_NAME"] as string;
  13. Console.WriteLine("************************************************************************");
  14. Console.WriteLine("Dumping Sheet: "+sheetName);
  15. Console.WriteLine("************************************************************************");
  16. using (DbCommand command = connection.CreateCommand())
  17. {
  18. string[] restrictions = { null, null, sheetName, null };
  19. DataRowCollection columns = connection.GetSchema("Columns", restrictions).Rows;
  20. string[] columnNames = new string[columns.Count];
  21. for (int i=0; i<columns.Count; i++)
  22. {
  23. columnNames[i] = columns[i]["COLUMN_NAME"] as string;
  24. }
  25. Console.Write("Columns in Sheet: ");
  26. foreach (string s in columnNames)
  27. {
  28. Console.Write(s);
  29. Console.Write(" ");
  30. }
  31. Console.WriteLine("");
  32.  
  33. command.CommandText = "SELECT * from [" + sheetName + "]";
  34. using (DbDataReader dr = command.ExecuteReader())
  35. {
  36. while (dr.Read())
  37. {
  38. foreach (string columnName in columnNames)
  39. {
  40. Object fieldValue = dr[columnName];
  41. Console.WriteLine("'" + columnName + "' : " + "'" + fieldValue + "'");
  42. }
  43. Console.WriteLine("------------------------------------------------------------------------");
  44. }
  45. }
  46. }
  47. }
  48. }
  49. }
  50. catch (Exception e)
  51. {
  52. Console.Write(e.StackTrace);
  53. }

To be honest - I did not expect it to be that easy. I guess I spent too much time developing Java applications.

Syndicate content