在控制台中用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);
}
一段神奇的代码
____________________________________________________________________________________________________________________________________
测试连接数据库
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=F:\project\数据库\数据库\Database1.mdf;Integrated Security=True;User Instance=True")){
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "insert into MyTable1 (Name) values('123')";
cmd.ExecuteNonQuery();
Console.WriteLine("插入成功");
}
}
Console.WriteLine("打开数据库连接成功");//在控制台中测试是否成功连接数据库
Console.ReadKey();
____________________________________________________________________________________________________________________________________
用户登录的代码 思路根据用户名返回数据集 如果可读出数据 证明用户名存在 否则不存在 用户名存在后从数据库中读出密码 与用户输入的密码比对
注意ADO.NET中的连接等资源都实现了IDisposable 接口 可以使用using 进行资源管理 也可以用 try catch finally
string username = Console.ReadLine();
Console.WriteLine("请输入密码");
string password = Console.ReadLine();
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=F:\project\数据库\数据库\Database1.mdf;Integrated Security=True;User Instance=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 (DBpassword == password)
{
Console.WriteLine("登陆成功");
}
else
{
Console.WriteLine("密码错误");
}
}
else
{
Console.WriteLine("用户名错误");
}
}
}
}
____________________________________________________________________________________________________________________________________
实现用户插入用户名密码 即注册的代码
Console.WriteLine("请输入要插入的用户名");
string username = Console.ReadLine();
Console.WriteLine("请输入要插入的密码");
string password = Console.ReadLine();
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=
F:\project\数据库\数据库\Database1.mdf;Integrated Security=True;User Instance=True"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "insert into T_users (UserName,PassWord) values('" + username + "','" + password + "')";
cmd.ExecuteNonQuery();
Console.WriteLine("插入成功");
}
}
____________________________________________________________________________________________________________________________________
ExecuteReader 返回一个数据集 可以通过GetOrdinal方法得到某个字段的起始编号再通过GetString得到具体值
F:\project\数据库\数据库\Database1.mdf;Integrated Security=True;User Instance=True"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from T_users";
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
Console.WriteLine(sdr.GetString(sdr.GetOrdinal("PassWord")));
}
}
}
____________________________________________________________________________________________________________________________
ExecuteScalar方法用于执行查询,并返回查询所返回的结果集中第一行的第一列,因为不能确定返回值的类型,所以返回值是object类型。 一般用于count(*)
用ExecuteScalar查询插入数据的id 在values关键字前加上output inserted.Id即可,其中Id为主键字段名。执行结果就是插入的主键值
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=
F:\project\数据库\数据库\Database1.mdf;Integrated Security=True;User Instance=True"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "insert into T_users (UserName,PassWord) output inserted.Id values('" + username + "','" + password + "')";
int id = Convert.ToInt32(cmd.ExecuteScalar());
Console.WriteLine("id={0}",id);
}
}
}
}
}---------------------- Windows Phone 7手机开发、 .Net培训、期待与您交流! ----------------------