Category: SQL

05 May 2019

Entity-Framework LINQ NoSQL FireBase Json-To-String

sql2

We used Entity Framework in Data-base-first strategy in order to practice LINQ to DB using more complicated commands. we updated the database tables using Entity Model methods (without using SQL queries!). we saw a fast way to print an object using JsonConverter (instead of overriding ToString method), from the Newtonsoft library. then we explored NoSQL using Google’s Firebase free cloud storage. we connected to the Firebase using FireSharp library (nugget) and retrieved data from the FireBase using a C# program.

firebase

sqlnosql

More topics covered:

  • LINQ join command
  • Methods in LINQ which can only be written in method syntax
  • In order to use EF to add row – table must have a PK
  • JSON object serialization
  • Firebase console (+db creation)
  • FireSharp installation
  • Retrieving our Firebase DB URL (for the C# program)
  • Retrieving our Firebase secret  (for the C# program)

Links:

 

 

28 Apr 2019

App.Config SKU-version Entity-Framework

sql2

We learned about App.Config file and the benefits of using a configuration file in our application. we saw how to read data from the App.Config using ConfigurationManager class. we explored the location of the super config file (machine.config) in the computer’s .NET framework folder. Then we stored our ConnectionString in the App.Config file. Later, we discussed about Entity Framework: DataBase first, Code first, Model first methodology. then we implemented a DataBase first approach in a C# console application which uses the Entity Framework to interact with the MSSQL data base.

ef2

More topics covered:

  • App.Config – SKU vs supported version
  • ConfigurationManager key value pair
  • Modifying App.config without compilation
  • Running console application from command line
  • ADO.NET Entity Data Mode
  • Pluralize/ signalize POCO class

Links:

 

 

17 Apr 2019

MSSQL T-SQL stored-procedures ADO.NET winform-MSSQL

sql2

We learned about T-SQL and the benefits from using T-SQL. We used the Sql Server Management Studio (SSMS) to generate Stored-Procedures. then we executed the stored procedures from within the SSMS and from C# code. we generated store-procedures with parameters and without parameters. later on, we created a Winform application and used ADO.NET to create a GridView and ComboBox which retrieves data from the MSSQL automatically using queries and stored-procedures.

storepr

ado

More topics covered:

  • Stored-procedures location in SSMS
  • Stored-procedures execution in SSMS designer
  • C# ADO.NET using code and wizard
  • Retrieving data from MSSQL into ListBox

Links:

14 Apr 2019

MSSQL Join inner left right full cross union

sql2

We used the Sql Server Management Studio (SSMS) in MSSQL and explored the different kind of joins: inner-join, left-join, right-join, full-join, cross-join and union. inner join selects records that have matching values in both tables, left join returns all records from the left table (“left” means the left table name in the query. the first table name appearing in the query), and the matched records from the right table (right table name in the query). The result is NULL from the right side, if there is no match. right-join returns all records from the right table , and the matched records from the left table. The result is NULL from the left side, when there is no match. full-join return all records when there is a match in either left or right  table records. Union operator is used to combine the result-set of two or more SELECT statements. Cross-join produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table (This kind of result is called as Cartesian Product). and finally we saw how to insert multiple rows to a table using single query of insert + select

joins

More topics covered:

  • Working with SSMS
  • Creating foreign key in SSMS designer
  • Displaying two tables in one query
  • SSMS “prevent saving changes…” option

Links:

10 Apr 2019

SQLITE many : many nested-query MSSQL

sql2

We explored the many : many table relationship architecture using Primary Key (PK) in one table (for example Customers table) and using Primary Key (PK)  in a second table (for example Products table) and using a set of  2xPrimary Keys (PK) in a third table (for example Orders table). both of these primary keys are also defined as Foreign Keys (FK) to the first two tables. so what did we gain? we got customers which could buy many products. and products which could be bought buy many customers, so there we have- many : many. each purchase is a new row in Orders table. we saw how to combine all of these three tables in a join query. we also saw how to create a DB using Sql Server Management Studio (SSMS) in MSSQL. and how to read the tables from c#.

many_

More topics covered:

  • more complicated nested queries
  • Go keyword in MSSQL
  • Connecting VS 2017 to MSSQL directly
  • MSSQL connection string using integrated security

Links:

07 Apr 2019

DATA-ACCESS-OBJECT SQLITE 1:many

sql2

We explored the 1:many table relationship architecture using Primary Key (PK) in one table (for example Department table) and using Primary Key (PK) + Foreign Key (FK) in the second table (for example Employee table). so what did we gain? we got a department which could include many employees (1:many). then we saw how to retrieve an employee with his department name using join query. we also saw how to perform a count in SQL query, i.e. “how many employees belong to each department?”: we wrote an SQL query which updates the count column for each row in the Department table using a nested Update query. then we wrote a C# program which executes a join query on these two tables, and stores the result in an anonymous object (for a new view). we also saw how to modify a query result column name in order to avoid ambiguity in the C# reader result

1_n.PNG

More topics covered:

  • Using = and == in SQL query
  • Adding customozied names to an anonymous object properties
  • POCO should override == , != , Equals,  GetHashCode
  • SQLite Auto Increment

Links:

31 March 2019

DATA-ACCESS-OBJECT SQLITE 1:1 1:many many:many

sql2

We discussed the separation between Graphic User Interface (GUI), Business Logic (BL) and Data Access Layer (DAL) – which includes the Data Access Object (DAO). We explored the table relationship flavors 1:1 , 1:many , many:many. we created two SQLite tables containing 1:1 relationship using Primary Key (PK) and Foreign Key (FK) references. then we wrote a C# program which executes a join query on these two tables, and stored the result in an anonymous object (for a new view)

1_1

More topics covered:

  • Join select query in C#
  • Creation of anonymous object from an SQL result
  • Combining PK + FK between two tables (SQLite)
  • Creating SQLite table with FK null chicken-egg dilemma

Links:

Design a site like this with WordPress.com
Get started