How to install FREE ssl into your windows web server?

SSL is expensive to renew and this is how you can do it with Let's Encrypt FREE SSL

Step 1
go to https://www.sslforfree.com/ to generate free SSL certificate. There are 3 files: private.key, certificate.crt and ca_bundle.crt

Step 2
Download openssl for windows https://slproweb.com/products/Win32OpenSSL.html Win32 OpenSSL v1.1.1c is preferred.

Step 3
Generate .pfx from the SSL certificate downloaded from www.sslforfree.com. There are 3 files.

just execute from the openssl bin directory, make sure the 3 files are copied into the bin directory for simplicity: openssl pkcs12 -export -out certificate.pfx -inkey private.key -in certificate.crt -certfile ca_bundle.crt

Step 4
Go to IIS >  Server Certificates >  Import > Select Web Hosting as your Certificate store

Step 5
Bind the SSL certificate as usual.

How to create a dynamic table which can dynamically storing data without the need of create physical stucture tables.

DB design schema

GO
/****** Object:  Table [dbo].[Designer_Form]    Script Date: 04/10/2013 17:24:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Designer_Form](
    [FormID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [FormTitle] [nvarchar](150) NULL,
    [SubCategoryID] [smallint] NULL,
    [Description] [nvarchar](1150) NULL,
    [DevelopmentID] [int] NULL,
 CONSTRAINT [PK_Form] PRIMARY KEY CLUSTERED
(
    [FormID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Designer_FieldType]    Script Date: 04/10/2013 17:24:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Designer_FieldType](
    [FieldTypeID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [Type] [nvarchar](50) NOT NULL,
    [Description] [nvarchar](250) NULL,
    [Code] [varchar](2) NOT NULL,
 CONSTRAINT [PK_FieldType] PRIMARY KEY CLUSTERED
(
    [FieldTypeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Designer_Applicant]    Script Date: 04/10/2013 17:24:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Designer_Applicant](
    [ApplicantID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Phone] [varchar](50) NOT NULL,
    [Email] [varchar](50) NOT NULL,
    [IsAgreeReceiveUpdates] [bit] NOT NULL,
    [Remarks] [varchar](350) NULL,
    [DevelopmentID] [int] NULL,
    [EnquiryID] [int] NULL,
 CONSTRAINT [PK_Designer_Applicant] PRIMARY KEY CLUSTERED
(
    [ApplicantID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Designer_FormGroup]    Script Date: 04/10/2013 17:24:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Designer_FormGroup](
    [FormGroupID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [GroupName] [nvarchar](100) NULL,
    [Description] [nvarchar](550) NULL,
    [FormID] [int] NULL,
 CONSTRAINT [PK_FormGroup] PRIMARY KEY CLUSTERED
(
    [FormGroupID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Designer_Form_SubCategory]    Script Date: 04/10/2013 17:24:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Designer_Form_SubCategory](
    [FormID] [int] NOT NULL,
    [SubCategoryID] [smallint] NOT NULL,
    [CountryCode] [char](2) NULL,
 CONSTRAINT [PK_Designer_Form_SubCategory] PRIMARY KEY CLUSTERED
(
    [FormID] ASC,
    [SubCategoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Designer_Field]    Script Date: 04/10/2013 17:24:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Designer_Field](
    [FieldID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [FieldName] [nvarchar](50) NOT NULL,
    [FormGroupID] [int] NOT NULL,
    [FieldTypeID] [int] NOT NULL,
    [FormID] [int] NOT NULL,
    [Description] [nvarchar](250) NULL,
    [IsRequiredField] [bit] NULL,
 CONSTRAINT [PK_Field] PRIMARY KEY CLUSTERED
(
    [FieldID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Designer_RepliedForm]    Script Date: 04/10/2013 17:24:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Designer_RepliedForm](
    [RepliedFormID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [FieldID] [int] NOT NULL,
    [Answer] [nvarchar](max) NULL,
    [ApplicantID] [int] NULL,
 CONSTRAINT [PK_RepliedForm] PRIMARY KEY CLUSTERED
(
    [RepliedFormID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Designer_FieldAttribute]    Script Date: 04/10/2013 17:24:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Designer_FieldAttribute](
    [FieldAttributeID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [AttributeName] [nvarchar](250) NULL,
    [FieldID] [int] NULL,
 CONSTRAINT [PK_FieldAttribute] PRIMARY KEY CLUSTERED
(
    [FieldAttributeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Default [DF_Designer_Applicant_IsAgreeReceiveUpdates]    Script Date: 04/10/2013 17:24:36 ******/
