public string ExecuteScalar(string sql)
{
string result = "";
switch (_dbConn.DbType)
{
case EDbType.MySql:
object resultObj=new MySqlCommand(sql, mySqlConn).ExecuteScalar();
result = resultObj ==null?"":resultObj.ToString();
break;
case EDbType.Oracle:
object oracleResultObj = new OracleCommand(sql, oracleConn).ExecuteScalar();
result = oracleResultObj == null ? "" : oracleResultObj.ToString();
break;
}
return result;
}
public async Task<int> ExecuteNonQuery(MySqlCommand stmt)
{
int result = 0;
if (connState == ConnectionState.Open)
{
try
{
result = await stmt.ExecuteNonQueryAsync();
}
catch (MySqlException e)
{
Logger.LOG_DATABASE.Error("Could not execute prepared statement " + stmt.ToString() + " as NonQuery. " + e.Message);
}
}
return result;
}
public async Task<object> ExecuteScalar(MySqlCommand stmt)
{
object result = null;
if (connState == ConnectionState.Open)
{
try
{
result = await stmt.ExecuteScalarAsync();
}
catch (MySqlException e)
{
Logger.LOG_DATABASE.Error("Could not execute prepared statement " + stmt.ToString() + " as scalar. " + e.Message);
}
}
return result;
}
public int SaveStatus(OrderShowMasterQuery store)
{
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
int re = 0;
try
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
store.serial_id = GetSerialValue(29); //得到serial_value,是order_master_status的id
mySqlConn.Open();
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
mySqlCmd.CommandText = UpdateSerialVal(store.serial_id, 29);
mySqlCmd.CommandText += InsertOrderMasterStatus(store);
re = mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("OrderShowBaseInfoDao-->SaveStatus-->" + mySqlCmd.ToString() + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return re;
}
public int NewsContentSave(NewsContentQuery store)
{
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
StringBuilder sql = new StringBuilder();
int re = 0;
try
{
#region 新增
if (store.news_id == 0)//新增
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
store.serialvalue = GetSerialValue(20);//news_content
store.log_value = GetSerialValue(21);//news_log
mySqlConn.Open();
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
mySqlCmd.CommandText = UpdateSerialVal(store.serialvalue, 20);
mySqlCmd.CommandText += InsertNewsContent(store);
mySqlCmd.CommandText += UpdateSerialVal(store.log_value, 21);
mySqlCmd.CommandText += AddNewsLog(store);
re = mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
}
#endregion
#region 編輯
else//編輯
{
store.Replace4MySQL();
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
store.serialvalue = GetSerialValue(20);//news_content
store.log_value = GetSerialValue(21);//news_log
mySqlConn.Open();
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
mySqlCmd.CommandText = UpdateNewsContent(store);
mySqlCmd.CommandText += UpdateSerialVal(store.log_value, 21);
mySqlCmd.CommandText += AddNewsLog(store);
re = mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
}
#endregion
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("NewsContentDao-->NewsContentSave-->" + mySqlCmd.ToString() + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return re;
}
public int UpMailGroupStatus(MailGroupQuery query)
{
StringBuilder sqlGroup = new StringBuilder();
StringBuilder sqlMap = new StringBuilder();
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
int re = 0;
try
{
sqlGroup.AppendFormat("update mail_group set status={0},update_time='{1}',update_user={2} where row_id={3};", query.status, CommonFunction.DateTimeToString(query.update_time), query.update_user, query.row_id);
//sqlMap.AppendFormat("set sql_safe_updates=0; update mail_group_map set status={0} where group_id={1};set sql_safe_updates=1; ", query.status, query.row_id);
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
mySqlConn.Open();
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
mySqlCmd.CommandText = sqlGroup.ToString();
// mySqlCmd.CommandText += sqlMap.ToString();
re = mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("MapGroupDao-->UpMailGroupStatus-->" + mySqlCmd.ToString() + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return re;
}
public int AnnounceSave(AnnounceQuery store)
{
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
StringBuilder sql = new StringBuilder();
int re = 0;
store.Replace4MySQL();
try
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
mySqlConn.Open();
}
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
SerialDao _serialDao = new SerialDao(connStr);
#region 新增
if (store.announce_id == 0)//新增
{
Serial sQuery = new Serial();
store.announce_id = Convert.ToUInt32(_serialDao.GetSerialById(65).Serial_Value + 1); // GetSerialValue(20);
mySqlCmd.CommandText = _serialDao.UpdateAutoIncreament(new Serial { Serial_id = 65, Serial_Value = store.announce_id });
mySqlCmd.CommandText += InsertAnnounce(store);
re = mySqlCmd.ExecuteNonQuery();
}
else//編輯
{
mySqlCmd.CommandText = UpdateAnnounce(store);
re = mySqlCmd.ExecuteNonQuery();
}
#endregion
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("AnnounceDao-->AnnounceSave-->" + mySqlCmd.ToString() + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return re;
}
public int EdmContentSave(EdmContentQuery store)
{
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
StringBuilder sql = new StringBuilder();
int re = 0;
if (store.content_id == 0)//新增
{
try
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
store.serialvalue = GetSerialValue();
mySqlConn.Open();
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
mySqlCmd.CommandText = UpdateSerialVal(store.serialvalue);
mySqlCmd.CommandText += InsertEdmContent(store);
re = mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("EdmContentDao-->EdmContentSave-->" + mySqlCmd.ToString() + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return re;
}
else //編輯
{
try
{
store.Replace4MySQL();
sql.AppendFormat("update edm_content set group_id={0},content_status={1},content_start={2},", store.group_id, store.content_status, store.content_start);
sql.AppendFormat("content_from_name='{0}',content_from_email='{1}',content_reply_email='{2}',content_priority={3},info_epaper_id='{4}',", store.content_from_name, store.content_from_email, store.content_reply_email, store.content_priority, store.info_epaper_id);
sql.AppendFormat("content_title='{0}',content_body='{1}',content_updatedate={2} where content_id={3};", store.content_title, store.content_body, CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")), store.content_id);
return _accessMySql.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmContentDao-->EdmContentSave-->" + sql.ToString() + ex.Message, ex);
}
}
}
public int SaveEpaperContent(EpaperContentQuery query)
{
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
int re = 0;
try
{
#region 新增
if (query.epaper_id == 0)
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
query.epaper_id = Convert.ToUInt32(GetSerialValue(24));//epapercontent
query.log_id = GetSerialValue(25);//log
mySqlConn.Open();
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
mySqlCmd.CommandText = UpdateSerialVal((Convert.ToInt32(query.epaper_id)), 24);
mySqlCmd.CommandText += InsertEpaperContent(query);
mySqlCmd.CommandText += UpdateSerialVal(query.log_id, 25);
mySqlCmd.CommandText += AddEpaperLog(query);
re = mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
}
#endregion
#region 編輯
else
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
//query.epaper_id = Convert.ToUInt32(GetSerialValue(24));//epapercontent
query.log_id = GetSerialValue(25);//log
mySqlConn.Open();
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
mySqlCmd.CommandText = UpdateEpaperContent(query);
mySqlCmd.CommandText += UpdateSerialVal(query.log_id, 25);
mySqlCmd.CommandText += AddEpaperLog(query);
re = mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
}
#endregion
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("NewsContentDao-->SaveEpaperContent-->" + mySqlCmd.ToString() + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return re;
}