.Net新手☞数据库操作
在线对象：
Connection 用来和数据库建立连接
Command 表示执行的数据操作命令
Parameter 表示数据操作命令中的参数
DataReader 用来以只读只进方式读取数据
Transaction用来实现事务
DataAdapter用来为数据容器加载数据和把更新后的数据传回数据库
离线对象：
DataSet 数据容器，就好像一个数据库，容纳多个DataTable和关系
DataTable 数据容器，就好像一个数据表，又DataRow和DataColunn构成
DataRow 代表DataTable中的一行记录
DataColumn 代表DataTable的列，就好像字段
DataView 和数据库的视图差不多，用来为一个DataTable建立多种视图
DataRelation 表示各个DataTable之间的关系，并提供浏览父表记录和子表记录的方式
Constraint 表示DataTable的主键约束和外键约束
一、使用Connection对象连接数据库
string sConnectionString="";
sConnectionString=string.Format("Sever=.;Database=数据库名;User ID=sa;Password=sa";Connection TimeOut=2);//可信任连接Trusted_Connection=True
Sqlconnection conn=new Sqlconnection(sConnectionString);
try
{
conn.Open();
if(conn.State==ConnectionState.Open)
Response.Write("数据库连接成功");
}
catch(SqlException sqlException)
{
Response.Write(sqlException.Message);
}
finally
{
if(conn.State==ConnectionState.Open)
conn.Close();
}
从Web.config中获取字符串连接：ConfigurtionManager.ConnectionString["con"].ToString
二、枚举所有可用数据源
protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
{
SqlDataSourceEnumerator instance = SqlDataSourceEnumerator.Instance;
ddl_Server.DataSource = instance.GetDataSource();
ddl_Server.DataTextFiled = "ServerName";
ddl_Server.DataBind();
}
}
注意：列举当前网络中可用的SQL Server服务器示例是个非常慢的操作，请谨慎用。
三、使用SqlCommand执行SQL语句
在使用在线对象时，要尽量早关闭活动连接。我们使用using{}语句来自动释放活动连接。
代码如下：
using (Sqlconnection conn=new Sqlconnection(sConnectionString))
{
conn.open()
using(Sqlcommand cmd=new Sqlcommand(sSql,conn))
{
cmd.ExecuteNonQuery();
}
}
使用了using以后，Connection对象不再需要Close(),using语句会在代码结束时自动调用相应对象的Dispose()方法来释放对象资源。
SqlCommand对象的主要方法为：
ExecuteNonQuery() 执行SQL语句并返回所影响的行，常用于执行一个不返回任何结果的操作。比如插入等
ExevuteReader() 执行查询语句并返回SqlDataReader，常用于返回记录集的操作。比如查询
ExecuteScalar() 执行查询并返回结果的第一行第一列，常用于返回一个值的操作。比如Select count(*) from tbClass
SQL注入带来的威胁
在SQL语句中要求对字符型数据使用单引号来包围，而对于数字型数据又不需要。如果我们不检测输入的数字型参数是否真正是数字，或者不检测输入的字符型参数中是否包含单引号，则很有可能产生漏洞，遭到著名的“SQL注入攻击”。
SQL语句用到的SQL两个技术：
1、SQL语句可以多个一起执行，用分号隔开
2、“--”会注释掉后面的语句，因此“'”就会被忽略，不会引发单引号不匹配的错误。
使用参数防止SQL注入
protected void btn_SeachClass_Click(0bject sender,EventArgs e)
{
string sConnectionString=@"server=.;database=Forum;Trusted_Connection=True";
using(SqlConnection conn=new SqlConnection(sConnectionString))
{
conn.Open();
using(SqlCommand cmd=new SqlCommand("select count(*) from tbClass where ClassName=@ClassName",conn))
{
cmd.Parameters.Add("@ClassName",SqlDbType.Varchar,50);
cmd.Parameters["@ClassName"].Value=tb_ClassName.Text;
Response.Write(string.Format("共有{0}条记录符合要求<br>",cmd.ExecuteScalar().ToString()));
}
}
}
注意：
SQL语句或者存储过程中指定的所有参数必须和Parameters属性中所有参数对应。
参数集合的Add()方法有多种重载，还有一个AddWithValue()方法可以同时为参数赋值。cmd.Parameters.AddWithValue("@ClassName",tb_ClassName.Text)
存储过程：
SqlParameter对象有个Direction方法。对于SQL语句中的参数，这个值没有什么意义，它是用来指定存储过程参数的方向。它的值由ParameterDirection枚举来定义的，共有以下4个类型：
Input
InputOutput
Output
ReturnValue
存储过程模板：
CREATE PROCEDURE db.StoredProcedure1
/*
(
@parameter1 int =5,
@parameter2 datatype output
)
*/
AS
/*SET NOCOUNT ON */
RETURN
例子：
CREATE PROCEDURE db.StoredProcedure1
(
@ClassName varchar(50), //输入参数
@BoardName varchar(50), //输入参数
@ClassID varchar(50) output //输出参数
)
AS
declare @BoardCount int;
Set @ClassID=(Select ClassID from tbClass where ClassName=@ClassName);
Insert into tbBoard(BoardName,BoardClassID)vaules(@BoardName,@ClassID);
Set @BoardCount = (Select count(*) from tbBoard);
RETURN @BoardCount;
使用SqlCommand对象执行存储过程
protected void btn_AddBoard_Click(object sender ,EventArgs e)
{
string sConnectionString = @"sever=(local);database=Forum;Trusted_Connection=True";
using(Sqlconnection conn=new Sqlconnection(sConnectionString))
{
conn.open();
using(Sqlcommand com=new Sqlcommand ("CreateBoard",conn)) //CreateBoard是存储过程名字
{
cmd.CommandType=Command.Type.StoredProcedure;
cmd.Parameters.Add("@ClassName",SqlDbType.Varchar,50);
cmd.Parameters["@ClassName"].Value=tbClassName.Text;
cmd.Parameters["@ClassName"].Direction=ParameterDirection.Input;
cmd.Parameters.Add("@BoardName",SqlDbType.Varchar,50);
cmd.Parameters["@BoardName"].Value=tbBoardName.Text;
cmd.Parameters["@BoardName"].Direction=ParameterDirection.Input;
cmd.Parameters.Add("@ClassID",SqlDbType.Varchar,50);
cmd.Parameters["ClassID"].Direction=ParameterDirection.Output;
cmd.Parameters.Add("@BoardCount",SqlDbType.Int);
cmd.Parameters["@BoardCount"].Direction=ParameterDirection.ReternValue;
cmd.ExecuteNonQuery();
foreach(SqlParameter parameter in cmd.Parameters)
{
Response.Write(string.Format("参数名：{0},参数方向：{1},参数值：{2}<br>",parameter.ParameterName,parameter.Direction.ToString(),parameter.Value));
}
}
}
}
注意以下几点：
1、CommandType枚举。用来枚举所有的命令类型，默认是CommandType.Text,用于执行SQL语句。如果把SqlCommand的CommandText设置为一个存储过程名，就指定CommandType为CommandType.StoredProcedure。
2、Parameter集合。我们需要把所有存储过程需要的参数都添加到Sqlcommand的SqlParameterCollection集合中去，参数名、参数类型和参数大小都应该和存储过程中声明的参数对应。参数的方向用ParameterDirection枚举来定义。
存储过程与事务：
Create procedure Transfer
as
begin tran
/*内容*/
commit tran
return
在存储过程中声明变量的方法是：declare @变量名 数据类型(example: int vachar and so on)
使用DataReader访问数据
一、读取单记录集
string sConnection = @"server=(local);database=Forum;Trusted_Connection=True";
using (SqlConnection conn=new SqlConnection(sConnection))
{
conn.Open();
using (SqlCommand cmd=new SqlCommand("select * from tbBoard",conn))
{
using (SqlDataReader dr=cmd.ExecuteReader())
{
if(dr.HasRows)//记录集是否为空
{
System.Text.StringBuilder htmlStr=new System.Text.StringBuilder();//使用StringBuilder构造字符串的效率高
for(int i=0;i<=dr.FieldCount;i++)
{
htmlStr.Append(string.Format("{0}",dr.GetName(i)));
}
while(dr.Read())
{
for(int i=0;i<dr.FieldCount;i++)
{
htmlStr.Apend(string.Format("{0}",dr.GetValues(i)));//构造记录行
}
}
Response.Write(htmlStr);
}
}
}
}
注意的问题：
1、DataReader对象是不能用New关键字来实例化的，可以通过Command对象的ExecuteReader()方法获得一个DataReader对象。
2、DataReader是一行一行向前读取记录的，因此，我们常使用while(dr.Read())来遍历所有行。Read()方法能使DataReader读取一条记录，并前进到下一条记录，如果已经到达了记录的底部则返回false。
3、DataRead对象的一些属性如下：
FieldCount 获取当前行的列数。我们的程序需要遍历所有列，可以使用这个属性获得列数。
HasRows 指示DataReader是否包含一行或多行。在读取记录集内容以前常用这个属性来判断记录集是否有记录。
4、DataRead对象的一些重要方法如下：
GetInt16()、GetString()、GetDataTime()等GetXXX()方法（其中XXX代表.NET的一种类型）。使用这些方法可以读取行中某列的值，并直接以相应的.NET类型返回。
GetValue()和GetName(). 使用GetValue()方法可以读取行中某列的值，它和上面的GetXXX()差不多，只不过返回object类型的值。GetName()则返回行中某列的列名。
二、读取多记录集
DataReader支持多记录集的读取，可以使用NextResult()方法移动到下一个记录集。
1、DataReader的构造方法还可以接受CommandBehavior枚举作为参数，我们可以使用CommandBehavior.CloseConnection，使得DataReader关联的Connection对象在记录集读取完毕后立即被关闭，且比using代码块要快。
2、在真正读取某行某列值以前应该使用DataReader的IsDBNull(列索引号)方法来判断某列是否有值，以免因为不能进行类型转换导致异常。
string sConnection = @"server=(local);database=Forum;Trusted_Connection=True";
using (SqlConnection conn=new SqlConnection(sConnection))
{
conn.Open();
using (SqlCommand cmd=new SqlCommand("select * from tbBoard",conn))
{
using (SqlDataReader dr=cmd.ExecuteReader(CommandBehavior.CloseConnection)
{
if(dr.HasRows)//记录集是否为空
{
do
{
System.Text.StringBuilder htmlStr=new System.Text.StringBuilder();//使用StringBuilder构造字符串的效率高
for(int i=0;i<=dr.FieldCount;i++)
{
htmlStr.Append(string.Format("{0}",dr.GetName(i)));
}
while(dr.Read())
{
for(int i=0;i<dr.FieldCount;i++)
{
htmlStr.Apend(string.Format("{0}",dr.GetValues(i)));//构造记录行
}
}
Response.Write(htmlStr);
}while(dr.NextResult())
}
}
}
}
DataSet数据容器
一、创建DataSet
我们建立数据库中tbClass表和tbBoard表的本地副本
//先建立数据库
DataSet Forum=new DataSet("Forum");
//再来建立两个数据表
DataTable tbClass=new DataTable("tbClass");
DataTable tbBoard=new DataTable("tbBorad");
//把两个表加入数据库
Forum.Tables.Add(tbClass);
Forum.Tables.Add(tbBorad);
//建立tbClass的两列
DataColumn ClassID=new DataColumn("ClassID",typeof(System.String));
DataColumn ClassName=new DataColumn("ClassName",typeof(System.String));
//设定ClassID不为空
ClassID.AllowDBNull=false;
//把列加入到tbClass表
tbClass.Columns.Add(ClassID);
tbClass.Columns.Add(ClassName);
//设定tbClass表的主键
tbClass.PrimaryKey=new DataColumn[]{ClassID};
//建立tbBoard的三列
DataColumn BoardID=new DataColumn("BoardID",typeof(System.String));
DataColumn BoardName=new DataColumn("BoardName",typeof(System.String));
DataColumn BoardClassID=new DataColumn("BoardClassID",typeof(System.String));
//设定BoardID不为空
BoardID.AllowDBNull=false;
//把列加入到tbBoardID表
tbBoard.Columns.Add(BoardID);
tbBoard.Columns.Add(BoardName);
tbBoard.Columns.Add(BoardClassID);
//设定tbBoard表的主键
tbBoard.PrimaryKey=new DataColumn[]{BoardID};
说明：
1、在建立了DataTable后别忘了把它加入到DataSet；同样，在建立DataColumn后别忘了加入到DataTable中。
2、对于DataTable的PrimayKey属性，它需要赋值一个DataColumn的数组。这是因为一个表可能有几个主键组成联合主键，对于单主键的表，数组只有一个成员。
然后是构建数据库
//为两个表各加入5条记录
for(int i=0;1<=5;i++)
{
//实例化tbClass表的行
DataRow tbClassRow=tbClass.NewRow();
tbClassRow["ClassID"]=Guid.NewGuid();
tbClassRow["ClassName"]=string.Format("分类{0}",i);
//把行加入到tbClass表
tbClass.Rows.Add(tbClassRow);
//实例化tbBoard表的行
DataRow = tbBoard.NewRow();
tbBoardRow["BoardID"]=Guid.NewGuid();
tbBoardRow["BoardName"]=string.Format("版块{0}",i);
tbBoardRow["BoardClassID"]=tbClassRow["ClassID"];
//把行加入tbBoard表
tbBoard.Rows.Add(tbBoardRow);
}
二、访问DataSet
1、假设我们有一个DataTable dt,可以使用dt.Rows[行索引号][列索引号]得到某行某列的值。比如我们要得到dt三行二列的数据，就使用dt[2][1].
2、我们有两种遍历集合的方法：使用foreach和for；对于后者，我们需要知道集合的Item总数，可以使用集合的Count属性。如果遍历需要有序，我们只能使用for进行。
三、使用DataRelation实现父子表
DataSet.Relations.Add("关联名称", 父关联主键字段, 子关联外来键字段)
// 构建父子关系
Forum.Relations.Add("RelationBetweenClassAndBoard", ClassID, BoardClassID);
// 从关系获取父表
DataTable dtParent = Forum.Relations["RelationBetweenClassAndBoard"].ParentTable;
// 从关系获取子表
DataTable dtChild = Forum.Relations["RelationBetweenClassAndBoard"].ChildTable;
// 构建输出字符串
System.Text.StringBuilder htmlStr = new System.Text.StringBuilder();
// 表开始
htmlStr.Append("<table border='1' cellPadding='5' cellSpacing='0' style='font-size:9pt;font:宋体'>");
// 遍历父表中所有行
for (int i = 0; i < dtParent.Rows.Count; i++)
{
// 父表数据行开始
htmlStr.Append("<tr style='background-color=#f0f0f0'>");
// 遍历父表行中列
for (int j = 0; j < dtParent.Columns.Count; j++)
{
if (!dtParent.Rows[i].IsNull(j))
htmlStr.Append(string.Format("<td>{0}</td>", dtParent.Rows[i][j]));
}
// 父表数据行结束
htmlStr.Append("</tr>");
// 遍历子表中所有行
for (int j = 0; j < dtParent.Rows[i].GetChildRows("RelationBetweenClassAndBoard").Length; j++)
{
// 子表数据行开始
htmlStr.Append("<tr>");
// 遍历子表行中列
for (int k = 0; k < dtParent.Columns.Count; k++)
{
if (!dtParent.Rows[i].GetChildRows("RelationBetweenClassAndBoard")[j].IsNull(k))
htmlStr.Append(string.Format("<td>{0}</td>", dtParent.Rows[i].GetChildRows("RelationBetweenClassAndBoard")[j][k]));
}
// 子表数据行结束
htmlStr.Append("</tr>");
}
}
// 表结束
htmlStr.Append("</table><br>");
Response.Write(htmlStr);
使用DataAdapter来获取数据
使用DataTableMapping类来对DataTable和DataColumn进行友好名称映射
DataTableMapping dtmClass=da.TableMappings.Add("Table","论坛分类表");
dtmClass.ColumnMappings.Add("ClassID","分类ID");
dtmClass.ColumnMappings.Add("ClassName","分类名");
DataTableMapping dtmBoard=da.TableMappings.Add("Table","论坛版块表");
dtmBoard.ColumnMappings.Add("BoardID","版块ID");
dtmBoard.ColumnMappings.Add("BoardName","版块名");
dtmBoard.ColumnMappings.Add("BoardClassID","所属分类ID");
更多连接字符串的格式可以去http://www.connectionstrings.com/ 去查询
