C# SimpleStatement.Bind方法代码示例

        public void SimpleStatementNamedValuesNotSpecifiedTest()
        {
            var insertQuery = String.Format("INSERT INTO {0} (float_sample, text_sample, bigint_sample, id) VALUES (:MY_float, :my_TexT, :my_BIGint, :id)", AllTypesTableName);
            var statement = new SimpleStatement(insertQuery);

            Assert.Throws<InvalidQueryException>(() => Session.Execute(
                statement.Bind(
                    new {id = Guid.NewGuid(), my_bigint = 1L })));
        }

        public void SimpleStatementNamedValuesCaseInsensitivityTest()
        {
            var insertQuery = String.Format("INSERT INTO {0} (id, \"text_sample\", int_sample) VALUES (:my_ID, :my_TEXT, :MY_INT)", AllTypesTableName);
            var statement = new SimpleStatement(insertQuery);

            var id = Guid.NewGuid();
            Session.Execute(
                statement.Bind(
                    new { my_INt = 1, my_TEXT = "WAT1", my_id = id}));

            var row = Session.Execute(String.Format("SELECT * FROM {0} WHERE id = {1:D}", AllTypesTableName, id)).First();
            Assert.AreEqual(1, row.GetValue<int>("int_sample"));
            Assert.AreEqual("WAT1", row.GetValue<string>("text_sample"));
        }

        public void CollectionParamsTests()
        {
            var id = Guid.NewGuid();
            var map = new SortedDictionary<string, string> { { "fruit", "apple" }, { "band", "Beatles" } };
            var list = new List<string> { "one", "two" };
            var set = new List<string> { "set_1one", "set_2two" };

            var insertStatement = new SimpleStatement(String.Format("INSERT INTO {0} (id, map_sample, list_sample, set_sample) VALUES (?, ?, ?, ?)", AllTypesTableName));
            Session.Execute(insertStatement.Bind(id, map, list, set));
            var row = Session.Execute(new SimpleStatement(String.Format("SELECT * FROM {0} WHERE id = ?", AllTypesTableName)).Bind(id)).First();
            CollectionAssert.AreEquivalent(map, row.GetValue<IDictionary<string, string>>("map_sample"));
            CollectionAssert.AreEquivalent(list, row.GetValue<List<string>>("list_sample"));
            CollectionAssert.AreEquivalent(set, row.GetValue<List<string>>("set_sample"));
        }

        public void EncodeDecodeTupleAsNestedTest()
        {
            var achievements = new List<Tuple<string, int>>
            {
                new Tuple<string, int>("What", 1),
                new Tuple<string, int>(null, 100),
                new Tuple<string, int>(@"¯\_(ツ)_/¯", 150)
            };
            var insert = new SimpleStatement("INSERT INTO users_tuples (id, achievements) values (?, ?)");
            Session.Execute(insert.Bind(31, achievements));
            var row = Session.Execute("SELECT * FROM users_tuples WHERE id = 31").First();

            Assert.AreEqual(achievements, row.GetValue<List<Tuple<string, int>>>("achievements"));
        }

        public void SimpleStatementNamedValuesTest()
        {
            var insertQuery = String.Format("INSERT INTO {0} (text_sample, int_sample, bigint_sample, id) VALUES (:my_text, :my_int, :my_bigint, :my_id)", AllTypesTableName);
            var statement = new SimpleStatement(insertQuery);

            var id = Guid.NewGuid();
            Session.Execute(
                statement.Bind(
                    new { my_int = 100, my_bigint = -500L, my_id = id, my_text = "named params ftw again!" }));

            var row = Session.Execute(String.Format("SELECT int_sample, bigint_sample, text_sample FROM {0} WHERE id = {1:D}", AllTypesTableName, id)).First();

            Assert.AreEqual(100, row.GetValue<int>("int_sample"));
            Assert.AreEqual(-500L, row.GetValue<long>("bigint_sample"));
            Assert.AreEqual("named params ftw again!", row.GetValue<string>("text_sample"));
        }

        public void BatchSimpleStatementSingleBinded()
        {
            var tableName = "table" + Guid.NewGuid().ToString("N").ToLower();

            CreateTable(tableName);

            var batch = new BatchStatement();

            var simpleStatement = new SimpleStatement(String.Format("INSERT INTO {0} (id, label, number) VALUES (?, ?, ?)", tableName));
            batch.Add(simpleStatement.Bind(100, "label 100", 10000));
            Session.Execute(batch);

            //Verify Results
            var rs = Session.Execute("SELECT * FROM " + tableName);
            var row = rs.First();
            Assert.True(row != null, "There should be a row stored.");
            Assert.True(row.SequenceEqual(new object[] { 100, "label 100", 10000}), "Stored values dont match");
        }

        public void MappingEncodingNestedTest()
        {
            foreach (var protocolVersion in UdtProtocolVersionSupported)
            {
                //Use all possible protocol versions
                Cluster.MaxProtocolVersion = protocolVersion;
                //Use a local cluster
                var localCluster = Cluster.Builder().AddContactPoint(IpPrefix + "1").Build();
                var localSession = localCluster.Connect("tester");
                localSession.UserDefinedTypes.Define(
                    UdtMap.For<Phone>(),
                    UdtMap.For<Contact>()
                          .Map(c => c.FirstName, "first_name")
                          .Map(c => c.LastName, "last_name")
                          .Map(c => c.Birth, "birth_date")
                    );

                var insert = new SimpleStatement("INSERT INTO users_contacts (id, contacts) values (?, ?)");

                //All of the fields null
                var id = 301;
                var contacts = new List<Contact>
                {
                    new Contact
                    {
                        FirstName = "Vincent",
                        LastName = "Vega",
                        Phones = new List<Phone>
                        {
                            new Phone {Alias = "Wat", Number = "0000000000121220000"},
                            new Phone {Alias = "Office", Number = "123"}
                        }
                    }
                };
                localSession.Execute(insert.Bind(id, contacts));
                var rs = localSession.Execute(new SimpleStatement("SELECT * FROM users_contacts WHERE id = ?").Bind(id));
                Assert.AreEqual(contacts, rs.First().GetValue<List<Contact>>("contacts"));
            }
        }

        public void NoMappingDefinedTest()
        {
            const string cqlType = "CREATE TYPE temp_udt (text_sample text, date_sample timestamp)";
            const string cqlTable = "CREATE TABLE temp_table (id int PRIMARY KEY, sample_udt temp_udt, sample_udt_list list<temp_udt>)";
            const string cqlInsert = "INSERT INTO temp_table (id, sample_udt, sample_udt_list) VALUES (1, {text_sample: 'one', date_sample: 1}, [{text_sample: 'first'}])";

            var localCluster = Cluster.Builder().AddContactPoint(IpPrefix + "1").Build();
            var localSession = localCluster.Connect("tester");
            localSession.Execute(cqlType);
            localSession.Execute(cqlTable);
            localSession.Execute(cqlInsert);

            var row = localSession.Execute("SELECT * from temp_table").First();

            Assert.IsNotNull(row.GetValue<object>("sample_udt"));
            Assert.IsInstanceOf<byte[]>(row.GetValue<object>("sample_udt"));

            Assert.IsNotNull(row.GetValue<object>("sample_udt_list"));
            Assert.IsInstanceOf<List<byte[]>>(row.GetValue<object>("sample_udt_list"));

            row = localSession.Execute("SELECT id, sample_udt.text_sample from temp_table").First();
            Assert.AreEqual("one", row.GetValue<string>("sample_udt.text_sample"));

            //Trying to bind to an unmapped type should throw
            var statement = new SimpleStatement("INSERT INTO temp_table (id, sample_udt) VALUES (?, ?)");
            Assert.Throws<InvalidTypeException>(() => localSession.Execute(statement.Bind(2, new DummyClass())));

            localCluster.Dispose();
        }

        public void MappingEncodingSingleTest()
        {
            foreach (var protocolVersion in UdtProtocolVersionSupported)
            {
                //Use all possible protocol versions
                Cluster.MaxProtocolVersion = protocolVersion;
                //Use a local cluster
                var localCluster = Cluster.Builder().AddContactPoint(IpPrefix + "1").Build();
                var localSession = localCluster.Connect("tester");
                localSession.UserDefinedTypes.Define(
                    UdtMap.For<Phone>("phone")
                        .Map(v => v.Alias, "alias")
                        .Map(v => v.CountryCode, "country_code")
                        .Map(v => v.Number, "number")
                );

                var insert = new SimpleStatement("INSERT INTO users (id, main_phone) values (?, ?)");

                //All of the fields null
                var id = 201;
                var phone = new Phone();
                localSession.Execute(insert.Bind(id, phone));
                var rs = localSession.Execute(new SimpleStatement("SELECT * FROM users WHERE id = ?").Bind(id));
                Assert.AreEqual(phone, rs.First().GetValue<Phone>("main_phone"));

                //Some fields null and others with value
                id = 202;
                phone = new Phone() {Alias = "Home phone"};
                localSession.Execute(insert.Bind(id, phone));
                rs = localSession.Execute(new SimpleStatement("SELECT * FROM users WHERE id = ?").Bind(id));
                Assert.AreEqual(phone, rs.First().GetValue<Phone>("main_phone"));

                //All fields filled in
                id = 203;
                phone = new Phone() { Alias = "Mobile phone", CountryCode = 54, Number = "1234567"};
                localSession.Execute(insert.Bind(id, phone));
                rs = localSession.Execute(new SimpleStatement("SELECT * FROM users WHERE id = ?").Bind(id));
                Assert.AreEqual(phone, rs.First().GetValue<Phone>("main_phone"));
            }
        }

 public void QueryBinding()
 {
     string tableName = CreateSimpleTableAndInsert(0);
     var sst = new SimpleStatement(string.Format("INSERT INTO {0}(id, label) VALUES(?, ?)", tableName));
     Session.Execute(sst.Bind(new object[] { Guid.NewGuid(), "label" }));
 }

 public void SimpleStatementSetTimestamp()
 {
     var timestamp = new DateTimeOffset(1999, 12, 31, 1, 2, 3, TimeSpan.Zero);
     var id = Guid.NewGuid();
     var insertStatement = new SimpleStatement(String.Format("INSERT INTO {0} (id, text_sample) VALUES (?, ?)", AllTypesTableName));
     Session.Execute(insertStatement.Bind(id, "sample text").SetTimestamp(timestamp));
     var row = Session.Execute(new SimpleStatement(String.Format("SELECT id, text_sample, writetime(text_sample) FROM {0} WHERE id = ?", AllTypesTableName)).Bind(id)).First();
     Assert.NotNull(row.GetValue<string>("text_sample"));
     Assert.AreEqual(TypeCodec.ToUnixTime(timestamp).Ticks / 10, row.GetValue<object>("writetime(text_sample)"));
 }

        private void ParameterizedStatementTimeStampTest()
        {
            RowSet rs = null;
            var expectedValues = new List<object[]>(1);
            var tableName = "table" + Guid.NewGuid().ToString("N").ToLower();
            var valuesToTest = new List<object[]> { new object[] { Guid.NewGuid(), new DateTimeOffset(2011, 2, 3, 16, 5, 0, new TimeSpan(0000)) },
                                                    {new object[] {Guid.NewGuid(), (long)0}}};

            foreach (var bindValues in valuesToTest)
            {
                expectedValues.Add(bindValues);

                CreateTable(tableName, "timestamp");

                SimpleStatement statement = new SimpleStatement(String.Format("INSERT INTO {0} (id, val) VALUES (?, ?)", tableName));
                statement.Bind(bindValues);

                Session.Execute(statement);

                // Verify results
                rs = Session.Execute("SELECT * FROM " + tableName);

                VerifyData(rs, expectedValues);

                DropTable(tableName);

                expectedValues.Clear();
            }
        }

        private void ParameterizedStatementTest(Type type, bool testAsync = false)
        {
            var tableName = "table" + Guid.NewGuid().ToString("N").ToLower();
            var cassandraDataTypeName = QueryTools.convertTypeNameToCassandraEquivalent(type);
            var expectedValues = new List<object[]>(1);
            var val = Randomm.RandomVal(type);
            var bindValues = new object[] { Guid.NewGuid(), val };
            expectedValues.Add(bindValues);

            CreateTable(tableName, cassandraDataTypeName);

            SimpleStatement statement = new SimpleStatement(String.Format("INSERT INTO {0} (id, val) VALUES (?, ?)", tableName));
            statement.Bind(bindValues);

            if (testAsync)
            {
                Session.ExecuteAsync(statement).Wait(500);
            }
            else
            {
                Session.Execute(statement);
            }

            // Verify results
            RowSet rs = Session.Execute("SELECT * FROM " + tableName);
            VerifyData(rs, expectedValues);
        }

        public void Batch_SimpleStatement_Bound()
        {
            var batch = new BatchStatement();

            var simpleStatement = new SimpleStatement(String.Format("INSERT INTO {0} (id, label, number) VALUES (?, ?, ?)", _tableName));
            #pragma warning disable 618
            batch.Add(simpleStatement.Bind(100, "label 100", 10000));
            #pragma warning restore 618
            Session.Execute(batch);

            //Verify Results
            var rs = Session.Execute(String.Format("SELECT * FROM {0} WHERE id IN ({1})", _tableName, 100));
            var row = rs.First();
            CollectionAssert.AreEqual(row, new object[] {100, "label 100", 10000});
        }