Friday 9 August 2013

How to perform sorting on Gridview bound to generic list collection

Let's say we have a list or collection based on some entity class and the underlying data is bound to the grid.

Following code performs sorting operation on such kind of grid whose data source is collection.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
             CellPadding="4" 
            EnableModelValidation="True" ForeColor="#333333" GridLines="None" 
            AllowSorting="True" 
            onsorting="GridView1_Sorting">
            <AlternatingRowStyle BackColor="White" />
            <EmptyDataTemplate><h2>No Data Found</h2></EmptyDataTemplate>
            <Columns>           
                <asp:TemplateField HeaderText="ProductId" SortExpression="ProductId">
                    <ItemTemplate>
                        <asp:Label ID="lblEmpNo" runat="server" Text='<%# Eval("ProductId") %>' />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="ProducName" SortExpression="ProductName">
                    <ItemTemplate>
                        <asp:Label ID="lblEmpNo" runat="server" Text='<%# Eval("ProductName") %>' />
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
            <EditRowStyle BackColor="#2461BF" />
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
            <RowStyle BackColor="#EFF3FB" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
        </asp:GridView>

Now create a function like this which returns list of products.
 private List<Products> GetProducts()    //Products is class with two properties ProductId & ProductName
        {
            List<Products> lst = new List<Products>();
            lst.Add(new Products { ProductId = 11, ProductName = "Samsung" });
            lst.Add(new Products { ProductId = 12, ProductName = "nokia" });
            lst.Add(new Products { ProductId = 14, ProductName = "Whirepool" });
            lst.Add(new Products { ProductId = 22, ProductName = "pupil" });
            lst.Add(new Products { ProductId = 31, ProductName = "softtech" });
            return lst;

        }

 protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
        {
            List<Products> lstcoll = GetProducts();
            lstcoll.Sort(new GenericComparer<Products>(e.SortExpression, GridViewSortDirection));
            GridView1.DataSource = lstcoll;
            GridView1.DataBind();
        }
        public SortDirection GridViewSortDirection
        {
            get
            {                            
                if (ViewState["sortDirection"] == null)
                    ViewState["sortDirection"] = SortDirection.Ascending;
                else
                {
                    if (((SortDirection)ViewState["sortDirection"]) == SortDirection.Ascending)
                    {
                        ViewState["sortDirection"] = SortDirection.Descending;
                    }
                    else
                    {
                        ViewState["sortDirection"] = SortDirection.Ascending;
                    }
                }
                return (SortDirection)ViewState["sortDirection"];
            }
            set
            {
                ViewState["sortDirection"] = value;
            }
        }

Now implement the GenericComparer where constructor takes sortexpression and sortdirection inputs.

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.Collections;
using System.Collections.Generic;
using System.Reflection;


public class GenericComparer<T> : IComparer<T>
{
    private SortDirection sortDirection;
    public SortDirection SortDirection
    {
        get { return this.sortDirection; }
        set { this.sortDirection = value; }
    }

    private string sortExpression;
    public GenericComparer(string sortExpression, SortDirection sortDirection)
    {
        this.sortExpression = sortExpression;
        this.sortDirection = sortDirection;
    }

    public int Compare(T x, T y)
    {
        PropertyInfo propertyInfo = typeof(T).GetProperty(sortExpression);
        IComparable obj1 = (IComparable)propertyInfo.GetValue(x, null);
        IComparable obj2 = (IComparable)propertyInfo.GetValue(y, null);
        if (SortDirection == SortDirection.Ascending)
        {
            return obj1.CompareTo(obj2);
        }
        else return obj2.CompareTo(obj1);
    }


}

Monday 3 June 2013

SQL Helper class for different Database Operations

How we pass or add parameters in SQLCommand class?
For beginners it is like:

SqlCommand cmd=new SqlCommand("SQL Query",con);

But with dictionary collection it can be done very easily where we can keep logic in separate layer.Here is an example.

