+91-90427 10472
mariatrainingacademy@gmail.com
         
Dot net training in Chennai

Create External List using BCS in share point 2010

Open Central Administration in SharePoint.
Go to Application Management -> Manage Service Applications as shown below.

Click on New ->Business Data Connectivity Service

Enter Service Application Name and Click OK.

Business Data Connectivity Service application is created as shown below,

Create New -> Secure Store Service application as shown below,

Enter the name “Secure Store Service” and Click Ok.

Secure Store service is created as shown below.

Business Connectivity service and Secure store service are the prerequisites for Creating BCS in SharePoint.
Open site in SharePoint Designer as shown below.

Open Sql Server and Create a Database StudentDB.
Create a Table EmployeeDB with below Script,

CREATE TABLE [dbo].[EmployeeDB](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[EmployeedName] [text] NULL,
[EmployeedSalary] [float] NULL,
CONSTRAINT [PK_EmployeeDB] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Table Design looks as below,

Go to External Content types option in SPD as shown below,

Click on External Content Type button in Top menu. The designer looks as below,

Click on the option “Click here to discover external data sources and define operations” under External Content Type Operations.
The designer looks as below,

Click on Add Connection.


Click OK.

Right Click on the Table and Select Create all operations as shown below.


Click on Next.

Click on Finish.
Save the Content Type in the Name EmployeeDB as shown below.

Open the SharePoint Site http://htc:7004/ in Browser.
Click on Site Action -> Site Content.
Click on Create.

Click on External List.

Click Create.
Select the Content type from the list.

Click Ok.

Give name as Employee Table and Click on Create.
The Output as below,
External List is created as shown below.



Now the External List allow CRUD Operation.

CRUD operation with SharePoint List in Visual web part

Document by Ganesan – Ganesanva@hotmail.com – + 919600370429

  • Create EmployeeList in Sharepoint 2010 site with the below Columns.

    EmployeeName, Age

  • Create a new Visual Web part Project using File –>New Project


Click Ok.
The Solution Explorer will looks as below,

In VisualWebPart1UserControl.ascx Put the below code snippet,

<table border=”0″ cellpadding=”2″ cellspacing=”2″>
<tr>
<td colspan=”2″ style=”color:Green”>
<asp:Label ID=”lblresults” runat=”server”></asp:Label>
</td>
</tr>
<tr>
<td>
Employee Name
</td>
<td>
<asp:TextBox ID=”txtEmployeeName” runat=”server”></asp:TextBox>
</td>
</tr>
<tr>
<td>
Age
</td>
<td>
<asp:TextBox ID=”txtAge” runat=”server”></asp:TextBox>
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID=”btnSave” runat=”server” Text=”Save” OnClick=”btnSave_Click” />
<asp:Button ID=”btnUpdate” runat=”server” Text=”Update”
onclick=”btnUpdate_Click” />
<asp:HiddenField ID=”hfId” runat=”server” />
<asp:Button ID=”btnClear” runat=”server” Text=”Clear”
onclick=”btnClear_Click” />
</td>
</tr>
<tr>
<td colspan=”2″>
<asp:GridView ID=”grvEmployeeDetails” runat=”server” AutoGenerateColumns=”false”>
<Columns>
<asp:BoundField DataField=”ID” HeaderText=”ID” />
<asp:BoundField DataField=”EmployeeName” HeaderText=”Employee Name” />
<asp:BoundField DataField=”Age” HeaderText=”Age” />
<asp:TemplateField>
<ItemTemplate>
<asp:Button ID=”btnEdit” runat=”server” Text=”Edit” onclick=”btnEdit_Click” />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
</table>


In VisualWebPart1UserControl.ascx.cs put/Replace the below code snippet,

public partial class VisualWebPart1UserControl : UserControl
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindGrid();
btnSave.Visible = true;
btnUpdate.Visible = false;
}
}
private void BindGrid()
{
using (SPSite site = new SPSite(SPContext.Current.Site.Url)) // Site Collection
{
using (SPWeb web = site.OpenWeb()) // Site
{
SPList list = web.Lists[“EmployeeList”]; // List
SPQuery curQry = new SPQuery();
curQry.Query = “”;
//Get the Items using Query
SPListItemCollection olistitemcollection = list.GetItems(curQry);
DataTable dt = olistitemcollection.GetDataTable();
grvEmployeeDetails.DataSource = dt;
grvEmployeeDetails.DataBind();
}
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
using (SPSite site = new SPSite(SPContext.Current.Site.Url)) // Site Collection
{
using (SPWeb web = site.OpenWeb()) // Site
{
SPList list = web.Lists[“EmployeeList”]; // List
SPListItem oListItem = list.Items.Add();
oListItem[“Title”] = txtEmployeeName.Text;
oListItem[“EmployeeName”] = txtEmployeeName.Text;
oListItem[“Age”] = txtAge.Text;
oListItem.Update();
BindGrid();
lblresults.Text = “Saved Successfully”;
}
}
}
protected void btnEdit_Click(object sender, EventArgs e)
{
Button btn = (Button)sender;
//Get the row that contains this button
GridViewRow gvr = (GridViewRow)btn.NamingContainer;
hfId.Value = gvr.Cells[0].Text;
txtEmployeeName.Text = gvr.Cells[1].Text;
txtAge.Text = gvr.Cells[2].Text;
btnSave.Visible = false;
btnUpdate.Visible = true;
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
using (SPSite site = new SPSite(SPContext.Current.Site.Url)) // Site Collection
{
using (SPWeb web = site.OpenWeb()) // Site
{
SPList list = web.Lists[“EmployeeList”]; // List
SPListItem oListItem = list.Items.GetItemById(Convert.ToInt32(hfId.Value));
oListItem[“Title”] = txtEmployeeName.Text;
oListItem[“EmployeeName”] = txtEmployeeName.Text;
oListItem[“Age”] = txtAge.Text;
oListItem.Update();
BindGrid();
lblresults.Text = “Updated Successfully”;
}
}
}
protected void btnClear_Click(object sender, EventArgs e)
{
txtEmployeeName.Text = string.Empty;
txtAge.Text = string.Empty;
hfId.Value = string.Empty;
lblresults.Text = string.Empty;
btnSave.Visible = true;
btnUpdate.Visible = false;
}
}


Right click on the Project and Click Deploy.
Go to Home Page and Click on Site Actions –>Edit Page
Insert –>Web part in Ribbon.

Select the Web part and click Add.

The Output as below,




Click below to download the solution,
https://1drv.ms/u/s!ArddhCoxftkQg6l3ED81o25f4nzffg


Maria academy

Recent Posts

Categories