Showing posts with label DataTable. Show all posts
Showing posts with label DataTable. Show all posts

Sunday, March 20, 2011

Should I Create a Business Logic Layer?

The Business Logic Layer (BLL) is located between the Data Access Layer and the User Interface.  This is where your data is traslated from Database objects, like DataTables and DataRows into Classes and Objects.

Sometimes I use them, and other times I don’t.  This should become obvious in your design phase, when you learn whether or not your application is manipulating data after retrieving it from a database.  If your application is CRUD (Create Retrieve Update Delete), then most likely you don’t need a BLL.  It would be a waste of your time to add an unnecessary layer just because it is a “Proper Model”.

I find that with the Entity Framework the need for a business layer is further diminished.  The reason is that the auto generated code, that is the Model, takes out the need to translate DataRow to an Object.

Now, the only reason left to have a business layer is for business logic.  This is were much of your common code may go, like generation of reports, charts, and calculations.  The last few projects I have done only had partial business logic layers.  I only created them when necessary.

Saturday, February 26, 2011

System.ServiceModel.CommunicationException was unhandled Exception

Problem:
Message=The underlying connection was closed: The connection was closed unexpectedly.

I was making a WCF client/service, I ran it for the first time and got that exception.  So, my WCF service had an OperationContract to return a DataTable like so:


[ServiceContract]
    public interface IUserService
    {
        [OperationContract]
        DataTable GetApprovedUser();
    }

And it was implemented like this:


public DataTable GetApprovedUser()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("UserId", typeof (Int64));
            dt.Columns.Add("IsApproved", typeof (bool));

            DataRow newUser = dt.NewRow();
            newUser["UserId"] = 1000;
            newUser["IsApproved"] = true;
            dt.Rows.Add(newUser);
            newUser = dt.NewRow();
            newUser["UserId"] = 1001;
            newUser["IsApproved"] = true;
            dt.Rows.Add(newUser);
            newUser = dt.NewRow();
            newUser["UserId"] = 1002;
            newUser["IsApproved"] = false;
            dt.Rows.Add(newUser);
            newUser = dt.NewRow();
            newUser["UserId"] = 1003;
            newUser["IsApproved"] = true;
            dt.Rows.Add(newUser);

            return dt;
        }


And my Client:
            ServiceReference1.UserServiceClient proxy = new ServiceReference1.UserServiceClient();
            string User = proxy.Authenticate("theuser", "somecrypt");
            foreach(DataRow userRow in proxy.GetApprovedUser().Tables[0].Rows)
            {
                Console.WriteLine(string.Format("User: {0}, IsApproved: ", userRow["UserId"], userRow["IsApproved"]));
            }



Solution:
Turns out that DataTables are not serializable, so I tried wrapping the DataTable in a DataSet like this:

Interface:
    [ServiceContract]
    public interface IUserService
    {
        [OperationContract]
        void DoWork();

        [OperationContract]
        string Authenticate(string userName, string aCryptKey);

        [OperationContract]
        DataSet GetApprovedUser();
    }



Implementation:


        public DataSet GetApprovedUser()
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            ds.Tables.Add(dt);
            dt.Columns.Add("UserId", typeof (Int64));
            dt.Columns.Add("IsApproved", typeof (bool));

            DataRow newUser = dt.NewRow();
            newUser["UserId"] = 1000;
            newUser["IsApproved"] = true;
            dt.Rows.Add(newUser);
            newUser = dt.NewRow();
            newUser["UserId"] = 1001;
            newUser["IsApproved"] = true;
            dt.Rows.Add(newUser);
            newUser = dt.NewRow();
            newUser["UserId"] = 1002;
            newUser["IsApproved"] = false;
            dt.Rows.Add(newUser);
            newUser = dt.NewRow();
            newUser["UserId"] = 1003;
            newUser["IsApproved"] = true;
            dt.Rows.Add(newUser);

            return ds;
        }

And that fixed it.



For those interested, here was the full stack trace:


  Message=The underlying connection was closed: The connection was closed unexpectedly.
  Source=mscorlib
  StackTrace:
    Server stack trace:
       at System.ServiceModel.Channels.HttpChannelUtilities.ProcessGetResponseWebException(WebException webException, HttpWebRequest request, HttpAbortReason abortReason)
       at System.ServiceModel.Channels.HttpChannelFactory.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpan timeout)
       at System.ServiceModel.Channels.RequestChannel.Request(Message message, TimeSpan timeout)
       at System.ServiceModel.Dispatcher.RequestChannelBinder.Request(Message message, TimeSpan timeout)
       at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
       at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
       at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)
    Exception rethrown at [0]:
       at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
       at WCRTestClient.ServiceReference1.IUserService.GetApprovedUser()
       at WCRTestClient.ServiceReference1.UserServiceClient.GetApprovedUser() in xxxx\visual studio 2010\Projects\Test WCF\WCRTestClient\Service References\ServiceReference1\Reference.cs:line 64
       at WCRTestClient.Program.Main(String[] args) in xxxx\visual studio 2010\Projects\Test WCF\WCRTestClient\Program.cs:line 15
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: System.Net.WebException
       Message=The underlying connection was closed: The connection was closed unexpectedly.
       Source=System
       StackTrace:
            at System.Net.HttpWebRequest.GetResponse()
            at System.ServiceModel.Channels.HttpChannelFactory.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpan timeout)


