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";
        }
    }
}

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]

Wednesday, February 23, 2011

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);

       

    }