This type of connection is OK for general query type stuff. When performing large transfers of data from UniVerse to SQL server I've found that it's much faster to use bulk loading from DTS (or a stored procedure). Here's a sample of code (without the DTS part which is just a simple bulk load job). The commented section is a piece of code the DTS job replaced. The commented section took over an hour, the bulk load DTS job takes 12 minutes.
This is used to transfer some raw budget numbers off a UniVerse based
accounting system and put them into a SQL server based budgeting
system (Aderant's Executive Office).
-----------begin-----------
namespace FHFGD {
using System;
using System.Data;
using System.Data.SqlClient;
using IBMU2.UODOTNET;
using System.IO;
using System.Collections;
using System.Text;
using System.Windows.Forms;
using C1.Common;
using C1.Win.C1FlexGrid;
using DTS = Microsoft.SQLServer.DTSPkg80;
public class BudgetLoad : IDisposable {
// This delegate used to pass back status messages in string
format
dlgStatus dlgMessage;
#region CodeObject Interface IDisposable implementation
// (Madinina Info) Do not edit prototype by hand : Access
ReturnType
Name Parameters
public void Dispose() {
GC.SuppressFinalize(this);
}
#endregion CodeObject Interface IDisposable implementation
public BudgetLoad(UniSession uSession, string NRDataConn, string
FilePath, dlgStatus dlgIn, C1.Win.C1FlexGrid.C1FlexGrid FlexG) {
///<summary>
/// generate budget load data for Aderant Executive
Office Budgeting system
/// aka Keystone.
///</summary>
this.dlgMessage = dlgIn;
dlgMessage("Begin EO Budget Import Table Build");
string AcctID = "";
string CostCenter = "";
string AccountName = "";
string AccountType = "";
string Active = "";
int svlimit = 0;
int rCnt = 1;
decimal Credit = 0;
decimal Debit = 0;
decimal MTDBalance = 0;
decimal Budget = 0;
string CHID = "";
string TAB = "\t";
StreamWriter swGL;
StreamWriter swGLAcct;
string fGL = "gl.txt";
string fGLAcct = "glAcct.txt";
string [] lRecIDArray;
ArrayList lRecIDList = new ArrayList(10000);
swGL = new StreamWriter(@"c:\" + fGL, false);
swGL.WriteLine("InputFileLineNum" + TAB + "Year" + TAB
+ "Period" +
TAB + "AccountID" + TAB + "CostCenterID" + TAB + "CurrencyID" + TAB +
"MTDBalance" + TAB + "Budget" + TAB + "nbFlag");
swGLAcct = new StreamWriter(@"c:\" + fGLAcct, false);
swGLAcct.WriteLine("AccountID" + TAB + "AccountName" +
TAB +
"AccountType" + TAB + "Active");
#region Datatable initialization area
System.Data.DataTable dtGL = new
System.Data.DataTable("GL");
DataColumn colLineNum = new DataColumn();
colLineNum.ColumnName = "InputFileLineNum";
colLineNum.DataType = Type.GetType("System.Int32");
dtGL.Columns.Add(colLineNum);
DataColumn colYear = new DataColumn();
colYear.ColumnName = "Year";
colYear.DataType = Type.GetType("System.Int16");
dtGL.Columns.Add(colYear);
DataColumn colPeriod = new DataColumn();
colPeriod.ColumnName = "Period";
colPeriod.DataType = Type.GetType("System.Int16");
dtGL.Columns.Add(colPeriod);
DataColumn colGLAcctID = new DataColumn();
colGLAcctID.ColumnName = "AccountID";
colGLAcctID.DataType = Type.GetType("System.String");
dtGL.Columns.Add(colGLAcctID);
DataColumn colCostCenter = new DataColumn();
colCostCenter.ColumnName = "CostCenter";
colCostCenter.DataType = Type.GetType("System.String");
dtGL.Columns.Add(colCostCenter);
DataColumn colCurrency = new DataColumn();
colCurrency.ColumnName = "CurrencyID";
colCurrency.DataType = Type.GetType("System.String");
dtGL.Columns.Add(colCurrency);
DataColumn colMTDBalance = new DataColumn();
colMTDBalance.ColumnName = "MTDBalance";
colMTDBalance.DataType = Type.GetType("System.Decimal");
dtGL.Columns.Add(colMTDBalance);
DataColumn colBudget = new DataColumn();
colBudget.ColumnName = "Budget";
colBudget.DataType = Type.GetType("System.Decimal");
dtGL.Columns.Add(colBudget);
DataColumn colFlag = new DataColumn();
colFlag.ColumnName = "Flag";
colFlag.DataType = Type.GetType("System.String");
dtGL.Columns.Add(colFlag);
System.Data.DataTable dtGLAcct = new
System.Data.DataTable("GLACCT");
DataColumn colAcctID = new DataColumn();
colAcctID.ColumnName = "AccountID";
colAcctID.DataType = Type.GetType("System.String");
dtGLAcct.Columns.Add(colAcctID);
DataColumn colAcctName = new DataColumn();
colAcctName.ColumnName = "AccountName";
colAcctName.DataType = Type.GetType("System.String");
dtGLAcct.Columns.Add(colAcctName);
DataColumn colAcctType = new DataColumn();
colAcctType.ColumnName = "AccountType";
colAcctType.DataType = Type.GetType("System.String");
dtGLAcct.Columns.Add(colAcctType);
DataColumn colActive = new DataColumn();
colActive.ColumnName = "Active";
colActive.DataType = Type.GetType("System.String");
dtGLAcct.Columns.Add(colActive);
#endregion
UniFile uvfATMF = uSession.CreateUniFile("AT-MF");
UniFile uvfCH = uSession.CreateUniFile("CH");
UniCommand uvCmd = uSession.CreateUniCommand();
dlgIn("Data Select Beginning - " +
DateTime.Now.ToString());
uvCmd.Command = "SSELECT CH BY @ID WITH CO = \"01\" AND
WITH ACCT >
\"40000\"";
uvCmd.Execute();
dlgIn(uvCmd.Response.Trim());
UniSelectList uvsl = uSession.CreateUniSelectList(0);
UniDynArray uvTmp = uSession.CreateUniDynArray();
uvTmp = uvsl.ReadList();
for(int Index = 0; Index < uvTmp.Dcount(); Index++)
{
string id = uvTmp.Extract(Index).ToString();
lRecIDList.Add(id);
if (lRecIDList.Count % 100 == 0){
dlgIn("Processing - " +
lRecIDList.Count);
}
}
dlgIn("Data Select Complete - " +
DateTime.Now.ToString());
lRecIDArray = new string[lRecIDList.Count];
lRecIDList.CopyTo(0,lRecIDArray,0,lRecIDList.Count);
// read records using array of records ids
UniDataSet uvDS = uvfCH.ReadRecords(lRecIDArray);
dlgIn("Outer Loop Processing Beginning - " +
DateTime.Now.ToString());
bool LogFlag = true;
foreach (UniRecord item in uvDS){
if (item.RecordReturnValue.Equals(30001)){
// record not found?!?
continue;
}
CHID = item.RecordID;
string[] sTmp = new string[4];
sTmp = item.RecordID.Split(new Char[]{'.'},3);
AcctID = sTmp[1];
CostCenter = sTmp[2];
if (CostCenter.Equals("1")){
continue;
}
AccountName = item.Record.Extract(1).ToString();
svlimit = item.Record.Dcount(7);
AccountType =
item.Record.Extract(20).ToString();
switch (AccountType) {
case "I":
AccountType = "Inactive";
break;
case "E" :
AccountType = "Expense";
break;
case "A" :
AccountType = "Asset";
break;
case "C" :
AccountType = "Capitol";
break;
case "L" :
AccountType = "Liability";
break;
default :
AccountType = "Unknown";
break;
}
string CloseDate =
item.Record.Extract(21).ToString();
int iLen = CloseDate.Length;
if (iLen > 1){
Active = "Not Active";
} else {
Active = "";
}
//System.Data.DataRow nRow = dtGLAcct.NewRow();
//nRow[0] = CHID;
//nRow[1] = AccountName;
//nRow[2] = AccountType;
//nRow[3] = Active;
//dtGLAcct.Rows.Add(nRow);
swGLAcct.WriteLine(CHID + TAB + AccountName +
TAB + AccountType +
TAB + Active);
FlexG.Rows.Add();
FlexG[rCnt,0] = CHID;
FlexG[rCnt,1] = AccountName;
FlexG[rCnt,2] = AccountType;
FlexG[rCnt,3] = Active;
if (rCnt % 100 == 0) {
System.Windows.Forms.Application.DoEvents();
}
rCnt++;
int recCnt = 0;
// build the GL table by blowing out the
subvalues into a flat format
if (LogFlag){
dlgIn("Inner Loop (GL) Processing
Begining - " + DateTime.Now.ToString());
LogFlag = false;
}
for(int iYr = 9; iYr <= svlimit; iYr++){
int Year = 1998;
string nbFlag = "";
Year = Year + (iYr - 9);
DateTime yrDate = new DateTime();
yrDate = DateTime.Now;
string yrCheck = yrDate.Year.ToString();
string mthCheck =
yrDate.Month.ToString();
for(int iPeriod = 1; iPeriod <= 12 ;
iPeriod++){
string tmp = "";
tmp = item.Record.Extract(7,
iYr, iPeriod).ToString();
if (tmp.Equals("")){
Debit = 0;
} else {
//tmp =
uSession.Oconv(tmp,"MD2").ToString();
tmp = Convertuvd(tmp);
Debit =
Convert.ToDecimal(tmp);
}
tmp = item.Record.Extract(8,
iYr, iPeriod).ToString();
if (tmp.Equals("")){
Credit = 0;
} else {
//tmp =
uSession.Oconv(tmp, "MD2").ToString();
tmp = Convertuvd(tmp);
Credit =
Convert.ToDecimal(tmp);
}
MTDBalance = (Debit - Credit);
tmp = item.Record.Extract(12,
iYr, iPeriod).ToString();
if (tmp.Equals("")){
Budget = 0;
} else {
//tmp =
uSession.Oconv(tmp, "MD2").ToString();
tmp = Convertuvd(tmp);
Budget =
Convert.ToDecimal(tmp);
}
if (Year.Equals(yrCheck)){
if
(iPeriod.Equals(mthCheck)){
nbFlag = "NB";
}
}
recCnt++;
//
System.Data.DataRow pRow = dtGL.NewRow();
//
pRow[0] = recCnt;
//
pRow[1] = Year.ToString();
//
pRow[2] = iPeriod.ToString();
//
pRow[3] = AcctID;
//
pRow[4] = CostCenter;
//
pRow[5] = "";
//
pRow[6] = MTDBalance;
//
pRow[7] = Budget;
//
pRow[8] = nbFlag;
//
dtGL.Rows.Add(pRow);
swGL.WriteLine(recCnt.ToString() + TAB + Year.ToString() + TAB +
iPeriod.ToString() + TAB + AcctID + TAB + CostCenter + TAB + "" + TAB
+ MTDBalance.ToString() + TAB + Budget.ToString() + TAB + nbFlag);
if (recCnt % 100 == 0){
dlgIn("Processing GL -
" + recCnt);
System.Windows.Forms.Application.DoEvents();
}
}// end of period loop
} //end of year loop
} // end of recordset loop
dlgIn("Recordset Processing Complete - " +
DateTime.Now.ToString());
// System.Data.DataSet
dsNRDataLoad = new DataSet();
//
dsNRDataLoad.Tables.Add(dtGLAcct);
// dsNRDataLoad.Tables.Add(dtGL);
SqlConnection BudConn = new SqlConnection(NRDataConn);
BudConn.Open();
// SqlCommand sqlCrTable = new
SqlCommand("FH_TRCreate", BudConn);
// sqlCrTable.CommandType =
CommandType.StoredProcedure;
//
// try {
//
sqlCrTable.ExecuteNonQuery();
// dlgIn("Tables Created");
// }
// catch (SqlException sqlExp){
//
MessageBox.Show(sqlExp.Message);
// }
//
// SqlDataAdapter daNR = new
SqlDataAdapter("",BudConn);
//
// SqlCommand sqlIns = new
SqlCommand("FH_InsertGLAcct", BudConn);
// sqlIns.CommandType =
CommandType.StoredProcedure;
//
sqlIns.Parameters.Add("@AccountID", SqlDbType.VarChar,100, "AccountID");
//
sqlIns.Parameters.Add("@AccountName", SqlDbType.VarChar,100,
"AccountName");
//
sqlIns.Parameters.Add("@AccountType", SqlDbType.VarChar,20,
"AccountType");
//
sqlIns.Parameters.Add("@Active", SqlDbType.VarChar,20, "Active");
//
// daNR.InsertCommand = sqlIns;
// try {
//
daNR.Update(dsNRDataLoad, "GLACCT");
// dlgIn("l_GLAcct
Updated");
// }
// catch (SqlException sqlExp){
//
MessageBox.Show(sqlExp.Message);
// }
//
// SqlCommand sqlInsGL = new
SqlCommand("FH_insertGL", BudConn);
// sqlInsGL.CommandType =
CommandType.StoredProcedure;
//
sqlInsGL.Parameters.Add("@InputFileLineNum", SqlDbType.Int, 0,
"InputFileLineNum");
//
sqlInsGL.Parameters.Add("@Year", SqlDbType.SmallInt, 0, "Year");
//
sqlInsGL.Parameters.Add("@Period", SqlDbType.SmallInt, 0, "Period");
//
sqlInsGL.Parameters.Add("@AccountID", SqlDbType.VarChar, 100,
"AccountID");
//
sqlInsGL.Parameters.Add("@CostCenterID", SqlDbType.VarChar,
100, "CostCenter");
//
sqlInsGL.Parameters.Add("@CurrencyID", SqlDbType.VarChar, 100,
"Currency");
//
sqlInsGL.Parameters.Add("@MTDBalance", SqlDbType.Money, 0,
"MTDBalance");
//
sqlInsGL.Parameters.Add("@budget", SqlDbType.Money, 0, "Budget");
//
sqlInsGL.Parameters.Add("@Flag", SqlDbType.VarChar, 2, "Flag");
//
// daNR.InsertCommand = sqlInsGL;
//
// try {
//
daNR.Update(dsNRDataLoad, "GL");
// dlgIn("l_GL Updated");
// }
// catch (SqlException sqlExp){
//
MessageBox.Show(sqlExp.Message);
// }
dlgIn("SQL Tables Updated - " +
DateTime.Now.ToString());
swGL.Flush();
swGL.Close();
swGLAcct.Flush();
swGLAcct.Close();
uvfATMF.Close();
uvfCH.Close();
BudConn.Close();
File.Copy(@"c:\" + fGL, @"\\enterprise\transfer\" +
fGL, true);
File.Copy(@"c:\" + fGLAcct, @"\\enterprise\transfer\" +
fGLAcct, true);
try{
DTS.Package2Class GoDTS = new
DTS.Package2Class();
object pVarPersistStgOfHost = null;
GoDTS.LoadFromSQLServer("enterprise", null,
null,
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, null,
null, null, "EODataLoad", ref pVarPersistStgOfHost);
GoDTS.Execute();
GoDTS.UnInitialize();
System.Runtime.InteropServices.Marshal.ReleaseComObject(GoDTS);
dlgIn("DTS Package Done");
GoDTS = null;
}
catch (System.Runtime.InteropServices.COMException ex) {
MessageBox.Show(ex.Message, "COM Error",
MessageBoxButtons.OK,
MessageBoxIcon.Exclamation);
}
catch (System.Exception ex) {
MessageBox.Show(ex.Message, "System Error",
MessageBoxButtons.OK,
MessageBoxIcon.Exclamation);
}
}
public string Convertuvd(string inTmp){
if (inTmp.Length > 2){
inTmp = inTmp.Insert(inTmp.Length - 2,".");
} else {
inTmp = inTmp.Insert(1,".");
}
return(inTmp);
}
}
}
-------
u2-users mailing list
[email protected]
To unsubscribe please visit http://listserver.u2ug.org/
