using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
namespace Sun.WebExam
{
/// <summary>
/// LocalDb 的摘要说明。
/// </summary>
public class LocalDb
{
OleDbConnection conn;
public LocalDb()
{
//
// TODO: 在此处添加构造函数逻辑
//
//在此处设置访问的数据库文件
string strConn=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Application.StartupPath+"//Data//SunWebExam.mdb";
conn=new OleDbConnection(strConn);
}
private void Open()
{
if(conn==null)
{
//在此处设置访问的数据库文件
string strConn=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Application.StartupPath+"//Data//SunWebExam.mdb";
conn=new OleDbConnection(strConn);
conn.Open();
}
else if(conn.State==System.Data.ConnectionState.Closed)
conn.Open();
}
public void Close()
{
if (conn != null)
conn.Close();
}
/// <summary>
/// 执行一条不需要返回值SQL命令,比如插入、删除
/// </summary>
/// <param name="commtext"></param>
public void ExecCommand(string commtext)
{
Open();
OleDbCommand cmd = new OleDbCommand(commtext,conn);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
}
/// <summary>
/// 执行多条不需要返回值SQL命令,比如插入、删除
/// </summary>
/// <param name="commtext"></param>
public void ExecCommand(string[] commtext)
{
Open();
for(int i=0;i<commtext.Length;i++)
{
OleDbCommand cmd = new OleDbCommand(commtext[i],conn);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
cmd.Dispose();
//conn.Close();
}
}
/// <summary>
/// 执行一条不需要返回值SQL命令,比如插入、删除,可以带一个参数数组
/// </summary>
/// <param name="commtext"></param>
/// <param name="prams"></param>
public void ExecCommand(string commtext,OleDbParameter[] prams)
{
Open();
OleDbCommand cmd = new OleDbCommand(commtext,conn);
cmd.CommandType = CommandType.Text;
if (prams != null)
{
foreach (OleDbParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
cmd.ExecuteNonQuery();
cmd.Dispose();
}
/// <summary>
/// 执行一条统计类的SQL语句,返回一个整数
/// </summary>
/// <param name="CommText"></param>
/// <returns></returns>
public int ExecScalar(string CommText)
{
Open();
OleDbCommand cmd=new OleDbCommand(CommText,conn);
cmd.CommandType=CommandType.Text;
return cmd.ExecuteScalar()!=null?(int)cmd.ExecuteScalar():0;
conn.Close();
}
/// <summary>
/// 执行一条SQL语句,输出一个DataRerader
/// </summary>
/// <param name="commtext"></param>
/// <param name="dr"></param>
public void ExecQuery(string commtext,out OleDbDataReader dr)
{
Open();
OleDbCommand cmd = new OleDbCommand(commtext,conn);
cmd.CommandType = CommandType.Text;
dr=cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
cmd.Dispose();
//conn.Close();
}
/// <summary>
/// 执行一条OleDb语句,输出一个DataSet
/// </summary>
/// <param name="CmdText"></param>
/// <param name="TableName"></param>
/// <param name="DS"></param>
public void Exec4DS(string CmdText,string TableName,out DataSet DS)
{
Open();
DS=new DataSet();
OleDbDataAdapter da=new OleDbDataAdapter(CmdText,conn);
da.Fill(DS,TableName);
conn.Close();
}
/// <summary>
/// 执行一条带参数的SQL语句,并返回一个命名了数据表的数据集
/// </summary>
/// <param name="cmdtext">SQL语句</param>
/// <param name="tablename">表名</param>
/// <param name="prams">参数数组</param>
/// <param name="ds">数据集</param>
public void Exec4DS(string cmdtext,string tablename,OleDbParameter[] prams,out DataSet ds)
{
Open();
ds=new DataSet();
OleDbCommand cmd = new OleDbCommand(cmdtext, conn);
cmd.CommandType = CommandType.Text;
if (prams != null)
{
foreach (OleDbParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
OleDbDataAdapter da=new OleDbDataAdapter(cmd);
da.Fill(ds,tablename);
}
/// <summary>
/// 一次执行多条SQL语句,将多个表填入一个DataSet
/// </summary>
/// <param name="sqlCmds">SQL语句数组</param>
/// <param name="TableNames">表名数组</param>
/// <param name="DS">数据集名</param>
public void ExecXDS(string[] sqlCmds,string[] TableNames,out DataSet DS)
{
Open();
DS=new DataSet();
for(int i=0;i<sqlCmds.Length;i++)
{
OleDbDataAdapter da=new OleDbDataAdapter(sqlCmds[i],conn);
da.Fill(DS,TableNames[i]);
}
}
}
}