コード例 #1
0
ファイル: Program.cs プロジェクト: ctigeek/CSharpQueryHelper
        public static SQLQuery GetInsertQuery(this ChildObject childObject, Action <SQLQuery> preQuery)
        {
            string sql   = "insert into Child (parent_pk, Name, SomeProp) values (@parentpk,@name,@someprop);";
            var    query = new SQLQuery(sql, SQLQueryType.NonQuery);

            query.Parameters.Add("name", childObject.Name);
            query.Parameters.Add("someprop", childObject.SomeProp);
            query.PreQueryProcess = preQuery;
            return(query);
        }
コード例 #2
0
ファイル: Test.cs プロジェクト: ctigeek/CSharpQueryHelper
        public void NonQueryTestNoParameters()
        {
            var query = new SQLQuery(sqlString, SQLQueryType.NonQuery);

            queryHelper.RunQuery(query);
            VerifyLogging(sqlString);
            Assert.AreEqual(sqlString, MockDatabaseFactory.DbCommand.Object.CommandText);
            MockDatabaseFactory.DbConnection.VerifySet(dbc => dbc.ConnectionString = connectionString, Times.Exactly(1));
            MockDatabaseFactory.Parameters.Verify(p => p.Add(It.IsAny <DbParameter>()), Times.Exactly(0));
            MockDatabaseFactory.DbCommand.Verify(dbc => dbc.ExecuteNonQueryAsync(It.IsAny <System.Threading.CancellationToken>()), Times.Exactly(1));
            Assert.AreEqual(345, query.RowCount);
        }
コード例 #3
0
        private DbCommand CreateCommand(SQLQuery query, DbConnection connection, DbTransaction transaction = null)
        {
            var command = DbFactory.CreateCommand();

            command.Connection = connection;
            AddParameters(command, query);
            command.CommandText = query.ModifiedSQL;
            if (transaction != null)
            {
                command.Transaction = transaction;
            }
            return(command);
        }
コード例 #4
0
ファイル: Program.cs プロジェクト: ctigeek/CSharpQueryHelper
        public static List <ParentObject> LoadParents()
        {
            var parentDict = new Dictionary <int, ParentObject>();
            var sql        = @"select Parent.pk as ParentPk,
                                Parent.Name as ParentName,
                                Parent.SomeProp as ParentSomeProp,
                                Child.pk as ChildPk,
                                Child.Name as ChildName,
                                Child.SomeProp as ChildSomeProp
                            from Parent inner join Child on (Parent.pk = Child.parent_pk)";
            var query      = new SQLQuery(sql, SQLQueryType.DataReader);

            query.ProcessRow = dr =>
            {
                //add parent
                int parentpk = (int)dr["ParentPk"];
                if (!parentDict.ContainsKey(parentpk))
                {
                    var newParentObject = new ParentObject()
                    {
                        ChildObjects = new List <ChildObject>(),
                        Name         = (string)dr["ParentName"],
                        SomeProp     = (int)dr["ParentSomeProp"]
                    };
                    parentDict.Add(parentpk, newParentObject);
                }
                var parentObject = parentDict[parentpk];

                //add child
                var newChildObject = new ChildObject()
                {
                    Name     = (string)dr["ChildName"],
                    SomeProp = (int)dr["ChildSomeProp"]
                };
                parentObject.ChildObjects.Add(newChildObject);

                return(true);
            };

            var qh = new QueryHelper("sandbox");

            qh.RunQuery(query);

            return(parentDict.Values.ToList());
        }
