Hi everyone,Been fighting with this issue for several weeks now and not getting anywhere with it. The goal is to programmatically read attribute values from an Oracle table and update fields in an SDE feature class that is versioned. I am only working on one version currently but eventually, this version will be used to update default once the attributes are updated.The code below works.... sometimes but not always. I can run it consecutive times and it is hit or miss.In the code below I have tried updating fields by using both IFeatureClass.Update with IFeatureCursor.UpdateFeature and IFeatureClass.Search with IFeature.Store. On a layer with 24,000 records, using IFeatureClass.Update is slow taking 48 minutes compared to 3 minutes for IFeatureClass.Search. I have tried using recycling and non-recycling cursors. I have even tried reducing the number of fields I update but no matter what I do, sometimes the code updates and sometimes it does not. When it does not, I do not get any errors but the fields are not updated. I am out of ideas on what to try next.Does anyone see a problem or a better way to do this? I reduced toe number of fields to get the code to fit here. In reality, I have to update over 50 fields on 20 SDE layers with over 300,000 records total!private static void ProcessLayer(string permitType)
{
IPropertySet pPropertySet = new PropertySetClass();
IWorkspaceFactory pWorkspaceFactory = new SdeWorkspaceFactoryClass();
IFeatureWorkspace pFeatureWorkspace = null;
IFeatureClass pFeatureClass = null;
IDataset pDataset = null;
IWorkspace pWorkspace = null;
IWorkspaceEdit pWorkspaceEdit = null;
ITrackCancel pTrackCancel = new CancelTrackerClass();
IProgressDialogFactory pProgressDialogFactory = new ProgressDialogFactoryClass();
IProgressDialog2 pProgressDialog2 = null;
IStepProgressor pStepProgressor = null;
OracleConnection dataConnection = new OracleConnection();
OracleCommand dataCommand = new OracleCommand();
OracleDataReader dataReader = null;
IFeatureCursor pFeatureCursor = null;
IFeature pFeature = null;
try
{
pPropertySet.SetProperty("SERVER", "gresssde");
pPropertySet.SetProperty("INSTANCE", "5195:rim");
pPropertySet.SetProperty("USER", "GORT");
pPropertySet.SetProperty("PASSWORD", "Klaatu");
pPropertySet.SetProperty("VERSION", "RIM.GORT");
pFeatureWorkspace = pWorkspaceFactory.Open(pPropertySet, 0) as IFeatureWorkspace;
pFeatureClass = pFeatureWorkspace.OpenFeatureClass(permitType);
pDataset = pFeatureClass as IDataset;
pWorkspace = pDataset.Workspace;
pWorkspaceEdit = pWorkspace as IWorkspaceEdit;
//If permit type field not found, stop code.
if (pFeatureClass.FindField("FEE_PERMIT_TYPE") != -1)
{
//Start an edit session and operation
pWorkspaceEdit.StartEditing(false);
pWorkspaceEdit.StartEditOperation();
pFeatureCursor = pFeatureClass.Search(null, false);
//pFeatureCursor = pFeatureClass.Search(null, false);
//pFeatureCursor = pFeatureClass.Update(null, true);
//Get the index numbers for every field being updated.
int permitNoIdx = pFeatureClass.FindField("PERMIT_NO");
int appNoIdx = pFeatureClass.FindField("APP_NO");
int projectNameIdx = pFeatureClass.FindField("PROJECT_NAME");
int appStatusIdx = pFeatureClass.FindField("APP_STATUS");
int activeModIdx = pFeatureClass.FindField("ACTIVE_MOD");
string appNo = string.Empty;
string permitNo = string.Empty;
string projectName = string.Empty;
string appStatus = string.Empty;
string activeMod = string.Empty;
//Set up and open the progress dialog.
int featureCount = 1;
int numberOfFeatures = pFeatureClass.FeatureCount(null);
pProgressDialog2 = pProgressDialogFactory.Create(pTrackCancel, 0) as IProgressDialog2;
pProgressDialog2.CancelEnabled = true;
pProgressDialog2.Description = "Processing Record " + featureCount + " of " + numberOfFeatures;
pProgressDialog2.Title = "Processing...";
pProgressDialog2.Animation = esriProgressAnimationTypes.esriProgressGlobe;
pStepProgressor = pProgressDialog2 as IStepProgressor;
pStepProgressor.MinRange = 0;
pStepProgressor.MaxRange = numberOfFeatures;
pStepProgressor.StepValue = 1;
pStepProgressor.Message = permitType;
pProgressDialog2.ShowDialog();
bool canContinue = true;
//Read values from Oracle and update each feature with the info.
dataConnection.ConnectionString = "Data Source=genp;" +
"Persist Security Info=True;" +
"User ID=pub;" +
"Password=pub;" +
"Unicode=True";
dataConnection.Open();
dataCommand.Connection = dataConnection;
pFeature = pFeatureCursor.NextFeature();
while (pFeature != null)
{
appNo = pFeature.get_Value(appNoIdx).ToString();
dataCommand.CommandText = "SELECT PERMIT_NO, PROJECT_NAME, APP_STATUS, ACTIVE_MOD, " +
"FROM REG.REG_GIS WHERE app_no = '" + appNo + "'";
dataReader = dataCommand.ExecuteReader();
while (dataReader.Read())
{
if (!dataReader.IsDBNull(0))
{
permitNo = dataReader.GetString(0);
permitNo = permitNo.Replace("'", "");
}
if (!dataReader.IsDBNull(1))
{
projectName = dataReader.GetString(1);
projectName = projectName.Replace("'", "");
}
if (!dataReader.IsDBNull(2))
{
appStatus = dataReader.GetString(2);
appStatus = appStatus.Replace("'", "");
}
if (!dataReader.IsDBNull(3))
{
activeMod = dataReader.GetString(3);
activeMod = activeMod.Replace("'", "");
}
//Only update fields whose value have changed.
if (permitNoIdx != -1)
{
if (pFeature.get_Value(permitNoIdx) != DBNull.Value)
{
string permitNoIn = Convert.ToString(pFeature.get_Value(permitNoIdx));
if (permitNoIn != permitNo) { pFeature.set_Value(permitNoIdx, permitNo); }
}
}
if (projectNameIdx != -1)
{
if (pFeature.get_Value(projectNameIdx) != DBNull.Value)
{
string projectNameIn = Convert.ToString(pFeature.get_Value(projectNameIdx));
if (projectNameIn != projectName) { pFeature.set_Value(projectNameIdx, projectName); }
}
}
if (appStatusIdx != -1)
{
if (pFeature.get_Value(appStatusIdx) != DBNull.Value)
{
string appStatusIn = Convert.ToString(pFeature.get_Value(appStatusIdx));
if (appStatusIn != appStatus) { pFeature.set_Value(appStatusIdx, appStatus); }
}
}
if (activeModIdx != -1)
{
if (pFeature.get_Value(activeModIdx) != DBNull.Value)
{
string activeModIn = Convert.ToString(pFeature.get_Value(activeModIdx));
if (activeModIn != activeMod) { pFeature.set_Value(activeModIdx, activeMod); }
}
}
pFeature.Store();
//pFeatureCursor.UpdateFeature(pFeature);
} //while (dataReader.Read())
dataReader.Close();
dataReader.Dispose();
pFeature = pFeatureCursor.NextFeature();
//Increment the progress dialog.
canContinue = pTrackCancel.Continue();
if (canContinue == true)
{
pStepProgressor.Step();
featureCount++;
pProgressDialog2.Description = "Processing Record " + featureCount + " of " + numberOfFeatures;
}
else
{
pProgressDialog2.HideDialog();
pWorkspaceEdit.StopEditOperation();
pWorkspaceEdit.StopEditing(true);
return;
}
//Clear values.
appNo = string.Empty;
permitNo = string.Empty;
projectName = string.Empty;
appStatus = string.Empty;
activeMod = string.Empty;
}
//Stop editing
pWorkspaceEdit.StopEditOperation();
pWorkspaceEdit.StopEditing(true);
//Close progress dialog.
pProgressDialog2.HideDialog();
} //if (pFeatureClass.FindField("FEE_PERMIT_TYPE") != -1)
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace + "\r\n" + ex.Message);
}
}
Thanks,Carlos
