private void btnGenInsertSql_Click(object sender, EventArgs e) { try { var helper = new DbHelper(DBGlobalService.Connection); var sql = this.textEditorControl1.Text.Trim(); var ds = helper.ExecuteDataSet(sql); var insertSql = "insert into " + ds.Tables[0].TableName; insertSql += "("; foreach (DataColumn column in ds.Tables[0].Columns) { insertSql += column.ColumnName; } insertSql += ")"; foreach (var row in ds.Tables[0].Rows) { } helper.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public void can_get_open_connection() { using (var cn = new DbHelper(_connectionString).GetConnection(InitialConnectionStates.Open)) { Assert.AreEqual(ConnectionState.Open, cn.State); } }
public void can_get_closed_connection() { using (var cn = new DbHelper(_connectionString).GetConnection(InitialConnectionStates.Closed)) { Assert.AreEqual(ConnectionState.Closed, cn.State); } }
public List<DataFilterValueEntity> GetDataFilterValuesByDataFilter(string dataFilter, string functionID) { // 1. Get available user data filter values from database. DataTable dt = new DataTable(); DbHelper helper = new DbHelper(); DbCommand command = helper.BuildDbCommand("P_IC_DATA_FILTER_VALUES_GET_BY_FUNCTION"); helper.AssignParameterValues( command, ExtendedMembership.ApplicationName, AppContext.Current.UserName, dataFilter, functionID ); helper.Fill(dt, command); // 2. Fill available data filter values into collcection (List<DataFilterValues>) List<DataFilterValueEntity> availableDataFilterValues = new List<DataFilterValueEntity>(); if (dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { availableDataFilterValues.Add( new DataFilterValueEntity( functionID, row["DATA_FILTER"].ToString(), row["DATA_FILTER_VALUE"].ToString(), row["DATA_FILTER_ID"].ToString(), row["DATA_FILTER_VALUE_ID"].ToString() )); } } return availableDataFilterValues; }
public OfficeDetailEntity GetOfficeDetails(string OrganisationalUnitID) { OfficeDetailEntity rOfficeDetailEntity = new OfficeDetailEntity(); DataTable dt = new DataTable(); DbHelper helper = new DbHelper(); DbCommand command = helper.BuildDbCommand("P_CM_OrganisationalUnitDetail_S"); helper.AssignParameterValues(command, OrganisationalUnitID); Helper.Fill(dt, command); if (dt.Rows.Count > 0) { rOfficeDetailEntity.OrganisationalUnitDetailsID = int.Parse(dt.Rows[0]["OrganisationalUnitDetailID"].ToString()); rOfficeDetailEntity.OrganisationalUnitID = dt.Rows[0]["OrganisationalUnitID"].ToString(); rOfficeDetailEntity.OrganisationalUnitName = dt.Rows[0]["OrganisationalUnitName"].ToString(); rOfficeDetailEntity.Address1 = dt.Rows[0]["Address1"].ToString(); rOfficeDetailEntity.Address2 = dt.Rows[0]["Address2"].ToString(); rOfficeDetailEntity.Address3 = dt.Rows[0]["Address3"].ToString(); rOfficeDetailEntity.Postcode = dt.Rows[0]["Postcode"].ToString(); rOfficeDetailEntity.Suburb = dt.Rows[0]["Suburb"].ToString(); rOfficeDetailEntity.State = dt.Rows[0]["State"].ToString(); rOfficeDetailEntity.CountryNameCode = dt.Rows[0]["CountryNameCode"].ToString(); rOfficeDetailEntity.PostalDeliveryNumber = dt.Rows[0]["PostalDeliveryNumber"].ToString(); rOfficeDetailEntity.StreetNumber1 = dt.Rows[0]["StreetNumber1"].ToString(); rOfficeDetailEntity.StreetNumber2 = dt.Rows[0]["StreetNumber2"].ToString(); rOfficeDetailEntity.StreetName = dt.Rows[0]["StreetName"].ToString(); rOfficeDetailEntity.LevelNumber = dt.Rows[0]["LevelNumber"].ToString(); rOfficeDetailEntity.UnitNumber = dt.Rows[0]["UnitNumber"].ToString(); rOfficeDetailEntity.PhoneNumber = dt.Rows[0]["PhoneNumber"].ToString(); rOfficeDetailEntity.FaxNumber = dt.Rows[0]["FaxNumber"].ToString(); rOfficeDetailEntity.EmailAddress = dt.Rows[0]["EmailAddress"].ToString(); rOfficeDetailEntity.PhoneNumberTollFree = dt.Rows[0]["PhoneNumberTollFree"].ToString(); } return rOfficeDetailEntity; }
public MainWindow() { InitializeComponent(); //LoadPath = System.Configuration.ConfigurationManager.AppSettings["LoadPath"]; // Db_Sqlite = new DbHelper(EncryptionString.Decrypto(System.Configuration.ConfigurationManager.AppSettings["SqlitePath"]), DbProvider.Sqlite);//连接sqlite数据库 Db_Sql = new DbHelper(EncryptionString.Decrypto(System.Configuration.ConfigurationManager.AppSettings["SqlPath"]), DbProvider.Sql); //连接sql数据 }
public List<VwEstagio> ObterListaEstagio(string numeroAES, int itemAES) { var estagios = new List<VwEstagio>(); DbHelper db = new DbHelper(); string cmdText = @" select Numero_AES, Item_AES, Mantenedora, Cod_Mantida, Mantida, Diretoria_Ensino, CodCurso, NomCurso, vagas, valorAluno, valor_hora_aula, area, esta.idMatricula, esta.RA, esta.NomeAluno, cargaHoraEstagio, sum(qtd_Horas_Estagio) as Total_Horas_Estagio from vw_estagio esta where 1=1"; if (!string.IsNullOrEmpty(numeroAES)) { cmdText += " and Numero_AES = @numeroAES"; db.AddParameter(new System.Data.SqlClient.SqlParameter("@numeroAES", numeroAES)); } if (itemAES > -1) { cmdText += " and Item_AES = @itemAES"; db.AddParameter(new System.Data.SqlClient.SqlParameter("@itemAES", itemAES)); } cmdText += @" group by Numero_AES, Item_AES, Mantenedora, Cod_Mantida, Mantida, Diretoria_Ensino, CodCurso, NomCurso, vagas, valorAluno, valor_hora_aula, area, idMatricula, RA, NomeAluno, cargaHoraEstagio"; SqlDataReader dr = db.GetDataReader(cmdText); if (dr.HasRows) { while (dr.Read()) { estagios.Add(new VwEstagio { NumeroAES = dr["Numero_AES"].ToString(), ItemAES = Convert.ToInt32(dr["Item_AES"]), NomeMantenedora = dr["Mantenedora"].ToString(), IdMantida = Convert.ToInt32(dr["Cod_Mantida"]), NomeMantida = dr["Mantida"].ToString(), NomeDiretoria = dr["Diretoria_Ensino"].ToString(), IdCurso = Convert.ToInt32(dr["CodCurso"]), NomeCurso = dr["NomCurso"].ToString(), NumeroVagas = Convert.ToInt32(dr["vagas"]), ValorAluno = Convert.ToDecimal(dr["valorAluno"]), ValorHoraAula = Convert.ToDecimal(dr["valor_hora_aula"]), Area = dr["area"].ToString(), IdMatricula = Convert.ToInt32(dr["idMatricula"]), NumeroRA = dr["RA"].ToString(), NomeAluno = dr["NomeAluno"].ToString(), CargaHorariaEstagio = Convert.ToInt32(dr["cargaHoraEstagio"]), TotalHoraEstagio = Convert.ToInt32(dr["Total_Horas_Estagio"]) }); } } db.CloseDbConnection(); return estagios; }
public SessionData CreateOrRenewSession(string sessionId,string userName, string fullName, string ipAddress, string hostName) { int timeoutMinutes = SessionSettings.GetServiceSetting().SessionTimeout; DbHelper dbHelper = new DbHelper(); DbCommand command = dbHelper.BuildDbCommand("P_IC_SESSIONS_CREATE_OR_RENEW"); dbHelper.AssignParameterValues(command, sessionId, userName, fullName, DateTime.Now, DateTime.Now, ipAddress, hostName, timeoutMinutes, DateTime.Now); dbHelper.ExecuteNonQuery(command); TimeSpan refreshInterval = new TimeSpan(0, 0, (int)(SessionSettings.GetServiceSetting().RefreshInterval * 60)); TimeSpan timeoutInterval = new TimeSpan(0, 0, (SessionSettings.GetServiceSetting().SessionTimeout * 60)); return new SessionData { SessionID = sessionId, RefreshInterval = refreshInterval, SessionTimeoutInterval = timeoutInterval }; }
public SessionData CheckSessionID(string sessionID, string userName) { int timeoutMinutes = SessionSettings.GetServiceSetting().SessionTimeout; DbHelper dbHelper = new DbHelper(); DbCommand comannd = dbHelper.BuildDbCommand("P_IC_SESSIONS_CHECK_USER_NAME"); dbHelper.AssignParameterValues(comannd, sessionID, userName, timeoutMinutes,DateTime.Now); dbHelper.ExecuteNonQuery(comannd); SessionData sessionData = new SessionData(); sessionData.IsKilled = (bool)dbHelper.GetParameterValue(comannd, "p_is_killed"); sessionData.IsSessionMatched = (bool)dbHelper.GetParameterValue(comannd, "p_user_session_matched"); sessionData.IsTimeoutOrInvalid = (bool)dbHelper.GetParameterValue(comannd, "p_is_timeout"); return sessionData; }
public void can_get_reader_from_sql_text() { var dbhelper = new DbHelper(_connectionString); int count = 0; using (var reader = dbhelper.ExecuteReader("select id, label from addresstype")) { while (reader.Read()) { count++; } } Assert.AreEqual(2, count); }
public List<DelegationFunctionEntity> GetAllDelegationFunctions() { List<DelegationFunctionEntity> lstDelegationFunctionEntity = new List<DelegationFunctionEntity>(); DataTable dtDelegationFunction = new DataTable(); DbHelper helper = new DbHelper(); DbCommand command = helper.BuildDbCommand("P_SS_DelegationFunction_S"); helper.AssignParameterValues(command); Helper.Fill(dtDelegationFunction, command); foreach (DataRow dr in dtDelegationFunction.Rows) { lstDelegationFunctionEntity.Add(new DelegationFunctionEntity(Convert.ToInt32(dr["DelegationFunctionID"].ToString()), dr["Name"].ToString(), dr["Description"].ToString())); } return lstDelegationFunctionEntity; }
public DataSet ObterDataSetEstagio(string chave) { DbHelper db = new DbHelper(); db.AddParameter(new System.Data.SqlClient.SqlParameter("@Chave", chave)); string cmdText = @" select Numero_AES, Item_AES, Mantenedora, Cod_Mantida, Mantida, Diretoria_Ensino, CodCurso, NomCurso, vagas, valorAluno, valor_hora_aula, area, esta.idMatricula, esta.RA, esta.NomeAluno, cargaHoraEstagio, sum(qtd_Horas_Estagio) as Total_Horas_Estagio from vw_estagio esta join Token tk on esta.Numero_AES = tk.AES where tk.Chave = @Chave group by Numero_AES, Item_AES, Mantenedora, Cod_Mantida, Mantida, Diretoria_Ensino, CodCurso, NomCurso, vagas, valorAluno, valor_hora_aula, area, idMatricula, RA, NomeAluno, cargaHoraEstagio"; DataSet ds = db.GetDataSet(cmdText); db.CloseDbConnection(); return ds; }
public List<string> GetActionsForUser(string userName) { string AppName = NCS.IConnect.Security.BusinessActions.ApplicationName; DataTable dt = new DataTable(); DbHelper helper = new DbHelper(); DbCommand command = helper.BuildDbCommand("P_IC_ACTIONS_GET_FOR_USER"); helper.AssignParameterValues( command, AppName, userName ); helper.Fill(dt, command); var list = new List<string>(); foreach (DataRow row in dt.Rows) { list.Add(row["ACTION_CODE"].ToString()); } return list; }
public List<DelegationValueEntity> FindDelegationValueEntity(DelegationSearchCriteria objDelegationSearchCriteria) { int showActive = objDelegationSearchCriteria.IsShowActive ?1:0; List<DelegationValueEntity> lstDelegationValueEntity = new List<DelegationValueEntity>(); DataTable dt = new DataTable(); DbHelper helper = new DbHelper(); DbCommand command = helper.BuildDbCommand("P_SS_DelegationValue_SEARCH"); helper.AssignParameterValues( command, objDelegationSearchCriteria.DelegationFunctionId, objDelegationSearchCriteria.AdminNotes, showActive ); Helper.Fill(dt, command); foreach (DataRow dr in dt.Rows) { DelegationValueEntity objDelegationValueEntity = new DelegationValueEntity(); objDelegationValueEntity.DelegationValueId = Convert.ToInt32(dr["DelegationValueID"].ToString()); objDelegationValueEntity.DelegationFunctionId = Convert.ToInt32(dr["DelegationFunctionID"].ToString()); objDelegationValueEntity.DelegationFunctionName = dr["DelegationName"].ToString(); objDelegationValueEntity.Branch = dr["Branch"].ToString(); objDelegationValueEntity.Unit = dr["Unit"].ToString(); objDelegationValueEntity.Subunit = dr["Subunit"].ToString(); objDelegationValueEntity.Grade = dr["Grade"].ToString(); objDelegationValueEntity.Act = dr["Act"].ToString(); objDelegationValueEntity.DelegationValue = dr["DelegationValue"].ToString(); objDelegationValueEntity.OperationalCondition = dr["OperationalConditions"].ToString(); objDelegationValueEntity.PolicyValue = dr["PolicyValue"].ToString(); objDelegationValueEntity.DelegationReference = dr["DelegationReference"].ToString(); objDelegationValueEntity.StartDate = Convert.ToDateTime(dr["EffectiveStartDate"].ToString()); objDelegationValueEntity.EndDate = Convert.ToDateTime(dr["EffectiveEndDate"].ToString()); lstDelegationValueEntity.Add(objDelegationValueEntity); } return lstDelegationValueEntity; }
static WorkDb() { s_db = new DbHelper( @"Data Source=RUFRW-OSHU\SQL2005; Initial Catalog=Work; Integrated Security=True;", TimeSpan.Parse("00:10:00")); }
private List<XElement> BackupDatabase(int tenant, ConnectionStringSettings connectionString, IDataWriteOperator writer) { var xml = new List<XElement>(); var errors = 0; var timeout = TimeSpan.FromSeconds(1); using (var dbHelper = new DbHelper(tenant, connectionString)) { var tables = dbHelper.GetTables(); for (int i = 0; i < tables.Count; i++) { var table = tables[i]; OnProgressChanged(table, (int)(i / (double)tables.Count * 100)); xml.Add(new XElement(table)); var stream = writer.BeginWriteEntry(string.Format("{0}\\{1}\\{2}", Name, connectionString.Name, table).ToLower()); DataTable dataTable = null; while (true) { try { dataTable = dbHelper.GetTable(table); break; } catch { errors++; if (20 < errors) throw; Thread.Sleep(timeout); } } foreach (DataColumn c in dataTable.Columns) { if (c.DataType == typeof(DateTime)) c.DateTimeMode = DataSetDateTime.Unspecified; } dataTable.WriteXml(stream, XmlWriteMode.WriteSchema); writer.EndWriteEntry(); } } return xml; }
private void RestoreDatabase(int tenant, ConnectionStringSettings connectionString, IEnumerable<XElement> elements, IDataReadOperator reader) { var dbName = connectionString.Name; var dbElement = elements.SingleOrDefault(e => string.Compare(e.Name.LocalName, connectionString.Name, true) == 0); if (dbElement != null && dbElement.Attribute("ref") != null) { dbName = dbElement.Attribute("ref").Value; dbElement = elements.Single(e => string.Compare(e.Name.LocalName, dbElement.Attribute("ref").Value, true) == 0); } if (dbElement == null) return; using (var dbHelper = new DbHelper(tenant, connectionString)) { var tables = dbHelper.GetTables(); for (int i = 0; i < tables.Count; i++) { var table = tables[i]; OnProgressChanged(table, (int)(i / (double)tables.Count * 100)); if (dbElement.Element(table) == null) continue; var stream = reader.GetEntry(string.Format("{0}\\{1}\\{2}", Name, dbName, table).ToLower()); var data = new DataTable(); data.ReadXml(stream); dbHelper.SetTable(data); } } }
private void btnGenerateMethod_Click(object sender, EventArgs e) { if (tabControl1.SelectedTab.Name == "tpSql") { // sql语句 Regex rg = new Regex("@[a-zA-z0-9_]+"); MatchCollection mc = rg.Matches(textBoxSql.Text.Trim()); List<string> listed_params = new List<string>(); clear_dataset(); DataTable dt = params_set.Tables["params"]; foreach (Match m in mc) { if (!listed_params.Contains(m.Value)) { DataRow dr = dt.NewRow(); string match_param = m.Value; dr[0] = match_param; dr[1] = "string"; dt.Rows.Add(dr); listed_params.Add(match_param); } } FormMethod fm = new FormMethod(); fm.ParentFormTb = this; fm.Type = "method"; fm.DataSource = params_set; fm.ShowDialog(); } else if (tabControl1.SelectedTab.Name == "tpPdr") { string procedure_name = textBoxPdr.Text.Trim(); // 存储过程 DbHelper db = new DbHelper(cnnstring); string Query = @"SELECT param.name AS [Name], ISNULL(baset.name, N'') AS [SystemType], CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [Length] FROM sys.all_objects AS sp INNER JOIN sys.all_parameters AS param ON param.object_id=sp.object_id LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = param.system_type_id and baset.user_type_id = baset.system_type_id WHERE (sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')and(sp.name= @procedurename and SCHEMA_NAME(sp.schema_id)=N'dbo') ORDER BY param.parameter_id ASC"; db.AddParameter("@procedurename", procedure_name); DataTable dtparams = db.ExecuteDataSet(Query, CommandType.Text).Tables[0]; List<string> listed_params = new List<string>(); clear_dataset(); DataTable dt = params_set.Tables["params"]; foreach (DataRow drp in dtparams.Rows) { if (!listed_params.Contains(drp["name"].ToString())) { DataRow dr = dt.NewRow(); dr[0] = drp["name"].ToString(); dr[1] = data_type_convert(drp["systemtype"].ToString()); dt.Rows.Add(dr); listed_params.Add(drp["name"].ToString()); } } FormMethod fm = new FormMethod(); fm.ParentFormTb = this; fm.Type = "procedure"; fm.DataSource = params_set; fm.ShowDialog(); } }
private void Table_Load(object sender, EventArgs e) { DbHelper db = new DbHelper(cnnstring); checkedListBox1.DataSource = db.ExecuteDataSet("select * from sys.tables").Tables[0]; checkedListBox1.ValueMember = "name"; checkedListBox1.DisplayMember = "name"; }
public void SaveOrganisationLookup(LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable dtLookupOrganisationalUnitDataTable) { string transactionid = NCS.IConnect.ApplicationContexts.ApplicationContextFactory.GetApplicationContext().TransactionId; DbHelper helper = new DbHelper(); DbCommand command = helper.BuildDbCommand("P_SS_LookupOrganisationalUnit_U"); helper.AssignParameterValues(command, Membership.ApplicationName, dtLookupOrganisationalUnitDataTable[0].OrganisationalUnitID, dtLookupOrganisationalUnitDataTable[0].OrganisationalUnitName, dtLookupOrganisationalUnitDataTable[0].VersionNo, transactionid, AppContext.Current.UserName.ToString(), DateTime.Now); Helper.ExecuteNonQuery(command); LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable dtOrgDetail = GetOrganisationalUnitDetailByOrgID(dtLookupOrganisationalUnitDataTable[0].OrganisationalUnitID); int? addressId = SaveAddress(dtLookupOrganisationalUnitDataTable,dtOrgDetail); SaveOrganisationLookupDetail(dtLookupOrganisationalUnitDataTable,dtOrgDetail,addressId); }
private string a_class(string tablename, bool is_class) { try { /* * * 获取数据库中该表的信息 * */ DbHelper db = new DbHelper(cnnstring); string Query = @"SELECT syscolumns.name AS ColumnName, systypes.name AS DataType, syscolumns.length AS ColumnLength, syscolumns.prec AS ColumnPrecision, IsPrimaryKey = CASE WHEN EXISTS ( SELECT 1 FROM sysobjects INNER JOIN sysindexes ON sysindexes.name = sysobjects.name INNER JOIN sysindexkeys ON sysindexes.id = sysindexkeys.id AND sysindexes.indid = sysindexkeys.indid WHERE xtype = 'PK' AND parent_obj = syscolumns.id AND sysindexkeys.colid = syscolumns.colid ) THEN 1 ELSE 0 END, syscolumns.isnullable AS IsNullable, sys.extended_properties.value AS ColumnDiscription --IsIdentity = CASE syscolumns.status -- WHEN 128 THEN 1 -- ELSE 0 -- END FROM syscolumns INNER JOIN systypes ON ( syscolumns.xtype = systypes.xtype AND systypes.name <> '_default_' AND systypes.name <> 'sysname' ) LEFT OUTER JOIN sys.extended_properties ON ( sys.extended_properties.major_id = syscolumns.id AND minor_id = syscolumns.colid ) WHERE syscolumns.id = ( SELECT id FROM sysobjects WHERE NAME = @TableName ) ORDER BY syscolumns.colid"; db.AddParameter("@TableName", tablename); DataTable dt = db.ExecuteDataSet(Query, CommandType.Text).Tables[0]; /* * * 组合成类 * */ string o = string.Empty; if (is_class) { write_class_head(ref o, tablename); write_insert_method(ref o, tablename, dt, 1); write_delete_method(ref o, tablename, dt, 1); write_update_method(ref o, tablename, dt, 1); write_get_method(ref o, tablename, dt, 1); write_end(ref o); } else { write_model_head(ref o, tablename); for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; append(ref o, "private " + data_type_convert(dr["datatype"].ToString()) + param_null_set(dr["datatype"].ToString(), dr["isnullable"].ToString()) + " _" + dr["columnname"].ToString() + ";", 1); } append(ref o, ""); for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; append(ref o, "public " + data_type_convert(dr["datatype"].ToString()) + param_null_set(dr["datatype"].ToString(), dr["isnullable"].ToString()) + " " + first_letter_uplower(dr["columnname"].ToString()), 1); append(ref o, "{", 1); append(ref o, "get{ return _" + dr["columnname"].ToString() + "; }", 2); append(ref o, "set{ _" + dr["columnname"].ToString() + " = value; }", 2); append(ref o, "}", 1); } write_end(ref o); } return o; } catch (Exception ex) { throw ex; } }
public List<VwAcompCargaHoraria> ObterListaCargaHoraria(string chave) { var lista = new List<VwAcompCargaHoraria>(); DbHelper db = new DbHelper(); db.AddParameter(new System.Data.SqlClient.SqlParameter("@Chave", chave)); string sql = @" with HorasRealizadas as (select Numero_AES, nomeCurso , Item_AES , idMatricula , ra , NomeAluno , mes_ref , Carga_Hora_Total , Carga_Mes, idCursoTurnoTurma , sum(horas_dia*qtd_presenca) Carga_Mes_Realizada from [dbo].[vw_frequencia] where Numero_AES in ( select AES from Token where chave = @Chave ) --and Item_AES = 4 --and mes_ref = '02/2014' --and idMatricula = 27916 group by Numero_AES , Item_AES , ra , NomeAluno , Carga_Hora_Total , Carga_Mes , mes_ref , idMatricula, idCursoTurnoTurma , nomeCurso) select Numero_AES, nomeCurso , Item_AES , idMatricula , ra , NomeAluno , format(max( cast(concat(substring(mes_ref,1,2),'/01/',substring(mes_ref,4,4)) as date ) ),'MM/yyyy') ult_mes_lanc , Carga_Hora_Total , (select sum(cutc.cargaHoraria) / 60 from [dbo].[CursoTurnoTurma] cutu , [dbo].[CursoTurnoTurmaCalendario] cutc where cutu.idCursoTurnoTurma = hora.idCursoTurnoTurma and cutu.idCursoTurnoTurma = cutc.idCursoTurnoTurma ) CargaRealizadaCurso , sum(Carga_Mes_Realizada) Carga_Realizada_Aluno from HorasRealizadas hora group by Numero_AES,nomeCurso , Item_AES , idMatricula , ra , NomeAluno , Carga_Hora_Total ,hora.idCursoTurnoTurma"; SqlDataReader dr = db.GetDataReader(sql); if (dr.HasRows) { while (dr.Read()) { lista.Add(new VwAcompCargaHoraria { Numero_AES = dr["Numero_AES"] != DBNull.Value ? dr["Numero_AES"].ToString() : "", Item_AES = dr["Item_AES"] != DBNull.Value ? dr["Item_AES"].ToString() : "", idMatricula = dr["idMatricula"] != DBNull.Value ? Convert.ToInt32(dr["idMatricula"]) : 0, ra = dr["ra"] != DBNull.Value ? dr["ra"].ToString() : "", NomeAluno = dr["NomeAluno"] != DBNull.Value ? dr["NomeAluno"].ToString() : "", ult_mes_lanc = dr["ult_mes_lanc"].ToString(), Carga_Hora_Total = dr["Carga_Hora_Total"] != DBNull.Value ? Convert.ToInt32(dr["Carga_Hora_Total"]) : 0, CargaRealizadaCurso = dr["CargaRealizadaCurso"] != DBNull.Value ? Convert.ToInt32(dr["CargaRealizadaCurso"]) : 0 , Carga_Realizada_Aluno = dr["Carga_Realizada_Aluno"] != DBNull.Value ? Convert.ToInt32(dr["Carga_Realizada_Aluno"]) : 0, NomeCurso = dr["nomeCurso"].ToString() }); } } db.CloseDbConnection(); return lista; }
public DataSet ObterDataSetCargaHoraria(string chave) { DbHelper db = new DbHelper(); db.AddParameter(new System.Data.SqlClient.SqlParameter("@Chave", chave)); string sql = @" with HorasRealizadas as (select Numero_AES, nomeCurso , Item_AES , idMatricula , ra , NomeAluno , mes_ref , Carga_Hora_Total , Carga_Mes, idCursoTurnoTurma , sum(horas_dia*qtd_presenca) Carga_Mes_Realizada from [dbo].[vw_frequencia] where Numero_AES in ( select AES from Token where chave = @Chave ) --and Item_AES = 4 --and mes_ref = '02/2014' --and idMatricula = 27916 group by Numero_AES , Item_AES , ra , NomeAluno , Carga_Hora_Total , Carga_Mes , mes_ref , idMatricula, idCursoTurnoTurma , nomeCurso) select Numero_AES, nomeCurso , Item_AES , idMatricula , ra , NomeAluno , format(max( cast(concat(substring(mes_ref,1,2),'/01/',substring(mes_ref,4,4)) as date ) ),'MM/yyyy') ult_mes_lanc , Carga_Hora_Total , (select sum(cutc.cargaHoraria) / 60 from [dbo].[CursoTurnoTurma] cutu , [dbo].[CursoTurnoTurmaCalendario] cutc where cutu.idCursoTurnoTurma = hora.idCursoTurnoTurma and cutu.idCursoTurnoTurma = cutc.idCursoTurnoTurma ) CargaRealizadaCurso , sum(Carga_Mes_Realizada) Carga_Realizada_Aluno from HorasRealizadas hora group by Numero_AES,nomeCurso , Item_AES , idMatricula , ra , NomeAluno , Carga_Hora_Total ,hora.idCursoTurnoTurma"; DataSet ds = db.GetDataSet(sql); db.CloseDbConnection(); return ds; }
public void SaveOrganisationLookup(string OrganisationalUnitID, string OrganisationName, int VersionNumber) { string transactionid = NCS.IConnect.ApplicationContexts.ApplicationContextFactory.GetApplicationContext().TransactionId; DbHelper helper = new DbHelper(); DbCommand command = helper.BuildDbCommand("P_SS_LookupOrganisationalUnit_U"); helper.AssignParameterValues(command, Membership.ApplicationName, OrganisationalUnitID, OrganisationName, VersionNumber, transactionid, AppContext.Current.UserName.ToString(), DateTime.Now ); Helper.ExecuteNonQuery(command); }
static TPD() { s_db = new DbHelper( ConfigurationHelper.ReadConnectionString("TPD"), ConfigurationHelper.ReadTimeSpan("TPD.Timeout")); }
/// <summary> /// 创建相应的文件和文件夹 /// </summary> private string CreateFile() { string path = this.BrowseName.Text.Trim(); if (string.IsNullOrEmpty(path)) { //WriteLog("请选择文件夹"); return ""; } if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } path = path + @"\UpdateFile"; if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } string SouPath = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Data.db"); string DisPath = path + @"\Data.db"; if (!Directory.Exists(DisPath)) { System.IO.File.Copy(SouPath, DisPath, true); } string Connect = @"Data Source=" + DisPath + @";Initial Catalog=T_Company;Persist Security Info=True;"; Db_Sqlite = new DbHelper(Connect, DbProvider.Sqlite);//连接sqlite数据库 return path; }
public LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable GetOrganisationLookup() { LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable LookupOrganisationalUnitDataTable = new LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable(); DbHelper helper = new DbHelper(); DbCommand command = helper.BuildDbCommand("P_SS_LookupOrganisationalUnit_S"); helper.AssignParameterValues(command); Helper.Fill(LookupOrganisationalUnitDataTable, command); return LookupOrganisationalUnitDataTable; }
public LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable GetOrganisationalUnitDetailByOrgID(string OrganisationalUnitID) { LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable LookupOrganisationalUnitDataTable = new LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable(); DbHelper helper = new DbHelper(); DbCommand command = helper.BuildDbCommand("P_SS_OrganisationalUnitDetailByOrgID_S"); helper.AssignParameterValues(command, OrganisationalUnitID); Helper.Fill(LookupOrganisationalUnitDataTable, command); return LookupOrganisationalUnitDataTable; }
private int? SaveAddress(LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable dtLookupOrganisationalUnitDataTable, LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable dtOrgDetail) { int? retaddressID = null; int versionAddressId = 1; var row = dtLookupOrganisationalUnitDataTable[0]; if (string.IsNullOrEmpty(row.State) && string.IsNullOrEmpty(row.UnitNumber) && string.IsNullOrEmpty(row.StreetNumber) && string.IsNullOrEmpty(row.Street) && string.IsNullOrEmpty(row.Type) && string.IsNullOrEmpty(row.Suffix) && string.IsNullOrEmpty(row.Suburb) && string.IsNullOrEmpty(row.PostCode)) { return retaddressID; } //If need to add address, State cannot be empty if (string.IsNullOrEmpty(row.State)) { throw new ArgumentException("State is mandatory when to add address."); } string address1 = string.Concat(row.UnitNumber, string.IsNullOrEmpty(row.UnitNumber) && string.IsNullOrEmpty(row.StreetNumber) ? string.Empty : "/", row.StreetNumber, " ", row.Street, " ", row.Type).Trim(); if(string.IsNullOrEmpty(address1) == false) { if(address1.EndsWith("/")) { address1 = address1.Substring(0, address1.Length - 1); } if(address1.StartsWith("/")) { address1 = address1.Substring(1, address1.Length - 1); } } if (dtOrgDetail.Rows.Count > 0) { if (string.IsNullOrEmpty(dtOrgDetail[0].AddressID)) { retaddressID = IsAddressExist(dtLookupOrganisationalUnitDataTable, address1, out versionAddressId); } else { versionAddressId = GetAddressVersion(Convert.ToInt32(dtOrgDetail[0].AddressID)); retaddressID = Convert.ToInt32(dtOrgDetail[0].AddressID.ToString()); } } else retaddressID = IsAddressExist(dtLookupOrganisationalUnitDataTable, address1, out versionAddressId); if (retaddressID == null) { string transactionid = NCS.IConnect.ApplicationContexts.ApplicationContextFactory.GetApplicationContext().TransactionId; DbHelper helper = new DbHelper(); DbCommand command = helper.BuildDbCommand("P_CM_Address_I"); helper.AssignParameterValues(command, null, null, string.IsNullOrEmpty(row.UnitNumber)?null:row.UnitNumber, null, null, string.IsNullOrEmpty(row.StreetNumber) ? null : row.StreetNumber, null, string.IsNullOrEmpty(row.Street) ? null : row.Street, string.IsNullOrEmpty(row.Type) ? null : row.Type, string.IsNullOrEmpty(row.Suffix) ? null : row.Suffix, null, null, string.IsNullOrEmpty(row.Suburb) ? null : row.Suburb, string.IsNullOrEmpty(row.State) ? null : row.State, string.IsNullOrEmpty(row.PostCode) ? null : row.PostCode, string.IsNullOrEmpty(address1.Trim()) ? null : address1.Trim(), null, null, null, null, null, 0, null, null, null, null, 1101, null, 0, versionAddressId, transactionid, AppContext.Current.UserName.ToString(), DateTime.Now, AppContext.Current.UserName.ToString(), DateTime.Now, null); object objAddID = null; Helper.ExecuteNonQuery(command); objAddID = command.Parameters["@addressid"].Value; return (int) objAddID; } else { string transactionid = NCS.IConnect.ApplicationContexts.ApplicationContextFactory.GetApplicationContext().TransactionId; DbHelper helper = new DbHelper(); DbCommand command = helper.BuildDbCommand("P_CM_Address_U"); helper.AssignParameterValues(command, retaddressID, null, null, string.IsNullOrEmpty(row.UnitNumber) ? null : row.UnitNumber, null, null, string.IsNullOrEmpty(row.StreetNumber) ? null : row.StreetNumber, null, string.IsNullOrEmpty(row.Street) ? null : row.Street, string.IsNullOrEmpty(row.Type) ? null : row.Type, string.IsNullOrEmpty(row.Suffix) ? null : row.Suffix, null, null, string.IsNullOrEmpty(row.Suburb) ? null : row.Suburb, string.IsNullOrEmpty(row.State) ? null : row.State, string.IsNullOrEmpty(row.PostCode) ? null : row.PostCode, string.IsNullOrEmpty(address1) ? null : address1, null, null, null, null, null, 0, null, null, null, null, 1101, null, 0, versionAddressId, transactionid, AppContext.Current.UserName.ToString(), DateTime.Now, AppContext.Current.UserName.ToString(), DateTime.Now, null); Helper.ExecuteNonQuery(command); } return retaddressID; }
private void SaveOrganisationLookupDetail(LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable dtLookupOrganisationalUnitDataTable, LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable dtOrgDetail, int? AddressID) { string transactionid = NCS.IConnect.ApplicationContexts.ApplicationContextFactory.GetApplicationContext().TransactionId; DbHelper helper = new DbHelper(); DbCommand command = null; if (dtOrgDetail.Rows.Count > 0) command = helper.BuildDbCommand("P_SS_OrganisationalUnitDetail_U"); else command = helper.BuildDbCommand("P_SS_OrganisationalUnitDetail_I"); helper.AssignParameterValues(command, Membership.ApplicationName, dtLookupOrganisationalUnitDataTable[0].OrganisationalUnitID, AddressID, dtLookupOrganisationalUnitDataTable[0].Phone, dtLookupOrganisationalUnitDataTable[0].Fax, dtLookupOrganisationalUnitDataTable[0].Mobile, dtLookupOrganisationalUnitDataTable[0].TollFreePhone, dtLookupOrganisationalUnitDataTable[0].Email, 1, transactionid, AppContext.Current.UserName.ToString(), DateTime.Now); Helper.ExecuteNonQuery(command); }