Sunday, August 16, 2015

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:

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:

How to insert record into Sql Server database using WCF service
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:

How to insert record into Sql Server database using 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.

How to insert record into Sql Server database using WCF service

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.

How to insert record into Sql Server database using WCF service

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:
How to insert record into Sql Server database using WCF service

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

No comments:

Post a Comment