Friday, August 21, 2015

WCF tutorial to Insert, Bind, Edit, update and Delete the record from sql server database in Asp.net

In this tutorial I am going to explain how to Insert, Bind, Edit, update and Delete the record using WCF service from sql server database in Asp.net using C#

Description:

We have to perform the following task to Insert, Bind, Edit, update and Delete the record into Sql Server database:
Create Database and table
Create a WCF service
Create a Web application (Website) to consume the WCF service

Implementation:
I have created a Table Movie:

WCF tutorial to Insert, Bind, Edit, update and Delete the record from sql server database in Asp.net

Create store procedures to Insert, Update, Delete and fetch record.


Procedure to Insert record:
Create PROCEDURE Sp_InsertMovieDetail
(
@name varchar(100),
@genre varchar(100),
@cost int,
@poster varchar(max)
)
AS
BEGIN
            SET NOCOUNT ON;
Insert into Movie(Name,Genre,Cost,Poster) values(@name,@genre,@cost,@poster)
END

Procedure to Delete record:
Create PROCEDURE Sp_DeleteMovieRecord
(
@id int
)
AS
BEGIN
            SET NOCOUNT ON;
Delete from Movie where Id =@id
END

Procedure to Update record:
Create PROCEDURE Sp_UpdateMovieDetail
(
@id int,
@name varchar(100),
@genre varchar(100),
@cost int
)
AS
BEGIN
            SET NOCOUNT ON;
Update Movie set Name=@name,Genre=@genre,Cost=@cost where Id =@id
END

Procedure to Fetch record:
Create Proc Sp_GetMovieData
as
begin
Select * from Movie
End

Create a WCF Service:
To create a WCF open Visual Studio >> File>> New >> Project >> Visual C# >> WCF service application >> Type the name of WCF service that you want to keep. In this tutorial WCFService is the name of service. 
WCF service application will open.

First of all open the web.config file and set the connectionString.
<connectionStrings>
  <add name="Connection" connectionString="Data Source=VIJAY-PC;Initial Catalog=Demo;Integrated Security=True"/>
</connectionStrings>

Now open the Iservice.cs file and remove the sample code from it.

Add the namespace
using System.Data;

Write the below given code in Iservice.cs below the ServiceContract.
public interface IService
{        
    [OperationContract]
    void InsertMovieData(Movie objmovie);
    [OperationContract]
    DataSet GetMovieData();
    [OperationContract]
    void UpdateMovieRecord(Movie objmovie);
    [OperationContract]
    void DeleteMovieRecord(Movie objmovie);
}

[DataContract]
public class Movie
{
    [DataMember]
    public int id { get; set; }
    [DataMember]
    public string name {get; set;}
    [DataMember]
    public string genre{ get; set;}
    [DataMember]
    public int Cost { get; set; }
    [DataMember]
    public string Poster { get; set; }  
}

After that move to Service.svc.cs/Service.cs file and also remove the sample code.

Add the namespace
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

Write the code to define the definition of function to insert, get, delete and update the record.

  SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Connection"].ToString());
    public void InsertMovieData(Movie objmovie)
    {
        SqlCommand cmd = new SqlCommand("Sp_InsertMovieDetail", con);
        cmd.CommandType = CommandType.StoredProcedure;
        con.Open();
        cmd.Parameters.AddWithValue("@name", objmovie.name);
        cmd.Parameters.AddWithValue("@genre", objmovie.genre);
        cmd.Parameters.AddWithValue("@cost", objmovie.Cost);
        cmd.Parameters.AddWithValue("@poster", objmovie.Poster);
        cmd.ExecuteNonQuery();
        con.Close();
    }
    public DataSet GetMovieData()
    {
        SqlDataAdapter adp = new SqlDataAdapter("Sp_GetMovieData", con);
        adp.SelectCommand.CommandType = CommandType.StoredProcedure;
        DataSet ds = new DataSet();
        adp.Fill(ds);
        return ds;
    }
    public void UpdateMovieRecord(Movie objmovie)
    {
        try
        {
            SqlCommand cmd = new SqlCommand("Sp_UpdateMovieDetail", con);
            cmd.CommandType = CommandType.StoredProcedure;
            con.Open();
            cmd.Parameters.AddWithValue("@id", objmovie.id);
            cmd.Parameters.AddWithValue("@name", objmovie.name);
            cmd.Parameters.AddWithValue("@genre", objmovie.genre);
            cmd.Parameters.AddWithValue("@cost", objmovie.Cost);
           // cmd.Parameters.AddWithValue("@poster", objmovie.Poster);
            cmd.ExecuteNonQuery();
            con.Close();
        }
        catch(Exception ex)
        { }
    }
    public void DeleteMovieRecord(Movie objmovie)
    {
        SqlCommand cmd = new SqlCommand("Sp_DeleteMovieRecord", con);
        cmd.CommandType = CommandType.StoredProcedure;
        con.Open();
        cmd.Parameters.AddWithValue("@id", objmovie.id);
        cmd.ExecuteNonQuery();
        con.Close();
    }

