Tuesday 30 April 2013

Sharepoint 2010 server object model

I am going to write a few methods showing some of the basic operations which can be performed using object model.

Microsoft provides lot of API's for working with object model.

Examples are- SPSite,SPWeb,SPList etc..
All these api's are availbale in Microsoft.Sharepoint.dll .so add it as a reference and insert in code
using Micosoft.Sharepoint namespace.

create a new project in visual studio.
(My suggestion is to create a Empty sharepoint project from sharepoint 2010 template).

Then add a visual web part there.

To get the current context -

using(SPSite site =new SPSite(SPContext.current.site.URL))

to get a current web inside the site-

using(SPWeb web =new SPWeb(site.OpenWeb())

Let's say I have a Student entity class.

[serializable]
Public class Student
{
  public string Name {get;set:}
  public string Email {get;set;}
}

Now I want to get all the list items stored in student list of sharepoint site.


public List<Student> GetAllStudents()
        {
            List<Student> lstStudent = new List<Student>();
            SPList list = web.Lists["Student"];                   
            SPListItemCollection collectionItem = list.Items;  //Get all items
            //SPListItemCollection collectionItem = list.getItems(query) //If CAML query is used
            foreach (SPItem item in collectionItem)
            {
                lstStudent .Add(GetStudent(item));
            }
            
        }


 private Student GetStudent(SPItem item)
        {
            return new Student
            {
                Name= (string)item.Name,
                Email = (string)item["Email"]
            };
        }

Lets say I want to add in student list.

We will write a method like

public void AddStudent(Student objstudent)  //Passing object of student class
{
  SPList list = web.Lists["Student"];             
  SPItem itemstudent;
  itemstudent=list.AddItem();
  itemstudent["Name"]=objstudent.Name;
  itemstudent["Email"]=objstudent.Email;
  itemstudent.update();
}



I will be writing few more examples..

Sunday 14 April 2013

asp.net Datalist insert update delete example

I want to show you how to use datalist for CRUD operation in asp.net.

I created one table 'customer' with CustomerID(Primary Key) and CustomerName as two fields in database.

Following is the HTML source
-------------------------------

<h2>Datalist Example Application:</h2>
        <asp:DataList ID="DataList1" runat="server" CellPadding="4"
            onitemcommand="DataList1_ItemCommand"
            oncancelcommand="DataList1_CancelCommand" oneditcommand="DataList1_EditCommand"
            onupdatecommand="DataList1_UpdateCommand"
            ondeletecommand="DataList1_DeleteCommand" ForeColor="#333333">
            <FooterStyle BackColor="#1C5E55" ForeColor="White" Font-Bold="True" />
            <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            <ItemStyle BackColor="#E3EAEB" />
        <ItemTemplate>
        <table border="0" cellpadding="0" cellspacing="0">
        <tr>
        <th>Customer ID:</th>
        <td><asp:Label ID="lblCustNo" runat="server" Text='<%#Bind("CustomerID") %>'></asp:Label></td>
        </tr>
        <tr>
        <th>Customer Name:</th>
        <td><asp:Label ID="lblCustName" runat="server" Text='<%#Bind("CustomerName") %>'></asp:Label></td>
        </tr>
        <tr><td><asp:Image ID="Image1" Height="20px" Width="20px" ImageUrl="~/Images/edit-icon.png" runat="server" />
        <asp:Button ID="btnEdit" runat="server" Text="Edit" CommandName="Edit" OnClientClick="return confirm('Are you sure you want to Edit this record?');" /></td>
        <td>
        <asp:Image ID="Image2" Height="20px" Width="20px" ImageUrl="~/Images/delete.png" runat="server" />
        <asp:Button ID="btnDelete" runat="server" Text="Delete" CommandName="Delete"  OnClientClick="return confirm('Are you sure you want to delete this record?');" /></td>
        </tr>
         </table>
        </ItemTemplate>
         <FooterTemplate>
          <table border="0" cellpadding="0" cellspacing="0">
        <tr>
        <th>Customer ID:</th>
        <th>Customer Name:</th>
        </tr>
        <tr>
        <td><asp:TextBox ID="txtCustNo" runat="server" Text='<%#Bind("CustomerID") %>'></asp:TextBox></td>
        <td><asp:TextBox ID="txtCustName" runat="server" Text='<%#Bind("CustomerName") %>'></asp:TextBox></td>
        <td>
        <asp:Image ID="Image1" Height="20px" Width="20px" ImageUrl="~/Images/add.png" runat="server" />
        <asp:Button ID="btnInsert" runat="server" Text="Add New Customer" CommandName="Insert" /></td>
        </tr>
         </table>
         </FooterTemplate>
            <AlternatingItemStyle BackColor="White" />
         <EditItemTemplate>
          <table border="0" cellpadding="0" cellspacing="0">
            <tr>
            <td>Customer ID:</td>
            <td><asp:TextBox ID="etxtCustNo" runat="server" Text='<%#Bind("CustomerID") %>'></asp:TextBox></td>
            <//tr>
             <tr>
            <td>Customer Name:</td>
            <td><asp:TextBox ID="etxtCustName" runat="server" Text='<%#Bind("CustomerName") %>'></asp:TextBox></td>
            <//tr>
            <tr>
            <td><asp:Button ID="btnUpdate" runat="server" Text="Update" CommandName="Update" /></td>
            <td><asp:Button ID="btnCancel" runat="server" Text="Cancel" CommandName="Cancel" /></td>
            </tr>
         </EditItemTemplate>
            <SelectedItemStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
        </asp:DataList>
---------------------------------
Now on code behind please write following code:


public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                Bind();
            }
        }

        private void Bind()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["mystring"].ConnectionString;
            SqlConnection conn = new SqlConnection(connectionString);
            SqlDataAdapter ad = new SqlDataAdapter("select * from Emp", conn);
            DataSet ds = new DataSet();
            conn.Open();
            ad.Fill(ds);
            conn.Close();
            DataList1.DataSource = ds.Tables[0];
            DataList1.DataBind();

        }

        protected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
        {
           
            string connectionString = ConfigurationManager.ConnectionStrings["mystring"].ConnectionString;
            if (e.CommandName.Equals("Insert"))
            {
                TextBox txtCustNo = (TextBox)e.Item.FindControl("txtCustNo");
                TextBox txtCustName = (TextBox)e.Item.FindControl("txtCustName");
                SqlConnection conn = new SqlConnection(connectionString);
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "Insert into Emp(CustomerID,CustomerName) values(@1,@2)";
                cmd.Parameters.AddWithValue("@1", txtCustNo.Text);
                cmd.Parameters.AddWithValue("@2", txtCustName.Text);
                conn.Open();
                cmd.ExecuteNonQuery();
                ClientScript.RegisterStartupScript(GetType(), "ShowAlert", "alert('Record Added Succesfully');", true);
                conn.Close();
                Bind();
            }
        }

        protected void DataList1_EditCommand(object source, DataListCommandEventArgs e)
        {

            DataList1.EditItemIndex = e.Item.ItemIndex;
            Bind();
        }

        protected void DataList1_CancelCommand(object source, DataListCommandEventArgs e)
        {
            DataList1.EditItemIndex = -1;
            Bind();
        }

        protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["mystring"].ConnectionString;
            if (e.CommandName.Equals("Update"))
            {
                TextBox txtCustNo = (TextBox)e.Item.FindControl("etxtCustNo");
                TextBox txtCustName = (TextBox)e.Item.FindControl("etxtCustName");
                SqlConnection conn = new SqlConnection(connectionString);
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "Update Emp set CustomerName=@1 where CustomerID=@2";
                if (txtCustName != null)
                    cmd.Parameters.Add("@1", SqlDbType.VarChar, 50).Value = txtCustName.Text;
                if (txtCustNo != null)
                    cmd.Parameters.Add("@2", SqlDbType.Int, 20).Value = txtCustNo.Text;
                conn.Open();
                cmd.ExecuteNonQuery();
                DataList1.EditItemIndex = -1;
                ClientScript.RegisterStartupScript(GetType(), "ShowAlert", "alert('Record Updated Succesfully');", true);
                conn.Close();
                Bind();
            }
        }

        protected void DataList1_DeleteCommand(object source, DataListCommandEventArgs e)
        {
           
            string connectionString = ConfigurationManager.ConnectionStrings["mystring"].ConnectionString;
            if (e.CommandName.Equals("Delete"))
            {
                Label txtCustNo = (Label)e.Item.FindControl("lblCustNo");
                Label txtCustName = (Label)e.Item.FindControl("lblCustName");
                SqlConnection conn = new SqlConnection(connectionString);
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "Delete from Emp where CustomerID=@1";
                if(txtCustNo!=null)
                cmd.Parameters.Add("@1", SqlDbType.Int, 20).Value = txtCustNo.Text;
                conn.Open();
                cmd.ExecuteNonQuery();
                ClientScript.RegisterStartupScript(GetType(), "ShowAlert", "alert('Record Deleted Succesfully');", true);
                conn.Close();
                Bind();

            }
        }
    }

