C# Data.SqlQuery类代码示例

 public void FirstIntoRowShouldReturnFirstRowIfMoreThanOneEntityUsed()
 {
     var first = new MyEntity() { Table = "x" };
     var second = new MyEntity() { Table = "y" };
     var query = new SqlQuery().From(first).From(second.Table, Alias.T1).Into(second);
     Assert.Equal(first, ((ISqlQueryExtensible)query).FirstIntoRow);
 }

        public void DistinctAddsKeyword()
        {
            var query = new SqlQuery()
                .Distinct(true)
                .Select("TestColumn")
                .From("TestTable");

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT DISTINCT TestColumn FROM TestTable"),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        public void FromMultipleCallsDoesCrossJoin()
        {
            var query = new SqlQuery()
                .From("TestTable1")
                .From("TestTable2")
                .Select("TestColumn");

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT TestColumn FROM TestTable1, TestTable2"),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        public void DistinctCanBeTurnedOff()
        {
            var query = new SqlQuery()
                .Distinct(true)
                .Select("TestColumn")
                .From("TestTable")
                .Distinct(false);

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT TestColumn FROM TestTable"),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        public void FromMixedOverloadsMultipleCallsDoesCrossJoin()
        {
            var query = new SqlQuery()
                .From("TestTable1", new Alias("x1"))
                .From(new Alias("TestTable2", "x2"))
                .From("TestTable3")
                .Select("TestColumn");

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT TestColumn FROM TestTable1 x1, TestTable2 x2, TestTable3"),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        public override void OnPrepareQuery(ISaveRequestHandler handler, SqlQuery query)
        {
            base.OnPrepareQuery(handler, query);

            if (this.replaceFields != null)
            {
                foreach (var field in replaceFields.Values)
                {
                    if (!field.IsTableField() &&
                        (!(query is ISqlQueryExtensible) ||
                          ((ISqlQueryExtensible)query).GetSelectIntoIndex(field) <= 0))
                        query.Select(field);
                }
            }
        }

        public void DoesAutomaticJoinProperlyWithAliasedFields()
        {
            var x = RowMappingTests.ComplexRow.Fields.As("x");

            var query = new SqlQuery()
                    .From(x)
                    .Select(x.CountryName);

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT x_c.Name AS [CountryName] FROM ComplexTable x " +
                    "LEFT JOIN TheCountryTable x_c ON (x_c.TheCountryID = x.CountryID)"),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        /// <summary>
        /// Creates a clone of the query.
        /// </summary>
        /// <returns>A cloned query.</returns>
        /// <remarks>
        /// Clones states like TrackAssignments, AssignedFields etc,
        /// creates a copy of Params dictionary
        /// </remarks>
        public SqlQuery Clone()
        {
            var clone = new SqlQuery();

            clone.dialect = dialect;
            clone.dialectOverridden = dialectOverridden;
            clone.skip = skip;
            clone.take = take;
            clone.countRecords = countRecords;

            clone.into = new List<object>(into);
            clone.intoIndex = intoIndex;

            Column s;
            for (int i = 0; i < columns.Count; i++)
            {
                s = columns[i];
                var si = new Column(s.Expression, s.ColumnName, s.IntoRowIndex, s.IntoField);
                clone.columns.Add(si);
            }

            clone.from = new StringBuilder(from.ToString());
            if (where != null)
                clone.where = new StringBuilder(where.ToString());

            if (orderBy != null)
            {
                clone.orderBy = new List<string>();
                clone.orderBy.AddRange(orderBy);
            }

            if (groupBy != null)
                clone.groupBy = new StringBuilder(groupBy.ToString());

            if (having != null)
                clone.having = new StringBuilder(having.ToString());

            if (parent != null)
                clone.parent = this.parent;
            else if (this.Params != null)
                foreach (var pair in this.Params)
                    clone.AddParam(pair.Key, pair.Value);

            return clone;
        }

        public void DoesAutomaticJoinProperlyWithAliasedAndJoinedFieldsAndSelect()
        {
            var x = RowMappingTests.ComplexRow.Fields.As("x");
            var y = RowMappingTests.ComplexRow.Fields.As("y");

            var query = new SqlQuery()
                    .From(x)
                    .LeftJoin(y, y.ID == x.ID)
                    .Select(x.CountryName, "CountryNameX")
                    .Select(y.CountryName, "CountryNameY");

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT " +
                        "x_c.Name AS [CountryNameX], " +
                        "y_c.Name AS [CountryNameY] " +
                    "FROM ComplexTable x " +
                    "LEFT JOIN ComplexTable y ON (y.ComplexID = x.ComplexID) " +
                    "LEFT JOIN TheCountryTable x_c ON (x_c.TheCountryID = x.CountryID) " +
                    "LEFT JOIN TheCountryTable y_c ON (y_c.TheCountryID = y.CountryID)"),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        public void DoesAutomaticJoinProperlyWithViewRowIfAllNamesAreSelectedInJoinOrder()
        {
            var fld = ViewRow.Fields;

            var query = new SqlQuery()
                    .From(fld)
                    .Select(fld.District)
                    .Select(fld.City)
                    .Select(fld.Country);

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT " +
                        "d.Name AS [District], " +
                        "c.Name AS [City], " +
                        "o.Name AS [Country] " +
                    "FROM ViewTable T0 " +
                    "LEFT JOIN Districts d ON (d.DistrictID = T0.DistrictID) " +
                    "LEFT JOIN Cities c ON (c.CityID = d.CityID) " +
                    "LEFT JOIN Countries o ON (o.CountryID = c.CountryID) "),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        public SaveResponse TxtYukle(IUnitOfWork uow, SaveRequest<MyRow> request)
        {
            if (request.Entity == null)
                throw new Exception("Entity null olamaz! ");

            if (request.Entity.KurumId == null)
                throw new Exception("KurumId null olamaz! ");

            if (request.Entity.OptikFormId == null)
                throw new Exception("OptikFormId null olamaz! ");

            if (request.Entity.SinavId == null)
                throw new Exception("SinavId null olamaz! ");

            if (request.Entity.Txt.IsEmptyOrNull())
                throw new Exception("Dosya null olamaz! ");

            var kurum = new KurumRepository().Retrieve(uow.Connection, new RetrieveRequest() { EntityId = request.Entity.KurumId }).Entity;
            var optikFormBayi = new OptikFormRepository().Retrieve(uow.Connection, new RetrieveRequest() { EntityId = request.Entity.OptikFormId }).Entity;
            var sinav = new SinavRepository().Retrieve(uow.Connection, new RetrieveRequest() { EntityId = request.Entity.SinavId }).Entity;
            var optikFormSinav = new OptikFormRepository().Retrieve(uow.Connection, new RetrieveRequest() { EntityId = request.Entity.OptikFormId }).Entity;
            var bayi = new BayiRepository().Retrieve(uow.Connection, new RetrieveRequest() { EntityId = 2 }).Entity;

            if (kurum == null)
                throw new Exception("Entity null olamaz! ");

            ////zaten retrieve çağrısında kayıt gelmez ise kendisi otomatik olarak hata dönüyor.
            if (optikFormSinav == null || optikFormBayi == null)
                throw new Exception("optikForm null olamaz! ");

            if (sinav == null)
                throw new Exception("sinav null olamaz! ");

            if (bayi == null)
                throw new Exception("Bayi null olamaz! ");

            bool optikFormDegistiMi = optikFormBayi.Id != optikFormSinav.Id;
            //var dd = KurumRow.Fields.IlceAd.PropertyName;
            //var flds = OptikFormAlanRow.Fields;

            //var optikFormAlanlari = new OptikFormAlanRepository()
            //    .List(uow.Connection, new ListRequest() { Criteria = new Criteria(OptikFormAlanRow.Fields.FormKodu.PropertyName.ToString()) == optikFormBayi.FormKodu.Value, Sort = new List<SortBy> { new SortBy { Field = OptikFormAlanRow.Fields.Baslangic.PropertyName, Descending = false } }.ToArray() })
            //    .Entities;

            //var fldOptikFormAlan = OptikFormAlanRow.Fields;
            //var qq = new SqlQuery().From(OptikFormAlanRow.Fields.TableName)
            //       .Select(fldOptikFormAlan.AlanAdi)
            //       .Select(fldOptikFormAlan.Baslangic.PropertyName + " as  old" + fldOptikFormAlan.Baslangic.PropertyName)
            //       .Select(fldOptikFormAlan.Uzunluk.PropertyName + " as  old" + fldOptikFormAlan.Uzunluk.PropertyName)
            //       .Select(fldOptikFormAlan.Baslangic.PropertyName + " as  new" + fldOptikFormAlan.Baslangic.PropertyName)
            //       .Select(fldOptikFormAlan.Uzunluk.PropertyName + " as  new" + fldOptikFormAlan.Uzunluk.PropertyName)
            //       .Join(new LeftJoin(fldOptikFormAlan.TableName, "T1", new Criteria(fldOptikFormAlan.FormKodu) == 100 & new Criteria(fldOptikFormAlan.FormKodu) == 2001))
            //       ;
            //var dd = qq.ToString();

            /*
             new Criteria(fldOptikFormAlan.FormKodu) == 2001
             sonuç olarak T0.FormKodu = 2001 sonucu çıkıyor
             *
             * coalesce kullanımı == Select(Sql.Coalesce(new string[2] { s.AlanAdi.Expression, b.AlanAdi.Expression }))
            */

            var s = OptikFormAlanRow.Fields.As("s");
            var b = OptikFormAlanRow.Fields.As("b");
            var sss = new SqlQuery()
                .From(s)
                .InnerJoin(b, new Criteria(s.FormKodu) == optikFormSinav.FormKodu.Value & new Criteria(b.FormKodu) == optikFormSinav.FormKodu.Value & new Criteria(s.AlanAdi) == new Criteria(b.AlanAdi))
                .Select(s.AlanAdi)
                .Select(s.Baslangic, "BaslangicS")
                .Select(s.Uzunluk, "UzunlukS")
                .Select(b.Baslangic, "BaslangicB")
                .Select(b.Uzunluk, "UzunlukB")
                .OrderBy(s.Baslangic);

            List<OptikFormEslestirmeView> optikFormAlanlari = new List<OptikFormEslestirmeView>();

            using (IDataReader reader = SqlHelper.ExecuteReader(uow.Connection, sss))
            {
                while (reader.Read())
                {
                    OptikFormEslestirmeView tRow = new OptikFormEslestirmeView();
                    tRow.AlanAdi = reader.AsString(0);
                    tRow.SinavBaslangic = reader.AsInt32(1);
                    tRow.SinavUzunluk = reader.AsInt32(2);
                    tRow.BayiBaslangic = reader.AsInt32(3);
                    tRow.BayiUzunluk= reader.AsInt32(4);

                    optikFormAlanlari.Add(tRow);
                }
            }

            //        .Where(filter)
            //       .GetFirst(connection))
            //{
            //    return row;
            //}

            int offset = 395;
            int length = 20;
            var path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"App_Data\upload\" + request.Entity.Txt.Replace("/", "\\"));
//.........这里部分代码省略.........

        public void WithPassesAndReturnsTheQueryItself()
        {
            var query = new SqlQuery();
            var afterWith = query.With(insideWidth =>
            {
                Assert.Equal(query, insideWidth);
            });

            Assert.Equal(query, afterWith);
        }

 public void WhereDoesAndWhenCalledMoreThanOnce()
 {
     var query = new SqlQuery().From("t").Select("c").Where("x > 5").Where("y < 4");
     Assert.Equal(
         TestSqlHelper.Normalize(
             "SELECT c FROM t WHERE x > 5 AND y < 4"),
         TestSqlHelper.Normalize(
             query.ToString())
     );
 }

        public void TakeUsesCorrectSyntaxForSqliteDialect()
        {
            var query = new SqlQuery()
                .Dialect(SqliteDialect.Instance)
                .Select("c")
                .From("t")
                .Take(10);

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT c FROM t LIMIT 10"),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        public void SubQueryShouldBeEnclosedInParen()
        {
            var sub = new SqlQuery().SubQuery()
                .Select("TestColumn")
                .From("TestTable");

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "(SELECT TestColumn FROM TestTable)"),
                TestSqlHelper.Normalize(
                    sub.ToString()));
        }