private static void KillDBObjects(System.Data.SqlClient.SqlConnection sqlConnection, System.Data.SqlClient.SqlCommand sqlCommand, System.Data.SqlClient.SqlDataReader sqlDataReader)
{
//close out the reader
sqlDataReader?.Close();
//dispose command
sqlCommand?.Dispose();
//dispose connection
if (sqlConnection != null)
{
if (sqlConnection.State == ConnectionState.Open)
{
sqlConnection.Close();
}
sqlConnection.Dispose();
}
}
public List <Tipo60> Select(TpBusca[] vBusca, Int32 vTop, string vNM_Campo)
{
List <Tipo60> retorno = new List <Tipo60>();
bool podeFecharBco = false;
if (Banco_Dados == null)
{
podeFecharBco = this.CriarBanco_Dados(false);
}
System.Data.SqlClient.SqlDataReader reader = this.ExecutarBusca(this.SqlCodeBusca(vBusca, Convert.ToInt16(vTop), vNM_Campo));
try
{
while (reader.Read())
{
Tipo60 reg = new Tipo60();
if (!reader.IsDBNull(reader.GetOrdinal("cd_empresa")))
{
reg.Cd_empresa = reader.GetString(reader.GetOrdinal("cd_empresa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ID_Equipamento")))
{
reg.Id_equipamento = reader.GetDecimal(reader.GetOrdinal("ID_Equipamento"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ID_COO_ECF")))
{
reg.Id_coo_ecf = reader.GetDecimal(reader.GetOrdinal("ID_COO_ECF"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DT_Emissao")))
{
reg.Dt_emissao = reader.GetDateTime(reader.GetOrdinal("DT_Emissao"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ST_Registro")))
{
reg.St_registro = reader.GetString(reader.GetOrdinal("ST_Registro"));
}
if (!reader.IsDBNull(reader.GetOrdinal("CD_Modelo")))
{
reg.Cd_modelo = reader.GetString(reader.GetOrdinal("CD_Modelo"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Nr_Sequencial_ECF")))
{
reg.Nr_sequencial_ecf = reader.GetDecimal(reader.GetOrdinal("Nr_Sequencial_ECF"));
}
if (!reader.IsDBNull(reader.GetOrdinal("CD_Produto")))
{
reg.Cd_produto = reader.GetString(reader.GetOrdinal("CD_Produto"));
}
if (!reader.IsDBNull(reader.GetOrdinal("TP_Situacao")))
{
reg.Tp_situacao = reader.GetString(reader.GetOrdinal("TP_Situacao"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ST_SubstTrib")))
{
reg.St_substtrib = reader.GetString(reader.GetOrdinal("ST_SubstTrib"));
}
if (!reader.IsDBNull(reader.GetOrdinal("PC_Aliquota")))
{
reg.Pc_aliquota = reader.GetDecimal(reader.GetOrdinal("PC_Aliquota"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Vl_BaseCalc")))
{
reg.Vl_basecalc = reader.GetDecimal(reader.GetOrdinal("Vl_BaseCalc"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Vl_icms")))
{
reg.Vl_icms = reader.GetDecimal(reader.GetOrdinal("Vl_icms"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Quantidade")))
{
reg.Quantidade = reader.GetDecimal(reader.GetOrdinal("Quantidade"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Vl_Subtotal")))
{
reg.Vl_subtotal = reader.GetDecimal(reader.GetOrdinal("Vl_Subtotal"));
}
retorno.Add(reg);
}
return(retorno);
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
}
private void button1_Click(object sender, System.EventArgs e)
{
string strYear;
strYear = dTPicker.Value.Year.ToString();
sqlComm.CommandText = "SELECT COUNT(*) FROM borrow WHERE (借出时间 > '" + strYear + "-01-01') AND (借出时间 < '" + strYear + "-12-31 23:59:59')";
sqlConn.Open();
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
booktv.BeginUpdate();
booktv.Nodes.Clear();
TreeNode rootNode = new TreeNode(strYear + "年度图书借阅信息：");
booktv.Nodes.Add(rootNode);
TreeNode tnBook1 = new TreeNode("共借出图书：" + sqldr.GetValue(0).ToString() + "册");
rootNode.Nodes.Add(tnBook1);
sqldr.Close();
sqlComm.CommandText = "SELECT COUNT(*) FROM borrow WHERE (实际归还时间 > '" + strYear + "-01-01') AND (实际归还时间 < '" + strYear + "-12-31 23:59:59') AND (实际归还时间 IS NOT NULL)";
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
TreeNode tnBook2 = new TreeNode("共归还图书：" + sqldr.GetValue(0).ToString() + "册");
rootNode.Nodes.Add(tnBook2);
sqldr.Close();
//sqlComm.CommandText="SELECT COUNT(*) FROM borrow WHERE (借出时间 > '"+ strYear +"-01-01') AND (借出时间 < '"+ strYear +"-12-31') AND (实际归还时间 IS NULL)";
//sqlComm.CommandText="SELECT COUNT(*) FROM borrow WHERE (实际归还时间 IS NULL)";
sqlComm.CommandText = "SELECT COUNT(*) FROM borrow WHERE (实际归还时间 > CONVERT(DATETIME, '" + strYear + "-12-31 23:59:59', 102) OR 实际归还时间 IS NULL) AND (借出时间 < CONVERT(DATETIME, '" + strYear + "-12-31 23:59:59', 102))";
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
TreeNode tnBook21 = new TreeNode("未归还图书：" + sqldr.GetValue(0).ToString() + "册");
rootNode.Nodes.Add(tnBook21);
sqldr.Close();
//sqlComm.CommandText="SELECT COUNT(*) FROM borrow WHERE (实际归还时间 IS NULL) AND (归还时间<'"+System.DateTime.Now.ToString()+"')";
sqlComm.CommandText = "SELECT COUNT(*) FROM borrow WHERE (归还时间<'" + strYear + "-12-31 23:59:59')";
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
TreeNode tnBook3 = new TreeNode("已到期图书：" + sqldr.GetValue(0).ToString() + "册");
rootNode.Nodes.Add(tnBook3);
sqldr.Close();
sqlComm.CommandText = "SELECT COUNT(*) FROM book WHERE (入库日期 > '" + strYear + "-01-01') AND (入库日期 < '" + strYear + "-12-31 23:59:59')";
sqldr = sqlComm.ExecuteReader();
sqldr.Read();
TreeNode tnBook4 = new TreeNode("入库图书：" + sqldr.GetValue(0).ToString() + "册");
rootNode.Nodes.Add(tnBook4);
sqldr.Close();
sqlConn.Close();
booktv.EndUpdate();
rootNode.Expand();
makebufferprint();
}
public TList_CFGLocacao Select(Utils.TpBusca[] vBusca, Int32 vTop, string vNm_Campo)
{
TList_CFGLocacao lista = new TList_CFGLocacao();
System.Data.SqlClient.SqlDataReader reader = null;
bool podeFecharBco = false;
if (Banco_Dados == null)
{
podeFecharBco = CriarBanco_Dados(false);
}
try
{
reader = ExecutarBusca(SqlCodeBusca(vBusca, Convert.ToInt16(vTop), vNm_Campo));
while (reader.Read())
{
TRegistro_CFGLocacao reg = new TRegistro_CFGLocacao();
if (!reader.IsDBNull(reader.GetOrdinal("cd_empresa")))
{
reg.Cd_empresa = reader.GetString(reader.GetOrdinal("cd_empresa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("nm_empresa")))
{
reg.Nm_empresa = reader.GetString(reader.GetOrdinal("nm_empresa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("TP_Ordem")))
{
reg.Tp_ordem = reader.GetDecimal(reader.GetOrdinal("TP_Ordem"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DS_TipoOrdem")))
{
reg.Ds_tipoordem = reader.GetString(reader.GetOrdinal("DS_TipoOrdem"));
}
if (!reader.IsDBNull(reader.GetOrdinal("tp_duplicata")))
{
reg.Tp_duplicata = reader.GetString(reader.GetOrdinal("tp_duplicata"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_tpduplicata")))
{
reg.Ds_tpduplicata = reader.GetString(reader.GetOrdinal("ds_tpduplicata"));
}
if (!reader.IsDBNull(reader.GetOrdinal("tp_docto")))
{
reg.Tp_docto = reader.GetDecimal(reader.GetOrdinal("tp_docto"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_tpdocto")))
{
reg.Ds_tpdocto = reader.GetString(reader.GetOrdinal("ds_tpdocto"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_historico")))
{
reg.Cd_historico = reader.GetString(reader.GetOrdinal("cd_historico"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_historico")))
{
reg.Ds_historico = reader.GetString(reader.GetOrdinal("ds_historico"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cfg_pedido_servico")))
{
reg.Cfg_pedido_servico = reader.GetString(reader.GetOrdinal("cfg_pedido_servico"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_tipopedido_servico")))
{
reg.Ds_tipopedido_servico = reader.GetString(reader.GetOrdinal("ds_tipopedido_servico"));
}
if (!reader.IsDBNull(reader.GetOrdinal("id_config")))
{
reg.Id_configboleto = reader.GetDecimal(reader.GetOrdinal("id_config"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_config")))
{
reg.Ds_configboleto = reader.GetString(reader.GetOrdinal("ds_config"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_centroresultdia")))
{
reg.Cd_centroresultdia = reader.GetString(reader.GetOrdinal("cd_centroresultdia"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_centroresultdia")))
{
reg.Ds_centroresultdia = reader.GetString(reader.GetOrdinal("ds_centroresultdia"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_centroresultsem")))
{
reg.Cd_centroresultsem = reader.GetString(reader.GetOrdinal("cd_centroresultsem"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_centroresultsem")))
{
reg.Ds_centroresultsem = reader.GetString(reader.GetOrdinal("ds_centroresultsem"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_centroresultquinz")))
{
reg.Cd_centroresultquinz = reader.GetString(reader.GetOrdinal("cd_centroresultquinz"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_centroresultquinz")))
{
reg.Ds_centroresultquinz = reader.GetString(reader.GetOrdinal("ds_centroresultquinz"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_centroresultmes")))
{
reg.Cd_centroresultmes = reader.GetString(reader.GetOrdinal("cd_centroresultmes"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_centroresultmes")))
{
reg.Ds_centroresultmes = reader.GetString(reader.GetOrdinal("ds_centroresultmes"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Tp_ProdCombustivel")))
{
reg.Tp_prodcombustivel = reader.GetString(reader.GetOrdinal("Tp_ProdCombustivel"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Ds_TpProduto")))
{
reg.Ds_tpprodcombustivel = reader.GetString(reader.GetOrdinal("Ds_TpProduto"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Nr_SeqRecibo")))
{
reg.Nr_seqrecibo = reader.GetDecimal(reader.GetOrdinal("Nr_SeqRecibo"));
}
if (!reader.IsDBNull(reader.GetOrdinal("st_horaauto")))
{
reg.St_HoraAuto = reader.GetBoolean(reader.GetOrdinal("st_horaauto"));
}
if (!reader.IsDBNull(reader.GetOrdinal("TP_OrdemP")))
{
reg.Tp_ordemp = reader.GetDecimal(reader.GetOrdinal("TP_OrdemP"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DS_TipoOrdemP")))
{
reg.Ds_tipoordemP = reader.GetString(reader.GetOrdinal("DS_TipoOrdemP"));
}
lista.Add(reg);
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
deletarBanco_Dados();
}
}
return(lista);
}
public TList_TpOrdem_X_Etapa Select(Utils.TpBusca[] vBusca, Int32 vTop, string vNm_Campo)
{
TList_TpOrdem_X_Etapa lista = new TList_TpOrdem_X_Etapa();
System.Data.SqlClient.SqlDataReader reader = null;
bool podeFecharBco = false;
if (Banco_Dados == null)
{
podeFecharBco = this.CriarBanco_Dados(false);
}
try
{
reader = this.ExecutarBusca(this.SqlCodeBusca(vBusca, Convert.ToInt16(vTop), vNm_Campo));
while (reader.Read())
{
TRegistro_TpOrdem_X_Etapa reg = new TRegistro_TpOrdem_X_Etapa();
if (!reader.IsDBNull(reader.GetOrdinal("tp_ordem")))
{
reg.Tp_ordem = reader.GetDecimal(reader.GetOrdinal("tp_ordem"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_tipoordem")))
{
reg.Ds_tipoordem = reader.GetString(reader.GetOrdinal("ds_tipoordem"));
}
if (!reader.IsDBNull(reader.GetOrdinal("id_etapa")))
{
reg.Id_etapa = reader.GetDecimal(reader.GetOrdinal("id_etapa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_etapa")))
{
reg.Ds_etapa = reader.GetString(reader.GetOrdinal("ds_etapa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Ordem")))
{
reg.Ordem = reader.GetDecimal(reader.GetOrdinal("Ordem"));
}
if (!reader.IsDBNull(reader.GetOrdinal("st_iniciarOS")))
{
reg.St_iniciarOS = reader.GetString(reader.GetOrdinal("st_iniciarOS"));
}
if (!reader.IsDBNull(reader.GetOrdinal("st_finalizarOS")))
{
reg.St_finalizarOS = reader.GetString(reader.GetOrdinal("st_finalizarOS"));
}
if (!reader.IsDBNull(reader.GetOrdinal("st_etapaorcamento")))
{
reg.St_etapaorcamento = reader.GetString(reader.GetOrdinal("st_etapaorcamento"));
}
if (!reader.IsDBNull(reader.GetOrdinal("st_envterceiro")))
{
reg.St_envterceiro = reader.GetString(reader.GetOrdinal("st_envterceiro"));
}
lista.Add(reg);
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
return(lista);
}
public Model.Table[] GetTables()
{
string sql = string.Format(@"SELECT TABLE_NAME,
(
SELECT
CAST(
CASE
WHEN tbl.is_ms_shipped = 1 THEN 1
WHEN (
SELECT
major_id
FROM
sys.extended_properties
WHERE
major_id = tbl.object_id and
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support')
IS NOT NULL THEN 1
ELSE 0
END
AS bit) AS [IsSystemObject]
FROM
sys.tables AS tbl
WHERE
(tbl.name = T.TABLE_NAME and SCHEMA_NAME(tbl.schema_id)=N'dbo')
) AS IsSystemObject
FROM INFORMATION_SCHEMA.TABLES T
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY T.TABLE_NAME", DatabaseName);
System.Collections.ArrayList arrTableNames = new System.Collections.ArrayList();
System.Data.SqlClient.SqlDataReader dr = null;
try
{
dr = RunQuerySqlClient(sql);
bool isSystemObject;
int isSysObjectColumnOrdinal = dr.GetOrdinal("IsSystemObject");
while (dr.Read())
{
isSystemObject = dr.IsDBNull(isSysObjectColumnOrdinal) ? false : (bool)dr[isSysObjectColumnOrdinal];
if (!isSystemObject || Model.Database.IncludeSystemObjects)
{
arrTableNames.Add((string)dr["TABLE_NAME"]);
}
}
}
finally
{
if (dr != null)
{
dr.Close();
}
}
List <Model.Table> tables = new List <Model.Table>();
for (int i = 0; i < arrTableNames.Count; i++)
{
Model.Table table = GetNewTable((string)arrTableNames[i]);
tables.Add(table);
}
return((Model.Table[])tables.ToArray());
}
public List <Tipo53> Select(TpBusca[] vBusca, Int32 vTop, string vNM_Campo)
{
List <Tipo53> retorno = new List <Tipo53>();
bool podeFecharBco = false;
if (Banco_Dados == null)
{
podeFecharBco = this.CriarBanco_Dados(false);
}
System.Data.SqlClient.SqlDataReader reader = this.ExecutarBusca(this.SqlCodeBusca(vBusca, Convert.ToInt16(vTop), vNM_Campo));
try
{
while (reader.Read())
{
Tipo53 reg = new Tipo53();
if (!reader.IsDBNull(reader.GetOrdinal("cnpj")))
{
reg.Cnpj = reader.GetString(reader.GetOrdinal("cnpj"));
}
if (!reader.IsDBNull(reader.GetOrdinal("insc_estadual")))
{
reg.Insc_estadual = reader.GetString(reader.GetOrdinal("insc_estadual"));
}
if (!reader.IsDBNull(reader.GetOrdinal("uf")))
{
reg.Uf = reader.GetString(reader.GetOrdinal("uf"));
}
if (!reader.IsDBNull(reader.GetOrdinal("nr_serie")))
{
reg.Nr_serie = reader.GetString(reader.GetOrdinal("nr_serie"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_modelo")))
{
reg.Cd_modelo = reader.GetString(reader.GetOrdinal("cd_modelo"));
}
if (!reader.IsDBNull(reader.GetOrdinal("nr_notafiscal")))
{
reg.Nr_notafiscal = reader.GetDecimal(reader.GetOrdinal("nr_notafiscal"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_cfop")))
{
reg.Cd_cfop = reader.GetString(reader.GetOrdinal("cd_cfop"));
}
if (!reader.IsDBNull(reader.GetOrdinal("dt_emissao_recebimento")))
{
reg.Dt_emissao_recebimento = reader.GetDateTime(reader.GetOrdinal("dt_emissao_recebimento"));
}
if (!reader.IsDBNull(reader.GetOrdinal("st_registro")))
{
reg.Situacao = reader.GetString(reader.GetOrdinal("st_registro")).Trim().ToUpper().Equals("C") ? "S" : "N";
}
if (!reader.IsDBNull(reader.GetOrdinal("Tp_Nota")))
{
reg.Tp_nota = reader.GetString(reader.GetOrdinal("Tp_Nota"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Vl_basecalcicms")))
{
reg.Vl_basecalc = reader.GetDecimal(reader.GetOrdinal("Vl_basecalcicms"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Vl_icms")))
{
reg.Vl_icms = reader.GetDecimal(reader.GetOrdinal("Vl_icms"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Vl_despesas")))
{
reg.Vl_despesas = reader.GetDecimal(reader.GetOrdinal("Vl_despesas"));
}
retorno.Add(reg);
}
return(retorno);
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
}
/// <summary>
/// 获取blob类型的字段值
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public byte[] GetLargeField(string strSql)
{
this.command.Connection = this.Conn as System.Data.SqlClient.SqlConnection;
this.command.Transaction = Mder.FC.DB.DBTrans.Trans as System.Data.SqlClient.SqlTransaction;
command.CommandText = strSql + "";
command.CommandType = System.Data.CommandType.Text;
System.Data.SqlClient.SqlDataReader reader;
command.Parameters.Clear();
try
{
reader = command.ExecuteReader();
}
catch (System.Data.SqlClient.SqlException ex)
{
this.Err = "执行产生错误!" + ex.Message;
this.ErrCode = strSql;
this.DBErrCode = ex.ErrorCode;
this.WriteErr();
return null;
}
catch (Exception ex)
{
this.Err = ex.Message;
this.WriteErr();
return null;
}
reader.Read();
byte[] byteData = new byte[0];
try
{
byteData = (byte[])(reader[0]);
}
catch (Exception ex)
{
this.Err = ex.Message;
reader.Close();
return null;
}
reader.Close();
return byteData;
}
