实验三 C#数据库操作

实验三数据库操作

一、实验目的：略

二、实验内容

在SQl Sever 2005中建立数据表关系模式如下，完成后面操作：

mima(ID,UserName,Password)

字段类型参考：

ID：int 自增

UserName：nvarchar（50）；

Password：nvarchar（50）；

1、//输出多列

//采用连接方式直接操作数据库：

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.SqlClient;

namespace ConsoleApplication3

{

class Program

{

static void Main(string[] args)

{

using (SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=xkxt;uid=sa;pwd=111111"))

{

con.Open();

using (SqlCommand cmd = con.CreateCommand())

{

https://www.360docs.net/doc/8413701809.html,mandText = "SELECT * FROM mima";

using (SqlDataReader reader = cmd.ExecuteReader())

while (reader.Read())

{

//Console.WriteLine(reader.GetString(1));

//GetString(1)读第一列的值

String username =

reader.GetString(reader.GetOrdinal("UserName"));

int id = reader.GetInt32(reader.GetOrdinal("ID"));

String Password =

reader.GetString(reader.GetOrdinal("Password"));

Console.WriteLine("ID={0},UserName={1},Password={2}", id, username, Password);

}

}

}

}

}

}

2、//插入记录

Console.WriteLine("请输入要插入用户名");

string username = Console.ReadLine();

Console.WriteLine("请输入要插入密码");

string password = Console.ReadLine();

using (SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=xkxt;uid=sa;pwd=111111"))

{

con.Open();

using (SqlCommand insertcmd = con.CreateCommand())

{

https://www.360docs.net/doc/8413701809.html,mandText = "insert into mima(UserName,Password) values(@UN,@PD)";

insertcmd.Parameters.Add(new SqlParameter("UN", username));

insertcmd.Parameters.Add(new SqlParameter("PD", password));

insertcmd.ExecuteNonQuery();

Console.WriteLine("插入成功！");

}

}

3、 //修改数据

Console.WriteLine("请输入要修改密码用户名");

string username = Console.ReadLine();

Console.WriteLine("请输入新密码");

string password = Console.ReadLine();

using (SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=xkxt;uid=sa;pwd=111111"))

{

con.Open();

using (SqlCommand updatecmd = con.CreateCommand())

{

https://www.360docs.net/doc/8413701809.html,mandText = "update mima set Password=@PD where UserName=@UN"; updatecmd.Parameters.Add(new SqlParameter("UN", username));

updatecmd.Parameters.Add(new SqlParameter("PD", password));

updatecmd.ExecuteNonQuery();

Console.WriteLine("成功修改密码！");

}

} }

4、//多个查询：

using (SqlConnection con = new SqlConnection("Data Source=.;Initial

Catalog=xkxt;uid=sa;pwd=111111"))

{

con.Open();

using (SqlCommand cmd = con.CreateCommand())

{

https://www.360docs.net/doc/8413701809.html,mandText = "SELECT Sno FROM student";

SqlDataReader sdr = cmd.ExecuteReader();

while (sdr.Read())

{

Console.WriteLine(sdr[0]);

}

}

}

using (SqlConnection con = new SqlConnection("Data Source=.;Initial

Catalog=xkxt;uid=sa;pwd=111111"))

{

con.Open();

using (SqlCommand cmd = con.CreateCommand())

{

https://www.360docs.net/doc/8413701809.html,mandText = "SELECT sname FROM student";

SqlDataReader sdr = cmd.ExecuteReader();

while (sdr.Read())

{

Console.WriteLine(sdr[0]);

}

}

}