public int Update <T>(List <T> values)
{
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>();
int count = 0;
foreach (T titem in values)
{
object primarykeyvalue = null;
count++;
sb.AppendFormat(" Update {0} set ", typeof(T).Name);
foreach (PropertyInfo info in pinfos)
{
if (primarykey == info.Name)
{
primarykeyvalue = info.GetValue(titem, null);
continue;
}
string name = info.Name + count;
sb.AppendFormat("[{0}]=@{1},", info.Name, name);
parameters.Add(new SqlParameter(name, info.GetValue(titem, null)));
}
sb = sb.Remove(sb.Length - 1, 1);
sb.AppendFormat(" where {0}={1} ", primarykey, primarykeyvalue);
}
return(ExecuteNonQuery(sb.ToString(), parameters.ToArray()));
}
private string GetCmdtext <T>(int page, int count, string where, string orderbycolumn, bool orderby)
{
// select * from tablename limit count,page
StringBuilder sb = new StringBuilder(" select ");
PropertyInfo[] pinfos =
typeof(T).GetProperties(BindingFlags.Public | BindingFlags.DeclaredOnly | BindingFlags.Instance);
foreach (PropertyInfo item in pinfos)
{
sb.AppendFormat(" [{0}],", item.Name);
}
sb = sb.Remove(sb.Length - 1, 1);
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);
}
return(sb.ToString());
}
public List <T> Top <T>(int count, string where, string orderbycolumn, bool orderby)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" select top {0} ", count);
PropertyInfo[] pinfos =
typeof(T).GetProperties(BindingFlags.Public | BindingFlags.DeclaredOnly | BindingFlags.Instance);
foreach (PropertyInfo info in pinfos)
{
sb.AppendFormat("[{0}],", info.Name);
}
sb = sb.Remove(sb.Length - 1, 1);
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} {1} ", orderbycolumn, orderby ? "asc" : "desc");
SQLiteDataReader reader = ExecuteReader(sb.ToString()) as SQLiteDataReader;
List <T> tlist = new List <T>();
try
{
if (reader.HasRows)
{
while (reader.Read())
{
T t = Activator.CreateInstance <T>();
foreach (PropertyInfo info in pinfos)
{
object obj = reader[info.Name];
if (obj == DBNull.Value)
{
continue;
}
info.SetValue(t, reader[info.Name], null);
}
tlist.Add(t);
}
}
}
finally
{
reader.Close();
}
return(tlist);
}
public int Update <T>(List <T> values)
{
PropertyInfo[] pinfos =
typeof(T).GetProperties(BindingFlags.Public | BindingFlags.DeclaredOnly | BindingFlags.Instance);
StringBuilder sb = new StringBuilder(string.Format(" Update {0} set ", typeof(T).Name));
string primarykey = DbComm.GetPrimarykey <T>();
foreach (PropertyInfo pinfo in pinfos)
{
if (primarykey == pinfo.Name)
{
continue;
}
sb.AppendFormat("{0}=?,", pinfo.Name);
}
sb = sb.Remove(sb.Length - 1, 1);
int count = 0;
SQLiteConnection conn = new SQLiteConnection(_connectionstr);
SQLiteCommand comm = new SQLiteCommand(sb.ToString(), conn);
conn.Open();
SQLiteTransaction tran = conn.BeginTransaction();
foreach (T titem in values)
{
List <SQLiteParameter> parameters = new List <SQLiteParameter>();
foreach (PropertyInfo pinfo in pinfos)
{
if (primarykey == pinfo.Name)
{
comm.CommandText = sb + string.Format(" where {0}={1} ", primarykey, pinfo.GetValue(titem, null));
continue;
}
parameters.Add(new SQLiteParameter(pinfo.Name, pinfo.GetValue(titem, null)));
}
comm.Parameters.Clear();
comm.Parameters.AddRange(parameters.ToArray());
comm.ExecuteNonQuery();
count++;
}
tran.Commit();
tran.Dispose();
return(count);
}
public int Insert <T>(T value)
{
StringBuilder sb = new StringBuilder(string.Format(" insert into {0} (", typeof(T).Name));
StringBuilder sb2 = new StringBuilder(" values (");
PropertyInfo[] pinfos =
typeof(T).GetProperties(BindingFlags.Public | BindingFlags.DeclaredOnly | BindingFlags.Instance);
List <SqlParameter> parameters = new List <SqlParameter>();
string primarykey = DbComm.GetPrimarykey <T>();
bool isauto = false;
foreach (PropertyInfo item in pinfos)
{
if (primarykey == item.Name)
{
IsAutoId isautoid = Attribute.GetCustomAttribute(item, typeof(IsAutoId)) as IsAutoId;
if (isautoid != null)
{
isauto = isautoid.SetIsAutoId;
}
continue;
}
sb.AppendFormat(" [{0}],", item.Name);
sb2.AppendFormat("@{0},", item.Name);
parameters.Add(new SqlParameter(item.Name, item.GetValue(value, null)));
}
sb = sb.Replace(',', ')', sb.Length - 1, 1);
sb2 = sb2.Replace(',', ')', sb2.Length - 1, 1);
sb.Append(sb2);
if (isauto)
{
sb.AppendFormat(" SELECT IDENT_CURRENT('{0}') ", typeof(T).Name);
object obj = ExecuteScalar(sb.ToString(), parameters.ToArray());
if (obj != null && obj != DBNull.Value)
{
return(Convert.ToInt32(obj));
}
}
return(ExecuteNonQuery(sb.ToString(), parameters.ToArray()));
}
public int Insert <T>(List <T> values)
{
PropertyInfo[] pinfos = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.DeclaredOnly | BindingFlags.Instance);
StringBuilder sb = new StringBuilder();
StringBuilder columns = new StringBuilder(string.Format(" insert into {0} ( ", typeof(T).Name));
string primarykey = DbComm.GetPrimarykey <T>();
foreach (PropertyInfo item in pinfos)
{
if (primarykey == item.Name)
{
continue;
}
columns.AppendFormat("[{0}],", item.Name);
}
columns = columns.Replace(',', ')', columns.Length - 1, 1);
columns.Append(" values (");
int count = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
foreach (T item in values)
{
sb.Append(columns);
count++;
foreach (PropertyInfo properyinfo in pinfos)
{
if (primarykey == properyinfo.Name)
{
continue;
}
string name = properyinfo.Name + count;
sb.AppendFormat("@{0},", name);
parameters.Add(new SqlParameter(name, properyinfo.GetValue(item, null)));
}
sb = sb.Replace(',', ')', sb.Length - 1, 1);
sb.Append(" ; ");
}
return(ExecuteNonQuery(sb.ToString(), parameters.ToArray()));
}
private string GetCmdtext <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 ");
PropertyInfo[] pinfos =
typeof(T).GetProperties(BindingFlags.Public | BindingFlags.DeclaredOnly | BindingFlags.Instance);
foreach (PropertyInfo item in pinfos)
{
sb.AppendFormat(" [{0}],", item.Name);
}
sb = sb.Remove(sb.Length - 1, 1);
sb.AppendFormat(" from {0} mi where ", typeof(T).Name);
if (count > 0)
{
string primarykey = DbComm.GetPrimarykey <T>();
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 ) ", primarykey, count, page + 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(sb.ToString());
}
public int Update <T>(T value, string where)
{
PropertyInfo[] pinfos = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.DeclaredOnly | BindingFlags.Instance);
StringBuilder sb = new StringBuilder(" Update " + typeof(T).Name + " set ");
List <SqlParameter> parameters = new List <SqlParameter>();
string primarykey = DbComm.GetPrimarykey <T>();
foreach (PropertyInfo item in pinfos)
{
if (primarykey == item.Name)
{
if (string.IsNullOrEmpty(where))
{
where = string.Format(" and {0}={1} ", primarykey, item.GetValue(value, null));
}
continue;
}
sb.AppendFormat("[{0}]=@{0},", item.Name);
parameters.Add(new SqlParameter(item.Name, item.GetValue(value, null)));
}
sb = sb.Remove(sb.Length - 1, 1);
sb.AppendFormat(" where 1=1 {0} ", where);
return(ExecuteNonQuery(sb.ToString(), parameters.ToArray()));
}
public int Insert <T>(List <T> values)
{
PropertyInfo[] pinfos = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.DeclaredOnly | BindingFlags.Instance);
StringBuilder sb = new StringBuilder(string.Format(" insert into {0} ( ", typeof(T).Name));
StringBuilder sb1 = new StringBuilder(" values ( ");
string primarykey = DbComm.GetPrimarykey <T>();
bool isauto = false;
foreach (PropertyInfo item in pinfos)
{
if (primarykey == item.Name)
{
IsAutoId isautoid = Attribute.GetCustomAttribute(item, typeof(IsAutoId)) as IsAutoId;
if (isautoid != null)
{
isauto = isautoid.SetIsAutoId;
}
continue;
}
sb.AppendFormat("{0},", item.Name);
sb1.Append("?,");
}
sb = sb.Replace(',', ')', sb.Length - 1, 1);
sb1 = sb1.Replace(',', ')', sb1.Length - 1, 1);
sb.Append(sb1);
if (isauto)
{
sb.Append(" ; select last_insert_rowid(); ");
}
int count = 0;
SQLiteConnection conn = new SQLiteConnection(_connectionstr);
SQLiteCommand comm = new SQLiteCommand(sb.ToString(), conn);
conn.Open();
SQLiteTransaction tra = conn.BeginTransaction();
foreach (T item in values)
{
List <SQLiteParameter> parameters = new List <SQLiteParameter>();
foreach (PropertyInfo pinfo in pinfos)
{
if (primarykey == pinfo.Name)
{
continue;
}
parameters.Add(new SQLiteParameter(pinfo.Name, pinfo.GetValue(item, null)));
}
comm.Parameters.Clear();
comm.Parameters.AddRange(parameters.ToArray());
if (isauto)
{
object obj = comm.ExecuteScalar();
if (obj != null && obj != DBNull.Value)
{
if (!string.IsNullOrEmpty(primarykey))
{
PropertyInfo info = item.GetType().GetProperty(primarykey);
info.SetValue(item, obj, null);
}
}
}
else
{
comm.ExecuteNonQuery();
}
count++;
}
tra.Commit();
tra.Dispose();
return(count);
}
public T FirstDefault <T>(long id)
{
string where = DbComm.GetPrimaryKeywhere <T>(id);
return(FirstDefault <T>(where));
}
public DataTable ToTable <T>(long id)
{
string where = DbComm.GetPrimaryKeywhere <T>(id);
return(ToTable <T>(0, 0, where, null, false));
}
public DataTable ToTable <T>(long id, bool orderby)
{
string where = DbComm.GetPrimaryKeywhere <T>(id);
return(ToTable <T>(0, 0, where, null, orderby));
}
public int Del <T>(long id)
{
string where = DbComm.GetPrimaryKeywhere <T>(id);
return(Del <T>(where));
}
public List <T> ToList <T>(long id, bool orderby)
{
string where = DbComm.GetPrimaryKeywhere <T>(id);
return(ToList <T>(0, 0, where, null, orderby));
}
public List <T> ToList <T>(long id)
{
string where = DbComm.GetPrimaryKeywhere <T>(id);
return(ToList <T>(0, 0, where, null, false));
}
public int GetCount <T>(long id)
{
string where = DbComm.GetPrimaryKeywhere <T>(id);
return(GetCount <T>(null, where));
}
public int GetCount <T>(string column, long id)
{
string where = DbComm.GetPrimaryKeywhere <T>(id);
return(GetCount <T>(column, where));
}