SqlHelper
class SqlHelper { string sqlConnStr = ConfigurationManager.ConnectionStrings["str"].ConnectionString; public DataTable ExcuteDataTable(string sql, params SqlParameter[] param) { DataTable dt = new DataTable(); using (SqlConnection conn = new SqlConnection(sqlConnStr)) { SqlCommand cmd = new SqlCommand(sql, conn); if (param != null) { cmd.Parameters.AddRange(param); } using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { sda.Fill(dt); } } return dt; } public int ExcuteNoneQuery(string sql, params SqlParameter[] param) { using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.AddRange(param); conn.Open(); return cmd.ExecuteNonQuery(); } } } public object ExcuteScalar(string sql, params SqlParameter[] param) { using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { conn.Open(); if (param != null) { cmd.Parameters.AddRange(param); } return cmd.ExecuteScalar(); } } } //类里面不能有方法,只能有字段、属性、方法的定义。 SqlConnection conn ; public SqlHelper() { conn = new SqlConnection(sqlConnStr); } public SqlDataReader ExcuteReader(string sql, params SqlParameter[] param) { SqlCommand cmd = new SqlCommand(sql,conn); if (param != null) { cmd.Parameters.AddRange(param); } conn.Open(); return cmd.ExecuteReader(CommandBehavior.CloseConnection);//当sqlDataReader关的时候连接就关 } }
View Code
public Form1() { InitializeComponent(); } SqlHelper help = new SqlHelper(); private void button1_Click(object sender, EventArgs e) { string sql = "select *from student"; dataGridView1.DataSource= help.ExcuteDataTable(sql); } private void button2_Click(object sender, EventArgs e) { string sql="select *from student where sSex=@sex"; dataGridView1.DataSource= help.ExcuteDataTable(sql,new SqlParameter("@sex","女")); } private void button3_Click(object sender, EventArgs e) { string sql = "select *from student where sSex=@sex and sAge>@age and sClassId=@class"; SqlParameter[] sps={ new SqlParameter("@sex","男"), new SqlParameter("@age",18), new SqlParameter("@class",4) }; dataGridView1.DataSource = help.ExcuteDataTable(sql, sps); } private void button4_Click(object sender, EventArgs e) { string sql = "insert into student(sName,sAge,sSex,sClassId) values(@name,@age,@sex,@class)"; SqlParameter[] param={ new SqlParameter("@name","张三"), new SqlParameter("@age",22), new SqlParameter("@sex","男"), new SqlParameter("@class",4) }; int result=help.ExcuteNoneQuery(sql,param); if (result > 0) { MessageBox.Show("插入成功"); } else { MessageBox.Show("插入失败"); } } private void button5_Click(object sender, EventArgs e) { string sql = "delete from student where sName=@name"; SqlParameter param = new SqlParameter("@name", "关羽"); int result= help.ExcuteNoneQuery(sql, param); if (result > 0) { MessageBox.Show("插入成功"); } else { MessageBox.Show("插入失败"); } } private void button6_Click(object sender, EventArgs e) { string sql = "select count(*) from student"; object o= help.ExcuteScalar(sql); MessageBox.Show("查询结果:"+o.ToString()); } private void button7_Click(object sender, EventArgs e) { string sql = "select *from student"; SqlDataReader sdr= help.ExcuteReader(sql); while (sdr.Read()) { Console.WriteLine(sdr["sName"]); Console.WriteLine(sdr["sAge"]); Console.WriteLine(sdr["sSex"]); } sdr.Close(); string sql1 = "select *from class"; SqlDataReader sdr1 = help.ExcuteReader(sql1); while (sdr1.Read()) { Console.WriteLine(sdr1["cId"]); Console.WriteLine(sdr1["cName"]); } }