封装sqlhelper【一】
控件信息展示:
1 //定义调用数据库类文件 2 3 namespace SqlHelper 4 { 5 public class TblClass 6 { 7 public int classId { get; set; } 8 public string class1 { get; set; } 9 public string classname { get; set; } 10 } 11 } 12 13 //主文件 14 15 using System; 16 using System.Collections.Generic; 17 using System.ComponentModel; 18 using System.Data; 19 using System.Drawing; 20 using System.Linq; 21 using System.Text; 22 using System.Windows.Forms; 23 using System.Data.SqlClient; 24 25 namespace SqlHelper 26 { 27 public partial class Form1 : Form 28 { 29 public Form1() 30 { 31 InitializeComponent(); 32 } 33 //定义一个连接字符串 34 //readonly修饰的变量,只能在初始化的时候赋值,以及在构造函数中赋值 35 //其他地方只能读取不能设置值 36 private static readonly string constr =@"database=ItcastCater;server=LAPTOP-2FGC7ARC\Wangjin;user=sa;pwd=sa"; 37 //1. 执行增(insert)、删(delect)、改(update)的方法 38 //ExecteNonQuery() 39 public static int ExecteNonQuery(string sql, params SqlParameter[] pms) 40 { 41 using (SqlConnection conn = new SqlConnection(constr)) 42 { 43 using (SqlCommand comm = new SqlCommand(sql, conn)) 44 { 45 if (pms != null) 46 { 47 comm.Parameters.AddRange(pms); 48 } 49 conn.Open(); 50 return comm.ExecuteNonQuery(); 51 } 52 } 53 } 54 //返回单个值的方法封装 ExecuteScalar 55 public static object ExecuteScalar(string sql, params SqlParameter[] pms) 56 { 57 using (SqlConnection conn = new SqlConnection(constr)) 58 { 59 using (SqlCommand comm = new SqlCommand(sql, conn)) 60 { 61 if (pms != null) 62 { 63 comm.Parameters.AddRange(pms); 64 } 65 conn.Open(); 66 return comm.ExecuteScalar(); 67 } 68 } 69 } 70 //返回SqlDataReader类型的多行多列数据 因为reader使用的时候必须保持连接池打开,所以方法和以上不一样 71 public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms) 72 { 73 SqlConnection conn = new SqlConnection(constr); 74 75 using (SqlCommand comm = new SqlCommand(sql, conn)) 76 { 77 if (pms != null) 78 { 79 comm.Parameters.AddRange(pms); 80 } 81 try 82 { 83 conn.Open(); 84 //System.Data.CommandBehavior.CloseConnection表示使用完毕以后在关闭reader的同时 85 //内部会将关联的connection对象页关闭掉 86 return comm.ExecuteReader(System.Data.CommandBehavior.CloseConnection); 87 } 88 catch 89 { 90 //关闭连接 91 conn.Close(); 92 conn.Dispose(); 93 //向上抛异常,表示需要获取的数据出现异常,并且提示出错信息 94 throw; 95 } 96 } 97 } 98 private void button1_Click(object sender, EventArgs e) 99 { 100 string sql = "select count(*) from classtable where classId=@uid and class=@pwd"; 101 SqlParameter[] pms = new SqlParameter[]{ 102 new SqlParameter("@uid",SqlDbType.Int){Value=textBox1.Text.Trim()}, 103 new SqlParameter("@pwd",SqlDbType.VarChar,50){Value=textBox2.Text} 104 }; 105 int r=(int) SqlHelper.Form1.ExecuteScalar(sql, pms); 106 if (r > 0) 107 { 108 MessageBox.Show("登陆成功"); 109 } 110 else 111 { 112 MessageBox.Show("登陆失败"); 113 } 114 } 115 //使用ExecuteReader读取数据 116 private void button3_Click(object sender, EventArgs e) 117 { 118 List<TblClass> list=new List<TblClass>(); 119 string sql = "select * from classtable"; 120 using (SqlDataReader reader = SqlHelper.Form1.ExecuteReader(sql)) 121 { 122 if (reader.HasRows) 123 { 124 while (reader.Read()) 125 { 126 TblClass model = new TblClass(); 127 model.classId = reader.GetInt32(0); 128 model.class1 = reader.GetString(1); 129 model.classname = reader.IsDBNull(2) ? null : reader.GetString(2); 130 list.Add(model); 131 } 132 } 133 MessageBox.Show(list.Count.ToString()); 134 } 135 } 136 } 137 }