DDL and DML Operations in Sharepoint

In this article you will learn how to use DDL and DML Operations in Sharepoint.

 

using System;

using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Serialization;
using System.Security;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;
using System.Data;

[assembly: AllowPartiallyTrustedCallers]

namespace Student_Details

{

    [Guid("83665ead-a671-4a26-b07f-c0b336753e24")]

    public class Student_Details : System.Web.UI.WebControls.WebParts.WebPart

    {

        TextBox txtSno;

        TextBox txtSname;

        RadioButton rbMale;

        RadioButton rbFemale;

        TextBox txtAddress;

        DropDownList ddlCountry;

        DropDownList ddlCity;

        TextBox txtCno;

        TextBox txtEmailID;

        TextBox txtInterest;

        RequiredFieldValidator rfvSno;

        RequiredFieldValidator rfvSname;

        RequiredFieldValidator rfvAddress;

        RequiredFieldValidator rfvCountry;

        RequiredFieldValidator rfvCity;

        RequiredFieldValidator rfvCno;

        RequiredFieldValidator rfvEmailId;

        Button btnSave;

        Button btnSearch;

        Button btnUpdate;

        Button btnDelete;

        string strError = string.Empty;

        protected override void Render(HtmlTextWriter writer)

        {

            writer.Write(strError);

            try

            {

                writer.Write("<Table width='100%'>");

                //Sno

                writer.Write("<Tr>");

                writer.Write("<Td>");

                writer.Write("Enter student number");

                writer.Write("</Td>");

                writer.Write("<Td>");

                txtSno.RenderControl(writer);

                writer.Write("</Td>");

                writer.Write("<Td>");

                rfvSno.RenderControl(writer);

                writer.Write("</Td>");

                writer.Write("<Td>");

                writer.Write("Enter student name");

                writer.Write("</Td>");

                writer.Write("<Td>");

                txtSname.RenderControl(writer);

                writer.Write("</Td>");

                writer.Write("<Td>");

                rfvSname.RenderControl(writer);

                writer.Write("</Td>");

                writer.Write("</Tr>");

               

                 writer.Write("<Tr>");

                writer.Write("<Td>");

                writer.Write("Select gender");

                writer.Write("</Td>");

                writer.Write("<Td colspan='2'>");

                writer.Write("<Table width='100%'>");

                writer.Write("<Tr>");

                writer.Write("<Td>");

                rbMale.RenderControl(writer);

                writer.Write("</Td>");

                writer.Write("</Tr>");

                writer.Write("<Tr>");

                writer.Write("<Td>");

                rbFemale.RenderControl(writer);

                writer.Write("</Td>");

                writer.Write("</Tr>");

                writer.Write("</Table>");

                writer.Write("</Td>");

                writer.Write("<Td>");

                writer.Write("Enter student address");

                writer.Write("</Td>");

                writer.Write("<Td>");

                txtAddress.RenderControl(writer);

                writer.Write("</Td>");

                writer.Write("<Td>");

                rfvAddress.RenderControl(writer);

                writer.Write("</Td>");

                writer.Write("</Tr>");

                //Country

                writer.Write("<Tr>");

                writer.Write("<Td>");

                writer.Write("Select Country");

                writer.Write("</Td>");

                writer.Write("<Td>");

                ddlCountry.RenderControl(writer);

                writer.Write("</Td>");

                writer.Write("<Td>");

                rfvCountry.RenderControl(writer);

                writer.Write("</Td>");

                writer.Write("<Td>");

                writer.Write("Select City");

                writer.Write("</Td>");

                writer.Write("<Td>");

                ddlCity.RenderControl(writer);

                writer.Write("</Td>");

                writer.Write("<Td>");

                rfvCity.RenderControl(writer);

                writer.Write("</Td>");

                writer.Write("</Tr>");

                //Contact number;

                writer.Write("<Tr>");

                writer.Write("<Td>");

                writer.Write("Enter contact number");

                writer.Write("</Td>");

                writer.Write("<Td>");

                txtCno.RenderControl(writer);

                writer.Write("</Td>");

                writer.Write("<Td>");

                rfvCno.RenderControl(writer);

                writer.Write("</Td>");

                writer.Write("<Td>");

                writer.Write("Enter email-id");

                writer.Write("</Td>");

                writer.Write("<Td>");

                txtEmailID.RenderControl(writer);

                writer.Write("</Td>");

                writer.Write("<Td>");

                rfvEmailId.RenderControl(writer);

                writer.Write("</Td>");

                writer.Write("</Tr>");

              

                writer.Write("<Tr>");

                writer.Write("<Td>");

                writer.Write("Enter interest");

                writer.Write("</Td>");

                writer.Write("<Td colspan='5'>");

                txtInterest.RenderControl(writer);

                writer.Write("</Td>");

                writer.Write("</Tr>");

                //Insert Buton

                writer.Write("<Tr>");

                writer.Write("<Td colspan='6' align='Center'>");

                btnSave.RenderControl(writer);

                btnSearch.RenderControl(writer);

                btnUpdate.RenderControl(writer);

                btnDelete.RenderControl(writer);

                writer.Write("</Td>");

                writer.Write("</Tr>");

                writer.Write("</Table>");

            }

            catch (Exception ex)

            {

                writer.Write(ex.ToString());

            }

        }

