I am new to programming in .NET, but I managed to put together some code that works fine in LINQPad using LINQ to SQL (C#). I cannot make it working in Visual Studio (2010 or 2012). I cannot use dbml files in VS, since my SQL server is version 2000 and is dbml is not supported. How can I translate this code to Visual Sutio, without using .dbml files?
I got an error in line 7 ( "select new Activity()" ), and the error was:
The type or namespace name 'Activity' could not be found (are you missing a using directive or an assembly reference?)
It looks like it doesn't know about the table "Activity".
Here is my code in LINQPad:
DateTime vBeginDate = new DateTime(2013, 1, 1);
DateTime vEndDate = new DateTime(2015, 12, 31);
var excel = ExcelProvider.Create(@".\Test.xlsx", "Sheet1");
var IDs =
(from r in excel.Rows
select new Activity()
{
ID = r.GetByName<string>("ID").Trim()
}).ToArray();
//IDs.Dump("ExcelProvider : cell values from rows");
var query2 = from b in IDs
where !(from n in Name
select n.ID)
.Contains(b.ID)
select b;
if (query2.Count() != 0) {
"Found IDs in Excel that are in iMIS. Probably missing 0s in the beginning in Excel. Please correct the Excel file first!".Dump();
query2.Dump("IDs from Excel not in iMIS:");
return;
}
var query3 = from c in Cert_Register
where (from m in IDs select m.ID).Contains(c.STUDENT_ID)
&& c.REG_TYPE == "P"
//&& c.STATUS == "E" && c.ENROLLED_DATE == vBeginDate
orderby c.STUDENT_ID
select c;
if (query3.Count() != 0) {
query3.Dump("These people already have a maintenance period. We cannot do these using the script:");
}
// These people from the list don't have any maintenance period, so we can run the script for them:
var FinalList = from a in IDs
where !(from c in Cert_Register where c.REG_TYPE == "P" select c.STUDENT_ID).Contains(a.ID)
select a;
FinalList.Dump("Final IDs to be processed:");
if (FinalList.Count() == 0) {
"There are no IDs to be processes. Exiting...".Dump(); // wire text to the screen
return;
}
Connection.Open();
foreach (Activity memberid in FinalList){
Transaction = Connection.BeginTransaction();
var newActivity = new Activity() {
SEQN = Counter.Single(cnt => cnt.COUNTER_NAME == "Activity").LAST_VALUE + 1 //SQL: select top 1 LAST_VALUE from Counter where COUNTER_NAME = 'Activity'
// SEQN = Activity.Max(l => l.SEQN) + 1 //not good, because we have higher numbers for SEQN in Activity table (used by other institutes)
, ID = memberid.ID
, NOTE = ""
, ACTIVITY_TYPE = "CERTIFICAT"
, SOURCE_SYSTEM = "CERTIFICAT"
, SOURCE_CODE = ""
, OTHER_CODE = ""
, TRANSACTION_DATE = vBeginDate // DateTime.Now
, AMOUNT = 0
, MEMBER_TYPE = Name.Single(n => n.ID == memberid.ID).MEMBER_TYPE
, UF_1 = ""
, UF_2 = ""
, UF_3 = ""
, UF_4 = 0
, UF_5 = 0
, UF_6 = null
, UF_7 = null
, CO_ID = ""
, EFFECTIVE_DATE = null
, THRU_DATE = vEndDate
, ACTION_CODES = "Enrolled"
, PAY_METHOD = ""
, CATEGORY = "Maint"
, UNITS = 0
, NOTE_2 = ""
, BATCH_NUM = ""
, INTENT_TO_EDIT = DateTime.Now.ToString("MMddyyHH:mm:ss.00")
, ORG_CODE = ""
, CAMPAIGN_CODE = ""
, OTHER_ID = ""
, SOLICITOR_ID = ""
};
int currentActivitySEQN = newActivity.SEQN;
Activity.InsertOnSubmit(newActivity);
SubmitChanges();
Counter vCounter_Act = Counter.Single(cnt => cnt.COUNTER_NAME == "Activity");
vCounter_Act.LAST_VALUE ++;
SubmitChanges();
var newCert_Register = new Cert_Register() {
SEQN = Counter.Single(cnt => cnt.COUNTER_NAME == "Cert_Register").LAST_VALUE + 1
, STUDENT_ID = memberid.ID
, BT_ID = memberid.ID
, REG_TYPE = "P"
, STATUS = "E"
, GRADE_TEXT = ""
, ENROLLED_DATE = vBeginDate
, GOOD_THRU_DATE = vEndDate
, DEADLINE = vEndDate
, ACTIVITY_SEQN = currentActivitySEQN
, PROGRAM_ID = ""
, UF_3 = ""
, UF_4 = ""
, REQUIREMENT_TYPE = 1
, COMPONENT_INSTANCE = ""
};
Cert_Register.InsertOnSubmit(newCert_Register);
SubmitChanges();
Counter vCounter_CertReg = Counter.Single(cnt => cnt.COUNTER_NAME == "Cert_Register");
vCounter_CertReg.LAST_VALUE ++;
SubmitChanges();
Transaction.Commit();
}
Connection.Close();
Thank you very much,
Steven
