OhRM

Every time I start writing a new application it makes me think how terribly tedious is to write all this CRUD logic running through the tiers and how extremely difficult and expensive (yet magical) is to write the custom ORM solution. Damn it.

I decided to keep things simple this time and use what Fowler calls Transaction script. Still I want some DTO's, at least not to pass the data into methods as separate variables.

So this will be my solution:

• first I will generate Xml schemas from tables
• second, I will use XSD.EXE to generate classes from Xml schemas.

Here is the implementation of this great idea:

List<string> tables = new List<string>();

string connectionString = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;

using (var connection = new SqlConnection(connectionString))

{

        var command = connection.CreateCommand();

        command.CommandType = CommandType.Text;

        command.CommandText =

                "SELECT [name] FROM dbo.sysobjects WHERE TYPE = N'U' AND name NOT LIKE N'sys%'";

        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

        {

                if (reader.HasRows)

                {

                        while (reader.Read())

                        {

                                tables.Add(reader[0].ToString());

                        }

                }

        }

        reader.Close();

        foreach (var tableName in tables)

        {

                command.CommandText = String.Format(

                        @"SELECT * FROM {0} FOR XML AUTO, ELEMENTS, XMLSCHEMA('{0}Schema')", tableName);

                XmlReader xmlReader = command.ExecuteXmlReader();

                var stream = File.Create(Path.ChangeExtension(tableName, "xsd"));

                using (StreamWriter sw = new StreamWriter(stream))

                {

                        while (xmlReader.Read())

                        {

                                sw.WriteLine(xmlReader.ReadOuterXml());

                        }

                }

                xmlReader.Close();

        }

}

StringBuilder sb = new StringBuilder();

foreach (var tableName in tables)

{

        sb.AppendLine(String.Format("xsd.exe {0}.xsd /c SqlTypes.xsd", tableName));

}

File.WriteAllText("GenerateClasses.bat", sb.ToString());