public int Insert(StockInfo oParam)
{
string sql = @"INSERT INTO Stock
(
SysNo, StockID, StockName, Address,
Contact, Phone, Status, StockType
)
VALUES (
@SysNo, @StockID, @StockName, @Address,
@Contact, @Phone, @Status, @StockType
)";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
SqlParameter paramStockID = new SqlParameter("@StockID", SqlDbType.NVarChar,20);
SqlParameter paramStockName = new SqlParameter("@StockName", SqlDbType.NVarChar,50);
SqlParameter paramAddress = new SqlParameter("@Address", SqlDbType.NVarChar,100);
SqlParameter paramContact = new SqlParameter("@Contact", SqlDbType.NVarChar,20);
SqlParameter paramPhone = new SqlParameter("@Phone", SqlDbType.NVarChar,50);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);
SqlParameter paramStockType = new SqlParameter("@StockType",SqlDbType.Int,4);
paramSysNo.Value = oParam.SysNo;
paramStockID.Value = oParam.StockID;
paramStockName.Value = oParam.StockName;
paramAddress.Value = oParam.Address;
paramContact.Value = oParam.Contact;
paramPhone.Value = oParam.Phone;
paramStatus.Value = oParam.Status;
paramStockType.Value = oParam.StockType;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramStockID);
cmd.Parameters.Add(paramStockName);
cmd.Parameters.Add(paramAddress);
cmd.Parameters.Add(paramContact);
cmd.Parameters.Add(paramPhone);
cmd.Parameters.Add(paramStatus);
cmd.Parameters.Add(paramStockType);
return SqlHelper.ExecuteNonQuery(cmd);
}
//StockType ²»×öµ÷Õû
public int Update(StockInfo oParam)
{
string sql = @"UPDATE Stock SET
StockID=@StockID,
StockName=@StockName, Address=@Address,
Contact=@Contact, Phone=@Phone,
Status=@Status
WHERE SysNo=@SysNo";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
SqlParameter paramStockID = new SqlParameter("@StockID", SqlDbType.NVarChar,20);
SqlParameter paramStockName = new SqlParameter("@StockName", SqlDbType.NVarChar,50);
SqlParameter paramAddress = new SqlParameter("@Address", SqlDbType.NVarChar,100);
SqlParameter paramContact = new SqlParameter("@Contact", SqlDbType.NVarChar,20);
SqlParameter paramPhone = new SqlParameter("@Phone", SqlDbType.NVarChar,50);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);
paramSysNo.Value = oParam.SysNo;
paramStockID.Value = oParam.StockID;
paramStockName.Value = oParam.StockName;
paramAddress.Value = oParam.Address;
paramContact.Value = oParam.Contact;
paramPhone.Value = oParam.Phone;
paramStatus.Value = oParam.Status;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramStockID);
cmd.Parameters.Add(paramStockName);
cmd.Parameters.Add(paramAddress);
cmd.Parameters.Add(paramContact);
cmd.Parameters.Add(paramPhone);
cmd.Parameters.Add(paramStatus);
return SqlHelper.ExecuteNonQuery(cmd);
}
private void map(StockInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.StockID = Util.TrimNull(tempdr["StockID"]);
oParam.StockName = Util.TrimNull(tempdr["StockName"]);
oParam.Address = Util.TrimNull(tempdr["Address"]);
oParam.Contact = Util.TrimNull(tempdr["Contact"]);
oParam.Phone = Util.TrimNull(tempdr["Phone"]);
oParam.Status = Util.TrimIntNull(tempdr["Status"]);
oParam.StockType = Util.TrimIntNull(tempdr["StockType"]);
}
public int Update(StockInfo oParam)
{
string sql = " select top 1 sysno from Stock where StockID = " + Util.ToSqlString(oParam.StockID) + " and sysno <> " + oParam.SysNo;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if ( Util.HasMoreRow(ds) )
throw new BizException("the same Stock ID exists already");
int result = new StockDac().Update(oParam);
SyncManager.GetInstance().SetDbLastVersion( (int)AppEnum.Sync.Stock );
stockHash.Remove(oParam.SysNo);
stockHash.Add(oParam.SysNo, oParam);
return result;
}
public void Init()
{
lock( stockLock )
{
if ( stockHash != null )
stockHash.Clear();
string sql = "select * from Stock";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if ( !Util.HasMoreRow(ds) )
return;
foreach(DataRow dr in ds.Tables[0].Rows)
{
StockInfo item = new StockInfo();
map(item, dr);
if( stockHash == null)
{
stockHash = new Hashtable(5);
}
stockHash.Add(item.SysNo, item);
}
}
}
public void ImportStock()
{
if ( !AppConfig.IsImportable)
throw new BizException("Is Importable is false");
/* do not use the following code after Data Pour in */
string sql = " select top 1 * from Stock ";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if ( Util.HasMoreRow(ds) )
throw new BizException("the table Stock is not empty");
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
string sql1 = "select * from ipp2003..Stock";
DataSet ds1 = SqlHelper.ExecuteDataSet(sql1);
foreach(DataRow dr1 in ds1.Tables[0].Rows )
{
StockInfo oInfo = new StockInfo();
oInfo.StockID = Util.TrimNull(dr1["StockID"]);
oInfo.StockName = Util.TrimNull(dr1["StockName"]);
oInfo.Address = Util.TrimNull(dr1["StockAddress"]);
oInfo.Contact =Util.TrimNull(dr1["Contact"]);
oInfo.Phone = Util.TrimNull(dr1["Tel"]);
oInfo.Status = Util.TrimIntNull(dr1["Status"]);
this.Insert(oInfo);
//insert into convert table
ImportInfo oVendorConvert = new ImportInfo();
oVendorConvert.OldSysNo = Util.TrimIntNull(dr1["SysNo"]);
oVendorConvert.NewSysNo = oInfo.SysNo;
new ImportDac().Insert(oVendorConvert, "Stock");
}
scope.Complete();
}
}
public int CompareTo(object obj)
{
StockInfo b = obj as StockInfo;
return(String.Compare(this.StockID, b.StockID));
}