SQL SERVER – What is Denali?

“What is Denali?”

Denali is code name of SQL Server 2011.

Here is the list of the code name of other versions of SQL Server.

In 1988, Microsoft released its first version of SQL Server. It was developed jointly by Microsoft and Sybase for the OS/2 platform.

• 1993 – SQL Server 4.21 for Windows NT
• 1995 – SQL Server 6.0, codenamed SQL95
• 1996 – SQL Server 6.5, codenamed Hydra
• 1999 – SQL Server 7.0, codenamed Sphinx
• 1999 – SQL Server 7.0 OLAP, codenamed Plato
• 2000 – SQL Server 2000 32-bit, codenamed Shiloh (version 8.0)
• 2003 – SQL Server 2000 64-bit, codenamed Liberty
• 2005 – SQL Server 2005, codenamed Yukon (version 9.0)
• 2008 – SQL Server 2008, codenamed Katmai (version 10.0)
• 2010 – SQL Server 2008 R2, Codenamed Kilimanjaro (aka KJ)
• Next – SQL Server 2011, Codenamed Denali

Any guesses what should be the next version after 2011 should be codenamed?

Sending Emails using .net

For sending email
1. You need SMTP Server to communicate with, as well you must be able to properly authenticate with this server.
Following class encapsulate all the setting required to authenticate with the server.

namespace SukeshMarlaBlogs
{
    public class ClsSmtpSettings
    {
        public string User { get; set; }
        public string Password { get; set; }
        public string Server { get; set; }
        public int Port { get; set; }
        public bool RequiresAuthentication { get; set; }
        public bool EnableSSl { get; set; }
        public string PreferredEncoding { get; set; }
        public bool IsValid
        {
            get
            {
                if (Server.Length == 0) { return false; }

                if (RequiresAuthentication)
                {
                    if (User.Length == 0) { return false; }
                    if (Password.Length == 0) { return false; }
                }

                return true;
            }
        }
    }
}

2. A Class which will encapsulate all the message parts

namespace SukeshMarlaBlogs
{
    public class ClsMailParts
    {
     
        public string StrFrom { get; set; }
        public string StrReplyTo { get; set; }
        public string StrTo { get; set; }
        public string StrCC { get; set; }
        public string StrBcc { get; set; }
        public string StrSubject { get; set; }
        public string StrMessageBody { get; set; }
        public bool IsBodyHtml { get; set; }
        public MailPriority ObjPriEmailPriority { get; set; }
        public List<ClsAttachment> ObjPriAttachments { get; set; }
    }
}

3. A Class For Attachments

namespace SukeshMarlaBlogs
{
    public class ClsAttachment
    {
        public string StrAttachmentName{get;set;}
        public string StrAttachmentPath{get;set;}
    }
}

4. A Class For Actually Sending the Email

