locked
SQL Dapper UNION extension method. RRS feed

  • Question

  • User842257015 posted

    Hi All,

    I have using Dapper for fetching data from SQL server, recently I have a new requirement to build UNION query to get the result.

    I have seen the Dapper package there is no UNION extension method to support. 

    I would strongly appreciate if some have any idea about to resolve this.

    Many thanks,

    Friday, November 20, 2020 7:04 AM

Answers

  • User1535942433 posted

    Hi jayakumarvinayagam,

    Accroding to your description,as far as I think,you could use QueryMultiple().Through the QueryMultiple method, the data collection of each SQL statement in the query is returned.

    Just like this demo:

    string strSql = "SELECT * FROM Student AS A;SELECT * FROM Teacher AS A";
                StringBuilder sbStudent = new StringBuilder();
                StringBuilder sbTeacher = new StringBuilder();
                using (var conn = GetCon())
                {
                    conn.Open();
                    var grid = conn.QueryMultiple(strSql);
                    var students = grid.Read<Student>();
                    var teachers = grid.Read<Teacher>();
                    foreach (var item in students)
                    {
                        sbStudent.Append($"StudentID={item.StudentID} Name={item.Name} Age={item.Age} Gender={item.Gender}n");
                    }
                    foreach (var item in teachers)
                    {
                        sbTeacher.Append($"TeacherID={item.TeacherID} Name={item.Name}n");
                    }
                    return sbStudent.ToString() + sbTeacher.ToString();
                }

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 23, 2020 2:54 AM

All replies

  • User-821857111 posted

    I'm not sure I see the issue? Union is part of the SQL you pass to Dapper. Dapper doesn't need to do anything special. Perhaps you can show some code to illustrate your issue?

    Friday, November 20, 2020 7:15 AM
  • User842257015 posted

    Thanks, @Mike, here my approach

                   var customer = "dbo.Customer";
                    var order = "dbo.Order";
                    int orderId = 10;
                    int custoemrId = 10001;
                    var customerBuilder = new SqlBuilder();
                    customerBuilder.Select("Name");
                    customerBuilder.Select("Address");
                    customerBuilder.Where($"id = {orderId}");                
                    var customerSQL= customerBuilder.AddTemplate($"SELECT DISTINCT /**select**/ from " +
                        $"{customer}" +
                        $" /**innerjoin**/" +
                        $" /**where**/ ");
                                    
                    var orderBuilder = new SqlBuilder();
                    orderBuilder.Select("Name");
                    orderBuilder.Select("Date");
                    orderBuilder.Where($"id = {custoemrId}");
                    var orderSQl = orderBuilder.AddTemplate($"SELECT DISTINCT /**select**/ from " +
                        $"{order}" +
                        $" /**innerjoin**/" +
                        $" /**where**/ ");
    
                    var sql = $"{customerSQL.RawSql} UNION {orderSQl.RawSql}";
    

     I just append two SQL template and execute the query. 

    I wonder something /**####**/ style approach to do UNION.

    Friday, November 20, 2020 7:55 AM
  • User1535942433 posted

    Hi jayakumarvinayagam,

    Accroding to your description,as far as I think,you could use QueryMultiple().Through the QueryMultiple method, the data collection of each SQL statement in the query is returned.

    Just like this demo:

    string strSql = "SELECT * FROM Student AS A;SELECT * FROM Teacher AS A";
                StringBuilder sbStudent = new StringBuilder();
                StringBuilder sbTeacher = new StringBuilder();
                using (var conn = GetCon())
                {
                    conn.Open();
                    var grid = conn.QueryMultiple(strSql);
                    var students = grid.Read<Student>();
                    var teachers = grid.Read<Teacher>();
                    foreach (var item in students)
                    {
                        sbStudent.Append($"StudentID={item.StudentID} Name={item.Name} Age={item.Age} Gender={item.Gender}n");
                    }
                    foreach (var item in teachers)
                    {
                        sbTeacher.Append($"TeacherID={item.TeacherID} Name={item.Name}n");
                    }
                    return sbStudent.ToString() + sbTeacher.ToString();
                }

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 23, 2020 2:54 AM