WCF后方法实现

Question

I have a WCF service which is connected to an sql server database which is called from a mobile application. I have the following method which helps create bookings.

public void CreateBooking(Booking booking)
    {
        Booking newbooking = new Booking();
        sql = new SqlConnection("Data Source=comp;Initial Catalog=BookingDB;Integrated Security=True");
        sql.Open();

        string command =
            ("INSERT INTO Bookings( BookingName, BookingStart, BookingEnd, RoomID ) " +
             "VALUES ("
                + "'" + booking.BookingName + "'" + ", "   
                + "'" + booking.BookingStart  + "'" + ", " 
                + "'" + booking.BookingEnd + "'" + ", "
                        + booking.RoomID + ")");

        SqlCommand cmd = new SqlCommand(command, sql);                
        cmd.ExecuteNonQuery();            
    }

    public void Close()
    {
        sql.Close();
    }

Markup:

<%@ ServiceHost Language="C#" Debug="true" Service="BookingServices.BookingService" CodeBehind="BookingService.svc.cs" Factory="System.ServiceModel.Activation.WebServiceHostFactory" %>

Config File:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <system.web>
    <!--<authentication mode="None"/>-->
    <compilation debug="true" targetFramework="4.0">
      <assemblies>
        <add assembly="System.Data.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
      </assemblies>
    </compilation>
  </system.web>
  <system.serviceModel>
<services>
  <service name="RoomBookingServices.RoomBookingService" behaviorConfiguration="RoomBookingServiceBehaviour">
    <endpoint address="http://192.168.0.4:6321/RoomBookingServices/RoomBookingService.svc" binding="webHttpBinding" bindingConfiguration="webHttpBindingWithJsonP" contract="RoomBookingServices.IRoomBookingService" behaviorConfiguration="webHttpBehavior">
      <identity>
        <servicePrincipalName value=""/>
      </identity>
    </endpoint>
  </service>
</services>
<behaviors>
  <serviceBehaviors>
    <behavior name="RoomBookingServiceBehaviour">
      <!-- To avoid disclosing metadata information, 
      set the value below to false and remove the metadata endpoint above before deployment -->
      <serviceMetadata httpGetEnabled="true" />
      <!-- To receive exception details in faults for debugging purposes, set the value below to true.  
      Set to false before deployment to avoid disclosing exception information -->
          <serviceDebug includeExceptionDetailInFaults="true" />
       </behavior>
     </serviceBehaviors>
      <endpointBehaviors>
        <behavior name="webHttpBehavior">
         <webHttp />
    </behavior>
  </endpointBehaviors>
</behaviors>
<bindings>
  <webHttpBinding>
    <binding name="webHttpBindingWithJsonP" crossDomainScriptAccessEnabled="true"></binding>
  </webHttpBinding>
</bindings>
<!--<serviceHostingEnvironment multipleSiteBindingsEnabled="true" />-->
<!--<serviceHostingEnvironment aspNetCompatibilityEnabled="true" multipleSiteBindingsEnabled="true" />-->
  </system.serviceModel>
  <system.webServer>
    <modules runAllManagedModulesForAllRequests="true" />
  </system.webServer>
  <connectionStrings>
    <add name="RoomBookingDatabaseEntities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=HAL;initial catalog=RoomBookingDatabase;integrated security=True;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
    <add name="RoomBookingDatabaseEntities1" connectionString="metadata=res://*/RoomBookingDB.csdl|res://*/RoomBookingDB.ssdl|res://*/RoomBookingDB.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=HAL;initial catalog=RoomBookingDatabase;integrated security=True;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>

Interface:

[OperationContract(Name="postmethod")]
    [WebInvoke(Method = "POST", BodyStyle = WebMessageBodyStyle.WrappedRequest, RequestFormat = WebMessageFormat.Json, UriTemplate = "postmethod/new")]

    void CreateBooking(Booking booking); 
}

Booking class:

[DataContract]
public class Booking
{
    [DataMember]
    public int BookingID { get; set; }

    [DataMember]
    public string BookingName { get; set; }

    [DataMember]
    public DateTime BookingStart { get; set; }

    [DataMember]
    public DateTime BookingEnd { get; set; }

    [DataMember]
    public int RoomID { get; set; }

}

However, whenever I call the method I am getting a 405 error. My question is, is the method above causing the error or is it something in the connection end of things? Thanks.

Answer 1

When you are hosting a WCF service in IIS the address attribute is inferred from the location to the .svc file as hosted in IIS so it should either empty or a relative address:

<service name="RoomBookingServices.RoomBookingService" behaviorConfiguration="RoomBookingServiceBehaviour">
    <endpoint 
        address="" 
        binding="webHttpBinding" 
        bindingConfiguration="webHttpBindingWithJsonP"
        contract="RoomBookingServices.IRoomBookingService" 
        behaviorConfiguration="webHttpBehavior">
    </endpoint>
</service>

The base address will be provided by IIS and the site where you are hosting your application under. So it will point to the location of the RoomBookingService.svc file as hosted in IIS.

Also when I look at the following code:

string command =
        ("INSERT INTO Bookings( BookingName, BookingStart, BookingEnd, RoomID ) " +
         "VALUES ("
            + "'" + booking.BookingName + "'" + ", "   
            + "'" + booking.BookingStart  + "'" + ", " 
            + "'" + booking.BookingEnd + "'" + ", "
                    + booking.RoomID + ")");

SqlCommand cmd = new SqlCommand(command, sql);

my eyes start to bleed. You should absolutely never write any code like that. Always use parametrized queries when dealing with SQL. Your code is vulnerable to SQL injection.

So:

public void CreateBooking(Booking booking)
{
    using (var conn = new SqlConnection("Data Source=comp;Initial Catalog=BookingDB;Integrated Security=True"))
    using (var cmd = conn.CreateCommand())
    {
        conn.Open();
        cmd.CommandText = 
        @"INSERT INTO 
          Bookings( BookingName, BookingStart, BookingEnd, RoomID ) 
          VALUES ( @BookingName, @BookingStart, @BookingEnd, @RoomID )";

        cmd.Parameters.AddWithValue("@BookingName", booking.BookingName);
        cmd.Parameters.AddWithValue("@BookingStart", booking.BookingStart);
        cmd.Parameters.AddWithValue("@BookingEnd", booking.BookingEnd);
        cmd.Parameters.AddWithValue("@RoomID", booking.RoomID);
        cmd.ExecuteNonQuery();
    }
}

And now you could invoke this service. For example using jQuery AJAX:

$.ajax({
    url: '/RoomBookingService.svc/postmethod/new',
    type: 'POST',
    contentType: 'application/json',
    data: JSON.stringify({ 
        booking: {
            BookingID: 1,
            BookingName: 'bn',
            BookingStart: '/Date(1232739449000+0000)/',
            BookingEnd: '/Date(1232776449000+0000)/',
            RoomID: 2
        }
    }),
    success: function (result) {

    }
});