fransbouma / massive Goto Github PK

DescriptorField does not have a setter and even if you set it when you declare a DynamicModel the local variable is never assigned. We need to add a line in the DynamicModel:
_descriptorField = descriptorField;
After fixing this, when callling KeyValues() the return (IDictionary<string, object>)Query(sql); line will throw an invalid cast exception.

Thank you for your time!

Hello everyone I'm very new to ORM and C#. I would like to know how can I use massive in MySQL?

@@IDENTITY in line 382 should be replaced with SCOPE_IDENTITY(), as @@IDENTITY is an unreliable way of returning an IDENTITY key from newly inserted row. @@IDENTITY can cause subtle bugs, especially in a case where triggers are used. http://blogs.msdn.com/b/spike/archive/2009/10/20/my-take-on-identity-vs-scope-identity.aspx

I have an Articles table and a Comments table and I want to get a one to many relationship between them. I also want that when I pull a single article all the comments are automatically retrieved.

Hi Rob,

I just want to ask if you are using Resharper? The massive cs file has so many issues found by Resharper such wrong naming convention, redundant arguments, redundant qualifiers, redundant .ToString() conversion, and many more. I do suggest use Resharper for a better, nicer, and cleaner massive cs file.

Line 102 of the current commit is hard coded to System.Data.SqlClient. It should be reverted to the fix for Issue #4

var _providerName = "System.Data.SqlClient";

#4

var _providerName = ConfigurationManager.ConnectionStrings[connectionStringName].ProviderName; if(String.IsNullOrEmpty(_providerName)) _providerName = "System.Data.SqlClient"; _factory = DbProviderFactories.GetFactory(_providerName);

dynamic _users = new User();
var count0 = _users.Count(); // it works

var result = _users.AllI(where: "WHERE CheckDate>@0", args: DateTime.Today.ToString()); 
var count1 = result.Count(); // not work 

and I found that args cannot receive a datetime type argument, a InvalidOperationException occurred, which says "an expression of type 'System.DateTime' cannot be used to initialize an array of type 'System.Object'".

I got this error:

'tbl' does not contain a definition for 'Query' and no extension method 'Query' accepting a first argument of type 'TableName' could be found (are you missing a using directive or an assembly reference?)"

from the latest version of massive.

In your "README.markdown" you still have Query method sample such as var result = tbl.Query("SELECT * FROM Categories"); but Query method is no longer accessible. Intellisense does not contain Query method anymore.

I am having trouble to refer to the DynamicModel class. Is the table based class some sort of special class that I need to generate or is it generated automatically?

Just a quick first feedback:

    /// <summary>
    /// Executes the reader using SQL async API - thanks to Damian Edwards
    /// </summary>
    public void QueryAsync(string sql, Action<List<dynamic>> callback, params object[] args) {
        //using (var conn = new SqlConnection(_connectionString)) { // remove using statment?
            var cmd = new SqlCommand(sql, new SqlConnection(_connectionString)); //new connection is created
            cmd.AddParams(args);
            cmd.Connection.Open();
            var task = Task.Factory.FromAsync<IDataReader>(cmd.BeginExecuteReader, cmd.EndExecuteReader, null);
            //task.ContinueWith(x => callback.Invoke(x.Result.ToExpandoList()));
            task.ContinueWith(x => {
                callback.Invoke(x.Result.ToExpandoList());
                cmd.Connection.Close(); //does this also get called when an error occured? will look into it.
                                  }
               );

        //}
    }

Creating an overload with SynchronisationContext works great with WPF, btw.

The README.markdown is lying. There is no Fetch method, is there?

It was hard enough trying to google for subsonic and find relevant information. Trying to google anything on Massive returns vast amounts of irrelevant clutter. Please change the name to something ungeneric (ma55ive, massiv3)?

