Data Tips & Tricks

Generally while creating entity for our documentDB collection/document, we used to add the JsonProperty() attribute to each & every field. Now to create a similar DomainModel or ViewModel we used to copy this entity class & deleting all the lines with JsonProperty() If our class contains more properties it looks very tedious task. What if we've something with which we can mark all the line having "JsonProperty" attribute then delete all those marked lines. In Visual studio we don't have such extension, but we can achieve this using notepad++ editor. here are the steps to remove all the lines contains specific word (e.g.: JsonProperty)  Copy your code to nodepad++  Ctrl + F & go to Mark tab  Enter your text here "JsonProperty"  Tick "Bookmark All" check-box and click on "Mark All" This way all the line containing your specific work (e.g. JsonProperty) will get highlighted. Now. Go to "Search" =>...

In any object oriented programming language ENUM is frequently used "user defined datatype" (UDD), but not every RDBMS databases support ENUM. MySQL supports ENUM data-type. Let's see how it behaves in the context of MySQL. -- Step1. create table with ENUM datatype -- 1a. drop table if already exists DROP TABLE IF EXISTS delete_enum_datatype; -- 1b. Create table with default values (1. large, 2. small, 3. x-large)  CREATE TABLE delete_enum_datatype (fittings ENUM('large', 'small', 'x-large')) -- Step2. Insert some Dummy data (already defined in DDL: 'large', 'small', 'Small' with capital S \ --            not in table definition: 'big'  INSERT INTO delete_enum_datatype(fittings) VALUES ('large'), (' big '), ('small'), ('Small')   Point To Ponder:  MySql internally stores the key of the  ENUM  values as integer data-types.            ...

Sometimes while we're fetching result-set from underlying database we also want to get the row number for each and every row of this result-set. Generally it can easily be achieved by using RANKing Functions ( e.g. ROW_NUMBER() OVER(ORDER BY <yourColumnName> ). But unfortunately my-sql doesn't support RANKing function. Let's see below query how to get row number for the given result set. CREATE TABLE testRowNumber ( firstName VARCHAR(50) NOT NULL ) INSERT INTO testRowNumber (firstName) VALUES  ('Ram'), ('Shyam'), ('Mohan'), ('Sohan'), ('Sita'), ('Gita') SELECT    (@rowNum := @rowNum + 1) AS rowNumber    , firstName FROM testRowNumber AS e,    (SELECT @rowNum := 0) a

Let's say we want to create a table in which each row will get unique id while inserting new row, means while we're inserting new row that particular column-value will be automatically inserted with unique integer value. In this scenario we can use " AUTO_INCREMENT " column type. -- drop table if exists DROP TABLE IF EXISTS `TEST_AUTO_INCREMENT` -- create a dummy table CREATE TABLE TEST_AUTO_INCREMENT   (      Id INT AUTO_INCREMENT PRIMARY KEY      , FirstName CHAR(40)   ) -- AUTO_INCREMENT is applicable only with following data-types (integral data-types)   TINYINT   SMALLINT   MEDIUMINT   INT   BIGINT   DOUBLE -- we can insert auto-increment values in different ways -- If want to use the database generated vaules   either don't include the AUTO_INCREMENT column into the insert statement ...

Let's say we've set of data in array format e.g. " Ram, Shyam, Mohan, Sohan, Sohit " and we want a result-set in tabular format Name Ram Shyam Mohan Sohan Sohit Mysql database doesn't support array data-type , so we can't do the conversion easily in the expected format. Here is the query to full-fill this requirement. Step1. Execute the below query in your MySQL database schema -- STORED PROCEDURES DROP PROCEDURE IF EXISTS ; split_words_into_rows DELIMITER # CREATE PROCEDURE split_words_into_rows(IN _in_tags_to_be_seperated VARCHAR(8000), IN _in_seperator CHAR(3)) proc_main:BEGIN -- 1. Begin Proc -- watch out for variable names that have the same names as fields !! DECLARE v_keyword VARCHAR(255); DECLARE v_keyword_id MEDIUMINT UNSIGNED; -- Iteration variable (v_tags_done: if done through each and every word of input string paramter, --        ...