示例#1
0
        public void SqlStringTest()
        {
            ISqlObject       source = new SqlServerSource("Table");
            ISqlObject       field0 = new SqlServerField(source, "Field0", null);
            ISqlFunction     field1 = new SqlFunction("COUNT").AddArgument(new SqlAllField(null));
            ISqlSelectClause target = new SqlSelectClause().AddExpressions(field0, field1);

            Assert.AreEqual <string>(string.Format("{0} {1}, {2}", target.Keyword, field0.SqlString, field1.SqlString), target.SqlString);
        }
        public void SqlStringTest_More()
        {
            ISqlObject      source = new SqlServerSource("Table");
            ISqlObject      field0 = new SqlServerField(source, "AddTime", null);
            ISqlObject      field1 = SqlObject.FromVariable("ROWNUM");
            ISqlOrderClause target = new SqlOrderClause().AddExpression(field0, SqlOrder.Desc).AddExpression(field1, SqlOrder.Asc);

            Assert.AreEqual <string>(string.Format("{0} ({1}) DESC, ({2}) ASC", target.Keyword, field0.Fullname, field1.Fullname), target.SqlString);
        }
示例#3
0
        public void ExecuteSelectScalarTest()
        {
            ISqlObject          source    = new SqlServerSource(TABLE_NAME);
            ISqlObject          field     = new SqlServerField(source, "ID", null);
            ISqlSelectStatement statement = new SqlServerSelectStatement();

            statement.SelectClause.AddExpressions(field);
            statement.FromClause.Source = source;
            statement.OrderClause.AddExpression(field, SqlOrder.Asc);
            Assert.AreEqual <int>(1, SqlHelper.ExecuteSelectScalar <int>(new SqlConnection(Properties.Settings.Default.SqlConnectionString), statement));
        }
        public void GetSqlStringTest()
        {
            ISqlObject     source = new SqlServerSource("Table");
            ISqlObject     field0 = new SqlServerField(source, "ID", null);
            ISqlExpression value0 = new SqlStringExpression("13");
            ISqlObject     field1 = new SqlServerField(source, "Type", null);
            ISqlExpression value1 = new SqlStringExpression("14");

            Assert.AreEqual <string>(string.Format("{0} ({1}, {2}) VALUES ({3}, {4})", source.Fullname, field0.Fullname, field1.Fullname, value0.SqlString, value1.SqlString),
                                     new SqlFieldValueClause(new SqlInsertFieldValueClauseSqlStringProvider()).SetSource(source).AddField(field0, value0).AddField(field1, value1).SqlString);
        }
示例#5
0
        public void SqlServerFieldConstructorTest_Function()
        {
            ISqlFunction function = new SqlFunction("SUM");

            function.AddArgument(new SqlStringExpression("1"));
            function.AddArgument(new SqlStringExpression("2"));
            ISqlObject field = new SqlServerField(function, "Total");

            Assert.AreEqual <string>(string.Format("[{0}]", field.Name), field.Fullname);
            Assert.AreEqual <string>(string.Format("{0} AS [{1}]", function.SqlString, field.Name), field.SqlString);
        }
示例#6
0
        public void CountTest()
        {
            ISqlObject            source = new SqlServerSource("Table");
            ISqlObject            field  = new SqlServerField(source, "ID", null);
            SqlServerSelectClause target = new SqlServerSelectClause();

            target.AddExpressions(field);
            target.Count = 13;

            Assert.AreEqual <string>(string.Format("{0} TOP {1} {2}", target.Keyword, target.Count, field.SqlString), target.SqlString);
        }
        public void SqlStringTest()
        {
            ISqlObject          source    = new SqlServerSource("Table");
            ISqlObject          field     = new SqlServerField(source, "ID", null);
            ISqlDeleteStatement statement = new SqlDeleteStatement();

            statement.FromClause.SetSource(source);
            statement.WhereClause.Condition = field.GreaterThan(new SqlStringExpression("13"));

            Assert.AreEqual <string>(string.Format("{0} {1} {2}", statement.Keyword, statement.FromClause.SqlString, statement.WhereClause.SqlString), statement.SqlString);
        }
        public void CountTest()
        {
            ISqlObject source = new SqlServerSource("Table");
            ISqlObject field  = new SqlServerField(source, "ID", null);
            SqlServerSelectStatement target = new SqlServerSelectStatement();

            target.SelectClause.AddExpressions(field);
            target.FromClause.SetSource(source);
            target.Count = 13;

            Assert.AreEqual <string>(string.Format("{0} {1}", target.SelectClause.SqlString, target.FromClause.SqlString), target.SqlString);
        }