I know Rob said that cool people name their DynamicModel class the same as their table, but he didn't say that uncool people will get uncool errors.
:(

So let's say my SQL table name is blog_Posts.

Instantiating the class below will cause "SqlException was unhandled by user code" in Massive.cs. I assume because it's attempting to use the class name as the table name somewhere.

public class MyPosts : DynamicModel
{
    public MyPosts()
        : base("blogdb")
    {
        PrimaryKeyField = "PostID";
    }
}

No one else has noticed this because everyone else is cool and names the class the same as the table. For instance, everything works fine if I use the code below:

public class blog_Posts: DynamicModel
{
    public blog_Posts()
        : base("blogdb")
    {
        PrimaryKeyField = "PostID";
    }
}

Example:
var videos = new Videos(); var headlines = videos.All(where: "headline = @0", orderBy: "create DESC", limit: 5, columns: "videoid", args: 1);

Create a SQL query like "SELECT TOP 5 videoid FROM videos WHERE headline = 1 ORDER BY create DESC;", which MySQL throws the following error:

/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5 videoid FROM videos WHERE headline = 1 ORDER BY create DESC' at line 1 */

Workaround using Linq:

var videos = new Videos(); var headlines = videos.All(where: "headline = @0", orderBy: "create DESC", columns: "videoid", args: 1).Take(5);

Just a suggestion more than an issue: You should mention in the Readme that you rely on System.Configuration.dll, and that users will have to add this reference to a brand new Console application if they want to use Massive.

It only took me a few moments to realize, but it may help a newbie.

In our system, we store a template of the connection string in the config file, not the actual string. I'd like to be able to use Massive in this scenario, which would require the ability to pass in a fully formatted connection string. Would anyone else like this behavior?

I had some trouble inserting into a table with a column called "User" which I believe is and sql keyword. Could probably fix it by delimiting column names eg. "[User]".

Line 139 - Seems that if providerName is set to empty ("") it crashes...

Perhaps change the line to something like:
providerName = (!String.IsNullOrEmpty(ConfigurationManager.ConnectionStrings[_connectionStringName].ProviderName) ? ConfigurationManager.ConnectionStrings[_connectionStringName].ProviderName : "System.Data.SqlClient");

Where is System.Dynamic DLL?

The current Schema property for the Sqlite code isn't quite right (imo). Here's one that will return column names instead of a table name and a create table blob:

    IEnumerable<dynamic> _schema;
    public IEnumerable<dynamic> Schema
    {
        get
        {
            if (_schema == null) {
                var rows = new List<dynamic>();
                foreach (var row in Query("PRAGMA table_info('" + TableName + "')")) {
                    rows.Add(new {
                        COLUMN_NAME = (row as IDictionary<string,object>)["name"].ToString(),
                        DATA_TYPE   = (row as IDictionary<string,object>)["type"].ToString(),
                        IS_NULLABLE = (row as IDictionary<string,object>)["notnull"].ToString() == "0" ? "NO" : "YES",
                    });
                }
                _schema = rows;
            }
            return _schema;
        }
    }

I have an email table which has a few columns such as To and From. These need to be surrounded in square braces when constructing the query.

It's probably not good practice to use these column names in your tables and I know massive is small and provided as-is so I was a little hesitant to write this issue report but I thought it was best to mention it :)

Can massive just wrap all column names in square braces?

Can I make this change? Would be good to contribute to something!

You should change the SQL in Insert(object o) from:
cmd.CommandText = "SELECT @@IDENTITY as newID";
to
cmd.CommandText = "SELECT SCOPE_IDENTITY() as newID";

With the current code, you may get the wrong ID if users have triggers (that perform inserts) on their tables. Using SCOPE_IDENTITY() ensures you get the right ID back, no matter what.

Came across this issue of documentation due to a question on stack overflow.

In your readme you have the following code.

var table = new Products();
//OH NO YOU DIDN't just pass in an integer inline without a parameter! 
//I think I might have... yes
var drinks = table.All("WHERE CategoryID = 8");
//what we get back here is an IEnumerable < ExpandoObject > - we can go to town
foreach(var item in drinks){
    //turn them into Haack Snacks
    item.CategoryID = 12;
}
//Let's update these in bulk, in a transaction shall we?
table.Save(drinks);

drinks is an IEnumerable<dynamic> but Save takes a params object[] so the drinks is treated like one entity to save.

The BuildSelect method in Massive.Sqlite needs changed to use the LIMIT keyword instead of the TOP syntax. Here's the fix:

private static string BuildSelect(string where, string orderBy, int limit) {
string sql = "SELECT {0} FROM {1} ";
if (!string.IsNullOrEmpty(where))
sql += where.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase) ? where : "WHERE " + where;
if (!String.IsNullOrEmpty(orderBy))
sql += orderBy.Trim().StartsWith("order by", StringComparison.CurrentCultureIgnoreCase) ? orderBy : " ORDER BY " + orderBy;
if (limit > 0)
sql += " LIMIT " + limit.ToString();
return sql;
}

