Programming with C#

Recently I did a bulk insert into a table from a CSV file and this data had trailing spaces. I didn't notice until some email addresses look like weren't valid. I read this article which explains when the data is trimed or not Since I had many columns to update I needed to find another solution to write/generate the update statements for "update table set column = rtrim(ltrim(column))" declare @table_name varchar(150) = 'dbo.Member' declare @stmt nvarchar(max) set @stmt = ( select '[' + c.name + '] = rtrim(ltrim([' + c.name + ']))' + ',' + char(10) + char(9) from dbo.syscolumns c inner join dbo.systypes st ON st.xusertype = c.xusertype inner join dbo.systypes bt ON bt.xusertype = c.xtype where c.id = object_id(@table_name,'U') and (bt.name = 'varchar' or bt.name = 'nvarchar') for xml path ('') ) if @stmt is not null begin set @stmt = 'update ' + @table_name + char(10) + ...

I needed a SQL statement which fills the null values from the second column of #T1 table with values from #T2(C1). There is no foreign key or match between the columns of those two tables. I found two approaches: 1. Using CTE create table #T1 (C1 varchar(10), T2C1 varchar(10)) create table #T2 (C1 varchar(10)) insert into #T1 values ('A1', '1') insert into #T1 values ('A2', null) insert into #T1 values ('A3', null) insert into #T1 values ('A4', '4') insert into #T1 values ('A5', null) insert into #T2 values ('a') insert into #T2 values ('b') ;with t2 as ( select C1, row_number() over (order by C1) as Index2 from #T2 ) ,t1 as ( select T2C1, row_number() over (order by C1) as Index1 from #T1 where T2C1 is null ) update t1 set t1.T2C1 = t2.C1 from t2 where t1.Index1 = t2.Index2 select * from #T1 drop table #T1 drop table #T2 2. With Derived Tables create table #T1 (C1 varchar(10), T2C1 va...

Scenario: a teacher schedules his courses on a random different dates in the near future, but he always know the start time. For sample in the current week he can schedule a course for Tuesday and Friday, at 08:00 AM. In a database there would be the following tables: 1. course (title, summary, ..) 2. c ourse_schedule(course_id, location, start_time) where data about that schedule is preserved( ie. location) 3. course_schedule_span(schedule_id, course_date) for storing the dates when the schedule spans The dates have always the same start time, I knew this before designing the database, so in the first time I let the time in the course_schedule_span table, in course_date column (ie. 31/12/2011 16:00). Later the teacher wanted to set and the end time so I decided to move the start time into course_schedule table and this new end time field to add it there also. The first attempt was to store the time in a varchar(5) column, this would suffice for storing like hh:mm values. Later I ch...

LINQ deferred execution is when you create the query, but this is not executed until you need it. So first you tell what you need and you'll get it when is needed by calling methods like ToList, First, Single, etc. var ctx = ... var query = from p in ctx.products where p.enabled == true && c.deleted == false select p; After calling query.ToList(), the LINQ creates the SQL, sends it to server, get the results and then creates the products list. query is an IQueryable<T> object and ToList creates an IList<T> object. An non-clustered SQL View acts pretty same like the LINQ non deferred execution. First you define the query, then you call it or use it in any other queries. CREATE VIEW vwProducts AS SELECT p.* FROM dbo.products p WHERE p.enabled = 1 AND p.deleted = 0 SELECT * FROM vwProducts p WHERE p.description like '%potatoes%' SQL Server will expand the View vwProducts when the last query is executed. So, how to see the first LINQ...

Having the tables "product" and "product_stock" I want to display for each product the comma-separated list of sizes and this list is ordered by a specific order. CREATE TABLE product( product_id int, code varchar(50) ) CREATE TABLE product_stock( product_stock_id int, size_order int , size varchar(20) , product_id int ) -- desired output Product_id Sizes In Stock 35 UK7, UK8, UK9, UK11, UK10.5, UK12, UK10 36 L, M, S, XL, XXL, XXXL In SQL this can be achieved with cursors, but everyone suggest not to use them. After some digging I found out about custom aggregates and they saved the day. In the MSDN article is an example which concatenates the strings and this I'm writing does the same, only it adds the ordering feature. The problem occurs with the ordering because SQL doesn't allow to use the "ORDER BY" clause in subqueries or if the "GROUP BY" exists, then the only columns allowed are the ones in gr...

Test: T - SQL: if ( null = null ) print 'null == null' else print 'null != null' C#: Console.WriteLine(null == null); Ce afiseaza fiecare? Si de ce?

CREATE TABLE dbo.Items ( ID int IDENTITY(1,1) NOT NULL, Name varchar(50) NOT NULL, CONSTRAINT PK_Items PRIMARY KEY (ID ASC) ) -- Ceva valori GO INSERT INTO Items VALUES ('X') INSERT INTO Items VALUES ('Y') INSERT INTO Items VALUES ('Z') -- O functie care returneaza itemurile care au id-ul mai mic decat @ItemID (cele anterioare unui anumit item) GO CREATE FUNCTION [dbo].[getPreviousItems] ( @ItemID int ) RETURNS @PreviousItemTab TABLE ( ID int, Name varchar(50) ) AS BEGIN INSERT INTO @PreviousItemTab SELECT * FROM dbo.Items AS i WHERE i.ID RETURN END Operatorul APPLY implica doi operanzi, primul operand (LEFT) actioneaza ca si input pentru cel de-al doilea (RIGHT). Pentru fiecare linie din rezultatul operandului LEFT se va evalua operandul RIGHT Operatorul APPLY e de doua tipuri: CROSS APPLY si OUTER APPLY. Rezultatul lui CROSS APPLY nu va include liniile pentru care LEFT returneaza NULL in urma evaluarii, iar OUTER APPLY va include si aceste linii...

Multe aplicatii ASP .Net au in spate si un server de baze de date. Ce tre’ sa faci atunci cand pagini cu diverse rapoarte se incarca tot mai greu de la o saptamana la alta, primesti erori de timeout tot mai des pe mail, iar clientii incep sa devina din ce in ce frustrati? Incepi sa gugalesti:). Presupun ca pe tabelele alea mari sunt deja construiti niste indecsi, iar indecsii au peste 1000 pagini (adica peste 8000 KB). O idee ar fi sa se reverifice cum sunt construiti indecsii. Daca raportul e construit pe baza unei interogari care ordoneaza randurile dupa col1 ASC si col2 DESC si raportul e cerut destul de des, atunci indexul ar trebuie sa fie construit la fel (adica dupa col1 ASC si col2 DESC). Daca indexul are un fill factor de 100% (sau 0%) si pe tabela se fac la fel de des si operatii de insert,update sau delete atunci ar trebui reconsiderata valoarea acestui fill factor la una sub 100%. Cum un raport returneaza si alte coloane in afara de cele folosite in clause WHERE sau la ORDE...