Next step of programming

While showing the file path in the menus/status bar/caption bar we face difficulty with long paths. Since it will increase the width/length too much and it will become odd. We can cut short that path with the help of the following function and we will utilise those path where ever we want to show with ellipses format. Now here in this particular function it is done to very simple extent. You need to pass the path it will take out the drive or base folder and append one directory name to it. Later it will add the file name to this path with ellipses ‘…’.

public string GetEllipsesPath(string fullPath)
{
string ellipsesPath = "";

if (!string.IsNullOrEmpty(fullPath))
{
string[] dirName = fullPath.Split('\\');
string fileName = Path.GetFileName(fullPath);

//Shorten the file name················
if (fileName.Length > 25)
{
fileName = fileName.Substring(0, 15) + "..." 
+ fileName.Substring(fileName.Length - 5, 5);
}

//Shortten the complete path
                ellipsesPath = Path.GetPathRoot(fullPath) + (dirName.Length > 2 ? dirName[1]
+ @"\..\" : "") + fileName;
}

return ellipsesPath;
}

This function can further be modified having another parameter named ‘length’. with that we can give developer option of having the of the specified size. You can also use the api function to perform the same function .

[DllImport("shlwapi.dll")]
private static extern bool PathCompactPathEx(string pszOut, 
string szPath, int cchMax, int dwFlags);

With new feature of CLR integration we can provide function with in our assemblies which can be accessed by the user in the TSQL statements. Let us now try to create one title case function. First create one library project in visual studio 2005. Then create one class with one function inside that.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace AssemblyFunctions
{

public partial class MySQLFunctions    
{
[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static string TitleCase(string data)
{
string caseData = data.ToString().Substring(0, 1).ToUpper()
+ data.ToString().Substring(1, data.ToString().Length -1).ToLower();

return caseData;
}
}
}

Now we can compile this assembly and keep it at one location. Then move to MSSQL Management Studio and there in query window. execute following commands one bye one
First we need to enable the CLR integration for that we have to use the sp_configure store procedure in the following way

--Configure the clr enabled state
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

Now to access the assembly into our MSSQL Server we have to register it first

--REGISTER CLASS LIBARIES IN SQL SERVER
CREATE ASSEMBLY ASSEMBLYFUNCTIONS 
FROM 'E:\personal\projects\AssemblyFunctions\AssemblyFunctions\bin\Debug\AssemblyFunctions.dll'
WITH PERMISSION_SET = SAFE
GO

To drop that assembly use the following commands

--DROP/UN-REGISTER THE ASSEMBLY 
DROP ASSEMBLY ASSEMBLYFUNCTIONS
GO

Now to access our functions we have to first register our functions, procedures or what ever is there

--REGISTER USER DEFINED FUNCTION IN SQL SERVER 
CREATE FUNCTION TITLECASE(@DATA NVARCHAR(MAX)) RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME AssemblyFunctions.[AssemblyFunctions.MySQLFunctions].TitleCase  GO

Since now all the things are done you can call your respective function

Select dbo.TitleCase('sample')
go

Also this assembly and function will be added to the mssql server. see the image below

CLR Integration

To associate declarative information with C# code we use Attributes for are methods, types properties etc. Now this attribute can be ustilised at the runtime by querying them. Some of the common used attributes are

• Flag
• Obsolete
• Serializable
• and many more are there

Let us see the example of the obsolete

public class AttributeTest    
{
    [Obsolete("This is obsolete Method", true)]
    private void ObsoleteMethod()
    {
        Debug.Print("I am obsolete");
    }

    public void TestIt()
    {
        ObsoleteMethod();
    }
}

Now after compliation you will get the following result

Compilation Result

Creating custom attribute

First of all it should be inherited from the Attribute class. We are also using on extra attribute on the top of are our attribute class which is [AttributeUsage(AttributeTargets.All)]. which is used for setting various options for our attribute

AttributeUsage

[AttributeUsage(AttributeTargets.All)]
public class HelpAttribute : Attribute    
{
private string _helpText;
public string HelpText
{
get            
{
return _helpText;

}
}

public HelpAttribute(string helpText)
{
_helpText = helpText;
}
}

[Help("Create file with encrypted text")]
public class EncryptedFile    
{
}

Now you can go though the custom attributes in the following way

Assembly assembly = Assembly.GetExecutingAssembly();
foreach (Attribute attribute in assembly.GetCustomAttributes(true))
{
if (attribute is HelpAttribute)
{
MessageBox.Show(((HelpAttribute)attribute).HelpText);
}
}

For more on attributes visit Attribute Tutorial

One of the way to protect the data of the user visiting your website or accessing you application through network is by using SSL. SSL Also know as Secure Socket Layer. SSL was first developed by netscape. It was enhanced from version 1.0 to 3.0. Its latest version is known as TLS (Transport Layer Security) 1.2. for further details visit

http://en.wikipedia.org/wiki/Transport_Layer_Security

From the framework 2.0 it is also provided as the part of the libraries and with that we can develop our server or client application further on that. The class used for purpose is SslStream and it available in System.Net.Security namspace. For developing a server running on SSL we need a SSL certificate which contains the public and the private key. Sample for the server and the client application with complete information on the SslStream class is available in MSDN

http://msdn.microsoft.com/en-us/library/system.net.security.sslstream.aspx

In the 2 Part of this post i will try to put a sample application(server and client) working on the SSL

To get the count of the rows we normally use

select count(ContactID) from AdventureWorks.Person.Contact 

But there is another way which is fast and less on cost as compared to previous method and the way is

select rows from sysindexes where id = OBJECT_ID('AdventureWorks.Person.Contact')
 and indid < 2

Below see the query cost details

Query Cost Details