How to insert record into Sql Server database using WCF service

In this tutorial I am going to explain how to insert record into Sql Server database using WCF service

Description:

In the previous article I have explained Introduction to Window communicationfoundation (WCF) in Asp.net, Create and Consume WCF Service using visual studio2013 and WCF services VS Web services in Asp.net

We have to perform task to insert 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:

Create a store procedure to insert record in database table

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

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. Write the below given code in Iservice.cs below the ServiceContract.

public interface IService

{        

    [OperationContract]

    void InsertMovieData(Movie objmovie);

}

[DataContract]

public class Movie

{

    [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 below given code to define the definition of function InsertMovieData to insert the records.

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

    }

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:

To consume the WCF service create 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. In this website ServiceReference is the namespace.

Add a webform to website.

HTML Markup of Webform:

   <table>

            <tr><td>Name :</td><td><asp:TextBox ID="txtname" runat="server"></asp:TextBox></td></tr>

       <tr><td></td><td></td></tr>

            <tr><td>Genre :</td><td> <asp:TextBox ID="txtgenre" runat="server"></asp:TextBox></td></tr>

       <tr><td></td><td></td></tr>

            <tr><td>Budget :</td><td><asp:TextBox ID="txtcost" runat="server"></asp:TextBox></td></tr>

       <tr><td></td><td></td></tr>

            <tr><td>Upload poster :</td><td>

                <asp:FileUpload ID="FileUpload1" runat="server" /></td></tr>     

       <tr><td></td><td></td></tr>    

            <tr><td></td><td>

                <asp:Button ID="btnsave" runat="server" Text="Save" OnClick="btnsave_Click" /></td></tr>

        </table>

Add the namespace

using ServiceReference;

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

    protected void btnsave_Click(object sender, EventArgs e)

    {

        try

        {

            ServiceReference.ServiceClient objclient = new ServiceReference.ServiceClient();

            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() + FileUpload1.PostedFile.FileName;

            FileUpload1.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>");

            ClearControls();

        }

        catch(Exception ex)

        { }

    }

    private void ClearControls()

    {

        txtname.Text = string.Empty;

        txtgenre.Text = string.Empty;

        txtcost.Text = string.Empty;

    }

Build and run the project.

Result:

  In this article we have learn to how to insert record into database using WCF service in asp.net. I hope you enjoyed this article.