USE [MyDB]
GO
/****** Object:  Table [dbo].[Person]    Script Date: 01/05/2009 01:34:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Person](
	[PersonID] [int] IDENTITY(1,1) NOT NULL,
	[PersonEmail] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[PersonName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[PersonSex] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[PersonDOB] [datetime] NULL,
	[PersonImage] [image] NULL,
	[PersonImageType] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

CREATE PROC sp_person_isp
@PersonEmail varchar(255),
@PersonName varchar(255),
@PersonSex char(1),
@PersonDOB datetime,
@PersonImage image,
@PersonImageType varchar(255)
AS
BEGIN
	Insert into person
	(PersonEmail,PersonName,PersonSex,
	PersonDOB,PersonImage,PersonImageType)
	Values
	(@PersonEmail,@PersonName,@PersonSex,
	@PersonDOB,@PersonImage,@PersonImageType)
END

 public void AddPerson(object sender, EventArgs e)
    {
        int imageSize = 0;
        string imageType = string.Empty;
        Stream ImgStream;

        //get the size of the image
        imageSize = PersonImage.PostedFile.ContentLength;
       //get the image type
        imageType = PersonImage.PostedFile.ContentType;
        //read the image
        ImgStream = PersonImage.PostedFile.InputStream;
        byte[] ImageContent = new byte[imageSize + 1];
        int intStatus = 0;
        intStatus = ImgStream.Read(ImageContent, 0, imageSize);
        //create an instance of the connection
        SqlConnection mycon = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
        SqlCommand myCommand = new SqlCommand("sp_person_isp", mycon);
        //set the command type to stored procedure
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("@PersonEmail", SqlDbType.VarChar, 255);
        myCommand.Parameters["@PersonEmail"].Value = txtPersonMail.Text;
        myCommand.Parameters.Add("@PersonName", SqlDbType.VarChar, 255);
        myCommand.Parameters["@PersonName"].Value = txtPersonName.Text;
        myCommand.Parameters.Add("@PersonSex", SqlDbType.Char, 1);
        if (sexMale.Checked)
        {
            myCommand.Parameters["@PersonSex"].Value ="M";
        }
        else
        {
            myCommand.Parameters["@PersonSex"].Value = "F";
        }
        myCommand.Parameters.Add("@PersonDOB", SqlDbType.DateTime);
        myCommand.Parameters["@PersonDOB"].Value = Convert.ToDateTime(txPersonDOB.Text);
        myCommand.Parameters.Add("@PersonImage", SqlDbType.Image);
        myCommand.Parameters["@PersonImage"].Value = ImageContent;
        myCommand.Parameters.Add("@PersonImageType", SqlDbType.VarChar,255);
        myCommand.Parameters["@PersonImageType"].Value = imageType;
        try
        {
            mycon.Open();
            myCommand.ExecuteNonQuery();
            mycon.Close();
            Response.Write("Person added successfully");
        }
        catch (Exception ex)
        {
            Response.Write("Error Details"+ ex.Message.ToString());
        }
    }