【知识碎片】SQL篇

 

42、SQL Server 导入 MDF LDF文件

EXEC  sp_attach_db  @dbname  =  'OA',     
@filename1  =  'C:\OA.mdf',     
@filename2  =  'C:\OA_log.ldf'

 

40、 循环更新

  declare @temp int
  set @temp=1
  while @temp<21
  begin
  --insert into Person (TNmae,TXingBie,Tlike) values('name001',1,'lpay')
  update Person set TNmae='name'+CONVERT(nvarchar, @temp)
  where ID=@temp
  set @temp=@temp+1
  end

 

39、 MSSQL 时间转换

SELECT  REPLACE(CONVERT(varchar, Date, 111 ),'/','-') date
  FROM Logs
  where ISNULL(SiteName,'')>''

 

38、oracle 转换成时间

  to_date('2016/4/21 9:19:05', 'yyyy-mm-dd hh24:mi:ss'),

 

 36、oracle nvl（） 等同于 sql server isnull()

NVL(num, 0)

 

35、SQL,根据不同条件拼接不同SQL，非if拼接 改为SQL where形式

（参数=0）or(参数=1 and 其他条件)or(参数=2 and 其他条件)

 

 34 oracle连接字符串

<！--<configuration>-->
  <connectionStrings>
    <!--<add name="connStr" connectionString="Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.68)(PORT = 1521)))(CONNECT_DATA =(SID =orcl)(SERVER =geothermal)));Integrated Security=no;User ID=geothermal;Password=geothermal;Unicode=True;Max Pool Size=75; Min Pool Size=5" providerName="System.Data.OracleClient"/>-->
    <add name="connStr" connectionString="Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.122)(PORT = 1521)))(CONNECT_DATA =(SID =orcldb)(SERVER =orcname)));Integrated Security=no;User ID=username;Password=pwd;Unicode=True;Max Pool Size=75; Min Pool Size=5" providerName="System.Data.OracleClient" />
  </connectionStrings>

 

33、插入时返回自增ID

--执行这个SQL,就能查出来刚插入记录对应的自增列的值
insert into mytable values('李四')
select @@identity  --不推荐用这个  用scope_identity() 这个是区分作用域的  而上一个不区分只返回最后一个，微软的EF用的也是这个方法


insert into tname(name) output
inserted.ID  --inserted触发器
values('dfdf')

 

32、判断data中 row中某个字段是否为空

userInfo.UserName = row["UserName"] != DBNull.Value ? row["UserName"].ToString() : string.Empty;

 

 31、SQLServer 辅助功能

set statistics time on 
-- 设置SQLSever 显示详细执行消息
--CPU 时间 = 0 毫秒，占用时间 = 0 毫秒

Ctrl+L 对比两个语句执行所消耗的不同

 30、创建临时表备份表

create table tablename1 as select  * from tablename2  where 1=1;--如果是1=2是指复制表结构，不复制数据，1=1可不加
 select * into test2 from PersonList

 

29、存储过程

--创建存储过程
create proc trim
    @str1 varchar(10)
as
begin
    --begin end内 存储过程内容
    select LTRIM(RTRIM(@str1))
end
--执行存储过程'abc'
exec trim '  abc  '

--编辑存储过程,查询表中的总数据,及当前页的数据
--pageindex,pagesize
alter proc GetPageList
    @pageIndex int,
    @pageSize int,
    @rowsCount int output
as
begin
    SET NOCOUNT ON;//不显示：（2行受到影响）
    select @rowsCount=COUNT(*) from StudentInfo where IsDelete=0
    
    select * from 
    (select *,ROW_NUMBER() over(order by sid desc) as rowIndex
    from StudentInfo where IsDelete=0) as t1
    where rowindex between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize
end
View Code

 

调用

declare @temp int
exec GetPageList 1,2,@temp output
print @temp

 

C#调用  和正常执行一样，指定名称和类型即可 

//指定命令类型为存储过程
cmd.CommandType = CommandType.StoredProcedure;

 

 28、SQL取diff

DATEDIFF(DAY, getdate(), EndTime) EndDayNum

 

 

 27、判断是否为空

