public ResponseObjectPackage<List<ControlQueryMappingModel>> GetControlQueryMappingByFormID(int formID)
{
RequestPackage request = new RequestPackage() { requestID = formID };
List<ControlQueryMappingModel> mappings = new DBUtils().RunSqlAction(ControlQueryMappingRepository.GetByFormID, request).GetDataOrExceptionIfError();
ResponseObjectPackage<List<ControlQueryMappingModel>> response = new ResponseObjectPackage<List<ControlQueryMappingModel>>() { resultData = mappings };
return response;
}
public ResponseObjectPackage<List<List<QueryOutParameter>>> ExecuteQuery(int queryID, Dictionary<int, object> dictionary)
{
Query query = this.GetQueryViewModel(queryID).GetDataOrExceptionIfError();
string sql = query.sqlText;
foreach (QueryInParameter inParam in query.inParameters)
{
object value = dictionary[inParam.ID];
if (value == null)
{
throw new Exception("Для запроса заданы не все параметры! Проверьте мета-описания формы!");
}
inParam.value = ValueTypesConverter.Initialize(value.ToString(), inParam.domainValueTypeID, true);
sql = sql.Replace("{" + inParam.name + "}", inParam.value.ToSQL());
}
RequestPackage request = new RequestPackage() { requestString = sql };
List<Dictionary<string, object>> result = new DBUtils().RunSqlAction(new DynamicCRUD().GetDictionaryData, request).GetDataOrExceptionIfError();
List<List<QueryOutParameter>> resultOut = new List<List<QueryOutParameter>>();
foreach (Dictionary<string, object> row in result)
{
List<QueryOutParameter> rowOut = new List<QueryOutParameter>();
foreach (QueryOutParameter param in query.outParameters)
{
param.name = param.name.ToUpper();
if (!row.ContainsKey(param.name))
{
throw new Exception("Для запроса заданы не все параметры! Проверьте мета-описания формы!");
}
string value = row[param.name] == null ? null : row[param.name].ToString();
rowOut.Add(new QueryOutParameter(param, ValueTypesConverter.Initialize(value, param.domainValueTypeID, true)));
}
resultOut.Add(rowOut);
}
return new ResponseObjectPackage<List<List<QueryOutParameter>>>() { resultData = resultOut };
}
public ResponseObjectPackage<Dictionary<int, object>> GetDictionaryObjectByID(int dictionaryID, int pkValue)
{
Dictionary dictionary = new DictionariesLogic().GetDictionaryViewModel(dictionaryID).GetDataOrExceptionIfError();
string sql = string.Format(
"select {0} " + Environment.NewLine +
"from {1} " + Environment.NewLine +
"where {2} = {3}",
string.Join(", ", dictionary.fields.Select(e => e.columnName)),
dictionary.tableName,
dictionary.GetPrimaryKey().columnName,
pkValue
);
RequestPackage request = new RequestPackage() { requestString = sql };
List<Dictionary<string, object>> response = new DBUtils().RunSqlAction(new DynamicCRUD().GetDictionaryData, request).GetDataOrExceptionIfError();
if (response.Count > 1 || response.Count == 0)
{
throw new Exception("Запрос данных полей из словаря возвратил не одну строку! dictionaryID = " + dictionaryID + " pkValue = " + pkValue);
}
Dictionary<int, object> result = new Dictionary<int, object>();
foreach (DictionaryField field in dictionary.fields)
{
result.Add(field.ID, response[0][field.columnName]);
}
return new ResponseObjectPackage<Dictionary<int, object>>() { resultData = result };
}
public ResponseObjectPackage<List<FormInParameterModel>> GetFormInParametersByFormID(int formID)
{
RequestPackage request = new RequestPackage()
{
requestID = formID
};
ResponseObjectPackage<List<FormInParameterModel>> response = new DBUtils().RunSqlAction(FormInParametersRepository.GetByFormID, request);
return response;
}
public ResponseObjectPackage<List<DictionaryForeignKeyModel>> GetDictionaryForeignKeysByDictionaryIDSource(int dictionaryID)
{
RequestPackage request = new RequestPackage()
{
requestID = dictionaryID
};
ResponseObjectPackage<List<DictionaryForeignKeyModel>> response = new DBUtils().RunSqlAction(DictionaryForeignKeysRepository.GetByDictionaryIDSource, request);
return response;
}
public ResponseObjectPackage<List<DictionaryModel>> GetAllDictionaries()
{
RequestObjectPackage<DictionarySearchTemplate> request = new RequestObjectPackage<DictionarySearchTemplate>()
{
requestData = new DictionarySearchTemplate()
};
ResponseObjectPackage<List<DictionaryModel>> response = new DBUtils().RunSqlAction(DictionariesRepository.GetBySearchTemplate, request);
return response;
}
public ResponsePackage DeleteForeignKey(int foreignKeyID)
{
RequestPackage request = new RequestPackage()
{
requestID = foreignKeyID
};
ResponsePackage response = new DBUtils().RunSqlAction(DictionaryForeignKeysRepository.DeleteForeignKey, request);
response.ThrowExceptionIfError();
return response;
}
public ResponsePackage DeleteDictionaryField(int dictionaryFieldID)
{
RequestPackage fieldRequest = new RequestPackage()
{
requestID = dictionaryFieldID
};
ResponsePackage response = new DBUtils().RunSqlAction(new DictionaryTypesAdministrator().DeleteDictionaryField, fieldRequest);
response.ThrowExceptionIfError();
return response;
}
public ResponseObjectPackage<List<DictionaryFieldAdminModel>> GetDictionaryTypesAdministratorData(int dictionaryID)
{
RequestPackage request = new RequestPackage()
{
requestID = dictionaryID
};
ResponseObjectPackage<List<DictionaryFieldAdminModel>> response = new DBUtils().RunSqlAction(new DictionaryTypesAdministrator().GetDictionaryTypesAdministratorData, request);
response.ThrowExceptionIfError();
return response;
}
public ResponsePackage DeleteDictionaryGroup(int dictionaryGroupID)
{
RequestPackage request = new RequestPackage()
{
requestID = dictionaryGroupID
};
ResponsePackage response = new DBUtils().RunSqlAction(DictionaryGroupsRepository.DeleteDictionaryGroup, request);
response.ThrowExceptionIfError();
return response;
}
public ResponseObjectPackage<List<QueryQueryInParameterModel>> GetQueryQueryInParametersByQueryID(int queryID)
{
RequestObjectPackage<QueryQueryInParameterSearchTemplate> request = new RequestObjectPackage<QueryQueryInParameterSearchTemplate>()
{
requestData = new QueryQueryInParameterSearchTemplate()
{
queryID = queryID
}
};
ResponseObjectPackage<List<QueryQueryInParameterModel>> response = new DBUtils().RunSqlAction(QueryQueryInParametersRepository.GetBySearchTemplate, request);
return response;
}
public ResponseObjectPackage<DictionaryFieldModel> GetDictionaryFieldByID(int dictionaryFieldID)
{
RequestObjectPackage<DictionaryFieldSearchTemplate> request = new RequestObjectPackage<DictionaryFieldSearchTemplate>()
{
requestData = new DictionaryFieldSearchTemplate()
{
ID = dictionaryFieldID
}
};
DictionaryFieldModel response = new DBUtils().RunSqlAction(DictionaryFieldsRepository.GetBySearchTemplate, request).GetDataOrExceptionIfError().First();
return new ResponseObjectPackage<DictionaryFieldModel>() { resultData = response };
}
public ResponseObjectPackage<List<DictionaryFieldModel>> GetDictionaryFieldsByDictionaryID(int dictionaryID)
{
RequestObjectPackage<DictionaryFieldSearchTemplate> request = new RequestObjectPackage<DictionaryFieldSearchTemplate>()
{
requestData = new DictionaryFieldSearchTemplate()
{
dictionaryID = dictionaryID
}
};
ResponseObjectPackage<List<DictionaryFieldModel>> response = new DBUtils().RunSqlAction(DictionaryFieldsRepository.GetBySearchTemplate, request);
return response;
}
/// <summary>
/// Функция получения списка свойств компонента по его ID
/// </summary>
/// <param name="request">Объект-оболочка RequestPackage, содержащая в поле requestID id компонента</param>
/// <returns>Объект-оболочка ResponseObjectPackagе, содержащая в поле resultData список свойств компонента</returns>
public ResponseObjectPackage<List<ControlPropertyViewModel>> GetPropertiesByControlID(RequestPackage request)
{
ResponseObjectPackage<List<ControlPropertyViewModel>> response = new DBUtils().RunSqlAction(new ControlPropertiesRepository().GetPropertiesByControlID, request);
response.ThrowExceptionIfError();
// Свойству id ставим значение ID компонента
response.resultData.Find(x => x.property.ToLower() == "id").value = request.requestID.ToString();
foreach (ControlPropertyViewModel c in response.resultData)
{
c._value = c.GetRightValue();
}
return response;
}
public ResponseObjectPackage<QueryTypeModel> GetQueryTypeByID(int queryTypeID)
{
RequestObjectPackage<QueryTypeSearchTemplate> request = new RequestObjectPackage<QueryTypeSearchTemplate>()
{
requestData = new QueryTypeSearchTemplate()
{
ID = queryTypeID
}
};
ResponseObjectPackage<List<QueryTypeModel>> response = new DBUtils().RunSqlAction(QueryTypesRepository.GetBySearchTemplate, request);
QueryTypeModel model = response.GetDataOrExceptionIfError().First();
return new ResponseObjectPackage<QueryTypeModel>() { resultData = model };
}
public ResponseObjectPackage<DictionaryModel> GetDictionaryByID(int dictionaryID)
{
RequestObjectPackage<DictionarySearchTemplate> request = new RequestObjectPackage<DictionarySearchTemplate>()
{
requestData = new DictionarySearchTemplate()
{
ID = dictionaryID
}
};
ResponseObjectPackage<List<DictionaryModel>> response = new DBUtils().RunSqlAction(DictionariesRepository.GetBySearchTemplate, request);
DictionaryModel dictionary = response.GetDataOrExceptionIfError().First();
return new ResponseObjectPackage<DictionaryModel>() { resultData = dictionary };
}
/// <summary>
/// Функция получения списка свойств компонентов формы по ее ID
/// </summary>
/// <param name="request">Объект-оболочка RequestPackage, содержащая в поле requestID ID формы</param>
/// <returns>Объект-оболочка ResponseObjectPackagе, содержащая в поле resultData список свойств компонентов формы</returns>
public ResponseObjectPackage<List<ControlPropertyViewModel>> GetPropertiesByFormID(RequestPackage request)
{
ResponseObjectPackage<List<ControlPropertyViewModel>> response = new DBUtils().RunSqlAction(new ControlPropertiesRepository().GetPropertiesByFormID, request);
response.ThrowExceptionIfError();
foreach (ControlPropertyViewModel c in response.resultData)
{
c._value = c.GetRightValue();
}
foreach (ControlPropertyViewModel c in response.resultData.FindAll(x => x.property.ToLower() == "id"))
{
c._value = c.controlID;
}
return response;
}
public ResponseObjectPackage<List<Dictionary<string, object>>> GetDictionaryData(int dictionaryID)
{
DictionaryModel dictionary = new DictionariesLogic().GetDictionaryByID(dictionaryID).GetDataOrExceptionIfError();
List<DictionaryFieldModel> fields = new DictionaryFieldsLogic().GetDictionaryFieldsByDictionaryID(dictionaryID).GetDataOrExceptionIfError();
string sql = string.Format(
"select {0} " +
"from {1};",
string.Join(", ", fields.Select(e => e.columnName)),
dictionary.tableName
);
RequestPackage request = new RequestPackage() { requestString = sql };
ResponseObjectPackage<List<Dictionary<string, object>>> response = new DBUtils().RunSqlAction(new DynamicCRUD().GetDictionaryData, request);
return response;
}
/// <summary>
/// Удалить удаленные пользвателем компоненты
/// </summary>
/// <param name="package"></param>
/// <returns></returns>
public ResponsePackage DeleteOldControls(RequestObjectPackage<SaveControlModel> package)
{
// все контролы формы
List<ControlListEntity> oldControls = new ControlsLogic().GetControlsByFormID(new RequestPackage() { requestID = package.requestID }).GetDataOrExceptionIfError();
// список свежих ID контролов
List<int> newIDs = new List<int>();
Action<SaveControlModel> action = null;
action = delegate(SaveControlModel current)
{
if (current.control.ID > 0)
{
newIDs.Add(current.control.ID);
}
if (current.items != null && current.items.Count > 0)
{
foreach (SaveControlModel ctrl in current.items)
{
action(ctrl);
}
}
};
action(package.requestData);
// Для контролов удалить все евенты
RequestPackage delEvents = new RequestPackage() { requestID = package.requestID };
ResponsePackage responseDelEvents = new DBUtils().RunSqlAction(new EventEditorDataCRUD().DeleteAllEvents, delEvents);
responseDelEvents.ThrowExceptionIfError();
// ID контролов для удаления
List<int> IDsToDelete = oldControls.FindAll(x => newIDs.Contains(x.ID) == false).OrderByDescending(x => x.orderNumber).Select(x => x.ID).ToList();
if (IDsToDelete != null && IDsToDelete.Count > 0)
{
foreach (int id in IDsToDelete)
{
RequestPackage req = new RequestPackage() { requestID = id };
// Проверить возможность удаления
ResponsePackage responseCheck = new DBUtils(new FireBirdConnectionFactory()).RunSqlAction(new ControlsRepository().CheckDeleteControl, req);
responseCheck.ThrowExceptionIfError();
// Удалить свойства контрола
ResponsePackage responseDelProperty = new DBUtils(new FireBirdConnectionFactory()).RunSqlAction(new ControlPropertiesRepository().DeletePropertiesByControlID, req);
responseDelProperty.ThrowExceptionIfError();
// Удалить контрол
ResponsePackage responseDelControl = new DBUtils(new FireBirdConnectionFactory()).RunSqlAction(new ControlsRepository().DeleteControlByID, req);
responseDelControl.ThrowExceptionIfError();
}
}
return new ResponsePackage();
}
public ResponseObjectPackage<List<ControlProperty>> GetControlPropertiesByFormID(int formID)
{
RequestPackage request = new RequestPackage() { requestID = formID };
List<ControlPropertyViewModel> properties = new DBUtils().RunSqlAction(new ControlPropertiesRepository().GetPropertiesByFormID, request).GetDataOrExceptionIfError();
List<ControlProperty> propertiesList = properties.Select(e =>
new ControlProperty()
{
controlID = e.controlID,
controlPropertyTypeID = e.controlPropertyTypeID,
domainValueTypeID = e.DOMAIN_VALUE_TYPE_ID_LOGIC,
ID = e.ID,
name = e.property,
value = ValueTypesConverter.Initialize(e.value, e.logicValueTypeID, true)
}
).ToList();
ResponseObjectPackage<List<ControlProperty>> response = new ResponseObjectPackage<List<ControlProperty>>() { resultData = propertiesList };
return response;
}
public ResponseObjectPackage<List<Control>> GetEventsbyControls(List<Control> controls)
{
List<Event> events = new List<Event>();
RequestObjectPackage<List<Control>> eventsRequest = new RequestObjectPackage<List<Control>>()
{
requestData = controls
};
List<EventModel> eventModels = new DBUtils().RunSqlAction(EventsRepository.GetByControlsList, eventsRequest).GetDataOrExceptionIfError();
RequestObjectPackage<List<EventModel>> actionsRequest = new RequestObjectPackage<List<EventModel>>()
{
requestData = eventModels
};
List<EventAction> actions = new DBUtils().RunSqlAction(new EventActionsCRUD().GetActionsByEventsList, actionsRequest).GetDataOrExceptionIfError();
RequestObjectPackage<List<EventAction>> paramsRequest = new RequestObjectPackage<List<EventAction>>()
{
requestData = actions
};
List<EventActionParameter> parameters = new DBUtils().RunSqlAction(new EventActionsCRUD().GetActionParametersByActionsList, paramsRequest).GetDataOrExceptionIfError();
List<ActionTypeProperty> properties = new DBUtils().RunSqlAction(new EventActionsCRUD().GetActionTypePropertiesByActionsList, paramsRequest).GetDataOrExceptionIfError();
foreach (EventAction action in actions)
{
action.parameters = parameters.Where(e => e.actionID == action.ID).ToList();
action.properties = properties.Where(e => e.actionTypeID == action.actionTypeID).ToList();
}
foreach (EventModel model in eventModels)
{
Event newEvent = new Event();
newEvent.ID = model.ID;
newEvent.eventTypeID = model.eventTypeID;
newEvent.controlID = model.controlID;
newEvent.actions = actions.Where(e => e.eventID == model.ID).ToList();
events.Add(newEvent);
}
foreach (Control control in controls)
{
control.events = events.Where(e => e.controlID == control.ID).ToList();
}
return new ResponseObjectPackage<List<Control>>() { resultData = controls };
}
public ResponsePackage DeleteDictionaryData(DictionaryValue row, int dictionaryID)
{
string sql = null;
DictionaryFieldValue pk = row.GetPrimaryKey();
if (pk.value.IsNotDefault())
{
sql = string.Format(
"delete from {0} " + Environment.NewLine +
"where {1} = {2};",
row.dictionary.tableName,
pk.dictionaryField.columnName,
pk.value.ToSQL()
);
}
RequestPackage request = new RequestPackage() { requestString = sql };
ResponsePackage response = new DBUtils().RunSqlAction(new DynamicCRUD().UpdateDictionaryData, request);
return response;
}
public ResponseObjectPackage<List<Control>> GetControlsByFormID(int formID)
{
RequestPackage request = new RequestPackage() { requestID = formID };
List<ControlListEntity> controls = new DBUtils().RunSqlAction(new ControlsRepository().GetControlsByFormID, request).GetDataOrExceptionIfError();
List<Control> controlsList = controls.Select(e =>
new Control()
{
children = new List<Control>(),
controlDictionaryMappings = new List<ControlDictionaryMappingModel>(),
controlIDParent = e.controlIDParent,
controlQueryMappings = new List<ControlQueryMappingModel>(),
controlTypeID = e.controlTypeID,
formID = e.formID,
ID = e.ID,
orderNumber = e.orderNumber,
properties = new List<ControlProperty>()
}
).ToList();
ResponseObjectPackage<List<Control>> response = new ResponseObjectPackage<List<Control>>() { resultData = controlsList };
return response;
}
public static SysOnline ContinueOnline(string manid, short regid)
{
SysOnline item = new SysOnline();
try
{
ArrayList paras = new ArrayList();
paras.Add(DBUtils.MakeInParam("@Man_ID", SqlDbType.NVarChar, 6, manid));
paras.Add(DBUtils.MakeInParam("@Reg_ID", SqlDbType.SmallInt, regid));
SqlDataReader reader = DBUtils.ExecuteReader(CommandType.StoredProcedure, PublicConsts.DatabaseOwner + ".P_Continue_Online", paras);
if (reader.Read())
{
item = PopulateSysOnline(reader);
}
reader.Close();
}
catch { throw; }
return(item);
}
public void delete(int id)
{
logger.InfoFormat("Se sterge cursa cu id-ul {0}", id);
IDbConnection conn = DBUtils.getConnection();
using (var com = conn.CreateCommand())
{
com.CommandText = "delete from Cursa where idCursa=@id";
IDbDataParameter paramId = com.CreateParameter();
paramId.ParameterName = "@id";
paramId.Value = id;
com.Parameters.Add(paramId);
var result = com.ExecuteNonQuery();
if (result == 0)
{
logger.Info("Eroare incercand sa se stearga cursa cu id-ul");
throw new Exception("Eroare la stergere");
}
logger.InfoFormat("S-a sters cursa cu id-ul {0}", id);
}
}
public static SysMan GetSysManByType(string deptid, short type)
{
SysMan item = new SysMan();
try
{
ArrayList paras = new ArrayList();
paras.Add(DBUtils.MakeInParam("@Dept_ID", SqlDbType.NVarChar, 10, deptid));
paras.Add(DBUtils.MakeInParam("@Man_Type", SqlDbType.SmallInt, type));
SqlDataReader reader = DBUtils.ExecuteReader(CommandType.StoredProcedure, PublicConsts.DatabaseOwner + ".P_Get_ManByType", paras);
if (reader.Read())
{
item = PopulateSysMan(reader);
}
reader.Close();
}
catch { throw; }
return(item);
}
public static DataTable SelectSysMan(string deptid, string manname)
{
DataTable tblMain;
try
{
ArrayList paras = new ArrayList();
if (!String.IsNullOrEmpty(manname))
{
paras.Add(DBUtils.MakeInParam("@Man_Name", SqlDbType.NVarChar, 10, manname));
}
if (!String.IsNullOrEmpty(deptid))
{
paras.Add(DBUtils.MakeInParam("@Dept_ID", SqlDbType.NVarChar, 8, deptid));
}
tblMain = DBUtils.ExecuteDataTable(CommandType.StoredProcedure, PublicConsts.DatabaseOwner + ".P_Select_SysMan", paras);
tblMain.TableName = "SelectMain";
}
catch { throw; }
return(tblMain);
}
private void populateDiagnosis()
{
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCommand cmd = new SqlCommand("SELECT diagnosis from tblDiagnosis ORDER BY id ASC", conn))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
cmbDiagnosis.Items.Clear();
while (reader.Read())
{
int diagnosisIndex = reader.GetOrdinal("diagnosis");
string diagnosis = Convert.ToString(reader.GetValue(diagnosisIndex));
cmbDiagnosis.Items.Add(diagnosis);
}
}
}
}
}
DataView GetItemPrices()
{
DataView ret = null;
int ItemId = (int)DataBinder.Eval(this.NamingContainer, "DataItem.ItemId");
DataTable dt = null;
if (thisPage.PageContext["ItemPrices"] != null)
{
dt = (DataTable)thisPage.PageContext["ItemPrices"];
}
else
{
string sql = "select * from ItemPrices";
dt = DBUtils.GetDataSet(sql, cte.lib).Tables[0];
thisPage.PageContext["ItemPrices"] = dt;
}
ret = new DataView(dt, "ItemId=" + ItemId.ToString(), "IsDefault", DataViewRowState.CurrentRows);
return(ret);
}
public int RemoveDataCase(Dictionary <string, string> paramMaps)
{
string sql = @"DELETE FROM [FxtData_Case].[dbo].[住宅案例_出售] WITH (NOLOCK) WHERE CityId = [#CityID#] and [案例时间] >= '[#StartTime#]' and [案例时间] < '[#EndTime#]'";
foreach (KeyValuePair <string, string> item in paramMaps)
{
logger.DebugFormat("RemoveDataCase key={0}, Value = {1}", item.Key, item.Value);
sql = sql.Replace(item.Key, item.Value);
}
int cnt = 0;
try
{
cnt = DBUtils.DeleteData(CaseUrl, sql);
logger.DebugFormat("Delete DataCase Count={0}", cnt);
}
catch (Exception e)
{
logger.Error(e);
logger.ErrorFormat("RemoveDataCase SQL: {0}", sql);
}
return(cnt);
}
public DataTable Select()
{
SqlConnection conn = DBUtils.GetDBConnection();
DataTable dt = new DataTable();
try
{
string sqlExpression = "SELECT * FROM appointments";
SqlCommand command = new SqlCommand(sqlExpression, conn);
SqlDataAdapter adapter = new SqlDataAdapter(command);
conn.Open();
adapter.Fill(dt);
}
catch (Exception ex)
{
MessageBox.Show($"Error: {ex.Message}", "Error", MessageBoxButtons.OK);
}
finally
{
conn.Close();
}
return(dt);
}
public void DeleteDownload(int DownloadId)
{
DataRow download = GetDownload(DownloadId);
if (download == null)
{
return;
}
string file = Path.Combine(DownloadsFolder, download["UniqueName"].ToString());
file = Path.Combine(file, download["FileName"].ToString());
string sql = string.Format("delete from Downloads where DownloadId={0}",
DownloadId);
DBUtils.ExecuteQuery(sql, _lib);
if (File.Exists(file))
{
File.Delete(file);
}
}
public ActionResult ValidateLogin(Logon user)
{
if (ModelState.IsValid)
{
Logon users = null;
using (MySqlConnection conn = DBUtils.GetConnection())
{
LogonRepository repo = new LogonRepository(conn);
users = repo.GetByName(user.UserName);
}
if (users.Password.Equals(user.Password))
{
context.SetAuthenticationToken(user.UserName.ToString(), false, user);
return(RedirectToAction("Index", "Home"));
}
else
{
ModelState.AddModelError(string.Empty, "Invalid Login Information.");
return(View("Login", user));
}
}
return(View("Login", user));
}
public static SysDept GetSysDept(string deptid)
{
SysDept item = new SysDept();
try
{
ArrayList paras = new ArrayList();
paras.Add(DBUtils.MakeInParam("@Dept_ID", SqlDbType.NVarChar, 8, deptid));
paras.Add(DBUtils.MakeInParam("@Get_Action", SqlDbType.NVarChar, 20, DataGetAction.Row.ToString().ToLower()));
SqlDataReader reader = DBUtils.ExecuteReader(CommandType.StoredProcedure, PublicConsts.DatabaseOwner + ".P_Get_SysDept", paras);
if (reader.Read())
{
item = PopulateSysDept(reader);
}
reader.Close();
}
catch { throw; }
return(item);
}
public IDbDataParameter AddParameter(IDbCommand command, string name, object value, Type type)
{
if (type == typeof(byte[]))
{
SqlCeParameter result = (SqlCeParameter)command.CreateParameter();
result.ParameterName = name;
result.Value = value ?? DBNull.Value;
result.SqlDbType = SqlDbType.Image;
command.Parameters.Add(result);
return(result);
}
// We need to use NText as parameter type, if the value is of "IsCLOB" type.
if (type == typeof(string) && value != null && IsCLOB((uint)value.ToString().Length))
{
SqlCeParameter result = (SqlCeParameter)command.CreateParameter();
result.ParameterName = name;
result.Value = value;
result.SqlDbType = SqlDbType.NText;
command.Parameters.Add(result);
return(result);
}
return(DBUtils.AddSimpleParameter(command, name, value, type));
}
private void populateTreatment()
{
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCommand cmd = new SqlCommand("SELECT treatment from tblTreatment ORDER BY id ASC", conn))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
cmbTreatment.Items.Clear();
while (reader.Read())
{
int treatmentIndex = reader.GetOrdinal("treatment");
string treatment = Convert.ToString(reader.GetValue(treatmentIndex));
cmbTreatment.Items.Add(treatment);
}
}
}
}
}
/// <summary>
/// 通过FID获取单据数据
/// </summary>
/// <param name="FID"></param>
/// <returns></returns>
private DynamicObjectCollection DB_GetFormData(string FID)
{
string formid = CZ_GetFormType();
string[] tb = FormIdToTbNameMap[formid];
string t_head = tb[0];
string t_entry = tb[1];
string sql = "";
if (t_entry == "")
{
sql = string.Format("select FID FEId, * from {0} where FID='{1}'", t_head, FID);
}
else
{
sql = string.Format(@"select h.FID FEId, * from {0} h
inner join {1} e on h.FID=e.FID
where h.FID='{2}'", t_head, t_entry, FID);
}
var objs = DBUtils.ExecuteDynamicObject(this.Context, sql);
return(objs);
}
public static SysRegister GetSysRegister(string regnumber)
{
SysRegister item = new SysRegister();
try
{
ArrayList paras = new ArrayList();
paras.Add(DBUtils.MakeInParam("@Reg_Number", SqlDbType.NVarChar, 40, regnumber));
paras.Add(DBUtils.MakeInParam("@Get_Action", SqlDbType.NVarChar, 10, DataGetAction.Row.ToString().ToLower()));
SqlDataReader reader = DBUtils.ExecuteReader(CommandType.StoredProcedure, PublicConsts.DatabaseOwner + ".P_Get_SysRegister", paras);
if (reader.Read())
{
item = PopulateSysRegister(reader);
}
reader.Close();
}
catch { throw; }
return(item);
}
/// <summary>
/// 分页
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="allRowsCount"></param>
/// <param name="predicate"></param>
/// <param name="sort"></param>
/// <returns></returns>
public IEnumerable <TReturn> GetPageList <TReturn>(int pageIndex, int pageSize, out long allRowsCount,
object predicate = null, IList <ISort> sort = null) where TReturn : class
{
DbConnObj ConnObj = GetConnObj();
allRowsCount = 0;
IEnumerable <TReturn> entityList = new List <TReturn>();
if (ConnObj.DbConnection != null)
{
entityList = ConnObj.DbConnection.GetPage <T, TReturn>(predicate, sort, pageIndex, pageSize, ConnObj.DbTransaction, null, dbType: ConnObj.dbType);
allRowsCount = ConnObj.DbConnection.Count <T>(predicate, transaction: ConnObj.DbTransaction, dbType: ConnObj.dbType);
}
else
{
DataBaseType dbType;
using (IDbConnection conn = DBUtils.CreateDBConnection(ConnKey, out dbType)) {
entityList = conn.GetPage <T, TReturn>(predicate, sort, pageIndex, pageSize, ConnObj.DbTransaction, null, dbType: dbType);
allRowsCount = conn.Count <T>(predicate, transaction: ConnObj.DbTransaction, dbType: dbType);
}
}
return(entityList);
}
public IEnumerable <Listener> GetAll()
{
List <Listener> listeners = new List <Listener>();
var con = DBUtils.getConnection();
var command = (SqlCommand)con.CreateCommand();
command.CommandText = "SELECT * FROM Listeners";
var reader = command.ExecuteReader();
while (reader.Read())
{
string username = reader.GetString(1);
string password = reader.GetString(2);
string firstName = reader.GetString(3);
string surName = reader.GetString(4);
string email = reader.GetString(5);
Listener listener = new Listener(username, password, firstName, surName, email);
listeners.Add(listener);
}
reader.Close();
return(listeners);
}
// 添加管理员信息
public string AddAdminInfo(string adminID, string adminName, string adminSex, string adminIDCard, string adminDepartment, string adminContact, string adminOther, int adminStatus)
{
string sql = "Insert into Administrator(administrator_id,administrator_name,administratort_sex,administrator_id_card,administrator_department,administrator_contact,administrator_other,status)values" +
"(@adminID, @adminName, @adminSex, @adminIDCard, @adminDepartment,@adminContact, @adminOther, @adminStatus)";
//adminID adminName adminSex adminIDCard adminDepartment adminContact adminOther adminStatus
SqlParameter[] parameters =
{
new SqlParameter("@adminID", adminID),
new SqlParameter("@adminName", adminName),
new SqlParameter("@adminSex", adminSex),
new SqlParameter("@adminIDCard", adminIDCard),
new SqlParameter("@adminDepartment", adminDepartment),
new SqlParameter("@adminContact", adminContact),
new SqlParameter("@adminOther", adminOther),
new SqlParameter("@adminStatus", adminStatus)
};
int count = DBUtils.getDBUtils().cud(sql, parameters);
string succeed = "成功添加" + count + "个管理员信息";
return(succeed);
}
public ResponseResult SuaPhanCongNhomLop(PhanCongNhomLopModel model)
{
ResponseResult res = null;
try
{
List <SqlParameter> listParameter = new List <SqlParameter>();
listParameter.Add(new SqlParameter("@PhanCongNhomLopID", model.PhanCongNhomLopID));
listParameter.Add(new SqlParameter("@BangPhanCongID", model.BangPhanCongID));
listParameter.Add(new SqlParameter("@KhoaID", model.KhoaID));
listParameter.Add(new SqlParameter("@HocPhanLogID", model.HocPhanLogID));
listParameter.Add(new SqlParameter("@SLSVNhomLop", model.SLSVNhomLop));
listParameter.Add(new SqlParameter("@SoLuongNhomLopLT", model.SoLuongNhomLopLT));
listParameter.Add(new SqlParameter("@SoLuongNhomLopTH", model.SoLuongNhomLopTH));
listParameter.Add(new SqlParameter("@NguoiTao", model.NguoiTao));
res = DBUtils.ExecuteSP <ResponseResult>("SP_PhanCongNhomLop_Sua", listParameter);
}
catch (Exception ex)
{
throw ex;
}
return(res);
}
protected Boolean fnCheckEmail(string Tid)
{
try
{
string qry = "SELECT [To_Email_Id] FROM [tbl_Email_TO_Master] WHERE [TYPE_ID]='" + Tid + "'";
DataTable dtTo = DBUtils.SQLSelect(new SqlCommand(qry));
qry = "SELECT [CC_Email_Id] FROM [tbl_Email_CC_Master] WHERE [TYPE_ID]='" + Tid + "'";
DataTable dtCC = DBUtils.SQLSelect(new SqlCommand(qry));
if (dtTo.Rows.Count == 0 || dtCC.Rows.Count == 0)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception ex)
{
throw ex;
}
}
public static RibPanel GetRibPanel(short id)
{
RibPanel item = new RibPanel();
try
{
ArrayList paras = new ArrayList();
paras.Add(DBUtils.MakeInParam("@Rib_Panel", SqlDbType.SmallInt, id));
paras.Add(DBUtils.MakeInParam("@Get_Action", SqlDbType.NVarChar, 10, DataGetAction.Row.ToString().ToLower()));
SqlDataReader reader = DBUtils.ExecuteReader(CommandType.StoredProcedure, PublicConsts.DatabaseOwner + ".P_Get_RibPanel", paras);
if (reader.Read())
{
item = PopulateRibPanel(reader);
}
reader.Close();
}
catch { throw; }
return(item);
}
public int AddExceptionData(DataCase dc)
{
string insert_sql = @"
INSERT INTO [FxtData_Case].[dbo].[住宅案例_出售_异常案例]
( [楼盘] ,[行政区] ,[楼栋] ,[楼层] ,[房号] ,[总楼层] ,[案例时间] ,[用途] ,[面积] ,[单价] ,[总价] ,[案例类型] ,[朝向] ,[建筑类型] ,[户型] ,[户型结构] ,[建筑年代] ,[装修] ,[使用面积],[剩余年限] ,[成新率] ,[币种] ,[附属房屋],[配套] ,[来源] ,[电话], [备注] ,[链接] ,[标题] ,[地址],[城市] ,[城市ID],[案例统计周])
values ('" + dc.ProjectName + @"','" + dc.AreaName + @"',NULL,'" + dc.FloorNumber + @"',NULL,'" + dc.TotalFloor + @"','" + dc.CaseDate.ToString("yyyy-MM-dd") + @"','" + dc.PurposeName + @"','" + dc.BuildingArea + @"','" + dc.UnitPrice + @"','" + dc.TotalPrice + @"','" + dc.CaseTypeName + @"','" + dc.FrontName + @"','" + dc.BuildingTypeName + @"','" + dc.HouseTypeName + @"','" + dc.StructureName + @"','" + dc.BuildingDate + @"','" + dc.ZhuangXiu + @"',NULL,'" + dc.RemainYear + @"','" + dc.Depreciation + @"','" + dc.MoneyUnitName + @"',NULL,'" + dc.PeiTao + @"','" + dc.SourceName + @"','" + dc.SourcePhone + @"','" + dc.Remark + @"','" + dc.SourceLink + @"',NULL,NULL,NULL,'" + dc.CityID + @"','" + dc.RecordWeek + @"')
";
logger.DebugFormat("AddExceptionData SQL={0}", insert_sql);
int rows = 0;
try
{
rows = DBUtils.insertData(CaseUrl, insert_sql);
}
catch (Exception e)
{
logger.Error(e);
logger.ErrorFormat("AddExceptionData SQL={0}", insert_sql);
}
return(rows);
}
/// <summary>
/// 获取数据规则下项目信息id集合
/// </summary>
/// <param name="ctx"></param>
/// <param name="personId"></param>
/// <returns></returns>
public List <long> getProjectIds(Context ctx, long personId)
{
List <long> proIds = new List <long>();
List <long> salerIds = getSalerPersonids(ctx, personId);
if (salerIds == null)
{
return(null);
}
string Ids = string.Join(",", salerIds);
string strSql = string.Format(@"/*dialect*/SELECT DISTINCT AA.F_PEJK_PRONO FROM PEJK_SALECONTRACTS AA INNER JOIN PEJK_SALECONTRACTENTRY BB ON AA.FID = BB.FID WHERE BB.F_PEJK_SALER IN ({0})", Ids);
DynamicObjectCollection col = DBUtils.ExecuteDynamicObject(ctx, strSql);
if (col == null || col.Count() == 0)
{
return(null);
}
foreach (var item in col)
{
proIds.Add(Convert.ToInt64(item["F_PEJK_PRONO"]));
}
return(proIds);
}
void createMasType()
{
MySqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
string sql = "SELECT * FROM diploma.document_specification;";
MySqlCommand command = new MySqlCommand(sql, conn);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
masTypeDocument[countMasTypeDocument] = new InformationAboutDocumentAll();
masTypeDocument[countMasTypeDocument].id = Convert.ToInt32(reader[0]);
masTypeDocument[countMasTypeDocument++].type = reader[1].ToString();
comboBox1.Items.Add(reader[1].ToString());
}
reader.Close();
conn.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DBUtils dbUtls = new DBUtils();
DataSet dst = new DataSet();
dst = dbUtls.ListarCategorias();
if (dst.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dst.Tables[0].Rows.Count; i++)
{
if (!Convert.IsDBNull(dst.Tables[0].Rows[i]["CategoriaPai"]))
{
ListItem it = new ListItem();
it.Value = dst.Tables[0].Rows[i]["idCategoria"].ToString();
it.Text = dst.Tables[0].Rows[i]["descrCategoria"].ToString();
ddlCategoria.Items.Add(it);
}
}
}
}
}
void createMasDiscipline()
{
MySqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
string sql = "SELECT ID_s_l, ID_discipline, discipline_name FROM studyload, users, disciplines WHERE studyload.ID_user = users.ID_u and studyload.ID_discipline = disciplines.ID_d and fio LIKE ('%" + MainForm.strWay[0] + "%');";
MySqlCommand command = new MySqlCommand(sql, conn);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
masDisciplineDocument[countMasDisciplineDocument] = new InformationAboutDocumentAll();
masDisciplineDocument[countMasDisciplineDocument].id = Convert.ToInt32(reader[0]);
masDisciplineDocument[countMasDisciplineDocument++].discipline = reader[2].ToString();
comboBox2.Items.Add(reader[2].ToString());
}
reader.Close();
conn.Close();
}
void createMasKeyWords()
{
MySqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
string sql = "SELECT * FROM diploma.tags;";
MySqlCommand command = new MySqlCommand(sql, conn);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
masKeyWords[countKeyWords] = new InformationAboutDocumentAll();
masKeyWords[countKeyWords].id = Convert.ToInt32(reader[0]);
masKeyWords[countKeyWords++].keyword = reader[1].ToString();
listBox1.Items.Add(reader[1].ToString());
}
reader.Close();
conn.Close();
}
public ActionResult Product(EditProduct prod)
{
if (ModelState.IsValid)
{
using (MySqlConnection conn = DBUtils.GetConnection())
{
Product product = new Product();
product.ProductID = prod.ProductID;
product.ProductName = prod.ProductName;
product.UnitsInStock = prod.UnitsInStock;
product.UnitsOnOrder = prod.UnitsOnOrder;
product.ActiveYN = prod.ActiveYN;
ProductRepository repo = new ProductRepository(conn);
repo.Save(product);
}
return(RedirectToAction("Products"));
}
return(View(prod));
}
// 修改管理员信息
public string UpdateAdminInfo(string adminID, string adminName, string adminSex, string adminIDCard, string adminDepartment, string adminContact, string adminOther, int adminStatus)
{
string sql = "update Administrator set administrator_name=@adminName,administratort_sex=@adminSex,administrator_id_card=@adminIDCard," +
"administrator_department=@adminDepartment,administrator_contact=@adminContact,administrator_other=@adminOther,status=@adminStatus " +
"where administrator_id = @adminID";
SqlParameter[] parameters =
{
new SqlParameter("@adminID", adminID),
new SqlParameter("@adminName", adminName),
new SqlParameter("@adminSex", adminSex),
new SqlParameter("@adminIDCard", adminIDCard),
new SqlParameter("@adminDepartment", adminDepartment),
new SqlParameter("@adminContact", adminContact),
new SqlParameter("@adminOther", adminOther),
new SqlParameter("@adminStatus", adminStatus)
};
int count = DBUtils.getDBUtils().cud(sql, parameters);
string succeed = "成功修改" + count + "个管理员信息";
return(succeed);
}
public Review GetOne(int id)
{
var _connectionString = DBUtils.getConnection();
var command = (SqlCommand)_connectionString.CreateCommand();
command.CommandText = "SELECT * FROM Reviews WHERE idR = @id";
command.Parameters.AddWithValue("@id", id);
var reader = command.ExecuteReader();
Review review = new Review();
if (reader.Read())
{
review.IdP = Int32.Parse(reader["idP"].ToString());
review.IdR = Int32.Parse(reader["idR"].ToString());
review.Comment = reader["comment"].ToString();
review.Qualifier = reader["qualifier"].ToString();
review.UsernameCommiteeMember = reader["username"].ToString();
}
reader.Close();
return(review);
}
public static SysMenuGroup GetSysMenuGroup(short mgid)
{
SysMenuGroup item = new SysMenuGroup();
try
{
ArrayList paras = new ArrayList();
paras.Add(DBUtils.MakeInParam("@Mg_ID", SqlDbType.SmallInt, mgid));
paras.Add(DBUtils.MakeInParam("@Get_Action", SqlDbType.NVarChar, 10, DataGetAction.Row.ToString().ToLower()));
SqlDataReader reader = DBUtils.ExecuteReader(CommandType.StoredProcedure, PublicConsts.DatabaseOwner + ".P_Get_SysMenuGroup", paras);
if (reader.Read())
{
item = PopulateSysMenuGroup(reader);
}
reader.Close();
}
catch { throw; }
return(item);
}
public JobHistoryDBMapper(DBUtils _dbConn)
: base(_dbConn)
{
}
/// <summary>
/// Функция удаления контрола из формы
/// </summary>
/// <param name="request">Объект-оболочка RequestPackage, содержащая в поле requestID ID контрола</param>
/// <returns>Объект-оболочка ResponsePackagе</returns>
public ResponsePackage DeleteControlByID(RequestPackage request)
{
ResponsePackage response = new DBUtils().RunSqlAction(new ControlsRepository().DeleteControlByID, request);
response.ThrowExceptionIfError();
return response;
}
/// <summary>
/// Функция получения списка контролов формы по ее ID
/// </summary>
/// <param name="request">Объект-оболочка RequestPackage, содержащая в поле requestID id формы</param>
/// <returns>Объект-оболочка ResponseObjectPackagе, содержащая в поле resultData список контролов формы</returns>
public ResponseObjectPackage<List<ControlListEntity>> GetControlsByFormID(RequestPackage request)
{
ResponseObjectPackage<List<ControlListEntity>> response = new DBUtils().RunSqlAction(new ControlsRepository().GetControlsByFormID, request);
response.ThrowExceptionIfError();
return response;
}
/// <summary>
/// Функция сохранения контрола формы для редактора форм.
/// </summary>
/// <param name="package">Объект-оболочка RequestPackage, содержащая в поле requestData контрол</param>
/// <returns>Объект-оболочка ResponsePackagе</returns>
public ResponsePackage SaveControl(RequestObjectPackage<ControlModel> package)
{
ResponsePackage response = new DBUtils(new FireBirdConnectionFactory()).RunSqlAction(new ControlsRepository().SaveControl, package);
response.ThrowExceptionIfError();
return response;
}
public CountryDBMapper(DBUtils _dbConn)
: base(_dbConn)
{
}
public EmployeeTrainingHistoryDBMapper(DBUtils _dbConn)
: base(_dbConn)
{
}
