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.
Monday, June 10, 2019
Wednesday, April 10, 2013
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,'')
Thursday, May 31, 2012
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";
}
}
}
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";
}
}
}
Thursday, April 26, 2012
Wednesday, August 24, 2011
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]
Subscribe to:
Posts (Atom)