2012-05-04 ADO.NET 学习笔记

2012.5.4 ADO.NET 学习笔记

——张伟

ADO.net是什么？
ADO.net提供了对各种不同数据库的统一操作接口

一个服务器上可以装多个SQL Server


连接SQLServer
->连接字符串：程序通过连接字符串指定要连接哪台服务器上的、哪个实例的哪个数据库、用什么用户名密码等 


项目内嵌mdf文件形式的链接字符串
->"data source=.\sqlexpress;attachdbfilename=|datadirectory|\database1.mdf;integrated security=true;user instance=true"
如果数据库实例名不是SQLEXPRESS，则需修改。database1.mdf为mdf的文件名

ADO.NET中通过sqlconnection类创建到sqlserver的连接，sqlconnection代表一个数据库链接，ADO.net中的链接等资源都实现了IDisposable接口，可以使用using进行资源管理。‘
->例如：sqlconnection conn=new sqlconnection(@"data source=.\sqlexpress;attachdbfilename=|datadirectory|\database1.mdf;integrated security=true;user instance=true");
coon.open();

当调试过程中出现缺少using命名空间时，可在出错的变量上点右键，通常第一项即是


一条简单的insert命令语句
->using (sqlcommand cmd=conn.CreatCommand())
{
cmd.CommandText = "insert into t_users(username.password) values ('admin','888888')";
cmd.ExecuteNonQuery();
}
->ExecuteNonQuery()方法执行一个非查询语句返回值是执行的影响行数
->sqlCommand表示向服务器提交一个命令
->commandText属性为要执行的SQL语句


执行查询语句用ExecuteReader
->SqlDataReader reader = cmd.EXecuteReader();
while(reader.Read())
{
console.WtriteLine(reader.GetString(1));
}
->reader的GetString、GetInt32等方法只接受整形参数，也就是序号，用GetOrdinal方法根据列名动态得到序号

用户登录代码：
Console.WriteLine("请输入用户名：");
string username = Console.ReadKey();
Console.WriteLine("请输入密码");
string password = Console.ReadLine();
using(sqlconnection conn=new sqlconnection(@"data source=.\sqlexpress;attachdbfilename=|datadirectory|\database1.mdf;integrated security=true;user instance=true"));
{
conn.Open();
using(SqlCommand cmd = conn.CreatCommand())
{
cmd.CommandText = "select * T_User wehre UserName=" "+username+"";
using(SqlDataReader reader = cmd.ExecuteReader())
{
if(reader.Read())
{
string dbpassword = reader.GetString(reader.GetOrdinal("Password"));
if(password == dbpassword)
{
console.writeline("登录成功");
}
else
{
console.writeline("密码错误，登录失败");
}
}
else
{
Console.WriteLine("用户名错误");
}
}
}
Console.WriteLine("OK");
Console.ReadKey();
}


读取用户输入的用户名，然后插入输入的密码代码：
Console.WriteLine("请输入用户名：");
string username = Console.ReadKey();
Console.WriteLine("请输入密码");
string password = Console.ReadLine();
using(sqlconnection conn=new sqlconnection(@"data source=.\sqlexpress;attachdbfilename=|datadirectory|\database1.mdf;integrated security=true;user instance=true"));
{
conn.Open();
using(SqlCommand cmd = conn.CreatCommand())
{
cmd.CommandText = "Insert into T_Users(UserName,Password) values('"+username+"' '"+password"')"
cmd.ExecuteNonQuery();
Console.WriteLine("插入成功");
}
}
Console.WriteLine("打开数据库连接成功");
Console.ReadKey();


user在SQL中属于关键字，命名习惯：表格用T_开头，字段用F_开头


ExecuteScalar()返回第一行第一列数据，返回值为object类型，通常用于查表中有多少条数据，查下最大值


OUTPUT inserted.ID；输出当前自增主键的值，是在2005版后SQL新增的功能
->例如：
using(sqlconnection conn=new sqlconnection(@"data source=.\sqlexpress;attachdbfilename=|datadirectory|\database1.mdf;integrated security=true;user instance=true"));
{
conn.Open();
using(SqlCommand cmd = conn.CreatCommand())
{
cmd.CommandText = "INSERT INTO T_Users(UserName,Password) OUTPUT inserted ID VALUES('admin','888888')";
int id = Convert.ToInt32(cmd.ExecutedScalar());
Console.WriteLine("新插入的主键：{0}"，id);
}
}
Console.WriteLine("OK");
Console.ReadKey();


SqlDataReader语句
->using(sqlconnection conn=new sqlconnection(@"data source=.\sqlexpress;attachdbfilename=|datadirectory|\database1.mdf;integrated security=true;user instance=true"));
{
conn.Open();
using(SqlCommand cmd = conn.CreatCommand())
{
cmd.CommandText = "select * from T_Users";
using(SqlDataReader reader = cmd.ExecuteReader())
{
while(reader.Read())
{
//Console.WriteLine("reader.GetString(1)");
Console.WriteLine(reader.GetString(reader.GerOrdinal("UserName")));
}
}
}
}
Console.WriteLine("OK");
Console.ReadKey();


关闭功能： Close:关闭以后还能打开，Dispose：直接销毁，下次不能再使用。using在除了作用域以后调用Dispose,SqlConnection ,FileStream等的Dispose内部都会被这样的判断：判断有没有Close,如果没有Close就先Close再Dispose。
->sqlconnection conn=new sqlconnection(@"data source=.\sqlexpress;attachdbfilename=|datadirectory|\database1.mdf;integrated security=true;user instance=true");
coon.Open();
//........此部分假设为语句
conn.Close();
conn.Dispose();
Console.WriteLine("OK");
Console.ReadKey();

判断用户名登录的另一种方法：
Console.WriteLine("请输入用户名：");
string username = Console.ReadKey();
Console.WriteLine("请输入密码");
string password = Console.ReadLine();//输入1‘ or ’1‘ = ‘1造成SQL注入漏洞攻击
using(sqlconnection conn=new sqlconnection(@"data source=.\sqlexpress;attachdbfilename=|datadirectory|\database1.mdf;integrated security=true;user instance=true"));
{
conn.Open();
using(SqlCommand cmd = conn.CreatCommand())
{
cmd.CommandText = "select count(*) from T_Users where UserName = '"+username"' and Password = '"+password"'";
int i = Convert.ToInt32(cmd.ExecutedScalar());
if(i>0)
{
Console.WriteLine("登录成功");
}
else
{
Consoel.WriteLine("用户名或者密码错误");
}
}
}
上面需要改进语句：
把cmd.CommandText = "select count(*) from T_Users where UserName = '"+username"' and Password = '"+password"'";
替换成 cmd CommandText = "select count(*) from T_Users where UserName = @UN and Password = @P";
cmd.Parameters.Add(new SqlParameter("UN",username));
cmd.Parameter.Add(new SqlParameter("P",password));

必用的那段神奇代码：
string dataDir = AppDomain.CurrentDomain.BaseDirectory;
if(dataDir.EndsWith(@"\bin\Debug\")||dataDir.EndsWith(@"\bin\Release\"))
{
dataDir = System.IO.Directory.GetParent(dataDir).Parent.FullName;
AppDomain.CurrentDomain.SetData("DataDirectory",dataDir);
}
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());