DDL and DML Operations using SharePoint Linq Concept

using System;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using Microsoft.SharePoint;

using Microsoft.SharePoint.Linq;

using System.Linq;

using System.Data;

namespace LinqSample.VisualWebPart1

{

public partial class VisualWebPart1UserControl : UserControl

{

DataSet dset = new DataSet();

DataTable dt = new DataTable();

string strTitle = string.Empty;

string strStatus = string.Empty;

string id;

protected void Page_Load(object sender, EventArgs e)

{

sgvConcepts.DataSource = getData();

sgvConcepts.DataBind();

/*var context = new LinqSampleDataContext(SPContext.Current.Web.Url);

EntityList Concept = context.GetList("SharePoint 2010 Concepts");

var query = from cs in Concept

where cs.Status.Title.ToString() != "Completed"

select new

{

strTitle= cs.Title,

strStatus=cs.Status.Title.ToString(),

cs.Id

};

if (query.Count() > 0)

{

this.Controls.Add(new LiteralControl("

"));

foreach (var q in query)

{

strTitle = q.strTitle;

strStatus = q.strStatus;

this.Controls.Add(new LiteralControl("

"));

this.Controls.Add(new LiteralControl("

"));

this.Controls.Add(new LiteralControl("

"));

this.Controls.Add(new LiteralControl("

"));

}

this.Controls.Add(new LiteralControl("

")); this.Controls.Add(new LiteralControl(strTitle)); this.Controls.Add(new LiteralControl("

")); this.Controls.Add(new LiteralControl(strStatus)); this.Controls.Add(new LiteralControl("

"));

}*/

}

public DataSet getData()

{

try

{

dt.Columns.Add("Title");

dt.Columns.Add("Status");

dt.Columns.Add("ID");

SPWeb currentWeb = SPContext.Current.Web;

var context = new LinqSampleDataContext(currentWeb.Url);

EntityList Concept = context.GetList("SharePoint 2010 Concepts");

var query = from cs in Concept

where cs.Status.Title.ToString() != "Completed" orderby cs.Id descending

select new

{

strTitle = cs.Title,

strStatus = cs.Status.Title.ToString(),

id=cs.Id

};

if (query.Count() > 0)

{

int i = 0;

foreach (var q in query)

{

i++;

strTitle = q.strTitle;

strStatus = q.strStatus;

id = q.id.ToString();

DataRow drow = dt.NewRow();

drow["Title"] = strTitle;

drow["Status"] = strStatus;

drow["ID"] = id;

dt.Rows.Add(drow);

if (i == 5)

{

break;

}

}

}

dset.Tables.Clear();

dset.Tables.Add(dt);

return dset;

}

catch (Exception ex)

{

this.Controls.Add(new LiteralControl(ex.ToString()));

return null;

}

}

protected void btnSaveRecord_Click(object sender, EventArgs e)

{

var context = new LinqSampleDataContext(SPContext.Current.Web.Url);

EntityList login_Insert = context.GetList("Login");

LoginItem NewREc = new LoginItem() {Title=txtUserName.Text,Password=txtPassWord.Text };

login_Insert.InsertOnSubmit(NewREc);

context.SubmitChanges();

txtUserName.Text = "";

txtPassWord.Text = "";

this.Controls.Add(new LiteralControl("Insertion Successful"));

}

protected void btnSearch_Click(object sender, EventArgs e)

{

string strPassWord=string.Empty;

var context = new LinqSampleDataContext(SPContext.Current.Web.Url);

EntityList login_Insert = context.GetList("Login");

var query = from cs in login_Insert

where cs.Title.ToString() == txtUserName.Text

select new

{

strPassWord=cs.Password

};

if (query.Count() > 0)

{

foreach (var q in query)

{

txtPassWord.Text = q.strPassWord;

break;

}

}

else

{

this.Controls.Add(new LiteralControl("No Record Found"));

}

}

protected void btnUpdate_Click(object sender, EventArgs e)

{

var context = new LinqSampleDataContext(SPContext.Current.Web.Url);

EntityList login_Insert = context.GetList("Login");

var query = from cs in login_Insert

where cs.Title.ToString() == txtUserName.Text

select new

{

cs.Title,

cs.Password

};

var UpadteQuery = context.Login.SingleOrDefault(cs => cs.Title == txtUserName.Text);

if (UpadteQuery != null)

{

UpadteQuery.Password = txtPassWord.Text;

context.SubmitChanges();

}

this.Controls.Add(new LiteralControl("Updation Sucessful"));

}

protected void btnDelete_Click(object sender, EventArgs e)

{

var context = new LinqSampleDataContext(SPContext.Current.Web.Url);

foreach (LoginItem item in context.Login)

{

if (item.Title == txtUserName.Text)

{

context.Login.DeleteOnSubmit(item);

}

}

context.SubmitChanges();

this.Controls.Add(new LiteralControl("Record Successfully Deleted"));

}

}

}