public void WhereKeys()
        {
            var dict = new Dictionary<int, string>() { { 1, "one" }, { 2, "two" }, { 3, "three" } };

            Assert.AreEqual("([1,one],[2,two])", dict.WhereKeys(k => k < 3).Print());
        }
Example #2
0
        /// <summary>
        /// Does an insert and returns the keys of the row just inserted
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dataFields"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public Dictionary<string, object> Insert(string tableName, Dictionary<string, object> dataFields, ITransaction transaction = null)
        {
            //Retrieve the AutoNumbered key name if there is one
            var autoNumberKeyName = Analyzer.GetAutoNumberKey(tableName);

            //Retrieve all of the primary keys to be sure they are all set, except we don't want the autonumbered key since the DB will set it
            var requiredKeys = Analyzer.GetPrimaryKeys(tableName).Except(autoNumberKeyName);

            //Check that all of the required keys are actually set
            var invalid = requiredKeys.Where(key => dataFields[key] == null);
            if (invalid.Any())
                throw new KeyNotSetException(tableName, invalid);

            // Keep in mind that GetFields might return an empty list, which means that it doesn't know
            var dbFields = Analyzer.GetFields(tableName).Or(dataFields.Keys);
            // Take out the autonumber keys, and take out any supplied data fields
            // that aren't actually fields in the DB
            var fieldNames = dataFields.Keys.Except(autoNumberKeyName)
                .Intersect(dbFields);

            var sql = "INSERT INTO " + tableName + " (" + fieldNames.Join(", ") + ") VALUES (" + fieldNames.Select(x => "@" + x).Join(", ") + ")\n";
            sql += "SELECT SCOPE_IDENTITY()";

            var autoKey =  sqlInsert(sql, transaction, dataFields);

            if (autoNumberKeyName != null && autoKey == null)
                throw new ThisSadlyHappenedException("The SQL ran beautifully, but you were expecting an autogenerated number and you did not get it");

            if (autoNumberKeyName != null)
            {
                var autoKeyDict = new Dictionary<string, object>() { { autoNumberKeyName, autoKey } };
                dataFields = dataFields.Union(autoKeyDict);
            }

            return dataFields.WhereKeys(key => Analyzer.GetPrimaryKeys(tableName).Contains(key));
        }