select * from 表 where isnull(字段,'')=''

CASE WHEN ISNULL(a.RemarkName,'')='' THEN b.NickName ELSE a.RemarkName END DispName

 

26、Oracle虚拟表

--dual oracle 虚表
select rownum, t3.*
  from (select count(*)
          from (select *
                  from TRIPINFO t
                 where t.starttime between 20130905001339 and 20130905202651) t2
         group by t2.starthour
         order by t2.starthour) t3
  from dual
View Code

 

25、 查询数据库表的总数

--SQL Server 查询当前数据库表的总数
select count(*) as TableCount 
from sysobjects
where type='u' and status>=0

 

 24、MySql字符串拼接

不能直接  ' '+' '  需要用concat(' ',' ')

UPDATE p_exp SET expdoc=REPLACE(expdoc,'/static/resources/img',CONCAT('/static/resources/img/',expcode))

 

这段代码  是更新expdoc字段，将expdoc字段内的‘/static/resources/img’ 替换成‘/static/resources/img’+expcode。

结果： /static/resources/img/20150909

 

23、大小写转换

LOWER('adc')  --大写转小写,sqlserver

UPPER（'text') --小写转大写,sqlserver

UCASE('adc')  --大写转小写,其他

LCASE（'text') --小写转大写,其他

SELECT LCASE(column_name) FROM table_name;

 

21、事务

-- tran 是TRANSACTION  简写，没区别
begin try
    begin tran--设置反悔点,开启事务
    delete from UserInfo where UserId>5
    delete from ClassInfo
    commit tran--不反悔,提交事务
end try
begin catch
    rollback tran--反悔啦,回滚事务
end catch

--也可以不等保存提交回滚，直接回滚，适合调试，设置执行以下，看看报错不
begin tran--设置反悔点,开启事务
    delete from UserInfo where UserId>5
    delete from ClassInfo
rollback tran--反悔啦,回滚事务 没有结果
View Code

20、try catch

--异常处理
begin try
    delete from ClassInfo
end try
begin catch
    print @@error
end catch
View Code

 

 

19、循环语句 if/while

--选择语句
declare @id int
set @id=10
if @id>5
begin
    --满足条件时,执行如下代码
    print 'ok'
end
else
begin
    --不满足条件时,执行如下代码
    print 'no'
end

--循环
declare @id int
set @id=1
while @id<10
begin
    print @id
    set @id=@id+1
end

--输出1-10之间的所有偶数
declare @num int
set @num=1
while @num<11
begin
    if @num%2=0
    begin
        print @num
    end
    set @num=@num+1
end
View Code

 

18、变量

declare @name nvarchar(10)--声明变量，MySQL oracle于此有所不同
set @name='武大头帖'--赋值
print @name--使用输出

--全局变量：使用双 @ 符号
--@@version --数据库版本
--@@identity --进行插入后调用，返回最亲的标识值
insert into ClassInfo values('四不像123');
select @@IDENTITY--最近的insert语句的标识
--@@servername --服务器名称
--@@error --返回执行的上一个 Transact-SQL 语句的错误号，如果没有错误则返回0
--@rowcount --返回受上一语句影响的行数
View Code

 

17、case when

/*判不等语法：
    case 
        when 条件1 then ...
        when 条件2 then ...
        when 条件3 then ...
        else ...
    end*/
//列转行
select sName 姓名,
--如果当前的STitle的值是"语文",则输出ScoreValue
max(case sTitle when '语文' then scoreValue end) 语文,
min(case when sTitle='数学' then scoreValue end) 数学,
sum(case sTitle when '英语' then scoreValue end) 英语
 from Student_Score
 group by sName
View Code

 

15、视图

--创建视图
create view VStudent_Class--alter
as
select StudentInfo.*,ClassInfo.cTitle from StudentInfo
inner join ClassInfo on StudentInfo.cid=ClassInfo.cId
--视图中存储的是select语句,而不是结果集数据
select * from VStudent_Class
where IsDelete=0 and cTitle='白虎'
--删除
drop view VStudent_Class
View Code

 

