ADO.NET
ADO.NET是一组用于和数据源进行交互的面向对象类库。通常数据源是数据库，但也可以是文本文件、Excel表格、xml文件。
说白了就是使用.net操作数据库的一套类库。
ADO.NET 命名空间
System.Data; //描述数据的命名空间
System.Data.SqlClient; //针对SQL Server的命名空间
System.Data.OleDB; //access用这个
System.Data.Odbc;
System.Data.OracleClinet; //Oracle用这个
ADO.NET 四大类库
Connection //用来和数据库建立连接。
Command //执行查询、修改、删除等命令。
ExecuteNonQuery() //执行非查询语句，返回受影响行数 如果执行非增、删、改操作返回-1。
ExecuteScalar() //返回第一行第一列结果 返回Object值
ExecuteReader() //读取多行数据
DataReader //读取一串数据，从DataReader返回的数据都是快速的只是向前的数据流。
DataAdapter //读取后缓存下来，相当于离线数据库，包含对连接对象以及当对数据库进行读取或者写入的时候自动的打开或者关闭连接的引用
OledbConnection、OdbcConnection、SqlConnection 用来链接不同的数据库，换个前缀即可。
Connection 类
connection 用于和数据源建立连接，在执行任何操作之前必须建立连接。
创建Connection对象时，需要提供连接字符串，连接字符串是用；分号分割的一系列名称/值的选项，用来描述连接的基本信息。
IDbConnection 接口定义了核心的connection属性和方法，该接口由所有connection类实现。
1、使用当前登陆到windows的用户身份访问数据库，无需输入密码。
stringconStr ="Data Source=.; Initial Catalog=hzsweb; Integrated Security=SSPI";
2、使用账号密码连接数据库。
stringconStr ="Server=localhost;DataBase=hzsweb;uid=sa;pwd=123;";
连接字符串中需要指定数据库所在的服务器（locahost 和. 都是本地的意思），数据库名称，以及验证方式。
因为数据库放到服务器后ip数据库名都要经常改变，所以常把连接字符串放在配置文件中。
设置web.config
<configuration>
<connectionStrings>
<add name="connStr"connectionString="Server=localhost;DataBase=hzsweb;uid=sa;pwd=123;"/>
</connectionStrings>
</configuration>
添加引用
引用->右键->添加->框架->选择->System.Configuration。
在ConnectionStrings集合中获取连接字符串。
varconStr = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ToString();
以后如果数据库参数有变动，只需要修改配置文件即可。
测试连接
varconStr = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ToString();//创建connection对象System.Data.SqlClient.SqlConnection con =newSystem.Data.SqlClient.SqlConnection();
con.ConnectionString= conStr;//也可以使用用构造方法写在括号中//打开连接con.Open();
context.Response.Write(con.State.ToString());//输出连接状态//关闭连接con.Close();
结果为Open，已连接状态。需要注意的是，每次使用完毕需要关闭连接，释放资源。con.Close()效果等同于con.Dispose()。
使用using语句自动释放资源
using(con)
{//打开连接con.Open();
}
context.Response.Write(con.State.ToString());//输出连接状态
输出结果为Closed，已关闭。可以看出在using语块后是自动释放资源的。必须实现IDispose接口，才可以使用using语句自动释放。
连接池
连接池保持已经打开的数据库连接，这些连接在使用相同数据源的会员间共享，这样就省了不断创建和销毁连接的时间。
当客户端调用open()方法请求打开连接时，连接直接由连接池提供而不是再次创建，当调用Close()方法释放时，它并没有被释放，而是重新回到池中等待下一次请求。
<add name="connStr"connectionString="Server=localhost;DataBase=hzsweb;uid=sa;pwd=123;pooling=true;min pool size=5;max pool size=10"/>
pooling ： 是否打开连接池，默认为true。
min pool size ： 连接池中最小连接数。
max pool size ： 连接池中允许最大连接数。
查看连接池状态可以使用sql系统存储过程sp_who2如：
execsp_who2
如果设置最小连接池数量为5，则数据库中就有 5 个对应数据库名的连接，如果没有操作，其状态就会处于 sleeping 状态。
Command 类
command类可以执行所有类型的SQL语句，和connection类一样，实现了IDbCommand接口。
在使用command类操作数据时，需要指明三个属性。CommandType，CommandText和Connection。
CommandType 枚举值：Text（一条SQL语句，默认值），StoredPRocedure（存储过程），TableDirect（表的名称，获取表的所有记录）。
使用ExecuteNonQuery()插入一条数据
intresult;varconStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();//创建connection对象using(varcon =newSqlConnection(conStr))
{//创建command对象using(varcmd =newSqlCommand())
{
cmd.CommandType= System.Data.CommandType.Text;//默认值 sql语句cmd.CommandText ="insert into article ([title],[content],[createTime]) values ('title','content','2015-08-31')";//执行脚本cmd.Connection = con;//cmd使用的连接con.Open();//打开连接result = cmd.ExecuteNonQuery();//返回受影响行数 插入、修改、删除 都用这个}
}
context.Response.Write(result);
返回结果为1。
使用ExecuteScalar()返回表中的总记录数
varconStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();intcount;using(varcon =newSqlConnection(conStr))
{using(varcmd =newSqlCommand("select count(*) from news", con))//构造函数 type默认就是text{
con.Open();
count= Convert.ToInt32(cmd.ExecuteScalar());//返回第一行第一个字段的值}
}
DataReader 类
可以读取多条数据，从DataReader返回的数据都是快速的只是向前的数据流。
DataReader的常用方法：
Read() ： 判断下一行是否读取到数据，如果有返回true，否则为false。
GetValue() : 返回当前行中指定需要的字段值。
getValues() : 将当前行中的值保存到数组中。可以使用DataReader.FieldCount属性确定一行记录的列数。
NextResult() : 多个结果集时，用来获取下一个结果集
Close() ： 关闭Reader
使用ExecuteReader()方法查询表中所有记录
varconStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();
System.Text.StringBuilder sb=newSystem.Text.StringBuilder();using(varcon =newSqlConnection(conStr))
{using(varcmd =newSqlCommand("select * from news", con))
{
con.Open();varrd =cmd.ExecuteReader();while(rd.Read())//一直读取到没有为止{//sb.Append(rd["title"]);sb.Append(rd.GetValue(1));
sb.Append(rd.GetString(2));
sb.Append("<br/>");
}
rd.Close();
}
}
CommandBehavior 自动关掉关联连接
ExecuteReader()方法有一个重载，可以在关闭DataReader的同时，自动关掉关联的连接。//con.Close()
varrd = cmd.ExecuteReader(CommandBehavior.CloseConnection);
封装为方法调用时，很有用。
多个结果集
当在一个sql语句中使用；分号分割多个查询结果集时，可以使用rd.Nextresult()来找到下一个结果集。
varconStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();
System.Text.StringBuilder sb=newSystem.Text.StringBuilder();using(varcon =newSqlConnection(conStr))
{using(varcmd =newSqlCommand("select top 10 * from news;select top 10 * from article", con))
{
con.Open();varrd =cmd.ExecuteReader(CommandBehavior.CloseConnection);inti =1;do{
sb.Append("<h2>第"+ i +"个结果集：</h2>");while(rd.Read())
{for(intj =0; j < rd.FieldCount; j++)
{
sb.Append("<li>");
sb.Append(rd.GetName(j).ToString());//获取字段名sb.Append("：");
sb.Append(rd.GetValue(j).ToString());//获取字段值sb.Append("</li>");
}
}
i++;
}while(rd.NextResult());
rd.Close();
}
}
结果为 拆分的两组前10条数据。
SQL注入
由于拼接字符串的不安全，前台输入追加条件 or '1' = '1' 则永远为真。以及输入些其他对数据库表操作的语句。或者输入--把你后面的代码都注释掉。
解决办法1： string.Replace("\'","\'\'"); 将所有1个单引号转换为两个单引号
解决方法2：参数化编程，将需要拼接值的地方，用一个参数变量表示，而操作数据库的时候，给这个参数赋值。
参数化编程
1、将需要拼接字符串的值，用一个@引导的变量名代替。
2、使用SqlParameter类型将参数变量与值绑定在一起。
3、将SqlParameter对象交给Command对象的Prarmeters集合。
using(varcon =newSqlConnection(conStr))
{using(varcmd =newSqlCommand("delete from article where id = @id", con))
{
con.Open();
cmd.Parameters.Add("@id",1197);
sb.Append(cmd.ExecuteNonQuery());
}
}
调用存储过程
创建一个存储过程，添加指定数据，并返回新增的id。
usehzswebgocreateprocInsertArticle@titlevarchar(250),@contentvarchar(250),@createTimedatetime,@lastIdintoutputasbegininsertintoarticle (title,content,createTime)values(@title,@content,@createTime);set@lastId=@@IDENTITY;end
在c#中调用存储过程
varconStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();using(varcon =newSqlConnection(conStr))
{using(varcmd =newSqlCommand("InsertArticle", con))
{
cmd.CommandType= CommandType.StoredProcedure;//存储过程con.Open();
cmd.Parameters.AddRange(newSqlParameter[]{newSqlParameter("@title","标题"),newSqlParameter("@content","内容"),newSqlParameter("@createTime",DateTime.Now.ToString()),newSqlParameter("@lastId",SqlDbType.Int,4)
});
cmd.Parameters["@lastId"].Direction = ParameterDirection.Output;//设置为 输出参数intresult =cmd.ExecuteNonQuery();intlastId = (int)cmd.Parameters["@lastId"].Value;
context.Response.Write(lastId);
}
}
事务
事务是一组必须全部成功或全部失败的操作，事务的目标是保证数据总能处于有效一致的状态。
事务有4个被称为ACID属性的特征。
Atomic（原子性）：事务中的所有步骤必须同时成功或失败。
Consist（一致性）：事务使底层数据库在稳定状态间转换。
lsolated（隔离性）：每个事务都是独立的实体，不会互相影响。
Durable（持久性）：在事务成功前，事务产生的变化永久的存储在硬盘上。
使用事务时不要在事务中间使用select语句返回数据，应该在事务开始前返回数据，这样可以减少事务锁定数据的数目。
在T-SQL中使用事务
在T-SQL中使用Begin transaction 开始事务，使用commit提交事务，使用rollback回滚事务。
createprocupdateSort
(@sortAint,@sortBint,@id_aint,@id_bint)asbegintrybegintransactionupdatearticlesetsort=@sortAwhereid=@id_a;updatearticlesetsort=@sortBwhereid=@id_b;commit--提交endtrybegincatchif(@@trancount>0)rollback--回滚endcatchexecupdateSort50,51,1,2
在C#中使用事务
使用connection对象.BeginTransaction()方法返回一个transaction对象，用于管理事务。
varconStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();using(varcon =newSqlConnection(conStr))
{varcmd1 =newSqlCommand("update article set sort = @sortA where id = @idA", con);varcmd2 =newSqlCommand("update article set sort = @sortB where id = @idB", con);
cmd1.Parameters.AddRange(newSqlParameter[] {newSqlParameter("@sortA",100),newSqlParameter("@idA",1)
});
cmd2.Parameters.AddRange(newSqlParameter[] {newSqlParameter("@sortB",101),newSqlParameter("@idB",2)
});
SqlTransaction tran=null;try{
con.Open();
tran=con.BeginTransaction();
cmd1.Transaction=tran;
cmd2.Transaction=tran;
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
tran.Commit();
}catch{
tran.Rollback();
}
}
可以使用tran.save("abc");方法指定保存点，并使用tran.Rollback("abc");回滚到某保存点。回滚全部无需输入参数。
DataSet
DataSet（数据集）内存中的数据库。
在SQL Server中
数据库对象（实例\Sqlexpress）
架构（dbo，表的命名空间）
表（列数据）
行
DataSet中
DataSet实例（new DataSet()）
Tables表集合 newDataTable（表名，命名空间）
DataColumn （DataTable中的列）
DataRow（DataTable中的行）
DataAdapter 类
要在DataSet中提取记录并将它们填入表中，需要使用DataAdapter对象，它含有查询和更新的全部命令。
SelectCommand 查询数据。
varconStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();using(varcon =newSqlConnection(conStr))
{//创建DataAdapter对象，并设置查询语句和数据库连接SqlDataAdapter sda =newSqlDataAdapter();//或直接使用构造方法new SqlDataAdapter("sql",con);sda.SelectCommand =newSqlCommand();
sda.SelectCommand.Connection=con;
sda.SelectCommand.CommandText="select * from article";
DataSet ds=newDataSet();//将数据填充到数据集，使用Fill()时自动打开连接sda.Fill(ds);
DataTable dt= ds.Tables[0];//第一个数据表foreach(DataRow rowindt.Rows)
{
context.Response.Write(row[0]);
context.Response.Write(row[1]);
context.Response.Write(row[2]);
context.Response.Write(row[3]);
context.Response.Write("<hr>");
}
}
DeleteComand 删除数据
varconStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();using(varcon =newSqlConnection(conStr))
{
SqlDataAdapter sda=newSqlDataAdapter("select top 10 id,title from article",con);
DataSet ds=newDataSet();
sda.Fill(ds);
sda.DeleteCommand=newSqlCommand("delete from article where id = 1",con);
sda.Update(ds);
DataTable dt= ds.Tables[0];foreach(DataRow rowindt.Rows)
{
context.Response.Write(row[0]);
context.Response.Write(row[1]);
context.Response.Write("<hr>");
}
}
修改使用UpdateCommand，添加使用InsertCommand 不要忘记Update(ds) 否则不会更新。
手动创建一个DataSet
vards =newDataSet("web");vardt =newDataTable("table1");varcolId =newDataColumn("id",typeof(int));varcolName =newDataColumn("name",typeof(string));varcolSex =newDataColumn("sex",typeof(string));
colId.AutoIncrement=true;//自增colId.AutoIncrementSeed =1;//起始1colId.AutoIncrementStep =1;//递增colId.Unique =true;//唯一dt.Columns.Add(colId);
dt.Columns.Add(colName);
dt.Columns.Add(colSex);
ds.Tables.Add(dt);varrow =dt.NewRow();
row[0] =1;
row[1] ="奉先";
row[2] ="男";
dt.Rows.Add(row);foreach(DataRow rindt.Rows)
{
context.Response.Write(r[0]);
context.Response.Write(r[1]);
context.Response.Write(r[2]);
context.Response.Write("<hr>");
}
SQLHelper
将一些重复的数据库连接Connection,Command,DataReader等封装成一个类，调用方法时只需要传入一些参数和数据库连接字符串就可以访问数据库了。
publicstaticclassSQLHelper
{publicstaticreadonlystringconStr = ConfigurationManager.ConnectionStrings["conStr"].ToString();///<summary>///执行增、删、改操作，返回受影响行数///</summary>///<param name="sql"></param>///<param name="cmdParams"></param>///<returns></returns>publicstaticintExecuteNonQuery(stringsql,paramsSqlParameter[] cmdParams)
{using(varcon =newSqlConnection(conStr))
{using(varcmd =newSqlCommand(sql, con))
{
cmd.Parameters.AddRange(cmdParams);
con.Open();returncmd.ExecuteNonQuery();
}
}
}///<summary>///返回首行首列信息///</summary>///<param name="sql"></param>///<param name="cmdParams"></param>///<returns></returns>publicstaticobjectExecuteScalar(stringsql,paramsSqlParameter[] cmdParams)
{using(varcon =newSqlConnection(conStr))
{using(varcmd =newSqlCommand(sql, con))
{
cmd.Parameters.AddRange(cmdParams);
con.Open();returncmd.ExecuteScalar();
}
}
}///<summary>///返回只进的读取流///</summary>///<param name="sql"></param>///<param name="cmdParams"></param>///<returns></returns>publicstaticSqlDataReader ExecuteReader(stringsql,paramsSqlParameter[] cmdParams)
{varcon =newSqlConnection(conStr);try{using(varcmd =newSqlCommand(sql, con))
{
cmd.Parameters.AddRange(cmdParams);
con.Open();returncmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}catch(Exception e)
{
con.Close();throwe;
}
}///<summary>///返回DataTable///</summary>///<param name="sql"></param>///<param name="cmdParams"></param>///<returns></returns>publicstaticDataTable getDataTable(stringsql,paramsSqlParameter[] cmdParams)
{vards =newDataSet();using(varsda =newSqlDataAdapter(sql, conStr))
{
sda.SelectCommand.Parameters.AddRange(cmdParams);
sda.Fill(ds);
}returnds.Tables[0];
}
}
调用
//修改stringsql ="update article set sort = @sort where id = @id";varprams =newSqlParameter[]{newSqlParameter("@sort",15),newSqlParameter("@id",3)
};intresult =SQLHelper.ExecuteNonQuery(sql);//条数stringsqlCount ="select count(*) from article";varcount =SQLHelper.ExecuteScalar(sqlCount);//DataReaderstringreaderSql ="select id,title,createTime from article";using(varrd =SQLHelper.ExecuteReader(readerSql))
{while(rd.Read())
{////}
}//DataTablestringdtSql ="select id,title,createTime from article";vardt =SQLHelper.getDataTable(dtSql);foreach(DataRow rowindt.Rows)
{///}