Build the project and run.
Don’t close the WCF service application keep it running to consume.

Create a Web application:
To consume the WCF service creates a new website.
After creating new website go to Solution Explore and Right click on Website >> ADD >> Click on Service reference. On click Add service reference pop up will be open. Copy the URL of WCF service and paste it in displaying input. Enter the namespace and click on Go button. 
WCF tutorial to Insert, Bind, Edit, update and Delete the record from sql server database in Asp.net

In this website ServiceReference is the namespace.

Add a webform to website.
HTML Markup of Webform:
<asp:GridView ID="grdmovie" runat="server" Width="550px" AutoGenerateColumns="False" ShowFooter="True" DataKeyNames="Id"
     CellPadding="4" ForeColor="#333333" GridLines="None" OnRowCommand="grdmovie_RowCommand" OnRowCancelingEdit="grdmovie_RowCancelingEdit" OnRowEditing="grdmovie_RowEditing" OnRowDeleting="grdmovie_RowDeleting" OnRowUpdating="grdmovie_RowUpdating">
  <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    <Columns>
        <asp:TemplateField HeaderText="Name">
            <ItemTemplate>
                <asp:Label ID="lblname" runat="server" Text='<%# Eval("Name") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="txtname" runat="server" Text='<%# Eval("Name") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtname" runat="server" />
                <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="Enter Movie Name" ControlToValidate="txtname"></asp:RequiredFieldValidator>
            </FooterTemplate>
            <ItemStyle HorizontalAlign="Center" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Genre">
            <ItemTemplate>
               <asp:Label ID="lblgenre" runat="server" Text='<%# Eval("Genre") %>'></asp:Label>
            </ItemTemplate>
             <EditItemTemplate>
                <asp:TextBox ID="txtgenre" runat="server" Text='<%# Eval("Genre") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtgenre" runat="server" />
                 <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ErrorMessage="Enter Genre" ControlToValidate="txtgenre"></asp:RequiredFieldValidator>
            </FooterTemplate>
             <ItemStyle HorizontalAlign="Center" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Budget(In Crore)">
            <ItemTemplate>
               <asp:Label ID="lblcost" runat="server" Text='<%# Eval("Cost") %>'></asp:Label>
            </ItemTemplate>
             <EditItemTemplate>
                <asp:TextBox ID="txtcost" runat="server" Text='<%# Eval("Cost") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtcost" runat="server"></asp:TextBox>
                 <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ErrorMessage="Enter Budget" ControlToValidate="txtcost"></asp:RequiredFieldValidator>
            </FooterTemplate>
             <ItemStyle HorizontalAlign="Center" />
        </asp:TemplateField>
           <asp:TemplateField HeaderText="Poster">
            <ItemTemplate>
                 <asp:Image ID="Image1" ImageUrl='<%# Eval("Poster") %>' runat="server" width="200px"/>
            </ItemTemplate>             
            <FooterTemplate>
                <asp:FileUpload ID="FileUpload1" runat="server" />
                   </FooterTemplate>
                <ItemStyle HorizontalAlign="Center" />
        </asp:TemplateField>
            <asp:TemplateField>
            <ItemTemplate>
             <asp:ImageButton ID="imgbtnedit" runat="server" ImageUrl="~/btnimages/edit.png" CommandName="Edit" CausesValidation="false"/> <asp:ImageButton ID="imgbtndelete" runat="server" ImageUrl="~/btnimages/delete.png" CommandName="Delete" CausesValidation="false"/>
            </ItemTemplate>
                <EditItemTemplate>
                    <asp:ImageButton ID="imgcancel" runat="server" ImageUrl="~/btnimages/cancel.png" CommandName="Cancel" CausesValidation="false"/><asp:ImageButton ID="imgupdate" runat="server" ImageUrl="~/btnimages/update.png" CommandName="Update" CausesValidation="false"/>
                     </EditItemTemplate>
            <FooterTemplate>
                <asp:ImageButton ID="imgbtninsert" runat="server" ImageUrl="~/btnimages/insert.png" CommandName="Insert"/>
            </FooterTemplate>
                <ItemStyle VerticalAlign="Top" Width="400px" />
        </asp:TemplateField>
    </Columns>
                       <EditRowStyle BackColor="#999999" />
                       <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                       <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                       <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                       <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                       <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                       <SortedAscendingCellStyle BackColor="#E9E7E2" />
                       <SortedAscendingHeaderStyle BackColor="#506C8C" />
                       <SortedDescendingCellStyle BackColor="#FFFDF8" />
                       <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>

