I’m curious on what position folks in the know have in regard to using C# to write directly to the Epicor database in the case of UD Fields and Tables?
For instance, I have added a new UD field called Week_C in the Job Assembly table. Is it acceptable to bypass the JobEntry Adapter and go directly to the database?
If it is acceptable, then what is the best approach to preform the following SQL Update via C#?
USE [E10Test]
GO
UPDATE [dbo].[JobAsmbl]
SET [Week_c] = 44
WHERE [JobNum] = ‘000052900’ and [AssemblySeq] = ‘81’
GO
That being said in practice, you’re least likely to create issues on a UD table, UD fields linked to production tables could experience unexpected or unintended record locking if you’re not careful. In general I don’t recomend it for anyone. Staying within the established business objects and methods is always your best bet and affords you the most protection. Establishing a connection and running SQL command line is pretty straight forward in C#, but updating fields through the adapters is pretty straight forward as well. If you blow up your DB writing SQL statements against it, you won’t have much luck getting Epicor to help you. I can appreciate wanting to because coding SQL to do it is so simple. Is there a specific reason you can’t use the adapter?
A theoretical example just for discusion purposes, I do not advise using this in your live Epicor production environment! might look something like this:
private void btnKitOp_Click(object sender, System.EventArgs args)
{
// ** Place Event Handling Code Here **
string strJob = JobDetailView.dataView[JobDetailView.Row]["JobNum"].ToString();
string strAssy = JobDetailView.dataView[JobDetailView.Row]["AsmNum"].ToString();
int intAssy = Convert.ToInt32(JobDetailView.dataView[JobDetailView.Row]["AsmNum"]);
string strOper = JobDetailView.dataView[JobDetailView.Row]["OprNum"].ToString();
string strOpCode = JobDetailView.dataView[JobDetailView.Row]["OpCode"].ToString();
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=<YourEpicorDB>;" + "Initial Catalog=E10;" + "Integrated Security=SSPI;";
conn.Open();
try
{
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand("Select OprSeq, OpCode, SchedRelation from JobOper Where JobNum = '" + strJob + "'" + " and AssemblySeq = " + intAssy + " and OprSeq = (select top 1(OprSeq) From JobOper where JobNum = '" + strJob + "'" + " and AssemblySeq = " + intAssy + " and OprSeq < " + strOper + " order by OprSeq desc)", conn);
myReader = myCommand.ExecuteReader();
while(myReader.Read())
if (myReader.HasRows == true)
{
//MessageBox.Show("Has Rows");
myReader.Close();
myReader = null;
myCommand = new SqlCommand("Select OprSeq, OpCode, SchedRelation from JobOper Where JobNum = '" + strJob + "'" + " and AssemblySeq = " + intAssy + " and OprSeq = (select top 1(OprSeq) From JobOper where JobNum = '" + strJob + "'" + " and AssemblySeq = " + intAssy + " and OprSeq < " + strOper + " order by OprSeq desc)", conn);
myReader = myCommand.ExecuteReader();
while(myReader.Read())
if (myReader["OpCode"].ToString() == "KIT")
{
myReader.Close();
myReader = null;
myCommand = new SqlCommand("Update JobOper Set SchedRelation = 'SS' Where JobNum = '" + strJob + "'" + " and AssemblySeq = " + intAssy + " and OprSeq = " + strOper
+ "Select OprSeq, OpCode, SchedRelation from JobOper Where JobNum = '" + strJob + "'" + " and AssemblySeq = " + intAssy + " and OprSeq = " + strOper, conn);
myReader = myCommand.ExecuteReader();
while(myReader.Read())
MessageBox.Show("Set Operation " + myReader["OprSeq"].ToString() + " " + myReader["OpCode"].ToString() + " to " + myReader["SchedRelation"].ToString());
}
else
{
MessageBox.Show("Previous operation is not a KIT operation");
//myReader.Close();
}
}
else
{
MessageBox.Show("No Previous operation");
//myReader.Close();
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
try
{
conn.Close();
SqlDataReader myReader = null;
//MessageBox.Show("Closing Connection");
}
catch(Exception e)
{
MessageBox.Show(e.ToString());
}
}
I was able to find an example that I modified to meet my situation, but I can’t seem to get the code to compile. I’m guessing that I’m missing some reference or a using statement of some sort?
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
foreach(var JobAsmbl in (from row in Db.JobAsmbl.With(LockHint.UpdLock) where
row.Company == Session.CompanyID && row.JobNum == “000052900” && row.AssemblySeq == “81”
select row))
{
JobAsmbl.Week_c = 44;
}
Db.Validate();
txScope.Complete();
}
what method are you calling this from? In most cases you really don’t need the transaction scope if you are currently operating within one i.e. you can update data if it’s within the current scope using the existing transaction scope created by the method invoked.
I was trying to call this from a button click event, but I can’t get it to compile…
Error: CS0103 - line 226 (355) - The name ‘Db’ does not exist in the current context
Error: CS0103 - line 226 (355) - The name ‘LockHint’ does not exist in the current context
Error: CS0103 - line 232 (361) - The name ‘Db’ does not exist in the current context
Error: CS0103 - line 227 (356) - The name ‘Session’ does not exist in the current context
If the following code did work, does it bypass the Business Objects and essential do what you posted above and is this approach just as Taboo?
foreach(var JobAsmbl in (from row in Db.JobAsmbl.With(LockHint.UpdLock) where
row.Company == Session.CompanyID && row.JobNum == “000052900” && row.AssemblySeq == “81”
select row))
{
JobAsmbl.Week_c = 44;
}
Db.Validate();
You don’t want to write directly to the DB. Sure, you can do it without breaking things if you know what you’re doing. You can also bit bang the DB directly without breaking things if you know what you’re doing =)
Glibness aside, if you’re writing SQL directly you’re basically jamming a crowbar right through all those abstraction layers you paid big bucks for. You’re bypassing data validation. You’re writing code that will be needlessly difficult to validate and test. You’re incurring technical debt that is going to come due on your next upgrade, if not sooner. At the end of the day, you’re asking for a bunch of headaches and not really getting any advantages in exchange.
It may be a bit daunting if you’re coming in fresh, but the framework gives you a ton of convenience methods, easier data access, validations, etc that ultimately make it much easier to use than SQL. Implementing high-level logic is always going to be easier when you’re working at a high level.
I’m sure you weren’t really interested in being preached at. So, thanks for listening and here’s the actual code:
// This is written with the assumption that you're wiring it to a button click in Job Entry.
// However, the basic approach should work anywhere in the system.
// You can use the Object Explorer from the customization screen to locate the dataviews, adapters, etc for
// the particular screen you're interested in.
private void SomeButton_Click(object sender, EventArgs e) {
//
// Method 1: Using adapters
//
// Find your adapter!
var jobAdapter = (JobEntryAdapter)csm.TransAdaptersHT["oTrans_jobAdapter"];
// Set you data!
// Just looking at the first available assembly:
jobAdapter.JobEntryData.JobAsmbl[0]["MyCustomField_c"] = "Some data!";
// Alternativly, use some linq to find a specific record
var mySelectedAssembly = jobAdapter.JobEntryData.JobAsmbl.Cast<JobEntryDataSet.JobAsmblRow>().FirstOrDefault(x => x.AssemblySeq==2);
mySelectedAssembly["MyCustomField_c"] = "Some data!";
// Save it!
jobAdapter.Update();
//
// Method 2: Using the dataview (usually the preferable approach)
//
// Find your dataview!
var edvJobAsmbl = (EpiDataView)oTrans.EpiDataViews["JobAsmbl"];
// Set your data!
// Just looking at the first available assembly:
var currentAssembly = (JobEntryDataSet.JobAsmblRow)edvJobAsmbl.CurrentDataRow;
currentAssembly["MyCustomField_c"] = "Yay data!";
// Alternativly, use some linq to find a specific record
var selectedAssembly = edvJobAsmbl.dataView.Table.Rows.Cast<JobEntryDataSet.JobAsmblRow>().FirstOrDefault(x => x.AssemblySeq==2);
selectedAssembly["MyCustomField_c"] = "Also data!";
// Notify the dataview of the changes
edvJobAsmbl.Notify(new EpiNotifyArgs(oTrans, edvJobAsmbl.Row, EpiTransaction.NotifyType.Initialize));
}
Also, the code you posted is written to for a BPM directive which is why it isn’t working in the UI customization. Although the broad strokes are similar, there are quite a few differences in the code needed for a BPM vs a screen customization.
Stylistic issues aside, it would work just fine in that context.