        protected override void CreateChildControls()

        {

            try

            {

                txtSno = new TextBox();

                txtSno.ID = "txtSno";

                this.Controls.Add(txtSno);

 

                rfvSno = new RequiredFieldValidator();

                rfvSno.ID = "rfvSno";

                rfvSno.ControlToValidate = txtSno.ID;

                rfvSno.ErrorMessage = "Student number required";

                this.Controls.Add(rfvSno);

 

                txtSname = new TextBox();

                txtSname.ID = "txtSname";

                this.Controls.Add(txtSname);

 

                rfvSname = new RequiredFieldValidator();

                rfvSname.ID = "rfvSname";

                rfvSname.ControlToValidate = txtSname.ID;

                rfvSname.ErrorMessage = "Student name required";

                this.Controls.Add(rfvSname);

 

                rbMale = new RadioButton();

                rbMale.ID = "rbMale";

                rbMale.Checked = true;

                rbMale.GroupName = "rbGender";

                rbMale.Text = "Male";

                this.Controls.Add(rbMale);

 

                rbFemale = new RadioButton();

                rbFemale.ID = "rbFemale";

                rbFemale.GroupName = "rbGender";

                rbFemale.Text = "Female";

                this.Controls.Add(rbFemale);

 

                txtAddress = new TextBox();

                txtAddress.ID = "txtAddress";

                txtAddress.TextMode = TextBoxMode.MultiLine;

                this.Controls.Add(txtAddress);

 

                rfvAddress = new RequiredFieldValidator();

                rfvAddress.ID = "rfvAddress";

                rfvAddress.ControlToValidate = txtAddress.ID;

                rfvAddress.ErrorMessage = "Student address required";

                this.Controls.Add(rfvAddress);

 

                ddlCountry = new DropDownList();

                ddlCountry.ID = "ddlCountry";

                ddlCountry.AutoPostBack = true;

                ddlCountry.Items.Add("India");

                ddlCountry.Items.Add("Pakistan");

                ddlCountry.Items.Add("Bangladesh");

                ddlCountry.Items.Insert(0, new ListItem("Select Country", "0"));

                ddlCountry.SelectedIndexChanged += new EventHandler(ddlCountry_SelectedIndexChanged);

                this.Controls.Add(ddlCountry);

 

                rfvCountry = new RequiredFieldValidator();

                rfvCountry.ID = "rfvCountry";

                rfvCountry.ControlToValidate = ddlCountry.ID;

                rfvCountry.InitialValue = "Select Country";

                rfvCountry.ErrorMessage = "Select country";

                this.Controls.Add(rfvCountry);

                ddlCity = new DropDownList();

                ddlCity.ID = "ddlCity";

                ddlCity.Items.Insert(0, new ListItem("Select City", "0"));

                this.Controls.Add(ddlCity);

                rfvCity = new RequiredFieldValidator();

                rfvCity.ID = "rfvCity";

                rfvCity.ControlToValidate = ddlCity.ID;

                rfvCity.InitialValue = "Select City";

                rfvCity.ErrorMessage = "Select city";

                this.Controls.Add(rfvCountry);

                txtCno = new TextBox();

                txtCno.ID = "txtCno";

                this.Controls.Add(txtCno);

                rfvCno = new RequiredFieldValidator();

                rfvCno.ID = "rfvCno";

                rfvCno.ControlToValidate = txtCno.ID;

                rfvCno.ErrorMessage = "Contact number required";

                this.Controls.Add(rfvCno);

                txtEmailID = new TextBox();

                txtEmailID.ID = "txtEmailID";
                this.Controls.Add(txtEmailID);

                rfvEmailId = new RequiredFieldValidator();

                rfvEmailId.ID = "rfvEmailId";

                rfvEmailId.ControlToValidate = txtEmailID.ID;

                rfvEmailId.ErrorMessage = "Student email-id required";

                this.Controls.Add(rfvEmailId);

 

                txtInterest = new TextBox();

                txtInterest.ID = "txtInterest";

                this.Controls.Add(txtInterest);

 

                btnSave = new Button();

                btnSave.ID = "btnSave";

                btnSave.Text = "Save";

                btnSave.Click += new EventHandler(btnSave_Click);

                this.Controls.Add(btnSave);

 

                btnSearch = new Button();

                btnSearch.ID = "btnSearch";

                btnSearch.Text = "Search";

                btnSearch.CausesValidation = false;

                btnSearch.Click += new EventHandler(btnSearch_Click);

                this.Controls.Add(btnSearch);

 

                btnUpdate = new Button();

                btnUpdate.ID = "btnUpdate";

                btnUpdate.Text = "Update";

                btnUpdate.Click += new EventHandler(btnUpdate_Click);

                this.Controls.Add(btnUpdate);

 

                btnDelete = new Button();

                btnDelete.ID = "btnDelete";

                btnDelete.Text = "Delete";

                btnDelete.CausesValidation = false;

                btnDelete.Click += new EventHandler(btnDelete_Click);

                this.Controls.Add(btnDelete);

            }

            catch (Exception ex)

            {

                strError = ex.ToString();

            }

        }

 

