DataBaseHelper类。
1、执行sql语句
2、执行事物
数据库操作类
public class DBOperator
{
private SqlConnection _cn; //数据库连接
private string _strConfigPath;
public string StrConfigPath
{
get { return _strConfigPath; }
set { _strConfigPath = value; }
}
public SqlConnection Connection
{
get { return _cn; }
set { _cn = value; }
}
public DBOperator(string connectionString)
{
_cn = new SqlConnection(connectionString);
}
public DBOperator()
{
ConnectionString connectionString = XMLReader.ReadXml(_strConfigPath);
Common.ConnectionString = string.Format(Common.ConnectionString, connectionString.DbServerPath, connectionString.DbName,
connectionString.DbUser, connectionString.DbPwd);
_cn = new SqlConnection(Common.ConnectionString);
}
/// <summary>
/// 执行Sql语句,返回Sql语句影响的函数
/// </summary>
/// <param name="cmdText">待执行的Sql语句</param>
/// <returns>Sql语句影响的函数</returns>
public int ExecuteNonQuery(string cmdText)
{
int row = 0;
try
{
if (_cn != null && _cn.State != ConnectionState.Open)
{
_cn.Open();
}
SqlCommand cmd = new SqlCommand(cmdText, _cn);
cmd.CommandTimeout = 10000;
row = cmd.ExecuteNonQuery();
}
finally
{
if (_cn != null && _cn.State == ConnectionState.Open)
{
_cn.Close();
}
}
return row;
}
/// <summary>
/// 返回查询得到的结果及的第一行,第一列.
/// </summary>
/// <param name="cmdText">待执行的Sql语句</param>
/// <returns></returns>
public object ExecuteScalar(string cmdText)
{
object obj = null;
try
{
if (_cn != null && _cn.State != ConnectionState.Open)
{
_cn.Open();
}
SqlCommand cmd = new SqlCommand(cmdText, _cn);
cmd.CommandTimeout = 10000;
obj = cmd.ExecuteScalar();
}
finally
{
if (_cn != null && _cn.State == ConnectionState.Open)
{
_cn.Close();
}
}
return obj;
}
/// <summary>
/// 返回查询得到的结果集
/// </summary>
/// <param name="cmdText">sql语句</param>
/// <returns>DataSet</returns>
public DataSet ExecuteDataSet(string cmdText)
{
DataSet returnDs = null;
try
{
if (_cn != null && _cn.State != ConnectionState.Open)
{
_cn.Open();
}
SqlDataAdapter da = new SqlDataAdapter(cmdText, _cn);
DataSet ds = new DataSet();
da.Fill(ds);
returnDs = ds;
}
finally
{
if (_cn != null && _cn.State == ConnectionState.Open)
{
_cn.Close();
}
}
return returnDs;
}
/// <summary>
/// 返回查询到的结果集
/// </summary>
/// <param name="cmdText">sql语句</param>
/// <returns>DataTable</returns>
public DataTable ExecuteDataTable(string cmdText)
{
DataTable returnDt = null;
try
{
if (_cn != null && _cn.State != ConnectionState.Open)
{
_cn.Open();
}
SqlDataAdapter da = new SqlDataAdapter(cmdText, _cn);
DataSet ds = new DataSet();
da.Fill(ds);
returnDt = ds.Tables[0];
}
finally
{
if (_cn != null && _cn.State == ConnectionState.Open)
{
_cn.Close();
}
}
return returnDt;
}
public int ExecuteNonQuery(string cmdText,SqlTransaction transaction)
{
int row = 0;
//try
//{
//if (_cn != null && _cn.State != ConnectionState.Open)
//{
// _cn.Open();
//}
SqlCommand cmd = new SqlCommand(cmdText, _cn, transaction);
cmd.CommandTimeout = 10000;
row = cmd.ExecuteNonQuery();
//}
//finally
//{
// if (_cn != null && _cn.State == ConnectionState.Open)
// {
// _cn.Close();
// }
//}
return row;
}
/// <summary>
/// 返回查询得到的结果集
/// </summary>
/// <param name="cmdText">sql语句</param>
/// <returns>DataSet</returns>
public DataSet ExecuteDataSet(string cmdText,SqlTransaction transaction)
{
DataSet returnDs = null;
//try
//{
// if (_cn != null && _cn.State != ConnectionState.Open)
// {
// _cn.Open();
// }
SqlCommand cmd = new SqlCommand(cmdText, _cn, transaction);
cmd.CommandTimeout = 10000;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
returnDs = ds;
//}
//finally
//{
// if (_cn != null && _cn.State == ConnectionState.Open)
// {
// _cn.Close();
// }
//}
return returnDs;
}
公共类
class Common
{
public static string ConnectionString = "Data Source={0};Initial Catalog={1};User Id={2};Password={3}";
}
读取配置文件
class XMLReader
{
public static ConnectionString ReadXml(string inStrXmlPath)
{
ConnectionString returnConnectionString = new ConnectionString();
#region 读取XML
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(AppDomain.CurrentDomain.BaseDirectory + @"\Config.xml");
XmlNodeList nodeList = xmlDoc.SelectSingleNode("Connection").ChildNodes;
foreach (XmlNode xn in nodeList)
{
XmlElement xe = (XmlElement)xn;
if (xe.Name == "ConnectionString")
{
XmlNodeList nls = xe.ChildNodes;
foreach (XmlNode xn1 in nls)
{
XmlElement xe2 = (XmlElement)xn1;
switch (xe2.Name)
{
case "ServerPath":
returnConnectionString.DbServerPath = xe2.InnerText;
break;
case "User":
returnConnectionString.DbUser = xe2.InnerText;
break;
case "Pwd":
returnConnectionString.DbPwd = xe2.InnerText;
break;
case "DBName":
returnConnectionString.DbName = xe2.InnerText;
break;
}
}
}
}
#endregion
return returnConnectionString;
}
}
连接串实体类
class ConnectionString
{
private string _dbServerPath;
public string DbServerPath
{
get { return _dbServerPath; }
set { _dbServerPath = value; }
}
private string _dbUser;
public string DbUser
{
get { return _dbUser; }
set { _dbUser = value; }
}
private string _dbPwd;
public string DbPwd
{
get { return _dbPwd; }
set { _dbPwd = value; }
}
private string _dbName;
public string DbName
{
get { return _dbName; }
set { _dbName = value; }
}
}
配置文件
<?xml version="1.0" encoding="utf-8" ?>
<Connection>
<ConnectionString genre="电信">
<ServerPath>192.168.1.66</ServerPath>
<User>sa</User>
<Pwd>kingdee</Pwd>
<DBName>AIS20071113082248</DBName>
</ConnectionString>
</Connection>
浙公网安备 33010602011771号