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 Advantages & DisAdvantages

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

Entity Framework Advantages & DisAdvantages * EF saves a lot of time of the object mapping, the writing of the stored Procedures, views, functions and more ... * EF is more convenient for the developers and easier to work with * EF mapped automatically therefore, any structured radical change can be implemented faster than searching all the Stored.

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

Trusted Connection String

Posted by: RcBuilder  /   Category: configuration / SQL   /   No Comments
01
Oct
2014

Trusted Connection String <connectionStrings> <add name="ConnectionString" connectionString="Data Source=.;Initial Catalog=SAR_TEST;Trusted_Connection=True;" providerName="System.Data.SqlClient"/> </connectionStrings> OR <connectionStrings> <add name="ConnectionString" connectionString="Server=.;Database=SAR_TEST;Trusted_Connection=True;".

Read more

SQL COUNT NULLIF

Posted by: RcBuilder  /   Category: DBA / SQL / SQL Function / Stored Procedure / System Function   /   No Comments
28
Sep
2014

SQL COUNT NULLIF // nullif return null if the condition is true // use this technique to count multiple counters in a single group by clause declare @tbl table(Id int, isOK bit, Grade tinyint) insert into @tbl select 1, 1, 50 union all select 1, 1, 75 union all select 4, 0, 25 union all select 6, 1, 54 union all select 6, 0,.

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