.net VB.NET: c# c#.net development https vb VB.NET wcf winforms wsdl
by Derek
leave a comment
WCF and WSDL and HTTPS and WinForms
So everytime I bring a post to this blog, I try to make sure it something that other people don’t just post willy nilly. So here is an interesting problem I had the other day, and the series of searches and trials, and time spent to bring this to some poor soul looking for an answer.
The problem: You are using .NET 3.0 or 3.5, and you need to connect to an older Web Service. Specifically, you need to connect to a WSDL that is secured. The WebService address could be something such as: https://www.here.com/service.wsdl
When you use Visual Studio to make this connection it does many things incorrectly. The big error you are likely to encounter, less any specifics about the WSDL you are trying to connect to is:
Type TEXT/XML is not valid, Type Application/XML was expected.
In one easy little new Binding, we can make this all go away. For the novices here is a detailed explanation.
Take this custom binding here
Stick it into your Bindings section in App.Config and point the EndPoint to use that binding. You should be off and running in NO TIME!
VB.NET: .net dts execute execution remote server sql ssis VB.NET
by Derek
2 comments
Remote execution of SSIS packages from an application server, setting custom parameters.
Generally I am known on this website for bringing infrequent, but wise tips to as many developers as I can. *wink* *wink* Here is an attempt to ramp up my efforts to help educate people, convert as much C# code to VB, and create some real Microsoft development enthusiasts.
This post will cover, SQL Server Integrated Services (SSIS), and namely one question, with several requirements. You want to execute an SSIS package remotely, perhaps from an ASP.NET webpage? You have a need to do this adhoc, with multiple users. You need to set variables inside the package, hell why else would you go through all the hassle to run this on demand?!
Ok so this sounds like a big issue. First, this is not a beginners SSIS post. If you are looking for how to set custom parameters through execution of SSIS this is not the place to start. However that said, I will provide a brief overview of how it works.
SSIS allows you to use the DTEXEC command (a windows executable, and SQL server command) to execute SSIS packages stored in various locations (local, ssis package store). The following option in a command line allows you to set a variable at runtime.
/SET "\package.variables[VariableName]“;”Value”By setting these commands at runtime, you just increased the power of your SSIS capabilties 10 fold. You can now user the power and localization of the SQL server to bear the grunt work of many data tasks, while you leave your application server’s resources left open for other user crunching. Now we are on the path for removing mindless data routines out of code, into a proper visual designer, and enchancing out application performance by offloading data routines to something that is dedicated to data!
Ok with that little bit of education out of the way lets continue! We’re assuming that you understand SSIS and SQL Server enough, to be able to Create an SSIS package, upload it to your server and execute it from a SQL Agent Job. This includes making a proxy. In addition to this, you will need a user that belongs to msdb, and can execute dts jobs. (dtsadmin)
Now for the code! This is converted, revamped from dtRemoteExec in C# found on codeplex. There were a few issues I found with this process in daily use. I have started to modify (it is by no means perfect) this code, which can be used in enterprise wide applications. Eventually I would like to have the code build the DTS Exec string for you, but this is still needed.
Here is a little explanation of how things work first! I almost forgot. You will need a DTS execution string like this.
"/SQL "\Maintenance Plans\SSISPACKAGE" /SERVER "b3studios" /USER username /PASSWORD password /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /SET "\package.variables[ExportLocation]“;”C:\Export”
I prefer to store the package with in the SSIS Package store. HOWEVER!!! Do not use a /DTS for calling the package, which is the default when using SSIS Package Store. This will almost certinly fail, or produce sporatic results. Instead, I prefer to execute the package from the SQL Server, using the /SQL command, let me know your thoughts in the comments.
Ok wheew mouth full, and I should rewrite a lot of this! But I’m too busy to do so! Here is the code, dubbed RemoteSSIS, based on dtexecremote, and rewritten in VB.NET
Here are the calls you will use from your application.
Dim oRemote As New RemoteSSIS
oRemote.sqlServer(False) = GetServer()
oRemote.useProxy("ProxyName") = True
oRemote.username = "Username"
oRemote.password = "Password"
oRemote.dtsCommand = DtsCommand
If oRemote.CreateJob() Then
oRemote.RunJob()
End If
If oRemote.iscomplete Then
Return oRemote.ClearJob()
End If
Here is the code you can add to any module in your project. Add the following references,
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlSever.SMO
Microsoft.SqlSever.SmoEnum
Microsoft.SqlServer.SqlEnum
Imports System.Data
Imports System.Data.Sql
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Smo.Agent
Imports Microsoft.SqlServer.Management.Common
Public Class RemoteSSIS
#Region "Variables"
'entry variables
Private strSqlServer As String = Nothing
Private strDtsCommand As String = Nothing
Private strProxyName As String = Nothing
Private boolUseProxy As Boolean = False
Private boolSecureConnection As Boolean = False
Private strUsername As String = Nothing
Private strPassword As String = Nothing
Private jobCreated As Boolean = False
Private jobRan As Boolean = True
Private jobCleared As Boolean = True
Private strjobStatus As String = Nothing
Private boolisfinished As Boolean = False
'server objects
Private serverConn As ServerConnection = New ServerConnection
Private svr As Server
Private js As JobServer
Private jb As Job
#End Region
#Region "Properties"
Public Property sqlServer(ByVal secureConnection As Boolean) As String
Get
Return strSqlServer
End Get
Set(ByVal value As String)
strSqlServer = value
End Set
End Property
Public Property dtsCommand() As String
Get
Return strDtsCommand
End Get
Set(ByVal value As String)
strDtsCommand = value
End Set
End Property
Public Property useProxy(Optional ByVal ProxyName As String = Nothing) As Boolean
Get
Return boolUseProxy
End Get
Set(ByVal value As Boolean)
boolUseProxy = value
strProxyName = ProxyName
If boolUseProxy = True And strProxyName = Nothing Then Throw New Exception("can not set use proxy to true, and proxy to Nothing")
End Set
End Property
Public Property username() As String
Get
Return strUsername
End Get
Set(ByVal value As String)
strUsername = value
End Set
End Property
Public Property password() As String
Get
Return strPassword
End Get
Set(ByVal value As String)
strPassword = value
End Set
End Property
Public ReadOnly Property iscomplete() As Boolean
Get
Return boolisfinished
End Get
End Property
Public ReadOnly Property jobStatus() As String
Get
Return strjobStatus
End Get
End Property
#End Region
#Region "Public Methods"
Public Function CreateJob() As Boolean
Try
'create a new sqlserver object
serverConn.ServerInstance = strSqlServer
serverConn.LoginSecure = boolSecureConnection
serverConn.Login = strUsername 'these should be moved over to the config file
serverConn.Password = strPassword 'these should be moved over to the config file
'create a connection to the server
svr = New Server(serverConn)
'set the jobserver
js = svr.JobServer
'create a unique job
Dim jobName As String = "dtexecRemote_temp_job_" + Guid.NewGuid.ToString
'create a new job catagory
Dim jc As JobCategory = New JobCategory(js, "dtexecRemote")
'set the job type to local
jc.CategoryType = CategoryType.LocalJob
'refresh the job category to see if it exists
jc.Refresh()
If Not jc.State = SqlSmoState.Existing Then
jc.Create()
End If
'create a new job
jb = New Job(js, jobName)
'set the category
jb.Category = jc.Name
'create the job
jb.Create()
'add the ssis goodies next
jb.ApplyToTargetServer(svr.Name)
'create a jobstep pointing to the package
Dim JobStep As JobStep = New JobStep(jb, "run package")
'add the DTS command to the jobstep
JobStep.Command = strDtsCommand
JobStep.ProxyName = strProxyName
'tell the agent to run an ssis job and actions to take
JobStep.SubSystem = AgentSubSystem.Ssis
JobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess
JobStep.OnFailAction = StepCompletionAction.QuitWithFailure
'create the job
JobStep.Create()
jobCreated = True
Return True
Catch ex As Exception
jobCreated = False
Trace.Write(ex.Message)
Return False
End Try
End Function
Public Sub RunJob()
jb.Start()
While (jb.CurrentRunStatus = JobExecutionStatus.Executing)
Threading.Thread.Sleep(TimeSpan.FromSeconds(2))
jb.Refresh()
End While
Do Until Not jb.LastRunOutcome = CompletionResult.InProgress And Not jb.LastRunOutcome = CompletionResult.Unknown
Threading.Thread.Sleep(TimeSpan.FromSeconds(2))
jb.Refresh()
Loop
Dim outcome As CompletionResult = jb.LastRunOutcome
boolisfinished = True
strjobStatus = outcome.ToString
End Sub
Public Function ClearJob() As Boolean
Try
If jb.LastRunOutcome = CompletionResult.Succeeded Then
jb.Drop()
Return True
Else
Return False
End If
Catch ex As Exception
Trace.Write(ex.Message)
Return False
End Try
End Function
#End Region
End Class
VB.NET: accounts contacts conversion convert crm leads microsoft qualify VB.NET
by Derek
1 comment
CRM 3.0, Entity Conversion done easy! Vb.Net
Ok, so your working with Microsoft CRM 3.0, right? And you have hundreds if not thousands of leads. You need to Qualify these leads, and Convert them to Accounts or Contacts. You’ve checked the Web API, but it doesn’t seem possible, or even easy… Well it is. After I spent countless hours surfing google, looking for an answer, I couldn’t find a good one. Here it is folks, and done in the wonderful world of VB instead of C#!
The first order of business is to create a new project, and add the Web Reference for CRM 3.0 to your project. It does not matter what type of project you create, winforms, console, asp.net. This is general VB code that will sit behind what ever interface you choose.
I will not cover CRM Web Reference addition to your project, you should be able to find that through google very easily, or know it before you attempt this :).
First import the CRM namespace. For the purposes of this write up our project is called ConsoleApplication1. And our WebReference is called CRM
Imports ConsoleApplication1.CRM
Next, we need to create an instance of the CRM service.
Dim oCrmService As New CrmService
Great! Now we are getting somewhere :). Lets follow it up by creating a function to return all of our Leads. We will be using FetchXML for this. This will return formatted XML, where you will be able to pull out all of your GUIDs from the LeadID field. The prefered method here, would be to load these into a XML document. I will write an XML memory document post later.
Dim fetch1 As String = _
'change [ and ] to < and > wordpress messed this up
“[fetch mapping='logical']” + _
“[entity name='lead']” + _
“[all-attributes/]” + _
“[/entity]” + _
“[/fetch]”
‘ Fetch the results. Into a String
Dim returnString1 = service.Fetch(fetch1)
To qualify the lead, we do it like this. Very short.
Dim oLead As SetStateLeadRequest = New SetStateLeadRequest 'create request for new change to the state of the lead
'qualify the lead
oLead.EntityId = LeadGUID ' This is the GUID from the fetch statement.
oLead.LeadState = LeadState.Qualified 'set the state of the lead to qualified
oLead.LeadStatus = -1 'not open anymore
oCrmService.Execute(oLead) 'execute the lead change
Now we need to Convert the Lead to an Entity of our Choice.
Dim oContact As InitializeFromRequest = New InitializeFromRequest 'create a request to make a contact
Dim oContactResponse As InitializeFromResponse = New InitializeFromResponse
Dim oContactObject As contact
'setup a entity
oContact.EntityMoniker = New Moniker
oContact.EntityMoniker.Id = LeadGUID
oContact.EntityMoniker.Name = "target entity as string"
oContact.TargetEntityName = TargetEntity
oContact.TargetFieldType = TargetFieldType.All 'target all the records on the lead entity to the target entity
'tell crm server to setup the entity
oContactResponse = oCrmService.Execute(oContact)
'tell crm to create and confirm the entity it setup
oCrmService.Create(oContactResponse.Entity)
Now as you see, this task is much easier than you thought! And there you have it, for you in VB.NET!