数据库设计好了,我们开始设计SQLHelper了,是一个SQL基类.
连接数据源:
private SqlConnection myConnection = null;
|
private readonly string RETURNVALUE = "RETURNVALUE";
|
打开数据库连接.
private void Open()
|
{
|
// 打开数据库连接
|
if (myConnection == null)
|
{
|
// myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
|
myConnection = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString());
|
|
}
|
if (myConnection.State == ConnectionState.Closed)
|
{
|
try
|
{
|
///打开数据库连接
|
myConnection.Open();
|
}
|
catch (Exception ex)
|
{
|
|
SystemError.CreateErrorLog(ex.Message);
|
}
|
finally
|
{
|
///关闭已经打开的数据库连接
|
}
|
}
|
}
|
关闭数据库连接
public void Close()
|
{
|
///判断连接是否已经创建
|
if (myConnection != null)
|
{
|
///判断连接的状态是否打开
|
if (myConnection.State == ConnectionState.Open)
|
{
|
myConnection.Close();
|
}
|
}
|
}
|
释放资源
public void Dispose()
|
{ |
// 确认连接是否已经关闭
|
if (myConnection != null)
|
{
|
myConnection.Dispose();
|
myConnection = null;
|
}
|
} |
执行无参数和返回int型的存储过程
public int RunProc(string procName)
|
{
|
SqlCommand cmd = CreateProcCommand(procName, null);
|
try
|
{
|
///执行存储过程
|
cmd.ExecuteNonQuery();
|
}
|
catch (Exception ex)
|
{
|
///记录错误日志
|
SystemError.CreateErrorLog(ex.Message);
|
}
|
finally
|
{
|
///关闭数据库的连接
|
Close();
|
}
|
///返回存储过程的参数值
|
return (int)cmd.Parameters[RETURNVALUE].Value;
|
}
|
执行传入参数和返回int型的存储过程
public int RunProc(string procName, SqlParameter[] prams)
|
{
|
SqlCommand cmd = CreateProcCommand(procName, prams);
|
try
|
{
|
///执行存储过程
|
cmd.ExecuteNonQuery();
|
}
|
catch (Exception ex)
|
{
|
///记录错误日志
|
SystemError.CreateErrorLog(ex.Message);
|
}
|
finally
|
{
|
///关闭数据库的连接
|
Close();
|
}
|
///返回存储过程的参数值
|
return (int)cmd.Parameters[RETURNVALUE].Value;
|
}
|
执行存储过程和返回SqlDataReader
public void RunProc(string procName, out SqlDataReader dataReader)
|
{ |
///创建Command
|
SqlCommand cmd = CreateProcCommand(procName, null);
|
try
|
{
|
///读取数据
|
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
|
}
|
catch (Exception ex)
|
{
|
dataReader = null;
|
///记录错误日志
|
SystemError.CreateErrorLog(ex.Message);
|
}
|
} |
执行传入参数和返回SqlDataReader存储过程
public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
|
{
|
///创建Command
|
SqlCommand cmd = CreateProcCommand(procName, prams);
|
try
|
{
|
///读取数据
|
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
|
}
|
catch (Exception ex)
|
{
|
dataReader = null;
|
///记录错误日志
|
SystemError.CreateErrorLog(ex.Message);
|
}
|
}
|
执行无参数存储过程返回DataSet
public void RunProc(string procName, ref DataSet dataSet)
|
{ |
if (dataSet == null)
|
{
|
dataSet = new DataSet();
|
}
|
///创建SqlDataAdapter
|
SqlDataAdapter da = CreateProcDataAdapter(procName, null);
|
try
|
{
|
///读取数据
|
da.Fill(dataSet);
|
}
|
catch (Exception ex)
|
{
|
///记录错误日志
|
SystemError.CreateErrorLog(ex.Message);
|
}
|
finally
|
{
|
///关闭数据库的连接
|
Close();
|
}
|
} |
执行传入参数的存储过程返回DataSet
public void RunProc(string procName, SqlParameter[] prams, ref DataSet dataSet)
|
{
|
if (dataSet == null)
|
{
|
dataSet = new DataSet();
|
}
|
///创建SqlDataAdapter
|
SqlDataAdapter da = CreateProcDataAdapter(procName, prams);
|
try
|
{
|
///读取数据
|
da.Fill(dataSet);
|
}
|
catch (Exception ex)
|
{
|
///记录错误日志
|
SystemError.CreateErrorLog(ex.Message);
|
}
|
finally
|
{
|
///关闭数据库的连接
|
Close();
|
}
|
}
|
执行传入参数和表名的存储过程返回DataSet
public void RunProc(string procName, SqlParameter[] prams, string TableName, ref DataSet dataSet)
|
{
|
if (dataSet == null)
|
{
|
dataSet = new DataSet();
|
}
|
///创建SqlDataAdapter
|
SqlDataAdapter da = CreateProcDataAdapter(procName, prams);
|
try
|
{
|
///读取数据
|
da.Fill(dataSet, TableName);
|
}
|
catch (Exception ex)
|
{
|
///记录错误日志
|
SystemError.CreateErrorLog(ex.Message);
|
}
|
finally
|
{
|
///关闭数据库的连接
|
Close();
|
}
|
}
|
执行无参数SQL语句
public int RunSQL(string cmdText)
|
{ |
SqlCommand cmd = CreateSQLCommand(cmdText, null);
|
try
|
{
|
///执行存储过程
|
cmd.ExecuteNonQuery();
|
}
|
catch (Exception ex)
|
{
|
///记录错误日志
|
SystemError.CreateErrorLog(ex.Message);
|
}
|
finally
|
{
|
///关闭数据库的连接
|
Close();
|
}
|
///返回存储过程的参数值
|
return (int)cmd.Parameters[RETURNVALUE].Value;
|
} |
执行传入参数SQL语句
public int RunSQL(string cmdText, SqlParameter[] prams)
|
{
|
SqlCommand cmd = CreateSQLCommand(cmdText, prams);
|
try
|
{
|
///执行存储过程
|
cmd.ExecuteNonQuery();
|
}
|
catch (Exception ex)
|
{
|
///记录错误日志
|
SystemError.CreateErrorLog(ex.Message);
|
}
|
finally
|
{
|
///关闭数据库的连接
|
Close();
|
}
|
///返回存储过程的参数值
|
return (int)cmd.Parameters[RETURNVALUE].Value;
|
}
|
执行无参数SQL语句返回SqlDataReader
public void RunSQL(string cmdText, out SqlDataReader dataReader)
|
{
|
///创建Command
|
SqlCommand cmd = CreateSQLCommand(cmdText, null);
|
try
|
{
|
///读取数据
|
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
|
}
|
catch (Exception ex)
|
{
|
dataReader = null;
|
///记录错误日志
|
SystemError.CreateErrorLog(ex.Message);
|
}
|
}
|
执行传入参数SQL语句返回SqlDataReader
public void RunSQL(string cmdText, SqlParameter[] prams, out SqlDataReader dataReader)
|
{ |
///创建Command
|
SqlCommand cmd = CreateSQLCommand(cmdText, prams);
|
try
|
{
|
///读取数据
|
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
|
}
|
catch (Exception ex)
|
{
|
dataReader = null;
|
///记录错误日志
|
SystemError.CreateErrorLog(ex.Message);
|
}
|
} |
执行无参数SQL语句返回DataSet
public void RunSQL(string cmdText, ref DataSet dataSet)
|
{
|
if (dataSet == null)
|
{
|
dataSet = new DataSet();
|
}
|
///创建SqlDataAdapter
|
SqlDataAdapter da = CreateSQLDataAdapter(cmdText, null);
|
try
|
{
|
///读取数据
|
da.Fill(dataSet);
|
}
|
catch (Exception ex)
|
{
|
///记录错误日志
|
SystemError.CreateErrorLog(ex.Message);
|
}
|
finally
|
{
|
///关闭数据库的连接
|
Close();
|
}
|
}
|
执行传入参数SQL语句返回DataSet
public void RunSQL(string cmdText, SqlParameter[] prams, ref DataSet dataSet)
|
{
|
if (dataSet == null)
|
{
|
dataSet = new DataSet();
|
}
|
///创建SqlDataAdapter
|
SqlDataAdapter da = CreateProcDataAdapter(cmdText, prams);
|
try
|
{
|
///读取数据
|
da.Fill(dataSet);
|
}
|
catch (Exception ex)
|
{
|
///记录错误日志
|
SystemError.CreateErrorLog(ex.Message);
|
}
|
finally
|
{
|
///关闭数据库的连接
|
Close();
|
}
|
}
|
执行传入参数SQL语句和表名返回DataSet
public void RunSQL(string cmdText, SqlParameter[] prams, string TableName, ref DataSet dataSet)
|
{
|
if (dataSet == null)
|
{
|
dataSet = new DataSet();
|
}
|
///创建SqlDataAdapter
|
SqlDataAdapter da = CreateProcDataAdapter(cmdText, prams);
|
try
|
{
|
///读取数据
|
da.Fill(dataSet, TableName);
|
}
|
catch (Exception ex)
|
{
|
///记录错误日志
|
SystemError.CreateErrorLog(ex.Message);
|
}
|
finally
|
{
|
///关闭数据库的连接
|
Close();
|
}
|
}
|
创建一个SqlCommand对象以此来执行存储过程
private SqlCommand CreateProcCommand(string procName, SqlParameter[] prams)
|
{
|
///打开数据库连接
|
Open();
|
///设置Command
|
SqlCommand cmd = new SqlCommand(procName, myConnection);
|
cmd.CommandType = CommandType.StoredProcedure;
|
///添加把存储过程的参数
|
if (prams != null)
|
{
|
foreach (SqlParameter parameter in prams)
|
{
|
cmd.Parameters.Add(parameter);
|
}
|
}
|
///添加返回参数ReturnValue
|
cmd.Parameters.Add(
|
new SqlParameter(RETURNVALUE, SqlDbType.Int, 4, ParameterDirection.ReturnValue,
|
false, 0, 0, string.Empty, DataRowVersion.Default, null));
|
///返回创建的SqlCommand对象
|
return cmd;
|
}
|
创建一个SqlCommand对象以此来执行存储过程
private SqlCommand CreateSQLCommand(string cmdText, SqlParameter[] prams)
|
{
|
///打开数据库连接
|
Open();
|
///设置Command
|
SqlCommand cmd = new SqlCommand(cmdText, myConnection);
|
///添加把存储过程的参数
|
if (prams != null)
|
{
|
foreach (SqlParameter parameter in prams)
|
{
|
cmd.Parameters.Add(parameter);
|
}
|
}
|
///添加返回参数ReturnValue
|
cmd.Parameters.Add(
|
new SqlParameter(RETURNVALUE, SqlDbType.Int, 4, ParameterDirection.ReturnValue,
|
false, 0, 0, string.Empty, DataRowVersion.Default, null));
|
///返回创建的SqlCommand对象
|
return cmd;
|
}
|
创建一个SqlDataAdapter对象，用此来执行存储过程
private SqlDataAdapter CreateProcDataAdapter(string procName, SqlParameter[] prams)
|
{
|
///打开数据库连接
|
Open();
|
///设置SqlDataAdapter对象
|
SqlDataAdapter da = new SqlDataAdapter(procName, myConnection);
|
da.SelectCommand.CommandType = CommandType.StoredProcedure;
|
///添加把存储过程的参数
|
if (prams != null)
|
{
|
foreach (SqlParameter parameter in prams)
|
{
|
da.SelectCommand.Parameters.Add(parameter);
|
}
|
}
|
///添加返回参数ReturnValue
|
da.SelectCommand.Parameters.Add(
|
new SqlParameter(RETURNVALUE, SqlDbType.Int, 4, ParameterDirection.ReturnValue,
|
false, 0, 0, string.Empty, DataRowVersion.Default, null));
|
///返回创建的SqlDataAdapter对象
|
return da;
|
}
|
创建一个SqlDataAdapter对象，用此来执行SQL语句
private SqlDataAdapter CreateSQLDataAdapter(string cmdText, SqlParameter[] prams)
|
{
|
///打开数据库连接
|
Open();
|
///设置SqlDataAdapter对象
|
SqlDataAdapter da = new SqlDataAdapter(cmdText, myConnection);
|
///添加把存储过程的参数
|
if (prams != null)
|
{
|
foreach (SqlParameter parameter in prams)
|
{
|
da.SelectCommand.Parameters.Add(parameter);
|
}
|
}
|
///添加返回参数ReturnValue
|
da.SelectCommand.Parameters.Add(
|
new SqlParameter(RETURNVALUE, SqlDbType.Int, 4, ParameterDirection.ReturnValue,
|
false, 0, 0, string.Empty, DataRowVersion.Default, null));
|
///返回创建的SqlDataAdapter对象
|
return da;
|
}
|
生成存储过程参数
public SqlParameter CreateParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
|
{
|
SqlParameter param;
|
///当参数大小为0时，不使用该参数大小值
|
if (Size > 0)
|
{
|
param = new SqlParameter(ParamName, DbType, Size);
|
}
|
else
|
{
|
///当参数大小为0时，不使用该参数大小值
|
param = new SqlParameter(ParamName, DbType);
|
}
|
///创建输出类型的参数
|
param.Direction = Direction;
|
if (!(Direction == ParameterDirection.Output && Value == null))
|
{
|
param.Value = Value;
|
}
|
///返回创建的参数
|
return param;
|
}
|
传入输入参数
public SqlParameter CreateInParam(string ParamName, SqlDbType DbType, int Size, object Value)
|
{ |
return CreateParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
|
} |
传入返回值参数
public SqlParameter CreateOutParam(string ParamName, SqlDbType DbType, int Size)
|
{ |
return CreateParam(ParamName, DbType, Size, ParameterDirection.Output, null);
|
} |
传入返回值参数
public SqlParameter CreateReturnParam(string ParamName, SqlDbType DbType, int Size)
|
{ |
return CreateParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null);
|
} |
把所有这些方法放在SQLHelper.cs类里.再建一个SQLTools.cs,里面的方法如下:
public class SystemException : Exception
|
{
|
/// <summary>
|
/// 包含系统Excepton
|
/// </summary>
|
public SystemException(string source, string message, Exception inner)
|
: base(message, inner)
|
{
|
base.Source = source;
|
}
|
/// <summary>
|
/// 不包含系统Excepton
|
/// </summary>
|
public SystemException(string source, string message)
|
: base(message)
|
{
|
base.Source = source;
|
}
|
}
|
/// <summary>
|
/// 处理网页中的HTML代码，并消除危险字符
|
/// </summary>
|
public class SystemHTML
|
{
|
private static string HTMLEncode(string fString)
|
{
|
if (fString != string.Empty)
|
{
|
///替换尖括号
|
fString.Replace("<", "<");
|
fString.Replace(">", "&rt;");
|
///替换引号
|
fString.Replace(((char)34).ToString(), """);
|
fString.Replace(((char)39).ToString(), "'");
|
///替换空格
|
fString.Replace(((char)13).ToString(), "");
|
///替换换行符
|
fString.Replace(((char)10).ToString(), "<BR> ");
|
}
|
return (fString);
|
}
|
}
|
/// <summary>
|
/// SystemTools 的摘要说明。
|
/// </summary>
|
public class SystemTools
|
{
|
/// <summary>
|
/// 将DataReader转为DataTable
|
/// </summary>
|
/// <param name="DataReader">DataReader</param>
|
public static DataTable ConvertDataReaderToDataTable(SqlDataReader dataReader)
|
{
|
///定义DataTable
|
DataTable datatable = new DataTable();
|
try
|
{ ///动态添加表的数据列
|
for (int i = 0; i < dataReader.FieldCount; i++)
|
{
|
DataColumn myDataColumn = new DataColumn();
|
myDataColumn.DataType = dataReader.GetFieldType(i);
|
myDataColumn.ColumnName = dataReader.GetName(i);
|
datatable.Columns.Add(myDataColumn);
|
}
|
///添加表的数据
|
while (dataReader.Read())
|
{
|
DataRow myDataRow = datatable.NewRow();
|
for (int i = 0; i < dataReader.FieldCount; i++)
|
{
|
myDataRow[i] = dataReader[i].ToString();
|
}
|
datatable.Rows.Add(myDataRow);
|
myDataRow = null;
|
}
|
///关闭数据读取器
|
dataReader.Close();
|
return datatable;
|
}
|
catch (Exception ex)
|
{
|
///抛出类型转换错误
|
SystemError.CreateErrorLog(ex.Message);
|
throw new Exception(ex.Message, ex);
|
}
|
}
|
}
|
主要是处理异常和一些特殊字符.
再建一个SystemError.cs,里面的方法如下:
public class SystemError
|
{ |
private static string m_fileName = "c:\\Systemlog.txt";
|
public static String FileName
|
{
|
get
|
{
|
return (m_fileName);
|
}
|
set
|
{
|
if (value != null || value != "")
|
{
|
m_fileName = value;
|
}
|
}
|
}
|
public static void CreateErrorLog(string message)
|
{
|
if (File.Exists(m_fileName))
|
{
|
///如果日志文件已经存在，则直接写入日志文件
|
StreamWriter sr = File.AppendText(FileName);
|
sr.WriteLine("\n");
|
sr.WriteLine(DateTime.Now.ToString() + message);
|
sr.Close();
|
}
|
else
|
{
|
///创建日志文件
|
StreamWriter sr = File.CreateText(FileName);
|
sr.Close();
|
}
|
}
|
} |
主要记录日志.
http://www.cnblogs.com/springyangwc/archive/2011/03/23/1993061.html
