ASP.NET Web 开发之SQL语句

ExecuteScalar 返回结果集中第一行的第一列 常用于查询

例：object obj = db.ExecuteScalar(dbCommand);

if (obj == null || obj.ToString() == "")

{

return "";

}

return DateTime.Parse(obj.ToString()).ToString("yyyy-MM-dd HH:mm");

ExecuteReader 返回SqlDataReader对象 常用于实体类的绑定

ExecuteDataSet 返回DataSet数据表

ExecuteNonQuery 执行sql语句返回操作影响的行数

一、分页存储过程SQL2005以上版本

-- =============================================

-- Author:开优网络

-- Create date: 2009-07-22 12:41

-- Description:分页，用到了ROW_NUMBER()

-- =============================================

create PROCEDURE [dbo].[proc_SplitPage]

@tblName   varchar(255),       -- 表名

@strFields varchar(1000) = '*', -- 需要返回的列，默认*

@strOrder varchar(255)='',      -- 排序的字段名，必填

@strOrderType varchar(10)='ASC', -- 排序的方式,默认ASC

@PageSize   int = 10,          -- 页尺寸,默认10

@PageIndex int = 1,           -- 页码,默认1

@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)

AS

declare @strSQL   varchar(5000)

if @strWhere !=''

set @strWhere=' where '+@strWhere

set @strSQL=

'SELECT * FROM ('+

'SELECT ROW_NUMBER() OVER (ORDER BY '+@strOrder+' '+@strOrderType+') AS pos,'+@strFields+' '+

'FROM '+@tblName+' '+@strWhere+

') AS sp WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)

exec (@strSQL)

二、分页存储过程SQL2000