Example #3
0
        static SqlTypeConversion()
        {
            _toSqlMappings.Add(typeof(bool?), new SqlColumnType(SqlDbType.Bit, true));
            _toSqlMappings.Add(typeof(bool), new SqlColumnType(SqlDbType.Bit));
            _toSqlMappings.Add(typeof(char?), new SqlColumnType(SqlDbType.Char, true, length: 1));
            _toSqlMappings.Add(typeof(char), new SqlColumnType(SqlDbType.Char, false, length: 1));
            _toSqlMappings.Add(typeof(int?), new SqlColumnType(SqlDbType.Int, true));
            _toSqlMappings.Add(typeof(int), new SqlColumnType(SqlDbType.Int));
            _toSqlMappings.Add(typeof(Nullable<Int64>), new SqlColumnType(SqlDbType.BigInt, true));
            _toSqlMappings.Add(typeof(Int64), new SqlColumnType(SqlDbType.BigInt));
            _toSqlMappings.Add(typeof(float?), new SqlColumnType(SqlDbType.Real, true));
            _toSqlMappings.Add(typeof(float), new SqlColumnType(SqlDbType.Real));
            _toSqlMappings.Add(typeof(double?), new SqlColumnType(SqlDbType.Float, true));
            _toSqlMappings.Add(typeof(double), new SqlColumnType(SqlDbType.Float));
            _toSqlMappings.Add(typeof(decimal?), new SqlColumnType(SqlDbType.Decimal, true, precision: 36, scale: 12));
            _toSqlMappings.Add(typeof(decimal), new SqlColumnType(SqlDbType.Decimal, precision: 36, scale: 12));
            _toSqlMappings.Add(typeof(DateTime?), new SqlColumnType(SqlDbType.DateTime, true));
            _toSqlMappings.Add(typeof(DateTime), new SqlColumnType(SqlDbType.DateTime));
            _toSqlMappings.Add(typeof(string), new SqlColumnType(SqlDbType.NVarChar, true, length: 100));
            _toSqlMappings.Add(typeof(XmlDocument), new SqlColumnType(SqlDbType.Xml, true));
            _toSqlMappings.Add(typeof(Guid?), new SqlColumnType(SqlDbType.UniqueIdentifier, true));
            _toSqlMappings.Add(typeof(Guid), new SqlColumnType(SqlDbType.UniqueIdentifier));
            _toSqlMappings.Add(typeof(Byte), new SqlColumnType(SqlDbType.TinyInt));
            // Note - this isn't ideal - SByte is signed 8-bit, while SmallInt is signed 16-bit
            // I don't think there's an exact match
            _toSqlMappings.Add(typeof(SByte), new SqlColumnType(SqlDbType.SmallInt));

            // _toCMappings are just the inverses of the _toSqlMappings, plus a few overloads
            _toCMappings = _toSqlMappings.Invert();
            _toCMappings.Add(new SqlColumnType(SqlDbType.NVarChar), typeof(string));
            _toCMappings.Add(new SqlColumnType(SqlDbType.VarChar, true), typeof(string));
            _toCMappings.Add(new SqlColumnType(SqlDbType.VarChar), typeof(string));
            var charTypes = _toCMappings.WhereKeys(k => k.SqlType == SqlDbType.Char).Keys;
            foreach (var charType in charTypes)
                _toCMappings.Remove(charType);
            _toCMappings.Add(new SqlColumnType(SqlDbType.Char), typeof(string));
            _toCMappings.Add(new SqlColumnType(SqlDbType.Char, true), typeof(string));
            _toCMappings.Add(new SqlColumnType(SqlDbType.NChar), typeof(string));
            _toCMappings.Add(new SqlColumnType(SqlDbType.Xml), typeof(XmlDocument));

            // _toDbMappings contain mappings of SqlDbTypes to DbTypes
            // DbTypes are used for database-agnostic mapping, while SqlDbTypes are SQL-server specific
            _toDbMappings.Add(SqlDbType.BigInt, DbType.Int64);
            _toDbMappings.Add(SqlDbType.Binary, DbType.Binary);
            _toDbMappings.Add(SqlDbType.Bit, DbType.Boolean);
            _toDbMappings.Add(SqlDbType.Char, DbType.AnsiString);
            _toDbMappings.Add(SqlDbType.Date, DbType.Date);
            _toDbMappings.Add(SqlDbType.DateTime, DbType.DateTime);
            _toDbMappings.Add(SqlDbType.DateTime2, DbType.DateTime2);
            _toDbMappings.Add(SqlDbType.DateTimeOffset, DbType.DateTimeOffset);
            _toDbMappings.Add(SqlDbType.Decimal, DbType.Decimal);
            _toDbMappings.Add(SqlDbType.Float, DbType.Single);
            _toDbMappings.Add(SqlDbType.Image, DbType.Binary);
            _toDbMappings.Add(SqlDbType.Int, DbType.Int32);
            _toDbMappings.Add(SqlDbType.Money, DbType.Currency);
            _toDbMappings.Add(SqlDbType.NChar, DbType.String);

            _toDbMappings.Add(SqlDbType.NText, DbType.String);
            _toDbMappings.Add(SqlDbType.NVarChar, DbType.String);
            _toDbMappings.Add(SqlDbType.Real, DbType.Double);
            _toDbMappings.Add(SqlDbType.SmallDateTime, DbType.DateTime);
            _toDbMappings.Add(SqlDbType.SmallInt, DbType.Int16);
            _toDbMappings.Add(SqlDbType.SmallMoney, DbType.Currency);
            //SqlDbType.Structured
            _toDbMappings.Add(SqlDbType.Text, DbType.String);
            _toDbMappings.Add(SqlDbType.Time, DbType.Time);
            // SqlDbType.Timestamp
            _toDbMappings.Add(SqlDbType.TinyInt, DbType.Byte);
            //SqlDbType.Udt
            _toDbMappings.Add(SqlDbType.UniqueIdentifier, DbType.Guid);
            _toDbMappings.Add(SqlDbType.VarBinary, DbType.Binary);
            _toDbMappings.Add(SqlDbType.VarChar, DbType.String);
            //SqlDbType.Variant
            _toDbMappings.Add(SqlDbType.Xml, DbType.Xml);
        }