public static TableEntity CreateTableEntity(Type t) { TableAttribute table = t.GetCustomAttributes(false).FirstOrDefault(f => f is TableAttribute) as TableAttribute; if (table == null) { throw new Exception("Class " + t.Name + " is not labeled [TableAttribute], please label it first"); } else { TableEntity model = new TableEntity(); model.TableName = table.TableName; model.KeyName = table.KeyName; model.IsIdentity = table.IsIdentity; model.AllFieldList = new List <string>(); model.ExceptKeyFieldList = new List <string>(); var allproperties = t.GetProperties(); foreach (var item in allproperties) { var igore = item.GetCustomAttributes(false).FirstOrDefault(f => f is IgoreAttribute) as IgoreAttribute; if (igore == null) { string Name = item.Name; var column = item.GetCustomAttributes(false).FirstOrDefault(f => f is ColumnAttribute) as ColumnAttribute; if (column != null) { Name = column.Name; } model.AllFieldList.Add(Name); //所有列 if (Name.ToLower().Equals(model.KeyName.ToLower())) { model.KeyType = item.PropertyType; } else { model.ExceptKeyFieldList.Add(Name); //去除主键后的所有列 } } } return(model); } }
public static void InitTableForOracle(TableEntity table) //初始化增删改等语句 { string Fields = GetFieldsStr(table.AllFieldList, "\"", "\""); string FieldsAt = GetFieldsAtStr(table.AllFieldList, ":"); string FieldsEq = GetFieldsEqStr(table.AllFieldList, "\"", "\"", ":"); string FieldsExtKey = GetFieldsStr(table.ExceptKeyFieldList, "\"", "\""); string FieldsAtExtKey = GetFieldsAtStr(table.ExceptKeyFieldList, ":"); string FieldsEqExtKey = GetFieldsEqStr(table.ExceptKeyFieldList, "\"", "\"", ":"); table.AllFields = Fields; table.AllFieldsAt = FieldsAt; table.AllFieldsAtEq = FieldsEq; table.AllFieldsExceptKey = FieldsExtKey; table.AllFieldsAtExceptKey = FieldsAtExtKey; table.AllFieldsAtEqExceptKey = FieldsEqExtKey; table.InsertSql = string.Format("INSERT INTO {0}({1})VALUES({2})", table.TableName, Fields, FieldsAt); if (!string.IsNullOrEmpty(table.SequenceName)) { table.InsertSql = string.Format("INSERT INTO {0}({1})VALUES({2}.NEXTVAL, {3})", table.TableName, Fields, table.SequenceName, FieldsAt); } if (!string.IsNullOrEmpty(table.KeyName)) //有主键 { table.InsertReturnIdSql = string.Format("INSERT INTO {0}({1})VALUES(```seq```.NEXTVAL, {2})", table.TableName, Fields, FieldsAtExtKey); if (table.IsIdentity) //是自增 { table.InsertSql = string.Format("INSERT INTO {0}({1})VALUES({2})", table.TableName, FieldsExtKey, FieldsAtExtKey); } table.InsertIdentitySql = string.Format("INSERT INTO {0}({1})VALUES({2})", table.TableName, Fields, FieldsAt); table.DeleteByIdSql = string.Format("DELETE FROM {0} WHERE {1}=:id", table.TableName, table.KeyName); table.DeleteByIdsSql = string.Format("DELETE FROM {0} WHERE {1} IN :ids", table.TableName, table.KeyName); table.GetByIdSql = string.Format("SELECT {0} FROM {1} WHERE {2}=:id", Fields, table.TableName, table.KeyName); table.GetByIdsSql = string.Format("SELECT {0} FROM {1} WHERE {2} IN :ids", Fields, table.TableName, table.KeyName); table.UpdateSql = string.Format("UPDATE {0} SET {1} WHERE {2}=:{2}", table.TableName, FieldsEqExtKey, table.KeyName); } table.DeleteAllSql = string.Format("DELETE FROM {0} ", table.TableName); table.GetAllSql = string.Format("SELECT {0} FROM {1} ", Fields, table.TableName); }
public static TableEntity GetTableEntity <T>() { Type t = typeof(T); RuntimeTypeHandle typeHandle = t.TypeHandle; if (!tableDict.Keys.Contains(typeHandle)) { lock (_locker) { if (!tableDict.Keys.Contains(typeHandle)) { TableEntity table = CommonUtil.CreateTableEntity(t); CommonUtil.InitTableForOracle(table); tableDict[typeHandle] = table; } } } return(tableDict[typeHandle]); }
private static void InitPage(StringBuilder sb, TableEntity table, int skip, int take, string where, string returnFields, string orderBy) { if (string.IsNullOrEmpty(returnFields)) { returnFields = table.AllFields; } if (string.IsNullOrEmpty(orderBy)) { if (!string.IsNullOrEmpty(table.KeyName)) { orderBy = string.Format("ORDER BY \"{0}\"", table.KeyName); } else { orderBy = string.Format("ORDER BY \"{0}\"", table.AllFieldList.First()); } } sb.AppendFormat("SELECT * FROM(SELECT A.*,ROWNUM RN FROM (SELECT {0} FROM \"{1}\" {2} {3}) A WHERE ROWNUM <= {4}) WHERE RN > {5}", returnFields, table.TableName, where, orderBy, skip + take, skip); }
private static void InitPage(StringBuilder sb, TableEntity table, int skip, int take, string where, string returnFields, string orderBy) { if (string.IsNullOrEmpty(returnFields)) { returnFields = table.AllFields; } if (string.IsNullOrEmpty(orderBy)) { if (!string.IsNullOrEmpty(table.KeyName)) { orderBy = string.Format("ORDER BY [{0}]", table.KeyName); } else { orderBy = string.Format("ORDER BY [{0}]", table.AllFieldList.First()); } } sb.AppendFormat("SELECT {0} FROM [{1}] {2} {3} LIMIT {4},{5}", returnFields, table.TableName, where, orderBy, skip, take); }
public static void InitTableForPostgresql(TableEntity table) //初始化增删改等语句postgresql专用 { string Fields = GetFieldsStr(table.AllFieldList, "\"", "\""); string FieldsAt = GetFieldsAtStr(table.AllFieldList); string FieldsEq = GetFieldsEqStr(table.AllFieldList, "\"", "\""); string FieldsExtKey = GetFieldsStr(table.ExceptKeyFieldList, "\"", "\""); string FieldsAtExtKey = GetFieldsAtStr(table.ExceptKeyFieldList); string FieldsEqExtKey = GetFieldsEqStr(table.ExceptKeyFieldList, "\"", "\""); table.AllFields = Fields; table.AllFieldsAt = FieldsAt; table.AllFieldsAtEq = FieldsEq; table.AllFieldsExceptKey = FieldsExtKey; table.AllFieldsAtExceptKey = FieldsAtExtKey; table.AllFieldsAtEqExceptKey = FieldsEqExtKey; table.InsertSql = string.Format("INSERT INTO \"{0}\"({1})VALUES({2})", table.TableName, Fields, FieldsAt); if (!string.IsNullOrEmpty(table.KeyName)) //有主键 { if (table.IsIdentity) //是自增 { table.InsertSql = string.Format("INSERT INTO \"{0}\"({1})VALUES({2})", table.TableName, FieldsExtKey, FieldsAtExtKey); table.InsertReturnIdSql = string.Format("INSERT INTO \"{0}\"({1})VALUES({2})RETURNING \"{3}\"", table.TableName, FieldsExtKey, FieldsAtExtKey, table.KeyName); } table.InsertIdentitySql = string.Format("INSERT INTO \"{0}\"({1})VALUES({2})", table.TableName, Fields, FieldsAt); table.DeleteByIdSql = string.Format("DELETE FROM \"{0}\" WHERE \"{1}\"=@id", table.TableName, table.KeyName); table.DeleteByIdsSql = string.Format("DELETE FROM \"{0}\" WHERE \"{1}\"=ANY(@ids)", table.TableName, table.KeyName); table.GetByIdSql = string.Format("SELECT {0} FROM \"{1}\" WHERE \"{2}\"=@id", Fields, table.TableName, table.KeyName); table.GetByIdsSql = string.Format("SELECT {0} FROM \"{1}\" WHERE \"{2}\"=ANY(@ids)", Fields, table.TableName, table.KeyName); table.UpdateSql = string.Format("UPDATE \"{0}\" SET {1} WHERE \"{2}\"=@{2}", table.TableName, FieldsEqExtKey, table.KeyName); } table.DeleteAllSql = string.Format("DELETE FROM \"{0}\" ", table.TableName); table.GetAllSql = string.Format("SELECT {0} FROM \"{1}\" ", Fields, table.TableName); }
public static void InitTableForSqlite(TableEntity table) //初始化增删改等语句sqlite专用 { string Fields = GetFieldsStr(table.AllFieldList, "[", "]"); string FieldsAt = GetFieldsAtStr(table.AllFieldList); string FieldsEq = GetFieldsEqStr(table.AllFieldList, "[", "]"); string FieldsExtKey = GetFieldsStr(table.ExceptKeyFieldList, "[", "]"); string FieldsAtExtKey = GetFieldsAtStr(table.ExceptKeyFieldList); string FieldsEqExtKey = GetFieldsEqStr(table.ExceptKeyFieldList, "[", "]"); table.AllFields = Fields; table.AllFieldsAt = FieldsAt; table.AllFieldsAtEq = FieldsEq; table.AllFieldsExceptKey = FieldsExtKey; table.AllFieldsAtExceptKey = FieldsAtExtKey; table.AllFieldsAtEqExceptKey = FieldsEqExtKey; table.InsertSql = string.Format("INSERT INTO [{0}]({1})VALUES({2})", table.TableName, Fields, FieldsAt); if (!string.IsNullOrEmpty(table.KeyName)) //有主键 { if (table.IsIdentity) //是自增 { table.InsertSql = string.Format("INSERT INTO [{0}]({1})VALUES({2})", table.TableName, FieldsExtKey, FieldsAtExtKey); table.InsertReturnIdSql = string.Format("INSERT INTO [{0}]({1})VALUES({2});SELECT last_insert_rowid();", table.TableName, FieldsExtKey, FieldsAtExtKey); } table.InsertIdentitySql = string.Format("INSERT INTO [{0}]({1})VALUES({2})", table.TableName, Fields, FieldsAt); table.DeleteByIdSql = string.Format("DELETE FROM [{0}] WHERE [{1}]=@id", table.TableName, table.KeyName); table.DeleteByIdsSql = string.Format("DELETE FROM [{0}] WHERE [{1}] IN @ids", table.TableName, table.KeyName); table.GetByIdSql = string.Format("SELECT {0} FROM [{1}] WHERE [{2}]=@id", Fields, table.TableName, table.KeyName); table.GetByIdsSql = string.Format("SELECT {0} FROM [{1}] WHERE [{2}] IN @ids", Fields, table.TableName, table.KeyName); table.UpdateSql = string.Format("UPDATE [{0}] SET {1} WHERE [{2}]=@{2}", table.TableName, FieldsEqExtKey, table.KeyName); } table.DeleteAllSql = string.Format("DELETE FROM [{0}] ", table.TableName); table.GetAllSql = string.Format("SELECT {0} FROM [{1}] ", Fields, table.TableName); }
public static void InitTableForMySql(TableEntity table) //初始化增删改等语句mysql专用 { string Fields = GetFieldsStr(table.AllFieldList, "`", "`"); string FieldsAt = GetFieldsAtStr(table.AllFieldList); string FieldsEq = GetFieldsEqStr(table.AllFieldList, "`", "`"); string FieldsExtKey = GetFieldsStr(table.ExceptKeyFieldList, "`", "`"); string FieldsAtExtKey = GetFieldsAtStr(table.ExceptKeyFieldList); string FieldsEqExtKey = GetFieldsEqStr(table.ExceptKeyFieldList, "`", "`"); table.AllFields = Fields; table.AllFieldsAt = FieldsAt; table.AllFieldsAtEq = FieldsEq; table.AllFieldsExceptKey = FieldsExtKey; table.AllFieldsAtExceptKey = FieldsAtExtKey; table.AllFieldsAtEqExceptKey = FieldsEqExtKey; table.InsertSql = string.Format("INSERT INTO `{0}` ({1})VALUES({2})", table.TableName, Fields, FieldsAt); if (!string.IsNullOrEmpty(table.KeyName)) //有主键 { if (table.IsIdentity) //是自增 { table.InsertSql = string.Format("INSERT INTO `{0}`({1})VALUES({2})", table.TableName, FieldsExtKey, FieldsAtExtKey); table.InsertReturnIdSql = string.Format("INSERT INTO `{0}`({1})VALUES({2});SELECT @@IDENTITY;", table.TableName, FieldsExtKey, FieldsAtExtKey); } table.InsertIdentitySql = string.Format("INSERT INTO `{0}`({1})VALUES({2})", table.TableName, Fields, FieldsAt); table.DeleteByIdSql = string.Format("DELETE FROM `{0}` WHERE `{1}`=@id", table.TableName, table.KeyName); table.DeleteByIdsSql = string.Format("DELETE FROM `{0}` WHERE `{1}` IN @ids", table.TableName, table.KeyName); table.GetByIdSql = string.Format("SELECT {0} FROM `{1}` WHERE `{2}`=@id", Fields, table.TableName, table.KeyName); table.GetByIdsSql = string.Format("SELECT {0} FROM `{1}` WHERE `{2}` IN @ids", Fields, table.TableName, table.KeyName); table.UpdateSql = string.Format("UPDATE `{0}` SET {1} WHERE `{2}`=@{2}", table.TableName, FieldsEqExtKey, table.KeyName); } table.DeleteAllSql = string.Format("DELETE FROM `{0}` ", table.TableName); table.GetAllSql = string.Format("SELECT {0} FROM `{1}` ", Fields, table.TableName); }
public static void InitTable(TableEntity table, string leftChar, string rightChar) //初始化增删改等语句 { string Fields = GetFieldsStr(table.AllFieldList, leftChar, rightChar); string FieldsAt = GetFieldsAtStr(table.AllFieldList); string FieldsEq = GetFieldsEqStr(table.AllFieldList, leftChar, rightChar); string FieldsExtKey = GetFieldsStr(table.ExceptKeyFieldList, leftChar, rightChar); string FieldsAtExtKey = GetFieldsAtStr(table.ExceptKeyFieldList); string FieldsEqExtKey = GetFieldsEqStr(table.ExceptKeyFieldList, leftChar, rightChar); table.AllFields = Fields; table.AllFieldsAt = FieldsAt; table.AllFieldsAtEq = FieldsEq; table.AllFieldsExceptKey = FieldsExtKey; table.AllFieldsAtExceptKey = FieldsAtExtKey; table.AllFieldsAtEqExceptKey = FieldsEqExtKey; table.InsertSql = string.Format("INSERT INTO {3}{0}{4}({1})VALUES({2})", table.TableName, Fields, FieldsAt, leftChar, rightChar); if (!string.IsNullOrEmpty(table.KeyName)) //有主键 { if (table.IsIdentity) //是自增 { table.InsertSql = string.Format("INSERT INTO {3}{0}{4}({1})VALUES({2})", table.TableName, FieldsExtKey, FieldsAtExtKey, leftChar, rightChar); } table.InsertIdentitySql = string.Format("INSERT INTO {3}{0}{4}({1})VALUES({2})", table.TableName, Fields, FieldsAt, leftChar, rightChar); table.DeleteByIdSql = string.Format("DELETE FROM {2}{0}{3} WHERE {2}{1}{3}=@id", table.TableName, table.KeyName, leftChar, rightChar); table.DeleteByIdsSql = string.Format("DELETE FROM {2}{0}{3} WHERE {2}{1}{3} IN @ids", table.TableName, table.KeyName, leftChar, rightChar); table.GetByIdSql = string.Format("SELECT {0} FROM {3}{1}{4} WHERE {3}{2}{4}=@id", Fields, table.TableName, table.KeyName, leftChar, rightChar); table.GetByIdsSql = string.Format("SELECT {0} FROM {3}{1}{4} WHERE {3}{2}{4} IN @ids", Fields, table.TableName, table.KeyName, leftChar, rightChar); table.UpdateSql = string.Format("UPDATE {3}{0}{4} SET {1} WHERE {3}{2}{4}=@{2}", table.TableName, FieldsEqExtKey, table.KeyName, leftChar, rightChar); } table.DeleteAllSql = string.Format("DELETE FROM {1}{0}{2} ", table.TableName, leftChar, rightChar); table.GetAllSql = string.Format("SELECT {0} FROM {2}{1}{3} ", Fields, table.TableName, leftChar, rightChar); }
public static void InitTableForSqlServer(TableEntity table) //初始化增删改等语句sqlserver专用 { string Fields = CommonUtil.GetFieldsStr(table.AllFieldList, "[", "]"); string FieldsAt = CommonUtil.GetFieldsAtStr(table.AllFieldList); string FieldsEq = CommonUtil.GetFieldsEqStr(table.AllFieldList, "[", "]"); string FieldsExtKey = CommonUtil.GetFieldsStr(table.ExceptKeyFieldList, "[", "]"); string FieldsAtExtKey = CommonUtil.GetFieldsAtStr(table.ExceptKeyFieldList); string FieldsEqExtKey = CommonUtil.GetFieldsEqStr(table.ExceptKeyFieldList, "[", "]"); table.AllFields = Fields; table.AllFieldsAt = FieldsAt; table.AllFieldsAtEq = FieldsEq; table.AllFieldsExceptKey = FieldsExtKey; table.AllFieldsAtExceptKey = FieldsAtExtKey; table.AllFieldsAtEqExceptKey = FieldsEqExtKey; table.InsertSql = string.Format("INSERT INTO [{0}]({1})VALUES({2})", table.TableName, Fields, FieldsAt); if (!string.IsNullOrEmpty(table.KeyName)) //有主键(table has primary key) { if (table.IsIdentity) //是自增 (and the parmary is identity) { table.InsertSql = string.Format("INSERT INTO [{0}]({1})VALUES({2})", table.TableName, FieldsExtKey, FieldsAtExtKey); table.InsertReturnIdSql = string.Format("INSERT INTO [{0}]({1})VALUES({2});SELECT @@IDENTITY;", table.TableName, FieldsExtKey, FieldsAtExtKey); } table.InsertIdentitySql = string.Format("SET IDENTITY_INSERT [{0}] ON;INSERT INTO [{0}]({1})VALUES({2});SET IDENTITY_INSERT [{0}] OFF", table.TableName, Fields, FieldsAt); table.DeleteByIdSql = string.Format("DELETE FROM [{0}] WHERE [{1}]=@id", table.TableName, table.KeyName); table.DeleteByIdsSql = string.Format("DELETE FROM [{0}] WHERE [{1}] IN @ids", table.TableName, table.KeyName); table.GetByIdSql = string.Format("SELECT {0} FROM [{1}] WITH(NOLOCK) WHERE [{2}]=@id", Fields, table.TableName, table.KeyName); table.GetByIdsSql = string.Format("SELECT {0} FROM [{1}] WITH(NOLOCK) WHERE [{2}] IN @ids", Fields, table.TableName, table.KeyName); table.UpdateSql = string.Format("UPDATE [{0}] SET {1} WHERE [{2}]=@{2}", table.TableName, FieldsEqExtKey, table.KeyName); } table.DeleteAllSql = string.Format("DELETE FROM [{0}] ", table.TableName); table.GetAllSql = string.Format("SELECT {0} FROM [{1}] WITH(NOLOCK) ", Fields, table.TableName); }
public static string CreateUpdateSql(TableEntity table, string updateFields, string leftChar, string rightChar, string symbol = "@") //oracle @换成 : { string updateList = GetFieldsEqStr(updateFields.Split(',').ToList(), leftChar, rightChar, symbol); return(string.Format("UPDATE {0}{1}{2} SET {3} WHERE {0}{4}{2}={5}{4}", leftChar, table.TableName, rightChar, updateList, table.KeyName, symbol)); }