ADO.NET复习总结（6）-断开式数据操作

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; ???????????} ???????} ???}}

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); ???????} ???}}

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 == 2) ???????????{ ???????????????if (Convert.ToBoolean(e.Value)) ???????????????{ ???????????????????e.Value = "男"; ???????????????} ???????????????else ???????????????{ ???????????????????e.Value = "女"; ???????????????} ???????????} ???????} ???????????}}

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; ???????}

 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 > 0) ???????????{ ???????????????FreshForm(); ???????????????this.Close(); ???????????} ???????????else ???????????{ ???????????????MessageBox.Show("保存失败"); ???????????} ???????}

 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[0].Cells[0].Value); ???????????string sql = "update studentinfo set isdelete=1 where sid=" + sid; ???????????if (SqlHelper.ExecuteNonQuery(sql) > 0) ???????????{ ???????????????LoadList(); ???????????} ???????????else ???????????{ ???????????????MessageBox.Show("删除失败"); ???????????} ???????}

private void editToolStripMenuItem_Click(object sender, EventArgs e) ???????{ ???????????int id = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells[0].Value); ???????????FormAdd formAdd=new FormAdd(); ???????????formAdd.FreshForm += LoadList; ???????????ShowStudent += formAdd.ShowInfo; ???????????formAdd.Show(); ???????????ShowStudent(id);//发布显示内容的消息 ???????}