Select top(n) clause

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for universal data access
Post Reply
mlagasio
Posts: 43
Joined: Mon 14 Mar 2011 13:42

Select top(n) clause

Post by mlagasio » Tue 22 Mar 2011 10:07

I need to connect my vc# application with SqlServer and Oracle using dotConnect Universal.

I think possible use UniMacro to implement

Select top(n) clause

for SqlServer and Oracle

In this the unique chance?

Thank, bye
Marco

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 23 Mar 2011 16:11

You can use the code like this:

Code: Select all

    static void Main(){
        string sqlConnStr = "Provider=SQL Server;Data Source=MSSQL2008RTM;user id=sa;Initial Catalog=Test;";
        string oracleConnStr = "Provider=Oracle;Data Source=orcl1120;user id=scott;password=tiger;";
        Top5(sqlConnStr);
        Top5(oracleConnStr);
        Console.ReadLine();
    }
    static void Top5(string connStr) {
        using (UniConnection conn = new UniConnection(connStr)) {
            conn.Macros.Add("topSql", "top(5)", "SQL Server");
            conn.Macros.Add("topOracle", "where rownum <= 5", "Oracle");
            conn.Open();
            UniCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select {topSql} * from dept {topOracle}";
            UniDataReader reader = cmd.ExecuteReader();
            Console.WriteLine("connected to {0}", conn.DataSource);
            while (reader.Read()) {
                Console.WriteLine(reader.GetInt32(reader.GetOrdinal("DeptNo")) + ", " + reader.GetString(reader.GetOrdinal("DName")));
            }
        }
    }
The "topSql" macros is active only for the SQL Server connection, "topOracle" - only for the Oracle one.
The corresponding scripts for the Dept table:
  • SQL Server -
    CREATE TABLE [Dept](
    [DEPTNO] [int] Primary Key,
    [DNAME] [varchar](14),
    [LOC] [varchar](13)
    );
  • Oracle -
    CREATE TABLE DEPT (
    DEPTNO NUMBER(9) Primary Key,
    DNAME VARCHAR2(14),
    LOC VARCHAR2(13)
    );

Post Reply