Sql和其他技巧随笔未完待续
1.insert into test(noimage)select no from Image 在一个表中添加另一个表的数据
2.DataTable dt = SqlHelper.ExecuteDataTable("select * from image inner join test on image.no =test.NoImage");内联查询
3.
select * from image inner join (select NoImage, NomCourt = stuff((select ',' +rtrim( NomCourt)+'('+CONVERT(varchar(100), DateDebut, 1)+' / '+CONVERT(varchar(100), DateFin, 1)+')' from Droits t where NoImage = Droits.NoImage for xml path('')) , 1 , 1 , '')
from Droits group by NoImage) as A on image.no = a.NoImage; ; 根据字段中相同的数据合并其他列,参考地址 http://www.cnblogs.com/hanmos/archive/2011/09/15/2177391.html
注:内联加子查询,rtim()去掉空格,convert()把日期转换为字符串(参考地址 http://www.jb51.net/article/41419.htm),stuff()合并数据
4.合并列之后,把里面的逗号换成<br>换行显示
<asp:LabelID="Label1"runat="server"Text='<%# DataBinder.Eval(Container.DataItem, "NomCourt").ToString().Replace(",","<br>") %>'>
</asp:Label></td>
5.将表中的部分查询结果,插入到表中
insert into Droits(NomCourt, NomLong, DateDebut, DateFin, Perime, NoImage) select NomCourt, NomLong, DateDebut, DateFin, Perime,3806 from Droits where NoImage=22
SqlHelper.ExecuteNonQuery(@"insert into Droits(NomCourt, NomLong, DateDebut, DateFin, Perime, NoImage)
select NomCourt, NomLong, DateDebut, DateFin, Perime,@numero from Droits where NoImage=@NoImageEtreCopie",
new SqlParameter("@numero", numero), new SqlParameter("@NoImageEtreCopie", NoImageEtreCopie));
6.有相同的数据就update否则就insert
SqlHelper.ExecuteNonQuery(@"if exists(select 1 from CheminDocument where NoImage=@NoImage)
begin
update CheminDocument
set NomDocument = @NomDocument,CheminDocument=@CheminDocument
where NoImage = @NoImage
end
else
begin
insert into CheminDocument(NoImage, NomDocument, CheminDocument)
values (@NoImage,@NomDocument,@CheminDocument)
end",
newSqlParameter("@NoImage", dataImage.No),
newSqlParameter("@NomDocument", Path.GetFileName(fileDocument.PostedFile.FileName)),
newSqlParameter("@CheminDocument", ConfigurationSettings.AppSettings["CheminRepertoireImage"] +
@"\" + Path.GetFileName(fileDocument.PostedFile.FileName))); //qi7775
7.前台调用后台变量,例如 public staticstring nomDocument 前台<%=nomDocument %>
8.<addkey="CheminRepertoireImage"value="C:\Programmation\PLC\PLC\DataImages"/>
9.scalar判断数据库是否有数据,有几个
int nombreDocument=(int)SqlHelper.ExecuteScalar("select count(*) from CheminDocument where NoImage=@NoImage", newSqlParameter("@NoImage", dataImage.No));