Why database should be taught before programming in universities

Learn Database before Coding

Often students from initial semester ask me how do we store our data in our programming project? When students join university to learn about computer science and technology they are usually taught programming first in courses like introduction to programming. As part of coursework students are required to work on a project. Majority of the projects, in fact almost all projects involve data handling and that data needs to be stored somewhere.

Problem students face

As a novice students don’t know how to store data. One option is to store data in plain text files if filing is taught to them but in that case their project becomes too complex for them. In my opinion, file formats is an advanced topic for students that have just started learning how to program. So, students get stuck where and how to store data. They create variables and arrays to store data in memory but that is not very useful until they have option to store their data somewhere permanently that they can retrieve later. Otherwise, every time they run their project they have to feed data from the beginning.

Teach Database before Programming

If universities modify their courses and add database in first semester and replace programming course with it then it would be easier for students to get started in computer science degree. Introduction to database is relatively easier course then programming and students will know what is database, how to store data in database, and how to retrieve it later using SQL. Then in next semester if they do a programming course then it will require only one lecture to teach them how to access database from your code and how to store and retrieve data. That will not only make their projects more valuable but will make more sense to them and they can take it to advance level in forthcoming courses.

Your Take?

What is your opinion? Please, let me know in the comments.

Deploy your first ASP.Net MVC App to AppHarbor

If you’ve been developing ASP.Net MVC apps lately you might be thinking some online or cloud based app hosting platform available as PaaS for Microsoft technologies specially for hosting ASP.Net MVC apps just like OpenShift, Heroku and other platforms are available for technologies like Ruby, Python, PHP, Node.js and even supporting CMS like WordPress. The good news for ASP.Net developers is that there is a PaaS platform available which you might already know. The platform is AppHarbor. AppHarbor runs over Amazon AWS and has some nice features that I won’t go into the details of. If you are interested in knowing how AppHarbor works you can see their page here.

Regardless of AppHarbor provides a decent service, new developers might still face some difficulty in deploying their applications to AppHarbor. Specially, if you are developing apps based on new Visual Studio 2015 templates like MVC. Their are different ways to deploy but I would follow below approach which in my opinion is good and provide auto deployment or in other words Continuous Integration (CI).

What you need?

We will be using following tools and accounts.

  • Visual Studio 2015 (any edition, I used Professional version)
  • ASP.Net MVC app created from VS2015 MVC template
  • GitHub repo for the app/project
  • Local git repo for app with remote repo set as your GitHub app repo
  • AppHarbor app

What’s not covered?

Our focus today is deployment of our ASP.Net MVC app to AppHarbor. Therefore, we won’t be going into the details of how application is created or its architecture, what’s new in Visual Studio 2015, what is MVC, what is Git and GitHub, and how to connect your GitHub repo to AppHarbor etc. We will assume that you already have all the pre-requisites and we will just focus what problems can we come across during deployment and how to fix them.

Deployment Steps

  1. Initialize a Git repo and connect it with your GitHub repo.
  2. Create AppHarbor app from your GitHub repo. Whenever we commit/push our changes to our GitHub repo AppHarbor will automatically fetches the latest push and build it. Upon successful build it will deploy the app on its server otherwise it will keep last successful build. This makes things very easy.
  3. Create an ASP.Net MVC application using Visual Studio 2015 MVC template.
  4. Add the packages folder to .gitignore
  5. Enable NuGet Package Restore. In VS2015 click Tools>Options and then select NuGet Package Manager and make sure both checkboxes are checked in this section.
  6. In VS2015 right click on project and click properties, then goto Build Events tab. In the Post-build event command line text area paste the following command.
    1. if not exist “$(WebProjectOutputDir)\bin\Roslyn” md “$(WebProjectOutputDir)\bin\Roslyn”
      start /MIN xcopy /s /y /R “$(OutDir)roslyn\*.*” “$(WebProjectOutputDir)\bin\Roslyn”
  7. Now commit and push your changes to GitHub.
  8. That’s it! AppHarbor will automatically fetch the latest version changes and build it and you can check your AppHarbor application on its URL.

What’s Next?

There are few things that you need to take care of specially related to security. This MVC app uses SQL Server Compact which isn’t a good option for production level apps. Secondly, your connection string or password to database must not be committed to public GitHub repo.

This post will just give you smooth start without any difficulties which I faced among other people that you can see in the resources section below.

If you think this was helpful or if I have missed anything please do let me know in the comments below.

Happy coding!

Resources

  1. https://blog.appharbor.com/2012/02/06/use-nuget-package-restore-to-avoid-pushing-packages-to-appharbor
  2. https://support.appharbor.com/discussions/problems/79727-error-msb3202-when-building
  3. https://support.appharbor.com/discussions/problems/78633-cant-build-aspnet-mvc-project-generated-from-vstudio-2015-enterprise#comment_37577678

