SqlDataAdapter和SqlConnection

再飞行！

已于 2023-03-10 16:59:58 修改

阅读量65

点赞数

文章标签：数据库 sql

于 2023-03-10 16:42:02 首次发布

本文链接：https://blog.csdn.net/qq_54964061/article/details/129446069

版权

Sql13.sql

use SCUT
go
create table [EMPLOYEE](
   [EmpNo] varchar(12) not null primary key,
   [EmpName] varchar(20) not null,
   [EmpSex] varchar(12) check([EmpSex]='男' or [EmpSex]='女'),
   [EmpAge] int check([EmpAge]>0)
   )
create table [COMPANY](
   [CmpNo] varchar(12) not null primary key,
   [CmpName] varchar(20) not null,
   )

create table [WORKS](
   [EmpNo] varchar(12) not null references [EMPLOYEE] ([EmpNo]),
   [CmpNo] varchar(12) not null references [COMPANY] ([CmpNo]),
   [Salary] int check([Salary]>0)

)
insert into [EMPLOYEE] values('E01','张三','女',32);
insert into [EMPLOYEE] values('E02','李四','男',28);
insert into [EMPLOYEE] values('E03','王五','女',42);
insert into [EMPLOYEE] values('E04','赵六','男',37);
insert into [EMPLOYEE] values('E05','陈七','男',51);
insert into [COMPANY] values('C01','阳光科技');
insert into [COMPANY] values('C02','晨光科技');
insert into [COMPANY] values('C03','未来科技');
insert into [WORKS] values('E01','C01',3000);
insert into [WORKS] values('E01','C02',4000);
insert into [WORKS] values('E02','C02',5000);
insert into [WORKS] values('E02','C03',2500);
insert into [WORKS] values('E03','C01',3500);
insert into [WORKS] values('E04','C02',3000);
insert into [WORKS] values('E05','C03',2000);

CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

CREATE INDEX index_name ON table_name (column_name);

Test0:

PublicFunction.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Test0
{
class PublicFunction
{
public static string cnnstring = "Server=localhost;Database=SCUT;Uid=sa;Pwd=123456";
public static SqlConnection cnn = new SqlConnection(PublicFunction.cnnstring);

public static void TableChange(string sql)
{ //对数据库表的增删改,sql是要进行的sql语句
try
{
SqlCommand cmd = new SqlCommand(sql, PublicFunction.cnn);
cmd.ExecuteNonQuery();
MessageBox.Show("操作成功!");
}
catch(Exception e)
{
MessageBox.Show(e.Message, "错误提示");
return;
}
}
public static DataTable TableSelect(string sql)
{ //对数据库表进行查找操作,sql是要进行的sql语句
try
{
DataTable table = new DataTable();
SqlDataAdapter sdp = new SqlDataAdapter(sql, PublicFunction.cnn);
sdp.Fill(table);
return table;
}
catch(Exception e)
{
MessageBox.Show(e.Message, "错误提示");
return null;

}
}
public static void ListView_load(ListView listView,DataTable dataTable)
{//为ListView加载数据
listView.Items.Clear();
if (dataTable != null && dataTable.Rows.Count > 0)
{
ListViewItem temp=null;
for (int i = 0; i < dataTable.Rows.Count; i++)
{
temp = new ListViewItem(dataTable.Rows[i][0].ToString());
for(int j = 1; j < dataTable.Columns.Count; j++)
{
temp.SubItems.Add(dataTable.Rows[i][j].ToString());
}
listView.Items.Add(temp);
}
}
}
public static void ComboBox_load(ComboBox comboBox,DataTable dataTable)
{//为ComboBox加载数据
comboBox.Items.Clear();
if (dataTable != null && dataTable.Rows.Count > 0)
{
for(int i=0;i<dataTable.Rows.Count;i++)
{
comboBox.Items.Add(dataTable.Rows[i][0].ToString());
}
}
}

}

}
FormMain.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;

