public static AccessDataSource GetSqlCommandString(DBTablesNames TableName)
{
AccessDataSource ADS = new AccessDataSource();
DataTable dt = new DataTable();
ADS.DataFile = AATSWEBPath;
switch (TableName)
{
case DBTablesNames.TBLTrainee:
ADS.SelectCommand = "SELECT TraineeID, TraineeName, NameInEnglish, NationalityId, Mobile, tel, Email, City, Address, mony, accname, countryId, TimeIn FROM TBLTrainee";
ADS.InsertCommand = @"INSERT INTO TBLTrainee (TraineeID, TraineeName, NameInEnglish, NationalityId, Mobile, tel, Email, City, Address, mony, accname, countryId, TimeIn)
VALUES (@TraineeID, @TraineeName, @NameInEnglish, @NationalityId, @Mobile, @tel, @Email, @City, @Address, @mony, @accname, @countryId, NOW())";
ADS.UpdateCommand = @"UPDATE TBLTrainee SET TraineeName = @TraineeName, NameInEnglish = @NameInEnglish, NationalityId = @NationalityId, Mobile = @Mobile,
tel = @tel, Email = @Email, City = @City, Address = @Address, mony = @mony, accname = @accname, countryId = @countryId, TimeIn = NOW()
Where TraineeID = @TraineeID";
ADS.DeleteCommand = @"DELETE FROM TBLTrainee WHERE (TraineeID = @TraineeID)";
//string xxx = ADS.InsertParameters.Count.ToString();
//dt = LoadTableAATSWEB(ADS.SelectCommand);
break;
case DBTablesNames.Cdcountry:
ADS.SelectCommand = "Select countryId, country From Cdcountry";
ADS.InsertCommand = "";
ADS.UpdateCommand = "";
ADS.DeleteCommand = "";
break;
case DBTablesNames.CdNationality:
ADS.SelectCommand = "Select NationalityId, Nationality From CdNationality";
ADS.InsertCommand = "";
ADS.UpdateCommand = "";
ADS.DeleteCommand = "";
break;
default:
break;
}
OleDbConnection con = ConnectionAATSWEB;
con.Open();
dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, TableName.ToString(), null });
foreach (DataRow row in dt.Rows)
{
//(int)row["DATA_TYPE"]
int datatype = Convert.ToInt16(row["DATA_TYPE"]);
ADS.InsertParameters.Add(row["COLUMN_NAME"].ToString(), ColumnType(datatype), "");
ADS.UpdateParameters.Add(row["COLUMN_NAME"].ToString(), ColumnType(datatype), "");
ADS.DeleteParameters.Add(row["COLUMN_NAME"].ToString(), ColumnType(datatype), "");
}
return ADS;
}
public static string GetNewID(DBTablesNames TableName)
{
string ColunmName = string.Empty;
string ReturnMe = string.Empty;
switch (TableName)
{
case DBTablesNames.TBLTrainee:
ColunmName = "TraineeID";
break;
case DBTablesNames.CdNationality:
ColunmName = "NationalityId";
break;
case DBTablesNames.Cdcountry:
ColunmName = "countryId";
break;
default:
break;
}
using (OleDbConnection con = ConnectionAATSWEB)
{
OleDbCommand cmd = new OleDbCommand("", con);
try
{
con.Open();
cmd.CommandText = string.Format(@"SELECT IIF(IsNull(MAX({0})), 1, MAX({0}) + 1) AS NewID FROM {1}", ColunmName, TableName.ToString());
ReturnMe = cmd.ExecuteScalar().ToString();
}
catch (OleDbException ex)
{
throw ex;
}
con.Close();
}
return ReturnMe;
}
