这个实验是用VS做界面,链接SQL servers数据库。
操作数据库,显示数据库信息。
简单的登录以及查询学生成绩的功能。
做法如下:
1、选择创建Windowows窗体应用
2、添加控件,并且点击控件,在属性窗的Text栏改名字。
3、 也可以在资源管理器里面点进去更改属性
4、 添加类sqlHelp(参考https://blog.csdn.net/weixin_38586839/article/details/78445830?utm_source=app&app_version=4.10.0&code=app_1562916241&uLinkId=usr1mkqgl919blen)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace WindowsFormsApp1
{
class SqlHelp
{
private const string connectinString = @"Data Source=(local);Initial Catalog=class_;Integrated Security=SSPI";
public SqlHelp() { }
public static SqlConnection conn;
public static void OpenConn()//打开数据库连接
{
string SqlCon = connectinString;
conn = new SqlConnection(SqlCon);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
}
public static void CloseConn()//关闭数据库连接
{
if (conn.State.ToString().ToLower() == "open")
{
conn.Close();
conn.Dispose();
}
}
public static SqlDataReader GetDataReaderValue(string sql)//读取数据
{
OpenConn();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader dr = cmd.ExecuteReader();
CloseConn();
return dr;
}
public static DataSet GetDataSetValue(string sql, string tableName)//返回DataSet
{
OpenConn();
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds, tableName);
CloseConn();
return ds;
}
public static DataView GetDataViewValue(string sql)//返回dataview
{
OpenConn();
SqlDataAdapter da;
DataSet ds = new DataSet();//DataSet 是一个集合
da = new SqlDataAdapter(sql, conn);
da.Fill(ds, "temp");
CloseConn();
return ds.Tables[0].DefaultView;
}
public static DataTable GetDataTableValue(string sql)//返回datatable
{
OpenConn();
SqlDataAdapter da = new SqlDataAdapter(sql, conn);//创建适配器
DataTable dt = new DataTable();
da.Fill(dt);
CloseConn();
return dt;
}
//执行一个Sql操作:添加,删除,更新操作,返回受影响行数
public int ExecuteNonQueryCount(string sql)
{
OpenConn();
SqlCommand cmd;
cmd = new SqlCommand(sql, conn);
int value = cmd.ExecuteNonQuery();
return value;
}
//执行一条返回第一条记录第一列的SqlCommand命令
public object ExecuteScalar(string sql)
{
OpenConn();
SqlCommand cmd = new SqlCommand(sql, conn);
object value = cmd.ExecuteScalar();
return value;
}
//返回记录数
public int SqlServerRecordCount(string sql)
{
OpenConn();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.Connection = conn;
SqlDataReader dr;
dr = cmd.ExecuteReader();
int RecordCount = 0;
while (dr.Read())
{
RecordCount++;
}
CloseConn();
return RecordCount;
}
//判断是否为数字
public static bool GetSafeValue(string value)
{
if (string.IsNullOrEmpty(value))
{
return false;
}
foreach (char ch in value)
{
if (!char.IsDigit(ch))
{
return false;
}
}
return true;
}
}
}
6、对Form1控件的代码设计。双击控件”登录“,代码如下:这个SQL语句根据数据库表的设置而定。我设置的登陆表有登录名,密码以及登陆人所属的类别(老师还是学生)
private void button1_Click(object sender, EventArgs e)
{
string user_name = this.textBox1.Text;
string user_psw = this.textBox2.Text;
string class0 = "学生";
if (user_name == "" || user_psw == "")
{
MessageBox.Show("用户名和密码不能为空,请重新输入");
}
else
{
string selectSql = "select * from Logic_Table where username='" + user_name + "' and Lpassword='" + user_psw + "' and Lclass='" + class0 + "'";
SqlHelp sqlHelper = new SqlHelp();
int count = sqlHelper.SqlServerRecordCount(selectSql);
if (count > 0)//count大于0说明匹配成功
{
MessageBox.Show("信息验证成功");
Form2 form = new Form2();//窗体跳转
form.Show();
this.Hide();
}
else
{
MessageBox.Show("用户名或者密码错误");
}
}
}
7、新建窗体Form2。添加控件ListView和botton和textbox
8、ListView属性设置:表头
9、View这一栏选择details
10、设置完成如下:
11、Form2的查询控件代码设置:
private void button1_Click(object sender, EventArgs e)
{
string SNO = textBox1.Text;
SqlHelp sqlHelper = new SqlHelp();
string sql1 = "select Cno,Grade from SSC where Sno='" + SNO + "' order by Cno ";
string sql2 = "select Sname,Savg from Student where Sno='" + SNO + "' order by Savg";
int count = sqlHelper.SqlServerRecordCount(sql2);
int itemnumber = listView1.Items.Count;
int[] grade = { 0, 0, 0, 0, 0 };
int i = 0;
int savg = 0;
ListViewItem lvi = new ListViewItem();
lvi.Text = SNO;
foreach (DataRow row in (SqlHelp.GetDataTableValue(sql2)).Rows)
{
String SName = (string)row["Sname"];
lvi.SubItems.Add(SName.ToString());
savg = (int)row["Savg"];
}
foreach (DataRow row in (SqlHelp.GetDataTableValue(sql1)).Rows)
{
grade[i] = (int)row["Grade"];
lvi.SubItems.Add(grade[i].ToString());
i++;
}
lvi.SubItems.Add(savg.ToString());
this.listView1.Items.Add(lvi);
}
12、运行