How to Find a Stored Procedure in all the Databases?

When working with MS SQL Server sometimes you need to find a stored procedure in the databases and all that you know is the name of the stored procedure that your teammate told you that he had used but couldn’t remember in which database it was. All the information about the stored procedures created is saved in the master database. So, if you have access to master database that you probably have then you can run the following query to get information about the stored procedure.

select * 
from 
   sys.procedures 
where 
   name like '%name_of_proc%'

This will tell you in which schema you will find the stored procedure in. Even, if you want to look into the code you can do so by running the following query.

select text 
from 
    syscomments c
    inner join sys.procedures p on p.object_id = c.id
where 
    p.name like '%name_of_proc%'

The text field contains the code of the stored procedure.

Let me know in the comments if you find this helpful or if you have any other ways to find the stored procedures.

Searching Lowercase data in MS SQL Server

Few days back I came across a requirement to fetch the records from the table having all lowercase letters. This was interesting scenario. The SQL Server database by default is set for case-insensitive. But, I need to do case-sensitive search. Case sensitive search in SQL Server can be achieved in two ways. Either by using COLLATE or by using BINARY_CHECKSUM().

  • COLLATE is the T-SQL clause used to define collation.
  • BINARY_CHECKSUM() is a built-in system function used to compare the binary check-sum value.

In this article I will show you how to use both the options. In addition to lowercase search, I’ll show you the uppercase search and mixed case search as well. 

To start with the experiment, lets create a table.

CREATE TABLE [dbo].[MyTecBits_Table_1](

[Sl_no] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](50) NULL,

[Description] [varchar](500) NULL

) ON [PRIMARY]

GO

Insert records some with all lowercase, some with all uppercase and some with mixed case.

insert into MyTecBits_Table_1 values (‘MYTECBITS’,’THISWEBSITEHASBITSANDPIECESOFTECHNICALINFORMATION’)

insert into MyTecBits_Table_1 values (‘mytecbits’,’thiswebsitehasbitsandpiecesoftechnicalinformation’)

insert into MyTecBits_Table_1 values (‘MyTecBits’,’ThisWebSiteHasBitsAndPiecesOfTechnicalInformation’)

insert into MyTecBits_Table_1 values (‘MY TEC BITS’,’THIS WEB SITE HAS BITS AND PIECES OF TECHNICAL INFORMATION’)

insert into MyTecBits_Table_1 values (‘my tec bits’,’this web site has bits and pieces of technical information’)

insert into MyTecBits_Table_1 values (‘My Tec Bits’,’This Web Site Has Bits And Pieces Of Technical Information’)

SQL Server Case-Sensitive Search

 

Now our test table with test strings with different case types are ready for our experiment.

Fetching all lowercase records:

Fetching only the rows having all lowercase in one of the column using COLLATE method.

select * from MyTecBits_Table_1 where Name COLLATE Latin1_General_CS_AI = Lower(Name)

Fetching only the rows having all lowercase in one of the column using BINARY_CHECKSUM() method.

select * from MyTecBits_Table_1 where BINARY_CHECKSUM(Name) = BINARY_CHECKSUM(Lower(Name))

See the results for lower case search:

SQL Server Case-Sensitive Search

 

Fetching all uppercase records:

Similar to fetching the lowercase string, we can search for uppercase string just by changing the LOWER string function to UPPER string function. See the example below.

Fetching only the rows having all uppercase in one of the column using COLLATE method.

select * from MyTecBits_Table_1 where Name COLLATE Latin1_General_CS_AI = Upper(Name)

Fetching only the rows having all uppercase in one of the column using BINARY_CHECKSUM() method.

select * from MyTecBits_Table_1 where BINARY_CHECKSUM(Name) = BINARY_CHECKSUM(Upper(Name))

See the results for upper case search:

SQL Server Case-Sensitive Search

Fetching only the mixed case records:

Just like uppercase and lowercase sensitive search, we can search for mixed case records by combining upper and lowercase search conditions.

Fetching only the rows having mixed case in one of the column using COLLATE method.

select * from MyTecBits_Table_1 where Name COLLATE Latin1_General_CS_AI != Upper(Name) and Name COLLATE Latin1_General_CS_AI != Lower(Name)

Fetching only the rows having all mixed case in one of the column using BINARY_CHECKSUM() method.

select * from MyTecBits_Table_1 where BINARY_CHECKSUM(Name) != BINARY_CHECKSUM(Upper(Name)) and BINARY_CHECKSUM(Name) != BINARY_CHECKSUM(Lower(Name))

See the results for mixed case search:

SQL Server Case-Sensitive Search

See my other articles on Database at https://mjawaid.wordpress.com/category/database/

[This article originally published here]