mvc

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
using Mvctest2.Models;



namespace Mvctest2.Controllers
{
public class TestController : Controller
{
//
// GET: /Test/

dbxwalkerEntities1 dbc = new dbxwalkerEntities1();
public ActionResult Index()
{

return View(dbc.tbl_Categories.ToList());
}


public ActionResult LogOn()
{
//MembershipCreateStatus status1;
// MembershipUser users = Membership.CreateUser("nancy90", "123456","nancy@dhhd.com","hi","hrr",true,out status1);
//MembershipUser users = Membership.GetUser("durgesh");

return View();


}

//[HttpPost]
//public ActionResult LogOn(FormCollection formCollection)
//{

// //foreach (string _formData in formCollection)
// //{
// // ViewData[_formData] = formCollection[_formData];
// //}
// ViewData["txtPassword"] = formCollection["txtPassword"];


// return View();
// //if (Membership.ValidateUser(formCollection.GetValue("txtUsername").AttemptedValue, formCollection.GetValue("txtPassword").AttemptedValue))
// //{
// // FormsAuthentication.SetAuthCookie(formCollection.GetValue("txtUsername").AttemptedValue, false);
// // return RedirectToAction("Index", "Home");
// //}
// //else
// //{
// // MembershipUser user = Membership.GetUser(formCollection.GetValue("txtUsername").AttemptedValue);
// // if (user.IsLockedOut)
// // {
// // ModelState.AddModelError("Error", "User is Locked");
// // }
// // else
// // {
// // ModelState.AddModelError("Error", "Invalid User");
// // }
// // return View();
// //}
//}



[HttpPost]
public ActionResult LogOn(FormCollection formCollection)
{


ViewData["txtPassword"] = formCollection["txtPassword"];
MembershipCreateStatus status1;


if (Membership.ValidateUser(formCollection["txtUsername"], formCollection["txtPassword"]))
{



return RedirectToAction("Index", "Home");


}
else
{
MembershipUser user = Membership.GetUser(formCollection["txtUsername"]);
if (user != null)
{

if (user.IsLockedOut)
{
ModelState.AddModelError("Error", "User is Locked");
}
else
{
ModelState.AddModelError("Error", "Invalid User");
}
}
else
{
MembershipUser newUser = Membership.CreateUser(formCollection[0], formCollection[1], "durgesh@gmail.com", "hi", "hi", true, out status1);


if (status1.ToString() == "Success")
{
newUser.UnlockUser();

FormsAuthentication.SetAuthCookie(formCollection["txtUsername"], false);

}
else
{
ModelState.AddModelError("Error", status1.ToString());
}

}
return View();
}
}

}
}

save multiple record into table using xml in sql server

CREATE PROC [dbo].[UTIL_sp_GetAllUniqueNames]
@Str_Xml_In NVARCHAR(4000)
, @Str_Xml_Schema NVARCHAR(4000)
AS
BEGIN
DECLARE @Handle INT
--Create an internal representation of the XML document.
EXEC Sp_Xml_PrepareDocument @Handle OUTPUT, @Str_Xml_In
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT PresentationID, PresentationUniqueName FROM aS_tblPresentations WHERE PresentationID IN
(
SELECT * FROM OPENXML (@Handle, @Str_Xml_Schema, 1) WITH (PID INT)
)
EXEC Sp_Xml_RemoveDocument @Handle
END
GO

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