14、SQLhelper

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace King.CMS.DAL
{
    public class SQLHelper
    {
        private static readonly string connString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
        public static DataTable GetTable(string sql, CommandType type, params SqlParameter[] pars)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                using (SqlDataAdapter apter = new SqlDataAdapter(sql, conn))
                {
                    apter.SelectCommand.CommandType = type;
                    if (pars != null)
                    {
                        apter.SelectCommand.Parameters.AddRange(pars);
                    }
                    DataTable da = new DataTable();
                    apter.Fill(da);
                    return da;
                }
            }
        }
        public static int ExecuteNonquery(string sql, CommandType type, params SqlParameter[] pars)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandType = type;
                    if (pars != null)
                    {
                        cmd.Parameters.AddRange(pars);
                    }
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }

        }
        public static object ExecuteScalare(string sql, CommandType type, params SqlParameter[] pars)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandType = type;
                    if (pars != null)
                    {
                        cmd.Parameters.AddRange(pars);
                    }
                    conn.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }

    }
}
View Code

 

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace t2_StudentInfo
{
    public static partial class SqlHelper
    {
        private static string connStr = ConfigurationManager.ConnectionStrings["dbtest"].ConnectionString;

        public static int ExecuteNonQuery(string sql,params SqlParameter[] ps)
        {
            using (SqlConnection conn=new SqlConnection(connStr))
            {
                SqlCommand cmd=new SqlCommand(sql,conn);
                cmd.Parameters.AddRange(ps);
                
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
        }

        public static object ExecuteScalar(string sql, params SqlParameter[] ps)
        {
            using (SqlConnection conn=new SqlConnection(connStr))
            {
                SqlCommand cmd=new SqlCommand(sql,conn);
                cmd.Parameters.AddRange(ps);

                conn.Open();
                return cmd.ExecuteScalar();
            }
        }

        public static DataTable ExecuteTable(string sql,params SqlParameter[] ps)
        {
            using (SqlConnection conn=new SqlConnection(connStr))
            {
                SqlDataAdapter adapter=new SqlDataAdapter(sql,conn);
                //用于进行select操作，可以通过SelectCommand属性获取此操作的SqlCommand对象
                adapter.SelectCommand.Parameters.AddRange(ps);

                DataTable dt=new DataTable();
                adapter.Fill(dt);

                return dt;
            }
        }

        public static SqlDataReader ExecuteReader(string sql,params SqlParameter[] ps)
        {
            SqlConnection conn=new SqlConnection(connStr);
            SqlCommand cmd=new SqlCommand(sql,conn);
            cmd.Parameters.AddRange(ps);

            conn.Open();

            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
    }
}
View Code

传参调用

string sql ="";
            if (string.IsNullOrEmpty(label1.Text))
            {
                //添加
                sql = "insert into studentinfo(sname,sgender,sbirthday,cid) values(@name,@gender,@birthday,@cid)";
            }
            else
            {
                //修改
                sql = "update studentinfo set sname=@name,sgender=@gender,sbirthday=@birthday,cid=@cid where sid=" +label1.Text;
            }

            SqlParameter[] ps =
            {
                new SqlParameter("@name",textBox1.Text), 
                new SqlParameter("@gender",radioButton1.Checked), 
                new SqlParameter("@birthday",dtpBirthday.Value), 
                new SqlParameter("@cid",cboClassInfo.SelectedValue), 
            };
            int result=SqlHelper.ExecuteNonQuery(sql, ps);
            if (result > 0)
            {
                FreshForm();
                this.Close();
            }
            else
            {
                MessageBox.Show("保存失败");
            }
View Code

 2019年8月4日

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
/// <summary>
/// 数据库基本操作类
/// </summary>
public class SQLHelper
{
    //数据库连接字符串
    private string connSTR;

    //数据库连接对象
    private SqlConnection myConnection;

    //数据库操作命令对象
    private SqlCommand myCommand;

    /// <summary>
    /// 构造函数，创建连接
    /// </summary>
    public SQLHelper()
    {
        connSTR = ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString;
        myConnection = new SqlConnection(connSTR);
    }

    #region 执行带参数的sql语句（插入、删除、修改）,返回-1表示执行失败

    public int ExcuSqlWithPara(string cmdText, SqlParameter[] para)
    {
        //创建Command
        myCommand = new SqlCommand(cmdText, myConnection);
       
        //传递参数
        for (int i = 0; i < para.Length; i++)
        {
            myCommand.Parameters.Add(para[i]);
        }

        //定义返回值
        int nResult = -1;

        try
        {
            //打开链接
            myConnection.Open();

            //执行SQL语句
            nResult = myCommand.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            //抛出异常
            throw new Exception(ex.Message, ex);
        }
        finally
        {   //关闭链接
            myConnection.Close();
        }
        //返回nResult
        return nResult;
    }
    #endregion

    #region  执行带参数的sql语句（select语句），返回数据流
    public SqlDataReader GetDRWithPara(string cmdText, SqlParameter[] para)
    {
        //创建Command
        myCommand = new SqlCommand(cmdText, myConnection);
        for (int i = 0; i < para.Length; i++)
        {
            myCommand.Parameters.Add(para[i]);
        }

        ///定义返回值
        SqlDataReader dr = null;
        try
        {
            ///打开链接
            myConnection.Open();
            ///执行SQL语句
            dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (SqlException ex)
        {
            ///抛出异常
            throw new Exception(ex.Message, ex);
        }
        return dr;
    }
    #endregion

    #region  执行带参数的sql语句（select语句），返回数据表
    /// <summary>
    /// 执行带参数的sql语句（select语句），返回数据表
    /// </summary>
    /// <param name="cmdText">带参数的SQL语句</param>
    /// <param name="para">参数列表</param>
    public DataTable GetDTWithPara(string cmdText, SqlParameter[] para)
    {
        //创建Command
        myCommand = new SqlCommand(cmdText, myConnection);
        for (int i = 0; i < para.Length; i++)
        {
            myCommand.Parameters.Add(para[i]);
        }
        SqlDataAdapter da = new SqlDataAdapter();
        DataSet ds = new DataSet();
        try
        {
            ///打开链接
            myConnection.Open();
            ///执行SQL语句
            da.SelectCommand = myCommand;
            da.Fill(ds);
        }
        catch (SqlException ex)
        {
            ///抛出异常
            throw new Exception(ex.Message, ex);
        }
        //返回dr时不能关闭连接
        finally
        {   ///关闭链接
            myConnection.Close();
        }
        //返回nResult
        return ds.Tables[0];
    }
    #endregion

    #region 执行不带参数的sql语句（select语句），返回数据表
    public DataTable GetDataTable(string cmdText)
    {
        ///定义返回值
        DataTable dt = null;
        try
        {
            myConnection.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmdText, myConnection);
            DataSet ds = new DataSet();
            da.Fill(ds, "Table1");
            dt = ds.Tables["Table1"];
            da.Dispose();

        }
        catch (SqlException ex)
        {
            throw new Exception(ex.Message, ex);
        }
        finally
        {
            myConnection.Close();

        }
        return dt;
    }
    #endregion

   
}
View Code

 

13、cast 函数

convert  cast  convert就是比cast多了第三个参数，定义格式的，且都是西方的，我们根本用不到，就当做一样就可以了

elect cast(89.000000 as decimal(4,1))  --结果 89.0

select convert(decimal(4,1),89.000000) --结果 89.0

数字与字符串拼接的时候会用到

select cast(1 as char(1))+'1'    --错误demo select 1+'1'

 

12、插入时从另一个表查询值

Insert into S_Citytest(CityName) Select CityName from  S_City

 

11、开窗函数 OVER

--将统计信息分布到行中

求科目一的平均分

错误

Select ScorrInfo.*, avg(scorrvalue) from ScoreInfo where subId=1

 

正确

Select ScorrInfo.*, avg(scorrvalue) over() from ScoreInfo where subId=1

效果

 

10、事务实例

BEGIN TRY  
    BEGIN TRANSACTION  --开始事务
    DECLARE @myMemberID NVARCHAR(36) ,--用户ID
        @myBizID NVARCHAR(36) ,
        @myBizType NVARCHAR(36) ,
        @myZanNum BIGINT

 --业务ID            
    SET @myMemberID = '773415785c964cf89ad40528228f111b'
    SET @myBizID = 'B6C0738D-CDDC-4361-964B-870FAA795FD2'
    SET @myBizType = '0601'
--根据点赞状态更新主表点赞数量
    UPDATE  [D_Activity]
    SET     ZanNum = CASE WHEN ( SELECT IsDel
                                 FROM   dbo.D_MemberAdmire
                                 WHERE  MemberID = @myMemberID
                                        AND BizID = @myBizID
                               ) = 0 THEN ZanNum - 1--记录为0,-1
                          ELSE ISNULL(ZanNum + 1, 1)
                     END --没有记录记录为1,+1
    WHERE   ID = @myBizID    
--如果存在点赞记录更新,否者插入              
    IF EXISTS ( SELECT  1
                FROM    D_MemberAdmire
                WHERE   MemberID = @myMemberID
                        AND BizID = @myBizID ) 
        UPDATE  dbo.D_MemberAdmire
        SET     IsDel = CASE WHEN IsDel = 1 THEN 0
                             ELSE 1
                        END--1变0,0变1
        WHERE   MemberID = @myMemberID
                AND BizID = @myBizID;     

                                         
    ELSE 
        INSERT  INTO D_MemberAdmire
                ( ID ,
                  BizType ,
                  BizID ,
                  MemberID ,
                  Status ,
                  IsDel
                    
                )
        VALUES  ( NEWID() ,
                  @myBizType ,
                  @myBizID ,
                  @myMemberID ,
,
 
                )
    COMMIT TRANSACTION --提交事务
END TRY
BEGIN CATCH --抓取异常
    ROLLBACK TRANSACTION --回滚事务
END CATCH
View Code

 

8、项目实例 声明包 创建存储过程，调用

--声明包
create or replace package pkg_region_to_region
as
 type  type_cursor is ref cursor;
 procedure read_rows (header varchar2, result out type_cursor);
end pkg_region_to_region;


--创建存储过程
create or replace package body pkg_region_to_region
as
 procedure read_rows(header varchar2, result out type_cursor)
 is
  sqlText varchar2(5000);
 begin
   open result for
   select  * from (
select count(*), br2.name, br2.code,header startcode
  from (select * 
          from (select R.name      startname,
                       R.code      startcode,
                       B.stationid startstationid
                  from Region R
                  left join BASECELLSTATION B

                    on B.region = R.code) br
          left join (select t.*, t.DESTINATIONSTATION stationid
                      from TRIPINFO t) TR
            on tr.stationid = br.startstationid
         where startcode = header
         order by startcode) TT1
  left join (select R.name name, R.code code, B.stationid stationid
               from Region R
               left join BASECELLSTATION B

                 on B.region = R.code) br2
    on br2.stationid = TT1.ORIGINSTATION
 GROUP by br2.code, br2.name
 order by br2.code);
  --dbms_output.put_line(sqlText);
   --sqlText;
 end read_rows;
end pkg_region_to_region;


--调用存储过程
var result refcursor
exec pkg_region_to_region.read_rows('1',:result);
print result
View Code

 

1.连接池，知道原理就好，没办法控制

close()之后把连接放到连接池，并不是真正关闭，然后再次连接的时候，数据库直接去连接池中取这个连接，效率大大提高，sqlserver默认是开始的，如果加“looping=false”,就手动关闭，不是特殊需要，不要关闭

2.参数化SQL语句，防止SQL注入

//using 释放资源 
using (SqlConnection conn = new SqlConnection("server=.;database=dbtest;uid=sa;pwd=123"))
            {
                string sql = "insert into userinfo values(@name,'202cb962ac59075b964b07152d234b70')";

                SqlCommand cmd = new SqlCommand(sql, conn);

                cmd.Parameters.Add(new SqlParameter("@name", textBox1.Text));

                conn.Open();
                int i = cmd.ExecuteNonQuery();
                MessageBox.Show(i.ToString());
            }
View Code

 

 创建存储过程

create proc trim
    @str1 varchar(10)
as
begin
    select LTRIM(RTRIM(@str1))
end
--'abc'


--trim 定义的存储过程名称     @str1  参数  


--执行存储过程
exec trim '  abc  '

 

创建存储过程

--编写存储过程,查询表中的总数据,及当前页的数据
--pageindex,pagesize
alter proc GetPageList
    @pageIndex int,
    @pageSize int,
    @rowsCount int output
as
begin
    SET NOCOUNT ON;
    select @rowsCount=COUNT(*) from StudentInfo where IsDelete=0
    
    select * from 
    (select *,ROW_NUMBER() over(order by sid desc) as rowIndex
    from StudentInfo where IsDelete=0) as t1
    where rowindex between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize
end

declare @temp int
exec GetPageList 1,2,@temp output
print @temp
View Code

 

 

 

调用存储过程

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Windows.Forms.VisualStyles;

namespace t1_ProcTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private int pageIndex,pageSize;
        private void Form1_Load(object sender, EventArgs e)
        {
            pageIndex = 1;//设置默认是第一页
            pageSize = 3;
            LoadList();
        }

        private void LoadList()
        {
            string sql = "GetPageList";//存储过程的名称

            SqlParameter pCount = new SqlParameter("@rowsCount", SqlDbType.Int);
            pCount.Direction = ParameterDirection.Output;//将参数设置为输出

            using (SqlConnection conn = new SqlConnection("server=.;database=dbtest;uid=sa;pwd=123"))
            {
                SqlCommand cmd=new SqlCommand(sql,conn);
                //指定命令类型为存储过程
                cmd.CommandType = CommandType.StoredProcedure;

                //根据存储过程来构造参数
                SqlParameter pIndex=new SqlParameter("@pageIndex",pageIndex);
                SqlParameter pSize = new SqlParameter("@pageSize",pageSize);
                
                //为cmd添加参数
                cmd.Parameters.Add(pIndex);
                cmd.Parameters.Add(pSize);
                cmd.Parameters.Add(pCount);

                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                List<StudentInfo> list=new List<StudentInfo>();
                while (reader.Read())
                {
                    list.Add(new StudentInfo()
                    {
                        Sid = Convert.ToInt32(reader["sid"]),
                        SName = reader["sname"].ToString()
                    });
                }

                dataGridView1.DataSource = list;
                
            }
            //当整个操作执行完成后，连接关闭了，再去获取参数的返回值
            txtCount.Text = pCount.Value.ToString();
        }

        private void 上一页ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            pageIndex--;

            if (pageIndex < 1)
            {
                pageIndex = 1;
            }

            LoadList();
        }

        private void 下一页ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            pageIndex++;

            int rowsCount = int.Parse(txtCount.Text);
            //1.1
            //计算总页数
            int pageCount = (int) Math.Ceiling(rowsCount*1.0/pageSize);
            //修正页索引，不能超出总页数
            if (pageIndex > pageCount)
            {
                pageIndex = pageCount;
            }

            LoadList();
        }
    }
}
View Code

 

