четверг, 8 сентября 2011 г.

Call WCF service (on Sharepoint 2010) from MSSQL2008 x64

Hello, I call wcf service (on sharepoint 2010) from MSSQL 2008 x64.
First open Visual studio, and create empty sharepoint project and add wcf project.
example


using Microsoft.SharePoint.Client.Services;
using System.ServiceModel.Activation;
using Microsoft.SharePoint;
using System;
using System.Text;

namespace SharePointWCF
{
    [BasicHttpBindingServiceMetadataExchangeEndpoint]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
    public class WCFSql : IWCFSql
    {
        // To test this service, use the Visual Studio WCF Test client
        // set the endpoint to http://sp1/_vti_bin/SharePointWCF/WCFSql.svc/mex
        public string UpdateStatus(string url,string testSTR)
        {
            SPSecurity.RunWithElevatedPrivileges(delegate() {
            using (SPWeb web = new SPSite(url).OpenWeb())
            {
                SPList list = web.Lists["HelloSql"];
                SPListItem item = list.AddItem();
                item["Title"] = testSTR;
                item.Update();
            }
            });
            return "true";
        }
    }
}


Example sql Trigger:

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Web;
using SqlServerProject1.SQLService;
using System.ServiceModel;
public partial class Triggers
{

    // Enter existing table or view for the target and uncomment the attribute line
    public static void Trigger1()
    {
        System.ServiceModel.EndpointAddress ep = new System.ServiceModel.EndpointAddress(new Uri("http://sp1/_vti_bin/SharePointWCF/WCFSql.svc"));
        BasicHttpBinding binding = new BasicHttpBinding();
        binding.Security.Mode = BasicHttpSecurityMode.TransportCredentialOnly;
        binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Ntlm;
        //Create an instance of the service proxye
        SqlServerProject1.SQLService.WCFSqlClient SqlService = new SqlServerProject1.SQLService.WCFSqlClient(binding, ep);
        SqlService.ChannelFactory.Credentials.Windows.ClientCredential = new System.Net.NetworkCredential("login", "pass", "domain");
        SqlService.ClientCredentials.Windows.ClientCredential = new System.Net.NetworkCredential("login", "pass", "domen");
        SqlService.ClientCredentials.UserName.UserName = @"domain\login";
        SqlService.ClientCredentials.UserName.Password = @"pass";
        // Replace with your own code
        SqlContext.Pipe.Send("Trigger FIRED");
        SqlTriggerContext triggContext = SqlContext.TriggerContext;
        SqlConnection conn = new SqlConnection("context connection =true");
        conn.Open();
        SqlCommand sqlComm = conn.CreateCommand();
        SqlPipe sqlP = SqlContext.Pipe;
        SqlDataReader dr;
        sqlP.Send("ddddd");
        sqlComm.CommandText = "SELECT [SP_ID],[Status] from inserted";
        dr = sqlComm.ExecuteReader();
        while (dr.Read())
            SqlService.UpdateStatus("http://sp1/",dr[0].ToString());
        
    }
}

Next go to MSSQL2008.

Steps.
1. Go to the management studio of SQL Server, first makes sure we are CLR enabled. 
user master
go
sp_configure 'clr enabled', 1
go
reconfigure
go


2. Set the database trushworthy
alter database [database name]
set trustworthy on;
go


3. Create assemblies. When I created relate assemblies, I got numerous errors. I' going to explain how I made them worked. 

problem 1)
Msg 33009, Level 16, State 2, Procedure usp_xxxxx, Line 0
The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘YourDatabase’. You should correct this situation by resetting the owner of database ‘YourDatabase’ using the ALTER AUTHORIZATION statement.


=> I executed the below command. 
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false

Sum up

all created assemblies and their paths are the following.
1) System.Runtime.Serialization
'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\System.Runtime.Serialization.dll'

2) SMDiagnostics
'c:\Windows\Microsoft.net\Framework\v3.0\Windows Communication Foundation\SMdiagnostics.dll'


3) System.Web
'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll'


4) System.Messaging
'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Messaging.dll'


5) System.IdentityModel
'C:\Windows\winsxs\msil_system.identitymodel_b77a5c561934e089_6.1.7601.17514_none_f1b6cabd00b1e098\System.IdentityModel.dll'


6) System.IdentityModel.Selectors
'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.Selectors.dll'      


7) Microsoft.Transactions.Bridge
'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll'      


8) System.ServiceModel
'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll'


9) System.Drawing
'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Drawing.dll'

Test you trigger!