Hi,

With this code:

var table = new tblResource();
dynamic resources = table.All();

and:

@foreach(var r in resources){
   <tr>
        <td>@r.ResourceId</td>
        <td>@r.DateAdded.ToString("dd-MM-yyyy")</td>
        <td>@r.Overview</td>
        <td></td>
        <td></td>
    </tr>
}

I get an error message as follows:

The best overloaded method match for 'System.DBNull.ToString(System.IFormatProvider)' has some invalid arguments.

I am completely new to dynamics, Web Matrix and Massive but should Massive have picked up that this is a DateTime object or is there something further I need to know/do.

Thanks in advance

In CreateUpdateCommand Properties changed to Null are ignored and will not be persisted.

Suggestion:
Remove "&& item.Value != null" so that AddParam can hanlde changing null to DBNull.Value.

Created a console app, table has two columns - ID, LocationText. This works fine:

var productsFour = table.Paged(columns:"ID, LocationText");
foreach (var o in productsFour.Items)
{
Console.WriteLine(o.LocationText);
}

        Console.ReadKey();

However, if I add a WHERE clause the query generated is incorrect.
e.g var productsFour = table.Paged(columns:"ID, LocationText", where:"WHERE Id > 1000);
produces this:
"SELECT ID, LocationText FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID) AS Row, ID, LocationText FROM Seq) AS Paged WHERE Row >=0 AND Row <=20 WHERE ID > 10000"

Line 201: ExecuteNonQuery() when the parameter type is a User Defined Type, as the SQL Server 2008 spatial types are, throws the following exception:

UdtTypeName property must be set for UDT parameters

When returning data from a Query, is it possible to convert DBNull values to null so can use ?? operator?

    public IEnumerable<dynamic> Query(string sql, params object[] args)
    {
        using (var conn = OpenConnection())
        {
            var rdr = CreateCommand(sql, conn, args).ExecuteReader(CommandBehavior.CloseConnection);
            while (rdr.Read())
            {
                var e = new ExpandoObject();
                var d = e as IDictionary<string, object>;
                for (var i = 0; i < rdr.FieldCount; i++)
                    d.Add(rdr.GetName(i), DBNull.Value.Equals(rdr[i]) ? null : rdr[i]);
                yield return e;
            }
        }
    }

Not an issue, just a question but couldn't find a better place to post...

Once I get my results as expando objects, is there a way to convert each expando into the appropriate model object? I think I must be missing something...

For example, if I have 'Products'

In the Controller:

Product product_search = new Product();
var results = product_search.All(...);
return View("Browse","_Layout",results);


Rather than IEnumerable I want IEnumerable so when I iterate through the results in my Browse view I have access to all of my methods in Product...

I've tried casting in a few different ways with no luck. Am I approaching this wrong?

Massive is returning a convenience class DB.Current that is using an if clause (ConfigurationManager.ConnectionStrings.Count >1) use the first connection from your web.config.

The problem I found is that if you develop using Oracle and you have the client installed, then in the machine.config file you will/may have 2 entries (one named "LocalSqlServer" and one "OraAspNetConString") and in this case DB.Current should use the 3-th connection string and not the 2-nd as it is now in Massive.cs.

My quick fix was to replace this (lines #98 and #99)
if (ConfigurationManager.ConnectionStrings.Count > 1) {
return new DynamicModel(ConfigurationManager.ConnectionStrings[1].Name);
with following code:
var allConnections = ConfigurationManager.ConnectionStrings.Count;
if ( allConnections > 1)
{
for (int i = 1; i < allConnections; i++)
{
if (ConfigurationManager.ConnectionStrings[i].ProviderName == "System.Data.SqlClient")
return new DynamicModel(ConfigurationManager.ConnectionStrings[i].Name);
}
throw new InvalidOperationException("Can't find a valid 'System.Data.SqlClient' connection provider");
}

Thank you for your time!

Need to add where and args to the update commands.

When you use: tbl.Update( Request.Form, 25 ) (where 25 is the key), ToExpando() fails on line 75 (of Massive2) saying "TargetParameterCountException was unhandled..."
I'm pretty sure that the problem is line 69 where a test for NameValueCollection is performed, but the incoming form is of type System.Web.HttpValueCollection (despite what the docs say about HttpRequest.Form and its type!).

Here is a clue: http://dylanbeattie.blogspot.com/2008/12/mocking-querystring-collection-in.html

@ symbols must be replaced with ":".

OpenConnection() is called quite a lot from Massive, that's not very good if you're using it with a sql ce db since each openconnection is quite costly. ~100 ms on my pc. I don't know how to deal with this the best way really. For now I changed it to keep the connection open.

I have a Categories table that has the columns Cat_ID and Cat_Name. I can't use the Rails-y method of finding records, because the call would be categories.FindBy_Cat_ID(id); and that doesn't work because it treats Cat and ID as two separate 'stems'.

An overload for Update that takes an object that specifies criteria for what in the table should be updated and an object for what values to set on all matching records. I might add this, but just wanted to register the feature in case anyone has any ideas for better ways to do this. Would be useful to use the same method for Deletes too.

Would you ever want to create an instance of DynamicModel? I think not. It seems to me that DynamicModel is always inherited, leading me to think it should be marked as abstract.

Just a question...Is stored procedure supported by current version of massive?

Line 303+, missing a space
var countSQL = string.Format("SELECT COUNT({0}) FROM {1}", PrimaryKeyField, TableName);
...
countSQL += where;

results in "...FROM MyTableWHERE ..."

In the Sqlite version of Massive, the Schema property returns a CREATE TABLE string. It is certainly possible to parse it to get similar data as is returned from the Sql Server version of Massive. However, the following code will return column names and types like the Sql Server version of Massive:

IEnumerable<dynamic> _schema;
public IEnumerable<dynamic> Schema
{
    get
    {
        if (_schema == null) {
            var rows = new List<dynamic>();
            foreach (var row in Query("PRAGMA table_info('" + TableName + "')")) {
                rows.Add(new {
                    COLUMN_NAME = (row as IDictionary<string,object>)["name"].ToString(),
                    DATA_TYPE   = (row as IDictionary<string,object>)["type"].ToString(),
                    IS_NULLABLE = (row as IDictionary<string,object>)["notnull"].ToString() == "0" ? "NO" : "YES",
                });
            }
            _schema = rows;
        }
        return _schema;
    }
}

"Where" is Paged function should have a space before it.

public virtual dynamic Paged(string where = "", string orderBy = "", string columns = "*", int pageSize = 20, int currentPage = 1, params object[] args) {

   -----
   -----
        if (!string.IsNullOrEmpty(where)) {
            if (!where.Trim().StartsWith("where", StringComparison.OrdinalIgnoreCase)) {
                //original
                //where = "WHERE " + where;
                //should be
                where = " WHERE " + where;
            }
        }

    }

Line 31 is missing a placeholder. The correct code should be p.ParameterName = string.Format(":{0}", cmd.Parameters.Count);

Was trying to use MySQL with Massive and the following line in DynamicModel gave me a lot of pain ie keyword port not supported.

I would suggest that you change the line

var _providerName = "System.Data.SqlClient";

to

var _providerName = ConfigurationManager.ConnectionStrings[connectionStringName].ProviderName;

BTW Massive rocks!

Hi Rob I got an issue with oracle.

//this code threw an exception: "ORA-01036: illegal variable name/number\n"
var matchDisplay = new MatchDisplay();
var query= matchDisplay.All("where Period=:0", args:5);

//but this one works fine
var matchDisplay = new MatchDisplay();
var query= matchDisplay.All("where Period=5");

what's wrong with my params?

I set up the code according to the front page docs, and set up a sql server express table Products with appropriate columns to test the code

var table = new Products();
//OH NO YOU DIDN't just pass in an integer inline without a parameter!
//I think I might have... yes
var drinks = table.All("WHERE CategoryID = 8");
//what we get back here is an IEnumerable < ExpandoObject > - we can go to town
foreach(var item in drinks){
//turn them into Haack Snacks
item.CategoryID = 12;
}
//Let's update these in bulk, in a transaction shall we?
table.Save(drinks);

This throws an error @ line 400 within Massive.cs v 100644 from Aug 31.

System.InvalidOperationException: Can't parse this object to the database - there are no properties set

the dynamic objects "item" never actually remember their new values.

It doesn't seem to like the SELECT SCOPE_IDENTITY()
I've even tried SELECT @@IDENTITY

If I remove it the insert works fine, but not with it in there.

Any ideas?

Hi Rob,

I've been playing with including some sort of dynamic Database/Repository class such as below:

This eliminates having to create new Model classes that derive from DynamicModel for each table in your database.
Instead, you simply call:

Database.Default.Products.Find(Id:1).

Using Database.Default makes some assumptions:

1. Uses first connection string in web.config by default
2. Assumes table is named the same as the property you access
3. Assumes the primary key field is named id

Great convention over configuration approach.

You could also call:

Database.Open("MyConnectionString").Products.Find(Id:1)

OR

Database.Default.Products("tblProducts","ProductID").Find(ProductID:1)

I don't really like this last one above because it could get messy dragging that around everywhere, but maybe a simple fluent interface for one-time registration/mapping would work. Maybe something like:

Database.Default.Products.Map("tblProducts", "ProductID")

From then on any call to Database.Default.Products would create a new DynamicModel using "tblProducts" as the table name and "ProductID" as the primary key field.

Maybe even something similar to register your default connection string too. Something like:

Database.SetDefault("ConnectionStringName")

Curious to get your thoughts?

Here's the class I'm using and it works quite nicely. For DI/IoC, simply add an interface that defines the Default property and Open method.

public class Database : DynamicObject
{
    private string _connectionStringName = ConfigurationManager.ConnectionStrings[0].Name;

    private Database()
    {
    }

    public static dynamic Default
    {
        get { return new Database(); }
    }

    public static dynamic Open(string connectionStringName)
    {
        _connectionStringName = connectionStringName;
        return new Database();
    }

    public override bool TryGetMember(GetMemberBinder binder, out object result)
    {
        var model = new DynamicModel(_connectionStringName, binder.Name, "Id");
        result = model;
        return true;
    }

    public override bool TryInvokeMember(InvokeMemberBinder binder, object[] args, out object result)
    {
        var tableName = string.Empty;
        var primaryKeyField = string.Empty;

        var argCount = args.Length;

        if (binder.CallInfo.ArgumentNames.Count > 0)
        {
            var arguments = binder.CallInfo.ArgumentNames.Select((item, index) => new { Name = item, Index = index });
            tableName = args[arguments.Where(n => n.Name == "tableName").FirstOrDefault().Index].ToString();
            primaryKeyField = args[arguments.Where(n => n.Name == "primaryKeyField").FirstOrDefault().Index].ToString();
        }
        else
        {
            if (argCount > 0)
            {
                tableName = args[0].ToString();
            }
            if (argCount > 1)
            {
                primaryKeyField = args[1].ToString();
            }
        }
        var model = new DynamicModel(_connectionStringName, tableName, primaryKeyField);
        result = model;
        return true;
    }
}

How do I use table.Scaler? I am using the following code which somehow returns an int value instead of the object.

table.Scalar("select * from articles where articleId=@0", id);

The NuGet package puts the following structure into a project.

->Project Root
-----> App_Code
---------> LICENSE.txt
---------> Massive.cs

To avoid any conflicts with other projects and due to the root level of App_Code, it might make sense to rename LICENSE.txt to something like Massive.LICENSE.txt (if the license will allow it).