in code behind let's declare a dictionary.
Dictionary<string, object> parameters = new Dictionary<string, object>();
Add parameters
parameters.Add("@name",txtName.Text);
dal.ExecNonQuery("insertrecord", parameters);

Here 'insertrecord' is stored procedure name and we pass parameter as object.

Now Let's have the implementation of this ExecNonQuery method.

 public void ExecNonQuery(string spName, Dictionary<string, object> values)
        {
            try
            {

                SqlCommand cmd = new SqlCommand(spName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = spName;

                foreach (string item in values.Keys)
                {
                    SqlParameter param = new SqlParameter(item, values[item]);
                    cmd.Parameters.Add(param);
                }
                cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                throw new Exception();
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }

Now let's have the implementation of SqlDataReader also.

 public SqlDataReader ExecSPDataReader(string spName, Dictionary<string, object> values)
        {
            SqlConnection conn = null;
            SqlDataReader dr = null;
            try
            {
               con = new SqlConnection("Your connection string");
               con.Open();
               SqlCommand cmd = new SqlCommand(spName, con);
               cmd.CommandType = CommandType.StoredProcedure;
               cmd.CommandText = spName;
               foreach (string item in values.Keys)
                {
                    SqlParameter param = new SqlParameter(item, values[item]);
                    cmd.Parameters.Add(param);
                }
                dr = cmd.ExecuteReader();
            }
            catch 
            {              

            }
            finally
            {             
                con.Close();
                con.Dispose();
            }
            return dr;

        }

Now similarly we can define ExecuteScalar also

public object ExecSPScalar(string spName, Dictionary<string, object> values)
        {

            object returnVal= null;
            try
            {
                conn = ConfigurationManager.AppSettings["Your DB Connection"].ToString();
                con = new SqlConnection(conn);
                con.Open();
                SqlCommand cmd = new SqlCommand(spName, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = spName;
                foreach (string item in values.Keys)
                {
                    SqlParameter param = new SqlParameter(item, values[item]);
                    cmd.Parameters.Add(param);
                }
                returnVal= cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception();
            }

            finally
            {
                con.Close();
                con.Dispose();
            }
            return returnVal;

        }

Now how to call it from  code behind?

  Dictionary<string, object> parameters = new Dictionary<string, object>();
  parameters.Add("@username",txtUserName.Text);
  parameters.Add("@password",txtPassword.Text);
  object value = dal.ExecSPScalar("SPName", parameters);
                if ((int)value== 1)
                {
                    Session["User"] = txtUserName.Text;
                    Response.Redirect("Welcome.aspx");

                }

Hope this helps.

Sunday 26 May 2013

Sharepoint server object model part 2


In the following example I will update list item that exist already.

For that I will use a function lets say UpdateEmployeeList which will take empid as parameter.
 

private void UpdateEmployeeList (SPList lst,int empid)
{
using (SPSite site = new SPSite(SPContext.Current.Site.Url))
 {
     using (SPWeb web = site.OpenWeb())

     {
      web.AllowUnsafeUpdates = true;         
      SPList list = web.Lists[lst];
      SPListItem item = null;
      if(empid!=null)
        {
           item = list.GetItemById(empid);   //check whether id exists
        }
      else
        {
           item = list.AddItem();            //else add new item
        }
           item["Title"]="Your value";
    item.Update();
           list.Update();
       web.AllowUnsafeUpdates = false;
     }
  }
}


To invoke this function we can call it with passing parameter values

UpdateEmployeeList ("Employee",3);

if you have any lookup column then use

item["ColumnName"] = new SPFieldLookupValue(LookupcolumnValue, null);






 

Now How we can delete from sharepoint list.

If we know the list item id then we can use getItemById() then use simply item.delete()

 public void DeleteFromList(int ID)
        {
            SPList list = web.Lists["Your List Name"];
            SPItem item;
            try
            {
            item = list.GetItemById(ID);
            }
            catch (ArgumentException ex) { throw ex; };
            item.Delete();
            item.Update();
        }

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();}
}
}
}

Sunday 17 February 2013

