public int Update <T>(T[] values)
{
string where = string.Empty;
PropertyInfo[] pinfos = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.DeclaredOnly | BindingFlags.Instance);
StringBuilder sb = new StringBuilder();
List <SqlParameter> parameters = new List <SqlParameter>();
string primarykey = DbComm.GetPrimaryKey <T>();
string tablename = typeof(T).Name;
int count = 0;
foreach (T value in values)
{
sb.Append(" Update " + tablename + " set ");
foreach (PropertyInfo item in pinfos)
{
if (primarykey == item.Name)
{
where = string.Format(" and {0}={1} ;", primarykey, item.GetValue(value, null));
continue;
}
sb.AppendFormat("[{0}]=@{0}{1},", item.Name, count);
parameters.Add(new SqlParameter(item.Name + count, item.GetValue(value, null)));
}
sb = sb.Remove(sb.Length - 1, 1);
sb.AppendFormat(" where 1=1 {0} ; ", where);
count++;
}
return(ExecuteNonQuery(sb.ToString(), parameters.ToArray()));
}
public int Insert <T>(T[] values)
{
PropertyInfo[] pinfos = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.DeclaredOnly | BindingFlags.Instance);
StringBuilder sbcmdtext = new StringBuilder();
List <SqlParameter> parameters = new List <SqlParameter>();
string primarykey = DbComm.GetPrimaryKey <T>();
int count = 0;
foreach (T item in values)
{
StringBuilder sbcolumn = new StringBuilder();
StringBuilder sbvalue = new StringBuilder();
foreach (PropertyInfo info in pinfos)
{
if (info.Name == primarykey)
{
continue;
}
sbcolumn.AppendFormat("[{0}],", info.Name);
sbvalue.AppendFormat("@{0}{1},", info.Name, count);
parameters.Add(new SqlParameter(info.Name + count, info.GetValue(item, null)));
}
sbcolumn = sbcolumn.Remove(sbcolumn.Length - 1, 1);
sbvalue = sbvalue.Remove(sbvalue.Length - 1, 1);
sbcmdtext.AppendFormat(" Insert Into {0}({1}) values({2}) ; ", typeof(T).Name, sbcolumn.ToString(), sbvalue.ToString());
count++;
}
return(ExecuteNonQuery(sbcmdtext.ToString(), parameters.ToArray()));
}
public DataTable ToTable <T>(int page, int count, string where, string orderbycolumn, bool orderby)
{
/*SELECT * FROM ARTICLE w1
* WHERE ID in
* (
* SELECT top 30 ID FROM
* (
* SELECT top 45030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC
* ) w ORDER BY w.YEAR ASC, w.ID ASC
* )
* ORDER BY w1.YEAR DESC, w1.ID DESC
*/
StringBuilder sb = new StringBuilder(" select ");
sb.Append(DbComm.GetColumnStr <T>());
sb.AppendFormat(" from {0} mi where ", typeof(T).Name);
if (count > 0)
{
sb.AppendFormat(" {0} in ( select top {1} {0} from ( select top {2} {0} from {3} where 1=1 {4} order by {0} desc ) mi1 order by mi1.{0} asc ) ", DbComm.GetPrimaryKey <T>(), count, (page * count) + count, typeof(T).Name, where);
}
else
{
sb.AppendFormat(" 1=1 {0} ", where);
}
if (string.IsNullOrEmpty(orderbycolumn))
{
orderbycolumn = DbComm.GetPrimaryKey <T>();
}
sb.AppendFormat(" order by mi.{0} ", orderbycolumn);
sb.AppendFormat(" {0} ", @orderby ? "asc" : "desc");
return(ToTable(sb.ToString()));
}
public T FirstDefault <T>(string where)
{
T t = default(T);
DataTable dt = ToTable <T>(where);
List <T> tlist = DbComm.DataToClass <T>(dt);
if (tlist.Count > 0)
{
t = tlist[0];
}
return(t);
}
public int Del <T>(T[] values)
{
StringBuilder sb = new StringBuilder();
string primarykey = DbComm.GetPrimaryKey <T>();
string tablename = typeof(T).Name;
foreach (T item in values)
{
sb.AppendFormat(" delete from {0} where 1=1 and {1}={2} ; ", tablename, primarykey, typeof(T).GetProperty(primarykey).GetValue(item, null));
}
return(ExecuteNonQuery(sb.ToString()));
}
public DataTable ToTable <T>(int page, int count, string where, string orderbycolumn, bool orderby)
{
StringBuilder sb = new StringBuilder(" select ");
sb.Append(DbComm.GetColumnStr <T>());
sb.AppendFormat(" from {0} where 1=1 {1} ", typeof(T).Name, where);
if (string.IsNullOrEmpty(orderbycolumn))
{
orderbycolumn = DbComm.GetPrimaryKey <T>();
}
sb.AppendFormat(" order by {0} ", orderbycolumn);
sb.AppendFormat(" {0} ", @orderby ? "asc" : "desc");
if (count > 0)
{
sb.AppendFormat(" limit {0},{1} ", page * count, count);
}
return(ToTable(sb.ToString()));
}
public int Insert <T>(T value)
{
string primarykey = DbComm.GetPrimaryKey <T>();
PropertyInfo[] pinfos = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly);
StringBuilder sb = new StringBuilder();
List <SQLiteParameter> parameters = new List <SQLiteParameter>();
StringBuilder sbcolumn = new StringBuilder();
StringBuilder sbvalue = new StringBuilder();
bool isautoid = false;
foreach (PropertyInfo item in pinfos)
{
if (item.Name == primarykey)
{
AutoId attributeautoid = Attribute.GetCustomAttribute(item, typeof(AutoId)) as AutoId;
if (attributeautoid != null)
{
isautoid = attributeautoid.SetAutoId;
}
continue;
}
sbcolumn.AppendFormat("[{0}],", item.Name);
sbvalue.AppendFormat("@{0},", item.Name);
parameters.Add(new SQLiteParameter(item.Name, item.GetValue(value, null)));
}
sbcolumn = sbcolumn.Remove(sbcolumn.Length - 1, 1);
sbvalue = sbvalue.Remove(sbvalue.Length - 1, 1);
sb.AppendFormat(" Insert Into {0} ({1}) values ({2}) ", typeof(T).Name, sbcolumn.ToString(), sbvalue.ToString());
if (isautoid)
{
sb.Append(" ; select last_insert_rowid(); ");
object obj = ExecuteScalar(sb.ToString(), parameters.ToArray());
if (obj != DBNull.Value)
{
return(Convert.ToInt32(obj));
}
}
return(ExecuteNonQuery(sb.ToString(), parameters.ToArray()));
}
public int Del <T>(long id)
{
return(Del <T>(DbComm.GetPrimaryKeyWhere <T>(id)));
}
public List <T> ToList <T>(int page, int count, string where, string column, bool orderby)
{
DataTable dt = ToTable <T>(page, count, where, column, orderby);
return(DbComm.DataToClass <T>(dt));
}
public T FirstDefault <T>(long id)
{
return(FirstDefault <T>(DbComm.GetPrimaryKeyWhere <T>(id)));
}