1 在项目中添加数据库，当要打包项目给别人的时候，需要断开数据库的连接
2 连接SQLserver:连接到那台服务器上，那个实例那个数据库，用户名和密码
“Data Data Source=ZHANGLIANG\SQLEXPRESS;Initial Catalog=MyDB;Integrated Security=True”
连接数据库语句：SqlConnection需要using
stringconStr=@"Data Source=ZHANGLIANG\SQLEXPRESS;Initial Catalog=MyDB;Integrated Security=True";
using(SqlConnectionconn=newSqlConnection(conStr)){
if (conn!=null)
{
conn.Open();
Console.WriteLine("Success!");
}}
3 插入操作：
using( SqlConnection conn = new SqlConnection(conStr) ){
if (conn != null)
{
conn.Open();
Console.WriteLine("连接 Success!");
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "insert into Person(name,age)values('yy',29) ";
cmd.ExecuteNonQuery();
Console.WriteLine("插入成功！");
}
}
}
4 登录练习：
/// <summary>
/// 登录方法
/// </summary>
static void LoginMethod(string conStr) {
Console.WriteLine("输入用户名：");
string name = Console.ReadLine();
Console.WriteLine("输入密码：");
string pwd = Console.ReadLine();
using (SqlConnection conn = new SqlConnection(conStr))
{
conn.Open();
using (SqlCommand cmd=conn.CreateCommand())
{
cmd.CommandText = "select * from T_user where name='"+name+"'";
using (SqlDataReader reader=cmd.ExecuteReader())
{
if (reader.Read())//有该用户
{
if (reader.GetString(reader.GetOrdinal("password"))==pwd.Trim())//密码一致
{
Console.WriteLine("登录成功！");
}
else
{
Console.WriteLine("登录失败！");
}
}
else
{
Console.WriteLine("用户不存在！");
}
}
}
}
5 用户界面插入数据
/// <summary>
/// 插入新用户信息
/// </summary>
/// <param name="conStr"></param>
static void InsertUserInfo(string conStr) {
Console.WriteLine("输入要插入的用户名：");
string name = Console.ReadLine();
Console.WriteLine("输入密码：");
string pwd = Console.ReadLine();
using (SqlConnection conn = new SqlConnection(conStr))
{
conn.Open();
using (SqlCommand cmd=conn.CreateCommand())
{
// cmd.CommandText = "insert into T_user(name,password)values('" + name + "','" + pwd + "')";
cmd.CommandText = string.Format("insert into T_user(name,password)values('{0}','{1}')",name,pwd);
cmd.ExecuteNonQuery();
Console.WriteLine("新用户信息插入成功！");
}
}
}
6 ExecuteScalar返回第一行和第一列的结果 ：cmd.CommandText=“select cout(*) from T_user”; cmd.ExecuteScalar();
7 获取插入数据的id值：
cmd.CommandText=string.Format("insert into T_user(name,password) output inserted.id values('{0}','{1}')",name,pwd); Console.WriteLine(cmd.ExecuteScalar());//返回插入时的id值
8 close()与dispose()的区别： close() 后可以在Open,而dispose()后不能在open()
9 防注入漏洞攻击
/// <summary>
/// 防注册漏洞攻击
/// </summary>
/// <param name="conStr"></param>
static void CheckUserProtected(string conStr)
{
string name, pwd;
Console.WriteLine("Name:");
name = Console.ReadLine();
Console.WriteLine("Password:");
pwd = Console.ReadLine();
using(SqlConnection conn=new SqlConnection(conStr)){
conn.Open();
using (SqlCommand cmd=conn.CreateCommand())
{
cmd.CommandText = "select * from T_user where name=@Name and password=@Password";
cmd.Parameters.Add("@Name",name);
cmd.Parameters.Add("@Password",pwd);
if (Convert.ToInt32(cmd.ExecuteScalar())>0)
{
Console.WriteLine("登录成功！");
}
else
{
Console.WriteLine("登录失败！");
}
}
}
}