示例#9
0
        public void InnerJoinTest()
        {
            ISqlObject     source0   = new SqlServerSource("Table0");
            ISqlObject     source1   = new SqlServerSource("Table1");
            ISqlObject     id0       = new SqlServerField(source0, "ID", null);
            ISqlObject     id1       = new SqlServerField(source1, "ID", null);
            ISqlExpression condition = id0.Equal(id1);
            SqlFromClause  target    = new SqlFromClause();

            Assert.AreEqual <string>(string.Format("FROM {0} INNER JOIN {1} ON {2}", source0.SqlString, source1.SqlString, condition.SqlString),
                                     target.SetSource(source0).InnerJoin(source1, condition).SqlString);
        }
        public void SqlStringTest_NoWhere()
        {
            ISqlObject          source    = new SqlServerSource("Table");
            ISqlObject          field0    = new SqlServerField(source, "ID", null);
            ISqlExpression      value0    = new SqlStringExpression("13");
            ISqlObject          field1    = new SqlServerField(source, "Type", null);
            ISqlExpression      value1    = new SqlStringExpression("14");
            ISqlUpdateStatement statement = new SqlUpdateStatement();

            statement.FieldValueClause.SetSource(source).AddField(field0, value0).AddField(field1, value1);

            Assert.AreEqual <string>(string.Format("{0} {1}", statement.Keyword, statement.FieldValueClause.SqlString),
                                     statement.SqlString);
        }
        public void ParametersTest()
        {
            ISqlObject     source = new SqlServerSource("Table");
            ISqlObject     field0 = new SqlServerField(source, "ID", null);
            ISqlExpression value0 = SqlStringExpression.FromParameter(new SqlParameter("@p0", 13));
            ISqlObject     field1 = new SqlServerField(source, "Type", null);
            ISqlExpression value1 = SqlStringExpression.FromParameter(new SqlParameter("@p1", 14));

            ReadOnlyCollection <IDataParameter> parameters = new SqlFieldValueClause(new SqlUpdateFieldValueClauseSqlStringProvider()).SetSource(source).AddField(field0, value0).AddField(field1, value1).Parameters;

            Assert.AreEqual <int>(2, parameters.Count);
            Assert.IsTrue(parameters.Contains(value0.Parameters[0]));
            Assert.IsTrue(parameters.Contains(value1.Parameters[0]));
        }
        public void SqlStringTest_Parametric()
        {
            ISqlObject          source    = new SqlServerSource("Table");
            ISqlObject          field     = new SqlServerField(source, "ID", null);
            ISqlExpression      value     = SqlStringExpression.FromParameter(new SqlParameter("@p", 13));
            ISqlDeleteStatement statement = new SqlDeleteStatement();

            statement.FromClause.SetSource(source);
            statement.WhereClause.Condition = field.GreaterThan(value);

            Assert.AreEqual <string>(string.Format("{0} {1} {2}", statement.Keyword, statement.FromClause.SqlString, statement.WhereClause.SqlString), statement.SqlString);
            ReadOnlyCollection <IDataParameter> parameters = statement.Parameters;

            Assert.AreEqual <int>(1, parameters.Count);
            Assert.IsTrue(parameters.Contains(value.Parameters[0]));
        }
        public void SqlStringTest()
        {
            ISqlObject          target    = new SqlServerSource("Table1");
            ISqlObject          field0    = new SqlServerField(target, "ID", null);
            ISqlExpression      value0    = new SqlStringExpression("13");
            ISqlObject          field1    = new SqlServerField(target, "Type", null);
            ISqlObject          source    = new SqlServerSource("Table2");
            ISqlExpression      value1    = new SqlServerField(source, "Type", null);
            ISqlUpdateStatement statement = new SqlUpdateStatement();

            statement.FieldValueClause.SetSource(target).AddField(field0, value0).AddField(field1, value1);
            statement.FromClause.Source     = source;
            statement.WhereClause.Condition = new SqlServerField(target, "Flag", null).Equal(new SqlStringExpression("1"));

            Assert.AreEqual <string>(string.Format("{0} {1} {2} {3}", statement.Keyword, statement.FieldValueClause.SqlString, statement.FromClause.SqlString, statement.WhereClause.SqlString),
                                     statement.SqlString);
        }
