泛型数据访问的方法（二）
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace DataAccess
{
[Serializable]
[AttributeUsage(AttributeTargets.Enum, Inherited = false)]
public class DataBaseStoreAttribute : Attribute
{
public string TableOrViewName
{
get;
set;
}
}
public struct FiledSetting
{
public string ColName;
public bool IsPrimaryKey;
public string EnumFiledName;
}
[Serializable]
[AttributeUsage(AttributeTargets.Field, Inherited = false )]
public class DataBaseFiledAttribute : Attribute
{
/// <summary>
/// 数据库中的表或试图的列名,列名中不得含有空格 ,请务必使用 [A-Z]{1,}[a-z0-9] 的格式
/// </summary>
public string ColumnName
{
get;
set;
}
/// <summary>
/// 是否是主键：必须指定
/// </summary>
public bool IsPrimaryKey
{
get;
set;
}
/// <summary>
/// DB数据类型,暂时不用
/// </summary>
public SqlDbType DatabaseType
{
get;
set;
}
/// <summary>
/// 长度,数值类型时此值无用，仅当DB数据类型为 [n]varchar,char等可变长度的数据类型有效
/// 暂时不用
/// </summary>
public int MaxLength
{
get;
set;
}
/// <summary>
/// 精度:目前不用
/// </summary>
public int Precision
{
get;
set;
}
/// <summary>
/// 小数位数:目前不用
/// </summary>
public int Scale
{
get;
set;
}
/// <summary>
/// 默认值:目前不用
/// </summary>
public object DefaultValue
{
get;
set;
}
/// <summary>
/// 当验证不通过时，是否使用默认值
/// 暂时不用
/// </summary>
public bool UsedDefaultValueWhenValidateError
{
get;
set;
}
/// <summary>
/// 是否允许为空，暂时不用
/// </summary>
public bool AllowNull
{
get;
set;
}
/// <summary>
/// 赋值时的验证接口,暂时不用
/// </summary>
public ValidateCallback ValidateProvider
{
get;
set;
}
}
public interface IExtend<TSource, TTarget>
{
TTarget ConvertToTarget(TSource src);
}
/// <summary>
/// 基本数据类型的扩展
/// </summary>
public static class BaseClassExtendMethod
{
// This is the extension method.
// The first parameter takes the "this" modifier
// and specifies the type for which the method is defined.
public static TTarget[] ToArray<TSource, TTarget>(this TSource[] source, IExtend<TSource, TTarget> convertTool)
{
if (source.Length == 0)
return null;
TTarget[] res = new TTarget[source.Length];
for (int i = 0; i < source.Length; i++)
{
res[i] = convertTool.ConvertToTarget(source[i]);
}
return res;
}
public static string ToFormat(this string txt, string format, params Object[] args)
{
return string.Format(System.Globalization.CultureInfo.InvariantCulture, format, args);
}
public static string ToFormat(this string txt, string format, Object arg0)
{
return string.Format(System.Globalization.CultureInfo.InvariantCulture, format, new object[] { arg0 });
}
public static string ToFormat(this string txt, string format, Object arg0, Object arg1)
{
return string.Format(System.Globalization.CultureInfo.InvariantCulture, format, new object[] { arg0, arg1 });
}
public static string ToFormat(this string txt, string format, Object arg0, Object arg1, Object arg2)
{
return string.Format(System.Globalization.CultureInfo.InvariantCulture, format, new object[] { arg0, arg1, arg2 });
}
//static public implicit operator bool(int iValue)
//{
// if(iValue >0)
// return true;
// return false;
//}
//static public explicit operator int(bool bValue)
//{
// if (bValue == true)
// return 1;
// return 0;
//}
}
}
--------------都是辅助方法和接口
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace DataAccess
{
public interface IExtend<TSource, TTarget>
{
TTarget ConvertToTarget(TSource src);
}
/// <summary>
/// 基本数据类型的扩展
/// </summary>
public static class BaseClassExtendMethod
{
// This is the extension method.
// The first parameter takes the "this" modifier
// and specifies the type for which the method is defined.
public static TTarget[] ToArray<TSource, TTarget>(this TSource[] source, IExtend<TSource, TTarget> convertTool)
{
if (source.Length == 0)
return null;
TTarget[] res = new TTarget[source.Length];
for (int i = 0; i < source.Length; i++)
{
res[i] = convertTool.ConvertToTarget(source[i]);
}
return res;
}
public static string ToFormat(this string txt, string format, params Object[] args)
{
return string.Format(System.Globalization.CultureInfo.InvariantCulture, format, args);
}
public static string ToFormat(this string txt, string format, Object arg0)
{
return string.Format(System.Globalization.CultureInfo.InvariantCulture, format, new object[] { arg0 });
}
public static string ToFormat(this string txt, string format, Object arg0, Object arg1)
{
return string.Format(System.Globalization.CultureInfo.InvariantCulture, format, new object[] { arg0, arg1 });
}
public static string ToFormat(this string txt, string format, Object arg0, Object arg1, Object arg2)
{
return string.Format(System.Globalization.CultureInfo.InvariantCulture, format, new object[] { arg0, arg1, arg2 });
}
//static public implicit operator bool(int iValue)
//{
// if(iValue >0)
// return true;
// return false;
//}
//static public explicit operator int(bool bValue)
//{
// if (bValue == true)
// return 1;
// return 0;
//}
}
}
-----
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace DataAccess.Core
{
/// <summary>
/// 数据连接组件
/// </summary>
public interface IConn
{
/// <summary>
/// 连接字符串
/// </summary>
string ConnectionString
{
get;
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common ;
using System.Data;
namespace DataAccess.Core
{
public delegate void RecordFormatCallback(IDataRecord record);
public delegate void RecordFormatCallback<T>(CoreDataRecord<T> record) where T:struct,IComparable;
}
-----------------------------例子:仅用于测试---------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using DataAccess;
using System.Reflection;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
using DataAccess.Core;
namespace DataAccess.DataFacotry
{
[DataBaseStore(TableOrViewName = "DtTeacher")]
public enum TeacherEnum : int
{
[DataBaseFiled(IsPrimaryKey = true)]
TechId,
TechUserName,
RealName,
IsPublish,
Introduc,
SeoKey,
SeoTitle,
SeoDesc,
CreateTime,
/// <summary>
/// 添加老师的用户
/// </summary>
Creator
}
public class TeacherEntity
{
public int TechId { get; set; }
public string TechUserName { get; set; }
public string RealName { get; set; }
public bool IsPublish { get; set; }
public string Introduc { get; set; }
public string SeoKey { get; set; }
public string SeoTitle { get; set; }
public string SeoDesc { get; set; }
public DateTime CreateTime { get; set; }
/// <summary>
/// 添加老师的用户
/// </summary>
public string Creator { get; set; }
}
public class TeacherReader : CoreBase<TeacherEnum>
{
public TeacherReader()
: base()
{
}
/// <summary>
/// 0:此用户的老师已经存在；-1:用户名为空；-2：实名为空,-3:返回值为NULL(此时是无法确认是否添加成功）
/// </summary>
/// <param name="tch"></param>
/// <returns></returns>
public int CreateTeacher(TeacherEntity tch)
{
if (tch != null)
throw new System.ArgumentNullException();
if (string.IsNullOrEmpty(tch.TechUserName))
return -1;
if (string.IsNullOrEmpty(tch.RealName))
return -2;
int idx = 0;
using (SqlConnection cnn = new SqlConnection(this.ConnectionString))
using (SqlCommand cmd = new SqlCommand("teacherCreate", cnn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@TeachUserName", tch.TechUserName);
cmd.Parameters.AddWithValue("@RealName", tch.RealName);
cmd.Parameters.AddWithValue("@IsPublish", tch.IsPublish);
cmd.Parameters.AddWithValue("@Introduc", tch.Introduc);
cmd.Parameters.AddWithValue("@SeoKey", tch.SeoKey);
cmd.Parameters.AddWithValue("@SeoTitle", tch.SeoTitle);
cmd.Parameters.AddWithValue("@SeoDesc", tch.SeoDesc);
cmd.Parameters.AddWithValue("@Creator", tch.Creator);
cnn.Open();
object jx = cmd.ExecuteScalar();
cnn.Close();
if (jx != null)
{
idx = (int)jx;
}
else
{
idx = -3;
}
}
return idx;
}
/// <summary>
/// 0:此用户的老师已经存在；-1:用户名为空；-2：实名为空,-3:返回值为NULL(此时是无法确认是否添加成功）
/// -4:TechId属性小于等于0
/// </summary>
/// <param name="tch"></param>
/// <returns></returns>
public int ModifyTeacher(TeacherEntity tch)
{
if (tch != null)
throw new System.ArgumentNullException();
if (string.IsNullOrEmpty(tch.TechUserName))
return -1;
if (string.IsNullOrEmpty(tch.RealName))
return -2;
if (tch.TechId <= 0)
return -4;
int idx = 0;
using (SqlConnection cnn = new SqlConnection(this.ConnectionString))
using (SqlCommand cmd = new SqlCommand("teacherModify", cnn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@TechId", tch.TechId);
cmd.Parameters.AddWithValue("@TeachUserName", tch.TechUserName);
cmd.Parameters.AddWithValue("@RealName", tch.RealName);
cmd.Parameters.AddWithValue("@IsPublish", tch.IsPublish);
cmd.Parameters.AddWithValue("@Introduc", tch.Introduc);
cmd.Parameters.AddWithValue("@SeoKey", tch.SeoKey);
cmd.Parameters.AddWithValue("@SeoTitle", tch.SeoTitle);
cmd.Parameters.AddWithValue("@SeoDesc", tch.SeoDesc);
cmd.Parameters.AddWithValue("@Creator", tch.Creator);
cnn.Open();
object jx = cmd.ExecuteScalar();
cnn.Close();
if (jx != null)
{
idx = (int)jx;
}
else
{
idx = -3;
}
}
return idx;
}
/// <summary>
/// 删除指定的教师
/// </summary>
/// <param name="techIds"></param>
public void DeleteTeacher(int[] techIds)
{
if (techIds == null || techIds.Length == 0)
return;
using (SqlConnection cnn = new SqlConnection(this.ConnectionString))
using (SqlCommand cmd = new SqlCommand("teacherDelete", cnn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@TechId", 0);
cnn.Open();
foreach (int idx in techIds)
{
if (idx <= 0)
continue;
cmd.Parameters[0].Value = idx;
cmd.ExecuteNonQuery();
}
cnn.Close();
}
}
}
}
-----------------------------------CORE》CS
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.Specialized;
using System.Text.RegularExpressions;
using DataAccess;
using System.Reflection;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
namespace DataAccess.Core
{
public class CoreDataRecord<T> where T:struct ,IComparable
{
IDataRecord mRecorder = null;
public IDataRecord Record
{
get { return mRecorder; }
}
public CoreDataRecord(IDataRecord dr)
{
if (dr == null)
throw new System.ArgumentNullException();
mRecorder = dr;
}
public object this[T columnField]
{
get
{
string na = columnField.ToString();
return mRecorder[na];
}
}
}
/// <summary>
/// 内部使用，不对外公开的操作符定义
/// </summary>
public enum AndOr : int
{
None = 0,
And = 1,
Or = 2
}
/// <summary>
/// 列过滤表达式基类
/// </summary>
/// <typeparam name="T"></typeparam>
public sealed class ColumnBase<T> where T : struct, IComparable
{
#region 变量
private string sqlExpress;
private AndOr expReleation;
private ColumnBase<T> LeftNode;
private ColumnBase<T> RightNode;
private Dictionary<string, object> pArgs;
private CoreBase<T> ptable;
#endregion
public static int currentIdx = 0;
internal ColumnBase(CoreBase<T> tx)
{
ptable = tx;
expReleation = AndOr.None;
LeftNode = null;
RightNode = null;
pArgs = null;
}
public string FilterString
{
get
{
string txt = "";
switch (this.expReleation)
{
case AndOr.Or:
txt = "(" + LeftNode.FilterString + " OR " + this.RightNode.FilterString + ")";
break;
case AndOr.And:
txt = "(" + LeftNode.FilterString + " AND " + this.RightNode.FilterString + ")";
break;
default:
txt = sqlExpress;
break;
}
return txt;
}
}
public SqlParameter[] Parameters
{
get
{
List<SqlParameter> ps = new List<SqlParameter>();
switch (this.expReleation)
{
case AndOr.Or:
case AndOr.And:
ps.AddRange(LeftNode.Parameters);
ps.AddRange(RightNode.Parameters);
break;
default:
if (this.pArgs != null)
{
Dictionary<string, object>.Enumerator ie = pArgs.GetEnumerator();
while (ie.MoveNext())
{
SqlParameter p = new SqlParameter(ie.Current.Key, ie.Current.Value);
ps.Add(p);
}
}
break;
}
return ps.ToArray();
}
}
#region 常用操作
/// <summary>
/// 等于
/// </summary>
/// <typeparam name="V"></typeparam>
/// <param name="t1"></param>
/// <param name="v"></param>
public ColumnBase<T> Equal<V>(T t1, V v)
{
ColumnBase<T> cb = new ColumnBase<T>(this.ptable);
string pn = string.Format("@{0}{1}", ptable.ColName(t1),currentIdx ++);
cb.sqlExpress = string.Format(" {0}.{1}={2}", ptable.TableName, ptable.ColName(t1), pn);
cb.pArgs = new Dictionary<string, object>();
cb.expReleation = AndOr.None;
cb.pArgs.Add(pn, v);
return cb;
}
/// <summary>
/// 不等于
/// </summary>
/// <typeparam name="V"></typeparam>
/// <param name="t1"></param>
/// <param name="v1"></param>
public ColumnBase<T> UnEqual<V>(T t1, V v1)
{
ColumnBase<T> cb = new ColumnBase<T>(this.ptable);
string pn = string.Format("@{0}{1}", ptable.ColName(t1), currentIdx++);
cb.sqlExpress = string.Format(" {0}.{1}<>{2}", ptable.TableName,ptable.ColName(t1), pn);
cb.pArgs = new Dictionary<string, object>();
cb.pArgs.Add(pn, v1);
return cb;
}
/// <summary>
/// 大于等于
/// </summary>
/// <typeparam name="V"></typeparam>
/// <param name="t1"></param>
/// <param name="v1"></param>
public ColumnBase<T> Biger<V>(T t1, V v1)
{
ColumnBase<T> cb = new ColumnBase<T>(this.ptable);
string pn = string.Format("@{0}{1}", ptable.ColName(t1),currentIdx ++);
cb.sqlExpress = string.Format(" {0}.{1}>{2}", ptable.TableName, ptable.ColName(t1), pn);
cb.pArgs = new Dictionary<string, object>();
cb.pArgs.Add(pn, v1);
return cb;
}
/// <summary>
/// 大于等于
/// </summary>
/// <typeparam name="V"></typeparam>
/// <param name="t1"></param>
/// <param name="v1"></param>
public ColumnBase<T> BigerThen<V>(T t1, V v1)
{
ColumnBase<T> cb = new ColumnBase<T>(this.ptable);
string pn = string.Format("@{0}{1}", ptable.ColName(t1),currentIdx ++);
cb.sqlExpress = string.Format(" {0}.{1}>={2}", ptable.TableName, ptable.ColName(t1), pn);
cb.pArgs = new Dictionary<string, object>();
cb.pArgs.Add(pn, v1);
return cb;
}
/// <summary>
/// 小于
/// </summary>
/// <typeparam name="V"></typeparam>
/// <param name="t1"></param>
/// <param name="v1"></param>
public ColumnBase<T> Smaller<V>(T t1, V v1)
{
ColumnBase<T> cb = new ColumnBase<T>(this.ptable);
string pn = string.Format("@{0}{1}", ptable.ColName(t1),currentIdx ++);
cb.sqlExpress = string.Format(" {0}.{1}<{2}", ptable.TableName, ptable.ColName(t1), pn);
cb.pArgs = new Dictionary<string, object>();
cb.pArgs.Add(pn, v1);
return cb;
}
/// <summary>
/// 小于等于
/// </summary>
/// <typeparam name="V"></typeparam>
/// <param name="t1"></param>
/// <param name="v1"></param>
public ColumnBase<T> SmallerThen<V>(T t1, V v1)
{
ColumnBase<T> cb = new ColumnBase<T>(this.ptable);
string pn = string.Format("@{0}{1}", ptable.ColName(t1),currentIdx ++);
cb.sqlExpress = string.Format(" {0}.{1}<={2}", ptable.TableName, ptable.ColName(t1), pn);
cb.pArgs = new Dictionary<string, object>();
cb.pArgs.Add(pn, v1);
return cb;
}
/// <summary>
/// In
/// </summary>
/// <typeparam name="V"></typeparam>
/// <param name="t1"></param>
/// <param name="v1"></param>
public ColumnBase<T> In<V>(T t1, V[] v1)
{
ColumnBase<T> cb = new ColumnBase<T>(this.ptable);
cb.pArgs = new Dictionary<string, object>();
int len = v1.Length;
string[] res = new string[len];
string cn = ptable.ColName(t1);
for (int i = 0; i < len; i++)
{
res[i] = string.Format("@{0}{1}{2}", cn, i + 1,currentIdx ++);
cb.pArgs.Add(res[i], v1[i]);
}
cb.sqlExpress = string.Format("{0} IN ({1})", cn, string.Join(",", res));
return cb;
}
/// <summary>
/// Not In
/// </summary>
/// <typeparam name="V"></typeparam>
/// <param name="t1"></param>
/// <param name="v1"></param>
public ColumnBase<T> NotIn<V>(T t1, V[] v1)
{
ColumnBase<T> cb = new ColumnBase<T>(this.ptable);
cb.pArgs = new Dictionary<string, object>();
int len = v1.Length;
string[] res = new string[len];
string cn = ptable.ColName(t1);
for (int i = 0; i < len; i++)
{
res[i] = string.Format("@{0}{1}{2}", cn, i + 1,currentIdx ++);
cb.pArgs.Add(res[i], v1[i]);
}
cb.sqlExpress = string.Format("{0} NOT IN ({1})", cn, string.Join(",", res));
return cb;
}
#endregion
#region 重载操作符
public static ColumnBase<T> operator &(ColumnBase<T> left, ColumnBase<T> right)
{
if (left == null && right == null)
{
return null;
}
else if (left != null && right != null)
{
if (string.IsNullOrEmpty(left.sqlExpress) && string.IsNullOrEmpty(right.sqlExpress))
{
return null;
}
else if ((string.IsNullOrEmpty(left.sqlExpress) == false) && (string.IsNullOrEmpty(right.sqlExpress) == false))
{
ColumnBase<T> t = new ColumnBase<T>(left.ptable);
t.sqlExpress = string.Format("( {0} AND {1} )", left.sqlExpress, right.sqlExpress);
t.LeftNode = left;
t.RightNode = right;
t.expReleation = AndOr.And;
return t;
}
else if (string.IsNullOrEmpty(right.sqlExpress))
{
return left;
}
else
return right;
}
else if (left == null)
return right;
else
return left;
}
public static ColumnBase<T> operator |(ColumnBase<T> left, ColumnBase<T> right)
{
if (left == null && right == null)
{
return null;
}
else if (left != null && right != null)
{
if (string.IsNullOrEmpty(left.sqlExpress) && string.IsNullOrEmpty(right.sqlExpress))
{
return null;
}
else if ((string.IsNullOrEmpty(left.sqlExpress) == false) && (string.IsNullOrEmpty(right.sqlExpress) == false))
{
ColumnBase<T> t = new ColumnBase<T>(left.ptable);
t.sqlExpress = string.Format("( {0} OR {1} )", left.sqlExpress, right.sqlExpress);
t.LeftNode = left;
t.RightNode = right;
t.expReleation = AndOr.Or;
return t;
}
else if (string.IsNullOrEmpty(right.sqlExpress))
{
return left;
}
else
return right;
}
else if (left == null)
return right;
else
return left;
}
#endregion
}
/// <summary>
/// 列排序的基类
/// </summary>
/// <typeparam name="T"></typeparam>
public sealed class ColumnOrder<T> where T : struct, IComparable
{
private CoreBase<T> ptable;
private string expOrd = "";
private ColumnOrder<T> pre;
internal ColumnOrder(CoreBase<T> tx)
{
ptable = tx;
pre = null;
}
public ColumnOrder<T> Asc(T t)
{
string cnName = ptable.ColName(t);
ColumnOrder<T> c = new ColumnOrder<T>(this.ptable);
c.pre = this;
c.expOrd = cnName + " ASC ";
return c;
}
public ColumnOrder<T> Desc(T t)
{
string cnName = ptable.ColName(t);
ColumnOrder<T> c = new ColumnOrder<T>(this.ptable);
c.pre = this;
c.expOrd = cnName + " DESC ";
return c;
}
/// <summary>
/// 注意可能返回空字符串
/// </summary>
public string OrderString
{
get
{
if (this.expOrd == "")
return string.Empty;
List<string> x = new List<string>();
x.Insert(0, this.expOrd);
while (pre != null && pre.expOrd !="" )
{
x.Insert(0, this.pre.expOrd);
pre = this.pre.pre;
}
return string.Join(",", x.ToArray());
}
}
}
/// <summary>
/// 选择列的基类
/// </summary>
/// <typeparam name="T"></typeparam>
public sealed class ColumnSelect<T> where T : struct, IComparable
{
private CoreBase<T> ptable;
private string expOrd = "";
internal ColumnSelect(CoreBase<T> tx)
{
ptable = tx;
}
public ColumnSelect<T> And(T t)
{
string cnName = ptable.ColName(t);
string asName = ptable.AliasName(t);
ColumnSelect<T> c = new ColumnSelect<T>(this.ptable);
c.expOrd = expOrd + (string.IsNullOrEmpty(expOrd) ? (ptable.TableName + "." + cnName + " as " + asName) : ("," + ptable.TableName + "." +cnName + " as " + asName));
return c;
}
public string SelectCoumns
{
get
{
return this.expOrd;
}
}
}
/// <summary>
/// 列表达式定义
/// </summary>
/// <typeparam name="T"></typeparam>
public abstract class CoreBase<T> : IDisposable, IConn where T : struct, IComparable
{
protected void Init()
{
string txtMsg = "";
#region 检查基本属性的设置
bool isEnum = typeof(T).IsEnum;
if (typeof(T).IsEnum == false)
throw new System.TypeLoadException(txtMsg.ToFormat("{0} 不是枚举类型", typeof(T)));
//是否应用的FlagsArttibutes属性
bool tabAttr = false;
object[] x = typeof(T).GetCustomAttributes(false);
#endregion
#region 验证表属性设置
DataBaseStoreAttribute dst = null;
foreach (object o in x)
{
if (o.GetType() == typeof(DataBaseStoreAttribute))
{
dst = (DataBaseStoreAttribute)o;
tabAttr = true;
break;
}
}
if (!tabAttr)
throw new System.TypeLoadException(txtMsg.ToFormat("{0}没有指定DataBaseStoreAttribute", typeof(T)));
if (string.IsNullOrEmpty(dst.TableOrViewName))
throw new System.TypeLoadException(txtMsg.ToFormat("{0}没有指定表或视图的名称", typeof(T)));
if (Regex.IsMatch(dst.TableOrViewName, @"^[A-Z]{1,100}[a-z0-9_]{0,100}$", RegexOptions.IgnoreCase) == false)
{
throw new System.TypeLoadException(txtMsg.ToFormat("表名:{0} 不符合 [A-Z]{1,100}[a-z0-9_]{0,100} 的命名规范", dst.TableOrViewName));
}
TableName = dst.TableOrViewName;
#endregion
#region 字段基类型
TypeCode tcode = Type.GetTypeCode(typeof(T));
if (tcode != TypeCode.Int32)
{
throw new System.TypeLoadException(txtMsg.ToFormat(" {0} 的类型定义出错 应该为：public enum {0} ：int ", typeof(T)));
}
#endregion
#region 字段的属性设置是否超出范围
FieldInfo[] cc = typeof(T).GetFields();
if (cc == null || cc.Length <= 1)
throw new System.TypeLoadException(txtMsg.ToFormat("{0} 的类型定义出错,没有指定可用字段", typeof(T)));//
//检查取值范围
List<int> tmpLst = new List<int>();
foreach (var ci in cc)
{
if (ci.IsSpecialName)
continue;
string name = ci.Name;
int pv = (int)ci.GetValue(default(T));
if (pv == 0)
{
continue;
}
if (tmpLst.Contains(pv))
{
throw new System.TypeLoadException(txtMsg.ToFormat("{0} 出现重复值 {1}={2} ", typeof(T), ci.Name, pv));
}
tmpLst.Add(pv);
}
#endregion
#region 主键是否存在
List<T> tmp = new List<T>();
foreach (var ci in cc)
{
if (ci.IsSpecialName)
continue;
DataBaseFiledAttribute[] cx = (DataBaseFiledAttribute[])ci.GetCustomAttributes(typeof(DataBaseFiledAttribute), false);
if (cx == null || cx.Length != 1)
{
continue;
// throw new System.TypeLoadException(txtMsg.ToFormat("{0}每一个字段只能应用一个DataBaseFiledAttribute属性",ci.Name));
}
DataBaseFiledAttribute d = cx[0];
if (!d.IsPrimaryKey)
continue;
int cv = (int)((object)ci.GetValue(default(T)));
if (!string.IsNullOrEmpty(d.ColumnName))
{
if (!Regex.IsMatch(d.ColumnName, @"^[A-Z]{1,100}[a-z0-9_]{0,100}$", RegexOptions.IgnoreCase))
{
throw new System.TypeLoadException(txtMsg.ToFormat("{0}.{1}的属性 DataBaseFiled.ColumnName的取值格式不正确,要么不设置，要么设为[A-Z]{1,}[a-z0-9_]{0,244} 的格式", typeof(T), ci.Name));
}
}
T xf = (T)ci.GetValue(default(T));
tmp.Add(xf);
}
if (tmp.Count == 0)
{
throw new System.TypeLoadException(txtMsg.ToFormat("{0} 没有指定主键", typeof(T)));
}
PrimaryKey = tmp.ToArray();
#endregion
#region 获取所有列名
Columns = new Dictionary<T, FiledSetting>();
foreach (var fi in cc)
{
if (fi.IsSpecialName)
continue;
object tmpV = fi.GetValue(default(T));
int k = (int)tmpV;
if (k == 0)
continue;
DataBaseFiledAttribute[] cx = (DataBaseFiledAttribute[])fi.GetCustomAttributes(typeof(DataBaseFiledAttribute), false);
FiledSetting fs = new FiledSetting();
if (cx != null && cx.Length == 1)
{
DataBaseFiledAttribute d = cx[0];
fs.ColName = string.IsNullOrEmpty(d.ColumnName) ? fi.Name : d.ColumnName;
fs.IsPrimaryKey = d.IsPrimaryKey;
fs.EnumFiledName = fi.Name;
}
else
{
fs.ColName = fs.EnumFiledName = fi.Name;
fs.IsPrimaryKey = false;
}
T tc = (T)(tmpV);
Columns.Add(tc, fs);
}
#endregion
}
/// <summary>
/// 主关键字列
/// </summary>
protected T[] PrimaryKey;
/// <summary>
/// 所有列列表
/// </summary>
protected Dictionary<T, FiledSetting> Columns;
protected CoreBase()
{
Init();
}
#region 查询列表接口
/// <summary>
/// 获取指定的班级信息
/// </summary>
/// <param name="recordCallback"></param>
/// <param name="selected"></param>
/// <param name="filter"></param>
/// <param name="orders"></param>
public void Enum(RecordFormatCallback recordCallback, ColumnSelect<T> selected,
ColumnBase<T> filter, ColumnOrder<T> orders)
{
if (recordCallback == null)
throw new ArgumentNullException("recordCallback没有指定，无任何意义的操作");
if (selected == null || selected.SelectCoumns =="" )
{
selected = this.AllColumns;
}
string selectString = selected.SelectCoumns;
string filterString = "";
if (filter != null && filter.FilterString != "")
{
filterString = filter.FilterString;
}
string orderString = "";
if ((orders == null) || (string.IsNullOrEmpty( orders.OrderString)))
{
int len = PrimaryKey.Length;
string[] cteTabCol = new string[len];
for (int i = 0; i < len; i++)
{
cteTabCol[i] = ColName(PrimaryKey[i]);
}
orderString = string.Join(",", cteTabCol);
}
else
{
orderString = orders.OrderString;
}
string cmdText="";
cmdText = cmdText.ToFormat("Select {0} From {1} {2} Order By {3}",
selectString,
this.TableName,
string.IsNullOrEmpty(filterString) ? "" : " Where " + filterString,
orderString
);
using(SqlConnection cnn=new SqlConnection(this.ConnectionString ))
using (SqlCommand cmd = new SqlCommand(cmdText, cnn))
{
if (filter !=null && filter.Parameters != null && filter.Parameters.Length > 0)
cmd.Parameters.AddRange(filter.Parameters);
cnn.Open();
using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
recordCallback(dr);
}
dr.Close();
}
cnn.Close();
}
}
/// <summary>
/// 获取指定行号与记录数的列表
/// </summary>
/// <param name="recordCallback"></param>
/// <param name="startRecord"></param>
/// <param name="recordCount"></param>
/// <param name="selected"></param>
/// <param name="filter"></param>
/// <param name="orders"></param>
public void Enum(RecordFormatCallback recordCallback, int startRecord, int recordCount, ColumnSelect<T> selected,
ColumnBase<T> filter, ColumnOrder<T> orders)
{
if (startRecord < 0 )
throw new System.ArgumentOutOfRangeException("startRecord必须不能小于0");
if( recordCount <=0)
throw new System.ArgumentOutOfRangeException("recordCount必须大于0");
if (recordCallback == null)
throw new ArgumentNullException("recordCallback没有指定，无任何意义的操作");
if (selected == null || selected.SelectCoumns == "")
{
selected = this.AllColumns;
}
string selectString = selected.SelectCoumns;
int len=PrimaryKey.Length ;
string ordString = "";
if ( (orders == null) || string.IsNullOrEmpty(orders.OrderString ))
{
string[] ort = new string[len];
for (int i = 0; i < len; i++)
{
ort[i] = ColName(PrimaryKey[i]);
}
ordString = string.Join(",", ort);
}
else
{
ordString = orders.OrderString;
}
string filterString = "";
if (filter!= null && filter.FilterString != "")
{
filterString = filter.FilterString;
}
string[] cteTabCol = new string[len ];
string[] cteRL = new string[len];
string tmp="";
for (int i = 0; i < len; i++)
{
cteTabCol[i] = ColName(PrimaryKey[i]);
cteRL[i] = tmp.ToFormat(" __Cte.{0}={1}.{0} ", ColName(PrimaryKey[i]), TableName);
}
string cmdText = ";With __Cte(cteRN,{0}) as( "
+ " Select Top (@CN) RN=ROW_NUMBER() OVER ( ORDER BY {1} ),{0} "
+ " From {2} {3} "
+ ") "
+ " Select {4} From {2},__Cte Where cteRN>(@ST) and ({5}) Order by cteRN asc ";
cmdText = cmdText.ToFormat(cmdText,
string.Join(",", cteTabCol),
ordString ,
TableName,
string.IsNullOrEmpty(filterString) ? "" : " Where " + filterString,
selected.SelectCoumns ,
string.Join(" and ", cteRL)
);
using(SqlConnection cnn=new SqlConnection(this.ConnectionString ))
using (SqlCommand cmd = new SqlCommand(cmdText, cnn))
{
if (filter !=null && filter.Parameters != null && filter.Parameters.Length > 0)
{
cmd.Parameters.AddRange(filter.Parameters);
}
cmd.Parameters.AddWithValue("@CN", startRecord + recordCount);
cmd.Parameters.AddWithValue("@ST", startRecord);
cnn.Open();
using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
recordCallback(dr);
}
dr.Close();
}
cnn.Close();
}
}
/// <summary>
/// 获取指定的班级信息
/// </summary>
/// <param name="recordCallback"></param>
/// <param name="selected"></param>
/// <param name="filter"></param>
/// <param name="orders"></param>
public void Enum(RecordFormatCallback<T> recordCallback, ColumnSelect<T> selected,
ColumnBase<T> filter, ColumnOrder<T> orders)
{
if (recordCallback == null)
throw new System.ArgumentNullException();
RecordFormatCallback rcb = (IDataRecord dr) =>
{
recordCallback(new CoreDataRecord<T>(dr));
};
this.Enum(rcb, selected, filter, orders);
}
/// <summary>
/// 获取指定行号与记录数的列表
/// </summary>
/// <param name="recordCallback"></param>
/// <param name="startRecord"></param>
/// <param name="recordCount"></param>
/// <param name="selected"></param>
/// <param name="filter"></param>
/// <param name="orders"></param>
public void Enum(RecordFormatCallback<T> recordCallback, int startRecord, int recordCount, ColumnSelect<T> selected,
ColumnBase<T> filter, ColumnOrder<T> orders)
{
if (recordCallback == null)
throw new System.ArgumentNullException();
RecordFormatCallback rcb=(IDataRecord dr)=>{
recordCallback(new CoreDataRecord<T>(dr));
};
this.Enum(rcb, startRecord, recordCount, selected, filter, orders);
}
public int Count(ColumnBase<T> filter)
{
string cmdText = "";
if (filter == null || filter.FilterString == "")
{
cmdText = " Select Count(*) From " + TableName;
}
else
{
cmdText = cmdText.ToFormat("Select Count(*) From {0} Where {1}", TableName, filter.FilterString);
}
int res = 0;
using (SqlConnection cnn = new SqlConnection(this.ConnectionString))
using (SqlCommand cmd = new SqlCommand(cmdText, cnn))
{
if (filter !=null && filter.Parameters != null && filter.Parameters.Length > 0)
{
cmd.Parameters.AddRange(filter.Parameters);
}
cnn.Open();
res = (int)cmd.ExecuteScalar();
cnn.Close();
}
return res;
}
#endregion
#region 删除
/// <summary>
/// 删除指定的记录，并输出删除的主键列表
/// </summary>
/// <param name="recordCallback"></param>
/// <param name="filters"></param>
public virtual void Delete(RecordFormatCallback recordCallback, ColumnBase<T> filters)
{
if((filters ==null) || string.IsNullOrEmpty(filters.FilterString ))
{
throw new System.NotSupportedException("不允许的操作:没有指定删除条件的删除动作,若要清表，请联系DBA");
}
string cmdText = "";
if (recordCallback == null)
{
cmdText = cmdText.ToFormat("Delete {0} Where {1} ", TableName, filters.FilterString);
}
else
{
int len = PrimaryKey.Length;
string[] cc = new string[len];
for (int i = 0; i < len; i++)
{
cc[i] = string.Format("deleted.{0} as {1} " , ColName(PrimaryKey[i]), AliasName(PrimaryKey[i]));
}
cmdText = cmdText.ToFormat("Delete {0} Output {1} Where {2} ",
TableName, string.Join(",", cc), filters.FilterString);
}
using(SqlConnection cn=new SqlConnection(this.ConnectionString ))
using (SqlCommand cmd = new SqlCommand(cmdText, cn))
{
cmd.Parameters.AddRange(filters.Parameters);
cn.Open();
if (recordCallback != null)
{
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
recordCallback(dr);
}
dr.Close();
}
}
else
{
cmd.ExecuteNonQuery();
}
cn.Close();
}
}
#endregion
#region 属性
/// <summary>
/// 原始数据列名
/// </summary>
/// <param name="field"></param>
/// <returns></returns>
public string ColName(T field)
{
return Columns[field].ColName;
}
/// <summary>
/// 希望输出时的列名或称为别名
/// </summary>
public string AliasName(T field)
{
return Columns[field].EnumFiledName;
}
/// <summary>
/// 表名
/// </summary>
public string TableName
{
get;
protected set;
}
/// <summary>
/// 创建一个过滤节点
/// </summary>
public ColumnBase<T> FilterNode
{
get
{
return new ColumnBase<T>(this);
}
}
/// <summary>
/// 创建一个排序节点
/// </summary>
public ColumnOrder<T> OrderNode
{
get
{
return new ColumnOrder<T>(this);
}
}
/// <summary>
/// 创建一个选择节点
/// </summary>
public ColumnSelect<T> SelectNode
{
get
{
return new ColumnSelect<T>(this);
}
}
#endregion
/// <summary>
/// 资源清理
/// </summary>
protected virtual void Collect()
{
}
#region 更新某些列
/// <summary>
/// 更新符合条件的某一特定的列为指定的值(请更新原子列及不和其它表有关联的列）
/// 此操作不会更新关联表，请慎重使用
/// </summary>
/// <typeparam name="V"></typeparam>
/// <param name="specialField"></param>
/// <param name="value"></param>
/// <param name="filters"></param>
public virtual void UploadSpecailCol<V>(T specialField, V value, ColumnBase<T> filters)
{
int kc = PrimaryKey.Length;
for (int i = 0; i < kc; i++)
{
if (specialField.CompareTo(PrimaryKey[i] )==0)
{
throw new System.Exception("试图更新关键字列");
}
}
string cmdText="";
if (filters == null || filters.FilterString == "")
{
cmdText = cmdText.ToFormat(" Update {0} set {1}=@A ", TableName, ColName(specialField));
}
else
{
cmdText = cmdText.ToFormat(" Update {0} set {1}=@A Where {2} ", TableName, ColName(specialField), filters.FilterString);
}
using(SqlConnection cnn=new SqlConnection(this.ConnectionString))
using(SqlCommand cmd=new SqlCommand (cmdText ,cnn))
{
cmd.CommandType =CommandType.Text ;
cmd.Parameters.AddWithValue("@A",value );
if(filters !=null && filters.Parameters !=null && filters.Parameters .Length >0)
cmd.Parameters.AddRange(filters.Parameters );
cnn.Open ();
cmd.ExecuteNonQuery ();
cnn.Close ();
}
}
/// <summary>
/// 更新指定的两列(请更新原子列及不和其它表有关联的列）
/// 此操作不会更新关联表，请慎重使用
/// </summary>
/// <typeparam name="V1"></typeparam>
/// <typeparam name="V2"></typeparam>
/// <param name="specialField"></param>
/// <param name="value"></param>
/// <param name="col2"></param>
/// <param name="value2"></param>
/// <param name="filters"></param>
public virtual void UploadSpecailCol<V1, V2>(T specialField, V1 value, T col2, V2 value2, ColumnBase<T> filters)
{
int kc = PrimaryKey.Length;
for (int i = 0; i < kc; i++)
{
if (specialField.CompareTo(PrimaryKey[i]) == 0)
throw new System.Exception("试图更新关键字列");
if(col2.CompareTo(PrimaryKey[i])==0)
throw new System.Exception("试图更新关键字列");
}
string cmdText="";
if (filters == null || filters.FilterString == "")
{
cmdText = cmdText.ToFormat(" Update {0} set {1}=@A ,{2}=@B ", TableName, ColName(specialField),ColName(col2) );
}
else
{
cmdText = cmdText.ToFormat(" Update {0} set {1}=@A ,{2}=@B Where {3} ",
TableName,
ColName(specialField),
ColName(col2 ),
filters.FilterString);
}
using(SqlConnection cnn=new SqlConnection(this.ConnectionString))
using(SqlCommand cmd=new SqlCommand(cmdText ,cnn))
{
cmd.CommandType =CommandType.Text ;
cmd.Parameters.AddWithValue("@A",value );
cmd.Parameters.AddWithValue("@B", value2 );
if(filters !=null && filters.Parameters !=null && filters.Parameters .Length >0)
cmd.Parameters.AddRange(filters.Parameters );
cnn.Open ();
cmd.ExecuteNonQuery ();
cnn.Close ();
}
}
/// <summary>
/// 更新指定的三列(请更新原子列及不和其它表有关联的列）
/// 此操作不会更新关联表，请慎重使用
/// </summary>
/// <typeparam name="V1"></typeparam>
/// <typeparam name="V2"></typeparam>
/// <typeparam name="V3"></typeparam>
/// <param name="t1"></param>
/// <param name="v1"></param>
/// <param name="t2"></param>
/// <param name="v2"></param>
/// <param name="t3"></param>
/// <param name="v3"></param>
/// <param name="filters"></param>
public virtual void UploadSpecailCol<V1, V2, V3>(T t1, V1 v1, T t2, V2 v2, T t3, V3 v3, ColumnBase<T> filters)
{
int kc = PrimaryKey.Length;
for (int i = 0; i < kc; i++)
{
if (t1.CompareTo(PrimaryKey[i]) == 0)
throw new System.Exception("试图更新关键字列");
if (t2.CompareTo(PrimaryKey[i]) == 0)
throw new System.Exception("试图更新关键字列");
if (t3.CompareTo(PrimaryKey[i]) == 0)
throw new System.Exception("试图更新关键字列");
}
string cmdText="";
if (filters == null || filters.FilterString == "")
{
cmdText = cmdText.ToFormat(" Update {0} set {1}=@A ,{2}=@B ,{3}=@C ", TableName, ColName(t1),ColName(t2),ColName(t3) );
}
else
{
cmdText = cmdText.ToFormat(" Update {0} set {1}=@A ,{2}=@B ,{3}=@C Where {4} ", TableName, ColName(t1),ColName(t2),ColName(t3) , filters.FilterString);
}
using(SqlConnection cnn=new SqlConnection(this.ConnectionString))
using(SqlCommand cmd=new SqlCommand (cmdText ,cnn))
{
cmd.CommandType =CommandType.Text ;
cmd.Parameters.AddWithValue("@A", v1);
cmd.Parameters.AddWithValue("@B", v2);
cmd.Parameters.AddWithValue("@C", v3);
if(filters !=null && filters.Parameters !=null && filters.Parameters .Length >0)
cmd.Parameters.AddRange(filters.Parameters );
cnn.Open ();
cmd.ExecuteNonQuery ();
cnn.Close ();
}
}
#endregion
protected ColumnSelect<T> pSelecteAll;
protected static object lockj = new object();
protected static object lockj2 = new object();
/// <summary>
/// 如果传入值为NULL或是数组长度为0时均会返回所有的列
/// </summary>
/// <param name="fields"></param>
/// <returns></returns>
public ColumnSelect<T> SelectedAllExcepted(T[] fields)
{
if (fields == null || fields .Length ==0 )
return AllColumns;
List<T> rest = new List<T>();
foreach (T t in fields)
{
if (Columns.ContainsKey(t) == false)
{
rest.Add(t);
}
}
ColumnSelect<T> res = new ColumnSelect<T>(this);
foreach (T t in rest)
{
res = res.And(t);
}
return res;
}
public ColumnSelect<T> AllColumns
{
get
{
lock ( lockj2)
{
if (pSelecteAll == null)
{
lock ( lockj)
{
pSelecteAll = new ColumnSelect<T>(this);
Dictionary<T, FiledSetting>.Enumerator ie = Columns.GetEnumerator();
while (ie.MoveNext())
{
pSelecteAll = pSelecteAll.And(ie.Current.Key);
}
}
}
}
return pSelecteAll;
}
}
#region IDisposable 成员
private bool _displosed;
private void Dispose(bool disposed)
{
if (_displosed == false && disposed == true)
{
Collect();
_displosed = true;
}
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
#endregion
#region IConn 成员
private string cnnString = "";
public string ConnectionString
{
get
{
if (cnnString == "")
{
cnnString = DBConfiguration.DTConfiguration.ConnectionDTdbString;
}
return cnnString;
}
}
#endregion
}
}
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace DataAccess
{
[Serializable]
[AttributeUsage(AttributeTargets.Enum, Inherited = false)]
public class DataBaseStoreAttribute : Attribute
{
public string TableOrViewName
{
get;
set;
}
}
public struct FiledSetting
{
public string ColName;
public bool IsPrimaryKey;
public string EnumFiledName;
}
[Serializable]
[AttributeUsage(AttributeTargets.Field, Inherited = false )]
public class DataBaseFiledAttribute : Attribute
{
/// <summary>
/// 数据库中的表或试图的列名,列名中不得含有空格 ,请务必使用 [A-Z]{1,}[a-z0-9] 的格式
/// </summary>
public string ColumnName
{
get;
set;
}
/// <summary>
/// 是否是主键：必须指定
/// </summary>
public bool IsPrimaryKey
{
get;
set;
}
/// <summary>
/// DB数据类型,暂时不用
/// </summary>
public SqlDbType DatabaseType
{
get;
set;
}
/// <summary>
/// 长度,数值类型时此值无用，仅当DB数据类型为 [n]varchar,char等可变长度的数据类型有效
/// 暂时不用
/// </summary>
public int MaxLength
{
get;
set;
}
/// <summary>
/// 精度:目前不用
/// </summary>
public int Precision
{
get;
set;
}
/// <summary>
/// 小数位数:目前不用
/// </summary>
public int Scale
{
get;
set;
}
/// <summary>
/// 默认值:目前不用
/// </summary>
public object DefaultValue
{
get;
set;
}
/// <summary>
/// 当验证不通过时，是否使用默认值
/// 暂时不用
/// </summary>
public bool UsedDefaultValueWhenValidateError
{
get;
set;
}
/// <summary>
/// 是否允许为空，暂时不用
/// </summary>
public bool AllowNull
{
get;
set;
}
/// <summary>
/// 赋值时的验证接口,暂时不用
/// </summary>
public ValidateCallback ValidateProvider
{
get;
set;
}
}
public interface IExtend<TSource, TTarget>
{
TTarget ConvertToTarget(TSource src);
}
/// <summary>
/// 基本数据类型的扩展
/// </summary>
public static class BaseClassExtendMethod
{
// This is the extension method.
// The first parameter takes the "this" modifier
// and specifies the type for which the method is defined.
public static TTarget[] ToArray<TSource, TTarget>(this TSource[] source, IExtend<TSource, TTarget> convertTool)
{
if (source.Length == 0)
return null;
TTarget[] res = new TTarget[source.Length];
for (int i = 0; i < source.Length; i++)
{
res[i] = convertTool.ConvertToTarget(source[i]);
}
return res;
}
public static string ToFormat(this string txt, string format, params Object[] args)
{
return string.Format(System.Globalization.CultureInfo.InvariantCulture, format, args);
}
public static string ToFormat(this string txt, string format, Object arg0)
{
return string.Format(System.Globalization.CultureInfo.InvariantCulture, format, new object[] { arg0 });
}
public static string ToFormat(this string txt, string format, Object arg0, Object arg1)
{
return string.Format(System.Globalization.CultureInfo.InvariantCulture, format, new object[] { arg0, arg1 });
}
public static string ToFormat(this string txt, string format, Object arg0, Object arg1, Object arg2)
{
return string.Format(System.Globalization.CultureInfo.InvariantCulture, format, new object[] { arg0, arg1, arg2 });
}
//static public implicit operator bool(int iValue)
//{
// if(iValue >0)
// return true;
// return false;
//}
//static public explicit operator int(bool bValue)
//{
// if (bValue == true)
// return 1;
// return 0;
//}
}
}
--------------都是辅助方法和接口
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace DataAccess
{
public interface IExtend<TSource, TTarget>
{
TTarget ConvertToTarget(TSource src);
}
/// <summary>
/// 基本数据类型的扩展
/// </summary>
public static class BaseClassExtendMethod
{
// This is the extension method.
// The first parameter takes the "this" modifier
// and specifies the type for which the method is defined.
public static TTarget[] ToArray<TSource, TTarget>(this TSource[] source, IExtend<TSource, TTarget> convertTool)
{
if (source.Length == 0)
return null;
TTarget[] res = new TTarget[source.Length];
for (int i = 0; i < source.Length; i++)
{
res[i] = convertTool.ConvertToTarget(source[i]);
}
return res;
}
public static string ToFormat(this string txt, string format, params Object[] args)
{
return string.Format(System.Globalization.CultureInfo.InvariantCulture, format, args);
}
public static string ToFormat(this string txt, string format, Object arg0)
{
return string.Format(System.Globalization.CultureInfo.InvariantCulture, format, new object[] { arg0 });
}
public static string ToFormat(this string txt, string format, Object arg0, Object arg1)
{
return string.Format(System.Globalization.CultureInfo.InvariantCulture, format, new object[] { arg0, arg1 });
}
public static string ToFormat(this string txt, string format, Object arg0, Object arg1, Object arg2)
{
return string.Format(System.Globalization.CultureInfo.InvariantCulture, format, new object[] { arg0, arg1, arg2 });
}
//static public implicit operator bool(int iValue)
//{
// if(iValue >0)
// return true;
// return false;
//}
//static public explicit operator int(bool bValue)
//{
// if (bValue == true)
// return 1;
// return 0;
//}
}
}
-----
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace DataAccess.Core
{
/// <summary>
/// 数据连接组件
/// </summary>
public interface IConn
{
/// <summary>
/// 连接字符串
/// </summary>
string ConnectionString
{
get;
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common ;
using System.Data;
namespace DataAccess.Core
{
public delegate void RecordFormatCallback(IDataRecord record);
public delegate void RecordFormatCallback<T>(CoreDataRecord<T> record) where T:struct,IComparable;
}
-----------------------------例子:仅用于测试---------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using DataAccess;
using System.Reflection;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
using DataAccess.Core;
namespace DataAccess.DataFacotry
{
[DataBaseStore(TableOrViewName = "DtTeacher")]
public enum TeacherEnum : int
{
[DataBaseFiled(IsPrimaryKey = true)]
TechId,
TechUserName,
RealName,
IsPublish,
Introduc,
SeoKey,
SeoTitle,
SeoDesc,
CreateTime,
/// <summary>
/// 添加老师的用户
/// </summary>
Creator
}
public class TeacherEntity
{
public int TechId { get; set; }
public string TechUserName { get; set; }
public string RealName { get; set; }
public bool IsPublish { get; set; }
public string Introduc { get; set; }
public string SeoKey { get; set; }
public string SeoTitle { get; set; }
public string SeoDesc { get; set; }
public DateTime CreateTime { get; set; }
/// <summary>
/// 添加老师的用户
/// </summary>
public string Creator { get; set; }
}
public class TeacherReader : CoreBase<TeacherEnum>
{
public TeacherReader()
: base()
{
}
/// <summary>
/// 0:此用户的老师已经存在；-1:用户名为空；-2：实名为空,-3:返回值为NULL(此时是无法确认是否添加成功）
/// </summary>
/// <param name="tch"></param>
/// <returns></returns>
public int CreateTeacher(TeacherEntity tch)
{
if (tch != null)
throw new System.ArgumentNullException();
if (string.IsNullOrEmpty(tch.TechUserName))
return -1;
if (string.IsNullOrEmpty(tch.RealName))
return -2;
int idx = 0;
using (SqlConnection cnn = new SqlConnection(this.ConnectionString))
using (SqlCommand cmd = new SqlCommand("teacherCreate", cnn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@TeachUserName", tch.TechUserName);
cmd.Parameters.AddWithValue("@RealName", tch.RealName);
cmd.Parameters.AddWithValue("@IsPublish", tch.IsPublish);
cmd.Parameters.AddWithValue("@Introduc", tch.Introduc);
cmd.Parameters.AddWithValue("@SeoKey", tch.SeoKey);
cmd.Parameters.AddWithValue("@SeoTitle", tch.SeoTitle);
cmd.Parameters.AddWithValue("@SeoDesc", tch.SeoDesc);
cmd.Parameters.AddWithValue("@Creator", tch.Creator);
cnn.Open();
object jx = cmd.ExecuteScalar();
cnn.Close();
if (jx != null)
{
idx = (int)jx;
}
else
{
idx = -3;
}
}
return idx;
}
/// <summary>
/// 0:此用户的老师已经存在；-1:用户名为空；-2：实名为空,-3:返回值为NULL(此时是无法确认是否添加成功）
/// -4:TechId属性小于等于0
/// </summary>
/// <param name="tch"></param>
/// <returns></returns>
public int ModifyTeacher(TeacherEntity tch)
{
if (tch != null)
throw new System.ArgumentNullException();
if (string.IsNullOrEmpty(tch.TechUserName))
return -1;
if (string.IsNullOrEmpty(tch.RealName))
return -2;
if (tch.TechId <= 0)
return -4;
int idx = 0;
using (SqlConnection cnn = new SqlConnection(this.ConnectionString))
using (SqlCommand cmd = new SqlCommand("teacherModify", cnn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@TechId", tch.TechId);
cmd.Parameters.AddWithValue("@TeachUserName", tch.TechUserName);
cmd.Parameters.AddWithValue("@RealName", tch.RealName);
cmd.Parameters.AddWithValue("@IsPublish", tch.IsPublish);
cmd.Parameters.AddWithValue("@Introduc", tch.Introduc);
cmd.Parameters.AddWithValue("@SeoKey", tch.SeoKey);
cmd.Parameters.AddWithValue("@SeoTitle", tch.SeoTitle);
cmd.Parameters.AddWithValue("@SeoDesc", tch.SeoDesc);
cmd.Parameters.AddWithValue("@Creator", tch.Creator);
cnn.Open();
object jx = cmd.ExecuteScalar();
cnn.Close();
if (jx != null)
{
idx = (int)jx;
}
else
{
idx = -3;
}
}
return idx;
}
/// <summary>
/// 删除指定的教师
/// </summary>
/// <param name="techIds"></param>
public void DeleteTeacher(int[] techIds)
{
if (techIds == null || techIds.Length == 0)
return;
using (SqlConnection cnn = new SqlConnection(this.ConnectionString))
using (SqlCommand cmd = new SqlCommand("teacherDelete", cnn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@TechId", 0);
cnn.Open();
foreach (int idx in techIds)
{
if (idx <= 0)
continue;
cmd.Parameters[0].Value = idx;
cmd.ExecuteNonQuery();
}
cnn.Close();
}
}
}
}
-----------------------------------CORE》CS
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.Specialized;
using System.Text.RegularExpressions;
using DataAccess;
using System.Reflection;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
namespace DataAccess.Core
{
public class CoreDataRecord<T> where T:struct ,IComparable
{
IDataRecord mRecorder = null;
public IDataRecord Record
{
get { return mRecorder; }
}
public CoreDataRecord(IDataRecord dr)
{
if (dr == null)
throw new System.ArgumentNullException();
mRecorder = dr;
}
public object this[T columnField]
{
get
{
string na = columnField.ToString();
return mRecorder[na];
}
}
}
/// <summary>
/// 内部使用，不对外公开的操作符定义
/// </summary>
public enum AndOr : int
{
None = 0,
And = 1,
Or = 2
}
/// <summary>
/// 列过滤表达式基类
/// </summary>
/// <typeparam name="T"></typeparam>
public sealed class ColumnBase<T> where T : struct, IComparable
{
#region 变量
private string sqlExpress;
private AndOr expReleation;
private ColumnBase<T> LeftNode;
private ColumnBase<T> RightNode;
private Dictionary<string, object> pArgs;
private CoreBase<T> ptable;
#endregion
public static int currentIdx = 0;
internal ColumnBase(CoreBase<T> tx)
{
ptable = tx;
expReleation = AndOr.None;
LeftNode = null;
RightNode = null;
pArgs = null;
}
public string FilterString
{
get
{
string txt = "";
switch (this.expReleation)
{
case AndOr.Or:
txt = "(" + LeftNode.FilterString + " OR " + this.RightNode.FilterString + ")";
break;
case AndOr.And:
txt = "(" + LeftNode.FilterString + " AND " + this.RightNode.FilterString + ")";
break;
default:
txt = sqlExpress;
break;
}
return txt;
}
}
public SqlParameter[] Parameters
{
get
{
List<SqlParameter> ps = new List<SqlParameter>();
switch (this.expReleation)
{
case AndOr.Or:
case AndOr.And:
ps.AddRange(LeftNode.Parameters);
ps.AddRange(RightNode.Parameters);
break;
default:
if (this.pArgs != null)
{
Dictionary<string, object>.Enumerator ie = pArgs.GetEnumerator();
while (ie.MoveNext())
{
SqlParameter p = new SqlParameter(ie.Current.Key, ie.Current.Value);
ps.Add(p);
}
}
break;
}
return ps.ToArray();
}
}
#region 常用操作
/// <summary>
/// 等于
/// </summary>
/// <typeparam name="V"></typeparam>
/// <param name="t1"></param>
/// <param name="v"></param>
public ColumnBase<T> Equal<V>(T t1, V v)
{
ColumnBase<T> cb = new ColumnBase<T>(this.ptable);
string pn = string.Format("@{0}{1}", ptable.ColName(t1),currentIdx ++);
cb.sqlExpress = string.Format(" {0}.{1}={2}", ptable.TableName, ptable.ColName(t1), pn);
cb.pArgs = new Dictionary<string, object>();
cb.expReleation = AndOr.None;
cb.pArgs.Add(pn, v);
return cb;
}
/// <summary>
/// 不等于
/// </summary>
/// <typeparam name="V"></typeparam>
/// <param name="t1"></param>
/// <param name="v1"></param>
public ColumnBase<T> UnEqual<V>(T t1, V v1)
{
ColumnBase<T> cb = new ColumnBase<T>(this.ptable);
string pn = string.Format("@{0}{1}", ptable.ColName(t1), currentIdx++);
cb.sqlExpress = string.Format(" {0}.{1}<>{2}", ptable.TableName,ptable.ColName(t1), pn);
cb.pArgs = new Dictionary<string, object>();
cb.pArgs.Add(pn, v1);
return cb;
}
/// <summary>
/// 大于等于
/// </summary>
/// <typeparam name="V"></typeparam>
/// <param name="t1"></param>
/// <param name="v1"></param>
public ColumnBase<T> Biger<V>(T t1, V v1)
{
ColumnBase<T> cb = new ColumnBase<T>(this.ptable);
string pn = string.Format("@{0}{1}", ptable.ColName(t1),currentIdx ++);
cb.sqlExpress = string.Format(" {0}.{1}>{2}", ptable.TableName, ptable.ColName(t1), pn);
cb.pArgs = new Dictionary<string, object>();
cb.pArgs.Add(pn, v1);
return cb;
}
/// <summary>
/// 大于等于
/// </summary>
/// <typeparam name="V"></typeparam>
/// <param name="t1"></param>
/// <param name="v1"></param>
public ColumnBase<T> BigerThen<V>(T t1, V v1)
{
ColumnBase<T> cb = new ColumnBase<T>(this.ptable);
string pn = string.Format("@{0}{1}", ptable.ColName(t1),currentIdx ++);
cb.sqlExpress = string.Format(" {0}.{1}>={2}", ptable.TableName, ptable.ColName(t1), pn);
cb.pArgs = new Dictionary<string, object>();
cb.pArgs.Add(pn, v1);
return cb;
}
/// <summary>
/// 小于
/// </summary>
/// <typeparam name="V"></typeparam>
/// <param name="t1"></param>
/// <param name="v1"></param>
public ColumnBase<T> Smaller<V>(T t1, V v1)
{
ColumnBase<T> cb = new ColumnBase<T>(this.ptable);
string pn = string.Format("@{0}{1}", ptable.ColName(t1),currentIdx ++);
cb.sqlExpress = string.Format(" {0}.{1}<{2}", ptable.TableName, ptable.ColName(t1), pn);
cb.pArgs = new Dictionary<string, object>();
cb.pArgs.Add(pn, v1);
return cb;
}
/// <summary>
/// 小于等于
/// </summary>
/// <typeparam name="V"></typeparam>
/// <param name="t1"></param>
/// <param name="v1"></param>
public ColumnBase<T> SmallerThen<V>(T t1, V v1)
{
ColumnBase<T> cb = new ColumnBase<T>(this.ptable);
string pn = string.Format("@{0}{1}", ptable.ColName(t1),currentIdx ++);
cb.sqlExpress = string.Format(" {0}.{1}<={2}", ptable.TableName, ptable.ColName(t1), pn);
cb.pArgs = new Dictionary<string, object>();
cb.pArgs.Add(pn, v1);
return cb;
}
/// <summary>
/// In
/// </summary>
/// <typeparam name="V"></typeparam>
/// <param name="t1"></param>
/// <param name="v1"></param>
public ColumnBase<T> In<V>(T t1, V[] v1)
{
ColumnBase<T> cb = new ColumnBase<T>(this.ptable);
cb.pArgs = new Dictionary<string, object>();
int len = v1.Length;
string[] res = new string[len];
string cn = ptable.ColName(t1);
for (int i = 0; i < len; i++)
{
res[i] = string.Format("@{0}{1}{2}", cn, i + 1,currentIdx ++);
cb.pArgs.Add(res[i], v1[i]);
}
cb.sqlExpress = string.Format("{0} IN ({1})", cn, string.Join(",", res));
return cb;
}
/// <summary>
/// Not In
/// </summary>
/// <typeparam name="V"></typeparam>
/// <param name="t1"></param>
/// <param name="v1"></param>
public ColumnBase<T> NotIn<V>(T t1, V[] v1)
{
ColumnBase<T> cb = new ColumnBase<T>(this.ptable);
cb.pArgs = new Dictionary<string, object>();
int len = v1.Length;
string[] res = new string[len];
string cn = ptable.ColName(t1);
for (int i = 0; i < len; i++)
{
res[i] = string.Format("@{0}{1}{2}", cn, i + 1,currentIdx ++);
cb.pArgs.Add(res[i], v1[i]);
}
cb.sqlExpress = string.Format("{0} NOT IN ({1})", cn, string.Join(",", res));
return cb;
}
#endregion
#region 重载操作符
public static ColumnBase<T> operator &(ColumnBase<T> left, ColumnBase<T> right)
{
if (left == null && right == null)
{
return null;
}
else if (left != null && right != null)
{
if (string.IsNullOrEmpty(left.sqlExpress) && string.IsNullOrEmpty(right.sqlExpress))
{
return null;
}
else if ((string.IsNullOrEmpty(left.sqlExpress) == false) && (string.IsNullOrEmpty(right.sqlExpress) == false))
{
ColumnBase<T> t = new ColumnBase<T>(left.ptable);
t.sqlExpress = string.Format("( {0} AND {1} )", left.sqlExpress, right.sqlExpress);
t.LeftNode = left;
t.RightNode = right;
t.expReleation = AndOr.And;
return t;
}
else if (string.IsNullOrEmpty(right.sqlExpress))
{
return left;
}
else
return right;
}
else if (left == null)
return right;
else
return left;
}
public static ColumnBase<T> operator |(ColumnBase<T> left, ColumnBase<T> right)
{
if (left == null && right == null)
{
return null;
}
else if (left != null && right != null)
{
if (string.IsNullOrEmpty(left.sqlExpress) && string.IsNullOrEmpty(right.sqlExpress))
{
return null;
}
else if ((string.IsNullOrEmpty(left.sqlExpress) == false) && (string.IsNullOrEmpty(right.sqlExpress) == false))
{
ColumnBase<T> t = new ColumnBase<T>(left.ptable);
t.sqlExpress = string.Format("( {0} OR {1} )", left.sqlExpress, right.sqlExpress);
t.LeftNode = left;
t.RightNode = right;
t.expReleation = AndOr.Or;
return t;
}
else if (string.IsNullOrEmpty(right.sqlExpress))
{
return left;
}
else
return right;
}
else if (left == null)
return right;
else
return left;
}
#endregion
}
/// <summary>
/// 列排序的基类
/// </summary>
/// <typeparam name="T"></typeparam>
public sealed class ColumnOrder<T> where T : struct, IComparable
{
private CoreBase<T> ptable;
private string expOrd = "";
private ColumnOrder<T> pre;
internal ColumnOrder(CoreBase<T> tx)
{
ptable = tx;
pre = null;
}
public ColumnOrder<T> Asc(T t)
{
string cnName = ptable.ColName(t);
ColumnOrder<T> c = new ColumnOrder<T>(this.ptable);
c.pre = this;
c.expOrd = cnName + " ASC ";
return c;
}
public ColumnOrder<T> Desc(T t)
{
string cnName = ptable.ColName(t);
ColumnOrder<T> c = new ColumnOrder<T>(this.ptable);
c.pre = this;
c.expOrd = cnName + " DESC ";
return c;
}
/// <summary>
/// 注意可能返回空字符串
/// </summary>
public string OrderString
{
get
{
if (this.expOrd == "")
return string.Empty;
List<string> x = new List<string>();
x.Insert(0, this.expOrd);
while (pre != null && pre.expOrd !="" )
{
x.Insert(0, this.pre.expOrd);
pre = this.pre.pre;
}
return string.Join(",", x.ToArray());
}
}
}
/// <summary>
/// 选择列的基类
/// </summary>
/// <typeparam name="T"></typeparam>
public sealed class ColumnSelect<T> where T : struct, IComparable
{
private CoreBase<T> ptable;
private string expOrd = "";
internal ColumnSelect(CoreBase<T> tx)
{
ptable = tx;
}
public ColumnSelect<T> And(T t)
{
string cnName = ptable.ColName(t);
string asName = ptable.AliasName(t);
ColumnSelect<T> c = new ColumnSelect<T>(this.ptable);
c.expOrd = expOrd + (string.IsNullOrEmpty(expOrd) ? (ptable.TableName + "." + cnName + " as " + asName) : ("," + ptable.TableName + "." +cnName + " as " + asName));
return c;
}
public string SelectCoumns
{
get
{
return this.expOrd;
}
}
}
/// <summary>
/// 列表达式定义
/// </summary>
/// <typeparam name="T"></typeparam>
public abstract class CoreBase<T> : IDisposable, IConn where T : struct, IComparable
{
protected void Init()
{
string txtMsg = "";
#region 检查基本属性的设置
bool isEnum = typeof(T).IsEnum;
if (typeof(T).IsEnum == false)
throw new System.TypeLoadException(txtMsg.ToFormat("{0} 不是枚举类型", typeof(T)));
//是否应用的FlagsArttibutes属性
bool tabAttr = false;
object[] x = typeof(T).GetCustomAttributes(false);
#endregion
#region 验证表属性设置
DataBaseStoreAttribute dst = null;
foreach (object o in x)
{
if (o.GetType() == typeof(DataBaseStoreAttribute))
{
dst = (DataBaseStoreAttribute)o;
tabAttr = true;
break;
}
}
if (!tabAttr)
throw new System.TypeLoadException(txtMsg.ToFormat("{0}没有指定DataBaseStoreAttribute", typeof(T)));
if (string.IsNullOrEmpty(dst.TableOrViewName))
throw new System.TypeLoadException(txtMsg.ToFormat("{0}没有指定表或视图的名称", typeof(T)));
if (Regex.IsMatch(dst.TableOrViewName, @"^[A-Z]{1,100}[a-z0-9_]{0,100}$", RegexOptions.IgnoreCase) == false)
{
throw new System.TypeLoadException(txtMsg.ToFormat("表名:{0} 不符合 [A-Z]{1,100}[a-z0-9_]{0,100} 的命名规范", dst.TableOrViewName));
}
TableName = dst.TableOrViewName;
#endregion
#region 字段基类型
TypeCode tcode = Type.GetTypeCode(typeof(T));
if (tcode != TypeCode.Int32)
{
throw new System.TypeLoadException(txtMsg.ToFormat(" {0} 的类型定义出错 应该为：public enum {0} ：int ", typeof(T)));
}
#endregion
#region 字段的属性设置是否超出范围
FieldInfo[] cc = typeof(T).GetFields();
if (cc == null || cc.Length <= 1)
throw new System.TypeLoadException(txtMsg.ToFormat("{0} 的类型定义出错,没有指定可用字段", typeof(T)));//
//检查取值范围
List<int> tmpLst = new List<int>();
foreach (var ci in cc)
{
if (ci.IsSpecialName)
continue;
string name = ci.Name;
int pv = (int)ci.GetValue(default(T));
if (pv == 0)
{
continue;
}
if (tmpLst.Contains(pv))
{
throw new System.TypeLoadException(txtMsg.ToFormat("{0} 出现重复值 {1}={2} ", typeof(T), ci.Name, pv));
}
tmpLst.Add(pv);
}
#endregion
#region 主键是否存在
List<T> tmp = new List<T>();
foreach (var ci in cc)
{
if (ci.IsSpecialName)
continue;
DataBaseFiledAttribute[] cx = (DataBaseFiledAttribute[])ci.GetCustomAttributes(typeof(DataBaseFiledAttribute), false);
if (cx == null || cx.Length != 1)
{
continue;
// throw new System.TypeLoadException(txtMsg.ToFormat("{0}每一个字段只能应用一个DataBaseFiledAttribute属性",ci.Name));
}
DataBaseFiledAttribute d = cx[0];
if (!d.IsPrimaryKey)
continue;
int cv = (int)((object)ci.GetValue(default(T)));
if (!string.IsNullOrEmpty(d.ColumnName))
{
if (!Regex.IsMatch(d.ColumnName, @"^[A-Z]{1,100}[a-z0-9_]{0,100}$", RegexOptions.IgnoreCase))
{
throw new System.TypeLoadException(txtMsg.ToFormat("{0}.{1}的属性 DataBaseFiled.ColumnName的取值格式不正确,要么不设置，要么设为[A-Z]{1,}[a-z0-9_]{0,244} 的格式", typeof(T), ci.Name));
}
}
T xf = (T)ci.GetValue(default(T));
tmp.Add(xf);
}
if (tmp.Count == 0)
{
throw new System.TypeLoadException(txtMsg.ToFormat("{0} 没有指定主键", typeof(T)));
}
PrimaryKey = tmp.ToArray();
#endregion
#region 获取所有列名
Columns = new Dictionary<T, FiledSetting>();
foreach (var fi in cc)
{
if (fi.IsSpecialName)
continue;
object tmpV = fi.GetValue(default(T));
int k = (int)tmpV;
if (k == 0)
continue;
DataBaseFiledAttribute[] cx = (DataBaseFiledAttribute[])fi.GetCustomAttributes(typeof(DataBaseFiledAttribute), false);
FiledSetting fs = new FiledSetting();
if (cx != null && cx.Length == 1)
{
DataBaseFiledAttribute d = cx[0];
fs.ColName = string.IsNullOrEmpty(d.ColumnName) ? fi.Name : d.ColumnName;
fs.IsPrimaryKey = d.IsPrimaryKey;
fs.EnumFiledName = fi.Name;
}
else
{
fs.ColName = fs.EnumFiledName = fi.Name;
fs.IsPrimaryKey = false;
}
T tc = (T)(tmpV);
Columns.Add(tc, fs);
}
#endregion
}
/// <summary>
/// 主关键字列
/// </summary>
protected T[] PrimaryKey;
/// <summary>
/// 所有列列表
/// </summary>
protected Dictionary<T, FiledSetting> Columns;
protected CoreBase()
{
Init();
}
#region 查询列表接口
/// <summary>
/// 获取指定的班级信息
/// </summary>
/// <param name="recordCallback"></param>
/// <param name="selected"></param>
/// <param name="filter"></param>
/// <param name="orders"></param>
public void Enum(RecordFormatCallback recordCallback, ColumnSelect<T> selected,
ColumnBase<T> filter, ColumnOrder<T> orders)
{
if (recordCallback == null)
throw new ArgumentNullException("recordCallback没有指定，无任何意义的操作");
if (selected == null || selected.SelectCoumns =="" )
{
selected = this.AllColumns;
}
string selectString = selected.SelectCoumns;
string filterString = "";
if (filter != null && filter.FilterString != "")
{
filterString = filter.FilterString;
}
string orderString = "";
if ((orders == null) || (string.IsNullOrEmpty( orders.OrderString)))
{
int len = PrimaryKey.Length;
string[] cteTabCol = new string[len];
for (int i = 0; i < len; i++)
{
cteTabCol[i] = ColName(PrimaryKey[i]);
}
orderString = string.Join(",", cteTabCol);
}
else
{
orderString = orders.OrderString;
}
string cmdText="";
cmdText = cmdText.ToFormat("Select {0} From {1} {2} Order By {3}",
selectString,
this.TableName,
string.IsNullOrEmpty(filterString) ? "" : " Where " + filterString,
orderString
);
using(SqlConnection cnn=new SqlConnection(this.ConnectionString ))
using (SqlCommand cmd = new SqlCommand(cmdText, cnn))
{
if (filter !=null && filter.Parameters != null && filter.Parameters.Length > 0)
cmd.Parameters.AddRange(filter.Parameters);
cnn.Open();
using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
recordCallback(dr);
}
dr.Close();
}
cnn.Close();
}
}
/// <summary>
/// 获取指定行号与记录数的列表
/// </summary>
/// <param name="recordCallback"></param>
/// <param name="startRecord"></param>
/// <param name="recordCount"></param>
/// <param name="selected"></param>
/// <param name="filter"></param>
/// <param name="orders"></param>
public void Enum(RecordFormatCallback recordCallback, int startRecord, int recordCount, ColumnSelect<T> selected,
ColumnBase<T> filter, ColumnOrder<T> orders)
{
if (startRecord < 0 )
throw new System.ArgumentOutOfRangeException("startRecord必须不能小于0");
if( recordCount <=0)
throw new System.ArgumentOutOfRangeException("recordCount必须大于0");
if (recordCallback == null)
throw new ArgumentNullException("recordCallback没有指定，无任何意义的操作");
if (selected == null || selected.SelectCoumns == "")
{
selected = this.AllColumns;
}
string selectString = selected.SelectCoumns;
int len=PrimaryKey.Length ;
string ordString = "";
if ( (orders == null) || string.IsNullOrEmpty(orders.OrderString ))
{
string[] ort = new string[len];
for (int i = 0; i < len; i++)
{
ort[i] = ColName(PrimaryKey[i]);
}
ordString = string.Join(",", ort);
}
else
{
ordString = orders.OrderString;
}
string filterString = "";
if (filter!= null && filter.FilterString != "")
{
filterString = filter.FilterString;
}
string[] cteTabCol = new string[len ];
string[] cteRL = new string[len];
string tmp="";
for (int i = 0; i < len; i++)
{
cteTabCol[i] = ColName(PrimaryKey[i]);
cteRL[i] = tmp.ToFormat(" __Cte.{0}={1}.{0} ", ColName(PrimaryKey[i]), TableName);
}
string cmdText = ";With __Cte(cteRN,{0}) as( "
+ " Select Top (@CN) RN=ROW_NUMBER() OVER ( ORDER BY {1} ),{0} "
+ " From {2} {3} "
+ ") "
+ " Select {4} From {2},__Cte Where cteRN>(@ST) and ({5}) Order by cteRN asc ";
cmdText = cmdText.ToFormat(cmdText,
string.Join(",", cteTabCol),
ordString ,
TableName,
string.IsNullOrEmpty(filterString) ? "" : " Where " + filterString,
selected.SelectCoumns ,
string.Join(" and ", cteRL)
);
using(SqlConnection cnn=new SqlConnection(this.ConnectionString ))
using (SqlCommand cmd = new SqlCommand(cmdText, cnn))
{
if (filter !=null && filter.Parameters != null && filter.Parameters.Length > 0)
{
cmd.Parameters.AddRange(filter.Parameters);
}
cmd.Parameters.AddWithValue("@CN", startRecord + recordCount);
cmd.Parameters.AddWithValue("@ST", startRecord);
cnn.Open();
using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
recordCallback(dr);
}
dr.Close();
}
cnn.Close();
}
}
/// <summary>
/// 获取指定的班级信息
/// </summary>
/// <param name="recordCallback"></param>
/// <param name="selected"></param>
/// <param name="filter"></param>
/// <param name="orders"></param>
public void Enum(RecordFormatCallback<T> recordCallback, ColumnSelect<T> selected,
ColumnBase<T> filter, ColumnOrder<T> orders)
{
if (recordCallback == null)
throw new System.ArgumentNullException();
RecordFormatCallback rcb = (IDataRecord dr) =>
{
recordCallback(new CoreDataRecord<T>(dr));
};
this.Enum(rcb, selected, filter, orders);
}
/// <summary>
/// 获取指定行号与记录数的列表
/// </summary>
/// <param name="recordCallback"></param>
/// <param name="startRecord"></param>
/// <param name="recordCount"></param>
/// <param name="selected"></param>
/// <param name="filter"></param>
/// <param name="orders"></param>
public void Enum(RecordFormatCallback<T> recordCallback, int startRecord, int recordCount, ColumnSelect<T> selected,
ColumnBase<T> filter, ColumnOrder<T> orders)
{
if (recordCallback == null)
throw new System.ArgumentNullException();
RecordFormatCallback rcb=(IDataRecord dr)=>{
recordCallback(new CoreDataRecord<T>(dr));
};
this.Enum(rcb, startRecord, recordCount, selected, filter, orders);
}
public int Count(ColumnBase<T> filter)
{
string cmdText = "";
if (filter == null || filter.FilterString == "")
{
cmdText = " Select Count(*) From " + TableName;
}
else
{
cmdText = cmdText.ToFormat("Select Count(*) From {0} Where {1}", TableName, filter.FilterString);
}
int res = 0;
using (SqlConnection cnn = new SqlConnection(this.ConnectionString))
using (SqlCommand cmd = new SqlCommand(cmdText, cnn))
{
if (filter !=null && filter.Parameters != null && filter.Parameters.Length > 0)
{
cmd.Parameters.AddRange(filter.Parameters);
}
cnn.Open();
res = (int)cmd.ExecuteScalar();
cnn.Close();
}
return res;
}
#endregion
#region 删除
/// <summary>
/// 删除指定的记录，并输出删除的主键列表
/// </summary>
/// <param name="recordCallback"></param>
/// <param name="filters"></param>
public virtual void Delete(RecordFormatCallback recordCallback, ColumnBase<T> filters)
{
if((filters ==null) || string.IsNullOrEmpty(filters.FilterString ))
{
throw new System.NotSupportedException("不允许的操作:没有指定删除条件的删除动作,若要清表，请联系DBA");
}
string cmdText = "";
if (recordCallback == null)
{
cmdText = cmdText.ToFormat("Delete {0} Where {1} ", TableName, filters.FilterString);
}
else
{
int len = PrimaryKey.Length;
string[] cc = new string[len];
for (int i = 0; i < len; i++)
{
cc[i] = string.Format("deleted.{0} as {1} " , ColName(PrimaryKey[i]), AliasName(PrimaryKey[i]));
}
cmdText = cmdText.ToFormat("Delete {0} Output {1} Where {2} ",
TableName, string.Join(",", cc), filters.FilterString);
}
using(SqlConnection cn=new SqlConnection(this.ConnectionString ))
using (SqlCommand cmd = new SqlCommand(cmdText, cn))
{
cmd.Parameters.AddRange(filters.Parameters);
cn.Open();
if (recordCallback != null)
{
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
recordCallback(dr);
}
dr.Close();
}
}
else
{
cmd.ExecuteNonQuery();
}
cn.Close();
}
}
#endregion
#region 属性
/// <summary>
/// 原始数据列名
/// </summary>
/// <param name="field"></param>
/// <returns></returns>
public string ColName(T field)
{
return Columns[field].ColName;
}
/// <summary>
/// 希望输出时的列名或称为别名
/// </summary>
public string AliasName(T field)
{
return Columns[field].EnumFiledName;
}
/// <summary>
/// 表名
/// </summary>
public string TableName
{
get;
protected set;
}
/// <summary>
/// 创建一个过滤节点
/// </summary>
public ColumnBase<T> FilterNode
{
get
{
return new ColumnBase<T>(this);
}
}
/// <summary>
/// 创建一个排序节点
/// </summary>
public ColumnOrder<T> OrderNode
{
get
{
return new ColumnOrder<T>(this);
}
}
/// <summary>
/// 创建一个选择节点
/// </summary>
public ColumnSelect<T> SelectNode
{
get
{
return new ColumnSelect<T>(this);
}
}
#endregion
/// <summary>
/// 资源清理
/// </summary>
protected virtual void Collect()
{
}
#region 更新某些列
/// <summary>
/// 更新符合条件的某一特定的列为指定的值(请更新原子列及不和其它表有关联的列）
/// 此操作不会更新关联表，请慎重使用
/// </summary>
/// <typeparam name="V"></typeparam>
/// <param name="specialField"></param>
/// <param name="value"></param>
/// <param name="filters"></param>
public virtual void UploadSpecailCol<V>(T specialField, V value, ColumnBase<T> filters)
{
int kc = PrimaryKey.Length;
for (int i = 0; i < kc; i++)
{
if (specialField.CompareTo(PrimaryKey[i] )==0)
{
throw new System.Exception("试图更新关键字列");
}
}
string cmdText="";
if (filters == null || filters.FilterString == "")
{
cmdText = cmdText.ToFormat(" Update {0} set {1}=@A ", TableName, ColName(specialField));
}
else
{
cmdText = cmdText.ToFormat(" Update {0} set {1}=@A Where {2} ", TableName, ColName(specialField), filters.FilterString);
}
using(SqlConnection cnn=new SqlConnection(this.ConnectionString))
using(SqlCommand cmd=new SqlCommand (cmdText ,cnn))
{
cmd.CommandType =CommandType.Text ;
cmd.Parameters.AddWithValue("@A",value );
if(filters !=null && filters.Parameters !=null && filters.Parameters .Length >0)
cmd.Parameters.AddRange(filters.Parameters );
cnn.Open ();
cmd.ExecuteNonQuery ();
cnn.Close ();
}
}
/// <summary>
/// 更新指定的两列(请更新原子列及不和其它表有关联的列）
/// 此操作不会更新关联表，请慎重使用
/// </summary>
/// <typeparam name="V1"></typeparam>
/// <typeparam name="V2"></typeparam>
/// <param name="specialField"></param>
/// <param name="value"></param>
/// <param name="col2"></param>
/// <param name="value2"></param>
/// <param name="filters"></param>
public virtual void UploadSpecailCol<V1, V2>(T specialField, V1 value, T col2, V2 value2, ColumnBase<T> filters)
{
int kc = PrimaryKey.Length;
for (int i = 0; i < kc; i++)
{
if (specialField.CompareTo(PrimaryKey[i]) == 0)
throw new System.Exception("试图更新关键字列");
if(col2.CompareTo(PrimaryKey[i])==0)
throw new System.Exception("试图更新关键字列");
}
string cmdText="";
if (filters == null || filters.FilterString == "")
{
cmdText = cmdText.ToFormat(" Update {0} set {1}=@A ,{2}=@B ", TableName, ColName(specialField),ColName(col2) );
}
else
{
cmdText = cmdText.ToFormat(" Update {0} set {1}=@A ,{2}=@B Where {3} ",
TableName,
ColName(specialField),
ColName(col2 ),
filters.FilterString);
}
using(SqlConnection cnn=new SqlConnection(this.ConnectionString))
using(SqlCommand cmd=new SqlCommand(cmdText ,cnn))
{
cmd.CommandType =CommandType.Text ;
cmd.Parameters.AddWithValue("@A",value );
cmd.Parameters.AddWithValue("@B", value2 );
if(filters !=null && filters.Parameters !=null && filters.Parameters .Length >0)
cmd.Parameters.AddRange(filters.Parameters );
cnn.Open ();
cmd.ExecuteNonQuery ();
cnn.Close ();
}
}
/// <summary>
/// 更新指定的三列(请更新原子列及不和其它表有关联的列）
/// 此操作不会更新关联表，请慎重使用
/// </summary>
/// <typeparam name="V1"></typeparam>
/// <typeparam name="V2"></typeparam>
/// <typeparam name="V3"></typeparam>
/// <param name="t1"></param>
/// <param name="v1"></param>
/// <param name="t2"></param>
/// <param name="v2"></param>
/// <param name="t3"></param>
/// <param name="v3"></param>
/// <param name="filters"></param>
public virtual void UploadSpecailCol<V1, V2, V3>(T t1, V1 v1, T t2, V2 v2, T t3, V3 v3, ColumnBase<T> filters)
{
int kc = PrimaryKey.Length;
for (int i = 0; i < kc; i++)
{
if (t1.CompareTo(PrimaryKey[i]) == 0)
throw new System.Exception("试图更新关键字列");
if (t2.CompareTo(PrimaryKey[i]) == 0)
throw new System.Exception("试图更新关键字列");
if (t3.CompareTo(PrimaryKey[i]) == 0)
throw new System.Exception("试图更新关键字列");
}
string cmdText="";
if (filters == null || filters.FilterString == "")
{
cmdText = cmdText.ToFormat(" Update {0} set {1}=@A ,{2}=@B ,{3}=@C ", TableName, ColName(t1),ColName(t2),ColName(t3) );
}
else
{
cmdText = cmdText.ToFormat(" Update {0} set {1}=@A ,{2}=@B ,{3}=@C Where {4} ", TableName, ColName(t1),ColName(t2),ColName(t3) , filters.FilterString);
}
using(SqlConnection cnn=new SqlConnection(this.ConnectionString))
using(SqlCommand cmd=new SqlCommand (cmdText ,cnn))
{
cmd.CommandType =CommandType.Text ;
cmd.Parameters.AddWithValue("@A", v1);
cmd.Parameters.AddWithValue("@B", v2);
cmd.Parameters.AddWithValue("@C", v3);
if(filters !=null && filters.Parameters !=null && filters.Parameters .Length >0)
cmd.Parameters.AddRange(filters.Parameters );
cnn.Open ();
cmd.ExecuteNonQuery ();
cnn.Close ();
}
}
#endregion
protected ColumnSelect<T> pSelecteAll;
protected static object lockj = new object();
protected static object lockj2 = new object();
/// <summary>
/// 如果传入值为NULL或是数组长度为0时均会返回所有的列
/// </summary>
/// <param name="fields"></param>
/// <returns></returns>
public ColumnSelect<T> SelectedAllExcepted(T[] fields)
{
if (fields == null || fields .Length ==0 )
return AllColumns;
List<T> rest = new List<T>();
foreach (T t in fields)
{
if (Columns.ContainsKey(t) == false)
{
rest.Add(t);
}
}
ColumnSelect<T> res = new ColumnSelect<T>(this);
foreach (T t in rest)
{
res = res.And(t);
}
return res;
}
public ColumnSelect<T> AllColumns
{
get
{
lock ( lockj2)
{
if (pSelecteAll == null)
{
lock ( lockj)
{
pSelecteAll = new ColumnSelect<T>(this);
Dictionary<T, FiledSetting>.Enumerator ie = Columns.GetEnumerator();
while (ie.MoveNext())
{
pSelecteAll = pSelecteAll.And(ie.Current.Key);
}
}
}
}
return pSelecteAll;
}
}
#region IDisposable 成员
private bool _displosed;
private void Dispose(bool disposed)
{
if (_displosed == false && disposed == true)
{
Collect();
_displosed = true;
}
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
#endregion
#region IConn 成员
private string cnnString = "";
public string ConnectionString
{
get
{
if (cnnString == "")
{
cnnString = DBConfiguration.DTConfiguration.ConnectionDTdbString;
}
return cnnString;
}
}
#endregion
}
}
