110,570
社区成员
发帖
与我相关
我的任务
分享
public class DataHelper
{
// 连接数据源
public SqlConnection con = null;
public OleDbConnection conn = null;
/// <summary>
/// 数据库连接类型
/// </summary>
public enum dbType
{
/// <summary>
/// SQL数据库
/// </summary>
sql,
/// <summary>
/// access数据库
/// </summary>
access
}
//连接数据类型
dbType cType;
public DataHelper(string conStr,dbType type)
{
this.cType = type;
if (type == dbType.sql)
con = new SqlConnection(conStr);
else if (type == dbType.access)
conn = new OleDbConnection(conStr);
}
/// <summary>
/// 根据SQL查询返回DataSet对象,如果没有查询到则返回NULL
/// </summary>
/// <param name="sql">查询语句</param>
/// <returns>DataSet</returns>
public DataSet returnDS(string sql)
{
DataSet ds = new DataSet();
try
{
if (cType == dbType.sql)
{
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandTimeout = 20;
this.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds);
}
else if (cType == dbType.access)
{
OleDbCommand cmd = new OleDbCommand(sql, conn);
cmd.CommandTimeout = 20;
this.Open();
System.Data.OleDb.OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
adapter.Fill(ds);
}
}
catch (Exception e)
{
ds = null;
throw (e);
}
finally
{
this.Close();
}
return ds;
}
/// <summary>
/// 根据SQL查询返回DataSet对象,如果没有查询到则返回NULL
/// </summary>
/// <param name="sql">查询语句</param>
/// <param name="sRecord">开始记录数</param>
/// <param name="mRecord">最大记录数</param>
/// <param name="strTableName">表名</param>
/// <returns>DataSet</returns>
public DataSet returnDS(string sql, int sRecord, int mRecord, string strTableName)
{
DataSet ds = new DataSet();
try
{
if (cType == dbType.sql)
{
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandTimeout = 20;
this.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds, sRecord, mRecord, strTableName);
}
else if (cType == dbType.access)
{
OleDbCommand cmd = new OleDbCommand(sql, conn);
cmd.CommandTimeout = 20;
this.Open();
System.Data.OleDb.OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
adapter.Fill(ds, sRecord, mRecord, strTableName);
}
}
catch (Exception e)
{
ds = null;
throw (e);
}
finally
{
this.Close();
}
return ds;
}
/// <summary>
/// 对数据库的增,删,改的操作
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>是否成功</returns>
public bool OperateDB(string sql)
{
bool succeed = false;
int cnt = 0;
try
{
if (cType == dbType.sql)
{
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandTimeout = 20;
this.Open();
cnt = cmd.ExecuteNonQuery();
}
else if (cType == dbType.access)
{
OleDbCommand cmd = new OleDbCommand(sql, conn);
cmd.CommandTimeout = 20;
this.Open();
cnt = cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
throw (e);
}
finally
{
if (cnt > 0)
{
succeed = true;
}
this.Close();
}
return succeed;
}
/// <summary>
/// 获得该SQL查询返回的第一行第一列的值,如果没有查询到则返回NULL
/// </summary>
/// <param name="sql">查询语句</param>
/// <returns>返回的第一行第一列的值</returns>
public string getValue(string sql)
{
string str = null;
try
{
if (cType == dbType.sql)
{
SqlCommand cmd = new SqlCommand(sql, con);
this.Open();
str = cmd.ExecuteScalar().ToString();
}
else if (cType == dbType.access)
{
OleDbCommand cmd = new OleDbCommand(sql, conn);
this.Open();
str = cmd.ExecuteScalar().ToString();
}
}
catch (Exception e)
{
throw (e);
}
finally
{
this.Close();
}
return str;
}
/// <summary>
/// 获得该SQL查询返回DataTable,如果没有查询到则返回NULL
/// </summary>
/// <param name="sql">查询语句</param>
/// <returns></returns>
public DataTable getTable(string sql)
{
DataTable tb = null;
DataSet ds = this.returnDS(sql);
if (ds != null)
{
tb = ds.Tables[0];
}
return tb;
}
/// <summary>
/// 获得该SQL查询填充到 指定DataTable
/// </summary>
/// <param name="tb">指定的Table</param>
/// <param name="sql"> SQL 语句</param>
/// <returns></returns>
public DataTable FillTable(DataTable tb, string sql)
{
DataSet ds = tb.DataSet;
if (ds == null)
{
ds = new DataSet();
ds.Tables.Add(tb);
}
try
{
if (cType == dbType.sql)
{
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandTimeout = 20;
this.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds,tb.TableName);
}
else if (cType == dbType.access)
{
OleDbCommand cmd = new OleDbCommand(sql, conn);
cmd.CommandTimeout = 20;
this.Open();
System.Data.OleDb.OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
adapter.Fill(ds, tb.TableName);
}
}
catch (Exception e)
{
ds = null;
throw (e);
}
finally
{
this.Close();
}
if (ds != null)
{
return ds.Tables[0];
}
else
{
return null;
}
}
/// <summary>
/// 打开数据库连接.
/// </summary>
private void Open()
{
if (cType == dbType.sql)
{
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
else if (con.State == System.Data.ConnectionState.Broken)
{
con.Close();
con.Open();
}
}
else if (cType == dbType.access)
{
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
else if (conn.State == System.Data.ConnectionState.Broken)
{
conn.Close();
conn.Open();
}
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close()
{
if (cType == dbType.sql)
{
if (con != null)
{
con.Close();
}
}
else if (cType == dbType.access)
{
if (conn != null)
{
conn.Close();
}
}
}
}
using System;
using System.Data;
using System.Data.OleDb;
namespace zBirthdayReminderLib
{
public class BaseDao
{
private OleDbDataAdapter adapter;
private OleDbConnection conn;
private OleDbCommand command;
public bool modify(zContacter contacter, int id)
{
bool ret = false;
string sql = "update contacter";
sql += " set name = '" + contacter.Name + "', ";
sql += "cl_year = " + contacter.ChineseLunarBirthday.Year + ", ";
sql += "cl_month = '" + contacter.ChineseLunarBirthday.Month + "', ";
sql += "cl_day ='" + contacter.ChineseLunarBirthday.Day + "', ";
sql += "ge_year =" + contacter.GregorianBirthday.Year + ", ";
sql += "ge_month =" + contacter.GregorianBirthday.Month + ", ";
sql += "ge_day = " + contacter.GregorianBirthday.Day + " ";
sql += "where id = " + id;
conn = new OleDbConnection(ConstDefine.DB_CONN_STRING);
command = new OleDbCommand(sql, conn);
try
{
conn.Open();
command.ExecuteNonQuery();
if (conn != null)
{
conn.Close();
}
ret = true;
}
catch (System.Exception e)
{
Console.WriteLine(sql);
Console.WriteLine(e.Message);
ret = false;
}
return ret;
}
public bool delete(int id)
{
bool ret = false;
string sql = "delete * from contacter where id = " + id;
conn = new OleDbConnection(ConstDefine.DB_CONN_STRING);
command = new OleDbCommand(sql, conn);
try
{
conn.Open();
command.ExecuteNonQuery();
if (conn != null)
{
conn.Close();
}
ret = true;
}
catch (System.Exception e)
{
Console.WriteLine(sql);
Console.WriteLine(e.Message);
ret = false;
}
return ret;
}
public bool insert(zContacter contacter)
{
bool ret = false;
string sql = "insert into contacter (name, cl_year, cl_month, cl_day, ge_year, ge_month, ge_day) values ( ";
sql += "'" + contacter.Name + "',";
sql += contacter.ChineseLunarBirthday.Year + ", " ;
sql += "'" + contacter.ChineseLunarBirthday.Month + "', " ;
sql += "'" + contacter.ChineseLunarBirthday.Day + "', ";
sql += contacter.GregorianBirthday.Year + ", ";
sql += contacter.GregorianBirthday.Month + ", ";
sql += contacter.GregorianBirthday.Day + " )";
conn = new OleDbConnection(ConstDefine.DB_CONN_STRING);
command = new OleDbCommand(sql, conn);
try
{
conn.Open();
command.ExecuteNonQuery();
if (conn != null)
{
conn.Close();
}
ret = true;
}
catch (System.Exception e)
{
Console.WriteLine(sql);
Console.WriteLine(e.Message);
ret = false;
}
return ret;
}
public DataSet QueryAll()
{
string sql = "select * from contacter where name is not null";
conn = new OleDbConnection(ConstDefine.DB_CONN_STRING);
command = new OleDbCommand(sql);
command.Connection = conn;
command.CommandType = System.Data.CommandType.Text;
adapter = new OleDbDataAdapter();
adapter.SelectCommand = command;
DataSet ds = new DataSet();
try
{
adapter.Fill(ds);
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
return new DataSet();
}
return ds;
}
}
}
private static string DB_FILE
{
get
{
return System.AppDomain.CurrentDomain.BaseDirectory + "\\data.mdb";
}
}
public static string DB_CONN_STRING
{
get
{
return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ConstDefine.DB_FILE;
}
}