/****** Object:  StoredProcedure [dbo].[proc_SplitPage]    Script Date: 05/13/2011 14:17:06 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[proc_SplitPage]

 

 @tblName varchar(255),   -- 表名 

 @strFields varchar(255),   -- 显示字段名 

 @strOrder varchar(255),  -- 排序字段名 

 @strOrderType varchar(50),   -- 设置排序类型, asc || desc

 @PageSize int = 10,   -- 页尺寸 

 @PageIndex int = 1,   -- 页码 

 @strWhere varchar(1000) = ''  -- 查询条件 (注意: 不要加 where) 

AS 

 declare @strSQL varchar(6000)  -- 主语句 

 declare @strTmp varchar(100)   -- 临时变量 

 declare @strOrderTemp varchar(400)  -- 排序类型 

if @strOrderType = 'desc' 

 begin 

  set @strTmp = '<(select min' 

  set @strOrderTemp = ' order by [' + @strOrder +'] desc' 

 end 

else 

 begin 

  set @strTmp = '>(select max' 

  set @strOrderTemp = ' order by [' + @strOrder +'] asc' 

 end 

set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strFields + ' from [' 

 + @tblName + '] where [' + @strOrder + ']' + @strTmp + '([' 

 + @strOrder + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' 

 + @strOrder + '] from [' + @tblName + ']' + @strOrderTemp + ') as tblTmp)' 

 + @strOrderTemp 

if @strWhere != '' 

 set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strFields + ' from [' 

 + @tblName + '] where [' + @strOrder + ']' + @strTmp + '([' 

 + @strOrder + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' 

 + @strOrder + '] from [' + @tblName + '] where ' + @strWhere + ' ' 

 + @strOrderTemp + ') as tblTmp) and ' + @strWhere + ' ' + @strOrderTemp 

if @PageIndex = 1 

 begin 

  set @strTmp = '' 

  if @strWhere != '' 

  set @strTmp = ' where ' + @strWhere 

  set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strFields + ' from [' 

  + @tblName + ']' + @strTmp + ' ' + @strOrderTemp 

 end 

exec (@strSQL) 

GO

不采用存储过程分页方法

public DataSet GetList(string fileds, string order, string ordertype, int PageSize, int PageIndex, string strWhere)

{

// select top 每页显示的记录数 * from topic where id not in (select top （当前的页数-1）×每页显示的记录数 id from topic order by id desc)  order by id desc  

string sql = Xiaobin.Shop.Utility.Tool.Get_FenYeSQL("shop_news", fileds, order, ordertype, PageSize, PageIndex, strWhere);

       Database db = DatabaseFactory.CreateDatabase();

       DbCommand dbCommand = db.GetSqlStringCommand(sql);

       return db.ExecuteDataSet(dbCommand);

 }

三、上一件和下一件存储过程采用row()函数,适用于SQL2005

create procedure proc_PrevOrNextPro

@proid int,--商品id

@str nvarchar(20) --取的上一件或下一件，如果是prev是上一件,next下一件

as

begin

declare @rowid int 

select @rowid=rowid from(

select ROW_NUMBER() over (order by createdate desc)as rowid,* from shop_product)as a where a.id=@proid

if @str='prev'

set @rowid=@rowid-1

else

set @rowid=@rowid+1

select * from (

select ROW_NUMBER() over (order by createdate desc)as rowid,* from shop_product)as a where a.rowid=@rowid

end

cs中上一件

Model.Prodect m_prev=new DAL.ProductDAL().GetPrevNextProModel(model.id,"prev")

if(m_prev!=null)

{

hlprev.NavigateUrl="pro.aspx?id="+model.id

}

else

{

hlprev.visible=false;

}

下一件

Model.Prodect m_prev=new DAL.ProductDAL().GetPrevNextProModel(model.id,"next")

if(m_prev!=null)

{

hlnext.NavigateUrl="pro.aspx?id="+model.id

}

else

{

hlnext.visible=false;

}

在DAL层中ProductDAL.cs中加上获取上一件或下一件的方法

public Xiaobin.Shop.Model.Product GetPrevNextProModel(int proid, string str)

{

string proc = "proc_PrevOrNextPro";

Database db = DatabaseFactory.CreateDatabase();

DbCommand dbCommand = db.GetStoredProcCommand(proc);

db.AddInParameter(dbCommand, "proid", DbType.Int32, proid);

db.AddInParameter(dbCommand,"str",DbType.String,str);

Xiaobin.Shop.Model.Product model = null;

using (IDataReader dataReader = db.ExecuteReader(dbCommand))

{

if (dataReader.Read())

{

model = ReaderBind(dataReader);

}

}

return model;

}

第二种方法:

上一件 ExecuteScalar

int m_prev = new DAL.ProplDAL().GetPrevPro(x);

if (m_prev != 0)

{

hyPrve.NavigateUrl = "pro.aspx?id=" + m_prev;

}

else

{

hyPrve.Visible = false;

}

DAL层

下一件 ExecuteScalar

三、常用的SQL

(1) 数据记录筛选：

sql="select * from 数据表 where 字段名=字段值 order by 字段名 desc"

sql="select * from 数据表 where 字段名 like '%字段值%' order by 字段名 desc"

sql="select top 10 * from 数据表 where 字段名 order by 字段名 desc"

sql="select * from 数据表 where 字段名 in ('值1','值2','值3')"  //只读取

sql="select * from 数据表 where 字段名 between 值1 and 值2"//限制范围

(2) 更新数据记录：

sql="update 数据表 set 字段名=字段值 where 条件表达式"

sql="update 数据表 set 字段1=值1,字段2=值2 …… 字段n=值n where 条件表达式"

(3) 删除数据记录：

sql="delete from 数据表 where 条件表达式"

sql="delete from 数据表"  (将数据表所有记录删除)

DROP TABLE 数据表名称 (永久性删除一个数据表)

truncate table 表名 //清空数据表 id从1开始 mssql

ALTER TABLE cms_quanxian ALTER id COUNTER (1, 1)  //清空数据表 id从1开始  asccess

(4) 添加数据记录：

sql="insert into 数据表 (字段1,字段2,字段3 …) values (值1,值2,值3 …)"

sql="insert into 目标数据表 select * from 源数据表"  (把源数据表的记录添加到目标数据表)

(5) 数据记录统计函数：

AVG(字段名) 得出一个表格栏平均值

COUNT(字段名) 对数据行数的统计或对某一栏有值的数据行数统计

MAX(字段名) 取得一个表格栏最大的值

MIN(字段名) 取得一个表格栏最小的值

SUM(字段名) 把数据栏的值相加

GROUP BY(分组) 常用于和以上五种统计函数使用

SELECT ROUND(column_name,decimals) FROM table_name  //ROUND 四舍五入 ( 字段名，小数位)

引用以上函数的方法：

select sum(字段名) as 别名 from 数据表 where 条件表达式

例:

获取评分 

select AVG(pj) from shop_propl where proid=@proid

销售排行 

select top " + p + " proid,sum(quantity)as 销售量 from shop_orderdetails group by proid order by 销售量 desc

更新订单状态

update shop_order set state=@state where id=@id

获取购物总金额 

select sum(sendmoney + detailsmoney) from shop_order where state=5 and isdel=0 and username=@username

登录 用户名和密码、邮箱和密码 即通过用户名和密码获取实体类或用邮箱和密码获取实体类

select count(1) from shop_admin where username=@username and password=@password 

添加积分

update shop_user set interge=interge+@jf where username=@username

更新用户类型

update shop_user set type=@type where username=@username 

获取发贴量最大的一天 

select top 1 COUNT(1)as num from bbs_topic group by REPLACE(CONVERT(char(10),createdate,111),'/','-') order by num desc

按日期搜索

replace(CONVERT(char(10),createdate,111),'/','-')<='" + DateTime.Now.ToString("yyyy-MM-dd") + "'

获取最后登录的时间 

select top 1 createdate from bbs_log where username=@username and remark='登录' order by createdate desc

group by 分组查询用户发贴排行

select top 10 username,count(username)as num from bbs_topic group by username order by num desc

select top 10 username,(select count(1) from bbs_topic where username=u.username)as num from bbs_user u order by num desc

select top 10 caname,id,bh,(select count(1) from bbs_topic where cabh=c.bh)as num from bbs_category c where len(bh)=4 order by num desc

今天和昨天登录的人数记录

//今日  litToday.Text = ldao.CalcCount("replace(CONVERT(char(10),createdate,111),'/','-') = '" + DateTime.Now.ToString("yyyy-MM-dd") + "'").ToString();

//昨日  litYestoday.Text = ldao.CalcCount("replace(CONVERT(char(10),createdate,111),'/','-') = '" + DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + "'").ToString();

总访客人数：

litTotal.Text=ldao.CalcCount("").ToString();

临时插入自增列

SET IDENTITY_INSERT [dbo].[czrl_category] ON SET IDENTITY_INSERT [dbo].[czrl_category]

OFF

查找表中的重复行//where子查询，having为查询结果的分组操作

select * from tb_producttype where protname in(select protname from tb_producttype group by protname having count(*)>1)  

通过where子查询获取最大的id 内层的查询结果作为外层查询的比较条件

select * from goods where id=(select max(id) from goods);

对通过from子查询 内层的查询结果供外层再次查询，要加上" as 别名 " u代表外层的查询表，num代表别名

select top 10 username,(select count(1) from bbs_topic where username=u.username)as num from bbs_user u order by num desc

exists子查询，把外层查询结果代入到内层，看内层是否成立

select * from category where exists(select * from goods where good.pid=cagegory.id);

左连接查询 关系表 left join 左连接 right join 右连接  inner join 内连接即去掉左/右连接中不匹配的数据，只取相同的

select goods_id,goods.cat_id,cat_name,goods_name,shop_price from goods left join category on goods.cat_id=category.cat_id;

注：left join 连接表，on 连接条件 "goods left join category on goods.cat_id=category.cat_id" 查询出的就是一张完整的关系表。

goods.cat_id表示goods表中的cat_id字段 cat_name表示category表中的字段

distinct方法获取唯一不同的值

select distinct modulename FROM qx_qx;

mssql按时间查询

select * from orders where replace(CONVERT(char(10),riqi,111),'/','-') like '2008-09%'

replace(CONVERT(char(10),createdate,111),'/','-')<='" + DateTime.Now.ToString("yyyy-MM-dd")+ "'

获取类似的10数据 

select top 10 title from bbs_topic where like '%a%'

生成订单编号 

public string GetOrderBH()

        {

            string sql = "select max(orderbh) from shop_order where orderbh like '" + DateTime.Now.ToString("yyyyMMdd") + "%'";

            Database db = DatabaseFactory.CreateDatabase();

            object obj = db.ExecuteScalar(CommandType.Text, sql);

            if (obj != null && obj != DBNull.Value)

            {

                string tmp = obj.ToString().Substring(8);

                return DateTime.Now.ToString("yyyyMMdd") + (int.Parse(tmp) + 1).ToString("D5");

            }

            else

            {

                //当天没有订单

                return DateTime.Now.ToString("yyyyMMdd") + "00001";

            }

        }

通过pid生成bh

public string GetBH(string pid)

{

string sql = "select max(bh) from bbs_category where pid=@pid";

Database db = DatabaseFactory.CreateDatabase();

DbCommand dbCommand = db.GetSqlStringCommand(sql);

db.AddInParameter(dbCommand, "pid", DbType.Int32, pid);

object bh = db.ExecuteScalar(dbCommand);

if (bh == null || bh.ToString() == "")

{

if (pid == "0")

{

return "01";

}

else

{

return GetModel(int.Parse(pid)).bh + "01";

}

}

else

{

string tmp = bh.ToString();

string front = tmp.Substring(0, tmp.Length - 2);

string last = tmp.Substring(tmp.Length - 2);

return front + (int.Parse(last) + 1).ToString("D2");

}

}

上传数据库

sql2008 → 任务 → 生成脚本 → 选择要生成的数据库 → 下一步 → 为服务器的编写脚本和服务器SQL版本一样 → 架构限定对象名称 → false → 下一步 → 全选 → 存储过程 → 表 → 下一步 → 将脚本保存到新建查询窗口 → 完成 → 系统会生成一些查询脚本 → 断开连接 → 连接到远程服务器 → 删除第一句USE[xiaobinshop] → 验证一下 → 命令成功后 → 执行 → 成功 → 在远程数据上导入数据 → 源数据库 → 本机中的xiaobinshop 源10.0 → 下一步→选择目标 → 远程数据 → 用户名\密码  → 数据库存 → 远程数据 → 复制一个或多个视图的数据 → 所有表 → 编辑映射：启用标识插入 →  如果有原来相同的表或存储过程再勾选删除现有目标表的行 → 目标架构名称 → 远程数据的名称 → 修改web.config文件

网站发布到独立的服务器

附件  →  远程桌面连接  →  服务器

创建iis  →  ip为服务器的ip 端口为80  →  主机头 → 域名 → 读取 → 运行脚本 → 在文件夹上 → 属性 → 安全 → 添加 NETWORK_SERICE 和来宾帐号 → IUSR_INOP → 应用 

再到域名中解析一下 → ip为服务器ip

安装 FileZilla Server → user → Add → 文件夹名称 → <none> → OK → 开启Enable accout 和 password → shared folder → Add → 找到文件夹 → 开启 → Read、Write、Delete、Append 、Create 、Delete 、List  +subirs → ok

在本地从FTP上传到服务器上，上传网站再配置数据库 

本机上部置网站、花生壳

下载花生壳 → 注册 → 下载花生壳动态域名正式版

注册后会有一个免费的域名 → 免费域名 → 域名管理

把网站发布到本地的IIS上，端口为80

打开windows防火墙的80端口

子目录网站的发布

1、打开Web.config中的异常处理on

2、发布网站

3、上传FTP 除ASP.NET 、bin文件夹和Globl.asax和Web.config文件

4、把bin文件夹中的dll上传到网站根目录中的bin中，

5、整合web.config  Globl.asax 和error.html文件

    web.config修改

        //添加子目录

    //添加数据库连接