// get item and company name by itemId
public TemporaryTable GetItemNameAndCompanyNameByItemId(int itemId)
{
Connection.Open();
string query = "SELECT ItemName, CompanyName FROM Items, Company WHERE Items.CompanyId = Company.Id and Items.Id = @itemId";
Command = new SqlCommand(query, Connection);
Command.Parameters.Add("@itemId", itemId);
Reader = Command.ExecuteReader();
TemporaryTable tempTable = new TemporaryTable();
while (Reader.Read())
{
tempTable.ItemName = Reader["ItemName"].ToString();
tempTable.CompanyName = Reader["CompanyName"].ToString();
}
Reader.Close();
Connection.Close();
return(tempTable);
}
internal Database(DatabaseSystem system, IDatabaseContext context)
{
System = system;
Context = context;
Name = Context.DatabaseName();
DiscoverDataVersion();
TableComposite = new TableSourceComposite(this);
Context.RegisterInstance(this);
Context.RegisterInstance<ITableSourceComposite>(TableComposite);
Locker = new Locker(this);
Sessions = new ActiveSessionList(this);
// Create the single row table
var t = new TemporaryTable(context, "SINGLE_ROW_TABLE", new ColumnInfo[0]);
t.NewRow();
SingleRowTable = t;
TransactionFactory = new DatabaseTransactionFactory(this);
}
internal Database(DatabaseSystem system, IDatabaseContext context)
{
System = system;
Context = context;
Name = Context.DatabaseName();
DiscoverDataVersion();
TableComposite = new TableSourceComposite(this);
Context.RegisterInstance(this);
Context.RegisterInstance <ITableSourceComposite>(TableComposite);
Locker = new Locker(this);
Sessions = new ActiveSessionList(this);
// Create the single row table
var t = new TemporaryTable(context, "SINGLE_ROW_TABLE", new ColumnInfo[0]);
t.NewRow();
SingleRowTable = t;
TransactionFactory = new DatabaseTransactionFactory(this);
}
private void AddRow(TemporaryTable tmpTable, long id, string name, DateTimeOffset date)
{
var row = new Field[3];
row[0] = Field.BigInt(id);
row[1] = Field.String(name);
row[2] = Field.Date(date);
tmpTable.NewRow(row);
}
public InsertSearchTests()
{
var leftInfo = new TableInfo(ObjectName.Parse("tab1"));
leftInfo.Columns.Add(new ColumnInfo("a", PrimitiveTypes.Integer()));
leftInfo.Columns.Add(new ColumnInfo("b", PrimitiveTypes.Boolean()));
leftInfo.Columns.Add(new ColumnInfo("c", PrimitiveTypes.Double()));
left = new TemporaryTable(leftInfo);
left.AddRow(new[] { SqlObject.Integer(23), SqlObject.Boolean(true), SqlObject.Double(5563.22) });
left.AddRow(new[] { SqlObject.Integer(54), SqlObject.Boolean(null), SqlObject.Double(921.001) });
left.AddRow(new[] { SqlObject.Integer(23), SqlObject.Boolean(true), SqlObject.Double(2010.221) });
}
public override string Translate(SqlCompilerContext context, SchemaNode node)
{
TemporaryTable tmp = node as TemporaryTable;
//temporary tables need no schema qualifier
if (tmp == null && node.Schema != null)
{
if (context == null)// extractor for some reason uses this method without context
{
return(QuoteIdentifier(new[] { node.Schema.Name, node.Name }));
}
return(QuoteIdentifier(new[] { context.SqlNodeActualizer.Actualize(node.Schema), node.Name }));
}
return(QuoteIdentifier(new[] { node.Name }));
}
public TableIndexTests()
{
var tableInfo = new TableInfo(ObjectName.Parse("test_table1"));
tableInfo.Columns.Add(new ColumnInfo("a", PrimitiveTypes.BigInt()));
tableInfo.Columns.Add(new ColumnInfo("b", PrimitiveTypes.String()));
var temp = new TemporaryTable(tableInfo);
temp.AddRow(new [] { SqlObject.BigInt(22), SqlObject.String("o102") });
temp.AddRow(new [] { SqlObject.BigInt(103), SqlObject.String("t-23") });
temp.AddRow(new[] { SqlObject.BigInt(104), SqlObject.String("test22") });
temp.BuildIndex();
table = temp;
}
private void ReadAll()
{
if (Result.Type == StatementResultType.CursorRef)
{
var tableInfo = Result.Cursor.Source.TableInfo;
localTable = new TemporaryTable("##LOCAL##", tableInfo);
foreach (var row in Result.Cursor)
{
var rowIndex = localTable.NewRow();
for (int i = 0; i < row.ColumnCount; i++)
{
localTable.SetValue(rowIndex, i, row.GetValue(i));
}
}
}
}
public static string BuildTemporaryTable(TemporaryTable table)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine($"CREATE TEMPORARY TABLE {table.Name}(");
int i = 0;
foreach (var column in table.Columns)
{
sb.AppendLine($"{column.Name} {column.DataType}{(i == table.Columns.Count - 1 ? "" : ",")}");
i++;
}
sb.AppendLine(");");
return(sb.ToString());
}
public void TestSetUp()
{
var tableInfo = new TableInfo(new ObjectName("test_table"));
tableInfo.AddColumn("id", PrimitiveTypes.Numeric());
tableInfo.AddColumn("name", PrimitiveTypes.String());
tableInfo.AddColumn("date", PrimitiveTypes.DateTime());
var cornerTime = DateTimeOffset.UtcNow;
var tmpTable = new TemporaryTable(tableInfo);
AddRow(tmpTable, 1, "test1", cornerTime);
AddRow(tmpTable, 2, "test2", cornerTime.AddSeconds(2));
AddRow(tmpTable, 3, "test3", cornerTime.AddSeconds(5));
tmpTable.BuildIndexes();
table = tmpTable;
}
public void TestSetUp()
{
var tableInfo = new TableInfo(new ObjectName("test_table"));
tableInfo.AddColumn("id", PrimitiveTypes.Numeric());
tableInfo.AddColumn("name", PrimitiveTypes.String());
tableInfo.AddColumn("date", PrimitiveTypes.DateTime());
var cornerTime = DateTimeOffset.UtcNow;
var tmpTable = new TemporaryTable(tableInfo);
AddRow(tmpTable, 1, "test1", cornerTime);
AddRow(tmpTable, 2, "test2", cornerTime.AddSeconds(2));
AddRow(tmpTable, 3, "test3", cornerTime.AddSeconds(5));
tmpTable.BuildIndexes();
table = tmpTable;
}
public void TestSetUp()
{
var tableName = ObjectName.Parse("APP.test_table");
var tableInfo = new TableInfo(tableName);
tableInfo.AddColumn("id", PrimitiveTypes.Numeric(), true);
tableInfo.AddColumn("name", PrimitiveTypes.String(SqlTypeCode.VarChar));
tableInfo.AddColumn("date", PrimitiveTypes.DateTime());
cornerTime = DateTimeOffset.UtcNow;
var tmpTable = new TemporaryTable(tableInfo);
AddRow(tmpTable, 1, "test1", cornerTime);
AddRow(tmpTable, 2, "test2", cornerTime.AddSeconds(2));
AddRow(tmpTable, 3, "test3", cornerTime.AddSeconds(5));
tmpTable.BuildIndexes(DefaultIndexTypes.BlindSearch);
table = tmpTable;
}
// get data for temporary table
private List <TemporaryTable> GetDataForTemporaryTable(List <StockOut> stockOutList)
{
List <TemporaryTable> temporaryTableList = new List <TemporaryTable>();
// for serial no
int count = 0;
foreach (StockOut stockOut in stockOutList)
{
TemporaryTable tempTable = itemManager.GetItemNameAndCompanyNameByItemId(stockOut.ItemId);
count++;
tempTable.SL = count;
tempTable.Quantity = stockOut.StockOutQuantity;
temporaryTableList.Add(tempTable);
}
return(temporaryTableList);
}
public static IEnumerable <SchemaObject> CollectLocalSchema(this TSqlStatement statement, ILogger logger, SchemaFile file)
{
switch (statement)
{
case BeginEndBlockStatement beginEndBlockStatement:
return(beginEndBlockStatement
.StatementList
.Statements
.CollectLocalSchema(logger, file)
.ToList());
case DeclareVariableStatement declareVariableStatement:
{
foreach (var declaration in declareVariableStatement.Declarations)
{
var name = declaration.VariableName.Value;
var isNullable = false; // TODO : how to determine this?
var variable = declaration.DataType.GetField(name, isNullable, logger, file);
variable.Origin = OriginType.Variable;
file.FileContext.Variables.Peek().Add(variable);
}
// TODO : what should I return here?
break;
}
case DeclareTableVariableStatement declareTableVariableStatement:
{
var columns = declareTableVariableStatement
.Body
.Definition
.ColumnDefinitions
.GetFields(logger, file)
.ToList();
var tableReference = new Table()
{
Columns = columns,
File = file,
Database = SchemaObject.TempDb,
Schema = SchemaObject.DefaultSchema,
Identifier = declareTableVariableStatement.Body.VariableName.Value,
};
var field = new TableReferenceField()
{
Name = declareTableVariableStatement.Body.VariableName.Value,
Type = FieldType.Table,
Origin = OriginType.Variable,
IsNullable = false,
Reference = tableReference,
};
file.FileContext.Variables.Peek().Add(field);
// TODO : what should I return here?
break;
}
// TODO : this could be an actual create table statement and not just a temp table
case CreateTableStatement createTableStatement:
{
if (!createTableStatement.SchemaObjectName.BaseIdentifier.Value.StartsWith("#"))
{
break; // not a temp table
}
var columns = createTableStatement
.Definition
.ColumnDefinitions
.GetFields(logger, file)
.ToList();
columns.ForEach(c => c.Origin = OriginType.Table);
var tempTable = new TemporaryTable()
{
Database = createTableStatement.SchemaObjectName.DatabaseIdentifier?.Value ?? SchemaObject.TempDb,
Schema = createTableStatement.SchemaObjectName.SchemaIdentifier?.Value ?? SchemaObject.DefaultSchema,
Identifier = createTableStatement.SchemaObjectName.BaseIdentifier.Value,
File = file,
Columns = columns,
};
file
.LocalSchema
.Add(new KeyValuePair <string, SchemaObject>(tempTable.GetQualifiedIdentfier(), tempTable));
break;
}
case IfStatement ifStatement:
{
// TODO : conditional output? which data set to return? we don't know till runtime
var thenReferences = ifStatement.ThenStatement.CollectLocalSchema(logger, file).ToList();
if (ifStatement.ElseStatement != null)
{
var elseReferences = ifStatement.ElseStatement.CollectLocalSchema(logger, file).ToList();
return(thenReferences.Concat(elseReferences));
}
return(thenReferences);
}
case SelectStatement selectStatement:
{
var columns = selectStatement.GetFields(logger, file);
if (!columns.Any())
{
// if there are no columns there's no data set to return..
// this happens for SELECT statement that assigns values to variables
break;
}
if (selectStatement.Into != null &&
selectStatement.Into.BaseIdentifier.Value.StartsWith("#") &&
!file.LocalSchema.ContainsKey(selectStatement.Into.GetTemporaryQualifiedIdentfier()))
{
var tempTableColumns = selectStatement.GetFields(logger, file);;
var tempTable = new TemporaryTable()
{
Columns = tempTableColumns,
File = file,
Database = SchemaObject.TempDb,
Schema = SchemaObject.DefaultSchema,
Identifier = selectStatement.Into.BaseIdentifier.Value,
};
file
.LocalSchema
.Add(new KeyValuePair <string, SchemaObject>(tempTable.GetQualifiedIdentfier(), tempTable));
}
var dataSet = new DerivedTable()
{
Columns = columns,
File = file,
Identifier = selectStatement.GetTokenText(),
};
return(new List <SchemaObject>()
{
dataSet
});
}
case WhileStatement whileStatement:
return(whileStatement.Statement.CollectLocalSchema(logger, file));
case TryCatchStatement tryCatchStatement:
{
var tryReferences = tryCatchStatement.TryStatements.Statements.CollectLocalSchema(logger, file);
var catchReferences = tryCatchStatement.CatchStatements.Statements.CollectLocalSchema(logger, file);
return(tryReferences.Concat(catchReferences).ToList());
}
case ReturnStatement x:
{
// TODO : check this statement, do I want to stop collecting data sets now?
// what if it is conditinal return statement?
break;
}
case MergeStatement mergeStatement:
break; // TODO : what to do with this one?
// NOTE : I don't care about these statements yet
case PredicateSetStatement x: break;
case SetVariableStatement x: break;
case SetCommandStatement x: break;
case SetRowCountStatement x: break;
case UseStatement x: break;
case DenyStatement x: break;
case RevokeStatement x: break;
case SetIdentityInsertStatement x: break;
case SetTransactionIsolationLevelStatement x: break;
case BeginTransactionStatement x: break;
case RollbackTransactionStatement x: break;
case CommitTransactionStatement x: break;
case RaiseErrorStatement x: break;
case ThrowStatement x: break;
case BreakStatement x: break;
case ContinueStatement x: break;
case SaveTransactionStatement x: break;
case UpdateStatisticsStatement x: break;
case InsertStatement x: break;
case UpdateStatement x: break;
case DeleteStatement x: break;
case ExecuteStatement x: break;
case GrantStatement x: break;
case CreateIndexStatement x: break;
case GoToStatement x: break;
case LabelStatement x: break;
case PrintStatement x: break;
case DeclareCursorStatement x: break;
case OpenCursorStatement x: break;
case FetchCursorStatement x: break;
case CloseCursorStatement x: break;
case DeallocateCursorStatement x: break;
case WaitForStatement x: break;
case BeginDialogStatement x: break;
case SendStatement x: break;
case EndConversationStatement x: break;
// TODO : statements to generate schema.. might be useful for sql in the test project
case TruncateTableStatement x: break;
case DropTableStatement x: break;
case DropViewStatement x: break;
case CreateFunctionStatement x: break;
case AlterFunctionStatement x: break;
case CreateOrAlterFunctionStatement x: break;
case DropFunctionStatement x: break;
case AlterTableAddTableElementStatement x: break;
case AlterTableConstraintModificationStatement x: break;
case CreateTypeTableStatement x: break;
case CreateViewStatement x: break;
case AlterViewStatement x: break;
case DropProcedureStatement x: break;
case CreateProcedureStatement x: break;
case CreateOrAlterProcedureStatement x: break;
case CreateOrAlterViewStatement x: break;
case AlterTableSetStatement x: break;
case AlterProcedureStatement x: break;
case CreateTypeUddtStatement x: break;
default:
{
logger.Log(LogLevel.Warning,
LogType.NotSupportedYet,
file.Path,
$"\"{statement.GetType()}\" Tsql statement is not supported yet. " +
$"Fragment: \"{statement.GetTokenText()}\"");
break;
}
}
return(new List <SchemaObject>());
}
/// <inheritdoc/>
/// <exception cref="NotSupportedException">Method is not supported.</exception>
protected override IPathNode VisitTemporaryTable(TemporaryTable temporaryTable)
{
// http://support.x-tensive.com/question/3643/oracle-database-domainbuild-throws-notsupportedexception
return(null);
}
public void TestSetUp()
{
var tableName = ObjectName.Parse("APP.test_table");
var tableInfo = new TableInfo(tableName);
tableInfo.AddColumn("id", PrimitiveTypes.Numeric(), true);
tableInfo.AddColumn("name", PrimitiveTypes.String(SqlTypeCode.VarChar));
tableInfo.AddColumn("date", PrimitiveTypes.DateTime());
cornerTime = DateTimeOffset.UtcNow;
var tmpTable = new TemporaryTable(tableInfo);
AddRow(tmpTable, 1, "test1", cornerTime);
AddRow(tmpTable, 2, "test2", cornerTime.AddSeconds(2));
AddRow(tmpTable, 3, "test3", cornerTime.AddSeconds(5));
tmpTable.BuildIndexes(DefaultIndexTypes.BlindSearch);
table = tmpTable;
}
private void AddRow(TemporaryTable tmpTable, long id, string name, DateTimeOffset date)
{
var row = new Field[3];
row[0] = Field.BigInt(id);
row[1] = Field.String(name);
row[2] = Field.Date(date);
tmpTable.NewRow(row);
}
public void Dispose()
{
left = null;
}
public ActionResult GetAnsweredQuestion()
{
IEnumerable <int> getIds = b.UserPostedQuestions.Select(m => m.Id);
try
{
foreach (int id in getIds)
{
UserPostedQuestion del = b.UserPostedQuestions.Where(m => m.Id.Equals(id)).Single();
b.UserPostedQuestions.Remove(del);
}
b.SaveChanges();
TemporaryTable tmp = b.TemporaryTables.SingleOrDefault(m => m.Id.Equals(1));
UserPostedQuestion ui = new UserPostedQuestion();
var ansQuesList = (from q in _db.Questions
join u in _db.ForumUser on q.UserId equals u.UserId
join a in _db.Answers on q.QuestionId equals a.QuestionId
join c in _db.Category on q.CategoryId equals c.CategoryId
orderby q.PostedDate
select
new AnsweredQuestionModel()
{
Question = q.Question,
PostedDate = q.PostedDate,
Username = u.UserName,
Category = c.CategoryName,
Views = q.views,
Replies = q.answers,
Id = q.QuestionId,
Image = u.Image
});
foreach (var h in ansQuesList)
{
string g = h.Question;
var v = b.UserPostedQuestions.FirstOrDefault(
m => m.Question.Equals(g));
if (v == null)
{
ui.Question = h.Question;
ui.Answer = h.Question;
ui.ReplyUser = h.Username;
ui.PostedDate = h.PostedDate;
ui.Image = h.Image;
ui.QuesPostDate = h.PostedDate;
ui.QuestionId = h.Id;
ui.Category = h.Category;
ui.TotalAnswer = h.Views;
ui.TotalReplies = h.Replies;
b.UserPostedQuestions.Add(ui);
b.SaveChanges();
}
}
ViewData["AnsweredQuesList"] = (from a in b.UserPostedQuestions
select new QuestionList()
{
Question = a.Question,
PostedBy = a.ReplyUser,
Image = a.Image,
Id = a.QuestionId.Value,
Category = a.Category,
Views = a.TotalAnswer.Value,
Answers = a.TotalReplies.Value,
Posteddate = a.QuesPostDate.Value
}).OrderByDescending(x => x.Posteddate).ToList();
}
catch (Exception ex)
{
}
ViewData["FQAC"] = 2;
return(View());
}
