实现功能:窗体连接SQL Server数据库,并显示相应的数据库内容,同时也可以进行增删改查并实时显示
1、获取连接字符串
新建记事本,改后缀名为.udl 然后根据测试相应的数据库
最后用记事本打开.udl文件 截取Integrated Security=SSPI后面所有信息,再添加测试数据库
例如:Integrated Security=SSPI;PersistSecurity Info=False;Initial Catalog=db_stuInfo;Data Source=DESKTOP-6H6M5FF;Database=Library(红色部分即为添加,注意不要掉前面的分号)
2、建立简单页面
更改所有控件命名,注意规范
3,写增删改查的类
注意:添加以下两个命名空间:
using System.Data;
using System.Data.SqlClient;
原理如图:
//SQL.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace SQLServer
{
class SQL
{
static stringconnectString = "Integrated Security=SSPI;Persist SecurityInfo=False;Initial Catalog=db_stuInfo;DataSource=DESKTOP-6H6M5FF;Database=db_stuInfo";
SqlConnection conn =new SqlConnection(connectString);
public voidconnectToDatabase()
{
conn.Open();
}
public voidcloseDatabase()
{
conn.Close();
}
/// <summary>
/// 查询的语句返回给dt
/// </summary>
/// <paramname="sql"></param>
///<returns>DataTable</returns>
public DataTableSelectSqlReturnDataTable(string sql)
{
SqlDataAdapter sda = new SqlDataAdapter(sql, conn);//相当于运货车
DataTable dt = new DataTable();///相当于临时仓库
sda.Fill(dt);//运货车将货倒进临时仓库等待别人来拿货
return dt;
}
/// <summary>
/// 判断是否成功影响行
/// </summary>
/// <paramname="sql"></param>
///<returns></returns>
public boolIsSuccess(string sql)
{
SqlCommand cmd =new SqlCommand(sql, conn);
int i =cmd.ExecuteNonQuery();
if (i > 0)
{
return true;
}
else
{
return false;
}
}
}
}
//frmMain.cs
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;
using System.Data.SqlClient;
namespace SQLServer
{
public partial classfrmMain : Form
{
public frmMain()
{
InitializeComponent();
}
SQL publicsql = newSQL();
private voidfrmMain_Load(object sender, EventArgs e)
{
string sql ="select * from dbo.tb_student";
publicsql.connectToDatabase();
DataTable dt =publicsql.SelectSqlReturnDataTable(sql);
this.dgvshowdata.DataSource = dt;
publicsql.closeDatabase();
}
/// <summary>
/// 查询
/// </summary>
/// <paramname="sender"></param>
/// <paramname="e"></param>
private voidbtnQuery_Click(object sender, EventArgs e)
{
string tbname =this.tbName.Text.ToString();
string tbsex =this.tbSex.Text.ToString();
string tbage= this.tbAge.Text.ToString();
string tbclass =this.tbClass.Text.ToString();
if(tbname ==string.Empty && tbsex == string.Empty && tbage==string.Empty&& tbclass == string.Empty )
{
MessageBox.Show("至少要填一项");
}
else
{
string sql ="select * from dbo.tb_student where name ='"+tbname+"' orage='"+tbage+"' or sex='"+tbsex+"' orclass='"+tbclass+"'";
publicsql.connectToDatabase();
DataTable dt =publicsql.SelectSqlReturnDataTable(sql);
this.dgvshowdata.DataSource = dt;
publicsql.closeDatabase();
}
}
/// <summary>
/// 增加
/// </summary>
/// <paramname="sender"></param>
/// <paramname="e"></param>
private voidbtnAdd_Click(object sender, EventArgs e)
{
string tbname = this.tbName.Text.ToString();
string tbsex =this.tbSex.Text.ToString();
string tbage =this.tbAge.Text.ToString();
string tbclass =this.tbClass.Text.ToString();
if (tbname ==string.Empty || tbsex == string.Empty || tbage == string.Empty || tbclass ==string.Empty)
{
MessageBox.Show("要全部填完整");
}
else
{
string sql ="insert into dbo.tb_student values('" + tbname + "' ,'" +tbsex + "' ,'" + tbage + "' ,'" + tbclass + "')";
publicsql.connectToDatabase();
DataTable dt =publicsql.SelectSqlReturnDataTable(sql);
this.dgvshowdata.DataSource = dt;
publicsql.closeDatabase();
frmMain_Load(sender, e);
}
}
/// <summary>
/// 删除
/// </summary>
/// <paramname="sender"></param>
/// <paramname="e"></param>
private voidbtnDelete_Click(object sender, EventArgs e)
{
string tbname =this.tbName.Text.ToString();
if (tbname ==string.Empty){
MessageBox.Show("请填写名字");
}
else
{
string sql ="delete from dbo.tb_student where name='"+tbname+"'";
publicsql.connectToDatabase();
try
{
if(publicsql.IsSuccess(sql))
{
MessageBox.Show("删除成功");
}
else
{
MessageBox.Show("删除失败");
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
publicsql.closeDatabase();
}
frmMain_Load(sender, e);
}
/// <summary>
/// 更改
/// </summary>
/// <paramname="sender"></param>
/// <paramname="e"></param>
private voidbtnUpdate_Click(object sender, EventArgs e)
{
string tbname =this.tbName.Text.ToString();
string tbsex =this.tbSex.Text.ToString();
string tbage =this.tbAge.Text.ToString();
string tbclass =this.tbClass.Text.ToString();
if (tbname ==string.Empty || tbsex == string.Empty || tbage == string.Empty || tbclass ==string.Empty)
{
MessageBox.Show("要全部填完整");
}
else
{
string sql ="update dbo.tb_student setsex='"+tbsex+"',age='"+tbage+"', class='"+tbclass+"' where name='" + tbname + "'";
publicsql.connectToDatabase();
try
{
if(publicsql.IsSuccess(sql))
{
MessageBox.Show("更改成功");
}
else
{
MessageBox.Show("更改失败");
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
publicsql.closeDatabase();
}
frmMain_Load(sender, e);
}
}