Help with using EntLib w/ SqlCe

Topics: Logging Application Block
Mar 25, 2010 at 3:04 AM

I am new to EntLib and I am having some issues with using the logging to a SqlCE database. Now I had to modify the logging code to not use stored procedures as SqlCE does not support sproc's.  It's performance is very poor ie. 100 traces in 17 seconds on a fast machine.   Once reason is that it seems that the SqlCeConnection pooling is not being used but it is unclear on how to get the logging to use the SqlCe Connection pooling?  Is there a configuration option if so I cannot find it.  In addition I cannot get it to open the SDF file unless I give an explict path to the file even though it is in the same directory.

Thanks for any help

Craig

Mar 25, 2010 at 7:15 AM

Yes, I believe there is really an issue with regards to SqlCe Connection pooling. Try checking this WorkItem http://www.codeplex.com/entlib/WorkItem/View.aspx?WorkItemId=9005 if it will help you on your current problem.

As for "I cannot get it to open the SDF file unless I give an explict path to the file even though it is in the same directory", I have tried this one and it works for me. Please see my sample code below if it may help you.

Just make sure the SDF file is in the same directory of your executable. HTH

 static void Main(string[] args)
        {
            StringBuilder builder = new StringBuilder();
            SqlCeDatabase db = new SqlCeDatabase(@"Data Source=Northwind.sdf");
               
            string sqlStr = "Select * from Customers";
            DbCommand command = db.GetSqlStringCommand(sqlStr);
            using (IDataReader reader = db.ExecuteReader(command))
            {               
                while (reader.Read())
                {
                    builder.AppendLine(reader[0].ToString());
                }
            }
            Console.WriteLine(builder.ToString());
            Console.ReadLine();

        }

~ginkapitan

 

Mar 25, 2010 at 1:00 PM

Thanks for tip.  I thought since those posts where a few years old that they would have fixed the Connection pooling with SqlCe and EntLib Logging. It seems as if there are not many people using the Logging EntLib with SqlCe.  As is it cannot work anyway as it does not support stored procedures.  It looks like I will have to roll my own logging classes.

The explict path issue was not a code issue rather it was being specified in the App.config file in the EntLib specific settings ie. 

<add name="Connection String" connectionString="Data Source=Logging.sdf;Persist Security Info=False;" providerName="System.Data.SqlServerCe.3.5"/>

 

This works only if I put an explicit path in front of Logging.sdf as it is shown above the EntLib Logging cannot open this file even though it is in the same directory as the assembly that is testing it.

-Craig

Mar 26, 2010 at 6:21 AM

Craig,

Connection Pooling for SqlCe is the one that has a problem with regards to performance. Logging with SqlCe using EntLib is supported. You can still be able to achieve this, one way I know is by creating your own custom listener that would write in your SqlCe Database.  Below is the TraceData method of my Custom Trace Listener that I hope would help. 

        public override void TraceData(TraceEventCache eventCache, string source, TraceEventType eventType, int id, object data)
        {

            if (data is LogEntry && data != null)
            {
                LogEntry le = (LogEntry)data;
               
               
                //Database db = DatabaseFactory.CreateDatabase();
                SqlCeDatabase db = new SqlCeDatabase(this.Attributes["SqlCeConnectionString"]);                               
               
                DbCommand cmd = db.GetSqlStringCommand(this.Attributes["SQLCELogQuery"]);

                db.AddInParameter(cmd, "@Severity", DbType.String, le.Severity);
                db.AddInParameter(cmd, "@Priority", DbType.Int32, le.Priority);
                db.AddInParameter(cmd, "@Message", DbType.String, le.Message);

                db.ExecuteNonQuery(cmd);
 

            }

        }

I'm not sure how you are instantiating your database object. Though, using both of the code below

                Database db = DatabaseFactory.CreateDatabase();
                SqlCeDatabase db = new SqlCeDatabase(this.Attributes["SqlCeConnectionString"]);

 works good for me. Using my Connection String in my app.config.

                <add name="SQLCEString" connectionString="Data Source=SQLCELogging.sdf; Persist Security Info=False;" providerName="System.Data.SqlServerCe.3.5" />

Hope this helps,

~ginkapitan

 

 

 

Mar 28, 2010 at 10:39 PM

For what it's worth, one of the things we did in Entlib 5.0 was fix the SqlCE connection pooling issues, so performance with SqlCE and the data access block in general should be much better.