/// <summary>
/// 创建表脚本
/// </summary>
/// <param name="fields"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public override void CreateTable(DBExtend helper, List <Attribute.FieldAttribute> fields, string tableName)
{
var defaultValues = new List <string>();
string script = string.Format("create table {0}(\r\n", tableName);
List <string> list2 = new List <string>();
foreach (Attribute.FieldAttribute item in fields)
{
string nullStr = item.NotNull ? "NOT NULL" : "";
string str = string.Format("{0} {1} {2} ", item.KeyWordName, item.ColumnType, nullStr);
if (item.IsPrimaryKey)
{
str = " " + item.Name + " int primary key auto_increment";
}
list2.Add(str);
}
script += string.Join(",\r\n", list2.ToArray());
script += ") ";
helper.Execute(script);
foreach (string s in defaultValues)
{
if (!string.IsNullOrEmpty(s))
{
helper.Execute(s);
}
}
}
/// <summary>
/// 确认流水,并更改库存
/// </summary>
/// <param name="batchNo"></param>
/// <param name="operateType"></param>
public bool ConfirmSubmit <TStock>(string batchNo, StockOperateType operateType) where TStock : Style, new()
{
DBExtend helper = dbHelper;
helper.BeginTran();
string op = operateType == StockOperateType.出 ? "-" : "+";
string sql = "update $Style set Num=$Style.num" + op + "b.num from $IStockRecord b where $Style.id=b.styleId and b.Handled=0 and b.batchNo=@batchNo";
sql += @"
update $IStockRecord set Handled=1,OperateType=@OperateType,UpdateTime=getdate(),$IStockRecord.num=0" + op + "$IStockRecord.num where batchNo=@batchNo";
//sql = AutoFormat(sql, typeof(TStock), typeof(TRecord));
helper.AddParam("batchNo", batchNo);
helper.AddParam("OperateType", (int)operateType);
try
{
helper.Execute(sql, typeof(TStock), typeof(TModel));
}
catch (Exception ero)
{
helper.RollbackTran();
return(false);
}
helper.CommitTran();
return(true);
}
/// <summary>
/// 创建表脚本
/// </summary>
/// <param name="fields"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public override void CreateTable(DBExtend helper, List <Attribute.FieldAttribute> fields, string tableName)
{
var defaultValues = new List <string>();
string script = string.Format("create table [{0}] (\r\n", tableName);
List <string> list2 = new List <string>();
string primaryKey = "id";
foreach (Attribute.FieldAttribute item in fields)
{
if (item.IsPrimaryKey)
{
primaryKey = item.Name;
}
string nullStr = item.NotNull ? "NOT NULL" : "";
string str = string.Format("{0} {1} {2} ", item.KeyWordName, item.ColumnType, nullStr);
list2.Add(str);
//生成默认值语句
if (!string.IsNullOrEmpty(item.DefaultValue))
{
string v = string.Format("ALTER TABLE [dbo].[{0}] ADD CONSTRAINT [DF_{0}_{1}] DEFAULT ({2}) FOR [{1}]", tableName, item.Name, item.DefaultValue);
defaultValues.Add(v);
}
}
script += string.Join(",\r\n", list2.ToArray());
script += string.Format(@" CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED
(
[{1}] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
", tableName, primaryKey);
script += ") ON [PRIMARY]";
//var list3 = GetIndexScript();
//defaultValues.AddRange(list3);
helper.Execute(script);
foreach (string s in defaultValues)
{
if (!string.IsNullOrEmpty(s))
{
helper.Execute(s);
}
}
}
/// <summary>
/// 锁定一定金额
/// </summary>
public bool LockAmount(ILockRecord record, out int id, out string message)
{
message = "";
if (record.Amount <= 0)
{
id = 0;
message = "amount格式不正确";
return(false);
}
string key = string.Format("LockAmount_{0}_{1}_{2}_{3}", record.AccountId, 0, record.Remark, 0);
if (!CoreHelper.ConcurrentControl.Check(key, 3))
{
throw new Exception("同时提交了多次相同的参数" + key);
}
DBExtend helper = dbHelper;
string sql = "update $IAccountDetail set LockedAmount=LockedAmount+@LockedAmount where id=@AccountId and CurrentBalance-(abs(LockedAmount)+@LockedAmount)>=0";
//sql = FormatTable(sql);
helper.AddParam("LockedAmount", Math.Abs(record.Amount));
helper.AddParam("AccountId", record.AccountId);
helper.BeginTran();
try
{
int n = helper.Execute(sql, typeof(IAccountDetail));
if (n == 0)
{
message = "余额不足";
id = 0;
helper.RollbackTran();
return(false);
}
//helper.Clear();
id = helper.InsertFromObj(record);
helper.CommitTran();
}
catch (Exception ero)
{
message = "提交事物时发生错误:" + ero.Message;
helper.RollbackTran();
CoreHelper.ConcurrentControl.Remove(key);
CoreHelper.EventLog.Log("LockAmount 执行出错" + ero, true);
throw ero;
}
bool ok = id > 0;
if (!ok)
{
CoreHelper.ConcurrentControl.Remove(key);
}
return(ok);
}
/// <summary>
/// 解锁金额,没有事务
/// </summary>
public static bool UnlockAmount(DBExtend helper, int lockedId, out string message)
{
//helper.Clear();
message = "";
if (lockedId <= 0)
{
message = "参数值lockedId不能为0";
return(false);
}
string key = string.Format("UnlockAmount_{0}", lockedId);
if (!CoreHelper.ConcurrentControl.Check(key))
{
message = "同时提交了多次相同的参数" + key;
return(false);
}
string sql = "update $IAccountDetail set LockedAmount=LockedAmount-b.Amount from $ILockRecord b where $IAccountDetail.id=b.AccountId and b.id=@id\r\n";
sql += "delete from $ILockRecord where id=@id";
//sql = FormatTable(sql);
helper.AddParam("id", lockedId);
int count;
try
{
count = helper.Execute(sql, typeof(IAccountDetail), typeof(ILockRecord));
if (count == 0)
{
message = "找不到锁定的记录";
}
}
catch (Exception ero)
{
CoreHelper.ConcurrentControl.Remove(key);
CoreHelper.EventLog.Log("UnlockAmount 执行出错" + ero, true);
message = ero.Message;
return(false);
}
bool ok = count > 0;
if (!ok)
{
CoreHelper.ConcurrentControl.Remove(key);
}
return(ok);
}
/// <summary>
/// 解锁金额,没有事务
/// </summary>
public bool UnlockAmount(DBExtend helper, int lockedId, out string message)
{
//helper.Clear();
var lockRecord = helper.QueryItem <ILockRecord>(b => b.Id == lockedId);
message = "";
if (lockRecord == null)
{
message = "找不到锁ID:" + lockedId;
return(false);
}
if (lockRecord.Checked)
{
message = "该锁已经解过ID:" + lockedId;
return(false);
}
string key = string.Format("UnlockAmount_{0}", lockedId);
if (!CoreHelper.ConcurrentControl.Check(key))
{
message = "同时提交了多次相同的参数" + key;
return(false);
}
string sql = "update $IAccountDetail set LockedAmount=LockedAmount-b.Amount from $ILockRecord b where $IAccountDetail.id=b.AccountId and b.id=@id ";
//sql += "update $ILockRecord set checked=1 where id=@id";
sql += "delete from $ILockRecord where id=@id";
//sql = FormatTable(sql);
helper.AddParam("id", lockedId);
int count;
try
{
count = helper.Execute(sql, typeof(IAccountDetail), typeof(ILockRecord));
}
catch (Exception ero)
{
CoreHelper.ConcurrentControl.Remove(key);
CoreHelper.EventLog.Log("UnlockAmount 执行出错" + ero, true);
message = ero.Message;
return(false);
}
CoreHelper.ConcurrentControl.Remove(key);
return(true);
}
public void Test()
{
string sql = "update ProductData set ProductName1='fff'";
DBExtend.Execute(sql);
}
/// <summary>
/// 创建表
/// 会检查表是否存在,如果存在则检查字段
/// </summary>
/// <param name="helper"></param>
/// <param name="message"></param>
/// <returns></returns>
public bool CreateTable(DBExtend helper, out string message)
{
var dbAdapter = helper._DBAdapter;
message = "";
//TypeCache.SetDBAdapterCache(GetType(),dbAdapter);
string tableName = TypeCache.GetTableName(GetType(),helper.dbContext);
string sql = dbAdapter.GetSelectTop("0", "from " + tableName, "", 1);
bool needCreate = false;
try
{
//检查表是否存在
helper.Execute(sql);
}
catch
{
needCreate = true;
}
if (needCreate)
{
List<string> list = new List<string>();
try
{
List<Attribute.FieldAttribute> columns = GetColumns(dbAdapter);
dbAdapter.CreateTable(columns, tableName);
message = string.Format("创建表:{0}\r\n", tableName);
CheckIndexExists(helper);
return true;
}
catch (Exception ero)
{
message = "创建表时发生错误 类型{0} {1}\r\n";
message = string.Format(message, GetType(), ero.Message);
throw new Exception(message);
return false;
}
CoreHelper.EventLog.Log(message, "", false);
}
else
{
message = CheckColumnExists(helper);
}
return true;
}
/// <summary>
/// 检查对应的字段是否存在,不存在则创建
/// </summary>
/// <param name="helper"></param>
public string CheckColumnExists(DBExtend helper)
{
string result = "";
var dbAdapter = helper._DBAdapter;
List<Attribute.FieldAttribute> columns = GetColumns(dbAdapter);
string tableName = TypeCache.GetTableName(this.GetType(),helper.dbContext);
foreach (Attribute.FieldAttribute item in columns)
{
string sql = dbAdapter.GetSelectTop(item.KeyWordName, "from " + tableName, "", 1);
try
{
helper.Execute(sql);
}
catch//出错,按没有字段算
{
result += CreateColumn(helper, item);
}
}
return result;
}
internal static string CreateColumn(DBExtend helper, Attribute.FieldAttribute item)
{
var dbAdapter = helper._DBAdapter;
string result = "";
if (string.IsNullOrEmpty(item.ColumnType))
{
throw new Exception("ColumnType is null");
}
string str = dbAdapter.GetCreateColumnScript(item);
string indexScript = "";
if (item.FieldIndexType != Attribute.FieldIndexType.无)
{
indexScript = dbAdapter.GetColumnIndexScript(item);
}
try
{
helper.Execute(str);
if (!string.IsNullOrEmpty(indexScript))
{
helper.Execute(indexScript);
}
result = string.Format("创建字段:{0} {1} {2}\r\n", item.TableName, item.Name,item.PropertyType);
CoreHelper.EventLog.Log(result, "", false);
}
catch (Exception ero)
{
//CoreHelper.EventLog.Log("创建字段时发生错误:" + ero.Message);
result = string.Format("创建字段:{0} {1}发生错误:{2}\r\n", item.TableName, item.Name, ero.Message);
}
return result;
}
/// <summary>
/// 检查索引
/// </summary>
/// <param name="helper"></param>
/// <returns></returns>
public void CheckIndexExists(DBExtend helper)
{
var list = GetIndexScript(helper);
foreach (var item in list)
{
try
{
helper.Execute(item);
}
catch (Exception ero)//出错,
{
CoreHelper.EventLog.Log(string.Format("创建索引失败:{0}\r\n{1}", ero.Message, item));
}
}
}
/// <summary>
/// 创建表
/// </summary>
/// <param name="helper"></param>
/// <param name="fields"></param>
/// <param name="tableName"></param>
public override void CreateTable(DBExtend helper, List <Attribute.FieldAttribute> fields, string tableName)
{
var lines = new List <string>();
//tableName = tableName.ToUpper();
string script = string.Format("create table {0}(\r\n", tableName);
List <string> list2 = new List <string>();
string primaryKey = "id";
foreach (Attribute.FieldAttribute item in fields)
{
if (item.IsPrimaryKey)
{
primaryKey = item.Name;
}
string nullStr = item.NotNull ? "NOT NULL" : "";
string str = string.Format("{0} {1} {2} ", item.KeyWordName, item.ColumnType, nullStr);
if (item.IsPrimaryKey)
{
str = " " + item.Name + " INTEGER Not Null Primary Key";
}
list2.Add(str);
}
script += string.Join(",\r\n", list2.ToArray());
script += ")";
string sequenceName = string.Format("{0}_sequence", tableName);
string triggerName = string.Format("{0}_trigge", tableName);
string sequenceScript = string.Format("Create Sequence {0} MINVALUE 1 MAXVALUE 99999 INCREMENT BY 1 START WITH 1 NOCACHE CYCLE", sequenceName);
string triggerScript = string.Format(@"
create or replace trigger {0}
before insert on {1}
for each row
declare
nextid number;
begin
IF :new.{3} IS NULL or :new.{3}=0 THEN
select {2}.nextval
into nextid
from sys.dual;
:new.{3}:=nextid;
end if;
end ;", triggerName, tableName, sequenceName, primaryKey);
lines.Add(sequenceScript);
//defaultValues.Add(triggerScript); 暂不用触发器,不能编译成功
//script += script2;
helper.SetParam("script", script);
helper.Run("sp_ExecuteScript");
//helper.SetParam("script", sequenceScript);
//helper.Run("sp_ExecuteScript");
//helper.SetParam("script", triggerScript);
//helper.Run("sp_ExecuteScript");
foreach (string s in lines)
{
try
{
helper.Execute(s);
}
catch (Exception ero) { };
}
}
