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