calling REST with Jquery from sharepoint 2010

I will show how to call REST service with JQUERY to fetch data from sharepoint list.

I have one sharepoint list called Employee1 which has 2 columns(Ename and Contact).

Include these 2 scripts:

<script src="Scripts/jquery-1.3.2.js" type="text/javascript"></script>
<script src="Scripts/jquery-1.3.2.min.js" type="text/javascript"></script>
Next write following code- 

<script type="text/jscript" language="jscript">$(document).ready(function () {


$.getJSON('http://URL/_vti_bin/ListData.svc/Employee1', function (data) {
//alert(data);
var count = 0;
$.each(data.d.results, function (i, result) {
//alert(result);
var Ename= data.d.results[i].Ename
var Contact= data.d.results[i].Contact;
html = "<table border=’1′ style=’float: left’><tr><td style=’color:blue’>" + Ename+ "</td><td style=’color:blue’>" + Contact+ "</td></tr></table>";
//html1 = "<table border=’1′ style=’float: left’><tr><td style=’color:blue’>" + title + "</td></tr></table>";
$('#resultarea').append($(html));
});
});
});

</script>

Tuesday 5 February 2013

Working with Nested Repeater in Asp.net

Hi today I am writing on how to implement nested repeater.

I have taken 2 tables.
One is Emp{Eid(PK),Ename,Econt} and Book{Bid(PK),Bname,Eid(FK to EMP)}

So make these 2 tables in Sql Server database.

I want to show which employee has how many books under him/her.

Now I have taken 2 repeaters.below is the HTML

<form id="form1" runat="server">
<div><asp:Repeater ID="repParent" runat="server" OnItemDataBound="repParent_ItemDataBound">
<ItemTemplate ><table>
<tr>
<td>
<asp:HiddenField ID="hdnID" runat="server" Value=' <%# Eval("Eid") %>' /></td>
</tr><tr><td><b>Name :</b><asp:Label ID="lblEname" runat="server" Text=' <%# Eval("Ename") %>' /><br /></td><tr><td>------------------</td></tr></tr><tr><td><asp:Repeater ID="repChild" runat="server"><ItemTemplate><asp:Label ID="lblBname" runat="server" Text=' <%# Eval("Bname") %>' /><br /></ItemTemplate></asp:Repeater></td></tr>
</table></ItemTemplate><SeparatorTemplate>
</SeparatorTemplate></asp:Repeater></div></form>
Now on code behind paste following code.

namespace nestedRepeater{

  public partial class WebForm1 : System.Web.UI.Page  {  protected void Page_Load(object sender, EventArgs e)    {

    if (!IsPostBack)  
      {
      BindParentRep();
       }
    }

private void BindParentRep(){

string connStr = @"Data Source=your server name; initial catalog=Sales;integrated security=true";
SqlConnection conn = new SqlConnection(connStr);
string query = "select * from dbo.Emp";
SqlDataAdapter ad = new SqlDataAdapter(query,conn);
DataSet ds = new DataSet();ad.Fill(ds);
repParent.DataSource = ds;
repParent.DataBind();

}



protected void repParent_ItemDataBound(object sender, RepeaterItemEventArgs e){

if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem){

Repeater child = (Repeater)e.Item.FindControl("repChild");
HiddenField hdnId = (HiddenField)e.Item.FindControl("hdnID"); //Based on this Emp id I will //find all corresponding books.
if (hdnId.Value != null){

string connStr = @"Data Source=your server name; initial catalog=Sales;integrated security=true";
SqlConnection conn = new SqlConnection(connStr);
string query = "select Bname from dbo.Book where Eid=@Eid";
SqlDataAdapter ad = new SqlDataAdapter(query, conn);
SqlCommand cmd = new SqlCommand(query, conn);conn.Open();
cmd.Parameters.AddWithValue(
"@Eid", hdnId.Value);
SqlDataReader dr = cmd.ExecuteReader();child.DataSource = dr;
child.DataBind();
conn.Close();
}
}
}
}
}

After this press F5 to run.