Tag: SQL

Structured Query Language (SQL) is a language for managing data in relational database management systems. This tag is for general SQL programming questions; it is not for Microsoft SQL Server, nor does it refer to specific dialects of SQL on its own.

Categories
SQL

Replacing first occurrence of certain characters in MSSQL database

This post is regarding how to replace first occurrence of one or more characters from database table Column.

Suppose we have table named User with columns Id, Name, Phone Number as shown in the figure below.

User database table
User database table

And contains records as shown in figure below:

User records before script
User records before script

Now what I required to accomplish is replace the first ‘-‘ character with space ‘ ‘ so that for example phone number 214-654-1800 becomes 214 654-1800. The script for this as below and next to it is the resulting data.

MS Sql Script:

DECLARE @find varchar(8000)
SELECT @find='-' -- << Replace character
UPDATE [User]
SET [PhoneNumber]=Stuff([PhoneNumber], CharIndex(@find, [PhoneNumber]), Len(@find), ' ') -- << Replacement character

After applying the script the changes happen as in figure below:

User records after applying script
User records after applying script

 

Reference link: Is it possible to replace the first occurrence of a string in a column ?

Related articles

 

Categories
Interview Questions SQL Utility

MSSQL – Change default database by sql query

Microsoft SQL Server Management Studio showing...
Microsoft SQL Server Management Studio showing a query, the results of the query, and the Object Explorer pane while connected to a SQL Server database engine instance. (Photo credit: Wikipedia)

Dear reader,

Recently I noticed in my daily work that every time I logged in MS SQL I was required to select the database which I had to work on and this was regularly in use by me and this lead me to find the solution to change the default database selection after I log on to MS SQL using SSMS to reduce the hassle and also so that I don’t execute queries on different databases by mistake.

There is an inbuilt stored procedure name sp_defaultdb which can be used as below

Exec sp_defaultdb @loginame='Domain/UserName', @defdb='MyDefaultDatabaseName'

Here value of parameter @loginname is User Name such as ‘sa’ and it can change depending on the MSSQL login configurations such as Sql Authentication or Windows Authentication. Example shown here is for Windows Authentication. Then value of parameter @defdb is the database name such as ‘master’ or ‘AdventureWorks’ and it will change as per the need to set the default database for the particular login specified.

But this stored procedure seems soon to be obsolete as mentioned in MSDN link here. There is newer option available for this purpose – ALTER LOGIN which has many other usability apart from setting the default database but I would stick to the topic and show its usage.

ALTER LOGIN [Domain\UserName] WITH DEFAULT_DATABASE = [MyDefaultDatabaseName]

I hope you found this helpful and learnt something new from this.

Reference links: StackOverflow – How can I change my default database in SQL Server without using MS SQL Server Management Studio?

Keep your comments flowing for what you prefer. 🙂

Categories
.NET C# Entity Framework WPF

Understanding HB Code – Line of business application for WPF – Part I

 

HB Code – Line of business application for WPF

MVVM Architechture Overview

This project is made available as open-source and hosted freely on www.codeplex.com.

Prerequisites

  • Object-oriented programming using C#.NET and LINQ.
  • Nuget package usage.
  • Basic knowledge of WPF application building such as input controls, container controls, triggers, styles, templates.
  • Entity Framework, currently knowing any of versions of Entity Framework will work to check the working sample.
  • Sql Server 2005 database configuration and Sql queries.

Built

Technologies: .NET 4.0, WPF, Entity Framework 4.3 Code First,

Control toolkits: Avalon Dock, Avalon Dock MVVM, WPF Toolkit, WPF Extended toolkit, Microsoft Ribbon for WPF

Tools: Visual Studio Professional 2010, Sql Server 2005 Express, Entity Framework Power tools extension, Nuget Package Manager.

Please note: Current implementation is kept intentionally strongly typed to keep it easy to understand and reduce development time at initial stage.

Solution Projects Overview

  • HBCode Application (View)

This is the main entry point of the application. In this part, we have all the WPF and UI specific code implementations. It basically contains the WPF resources, WPF converters, Extended User Controls, Images, Application and Database Configurations, and Views (UserControls) used in the application.

  • HBCode Business Logic (Model)

This project contains all the business modules, business logic validation, and database connectivity, property change notifications implementations. Currently it uses Entity Framework 4.3 Code First with migrations to connect with the database. The Entity Framework 4.3 building blocks such as Database Context, Models, and Migration classes exist in this project.

  • HBCode Presentation (ViewModel)

This project contains the middle-tier ViewModel classes. View Model contains Collection of models, single models and sometimes aggregate properties for display purposes. The UserControls aka View are Databound to ViewModels containing model instances. ViewModels are built considering the UI layout and requirements. Such as if we consider a Sales billing UI will be containing Item list in grid and there will be one ComboBox column to display items to select from, so to Databound the Combobox we could either List<ItemModel> property in ViewModel at top-level or we can implement List<ItemModel> per row instance in List<ItemSaleModel> which would be bound to grid.

This project is made available as open-source and hosted freely on www.codeplex.com. You can download the source from www.hbcode.codeplex.com.

 

Related articles