New Posts New Posts RSS Feed: Typeless PassthruEsqlQuery?
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Typeless PassthruEsqlQuery?

 Post Reply Post Reply
Author
chuckc View Drop Down
Groupie
Groupie


Joined: 27-Feb-2010
Posts: 54
Post Options Post Options   Quote chuckc Quote  Post ReplyReply Direct Link To This Post Topic: Typeless PassthruEsqlQuery?
    Posted: 15-Mar-2011 at 7:49pm
I am implementing an audit history using "shadow" tables - i.e. separate tables that mirror a primary table but with additional tracking columns for modifying user, datetime of change, etc.  I would like to avoid creating and maintaining classes for these shadow tables, since they will be identical to the primary table except for an additional column or two.  With that in mind, how can I use DevForce to work with these shadow tables?  

Is it possible to somehow return a dataset using PassthruEsqlQuery instead of a typed entity collection?  I could then use that dataset to populate new primary entities with values from the shadow tables.

Might PassthruEsqlQuerySurrogate be of use?  It looks intriguing, but I can find no documentation on it.

Recommendations? 

Thanks!

Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715
Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post Posted: 16-Mar-2011 at 4:30pm
Hi chuckc;

Unfortunately, PassthruEsqlQuery requires a typed entity and PassthruEsqlQuerySurrogate is an internal class to help with the serialization so it won't be useful in this case.

The best suggestion I can give at this time, is to use a combination of DevForce POCOs with raw ADO.NET within your query and save logic.

Hope this helps.
Back to Top
chuckc View Drop Down
Groupie
Groupie


Joined: 27-Feb-2010
Posts: 54
Post Options Post Options   Quote chuckc Quote  Post ReplyReply Direct Link To This Post Posted: 16-Mar-2011 at 6:54pm
Dang.

Any suggestions or examples on how best to approach mixing ADO.Net with DevForce?  How to deal with connectionstrings?  Is there any ADO.Net helper stuff baked into DevForce?

Thanks!
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715
Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post Posted: 17-Mar-2011 at 3:43pm
Hi chuckc;

Unfortunately, there is currently no ADO.Net helper in DevForce and we don't have any examples on our code samples either. I have opened a feature request to add more documentation and code samples regarding this.

Fortunately, to do raw ADO.Net with DevForce is pretty simple and below is a code snippet showing you how to do it.

    public void UsingRawAdoWithDF() {
      string dsKeyName = "NorthwindIBEntityManager";
      
      //Extract EdmKey
      EdmKey edmKey = GetEdmKey(dsKeyName);
      
      //Create raw ADO.Net Db connection using EdmKey
      System.Data.IDbConnection aConnection = CreateDbConnection(edmKey);

      //Execute some Sql commands
      ExecuteSqlCommand(aConnection);
    }

    private EdmKey GetEdmKey(string dsKeyName) {
      var mgr = new NorthwindIBEntityManager();
      EdmKey testEdmKey = EdmKey.LocateEdmKey(dsKeyName);
      var dataSourceKey = mgr.DataSourceResolver.GetDataSourceKey(dsKeyName);

      //If this is done on the client, it will return a ClientEdmKey
      //If this is done on the server, it will return an EdmKey
      //Ideally, you want to do this on the server
      ClientEdmKey clientEdmKey = (ClientEdmKey)dataSourceKey;
      EdmKey edmKey = new EdmKey(clientEdmKey);

      return edmKey;
    }

    private System.Data.Common.DbConnection CreateDbConnection(EdmKey edmKey) {
      var providerName = edmKey.DbProviderName;
      var connectionString = edmKey.DbConnectionString;
      var providerFactory = System.Data.Common.DbProviderFactories.GetFactory(providerName);
      System.Data.Common.DbConnection connection = providerFactory.CreateConnection();
      connection.ConnectionString = connectionString;
      return connection;
    }

    private void ExecuteSqlCommand(System.Data.IDbConnection aConnection) {
      const String sqlSelect = "select UserName from dbo.[User] where FirstName = 'Admin'";
      const String sqlUpdate = "update dbo.[User] set UserName={0} where FirstName = 'Admin'";
      aConnection.Open();
      using (aConnection) {
        System.Data.IDbCommand aCommand = CreateDbCommand(aConnection);
        aCommand.CommandText = sqlSelect;
        System.Data.IDataReader aDataReader = aCommand.ExecuteReader();
        if (!aDataReader.Read()) {
          throw new IdeaBladeException("Unable to locate record");
        }
        Object tmp = aDataReader.GetValue(0);
        aDataReader.Close();

        //Execute sqlUpdate
        aCommand.CommandText = String.Format(sqlUpdate, "'adminxxx'");
        int numOfRowsAffected = aCommand.ExecuteNonQuery();
      }
    }

    private System.Data.Common.DbCommand CreateDbCommand(System.Data.IDbConnection connection) {
      if (connection.State != System.Data.ConnectionState.Open) connection.Open();
      System.Data.IDbCommand command = connection.CreateCommand();
      try {
        return (System.Data.Common.DbCommand)command;
      } catch {
        throw new IdeaBladeException("Unable to cast a DbCommand object from an IDbCommand for current connection");
      }
    }
Back to Top
chuckc View Drop Down
Groupie
Groupie


Joined: 27-Feb-2010
Posts: 54
Post Options Post Options   Quote chuckc Quote  Post ReplyReply Direct Link To This Post Posted: 18-Mar-2011 at 12:17pm

Thanks - that should be very helpful!

Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down