多层结构开发模型
using System;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections;
using System.IO;
namespace DAL
{
public sealed class GetDal
{
private GetDal()
{
}
static readonly GetDal dal = new GetDal();
public static GetDal getda
{
get
{
return dal;
}
}
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ConnectionString);
/// <summary>
/// 返回 ilist 查询操作
/// </summary>
/// <param name="sql">sql</param>
/// <returns>ilist</returns>
public IList Query(string strsql)
{
using (SqlDataAdapter sda = new SqlDataAdapter(strsql, conn))
{
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
return dt.DefaultView as IList;
}
}
}
/// <summary>
/// 返回bool值,判断 添,删,改操作是否成功
/// </summary>
/// <param name="sql">sql</param>
/// <returns>bool</returns>
public bool myExecuteNonQuery(string strsql)
{
using (SqlCommand cmd = new SqlCommand(strsql, conn))
{
conn.Open();
try
{
cmd.ExecuteNonQuery();
return true;
}
catch
{
return false;
}
finally
{
conn.Close();
}
}
}
public DbDataReader GetReader(string strsql)
{
SqlCommand Command = new SqlCommand(strsql);
Command.Connection = (SqlConnection)conn;
return Command.ExecuteReader();
}
public DbDataAdapter GetsqlDataAd(string strsql)
{
SqlCommand Command = new SqlCommand(strsql);
Command.Connection = (SqlConnection)conn;
SqlDataAdapter sqlDataAd = new SqlDataAdapter();
sqlDataAd.SelectCommand = Command;
return sqlDataAd;
}
public object GetValue(string strsql)
{
SqlCommand Command = new SqlCommand(strsql);
Command.Connection = (SqlConnection)conn;
return Command.ExecuteScalar();
}
/// <summary>
/// 判断记录是否存在
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public bool SelectHaveNote(string sql)
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
conn.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
try
{
if (sdr.HasRows)
{
return true;
}
else
{
return false;
}
}
catch
{
return false;
}
finally
{
conn.Close();
}
}
}
}
/// <summary>
/// 查询 返回一条结果集
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public string selectOneQuery(string sql)
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
conn.Open();
// SqlDataReader sdr;
try
{
string str = cmd.ExecuteScalar().ToString();
return str;
}
catch
{
return null;
}
finally
{
conn.Close();
}
}
}
//////添加 image类型字段
////public bool addfile(string sql,byte[] bt)
////{
/* Stream str = FileUpload1.PostedFile.InputStream;
byte[] b=new byte[str.Length];
str.Read(b, 0, b.Length);*/
//// using (SqlCommand cmd = new SqlCommand())
//// {
//// conn.Open();
//// cmd.Connection = conn;
//// cmd.CommandType = CommandType.Text;
//// cmd.CommandText = "insert into imageinfo values(@i_data)";
//// cmd.Parameters.Add("@i_data", SqlDbType.Image, b.Length).Value = b;
//// cmd.ExecuteNonQuery();
//// }
////}
string con = System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ConnectionString.ToString();
//读取
public byte[] GetLob(string strsql)
{
//打开一个事务连接
SqlTransaction tx = null;
SqlConnection TransactionConnection = null;
try
{
TransactionConnection = new SqlConnection(con);
//读取一个二进制大对象
SqlCommand Command = new SqlCommand(strsql);
Command.Connection = TransactionConnection;
TransactionConnection.Open();
tx = TransactionConnection.BeginTransaction();
Command.Transaction = tx;
byte[] tmp = (byte[])this.GetValue(strsql);
TransactionConnection.Close();
tx.Commit();
return tmp;
}
catch (Exception ex)
{
if (tx != null)
{
tx.Rollback();
TransactionConnection.Close();
}
throw new Exception(ex.Message);
}
}
//添加
public bool SetLob(string strsql,byte[] buffer)
{
SqlTransaction tx = null;
SqlConnection TransactionConnection = null;
try
{
TransactionConnection = new SqlConnection(con);
TransactionConnection.Open();
tx = TransactionConnection.BeginTransaction();
SqlCommand cmd = TransactionConnection.CreateCommand();
cmd.Transaction = tx;
cmd.CommandText = strsql;
SqlParameter parBuffer = cmd.Parameters.Add("@ImportLob", SqlDbType.Image);
parBuffer.Direction = ParameterDirection.Input;
cmd.Parameters["@ImportLob"].Value = buffer;
cmd.ExecuteNonQuery();
tx.Commit();
TransactionConnection.Close();
return true;
}
catch (Exception ex)
{
/*
string str = ex.ToString(); //str="错误信息"
System.IO.StreamWriter writer = new System.IO.StreamWriter(@"F:\error.log");
char[] error = new char[str.Length];
for (int i = 0; i < error.Length; i++)
{
error[i] = (char)str[i];
}
writer.Write(error, 0, error.Length);
writer.Close();
*/
if (tx != null)
{
tx.Rollback();
TransactionConnection.Close();
return false;
}
throw new Exception(ex.Message);
}
}
}
}
2、数据工厂
using System;
using System.Collections.Generic;
using System.Text;
namespace DataFactory
{
public abstract class MyFactory //工厂模式
{
public abstract DAL.GetDal Create();
public static MyFactory CreateAbstract()
{
return DBFactory.getdf;
}
}
sealed class DBFactory : MyFactory //封装为单例
{
private DBFactory()
{ }
static readonly DBFactory df = new DBFactory();
public static DBFactory getdf
{
get
{
return df;
}
}
public override DAL.GetDal Create()
{
return DAL.GetDal.getda;
}
}
}
3、业务逻辑层
using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data.Common;
using System.Data;
namespace Bussiness
{
public partial class MyBussiness
{
public IList SelectFile(string where)
{
string sqlstr = "select * from TBClass where SLID ='"+where+"'";
return DataFactory.MyFactory.CreateAbstract().Create().Query(sqlstr);
}
public string SelectFileId(int id)
{
string sqlstr = "select 文件内容 from TBClass where id="+id+"";
return DataFactory.MyFactory.CreateAbstract().Create().selectOneQuery(sqlstr);
}
public IList SeeAllFile()
{
string sqlstr = "select * from TBClass";
return DataFactory.MyFactory.CreateAbstract().Create().Query(sqlstr);
}
public IDataReader SelectFileGut(string id)
{
string sqlstr="select * from TBClass where id="+id+"";
return DataFactory.MyFactory.CreateAbstract().Create().GetReader(sqlstr);
}
}
}
4、表示层-创建逻辑层对象调用函数!
Bussiness.MyBussiness mybs = new Bussiness.MyBussiness();
TagName = mybs.getSeeTag("参数", tag, id);