Tuesday, June 1, 2010

C#: DataRow Update error Index was out of range. Must be non-negative and less than the size of the collection.

You are threading and need to use Monitor.Enter when updating DataRows on a table.  Did you know that DataTables are not thread safe?  Even if your threads are updating different rows of the same DataTable, you can get concurrency errors that manifest themselves in weird ways.

Here’s the error I was getting:
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

Solution:
Use Monitor.Enter to protect updates to your DataRow.

        public void DoWorkUpdatingRow(object state)
        {
            List<DataRow> rowsToWorkOn = (List<DataRow>)state;
            foreach (DataRow dr in rowsToWorkOn)
            {
                Monitor.Enter(this);
                try
                {
                    dr["value"] = dr["id"] + " new value";
                }
                finally
                {
                    Monitor.Exit(this);
                }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            // Setup of data for worker threads
            DataTable dt = new DataTable();
            dt.Columns.Add("id", typeof(int));
            dt.Columns.Add("value", typeof(string));
            for (int i = 0; i < 5000; i++)
            {
                DataRow newRow = dt.NewRow();
                newRow["id"] = i;
                newRow["value"] = i + " text";
                dt.Rows.Add(newRow);
            }

            // Chunk out the work
            const int numberOfThreads = 4;
            int chunkSize = dt.Rows.Count / numberOfThreads;
            int currentRow = 0;

            for (int i = 0; i < numberOfThreads; i++)
            {
                List<DataRow> rowsToWorkOn = new List<DataRow>();

                int maxRowNumber = (currentRow + chunkSize);
                if (i == numberOfThreads)
                {
                    // Last chunk, don't run over the end of the datatable
                    maxRowNumber = dt.Rows.Count;
                }

                for (int j = currentRow; j < maxRowNumber; j++)
                {
                    rowsToWorkOn.Add(dt.Rows[j]);
                }
                currentRow += chunkSize;

                ThreadPool.QueueUserWorkItem(DoWorkUpdatingRow, rowsToWorkOn);
            }
        }

Saturday, February 7, 2009

C#: DataTable Performance


I thought it was time for me to share a simple performance tip when working with DataTables in .Net. We all know indexes in SQL Server, or any database, improve select access times when used correctly. Did you know you can do the same with DataTables?

Here's what I did. I created a DataTable with 2 columns, an ID of type int, and a description of type string. The initial sample created 100,000 rows with the ID in incrementing order starting at 1.
DataSet ds = new DataSet();
DataTable dt = ds.Tables.Add("TestTable");
dt.Columns.Add("id", typeof(int));
dt.Columns.Add("description", typeof(string));

for (int i = 0; i < 100000; i++)
{
DataRow dr = dt.NewRow();
dr["id"] = i;
dr["description"] = "asdf" + i;
dt.Rows.Add(dr);
}

Simple enough. Now, we do the standard DataTable.Select() to get rows with ID between 40,000 and 60,000:
DataRow[] drs = dt.Select("id > 40000 and id < 60000");
Using the StopWatch class, we get a timing (on my 2.8GHz laptop) averaging 267ms. Not bad, it's a lot of data we're selecting. But, let's see what happens when we do this:
dt.PrimaryKey = new DataColumn[] {dt.Columns["id"]};
DataRow[] drs = dt.Select("id > 40000 and id < 60000");
The times average 2ms on my laptop! That's 2 orders of magnitude faster! Well, you're thinking, "I don't use that much data in my datatable! I only have 1000 rows, but I select from it often." Alright, let's try that out:
for (int i = 0; i < 1000; i++)
{
int lower = r.Next(0, 500);
int upper = r.Next(500, 1000);
DataRow[] drs = dt.Select("id > " + lower + " and id < " + upper);
}

Doing that with 1000 rows in the DataTable yields a total time averaging 1,916ms. Let's put in the primary key. We get a timing of 183ms!!! So, on a 1000 row DataTable, selecting from it 1,000 times using a primary key results in a gain of an order of magnitude in performance time!

So, use those primary keys! Of course, a StringBuilder in that last example would help things out, too...