ADO.NET+自己封装的SqlHelper
理论到处都是的，我就不写理论了，两张图表达一切：
下面直接写demo练习和自己封装一个sqlHelper分享大家看看
连接
static public void TestConnection() //连接
{
//1.连接通道的连接字符串
string Constr = @"server=.;database=book;uid=sa;pwd=sa";
//2.连接通道
SqlConnection con=new SqlConnection(Constr);
//3.打开通道
con.Open();
//4.关闭通道
con.Close();
Console.WriteLine("连接上数据库了");
//注意如果是orcale的，方式不同：
OleDbConnection conn = new OleDbConnection("Provider=MSDAORA.1;Data Source=(DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.234)(PORT = 1521)))(CONNECT_DATA = (SID = orcl))) ;User Id=sjk;Password=sssss");
conn.Open();
string sql = "select * from tbl_aa ";
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds, "ds");
dataGridView1.DataSource = ds.Tables[0]
}
查询单个值
static public void QuerySingle() //查询单个值 SqlCommand .ExecuteScalar()
{
string constr = @"server=.;database=book;uid=sa;pwd=sa;";
SqlConnection con=new SqlConnection(constr);
string sqlstr = "select count(*) from Category";
SqlCommand cmd = new SqlCommand(sqlstr, con);
con.Open();
object obj = cmd.ExecuteScalar();
con.Close();
Console.WriteLine(obj.ToString());
}
增，删，改
static public void AddRecord() //增，删，改 SqlCommand ExecuteNonQuery()
{
int res = 0; //准备接受新橧
//1.连接通道的字符串
string Constr = @"server=.;database=book;uid=sa;pwd=sa;";
//2.连接通道
SqlConnection con = new SqlConnection();
//3.手动指定连接字符串
con.ConnectionString = Constr;
//4.准备insert字符串语句
string sqlstr = "insert into Category(C_name,C_remark,C_isdel,C_addtime)values('编程书籍','asp.net','0',GetDate())";
//5.新建命令对象（工人），并告诉哪里走，做什么
SqlCommand cmd = new SqlCommand(sqlstr, con);
//cmd.CommandText = sqlstr;
//cmd.Connection = con;
//打开连接通道
con.Open();
//6.调用方法去执行sql语句
res = cmd.ExecuteNonQuery();//此方法负责增删改，返回受影响的行数（int）
//关闭连接通道
con.Close();
if (res > 0)
{
Console.WriteLine("新增成功");
}
else { Console.WriteLine("新增失败"); }
}
查询多行数据 SqlDataReader
static public void QueryList() //查询多行数据 SqlDataReader
{
string constr = @"server=.;database=book;uid=sa;pwd=sa;";
SqlConnection con = new SqlConnection(constr);
string sqlstr = "select * from Category";
SqlCommand cmd = new SqlCommand(sqlstr, con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)//是返回返回结果
{
while (dr.Read())//前进下一行记录
{
//Console.WriteLine(dr[4].ToString());
//for (int i = 0; i < 5; i++)
//{
// Console.WriteLine(dr[i].ToString());
//}
Console.WriteLine(dr["C_id"].ToString() + "+" + dr["C_name"].ToString() + "+" + dr["C_remark"].ToString() + "+" + dr["C_isdel"].ToString() +"+"+ dr["C_addtime"].ToString());
//Console.WriteLine(dr.GetOrdinal("C_id").ToString());
}
}
else { Console.WriteLine("无数据"); }
dr.Close();
con.Close();
}
使用适配器读取数据,填充数据集 SqlDataAdapter
static public void QueryListByAdapter() //使用适配器读取数据,填充数据集 SqlDataAdapter
{
string str = @"server=.;database=book;uid=sa;pwd=sa;";
SqlConnection con = new SqlConnection(str);
string sqlstr = "select * from Category";
//创建适配器，告诉它要做什么事情，走那条路
SqlDataAdapter da = new SqlDataAdapter(sqlstr, con);
//创建数据集对象（程序端的临时数据库）
DataSet ds = new DataSet();
//调用fill方法，填充数据集（先去数据库查询数据集，并把结果集返回赋值给数据集）
da.Fill(ds,"table1");
//获取数据集中的每一张表
DataTable dt = ds.Tables["table1"];
//循环数据表中的每一行
for (int i = 0; i < dt.Rows.Count;i++ )
{
DataRow dr = dt.Rows[i];
//每一行里的每一列
Console.WriteLine(dr["C_id"].ToString() + "+" + dr["C_name"].ToString() + "+" + dr["C_remark"].ToString() + "+" + dr["C_isdel"].ToString() + "+" + dr["C_addtime"].ToString());
}
}
使用适配器读取数据,填充表 SqlDataAdapter
static public void QueryListByAdapter2() //使用适配器读取数据,填充表 SqlDataAdapter
{
string str = @"server=.;database=book;uid=sa;pwd=sa;";
SqlConnection con = new SqlConnection(str);
string sqlstr = "select * from Category";
//创建适配器，告诉它要做什么事情，走那条路
SqlDataAdapter da = new SqlDataAdapter(sqlstr, con);
//创建数据表（程序端的临时数据库）
DataTable dt = new DataTable();
//调用fill方法，填充数据集（先去数据库查询数据集，并把结果集返回赋值给数据集）
da.Fill(dt);
//循环数据表中的每一行
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine(dr["C_id"].ToString() + "+" + dr["C_name"].ToString() + "+" + dr["C_remark"].ToString() + "+" + dr["C_isdel"].ToString() + "+" + dr["C_addtime"].ToString());
}
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// DataRow dr = dt.Rows[i];
// //每一行里的每一列
// Console.WriteLine(dr["C_id"].ToString() + "+" + dr["C_name"].ToString() + "+" + dr["C_remark"].ToString() + "+" + dr["C_isdel"].ToString() + "+" + dr["C_addtime"].ToString());
//}
}
调用存储过程 单个参数
static public void QueryListByProc() //调用存储过程 单个参数
{
string str = @"server=.;database=book;uid=sa;pwd=sa;";
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("usp_getBook", con);
cmd.CommandType = CommandType.StoredProcedure; //指定命令类型为存储过程
SqlParameter sp=new SqlParameter();//指定存储过程中的参数
sp.ParameterName = "@CateId";
sp.SqlDbType = SqlDbType.Int;
sp.Value = 3;
cmd.Parameters.Add(sp);
SqlDataAdapter da = new SqlDataAdapter(cmd);//将参数对象加入到命令对象的参数集合中
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine(dr["C_id"].ToString() + "+" + dr["C_name"].ToString() + "+" + dr["C_remark"].ToString() + "+" + dr["C_isdel"].ToString() + "+" + dr["C_addtime"].ToString());
}
}
调用存储过程，多参数
static public void QueryListByProc2()//调用存储过程，多参数
{
string str = @"server=.;database=Bank;uid=sa;pwd=sa;";
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("getBank", con);
cmd.CommandType = CommandType.StoredProcedure;//指定作为存储过程执行
SqlParameter[] sp = { new SqlParameter("@pageSize", SqlDbType.Int, 4),
new SqlParameter("@pageindex",SqlDbType.Int,4),
new SqlParameter("@rowCount",SqlDbType.Int,4),
new SqlParameter("@pageCount",SqlDbType.Int,4)
};
sp[0].Value = 2;//设置搜索第一页
sp[1].Value = 5;//设置显示的页数
sp[2].Direction = ParameterDirection.Output;//输出参数
sp[3].Direction = ParameterDirection.Output;
//sp[2].Value = 5;
//sp[3].Value = 5;
cmd.Parameters.AddRange(sp);//为cmd对象添加参数数组
SqlDataAdapter da = new SqlDataAdapter(cmd);//创建适配器
DataTable dt = new DataTable();//创建数据表（程序端）
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine(dr["R"].ToString()+"+"+dr["bankID"].ToString()+"+"+dr["customerName"].ToString()+"+"+dr["currentMoney"].ToString());
}
int rowCount = Convert.ToInt32(cmd.Parameters[2].Value);
int pageCount = Convert.ToInt32(cmd.Parameters[3].Value);
Console.WriteLine("{0},{1}",rowCount,pageCount);
}
SqlHelper
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
namespace Power_2.DAL
{
class SqlHelper
{ //每次打开一个连接保持快速连接
private static readonly string constr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
//封装方法的原则：把不变大的放到方法里，把变化的放参数中到
private static SqlConnection conn; //连接
public static SqlConnection Conn
{
get
{
if (conn == null || conn.State == ConnectionState.Broken)
{
conn = new SqlConnection(constr);
}
return conn;
}
}
/// <summary>
/// 析构函数，自动消除，防止没有删除完，程序自动调用
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
~SqlHelper()
{
Dispose();
}
public void Dispose() //写个方法可以调用回收
{
if (conn != null)
{
if (conn.State != ConnectionState.Closed)
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 执行的语句 执行增删改
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql)
{
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
return cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 参数执行的语句 执行增删改
/// </summary>
/// <param name="sqlstr"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string sqlstr, params SqlParameter[] paras)
{
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sqlstr, conn);
if (paras != null && paras.Length > 0)
{
cmd.Parameters.AddRange(paras);
}
int res = 0;
try
{
res = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (cmd != null)
{
if (cmd.Parameters.Count > 0)
{
cmd.Parameters.Clear();
}
cmd.Dispose();
cmd = null;
}
}
Conn.Close();
return res;
}
}
public static object ExecuteScalar(string sql) //查询单个值
{
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
return cmd.ExecuteScalar();
}
}
}
public static object ExecuteScalar(string sql, params SqlParameter[] paras) //查询单个值
{
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.Parameters.AddRange(paras);
cmd.CommandText = sql;
return cmd.ExecuteScalar();
}
}
}
/// <summary>
/// 查询结果集比较少的sql
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string sql, params SqlParameter[] paras)
{
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql,conn);
if (paras != null && paras.Length > 0)
{
cmd.Parameters.AddRange(paras);
}
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
try
{
adapter.Fill(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (adapter != null)
{
adapter.Dispose();
adapter = null;
}
}
return dt;
}
}
public static SqlDataReader ExecuteDataReader(string cmdText, params SqlParameter[] parameters)
{
SqlConnection conn = new SqlConnection(constr);
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
/// <summary>
/// 执行简单分页存储过程
/// </summary>
/// <param name="pageIndex">每页行数</param>
/// <param name="pageSize">当前页面数</param>
/// <param name="rowCount">总行数（传出参数）</param>
/// <param name="pageCount">总页数（传出参数）</param>
/// <returns></returns>
public static DataTable ExecProSimplePageList(int pageIndex, int pageSize, out int rowCount, out int pageCount)
{
rowCount = 1;
pageCount = 1;
SqlParameter[] parameters ={
new SqlParameter("@pageindex",SqlDbType.Int,4),
new SqlParameter("@pageSize ",SqlDbType.Int,4),
new SqlParameter("@rowCount ",SqlDbType.Int,4),
new SqlParameter("@pageCount",SqlDbType.Int,4)
};
parameters[0].Value = pageIndex;
parameters[1].Value = pageSize;
parameters[2].Value = rowCount;
parameters[2].Direction = ParameterDirection.Output;
parameters[3].Value = pageCount;
parameters[3].Direction = ParameterDirection.Output;
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(constr)){
cmd.Connection = conn;
cmd.CommandText = "usp_Book_paging";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(parameters);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
rowCount = Convert.ToInt32(parameters[2].Value);
pageCount = Convert.ToInt32(parameters[3].Value);
return dt;
}
}
public static object FromDbValue(object value)
{
if (value == DBNull.Value)
{
return null;
}
else
{
return value;
}
}
public static object ToDbValue(object value)
{
if (value == null)
{
return DBNull.Value;
}
else
{
return value;
}
}
////////////////////执行存储过程的增删查改//////////////////////////
private static SqlConnection CNN
{
get
{
if (conn == null)
{
conn = new SqlConnection(constr);
}
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
return conn;
}
}
#region 连接等都写在一个方法里
private static SqlCommand CreateCommand(string cmdText, CommandType cmdType, SqlParameter[] parms)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = CNN;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if (parms != null)
{
cmd.Parameters.AddRange(parms);
}
return cmd;
}
///
#endregion <summary>
///执行增删改功能储存过程
/// </summary>
/// <param name="cmdText"></param>
/// <returns></returns>
#region ExecuteCommand
public static int ExecuteCommand(string cmdText)
{
return ExecuteCommand(cmdText, null);
}
public static int ExecuteCommand(string cmdText, SqlParameter[] parms)
{
return ExecuteCommand(cmdText, CommandType.StoredProcedure, parms);
}
public static int ExecuteCommand(string cmdText, CommandType cmdType, SqlParameter[] parms)
{
SqlCommand cmd = CreateCommand(cmdText, cmdType, parms);
return cmd.ExecuteNonQuery();
}
#endregion
/// <summary>
/// 返回一张表
/// </summary>
/// <param name="cmdText"></param>
/// <returns></returns>
#region GetTable
public static DataTable GetTable(string cmdText)
{
return GetTable(cmdText, null);
}
public static DataTable GetTable(string cmdText, SqlParameter[] parms)
{
return GetTable(cmdText, CommandType.StoredProcedure, parms);
}
public static DataTable GetTable(string cmdText, CommandType cmdType, SqlParameter[] parms)
{
DataTable table = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(CreateCommand(cmdText, cmdType, parms));
adapter.Fill(table);
return table;
}
#endregion
/// <summary>
/// SqlDataReader可以读一行行数据的对象、查询多行数据 SqlDataReader
/// </summary>
/// <param name="cmdText"></param>
/// <returns></returns>
#region GetReader
public static SqlDataReader GetReader(string cmdText)
{
return GetReader(cmdText, null);
}
public static SqlDataReader GetReader(string cmdText, SqlParameter[] parms)
{
return GetReader(cmdText, CommandType.StoredProcedure, parms);
}
public static SqlDataReader GetReader(string cmdText, CommandType cmdType, SqlParameter[] parms)
{
SqlCommand cmd = CreateCommand(cmdText, cmdType, parms);
return cmd.ExecuteReader();
}
#endregion
/// <summary>
/// 返回单个值
/// </summary>
/// <param name="cmdText"></param>
/// <returns></returns>
#region GetScalar
public static object GetScalar(string cmdText)
{
return GetScalar(cmdText, null);
}
public static object GetScalar(string cmdText, SqlParameter[] parms)
{
return GetScalar(cmdText, CommandType.StoredProcedure, parms);
}
public static object GetScalar(string cmdText, CommandType cmdType, SqlParameter[] parms)
{
SqlCommand cmd = CreateCommand(cmdText, cmdType, parms);
return cmd.ExecuteScalar();
}
#endregion
}
}