コード例 #5
0
ファイル: Test.cs プロジェクト: ctigeek/CSharpQueryHelper
        public void NonQueryTestWithParametersBuiltDynamically()
        {
            var query = new SQLQuery(sqlString, SQLQueryType.NonQuery);

            query.PreQueryProcess = new Action <SQLQuery>(q =>
            {
                q.Parameters.Add("param1", "value1");
                q.Parameters.Add("param2", "value2");
                q.Parameters.Add("param3", 333);
            });

            queryHelper.RunQuery(query);
            VerifyLogging(sqlString);
            Assert.AreEqual(sqlString, MockDatabaseFactory.DbCommand.Object.CommandText);
            MockDatabaseFactory.DbConnection.VerifySet(dbc => dbc.ConnectionString = connectionString, Times.Exactly(1));
            MockDatabaseFactory.Parameters.Verify(p => p.Add(It.IsAny <DbParameter>()), Times.Exactly(3));
            MockDatabaseFactory.DbCommand.Verify(dbc => dbc.ExecuteNonQueryAsync(It.IsAny <System.Threading.CancellationToken>()), Times.Exactly(1));
            Assert.AreEqual(345, query.RowCount);
        }
コード例 #6
0
ファイル: Test.cs プロジェクト: ctigeek/CSharpQueryHelper
        public void NonQueryTransactionNoParametersRollbackWhenExceptionAsync()
        {
            try
            {
                int returnValue = 100;
                MockDatabaseFactory.DbCommand.Setup(dbc => dbc.ExecuteNonQueryAsync(It.IsAny <System.Threading.CancellationToken>()))
                .Returns(() =>
                {
                    returnValue++;
                    if (returnValue == 105)
                    {
                        throw new ApplicationException("blah blah");
                    }
                    return(Task.FromResult <int>(returnValue));
                });

                var queries = new Dictionary <int, SQLQuery>();
                for (int counter = 0; counter < 10; counter++)
                {
                    var query = new SQLQuery("insert into sometable values (" + counter + ");", SQLQueryType.NonQuery)
                    {
                        GroupNumber = counter
                    };
                    queries.Add(counter, query);
                }
                var task = queryHelper.RunQueryAsync(queries.Values, true);
                task.Wait();
            }
            catch (System.AggregateException ex)
            {
                throw ex.InnerExceptions[0];
            }
            finally
            {
                MockDatabaseFactory.DbCommand.VerifySet(dbc => dbc.Transaction = MockDatabaseFactory.DbTransaction.Object);
                MockDatabaseFactory.DbCommand.Verify(dbc => dbc.ExecuteNonQueryAsync(It.IsAny <System.Threading.CancellationToken>()), Times.Exactly(5));
                MockDatabaseFactory.DbConnection.VerifySet(dbc => dbc.ConnectionString = connectionString, Times.Exactly(1));
                MockDatabaseFactory.Parameters.Verify(p => p.Add(It.IsAny <DbParameter>()), Times.Exactly(0));
                MockDatabaseFactory.DbTransaction.Verify(dbt => dbt.Commit(), Times.Exactly(0));
                MockDatabaseFactory.DbTransaction.Verify(dbt => dbt.Rollback(), Times.Exactly(1));
            }
        }
コード例 #7
0
ファイル: Test.cs プロジェクト: ctigeek/CSharpQueryHelper
        public void ReadSingleRowNoParameters()
        {
            var dataContainer = new TestDataContainer();
            var dataReader    = MockDatabaseFactory.CreateDbDataReader(dataContainer);
            var query         = new SQLQuery(sqlString, SQLQueryType.DataReader)
            {
                ProcessRow = dataContainer.ProcessRow
            };

            queryHelper.RunQuery(query);

            dataContainer.AssertData();
            VerifyLogging(sqlString);
            MockDatabaseFactory.DbConnection.VerifySet(dbc => dbc.ConnectionString = connectionString, Times.Exactly(1));
            MockDatabaseFactory.Parameters.Verify(p => p.Add(It.IsAny <DbParameter>()), Times.Exactly(0));
            MockDatabaseFactory.DbCommand.Protected()
            .Verify <Task <DbDataReader> >("ExecuteDbDataReaderAsync", Times.Exactly(1), It.IsAny <CommandBehavior>(), It.IsAny <System.Threading.CancellationToken>());
            Assert.AreEqual(1, query.RowCount);
            dataReader.Verify(dr => dr.Read(), Times.Exactly(2));
        }