namespace SukeshMarlaBlogs
{
    public class ClsEmail
    {
        public void SendEmail(ClsSmtpSettings ObjSmtpSettings, ClsMailParts ObjPriMailParts)
        {
            if ((ObjSmtpSettings == null) || (!ObjSmtpSettings.IsValid))
            {
                throw new Exception("Invalid smtp settings detected in SendEmail ");
            }

            MailMessage ObjMail = new MailMessage();
            MailAddress ObjFromAddress = new MailAddress(ObjPriMailParts.StrFrom);
            MailAddress ObjToAddress = new MailAddress(ObjPriMailParts.StrTo);
            ObjMail.From = ObjFromAddress;
            ObjMail.To.Add(ObjToAddress);

            if (ObjPriMailParts.StrReplyTo.Length > 0)
            {
                MailAddress ObjReplyAddress = new MailAddress(ObjPriMailParts.StrReplyTo);
                ObjMail.ReplyTo = ObjReplyAddress;
            }

            if (ObjPriMailParts.StrCC.Length > 0)
            {
                MailAddress ObjCCAddress = new MailAddress(ObjPriMailParts.StrCC);
                ObjMail.CC.Add(ObjCCAddress);
            }

            if (ObjPriMailParts.StrBcc.Length > 0)
            {
                MailAddress ObjBccAddress = new MailAddress(ObjPriMailParts.StrBcc);
                ObjMail.Bcc.Add(ObjBccAddress);
            }

            ObjMail.Subject = ObjPriMailParts.StrSubject;
            ObjMail.Priority = ObjPriMailParts.ObjPriEmailPriority;
            ObjMail.Body = ObjPriMailParts.StrMessageBody;

            if (ObjPriMailParts.IsBodyHtml)
            {
                ObjMail.IsBodyHtml = true;
            }

            SmtpClient ObjSmtpCLient = new SmtpClient(ObjSmtpSettings.Server, ObjSmtpSettings.Port);
            ObjSmtpCLient.EnableSsl = ObjSmtpSettings.EnableSSl;

            if (ObjSmtpSettings.RequiresAuthentication)
            {
                NetworkCredential smtpCredential
                = new NetworkCredential(
                ObjSmtpSettings.User,
                ObjSmtpSettings.Password);

                ObjSmtpCLient.Credentials = smtpCredential;
            }

            // add attachments if there are any
            if (ObjPriMailParts.ObjPriAttachments!=null && ObjPriMailParts.ObjPriAttachments.Count > 0)
            {
                for (int i = 0; i < ObjPriMailParts.ObjPriAttachments.Count; i++)
                {
                    if (!File.Exists(ObjPriMailParts.ObjPriAttachments[i].StrAttachmentPath))
                    {
                        throw new Exception("could not find file for email attachment " +
                            ObjPriMailParts.ObjPriAttachments[i].StrAttachmentPath);
                    }

                    Attachment ObjPriAttachment = new Attachment(ObjPriMailParts.ObjPriAttachments[i].StrAttachmentPath);
                    ObjPriAttachment.Name = ObjPriMailParts.ObjPriAttachments[i].StrAttachmentName;
                    ObjMail.Attachments.Add(ObjPriAttachment);
                }
            }

            try
            {
                ObjSmtpCLient.Send(ObjMail);
            }
            catch (SmtpException ex)
            {
                throw new Exception("error sending email to " + ObjPriMailParts.StrTo + ", message was: " + ObjPriMailParts.StrMessageBody, ex);

            }
            catch (SocketException ex)
            {
                throw new Exception("error sending email to " + ObjPriMailParts.StrTo + ", message was: " + ObjPriMailParts.StrMessageBody, ex);
            }

        }
    }
}

Now to Test the Code we need following code Block (Just replace the settings according to your convenience)

    
    SukeshMarlaBlogs.ClsSmtpSettings ObjPriSmtp = new SukeshMarlaBlogs.ClsSmtpSettings();
    ObjPriSmtp.Server = "mail.somewhere.com";
    ObjPriSmtp.User = "someone@somewhere.com";
    ObjPriSmtp.RequiresAuthentication = true;
    ObjPriSmtp.Password = "pasword";
    ObjPriSmtp.Port = 85;
            

    SukeshMarlaBlogs.ClsMailParts ObjPriMailParts = new SukeshMarlaBlogs.ClsMailParts();
    ObjPriMailParts.IsBodyHtml = false;
    ObjPriMailParts.ObjPriAttachments = null;
    ObjPriMailParts.ObjPriEmailPriority = MailPriority.High;
    ObjPriMailParts.StrBcc = string.Empty;
    ObjPriMailParts.StrCC = string.Empty;              
    ObjPriMailParts.StrFrom = "Sukesh@Marla.com";
    ObjPriMailParts.StrMessageBody = "Welcome To Sukesh Marla's World";
    ObjPriMailParts.StrReplyTo = string.Empty;
    ObjPriMailParts.StrSubject = "Test Message";
    ObjPriMailParts.StrTo = "Tester@Blogspot.com";


    SukeshMarlaBlogs.ClsEmail ObjPriEmail = new SukeshMarlaBlogs.ClsEmail();
    ObjPriEmail.SendEmail(ObjPriSmtp, ObjPriMailParts);

Access Modifier Chart

	Private	Internal	Protected	Protected Internal	Public
