private static void Main()
{
Console.OutputEncoding = Encoding.UTF8;
var quoteUtil = new QuoteUtil();
int choose;
do
{
Console.OutputEncoding = Encoding.UTF8;
Console.WriteLine("1. Get list quotes");
Console.WriteLine("2. Add quote");
Console.WriteLine("3. Find quote");
Console.WriteLine("4. Upate quote");
Console.WriteLine("5. Delete quote");
Console.WriteLine("0. Exit");
Console.Write("Choose: ");
choose = int.Parse(Console.ReadLine() ?? throw new InvalidOperationException());
switch (choose)
{
case 1:
foreach (var itemQuote in quoteUtil.GetListQuotes())
{
Console.WriteLine("Quote Id: " + itemQuote.QuotesId);
Console.WriteLine("Quote Name: " + itemQuote.QuotesName);
Console.WriteLine("Author: " + itemQuote.Author);
Console.WriteLine("Category: " + itemQuote.Type);
Console.WriteLine("-----------------------------");
}
break;
case 2: // add quote
Console.Write("Quote Name: ");
var quoteName = Console.ReadLine();
Console.Write("Author: ");
var author = Console.ReadLine();
Console.WriteLine("Types of Category");
string category;
var count = 1;
var listConstantValue = typeof(TypesQuoteConstant).GetAllPublicConstantValues <string>();
listConstantValue.Sort();
foreach (var propertyInfo in listConstantValue)
{
Console.WriteLine(count++ + ". " + propertyInfo);
}
Console.WriteLine("Please choose a category: ");
category = Console.ReadLine();
if (category != null)
{
if (int.Parse(category) > 0 && int.Parse(category) <= listConstantValue.Count)
{
category = listConstantValue[int.Parse(category) - 1];
}
}
quoteUtil.AddQuote(new Quotes()
{
QuotesId = HandleRandom.RandomString(8), QuotesName = quoteName, Author = author, Type = category
});
HandleRandom.ChooseColorForString("Adding successful", ConsoleColor.Blue);
break;
case 3: // find quote
Console.Write("Quote name: ");
var findquoteName = Console.ReadLine();
var result = quoteUtil.FindQuoteBy(findquoteName);
if (result != null)
{
HandleRandom.ChooseColorForString("Found Quote", ConsoleColor.Blue);
Console.WriteLine("Info quote---");
Console.WriteLine("Id: " + result.QuotesId);
Console.WriteLine("Quote Name: " + result.QuotesName);
Console.WriteLine("Author: " + result.Author);
Console.WriteLine("Category: " + result.Type);
Console.WriteLine("-----------------------------");
break;
}
HandleRandom.ChooseColorForString("There are no quote you find", ConsoleColor.Blue);
break;
case 4: // update quote
Console.Write("Find quote to UPDATE: ");
var placeHoldquoteName = Console.ReadLine();
if (quoteUtil.FindQuoteBy(placeHoldquoteName) != null)
{
var currentquoteResult = quoteUtil.FindQuoteBy(placeHoldquoteName);
HandleRandom.ChooseColorForString("Found quote", ConsoleColor.Blue);
Console.WriteLine("Quote info---");
Console.WriteLine("Id: " + currentquoteResult.QuotesId);
Console.WriteLine("Quote Name: " + currentquoteResult.QuotesName);
Console.WriteLine("Author: " + currentquoteResult.Author);
Console.WriteLine("Category: " + currentquoteResult.Type);
Console.WriteLine("-----------------------------");
Console.Write("Quote name to update: ");
var quoteNameToUpdate = Console.ReadLine();
Console.Write("Author to update: ");
var authorToUpdate = Console.ReadLine();
Console.WriteLine("Types of Category");
var counts = 1;
var listConstantValues = typeof(TypesQuoteConstant).GetAllPublicConstantValues <string>();
listConstantValues.Sort();
foreach (var propertyInfo in listConstantValues)
{
Console.WriteLine(counts++ + ". " + propertyInfo);
}
Console.Write("Please choose a category to update: ");
var categoryToUpdate = Console.ReadLine();
if (categoryToUpdate != null)
{
if (int.Parse(categoryToUpdate) > 0 && int.Parse(categoryToUpdate) <= listConstantValues.Count)
{
categoryToUpdate = listConstantValues[int.Parse(categoryToUpdate) - 1];
}
}
if (quoteUtil.UpdateQuote(currentquoteResult.QuotesName, quoteNameToUpdate, authorToUpdate,
categoryToUpdate))
{
HandleRandom.ChooseColorForString("Update quote success", ConsoleColor.Blue);
break;
}
HandleRandom.ChooseColorForString("Update quote Failed", ConsoleColor.DarkRed);
}
else
{
HandleRandom.ChooseColorForString("Nothing found quote", ConsoleColor.DarkRed);
}
break;
case 5:
Console.Write("Quote name to delete: ");
var findquoteToDelete = Console.ReadLine();
if (quoteUtil.DeleteQuote(findquoteToDelete))
{
HandleRandom.ChooseColorForString("Deleted successful", ConsoleColor.Blue);
break;
}
HandleRandom.ChooseColorForString("Nothing quote name to delete", ConsoleColor.DarkRed);
break;
}
} while (choose != 0);
HandleRandom.ChooseColorForString("There is no option you chose", ConsoleColor.Blue);
Thread.Sleep(2000);
}
/// <summary>
/// Internal method for validating input columns with external metadata columns.
/// </summary>
/// <returns></returns>
private DTSValidationStatus ValidateWithExternalMetadata()
{
QuoteUtil quoteUtil = new QuoteUtil(m_DbConnection);
// The parameter column name restrictions of DbConnection.GetSchema is an array.
// For example, if the tablename is msdb.dbo.employee. The restrictions would be {"msdb", "dbo". "Employee"}
// initialize to null, as required by restrictios
bool isValidName = quoteUtil.GetValidTableName(m_fullTableName,
out m_tableNameLvl3, out m_tableNameLvl2, out m_tableNameLvl1);
if (!isValidName)
{
bool bCancel;
ErrorSupport.FireErrorWithArgs(HResults.DTS_E_ADODESTTABLENAMEERROR,
out bCancel, quoteUtil.Prefix, quoteUtil.Sufix);
return DTSValidationStatus.VS_ISBROKEN;
}
DataTable metadataTbl = null;
try
{
// get metadata by command
DbProviderFactory DbFactory = GetDbFactory();
PostDiagnosticMessage(
Localized.DiagnosticPre("DbCommand.CreateCommand"));
DbCommand dbCommand = DbFactory.CreateCommand();
PostDiagnosticMessage(
Localized.DiagnosticPost("DbCommand.CreateCommand"));
dbCommand.CommandText = "select * from " + m_fullTableName;
dbCommand.Connection = m_DbConnection;
dbCommand.CommandTimeout = m_commandTimeout;
// the next method will throw if null table returned.
metadataTbl = GetMetadataTableByCommand(dbCommand);
}
catch (Exception e)
{
// only post diagnostic message.
// Error mesasge will only be posted to UI if the second attemp has also failed.
PostDiagnosticMessage(Localized.FailureGetMetadataTableByCommand(e.Message));
bool bCancel;
ErrorSupport.FireErrorWithArgs(HResults.DTS_E_ADODESTGETSCHEMATABLEFAILED,
out bCancel, String.Format(CultureInfo.CurrentCulture, "\n{0}\n{1}", e.Message, e.Message));
metadataTbl.Dispose();
// attemp failed, return broken.
return DTSValidationStatus.VS_ISBROKEN;
}
// To check if available external columns synchronize with database column,
// we need to check two directions.
// First check if each external column is still valid against database column
// and then the other direction.
#region preparation work.
IDTSInputColumnCollection100 iDTSInpCols = ComponentMetaData.InputCollection[0].InputColumnCollection;
IDTSExternalMetadataColumnCollection100 iDTSExtCols = ComponentMetaData.InputCollection[0].ExternalMetadataColumnCollection;
// build hashtable mapping name to its index in metadata table
int cMetaCols = metadataTbl.Rows.Count;
Hashtable hashMetaTblNameToIndex = new Hashtable(cMetaCols);
for (int iCol = 0; iCol < cMetaCols; iCol++)
{
DataRow currRow = metadataTbl.Rows[iCol];
hashMetaTblNameToIndex.Add((String)currRow[COLUMNNAME], iCol);
}
// Bool array used to record if a metadata col matches an available external
// column (by name).
bool[] aryMetaColHit = new bool[cMetaCols];
for (int iCol = 0; iCol < cMetaCols; iCol++)
{
aryMetaColHit[iCol] = false;
}
// build record of mapped external cols
int cInpCols = iDTSInpCols.Count;
Hashtable hashTblMappedMetaID = new Hashtable(cInpCols);
for (int iCol = 0; iCol < cInpCols; iCol++)
{
hashTblMappedMetaID.Add(iDTSInpCols[iCol].ExternalMetadataColumnID, null);
}
#endregion
#region For each external column, find its occurence in metadata table by name
string name = string.Empty;
int codePage = 0;
int length = 0;
int precision = 0;
int scale = 0;
DataType dtsType = DataType.DT_EMPTY;
int cExtCols = iDTSExtCols.Count;
for (int iCol = 0; iCol < cExtCols; iCol++)
{
IDTSExternalMetadataColumn100 iDTSExtCol = iDTSExtCols[iCol];
Object val = hashMetaTblNameToIndex[iDTSExtCol.Name];
// if the External column shown on the componenet is gone from the database
if (val == null)
{
// Fire error or warning depending on if the column is mapped with an input
if (hashTblMappedMetaID.ContainsKey(iDTSExtCols[iCol].ID))
{
//error message, ext col is not found
bool bCancel;
ErrorSupport.FireErrorWithArgs(
HResults.DTS_E_ADODESTEXTERNALCOLNOTEXIST,
out bCancel, iDTSExtCol.IdentificationString);
metadataTbl.Dispose();
return DTSValidationStatus.VS_NEEDSNEWMETADATA;
}
else
{
ErrorSupport.FireWarningWithArgs(
HResults.DTS_W_ADODESTEXTERNALCOLNOTEXIST,
iDTSExtCol.IdentificationString);
continue;
}
}
// find the matching col in metadata table, next check if anything has changed
int index = (int)val;
aryMetaColHit[index] = true;
DataRow currRow = metadataTbl.Rows[index];
SetMetadataValsFromRow(currRow, ref name, ref codePage, ref length, ref precision,
ref scale, ref dtsType);
STrace.Assert(name.Equals(iDTSExtCol.Name),
"meta col name is not same with ext col name used to build hash table");
string diffStr = string.Empty;
if (iDTSExtCol.CodePage != codePage)
{
diffStr += string.Format(CultureInfo.CurrentCulture, "new code page: {0} ", codePage);
}
if (iDTSExtCol.Length != length)
{
diffStr += string.Format(CultureInfo.CurrentCulture, "new length: {0} ", length);
}
if (iDTSExtCol.Precision != precision)
{
diffStr += string.Format(CultureInfo.CurrentCulture, "new precision: {0} ", precision);
}
if (iDTSExtCol.Scale != scale)
{
diffStr += string.Format(CultureInfo.CurrentCulture, "new scale: {0} ", scale);
}
if (iDTSExtCol.DataType != dtsType)
{
diffStr += string.Format(CultureInfo.CurrentCulture, "new data type: {0} ", dtsType.ToString());
}
if (diffStr != string.Empty)
{
// Fire error or warning depending on if the column is mapped with an input
if (hashTblMappedMetaID.ContainsKey(iDTSExtCols[iCol].ID))
{
bool bCancel;
ErrorSupport.FireErrorWithArgs(
HResults.DTS_W_ADODESTEXTERNALCOLNOTMATCHSCHEMACOL,
out bCancel, iDTSExtCol.IdentificationString, diffStr);
metadataTbl.Dispose();
return DTSValidationStatus.VS_NEEDSNEWMETADATA;
}
else
{
ErrorSupport.FireWarningWithArgs(
HResults.DTS_W_ADODESTEXTERNALCOLNOTMATCHSCHEMACOL,
iDTSExtCol.IdentificationString, diffStr);
}
}
}
#endregion
#region check if each metadata col still has a matching avail external col.
for (int iCol = 0; iCol < cMetaCols; iCol++)
{
if (!aryMetaColHit[iCol])
{
ErrorSupport.FireWarningWithArgs(
HResults.DTS_W_ADODESTNEWEXTCOL,
metadataTbl.Rows[iCol][COLUMNNAME]);
}
}
#endregion
metadataTbl.Dispose();
hashMetaTblNameToIndex.Clear();
hashTblMappedMetaID.Clear();
return DTSValidationStatus.VS_ISVALID;
}
public QuotesController()
{
_quoteUtil = new QuoteUtil();
}
private void extractCatalogAndSchemaNames(string fullTableName)
{
QuoteUtil quoteUtil = new QuoteUtil(m_DbConnection);
// The parameter column name restrictions of DbConnection.GetSchema is an array.
// For example, if the tablename is msdb.dbo.employee. The restrictions would be {"msdb", "dbo". "Employee"}
bool isValidName = quoteUtil.GetValidTableName(fullTableName,
out m_tableNameLvl3, out m_tableNameLvl2, out m_tableNameLvl1);
if (!isValidName)
{
bool bCancel;
ErrorSupport.FireErrorWithArgs(HResults.DTS_E_ADODESTTABLENAMEERROR,
out bCancel, quoteUtil.Prefix, quoteUtil.Sufix);
throw new PipelineComponentHResultException(HResults.DTS_E_ADODESTTABLENAMEERROR);
}
}