paypal transacties inlezen naar sql server

[blokbuster23]

middels het onderstaande stukje code wil ik paypal transacties inlezen richting onze sql server.
soms krijg ik de foutmelding:

conversion of one or more characters from xml to target collation impossible

de oorzaak lijkt mij te maken te hebben met het omzetten van data uit xml naar sql


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PayPalService.Classes;
using System.Net;
using System.IO;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace PayPalService
{
class Program
{
static void Main(string[] args)
{
DateTime startDate = Startdate;
DateTime endDate = DateTime.Now;
if (args.Length>=1) startDate = DateTime.Parse(args[0]);
if (args.Length >= 2) endDate = DateTime.Parse(args[1]);
foreach (Account acc in Accounts)
{
Console.WriteLine("*** NEW TRANSACTIONS ***");
for (DateTime d = startDate; d <= endDate; d = d.AddMinutes(15))
{
string sTransactions = "<Transactions>" + GetTransactions(acc, d, d.AddMinutes(15).AddMilliseconds(-1)) + "</Transactions>";
//Console.WriteLine(sTransactions);
SaveAccountTransactions(acc.AccountID, sTransactions);
Console.WriteLine(string.Format("{2}: SAVED !!! (AccountID: {0} Datum: {1})", acc.AccountID, d.ToString("yyyy-MM-dd HH:mm:ss"), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
}
Console.WriteLine("*** UPDATE TRANSACTIONS ***");
foreach (string transactionID in TransactionsToUpdate)
{
string sTransactions = "<Transactions>" + GetTransactions(acc, transactionID) + "</Transactions>";
//Console.WriteLine(sTransactions);
SaveAccountTransactions(acc.AccountID, sTransactions);
Console.WriteLine(string.Format("{2}: SAVED !!! (AccountID: {0} ID: {1})", acc.AccountID, transactionID, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
}
}
Console.WriteLine("KLAAR !!!");
//Console.ReadLine();
}

const string APIVersion = "&VERSION=66.0";
const string ConnectionString = @"Data Source=server-CRM;Initial Catalog=PayPal;User ID=paypal;Password=eenpassword";


static DateTime Startdate
{
get
{
DateTime startDate = DateTime.Parse("1900-01-01");
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
using (SqlCommand dbCommand = new SqlCommand(@"P_GetStartDate", conn))
{
dbCommand.CommandType = CommandType.StoredProcedure;
SqlDataReader reader = dbCommand.ExecuteReader();
reader.Read();
if (!String.IsNullOrEmpty(reader["StartDate"].ToString())) startDate = DateTime.Parse(reader["StartDate"].ToString());
reader.Close();
reader.Dispose();
}
conn.Close();
return startDate.AddSeconds(1);
}
}

static List<string> TransactionsToUpdate
{
get
{
List<string> transactions = new List<string>();

SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
using (SqlCommand dbCommand = new SqlCommand(@"P_GetTransactionsToUpdate", conn))
{
dbCommand.CommandType = CommandType.StoredProcedure;
SqlDataReader reader = dbCommand.ExecuteReader();
while (reader.Read())
{
transactions.Add((string)reader["TransactionID"]);
}
reader.Close();
reader.Dispose();
}
conn.Close();
return transactions;
}
}

static List<Account> Accounts
{
get
{
List<Account> accounts = new List<Account>();

SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
using (SqlCommand dbCommand = new SqlCommand(@"P_GetAccounts", conn))
{
dbCommand.CommandType = CommandType.StoredProcedure;
SqlDataReader reader = dbCommand.ExecuteReader();
while (reader.Read())
{
accounts.Add(
new Account()
{
AccountID = (int)reader["AccountID"],
Server = (string)reader["Server"],
UserName = (string)reader["UserName"],
Password = (string)reader["Password"],
Signature = (string)reader["Signature"]
}
);
}
reader.Close();
reader.Dispose();
}
conn.Close();
return accounts;
}
}

static string GetTransactions(Account Account, string TransactionID)
{
string sNVP = string.Format(
Account.Credentials + APIVersion +
"&METHOD=TransactionSearch&TRANSACTIONID={0}",
TransactionID
);
return GetTransactionsBase(Account, sNVP);
}

static string GetTransactions(Account Account, DateTime Start, DateTime End)
{
string sNVP = string.Format(
Account.Credentials + APIVersion +
"&METHOD=TransactionSearch&STARTDATE={0}&ENDDATE={1}",
Start.ToString("yyyy-MM-ddTHH:mm:ss"),
End.ToString("yyyy-MM-ddTHH:mm:ss")
);
return GetTransactionsBase(Account, sNVP);
}

static string GetTransactionsBase(Account Account, string NVP)
{
string sXml = "";

string sNVP = NVP;
//string sNVP = string.Format(
// Account.Credentials + APIVersion +
// "&METHOD=TransactionSearch&STARTDATE={0}&ENDDATE={1}",
// Start.ToString("yyyy-MM-ddTHH:mm:ss"),
// End.ToString("yyyy-MM-ddTHH:mm:ss")
// );

//Console.WriteLine(sNVP);

try
{
//Create web request and web response objects, make sure you using the correct server (sandbox/live)
HttpWebRequest wrWebRequest = (HttpWebRequest)WebRequest.Create(Account.Server);
wrWebRequest.Method = "POST";
StreamWriter requestWriter = new StreamWriter(wrWebRequest.GetRequestStream());
requestWriter.Write(sNVP);
requestWriter.Close();

// Get the response.
HttpWebResponse hwrWebResponse = (HttpWebResponse)wrWebRequest.GetResponse();
StreamReader responseReader = new StreamReader(wrWebRequest.GetResponse().GetResponseStream());

//and read the response
string responseData = responseReader.ReadToEnd();
responseReader.Close();

string result = HttpUtility.UrlDecode(responseData);

string[] arrResult = result.Split('&');
foreach (string responseItem in arrResult)
{
string[] responseItemArray = responseItem.Split('=');
//if (responseItemArray[0].StartsWith("L_TRANSACTIONID")) sTransactionID = responseItemArray[1];
//if (responseItemArray[0].StartsWith("L_TYPE")) Console.WriteLine(responseItemArray[1]);
if (responseItemArray.Length == 2) sXml += string.Format("<{0}>{1}</{0}>", responseItemArray[0], HttpUtility.HtmlEncode(responseItemArray[1]));
if (responseItemArray[0].StartsWith("L_TRANSACTIONID"))
{
string nr = responseItemArray[0].Substring(15);
string transactionID = responseItemArray[1];
string transactionDetails = GetTransactionDetails(Account, transactionID);
var temp = String.Format("<TransactionDetails{0}>",nr) + transactionDetails + String.Format("</TransactionDetails{0}>", nr);
sXml = sXml + temp.ToString();
}
}


}
catch (Exception ex)
{
// do something to catch the error, like write to a log file.
Console.WriteLine(string.Format("GetTransactions: error processing ({0})", ex.Message));
}

return sXml;
}

static string GetTransactionDetails(Account Account, string TransactionID)
{
string sXml = "";

string sNVP = string.Format(
Account.Credentials + APIVersion +
"&METHOD=GetTransactionDetails&TRANSACTIONID={0}",
TransactionID
);

//Console.WriteLine(sNVP);

try
{
//Create web request and web response objects, make sure you using the correct server (sandbox/live)
HttpWebRequest wrWebRequest = (HttpWebRequest)WebRequest.Create(Account.Server);
wrWebRequest.Method = "POST";
StreamWriter requestWriter = new StreamWriter(wrWebRequest.GetRequestStream());
requestWriter.Write(sNVP);
requestWriter.Close();

// Get the response.
HttpWebResponse hwrWebResponse = (HttpWebResponse)wrWebRequest.GetResponse();
StreamReader responseReader = new StreamReader(wrWebRequest.GetResponse().GetResponseStream());

//and read the response
string responseData = responseReader.ReadToEnd();
responseReader.Close();

string result = HttpUtility.UrlDecode(responseData);

string[] arrResult = result.Split('&');
foreach (string responseItem in arrResult)
{
string[] responseItemArray = responseItem.Split('=');
if (responseItemArray.Length == 2) sXml += string.Format("<{0}>{1}</{0}>", responseItemArray[0], HttpUtility.HtmlEncode(responseItemArray[1]));
}

}
catch (Exception ex)
{
// do something to catch the error, like write to a log file.
Console.WriteLine(string.Format("GetTransactionDetails: error processing ({0})", ex.Message));
}

return sXml;
}

static void SaveAccountTransactions(int AccountID, string Transactions)
{
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
using (SqlCommand dbCommand = new SqlCommand(@"P_SaveAccountTransactions", conn))
{
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.Add(new SqlParameter("AccountID", AccountID));
dbCommand.Parameters.Add(new SqlParameter("Transactions", Transactions));
dbCommand.ExecuteNonQuery();
}
conn.Close();
}
}
}

[gorgi_19]

Gokje:
Of je encoding is verkeerd, of je localization (cultureinfo) is verkeerd.

[Voor 9% gewijzigd door gorgi_19 op 27-06-2011 15:33]

[CodeCaster]

Je post een probleem en een lap code, maar niet wat je zelf hebt gedaan. Heb je de fout al eens in Google gegooid? En wat heb je aan de eerste tien hits die je daar krijgt, die allemaal spreken van encoding?

[Janoz]

Het is niet de bedoeling om een enorme lap code te posten (en dan ook nog zonder [code] tags waardoor het redelijk onleesbaar wordt) met daarbij de melding "Dit werkt niet en ik krijg soms deze fout".

Ga debuggen. Breng je code terug naar de relevante delen en geef in je topic aan wat je al geprobeerd hebt. Dit topic gaat op slot. Wanneer je een topicstart kunt maken die wel voldoet aan de quickstart mag je een nieuw topic openen. (en vergeet dan niet de [code] tags)