What is the fastest way to GET a list of records from Universe DB file using C#.NET?

Question

I want to perform a SELECT query which would make a hit to the Universe DB once only and get the list of records quickly and efficiently which would then be added to a List<T>.

I have tried to use U2DataAdapter as well as U2DataReader but not sure which one is more efficient.

Below are two different approaches used for executing a SELECT query:

Approach 1:

// Connection
U2ConnectionStringBuilder csb = new U2ConnectionStringBuilder();            
csb.Server = "server";            
csb.Database = "db";              
csb.UserID = "user";            
csb.Password = "pwd";          
csb.ServerType = "UNIVERSE";            
csb.AccessMode = "Native";           
csb.RpcServiceType = "uvcs";
U2Connection con = new U2Connection(csb.ToString());
con.Open();                
U2Command cmd = con.CreateCommand();
cmd.CommandText = "Action=Select;File=SOME.FILE;Attributes=COL1,COL2,COL3,COL4;Where=COL2=XYZ";

// Code to get the data
U2DataAdapter da = new U2DataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
DataTable dt = ds.Tables[0];
var list = new List<T>();
// storing result in List<T>. this takes most of the time
foreach(DataRow dr in dt.Rows)
{
  T obj = new T
  {
    col1 = item.ItemArray[0].ToString(),                       
    col2 = item.ItemArray[1].ToString(),                        
    col3 = item.ItemArray[2].ToString(),                        
    col4 = item.ItemArray[3].ToString(),                        
  };                   
  list.Add(obj);                   
}

Approach 2:

// Connection
U2ConnectionStringBuilder csb = new U2ConnectionStringBuilder();            
csb.Server = "server";            
csb.Database = "db";              
csb.UserID = "user";            
csb.Password = "pwd";          
csb.ServerType = "UNIVERSE";
U2Connection con = new U2Connection(csb.ToString());
con.Open();                
U2Command cmd = con.CreateCommand();
cmd.CommandText = "SELECT COL1,COL2,COL3,COL4 FROM SOME.FILE WHERE COL2= 'XYZ'";

// Code to get the data
var dr= cmd.ExecuteReader();
if (dr.HasRows)               
{                    
  while (dr.Read())                    
  {                        
    string col1 = dr.GetString(0);                        
    string col2 = dr.GetString(1);                        
    string col3 = dr.GetString(2);                        
    string col4 = dr.GetString(3);

    T obj = new T { col1, col2, col3, col4 };                        
    list.Add(obj);                  
  }                
}

Your feedback is appreciated on how to improve any one of the above mentioned approaches and if there is any better approach than these two then please do share it.

Answer 1

If you haven't already, I would look through the samples provided with the U2 Toolkit for .NET which I would hope you have access to. There is a samples folder in the install directory. This provides a couple of different examples.

As to which works better, it probably depends on what you are trying to accomplish and the state of the data. I have used both Access Modes and they both work depending on the state of your dictionaries. As far as return a List If you are expecting Strings all around, I would say either of these methods would work and it is just a matter of performance testing.

I don't like to so ignore type, so in the case of the above I would want it to return a DataTable or a List or something like that. I think it depends on where you need it to be an object and where you just need to show some some data.

Personally, I have been on a kick where I use the Entity Framework for this so it forces the typing issue upfront and encourages you to be more mindful of dictionary items. This works when your metadata is good or you have enough control over it to make it good. You can then use Linq to query and return a List which you can then abstract. I generally have an interface pattern I use pointing to Get, GetAll>, Upsert>.

First I create a model of my file. Reference the Filename in Table and the Dictionary name for your attributes in the Column properties. You can even use I-Types as shown.

[Table("MY.FILE)]
public class MyFile
{
    [Key]
    [Column("MyAttribute0", Order = 1)]
    public string RecordID { get; set; }
    [Column("MyAttribute1")]
    public decimal RecordData1 { get; set; }
    Column("MyAttribute2")]
    public decimal RecordData2 { get; set; }
    Column("MyAttribute3")]
    public decimal RecordData2 { get; set; }
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    Column("MYITYPE1")]
    public decimal MyIType1{ get; set; }
}

Then in the dbContext for the database make sure you reference this model. You will have set this up with a connection string according the examples.

 public DbSet<MyFile> MyFiles { get; set; }

Then when you what to get your data you would something like this.

 this.context.MyFiles.Where(f => f.RecordData2 == "Foo").ToList()

Good Luck.