要连接数据库,首先
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);
}
连接SQLServer
连接字符串:程序通过连接字符串指定要连接哪台服务器上的,哪个实例的哪个数据库、用什么用户名密码等
项目内嵌MDF文件形式的连接字符串
“Date Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True”.
“.\SQLEXPRESS”表示“本机上的SQLEXPRESS实例”,如果数据库实例名不是SQLEXPRESS,则修改“Database1.mdf”为mdf的文件名
而在VS2015内嵌的SQL具体的连接符可以在右下角的属性可以看到
ADO.Net中通过SQLConnection类创建SQLServer的连接,SQLConnection代表一个数据库连接,ADO.Net中的连接等资源都实现了IDisposable接口,可以使用using进行资源管理。
using (SqlConnection conn = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C#\TheFirstmdf\TheFirstmdf\MyTable1.mdf;Integrated Security=True"))
{ conn.Open(); }
``
初始化SqlConnection,SqlCommand cmd = conn.CreateCommand()
用using进行资源管理
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = “Create table T_Users(UserName nvarchar(50),Password nvarchar(50)) insert into T_Users(UserName,Password)values(‘admin’,’888888’)”;//创建表,并输入数据进去
cmd.CommandText = “select * from T_Users”;
using (SqlDataReader reader = cmd.ExecuteReader())//查询表格,再把结果读取出来
{
while (reader.Read())//只能逐行向前处理,无法回头,无法往前跳着走
{
string username = reader.GetString(reader.GetOrdinal(“UserName”));
string password = reader.GetString(reader.GetOrdinal(“Password”));
Console.WriteLine(“Username={0},Password={1}”, username, password);
}
}
}
练习,做一个简单登录程序
Console.WriteLine(“请输入用户名:”);
string username = Console.ReadLine();
Console.WriteLine(“请输入密码:”);
string password = Console.ReadLine();
using (SqlConnection conn = new SqlConnection(@”Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C#\TheFirstmdf\TheFirstmdf\MyTable1.mdf;Integrated Security=True”))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = “select * from T_Users where 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//Read返回false,就是没查到这个用户
{ Console.WriteLine(“用户名不存在!”); }
}
}
}
Console.WriteLine(“OK”);
Console.ReadLine();
}
}
}
“`