コード例 #8
0
ファイル: Test.cs プロジェクト: ctigeek/CSharpQueryHelper
        public void NonQueryTransactionWithParametersAsync()
        {
            int returnValue = 100;

            MockDatabaseFactory.DbCommand.Setup(dbc => dbc.ExecuteNonQueryAsync(It.IsAny <System.Threading.CancellationToken>()))
            .Returns(() =>
            {
                returnValue++;
                return(Task.FromResult <int>(returnValue));
            });

            var queries = new Dictionary <int, SQLQuery>();

            for (int counter = 0; counter < 10; counter++)
            {
                var query = new SQLQuery("insert into sometable values (" + counter + ");", SQLQueryType.NonQuery)
                {
                    GroupNumber = counter
                };
                query.Parameters.Add("param1", "value1");
                query.Parameters.Add("param2", "value2");
                query.Parameters.Add("param3", 333);
                queries.Add(counter, query);
            }
            var task = queryHelper.RunQueryAsync(queries.Values, true);

            task.Wait();

            for (int counter = 0; counter < 10; counter++)
            {
                Assert.AreEqual(101 + counter, queries[counter].RowCount);
            }
            MockDatabaseFactory.DbCommand.VerifySet(dbc => dbc.Transaction = MockDatabaseFactory.DbTransaction.Object);
            MockDatabaseFactory.DbCommand.Verify(dbc => dbc.ExecuteNonQueryAsync(It.IsAny <System.Threading.CancellationToken>()), Times.Exactly(10));
            MockDatabaseFactory.DbConnection.VerifySet(dbc => dbc.ConnectionString = connectionString, Times.Exactly(1));
            MockDatabaseFactory.Parameters.Verify(p => p.Add(It.IsAny <DbParameter>()), Times.Exactly(30));
            MockDatabaseFactory.DbTransaction.Verify(dbt => dbt.Commit(), Times.Exactly(1));
            MockDatabaseFactory.DbTransaction.Verify(dbt => dbt.Rollback(), Times.Exactly(0));
        }
コード例 #9
0
        private void DumpSqlAndParamsToLog(SQLQuery query)
        {
            if (LogMessage == null || !DebugLoggingEnabled)
            {
                return;
            }

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.AppendFormat("About to execute \"{0}\" ", query.ModifiedSQL);
            if (query.Parameters.Count > 0)
            {
                sb.Append(" with parameters:\r\n");
                foreach (string key in query.Parameters.Keys)
                {
                    sb.AppendFormat("{0}={1} with type {2}.\r\n", key, query.Parameters[key], query.Parameters[key].GetType());
                }
            }
            else
            {
                sb.Append(" with no parameters.");
            }
            LogDebug(sb.ToString());
        }
コード例 #10
0
 private void AddParameters(DbCommand command, SQLQuery query)
 {
     foreach (string paramName in query.InParameters.Keys)
     {
         if (query.InParameters[paramName] != null && query.InParameters[paramName].Any() && query.ModifiedSQL.Contains("@" + paramName))
         {
             var parameterDictionary = new Dictionary <string, object>();
             foreach (var value in query.InParameters[paramName])
             {
                 parameterDictionary.Add(string.Format("{0}{1}", paramName, parameterDictionary.Count), value);
             }
             //TODO: this needs to be a regex otherwise it could be prone to replacing paramName that starts with the same name eg. @myvar & @myvar2.
             query.ModifiedSQL = query.ModifiedSQL.Replace("@" + paramName, string.Join(",", parameterDictionary.Select(pd => "@" + pd.Key)));
             foreach (var parameter in parameterDictionary.Keys)
             {
                 query.Parameters.Add(parameter, parameterDictionary[parameter]);
             }
         }
     }
     foreach (string paramName in query.Parameters.Keys)
     {
         command.Parameters.Add(CreateParameter(paramName, query.Parameters[paramName]));
     }
 }
コード例 #11
0
 public async Task RunQueryAsync(SQLQuery query)
 {
     await RunQueryAsync(new[] { query }, TransactionOpen);
 }
コード例 #12
0
 public void RunQuery(SQLQuery query)
 {
     RunQuery(new[] { query }, TransactionOpen);
 }