/// <summary> /// 自动生成列的值。 /// </summary> /// <param name="database">提供给当前插件的 <see cref="IDatabase"/> 对象。</param> /// <param name="tableName">表的名称。</param> /// <param name="columnName">列的名称。</param> /// <returns>用于标识唯一性的值。</returns> public int GenerateValue(IDatabase database, string tableName, string columnName = null) { tableName = tableName.ToUpper(); columnName = columnName.ToUpper(); var value = 0; var sequenceName = FixSequenceName(tableName, columnName); SqlCommand sql; if (GeneratorCache.IsSequenceCreated(tableName, columnName, () => { //查找是否存在序列 sql = string.Format("SELECT 1 FROM USER_SEQUENCES WHERE SEQUENCE_NAME = '{0}'", sequenceName); var result = database.ExecuteScalar(sql); //不存在的话先创建序列 if (result == DBNull.Value || result == null) { //取表中该列的最大值 + 1 sql = string.Format("SELECT MAX({1}) FROM {0}", tableName, columnName); value = database.ExecuteScalar <int>(sql) + 1; sql = string.Format("CREATE SEQUENCE {0} START WITH {1}", sequenceName, value); try { database.ExecuteNonQuery(sql); } catch (Exception exp) { throw new Exception(SR.GetString(SRKind.UnableCreateSequence, sequenceName), exp); } } return(true); })) { //查询下一个值 sql = string.Format("SELECT {0}.NEXTVAL FROM DUAL", sequenceName); value = database.ExecuteScalar <int>(sql); } return(value); }
/// <summary> /// 自动生成列的值。 /// </summary> /// <param name="database">提供给当前插件的 <see cref="IDatabase"/> 对象。</param> /// <param name="tableName">表的名称。</param> /// <param name="columnName">列的名称。</param> /// <returns>用于标识唯一性的值。</returns> public int GenerateValue(IDatabase database, string tableName, string columnName = null) { //查询下一个值 var value = 0; var syntax = database.Provider.GetService <ISyntaxProvider>(); database.WithTransaction(db => { if (GeneratorCache.IsSequenceCreated(tableName, columnName, () => { if (db.ExecuteScalar <int>((SqlCommand)syntax.ExistsTable("SYS_SEQUENCE")) == 0) { db.ExecuteNonQuery((SqlCommand)string.Format("CREATE TABLE SYS_SEQUENCE(TABLE_NAME {0}, COLUMN_NAME {0}, VALUE {1})", syntax.Column(DbType.String, 100), syntax.Column(DbType.Int32))); db.ExecuteNonQuery((SqlCommand)string.Format("INSERT INTO SYS_SEQUENCE(TABLE_NAME, COLUMN_NAME, VALUE) VALUES('{0}', '{1}', {2})", tableName, columnName, ++value)); } return(true); })) { var o = db.ExecuteScalar((SqlCommand)string.Format("SELECT VALUE FROM SYS_SEQUENCE WHERE TABLE_NAME = '{0}' AND COLUMN_NAME = '{1}'", tableName, columnName)); if (o == DBNull.Value || o == null) { db.ExecuteNonQuery((SqlCommand)string.Format("INSERT INTO SYS_SEQUENCE(TABLE_NAME, COLUMN_NAME, VALUE) VALUES('{0}', '{1}', {2})", tableName, columnName, ++value)); } else { value = o.To <int>() + 1; db.ExecuteNonQuery((SqlCommand)string.Format("UPDATE SYS_SEQUENCE SET VALUE = {0} WHERE TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", value, tableName, columnName)); } } }); return(value); }