March 2007 - Posts
When commands are executed synchronously, the thread is waiting for a response from the database server and it is getting into a blocking state (until the command is finishing to be executed).
protected void ExecuteCommandInSynchronousAccess()
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText("UPDATE CUSTOMERS SET NAME='Yuval'");
// Processing the command on the SQL server...
// Waiting for return results
int affectedRowNumber = command.ExecuteNonQuery();
}
}
}
protected void ExecuteCommandInAsynchronousAccess()
{
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand command = new SqlCommand(connection);
command.CommandText("Select CUSTOMERS.NAME FROM CUSTOMERS");
command.BeginExecuteNonQuery(new AsyncCallback(ProcessResult),command);
}
// Run in a new thread,and not blocking the current program
public void ProcessResult(IAsyncResult asyncResult)
{
SqlCommand command = (SqlCommand)asyncResult.AsyncState;
using (command.Connection)
{
using (command)
{
int affectedRowNumber = command.EndExecuteNonQuery();
}
}
}
Creating and opening a connection to a database is very expensive operation (In time & resource). To handle that problem we should use connection pooling that holds a number of existing connections. Each connection pooling has a minimum pool size and maximum pool size. The minimum pool size is use to determine the minimum number of connection that the pool must hold, and the maximum pool size define the maximum connection that the pool can hold at the same time. If the pool reaches to maximum connection, the next request will be added to a waiting queue until another connection is return to the pool and become available.
Base rules to implement connection pooling:
- The connection string must be the same one in all connections.
- The user ID must be the same one in all requests.
- You can't share connections across processes (always use the same process ID).
The connection pooling is being created only in the first access to the pooling.
When a connection is return to the connection pool, it has an idle lifetime of 4 – 8 minutes before its being released (it is happening only if the minimum connection number isn't lower than the minimum pool size).
How to identify connection leaks?
You can use the performance monitoring to define the NumberOfPooledConnection counter. If the number of pooled connection rises uncontrollably, you should check if you closed the connection properly.
You have some ways to close the connection like:
- Implementing try-catch-finally block(and close the connection in the finally section)
- Implementing using block (This is the elegant way, it will call the dispose method automatically).
When a connection to the server fall down or the server become unavailable, the connections in the pool becomes corrupted. To recover the connections you can use the ClearPool() or ClearAllPools() Methods(a static methods in connection classes).
Last Thursday I lectured at my company about agile methodologies family (XP & Scrum). The lecture contained the following parts: a short briefing about problems in software development, Agile methodology, the12 XP practices(and some extra practices) and finally present the Scrum process in action(with samples that showed how to manage a scrum project using the Team Foundation Server).
It's the third time that I have lectured this presentation (The first time I lectured it to my team and the second time I lectured to TTM team in my company). At the beginning, I had a heavy lecture with long slides, and in the last lecture I shrank the presentation and I added some pictures and dynamic slides.
You can download the presentation from here.
I really hope that the audience liked my show and my presentation.
There is an option to put a different access level to one property(in .Net v2.0). You can write a property with a public get and a private/protected set. But you can write the explicitly access level only to one of the get/set in the property. In addition, we can only put a lower level of access to set/get from the outer access level(of the entire property).
public int MyProperty
{
get {return myProp;}
protected set {myProp = value;}
}
protected int MyProperty2
{
get {return myProp2;}
private set {myProp2 = value;}
// Will not work:
// public set {myProp2 = value;}
}
At any case the CLR is creating tow methods from a property, but now it will create tow methods with a different access level.
I started to read the book "Programming Microsoft ADO.NET 2.0 applications Advanced topics", and I want to share you with a short summary that I wrote.
DataSet:
Memory based relational representation of data (or cache data) without any transaction properties.
Typed DataSet comes to improve the regular DataSet to a stronger type (it gets the errors in a compile time, the VS IntelliSense helps to reduce incorrect spelling).
The creation is manually or by XSD (XML schema definition) file.
Serialization options:
- XML-based serialization: serialize to xml file
- With schema definition (XmlWriteMode.WriteSchema).
- With DataRowVersion information(XmlWriteMode.DiffGram)
- Binary-based serialization: serialize in binary format
- RemotingFormat= SerializationFormat.Binary: The serialize binary file contains pure binary data (it's called - TrueBinary). It's good for large objects (it's faster but it has overhead of long initial time).
- RemotingFormat= SerializationFormat.Xml: The serialize binary file contains embedded XML. It's good for small objects.
Command:
- ExecuteNonQuery method:
- We use this method only when we don't need any rows as an answer. We usually use this method in insert, update and delete statements. The return value, contains the number of updated rows.
- ExecteScalar method:
- We use this method only if we get one value as return value(like in count(…) function).
- In this method the .Net runtime will not create a DataTable as a result (it gives better performance).
- ExecuteReader method:
- We use this method in all others queries cases.
- Return a DataReader object as a result (a read-only and forward-only object).
DataReader:
- The DataReader is a high performance method to retrieve data from the data store.
- The DataReader is a read-only, server-side cursor and forward-only object.
- It's useful for a read only data, and less for updated data.
- Read only one row at a time (by default).
- Multiple Active Result Sets(MARS) – When you try to retrieve data with the DataReader object(that keeps an open server-side cursor) you can't open another DataReader from the same connection. Unless you use the MultipleActiveResultSetes property which is a part of the connection string (for example in SQL server 2005).
DataAdapter:
- The DataAdapter is use for query and update data between DataTable and the data store.
- UpdateBatchSize property – is used to set the number of SQL statements that will be sent to the server. By default, the server sent only one row change statement at the same time (row by row). You should change this number to give the possibility to collect a big number of changes before we'll send it to the server.
Last week I had a task to find a way to export a current extent of map control (in ESRI ADF.NET 9.2) to image format (for examples: jpeg, bmp). I tried to look at examples in ESRI documentation and to look over the net, but I didn't found a good one(some of them are not completed and others are implemented in ADF.NET 9.1). I want to show you a complete example to do this:
public string GetMapImageUrl(ESRI.ArcGIS.ADF.Web.UI.WebControls.Map mapControl,
esriImageFormat imageFormat,
int imageHeight,int imageWidth,double imageDpi)
{
MapImage mapImage =
MapExport(mapControl, null, imageFormat, imageHeight,
imageWidth, imageDpi, esriImageReturnType.esriImageReturnURL);
return mapImage.ImageURL;
}
public MapImage MapExport(
ESRI.ArcGIS.ADF.Web.UI.WebControls.Map mapControl,
ESRI.ArcGIS.ADF.ArcGISServer.Envelope exportedExtent,
esriImageFormat imageFormat, int imageHeight,
int imageWidth, double imageDpi, esriImageReturnType returnType)
{
MapDescription mapDescription;
ImageDescription imageDescription;
MapFunctionality mapFunctionality = GetMapFunctionality(mapControl);
mapDescription = GetMapDescription(mapFunctionality);
//Set the current map extent
mapDescription.MapArea.Extent = (exportedExtent != null)
? exportedExtent :
ESRI.ArcGIS.ADF.Web.DataSources.ArcGISServer.Converter.FromAdfEnvelope
(mapControl.Extent);
imageDescription = CreateImageDescription(imageFormat,
imageHeight, imageWidth, imageDpi, returnType);
MapResourceBase mapResource = (MapResourceBase)mapFunctionality.Resource;
MapServerProxy mapServerProxy = mapResource.MapServerProxy;
// Return MapImage class
return mapServerProxy.ExportMapImage(mapDescription,imageDescription);
}
private static MapDescription GetMapDescription(MapFunctionality mapFunctionality)
{
MapDescription mapDescription = mapFunctionality.MapDescription;
return mapDescription;
}
private static MapFunctionality GetMapFunctionality
(ESRI.ArcGIS.ADF.Web.UI.WebControls.Map mapControl)
{
MapFunctionality mapFunctionality =
(MapFunctionality)mapControl.GetFunctionality(1);
return mapFunctionality;
}
private static ImageDescription CreateImageDescription(esriImageFormat
imageFormat, int imageHeight, int imageWidth, double imageDpi,
esriImageReturnType returnType)
{
ImageDescription imageDescription = new ImageDescription();
ImageType imageType = new ImageType();
imageType.ImageFormat = imageFormat;
// Return url to map image or MIME data
imageType.ImageReturnType = returnType;
ImageDisplay imageDisplay = new ImageDisplay();
imageDisplay.ImageHeight = imageHeight;
imageDisplay.ImageWidth = imageWidth;
imageDisplay.ImageDPI = imageDpi;
imageDescription.ImageDisplay = imageDisplay;
imageDescription.ImageType = imageType;
return imageDescription;
}