I have created a program that pulls data from an excel spreadsheet and pushes it to a foxpro database. However I have encountered an issue with duplicate client records appearing.
This is due to one client being associated with multiple other records.
I need to know how to check the database to see if a particular record exists before writing, however I'm drawing a complete blank.
My code (for this particular class) as it stands reads like the below:
namespace PropertyImport
{
public class Landlord
{
public void Import()
{
int IDCOUNT = 0;
using (var exportConnection = new OleDbConnection(connectionString: Settings.ImportFrom))
using (var importConnection = new OleDbConnection(connectionString: Settings.ImportTo))
using (OleDbCommand exportCommand = new OleDbCommand(@"SELECT
[clcodel salute],
[clcodel fname],
[clcodel sname]
from [export_test$]"
, exportConnection))
using (OleDbCommand importCommand = new OleDbCommand(@"INSERT INTO CLIENT
(CLCODE,CLCODEDESC,CLCLASS,
FNAME,MNAME,SNAME
)
VALUES
(?,?,?,?,?,?,?,?)",
importConnection))
{
OleDbDataReader exportReader;
//
importCommand.Parameters.AddWithValue("CLCODE", "");
importCommand.Parameters.AddWithValue("CLCODEDESC", "");
//importCommand.Parameters.AddWithValue("CLCLASS", "");
//importCommand.Parameters.AddWithValue("NEGOTIATOR", "");
//importCommand.Parameters.AddWithValue("TITLE", "");
importCommand.Parameters.AddWithValue("FNAME", "");
importCommand.Parameters.AddWithValue("MNAME", "");
importCommand.Parameters.AddWithValue("SNAME", "");
// Open connections to excel sheet and foxpro database
exportConnection.Open();
importConnection.Open();
Console.WriteLine("Visual Foxpro connection open");
Console.WriteLine("Writing to table");
Console.WriteLine("...");
int nLoopCount = 0;
string space = " ";
// Initiate the reader to excel
exportReader = exportCommand.ExecuteReader();
// Start reading
while (exportReader != null && exportReader.Read())
{
//Set parameter values whilst reading from excel
string LandTitle = exportReader.IsDBNull(0)
? string.Empty
: Convert.ToString(exportReader.GetValue(0)).Trim();
string LandFname = exportReader.IsDBNull(1)
? string.Empty
: Convert.ToString(exportReader.GetValue(1)).Trim();
string LandSname = exportReader.IsDBNull(2)
? string.Empty
: Convert.ToString(exportReader.GetValue(2));
string CLCODE = string.Concat(LandFname, space, LandSname, " (P)").Trim();
Console.WriteLine("Working on record {0}, {1}", IDCOUNT, CLCODE);
importCommand.Parameters["CLCODE"].Value = string.Concat(LandFname, space, LandSname, " (P)").Trim();
importCommand.Parameters["CLCODEDESC"].Value = string.Concat(LandTitle, space, LandFname, space, LandSname).Trim();
importCommand.Parameters["TITLE"].Value = LandTitle.Trim();
importCommand.Parameters["FNAME"].Value = LandFname.Trim();
importCommand.Parameters["SNAME"].Value = LandSname.Trim();
try
{
importCommand.ExecuteNonQuery();
}
catch (Exception e)
{
Console.Write("Error Writing to database");
Console.Write(e);
Console.ReadKey();
}
// We must close and re-open the connection after a certain number of records or the OLEDB FoxPro SQL will eventually fail
if (nLoopCount % 100 == 0)
{
importConnection.Close();
importConnection.Open();
}
}
// done
exportConnection.Close();
importConnection.Close();
Console.WriteLine("Landlord Import Complete!");
Console.WriteLine("Press any key to continue...");
Console.ReadKey();
if (Settings.ImportPropertyPause) Console.ReadKey();
}
}
}
}
I want to do something like:
if (CLCODE exists)
{
Do not not create record
}
else
{
create record
}
Joshua, You are making it unnecessarily complex. On a multiuser environment you would have problems with a "check and insert if not exists" logic. If you think that is not a problem, you could do an ExecuteScalar() to check the count of rows with that clcode. If 0 then it doesn't exist.
If you would do this way, then prefer using Linq. With Linq it would simply be an:
bool exists = vfpdb.Clients.Any(c=>c.ClCode == "codetocheck");
to see if it exists or not.
Anyway, there is a much simpler way, well it is a trick really but one that works. Assuming you have client.dbf under "d:\backyard\temp" and "d:\temp\myclients.xlsx" file (just for demo purposes that is what I chose):
void Main()
{
string vfpInsert = @"Insert Into client
(CLCODE,CLCODEDESC,CLCLASS,FNAME,MNAME,SNAME)
SELECT CLCODE,CLCODEDESC,CLCLASS,FNAME,MNAME,SNAME
from (Iif(Xmltocursor(?,'xlData') > 0, 'xlData','')) xl
where Not Exists
(Select * From client c2 Where c2.CLCODE == xl.CLCODE)";
var xml = GetExcelData();
using (OleDbConnection con=new OleDbConnection(@"provider=VFPOLEDB;Data Source="+@"d:\backyard\temp"))
using (OleDbCommand cmd = new OleDbCommand(vfpInsert,con))
{
cmd.Parameters.Add("xldata", OleDbType.VarChar).Value = xml;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
private string GetExcelData()
{
string dataSource = @"D:\temp\myclients.xlsx";
DataTable t = new DataTable("Clients");
using (OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" +
string.Format("Data Source={0};", dataSource) +
"Extended Properties=\"Excel 12.0;HDR=Yes\""))
using (OleDbCommand cmd = new OleDbCommand("Select * from [clients$]", con))
{
con.Open();
t.Load(cmd.ExecuteReader());
con.Close();
}
using (MemoryStream ms = new MemoryStream())
using (var xmlwriter = XmlTextWriter.Create(ms))
{
t.WriteXml(xmlwriter, XmlWriteMode.WriteSchema);
xmlwriter.Flush();
xmlwriter.Close();
ms.Position = 0;
using (StreamReader streamreader = new StreamReader(ms))
{
return streamreader.ReadToEnd();
}
}
}
EDIT: Here is an explanation what the code does: