linq to sql + coolite 实战练习项目 手记
春节比较闲,刚学了一点linq to sql,打算做个小财务系统练下手
界面用coolite,用户体验比较好,只看了效果,刚把控件下载下来,一点不会,边做边学吧
系统就是做给外行用的,表面功夫是一定要到位滴
我直接把linq to sql生成的代码作为实体
下面是项目中遇到的问题,不断更新
问题一:
在做用户登录判断密码时,如果数据库里没有该记录(输入错误)
if (fdc.UserInfo.Single(c => c.UserName == name && c.PassWord == psw) != null)
此时报错:序列不包含任何元素
应该用SingleOrDefault
问题二: 这个可把我搞死了, ??0 这样从NULL转0 生成的SQL有问题
//计算多列的和,需要把NULL的列转为0
var test = (from c in tt.cost
select new
{
dress = (c.living.HasValue ? c.living : 0) + (c.dinner.HasValue ? c.dinner : 0)
}).Sum(v => v.dress);
var test = (from c in tt.cost
select new
{
dress = (c.living.HasValue ? c.living : 0) + (c.dinner.HasValue ? c.dinner : 0)
}).Sum(v => v.dress);
//这样写也没生成我理想的 null(XX,0)
SELECT SUM([t1].[value]) AS [value]
FROM (
SELECT (
(CASE
WHEN [t0].[living] IS NOT NULL THEN [t0].[living]
ELSE @p0
END)) + (
(CASE
WHEN [t0].[dinner] IS NOT NULL THEN [t0].[dinner]
ELSE @p1
END)) + (
(CASE
WHEN [t0].[sociality] IS NOT NULL THEN [t0].[sociality]
ELSE @p2
END)) AS [value]
FROM [dbo].[cost] AS [t0]
) AS [t1]
-- @p0: Input Float (Size = 0; Prec = 0; Scale = 0) [0]
-- @p1: Input Float (Size = 0; Prec = 0; Scale = 0) [0]
-- @p2: Input Float (Size = 0; Prec = 0; Scale = 0) [0]
SELECT SUM([t1].[value]) AS [value]
FROM (
SELECT (
(CASE
WHEN [t0].[living] IS NOT NULL THEN [t0].[living]
ELSE @p0
END)) + (
(CASE
WHEN [t0].[dinner] IS NOT NULL THEN [t0].[dinner]
ELSE @p1
END)) + (
(CASE
WHEN [t0].[sociality] IS NOT NULL THEN [t0].[sociality]
ELSE @p2
END)) AS [value]
FROM [dbo].[cost] AS [t0]
) AS [t1]
-- @p0: Input Float (Size = 0; Prec = 0; Scale = 0) [0]
-- @p1: Input Float (Size = 0; Prec = 0; Scale = 0) [0]
-- @p2: Input Float (Size = 0; Prec = 0; Scale = 0) [0]
问题三
- Convert.ToInt32 参数为 null 时,返回 0;
- int.Parse 参数为 null 时,抛出异常。
- Convert.ToInt32 参数为 "" 时,抛出异常;
- int.Parse 参数为 "" 时,抛出异常。
- Convert.ToInt32 可以转换的类型较多;
- int.Parse 只能转换数字类型的字符串。
总是记不住
问题四
由于Session用的stateServer模式,需要对象可序列化,linq to sql的实体序列化有问题,晕了,待解决
Coolite很强大,慢慢熟悉中
问题五
我后台动态的创建一些控件,却怎么也findControl到,原来是每次请求动态控件都会丢失
所以不能创建控件的方法放在 !isPostback里面
问题六:
SQL列转行操作
代码
declare @sql varchar(8000)
set @sql = 'select b.username as 名字 '
select @sql = @sql + ' , sum(case costtypeid when '+cast(id as varchar)+' then costmoney else 0 end) ['+typename+']'
from (select id,typename from costtype) as a
set @sql = @sql + ',date from cost a left join userinfo b on a.userinfoid=b.id group by username,date'
print @sql
exec(@sql)
set @sql = 'select b.username as 名字 '
select @sql = @sql + ' , sum(case costtypeid when '+cast(id as varchar)+' then costmoney else 0 end) ['+typename+']'
from (select id,typename from costtype) as a
set @sql = @sql + ',date from cost a left join userinfo b on a.userinfoid=b.id group by username,date'
print @sql
exec(@sql)