封装sqlhelper【一】

时间:2023-02-26 17:21:17

控件信息展示:
封装sqlhelper【一】

 //定义调用数据库类文件

 namespace SqlHelper
{
  public class TblClass
{
public int classId { get; set; }
public string class1 { get; set; }
public string classname { get; set; }
}
} //主文件 using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient; namespace SqlHelper
{
  public partial class Form1 : Form
{
public Form1()
{
  InitializeComponent();
}
//定义一个连接字符串
//readonly修饰的变量,只能在初始化的时候赋值,以及在构造函数中赋值
//其他地方只能读取不能设置值
private static readonly string constr =@"database=ItcastCater;server=LAPTOP-2FGC7ARC\Wangjin;user=sa;pwd=sa";
//1. 执行增(insert)、删(delect)、改(update)的方法
//ExecteNonQuery()
public static int ExecteNonQuery(string sql, params SqlParameter[] pms)
{
   using (SqlConnection conn = new SqlConnection(constr))
   {
      using (SqlCommand comm = new SqlCommand(sql, conn))
   {
   if (pms != null)
   {
      comm.Parameters.AddRange(pms);
   }
      conn.Open();
      return comm.ExecuteNonQuery();
   }
}
}
//返回单个值的方法封装 ExecuteScalar
public static object ExecuteScalar(string sql, params SqlParameter[] pms)
{
using (SqlConnection conn = new SqlConnection(constr))
{
using (SqlCommand comm = new SqlCommand(sql, conn))
{
if (pms != null)
{
comm.Parameters.AddRange(pms);
}
conn.Open();
return comm.ExecuteScalar();
}
}
}
//返回SqlDataReader类型的多行多列数据 因为reader使用的时候必须保持连接池打开,所以方法和以上不一样
public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms)
{
SqlConnection conn = new SqlConnection(constr); using (SqlCommand comm = new SqlCommand(sql, conn))
{
if (pms != null)
{
comm.Parameters.AddRange(pms);
}
try
{
conn.Open();
//System.Data.CommandBehavior.CloseConnection表示使用完毕以后在关闭reader的同时
//内部会将关联的connection对象页关闭掉
return comm.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch
{
//关闭连接
conn.Close();
conn.Dispose();
//向上抛异常,表示需要获取的数据出现异常,并且提示出错信息
throw;
}
}
}
private void button1_Click(object sender, EventArgs e)
{
string sql = "select count(*) from classtable where classId=@uid and class=@pwd";
SqlParameter[] pms = new SqlParameter[]{
new SqlParameter("@uid",SqlDbType.Int){Value=textBox1.Text.Trim()},
new SqlParameter("@pwd",SqlDbType.VarChar,){Value=textBox2.Text}
};
int r=(int) SqlHelper.Form1.ExecuteScalar(sql, pms);
if (r > )
{
MessageBox.Show("登陆成功");
}
else
{
MessageBox.Show("登陆失败");
}
}
//使用ExecuteReader读取数据
private void button3_Click(object sender, EventArgs e)
{
List<TblClass> list=new List<TblClass>();
string sql = "select * from classtable";
using (SqlDataReader reader = SqlHelper.Form1.ExecuteReader(sql))
{
if (reader.HasRows)
{
while (reader.Read())
{
TblClass model = new TblClass();
model.classId = reader.GetInt32();
model.class1 = reader.GetString();
model.classname = reader.IsDBNull() ? null : reader.GetString();
list.Add(model);
}
}
MessageBox.Show(list.Count.ToString());
}
}
}
}