MS SQL Server Archives - Ibexoft - Your Technology Partner!

Why database should be taught before programming in universities?

August 22, 2021August 4, 2022 by Muhammad Jawaid Shamshadin .Net, Android, AngularJS, ASP.Net, C/C++, C#, Database, HTML/CSS, Javascript, jQuery, MS SQL Server, MySQL, Oracle, PHP, Ruby, Web Development, XMLLeave a Comment

Database Programming — Learn Database before Coding

Often students from the initial semester ask me how do we store our data in our programming projects? 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 the coursework, students are required to work on a project. The majority of the projects, in fact almost all projects involve data handling and that data needs to be stored somewhere, usually in databases.

Problems 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 format is an advanced topic for students that have just started learning how to program. So, students get stuck on 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 the 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 the first semester and replace programming courses with it then it would be easier for students to get started in computer science degree. Introduction to databases is a relatively easier course than programming and students will know what a database is, how to store data in the database, and how to retrieve it later using SQL. Then in the next semester if they do a programming course then it will require only one lecture to teach them how to access a database from your code and how to store and retrieve data. That will make their projects more valuable and make more sense to them and they can take it to an advanced level in forthcoming courses.

Your Take?

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

Click here to read more about Databases.

Deploy your first ASP.Net MVC App to AppHarbor

January 20, 2016August 30, 2022 by Muhammad Jawaid Shamshadin .Net, Amazon, AWS, C#, Database, IIS, Microsoft, MS SQL Server, OpenShift, Technology, Web, Web Development, WindowsLeave a Comment

If you’ve been developing ASP.Net MVC apps lately you might be thinking of some online or cloud-based app hosting platform available as PaaS for Microsoft technologies especially 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 that 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 whether AppHarbor provides a decent service, new developers might still face some difficulty in deploying their applications to AppHarbor. Especially, if you are developing apps based on new Visual Studio 2015 templates like MVC. There are different ways to deploy but I would follow the below approach which in my opinion is good and provide auto deployment or in other words Continuous Integration (CI).

What do you need?

We will be using the following tools and accounts.

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

What’s not covered?

Our focus today is the deployment of our ASP.Net MVC app to AppHarbor. Therefore, we won’t be going into the details of how the 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 prerequisites and we will just focus on what problems can we come across during deployment and how to fix them.

Deployment Steps

Initialize a Git repo and connect it with your GitHub repo.
Create the AppHarbor app from your GitHub repo. Whenever we commit/push our changes to our GitHub repo AppHarbor will automatically fetch the latest push and build it. Upon successful build it will deploy the app on its server otherwise it will keep the last successful build. This makes things very easy.
Create an ASP.Net MVC application using the Visual Studio 2015 MVC template.
Add the packages folder to .gitignore
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.
In VS2015 right click on the project and click properties, then go to the 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”
Now commit and push your changes to GitHub.
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 a 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 the database must not be committed to a public GitHub repo.

This post will just give you a 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

How to Find a Stored Procedure in all the Databases?

September 18, 2015October 22, 2022 by Muhammad Jawaid Shamshadin Database, Microsoft, MS SQL Server, TechnologyLeave a Comment

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 the master database, 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. 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

September 17, 2015September 5, 2022 by Muhammad Jawaid Shamshadin Database, Microsoft, MS SQL Server, TechnologyLeave a Comment

A few days back I came across a requirement to fetch the records from the table having all lowercase letters. This was an interesting scenario. The SQL Server database by default is set for case-insensitive. But, I need to do a 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 checksum value.

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

To start with the experiment, let’s 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’)

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

Fetching all lowercase records:

Fetching only the rows having all lowercase in one of the columns 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 columns using the BINARY_CHECKSUM() method.

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

See the results for lower case search:

Fetching all uppercase records:

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

Fetching only the rows having all uppercase in one of the columns 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 columns using the BINARY_CHECKSUM() method.

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

See the results for upper case 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 cases in one of the columns 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 cases in one of the columns using the 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:

Click here to read more on the Databases.

[This article was originally published here]