RcBuilder@walla.com
call centre: 054-5614020

SQL Categories

Posted by: RcBuilder  /   Category: CTE / DBA / SQL / SQL Function / Stored Procedure / System Function   /   No Comments
27
Nov
2014

SQL Categories - OPTION 1 - declare @tbl table(Id int,Name nvarchar(50),Parent int,SubCategories nvarchar(500)) insert into @tbl select 1,'111',0,'' union all select 2,'222',1,',2,5,6,7,13,14,' union all select 3,'333',1,'' union all select 4,'444',1,'' union all select 5,'555',2,'' union all select 6,'666',2,'' union all select 7,'777',2,',7,13,14,' union all select 8,'888',3,'' union all select 9,'999',3,'' union all select 10,'aaa',3,'' union all select 11,'bbb',3,'' union all select 12,'ccc',3,'' union all select 13,'ddd',7,'' union all select 14,'eee',7,'' ------------------------------------ declare @Id int set.

Read more

SQL FOR XML EXPLICIT

Posted by: RcBuilder  /   Category: DBA / FOR XML / SQL / XML   /   No Comments
09
Nov
2014

SQL FOR XML EXPLICIT SELECT 1 AS 'Tag' ,NULL AS 'Parent' ,NULL AS 'XML!1' ,NULL AS 'Company!2!Code' ,NULL AS 'Company!2!Name' ,NULL AS 'Department!3!Code' ,NULL AS 'Department!3!Name' UNION ALL SELECT 2 AS 'Tag' ,1 AS 'Parent' ,NULL AS 'XML!1' ,RTRIM(Code) AS 'Company!2!Code' ,Name AS 'Company!2!Name' ,NULL AS 'Department!3!Code' ,NULL AS 'Department!3!Name' FROM Company UNION ALL SELECT 3 AS 'Tag' ,2 AS 'Parent' ,NULL AS 'XML!1' ,RTRIM(c.Code) AS 'Company!2!Code' ,NULL AS 'Company!2!Name' ,RTRIM(d.Code) AS 'Department!3!Code' ,d.Name AS 'Department!3!Name' FROM Company c INNER JOIN Department d ON.

Read more

LINQ 2 SQL

Posted by: RcBuilder  /   Category: ADO.NET / button / Button / DBA / div / GridView / HTML / LINQ 2 SQL / SOURCE CODE / SQL   /   No Comments
09
Nov
2014

LINQ 2 SQL STEPS 1. add new Item -> Linq To SQL Classes (.dbml) 2. open server explorer -> create new connection to the desired dataBase 3. drag-drop the tables you want to map to objects remark: you can also drag stored Procedures, views and more ... 4. add -> associations (create the connections between the tables - foreign keys) 5. use.

Read more

Entity Framework DataBase First

Posted by: RcBuilder  /   Category: ADO.NET / Entity Framework / SQL   /   No Comments
31
Oct
2014

Entity Framework DataBase First Create Entity Data Model * Add -> New Item -> (Data Tab) ADO.NET Entity Data Model (.edmx) -> set mame -> ok * in the wizard -> choose 'Generate from database' option * choose the connection string (web.config) or set a new one -> set the connection name -> next * choose the tables, views and.

Read more

Entity Framework Model First

Posted by: RcBuilder  /   Category: ADO.NET / Entity Framework / SQL / Stored Procedure   /   No Comments
30
Oct
2014

Entity Framework Model First Create model * Add -> New Item -> (Data Tab) ADO.NET Entity Data Model (.edmx) -> set mame -> ok * in the wizard -> choose 'Empty Model' option * by right clicking within the edmx area we can choose which elements to add to our model - Entity : add a new POCO object (Book,.

Read more

Entity Framework Data Annotations

Posted by: RcBuilder  /   Category: ADO.NET / Entity Framework / SQL / SQL Data Types   /   No Comments
30
Oct
2014

Entity Framework Data Annotations * Data Annotations are attributes that specify the following: 1. a validation rules 2. how the data is presented 3. how the data will be mapped to the database * add the following namespaces: - using System.ComponentModel.DataAnnotations; - using System.ComponentModel.DataAnnotations.Schema; * we can combine multi annotations within the same brackets e.g: * - schema attribute - set the property as PK.

Read more

Entity Framework Migrations

Posted by: RcBuilder  /   Category: ADO.NET / Entity Framework / SQL   /   No Comments
30
Oct
2014

Entity Framework Migrations Manual Migrations * usually works alongside the 'Code First' approach * Migrations are updates package for model structure changes (classes), we can see information about all the changes performed in each migration and we can roll back to a specific migration * open the Package Manager (PM) console - Tools -> Library Package Manager -> Package Manager Console * enable.

Read more

SQL COLLAPSE example

Posted by: RcBuilder  /   Category: SQL / SQL Data Types / SQL Function / Stored Procedure / System Function   /   No Comments
20
Oct
2014

SQL COLLAPSE - multiple rows to single value we can use the COLLAPSE sql function to concatenation values into a single string data declare @tbl2 table(Name nvarchar(10), Quantity int, CreatedDate smalldatetime) insert into @tbl2 SELECT 'Roby', 100, '2009-12-05' UNION ALL SELECT 'Avi', 40, '2011-11-05' UNION ALL SELECT 'Avi', 60, '2010-05-03' UNION ALL SELECT 'Avi', 15, '2009-06-11' UNION ALL SELECT 'Ron', 50, '2010-04-11' UNION ALL SELECT 'Roby', 77,.

Read more

SQL PIVOT

Posted by: RcBuilder  /   Category: DATES / DBA / SQL / SQL Data Types / SQL Function / Stored Procedure   /   No Comments
07
Oct
2014

SQL PIVOT creates an horizontal group by. MUST define the groups names. structure pivot(<aggregation function> FOR <group by column> IN (<group by column values>)) USING example 1: declare @tbl1 table(Name nvarchar(10), Quantity int) insert into @tbl1 SELECT 'Roby', 100 UNION ALL SELECT 'Avi', 40 UNION ALL SELECT 'Avi', 60 UNION ALL SELECT 'Avi', 15 UNION ALL SELECT 'Ron', 50 UNION ALL SELECT 'Roby', 77 UNION ALL SELECT 'Roby', 12 UNION.

Read more
  • פיתוח מערכות
  • פתרונות טכנולוגים
  • קידום אתרים
  • בניית אתרים