public DataSet GetProductModelAndCP()
{
DataSet dsReturn = new DataSet();
string sqlCommand = string.Empty;
try
{
BASE_PRODUCTMODEL _productModel = new BASE_PRODUCTMODEL();
BASE_PRODUCTMODEL_CP _productModel_cp = new BASE_PRODUCTMODEL_CP();
BASE_PRODUCTMODEL_POWER _productModel_power = new BASE_PRODUCTMODEL_POWER();
BASE_PRODUCTMODEL_CTM _productModel_ctm = new BASE_PRODUCTMODEL_CTM(); //add by chao.pang 20150701
Conditions _conditions = new Conditions();
_conditions.Add(DatabaseLogicOperator.And, BASE_PRODUCTMODEL.FIELDS_ISFLAG, DatabaseCompareOperator.Equal, "1");
sqlCommand = DatabaseTable.BuildQuerySqlStatement(_productModel, null, _conditions);
DataTable dtProModel = db.ExecuteDataSet(CommandType.Text, sqlCommand).Tables[0];
dtProModel.Columns.Add(BASE_PRODUCTMODEL.FIELDS_ISNEW);
dtProModel.TableName = BASE_PRODUCTMODEL.DATABASE_TABLE_NAME;
dsReturn.Merge(dtProModel, true, MissingSchemaAction.Add);
_conditions.RemoveAll();
_conditions.Add(DatabaseLogicOperator.And, BASE_PRODUCTMODEL_CP.FIELDS_ISFLAG, DatabaseCompareOperator.Equal, "1");
sqlCommand = DatabaseTable.BuildQuerySqlStatement(_productModel_cp, null, _conditions);
DataTable dtProModel_cp = db.ExecuteDataSet(CommandType.Text, sqlCommand).Tables[0];
dtProModel_cp.Columns.Add(BASE_PRODUCTMODEL_CP.FIELDS_ISNEW);
dtProModel_cp.TableName = BASE_PRODUCTMODEL_CP.DATABASE_TABLE_NAME;
dsReturn.Merge(dtProModel_cp, true, MissingSchemaAction.Add);
_conditions.RemoveAll();
_conditions.Add(DatabaseLogicOperator.And, BASE_PRODUCTMODEL_POWER.FIELDS_ISFLAG, DatabaseCompareOperator.Equal, "1");
sqlCommand = DatabaseTable.BuildQuerySqlStatement(_productModel_power, null, _conditions);
DataTable dtProModel_Power = db.ExecuteDataSet(CommandType.Text, sqlCommand).Tables[0];
dtProModel_Power.Columns.Add(BASE_PRODUCTMODEL_POWER.FIELDS_ISNEW);
dtProModel_Power.TableName = BASE_PRODUCTMODEL_POWER.DATABASE_TABLE_NAME;
dsReturn.Merge(dtProModel_Power, true, MissingSchemaAction.Add);
_conditions.RemoveAll();
_conditions.Add(DatabaseLogicOperator.And, BASE_PRODUCTMODEL_CTM.FIELDS_ISFLAG, DatabaseCompareOperator.Equal, "1"); //add by chao.pang 20150701
sqlCommand = DatabaseTable.BuildQuerySqlStatement(_productModel_ctm, null, _conditions); //add by chao.pang 20150701
DataTable dtProModelCtm = db.ExecuteDataSet(CommandType.Text, sqlCommand).Tables[0]; //add by chao.pang 20150701
dtProModelCtm.Columns.Add("ischecked", typeof(bool));
dtProModelCtm.TableName = BASE_PRODUCTMODEL_CTM.DATABASE_TABLE_NAME; //add by chao.pang 20150701
dsReturn.Merge(dtProModelCtm, true, MissingSchemaAction.Add); //add by chao.pang 20150701
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "");
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("GetProductModelAndCP Error: " + ex.Message);
}
return(dsReturn);
}
public DataSet DelProductModel(DataSet dsProModel)
{
DataSet dsReturn = new DataSet();
DataTable dtProModel = null, dtProModel_CP = null, dtProModel_Level = null;
List <string> sqlCommandList = new List <string>();
if (dsProModel.Tables.Contains(BASE_PRODUCTMODEL.DATABASE_TABLE_NAME))
{
dtProModel = dsProModel.Tables[BASE_PRODUCTMODEL.DATABASE_TABLE_NAME];
}
if (dsProModel.Tables.Contains(BASE_PRODUCTMODEL_CP.DATABASE_TABLE_NAME))
{
dtProModel_CP = dsProModel.Tables[BASE_PRODUCTMODEL_CP.DATABASE_TABLE_NAME];
}
if (dsProModel.Tables.Contains(BASE_PRODUCTMODEL_POWER.DATABASE_TABLE_NAME))
{
dtProModel_Level = dsProModel.Tables[BASE_PRODUCTMODEL_POWER.DATABASE_TABLE_NAME];
}
using (DbConnection dbConn = db.CreateConnection())
{
//Open Connection
dbConn.Open();
//Create Transaction
DbTransaction dbTran = dbConn.BeginTransaction();
string sqlCommand = string.Empty;
BASE_PRODUCTMODEL proModel = new BASE_PRODUCTMODEL();
BASE_PRODUCTMODEL_CP proModel_cp = new BASE_PRODUCTMODEL_CP();
BASE_PRODUCTMODEL_POWER proModel_Level = new BASE_PRODUCTMODEL_POWER();
try
{
if (dtProModel != null && dtProModel.Rows.Count > 0)
{
foreach (DataRow dr in dtProModel.Rows)
{
Hashtable hashTable = FanHai.Hemera.Share.Common.CommonUtils.ConvertRowToHashtable(dr);
WhereConditions wc = new WhereConditions(BASE_PRODUCTMODEL.FIELDS_PROMODEL_KEY, hashTable[BASE_PRODUCTMODEL.FIELDS_PROMODEL_KEY].ToString());
hashTable.Remove(BASE_PRODUCTMODEL.FIELDS_PROMODEL_KEY);
sqlCommand = DatabaseTable.BuildUpdateSqlStatement(proModel, hashTable, wc);
db.ExecuteNonQuery(dbTran, CommandType.Text, sqlCommand);
}
}
if (dtProModel_CP != null && dtProModel_CP.Rows.Count > 0)
{
foreach (DataRow dr in dtProModel_CP.Rows)
{
Hashtable hashTable = FanHai.Hemera.Share.Common.CommonUtils.ConvertRowToHashtable(dr);
WhereConditions wc = new WhereConditions(BASE_PRODUCTMODEL_CP.FIELDS_PROMODEL_DTL_KEY, hashTable[BASE_PRODUCTMODEL_CP.FIELDS_PROMODEL_DTL_KEY].ToString());
hashTable.Remove(BASE_PRODUCTMODEL_CP.FIELDS_PROMODEL_DTL_KEY);
sqlCommand = DatabaseTable.BuildUpdateSqlStatement(proModel_cp, hashTable, wc);
db.ExecuteNonQuery(dbTran, CommandType.Text, sqlCommand);
}
}
if (dtProModel_Level != null && dtProModel_Level.Rows.Count > 0)
{
foreach (DataRow dr in dtProModel_Level.Rows)
{
Hashtable hashTable = FanHai.Hemera.Share.Common.CommonUtils.ConvertRowToHashtable(dr);
WhereConditions wc = new WhereConditions(BASE_PRODUCTMODEL_POWER.FIELDS_PROMODEL_POWER_KEY, hashTable[BASE_PRODUCTMODEL_POWER.FIELDS_PROMODEL_POWER_KEY].ToString());
hashTable.Remove(BASE_PRODUCTMODEL_POWER.FIELDS_PROMODEL_POWER_KEY);
sqlCommand = DatabaseTable.BuildUpdateSqlStatement(proModel_Level, hashTable, wc);
db.ExecuteNonQuery(dbTran, CommandType.Text, sqlCommand);
}
}
//Commit Transaction
dbTran.Commit();
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "");
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
//Rollback Transaction
dbTran.Rollback();
LogService.LogError("DelProductModel Error: " + ex.Message);
}
finally
{
//Close Connection
dbConn.Close();
}
}
return(dsReturn);
}
public DataSet SaveProductModel(DataSet dsProModel)
{
DataSet dsReturn = new DataSet();
DataTable dtProModel_Update = null, dtProModel_Insert = null;
DataTable dtProModel_CP_Update = null, dtProModel_CP_Insert = null;
DataTable dtProModel_Level_Update = null, dtProModel_Level_Insert = null;
DataTable dtProModel_CTM_Update = null;
List <string> sqlCommandList = new List <string>();
if (dsProModel.Tables.Contains(BASE_PRODUCTMODEL.DATABASE_TABLE_FORINSERT))
{
dtProModel_Insert = dsProModel.Tables[BASE_PRODUCTMODEL.DATABASE_TABLE_FORINSERT];
}
if (dsProModel.Tables.Contains(BASE_PRODUCTMODEL.DATABASE_TABLE_FORUPDATE))
{
dtProModel_Update = dsProModel.Tables[BASE_PRODUCTMODEL.DATABASE_TABLE_FORUPDATE];
}
if (dsProModel.Tables.Contains(BASE_PRODUCTMODEL_CP.DATABASE_TABLE_FORINSERT))
{
dtProModel_CP_Insert = dsProModel.Tables[BASE_PRODUCTMODEL_CP.DATABASE_TABLE_FORINSERT];
}
if (dsProModel.Tables.Contains(BASE_PRODUCTMODEL_CP.DATABASE_TABLE_FORUPDATE))
{
dtProModel_CP_Update = dsProModel.Tables[BASE_PRODUCTMODEL_CP.DATABASE_TABLE_FORUPDATE];
}
if (dsProModel.Tables.Contains(BASE_PRODUCTMODEL_POWER.DATABASE_TABLE_FORINSERT))
{
dtProModel_Level_Insert = dsProModel.Tables[BASE_PRODUCTMODEL_POWER.DATABASE_TABLE_FORINSERT];
}
if (dsProModel.Tables.Contains(BASE_PRODUCTMODEL_POWER.DATABASE_TABLE_FORUPDATE))
{
dtProModel_Level_Update = dsProModel.Tables[BASE_PRODUCTMODEL_POWER.DATABASE_TABLE_FORUPDATE];
}
if (dsProModel.Tables.Contains("BASE_PRODUCTMODEL_CTM_INSERT"))
{
dtProModel_CTM_Update = dsProModel.Tables["BASE_PRODUCTMODEL_CTM_INSERT"];
}
using (DbConnection dbConn = db.CreateConnection())
{
//Open Connection
dbConn.Open();
//Create Transaction
DbTransaction dbTran = dbConn.BeginTransaction();
string sqlCommand = string.Empty;
BASE_PRODUCTMODEL proModel = new BASE_PRODUCTMODEL();
BASE_PRODUCTMODEL_CP proModel_cp = new BASE_PRODUCTMODEL_CP();
BASE_PRODUCTMODEL_POWER proModel_Level = new BASE_PRODUCTMODEL_POWER();
try
{
if (dtProModel_Insert != null && dtProModel_Insert.Rows.Count > 0)
{
foreach (DataRow dr in dtProModel_Insert.Rows)
{
Hashtable hashTable = FanHai.Hemera.Share.Common.CommonUtils.ConvertRowToHashtable(dr);
sqlCommand = DatabaseTable.BuildInsertSqlStatement(proModel, hashTable, null);
db.ExecuteNonQuery(dbTran, CommandType.Text, sqlCommand);
}
}
if (dtProModel_CP_Insert != null && dtProModel_CP_Insert.Rows.Count > 0)
{
foreach (DataRow dr in dtProModel_CP_Insert.Rows)
{
Hashtable hashTable = FanHai.Hemera.Share.Common.CommonUtils.ConvertRowToHashtable(dr);
sqlCommand = DatabaseTable.BuildInsertSqlStatement(proModel_cp, hashTable, null);
db.ExecuteNonQuery(dbTran, CommandType.Text, sqlCommand);
}
}
if (dtProModel_Level_Insert != null && dtProModel_Level_Insert.Rows.Count > 0)
{
foreach (DataRow dr in dtProModel_Level_Insert.Rows)
{
Hashtable hashTable = FanHai.Hemera.Share.Common.CommonUtils.ConvertRowToHashtable(dr);
sqlCommand = DatabaseTable.BuildInsertSqlStatement(proModel_Level, hashTable, null);
db.ExecuteNonQuery(dbTran, CommandType.Text, sqlCommand);
}
}
if (dtProModel_Update != null && dtProModel_Update.Rows.Count > 0)
{
foreach (DataRow dr in dtProModel_Update.Rows)
{
Hashtable hashTable = FanHai.Hemera.Share.Common.CommonUtils.ConvertRowToHashtable(dr);
WhereConditions wc = new WhereConditions(BASE_PRODUCTMODEL.FIELDS_PROMODEL_KEY, hashTable[BASE_PRODUCTMODEL.FIELDS_PROMODEL_KEY].ToString());
hashTable.Remove(BASE_PRODUCTMODEL.FIELDS_PROMODEL_KEY);
sqlCommand = DatabaseTable.BuildUpdateSqlStatement(proModel, hashTable, wc);
db.ExecuteNonQuery(dbTran, CommandType.Text, sqlCommand);
}
}
if (dtProModel_CP_Update != null && dtProModel_CP_Update.Rows.Count > 0)
{
foreach (DataRow dr in dtProModel_CP_Update.Rows)
{
Hashtable hashTable = FanHai.Hemera.Share.Common.CommonUtils.ConvertRowToHashtable(dr);
WhereConditions wc = new WhereConditions(BASE_PRODUCTMODEL_CP.FIELDS_PROMODEL_DTL_KEY, hashTable[BASE_PRODUCTMODEL_CP.FIELDS_PROMODEL_DTL_KEY].ToString());
hashTable.Remove(BASE_PRODUCTMODEL_CP.FIELDS_PROMODEL_DTL_KEY);
sqlCommand = DatabaseTable.BuildUpdateSqlStatement(proModel_cp, hashTable, wc);
db.ExecuteNonQuery(dbTran, CommandType.Text, sqlCommand);
}
}
if (dtProModel_Level_Update != null && dtProModel_Level_Update.Rows.Count > 0)
{
foreach (DataRow dr in dtProModel_Level_Update.Rows)
{
Hashtable hashTable = FanHai.Hemera.Share.Common.CommonUtils.ConvertRowToHashtable(dr);
WhereConditions wc = new WhereConditions(BASE_PRODUCTMODEL_POWER.FIELDS_PROMODEL_POWER_KEY, hashTable[BASE_PRODUCTMODEL_POWER.FIELDS_PROMODEL_POWER_KEY].ToString());
hashTable.Remove(BASE_PRODUCTMODEL_POWER.FIELDS_PROMODEL_POWER_KEY);
sqlCommand = DatabaseTable.BuildUpdateSqlStatement(proModel_Level, hashTable, wc);
db.ExecuteNonQuery(dbTran, CommandType.Text, sqlCommand);
}
}
if (dtProModel_CTM_Update != null && dtProModel_CTM_Update.Rows.Count > 0)
{
string key = dtProModel_CTM_Update.Rows[0]["PROMODEL_KEY"].ToString();
string sql = string.Format("UPDATE BASE_PRODUCTMODEL_CTM SET ISFLAG = 0 WHERE PROMODEL_KEY = '{0}'", key);
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
foreach (DataRow dr in dtProModel_CTM_Update.Rows)
{
string guid = System.Guid.NewGuid().ToString();
string sqlInsert = string.Format(@"INSERT INTO dbo.BASE_PRODUCTMODEL_CTM(PROMODEL_KEY,CTM_KEY,EFF_UP,EFF_LOW,CTM_UP,CTM_LOW)
VALUES('{0}','{1}','{2}','{3}','{4}','{5}')",
key,
guid,
dr["EFF_UP"].ToString(),
dr["EFF_LOW"].ToString(),
dr["CTM_UP"].ToString(),
dr["CTM_LOW"].ToString());
db.ExecuteNonQuery(dbTran, CommandType.Text, sqlInsert);
}
}
//Commit Transaction
dbTran.Commit();
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "");
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
//Rollback Transaction
dbTran.Rollback();
LogService.LogError("SaveProductModel Error: " + ex.Message);
}
finally
{
//Close Connection
dbConn.Close();
}
}
return(dsReturn);
}