分页存储过程 

GO
/****** Object:  StoredProcedure [dbo].[execByPage]    Script Date: 2015/9/9 9:55:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[execByPage]
      @sqlQuery NVARCHAR(MAX), --//输入参数：SQL检索语句或表名
      @pageSize INT, --//输入参数：每页显示记录条数
      @pageIndex INT, --//输入参数：当前页码
      @order NVARCHAR(100)='' --排序字段 ：例如ordercol desc
AS
      SET NOCOUNT ON
      SET ANSI_WARNINGS OFF
      
      DECLARE @tmpTableName VARCHAR(50)

      SET @tmpTableName = '##TB1516_' + REPLACE(CAST(NEWID() AS VARCHAR(36)),
                                                '-', '') --//生成随机临时表名称
      IF ( @order != '' )
         SET @order = ' order by ' + @order

      SET @sqlQuery = 'select *,IDENTITY(numeric,1,1) as ID1516 into ' +
          @tmpTableName + ' from ( ' + @sqlQuery + ') t' + @order
          print(@sqlQuery);
      EXEC sp_executesql @sqlQuery --//建立并初始化临时表数据

      DECLARE @indexStart VARCHAR(20),
              @indexEnd VARCHAR(20)
      SET @indexStart = CAST(( ( @pageIndex - 1 ) * @pageSize + 1 ) AS VARCHAR(20)) --//数据起始行ID
      SET @indexEnd = CAST(( @pageIndex * @pageSize ) AS VARCHAR(20)) --//数据结束行ID

      EXEC ('select * from ' + @tmpTableName + ' where ID1516 between ' + @indexStart + ' and ' + @indexEnd ) --//检索该页数据
      EXEC('select (case when max(ID1516)>0  then max(ID1516) else 0 end) as recordCount from ' + @tmpTableName) --//提取总条数
      EXEC('drop table ' + @tmpTableName) --//删除临时表


go

ALTER PROC [dbo].[execByPageList]
      @sqlQuery NVARCHAR(MAX), --//输入参数：SQL检索语句或表名
      @pageSize INT, --//输入参数：每页显示记录条数
      @pageIndex INT --//输入参数：当前页码
AS
BEGIN

    SET NOCOUNT ON
    SET ANSI_WARNINGS OFF

    declare @tmpTableName varchar(50)
    set @tmpTableName = '##TB1516_' + replace(cast(newid() as varchar(40)),'-','') --//生成随机临时表名称

    declare @subIndex int
    set @subIndex = charindex('from',@sqlQuery)
    if (@subIndex > 0)
    begin --//带FROM的标准检索语句
    declare @sqlQuery1 varchar(8000)
    declare @sqlQuery2 varchar(8000)
    set @sqlQuery1 = substring(@sqlQuery,1,@subIndex - 1)
    set @sqlQuery2 = substring(@sqlQuery,@subIndex,len(@sqlQuery))
    set @sqlQuery = @sqlQuery1 + ',IDENTITY(numeric,1,1) as ID1516 into ' + @tmpTableName + ' ' + @sqlQuery2
    end
    else --//不带FROM的表名
    begin
    set @sqlQuery = 'select *,IDENTITY(numeric,1,1) as ID1516 into ' + @tmpTableName + ' from' + @sqlQuery
    end
    exec(@sqlQuery) --//建立并初始化临时表数据

    declare @indexStart varchar(20),@indexEnd varchar(20)
    set @indexStart = cast((@pageIndex-1)*@pageSize+1 as varchar(20)) --//数据起始行ID
    set @indexEnd = cast(@pageIndex * @pageSize as varchar(20)) --//数据结束行ID

    exec('select * from ' + @tmpTableName + ' where ID1516 between ' + @indexStart + ' and ' + @indexEnd) --//检索该页数据

    exec('select (case when max(ID1516)>0  then max(ID1516) else 0 end) as recordCount from ' + @tmpTableName) --//提取总条数

    exec('drop table ' + @tmpTableName) --//删除临时表
View Code

 

 动态类型

public IEnumerable<dynamic> RunDynamicList(string sql)
        {
            var table = RunSelectQuery(sql);
            if (table == null || table.Rows.Count < 1)
                return null;
            return table.AsEnumerable().Select(x => new DynamicRow(x));
        }

　　

Linq 去重

 实现IEqualityComparer可以对自定义类型去重

 

索引、触发器

CREATE TRIGGER BeforeInsertTest
  ON student
  FOR INSERT
AS
DECLARE
  @major varchar(20) ;
BEGIN
 -- 取得 专业名.
 SELECT @major = major FROM INSERTED;
 -- 更新专业的人数.
 UPDATE
  major
 SET
  snumber  = snumber + 1
 WHERE
  name = @major;
END;
go
View Code

 

参考地址：http://www.runoob.com/sql/sql-hosting.html

posted @ 2014-10-31 13:24  xiaoshi657  阅读(461)  评论(0)  编辑  收藏  举报