Asp.net-知识总结（2）

来源：互联网发布：对社区网络式管理创新编辑：程序博客网时间：2024/05/19 06:17

1.回顾：上篇学习了asp.net 的运行机制和一般处理程序

2.这篇讲学习 ado.net

3.ADO.NET和SQL

(1).ADO.net一般理解和sqlhelper

Ado.net 是为了方便在开发的时候方便操作数据库，而封装好的一些对象：Connection,Command,DataAdapter,DataReader,DataSet ，这几项主要的操作对象。而ado.net操作的数据库有限，可以操作sql server ,Access 等，但是不可以操作mySQL,如果需要操作的话，需要另外下载操作mysql 的类库（.dll文件），引用即可。

同时为了方便开发者操作数据库，一般将写成数据库操作类 sqlHelper。

下面是一些总结：

连接字符串：

DataSource=.\sqlexpress;Initial Catalog=MySchool;Persist Security Info=True;UserID=sa;Password=sa

ado.net组成

数据提供程序

1)connection //连接对象

ConnectionString

Open()

Close()

2)command

CommandText

Connection

executeNonQuery() //执行增删改

executeScalar() //执行查询返回首行首列

insert into class() outputinserted.列明values()

insert into class()values();select @@identity

executeReader() //执行查询返回只读只进的结果集datareader

3)datareader 只读只进一次只读一条记录

必须独享一个Connection

hasRow //是否有行

fieldCount //字段的个数

read()

dr["列名"] //返回object 对的是select语句后的列明

dr.GetString(序号) dr.GetOrdinal("列名")

close()

4)SqlParameter

5) dataadapter

Fill()

Update()

* SqlCommandBuilder 自动生成增删改的语句

dataadapter的sql语句必须包含主键

数据集

dataset 断开式数据集

DataTable

DataColumn

DataRow dr = dt.NewRow();

