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; }