How to execute a custom sql statement in LLBLGen 3.1 (self servicing)?

Question

Using LLBLGen 3.1 (Self Servicing) on SQL Server, how would one execute custom SQL, such as:

• delete from UserPreference
• select * from UserPreference (into a datatable, for example)

Answer 1

While it is true that you can access the low level data readers, etc.. I think it kind of defeats the purpose of using the ORM. If you just want to fill a datatable from a collection (with or without filtering), you can use the static method GetMultiAsDataTable (which you can pass a predicate expression to if you want to do filtering). If you want to replace more complex SQL (very useful for reporting), check out the dynamic lists capabilities:

http://www.llblgen.com/documentation/4.0/LLBLGen%20Pro%20RTF/hh_start.htm

The QuerySpec is an even nicer way to specify a dynamic query and project it:

http://www.llblgen.com/documentation/4.0/LLBLGen%20Pro%20RTF/hh_start.htm

Answer 2

Just noticed this question hadn't been answered. With Self Servicing, you'll probably use the TypedListDAO class.

See: Generated code - Fetching DataReaders and projections, SelfServicing

The TypedListDAO class has what you need to do SQL against your database, and it can automatically do projections onto custom classes for you if you need that (see the article).

But basically, (from memory, so might need some slight adjustments), here's what your code might look like:

        // inside the DaoClasses namespace of your generated project
        TypedListDAO dao = new TypedListDAO(); 

        // do it yourself, and use your project's connection string
        string connectionString = CommonDaoBase.ActualConnectionString;
        using (var conn = new SqlConnection(connectionString)) { }

        // use a DbConnection directly
        DbConnection connection = dao.CreateConnection();
        // or
        connection = dao.DetermineConnectionToUse(null);
        DbCommand cmd = connection.CreateCommand();
        cmd.CommandText = "SELECT * FROM UserPreferences";
        cmd.CommandType = CommandType.Text;
        var reader = cmd.ExecuteReader(CommandBehavior.Default);
        while (reader.Read()){}
        reader.Close();

        // use a datareader 
        IRetrievalQuery query = new RetrievalQuery(
             new SqlCommand("SELECT * FROM UserPreferences")); 
             // or new RetrievalQuery(cmd); 
             // where you create the cmd using the dao connection

        IDataReader reader = dao.GetAsDataReader(null, query,    
             CommandBehavior.CloseConnection);
        while (reader.Read()){}
        reader.Close();

        // use a datatable - try something like this 
        // (BUT honestly, you might want to look at the custom projection 
        // into custom classes capability, or the data reader, instead of this)
        DataTable dt = new DataTable();
        dao.GetMultiAsDataTable(new EntityFields(0) /* can't be null, i don't think */, 
              dt, query, null);

        // other methods
        dao.ExecuteScalarQuery(query, null);
        ActionQuery actionQuery = new ActionQuery(new SqlCommand("INSERT ..."));
        dao.ExecuteActionQuery(actionQuery, null);

OR, use a micro-orm to do your sql, and just use the connection from the TypedListDAO class above Some Light-weight micro-orms, like: Dapper (1 cs file), PetaPoco, Massive, etc...