Add the namespace
using ServiceReference;
using System.Data;

Now create the object of WCF service and write the below given code:

ServiceReference.ServiceClient objclient = new ServiceReference.ServiceClient();
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!IsPostBack)
        {
            BindGridview();
        }
    }
    public void BindGridview()
    {
        DataSet ds = new DataSet();
        ds = objclient.GetMovieData();
        grdmovie.DataSource = ds;
        grdmovie.DataBind();
    }
    protected void grdmovie_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "Insert")
        {
            TextBox txtname = (TextBox)grdmovie.FooterRow.FindControl("txtname");
            TextBox txtgenre = (TextBox)grdmovie.FooterRow.FindControl("txtgenre");
            TextBox txtcost = (TextBox)grdmovie.FooterRow.FindControl("txtcost");
            FileUpload fileupload = (FileUpload)grdmovie.FooterRow.FindControl("FileUpload1");
            Movie objmovie = new Movie();
            objmovie.name = txtname.Text;
            objmovie.genre = txtgenre.Text;
            objmovie.Cost = Convert.ToInt32(txtcost.Text);

            string filepath = Server.MapPath("~/images/") + Guid.NewGuid() + fileupload.PostedFile.FileName;
            fileupload.SaveAs(filepath);
            string fl = filepath.Substring(filepath.LastIndexOf("\\"));
            string[] split = fl.Split('\\');
            string newpath = split[1];
            string imagepath = "~/images/" + newpath;
            objmovie.Poster = imagepath;
            objclient.InsertMovieData(objmovie);
            Response.Write("<script>alert('Record Insert Successfully');</script>");
            txtname.Text = string.Empty;
            txtcost.Text = string.Empty;
            txtgenre.Text = string.Empty;
            BindGridview();
        }
        if (e.CommandName == "Delete")
        {
            ImageButton imgbtn = (ImageButton)e.CommandSource;
            GridViewRow gvrow = ((GridViewRow)imgbtn.NamingContainer);
            int id = Convert.ToInt32(grdmovie.DataKeys[gvrow.RowIndex].Value.ToString());
            Movie objmovie = new Movie();
            objmovie.id = id;
            objclient.DeleteMovieRecord(objmovie);
            Response.Write("<script>alert('Record Delete Successfully');</script>");
            BindGridview();
        }
        if (e.CommandName == "Update")
        {
            ImageButton imgbtn = (ImageButton)e.CommandSource;
            GridViewRow gvrow = ((GridViewRow)imgbtn.NamingContainer);
            int id = Convert.ToInt32(grdmovie.DataKeys[gvrow.RowIndex].Value.ToString());
            Movie objmovie = new Movie();
            TextBox txtname = (TextBox)grdmovie.Rows[gvrow.RowIndex].FindControl("txtname");
            TextBox txtgenre = (TextBox)grdmovie.Rows[gvrow.RowIndex].FindControl("txtgenre");
            TextBox txtcost = (TextBox)grdmovie.Rows[gvrow.RowIndex].FindControl("txtcost");
            objmovie.id = id;
            objmovie.name = txtname.Text;
            objmovie.genre = txtgenre.Text;
            objmovie.Cost = Convert.ToInt32(txtcost.Text);
            objclient.UpdateMovieRecord(objmovie);
            Response.Write("<script>alert('Record Update Successfully');</script>");
            grdmovie.EditIndex = -1;
            BindGridview();
        }
    }
    protected void grdmovie_RowEditing(object sender, GridViewEditEventArgs e)
    {
        grdmovie.EditIndex = e.NewEditIndex;
        BindGridview();
    }
    protected void grdmovie_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        grdmovie.EditIndex = -1;
        BindGridview();
    }
    protected void grdmovie_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {

    }
    protected void grdmovie_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {

    }

Build and run the project.

 Result:
WCF tutorial to Insert, Bind, Edit, update and Delete the record from sql server database in Asp.net
  In this article we have learn to how to Insert, Select, Edit, Update and Delete record in Gridview using WCF service in asp.netI hope you enjoyed this article. 

2 comments: