注：数据库（表名 job id 工作id ,job工作字段）
数据库放在app_data文件中。名称为database.mdb
如果用codesmith生成，选择的数据库连接类型如下图：
项目结构图：
webconfig配置如下：（在configuration中添加）
<connectionStrings>
<add name="ConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\database.mdb" providerName="System.Data.OleDb"/>
</connectionStrings>
OleDbHelper.cs类：
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OleDb; public class OleDbHelper
{
//从配置文件中读取连接字符串
private static string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString(); #region 增删改
public static int ExecuteNonQuery(string commandText, CommandType commandType, params OleDbParameter[] commandParameters)
{
int row = ;
using (OleDbConnection conn = new OleDbConnection(ConnectionString))
{
OleDbCommand cmd = new OleDbCommand();
string os = null;
PrepareCommand(cmd, commandType, conn, commandText, commandParameters);
row = cmd.ExecuteNonQuery();
}
return row;
}
#endregion #region 返回实体集
public static OleDbDataReader ExecuteReader(string commandText, CommandType commandType, params OleDbParameter[] commandParameters)
{
OleDbDataReader dr = null;
OleDbConnection conn = new OleDbConnection(ConnectionString);
try
{
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, commandType, conn, commandText, commandParameters);
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
conn.Close();
throw;
}
return dr;
}
#endregion #region ExecuteScalar
/// <summary>
/// 执行Sql Server存储过程
/// 注意：不能执行有out 参数的存储过程
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="spName">存储过程名</param>
/// <param name="parameterValues">对象参数</param>
/// <returns>执行结果对象</returns>
public static object ExecuteScalar(string commandText, CommandType commandType,params OleDbParameter[] commandParameters)
{
OleDbCommand cmd = new OleDbCommand(); using (OleDbConnection conn = new OleDbConnection(ConnectionString))
{
PrepareCommand(cmd, commandType, conn, commandText, commandParameters);
object val = cmd.ExecuteScalar();
return val;
}
} #endregion #region Private Method
/// <summary>
/// 设置一个等待执行的OleDbCommand对象
/// </summary>
/// <param name="cmd">OleDbCommand 对象，不允许空对象</param>
/// <param name="conn">OleDbConnection 对象，不允许空对象</param>
/// <param name="commandText">Sql 语句</param>
/// <param name="cmdParms">OleDbParameters 对象,允许为空对象</param>
private static void PrepareCommand(OleDbCommand cmd, CommandType commandType, OleDbConnection conn, string commandText, OleDbParameter[] cmdParms)
{
//打开连接
if (conn.State != ConnectionState.Open)
conn.Open(); //设置OleDbCommand对象
cmd.Connection = conn;
cmd.CommandText = commandText;
cmd.CommandType = commandType; if (cmdParms != null)
{
foreach (OleDbParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
#endregion #region 返回一个表的数据
public static DataSet ExcuteDataSet(string cmdText, params OleDbParameter[] pars)
{
using (OleDbConnection con = new OleDbConnection(ConnectionString))
{
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, CommandType.Text, con, cmdText, pars);
DataSet ds = new DataSet();
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
da.Fill(ds, "Authorities");
return ds;
}
}
}
#endregion
}
jobs.cs类：
using System;
using System.Collections.Generic;
using System.Web; namespace WebTestAccess
{
public class jobs
{
public jobs() { } public jobs(string job)
{
this.job = job;
} public jobs(int id, string job)
{
this.id = id;
this.job = job;
}
private int id; public int Id
{
get { return id; }
set { id = value; }
} private string job; public string Job
{
get { return job; }
set { job = value; }
}
}
}
jobservice.cs类：
using System;
using System.Collections.Generic;
using System.Web;
using System.Data.OleDb;
using System.Configuration;
using System.Data; namespace WebTestAccess
{
public class jobservice
{
public List<jobs> getjobsall()
{
List<jobs> jobss = new List<jobs>();
string sql = "select * from jobs";
using(OleDbDataReader dr=OleDbHelper.ExecuteReader(sql,CommandType.Text))
{
while(dr.Read())
{
jobs job = new jobs(
Convert.ToInt32(dr["id"]),
dr["job"].ToString()
);
jobss.Add(job);
}
}
return jobss;
} //id为自增。
public int addjob(jobs job)
{
int result = -;
string sql = string.Format("insert into jobs(job) values('{0}')",job.Job);
OleDbParameter para = new OleDbParameter("@job", job.Job);
result = OleDbHelper.ExecuteNonQuery(sql, CommandType.Text, para);
return result;
}
}
}