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));

 

转载于:https://www.cnblogs.com/lotoquebec/p/4561977.html