ALTER TABLE [dbo].[Designer_Applicant] ADD  CONSTRAINT [DF_Designer_Applicant_IsAgreeReceiveUpdates]  DEFAULT ((0)) FOR [IsAgreeReceiveUpdates]
GO
/****** Object:  ForeignKey [FK_Field_FieldType]    Script Date: 04/10/2013 17:24:36 ******/
ALTER TABLE [dbo].[Designer_Field]  WITH CHECK ADD  CONSTRAINT [FK_Field_FieldType] FOREIGN KEY([FieldTypeID])
REFERENCES [dbo].[Designer_FieldType] ([FieldTypeID])
GO
ALTER TABLE [dbo].[Designer_Field] CHECK CONSTRAINT [FK_Field_FieldType]
GO
/****** Object:  ForeignKey [FK_Field_Form]    Script Date: 04/10/2013 17:24:36 ******/
ALTER TABLE [dbo].[Designer_Field]  WITH CHECK ADD  CONSTRAINT [FK_Field_Form] FOREIGN KEY([FormID])
REFERENCES [dbo].[Designer_Form] ([FormID])
GO
ALTER TABLE [dbo].[Designer_Field] CHECK CONSTRAINT [FK_Field_Form]
GO
/****** Object:  ForeignKey [FK_Field_FormGroup]    Script Date: 04/10/2013 17:24:36 ******/
ALTER TABLE [dbo].[Designer_Field]  WITH CHECK ADD  CONSTRAINT [FK_Field_FormGroup] FOREIGN KEY([FormGroupID])
REFERENCES [dbo].[Designer_FormGroup] ([FormGroupID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Designer_Field] CHECK CONSTRAINT [FK_Field_FormGroup]
GO
/****** Object:  ForeignKey [FK_FieldAttribute_Field]    Script Date: 04/10/2013 17:24:36 ******/
ALTER TABLE [dbo].[Designer_FieldAttribute]  WITH CHECK ADD  CONSTRAINT [FK_FieldAttribute_Field] FOREIGN KEY([FieldID])
REFERENCES [dbo].[Designer_Field] ([FieldID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Designer_FieldAttribute] CHECK CONSTRAINT [FK_FieldAttribute_Field]
GO
/****** Object:  ForeignKey [FK_Designer_Form_SubCategory_Designer_Form]    Script Date: 04/10/2013 17:24:36 ******/
ALTER TABLE [dbo].[Designer_Form_SubCategory]  WITH CHECK ADD  CONSTRAINT [FK_Designer_Form_SubCategory_Designer_Form] FOREIGN KEY([FormID])
REFERENCES [dbo].[Designer_Form] ([FormID])
GO
ALTER TABLE [dbo].[Designer_Form_SubCategory] CHECK CONSTRAINT [FK_Designer_Form_SubCategory_Designer_Form]
GO
/****** Object:  ForeignKey [FK_Designer_FormGroup_Designer_Form]    Script Date: 04/10/2013 17:24:36 ******/
ALTER TABLE [dbo].[Designer_FormGroup]  WITH CHECK ADD  CONSTRAINT [FK_Designer_FormGroup_Designer_Form] FOREIGN KEY([FormID])
REFERENCES [dbo].[Designer_Form] ([FormID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Designer_FormGroup] CHECK CONSTRAINT [FK_Designer_FormGroup_Designer_Form]
GO
/****** Object:  ForeignKey [FK_Designer_RepliedForm_Designer_Applicant]    Script Date: 04/10/2013 17:24:36 ******/
ALTER TABLE [dbo].[Designer_RepliedForm]  WITH CHECK ADD  CONSTRAINT [FK_Designer_RepliedForm_Designer_Applicant] FOREIGN KEY([ApplicantID])
REFERENCES [dbo].[Designer_Applicant] ([ApplicantID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Designer_RepliedForm] CHECK CONSTRAINT [FK_Designer_RepliedForm_Designer_Applicant]
GO
/****** Object:  ForeignKey [FK_RepliedForm_Field]    Script Date: 04/10/2013 17:24:36 ******/
ALTER TABLE [dbo].[Designer_RepliedForm]  WITH CHECK ADD  CONSTRAINT [FK_RepliedForm_Field] FOREIGN KEY([FieldID])
REFERENCES [dbo].[Designer_Field] ([FieldID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Designer_RepliedForm] CHECK CONSTRAINT [FK_RepliedForm_Field]
GO

Select query:

select * from (
SELECT   
  (SELECT Answer =
Stuff((SELECT   ', ' + Answer AS [text()] 
        FROM 
        (SELECT DISTINCT Answer, ApplicantID    FROM (SELECT  [Answer], ApplicantID    
  FROM [Designer_RepliedForm]
  where FieldID = Designer_Field.FieldID and ApplicantID = Designer_Applicant.ApplicantID) as Answer
         ) x
        For XML PATH ('')),1,1,'')  ) as Answer

, Designer_Field.FieldName, Designer_Applicant.DevelopmentID,Designer_Applicant.EnquiryID , Designer_Applicant.ApplicantID, Designer_Applicant.Name, Designer_Applicant.Email,Designer_Applicant.Phone, Designer_Applicant.Remarks
FROM         Designer_RepliedForm INNER JOIN
                      Designer_Applicant ON Designer_RepliedForm.ApplicantID = Designer_Applicant.ApplicantID INNER JOIN
                      Designer_Field ON Designer_RepliedForm.FieldID = Designer_Field.FieldID
                      ) as temp
                      pivot(
                     Max(Answer) for FieldName in ([About Me],[I would like to] ) )
                      as Answers
                      order by ApplicantID

How to create a pivot table in sql server

Outcome:

VendorId   MON         TUE         WED         THU         FRI         SAT         SUN

---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------

SPIKE      900         200         500         300         300         100         400

The short story on how it works using the last query.

select * from DailyIncome                                 -- Colums to pivot

pivot (

   max (IncomeAmount)                                                    -- Pivot on this column

   for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN]))         -- Make colum where IncomeDay is in one of these.

   as MaxIncomePerDay                                                     -- Pivot table alias

where VendorId in ('SPIKE')    

How to concatenate or join multiple rows into single row in sql server using STUFF and XML Path

SELECT ColorCommaDelimitedList =

Stuff((SELECT ', ' + Color AS [text()]

        FROM  

        (SELECT DISTINCT Color FROM AdventureWorks2008.Production.Product

         ) x

        For XML PATH ('')),1,1,'')

 

How to create sub domain and upload with files using C# (Microsoft.Web.Administration)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Administration;
using Microsoft.Web.Administration;
using System.IO;


public partial class _Default : System.Web.UI.Page
{

    private const string SERVER_IP = "192.168.111.112";
  private const int PORT = 80;
  private const string WEB_DOMAIN_PATH = @"F:\\web\domains\{0}\";

    //Live server
    //private const string SERVER_IP = "192.168.111.111";

    protected void Page_Load(object sender, EventArgs e)
    {
   if (!string.IsNullOrEmpty(Request.QueryString["user"]))
   {

    try
    {
     string username = Request.QueryString["user"];
     string status = CreateUserSite(username, "abcdomain.my");

     Response.Write(status);
    }
    catch(Exception ex)
    {
     Response.Write(ex.Message);
    }
   }
   else
   {
    Response.Write("user parameter not supplied");
   }

  
    }


    private string CreateUserSite(string user, string domain)
    {


   string path = string.Format(WEB_DOMAIN_PATH, domain);

        string userpath = path + user;

        string userUrl = user + "." + domain;

        using (ServerManager serverManager = new ServerManager())
        {

            bool siteExists = false;
            int number = serverManager.Sites.Where(p => p.Name.ToLower().Equals(userUrl.ToLower())).Count();

            if (number == 0)
            {
                siteExists = false;
            }
            else
            {
                siteExists = true;
            }

            if (!siteExists)
            {

                //create user directory
                Directory.CreateDirectory(userpath);

                //copy every files from a-base to a new created folder
                FileInfo[] d = new DirectoryInfo(path + @"\a-base").GetFiles();
                foreach (FileInfo fi in d)
                {
                    File.Copy(fi.FullName, userpath + @"\" + fi.Name, true);
                }

                //create a directory
                Directory.CreateDirectory(userpath + @"\swfobject");

                FileInfo[] d1 = new DirectoryInfo(path + @"\a-base\swfobject").GetFiles();
                foreach (FileInfo fi in d1)
                {
                    File.Copy(fi.FullName, userpath + @"\swfobject\" + fi.Name, true);
                }



                //create site
                Site mySite = serverManager.Sites.Add(userUrl, path + user, PORT);
                mySite.ServerAutoStart = true;
                mySite.Applications[0].ApplicationPoolName = domain;

                //create bindings
                mySite.Bindings.Clear();
                mySite.Bindings.Add(string.Format("{0}:{2}:{1}", SERVER_IP, userUrl, PORT ), "http");
                mySite.Bindings.Add(string.Format("{0}:{2}:www.{1}", SERVER_IP, userUrl, PORT), "http");


                Configuration config = serverManager.GetApplicationHostConfiguration();
                ConfigurationSection httpLoggingSection = config.GetSection("system.webServer/httpLogging", userUrl);
                httpLoggingSection["dontLog"] = true;

                serverManager.CommitChanges();

              //  ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "success", "alert('" + userUrl + " created');", true);

            }
            else
            {
                //ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "error", "alert('user exists. Please use other name');", true);
       throw new Exception("user exists. Please use other name");
      }


      return userUrl + " has been successfully created";
        }
    }
}

get database columns using mssql server

SELECT   schemas.name AS [Schema],
         tables.name AS [Table],
         columns.name AS [Column],
         CASE 
             WHEN columns.system_type_id = 34    THEN 'byte[]'
             WHEN columns.system_type_id = 35    THEN 'string'
             WHEN columns.system_type_id = 36    THEN 'System.Guid'
             WHEN columns.system_type_id = 48    THEN 'byte'
             WHEN columns.system_type_id = 52    THEN 'short'
             WHEN columns.system_type_id = 56    THEN 'int'
             WHEN columns.system_type_id = 58    THEN 'System.DateTime'
             WHEN columns.system_type_id = 59    THEN 'float'
             WHEN columns.system_type_id = 60    THEN 'decimal'
             WHEN columns.system_type_id = 61    THEN 'System.DateTime'
             WHEN columns.system_type_id = 62    THEN 'double'
             WHEN columns.system_type_id = 98    THEN 'object'
             WHEN columns.system_type_id = 99    THEN 'string'
             WHEN columns.system_type_id = 104   THEN 'bool'
             WHEN columns.system_type_id = 106   THEN 'decimal'
             WHEN columns.system_type_id = 108   THEN 'decimal'
             WHEN columns.system_type_id = 122   THEN 'decimal'
             WHEN columns.system_type_id = 127   THEN 'long'
             WHEN columns.system_type_id = 165   THEN 'byte[]'
             WHEN columns.system_type_id = 167   THEN 'string'
             WHEN columns.system_type_id = 173   THEN 'byte[]'
             WHEN columns.system_type_id = 175   THEN 'string'
             WHEN columns.system_type_id = 189   THEN 'long'
             WHEN columns.system_type_id = 231   THEN 'string'
             WHEN columns.system_type_id = 239   THEN 'string'
             WHEN columns.system_type_id = 241   THEN 'string'
             WHEN columns.system_type_id = 241   THEN 'string'
         END AS [Type],
         columns.is_nullable AS [Nullable]


FROM              sys.tables tables
    INNER JOIN    sys.schemas schemas ON (tables.schema_id = schemas.schema_id )
    INNER JOIN    sys.columns columns ON (columns.object_id = tables.object_id) 
 

WHERE     tables.name <> 'sysdiagrams' 
    AND   tables.name <> 'dtproperties' 

ORDER BY [Schema], [Table], [Column], [Type]

how to generate json file from .net object

    public class data_city
    {
        private string _city;

        public string city
        {
            get { return _city; }
        }

        public data_city(string city)
        {
            _city = city;
        }

    }


    public class data_state
    {
        public data_state(string id, string name)
        {
            _id = id;
            _name = name;
        }

        private string _id;

        public string id
        {
            get { return _id; }
        }
        private string _name;

        public string name
        {
            get { return _name; }
        }

        private List<data_city> _Cities = new List<data_city>();

        public List<data_city> cities
        {
            get { return _Cities; }
            set { _Cities = value; }
        }

    }


    private void GenerateJsonCities()
    {

        var state = from s in developerDataContext.States
                                orderby s.DisplayOrder
                                select new { id = s.StateCode, name = s.Description };



        List<data_state> dsl = new List<data_state>(); 
        foreach (var s in state)
        {
            data_state ds = new data_state(s.id, s.name);


            List<City> ss  = developerDataContext.Cities.Where(p => p.StateCode == s.id).OrderBy(p => p.City1).ToList();

            foreach (City c in ss)
            {
                ds.cities.Add(new data_city(c.City1));
            }

            dsl.Add(ds);

        }
       
           

        JavaScriptSerializer serializer = new JavaScriptSerializer();

        string json = serializer.Serialize(dsl);

        string city_json = string.Format("var data_state = {0};", json);

        File.WriteAllText(MapPath("~/scripts/referenceData/state_city.js"), city_json);

       

    }

how to get optimum sizes of resized image with proportion in .NET

Private _MaxWidth As Integer
Private _MaxHeight As Integer

Public Property MaxWidth() As Integer
Get
Return _MaxWidth
End Get
Set(ByVal value As Integer)
_MaxWidth = value
End Set
End Property

Public Property MaxHeight() As Integer
Get
Return _MaxHeight
End Get
Set(ByVal value As Integer)
_MaxHeight = value
End Set
End Property



Private Function CalculateOptimumImageSize(ByVal src As System.Drawing.Image) As Integer()
Dim sizeImgDim As Integer() = New Integer(1) {}

sizeImgDim(0) = src.Width
sizeImgDim(1) = src.Height

'if the max and min are larger than the original size, therefore follow the original
If sizeImgDim(0) > _MaxWidth OrElse sizeImgDim(1) > _MaxHeight Then
Dim imageRatio As Decimal = CDec(sizeImgDim(0)) / CDec(sizeImgDim(1))
Dim w As Decimal = imageRatio * CDec(_MaxHeight)
If w > _MaxWidth Then

sizeImgDim(0) = _MaxWidth
Else
sizeImgDim(0) = Convert.ToInt32(w)
End If


Dim h As Decimal = CDec(_MaxWidth) / imageRatio
If h > _MaxHeight Then

sizeImgDim(1) = _MaxHeight
Else

sizeImgDim(1) = Convert.ToInt32(h)

End If
End If
Return sizeImgDim
End Function

Clean up html code and words tag in .NET

Private Shared Function CleanHTML(ByVal html As String) As String
html = Regex.Replace(html, "<[/]?(font|span|xml|del|ins|[ovwxp]:\w+)[^>]*?>", "", RegexOptions.IgnoreCase)
html = Regex.Replace(html, "<([^>]*)(?:class|lang|style|size|face|[ovwxp]:\w+)=(?:'[^']*'|""[^""]*""|[^\s>]+)([^>]*)>", "<$1$2>", RegexOptions.IgnoreCase)
html = Regex.Replace(html, "<([^>]*)(?:class|lang|style|size|face|[ovwxp]:\w+)=(?:'[^']*'|""[^""]*""|[^\s>]+)([^>]*)>", "<$1$2>", RegexOptions.IgnoreCase)
Return html
End Function

How to capitalize every first letter using .NET

Public Shared Function Capitalize(ByVal value As String) As String
Return System.Globalization.CultureInfo.CurrentCulture.TextInfo.ToTitleCase(value)
End Function

Regex for strong password implementation

Advance password

(?=^.{12,}$)((?=.*\d)(?=.*\W+))(?![.\n])(?=.*[A-Z])(?=.*[a-z]).*$

Passwords will contain at least (1) upper case letter
Passwords will contain at least (1) lower case letter
Passwords will contain at least (1) number or special character
Passwords will contain at least (12) characters in length

Basic password

(?=^.{12,}$)((?=.*\d)(?=.*\W+))(?![.\n])(?=.*[a-z]).*$

Passwords will contain at least (1) letter
Passwords will contain at least (1) number or special character
Passwords will contain at least (12) characters in length

how to add mata keyword & meta description in ASP.NET?

There is no easy way to add but I have a simple method to introduce


pageEx.cs

using System;
using System.Data;
using System.Configuration;
using
System.Web;
using System.Web.Security;
using System.Web.UI;
using
System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public class PageEx : System.Web.UI.Page
{

private HtmlMeta
metaDescription = new HtmlMeta();
private HtmlMeta metaKeywords = new
HtmlMeta();

public string Description
{
get { return
metaDescription.Content; }
set { metaDescription.Content = value; }
}


public string[] Keywords
{
get
{
if
(metaKeywords.Content == null)
{
return new string[] { "" };
}
else
{
return metaKeywords.Content.Split(new char[] { ',' });
}
}

set
{
if (value != null)
{
metaKeywords.Content =
string.Join(",", value);
}
}
}


public PageEx()
{
Init += new EventHandler(PageEx_Init);
}

void PageEx_Init(object
sender, EventArgs e)
{
//Add the description Meta control
metaDescription.Name = "description";
Page.Header.Controls.Add(metaDescription);
//Add the keywords Meta
control
metaKeywords.Name = "keywords";
Page.Header.Controls.Add(metaKeywords);
}


}

someasp.aspx

protected void Page_Load(object sender, EventArgs e)
{

this.Description = "the power 123";
this.Keywords = new string[] {
"Enquiries", "enquiry", "contact", "contacts", "email", "tel" };

}

Hope this helps :)

a simple method to manipulate data using sql using .net

public static DataTable ExecuteSQL(string sql, Dictionary parameters)
{
DataTable dt = new DataTable("DataTable");
using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["localConnectionString"].ConnectionString))
{
myConnection.Open();
SqlCommand mySqlCommand = new SqlCommand(sql, myConnection);

if (parameters != null)
{
foreach (KeyValuePair p in parameters)
{
mySqlCommand.Parameters.AddWithValue(p.Key, p.Value);
}
}

using (SqlDataReader datareader = mySqlCommand.ExecuteReader())
{
dt.Load(datareader);
}
}
return dt;
}

//insert a tag record
Dictionary param = new Dictionary();
param.Add("@TagName", txtTagName.Text);
ExecuteSQL("INSERT TOP (200) INTO Tag(TagName) VALUES (@TagName)", param);

add or modify table column using sql server database script

it is always recommended that you use script to run for modifucation rather set by using interface for this case.

Here is the trick


To add new column:

ALTER TABLE Customer ADD IsFeatured bit not NULL default 0

To modify existing column such as data type:

ALTER TABLE news ALTER COLUMN title VARCHAR(150) NOT NULL

How to run VB code and C# code in appCode folder?

In the web.config, under compilation

<compilation debug="true" strict="false" explicit="true">

add,

<codeSubDirectories>
<add directoryName="VBCode" />
<add directoryName="CSCode" />
</codeSubDirectories>

VBCode and CSCode are the folders in the AppCode.

ASP.NET move to next record or move to previous record

Basically, ASP.NET does not have move next or move previous function, therefore you need to manually use a for loop to determine the position of the target index and then use position + 1 or position -1 to get the next or previous record.

If you are developing a windows form then you are lucky you can use BindingContext[dataTable].Position + 1 or BindingContext[dataTable].Position - 1.

Well, we are talking about ASP.NET here, if we are populating a few hundreds items and use for loop to get the position and I think it is fine. Or u might be using datatable something like

DataTable dt = new DataTable();
dt.Rows.IndexOf(datarow);

to get the index and from there u manipulate it.

However what if we have 1 million of records in the table? That would be crazy!

The more efficient way is to use over keyword which only available in SQL Server 2005 or later. The idea is to get the previous record and the next record given the record identity. I just have to get 2 records and I just want the first record as previous and second record as next. So here is the trick.

subcategoryid, name

31	Game Downloads
32	Computers & Accessories
33	Software
34	PC Games
35	Computer Components
36	Office Products & Supplies

select top(1) row_number() over (order by subcategoryid desc) row, subcategoryid, name from subcategory where subcategoryid<33
union
select top(1) row_number() over (order by subcategoryid) row, subcategoryid, name from subcategory where subcategoryid>33

results:

1 32 Computers & Accessories
1 34 PC Games

p/s please note that this solution only applicable for int column having auto numbering enabled only. the subcategoryid is the primary key, auto number and numaric

SQL upcoming date

if you want to get the up coming listing such as event you can do a SQL query like this:

SELECT EventID, EventName, StartDate, EndDate, EventTime, EventLink
FROM Events
WHERE (StartDate BETWEEN GETDATE() AND GETDATE() + 45) AND (GETDATE() BETWEEN StartDate AND EndDate + 1)
ORDER BY StartDate

get data from excel using linq .net

You might want to read excel file and import the data from the excel into your .net application. Therefore you need a service that provide you this functionality. Here is the class that you can use it.

I suggest you to import the data and store into a DataTable and from there you can start to manipulate it. You can also use a linq to query the data. Is that simple and easy?

Initialization

PrivatePropertyExcel ppe;
DataTable currentExcelDataTable;

private void btnTestConnection_Click(object sender, EventArgs e)
{
try
{
if (openFileDialog1.ShowDialog() == DialogResult.OK && txtSheetName.Text != "")
{

ppe = new PrivatePropertyExcel(openFileDialog1.FileName, txtSheetName.Text);
currentExcelDataTable = ppe.ExcelProvider.GetDataTable();
gridSource.DataSource = currentExcelDataTable;
gridTarget.DataSource = null;


btnValidate.Enabled = true;
txtStatus.Text = "";
}
else
{
if (txtSheetName.Text == "")
{
MessageBox.Show("Please enter sheet name");
}
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}

Calling function

public List<string> GetPropertyType(int columnIndex)
{
List<string> propertyTypeList = new List<string>();

var propertyTypes =
(from e in ppe.ExcelProvider
where e.GetString(columnIndex).Trim() != ""
select e.GetString(columnIndex).Trim()).Distinct();

foreach (string pt in propertyTypes)
{
if (!propertyTypeList.Contains(pt))
{
propertyTypeList.Add(pt);
}
}

return propertyTypeList;
}

Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Collections;
using System.Data;

namespace ReplicatorServices
{
public class ExcelRow
{
List<object> columns;

public ExcelRow()
{
columns = new List<object>();
}

internal void AddColumn(object value)
{
columns.Add(value);
}

public object this[int index]
{
get { return columns[index]; }
}

public string GetString(int index)
{
if (columns[index] is DBNull)
{
return null;
}
return columns[index].ToString();
}

public int Count
{
get { return this.columns.Count; }
}
}

public class ExcelProvider:IEnumerable<ExcelRow>
{
private string sheet;
private string filePath;
private List<ExcelRow> rows;
private string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties= ""Excel 8.0;HDR=YES;""";

public ExcelProvider()
{
rows = new List<ExcelRow>();
}

public static ExcelProvider Create(string filePath, string sheet)
{
ExcelProvider provider = new ExcelProvider();
provider.sheet = sheet;
provider.filePath = filePath;
return provider;
}

public DataTable GetDataTable()
{
DataTable dt= new DataTable();
connectionString = string.Format(connectionString, filePath);
rows.Clear();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select DISTINCT * from [" + sheet + "$] ";
using (OleDbDataReader reader = cmd.ExecuteReader())
{
dt.Load(reader);
}
}
}

return dt;
}

public DataTable GetDataTable(string sqlCommand)
{
DataTable dt = new DataTable();
connectionString = string.Format(connectionString, filePath);
rows.Clear();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sqlCommand;
using (OleDbDataReader reader = cmd.ExecuteReader())
{
dt.Load(reader);
}
}
}

return dt;
}
public List<string> GetColumnsName()
{
List<string> columnlist = new List<string>();
connectionString = string.Format(connectionString, filePath);
rows.Clear();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from [" + sheet + "$]";
using (OleDbDataReader reader = cmd.ExecuteReader())
{
for (int i = 0; i < reader.FieldCount; i++)
{
columnlist.Add(reader.GetName(i).Trim());
}
}
}
}

return columnlist;
}

private void Load()
{
connectionString = string.Format(connectionString, filePath);
rows.Clear();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from [" + sheet + "$]";
using (OleDbDataReader reader = cmd.ExecuteReader())
{

while (reader.Read())
{
ExcelRow newRow = new ExcelRow();
for(int count = 0; count < reader.FieldCount; count++) {
newRow.AddColumn(reader[count]);
}
rows.Add(newRow);
}
}
}
}
}

public IEnumerator<ExcelRow> GetEnumerator()
{
Load();
return rows.GetEnumerator();
}

IEnumerator IEnumerable.GetEnumerator()
{
Load();
return rows.GetEnumerator();
}

}
}

Get html tag using regex .net

Let say you want to get a specific tag from the html, you need to use regex to scan and grab it. here is the example :)

private string FixedImgHtml(string html, int fixedwidth)
{
string input = html;
MatchCollection mc = Regex.Matches(input, "<img[a-zA-Z0-9_\\^\\$\\.\\\\{\\[\\}\\]\\(\\)\\*\\+\\?\\\\~`!@#%&-=;:'\",/\\n\\s]*>", RegexOptions.IgnoreCase);
foreach (Match m in mc)
{
input = input.Replace(m.Value, m.Value + " onload='AutoImageResizing(this, "+ fixedwidth.ToString()+")'");
}
return input;
}