        void btnDelete_Click(object sender, EventArgs e)

        {

            try

            {

                SPWeb currentWeb = SPControl.GetContextWeb(Context);

                SPList lst = currentWeb.Lists["Student Details"];

                SPListItemCollection myColl = currentWeb.Lists["Student Details"].Items;

                foreach (SPListItem item in myColl)

                {

                    if (item["Title"].ToString() == txtSno.Text)

                    {

                        item.Delete();

                        txtSno.Text = "";

                        break;

                    }

                }

                strError += "Record deleted successfully";

            }

            catch (Exception ex)

            {

                strError = ex.ToString();

            }

        }

 

        void btnUpdate_Click(object sender, EventArgs e)

        {

            try

            {

                SPWeb currentWeb = SPControl.GetContextWeb(Context);

                SPList lst = currentWeb.Lists["Student Details"];

                SPListItemCollection mycoll = lst.Items;

                foreach (SPListItem item in mycoll)

                {

                    if (item["Title"].ToString() == txtSno.Text)

                    {

                        item["Sname"] = txtSname.Text;

                        if (rbMale.Checked == true)

                        {

                            item["Gender"] = "Male";

                        }

                        if (rbFemale.Checked == true)

                        {

                            item["Gender"] = "Female";

                        }

                        item["Address"] = txtAddress.Text;

                        item["Country"] = ddlCountry.SelectedValue.ToString();

                        item["City"] = ddlCity.SelectedValue.ToString();

                        item["Contact_x0020_Number"] = txtCno.Text;

                        item["EmailID"] = txtEmailID.Text;               

                        item["Interest"] = txtInterest.Text;

                        item.Update();

                        Clear();

                        break;

                    }

                }

                strError += "Record updated successfully";

            }

            catch (Exception ex)

            {

                strError = ex.ToString();

            }

        }

 

        void btnSearch_Click(object sender, EventArgs e)

        {

            try

            {

                SPWeb currentWeb = SPControl.GetContextWeb(Context);

                SPList lst = currentWeb.Lists["Student Details"];

                SPListItemCollection myColl = currentWeb.Lists["Student Details"].Items;

                foreach (SPListItem item in myColl)

                {

                    if (item["Title"].ToString() == txtSno.Text)

                    {

                        txtSno.Text = item["Title"].ToString();

                        txtSname.Text = item["Sname"].ToString();

                        if (item["Gender"].ToString().Trim().ToLower() == "Male".ToLower())

                        {

                            rbMale.Checked = true;

                        }

                        else

                        {

                            rbFemale.Checked = true;

                        }

                        txtAddress.Text = item["Address"].ToString();

                        ddlCountry.Text = item["Country"].ToString();

                        ddlCity.Text = item["City"].ToString();

                        txtCno.Text = item["Contact_x0020_Number"].ToString();

                        txtEmailID.Text = item["EmailID"].ToString();

                        if (item["Interest"] != null)

                        {

                            txtInterest.Text = item["Interest"].ToString();

                        }

                        else

                        {

                            item["Interest"] = "--";

                            txtInterest.Text = item["Interest"].ToString();

                        }

                        item.Update();

                        break;

                    }

                }

                strError += "Record found";

            }

            catch (Exception ex)

            {

                strError += ex.ToString();

            }

        }

 

