SQL Data Types vs. C# Data Types
| April 15, 2011 | Posted by Matt under .NET, SQL |
This article is just a reference of SQL Data Types to C# Data Types.
| SQL Server data type | CLR data type (SQL Server) | CLR data type (.NET Framework) |
| varbinary | SqlBytes, SqlBinary | Byte[] |
| binary | SqlBytes, SqlBinary | Byte[] |
| varbinary(1), binary(1) | SqlBytes, SqlBinary | byte, Byte[] |
| image | None | None |
| varchar | None | None |
| char | None | None |
| nvarchar(1), nchar(1) | SqlChars, SqlString | Char, String, Char[] |
| nvarchar | SqlChars, SqlString | String, Char[] |
| nchar | SqlChars, SqlString | String, Char[] |
| text | None | None |
| ntext | None | None |
| uniqueidentifier | SqlGuid | Guid |
| rowversion | None | Byte[] |
| bit | SqlBoolean | Boolean |
| tinyint | SqlByte | Byte |
| smallint | SqlInt16 | Int16 |
| int | SqlInt32 | Int32 |
| bigint | SqlInt64 | Int64 |
| smallmoney | SqlMoney | Decimal |
| money | SqlMoney | Decimal |
| numeric | SqlDecimal | Decimal |
| decimal | SqlDecimal | Decimal |
| real | SqlSingle | Single |
| float | SqlDouble | Double |
| smalldatetime | SqlDateTime | DateTime |
| datetime | SqlDateTime | DateTime |
| sql_variant | None | Object |
| User-defined type(UDT) | user-defined type | None |
| table | None | None |
| cursor | None | None |
| timestamp | None | None |
| xml | SqlXml | None |
Interface vs. Abstract Class in .Net
| November 26, 2010 | Posted by Matt under .NET |
In this article I will try to briefly explain main differences between interface and abstract class concepts in .Net. Although the differences can appear to be very subtle and little important, badly design inheritance can lead to massive problems and great mess in the code.
Abstract Class
Accordingly to MSDN abstract classes “are classes that cannot be instantiated (…), useful when creating components because they allow you specify an invariant level of functionality in some methods, but leave the implementation of other methods until a specific implementation of that class is needed. They also version well, because if additional functionality is needed in derived classes, it can be added to the base class without breaking code.”
Do declare abstract class following syntax is required:
abstract class Car() { public Car() { }; public string Make { get; set; } abstract public void Drive(); }
This class cannot be instantiated so following line will cause compiler throwing error:
Car a = new Car(); // this won’t compile
The only way to instantiate class Car is by creating child class that will inherit from it:
public class OffRoadCar : Car { public OffRoadCar(); public void Switch4WD(bool On) { // method code }; }
Because class OffRoadCar inherits from Car, following code will work:
OffRoadCar offroader = new OffRoadCar(); offroader.Switch4WD(true); // from OffRoadClass offroader.Drive(); // from Car class
Interface
Interface, after MSDN, is a “definition that can never change after the interface definition has been released. This interface invariance is an important principle of component design, because it protects existing systems that have been written to use the interface.”
Interface cannot be instantiated, but serve as sort of pattern for derived classes, that share and implement specific features. Derived classes can also extend interface functionality, by defining extra members. Following code demonstrates sample interface:
public interface ICar { string Make { get; set; } void Drive(); }
Because interface can serve as a “template” for series of classes, implementing it is a way of ensuring that all derived object will have set of common features. In example:
public class SportsCar : ICar { public string Make { get; set; } // required from ICar public bool IsConvertible { get; set; } // adds extra feature public void Drive() // required by ICar { }; } public class Van : ICar { public string Make { get; set; } // required from ICar public double LoadWeight { get; set; } // adds extra feature public void Drive() // required by ICar { }; }
Because both SportsCar and Van classes inherit from ICar interface, they are forced to have common set of features (Make property and Drive() method). Thanks to that it is possible to write:
List<ICar> vehicles = new { new SportsCar() { Make = "Ferrari", IsConvertible = false }, new Van() { Make = "Ford", LoadWeight = 3000 } }; foreach(ICar car in vehicles) { Console.WriteLine(car.Make); }
Conclusion
In opposite to abstract class, interface cannot implement any default functionality in its methods. It also cannot implement default constructor. This is why we say we implement interface and inherit from abstract class.
Class may implement an unlimited number of interfaces, but may inherit from only one abstract class. A class that is derived from an abstract class may still implement interfaces.
Abstract classes and interfaces play extremely important role in programming concept called polymorphism, which in essence is “ability for classes to provide different implementations of methods that are called by the same name” (MSDN).
Resources
Web service proxy in ASP.NET MVC – how to avoid cross-site script warnings
| October 19, 2010 | Posted by Matt under .NET, Ajax, Web |
In this article I will walk through the process of creation of web service proxy in ASP.NET MVC. This is specifically useful as a method of preventing cross-site scripting (XSS) warnings on web page. This is a feature of all modern web browsers and cannot be easily omitted as it enforces same origin policy. This sort of attack can happen when browser tries to download client script from outside of application domain – simply speaking when web browser downloads script from different location.
For the purpose of this demo I will use IP look-up web service available at http://ipinfodb.com/. This web service allows to obtain geodata for given IP address and can be extremely useful for in example centralizing map (i.e. Google maps or Bing maps) on user’s location.
Valid request should look like this: http://ipinfodb.com/ip_query.php?ip=213.180.146.27
Of course it is possible to call this web service at Ajax request level, by simply writing:
var ipaddress = '213.180.146.27';
$.get('http://ipinfodb.com/ip_query.php?ip=' + ipaddress + '&timezone=false',
function(data) {...}
);
although on all most recent web browsers (IE 8, FF 3.6, Chrome, Safari) user will get very ugly security warning every time browser executes this code. This is due to XSS vulnerability mentioned above and can be extremely annoying to user when application performs this operation on very first page load.
Thankfully ASP.NET provides set of mechanisms to overcome this problem. In simplest words there is an extra layer between web service and Ajax call needed, and this layer is web service proxy.
The core component of proposed solution is a HTTP handler class, in this example named LocationProxyHandler. This class will create and execute HTTP request to web service and return response to client. It needs to implement two interfaces: IHttpHandler and IRouteHandler. First interface guarantees that class can process HTTP request, second allows to use class in routing rule that will be required to redirect requests to the handler class. Implementation of class is as follows:
public class LocaionProxyHandler : IHttpHandler, IRouteHandler
{
public bool IsReusable
{
get { return true; }
}
public void ProcessRequest(HttpContext context)
{
string ipAddress = context.Request.QueryString["ipaddress"];
string str = string.Format(@"http://ipinfodb.com/ip_query.php?ip={0}",
ipAddress);
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(str);
request.Method = "GET";
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
StreamReader reader = new StreamReader(response.GetResponseStream());
HttpResponse res = context.Response;
res.ContentType = "application/xml; charcode=utf8";
res.StatusCode = 200;
res.Write(reader.ReadToEnd());
}
public IHttpHandler GetHttpHandler(RequestContext requestContext)
{
return this;
}
}
The most important method in the class is ProcessRequest(). It takes current HttpContext as parameter and for this context it executes HTTP request to external web service and builds and returns response. Before it fires HTTP request, it takes IP address as parameter from URL.
There is one more thing that needs doing before this proxy can serve the purpose. It has to be registered in ASP.NET MVC routing table within the application. To accomplish this the following code is needed in RegisterRoutes() method inside Global.asax.cs:
routes.Add(new Route("{action}.proxy", new SocialCitiProxy.LocaionProxyHandler()))
This makes calls like http://localhost:1431/myApp/Service.proxy understandable for MVC engine and ensures the request is redirected to LocationProxyHandler.
Very final step is Ajax request to LocationProxyHandler:
var ipaddress = '213.180.146.27';
$.ajax({
url: 'GetUserLocation.proxy',
data: 'ipAddress=' + ipaddress,
dataType: 'xml',
contentType: 'application/xml; charset=utf8',
error: function (xhr, status, e) {...},
success: function (data) {...}
});
No security warning messages appears now and this is exactly what we wanted to achieve.
Clustered vs. Non-clustered indexes in SQL Server
| October 14, 2010 | Posted by Matt under SQL |
In this article I will make attempt to outline differences between clustered and non-clustered indexes in SQL Server. Be fully aware that it is not going to be comprehensive explanation, but rather brief overview highlighting purpose of both types of indexes.
Indexes
Indexes are one of the most important part of database optimization, especially for large and growing database tables. They work in very similar way to index in a book - just imagine massive address book, with thousands pages but no index. Searching this book for specific address is nearly impossible without index.
When query is ran against SQL Server database, Query Optimizer analyses all available indexes for tables involved, and creates most efficient execution plan to run statement. SQL Server reads data in 8KB pages, so if table is small enough to fit on one or two pages, there is no need for an index at all, as SQL Server will only have to make one or two read operations. This operation is called Full Table Scan and for small table it is the most efficient way of pulling the data out. On the other hand, if table has 1,000,000 records and size of row allows reading only 50 rows per page, number of read operations rises to 20,000. That clearly will take a lot of time and this is where indexes come into play. Indexes can massively improve SQL query, however is worth mentioning, that it does not come for free - indexes can take a lot of storage. When index is involved in selecting data from table, this operation is called Index Seek. In general we can distinguish two types of indexes: clustered and non-clustered.
Clustered Index
This index assumes that data in a table is physically sorted in specific order. This means that when new row is inserted, it physically goes to the place determined by index, allowing data after new row to be moved to next page if required. The table can only have one clustered index. This index is specifically useful if we use BETWEEN statement in WHERE clause of our statement (i.e. SELECT * FROM Table WHERE Col1 BETWEEN ‘A’ AND ‘Z’).
Non-clustered Index
When this type of index is used logical order of index does not match physical order of data stored on the disk. Index plays a role of “pointer to row”, allowing faster access to the row that is requested.
A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. This type of index works best when used having = in WHERE clause (i.e. SELECT * FROM Table WHERE Col1 = ‘A’) or if the column is used in JOIN.
Reference
- http://msdn.microsoft.com/en-us/library/ms188783.aspx - MSDN on indexes
- http://en.wikipedia.org/wiki/Index_(database) – Wikipedia on indexes
- http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx - good article explaining difference between clustered and non-clustered indexes much deeper
How to refer to previous row in SQL 2005
| September 22, 2010 | Posted by Matt under SQL |
In this post I will describe simple mechanism allowing to refer back to previous row in SQL Server 2005/2008 database table.
For the purpose of this article let’s create a table dbo.AgentLog:
create table dbo.AgentLog ( AgentID int ,LogInTime datetime ,LogOutTime datetime ) go insert dbo.AgentLog select 1, '2010-09-08 09:40:00.000', '2010-09-08 10:14:00.000' union all select 1, '2010-09-08 10:35:00.000', '2010-09-08 12:04:00.000' union all select 1, '2010-09-08 13:11:00.000', '2010-09-08 14:24:00.000' union all select 1, '2010-09-08 14:35:00.000', '2010-09-08 16:08:00.000' union all select 2, '2010-09-08 09:00:00.000', '2010-09-08 09:23:00.000' union all select 2, '2010-09-08 10:12:00.000', '2010-09-08 12:05:00.000' union all select 2, '2010-09-08 13:12:00.000', '2010-09-08 15:07:00.000' union all select 2, '2010-09-08 15:10:00.000', '2010-09-08 16:25:00.000' go
Our table will look like this:
AgentID LogInTime LogOutTime ----------- ----------------------- ----------------------- 1 2010-09-08 09:40:00.000 2010-09-08 10:14:00.000 1 2010-09-08 10:35:00.000 2010-09-08 12:04:00.000 1 2010-09-08 13:11:00.000 2010-09-08 14:24:00.000 1 2010-09-08 14:35:00.000 2010-09-08 16:08:00.000 2 2010-09-08 09:00:00.000 2010-09-08 09:23:00.000 2 2010-09-08 10:12:00.000 2010-09-08 12:05:00.000 2 2010-09-08 13:12:00.000 2010-09-08 15:07:00.000 2 2010-09-08 15:10:00.000 2010-09-08 16:25:00.000
The task is to for each agent list all breaks the person made and its length. To do this I need to somehow refer back to previous row to calculate difference in minutes between login and previous logout.
To accomplish the task I will use two features new to SQL Server 2005 (when compared to SQL Server 2000). These are CTE (Common Table Expression) and Row_Number() function.
First one in essence can be thought as variable table of results from SELECT statement. This data set only exists in current scope.
Row_Number() is a SQL Server built-in function that adds extra identity column to data set retrieved with SELECT statement.
SELECT Row_Number() over (partition by AgentID order by LogInTime) AS RowID ,* FROM dbo.AgentLog
Row_Number() takes partition by argument, which tells function that you want unique row numbers against specific column. If this isn’t provided, SQL Server will just number all rows on one-by-one basis. Combining CTE and Row_Number() we can now get the following:
;with AgentLogCTE as
(select Row_Number() over (partition by AgentID order by LogInTime) as RowID, * from dbo.AgentLog )
This CTE then contains following information:
RowID AgentID LogInTime LogOutTime -------------------- ----------- ----------------------- ----------------------- 1 1 2010-09-08 09:40:00.000 2010-09-08 10:14:00.000 2 1 2010-09-08 10:35:00.000 2010-09-08 12:04:00.000 3 1 2010-09-08 13:11:00.000 2010-09-08 14:24:00.000 4 1 2010-09-08 14:35:00.000 2010-09-08 16:08:00.000 1 2 2010-09-08 09:00:00.000 2010-09-08 09:23:00.000 2 2 2010-09-08 10:12:00.000 2010-09-08 12:05:00.000 3 2 2010-09-08 13:12:00.000 2010-09-08 15:07:00.000 4 2 2010-09-08 15:10:00.000 2010-09-08 16:25:00.000
Now what we need to do is just join this table to itself in some clever way, linking current row to previous one:
select t1.AgentID ,t2.LogOutTime as PrevLogout ,t1.LogInTime as Login ,convert(varchar, t1.LogInTime - t2.LogOutTime, 108) as BreakTime from AgentLogCTE t1 join AgentLogCTE t2 on t2.RowID = (t1.RowID-1) and t1.AgentID = t2.AgentID go
And this is it! Resultset looks like the following:
AgentID PrevLogout Login BreakTime ----------- ----------------------- ----------------------- ------------------------------ 1 2010-09-08 10:14:00.000 2010-09-08 10:35:00.000 00:21:00 1 2010-09-08 12:04:00.000 2010-09-08 13:11:00.000 01:07:00 1 2010-09-08 14:24:00.000 2010-09-08 14:35:00.000 00:11:00 2 2010-09-08 09:23:00.000 2010-09-08 10:12:00.000 00:49:00 2 2010-09-08 12:05:00.000 2010-09-08 13:12:00.000 01:07:00 2 2010-09-08 15:07:00.000 2010-09-08 15:10:00.000 00:03:00
Whole SQL script explained in this article can be obtained from here.
I am IT professional with over 10 years of experience in computer programming and web development, specializing in Microsoft technologies.
Recent Comments