private void generateSpreadsheet(string astrWebServiceURI, string astrUsername, string astrPassword, string astrMethod, Microsoft.Office.Interop.Excel.Workbook workbook) { CompilerResults compilerResult = null; compiledAssembly compiledAsm = generateCompiledAssembly(astrWebServiceURI, astrUsername, astrPassword, astrMethod); if (null != (compilerResult = compiledAsm.compilerResults)) { if (compilerResult.Errors.Count <= 0) { var allTypes = compilerResult.CompiledAssembly.GetTypes(); List <Type> lsTypeHierarchy = getArgumentClassType(allTypes, astrMethod); Type tpParametersToPopulate = lsTypeHierarchy.Last(); PropertyInfo[] piProperties = tpParametersToPopulate.GetProperties(); if (null != piProperties) { int i = iDataHorizontalStartPosition; Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.Sheets["Data"]; if (null != worksheet) { spreadsheetDataSetTemplate(worksheet, astrMethod); generateSpreadsheetColumnsRecursive(piProperties, worksheet, i); //foreach (PropertyInfo currentProperty in piProperties) //{ // Microsoft.Office.Interop.Excel.Range rangeName = worksheet.get_Range(GetColumnLetter(i) + (iDataVerticalStartPosition - 2)); // Microsoft.Office.Interop.Excel.Range rangeType = worksheet.get_Range(GetColumnLetter(i) + (iDataVerticalStartPosition + 1 - 2)); // rangeName.Value2 = currentProperty.Name; // rangeName.Font.Bold = true; // rangeType.Value2 = extractHumanReadableType(currentProperty.PropertyType.ToString()); // rangeType.Font.Italic = true; // i++; //} } } } } }
private void btnLoad_Copy_Click(object sender, RoutedEventArgs e) { OpenFileDialog ofdOpenFileDialog = new OpenFileDialog(); ofdOpenFileDialog.Filter = "Excel Files (*.xlsx, *.xls)|*.xlsx;*.xls|All Files (*.*)|*.*"; ofdOpenFileDialog.Multiselect = false; if (true == ofdOpenFileDialog.ShowDialog()) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); if (null != excel) { excel.Visible = true; Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(ofdOpenFileDialog.FileName); if (null != workbook) { Microsoft.Office.Interop.Excel.Worksheet wsParameters = workbook.Sheets["Parameters"]; Microsoft.Office.Interop.Excel.Worksheet wsData = workbook.Sheets["Data"]; if (null != wsParameters && null != wsData) { Microsoft.Office.Interop.Excel.Range rangeWebService = wsParameters.get_Range("C3"); Microsoft.Office.Interop.Excel.Range rangeUsername = wsParameters.get_Range("C4"); Microsoft.Office.Interop.Excel.Range rangePassword = wsParameters.get_Range("C5"); Microsoft.Office.Interop.Excel.Range rangeMethod = wsParameters.get_Range("C7"); Microsoft.Office.Interop.Excel.Range rangeDataStartRow = wsParameters.get_Range("C11"); Microsoft.Office.Interop.Excel.Range rangeDataStartColumn = wsParameters.get_Range("C10"); string strStartRow = rangeDataStartRow.Value2.ToString(); string strStartColumn = rangeDataStartColumn.Value2.ToString(); int.TryParse(strStartRow, out iDataVerticalStartPosition); int.TryParse(strStartColumn, out iDataHorizontalStartPosition); // Microsoft.Office.Interop.Excel.Range rangeInputClassName = wsData.get_Range("D" + (iDataVerticalStartPosition - 3)); string strURI = rangeWebService.Value; string strUsername = rangeUsername.Value; string strPassword = rangePassword.Value; string strMethod = rangeMethod.Value; // string strInputClass = rangeInputClassName.Value; List <Parameter> lsParameters = getParameterColumns(wsData, iDataHorizontalStartPosition, iDataVerticalStartPosition); // generateSpreadsheet(strURI, strUsername, strPassword, strMethod, workbook); compiledAssembly compiledAsm = generateCompiledAssembly(strURI, strUsername, strPassword, strMethod); //if (null != (compilerResults = compiledAsm.compilerResults)) callWebService(generateCompiledAssembly(strURI, strUsername, strPassword, strMethod), strMethod, lsParameters, wsData, iDataVerticalStartPosition); } else { Console.WriteLine("Failed to retrieve Parameters sheet"); } } else { Console.WriteLine("Failed to open " + ofdOpenFileDialog.FileName); } excel.Quit(); } } }
/// <summary> /// compile our wsdl file /// </summary> /// <param name="astrWebServiceURI"></param> /// <param name="astrUsername"></param> /// <param name="astrPassword"></param> /// <param name="astrMethod"></param> /// <returns></returns> private compiledAssembly generateCompiledAssembly(string astrWebServiceURI, string astrUsername, string astrPassword, string astrMethod) { CompilerResults result = null; // Define the WSDL Get address, contract name and parameters, with this we can extract WSDL details any time //Uri address = new Uri("http://ifbenp.indfish.co.nz:16201/mws-ws/services/Vessel?wsdl"); //("http://localhost:64508/Service1.svc?wsdl"); Uri address = new Uri(astrWebServiceURI); // For HttpGet endpoints use a Service WSDL address a mexMode of .HttpGet and for MEX endpoints use a MEX address and a mexMode of .MetadataExchange MetadataExchangeClientMode mexMode = MetadataExchangeClientMode.HttpGet; // Get the metadata file from the service. MetadataExchangeClient metadataExchangeClient = new MetadataExchangeClient(address, mexMode); metadataExchangeClient.ResolveMetadataReferences = true; //One can also provide credentials if service needs that by the help following two lines. ICredentials networkCredential = new NetworkCredential(astrUsername, astrPassword, ""); metadataExchangeClient.HttpCredentials = networkCredential; //Gets the meta data information of the service. MetadataSet metadataSet = metadataExchangeClient.GetMetadata(); // Import all contracts and endpoints. WsdlImporter wsdlImporter = new WsdlImporter(metadataSet); //Import all contracts. Collection <ContractDescription> contracts = wsdlImporter.ImportAllContracts(); //Import all end points. ServiceEndpointCollection allEndpoints = wsdlImporter.ImportAllEndpoints(); // Generate type information for each contract. ServiceContractGenerator serviceContractGenerator = new ServiceContractGenerator(); //Dictinary has been defined to keep all the contract endpoints present, contract name is key of the dictionary item. var endpointsForContracts = new Dictionary <string, IEnumerable <ServiceEndpoint> >(); string contractName = null; foreach (ContractDescription contract in contracts) { serviceContractGenerator.GenerateServiceContractType(contract); // Keep a list of each contract's endpoints. endpointsForContracts[contract.Name] = allEndpoints.Where(ep => ep.Contract.Name == contract.Name).ToList(); contractName = contract.Name; } // Generate a code file for the contracts. CodeGeneratorOptions codeGeneratorOptions = new CodeGeneratorOptions(); codeGeneratorOptions.BracingStyle = "C"; // Create Compiler instance of a specified language. CodeDomProvider codeDomProvider = CodeDomProvider.CreateProvider("C#"); // Adding WCF-related assemblies references as copiler parameters, so as to do the compilation of particular service contract. CompilerParameters compilerParameters = new CompilerParameters(new string[] { "System.dll", "System.ServiceModel.dll", "System.Runtime.Serialization.dll" }); compilerParameters.GenerateInMemory = true; //Gets the compiled assembly. result = codeDomProvider.CompileAssemblyFromDom(compilerParameters, serviceContractGenerator.TargetCompileUnit); object proxyInstance = null; if (result.Errors.Count <= 0) { // Find the proxy type that was generated for the specified contract (identified by a class that implements the contract and ICommunicationbject - this is contract //implemented by all the communication oriented objects). Type proxyType = result.CompiledAssembly.GetTypes().First(t => t.IsClass && t.GetInterface(contractName) != null && t.GetInterface(typeof(ICommunicationObject).Name) != null); // Now we get the first service endpoint for the particular contract. ServiceEndpoint serviceEndpoint = endpointsForContracts[contractName].First(); BasicHttpBinding newBinding = new BasicHttpBinding(); newBinding.Security.Mode = BasicHttpSecurityMode.TransportCredentialOnly; newBinding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Basic; newBinding.Security.Transport.ProxyCredentialType = HttpProxyCredentialType.None; newBinding.Security.Message.AlgorithmSuite = System.ServiceModel.Security.SecurityAlgorithmSuite.Default; newBinding.Security.Message.ClientCredentialType = BasicHttpMessageCredentialType.UserName; serviceEndpoint.Binding = newBinding; // Create an instance of the proxy by passing the endpoint binding and address as parameters. proxyInstance = result.CompiledAssembly.CreateInstance(proxyType.Name, false, System.Reflection.BindingFlags.CreateInstance, null, new object[] { serviceEndpoint.Binding, serviceEndpoint.Address }, CultureInfo.CurrentCulture, null); PropertyInfo clientCredentials = proxyType.GetProperty("ClientCredentials"); if (null != clientCredentials) { ClientCredentials credentials = new ClientCredentials(); credentials.UserName.UserName = astrUsername; credentials.UserName.Password = astrPassword; object objCredentials = clientCredentials.GetValue(proxyInstance); credentials = objCredentials as ClientCredentials; if (null != credentials) { credentials.UserName.Password = astrPassword; credentials.UserName.UserName = astrUsername; } } } compiledAssembly finalResults = new compiledAssembly() { compilerResults = result, instantiatedObject = proxyInstance }; return(finalResults); }
private void callWebService(compiledAssembly compiledAsm, string astrMethod, List <Parameter> alsParameters, Microsoft.Office.Interop.Excel.Worksheet awsData, int aiStartRow) { CompilerResults compilerResult = compiledAsm.compilerResults as CompilerResults; int iErrorCount = 0; DateTime dtStart = DateTime.Now; DateTime dtEnd; if (null != compilerResult && false == string.IsNullOrEmpty(astrMethod)) { var allTypes = compilerResult.CompiledAssembly.GetTypes(); MethodInfo methodInfo = null; //compilerResult.GetType().GetMethod(astrMethod); foreach (var currentMethod in allTypes) { if (null != currentMethod && null != currentMethod.BaseType) { if (null != (methodInfo = currentMethod.GetMethod(astrMethod))) { break; } } } var headerInfo = allTypes.Where(t => t.Name == "lws" || t.Name == "mws").First(); object objHeader = System.Activator.CreateInstance(headerInfo); // this is the type of the final type in our heirachy where we plug in the spreadsheet values Type tpTypeToPopulate = null; // argumentType = getArgumentClassType(allTypes, astrMethod); List <Type> lsTypeHierarchy = getArgumentClassType(allTypes, astrMethod); List <object> lsCallObjects = new List <object>(); // create the object hierachy, the first object in the list should be the one // supplied to the webservice call. The last should be the one that we populate // with data from the spreadsheet createObjectHierachy(lsTypeHierarchy, lsCallObjects, 0); if (lsCallObjects.Count > 0) { tpTypeToPopulate = lsTypeHierarchy.Last(); // object objObjectToSet = lsCallObjects.Last(); object objObjectToPassToCall = lsCallObjects.First(); foreach (object objObjectToSet in lsCallObjects) { tpTypeToPopulate = objObjectToSet.GetType(); for (int i = aiStartRow; i < giMaxRowPosition; i++) { bool bBlank = true; foreach (Parameter currentParameter in alsParameters) { if (null != currentParameter) { var spreadSheetValue = awsData.get_Range(currentParameter.Value + i).Value2; if (null != spreadSheetValue) { // retrieve the value from the spreadsheet string strValue = awsData.get_Range(currentParameter.Value + i).Value2.ToString(); PropertyInfo currentProperty = tpTypeToPopulate.GetProperty(currentParameter.Name); if (null != currentProperty) { if (false == string.IsNullOrWhiteSpace(strValue)) { bBlank = false; } object objValue = strValue; if (typeof(int) == currentProperty.PropertyType) { int iValue = 0; int.TryParse(strValue, out iValue); objValue = iValue; } else if (typeof(decimal) == currentProperty.PropertyType) { decimal decValue = 0; decimal.TryParse(strValue, out decValue); objValue = decValue; } else if (typeof(double) == currentProperty.PropertyType) { double dblValue = 0; double.TryParse(strValue, out dblValue); objValue = dblValue; } else if (typeof(DateTime) == currentProperty.PropertyType) { DateTime dtValue = DateTime.MinValue; DateTime.TryParse(strValue, out dtValue); objValue = dtValue; } // save the parameter tpTypeToPopulate.InvokeMember(currentParameter.Name, BindingFlags.Instance | BindingFlags.Public | BindingFlags.SetProperty, Type.DefaultBinder, objObjectToSet, new object[] { objValue }); } else { // property doesn't exist } } } } // if we have a blank line we should stop if (true == bBlank) { break; } else { // call to the webservice here try { methodInfo.Invoke(compiledAsm.instantiatedObject, new object[] { objHeader, objObjectToPassToCall }); awsData.get_Range(gstrResultColumn + i).Value2 = "OK"; } catch (Exception ex) { awsData.get_Range(gstrResultColumn + i).Value2 = ex; iErrorCount++; } } } } dtEnd = DateTime.Now; awsData.get_Range(gstrErrorCountCell).Value2 = iErrorCount; awsData.get_Range(gstrStartTimeCell).Value2 = dtStart.ToString("yyyy-MM-dd HH:mm:ss"); awsData.get_Range(gstrEndTimeCell).Value2 = dtEnd.ToString("yyyy-MM-dd HH:mm:ss"); awsData.get_Range(gstrDurationCell).Value2 = (dtEnd - dtStart).TotalSeconds; } } }
/// <summary> /// compile our wsdl file /// </summary> /// <param name="astrWebServiceURI"></param> /// <param name="astrUsername"></param> /// <param name="astrPassword"></param> /// <param name="astrMethod"></param> /// <returns></returns> private compiledAssembly generateCompiledAssembly(string astrWebServiceURI, string astrUsername, string astrPassword, string astrMethod) { CompilerResults result = null; // Define the WSDL Get address, contract name and parameters, with this we can extract WSDL details any time //Uri address = new Uri("http://ifbenp.indfish.co.nz:16201/mws-ws/services/Vessel?wsdl"); //("http://localhost:64508/Service1.svc?wsdl"); Uri address = new Uri(astrWebServiceURI); // For HttpGet endpoints use a Service WSDL address a mexMode of .HttpGet and for MEX endpoints use a MEX address and a mexMode of .MetadataExchange MetadataExchangeClientMode mexMode = MetadataExchangeClientMode.HttpGet; // Get the metadata file from the service. MetadataExchangeClient metadataExchangeClient = new MetadataExchangeClient(address, mexMode); metadataExchangeClient.ResolveMetadataReferences = true; //One can also provide credentials if service needs that by the help following two lines. ICredentials networkCredential = new NetworkCredential(astrUsername, astrPassword, ""); metadataExchangeClient.HttpCredentials = networkCredential; //Gets the meta data information of the service. MetadataSet metadataSet = metadataExchangeClient.GetMetadata(); // Import all contracts and endpoints. WsdlImporter wsdlImporter = new WsdlImporter(metadataSet); //Import all contracts. Collection<ContractDescription> contracts = wsdlImporter.ImportAllContracts(); //Import all end points. ServiceEndpointCollection allEndpoints = wsdlImporter.ImportAllEndpoints(); // Generate type information for each contract. ServiceContractGenerator serviceContractGenerator = new ServiceContractGenerator(); //Dictinary has been defined to keep all the contract endpoints present, contract name is key of the dictionary item. var endpointsForContracts = new Dictionary<string, IEnumerable<ServiceEndpoint>>(); string contractName = null; foreach (ContractDescription contract in contracts) { serviceContractGenerator.GenerateServiceContractType(contract); // Keep a list of each contract's endpoints. endpointsForContracts[contract.Name] = allEndpoints.Where(ep => ep.Contract.Name == contract.Name).ToList(); contractName = contract.Name; } // Generate a code file for the contracts. CodeGeneratorOptions codeGeneratorOptions = new CodeGeneratorOptions(); codeGeneratorOptions.BracingStyle = "C"; // Create Compiler instance of a specified language. CodeDomProvider codeDomProvider = CodeDomProvider.CreateProvider("C#"); // Adding WCF-related assemblies references as copiler parameters, so as to do the compilation of particular service contract. CompilerParameters compilerParameters = new CompilerParameters(new string[] { "System.dll", "System.ServiceModel.dll", "System.Runtime.Serialization.dll" }); compilerParameters.GenerateInMemory = true; //Gets the compiled assembly. result = codeDomProvider.CompileAssemblyFromDom(compilerParameters, serviceContractGenerator.TargetCompileUnit); object proxyInstance = null; if (result.Errors.Count <= 0) { // Find the proxy type that was generated for the specified contract (identified by a class that implements the contract and ICommunicationbject - this is contract //implemented by all the communication oriented objects). Type proxyType = result.CompiledAssembly.GetTypes().First(t => t.IsClass && t.GetInterface(contractName) != null && t.GetInterface(typeof(ICommunicationObject).Name) != null); // Now we get the first service endpoint for the particular contract. ServiceEndpoint serviceEndpoint = endpointsForContracts[contractName].First(); BasicHttpBinding newBinding = new BasicHttpBinding(); newBinding.Security.Mode = BasicHttpSecurityMode.TransportCredentialOnly; newBinding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Basic; newBinding.Security.Transport.ProxyCredentialType = HttpProxyCredentialType.None; newBinding.Security.Message.AlgorithmSuite = System.ServiceModel.Security.SecurityAlgorithmSuite.Default; newBinding.Security.Message.ClientCredentialType = BasicHttpMessageCredentialType.UserName; serviceEndpoint.Binding = newBinding; // Create an instance of the proxy by passing the endpoint binding and address as parameters. proxyInstance = result.CompiledAssembly.CreateInstance(proxyType.Name, false, System.Reflection.BindingFlags.CreateInstance, null, new object[] { serviceEndpoint.Binding, serviceEndpoint.Address }, CultureInfo.CurrentCulture, null); PropertyInfo clientCredentials = proxyType.GetProperty("ClientCredentials"); if(null != clientCredentials) { ClientCredentials credentials = new ClientCredentials(); credentials.UserName.UserName = astrUsername; credentials.UserName.Password = astrPassword; object objCredentials = clientCredentials.GetValue(proxyInstance); credentials = objCredentials as ClientCredentials; if (null != credentials) { credentials.UserName.Password = astrPassword; credentials.UserName.UserName = astrUsername; } } } compiledAssembly finalResults = new compiledAssembly() { compilerResults = result, instantiatedObject = proxyInstance }; return (finalResults); }
private void callWebService(compiledAssembly compiledAsm, string astrMethod, List<Parameter> alsParameters, Microsoft.Office.Interop.Excel.Worksheet awsData, int aiStartRow) { CompilerResults compilerResult = compiledAsm.compilerResults as CompilerResults; int iErrorCount = 0; DateTime dtStart = DateTime.Now; DateTime dtEnd; if (null != compilerResult && false == string.IsNullOrEmpty(astrMethod)) { var allTypes = compilerResult.CompiledAssembly.GetTypes(); MethodInfo methodInfo = null; //compilerResult.GetType().GetMethod(astrMethod); foreach (var currentMethod in allTypes) { if (null != currentMethod && null != currentMethod.BaseType) { if(null != (methodInfo = currentMethod.GetMethod(astrMethod))) { break; } } } var headerInfo = allTypes.Where(t => t.Name == "lws" || t.Name == "mws").First(); object objHeader = System.Activator.CreateInstance(headerInfo); // this is the type of the final type in our heirachy where we plug in the spreadsheet values Type tpTypeToPopulate = null; // argumentType = getArgumentClassType(allTypes, astrMethod); List<Type> lsTypeHierarchy = getArgumentClassType(allTypes, astrMethod); List<object> lsCallObjects = new List<object>(); // create the object hierachy, the first object in the list should be the one // supplied to the webservice call. The last should be the one that we populate // with data from the spreadsheet createObjectHierachy(lsTypeHierarchy, lsCallObjects, 0); if(lsCallObjects.Count > 0) { tpTypeToPopulate = lsTypeHierarchy.Last(); // object objObjectToSet = lsCallObjects.Last(); object objObjectToPassToCall = lsCallObjects.First(); foreach(object objObjectToSet in lsCallObjects) { tpTypeToPopulate = objObjectToSet.GetType(); for (int i = aiStartRow; i < giMaxRowPosition; i++) { bool bBlank = true; foreach (Parameter currentParameter in alsParameters) { if (null != currentParameter) { var spreadSheetValue = awsData.get_Range(currentParameter.Value + i).Value2; if (null != spreadSheetValue) { // retrieve the value from the spreadsheet string strValue = awsData.get_Range(currentParameter.Value + i).Value2.ToString(); PropertyInfo currentProperty = tpTypeToPopulate.GetProperty(currentParameter.Name); if (null != currentProperty) { if (false == string.IsNullOrWhiteSpace(strValue)) { bBlank = false; } object objValue = strValue; if (typeof(int) == currentProperty.PropertyType) { int iValue = 0; int.TryParse(strValue, out iValue); objValue = iValue; } else if (typeof(decimal) == currentProperty.PropertyType) { decimal decValue = 0; decimal.TryParse(strValue, out decValue); objValue = decValue; } else if (typeof(double) == currentProperty.PropertyType) { double dblValue = 0; double.TryParse(strValue, out dblValue); objValue = dblValue; } else if (typeof(DateTime) == currentProperty.PropertyType) { DateTime dtValue = DateTime.MinValue; DateTime.TryParse(strValue, out dtValue); objValue = dtValue; } // save the parameter tpTypeToPopulate.InvokeMember(currentParameter.Name, BindingFlags.Instance | BindingFlags.Public | BindingFlags.SetProperty, Type.DefaultBinder, objObjectToSet, new object[] { objValue }); } else { // property doesn't exist } } } } // if we have a blank line we should stop if (true == bBlank) { break; } else { // call to the webservice here try { methodInfo.Invoke(compiledAsm.instantiatedObject, new object[] { objHeader, objObjectToPassToCall }); awsData.get_Range(gstrResultColumn + i).Value2 = "OK"; } catch (Exception ex) { awsData.get_Range(gstrResultColumn + i).Value2 = ex; iErrorCount++; } } } } dtEnd = DateTime.Now; awsData.get_Range(gstrErrorCountCell).Value2 = iErrorCount; awsData.get_Range(gstrStartTimeCell).Value2 = dtStart.ToString("yyyy-MM-dd HH:mm:ss"); awsData.get_Range(gstrEndTimeCell).Value2 = dtEnd.ToString("yyyy-MM-dd HH:mm:ss"); awsData.get_Range(gstrDurationCell).Value2 = (dtEnd - dtStart).TotalSeconds; } } }