        void btnSave_Click(object sender, EventArgs e)

        {

            try

            {

                SPWeb currentWeb = SPControl.GetContextWeb(Context);

                SPList lst = currentWeb.Lists["Student Details"];

                SPListItemCollection mycoll = lst.Items;

                SPListItem item = mycoll.Add();

 

                item["Title"] = txtSno.Text;

                item["Sname"] = txtSname.Text;

                if (rbMale.Checked == true)

                {

                    item["Gender"] = "Male";

                }

                else

                {

                    item["Gender"] = "Female";

                }

                item["Address"] = txtAddress.Text;

                item["Country"] = ddlCountry.SelectedValue.ToString();

                item["City"] = ddlCity.SelectedValue.ToString();

                item["Contact_x0020_Number"] = txtCno.Text;

                item["EmailID"] = txtEmailID.Text;

                if (txtInterest != null)

                {

                    item["Interest"] = txtInterest.Text;
                }

                 else

                {

                    item["Interest"] = "--";

                }

                item.Update();

                Clear();

                strError += "Record inserted successfully";

            }

            catch (Exception ex)

            {

                strError = ex.ToString();

            }

        }

        public void Clear()

        {

            txtSno.Text = "";

            txtSname.Text = "";

            txtAddress.Text = "";

            rbMale.Checked = true;

            rbFemale.Checked = false;

             ddlCountry.SelectedIndex = 0;

             ddlCity.SelectedIndex = 0;

            txtCno.Text = "";

            txtEmailID.Text = "";

            txtCno.Text = "";

            txtInterest.Text  = "";

        }

        void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)

        {

             try

            {

                if (ddlCountry.SelectedItem.Text == "India")

                {

                     ddlCity.Items.Clear();

                     ddlCity.Items.Insert(0, new ListItem("Select City", "0"));

                     ddlCity.Items.Add("Andhra Pradesh");

                     ddlCity.Items.Add("Arunachal Pradesh");

                     ddlCity.Items.Add("Bhopal");

                     ddlCity.Items.Add("Maharastra");

                     ddlCity.Items.Add("Mumbai");

                     ddlCity.Items.Add("Chennai");

                    ddlCity.Items.Add("Kerala");

                     ddlCity.Items.Add("Orissa");

                     ddlCity.Items.Add("Karnataka");

                     ddlCity.Items.Add("Bangalore");

                     ddlCity.Items.Add("Trivandrum");

                    ddlCity.Items.Add("Vizag");

                     ddlCity.Items.Add("Vijayawada");

                     ddlCity.Items.Add("Guntur");

                }

                else if (ddlCountry.SelectedItem.Text == "Pakistan")

                {

                     ddlCity.Items.Clear();

                     ddlCity.Items.Insert(0, new ListItem("Select City", "0"));

                     ddlCity.Items.Add("Lahore");

                     ddlCity.Items.Add("Rawilpindi");

                     ddlCity.Items.Add("P1");

                    ddlCity.Items.Add("P2");

                     ddlCity.Items.Add("P3");

                     ddlCity.Items.Add("P4");

                     ddlCity.Items.Add("P5");

                     ddlCity.Items.Add("P6");

                     ddlCity.Items.Add("P7");

                     ddlCity.Items.Add("P8");

                     ddlCity.Items.Add("P9");

                     ddlCity.Items.Add("P10");

                     ddlCity.Items.Add("P11");

                     ddlCity.Items.Add("P12");

                }

                else if (ddlCountry.SelectedItem.Text == "Bangladesh")

                {

                     ddlCity.Items.Clear();

                     ddlCity.Items.Insert(0, new ListItem("Select City", "0"));

                     ddlCity.Items.Add("b1");

                    ddlCity.Items.Add("b2");

                     ddlCity.Items.Add("b3");

                     ddlCity.Items.Add("b4");

                     ddlCity.Items.Add("b5");

                     ddlCity.Items.Add("b6");

                }

               else if (ddlCountry.SelectedIndex == 0)

                {

                     ddlCity.Items.Clear();

                     ddlCity.Items.Insert(0, new ListItem("Select City", "0"));

                }

            }

            catch (Exception ex)

            {

                strError = ex.ToString();

            }

        }

    }

}