取日三省之意,记生活珠玑,每日清新,谓日新阁.
另一种Access数据库操作类
using System;
using System.Data;
using System.Data.OleDb;
using System.Text;
namespace ###3
{
/**//// <summary>
/// GetData Access数据库。
/// </summary>
///
public class GetData
{
// private string connString =" Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source ="; //连接字符串
public OleDbConnection conn=null;
/*属性*/#region /*属性*/
/**//// <summary>
/// 连接数据库字符串
/// </summary>
private string p_connString=string.Empty;
public string ConnString
{
get
{
return p_connString;
}
set
{
p_connString= value;
}
}
/**//// <summary>
/// 表名
/// </summary>
private string tableName="****";
public string TableName
{
get
{
return tableName;
}
set
{
tableName= value;
}
}
#endregion
/**//// <summary>
/// 构造函数
/// </summary>
public GetData()
{
OleDbConnection conn = new OleDbConnection();
}
/***DbConnet***/#region/***DbConnet***/
/**//// <summary>
/// 定义数据连接的字符串
/// </summary>
/// <returns>OleDbConnection</returns>
private OleDbConnection ConnectToAccess()
{
OleDbConnection conn = new OleDbConnection();
conn.ConnectionString=this.p_connString;
try
{
conn.Open();
}
catch (Exception ex)
{
throw ex;
}
return conn;
}
#endregion
/***查询数据***/#region/***查询数据***/
/**//// <summary>
/// 查询数据
/// </summary>
/// <param name="operInt">操作查询语句的标示</param>
/// <returns>DataSet查询结果</returns>
public DataSet GetTreeDataSet(int operInt)
{
OleDbDataAdapter da=null;
string selectSQL=getStrByOperInt(operInt);
if(selectSQL=="")
{
return null;
}
conn=ConnectToAccess();
da = new OleDbDataAdapter(selectSQL,conn);
DataSet ds=new DataSet();
try
{
da.Fill(ds);
da.Dispose();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (conn.State!=ConnectionState.Closed)
conn.Close();
conn.Dispose();
}
return ds;
}
/**//// <summary>
/// 构造查询字符串
/// </summary>
/// <param name="operInt">操作查询语句的标示</param>
/// <returns>string</returns>
private string getStrByOperInt(int operInt)
{
string sqlStr=string.Empty;
try
{
switch(operInt)
{
case 1:
sqlStr=Get*DataStr();
break;
case 2:
sqlStr=Get1DataStr();
break;
case 3:
sqlStr=Get2DataStr();
break;
}
}
catch (Exception err)
{
throw new Exception(err.Message);
}
return sqlStr;
}
/***清除相同***/#region/***清除相同***/
/**//// <summary>
/// 清除StatckInfo表格中的相同记录
/// </summary>
/// <returns>是否成功清空</returns>
public bool DeleteSameStatckInfoData()
{
int rowCount=-1;
try
{
string strDelete=DeleteSameDataStr();
conn=ConnectToAccess();
OleDbCommand deleteCmd = new OleDbCommand(strDelete.ToString(),conn);
rowCount=deleteCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (conn.State!=ConnectionState.Closed)
conn.Close();
conn.Dispose();
}
if(rowCount==-1||rowCount==0)
{
return false;
}
else
{
return true;
}
}
/**//// <summary>
/// 删除##表中部分相同数据
/// </summary>
/// <returns>查询字符串</returns>
private string DeleteSameDataStr()
{
StringBuilder strDelete =new StringBuilder();
strDelete.Append(" DELETE * ");
strDelete.Append(" FROM ## ");
strDelete.Append(" WHERE id NOT IN ( ");
strDelete.Append(" SELECT MAX(id) FROM ## ");
strDelete.Append(" GROUP BY name,card,Fundsum,ID) ");
return strDelete.ToString();
}
#endregion
/***存在验证***/#region/***存在验证***/
/**//// <summary>
/// 某记录是否存在的查询
/// </summary>
/// <param name="statckinfo">一条记录</param>
/// <returns>是否存在</returns>
public bool GetDataExitsOrNot(StatckInfo statckinfo)
{
int rowCount=-1;
try
{
StringBuilder strSelect =new StringBuilder();
strSelect.Append(" SELECT count(*) as co FROM # ");
strSelect.Append("'");
conn=ConnectToAccess();
OleDbCommand selectCmd = new OleDbCommand(strSelect.ToString(),conn);
OleDbDataReader dr = selectCmd.ExecuteReader();
if(dr.Read())
{
if(!(dr["co"] is System.DBNull))
rowCount = Int32.Parse(dr["co"].ToString());
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (conn.State!=ConnectionState.Closed)
conn.Close();
conn.Dispose();
}
if(rowCount==-1||rowCount==0)
{
return false;
}
else
{
return true;
}
}
#endregion
/***合并字符***/#region/***合并字符***/
/**//// <summary>
/// 不同sql语句查询数据库，填充DataSet
/// </summary>
/// <param name="selectSQL">string</param>
/// <returns>DataSet</returns>
public DataSet GetOtherDataByIDreceipt(string selectSQL)
{
if(selectSQL=="")
{
return null;
}
conn=ConnectToAccess();
OleDbDataAdapter da = new OleDbDataAdapter(selectSQL,conn);
DataSet ds=new DataSet();
try
{
da.Fill(ds);
da.Dispose();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (conn.State!=ConnectionState.Closed)
conn.Close();
conn.Dispose();
}
return ds;
}
/**//// <summary>
/// 循环DataSet,用","将所有ID合并成“,”间隔的字符串
/// </summary>
/// <param name="ds">DataSet</param>
/// <returns>合并后的字符串</returns>
private string getStrByDatset(DataSet ds)
{
string characterStr=string.Empty;
if (ds==null)
{
return "";
}
try
{
DataTable myTable=ds.Tables[0];
int count=myTable.Rows.Count;
for(int i=0;i<myTable.Rows.Count;i++)
{
string name=myTable.Rows[i][0].ToString();
characterStr+=name+",";
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return characterStr;
}
#endregion
/***清空表格***/#region/***清空表格***/
/**//// <summary>
/// 清空##表格
/// </summary>
/// <returns>是否成功清空</returns>
public bool DeleteAllStatck()
{
int rowCount=-1;
try
{
string strDelete=" delete * FROM #·#";
conn=ConnectToAccess();
OleDbCommand deleteCmd = new OleDbCommand(strDelete.ToString(),conn);
rowCount=deleteCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (conn.State!=ConnectionState.Closed)
conn.Close();
conn.Dispose();
}
if(rowCount==-1||rowCount==0)
{
return false;
}
else
{
return true;
}
}
#endregion
}
}