With In class	Y	Y	Y	Y	Y
With IN child class of same project	N	Y	Y	Y	Y
With IN Non child class of same project	N	Y	N	Y	Y
With IN child class of other project	N	N	Y	Y	Y
With IN Non child class of other project	N	N	N	N	Y

Calling Virtual Functions from a Constuructor

when the virtual method is overriden in a derived class, this method will be invoked even before the constructor of the derived class has been invoked! which probably was not anticipated by the developer of the derived class!

Example

using System;
namespace VirtualTest
{
    public class Base
    {
        protected  string StrMyString= "Value In Base";


        public Base()
        {
            Console.WriteLine("Base Class Constructor");
            Example1();
        }
        // This will be overridden in the derived type.
        public virtual void Example1()
        {
            Console.WriteLine ("Base DoSomething");
        }
    }

    public class Derived: Base
    {
        public Derived()
        {
            Console.WriteLine("Derived Class Constructor");
            StrMyString= "Value In Derived";
        }
        public override void Example1()
        {
            Console.WriteLine("Derived function is called and Value of StrMyString is {0}", StrMyString);
        }
    }

    public class TestingClass
    {
        public static void Main()
        {
            Derived d = new Derived (); 
        }
    }
}

Output will be

Base Class Constructor
Derived DoSomething is called - initialized ? Value In Base

Derived Class Constructor

Because this is very contra-intuitive, it is best not to invoke virtual methods from a constructor altogether. Except when the class is sealed of course, because then there can be no derived class.

Extract Only Date from Date time field

The following SQL Script helps to Extract only Date from DateTime field, it will ignore the time and display as "00:00:00.000".

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

Here I used the GetDate() function. In the Same way, we can use any datefield column.

Class Vs structures

1. Classes are reference types and structs are value types.
    means Class will be allocated on a heap and structs will be allocated on the stack.

2. You cannot have instance Field initializers in structs.
    means we cant have variables as structs instance members.
3. Classes can have explicit parameter less constructors. But struts cannot have.
4. Classes must be instantiated using the new operator. But struts can be without.
5. Class support Polymorphism and inheritance but struct wont.
6. Protected and ProtectedInternal is not supported in struct.
8. A class is permitted to declare a destructor. But a struct is not
7. classes are used for complex and large set data. structs are for simple data structures.

Generate Random number using C#

Let's see how to generate random number:

Random ran = new Random();
Console.WriteLine(ran.Next());

Sql Primary Key - GUID or Int

INT Data Type:

Advantages:

1. Its required small space in terms of the storage it will only allocates 4 bytes to store data.
2. Insert and update performance will be faster then the GUID. It will increase the performance of the application.
3. Easy to index and Join will give best performance with the integer.
4. Easy to understand and remember
5. Support of function that will give last value generated like Scope_Indentity()

Disadvantages:

1. If you are going to merge table frequently then there may be a chance to duplicated primary key.
2. Limited range of uniqueness if you are going to store lots of data then it may be chance to run out of storage for INT data type.
3. Hard to work with distributed tables.

GUID Data Type:

Advantages:

1. It is unique for the current domains. For primary key is uniquely identifies the table.
2. Less chances of for duplication.
3. Suitable for inserting and updating large amount of data.
4. Easy for merging data across servers.

Disadvantages:

1. Bigger storage size (16bytes) will occupy more disk size then integer.
2. Hard to remember and lower performance with Join then integer.
3. Don’t have function to get last uniquely generated primary key.
4. A GUID primary Key will added to all the other indexes on tables. So it will decrease the performance.

 

Convert String to Stream and stream to string

using System.IO;
using System.Text;
using System;
namespace CustomIoClasses
{
    class ClsStream
    {
        public static string GetString(Stream ObjPriReader)
        {
            return new StreamReader(ObjPriReader).ReadToEnd();
        }
        public static Stream GetStream(String StrPriData)
        {
            byte[] byteArray = Encoding.ASCII.GetBytes(StrPriData);
            MemoryStream stream = new MemoryStream(byteArray);
            return stream;
        }
        static void Main(string[] args)
        {
            string str = "I love my India";
            System.Console.WriteLine(GetString(GetStream(str)));
            Console.ReadKey();

        }
    }
}