namespace Test0
{
public partial class FormMain : Form
{
public static Form2 form2 = null;
public static Form3_1 form3_1 = null;
public static Form3_2 form3_2 = null;
public static Form3_3 form3_3 = null;
public FormMain()
{
InitializeComponent();
}

private void FormMain_Load(object sender, EventArgs e)
{
//PublicFunction.cnn.Open();
}

private void ToolStripMenuItem3_1_Click(object sender, EventArgs e)
{
if (form3_1 == null)
{
form3_1 = new Form3_1();
}
form3_1.Show();
}

private void ToolStripMenuItem2_Click(object sender, EventArgs e)
{
if (form2 == null)
{
form2 = new Form2();
}
form2.Show();

}

private void FormMain_FormClosed(object sender, FormClosedEventArgs e)
{
PublicFunction.cnn.Close();
Application.Exit();
}

private void ToolStripMenuItem3_2_Click(object sender, EventArgs e)
{
if (form3_2 == null)
{
form3_2 = new Form3_2();
}
form3_2.Show();
}

private void ToolStripMenuItem3_3_Click(object sender, EventArgs e)
{
if (form3_3 == null)
{
form3_3 = new Form3_3();
}
form3_3.Show();
}
}
}
Form2.cs

namespace Test0
{
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();
}

private void listView1_SelectedIndexChanged(object sender, EventArgs e)
{

}

private void button1_Click(object sender, EventArgs e)
{
new Form2_change().ShowDialog();
}

private void listView1_SelectedIndexChanged_1(object sender, EventArgs e)
{

}

public void Form2_Load(object sender, EventArgs e)
{
string sql = "select * from EMPLOYEE";
PublicFunction.ListView_load(this.listView1, PublicFunction.TableSelect(sql));
}

private void button3_Click(object sender, EventArgs e)
{
if (listView1.SelectedItems.Count == 1)
{
new Form2_change(listView1.SelectedItems[0].SubItems[0].Text).ShowDialog();

}
else
{
MessageBox.Show("选中一行进行修改");
}
}

private void button2_Click(object sender, EventArgs e)
{
if (listView1.SelectedItems.Count == 1)
{
string sql = String.Format("delete from [EMPLOYEE] where EmpNo='{0}'", listView1.SelectedItems[0].SubItems[0].Text);
PublicFunction.TableChange(sql);
Form2_Load(sender, e);
}
else
{
MessageBox.Show("选中一行进行删除");
}
}
}
}
Form2_change.cs

namespace Test0
{
public partial class Form2_change : Form
{

public static int flag = -1;
public static string EmpNo = "";
public Form2_change()
{
flag = 1;
InitializeComponent();
}
public Form2_change(string EmpNo0)
{
flag = 2;
EmpNo = EmpNo0;
InitializeComponent();
}

private void radioButton1_CheckedChanged(object sender, EventArgs e)
{

}

private void label1_Click(object sender, EventArgs e)
{

}

private void Form2_change_Load(object sender, EventArgs e)
{
if (flag == 1)
{
this.Text = "添加员工信息";
this.button1.Text = "添加员工信息";
}
else if (flag == 2)
{
this.Text = "修改员工信息";
this.button1.Text = "修改员工信息";
this.textBox1.Text = EmpNo;
this.textBox1.Enabled = false;
}
}

private void button1_Click(object sender, EventArgs e)
{
string sex="女";
if (radioButton1.Checked)
{
sex = "男";
}
int age;
try
{
age = int.Parse(textBox3.Text);
}
catch
{
MessageBox.Show("年龄请输入整数");
return;
}
if (flag == 1)
{
string sql = String.Format("insert into [EMPLOYEE] values('{0}','{1}','{2}',{3})", textBox1.Text, textBox2.Text, sex, age);
PublicFunction.TableChange(sql);
}
else if(flag==2)
{
string sql= String.Format("update [EMPLOYEE] set EmpName='{1}',EmpSex='{2}',EmpAge={3} where EmpNo='{0}'", textBox1.Text,textBox2.Text, sex, age);
PublicFunction.TableChange(sql);
}
FormMain.form2.Form2_Load(sender,e);
}
}
}
Form3_1.cs

