sql

using nameof operator in entity framework

/ bonnu18 / Leave a comment

Recently while working on piece of code I wanted to delete a set of data satisfying a condition and therefore it would be a simple Delete statement with a Where clause. Since the data would be retrieved using Entity Framework the SQL statement would be written in a string and then executed using the ExecuteSqlCommand method. The idea was to have the SQL statement written in bits and pieces and then by means of method parameters I could execute it on different tables. Something like the following.


public void ClearTableWWhereClause(string strConnectionString, string strTableName, string strColumnName, string strValue)
{
string strQuery = "DELETE FROM [" + strTableName + "] WHERE " + strColumnName + " = '" + strValue + "'";
using (StatementsBenchmarksEntities dbContext = new StatementsBenchmarksEntities(strConnectionString))
{
dbContext.Database.ExecuteSqlCommand(strQuery);
}
}

view raw

nameofentityframeworkone.cs

hosted with ❤ by GitHub

So far so good. The problem I faced was when trying to get the column name dynamically instead of writing it hard coded in the parameter field when calling the method. Let’s say we have the following class called Blog which is a model of the database table with the same name.


public partial class Blog
{
public int ID_PK { get; set; }
public string Title { get; set; }
public string Author { get; set; }
public string Content { get; set; }
}

view raw

nameofentityframeworktwo.cs

hosted with ❤ by GitHub

Getting the table name was fairly easy and this was done by first creating an instance of the class and then calling the GetType method like so – objBlog.GetType().Name. On the other hand getting the column name proved to be a little bit more difficult. A quick Google search and visit to StackOverflow suggested that I should use a loop and get all column names but I wasn’t satisfied with that solution. I found other solutions but again they didn’t really impress me. I then started to think and treated the model as a class rather than a DB object. I then realised that I was interested in a property of a class and not in a column name of a table. The simple solution that worked for me was to use the “newly” introduced nameof operator. This operator is, and quoting from Microsoft’s own website, used to obtain the simple (unqualified) string name of a variable, type, or member. Implemented it my own solution and the end result looked something like the following line.


ClearTableWWhereClause(ConnectionString, objBlog.GetType().Name, nameof(objBlog.Author), "Bjorn Micallef");

view raw

nameofentityframeworkthree.cs

hosted with ❤ by GitHub

Nice, neat and effective. It surely did the trick for me and will certainly implement it again in the future.

Bjorn

 

fiddling around with a date parameter in an SQL query

/ bonnu18 / Leave a comment

Hey guys!

Continuing on my previous blog post which was databases oriented, this week I ended up fiddling around with an SQL query in order to fetch the data set I was after. Diving straight into it, say we have a table in the database and this table has records of different API releases. Four columns. Unique ID, name, version number and release date, and it would look something like this.

tbl_api_version

At one point I was interested in getting which versions the API was running on from a particular date onward. If we had to look at the table above, and for instance pick the first row, API version Superman was active from 01/01/2015 till 24/05/2015, one day before the release date of API version GreenLantern. Therefore, for my query, say I was interested in getting active API versions from 01/08/2017, the query should return API versions Atom, GreenArrow and Hawkgirl. The problem I was facing was that table, in my opinion, could have been designed better and include a start date column and an end date column. Having that structure querying would turn out to be easier and in fact that is what I aimed for when building my query. The idea behind it was to create two columns, start and end, on the fly and then manipulate them to get the desired result.

The query I came up with would be the following;


DECLARE @param DATETIME
SET @param = '2017-01-01 00:00:00.000'
SELECT Version, Name, StartDate FROM(
SELECT Version,
Name,
MIN(Date) StartDate,
ISNULL(( SELECT DATEADD(day, -1, MIN(c.Date)) EndDate
FROM tbl_api_version c
WHERE c.Version = b.Version + 1 GROUP BY Version, Name ), GETDATE()) EndDate
FROM tbl_api_version b GROUP BY Version, Name) a
WHERE @param BETWEEN a.StartDate AND a.EndDate OR a.EndDate > @param
GROUP BY Version, Name, StartDate
ORDER BY Version

view raw

dateparametersqlquery.sql

hosted with ❤ by GitHub

I’ve also created an SQL fiddle which has the create and insert script, and the actual select statement. Is this the best solution? Probably not. I ran the query constantly several times and the SQL Server Management Studio was producing the results quite instantaneously so for the time being I was satisfied. I’m still not sure how efficient this would be if we had to perform this query on a table that has way more rows, for instance thousands. I have my doubts it would be efficient though since we have two nested select statements. Feel free to share your thoughts in the comments section if you would have tackled it differently or perhaps if you thought of a cleaner or faster solution to his problem.

Till my next post,
Bjorn

entity framework model did not update

/ bonnu18 / 1 Comment

Recently I was working on this project and I was asked to create a new module to create a new feature. The C# solution, as most technologies, makes use of a database in order to fetch and store data. Basic CRUD operations. As you might have already guessed from the title of this post the project makes use of ADO.NET and Entity Framework to perform such processes. I will have to admit that I am not a fan of ADO.NET or Entity Framework. Personally speaking I prefer to create scripts, stored procedures, views, etc and then open connection strings and retrieve the data as data tables/data sets. Having said that, that was my opinion, and since the project was already in a stable running state I had to adapt and work with Entity Framework, which I’m also comfortable with.

The problem happened when I created a new table via SQL Server Management Studio, let’s call it tbl_weather. This new table would have historical data and store “raw” data that would be processed later on. tbl_weather would have three columns and roughly this type of data.

tbl_weather

I then switched to Visual Studio and tried to update my Entity Framework model. Right click > Update model from database > Next > Next > Finish … job done. But wait, where’s tbl_weather?? I tried this step again and again and nothing happened. The connection string was good because my new table was being detected when attempting to update my model from the database. So what is wrong with it?

After a while I tried to delete my table and re-create it but this time round I added another column, ID, and set it as unique primary key. I tried to update my model once more and this time round the table was added to my model straight away. To my understanding it seems that Entity Framework would not accept to add “basic” tables but it would require the new tables to be somewhat more structured. In this case it seemed that not having one of the most basic SQL standard, a primary key, was preventing me from adding my table to my model.

Now, I know that a primary key is a must and that a unique identifier must be present but in this case I was fetching the data from a source and bulk insert it into this table. This data would then be retrieved again, processed and insert in yet another new, better structured table. Therefore, I didn’t really worry the primary key.

Anyways, that’s my two pence that I wanted to share with you guys, until next time, adios!

Bjorn