Disconnect all users from Sql Server 2005/2008 Database

In some cases DBA's need to expire or close all connections from SQL server 2005 / SQL server 2008 database such as for attach detach DataBase, Make DB readonly, perform maintenance tasks etc. For such type of issues DBA wants to get exclusive access to the database. To do so, you can set the database to Single User Mode, which permits only one database connection at a time. At that moment if other users try to access the database while you are working on that active connection, they will receive an error.

To bring a database to the single user mode, use the following query:

1	ALTER DATABASE DATABASENAME SET SINGLE_USER

Users those already connected to the db when you run this command, they will not be disconnected. Instead the 'SET SINGLE_USER' command will wait till the others have disconnected. If you want to override this scenario and forcefully disconnect other users, then use the following query:

1	ALTER DATABASE DATABASENAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE

OK now your database immediately move to the single user mode. Now After completion of your maintenance task you need to go back to multiuser mode by applying another TSQL command which is given below:

1	ALTER DATABASE DATABASENAME SET MULTI_USER

Add service reference dynamically from user control

Steps:-

1. Create a New Website using Visual Studio 2008 or newer.
2. Create a usercontrol and Call it MyUserControl.
3. Create a function called AddScriptReferenceDynamically as 
    private void AddScriptReferenceDynamically()
    {
        ScriptManager scriptManger = ScriptManager.GetCurrent(this.Page);
        if (scriptManger != null) 
        { 
            ServiceReference serviceReference = new ServiceReference(); 
            serviceReference.Path="~/MyWebService.asmx";
            serviceReference.InlineScript = false;  
            scriptManger.Services.Add(serviceReference);    
        }
        else 
        { 
            throw new Exception("Script Manager Not Found"); 
        }
    } 
4. Override controls init event as 
    protected override void OnInit(EventArgs e)
    {
        AddScriptReference(); 
        base.OnInit(e); 
    } 

Thats it you done with it. Now just create a webpage in Your project and add a instance of control to your page.

Create a Windows Service in .NET That Can Also Run as Console Application

I  want to  create a simple windows service using Visual Studio but  I want to be able to easily test it by simply running the resulting exe without the need to install the service. For that we have to follow the below steps….

• Create a new Windows Service Project as:
  
• Once that’s done, you should have a Program.cs and a Service1.cs file in your project. Just delete the Program.cs
• And now instead add Main method in service1.cs class.
• Now add Some name to your service say MyService.
• Now update the code in Main method with the following the code in the following example.
  

   1 using System;
   2 using System.ServiceProcess;
   3 using System.Timers;
   4 
   5 namespace WindowsService1
   6 {
   7     public partial class MyService  : ServiceBase
   8     {
   9 
  10         public MyService()
  11         {
  12             InitializeComponent();
  13         }
  14 
  15         protected override void OnStart(string[] args)
  16         {
  17             Timer ObjPriTimer = new Timer();
  18             ObjPriTimer.Interval = 1000;
  19             ObjPriTimer.Elapsed += new ElapsedEventHandler(ObjPriTimer_Elapsed);
  20             ObjPriTimer.Start();
  21         }
  22 
  23         void ObjPriTimer_Elapsed(object sender, ElapsedEventArgs e)
  24         {
  25             Console.WriteLine("Welcome to SukeshMarla.Blogspot.com");
  26         }
  27 
  28         protected override void OnStop()
  29         {
  30         }
  31 
  32 
  33         static void Main(string[] args)
  34         {
  35 
  36             MyService  ObjPriMyService = new MyService ();
  37             if (Environment.UserInteractive)
  38             {
  39                 ObjPriMyService.OnStart(args);
  40                 Console.WriteLine("Service started,Press a key to stop!!!");
  41                 Console.ReadKey();
  42                 ObjPriMyService.OnStop();
  43             }
  44             else
  45             {
  46                 Run(ObjPriMyService);
  47             }
  48         }
  49     }
  50 }
  51 

  
  
• Now right click the project from the solution explorer and change the Output Type To Console Application
  
• Now Press F5 that’s it