黑马程序员——简单封装一个SQLHelper类，调用ADO.NET中常用方法
封装一个简单的SQLHelper类方便使用，提供ExecuteDataTable(string sql,params SqlParameter[] parameters)、ExecuteNonQuery(string sql,params SqlParameter[] parameters)、ExecuteScalar(string sql,params SqlParameter[] parameters)等方法。 网上有微软提供的最全的SQLHelper类，是Enterprise Library中的一部分。
新建一个WinForm项目“尝试封装”，右键项目，添加一个新项，基于服务的数据库“Database1.mdf”，新建两张表T_Persons和T_Users：
create table T_Persons
(
ID int identity(1,1) primary key,
Name varchar(50),
Age int
);
create table T_Users
(
ID int identity(1,1) primary key,
UserName varchar(50),
Password varchar(50),
ErrorTimes int
);
insert into T_Users(UserName,Password,ErrorTimes) values('Steve','888888',0);
给项目新建一个App.config文件，填加connectionString（使用ConfigurationManager类需添加引用System.Configuration，然后using它）：
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name="ConnStr" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True" />
</connectionStrings>
</configuration>
将如下代码添加到Program.cs里Main()方法里的开始位置，这段代码更改了connectionString里DataDirectory的默认位置，改成了项目的根目录，方便调试：
string dataDir = AppDomain.CurrentDomain.BaseDirectory;
if (dataDir.EndsWith(@"\bin\Debug\") || dataDir.EndsWith(@"\bin\Release\"))
{
dataDir = System.IO.Directory.GetParent(dataDir).Parent.Parent.FullName;
AppDomain.CurrentDomain.SetData("DataDirectory", dataDir);
}
给项目新建一个类SQLHelper.cs，代码如下：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace 尝试封装
{
class SQLHelper
{
public static int ExecuteNonQuery(string sql,params SqlParameter[] parameters)
{
string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
using(SqlConnection conn=new SqlConnection(connStr))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
foreach(SqlParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
return cmd.ExecuteNonQuery();
}
}
public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
{
string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
foreach (SqlParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
return cmd.ExecuteScalar();
}
}
//不能用这个封装的ExecuteReader方法了，因为用完连接就会断开，DataReader无法读到数据
public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameters)
{
string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
foreach (SqlParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
return cmd.ExecuteReader();
}
}
public static DataTable ExecuteDataTable(string sql,params SqlParameter[] parameters)
{
string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
using(SqlConnection conn=new SqlConnection(connStr))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
foreach(SqlParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
DataSet dataSet = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dataSet);
return dataSet.Tables[0];
}
}
}
}
在Form1.cs设计窗口添加如下控件：
添加一个DataSet，名为DataSet1.xsd，将表T_Users拖入其中，自动生成强类型DataSet：
在Form1.cs代码窗口添加如下代码：
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;
using System.Configuration;
using 尝试封装.DataSet1TableAdapters;
namespace 尝试封装
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
SQLHelper.ExecuteNonQuery("insert into T_Persons(Name,Age) values(@Name,@Age)",new SqlParameter("Name","Bill"),new SqlParameter("Age",24));
}
private void button2_Click(object sender, EventArgs e)
{
object o=SQLHelper.ExecuteScalar("select count(*) from T_Persons");
MessageBox.Show(o.ToString());
}
private void button3_Click(object sender, EventArgs e)
{
SqlDataReader reader = SQLHelper.ExecuteReader("select * from T_Persons");
while (reader.Read())//这里出现错误，连接断开后，reader已经关闭，不能再读出数据，reader里保存的是数据游标，并不是数据
{
string name = reader.GetString(reader.GetOrdinal("Name"));
MessageBox.Show(name);
}
}
private void button4_Click(object sender, EventArgs e)
{
/*
string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
using(SqlConnection conn=new SqlConnection(connStr))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from T_Persons";
DataSet dataSet = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dataSet);
DataTable table=dataSet.Tables[0];
foreach (DataRow row in table.Rows)
{
string name=row["Name"].ToString();
MessageBox.Show(name);
}
}
*/
DataSet dataSet = new DataSet();
string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from T_Persons";
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dataSet);
}
//DataSet中的数据存放在内存中，连接断开后仍然可用
DataTable table = dataSet.Tables[0];
foreach (DataRow row in table.Rows)
{
string name = row["Name"].ToString();
MessageBox.Show(name);
}
}
private void button5_Click(object sender, EventArgs e)
{
DataTable table=SQLHelper.ExecuteDataTable("select * from T_Persons");
foreach (DataRow row in table.Rows)
{
MessageBox.Show(row["Name"].ToString());
}
}
private void btnLogin_Click(object sender, EventArgs e)
{
DataTable table = SQLHelper.ExecuteDataTable("select * from T_Users where UserName=@UserName",new SqlParameter("UserName",txtUserName.Text));
if (table.Rows.Count == 0)
{
MessageBox.Show("用户名错误！");
return;
}
else
{
DataRow row = table.Rows[0];
if (Convert.ToInt32(row["ErrorTimes"]) > 3)
{
MessageBox.Show("登录错误次数过多，禁止登录！");
}
else
{
if (txtPassword.Text == row["Password"].ToString())
{
SQLHelper.ExecuteNonQuery("update T_Users set ErrorTimes=0 where UserName=@UserName", new SqlParameter("UserName", txtUserName.Text));
MessageBox.Show("登录成功！");
}
else
{
SQLHelper.ExecuteNonQuery("update T_Users set ErrorTimes=ErrorTimes+1 where UserName=@UserName", new SqlParameter("UserName", txtUserName.Text));
MessageBox.Show("密码错误！");
}
}
}
}
private void button6_Click(object sender, EventArgs e)
{
SQLHelper.ExecuteDataTable("select * from T_Users where ID=@ID",new SqlParameter("ID",(object)0));//0默认匹配第二个参数为SqlDbType的构造函数
}
private void button7_Click(object sender, EventArgs e)
{
string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from T_Users";
DataSet dataSet = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dataSet);
DataTable table=dataSet.Tables[0];
DataRow row=table.Rows[0];
row["UserName"] = "Steve";
table.NewRow();
//对DataSet修改都是在内存中，没有提交到数据库
//adapter.UpdateCommand = conn.CreateCommand();
//adapter.UpdateCommand.CommandText = "insert into ...";//格式要求严格，不使用它，使用SqlCommandBuilder自动生成
SqlCommandBuilder cmdBuilder= new SqlCommandBuilder(adapter);
//adapter.Update(dataSet);//更新时自动对修改过的数据进行更新
DataSet dataSetChanged = dataSet.GetChanges();//只获取被修改的数据，更新时降低数据传输量
adapter.Update(dataSetChanged);
}
}
private void button8_Click(object sender, EventArgs e)
{
T_UsersTableAdapter adapter = new T_UsersTableAdapter();
尝试封装.DataSet1.T_UsersDataTable data = adapter.GetData();
for (int i = 0; i < data.Count; i++)
{
尝试封装.DataSet1.T_UsersRow row = data[i];
MessageBox.Show(row.UserName);
}
}
}
}
详细请查看：http://net.itheima.com/
浙公网安备 33010602011771号