How to get the SQL

Jan 25, 2010 at 11:04 PM

Is there a way to get the SQL from the DBQuery, i.e.

            DBQuery sel = DBQuery.Select()
                                  .Field("CustomerID").Count("OrderID").As("orderCount")
                                  .From("ORDERS").As("O")
                                  .GroupBy("CustomerID")
                                  .OrderBy("orderCount", Order.Descending).OrderBy("CustomerID")
                                  .WhereField("orderDate", Compare.GreaterThan, since) //'since' is the parameter above
                                  .And("shippeddate", Compare.Is, DBConst.Null().Value.ToString());

I couldn't find a ToSQL() method.

Jan 28, 2010 at 9:56 PM

I don't have it front of me, but as I recall, there is SqlStatementBuilder (or something similar) class that can be used.

Coordinator
Feb 18, 2010 at 6:07 PM

jason - yes you are right, there is a SqlStatementBuilder,

However the whole point is not to see the SQL! Just execute it against the database.

I assume you're trying to find out exactly what is executing - debug compilation should output string to console before execution.

sorry for the delay in responding

R

Mar 28, 2010 at 3:33 PM

When I found this project I was also asking myself the same question as Dewey did above.

Regarding the above statement "the whole point is not to see the SQL" I do not think it is very consistent with what is emphasized in the home page, i.e. statements at the top saying that "It's all about the readability of code!" and "this project is there to help to create simple, readable code".

I do indeed like a library that helps to create readability, but not to be forced to use the same library for doing the execution also, but would prefer to see these two kind of features separated and possible to easy use separately.
People may want to use one library for creating the SQL statements and then sending the SQL for executions to other libraries.
For example, you might want to use Spring.NET AdoTemplate methods ( http://www.springframework.net/docs/1.3.0/reference/html/ado.html#ado-adotemplate-intro ) such as the method "AdoTemplate.QueryWithResultSetExtractor" ( http://www.springframework.net/docs/1.3.0/api/net-2.0/html/topic14017.html )

Regarding the above request method 'ToSQL' I experimented with it to make an extension method for the class DBQuery, and below is a code example which Dewey and others might want to use or extend:
(though of course, the below code can be considered as fragile and may stop working in future versions, since the library does not seem to have been designed for this general purpose yet, i.e. to separate the SQL creation from the SQL execution)

    public static class DbQueryExtensions {
        public static string ToSql(this DBQuery dbQuery) {
            using (DBStatementBuilder dbStatementBuilder = DbDatabase.CreateStatementBuilder())
            {
                dbQuery.BuildStatement(dbStatementBuilder);
                return dbStatementBuilder.ToString().Trim();
            }
        }

        private static DBDatabase _dbDatabase;
        private static DBDatabase DbDatabase {
            get {
                if (_dbDatabase == null) {
                    throw new ApplicationException("You have not set the database provider!"); // must invoke SetConnectionStringAndProviderName
                }
                return _dbDatabase;
            }
        }

        public static void SetConnectionStringAndProviderName(string connectionString, string providerName) {
            // e.g. providerName = "System.Data.OleDb"
            _dbDatabase = DBDatabase.Create(connectionString, providerName);
        }
    }



And the following code example illustrates how to use the above extension method "ToSql" with SQL code based on the example at the home page, i.e. you just invoke the ToSql method to the DBQuery.

            DbQueryExtensions.SetConnectionStringAndProviderName("x", "System.Data.OleDb"); // this works OK for offline creation of SQL, i.e. no actual data connection needed for this provider

            
	    DateTime ordersAfter = new DateTime(1990, 01, 01);
            string selectSql = 
                DBQuery.Select()
                .Field("CustomerID")
                .Count("OrderID")
                .From("Orders")
                .GroupBy("CustomerID")
                .OrderBy("CustomerID")
                .WhereField("OrderDate", Compare.GreaterThan, DBConst.DateTime(ordersAfter))
                .ToSql();


            DBParam cname = DBParam.ParamWithValue("test 2");
            DBParam cdesc = DBParam.ParamWithValue("this is the new description");
            string updateSql = 
                DBQuery.Update("Categories")
                .Set("CategoryName", cname)
                .AndSet("Description", cdesc)
                .WhereField("CategoryID", Compare.Equals, DBConst.Const(1))
                .ToSql();

/ Tomas

Mar 11, 2011 at 2:18 AM

I think return a DBCommand object is perfect, becuase we only use it to generate sql, but we will execute the command through other framework, such as Enterprise library.