Run it now by Pressing F5.




Friday 5 April 2013

Join operation in sharepoint 2010

In this article I am showing how can we perform inner join between 2 different sharepoint lists.


class Program
{static void Main(string[] args){

using (SPSite site = new SPSite("URL")){

using (SPWeb web = site.OpenWeb()){

SPQuery query = new SPQuery();
query.Joins =
"<Join Type='INNER' ListAlias='Employee1'>" +
"<Eq>" +
"<FieldRef Name='Manager' RefType='ID'/>" +
"<FieldRef List='Employee1' Name='ID'/>" +
"</Eq>" +
"</Join>"; 
query.ProjectedFields =
"<Field Name='Employee1Ename' Type='Lookup' " +
"List='Employee1' ShowField='Title'/>" +       
"<Field Name='Employee1Contact' Type='Lookup' " +
"List='Employee1' ShowField='NewColumn1'/>";  // Title & NewColumn1 is internal name
query.ViewFields =
"<FieldRef Name='Title'/>" +
"<FieldRef Name='Employee1Ename'/>" +
"<FieldRef Name='Employee1Contact'/>";
SPList list = web.Lists["Projects"];
SPListItemCollection coll = list.GetItems(query);

foreach (SPListItem item in coll){

SPFieldLookupValue Ename = new SPFieldLookupValue(item["Employee1Ename"].ToString());
SPFieldLookupValue contact = new SPFieldLookupValue(item["Employee1Contact"].ToString());
Console.WriteLine("{0} {1} {2}", item.Title, Ename.LookupValue,contact.LookupValue);}

Console.ReadKey();}
}
}
}