Prasad Bolla's SharePoint Blog: August 2012

Tuesday, August 21, 2012

Binding Data From Microsoft Excel WorkBook Sheet to GridView in .Net

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.OleDb;

public partial class _Default : System.Web.UI.Page

{

OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\prasad.b\Desktop\sample.xls;Extended Properties='Excel 12.0;HDR=YES;'");

OleDbDataAdapter ad;

DataSet dSet = new DataSet();

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

GridView1.DataSource = getDataFromExcel();

GridView1.DataBind();

}

public DataSet getDataFromExcel()

{

ad = new OleDbDataAdapter("Select * from [Sheet1$]", con);

dSet = new DataSet();

ad.Fill(dSet);

return dSet;

}

Friday, August 10, 2012

Insertion into SharePoint List Using GridView

Ascx

<asp:GridView ID="dgvInsertRecord" runat="server" AutoGenerateColumns="False"

CellPadding="4" ForeColor="#333333" GridLines="None" ShowFooter="True"

onrowcommand="dgvInsertRecord_RowCommand">

<asp:TemplateField>

<tr>

Fname

</td>

City

</td>

Telno

</td>

</tr>

</table>

</HeaderTemplate>

<tr>

<%#Eval("Fname") %>

</td>

<%#Eval("City") %>

</td>

<%#Eval("Telno") %>

</td>

</tr>

</table>

</ItemTemplate>

<tr>

<asp:TextBox ID="txtFname" runat="server"></asp:TextBox>

</td>

<asp:TextBox ID="txtCity" runat="server"></asp:TextBox>

</td>

<asp:TextBox ID="txtTelno" runat="server"></asp:TextBox> <asp:Button ID="btnSave" runat="server" Text="Save" CommandName="Insert" />

</td>

</tr>

</table>

</FooterTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

Ascx.Cs

using System;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using Microsoft.SharePoint;

using System.Security;

namespace GridInsert.VisualWebPart1

{

public partial class VisualWebPart1UserControl : UserControl

{

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindData();

}

public void BindData()

{

SPWeb currentWeb = SPContext.Current.Web;

SPList lst = currentWeb.Lists["Contacts"];

SPListItemCollection myColl = lst.Items;

dgvInsertRecord.DataSource = myColl.GetDataTable();

dgvInsertRecord.DataBind();

}

protected void dgvInsertRecord_RowCommand(object sender, GridViewCommandEventArgs e)

{

if (e.CommandName == "Insert")

{

SPWeb currentWeb = SPContext.Current.Web;

SPList lst = currentWeb.Lists["Contacts"];

SPListItemCollection myColl = lst.Items;

TextBox txtFname1 = (TextBox)dgvInsertRecord.FooterRow.FindControl("txtFname");

TextBox txtCity1 = (TextBox)dgvInsertRecord.FooterRow.FindControl("txtCity");

TextBox txtTelno1 = (TextBox)dgvInsertRecord.FooterRow.FindControl("txtTelno");

SPListItem item = myColl.Add();

item["Fname"] = txtFname1.Text;

item["City"] = txtCity1.Text;

item["Telno"] = txtTelno1.Text;

item.Update();

txtFname1.Text = "";

txtCity1.Text = "";

txtTelno1.Text = "";

BindData();

}

Thursday, August 09, 2012

Binding Data from Document Library to SpGridView using Client Object Model without Dataset

<%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>

<%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register Tagprefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register Tagprefix="asp" Namespace="System.Web.UI" Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %>

<%@ Import Namespace="Microsoft.SharePoint" %>

<%@ Register Tagprefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="VisualWebPart1UserControl.ascx.cs" Inherits="COMGridDocuments.VisualWebPart1.VisualWebPart1UserControl" %>

<SharePoint:SPGridView runat="server" ID="sgvDocuments" AutoGenerateColumns="false">

<asp:HyperLinkField HeaderText="Document Name" DataTextField="LinkFilename" DataNavigateUrlFields="EncodedAbsUrl" DataNavigateUrlFormatString="{0}" Target="_blank" />

</Columns>

</SharePoint:SPGridView>

using System;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Linq;

using Microsoft.SharePoint.Client;

using System.Collections.Generic;

using Microsoft.SharePoint;

namespace COMGridDocuments.VisualWebPart1

{

public partial class VisualWebPart1UserControl : UserControl

{

protected void Page_Load(object sender, EventArgs e)

{

sgvDocuments.DataSource = getData();

sgvDocuments.DataBind();

}

public class DocumentsData

{

public string LinkFilename { get; set; }

public string EncodedAbsUrl { get; set; }

}

public List<DocumentsData> getData()

{

List<DocumentsData> MyDocumentsData = new List<DocumentsData>();

using (ClientContext clientContext = new ClientContext(SPContext.Current.Web.Url))

{

List list = clientContext.Web.Lists.GetByTitle("NewDocuments");

var camlQuery = new CamlQuery { ViewXml = "<View Scope='Recursive'><Query><OrderBy><FieldRef Name='ID' Ascending='False' /></OrderBy></Query><ViewFields><FieldRef Name='LinkFilename' /><FieldRef Name='EncodedAbsUrl' /></ViewFields></View>" };

Microsoft.SharePoint.Client.ListItemCollection myColl = list.GetItems(camlQuery);

clientContext.Load(myColl,li => li.Include(pi => pi["LinkFilename"], pi => pi["EncodedAbsUrl"]));

clientContext.ExecuteQuery();

foreach (Microsoft.SharePoint.Client.ListItem item in myColl)

{

MyDocumentsData.Add(new DocumentsData()

{

LinkFilename = item["LinkFilename"].ToString(),

EncodedAbsUrl = item["EncodedAbsUrl"].ToString()

});

}

return MyDocumentsData;

}