CREATE PROC [dbo].[as_sp_InsertContactsByPresentationID]
(
@p_PresentationID INT
, @p_XmlContactList NVARCHAR(4000)
, @p_Share BIT = 0
, @Message NVARCHAR(4000) = NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @m_XmlDocHandle INT, @m_MemberID INT
SELECT @m_MemberID = MemberID FROM aS_tblPresentations WHERE PresentationID = @p_PresentationID
IF((@m_MemberID IS NULL) OR (@m_MemberID = 0))
BEGIN
RAISERROR('Invalid PresentationID. Contacts could not be saved.', 16, 1)
RETURN
END
EXEC SP_XML_PREPAREDOCUMENT @m_XmlDocHandle OUTPUT, @p_XmlContactList
IF (@p_Share = 1)
BEGIN
INSERT INTO as_WS_tblShareEmails (EmailAddress, DisplayName, PresentationID)
SELECT *, @p_PresentationID FROM OPENXML (@m_XmlDocHandle, '/ROOT/Contact', 1) WITH (Email NVARCHAR(100), Name NVARCHAR(100))
WHERE Email NOT IN (SELECT EmailAddress FROM as_WS_tblShareEmails WHERE PresentationID = @p_PresentationID)
IF(Select Private from as_tblPresentations where PresentationID = @p_PresentationID)=1
BEGIN
INSERT INTO as_tblSharePrivatePresentations(PresentationID,ShareDateTime,EmailID)
SELECT @p_PresentationID,getdate(),* FROM OPENXML(@m_XmlDocHandle, '/ROOT/Contact', 1) WITH(Email NVARCHAR(100))
END

END
INSERT INTO aS_tblMemberContacts(ContactEmail,ContactName,MemberID)
SELECT *, @m_MemberId FROM OPENXML (@m_XmlDocHandle, '/ROOT/Contact', 1) WITH (Email NVARCHAR(100), Name NVARCHAR(100))
WHERE Email NOT IN
(
SELECT ContactEmail FROM aS_tblMemberContacts WHERE MemberID = @m_MemberID
)
EXEC SP_XML_REMOVEDOCUMENT @m_XmlDocHandle
UPDATE aS_tblPresentations SET ShareEmailMessage = @Message WHERE PresentationID = @p_PresentationID
SET NOCOUNT OFF
END
GO
---------------------


CREATE PROCEDURE [dbo].[cs_PostMetadata_Update]
@PostID int,
@MetadataList ntext,
@SettingsID int
AS
SET Transaction Isolation Level Read UNCOMMITTED
delete from cs_PostMetadata where PostID = @PostID
if @MetadataList is null --or len(ltrim(rtrim(@MetadataList))) = 0
return
declare @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @MetadataList
insert into cs_PostMetaData (PostID, MetaKey, MetaType, MetaValue)
select @PostID, M.[key], M.[type], M.[value]
from openxml(@idoc, '/entries/entry', 1)
with ([key] nvarchar(50), [type] nvarchar(50), [value] nvarchar(50)) as M
EXEC sp_xml_removedocument @idoc
GO
/****** Object: StoredProcedure [dbo].[cs_PostMetadata_Get] Script Date: 02/18/2010 23:25:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO



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

CREATE PROC [dbo].[aS_sp_SaveContacts]
(
@contactListXml VARCHAR(MAX)
, @UserId bigint
, @ContactList VARCHAR(2000) = ''
, @SendInvite BIT =null
, @PresentationID BIGINT = 0
)
AS
BEGIN
DECLARE @hdoc int
DECLARE @ContactName VARCHAR(50)

exec sp_xml_preparedocument @hdoc output , @contactListXml

INSERT INTO aS_tblMemberContacts
SELECT @UserId , contacts.contactname , contacts.contact
FROM OPENXML(@hdoc , '/contacts/contact' , 2)
WITH (
contactname VARCHAR(50) '@contactName',
contact VARCHAR(50) '@contactemail'
) AS contacts
where
not exists (SELECT * FROM aS_tblMemberContacts WHERE ContactEmail = contacts.contact AND MemberID = @UserId )
-- to exclude the emailids which has been unscribed globally -- Modified by Amrik - 001
and not exists (SELECT EmailID FROM as_tblGlobalUnsubscribe WHERE EmailID = contacts.contact)

IF(@SendInvite =1)
BEGIN
INSERT INTO aS_tblSendInvites
SELECT @UserId , contacts.contactname , contacts.contact,getdate()
FROM OPENXML(@hdoc , '/contacts/contact' , 2)
WITH (
contactname VARCHAR(50) '@contactName',
contact VARCHAR(50) '@contactemail'
) AS contacts
where
not exists (SELECT * FROM aS_tblSendInvites WHERE ContactEmail = contacts.contact AND MemberID = @UserId )
-- to exclude the emailids which has been unscribed globally -- Modified by Amrik - 001
and not exists (SELECT EmailID FROM as_tblGlobalUnsubscribe WHERE EmailID = contacts.contact)
END

EXEC SP_XML_REMOVEDOCUMENT @hdoc

SELECT C.ContactEmail,ISNULL(ISNULL(M.MemberID,A.MemberID),0) as memberid,ISNULL(E.PresentationShare,1)as presentationShare FROM aS_tblMemberContacts C
LEFT OUTER JOIN as_tblmembers M ON C.ContactEmail = M.EmailID
LEFT OUTER JOIN aS_tblAlternateEmails A ON C.ContactEmail = A.EmailAddress
--LEFT OUTER JOIN as_tblemailsettings E ON E.MemberID = M.MemberID
LEFT OUTER JOIN as_tblemailsettings E on (C.ContactEmail = E.emailid or E.MemberID = M.MemberID or E.MemberID = A.MemberID)
WHERE C.MemberID = @UserId--11259
AND C.ContactEmail IN (SELECT rtrim(ltrim(value)) FROM dbo.aS_fn_Split(@ContactList,','))

-- to exclude the emailids which has been unscribed globally -- Modified by Amrik - 001
and C.ContactEmail NOT IN (SELECT EmailID FROM as_tblGlobalUnsubscribe)
EXEC dbo.as_sp_PremiumShareCount @PresentationID,@@ROWCOUNT
END
GO

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

as_sp_GetResetDetails 'test@authorgen.com','test',''*/
CREATE procedure [dbo].[as_sp_GetResetDetails]
@UserId VARCHAR(100) = Null ,
@Password VARCHAR(100) = Null,
@PIDS varchar(1000)
AS
BEGIN
DECLARE @Handle INT
DECLARE @MemberId INT
IF(NOT EXISTS(SELECT MemberID FROM aS_tblMembers WHERE EmailID = @UserId AND [Password] = @Password AND (UserType = 'A' OR UserType = 'I')))
BEGIN
RAISERROR('Login Failed', 16, 1)
RETURN
END
EXEC sp_Xml_PrepareDocument @Handle OUTPUT , @PIDS
SELECT PresentationID,Prefix,PresentationFileName,AmazonFileName from as_tblpresentations where presentationid in
(select id from OPENXML (@Handle,'/PIDS/PID') with (id INT)tbl_xml)
EXEC sp_xml_removedocument @handle
END

get xml value and insert record using xml in sql server 2008

Declare @XmlOutput xml
set @XmlOutput=(SELECT '-' as Detail,12 as DesNoteType,ActionTrackerId,@AdminloginId as LoginId,GETDATE() as LastUpdated,'Deleted User – Item Unassigned' as Activity,
getdate() as ActivityDate,0 as IsDeleted,7 as ActivityType,'-' as StatusActivity,100 as StatusActivityType,0 as Edited , 0 as EditedBy,GETDATE() as EditedDate,0 as Parentid FROM tblATActionTracker where LoginId=@Loginid and Completed in(0,5,6) FOR XML AUTO,Root('ActionTrack'), ELEMENTS)
select @XmlOutput

DECLARE @idoc INT

EXEC sp_xml_preparedocument @idoc OUTPUT, @XmlOutput
INSERT INTO tblATDesNote(Detail,DesNoteType,ActionTrackerId,LoginId,LastUpdated,Activity,ActivityDate,IsDeleted,ActivityType,StatusActivity,StatusActivityType,Edited,EditedBy,EditedDate,Parentid)

SELECT Detail,DesNoteType,ActionTrackerId,LoginId,LastUpdated,Activity,ActivityDate,IsDeleted,ActivityType,StatusActivity,StatusActivityType,Edited,EditedBy,EditedDate,Parentid
FROM Openxml( @idoc, '/ActionTrack/tblATActionTracker', 3) WITH (Detail varchar(100),DesNoteType tinyint ,ActionTrackerId bigint,LoginId INT,LastUpdated datetime,Activity varchar(50),ActivityDate datetime,IsDeleted bit,ActivityType int,StatusActivity varchar(50),StatusActivityType int,Edited int,EditedBy int,EditedDate datetime ,Parentid int)

get record in xml and multiple record insert into table using xml in sql server

Declare @XmlOutput xml
set @XmlOutput=(SELECT IDNumber,LoginId FROM tblTest where LoginId=2 FOR XML AUTO,Root('Product'), ELEMENTS)
select @XmlOutput
---return record in xml format

DECLARE @idoc INT

EXEC sp_xml_preparedocument @idoc OUTPUT, @XmlOutput
INSERT INTO tblTest(IDNumber,LoginId)

SELECT IDNumber,LoginId
FROM Openxml( @idoc, '/Product/tblTest', 3) WITH ( IDNumber int, LoginId int )

SET NOCOUNT OFF

Create XML Files Out Of SQL Server With SSIS And FOR XML Syntax

CREATE PROC prMusicCollectionXML
AS
DECLARE @XmlOutput xml
SET @XmlOutput = (SELECT ArtistName,AlbumName,YearReleased FROM Album
join Artist ON Album.ArtistID = Artist.ArtistID
FOR XML AUTO, ROOT('MusicCollection'), ELEMENTS)

SELECT @XmlOutput
go

save multiple record using xml formate in sql server

CREATE PROC dbo.as_sp_InsertContacts
(
@v_XML nvarchar(4000),
@v_UserId int
)
AS
BEGIN

SET NOCOUNT ON
DECLARE @idoc INT

EXEC sp_xml_preparedocument @idoc OUTPUT, @v_XML
INSERT INTO aS_tblMemberContacts(CONTACTEMAIL,CONTACTNAME,MEMBERID)

SELECT * FROM OPENXML (@idoc, '/ROOT/Contact',1)WITH (Email varchar(100), UName nvarchar(100),UID INT)
WHERE Email NOT IN
(
SELECT CONTACTEMAIL FROM aS_tblMemberContacts WHERE MEMBERID=@v_UserId
)
SET NOCOUNT OFF

END
--------------------

CREATE PROCEDURE aS_sp_SaveReferralContactList
(
@v_XML nvarchar(4000),
@v_UserId bigint
)
AS
BEGIN

DECLARE @idoc INT
DECLARE @ContactName VARCHAR(50)
Declare @EmailCount int
Declare @UpdateRowCount int
Declare @UserEarn int
set @UpdateRowCount=0
set @EmailCount=0
set @UserEarn=0

BEGIN TRANSACTION

BEGIN TRY

-- DECLARE @idoc INT

EXEC sp_xml_preparedocument @idoc OUTPUT, @v_XML

INSERT INTO as_tblReferContacts(ReferEmail,ReferContact,MemberID)
SELECT Email,UName, @v_UserId
FROM OPENXML (@idoc, '/ROOT/Contact',1)WITH (Email varchar(100), UName nvarchar(100),UID INT) xmlContacts
LEFT JOIN as_tblReferContacts RC ON RC.MEMBERID=@v_UserId AND RC.ReferEmail= xmlContacts.Email
LEFT OUTER JOIN
aS_tblMembers AS MD ON MD.AccountStatusId = 1 AND MD.EmailID = xmlContacts.Email LEFT OUTER JOIN
aS_tblAlternateEmails AS AE ON AE.EmailAddress = xmlContacts.Email AND AE.Verified = 1 LEFT OUTER JOIN
as_tblEmailSettings AS ES ON ES.MemberID = ISNULL(MD.MemberID, AE.MemberID) LEFT OUTER JOIN
aS_tblMembers AS M2 ON M2.MemberID = ISNULL(MD.MemberID, AE.MemberID)
left outer join aS_tblBlockedEmails as BE on BE.Emailid=xmlContacts.Email and BE.intactivestatus=2
WHERE RC.ReferID IS NULL AND M2.MemberID IS NULL

SET @EmailCount=@@ROWCOUNT

UPDATE RC SET RC.ReferDate = GETDATE()
FROM OPENXML (@idoc, '/ROOT/Contact',1)WITH (Email varchar(100), UName nvarchar(100),UID INT) xmlContacts
INNER JOIN as_tblReferContacts RC
ON RC.MEMBERID=@v_UserId AND RC.ReferEmail= xmlContacts.Email AND RC.ReferDate0)
BEGIN
insert into as_tblEarnasCredit (EarnasCredit,AsCreditTypeID,MemberID,EarnDate)values (@EmailCount,2,@v_UserId,getdate())
if not exists(select memberid from as_tblasCreditBalance where memberid=@v_UserId)
begin
insert into as_tblasCreditBalance(MemberID,EarnAsCredit) values (@v_UserId,@EmailCount)
End
else
begin
select @UserEarn=isnull(EarnAsCredit,0) from as_tblasCreditBalance where memberid=@v_UserId
update as_tblasCreditBalance set EarnAsCredit=@UserEarn+@EmailCount where memberid=@v_UserId
end

