数据库设计好了,我们开始设计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