Thanks to Ryan Farley, Jonathan Douglas, and Mark Dykun for their patience, insights, tricks, and methods. I run this off of a button on a Form. Just ran 155,000 rows without a hiccup or a memory leak.
///////////////////////////////////////////////////////////////////////////////////////////
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Windows.Forms;
using System.Reflection;
using System.Reflection.Emit;
namespace OSS_ImportC
{
static class Program
{
///
/// The main entry point for the application.
///
[STAThread]
public static void Main(string[] args)
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}
class RunIt
{
static string slxUSERID;
static string slxOwnerID;
static string ContactStatus;
static string ContactType;
static string AccountStatus;
static string AccountType;
static string sQuery;
// Application Files
static string gImportSource;
// Import Variables
static string sAddressID;
static string sContactID;
static string sAccountID;
static long intContactNO;
static string sCompany;
//OCC Import Table: OCC_CUST_MASTER in SQL Server.
static string strSalutation;
static string strPrefix;
static string strFirstName;
static string strMiddleName;
static string strLastName;
static string strSUFFIX;
static string strEMail;
static string strEM;
static string strJobTitle;
static string strAddress1;
static string strAddress2;
static string strCity;
static string strState;
static string strZipCode;
static string strCountry;
static string strPhone1;
static string strPhone2;
static DateTime strCreateDate;
static string strBrokerFirmName;
static string strClientClass;
static string strEmployeeID;
static string strAgeGroup;
static string strIncomeRange;
static string strSMail;
static DateTime strDateEntered;
static DateTime strDateUpdated;
static string strCompanyName;
static string strBrokerName;
static string strSLX_BrokerID;
static string strSLX_LeadsourceID;
static Int32 intCustomerID;
static string strErrorMsg;
static string Broker_AccountID;
//SQL 2000 Connection strings.
static string SQLconnstring = "server=RJSAMP-D610;database=OCC;uid=SYSDBA;pwd=masterkey";
static string OLEDBconnstring = "Provider=SQLOLEDB.1 assword=masterkeyersist Security Info=True;User ID=SYSDBA;Initial Catalog=OCC;Data Source=RJSAMP-D610";
static string SLXconnstring = GetSlxConnString("RJSAMP-D610", "OCC", "Admin", "", "");
public void ImportIt()
{
ImportData();
}
//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
static void ImportData()
{
// create Connections.
// OCC_Cust_Master_DataSet.OCC_Cust_MasterDataTable;
SqlConnection srcconn = new SqlConnection(SQLconnstring);
SqlConnection trgconn = new SqlConnection(SQLconnstring);
OleDbConnection SLXConn = new OleDbConnection(SLXconnstring);
//Open the connection
srcconn.Open();
//MessageBox.Show(SLXconnstring);
//Open the connection
trgconn.Open();
SLXConn.Open();
string iStartNo = "0";
// Int32 iStartNo = GetField("MAX(CONTACT_NO) ", "CONTACT_NO", "CONTACT_NO IS NOT NULL", SLXConn);
//Create a command
sQuery = "select CUSTOMERID FROM OCC_CUST_MASTER WHERE CUSTOMERID >= " + iStartNo + " ORDER BY CUSTOMERID ";
SqlCommand selectSqlCommand = new SqlCommand(sQuery, srcconn);
SqlDataReader DR = selectSqlCommand.ExecuteReader();
int icount;
icount = 0;
ContactStatus = "Import";
ContactType = "Individual Investor";
AccountStatus = "Import";
AccountType = "Family";
slxUSERID = "ADMIN ";
slxOwnerID = "SYST00000001";
gImportSource = "Customer Master";
while (DR.Read())
{
icount++;
int ipos;
ipos = 0;
// Get the Row Id from the BATCH record Set. Then Process one row at a time. "CUSTOMERID is the field name, non null, unique.
intCustomerID = Int32.Parse(DR[0].ToString());
// test for existing Contact No : intCustomerID
sContactID = GetField("CONTACTID", "CONTACT_NO", "CONTACT_NO = " + intCustomerID + " ", SLXConn);
if (sContactID == null)
{
// test for existing Contact No : intCustomerID
sContactID = GetField("CONTACTID", "CONTACT", "CONTACTNO = " + intCustomerID + " ", SLXConn);
}
if (sContactID == null)
{
// SqlCommand.CommandType = CommandType.Text;
sQuery = " SELECT * FROM OCC_CUST_MASTER WHERE CUSTOMERID = " + intCustomerID;
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(sQuery, trgconn);
//now create the DataSet and use the adapter to fill it
DataSet ds = new DataSet();
da.Fill(ds);
//pull out the created DataTable to work with
//our table is the first and only one in the tables collection
DataTable table = ds.Tables[0];
//iterate through the rows in the table's Rows collection
foreach (DataRow dr1 in table.Rows)
{
Int32 intCustID = (Int32)dr1[0];
strCreateDate = (DateTime)dr1[1];
ipos = 2;
if (dr1[ipos] is DBNull)
{
strSalutation = "";
}
else { strSalutation = dr1[ipos].ToString(); }
ipos++;
if (dr1[ipos] is DBNull)
{
strFirstName = "";
}
else { strFirstName = dr1[ipos].ToString(); }
ipos++;
if (dr1[ipos] is DBNull)
{
strMiddleName = "";
}
else
{ strMiddleName = dr1[ipos].ToString(); }
ipos++;
if (dr1[ipos] is DBNull)
{
strLastName = "";
}
else
{ strLastName = dr1[ipos].ToString(); }
ipos++;
if (dr1[ipos] is DBNull)
{
strSUFFIX = "";
}
else { strSUFFIX = dr1[ipos].ToString(); }
ipos++;
if (dr1[ipos] is DBNull)
{
strCompanyName = "";
}
else { strCompanyName = dr1[ipos].ToString(); }
ipos++;
if (dr1[ipos] is DBNull)
{
strAddress1 = "";
}
else { strAddress1 = dr1[ipos].ToString(); }
ipos++;
if (dr1[ipos] is DBNull)
{
strAddress2 = "";
}
else { strAddress2 = dr1[ipos].ToString(); }
ipos++;
if (dr1[ipos] is DBNull)
{
strCity = "";
}
else { strCity = dr1[ipos].ToString(); }
ipos++;
if (dr1[ipos] is DBNull)
{
strState = "";
}
else { strState = dr1[ipos].ToString(); }
ipos++;
if (dr1[ipos] is DBNull)
{
strZipCode = "";
}
else
{
strZipCode = dr1[ipos].ToString();
}
ipos++;
if (dr1[ipos] is DBNull)
{
strCountry = "USA";
}
else
{
strCountry = dr1[ipos].ToString();
if (strCountry == "")
{
strCountry = "USA";
}
}
ipos++;
if (dr1[ipos] is DBNull)
{ strEMail = ""; }
else
{ strEMail = dr1[ipos].ToString(); }
ipos++;
if (dr1[ipos] is DBNull)
{ strBrokerFirmName = ""; }
else { strBrokerFirmName = dr1[ipos].ToString(); }
ipos++;
if (dr1[ipos] is DBNull)
{ strPhone1 = ""; }
else { strPhone1 = dr1[ipos].ToString(); }
ipos++;
if (dr1[ipos] is DBNull)
{ strPhone2 = ""; }
else { strPhone2 = dr1[ipos].ToString(); }
ipos++;
if (dr1[ipos] is DBNull)
{ strJobTitle = ""; }
else { strJobTitle = dr1[ipos].ToString(); }
ipos++;
if (dr1[ipos] is DBNull)
{ strEmployeeID = ""; }
else { strEmployeeID = dr1[ipos].ToString(); }
ipos++;
if (dr1[ipos] is DBNull)
{ }
else { strDateEntered = (DateTime)dr1[ipos]; }
ipos++;
if (dr1[ipos] is DBNull)
{ }
else { strDateUpdated = (DateTime)dr1[ipos]; }
ipos++;
if (dr1[ipos] is DBNull)
{ strAgeGroup = ""; }
else { strAgeGroup = dr1[ipos].ToString(); }
ipos++;
if (dr1[ipos] is DBNull)
{ strIncomeRange = ""; }
else { strIncomeRange = dr1[ipos].ToString(); }
ipos++;
if (dr1[ipos] is DBNull)
{ ContactType = ""; }
else
{
strClientClass = dr1[ipos].ToString();
if (strClientClass == "")
{ strClientClass = "Individual Investor"; }
}
ContactType = strClientClass;
if (dr1[ipos] is DBNull)
{
strEM = "";
}
else { strEM = dr1[ipos].ToString(); }
if (dr1[ipos] is DBNull)
{
strSMail = "";
}
else { strSMail = dr1[ipos].ToString(); }
sCompany = strLastName + " Family of " + strCity + " " + strEMail + " " + strPhone1;
if (sCompany.Length > 127)
{ strCompanyName = sCompany.Substring(127); }
else
{ strCompanyName = sCompany; }
// MessageBox.Show(strCompanyName);
sContactID = CreateIDFor("CONTACT", SLXConn);
sAccountID = CreateIDFor("ACCOUNT", SLXConn);
sAddressID = CreateIDFor("ADDRESS", SLXConn);
// MessageBox.Show(strAddress1 + " " + strAddress2 + " " + strCity + " " + strState + " " + strZipCode + " " + strCountry + " " + sAddressID + " " + sAccountID + " " + strCompanyName);
CreateAddress(SLXConn, strAddress1, strAddress2, strCity, strState, strZipCode, strCountry, sAddressID, sAccountID, (string)"");
CreateAccount(SLXConn, sAccountID, strCompanyName, sAddressID);
sAddressID = CreateIDFor("ADDRESS", SLXConn);
CreateAddress(SLXConn, strAddress1, strAddress2, strCity, strState, strZipCode, strCountry, sAddressID, sContactID, strFirstName + " " + strLastName);
CreateContact(SLXConn, sAccountID, strCompanyName, sAddressID, intCustomerID);
} //foreach
da.Dispose();
table.Dispose();
ds.Dispose();
}
}
DR.Close();
srcconn.Dispose();
}
//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
static void CreateAddress(OleDbConnection Conn, string sAddLine1, string sAddLine2, string sAddCity, string sAddState, string sAddZIPCode, object sAddCountry, string sAddAddressID, string sAddEntityID, string DearJohn)
{
// -------------------------------------------------------
// Create an address record for this account
string sQuery = "INSERT INTO ADDRESS (ADDRESSID, ENTITYID, DESCRIPTION, ADDRESS1, ADDRESS2, CITY, POSTALCODE, STATE, COUNTRY,ISPRIMARY, ISMAILING, SALUTATION, TYPE) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
try
{
OleDbCommand cmd = new OleDbCommand( sQuery, Conn);
cmd.Parameters.AddWithValue("ADDRESSID", sAddAddressID);
cmd.Parameters.AddWithValue("ENTITYID", sAddEntityID);
cmd.Parameters.AddWithValue("DESCRIPTION", (string)"{Primary");
cmd.Parameters.AddWithValue("ADDRESS1", sAddLine1);
cmd.Parameters.AddWithValue("ADDRESS2", sAddLine2);
cmd.Parameters.AddWithValue("CITY", sAddCity);
cmd.Parameters.AddWithValue("POSTALCODE", sAddZIPCode);
cmd.Parameters.AddWithValue("STATE", sAddState);
cmd.Parameters.AddWithValue("COUNTRY", sAddCountry);
cmd.Parameters.AddWithValue("ISPRIMARY",(string)"T");
cmd.Parameters.AddWithValue("ISMAILING", (string)"T");
cmd.Parameters.AddWithValue("SALUTATION", DearJohn);
cmd.Parameters.AddWithValue("TYPE", (string)"Family");
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
string ErrorMsg = " " + sAddLine1 + " " + sAddLine2 + " " + sAddCity + " " + sAddState + " " + sAddZIPCode + " " + sAddCountry + " " + sAddAddressID + " " + sAddEntityID + " " + DearJohn;
MessageBox.Show("An error occurred: " + ex.Message, "Error" + " " + ErrorMsg);
}
}
//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
static void CreateAccount(OleDbConnection Conn, string sNewAccountID, string sAccountName, string sAddressID)
{
// -------------------------------------------------------
// Create the account record
try
{
sQuery = "INSERT INTO ACCOUNT (ACCOUNTID, ACCOUNT, ADDRESSID, SHIPPINGID, MAINPHONE, ACCOUNTMANAGERID, ACCOUNT_UC,IMPORTSOURCE, STATUS, TYPE, EMAIL, SECCODEID) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
using (OleDbCommand cmd = new OleDbCommand(sQuery, Conn))
{
cmd.Parameters.AddWithValue("ACCOUNTID", sNewAccountID);
cmd.Parameters.AddWithValue("ACCOUNT", sAccountName);
cmd.Parameters.AddWithValue("ADDRESSID", sAddressID);
cmd.Parameters.AddWithValue("SHIPPINGID", sAddressID);
cmd.Parameters.AddWithValue("MAINPHONE", strPhone1);
cmd.Parameters.AddWithValue("ACCOUNTMANAGERID", slxUSERID);
cmd.Parameters.AddWithValue("ACCOUNT_UC", sAccountName.ToUpper());
cmd.Parameters.AddWithValue("IMPORTSOURCE", gImportSource);
cmd.Parameters.AddWithValue("STATUS", AccountStatus);
cmd.Parameters.AddWithValue("TYPE", AccountType);
cmd.Parameters.AddWithValue("EMAIL", strEMail);
cmd.Parameters.AddWithValue("SECCODEID", slxOwnerID);
cmd.ExecuteNonQuery();
}
}
catch (OleDbException oex)
{ throw oex; }
catch (Exception ex)
{ throw ex; }
// -------------------------------------------------------
// Create the accountsummary record
try
{
sQuery = "INSERT ACCOUNTSUMMARY " + "(ACCOUNTID, ACCOUNT, SECCODEID, TYPE) " + "VALUES (?, ?, ?, ?)";
using (OleDbCommand cmd2 = new OleDbCommand(sQuery, Conn))
{
cmd2.Parameters.AddWithValue("ACCOUNTID", sNewAccountID);
cmd2.Parameters.AddWithValue("ACCOUNT", sAccountName);
cmd2.Parameters.AddWithValue("SECCODEID", slxOwnerID);
cmd2.Parameters.AddWithValue("TYPE", AccountType);
cmd2.ExecuteNonQuery();
}
}
catch (OleDbException oex)
{ throw oex; }
catch (Exception ex)
{ throw ex; }
}
//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
static void CreateContact(OleDbConnection Conn, string AccountID,string AccountName,string AddressID, long iContactNo)
{
string isemployee;
string emp_accountID;
string TheRole;
sQuery = "INSERT INTO sysdba.CONTACT (CONTACTID, ACCOUNTID, ACCOUNT, LASTNAME, MIDDLENAME, FIRSTNAME, ACCOUNTMANAGERID, IMPORTSOURCE, ADDRESSID, SHIPPINGID, EMAIL, WORKPHONE, HOMEPHONE, MOBILE, LASTNAME_UC, STATUS, TYPE, SECCODEID, CONTACTNO) " + "VALUES (?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
OleDbCommand cmd = new OleDbCommand(sQuery, Conn);
{
cmd.Parameters.AddWithValue("CONTACTID", sContactID);
cmd.Parameters.AddWithValue("ACCOUNTID", sAccountID);
cmd.Parameters.AddWithValue("ACCOUNT", AccountName);
cmd.Parameters.AddWithValue("LASTNAME", strLastName);
cmd.Parameters.AddWithValue("MIDDLENAME", strMiddleName);
cmd.Parameters.AddWithValue("FIRSTNAME", strFirstName);
cmd.Parameters.AddWithValue("ACCOUNTMANAGERID", slxUSERID);
cmd.Parameters.AddWithValue("IMPORTSOURCE", gImportSource);
cmd.Parameters.AddWithValue("ADDRESSID", AddressID);
cmd.Parameters.AddWithValue("SHIPPINGID", AddressID);
cmd.Parameters.AddWithValue("EMAIL", strEMail);
cmd.Parameters.AddWithValue("WORKPHONE", strPhone1);
cmd.Parameters.AddWithValue("HOMEPHONE", strPhone1);
cmd.Parameters.AddWithValue("FAX", strPhone2);
cmd.Parameters.AddWithValue("LASTNAME_UC", strLastName.ToUpper());
cmd.Parameters.AddWithValue("STATUS", ContactStatus);
cmd.Parameters.AddWithValue("TYPE", ContactType);
cmd.Parameters.AddWithValue("SECCODEID", slxOwnerID);
cmd.Parameters.AddWithValue("CONTACTNO", iContactNo);
cmd.ExecuteNonQuery();
}
// -------------------------------------------------------
// Create the Contact2 record
emp_accountID = "";
if (strCompanyName != "")
{
TheRole = "Broker";
isemployee = "T";
sCompany = strCompanyName.Replace("'", "''");
emp_accountID = GetField("ACCOUNTID", " ACCOUNT", "ACCOUNT = '" + sCompany + "' ", Conn);
}
else
{
TheRole = "";
isemployee = "F";
}
sQuery = "INSERT INTO ASI_CONTACT_EXT (CONTACTID, CLIENT_CLASSIFICATION, " + "FAMILY_ACCOUNTID, INV_INDUSTRY_EMPLOYEE, EMPLOYER, EMPLOYER_ACCOUNTID, FIRM_ROLE, FIRM_TYPE) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
using(OleDbCommand cmd2 = new OleDbCommand(sQuery,Conn))
{
cmd2.Parameters.AddWithValue("CONTACTID", sContactID);
cmd2.Parameters.AddWithValue("CLIENT_CLASSIFICATION", ContactType);
cmd2.Parameters.AddWithValue("FAMILY_ACCOUNTID", sAccountID);
cmd2.Parameters.AddWithValue("INV_INDUSTRY_EMPLOYEE", isemployee);
cmd2.Parameters.AddWithValue("EMPLOYER", strCompanyName);
cmd2.Parameters.AddWithValue("EMPLOYER_ACCOUNTID", emp_accountID);
cmd2.Parameters.AddWithValue("FIRM_ROLE", TheRole);
cmd2.Parameters.AddWithValue("FIRM_TYPE", TheRole);
cmd2.ExecuteNonQuery();
}
// -------------------------------------------------------
// Create the Contact NO record
string CNID;
CNID = CreateIDFor("CONTACT_NO", Conn);
sQuery = "INSERT INTO CONTACT_NO (CONTACT_NOID, CONTACTID, CONTACT_NO, EMAIL, POSTALCODE, PHONENUMBER, IMPORTDATE, DATEENTERED, IMPORT_SOURCE )" + "VALUES (?, ?, ?, ?,?, ?, ?, ?,?)";
using (OleDbCommand cmd3 = new OleDbCommand(sQuery, Conn))
{
cmd3.Parameters.AddWithValue("CONTACT_NOID", CNID);
cmd3.Parameters.AddWithValue("CONTACTID", sContactID);
cmd3.Parameters.AddWithValue("CONTACT_NO", intCustomerID);
cmd3.Parameters.AddWithValue("EMAIL", strEMail);
cmd3.Parameters.AddWithValue("POSTALCODE", strZipCode);
cmd3.Parameters.AddWithValue("PHONENUMBER", strPhone1);
cmd3.Parameters.AddWithValue("IMPORTDATE", strCreateDate);
cmd3.Parameters.AddWithValue("DATEENTERED", strDateEntered);
cmd3.Parameters.AddWithValue("IMPORT_SOURCE", gImportSource);
cmd3.ExecuteNonQuery();
}
// -------------------------------------------------------
// Create the Inv Firm record
string IFID;
IFID = CreateIDFor("INVESTMENT_FIRMS", Conn);
Broker_AccountID = "";
if (strBrokerFirmName.Length != 0)
{
Broker_AccountID = GetField("COALESCE(ACCOUNTID,'')", "ACCOUNT ", " ACCOUNT = '" + strBrokerFirmName.Replace("'", "''") + "' ", Conn);
}
sQuery = "INSERT INTO INVESTMENT_FIRMS (INVESTMENT_FIRMSID, CONTACTID, FIRM_NAME, FIRM_ACCOUNTID )" + "VALUES (?, ?, ?, ?)";
using(OleDbCommand cmd4 = new OleDbCommand(sQuery,Conn))
{
cmd4.Parameters.AddWithValue("INVESTMENT_FIRMSID", IFID);
cmd4.Parameters.AddWithValue("CONTACTID", sContactID);
cmd4.Parameters.AddWithValue("FIRM_NAME", strBrokerFirmName);
cmd4.Parameters.AddWithValue("FIRM_ACCOUNTID", Broker_AccountID);
cmd4.ExecuteNonQuery();
}
}
public static string CreateIDFor(string table, OleDbConnection SLX_Conn)
{
string strId;
strId = "";
string keyname = table;
switch (table.ToUpper())
{
case "ATTACHMENT":
keyname = "FILEATTACH";
break;
case "USERNOTIFICATION":
keyname = "USERNOTIFY";
break;
case "AGENTS":
keyname = "HOSTTASK";
break;
case "RESOURCELIST":
keyname = "RESOURCE";
break;
case "USEROPTION":
keyname = "USERVIEW";
break;
case "JOINDATA":
keyname = "JOIN";
break;
case "PROCEDURES":
keyname = "PROCEDURE";
break;
case "SEC_FUNCTIONOWNER":
keyname = "FUNCTIONHANDLER";
break;
}
using (OleDbCommand oCmd = SLX_Conn.CreateCommand())
{
oCmd.CommandText = String.Format("slx_dbids('{0}', 1)", keyname);
oCmd.CommandType = CommandType.Text;
return strId = oCmd.ExecuteScalar().ToString();
}
}
//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
//Returns one field for one record, based on an optional Where clause
static T GetField(string Field, string Table, string Where, OleDbConnection oconn)
{
string sqlq = string.Format("select {0} from {1} where {2}", Field, Table, (Where.Equals(string.Empty) ? "1=1" : Where));
//create the command and call ExecuteScalar to get the single result
using (OleDbCommand cmd = new OleDbCommand(sqlq, oconn))
{
object fieldval = cmd.ExecuteScalar();
return fieldval == DBNull.Value ? default(T) : (T)fieldval;
}
}
///
/// Returns a properly formated SLX Provider connection string
///
/// Server name
/// Alias name
/// Username
/// Password
/// Read/Write Password
/// SLX Provider Connection String
///
public static string GetSlxConnString(string server, string connName, string userLogin, string userPassword, string rwPass)
{
StringBuilder Conn = new StringBuilder();
Conn.Append("Provider=SLXOLEDB.1ersist Security Info=False;");
if (!String.IsNullOrEmpty(server))
Conn.Append(String.Format("Data Source='{0}';", server));
else
Conn.Append("Data Source='';");
if (!String.IsNullOrEmpty(connName))
Conn.Append(String.Format("Initial Catalog='{0}';", connName));
else
Conn.Append("Initial Catalog=NO_ALIAS;");
if (!String.IsNullOrEmpty(userLogin))
Conn.Append(String.Format("User ID='{0}';", userLogin));
else
Conn.Append("User ID='';");
if (!String.IsNullOrEmpty(userPassword))
Conn.Append(String.Format("Password='{0}';", userPassword));
else
Conn.Append("Password='';");
Conn.Append("Extended Properties ='");
if (!String.IsNullOrEmpty(rwPass))
Conn.Append(String.Format("RWPass='{0}';", rwPass));
Conn.Append(@"PORT=1706;LOG=ON'");
return Conn.ToString();
}
}
} |