END
if(@UpdateRowCount>0)
BEGIN
insert into as_tblEarnasCredit (EarnasCredit,AsCreditTypeID,MemberID,EarnDate)values (@UpdateRowCount,2,@v_UserId,getdate())
select @UserEarn=isnull(EarnAsCredit,0) from as_tblasCreditBalance where memberid=@v_UserId
update as_tblasCreditBalance set EarnAsCredit=@UserEarn+@UpdateRowCount where memberid=@v_UserId

END


EXEC SP_XML_REMOVEDOCUMENT @idoc


COMMIT TRANSACTION

END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END

Data Relation and bind data in inner,outer repeater Example

 private void GetEmailNotification(Int64 memberID)
        {
            try
            {
                dsMailNotification = objEmailNotifications.GetEmailNotification(memberID);
                String notificationSetting = String.Empty;
                if (dsMailNotification.Tables[2].Rows.Count > 0)
                {
                    notificationSetting = Convert.ToString(dsMailNotification.Tables[2].Rows[0]["NotificationSetting"]);
                }
                if (!notificationSetting.Equals(String.Empty))
                {
                    objNotificationList = objEmail.ReadNotificationSetting(notificationSetting);
                   
                }
// Example of data relation between dataset
                dsMailNotification.Relations.Add("NotificationGroupID", dsMailNotification.Tables[0].Columns["NotificationGroupID"], dsMailNotification.Tables[1].Columns["NotificationGroupID"]);
                rptEmailNotification.DataSource = dsMailNotification.Tables[0];
                rptEmailNotification.DataBind();
                dsMailNotification.Dispose();

            }
            catch (Exception ex)
            {
                _objError.WriteLog("Error on GetEmailNotification", ex.Message,ex.StackTrace);
            }
        }

 protected void rptEmailNotification_ItemDataBound(object sender, RepeaterItemEventArgs e)
        {
            try
            {
                if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
                {
                    string groupName = Convert.ToString(DataBinder.Eval(e.Item.DataItem, "GroupName"));
                    HtmlContainerControl divhappen = (HtmlContainerControl)e.Item.FindControl("divHappens");
                     int groupId = Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "NotificationGroupID"));
                    if (groupName.ToLower().Equals("presentation"))
                    {
                        divhappen.Style.Add("display", "none");
                       
                   }
                    if (groupId == 4)
                    {
                        divGroup.Style.Add("display", "none");
                    }
                   // rptNotificationDetails is inner repeater
                    Repeater rptDetails = (Repeater)e.Item.FindControl("rptNotificationDetails");
                    DataView dv = ((DataRowView)e.Item.DataItem).CreateChildView("NotificationGroupID");
                    if (dv != null)
                    {
                        rptDetails.DataSource = dv;
                        rptDetails.DataBind();
                    }
                }
            }
            catch (Exception ex)
            {
                _objError.WriteLog("Error on rptEmailNotification_ItemDataBound",ex.Message,ex.StackTrace);
               
            }

        }

        protected void rptNotificationDetails_ItemDataBound(object sender, RepeaterItemEventArgs e)
        {
            try
            {
                String[] disableNotificationId = DisableNotification.Split(',');
                if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
                {
                    CheckBox chkInstant = (CheckBox)e.Item.FindControl("ckhInstant");
                    Literal ltrInstant = (Literal)e.Item.FindControl("ltrInstant");
                    CheckBox chkDaily = (CheckBox)e.Item.FindControl("chkDaily");
                    Literal ltrDaily = (Literal)e.Item.FindControl("ltrDaily");
                    CheckBox chkWeekly = (CheckBox)e.Item.FindControl("chkWeekly");
                    Literal ltrWeekly = (Literal)e.Item.FindControl("ltrWeekly");
                    HtmlContainerControl divDescription = (HtmlContainerControl)e.Item.FindControl("pDescription");
                    DropDownList ddlEmailTrigger = (DropDownList)e.Item.FindControl("ddlEmailTrigger");
                    NotificationSetting notificationSetting = new NotificationSetting();
                    int mailNotificationID = Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "MailNotificationID"));                   
                    Int32 emailTriggerID;
                    foreach(string str in disableNotificationId)
                    {
                        if (str == Convert.ToString(mailNotificationID))
                        {
                            divDescription.Visible = false;
                        }
                    }
                    if (objNotificationList != null)
                    {
                        if (!objNotificationList.TryGetValue(mailNotificationID, out notificationSetting))
                        {
                            emailTriggerID = 0;
                            if (Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "IsInstant")) != 2)
                            {
                                SetNotificationOption(chkInstant, ltrInstant, 0);
                            }
                            else
                            {
                                SetNotificationOption(chkInstant, ltrInstant, 2);
                            }

                            if (Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "IsDailyDigest")) != 2)
                            {
                                SetNotificationOption(chkDaily, ltrDaily, 0);
                            }
                      
                            if (Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "IsWeeklyDigest")) != 2)
                            {
                                SetNotificationOption(chkWeekly, ltrWeekly, 0);
                            }
                          }
                         }
                    else
                    {
                        emailTriggerID = Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "EmailTriggerID"));
                        SetNotificationOption(chkInstant, ltrInstant, Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "IsInstant")));
                        SetNotificationOption(chkDaily, ltrDaily, Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "IsDailyDigest")));
                        SetNotificationOption(chkWeekly, ltrWeekly, Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "IsWeeklyDigest")));
                    }
                    BindEmailTriggerList(ddlEmailTrigger, mailNotificationID,  emailTriggerID, dsMailNotification.Tables[3]);

                   
                }
            }
            catch (Exception ex)
            {
                _objError.WriteLog("Error on rptNotificationDetails_ItemDataBound", ex.Message, ex.StackTrace);
            }
        }