public static void Insert <T>(this IDbConnection connection,
                                      T entityToInsert,
                                      IDbTransaction transaction = null,
                                      int?commandTimeout         = null) where T : class
        {
            var type     = typeof(T);
            var metadata = MetadataFor(type);

            string sql;

            if (!s_insertQueries.TryGetValue(type.TypeHandle, out sql))
            {
                s_insertQueries[type.TypeHandle] = sql = TSQLGenerator.BuildInsert(metadata, withoutKeys: false);
            }


            Log(sql, entityToInsert);


            connection.Execute(
                sql,
                entityToInsert,
                transaction: transaction,
                commandTimeout: commandTimeout);
        }
示例#2
0
        public void BuildCreate_WithSchemaAndMultipleKeys()
        {
            var tableMD = GetTestMetadata();

            tableMD.Properties.Add(new PropertyMetadata()
            {
                IsPK         = true,
                ColumnName   = "a2",
                PropertyName = "aa22"
            });
            tableMD.Schema = "testschema";

            var sql = TSQLGenerator.BuildCreate(tableMD, true);

            Assert.AreEqual(@"IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[testschema].[testtable]') AND type in (N'U'))
BEGIN
CREATE TABLE [testschema].[testtable](
   [a]  NOT NULL,
   [a2]  NOT NULL,
   [b]  NOT NULL,
   [c]  NOT NULL,
CONSTRAINT [PK_testtable] PRIMARY KEY CLUSTERED ( [a] ASC,[a2] ASC )
);
END
", sql);
        }
        public static bool Exists <T>(this IDbConnection connection, T entity, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
        {
            var type     = typeof(T);
            var metadata = MetadataFor(type);

            var keys = metadata.Properties.Where(x => x.IsPK);

            if (!keys.Any())
            {
                throw new Exception("This only support entites with a single key property at the moment.");
            }
            if (keys.Count() > 1)
            {
                throw new Exception("This only support entites with a single key property at the moment.");
            }

            string sql;

            if (!s_existsQueries.TryGetValue(type.TypeHandle, out sql))
            {
                s_existsQueries[type.TypeHandle] = sql = TSQLGenerator.BuildDelete(metadata);
            }

            Log(sql, entity);

            var noOfRowsMatching = connection.Execute(
                sql,
                entity,
                transaction: transaction,
                commandTimeout: commandTimeout);

            return(noOfRowsMatching > 0);
        }
示例#4
0
        /// <summary>
        /// Fills a datatable using a WhereBuilder object.
        /// </summary>
        /// <param name="dt">The datatable to fill.</param>
        /// <param name="where">The WhereBuilder for querying.</param>
        public virtual void Fill(DataTable dt, Where <T> where)
        {
            using (var c = OpenConnection())
            {
                var param = new Dictionary <string, object>();

                var whereSql = where == null ? string.Empty : where.Build(param).ToString();

                var selectClause = TSQLGenerator.BuildSelect(m_metadata) + " " + whereSql;
                var cmd          = c.CreateCommand();
                cmd.CommandText = selectClause;
                var sb = new StringBuilder();
                foreach (var p in param)
                {
                    sb.AppendFormat(" ^-- Parameter: {0} = '{1}'\r\n", p.Key, p.Value.ToString());
                    var dbparam = cmd.CreateParameter();
                    dbparam.ParameterName = p.Key;
                    dbparam.Value         = p.Value;
                    cmd.Parameters.Add(dbparam);
                }
                s_log.Debug(x => x("Filling datatable for type {0} with '{1}'\r\n{2}", typeof(T).Name, selectClause, sb.ToString().TrimEnd()));

                var sda = CreateAdapter(cmd);
                sda.Fill(dt);
            }
        }
示例#5
0
        public void BuildColumnList()
        {
            var tableMD = GetTestMetadata();

            var sql = TSQLGenerator.BuildColumnList(tableMD);

            Assert.AreEqual("[a],[b],[c]", sql);
        }
示例#6
0
        /// <summary>
        /// Performs a query with a set of AND filters.
        /// </summary>
        /// <param name="where">The values to filter for. Each entry will be concatenated with AND.</param>
        /// <returns></returns>
        public IEnumerable <T> GetByWhere(Dictionary <string, object> @where)
        {
            return(m_connectionCtxFactory.InDatabase <IEnumerable <T> >((c, t) =>
            {
                var param = new Dictionary <string, object>();
                var sql = TSQLGenerator.BuildSelect(m_metadata, @where, ref param);

                return c.QueryFor <T>(sql, new DynamicParameters(param), transaction: t);
            }));
        }
示例#7
0
        public void BuildInsert_WithSchema()
        {
            var tableMD = GetTestMetadata();

            tableMD.Schema = "testschema";

            var sql = TSQLGenerator.BuildInsert(tableMD, withoutKeys: false);

            Assert.AreEqual("INSERT INTO [testschema].[testtable] ([a],[b],[c]) VALUES (@aa,@bb,@cc)", sql);
        }
示例#8
0
        public void BuildDelete_WithSchema()
        {
            var tableMD = GetTestMetadata();

            tableMD.Schema = "testschema";

            var sql = TSQLGenerator.BuildDelete(tableMD);

            Assert.AreEqual("DELETE FROM [testschema].[testtable] WHERE [a]=@aa", sql);
        }
示例#9
0
        /// <summary>
        /// Performs a query with a set of AND filters.
        /// </summary>
        /// <param name="where">The values to filter for. Each entry will be concatenated with AND.</param>
        /// <returns></returns>
        public IEnumerable <T> GetByWhere(Dictionary <string, object> @where)
        {
            using (var c = OpenConnection())
            {
                var param = new Dictionary <string, object>();
                var sql   = TSQLGenerator.BuildSelect(m_metadata, @where, ref param);

                return(c.QueryFor <T>(sql, new DynamicParameters(param)));
            }
        }
示例#10
0
        public void BuildParameterList_WithoutKeys()
        {
            var tableMD = GetTestMetadata();

            tableMD.Schema = "testschema";

            var sql = TSQLGenerator.BuildParameterList(tableMD, withoutKeys: true);

            Assert.AreEqual("@bb,@cc", sql);
        }
示例#11
0
        public void BuildUpdate_WithSchema()
        {
            var tableMD = GetTestMetadata();

            tableMD.Schema = "testschema";

            var sql = TSQLGenerator.BuildUpdate(tableMD);

            Assert.AreEqual("UPDATE [testschema].[testtable] SET [b]=@bb,[c]=@cc WHERE [a]=@aa", sql);
        }
示例#12
0
        public void BuildDelete_NoSchema()
        {
            var tableMD = GetTestMetadata();

            Assert.IsNull(tableMD.Schema);

            var sql = TSQLGenerator.BuildDelete(tableMD);

            Assert.AreEqual("DELETE FROM [testtable] WHERE [a]=@aa", sql);
        }
示例#13
0
        public static void CreateTable <T>(this IDbConnection connection, bool onlyIfNotExists, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
        {
            var type = typeof(T);

            var values   = new Dictionary <string, object>();
            var metadata = MetadataFor(type);

            var sql = TSQLGenerator.BuildCreate(metadata, onlyIfNotExists: onlyIfNotExists);

            connection.Execute(sql, transaction: transaction, commandTimeout: commandTimeout);
        }
示例#14
0
        public static bool ExistsTable <T>(this IDbConnection connection, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
        {
            var type = typeof(T);

            var values   = new Dictionary <string, object>();
            var metadata = MetadataFor(type);

            var sql = TSQLGenerator.BuildExistsTable(metadata);

            return(connection.Query(sql, transaction: transaction, commandTimeout: commandTimeout).Any());
        }
示例#15
0
        public static IEnumerable <T> GetAll <T>(this IDbConnection connection, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
        {
            var    type = typeof(T);
            string sql;

            if (!s_selectAllQueries.TryGetValue(type.TypeHandle, out sql))
            {
                var metadata = MetadataFor(type);
                s_selectAllQueries[type.TypeHandle] = sql = TSQLGenerator.BuildSelect(metadata);
            }

            Log(sql, null);

            return(connection.Query <T>(sql, transaction: transaction, commandTimeout: commandTimeout));
        }
示例#16
0
        public void BuildWhereClause()
        {
            var tableMD = GetTestMetadata();

            var values = new Dictionary <string, object>();

            values.Add("aa", 1);
            values.Add("cc", "test");

            var parameters = new Dictionary <string, object>();

            var sql = TSQLGenerator.BuildWhereClause(tableMD, values, ref parameters);

            Assert.AreEqual("[a]=@aa AND [c]=@cc", sql);
            Assert.AreEqual(2, parameters.Count);
        }
示例#17
0
        public void BuildCreate_NoSchema()
        {
            var tableMD = GetTestMetadata();

            Assert.IsNull(tableMD.Schema);

            var sql = TSQLGenerator.BuildCreate(tableMD);

            Assert.AreEqual(@"CREATE TABLE [dbo].[testtable](
   [a]  NOT NULL,
   [b]  NOT NULL,
   [c]  NOT NULL,
CONSTRAINT [PK_testtable] PRIMARY KEY CLUSTERED ( [a] ASC )
);
", sql);
        }
示例#18
0
        public static TKey Insert <T, TKey>(this IDbConnection connection,
                                            T entityToInsert,
                                            IDbTransaction transaction = null,
                                            int?commandTimeout         = null,
                                            bool letDbGenerateKey      = false) where T : class
        {
            var type     = typeof(T);
            var metadata = MetadataFor(type);

            var keys = metadata.Properties.Where(x => x.IsPK);

            if (!keys.Any())
            {
                throw new Exception("This only support entites with a single key property at the moment.");
            }
            if (keys.Count() > 1)
            {
                throw new Exception("This only support entites with a single key property at the moment.");
            }

            string sql;

            if (!s_insertQueries.TryGetValue(type.TypeHandle, out sql))
            {
                s_insertQueries[type.TypeHandle] = sql = TSQLGenerator.BuildInsert(metadata, withoutKeys: letDbGenerateKey);
            }

            Log(sql, entityToInsert);

            connection.Execute(
                sql,
                entityToInsert,
                transaction: transaction,
                commandTimeout: commandTimeout);

            if (letDbGenerateKey)
            {
                //NOTE: would prefer to use IDENT_CURRENT('tablename') or IDENT_SCOPE but these are not available on SQLCE
                var r = connection.Query("select @@IDENTITY id", transaction: transaction,
                                         commandTimeout: commandTimeout);
                return((TKey)r.First().id);
            }
            else
            {
                return((TKey)keys.First().PropertyInfo.GetValue(entityToInsert, null));
            }
        }
示例#19
0
        public void FindUniqueNameInDictionary()
        {
            var dict    = new Dictionary <string, object>();
            var subject = "test";

            var s1 = TSQLGenerator.FindUniqueNameInDictionary(subject, dict);

            Assert.IsTrue(s1.Equals("test"));
            dict.Add(s1, null);
            var s2 = TSQLGenerator.FindUniqueNameInDictionary(subject, dict);

            Assert.IsTrue(s2.Equals("test2"));
            dict.Add(s2, null);
            var s3 = TSQLGenerator.FindUniqueNameInDictionary(subject, dict);

            Assert.IsTrue(s3.Equals("test3"));
        }
        public void BuildCreate_OneIntKey()
        {
            var md = new TableMetadata();

            MetadataHelper.MetadataForClass(typeof(TableWithOneIntKey), ref md);

            var sql = TSQLGenerator.BuildCreate(md, true);

            Assert.AreEqual(@"IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableWithOneIntKey]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TableWithOneIntKey](
   [Key1] [int] IDENTITY(1,1) NOT NULL,
   [Other] [NVARCHAR](50) NULL,
CONSTRAINT [PK_TableWithOneIntKey] PRIMARY KEY CLUSTERED ( [Key1] ASC )
);
END
", sql);
        }
示例#21
0
        public void BuildSelect_NoSchema()
        {
            var tableMD = GetTestMetadata();

            Assert.IsNull(tableMD.Schema);

            var values = new Dictionary <string, object>();

            values.Add("aa", 1);
            values.Add("cc", "test");

            var parameters = new Dictionary <string, object>();

            var sql = TSQLGenerator.BuildSelect(tableMD, values, ref parameters);

            Assert.AreEqual("SELECT [a] AS 'aa',[b] AS 'bb',[c] AS 'cc' FROM [testtable] WHERE [a]=@aa AND [c]=@cc", sql);
            Assert.AreEqual(2, parameters.Count);
        }
示例#22
0
        public void BuildSelectList_WithSchema()
        {
            var tableMD = GetTestMetadata();

            tableMD.Schema = "testschema";

            var values = new Dictionary <string, object>();

            values.Add("aa", new int[] { 1, 2, 3 });
            values.Add("cc", "test");

            var parameters = new Dictionary <string, object>();

            var sql = TSQLGenerator.BuildSelect(tableMD, values, ref parameters);

            Assert.AreEqual("SELECT [a] AS 'aa',[b] AS 'bb',[c] AS 'cc' FROM [testschema].[testtable] WHERE [a] IN @aa AND [c]=@cc", sql);
            Assert.AreEqual(2, parameters.Count);
        }
示例#23
0
        public static bool Delete <T>(this IDbConnection connection, T entityToDelete, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
        {
            var type     = typeof(T);
            var metadata = MetadataFor(type);

            string sql;

            if (!s_deleteQueries.TryGetValue(type.TypeHandle, out sql))
            {
                s_deleteQueries[type.TypeHandle] = sql = TSQLGenerator.BuildDelete(metadata);
            }

            Log(sql, entityToDelete);

            var deleted = connection.Execute(sql, entityToDelete, transaction: transaction, commandTimeout: commandTimeout);

            return(deleted > 0);
        }
示例#24
0
        public void BuildCreate_WithSchema()
        {
            var tableMD = GetTestMetadata();

            tableMD.Schema = "testschema";

            var sql = TSQLGenerator.BuildCreate(tableMD, true);

            Assert.AreEqual(@"IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[testschema].[testtable]') AND type in (N'U'))
BEGIN
CREATE TABLE [testschema].[testtable](
   [a]  NOT NULL,
   [b]  NOT NULL,
   [c]  NOT NULL,
CONSTRAINT [PK_testtable] PRIMARY KEY CLUSTERED ( [a] ASC )
);
END
", sql);
        }
示例#25
0
        public void BuildCreate_WithSchemaAndNoKeys()
        {
            var tableMD = GetTestMetadata();

            tableMD.Properties.Where(x => x.IsPK).ToList().ForEach(x => x.IsPK = false);
            tableMD.Schema = "testschema";

            var sql = TSQLGenerator.BuildCreate(tableMD, true);

            Assert.AreEqual(@"IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[testschema].[testtable]') AND type in (N'U'))
BEGIN
CREATE TABLE [testschema].[testtable](
   [a]  NOT NULL,
   [b]  NOT NULL,
   [c]  NOT NULL,

);
END
", sql);
        }
示例#26
0
        public static T Get <T>(this IDbConnection connection, dynamic id, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
        {
            var type       = typeof(T);
            var parameters = new Dictionary <string, object>();

            var values   = new Dictionary <string, object>();
            var metadata = MetadataFor(type);

            var keys = metadata.Properties.Where(x => x.IsPK);

            if (!keys.Any())
            {
                throw new Exception("This only support entites with a single key property at the moment.");
            }
            if (keys.Count() > 1)
            {
                throw new Exception("This only support entites with a single key property at the moment.");
            }

            values.Add(keys.First().PropertyName, (object)id);

            string sql;

            if (!s_selectQueries.TryGetValue(type.TypeHandle, out sql))
            {
                s_selectQueries[type.TypeHandle] = sql = TSQLGenerator.BuildSelect(metadata, values, ref parameters);
            }
            else
            {
                TSQLGenerator.BuildParameters(metadata, values, ref parameters);
            }

            var dynParms = new DynamicParameters(parameters);

            T obj = null;

            Log(sql, dynParms);
            obj = connection.Query <T>(sql, dynParms, transaction: transaction, commandTimeout: commandTimeout).FirstOrDefault();
            return(obj);
        }