SqlHelper 实现如下：

  using System;usingSystem.Collections.Generic;usingSystem.Text; usingSystem.Data;usingSystem.Data.SqlClient;usingSystem.Configuration;namespaceCommon{    public class SqlHelper    {        string connStr =ConfigurationManager.ConnectionStrings["str"].ConnectionString;        /// <summary>        /// 执行查询        /// </summary>        /// <paramname="sql">sql语句</param>        /// <paramname="param">sql语句中的参数</param>        /// <returns></returns>        public DataTableExecuteDataTable(string sql, params SqlParameter[] param)        {            DataTable dt = new DataTable();            using (SqlConnection conn = newSqlConnection(connStr))            {                SqlCommand cmd = newSqlCommand(sql, conn);                if (param != null)                {                    //添加参数                   cmd.Parameters.AddRange(param);                }                using (SqlDataAdapter sda = newSqlDataAdapter(cmd))                {                    //填充datatable                    sda.Fill(dt);                }            }            return dt;        }        /// <summary>        /// 执行增删改        /// </summary>        /// <param name="sql">要执行的sql语句</param>        /// <paramname="param">参数</param>        /// <returns></returns>        public int ExecuteNonQuery(string sql,params SqlParameter[] param)        {            using (SqlConnection conn = newSqlConnection(connStr))            {                using (SqlCommand cmd = newSqlCommand(sql, conn))                {                    if (param != null)                    {                       cmd.Parameters.AddRange(param);                    }                    conn.Open();                    returncmd.ExecuteNonQuery();                }            }        }        /// <summary>        /// 返回首行首列        /// </summary>        /// <paramname="sql"></param>        /// <paramname="param"></param>        /// <returns></returns>        public object ExecuteScalar(string sql,params SqlParameter[] param)        {            using (SqlConnection conn = newSqlConnection(connStr))            {                using (SqlCommand cmd = newSqlCommand(sql, conn))                {                    if (param != null)                    {                       cmd.Parameters.AddRange(param);                    }                    conn.Open();                    return cmd.ExecuteScalar();                }            }        }               /// <summary>        /// 执行查询返回datareader        /// </summary>        /// <paramname="sql"></param>        /// <paramname="param"></param>        /// <returns></returns>        public SqlDataReaderExecuteReader(string sql, params SqlParameter[] param)        {            SqlConnection conn = newSqlConnection(connStr);            SqlCommand cmd = newSqlCommand(sql, conn);            if (param != null)            {                cmd.Parameters.AddRange(param);            }            conn.Open();            //当datareader关闭，对应的连接就关闭            returncmd.ExecuteReader(CommandBehavior.CloseConnection);        }    }}

(2).子查询，分页查询，多表查询，表的连接

(3).视图，事务，存储过程 (此项作为了解)

见下面例子：

createtable test(    number varchar(10),    amount int)insertinto test(number,amount) values('RK1',10)insertinto test(number,amount) values('RK2',20)insertinto test(number,amount) values('RK3',-30)insertinto test(number,amount) values('RK4',-10) select* from test  casewhen then end CREATETABLE student0 (name nvarchar(10),subject nvarchar(10),result int)INSERTINTO student0 VALUES ('王','语文',null)INSERTINTO student0 VALUES ('王','数学',90)INSERTINTO student0 VALUES ('王','物理',85)INSERTINTO student0 VALUES ('yang','语文',85)INSERTINTO student0 VALUES ('yang','数学',92)INSERTINTO student0 VALUES ('yang','物理',null) select* from student0truncatetable student0selectname as '姓名',    sum(case subject --case  判断的是列名吗        when '语文'then result else 0 --when 列的值是多少的时候 then 返回一个值  else 否则 就 怎么样 最后 end 结束    end) as 语文,    sum(case subject        when '数学' then result    end) as 数学,    isnull(sum(case subject        when '物理' then result    end),0) as 物理fromstudent0groupby name deletefrom score where studentId in(selectsId from student where sName in ('刘备','关羽','张飞'))    --查询高一一班 高二一班所有的学生--子查询返回的值不止一个。当子查询跟随在=、!=、<、<=、>、>= 之后--子查询跟在比较运算符之后，要求子查询只返回一个值select* from student where sClassId =(selectcId from class where cName in ('高一一班','高二一班'))  select* from student where sClassId in(selectcId from class where cName in ('高一一班','高二一班'))  --查询刘关张的成绩select* from score where studentId in(selectsId from student where sName in ('刘备','关羽','张飞')) select* from student --删除刘关张deletefrom score where studentId in(selectsId from student where sName in ('刘备','关羽','张飞'))    --最近入学的个学生selecttop 3 * from studentorderby sId desc --查询第到个学生selecttop 3 * from studentwheresId not in (select top 3 sId from student order by sId desc)orderby sId desc  --查询到个学生selecttop 3 * from studentwheresId not in (select top 6 sId from student order by sId desc)orderby sId desc --查询第n页的学生selecttop 5 * from studentwheresId not in (select top (5*(2-1)) sId from student order by sId desc) 牛亮 16:30:26 (多人发送)  --内连接inner join...on...select* from studentinnerjoin class on sClassId=cId select* from class --查询所有学生的姓名、年龄及所在班级selectsName,sAge,cName,sSex from studentinnerjoin class on sClassId = cIdwheresSex ='女'--查询年龄超过岁的学生的姓名、年龄及所在班级selectsName,sAge,cName from classinnerjoin student on sClassId = cIdwheresAge > 20 --外连接--leftjoin...on...selectsName,sAge,cName from classleftjoin student on sClassId = cId --rightjoin...on...selectsName,sAge,cName from studentrightjoin class on sClassId = cId   selectsName,sAge,english from student as stuinnerjoin score as sc on stu.sId=studentId select* from studentselect* from score--查询学生姓名、年龄、班级及成绩selectsName,sAge,cName,english from studentinner joinclass on sClassId = cId inner join score selectsName,sAge,cName,isnull(english,0) 英语 from studentinnerjoin class on sClassId=cId inner join score on studentId=sId select* from scoreselect* from student--查询所有学生(参加和未参加考试)的学生姓名、年龄、成绩，如果没有参加考试显示缺考,如果小于60分显示不及格selectsName,sAge,case     whenenglish is null then '缺考'     whenenglish<60 then '不及格'    else convert(varchar(10),english)endfromstudentleftjoin score on sId=studentId

0 0