1.ado.net
程序要和数据库交互要通过ado.net进行,通过ado.net就能在程序中执行SQL了.ado.net
在拷贝数据库的时候要注意,要先关闭连接
神奇的代码
string
dataDir =
AppDomain
.CurrentDomain.BaseDirectory;
if
(dataDir.EndsWith(
@"\bin\Debug\"
)
|| dataDir.EndsWith(
@"\bin\Release\"
))
{
dataDir = System.IO.
Directory
.GetParent(dataDir).Parent.Parent.FullName;
AppDomain
.CurrentDomain.SetData(
"DataDirectory"
, dataDir);
}
//以上是必写代码,具体参考http://www.rupeng.com/forum/thread-11988-1-1.html
//防止数据库源文件选择出错
2.连接SQLServer
连接字符串,程序通过连接字符串指定要连哪个势力的哪个数据库,用什么用户名密码.
项目内嵌mdf文件形式的连接字符串
using
(
SqlConnection
conn =
new
SqlConnection
(
@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True;"
))
{
conn.Open();
}
Console
.WriteLine(
"打开数据库成功"
);
Console
.ReadKey();
ado.net中通过sqlconnection类创建到SQLServer的连接,SqlConnection代表数据库连接,ado.net中的连接等资源都实现IDisposable接口,可以使用using进行管理,using资源释放
别忘了using System.Data.Sqlclint
3.cmd
cmd.CommandText =
"INSERT INTO MyTable1(Name)VALUES('zjh')"
;
cmd.ExecuteNonQuery();
Console
.WriteLine(
"插入成功"
);
4.简单的用户输入用户名,密码,再插入到数据库中
//用户写入用户名,密码,插入数据
Console
.Write(
"请输入用户名:"
);
string
UserName =
Console
.ReadLine();
Console
.Write(
"请输入密码:"
);
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.CreateCommand())
{
cmd.CommandText =
"INSERT INTO T_User(UserName,Password)VALUES('"
+ UserName +
"','"
+ Password +
"')"
;
cmd.ExecuteNonQuery();
Console
.WriteLine(
"插入成功"
);
}
}
5.取表的第一行第一列
using
(
SqlConnection
conn =
new
SqlConnection
(
@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True;"
))
{
conn.Open();
using
(
SqlCommand
cmd = conn.CreateCommand())
{
cmd.CommandText =
"select count(*) from T_User"
;
int
i =
Convert
.ToInt32(cmd.ExecuteScalar());
//返回表的行数,取结果的第一行的第一列
Console
.WriteLine(i);
}
}
6.得到自增字段的值 OUTPUT inserted.Id
//用户写入用户名,密码,插入数据
Console
.Write(
"请输入用户名:"
);
string
UserName =
Console
.ReadLine();
Console
.Write(
"请输入密码:"
);
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.CreateCommand())
{
cmd.CommandText =
"insert into T_User(UserName,Password) output inserted.id values('"
+ UserName +
"','"
+ Password +
"')"
;
//得到自增字段的值
int
id =
Convert
.ToInt32(cmd.ExecuteScalar());
Console
.WriteLine(
"插入成功\n新插入的主键为:{0}"
, id);
}
}
7.ExecuteReader
执行多行结果集的用ExecuteReader
using
(
SqlCommand
cmd = conn.CreateCommand())
{
cmd.CommandText =
"SELECT * FROM MyTable1"
;
using
(
SqlDataReader
dr = cmd.ExecuteReader())
//只能逐行向前,无法回头,轻量级
{
while
(dr.Read())
{
//Console.WriteLine(dr.GetString(0));
string
userName = dr.GetString(dr.GetOrdinal(
"Name"
));
int
Id = dr.GetInt32(dr.GetOrdinal(
"Id"
));
string
password = dr.GetString(dr.GetOrdinal(
"Password"
));
Console
.WriteLine(
"Id={0},UserName={1},Password={2}"
, Id, userName, password);
//Console.WriteLine(dr.GetString(dr.GetOrdinal("Password")));
}
}
}
reader的GetString,GetInt32,等方法只接受整数参数,也就是序列号,用GetOrdinal方法根据列名动态得到序列号
8.登录练习
//用户写入用户名,密码,插入数据
Console
.Write(
"请输入用户名:"
);
string
UserName =
Console
.ReadLine();
Console
.Write(
"请输入密码:"
);
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.CreateCommand())
{
cmd.CommandText =
"select * from T_User where UserName='"
+ UserName +
"'"
;
//先到表中查询用户输入的用户名对应的信息
using
(
SqlDataReader
reader = cmd.ExecuteReader())
{
if
(reader.Read())
//个人感觉用reader.HasRows()比较好
{
//用户存在
string
dbpassword = reader.GetString(reader.GetOrdinal(
"Password"
));
if
(Password == dbpassword)
{
Console
.WriteLine(
"密码正确,登录成功"
);
}
else
{
Console
.WriteLine(
"密码错误,登录失败"
);
}
}
else
//reader返回false,就是没有查找到这个用户名
{
Console
.WriteLine(
"用户名错误"
);
}
}
}
}
9.close和dispose的区别
sql的close连接关闭后还可以打开,必须Dispose注销掉就不能打开
using在除了作用域以后调用Dispose, SqlConnection,FileStream等的Dispose内部都会做这样的判断:判断有没有close,如果没有Close就是先Close后再Dispose
10.注入漏洞与参数化查询
using
(
SqlConnection
conn =
new
SqlConnection
(
@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True;"
))
{
conn.Open();
using
(
SqlCommand
cmd = conn.CreateCommand())
{
cmd.CommandText =
"select count(*) from T_User where UserName=@username and Password=@password"
;
//先到表中查询用户输入的用户名对应的信息
cmd.Parameters.Add(
new
SqlParameter
(
"username"
,UserName));
cmd.Parameters.Add(
new
SqlParameter
(
"password"
,Password));
int
i =
Convert
.ToInt32(cmd.ExecuteScalar());
if
(i > 0)
{
Console
.WriteLine(
"登陆成功"
);
}
else
{
Console
.WriteLine(
"登录失败"
);
}
}
}
11.案例:用户登陆,登陆三次禁止登陆,用数据库记录错误次数
private
void
IntErrorTime()
{
//密码错误,进行errortime加1操作
using
(
SqlConnection
conn =
new
SqlConnection
(
@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\MYDB.mdf;Integrated Security=True;User Instance=True;"
))
{
conn.Open();
using
(
SqlCommand
updatecmd = conn.CreateCommand())
{
updatecmd.CommandText =
"update T_User set ErrorTime=ErrorTime+1 where UserName=@userName"
;
updatecmd.Parameters.Add(
new
SqlParameter
(
"username"
, txtbxUserName.Text));
updatecmd.ExecuteNonQuery();
}
}
}
private
void
ClearErroeTime()
{
//登录成功,清楚errortime的值,恢复0
using
(
SqlConnection
conn =
new
SqlConnection
(
@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\MYDB.mdf;Integrated Security=True;User Instance=True;"
))
{
conn.Open();
using
(
SqlCommand
updatecmd = conn.CreateCommand())
{
updatecmd.CommandText =
"update T_User set ErrorTime=0 where UserName=@userName"
;
updatecmd.Parameters.Add(
new
SqlParameter
(
"username"
, txtbxUserName.Text));
updatecmd.ExecuteNonQuery();
}
}
}
private
void
btnLand_Click(
object
sender,
EventArgs
e)
{
using
(
SqlConnection
conn =
new
SqlConnection
(
@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\MYDB.mdf;Integrated Security=True;User Instance=True;"
))
{
conn.Open();
using
(
SqlCommand
cmd = conn.CreateCommand())
{
cmd.CommandText =
"select * from T_User where UserName=@username"
;
cmd.Parameters.Add(
new
SqlParameter
(
"username"
, txtbxUserName.Text));
using
(
SqlDataReader
reader = cmd.ExecuteReader())
{
if
(reader.Read())
{
int
errortime = reader.GetInt32(reader.GetOrdinal(
"ErrorTime"
));
if
(errortime > 3)
{
MessageBox
.Show(
"登录错误次数过多,禁止登录"
);
return
;
}
string
dbpassword = reader.GetString(reader.GetOrdinal(
"Password"
));
if
(dbpassword == txtbxPassword.Text)
{
ClearErroeTime();
MessageBox
.Show(
"登录成功"
);
}
else
{
//在同一个连接中如果SqlDataReader没有关闭,那么是不能执行update之类的语句的
//using (SqlCommand updatecmd = conn.CreateCommand())
//{
// updatecmd.CommandText = "update T_User ErrorTime=ErrorTime+1 where UserName=@userName ";
// updatecmd.Parameters.Add(new SqlParameter("username", txtbxUserName.Text));
// updatecmd.ExecuteNonQuery();
//}
IntErrorTime();
MessageBox
.Show(
"登录失败"
);
}
}
else
{
MessageBox
.Show(
"用户名不存在"
);
}
}
}
}
}
12.案例手机号码归属地
数据库乱码要注意: 设置类型nvarchar(50), 名字要设置N'浙江省'
数据库连接字符串要写在配置文件,在使用是要先引用sys.configuration,再解析
public
partial
class
Form1
:
Form
{
public
Form1()
{
InitializeComponent();
}
private
void
Form1_Load(
object
sender,
EventArgs
e)
{
//设定CombBox的DisplayMember属性为Name
using
(
SqlConnection
conn =
new
SqlConnection
(
ConfigurationManager
.ConnectionStrings[
"ConnStr"
].ToString()))
{
conn.Open();
using
(
SqlCommand
cmd = conn.CreateCommand())
{
cmd.CommandText =
"SELECT * FROM promary"
;
using
(
SqlDataReader
dr = cmd.ExecuteReader())
//只能逐行向前,无法回头,轻量级
{
while
(dr.Read())
{
ProvinceItem
item =
new
ProvinceItem
();
item.Name = dr.GetString(dr.GetOrdinal(
"proName"
));
item.Id = dr.GetInt32(dr.GetOrdinal(
"proID"
));
cmbbxPromary.Items.Add(item);
}
}
}
}
}
private
void
cmbbxPromary_SelectedIndexChanged(
object
sender,
EventArgs
e)
{
cmbbxCity.Items.Clear();
cmbbxCity.SelectedItem =
null
;
this
.Refresh();
//清空
ProvinceItem
item = (
ProvinceItem
)cmbbxPromary.SelectedItem;
int
proid = item.Id;
//MessageBox.Show(item.Id.ToString());
using
(
SqlConnection
conn =
new
SqlConnection
(
ConfigurationManager
.ConnectionStrings[
"ConnStr"
].ToString()))
{
conn.Open();
using
(
SqlCommand
cmd = conn.CreateCommand())
{
cmd.CommandText =
"SELECT * FROM city where proID=@proid"
;
cmd.Parameters.Add(
new
SqlParameter
(
"proid"
,proid));
using
(
SqlDataReader
dr = cmd.ExecuteReader())
//只能逐行向前,无法回头,轻量级
{
while
(dr.Read())
{
cmbbxCity.Items.Add(dr.GetString(dr.GetOrdinal(
"cityName"
)));
}
}
}
}
}
}
public
class
ProvinceItem
{
public
string
Name {
get
;
set
; }
public
int
Id {
get
;
set
; }
}