Print Page | Close Window

Typeless PassthruEsqlQuery?

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=2560
Printed Date: 24-Jan-2026 at 1:13am


Topic: Typeless PassthruEsqlQuery?
Posted By: chuckc
Subject: Typeless PassthruEsqlQuery?
Date 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!




Replies:
Posted By: DenisK
Date 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 http://drc.ideablade.com/xwiki/bin/view/Documentation/POCOs - DevForce POCOs with raw ADO.NET within your query and save logic.

Hope this helps.


Posted By: chuckc
Date 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!


Posted By: DenisK
Date 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");
      }
    }


Posted By: chuckc
Date Posted: 18-Mar-2011 at 12:17pm

Thanks - that should be very helpful!




Print Page | Close Window