在windowform里创建一个按钮用来连接数据库,设置两个textbox控件,一个用来输入查询的信息,一个输出查询的信息。再添加一个combobox控件,用来选择以什么样的方式来查询数据。
先上代码
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Connect
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
SqlConnection conn;
//设置按钮连接数据库
private void button1_Click(object sender, EventArgs e)
{
//设置数据库登录信息
SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
scsb.DataSource = "SK-20220626HGWR";
scsb.UserID = "sa";
scsb.Password = "123456";
scsb.InitialCatalog = "test";
//连接数据库
conn = new SqlConnection(scsb.ToString());
if (conn.State == ConnectionState.Closed)
conn.Open();
if (conn.State == ConnectionState.Open)
MessageBox.Show("连接成功");
else
MessageBox.Show("连接失败");
}
private void Form1_Load(object sender, EventArgs e)
{
cbb.SelectedIndex = 2;
}
private void txtId_TextChanged(object sender, EventArgs e)
{
if (cbb.SelectedItem.ToString() == "按学号查询")
{
string stu = txtId.Text;
//创建要执行的SQL语句
string sqlStr = "SELECT*FROM students WHERE 学号 ='" + stu + "'";
//创建要执行SQL语句的对象
SqlCommand comm = new SqlCommand(sqlStr, conn);
//接受数据
SqlDataReader sdr = comm.ExecuteReader();
try
{
while (sdr.Read())
{
string result = "姓名:" + sdr["姓名"].ToString();
string result1 = "班级:" + sdr["班级"].ToString();
string result2 = "性别:" + sdr["性别"].ToString();
string result3 = "年龄:" + sdr["年龄"].ToString();
//显示查询的内容
txtResult.Text = result + "\r\n" + result1 + "\r\n" + result2 + "\r\n" + result3;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//关闭SqldateReader对象
sdr.Close();
}
}
//
else
{
if (cbb.SelectedItem.ToString() == "按姓名查询")
{
string name = txtId.Text;
//创建要执行的SQL语句
string sqlSt = "SELECT*FROM students WHERE 姓名 ='" + name + "'";
//创建执行SQL语句的对象
SqlCommand com = new SqlCommand(sqlSt, conn);
//接受数据
SqlDataReader sd = com.ExecuteReader();
try
{
while (sd.Read())
{
string result = "学号:" + sd["学号"].ToString();
string result1 = "班级:" + sd["班级"].ToString();
string result2 = "性别:" + sd["性别"].ToString();
string result3 = "年龄:" + sd["年龄"].ToString();
//显示数据
txtResult.Text = result + "\r\n" + result1 + "\r\n" + result2 + "\r\n" + result3;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//关闭SqlDateReader对象
sd.Close();
}
}
}
}
在这段代码中遇到的问题:
1.想在textbox方法里调用button方法里实例化的对象conn,结果显示未定义。
解决办法:在两个方法外面先声明conn。因为方法内部声名的字段什么的,只在该方法内部有效。
2.连接数据库有两种方法,上述代码是第一种,第二种方法为:
string mysql = "Data Source=SK-20220626HGWR;Initial Catalag=test;UserID=sa; Password=123456";
SqlConnection conn = new SqlConnection(mysql);
编译时显示不支持关键字 Initial Catalag 未解决
3.拼接字符串 不能 "SELECT*FROM students WHERE 学号 =" + "'stu' ""; stu就不是变量了
应为:"SELECT*FROM students WHERE 学号 ='" + stu + "'";