/// <summary>
/// 修改数据库表中的记录
/// </summary>
/// <param name="table"></param>
/// <returns>成功返回true,失败返回false</returns>
public bool Update(object table)
{
DbInfo info = getConnectionString(table.GetType());
DbConnection sqlConn = Activator.CreateInstance(info.sqlConn, info.connectionString) as DbConnection;
DbCommand cmd = sqlConn.CreateCommand();
LinkedList <Node>[] lists = this.getFieldsAndAttributes(table);
string sql = "update " + table.GetType().Name + " set ";
string setStr = "";
string whereStr = "";
foreach (Node node in lists[1])
{
setStr += "," + node.name + "=" + node.value;
}
foreach (Node node in lists[0])
{
whereStr += " and " + node.name + "=" + node.value;
}
sql += setStr.Substring(1) + " where " + whereStr.Substring(5);
cmd.CommandText = sql;
sqlConn.Open();
int n = cmd.ExecuteNonQuery();
sqlConn.Close();
return(n > 0);
}
/// <summary>
/// 将一条记录插入数据库的表中
/// </summary>
/// <param name="table">某条记录的对象,继承自Table,[PrimaryKey]标记表示该成员为主关键字</param>
/// <returns>插入成功返回true,失败返回false</returns>
public bool Insert(object table)
{
DbInfo info = getConnectionString(table.GetType());
DbConnection sqlConn = Activator.CreateInstance(info.sqlConn, info.connectionString) as DbConnection;
DbCommand cmd = sqlConn.CreateCommand();
LinkedList <Node>[] lists = this.getFieldsAndAttributes(table);
string sql = "insert into " + table.GetType().Name;
string name = "(";
string value = "(";
for (int i = 0; i < lists.Length; i++)
{
foreach (Node node in lists[i])
{
name += node.name + ",";
value += node.value + ",";
}
}
name = name.Remove(name.Length - 1) + ")";
value = value.Remove(value.Length - 1) + ")";
sql += name + " values" + value;
cmd.CommandText = sql;
sqlConn.Open();
int n = cmd.ExecuteNonQuery();
sqlConn.Close();
return(n > 0);
}
/// <summary>
/// 自定义select查询的where语句部分,其中select与from均可省略,如不省略,该方法也会自动忽略
/// </summary>
/// <typeparam name="T">继承自Table类的数据库表类型</typeparam>
/// <param name="where">自定义的where语句</param>
/// <returns>返回所有符合where语句限定条件的记录</returns>
public T[] SelectSome <T>(string where)
{
DbInfo info = getConnectionString(typeof(T));
DbConnection sqlConn = Activator.CreateInstance(info.sqlConn, info.connectionString) as DbConnection;
DbCommand cmd = sqlConn.CreateCommand();
if (where == null)
{
where = "";
}
if (where.Contains(where) && !where.Contains("'where'") && !where.Contains("\"where\""))
{
where = Regex.Replace(where, ".*where", "where");
}
List <T> table = new List <T>();
FieldInfo[] fields = typeof(T).GetFields();
PropertyInfo[] properties = typeof(T).GetProperties();
string sql = "select ";
string selectStr = "";
for (int i = 0; i < fields.Length; i++)
{
selectStr += "," + fields[i].Name;
}
for (int i = 0; i < properties.Length; i++)
{
selectStr += "," + properties[i].Name;
}
sql += selectStr.Substring(1) + " from " + typeof(T).Name + where;
cmd.CommandText = sql;
sqlConn.Open();
DbDataReader sdr = cmd.ExecuteReader();
fields = null;
properties = null;
while (sdr.Read())
{
T t = System.Activator.CreateInstance <T>();
FieldInfo[] tfields = t.GetType().GetFields();
PropertyInfo[] tproperties = t.GetType().GetProperties();
for (int i = 0; i < tfields.Length; i++)
{
tfields[i].SetValue(t, sdr[tfields[i].Name]);
}
for (int i = 0; i < tproperties.Length; i++)
{
tproperties[i].SetValue(t, sdr[tproperties[i].Name]);
}
table.Add(t);
}
sqlConn.Close();
return(table.ToArray());
}
/// <summary>
/// 查询数据库中的记录
/// </summary>
/// <param name="table">某条记录的对象,继承自Table,只要[PrimaryKey]有值即可,其余成员会自动填充完成</param>
public void Select(object table)
{
DbInfo info = getConnectionString(table.GetType());
DbConnection sqlConn = Activator.CreateInstance(info.sqlConn, info.connectionString) as DbConnection;
DbCommand cmd = sqlConn.CreateCommand();
FieldInfo[] fields;
PropertyInfo[] properties;
LinkedList <Node>[] lists = this.getFieldsAndAttributes(table, out fields, out properties);
string sql = "select ";
string selectStr = "";
string whereStr = "";
foreach (Node node in lists[0])
{
whereStr += " and " + node.value + "=" + node.name;
}
foreach (Node node in lists[1])
{
selectStr += "," + node.name;
}
whereStr = whereStr.Substring(5);
selectStr = selectStr.Substring(1);
sql += selectStr + " from " + table.GetType().Name + " where " + whereStr;
cmd.CommandText = sql;
sqlConn.Open();
DbDataReader sdr = cmd.ExecuteReader();
sdr.Read();
//int index = 0;
for (int i = 0; i < fields.Length; i++)
{
fields[i].SetValue(table, sdr[fields[i].Name]);
}
for (int i = 0; i < properties.Length; i++)
{
properties[i].SetValue(table, sdr[properties[i].Name]);
}
sqlConn.Close();
}
