public int SetPreferredHostNameOrder(HostNameOrderDB hostOrderName)
{
//open connection
if (this.OpenConnection() == true)
{
SQLiteTransaction trans = connection.BeginTransaction();
int res = -1;
try
{
SQLiteCommand comm = connection.CreateCommand();
comm.Transaction = trans;
if (hostOrderName.AccountTypeId == 9)
{
comm.CommandText = "UPDATE host_contact_name_order SET contact_name_order_id = @name_order_id where account_type_id=@account_type_id or account_type_id = 10";
}
else
{
comm.CommandText = "UPDATE host_contact_name_order SET contact_name_order_id = @name_order_id where account_type_id=@account_type_id";
}
comm.Parameters.AddWithValue("@name_order_id", hostOrderName.ContactNameOrderId);
comm.Parameters.AddWithValue("@account_type_id", hostOrderName.AccountTypeId);
comm.ExecuteNonQuery();
trans.Commit();
res = 1;
}
catch (Exception ex)
{
trans.Rollback();
}
this.CloseConnection();
return res;
}
return 0;
}
private void RefreshHostSetting(HostType host)
{
if (chosenHost == HostType.TYPE_UNKNOWN)
{
return;
}
string accTypeName = AccountTypeDB.FromTypeCodeToString(host);
listNameOrder = dbController.GetAllNameOrder();
hostNameOrder = dbController.GetHostNameOrder(accTypeName);
listEmailType = dbController.GetAllEmailTypeByAccountTypeName(accTypeName);
listFaxType = dbController.GetAllFaxTypeByAccountTypeName(accTypeName);
RefreshListNameOrder();
RefreshListEmailType();
RefreshListFaxType();
}
// name order
public HostNameOrderDB GetPreferredHostNameOrderByAccountTypeName(string accTypeName)
{
HostNameOrderDB order = new HostNameOrderDB();
if (this.OpenConnection() == true)
{
string query = "SELECT host_contact_name_order.id as id, host_contact_name_order.account_type_id, contact_name_order_id, name_order " +
"FROM contact_name_order, account_type, host_contact_name_order " +
"where host_contact_name_order.account_type_id = account_type.id and contact_name_order_id = contact_name_order.id " +
"and account_type.name = @hostName";
//Create Command
SQLiteCommand cmd = new SQLiteCommand(query, connection);
cmd.Parameters.AddWithValue("@hostName", accTypeName);
//Create a data reader and Execute the command
SQLiteDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
int id = Int32.Parse(dataReader["id"].ToString());
int account_type_id = Int32.Parse(dataReader["account_type_id"].ToString());
int contact_name_order_id = Int32.Parse(dataReader["contact_name_order_id"].ToString());
string name_order = dataReader["name_order"].ToString();
order.Id = id;
order.ContactNameOrderId = contact_name_order_id;
order.AccountTypeId = account_type_id;
order.ContactNameOrderName = name_order;
break;
}
//close Data Reader
dataReader.Close();
//close Connection
this.CloseConnection();
}
return order;
}
public HostNameOrderDB GetHostNameOrder(string accountTypeString)
{
string query = "SELECT host_contact_name_order.id as id, account_type_id, contact_name_order_id " +
"from account_type, host_contact_name_order where account_type.id = account_type_id and account_type.name = @name";
//Create a list to store the result
HostNameOrderDB hostNameOrder = new HostNameOrderDB();
//Open connection
if (this.OpenConnection() == true)
{
if (accountTypeString.Equals("File CSV, vCard"))
{
query = "SELECT host_contact_name_order.id as id, account_type_id, contact_name_order_id " +
"from account_type, host_contact_name_order where account_type.id = account_type_id and account_type.name = @name";
//Create Command
SQLiteCommand cmd = new SQLiteCommand(query, connection);
cmd.Parameters.AddWithValue("@name", "CSV");
//Create a data reader and Execute the command
SQLiteDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
int id = Int32.Parse(dataReader["id"].ToString());
int account_type_id = Int32.Parse(dataReader["account_type_id"].ToString());
int contact_name_order_id = Int32.Parse(dataReader["contact_name_order_id"].ToString());
hostNameOrder.Id = id;
hostNameOrder.AccountTypeId = account_type_id;
hostNameOrder.ContactNameOrderId = contact_name_order_id;
break;
}
//close Data Reader
dataReader.Close();
//close Connection
this.CloseConnection();
}
else
{
//Create Command
SQLiteCommand cmd = new SQLiteCommand(query, connection);
cmd.Parameters.AddWithValue("@name", accountTypeString);
//Create a data reader and Execute the command
SQLiteDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
int id = Int32.Parse(dataReader["id"].ToString());
int account_type_id = Int32.Parse(dataReader["account_type_id"].ToString());
int contact_name_order_id = Int32.Parse(dataReader["contact_name_order_id"].ToString());
hostNameOrder.Id = id;
hostNameOrder.AccountTypeId = account_type_id;
hostNameOrder.ContactNameOrderId = contact_name_order_id;
break;
}
//close Data Reader
dataReader.Close();
//close Connection
this.CloseConnection();
}
}
return hostNameOrder;
}
// name order
public HostNameOrderDB GetHostNameOrder(string accountTypeString)
{
string query = "SELECT host_contact_name_order.id as Id, host_contact_name_order.account_type_id as AccountTypeId, contact_name_order_id as ContactNameOrderId, name_order as ContactNameOrderName " +
"FROM contact_name_order, account_type, host_contact_name_order " +
"where host_contact_name_order.account_type_id = account_type.id and contact_name_order_id = contact_name_order.id " +
"and account_type.name = ?";
List<HostNameOrderDB> list = new List<HostNameOrderDB>();
HostNameOrderDB order = connection.Query<HostNameOrderDB>(query, accountTypeString).First();
if (order == null)
{
order = new HostNameOrderDB();
}
return order;
}
public int SetPreferredHostNameOrder(HostNameOrderDB hostOrderName)
{
string query = "UPDATE host_contact_name_order SET contact_name_order_id = ? where account_type_id=?";
int res = connection.Execute(query, hostOrderName.ContactNameOrderId, hostOrderName.AccountTypeId);
return res;
}
public int SetPreferredHostNameOrder(HostNameOrderDB hostOrderName)
{
DBManager db = new DBManager();
return db.SetPreferredHostNameOrder(hostOrderName);
}