示例#14
0
        public void InnerJoinTest_Parametric()
        {
            ISqlObject     source0   = new SqlServerSource("Table0");
            ISqlObject     source1   = new SqlServerSource("Table1");
            ISqlObject     id0       = new SqlServerField(source0, "ID", null);
            ISqlObject     id1       = new SqlServerField(source1, "ID", null);
            ISqlExpression value     = SqlStringExpression.FromParameter(new SqlParameter("@p", 13));
            ISqlExpression condition = id0.Equal(id1).And(id0.GreaterEqual(value));
            SqlFromClause  target    = new SqlFromClause();

            Assert.AreEqual <string>(string.Format("FROM {0} INNER JOIN {1} ON {2}", source0.SqlString, source1.SqlString, condition.SqlString),
                                     target.SetSource(source0).InnerJoin(source1, condition).SqlString);
            ReadOnlyCollection <IDataParameter> parameters = target.Parameters;

            Assert.AreEqual <int>(1, parameters.Count);
            Assert.IsTrue(parameters.Contains(value.Parameters[0]));
        }
示例#15
0
        public void ExecuteUpdateTest()
        {
            int?         id         = null;
            ISqlObject   source     = new SqlServerSource(TABLE_NAME);
            DbConnection connection = new SqlConnection(Properties.Settings.Default.SqlConnectionString);

            try {
                ISqlSelectStatement identityStatement = new SqlServerSelectStatement();
                identityStatement.SelectClause.AddExpressions(new SqlFunction("IDENT_CURRENT").AddArgument(new SqlServerExpressionFactory().ConstantFactory.Create(TABLE_NAME)));

                ISqlObject          nameField       = new SqlServerField(source, "Name", null);
                ISqlExpression      name            = SqlStringExpression.FromParameter(new SqlParameter("@name", "Insert Test"));
                ISqlInsertStatement insertStatement = new SqlInsertStatement();
                insertStatement.FieldValueClause.SetSource(source).AddField(nameField, name);
                id = SqlHelper.ExecuteInsert(connection, insertStatement, identityStatement);

                ISqlSelectStatement selectStatement = new SqlServerSelectStatement();
                selectStatement.SelectClause.AddExpressions(nameField);
                selectStatement.FromClause.Source     = source;
                selectStatement.WhereClause.Condition = new SqlServerField(source, "ID", null).Equal(new SqlServerExpressionFactory().ConstantFactory.Create(id.Value));
                Assert.AreEqual <string>((string)name.Parameters[0].Value, SqlHelper.ExecuteSelectScalar <string>(connection, selectStatement));

                name = SqlStringExpression.FromParameter(new SqlParameter("@name", "Insert Test Updated"));
                ISqlUpdateStatement updateStatement = new SqlUpdateStatement();
                updateStatement.FieldValueClause.SetSource(source).AddField(nameField, name);
                updateStatement.WhereClause.Condition = new SqlServerField(source, "ID", null).Equal(new SqlServerExpressionFactory().ConstantFactory.Create(id.Value));
                SqlHelper.ExecuteUpdate(connection, updateStatement);
                Assert.AreEqual <string>((string)name.Parameters[0].Value, SqlHelper.ExecuteSelectScalar <string>(connection, selectStatement));
            } finally {
                if (id.HasValue)
                {
                    ISqlDeleteStatement deleteStatement = new SqlDeleteStatement();
                    deleteStatement.FromClause.Source     = source;
                    deleteStatement.WhereClause.Condition = new SqlServerField(source, "ID", null).Equal(new SqlServerExpressionFactory().ConstantFactory.Create(id.Value));
                    SqlHelper.ExecuteDelete(connection, deleteStatement);
                }
            }
        }
        public void SqlStringTest_Parametric()
        {
            ISqlObject          source    = new SqlServerSource("Table");
            ISqlObject          field0    = new SqlServerField(source, "ID", null);
            ISqlExpression      value0    = SqlStringExpression.FromParameter(new SqlParameter("@ID", 13));
            ISqlObject          field1    = new SqlServerField(source, "Type", null);
            ISqlExpression      value1    = SqlStringExpression.FromParameter(new SqlParameter("@Type", 14));
            ISqlUpdateStatement statement = new SqlUpdateStatement();

            statement.FieldValueClause.SetSource(source).AddField(field0, value0).AddField(field1, value1);
            ISqlExpression value2 = SqlStringExpression.FromParameter(new SqlParameter("@Flag", 1));

            statement.WhereClause.Condition = new SqlServerField(source, "Flag", null).Equal(value2);

            Assert.AreEqual <string>(string.Format("{0} {1} {2}", statement.Keyword, statement.FieldValueClause.SqlString, statement.WhereClause.SqlString),
                                     statement.SqlString);
            ReadOnlyCollection <IDataParameter> parameters = statement.Parameters;

            Assert.AreEqual <int>(3, parameters.Count);
            Assert.IsTrue(parameters.Contains(value0.Parameters[0]));
            Assert.IsTrue(parameters.Contains(value1.Parameters[0]));
            Assert.IsTrue(parameters.Contains(value2.Parameters[0]));
        }