public void NullHandling() { /// Nulls should be stripped var testQ = new SqlQueryDef("someField=@val", new object[] { null }); Assert.AreEqual("someField=@val", testQ.GetQuery()); Assert.AreEqual(1,testQ.Parameters.Count()); testQ = new SqlQueryDef("someField=@p1 and someField2=@p2",new object[] { null,DBNull.Value}); Assert.AreEqual("someField=@p1 and someField2=@p2", testQ.GetQuery()); Assert.AreEqual(testQ.Parameters.Count(), 2); Assert.AreEqual(DBNull.Value, testQ.Parameters.ElementAt(0).Value); Assert.AreEqual(DBNull.Value, testQ.Parameters.ElementAt(1).Value); }
public void Basic() { var testQ = new SqlQueryDef("someField=@val", new object[] {123}); Assert.AreEqual("someField=@val", testQ.GetQuery()); Assert.AreEqual("@val", testQ.Parameters.First().ParameterName, "Parm name was correct when created for a simple field compariosn"); Assert.AreEqual(123, testQ.Parameters.First().Value, "Parm value correct"); testQ = new SqlQueryDef("someField=@valParm", new object[] { "abc"}); Assert.AreEqual("someField=@valParm",testQ.GetQuery() ); Assert.AreEqual("@valParm", testQ.Parameters.First().ParameterName, "Parm name was correct when created for a simple field compariosn"); Assert.AreEqual( "abc",testQ.Parameters.First().Value, "Parm value correct"); string query = "someField=@val1 and someOtherField=@val2"; testQ = new SqlQueryDef(query, new object[] {"@val2","abc","@val1",678}); Assert.AreEqual(testQ.GetQuery(), query); Assert.AreEqual(testQ.Parameters.ElementAt(1).ParameterName, "@val1", "Parm name was correct when created for a simple field compariosn"); Assert.AreEqual(testQ.Parameters.ElementAt(1).Value, 678, "Parm value correct"); }
public virtual int Count(IDbConnection connection, ISqlQuery query, IDbTransaction transaction = null, CommandBehavior commandBehavior = CommandBehavior.Default) { int count; if (query is ISqlQueryMaker) { var comp = (ISqlQueryMaker)query; count= Count(connection,comp.TableName,comp.Where.ToString(),comp.Parameters,transaction,commandBehavior); } else { ValidateQueryType(query.GetQuery(), "select"); var countQuery = new SqlQueryDef("SELECT COUNT(*) FROM (" + query + ") q", query.Parameters); count= RunQueryScalar(connection, countQuery,transaction, commandBehavior); } count -= query.FirstRow; if (query.TotalRows > 0) { count = Math.Min(count, query.TotalRows); } return count; }
protected override int InsertAndReturnNewID(IDbConnection conn, ISqlQuery query, IDbTransaction transaction = null, CommandBehavior commandBehavior = CommandBehavior.Default) { int result = 0; BeforeRunQuery(query); var newQuery= new SqlQueryDef(query.GetQuery() + "; SET @___ID=SCOPE_IDENTITY();", query.Parameters); using (IDbCommand cmd = GetCommand(conn, newQuery, transaction)) { IDataParameter ID = cmd.CreateParameter(); ID.ParameterName = "@___ID"; ID.DbType = System.Data.DbType.Int32; ID.Direction = ParameterDirection.Output; ID.Value = -1; cmd.Parameters.Add(ID); ExecuteSqlFinal(new Action(() => { cmd.ExecuteScalar(); })); result = Convert.ToInt32(ID.Value.ToString()); cmd.Parameters.Clear(); cmd.Dispose(); } if (commandBehavior == CommandBehavior.CloseConnection) { conn.Close(); } OnQueryComplete(); return result; }
public virtual IDataReader RunQuery(IDbConnection connection, ISqlQuery query, IDbTransaction transaction = null, CommandBehavior commandBehavior = CommandBehavior.Default) { IDataReader dataReader = null; string sql; if (query.FirstRow > 0 || query.TotalRows > 0) { sql = GetQueryForRows(query); } else { sql = query.GetQuery(); } var newQuery = new SqlQueryDef(sql, query.Parameters); BeforeRunQuery(query); using (IDbCommand cmd = GetCommand(connection, newQuery, transaction)) { ExecuteSqlFinal(new Action(() => { dataReader = cmd.ExecuteReader(commandBehavior); })); cmd.Parameters.Clear(); } OnQueryComplete(); return dataReader; }
protected virtual int Count(IDbConnection connection, string tableName, string where, IEnumerable<IDataParameter> parameters, IDbTransaction transaction, CommandBehavior commandBehavior = CommandBehavior.Default) { var countQuery = new SqlQueryDef(String.Format("SELECT COUNT(*) FROM {0}{1}", tableName, (!String.IsNullOrEmpty(where) ? " WHERE " + where : "")), parameters); return RunQueryScalar(connection, countQuery, transaction, commandBehavior); }
public ISqlQuery Clone() { SqlQueryDef query = new SqlQueryDef(QuerySql, Parameters); return query; }
public ISqlQuery Clone() { SqlQueryDef query = new SqlQueryDef(QuerySql, Parameters); return(query); }