// 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 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 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()); }