using
System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Text;
/**/ /// SQL数据库操作对象命名空间
namespace DBO
... {
/**//// 数据条目类,用来方便转换数据项目成各种格式
public class DateItem
...{
private Object fObject;
public DateItem(Object dataObj)
...{
if (dataObj == null)
throw new System.ArgumentNullException("数据条目对象参数不能为空");
fObject = dataObj;
}
//转换成字符串类型,DBnull转换为""
public override string ToString()
...{
string result = "";
if (!(fObject is DBNull))
result = fObject.ToString().TrimEnd(null);
return result;
}
//转换成数字类型,DBnull默认转换成0
public int ToInt()
...{
int result = 0;
int.TryParse(ToString(), out result);
return result;
}
//转换成日期类型
public DateTime ToDateTime()
...{
DateTime result = DateTime.Now;
result = System.Convert.ToDateTime(ToString());
return result;
}
//转换成bool型
public bool toBool()
...{
//尝试转换字符串
if (ToString().ToLower() == "true")
return true;
else if (ToString().ToLower() == "false")
return false;
else if (ToInt() != 0)
return true;
else
return false;
}
}
//数据库操作基础类
public class DBA
...{
//配置文件中连接字符串的名称
private static string CONN_STRING = "pc";
private string fQueryString;
// 当前数据游标位置
private int fCurIndex = 0;
private string fConnString;
private SqlConnection fConnection;
private SqlCommand fCommand;
private SqlDataAdapter fDataAdapter;
private DataSet fDataSet;
private SqlTransaction fsqlTrans;
public DBA()
...{
fConnString = GetConnString();
InitSQL("", null);
}
public DBA(string queryStr, params SqlParameter[] cmdParms)
...{
fConnString = GetConnString();
InitSQL(queryStr, cmdParms);
}
//初始化数据访问对象
private void InitSQL(string qString, SqlParameter[] cmdParms)
...{
fQueryString = qString;
fConnection = new SqlConnection(fConnString);
fConnection.Open();
fCommand = new SqlCommand();
fCommand.Connection = fConnection;
fCommand.CommandType = CommandType.Text;
fCommand.CommandText = fQueryString;
if (cmdParms != null)
PrepareSqlParms(fCommand, cmdParms);
fDataAdapter = new SqlDataAdapter();
fDataAdapter.SelectCommand = fCommand;
fDataSet = new DataSet();
if (fQueryString.Length > 0)
fDataAdapter.Fill(fDataSet);
}
//静态方法,SQL字符串清理:替换单引号
private static string sqlEncode(string sqlString)
...{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < sqlString.Length; i++)
sb.Append((sqlString[i] == ''') ? "''" : sqlString[i].ToString());
return sb.ToString();
}
//静态方法,给SqlCommand对象设定参数
private static void PrepareSqlParms(SqlCommand sqlCmd, SqlParameter[] cmdParms)
...{
sqlCmd.Parameters.Clear();
if (cmdParms.Length > 0)
...{
foreach (SqlParameter parm in cmdParms)
sqlCmd.Parameters.Add(parm);
}
}
//静态方法,返回DB连接字符串
public static string GetConnString()
...{
return ConfigurationManager.ConnectionStrings[CONN_STRING].ConnectionString;
}
//静态方法,用来执行执行一个查询(insert、update、delete),返回受影响的行数。
public static int ExecuteNonQuery(string queryString, params SqlParameter[] cmdParms)
...{
int result;
using (SqlConnection sqlConn = new SqlConnection(GetConnString()))
...{
sqlConn.Open();
SqlCommand sqlCmd = new SqlCommand(sqlEncode(queryString), sqlConn);
PrepareSqlParms(sqlCmd, cmdParms);
result = sqlCmd.ExecuteNonQuery();
sqlCmd.Parameters.Clear();
sqlConn.Close();
}
return result;
}
//静态方法,执行一个查询(count()、sum()等统计函数),返回结果集中第一行的第一列。
public static Object ExecuteScalar(string queryString, params SqlParameter[] cmdParms)
...{
Object result;
using (SqlConnection sqlConn = new SqlConnection(GetConnString()))
...{
sqlConn.Open();
SqlCommand sqlCmd = new SqlCommand(sqlEncode(queryString), sqlConn);
PrepareSqlParms(sqlCmd, cmdParms);
result = sqlCmd.ExecuteScalar();
sqlCmd.Parameters.Clear();
sqlConn.Close();
}
return result;
}
//静态方法,执行查询,返回sqlDataReader对象,Reader.Close()之后自动关闭连接
public static SqlDataReader QueryData(string queryString, params SqlParameter[] cmdParms)
...{
SqlConnection sqlConn = new SqlConnection(GetConnString());
sqlConn.Open();
SqlCommand sqlCmd = new SqlCommand(sqlEncode(queryString), sqlConn);
PrepareSqlParms(sqlCmd, cmdParms);
SqlDataReader sqlReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
return sqlReader;
}
// 缺省索引器
public DateItem this[string index]
...{
get
...{
object result;
try
...{
result = fDataSet.Tables[0].Rows[fCurIndex][index];
}
catch (ArgumentException e)
...{
result = "无此字段名(" + index + ")<br> " + e.Message;
}
catch (Exception e)
...{
result = "结果集中无记录<br> " + e.Message;
}
return new DateItem(result);
}
}
// 向后移动数据游标
public bool MoveNext()
...{
if (!EOF)
...{
fCurIndex++;
return true;
}
return false;
}
// 是否到达数据尾部
public bool EOF
...{
get ...{ return fCurIndex >= Count; }
}
//当前索引
public int CurrentIndex
...{
get ...{ return fCurIndex; }
}
public int Count
...{
get ...{ return Rows.Count; }
}
// 表的行集
public DataRowCollection Rows
...{
get ...{ return fDataSet.Tables[0].Rows; }
}
// 表的列集
public DataColumnCollection Columns
...{
get
...{
return fDataSet.Tables[0].Columns;
}
}
// 提供一个可供利用的数据源
public DataView DataSource
...{
get ...{ return fDataSet.Tables[0].DefaultView; }
}
// 执行已有查询,返回受影响的行数,结果集装载到内置对象中。
public int ReOpen()
...{
return fDataAdapter.Fill(fDataSet);
}
// 执行一个查询,返回受影响的行数,结果集装载到内置对象中。用于执行select。
public int Open(string queryString, params SqlParameter[] cmdParms)
...{
int result;
fQueryString = queryString;
fCommand.CommandText = fQueryString;
PrepareSqlParms(fCommand, cmdParms);
if (fsqlTrans != null)
fCommand.Transaction = fsqlTrans;
fDataSet.Clear();
result = fDataAdapter.Fill(fDataSet);
return result;
}
//执行一个查询返回影响行数
public int Execute(string queryString, params SqlParameter[] cmdParms)
...{
fQueryString = queryString;
fCommand.CommandText = fQueryString;
PrepareSqlParms(fCommand, cmdParms);
if (fsqlTrans != null)
fCommand.Transaction = fsqlTrans;
return fCommand.ExecuteNonQuery();
}
//开始一个事务操作
public void BeginTransaction()
...{
if ((fsqlTrans != null) && (fConnection != null))
fsqlTrans = fConnection.BeginTransaction();
}
//提交事务
public void Commit()
...{
if (fsqlTrans != null)
...{
fsqlTrans.Commit();
fsqlTrans = null;
}
}
//回滚事务
public void Rollback()
...{
if (fsqlTrans != null)
...{
fsqlTrans.Rollback();
fsqlTrans = null;
}
}
// 释放数据库连接。
public void Close()
...{
if (fDataSet != null)
fDataSet.Dispose();
if (fConnection != null)
...{
fConnection.Close();
fConnection.Dispose();
}
if (fCommand != null)
fCommand.Dispose();
if (fDataAdapter != null)
fDataAdapter.Dispose();
}
}
}
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Text;
/**/ /// SQL数据库操作对象命名空间
namespace DBO
... {
/**//// 数据条目类,用来方便转换数据项目成各种格式
public class DateItem
...{
private Object fObject;
public DateItem(Object dataObj)
...{
if (dataObj == null)
throw new System.ArgumentNullException("数据条目对象参数不能为空");
fObject = dataObj;
}
//转换成字符串类型,DBnull转换为""
public override string ToString()
...{
string result = "";
if (!(fObject is DBNull))
result = fObject.ToString().TrimEnd(null);
return result;
}
//转换成数字类型,DBnull默认转换成0
public int ToInt()
...{
int result = 0;
int.TryParse(ToString(), out result);
return result;
}
//转换成日期类型
public DateTime ToDateTime()
...{
DateTime result = DateTime.Now;
result = System.Convert.ToDateTime(ToString());
return result;
}
//转换成bool型
public bool toBool()
...{
//尝试转换字符串
if (ToString().ToLower() == "true")
return true;
else if (ToString().ToLower() == "false")
return false;
else if (ToInt() != 0)
return true;
else
return false;
}
}
//数据库操作基础类
public class DBA
...{
//配置文件中连接字符串的名称
private static string CONN_STRING = "pc";
private string fQueryString;
// 当前数据游标位置
private int fCurIndex = 0;
private string fConnString;
private SqlConnection fConnection;
private SqlCommand fCommand;
private SqlDataAdapter fDataAdapter;
private DataSet fDataSet;
private SqlTransaction fsqlTrans;
public DBA()
...{
fConnString = GetConnString();
InitSQL("", null);
}
public DBA(string queryStr, params SqlParameter[] cmdParms)
...{
fConnString = GetConnString();
InitSQL(queryStr, cmdParms);
}
//初始化数据访问对象
private void InitSQL(string qString, SqlParameter[] cmdParms)
...{
fQueryString = qString;
fConnection = new SqlConnection(fConnString);
fConnection.Open();
fCommand = new SqlCommand();
fCommand.Connection = fConnection;
fCommand.CommandType = CommandType.Text;
fCommand.CommandText = fQueryString;
if (cmdParms != null)
PrepareSqlParms(fCommand, cmdParms);
fDataAdapter = new SqlDataAdapter();
fDataAdapter.SelectCommand = fCommand;
fDataSet = new DataSet();
if (fQueryString.Length > 0)
fDataAdapter.Fill(fDataSet);
}
//静态方法,SQL字符串清理:替换单引号
private static string sqlEncode(string sqlString)
...{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < sqlString.Length; i++)
sb.Append((sqlString[i] == ''') ? "''" : sqlString[i].ToString());
return sb.ToString();
}
//静态方法,给SqlCommand对象设定参数
private static void PrepareSqlParms(SqlCommand sqlCmd, SqlParameter[] cmdParms)
...{
sqlCmd.Parameters.Clear();
if (cmdParms.Length > 0)
...{
foreach (SqlParameter parm in cmdParms)
sqlCmd.Parameters.Add(parm);
}
}
//静态方法,返回DB连接字符串
public static string GetConnString()
...{
return ConfigurationManager.ConnectionStrings[CONN_STRING].ConnectionString;
}
//静态方法,用来执行执行一个查询(insert、update、delete),返回受影响的行数。
public static int ExecuteNonQuery(string queryString, params SqlParameter[] cmdParms)
...{
int result;
using (SqlConnection sqlConn = new SqlConnection(GetConnString()))
...{
sqlConn.Open();
SqlCommand sqlCmd = new SqlCommand(sqlEncode(queryString), sqlConn);
PrepareSqlParms(sqlCmd, cmdParms);
result = sqlCmd.ExecuteNonQuery();
sqlCmd.Parameters.Clear();
sqlConn.Close();
}
return result;
}
//静态方法,执行一个查询(count()、sum()等统计函数),返回结果集中第一行的第一列。
public static Object ExecuteScalar(string queryString, params SqlParameter[] cmdParms)
...{
Object result;
using (SqlConnection sqlConn = new SqlConnection(GetConnString()))
...{
sqlConn.Open();
SqlCommand sqlCmd = new SqlCommand(sqlEncode(queryString), sqlConn);
PrepareSqlParms(sqlCmd, cmdParms);
result = sqlCmd.ExecuteScalar();
sqlCmd.Parameters.Clear();
sqlConn.Close();
}
return result;
}
//静态方法,执行查询,返回sqlDataReader对象,Reader.Close()之后自动关闭连接
public static SqlDataReader QueryData(string queryString, params SqlParameter[] cmdParms)
...{
SqlConnection sqlConn = new SqlConnection(GetConnString());
sqlConn.Open();
SqlCommand sqlCmd = new SqlCommand(sqlEncode(queryString), sqlConn);
PrepareSqlParms(sqlCmd, cmdParms);
SqlDataReader sqlReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
return sqlReader;
}
// 缺省索引器
public DateItem this[string index]
...{
get
...{
object result;
try
...{
result = fDataSet.Tables[0].Rows[fCurIndex][index];
}
catch (ArgumentException e)
...{
result = "无此字段名(" + index + ")<br> " + e.Message;
}
catch (Exception e)
...{
result = "结果集中无记录<br> " + e.Message;
}
return new DateItem(result);
}
}
// 向后移动数据游标
public bool MoveNext()
...{
if (!EOF)
...{
fCurIndex++;
return true;
}
return false;
}
// 是否到达数据尾部
public bool EOF
...{
get ...{ return fCurIndex >= Count; }
}
//当前索引
public int CurrentIndex
...{
get ...{ return fCurIndex; }
}
public int Count
...{
get ...{ return Rows.Count; }
}
// 表的行集
public DataRowCollection Rows
...{
get ...{ return fDataSet.Tables[0].Rows; }
}
// 表的列集
public DataColumnCollection Columns
...{
get
...{
return fDataSet.Tables[0].Columns;
}
}
// 提供一个可供利用的数据源
public DataView DataSource
...{
get ...{ return fDataSet.Tables[0].DefaultView; }
}
// 执行已有查询,返回受影响的行数,结果集装载到内置对象中。
public int ReOpen()
...{
return fDataAdapter.Fill(fDataSet);
}
// 执行一个查询,返回受影响的行数,结果集装载到内置对象中。用于执行select。
public int Open(string queryString, params SqlParameter[] cmdParms)
...{
int result;
fQueryString = queryString;
fCommand.CommandText = fQueryString;
PrepareSqlParms(fCommand, cmdParms);
if (fsqlTrans != null)
fCommand.Transaction = fsqlTrans;
fDataSet.Clear();
result = fDataAdapter.Fill(fDataSet);
return result;
}
//执行一个查询返回影响行数
public int Execute(string queryString, params SqlParameter[] cmdParms)
...{
fQueryString = queryString;
fCommand.CommandText = fQueryString;
PrepareSqlParms(fCommand, cmdParms);
if (fsqlTrans != null)
fCommand.Transaction = fsqlTrans;
return fCommand.ExecuteNonQuery();
}
//开始一个事务操作
public void BeginTransaction()
...{
if ((fsqlTrans != null) && (fConnection != null))
fsqlTrans = fConnection.BeginTransaction();
}
//提交事务
public void Commit()
...{
if (fsqlTrans != null)
...{
fsqlTrans.Commit();
fsqlTrans = null;
}
}
//回滚事务
public void Rollback()
...{
if (fsqlTrans != null)
...{
fsqlTrans.Rollback();
fsqlTrans = null;
}
}
// 释放数据库连接。
public void Close()
...{
if (fDataSet != null)
fDataSet.Dispose();
if (fConnection != null)
...{
fConnection.Close();
fConnection.Dispose();
}
if (fCommand != null)
fCommand.Dispose();
if (fDataAdapter != null)
fDataAdapter.Dispose();
}
}
}