数据库类的封装

每次写关于数据的基础操作很烦，所有就用老师所教的只是封装关于数据库的一些简单操作

在app.config中

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="SQLConn" value="server=.;database=MyDateBase;uid=sa;pwd=123456"/>
</appSettings>
</configuration>

在类中

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
static class SqlHleper
{
//只读的连接数据字符串
public static readonly string connstr = new System.Configuration.AppSettingsReader().GetValue("SQLConn", typeof(string)).ToString();

/// <summary>
/// 判断语句，关闭数据库
/// </summary>
static public void CloseConn(SqlConnection conn)
{
//判断数据库是否打开关闭，如果打开则关闭数据库，否则取反
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
else if (conn.State == ConnectionState.Closed)
{
conn.Open();
}

}

/// <summary>
/// 执行sql语句返回bool值
/// </summary>
/// <param name="str">字符串sql语句</param>
/// <param name="param">参数</param>
/// <returns>bool值</returns>
static public bool ExecuteNonQuery(string str, ref SqlParameter[] param)
{
SqlConnection conn= new SqlConnection(connstr);//创建连接对象;
conn.Open();
try
{
SqlCommand comm = new SqlCommand(str, conn);//创建command对象

if (param != null)
{
foreach (SqlParameter p in param)
{
comm.Parameters.Add(p);
}
}

if (comm.ExecuteNonQuery() == 0)
{
return false;
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
SqlHleper.CloseConn(conn);
}
return true;
}

/// <summary>
/// 执行sql语句返回dataset
/// </summary>
/// <param name="str">字符串sql语句</param>
/// <param name="param">参数</param>
/// <returns>DataSet</returns>
static public DataSet ExecuteDataSet(string str,ref SqlParameter[] param,CommandType cType)
{
SqlConnection conn = new SqlConnection(connstr);//创建连接对象
conn.Open();//打开数据库
DataSet ds;
try
{
//SqlCommand comm = new SqlCommand(str, conn);//创建command对象
SqlCommand comm = new SqlCommand();

if (param != null)
{
foreach (SqlParameter p in param)
{
comm.Parameters.Add(p);
}
}
//
//判断调用的类型是text还是存储过程
//
if( cType==CommandType.Text)
{
comm.CommandText = str;
comm.Connection = conn;
comm.CommandType = CommandType.Text;
}
else if (cType == CommandType.StoredProcedure)
{
comm.CommandText = str;
comm.Connection = conn;
comm.CommandType = CommandType.StoredProcedure;
}

SqlDataAdapter adapter = new SqlDataAdapter(comm);
ds = new DataSet();
adapter.Fill(ds);

}
catch (Exception ex)
{
ds = null;
throw new Exception(ex.ToString());
}
finally
{
SqlHleper.CloseConn(conn);
}
return ds;

}

/// <summary>
/// 执行sql语句返回SqlDataReader值
/// </summary>
/// <param name="str">字符串sql语句</param>
/// <param name="param">参数</param>
/// <returns>SqlDataReader值</returns>
static public SqlDataReader ExecuteReader(string str,ref SqlParameter[] param,CommandType cType)
{
SqlConnection conn = new SqlConnection(connstr);//创建连接对象
conn.Open();
try
{
SqlCommand comm = new SqlCommand();//创建command对象

if (param != null)
{
foreach (SqlParameter p in param)
{
comm.Parameters.Add(p);
}
}

//
//判断调用的类型是text还是存储过程
//
if (cType == CommandType.Text)
{
comm.CommandText = str;
comm.Connection = conn;
comm.CommandType = CommandType.Text;
}
else if (cType == CommandType.StoredProcedure)
{
comm.CommandText = str;
comm.Connection = conn;
comm.CommandType = CommandType.StoredProcedure;
}

return comm.ExecuteReader(CommandBehavior.CloseConnection);

}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}

}

/// <summary>
/// 执行sql语句返回第一行第一例的值
/// </summary>
/// <param name="str">字符串sql语句</param>
/// <param name="param">参数</param>
/// <returns>string的Scalar的值</returns>
static public string ExecuteScalar(string str, SqlParameter[] param)
{
SqlConnection conn= new SqlConnection(connstr);//创建连接对象;
try
{
SqlCommand comm = new SqlCommand(str, conn);//创建command对象
conn.Open();

if (param != null)
{
foreach (SqlParameter p in param)
{
comm.Parameters.Add(p);
}
}

if (comm.ExecuteScalar() != null)
{
return comm.ExecuteScalar().ToString();
}

}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
SqlHleper.CloseConn(conn);
}
return null;
}

#region 调用存储过程
/*
/// <summary>
/// 执行存储过程返回dataset
/// </summary>
/// <param name="str">存储过程名</param>
/// <param name="param">参数</param>
/// <returns>DataSet</returns>
static public DataSet ExecuteProc(string str, ref SqlParameter[] param)
{
SqlConnection conn = new SqlConnection(connstr); //创建连接对象
DataSet ds;
try
{
SqlCommand comm = new SqlCommand();//创建对象
comm.Connection = conn;
comm.CommandText = str;//存储过程名
comm.CommandType = CommandType.StoredProcedure;//执行类型StoredProcedure为存储过程默认为text
if (param != null)
{
foreach (SqlParameter p in param)
{
comm.Parameters.Add(p);
}
}

SqlDataAdapter adapter = new SqlDataAdapter(comm);
ds = new DataSet();
adapter.Fill(ds);
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
SqlHleper.CloseConn(conn);
}
return ds;
}
* */
#endregion

}
}