一、基础知识
主要类及成员（和数据库无关的）
（1）类DataSet：数据集，对应着库，属性Tables表示所有的表
（2）类DataTable：数据表，对应着表，属性Rows表示所有的行
（3）类DataRow：行数据，一个行数组，就对应着一个实体对象
-》使用DataAdapter的Fill方法，可以将数据填充到DataSet或DataTable中
二、练习：完成学生表的crud
（1）dataGridView的填充：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Windows.Forms.VisualStyles;
using t2_StudentInfo; namespace studentInfo
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
string sql = "select * from studentinfo";
using (SqlConnection conn = new SqlConnection("server=.;database=dbtest;uid=sa;pwd=123"))
{
SqlDataAdapter sda = new SqlDataAdapter(sql,conn);
DataTable table = new DataTable();
sda.Fill(table);
dataGridView1.DataSource = table;
}
}
}
}
（2）重新封装SQLhelper
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks; namespace t2_StudentInfo
{
public static partial class SqlHelper
{
private static string connStr = ConfigurationManager.ConnectionStrings["dbtest"].ConnectionString; public static int ExecuteNonQuery(string sql,params SqlParameter[] ps)
{
using (SqlConnection conn=new SqlConnection(connStr))
{
SqlCommand cmd=new SqlCommand(sql,conn);
cmd.Parameters.AddRange(ps); conn.Open();
return cmd.ExecuteNonQuery();
}
} public static object ExecuteScalar(string sql, params SqlParameter[] ps)
{
using (SqlConnection conn=new SqlConnection(connStr))
{
SqlCommand cmd=new SqlCommand(sql,conn);
cmd.Parameters.AddRange(ps); conn.Open();
return cmd.ExecuteScalar();
}
} public static DataTable ExecuteTable(string sql,params SqlParameter[] ps)
{
using (SqlConnection conn=new SqlConnection(connStr))
{
SqlDataAdapter adapter=new SqlDataAdapter(sql,conn);
//用于进行select操作，可以通过SelectCommand属性获取此操作的SqlCommand对象
adapter.SelectCommand.Parameters.AddRange(ps); DataTable dt=new DataTable();
adapter.Fill(dt); return dt;
}
} public static SqlDataReader ExecuteReader(string sql,params SqlParameter[] ps)
{
SqlConnection conn=new SqlConnection(connStr);
SqlCommand cmd=new SqlCommand(sql,conn);
cmd.Parameters.AddRange(ps); conn.Open(); return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
}
（3）查询
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Windows.Forms.VisualStyles;
using t2_StudentInfo; namespace studentInfo
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{ //连接查询
string sql = "select * from StudentInfo inner join ClassInfo on StudentInfo.cid=ClassInfo.cId ";
DataTable dt = SqlHelper.ExecuteTable(sql);
dataGridView1.AutoGenerateColumns = false;
dataGridView1.DataSource = dt; } private void dataGridView1_CellFormatting_1(object sender, DataGridViewCellFormattingEventArgs e)
{
if (e.ColumnIndex == )
{
if (Convert.ToBoolean(e.Value))
{
e.Value = "男";
}
else
{
e.Value = "女";
}
}
} }
}
（4）
A:列表：
B:列表代码：
private void FormAdd_Load(object sender, EventArgs e)
{
string sql = "select * from classinfo";
DataTable dt = SqlHelper.ExecuteTable(sql);
cdoClassInfo.DisplayMember = "CTitle";
cdoClassInfo.ValueMember = "CId";
cdoClassInfo.DataSource = dt;
}
C:添加、
private void button1_Click(object sender, EventArgs e)
{
string sql ="";
if (string.IsNullOrEmpty(label1.Text))
{
//添加
sql = "insert into studentinfo(sname,sgender,sbirthday,cid) values(@name,@gender,@birthday,@cid)";
}
else
{
//修改
sql = "update studentinfo set sname=@name,sgender=@gender,sbirthday=@birthday,cid=@cid where sid=" +label1.Text;
} SqlParameter[] ps =
{
new SqlParameter("@name",textBox1.Text),
new SqlParameter("@gender",radioButton1.Checked),
new SqlParameter("@birthday",dtpBirthday.Value),
new SqlParameter("@cid",cboClassInfo.SelectedValue),
};
int result=SqlHelper.ExecuteNonQuery(sql, ps);
if (result > )
{
FreshForm();
this.Close();
}
else
{
MessageBox.Show("保存失败");
}
}
D:删除：
private void removeToolStripMenuItem_Click(object sender, EventArgs e)
{
DialogResult result = MessageBox.Show("确定要删除吗？", "提示", MessageBoxButtons.OKCancel);
if (result == DialogResult.Cancel)
{
return;
}
int sid = Convert.ToInt32(dataGridView1.SelectedRows[].Cells[].Value);
string sql = "update studentinfo set isdelete=1 where sid=" + sid;
if (SqlHelper.ExecuteNonQuery(sql) > )
{
LoadList();
}
else
{
MessageBox.Show("删除失败");
}
}
E:修改：
private void editToolStripMenuItem_Click(object sender, EventArgs e)
{
int id = Convert.ToInt32(dataGridView1.SelectedRows[].Cells[].Value); FormAdd formAdd=new FormAdd();
formAdd.FreshForm += LoadList;
ShowStudent += formAdd.ShowInfo;
formAdd.Show(); ShowStudent(id);//发布显示内容的消息
}
-》事件（广播、消息）的代码实现：
《1》定义委托
《2》在发布消息的类型中定义事件
《3》在接收消息的类型中为事件添加方法