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 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; }
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 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 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 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 List<DadosAES> ObterNumeroAESPorChave(string chave) { List<DadosAES> listaAES = new List<DadosAES>(); DbHelper db = new DbHelper(); string cmdText = @"select esta.Numero_AES, esta.Item_AES from vw_estagio esta join Token tk on esta.Numero_AES = tk.AES where tk.Chave = @Chave group by esta.Numero_AES, esta.Item_AES"; db.AddParameter(new System.Data.SqlClient.SqlParameter("@Chave", chave)); SqlDataReader dr = db.GetDataReader(cmdText); if (dr.HasRows) { while (dr.Read()) { listaAES.Add(new DadosAES { NumeroAES = dr["Numero_AES"].ToString(), ItemAES = Convert.ToInt32(dr["Item_AES"]) }); } } db.CloseDbConnection(); return listaAES; }