/// <summary>
/// Login the user
/// </summary>
/// <param name="context">HttpContext object</param>
/// <param name="username">Username property</param>
/// <param name="password">Password property</param>
/// <param name="referrer">Referrer property</param>
/// <returns>Jessica Response object</returns>
private Response LoginUser(HttpContext context, string username, string password, string referrer)
{
var db = new DBFactory();
var user = db.DB().User.FindByLoginAndPassword(username, password.ToSHA512());
if (user != null) {
FormsAuthenticationTicket ticket = new FormsAuthenticationTicket(username, false, 15);
var cookieStr = FormsAuthentication.Encrypt(ticket);
var cookie = new HttpCookie(FormsAuthentication.FormsCookieName, cookieStr);
cookie.Path = FormsAuthentication.FormsCookiePath;
context.Response.Cookies.Add(cookie);
context.Session.Remove("user.name");
context.Session.Add("user.name", user.Name);
context.Session.Remove("user.login");
context.Session.Add("user.login", user.Login);
context.Session.Remove("user.id");
context.Session.Add("user.id", String.Format("{0}", user.Id));
return Response.AsRedirect(String.Format("{0}?login=true", referrer));
}
else {
return Response.AsRedirect(String.Format("/authentication/login?ref={0}&invalid=true", referrer));
}
}
public IEnumerable<MSI_Debtor> GetDebtors(string accountNumber)
{
string thisMethod = string.Format("{0}.{1}", thisClass, System.Reflection.MethodBase.GetCurrentMethod().Name);
string logMessage = string.Format("{0}|Method incoming parameters accountNumber={1}", thisMethod, accountNumber);
//LogHelper.Info(logMessage);
MSI_Debtor debtor = null;
DBFactory db;
List<MSI_Debtor> debtors = null;
System.Data.DataSet ds;
try
{
db = new DBFactory("CascadeDBEntities");
ds = db.ExecuteDataset("MSI_spGetDebtors", "Debtors", new SqlParameter("@pimsAccountNumber", accountNumber));
if (ds.Tables["Debtors"].Rows.Count > 0)
{
debtors = new List<MSI_Debtor>();
foreach (System.Data.DataRow dr in ds.Tables["Debtors"].Rows)
{
debtor = new MSI_Debtor();
debtor.Account = dr["ACCOUNT"].ToString();
debtor.FirstName = dr["FirstName"].ToString();
debtor.LastName = dr["LastName"].ToString();
debtor.Address1 = dr["ADDRESS1"].ToString();
debtor.Address2 = dr["ADDRESS2"].ToString();
debtor.City = dr["CITY"].ToString();
debtor.State = dr["STATE"].ToString();
debtor.Zip = dr["ZIP_CODE"].ToString();
string ssn = dr["SSN"].ToString().Trim();
debtor.LastFourSSN = ssn.Substring(7, 4);
debtor.MobilePhone = dr["PHONE_CELL"].ToString();
debtor.HomePhone = dr["PHONE_HOME"].ToString();
debtor.WorkPhone = dr["PHONE_WORK"].ToString();
debtor.DOB = dr["DOB"].ToString();
debtor.DebtCurrentBalance = dr["DebtCurrentBalance"].ToString();
debtor.DebtPurchaseBalance = dr["DebtorPurchaseBalance"].ToString();
debtor.CreditorName = dr["CreditorName"].ToString();
debtors.Add(debtor);
}
}
}
catch (Exception ex)
{
//ErrorLogHelper.Error(logMessage, ex);
throw ex;
}
return debtors.AsEnumerable<MSI_Debtor>();
}
/// <summary>
/// GetView method, to return a string value based on the url
/// </summary>
/// <param name="url">String object of the url, mostly only the domainname, without http://</param>
/// <returns>The alias of the view</returns>
private string GetView(string url)
{
DBFactory db = new DBFactory();
var view = db.DB().View.FindByUrl(url);
string viewAka = String.Empty;
if (view == null) {
viewAka = "default";
}
else {
viewAka = view.Aka;
}
return viewAka;
}
static void Main(string[] args)
{
/*
* Create and edit a DBEngine first.
* Then create a DBFactory using the DBEngine.
*/
"Testing DBEngine Package".title('=');
WriteLine();
Write("\n --- Test DBElement<int,string> ---");
DBElement<int, string> elem1 = new DBElement<int, string>();
elem1.payload = "a payload";
Write(elem1.showElement<int, string>());
WriteLine();
DBElement<int, string> elem2 = new DBElement<int, string>("Darth Vader", "Evil Overlord");
elem2.payload = "The Empire strikes back!";
Write(elem2.showElement<int, string>());
WriteLine();
int key = 0;
Func<int> keyGen = () => { ++key; return key; }; // anonymous function to generate keys
DBEngine<int, DBElement<int, string>> db = new DBEngine<int, DBElement<int, string>>();
bool p1 = db.insert(keyGen(), elem1);
bool p2 = db.insert(keyGen(), elem2);
Write("\n --- Create DBFactory<int, DBElement<int, string>> from DBEngine<int,DBElement<int, string>> ---");
DBFactory<int, DBElement<int, string>> dbFactory = new DBFactory<int, DBElement<int, string>>(db);
foreach (int dbKey in dbFactory.Keys())
{
DBElement<int, string> value;
dbFactory.getValue(dbKey, out value);
value.showElement();
}
dbFactory.showDB();
Write("\n\n");
}
public int insertWfCodcu(TOWfCdocu wfcdocu)
{
DateTime date = DateTime.Now;
StringBuilder sql = new StringBuilder();
sql.Append(" INSERT INTO WF_CDOCU ");
sql.Append(" (EMP_CODI,CAS_CONT,DOC_CONT,DOC_DESC,DOC_BLOB,AUD_ESTA,AUD_USUA,AUD_UFAC) ");
sql.Append(" VALUES(@EMP_CODI,@CAS_CONT,@DOC_CONT,@DOC_DESC,@DOC_BLOB,@AUD_ESTA,@AUD_USUA,@AUD_UFAC) ");
List <Parameter> parametros = new List <Parameter>();
parametros.Add(new Parameter("@EMP_CODI ", emp_codi));
parametros.Add(new Parameter("@CAS_CONT", wfcdocu.cas_cont));
parametros.Add(new Parameter("@DOC_CONT", wfcdocu.doc_cont));
parametros.Add(new Parameter("@DOC_DESC", wfcdocu.doc_desc));
parametros.Add(new Parameter("@DOC_BLOB", wfcdocu.doc_blob));
parametros.Add(new Parameter("@AUD_ESTA", "A"));
parametros.Add(new Parameter("@AUD_USUA", usu_codi));
parametros.Add(new Parameter("@AUD_UFAC", DateTime.Now));
OTOContext pTOContext = new OTOContext();
var conection = DBFactory.GetDB(pTOContext);
return(conection.Insert(pTOContext, sql.ToString(), parametros.ToArray()));
}
public static void GetFieldLengths()
{
if (GlobalSwitches.CachedMode)
{
return;
}
string query = "SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '" + ServerInfo.CurrentDataBase.ToString() + "'";
using (var results = DBFactory.GetDatabase().DataTableFromQueryString(query))
{
foreach (DataRow row in results.Rows)
{
if (!(row["CHARACTER_MAXIMUM_LENGTH"] is DBNull))
{
if (!columnLengths.ContainsKey(row["COLUMN_NAME"].ToString()))
{
columnLengths.Add(row["COLUMN_NAME"].ToString(), Convert.ToInt32(row["CHARACTER_MAXIMUM_LENGTH"]));
}
}
}
}
}
private static void DropTables()
{
string query = "SELECT * FROM sqlite_master WHERE type='table'";
using (var trans = DBFactory.GetSqliteDatabase().StartTransaction())
using (var conn = trans.Connection)
using (var results = DBFactory.GetSqliteDatabase().DataTableFromQueryString(query))
{
try
{
foreach (DataRow row in results.Rows)
{
string dropQuery = "DROP TABLE " + row["name"];
DBFactory.GetSqliteDatabase().ExecuteNonQuery(dropQuery, trans);
}
trans.Commit();
}
catch
{
trans.Rollback();
}
}
}
private void SetDisplayYears()
{
rebuildingCombo = true;
using (var results = DBFactory.GetDatabase().DataTableFromQueryString(Queries.SelectSibiDisplayYears))
{
var years = new List <string>();
years.Add("All");
foreach (DataRow row in results.Rows)
{
var yr = DateTime.Parse(row[SibiRequestCols.CreateDate].ToString()).Year.ToString();
if (!years.Contains(yr))
{
years.Add(yr);
}
}
DisplayYearComboBox.DataSource = years;
DisplayYearComboBox.SelectedIndex = 0;
rebuildingCombo = false;
}
}
public List <GnLocal> GetGnLocal(int pai_codi, int reg_codi, int dep_codi, int mun_codi)
{
StringBuilder sql = new StringBuilder();
sql.Append(" SELECT LOC_NOMB, LOC_CODI");
sql.Append(" FROM GN_LOCAL WHERE PAI_CODI=@PAI_CODI ");
sql.Append(" AND REG_CODI=@REG_CODI ");
sql.Append(" AND DEP_CODI=@DEP_CODI ");
sql.Append(" AND MUN_CODI=@MUN_CODI ");
sql.Append(" ORDER BY LOC_NOMB ");
List <Parameter> sqlParams = new List <Parameter>();
sqlParams.Add(new Parameter("PAI_CODI", pai_codi));
sqlParams.Add(new Parameter("REG_CODI", reg_codi));
sqlParams.Add(new Parameter("DEP_CODI", dep_codi));
sqlParams.Add(new Parameter("MUN_CODI", mun_codi));
OTOContext pTOContext = new OTOContext();
var conection = DBFactory.GetDB(pTOContext);
List <GnLocal> data = conection.ReadList(pTOContext, sql.ToString(), Make, sqlParams.ToArray());
return(data);
}
private IEnumerable <Channel> Get(string command)
{
foreach (var record in DBFactory.SQL_ExecuteReader(WebApiConfig.ConnectionString, command, System.Data.CommandType.Text))
{
var chan = new Channel();
var clCtrl = new ChannelLogoController();
chan.ServiceID = int.Parse(record.GetString(0));
chan.ChannelNumber = record.GetInt32(1);
chan.CallSign = record.GetString(2);
chan.StationName = record.GetString(3);
chan.Logo = clCtrl.getLogoInfoAsync(record.GetInt32(4)).Result ?? clCtrl.getLogoInfoAsync(record.GetInt32(4), false).Result;
chan.Version = record.GetString(5);
chan.VHO = record.GetString(6);
chan.LastUpdate = record.IsDBNull(7) ? (DateTime?)null : record.GetDateTime(7);
chan.RegionID = int.Parse(record.GetString(8));
chan.RegionName = record.IsDBNull(9) ? null : record.GetString(9);
chan.StationDescription = record.IsDBNull(10) ? null : record.GetString(10);
chan.UniqueString = record.IsDBNull(11) ? null : record.GetString(11);
yield return(chan);
}
}
/// <summary>
/// 得到指定条件的一个数据
/// </summary>
/// <param name="TableName"></param>
/// <param name="ColumnName"></param>
/// <param name="KeyColumnName"></param>
/// <param name="KeyValue"></param>
/// <returns></returns>
public static Object GetSpeciaRecord(String TableName, String ColumnName, String[] KeyColumnName, Object[] KeyValue)
{
//sql生成
SelectDBCommandBuilder sql = new SelectDBCommandBuilder();
sql.AddSelectTable(TableName);
sql.AddSelectColumn(ColumnName);
for (int i = 0; i < KeyColumnName.Length; i++)
{
sql.AddWhere(WhereRelation.And, KeyColumnName[i], DatabaseMaster.CommandComparison.Equals, KeyValue[i]);
}
//数据库连接
DatabaseInterface database = DBFactory.CreateDatabase(DatabaseInit.DefaultDatabase, DatabaseInit.ConnectName, DatabaseInit.EncryptType);
database.Open();
Object result = database.GetSpeciaRecordValue(sql.BuildCommand(), DatabaseInit.WaitTimeout);
database.Close();
return(result);
}
/// <summary>
/// 判断题目中有没有该选项
/// </summary>
/// <param name="TitleInfoId">题目ID</param>
/// <param name="TitleItemId">选项ID</param>
/// <returns></returns>
public bool IsExistTitleItemAssoc(int TitleInfoId, int TitleItemId)
{
string strSql = "select 1 from TitleItemAssoc where TitleInfoId=@TitleInfoId and TitleItemId=@TitleItemId";
DataTable dt = DBFactory.GetDB(DBType.SQLITE, m_strConn).ExecuteStrSql(strSql, new DbParameter[] {
new SQLiteParameter()
{
Value = TitleInfoId, ParameterName = "@TitleInfoId"
},
new SQLiteParameter()
{
Value = TitleItemId, ParameterName = "@TitleItemId"
}
});
if (dt != null && dt.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
public void updatePositions(DataSet1 dataSet1)
{
PositionDataAccessor DApos = new PositionDataAccessor();
AbstractConnection absConnection = null;
AbstractTransaction absTransaction = null;
try
{
absConnection = DBFactory.createConnection();
absConnection.open();
absTransaction = absConnection.beginTransaction();
DApos.Update(absConnection, absTransaction, dataSet1);
absTransaction.commit();
}
catch (Exception e)
{
absTransaction.rollback();
}
finally
{
absConnection.close();
}
}
public int AddTitleItemAssoc(TitleItemAssoc info)
{
string strSql = "insert into TitleItemAssoc (TitleInfoId,TitleItemId,TitleItemIndex,UpdateDateTime) values (@TitleInfoId,@TitleItemId,@TitleItemIndex,@UpdateDateTime)";
return(DBFactory.GetDB(DBType.SQLITE, m_strConn).ExecuteNonQuery(strSql, new DbParameter[] {
new SQLiteParameter()
{
Value = info.TitleInfoId, ParameterName = "@TitleInfoId"
},
new SQLiteParameter()
{
Value = info.TitleItemId, ParameterName = "@TitleItemId"
},
new SQLiteParameter()
{
Value = info.TitleItemIndex, ParameterName = "@TitleItemIndex"
},
new SQLiteParameter()
{
Value = info.UpdateDateTime, ParameterName = "@UpdateDateTime"
}
}));
}
/// <summary>
/// Test database connection
/// </summary>
private void btnTestDBConnection_Click(object sender, EventArgs e)
{
string dbServer = this.txtHost.Text + "," + this.txtPort.Text;
string dbDatabase = this.txtDatabaseName.Text;
string dbUser = this.txtUser.Text;
string dbPassword = this.txtPassword.Text;
string connString = "Data Source=" + dbServer + ";Initial Catalog=" + dbDatabase + ";Persist Security Info=True;User ID=" + dbUser + ";PWD=" + dbPassword;
DbConnection conn = DBFactory.GetConnection("MSSQL", connString);
conn.Open();
if (conn.State == ConnectionState.Open)
{
MessageBox.Show("Connect database success!");
}
else
{
MessageBox.Show("Fialed to connect database!");
}
conn.Close();
conn = null;
}
public int?InsertarGnRadju(TO.TOGnRadju radju)
{
DateTime date = DateTime.Now;
StringBuilder sql = new StringBuilder();
sql.Append(" INSERT INTO GN_RADJU ");
sql.Append(" (AUD_ESTA,AUD_USUA,AUD_UFAC,EMP_CODI,RAD_CONT,PRO_CODI,RAD_LLAV ,RAD_TABL )");
sql.Append(" VALUES(@AUD_ESTA,@AUD_USUA,@AUD_UFAC,@EMP_CODI,@RAD_CONT ,@PRO_CODI,@RAD_LLAV ,@RAD_TABL) ");
List <Parameter> parametros = new List <Parameter>();
parametros.Add(new Parameter("@AUD_ESTA ", "A"));
parametros.Add(new Parameter("@AUD_USUA", "Seven"));
parametros.Add(new Parameter("@AUD_UFAC", date));
parametros.Add(new Parameter("@EMP_CODI", radju.emp_codi)); //pendiente
parametros.Add(new Parameter("@RAD_TABL", "PQ_INPQR"));
parametros.Add(new Parameter("@PRO_CODI", "SPQINPQR"));
parametros.Add(new Parameter("@RAD_LLAV", radju.rad_llav));
parametros.Add(new Parameter("@RAD_CONT", radju.rad_cont));
OTOContext pTOContext = new OTOContext();
var conection = DBFactory.GetDB(pTOContext);
return(conection.Insert(pTOContext, sql.ToString(), parametros.ToArray()));
}
private void LoadCurrentScanItems(List <string> filterList = null)
{
if (currentScan == null)
{
return;
}
var query = "";
if (filterList != null)
{
query = Queries.Sqlite.SelectAllAssetDetailsWithLocationFilter(filterList);
}
else
{
query = Queries.Sqlite.SelectAllAssetDetails();
}
using (var detailResults = DBFactory.GetSqliteScanDatabase(currentScan.ID).DataTableFromQueryString(query))
{
view.LoadScanItems(detailResults);
}
}
/// <summary>
/// 删除试卷与题目的对应关系
/// </summary>
/// <param name="factory">数据库连接工厂</param>
/// <param name="paperid">试卷ID</param>
/// <param name="bankid">题目ID</param>
/// <returns></returns>
public bool Delete(DBFactory factory, int?paperid, int?bankid)
{
tbl_paper_bank model = new tbl_paper_bank()
{
};
if (paperid == null && bankid == null)
{
throw new Exception("试卷ID和题目ID不能同时为空");
}
if (paperid != null)
{
model.paperid = paperid;
}
if (bankid != null)
{
model.bankid = bankid;
}
factory.Delete(factory.Connection(), factory.Transaction(), model);
return(true);
}
static void Main(string[] args)
{
pool = DBFactory.CreateMySqlPool("Database=jcpt_auth;Data Source=10.1.55.44;Port=3306;User Id=auggie;Password=123456;Charset=utf8;Pooling=false", TimeOut: 20);
for (int i = 0; i < 20; i++)
{
int curTask = i;
Task.Factory.StartNew(() =>
{
RunAct(curTask);
});
}
bool first = false;
while (true)
{
Console.WriteLine("当前连接数：" + pool.CurrentCount);
if (pool.CurrentCount == 20)
{
first = true;
}
if (first && pool.CurrentCount < 5)
{
first = false;
for (int i = 0; i < 10; i++)
{
int curTask = i;
Task.Factory.StartNew(() =>
{
RunAct(curTask);
});
}
}
System.Threading.Thread.Sleep(1000);
}
}
private void TreeViewDrop(object sender, DragEventArgs e)
{
try
{
e.Effects = DragDropEffects.None;
e.Handled = true;
var item = GetNearestContainer(e.OriginalSource as UIElement);
var data = e.Data.GetData(Constants.DATA_FORMAT) as XElement;
if (data == null) return;
if (!CheckValidDrop(item, data)) return;
var xTarget = item.DataContext as XElement;
var parentId = xTarget.GetAttributeValue(Constants._ID);
data.SetAttributeValue(Constants.PARENT_ID, parentId);
if (!DBFactory.GetData().Save(data))
{
MessageBox.Show("update Tree item Failed.");
}
else
{
//sender to find the source item, then delete it
var toDelete = FindItemOnTree((sender as TreeView), Constants._ID,
data.GetAttributeValue(Constants._ID));
if (toDelete != null)
{
var parent = toDelete.Parent as TreeViewItem;
if (parent != null) parent.Items.Remove(toDelete);
}
item.Items.Add(data.GetTreeViewItemFromXElement());
}
}
catch (Exception ex)
{
MessageBox.Show(ExceptionHelper.FormatStackTrace("Error Happen:\n", ex));
}
}
private async void CheckServer()
{
DbSettings settings = DbSettings.Get();
string connectionString = settings.GetConnectionString();
Kernel.DB = DBFactory.Create(ServerType.SqlServer, connectionString);
if (Kernel.DB == null)
{
Application.Current.Dispatcher.Invoke(ShowErrorPanel);
}
else
{
bool connectionSucceed = Kernel.DB.CheckServer();
if (connectionSucceed)
{
await Task.Delay(2500);
Application.Current.Dispatcher.Invoke(() =>
{
LoginViewModel loginViewModel = new LoginViewModel();
LoginWindow loginWindow = new LoginWindow();
loginWindow.DataContext = loginViewModel;
loginViewModel.Window = loginWindow;
Close();
loginWindow.ShowDialog();
});
}
else
{
Application.Current.Dispatcher.Invoke(ShowErrorPanel);
}
}
}
/// <summary>
/// 使用主键获取表中一行数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
public T getOne <T>(int id) where T : new()
{
try
{
StringBuilder commandText = new StringBuilder();
commandText.Append("select * from ");
commandText.Append(databaseTableName);
commandText.Append(" where ");
commandText.Append(databaseTableName);
commandText.Append("ID=@id;");
List <Parameter> parameters = new List <Parameter>();
parameters.Add(new Parameter {
name = "id", value = id
});
DataTable dt = DBFactory.GetInstance().ExecuteQuery(commandText.ToString(), parameters);
//检查表中是否有数据，
if (!checkDataTable(dt))
{
return(default(T));
}
T t = new T();
setT <T>(ref t, dt.Rows[0]);
return(t);
}
catch (Exception e)
{
//如果出现异常
//记录
Log.LogInfo("getOne<" + typeof(T).Name + ">:", e);
//返回默认值列表
return(default(T));
}
}
/// <summary>
/// Returns a list of controls whose value differs from the previous historical state.
/// </summary>
/// <param name="currentData"></param>
/// <returns></returns>
private List <Control> GetChangedFields(DataTable currentData)
{
var changedControls = new List <Control>();
var currentTimeStamp = (System.DateTime)currentData.Rows[0][HistoricalDevicesCols.ActionDateTime];
// Query for all rows with a timestamp older than the current historical entry.
using (var olderData = DBFactory.GetDatabase().DataTableFromQueryString(Queries.SelectDevHistoricalEntriesOlderThan(deviceGuid, currentTimeStamp)))
{
if (olderData.Rows.Count > 0)
{
// Declare the current and previous DataRows.
DataRow previousRow = olderData.Rows[0];
DataRow currentRow = currentData.Rows[0];
var changedColumns = new List <string>();
// Iterate through the current row item array and compare them to the previous row items.
for (int i = 0; i <= currentRow.ItemArray.Length - 1; i++)
{
if (previousRow[i].ToString() != currentRow[i].ToString())
{
//Add column names to a list if the item values don't match.
changedColumns.Add(previousRow.Table.Columns[i].ColumnName);
}
}
// Get a list of all the controls with DBControlInfo tags.
var controlList = controlParser.GetDBControls(this);
// Get a list of all the controls whose data columns match the ChangedColumns.
foreach (string col in changedColumns)
{
changedControls.Add(controlList.Find(c => ((DBControlInfo)c.Tag).ColumnName == col));
}
}
}
return(changedControls);
}
public List <WF_SEGUI> SEListaFlujosDetalle(short pEMP_CODI, int pCAS_CONT, string pCOD_EMPL, int pSEG_CONT)
{
try
{
StringBuilder builder = new StringBuilder();
builder.AppendLine(" SELECT WF_SEGUI.EMP_CODI, WF_SEGUI.CAS_CONT, WF_SEGUI.SEG_CONT, WF_SEGUI.SEG_CONA, ");
builder.AppendLine(" WF_SEGUI.FLU_CONT, WF_SEGUI.ETA_CONT, WF_SEGUI.SEG_SUBJ, WF_SEGUI.SEG_PRIO, ");
builder.AppendLine(" WF_SEGUI.SEG_FREC, WF_SEGUI.SEG_HREC, WF_SEGUI.SEG_FLIM, WF_SEGUI.SEG_HLIM, ");
builder.AppendLine(" WF_SEGUI.SEG_DIAE, WF_SEGUI.SEG_FCUL, WF_SEGUI.SEG_HCUL, WF_SEGUI.SEG_DIAR, ");
builder.AppendLine(" WF_SEGUI.SEG_DIAD, WF_SEGUI.SEG_ESTC, WF_SEGUI.SEG_ABRE, WF_SEGUI.SEG_UORI, ");
builder.AppendLine(" WF_SEGUI.SEG_UENC, WF_SEGUI.SEG_COME, WF_SEGUI.SEG_ESTE, WF_SEGUI.SEG_RECO, ");
builder.AppendLine(" WF_SEGUI.AUD_ESTA, WF_SEGUI.AUD_USUA, WF_SEGUI.AUD_UFAC, GN_USUAR.USU_NOMB, ");
builder.AppendLine(" WF_CASOS.CAS_DESC, WF_SEGUI.SEG_COME, WF_ETAPA.ETA_SSQL ");
builder.AppendLine(" FROM WF_SEGUI, GN_USUAR, WF_CASOS, WF_ETAPA ");
builder.AppendLine(" WHERE WF_SEGUI.EMP_CODI = WF_CASOS.EMP_CODI ");
builder.AppendLine(" AND WF_SEGUI.CAS_CONT = WF_CASOS.CAS_CONT ");
builder.AppendLine(" AND WF_SEGUI.SEG_UORI = GN_USUAR.USU_CODI ");
builder.AppendLine(" AND WF_SEGUI.EMP_CODI = WF_ETAPA.EMP_CODI ");
builder.AppendLine(" AND WF_SEGUI.FLU_CONT = WF_ETAPA.FLU_CONT ");
builder.AppendLine(" AND WF_SEGUI.ETA_CONT = WF_ETAPA.ETA_CONT ");
builder.AppendLine(" AND WF_SEGUI.SEG_ESTE = 'P' ");
builder.AppendLine(" AND WF_SEGUI.EMP_CODI = " + pEMP_CODI + " ");
builder.AppendLine(" AND WF_SEGUI.CAS_CONT = " + pCAS_CONT + " ");
builder.AppendLine(" AND UPPER(WF_SEGUI.SEG_UENC) = '" + pCOD_EMPL.ToUpper().Trim() + "' ");
builder.AppendLine(" AND WF_SEGUI.SEG_CONT = " + pSEG_CONT + " ");
builder.AppendLine(" ORDER BY WF_SEGUI.EMP_CODI, WF_SEGUI.SEG_PRIO, WF_SEGUI.CAS_CONT ");
OTOContext pTOContext = new OTOContext();
var conection = DBFactory.GetDB(pTOContext);
var objeto = conection.ReadList(pTOContext, builder.ToString(), Make, null);
return(objeto);
}
catch (Exception)
{
return(null);
}
}
/// <summary>
/// 摘要：对数据进行分页
/// </summary>
/// <param name="dataSetSql">查询语句（包括列名、表等信息，支持表关联）</param>
/// <param name="where">条件组，二维数据【0】为包括字段名的语句段模板，【1】为取值</param>
/// <param name="orderByColumn">排序列，二维数据【0】为字段名，【1】为ASC（升序）或DESC（降序）</param>
/// <param name="rowBegin">排序后数据集的行开始索引</param>
/// <param name="rowEnd">排序后数据集的行结束索引</param>
/// <returns></returns>
public DataTable GetDataSet(string dataSetSql, List <string[]> where, List <string[]> orderByColumn, int rowBegin, int rowEnd)
{
DataTable dt = new DataTable();
string stTableName = string.Empty;
try
{
T obj = (T)this.MemberwiseClone();
stTableName = ((TableAttribute)obj.GetType().GetCustomAttributes(true)[0]).TableName;
IDBCore iDB = null;
if (DATABASE_TYPE == "XML")
{
iDB = new XMLCore(_TagData);
}
else
{
iDB = DBFactory.CreateDB();
}
if ((rowBegin == -1 || rowEnd == -1) && orderByColumn != null)
{
dt = iDB.GetDataSet(dataSetSql, where, orderByColumn);
}
else if ((rowBegin == -1 || rowEnd == -1) && orderByColumn == null)
{
dt = iDB.GetDataSet(dataSetSql, where);
}
else
{
dt = iDB.GetDataSet(dataSetSql, where, orderByColumn, rowBegin, rowEnd);
}
}
catch (Exception ex)
{
dt = null;
}
return(dt);
}
public static async void RefreshCache()
{
await Task.Run(() =>
{
if (refreshRunning)
{
return;
}
refreshRunning = true;
using (var trans = DBFactory.GetSqliteDatabase().StartTransaction())
using (trans.Connection)
{
try
{
DropTables(trans);
foreach (var table in TablesToCache())
{
CacheDBTable(table.TableName, table.PrimaryKey, trans);
}
trans.Commit();
}
catch
{
trans.Rollback();
}
finally
{
refreshRunning = false;
}
}
});
}
public void GetAll()
{
//Setup();
UnitsDataAccessor daUnit = new UnitsDataAccessor();
DataSet1 dataSet1 = new DataSet1();
AbstractConnection connection = null;
AbstractTransaction transaction = null;
try
{
connection = DBFactory.createConnection();
connection.open();
transaction = connection.beginTransaction();
daUnit.Read(connection, transaction, dataSet1);
transaction.commit();
}
catch (Exception e)
{
transaction.rollback();
}
finally
{
connection.close();
}
List <DataRow> list = dataSet1.unit_of_measurement.Select().OfType <DataRow>().ToList();
list.Sort((x, y) => ((int)x["id"]).CompareTo((int)y["id"])); //сортируем по ид
Assert.That(1, Is.EqualTo(1));
Assert.That(list.Count, Is.EqualTo(15));
Assert.That((int)(list[0]["id"]), Is.EqualTo(4));
Assert.That((string)(list[0]["unit_name"]), Is.EqualTo("Миллиметр"));
Assert.That((string)(list[0]["code"]), Is.EqualTo("003"));
Assert.That((string)(list[0]["national_symbol"]), Is.EqualTo("мм"));
}
/// <summary>
///
/// </summary>
/// <param name="info">修改条件：TitleInfoId</param>
/// <returns></returns>
public int UpdateTitleInfo(TitleInfo info)
{
string strSql = @"update TitleInfo set TitleConent=@TitleConent,TitleTypeId=@TitleTypeId,TypeId=@TypeId,
CorrectAnswer=@CorrectAnswer,UpdateDateTime=@UpdateDateTime,Score=@Score where TitleInfoId=@TitleInfoId";//
return(DBFactory.GetDB(DBType.SQLITE, m_strConn).ExecuteNonQuery(strSql, new DbParameter[] {
new SQLiteParameter()
{
Value = info.TitleInfoId, ParameterName = "@TitleInfoId"
},
new SQLiteParameter()
{
Value = info.TitleConent, ParameterName = "@TitleConent"
},
new SQLiteParameter()
{
Value = info.TitleTypeId, ParameterName = "@TitleTypeId"
},
new SQLiteParameter()
{
Value = info.TypeId, ParameterName = "@TypeId"
},
new SQLiteParameter()
{
Value = info.CorrectAnswer, ParameterName = "@CorrectAnswer"
},
new SQLiteParameter()
{
Value = info.Score, ParameterName = "@Score"
},
new SQLiteParameter()
{
Value = info.UpdateDateTime, ParameterName = "@UpdateDateTime"
}
}));
}
/// <summary>
/// 更新表中新数据
/// </summary>
/// <param name="TableName"></param>
/// <param name="ColumnName"></param>
/// <param name="Value"></param>
/// <returns></returns>
public static int UpdateNewDataToTable(String TableName, String[] ColumnName, Object[] Value, String[] KeyColumnName, DatabaseMaster.CommandComparison[] comparison, Object[] KeyValue)
{
//sql生成
UpdateDBCommandBuilder sql = new UpdateDBCommandBuilder();
sql.TableName = TableName;
sql.AddUpdateColumn(ColumnName, Value);
for (int i = 0; i < KeyColumnName.Length; i++)
{
sql.AddWhere(WhereRelation.And, KeyColumnName[i], (DatabaseMaster.CommandComparison)comparison[i], KeyValue[i]);
}
//数据库连接
DatabaseInterface database = DBFactory.CreateDatabase(DatabaseInit.DefaultDatabase, DatabaseInit.ConnectName, DatabaseInit.EncryptType);
database.Open();
int result = database.ExecueCommand(sql.BuildCommand(), DatabaseInit.WaitTimeout);
database.Close();
return(result);
}
public void Save(EscolaViewModel formModel)
{
var context = DBFactory.GetInstance().GetDb();
Escola escola;
if (formModel.Id.HasValue)
{
escola = GetById(formModel.Id.Value, context);
}
else
{
escola = new Escola();
context.Escola.Add(escola);
}
escola.Logradouro = formModel.Logradouro;
escola.Nome = formModel.Nome;
escola.Numero = formModel.Numero;
escola.Telefone = formModel.Telefone;
escola.Complemento = formModel.Complemento;
escola.Cidade = formModel.Cidade;
escola.Diretor = formModel.Diretor;
escola.Estado = formModel.Estado;
context.SaveChanges();
}
public int InseCTREVDO(int emp_codi, int rev_cont, List <CtDocpr> ctrevdo)
{
DateTime date = DateTime.Now;
int docNreg = 0;
foreach (CtDocpr doc in ctrevdo)
{
docNreg += 1;
StringBuilder sql = new StringBuilder();
int doc_cont = GetCont("CT_REVDO", "DOC_CONT", emp_codi);
sql.Append(" INSERT INTO CT_REVDO(AUD_ESTA, AUD_USUA, AUD_UFAC, EMP_CODI, DOC_CONT, REV_CONT, PRO_NREG, PRO_DDOC, PRO_FENT, PRO_FVEN, PRO_OBSE, REV_APRO) ");
sql.Append(" VALUES(@AUD_ESTA, @AUD_USUA, @AUD_UFAC, @EMP_CODI, @DOC_CONT, @REV_CONT, @PRO_NREG, @PRO_DDOC, @PRO_FENT, @PRO_FVEN, @PRO_OBSE, @REV_APRO) ");
List <Parameter> parametros = new List <Parameter>();
parametros.Add(new Parameter("@AUD_ESTA", "A"));
parametros.Add(new Parameter("@AUD_USUA", "SEVEN"));
parametros.Add(new Parameter("@AUD_UFAC", date));
parametros.Add(new Parameter("@EMP_CODI", emp_codi));
parametros.Add(new Parameter("@DOC_CONT", doc_cont));
parametros.Add(new Parameter("@REV_CONT", rev_cont));
parametros.Add(new Parameter("@PRO_NREG", docNreg));
parametros.Add(new Parameter("@PRO_DDOC", doc.pro_ddoc));
parametros.Add(new Parameter("@PRO_FENT", doc.pro_fent));
parametros.Add(new Parameter("@PRO_FVEN", doc.pro_fven));
parametros.Add(new Parameter("@PRO_OBSE", doc.pro_obse));
parametros.Add(new Parameter("@REV_APRO", doc.rev_apro));
OTOContext pTOContext = new OTOContext();
var conection = DBFactory.GetDB(pTOContext);
conection.Insert(pTOContext, sql.ToString(), parametros.ToArray());
GuardarArchivo(rev_cont, emp_codi, doc.pro_adju, doc.fil_name);
}
return(0);
}
public List <ExamTitleInfo> GetExamTitleInfo(int ExercisesTestId, int StudentId)
{
//c.Score 该题分数非学生得分
// string strSql = @"select c.TitleInfoId,c.TitleConent,c.CorrectAnswer,c.Score,c.ExercisesTitleIndex,c.ExercisesTestId,d.StudentAnswer from
//(select b.TitleInfoId,b.TitleConent,b.CorrectAnswer,b.Score,a.ExercisesTitleIndex,a.ExercisesTestId from ExercisesTitle a,TitleInfo b
//where a.ExercisesTestId=@ExercisesTestId and a.TitleInfoId=b.TitleInfoId order by a.ExercisesTitleIndex asc) c
//left join StudentExaminationPaper d on c.ExercisesTestId=d.ExercisesTestId and c.TitleInfoId=d.TitleInfoId and d.StudentExamId=@StudentExamId";
string strSql = @"select c.TitleInfoId,c.TitleConent,c.CorrectAnswer,c.Score,c.ExercisesTitleIndex,c.ExercisesTestId,d.StudentAnswer,e.TitleTypeName from
(select b.TitleInfoId,b.TitleConent,b.CorrectAnswer,b.Score,a.ExercisesTitleIndex,a.ExercisesTestId,b.TitleTypeId from ExercisesTitle a,TitleInfo b
where a.ExercisesTestId=@ExercisesTestId and a.TitleInfoId=b.TitleInfoId order by a.ExercisesTitleIndex asc) c
left join TitleType e on c.TitleTypeId=e.TiteTypeId
left join StudentExaminationPaper d on c.ExercisesTestId=d.ExercisesTestId and c.TitleInfoId=d.TitleInfoId and d.StudentExamId=@StudentExamId";
return(DataTableToExamTitleInfo(DBFactory.GetDB(DBType.SQLITE, m_strConn).ExecuteStrSql(strSql, new DbParameter[] {
new SQLiteParameter()
{
Value = ExercisesTestId, ParameterName = "@ExercisesTestId"
},
new SQLiteParameter()
{
Value = StudentId, ParameterName = "@StudentExamId"
}
})));
}
public int?insertGnAdju(TOGnAdjun adjun)
{
DateTime date = DateTime.Now;
StringBuilder sql = new StringBuilder();
sql.Append("INSERT INTO GN_ADJUN ");
sql.Append(" (EMP_CODI,RAD_CONT,ADJ_CONT,ADJ_NOMB,AUD_ESTA,AUD_USUA,AUD_UFAC,ADJ_TIPO) ");
sql.Append(" VALUES (@EMP_CODI,@RAD_CONT,@ADJ_CONT,@ADJ_NOMB,@AUD_ESTA,@AUD_USUA,@AUD_UFAC,@ADJ_TIPO) ");
List <Parameter> parametros = new List <Parameter>();
parametros.Add(new Parameter("@EMP_CODI ", adjun.Emp_Codi));
parametros.Add(new Parameter("@RAD_CONT", adjun.Rad_Cont));
parametros.Add(new Parameter("@ADJ_CONT", adjun.Adj_Cont));
parametros.Add(new Parameter("@ADJ_NOMB", adjun.Adj_Nomb)); //pendiente
parametros.Add(new Parameter("@AUD_ESTA", "A"));
parametros.Add(new Parameter("@AUD_USUA", adjun.Aud_Usua));
parametros.Add(new Parameter("@AUD_UFAC", DateTime.Now));
parametros.Add(new Parameter("@ADJ_TIPO", adjun.Adj_Tipo));
OTOContext pTOContext = new OTOContext();
var conection = DBFactory.GetDB(pTOContext);
return(conection.Insert(pTOContext, sql.ToString(), parametros.ToArray()));
}
private void TestResultTreeSelectedItemChanged(object sender, RoutedPropertyChangedEventArgs <object> e)
{
var selected = TestResultTree.SelectedItem as TreeViewItem;
if (selected == null)
{
return;
}
var parent = selected.DataContext as XElement;
if (parent == null)
{
return;
}
var parentId = parent.GetAttributeValue(Constants._ID);
var xRoot = DBFactory.GetData().GetChildren(parentId);
Log.Debug(xRoot.ToString());
if (parent.Name.ToString().Equals(Constants.RESULT) || parent.Name.ToString().Contains("." + Constants.RESULT))
{
//load its children to TestCaseResultTable
LoadResultTableOfTreeViewItem(selected, xRoot);
}
}
void TestR8()
{
"\nCreation of immutable database".title();
WriteLine();
try
{
var keys = qEngine.getListKeyPattern();
if (keys != null)
{
foreach (var key in keys)
{
var val = db.getValueOfKey(key);
dictFactory.Add(key, val); //add keys and values to the dictionary
}
dbFactory = new DBFactory<int, DBElement<int, string>>(dictFactory); //store the dictionary in the
WriteLine("\nThe below key/value pairs obtained from query which fetch even keys are saved as an immutable database\n");
dbFactory.showDB(); //display the immutable database
WriteLine();
WriteLine();
}
else
{
WriteLine("\nNo keys are obtained from a query for creation of immutable database\n");
}
WriteLine();
WriteLine();
}
catch (Exception e)
{
WriteLine("\n" + e.Message + "\n");
}
}
static void Main(string[] args)
{
"Testing DBFactory Package".title('=');
WriteLine();
"\nCreation of immutable database".title();
WriteLine();
"\nOriginal database".title();
DBEngine<int, DBElement<int, string>> db = new DBEngine<int, DBElement<int, string>>();
DBElement<int, string> elem1 = new DBElement<int, string>();
elem1.name = "India";
elem1.descr = "Country";
elem1.timeStamp = DateTime.Now;
elem1.children.AddRange(new List<int> { 2, 3 });
elem1.payload = "Famous cricket player";
db.insert(1, elem1);
DBElement<int, string> elem2 = new DBElement<int, string>();
elem2.name = "Roger federer";
elem2.descr = "Tennis player";
elem2.timeStamp = DateTime.Now.AddDays(-15);
elem2.children.AddRange(new List<int> { 3 });
elem2.payload = "Famous tennis player";
db.insert(2, elem2);
DBElement<int, string> elem3 = new DBElement<int, string>();
elem3.name = "Usain Bolt";
elem3.descr = "Athelte";
elem3.timeStamp = DateTime.Now;
elem3.children.AddRange(new List<int> { 1 });
elem3.payload = "Fastest in the world";
db.insert(3, elem3);
DBElement<int, string> elem4 = new DBElement<int, string>();
elem4.name = "Saina Nehwal";
elem4.descr = "Badminton Player";
elem4.timeStamp = DateTime.Now;
elem4.children.AddRange(new List<int> { 2 });
elem4.payload = "Famous badminton player";
db.insert(4, elem4);
db.showDB();
WriteLine();
"\n Fetch all the keys which are even from the above database".title();
try
{
QueryEngine<int, DBElement<int, string>> qEngine = new QueryEngine<int, DBElement<int, string>>(db);
Dictionary<int, DBElement<int, string>> dictFactory = new Dictionary<int, DBElement<int, string>>();
DBFactory<int, DBElement<int, string>> dbFactory;
var keys = qEngine.getListKeyPattern();
if (keys != null)
{
foreach (var key in keys)
{
var val = db.getValueOfKey(key);
dictFactory.Add(key, val); //add keys and values to the dictionary
}
dbFactory = new DBFactory<int, DBElement<int, string>>(dictFactory); //store the dictionary in the
WriteLine("\nThe below key/value pairs with even keys pattern are saved as an immutable database\n");
dbFactory.showDB(); //display the immutable database
WriteLine();
WriteLine();
}
else
{
WriteLine("\nNo keys are obtained from a query for creation of immutable database\n");
}
WriteLine();
WriteLine();
}
catch (Exception e)
{
Console.WriteLine("\n" + e.Message + "\n");
}
}
static void Main(string[] args)
{
"Testing DBFactory Package".title('=');
WriteLine();
DBFactoryTest dbft = new DBFactoryTest();
"\nCreation of immutable database".title();
WriteLine();
"\nOriginal database".title();
DBEngine<int, DBElement<int, string>> db = new DBEngine<int, DBElement<int, string>>();
dbft.insertData(db);
"\n Fetch all the keys which are even from the above database".title();
try
{
QueryEngine<int, DBElement<int, string>> qEngine = new QueryEngine<int, DBElement<int, string>>(db);
Dictionary<int, DBElement<int, string>> dictFactory = new Dictionary<int, DBElement<int, string>>();
DBFactory<int, DBElement<int, string>> dbFactory;
var keys = qEngine.getListKeyPattern();
if (keys != null)
{
foreach (var key in keys)
{
var val = db.getValueOfKey(key);
dictFactory.Add(key, val); //add keys and values to the dictionary
}
dbFactory = new DBFactory<int, DBElement<int, string>>(dictFactory); //store the dictionary in the
WriteLine("\nThe below key/value pairs with even keys pattern are saved as an immutable database\n");
dbFactory.showDB(); //display the immutable database
WriteLine();
WriteLine();
}
else
{
WriteLine("\nNo keys are obtained from a query for creation of immutable database\n");
}
WriteLine();
WriteLine();
}
catch (Exception e)
{
Console.WriteLine("\n" + e.Message + "\n");
}
}