namespace Test0
{
public partial class Form3_1 : Form
{
public Form3_1()
{
InitializeComponent();
}

private void Form3_1_Load(object sender, EventArgs e)
{
string sql = String.Format("select EmpNo from [EMPLOYEE]");
PublicFunction.ComboBox_load(comboBox1, PublicFunction.TableSelect(sql));
sql = String.Format("select EmpName from [EMPLOYEE]");
PublicFunction.ComboBox_load(comboBox2, PublicFunction.TableSelect(sql));
}

private void button1_Click(object sender, EventArgs e)
{
string sql = String.Format("select CmpName,Salary from [EMPLOYEE],[COMPANY],[WORKS] where [EMPLOYEE].EmpNo=[WORKS].EmpNo and [WORKS].CmpNo=[COMPANY].CmpNo and [EMPLOYEE].EmpNo='{0}'", comboBox1.SelectedItem.ToString());
PublicFunction.ListView_load(listView1, PublicFunction.TableSelect(sql));
}

private void button2_Click(object sender, EventArgs e)
{
string sql = String.Format("select CmpName,Salary from [EMPLOYEE],[COMPANY],[WORKS] where [EMPLOYEE].EmpNo=[WORKS].EmpNo and [WORKS].CmpNo=[COMPANY].CmpNo and [EMPLOYEE].EmpName='{0}' ", comboBox2.SelectedItem.ToString());
PublicFunction.ListView_load(listView1, PublicFunction.TableSelect(sql));
}
}
}
Form3_2.cs

namespace Test0
{
public partial class Form3_2 : Form
{
public Form3_2()
{
InitializeComponent();
}

private void Form3_2_Load(object sender, EventArgs e)
{
string sql = String.Format("select EmpName,sum(Salary) salary from [EMPLOYEE],[WORKS] where [EmpAge]>=40 and [EMPLOYEE].EmpNo=[WORKS].EmpNo Group By EmpName Order by salary desc");
PublicFunction.ListView_load(listView1, PublicFunction.TableSelect(sql));
}
}
}
Form3_3.cs

namespace Test0
{
public partial class Form3_3 : Form
{
public Form3_3()
{
InitializeComponent();
}

private void Form3_3_Load(object sender, EventArgs e)
{
string sql = String.Format("select EMPLOYEE.EmpName,COMPANY.CmpName from EMPLOYEE,COMPANY,WORKS,(select EmpName from [EMPLOYEE],[WORKS],[COMPANY] where [EMPLOYEE].EmpNo=[WORKS].EmpNo and [WORKS].CmpNo=[COMPANY].CmpNo Group By EmpName Having count([COMPANY].CmpName)>=2) as temp where EMPLOYEE.EmpNo=WORKS.EmpNo and COMPANY.CmpNo=WORKS.CmpNo and EMPLOYEE.EmpName=temp.EmpName");
PublicFunction.ListView_load(listView1, PublicFunction.TableSelect(sql));
}
}
}
Test1:

FormMain.cs

namespace SCUT1
{
public partial class FormMain : Form
{
public FormMain()
{
InitializeComponent();
}

private void FormMain_Load(object sender, EventArgs e)
{
PublicFunction.cnn.Open();
XmlDocument doc = new XmlDocument();
doc.Load(@"表.xml");
XmlNode xn = doc.SelectSingleNode("grades");
XmlNodeList xnl = xn.ChildNodes;
string id,name,course;
int score;
string sql;
foreach(XmlNode xNode in xnl)
{
XmlNodeList xnl0 = xNode.ChildNodes;
id = xnl0.Item(0).InnerText;
name = xnl0.Item(1).InnerText;
course = xnl0.Item(2).InnerText;
score =int.Parse(xnl0.Item(3).InnerText);
sql = String.Format("insert into Score values('{0}','{1}','{2}',{3})", id, name, course, score);
PublicFunction.TableChange(sql);
}
}
}
}

再飞行！

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
SqlDataAdapter和SqlConnection

insert into [EMPLOYEE] values('E01','张三','女',32);insert into [EMPLOYEE] values('E02','李四','男',28);insert into [EMPLOYEE] values('E03','王五','女',42);insert into [EMPLOYEE] values('E04','赵六','男',37);insert into [EMPLOYEE] values('E05','陈七','男',51);
复制链接

扫一扫