private void Name_TextChanged(object sender, EventArgs e)
{
myConn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|Database1.mdf;Integrated Security=True;User Instance=True");
myConn.Open();
string commandString = @"SELECT * FROM Asset WHERE [Asset Name] = '" + name.Text + "'";
SqlCommand cmd = new SqlCommand(commandString, myConn);
rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
if (rdr != null)
{
rdr.Dispose();
}
SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT Asset.[Asset ID], Asset.[Asset Name], Asset.[Asset Category], Category.[Category Type], Status.[Status] , History.* FROM Asset JOIN History ON Asset.[Asset ID] = History.[Asset ID] JOIN Status ON Status.[Status ID] = Asset.[Asset Status] JOIN Category ON Category.[Category ID] = Asset.[Asset Category] WHERE [Asset Name] = '"+ name.Text+ "'", myConn);
DataTable t = new DataTable();
adapter.Fill(t);
t.Columns.RemoveAt(5);
t.Columns.RemoveAt(10);
dataGridView1.DataSource = t;
dataGridView1.Columns[2].Visible = false;
this.dataGridView1.Columns[8].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dataGridView1.Refresh();
myConn.Close();
}
else
{
MessageBox.Show("Not in database!!");
this.Dispose();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
if (Page.PreviousPage != null)
{
System.Data.SqlClient.SqlDataReader dr = Rent.ReadData("select * from Owner_Details where OwnerID=" + ((Label)Page.PreviousPage.FindControl("lbl_Msg")).Text);
if (dr.Read())
{
hdn_ownrID.Value = dr["OwnerID"].ToString();
txtfname.Text = dr["FName"].ToString();
txtmname.Text = dr["MName"].ToString();
txtlname.Text = dr["LName"].ToString();
txtaddrs.Text = dr["Addrs"].ToString();
txtmobile.Text = dr["Mobile"].ToString();
txtlndline.Text = dr["Landline"].ToString();
txtcity.Text = dr["City"].ToString();
txtstate.Text = dr["State"].ToString();
txtmail.Text = dr["Email"].ToString();
btnAdd.Text = "Update";
dr.Close();
dr.Dispose();
}
else
{
dr.Close();
dr.Dispose();
}
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
Panel1.Visible = false;
if (!Page.IsPostBack)
{
dd_renttype.Items.Add("Select Rent Type");
DrRntTp = Rent.ReadData("Select RentTypeID,RentType from RentTypeDetails");
if (DrRntTp.HasRows)
{
while (DrRntTp.Read())
{
ListItem item = new ListItem();
item.Value = DrRntTp["RentTypeID"].ToString();
item.Text = DrRntTp["RentType"].ToString();
dd_renttype.Items.Add(item);
}
}
dd_owner.Items.Add("Select Owner");
DrOnr = Rent.ReadData("Select OwnerID,FName + ' ' + MName + ' ' + LName as [OwnerName] from Owner_Details");
if (DrOnr.HasRows)
{
while (DrOnr.Read())
{
ListItem item = new ListItem();
item.Value = DrOnr["OwnerID"].ToString();
item.Text = DrOnr["OwnerName"].ToString();
dd_owner.Items.Add(item);
}
}
if (Page.PreviousPage != null)
{
System.Data.SqlClient.SqlDataReader dr = Rent.ReadData("select * from Tbl_RentDetails where RentID=" + ((Label)Page.PreviousPage.FindControl("lbl_Msg")).Text);
if (dr.Read())
{
hdn_rentID.Value = dr["RentID"].ToString();
dd_renttype.Text = dr["RentTypeID"].ToString();
dd_owner.Text = dr["OwnerID"].ToString();
dd_avail.Text = dr["Availability"].ToString();
dd_Furnish.Text = dr["Furnished"].ToString();
dd_Bedrooms.Text = dr["BedRooms"].ToString();
dd_bath.Text = dr["BathRooms"].ToString();
txtsqft.Text = dr["Sqft"].ToString();
txtAmt.Text = dr["Amount"].ToString();
dd_Nego.Text = dr["Negotiable"].ToString();
btnAdd.Text = "Update";
dr.Close();
dr.Dispose();
}
else
{
dr.Close();
dr.Dispose();
}
}
}
}
/// <summary>
/// ��DataReader תΪ DataTable
/// </summary>
/// <param name="DataReader">DataReader</param>
public DataTable ConvertDataReaderToDataTable(SqlDataReader dataReader)
{
DataTable datatable = new DataTable();
try
{
DataTable schemaTable = dataReader.GetSchemaTable();
//��̬�����
foreach (DataRow myRow in schemaTable.Rows)
{
DataColumn myDataColumn = new DataColumn();
myDataColumn.DataType = System.Type.GetType("System.String");
myDataColumn.ColumnName = myRow[0].ToString();
datatable.Columns.Add(myDataColumn);
}
//�������
while (dataReader.Read())
{
DataRow myDataRow = datatable.NewRow();
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
myDataRow[i] = dataReader[i].ToString();
}
datatable.Rows.Add(myDataRow);
myDataRow = null;
}
schemaTable = null;
}
finally
{
dataReader.Close();
dataReader.Dispose();
}
return datatable;
}
protected void Listar_Click(object sender, EventArgs e)
{
string nome;
int idBusca;
ListaNomes.Items.Clear();
if(int.TryParse(textID.Text, out idBusca) == false)
{
lblMensagem.Text = "Campo de busca ID em branco..!";
return;
}
conn.Open();
command = new SqlCommand("SELECT Id, Nome FROM tbPessoa WHERE Id >= @a", conn);
command.Parameters.AddWithValue("@a", idBusca);
reader = command.ExecuteReader();
while(reader.Read())
{
idBusca = reader.GetInt32(0);
nome = reader.GetString(1);
ListItem item = new ListItem(nome, Convert.ToString(idBusca));
ListaNomes.Items.Add(item);
}
reader.Close();
reader.Dispose();
command.Dispose();
conn.Close();
conn.Dispose();
}
protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack == false)
{
conn.Open();
command = new SqlCommand("SELECT Nome, Id FROM tbAluno", conn);
reader = command.ExecuteReader();
string Nome;
int valor;
while (reader.Read())
{
Nome = reader.GetString(0);
valor = reader.GetInt32(1);
ListItem item = new ListItem(Nome, Convert.ToString(valor));
DropListNomes.Items.Add(item);
}
reader.Close();
reader.Dispose();
command.Dispose();
conn.Close();
conn.Dispose();
}
}
public System.Data.SqlClient.SqlDataReader Reader2(string sqlcumle, System.Windows.Forms.TextBox t1, System.Windows.Forms.TextBox t2, DevComponents.DotNetBar.Controls.ComboBoxEx t3, string s1, string s2, string s3, string s4)
{
baglantim();
System.Data.SqlClient.SqlCommand komut = new System.Data.SqlClient.SqlCommand("" + sqlcumle + "", baglantim());
System.Data.SqlClient.SqlDataReader rdr = komut.ExecuteReader();
try
{
while (rdr.Read())
{
t1.Text = rdr[s1].ToString();
t2.Text = rdr[s2].ToString();
t3.Text = rdr[s3].ToString();
sayi = rdr[s4].ToString();
if (sayi == "0")
{
kontrol = true;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ebubekir Stok Takip otomasyonu Otomasyon");
}
rdr.Dispose();
baglantim_kapat();
return(rdr);
}
protected void LiberarDataReader(ref SqlDataReader oDataReader)
{
if (oDataReader != null)
oDataReader.Dispose();
oDataReader = null;
}
public TList_Empreendimento Select(TpBusca[] vBusca, Int32 vTop, string vNM_Campo)
{
bool podeFecharBco = false;
TList_Empreendimento lista = new TList_Empreendimento();
if (Banco_Dados == null)
{
this.CriarBanco_Dados(false);
podeFecharBco = true;
}
System.Data.SqlClient.SqlDataReader reader = this.ExecutarBusca(this.SqlCodeBusca(vBusca, vTop, vNM_Campo));
try
{
while (reader.Read())
{
TRegistro_Empreendimento reg = new TRegistro_Empreendimento();
if (!(reader.IsDBNull(reader.GetOrdinal("ID_Empreendimento"))))
{
reg.Id_empreendimento = reader.GetDecimal(reader.GetOrdinal("ID_Empreendimento"));
}
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("DS_Empreendimento")))
{
reg.Ds_empreendimento = reader.GetString(reader.GetOrdinal("DS_Empreendimento"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DS_Observacao")))
{
reg.Ds_observacao = reader.GetString(reader.GetOrdinal("DS_Observacao"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DT_IniEmpreendimento")))
{
reg.Dt_iniempreendimento = reader.GetDateTime(reader.GetOrdinal("DT_IniEmpreendimento"));
}
if (!(reader.IsDBNull(reader.GetOrdinal("ST_Registro"))))
{
reg.St_registro = reader.GetString(reader.GetOrdinal("ST_Registro"));
}
lista.Add(reg);
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
return(lista);
}
public static void cargarListBox(ListBox unListBox, SqlDataReader reader)
{
while (reader.Read())
{
unListBox.Items.Add(reader.GetSqlString(0));
}
reader.Dispose();
}
/// <summary>
/// 关闭DataReade
/// </summary>
/// <param name="reader">参数1：SqlDataReader</param>
public static void DataReaderClose(SqlDataReader reader)
{
if (reader != null)
{
reader.Dispose();
reader.Close();
}
}
public static void cargarComboBox(ComboBox unCombo,SqlDataReader reader)
{
while (reader.Read())
{
unCombo.Items.Add(reader.GetSqlString(0));
}
reader.Dispose();
}
///<summary>
///关闭datareader
///传入SqlDataReader的ref
///</summary>
public static void closeDataReader(ref SqlDataReader sdr)
{
try
{
sdr.Close();
sdr.Dispose();
}
catch (Exception)
{ }
}
/// <summary>
/// Checked closing of SqlDataReader
/// </summary>
public static void CloseDataReader(System.Data.SqlClient.SqlDataReader dr)
{
if (dr != null)
{
if (!dr.IsClosed)
{
dr.Close();
}
dr.Dispose();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
if (Page.PreviousPage != null)
{
System.Data.SqlClient.SqlDataReader dr = Rent.ReadData("select * from RentTypeDetails where RentTypeID=" + ((Label)Page.PreviousPage.FindControl("lbl_Msg")).Text);
if (dr.Read())
{
hdn_rentID.Value = dr["RentTypeID"].ToString();
dd_Rtype.Text = dr["RentType"].ToString();
btnAdd.Text = "Update";
dr.Close();
dr.Dispose();
}
else
{
dr.Close();
dr.Dispose();
}
}
}
}
private void id_TextChanged(object sender, EventArgs e)
{
myConn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|Database1.mdf;Integrated Security=True;User Instance=True");
myConn.Open();
string commandString = @"SELECT * FROM Asset WHERE [Asset ID] = '" + id.Text + "'";
SqlCommand cmd = new SqlCommand(commandString, myConn);
string commandString1 = "";
rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
name.Text = (string)rdr["Asset Name"];
ACost.Text = rdr["Asset Acquisition Cost"].ToString();
Life.Text = rdr["Asset Useful Life"].ToString();
commandString = @"SELECT [Status] FROM Status WHERE [Status ID] like '" + rdr["Asset Status"] + "'";
commandString1 = @"SELECT [Category Type] FROM Category WHERE [Category ID] = " + rdr["Asset Category"];
}
if (rdr != null)
{
rdr.Dispose();
}
cmd = new SqlCommand(commandString, myConn);
stat.Text = (string)cmd.ExecuteScalar();
cmd = new SqlCommand(commandString1, myConn);
category.Text = (string)cmd.ExecuteScalar();
commandString = @"SELECT CASE WHEN (CAST([Asset Acquisition Cost] - [Asset Acquisition Cost]/[Asset Useful Life] * DATEDIFF(year, [Asset Purchase Date], GETDATE() ) as Decimal(12 , 2)) <= 0) THEN [Quantity] ELSE CAST([Asset Acquisition Cost] - [Asset Acquisition Cost]/[Asset Useful Life] * DATEDIFF(year, [Asset Purchase Date], GETDATE() ) as Decimal(12 , 2)) END From Asset Where [Asset ID] ='" + id.Text + "'";
cmd = new SqlCommand(commandString, myConn);
BookVal.Text = cmd.ExecuteScalar().ToString();
//SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT ([Asset Acquisition Cost] - [Depreciation Years]) AS [Test] From Asset Join Category ON Asset.[Asset Category] = Category.[Category ID] Where [Asset ID] =" + id.Text, myConn);
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM History WHERE [Asset ID] = '" + id.Text + "'", myConn);
DataTable t = new DataTable();
adapter.Fill(t);
dataGridView1.DataSource = t;
this.dataGridView1.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
}
else
{
MessageBox.Show("Not in database!!");
this.Dispose();
}
myConn.Close();
}
public string lay1dong(string sql)
{
connect();
cm = new SqlCommand(sql, con);
reader = cm.ExecuteReader();
string v = reader.Read() ? reader[0].ToString() : "";
reader.Dispose();
connectClose();
return v;
}
private List<TransportadorEmpresa> TransformaReaderEmLista(SqlDataReader reader)
{
var transportador = new List<TransportadorEmpresa>();
while (reader.Read())
{
var temObjeto = new TransportadorEmpresa
{
IdTransportador = int.Parse(reader["IDTRANSPORTADOR"].ToString()),
Nome = reader["NOME_TRANSPORTADOR"].ToString()
};
transportador.Add(temObjeto);
}
reader.Close();
reader.Dispose();
return transportador;
}
private void getdata()
{
cmd = new SqlCommand("select * from HOCSINH", conn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
var masv = (string)dr["MAHS"];
var hoten = (string)dr["HOTEN"];
var ngaysinh = dr["NGAYSINH"];
var quequan = (string)dr["QUEQUAN"];
var makhoa = (string)dr["MALOP"];
dataGridView1.Rows.Add(masv, hoten, ngaysinh.ToString(), quequan, makhoa);
}
dr.Close();
dr.Dispose();
}
public String AdminNameFetch(int admin_id)
{
String AdminName = "";
try
{
if (newcon.State == ConnectionState.Closed)
{
newcon.Open();
}
String query = "Select first_name,last_name from admin_info where admin_id = @aid";
command = new SqlCommand(query, newcon);
command.Parameters.AddWithValue("@aid", admin_id);
reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
{
AdminName = reader[0].ToString() + " " + reader[1].ToString();
}
}
else
{
AdminName = "";
}
command.Dispose();
reader.Dispose();
return AdminName;
}
catch
{
throw;
}
finally
{
if (newcon.State == ConnectionState.Open)
{
newcon.Close();
}
}
}
private static List<TipoButacaDTO> readerToListTipoButaca(SqlDataReader dataReader)
{
List<TipoButacaDTO> listaTipoServicio = new List<TipoButacaDTO>();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
TipoButacaDTO tb = new TipoButacaDTO();
tb.IdTipoButaca = Convert.ToInt32(dataReader["Id"]);
tb.Descripcion = Convert.ToString(dataReader["Descripcion"]);
listaTipoServicio.Add(tb);
}
}
dataReader.Close();
dataReader.Dispose();
return listaTipoServicio;
}
private static List<ModeloDTO> readerToListModelo(SqlDataReader dataReader)
{
List<ModeloDTO> listaMods = new List<ModeloDTO>();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
ModeloDTO mod = new ModeloDTO();
mod.Id = Convert.ToInt32(dataReader["Id"]);
mod.Modelo = Convert.ToString(dataReader["Modelo_Desc"]);
listaMods.Add(mod);
}
}
dataReader.Close();
dataReader.Dispose();
return listaMods;
}
private static List<TipoServicioDTO> readerToListTipoServicio(SqlDataReader dataReader)
{
List<TipoServicioDTO> listaFunc = new List<TipoServicioDTO>();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
TipoServicioDTO ts = new TipoServicioDTO();
ts.IdTipoServicio = Convert.ToInt32(dataReader["Id"]);
ts.Descripcion = Convert.ToString(dataReader["Descripcion"]);
listaFunc.Add(ts);
}
}
dataReader.Close();
dataReader.Dispose();
return listaFunc;
}
private static List<FabricanteDTO> readerToListFabricante(SqlDataReader dataReader)
{
List<FabricanteDTO> listaFabs = new List<FabricanteDTO>();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
FabricanteDTO fab = new FabricanteDTO();
fab.IdFabricante = Convert.ToInt32(dataReader["Id"]);
fab.Nombre = Convert.ToString(dataReader["Nombre"]);
listaFabs.Add(fab);
}
}
dataReader.Close();
dataReader.Dispose();
return listaFabs;
}
public void ShowDatabase()
{
ChuoiConnect = @"Data Source=.\SQLEXpress;Integrated Security=True";
Conn = new SqlConnection(ChuoiConnect);
Conn.Open();
//sql="EXEC sp_databases";
sql = "SELECT * FROM sys.databases d WHERE d.database_id>4";
Cmd = new SqlCommand(sql, Conn);
Reader = Cmd.ExecuteReader();
cbo_database.Items.Clear();
while(Reader.Read())
{
cbo_database.Items.Add(Reader[0].ToString());
}
Reader.Dispose();
Conn.Close();
Conn.Dispose();
}
private static List<FuncionalidadDTO> readerToListFunc(SqlDataReader dataReader)
{
List<FuncionalidadDTO> listaFunc = new List<FuncionalidadDTO>();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
FuncionalidadDTO func = new FuncionalidadDTO();
func.IdFuncionalidad = Convert.ToInt32(dataReader["Id"]);
func.Descripcion = Convert.ToString(dataReader["Descripcion"]);
listaFunc.Add(func);
}
}
dataReader.Close();
dataReader.Dispose();
return listaFunc;
}
public void combo1(ComboBox cbo)
{
try
{
Tabb = new SqlCommand("select tipo from tipovehiculo;", Conn);
Dr = Tabb.ExecuteReader();
while (Dr.Read())
{
cbo.Items.Add(Dr["tipo"]);
}
Dr.Dispose();
Dr.Close();
}
catch (Exception RR)
{
throw RR;
}
}
protected void LoadData(double _newsID)
{
SqlService _service = new SqlService();
System.Data.SqlClient.SqlDataReader _dr = null;
try
{
_service.AddParameter("@News_ID", SqlDbType.Int, _newsID);
_dr = _service.ExecuteSPReader(@"CMS_GetNewDetailsPrints");
if (_dr != null)
{
while (_dr.Read())
{
if (_dr["News_DateEdit"] != System.DBNull.Value)
{
this.litDateTime.Text = Convert.ToDateTime(_dr["News_DateEdit"]).ToString("HH:mm, dd/MM/yyyy");
}
if (_dr["News_DatePublished"] != System.DBNull.Value)
{
this.litDateTime.Text = Convert.ToDateTime(_dr["News_DatePublished"]).ToString("HH:mm, dd/MM/yyyy");
}
this.litTittle.Text = _dr["News_Tittle"].ToString();
this.litSapo.Text = _dr["News_Summary"].ToString();
string contents = _dr["News_Body"].ToString();
this.litCategorys.Text = "<a href=\"#\">" + HPCBusinessLogic.UltilFunc.GetCategoryName(_dr["CAT_ID"]) + "</a>";
//this.litContents.Text = SearchImgTag(contents);
this.litContents.Text = contents;
if (_dr["News_AuthorName"] != null)
{
this.litAuthor.Text += "<div class=\"author\">";
this.litAuthor.Text += _dr["News_AuthorName"].ToString();
this.litAuthor.Text += "</div>";
}
}
}
}
catch { }
finally { if (_dr != null)
{
_dr.Close(); _dr.Dispose();
}
_service.CloseConnect(); _service.Disconnect(); }
}
private static List<TipoPagoDTO> getTiposPago(SqlDataReader dataReader)
{
List<TipoPagoDTO> ListaTipoPago = new List<TipoPagoDTO>();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
TipoPagoDTO tipoPago = new TipoPagoDTO();
tipoPago.IdTipoPago = Convert.ToInt32(dataReader["Id"]);
tipoPago.Descripcion = Convert.ToString(dataReader["Descripcion"]);
ListaTipoPago.Add(tipoPago);
}
dataReader.Close();
dataReader.Dispose();
}
return ListaTipoPago;
}
public TList_LanServico_X_Duplicata Select(Utils.TpBusca[] vBusca, Int32 vTop, string vNM_Campo)
{
bool podeFecharBco = false;
TList_LanServico_X_Duplicata lista = new TList_LanServico_X_Duplicata();
if (Banco_Dados == null)
{
podeFecharBco = this.CriarBanco_Dados(false);
}
System.Data.SqlClient.SqlDataReader reader = this.ExecutarBusca(this.SqlCodeBusca(vBusca, vTop, vNM_Campo));
try
{
while (reader.Read())
{
TRegistro_LanServico_X_Duplicata reg = new TRegistro_LanServico_X_Duplicata();
if (!reader.IsDBNull(reader.GetOrdinal("id_os")))
{
reg.Id_os = reader.GetDecimal(reader.GetOrdinal("id_os"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_empresa")))
{
reg.Cd_empresa = reader.GetString(reader.GetOrdinal("cd_empresa"));
}
if (!reader.IsDBNull(reader.GetOrdinal("nr_lancto")))
{
reg.Nr_lancto = reader.GetDecimal(reader.GetOrdinal("nr_lancto"));
}
lista.Add(reg);
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
return(lista);
}
private static List<TipoTarjetaDTO> getTipoTarjetas(SqlDataReader dataReader)
{
List<TipoTarjetaDTO> ListaTarjetas = new List<TipoTarjetaDTO>();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
TipoTarjetaDTO tipoTarjeta = new TipoTarjetaDTO();
tipoTarjeta.IdTipoTarjeta= Convert.ToInt32(dataReader["Id"]);
tipoTarjeta.Nombre = Convert.ToString(dataReader["Nombre"]);
tipoTarjeta.NumeroCuotas = Convert.ToInt32(dataReader["Numero_Cuotas"]);
ListaTarjetas.Add(tipoTarjeta);
}
dataReader.Close();
dataReader.Dispose();
}
return ListaTarjetas;
}
//初始化类型信息下拉列表
public void initComb()
{
try
{
cmdText = "select typename from Type";
cmd = new SqlCommand(cmdText, sc.getConn());
reader = cmd.ExecuteReader();
while (reader.Read())
{
combType.Items.Add(reader.GetString(reader.GetOrdinal("typename")));
}
reader.Dispose();
reader.Close();
cmd.Dispose();
}
catch (Exception ex)
{
MessageBox.Show("初始化类型信息错误：" + ex.Message);
}
}
public Paciente obtenerPersona(decimal historia)
{
objPaciente = null;
try
{
cmd = new SqlCommand();
cmd.Connection = Conexion.instancia().obtenerConexion();
cmd.CommandText = "PacienteObtener";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@historia",historia);
dr = cmd.ExecuteReader();
if (dr.Read())
{
objPaciente = new Paciente();
objPaciente.persona = (decimal)dr["idHistoria"];
objPaciente.nroHistoria = dr["HClinica"].ToString();
objPaciente.aPaterno = dr["aPaterno"].ToString();
objPaciente.aMaterno = dr["aMaterno"].ToString();
objPaciente.nombres = dr["nombres"].ToString();
objPaciente.sexo = dr["sexo"].ToString();
objPaciente.fechaNacimiento = (DateTime)dr["fnacimiento"];
objPaciente.ubigeo = dr["ubigeo"].ToString();
objPaciente.departamento = dr["departamento"].ToString();
objPaciente.provincia = dr["provincia"].ToString();
objPaciente.distrito = dr["distrito"].ToString();
// objPaciente.activo = (char)dr["activo"];
}
}
catch (Exception e)
{
throw e;
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
cmd.Connection.Dispose();
dr.Dispose();
}
return objPaciente;
}
private void id_TextChanged(object sender, EventArgs e)
{
myConn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|Database1.mdf;Integrated Security=True;User Instance=True");
myConn.Open();
string commandString = @"SELECT * FROM Asset WHERE [Asset ID] = '" + id.Text + "'";
SqlCommand cmd = new SqlCommand(commandString, myConn);
rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
name.Text = (string)rdr["Asset Name"];
ACost.Text = rdr["Asset Acquisition Cost"].ToString();
Life.Text = rdr["Asset Useful Life"].ToString();
commandString = @"SELECT [Status] FROM Status WHERE [Status ID] like '" +rdr["Asset Status"] + "'";
cmd = new SqlCommand(commandString, myConn);
}
if (rdr != null)
{
rdr.Dispose();
}
stat.Text = (string)cmd.ExecuteScalar();
}
BindingSource bSource = new BindingSource();
//SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT ([Asset Acquisition Cost] - [Depreciation Years]) AS [Test] From Asset Join Category ON Asset.[Asset Category] = Category.[Category ID] Where [Asset ID] =" + id.Text, myConn);
adapter = new SqlDataAdapter("SELECT * FROM History WHERE [Asset ID] = '" + id.Text + "'", myConn);
cmdBldr = new SqlCommandBuilder(adapter);
t = new DataTable();
adapter.Fill(t);
t.Columns[0].DefaultValue = id.Text;
BindingSource bndsource = new BindingSource();
bndsource.DataSource= t;
dataGridView1.DataSource = bndsource;
dataGridView1.Columns[0].Visible = false;
dataGridView1.Columns[6].Visible = false;
this.dataGridView1.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
myConn.Close();
}
public System.Data.SqlClient.SqlDataReader Reader1(string sqlcumle, System.Windows.Forms.ComboBox kombo)
{
baglantim();
System.Data.SqlClient.SqlCommand komut = new System.Data.SqlClient.SqlCommand("" + sqlcumle + "", baglantim());
System.Data.SqlClient.SqlDataReader rdr = komut.ExecuteReader();
kombo.Items.Clear();
try
{
while (rdr.Read())
{
kombo.Items.Add(rdr["grp_ismi"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ebubekir Stok Takip otomasyonu Otomasyon");
}
rdr.Dispose();
baglantim_kapat();
return(rdr);
}
public static BindingSource getRoles(SqlDataReader dataReader)
{
List<RolDTO> ListaRoles = new List<RolDTO>();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
RolDTO rol = new RolDTO();
rol.IdRol = Convert.ToInt32(dataReader["Id"]);
rol.Estado = Convert.ToBoolean(dataReader["Activo"]);
rol.NombreRol = Convert.ToString(dataReader["Nombre"]);
if (ListaRoles.Contains(rol))
{
RolDTO rol1 = ListaRoles.Find(delegate(RolDTO rolcito)
{
return rolcito.Equals(rol);
});
rol1.ListaFunc.Add(new FuncionalidadDTO(Convert.ToInt32(dataReader["Funcionalidad"]), Convert.ToString(dataReader["Descripcion"])));
}
else
{
rol.ListaFunc.Add(new FuncionalidadDTO(Convert.ToInt32(dataReader["Funcionalidad"]), Convert.ToString(dataReader["Descripcion"])));
ListaRoles.Add(rol);
}
}
}
BindingSource bs = new BindingSource();
foreach (RolDTO rol in ListaRoles)
{
bs.Add(rol);
}
dataReader.Close();
dataReader.Dispose();
return bs;
}
protected void Page_Load(object sender, EventArgs e)
{
string nome = string.Empty;
int id;
if (int.TryParse(Request.QueryString["id"], out id) == false)
{
lblMsg.Text = "Id inválido ou não existe !";
btnSIM.Visible = false;
btnNAO.Visible = false;
return;
}
conn.Open();
command = new SqlCommand();
command.CommandText = "SELECT Nome FROM tbPessoa WHERE Id = @a";
command.Connection = conn;
command.Parameters.AddWithValue("@a", id);
reader = command.ExecuteReader();
if (reader.Read() == true)
{
nome = reader.GetString(0);
lblMsg.Text = "Deseja mesmo excluir registro com o Nome: " + nome + " - e com o ID: " + id + " ?";
btnSIM.Visible = true;
btnNAO.Visible = true;
}
else
{
lblMsg.Text = "Não existe usuário com este ID, tente novamente !";
btnSIM.Visible = false;
btnNAO.Visible = false;
}
reader.Close();
reader.Dispose();
command.Dispose();
conn.Close();
conn.Dispose();
}
protected void CloseDbObjects(SqlConnection conn, SqlCommand cmd, SqlDataReader rdr)
{
if (conn != null)
{
conn.Close();
conn.Dispose();
conn = null;
}
if (cmd != null)
{
cmd.Dispose();
cmd = null;
}
if (rdr != null)
{
rdr.Close();
rdr.Dispose();
rdr = null;
}
}
public TList_Empreendimento_X_LanCCusto Select(TpBusca[] vBusca, Int32 vTop, string vNM_Campo)
{
bool podeFecharBco = false;
TList_Empreendimento_X_LanCCusto lista = new TList_Empreendimento_X_LanCCusto();
if (Banco_Dados == null)
{
this.CriarBanco_Dados(false);
podeFecharBco = true;
}
System.Data.SqlClient.SqlDataReader reader = this.ExecutarBusca(this.SqlCodeBusca(vBusca, vTop, vNM_Campo));
try
{
while (reader.Read())
{
TRegistro_Empreendimento_X_lanCCusto reg = new TRegistro_Empreendimento_X_lanCCusto();
if (!(reader.IsDBNull(reader.GetOrdinal("ID_Empreendimento"))))
{
reg.Id_empreendimento = reader.GetDecimal(reader.GetOrdinal("ID_Empreendimento"));
}
if (!(reader.IsDBNull(reader.GetOrdinal("ID_CCustoLan"))))
{
reg.Id_ccustolan = reader.GetDecimal(reader.GetOrdinal("ID_CCustoLan"));
}
lista.Add(reg);
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
return(lista);
}
public int ExecuteReader(System.Data.CommandType CommandType, StoredProcedureEntity Sproc)
{
System.Data.SqlClient.SqlDataReader result = default(System.Data.SqlClient.SqlDataReader);
List <SqlParameter> SQLParams = new List <SqlParameter>();
int i = 0;
try
{
SqlConnection selectConnection = default(SqlConnection);
SQLParams = GetParameters(Sproc);
using (selectConnection = new SqlConnection(_ConnectionString))
{
selectConnection.Open();
SqlCommand selectCMD = new SqlCommand(Sproc.StoredProcedureName, selectConnection);
selectCMD.CommandType = CommandType;
selectCMD.CommandTimeout = 180;
if (SQLParams.Count > 0)
{
selectCMD.Parameters.AddRange(SQLParams.ToArray());
}
result = selectCMD.ExecuteReader();
while (result.Read())
{
i += 1;
}
result.Close();
result.Dispose();
selectConnection.Close();
}
}
catch (Exception exc)
{
throw new Exception(exc.Message);
}
return(i);
}
protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack == false)
{
int id;
if (int.TryParse(Request.QueryString["id"], out id) == false)
{
lblMensagem.Text = "Id inválido !";
return;
}
//SqlConnection conn = new SqlConnection("Server=tcp:eot9dccau4.database.windows.net,1433;Database=testedb;User ID=alunos@eot9dccau4;Password=web2015$;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;");
conn.Open();
cmd = new SqlCommand("SELECT Nome, Endereco, Email, Nascimento, Peso FROM tbPessoa WHERE Id = @a", conn);
cmd.Parameters.AddWithValue("@a", id);
reader = cmd.ExecuteReader();
if (reader.Read() == true)
{
TxtNome.Text = reader.GetString(0);
TxtEndereco.Text = reader.GetString(1);
TxtEmail.Text = reader.GetString(2);
TxtNascimento.Text = reader.GetDateTime(3).ToString("dd/MM/yyyy");
TxtPeso.Text = reader.GetDouble(4).ToString("00.0");
}
else
{
lblMensagem.Text = "Id inválido seu burro, usuário anta, aprende a usar isso seu animal!";
}
reader.Close();
reader.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
public string GetSourceTypefromFeatureClass(string FeatureDataset, string FeatureClass)
{
System.Data.SqlClient.SqlCommand sourceTypeCommand = null;
System.Data.SqlClient.SqlDataReader sourceTypeReader = null;
try
{
// Build the SQL Statement that will be used to Retrieve the Source Type from the Table.
string getSourceInfoSQLStatement = null;
if (!System.String.IsNullOrEmpty(FeatureDataset))
{
getSourceInfoSQLStatement = "SELECT Load_Source "
+ "FROM " + _monitorTableName + " "
+ "WHERE Feature_Class_Name = '" + FeatureClass + "' AND "
+ " ((Feature_Dataset = '" + FeatureDataset + "') OR "
+ " (Old_Feature_Dataset = '" + FeatureDataset + "'))";
}
else
{
getSourceInfoSQLStatement = "SELECT Load_Source "
+ "FROM " + _monitorTableName + " "
+ "WHERE Feature_Class_Name = '" + FeatureClass + "'";
}
// Build the Command Object that will be used to retrieve the Source Type from the Table.
sourceTypeCommand = new System.Data.SqlClient.SqlCommand();
sourceTypeCommand.Connection = _importMonitorDatabaseConnection;
sourceTypeCommand.CommandType = System.Data.CommandType.Text;
sourceTypeCommand.CommandText = getSourceInfoSQLStatement;
sourceTypeCommand.CommandTimeout = 30;
// Populate a Data Reader using the Command Object.
sourceTypeReader = sourceTypeCommand.ExecuteReader();
// If any information was retrieved, get the Source Type that was retrieved.
string sourceTypeValue = "Unknown";
if (sourceTypeReader.HasRows)
{
// Retrieve the data pulled from the Table.
sourceTypeReader.Read();
// Get the Source Type from the Data Reader.
sourceTypeValue = (string)sourceTypeReader["Load_Source"];
}
// Return the Source Type to the calling routine. If the name was not found, the returned string will be NULL.
return(sourceTypeValue);
}
catch (Exception caught)
{
// Return a message to the calling routine indicating that this process failed.
return("The GetSourceTypefromFeatureClass() process FAILED with message: " + caught.Message);
}
finally
{
// If the SQL Client Source Type SQL Data Reader Object was instantiated, close it.
if (sourceTypeReader != null)
{
if (!sourceTypeReader.IsClosed)
{
sourceTypeReader.Close();
}
sourceTypeReader.Dispose();
sourceTypeReader = null;
}
// If the SQL Client Source Type SQL Command Object was instantiated, close it.
if (sourceTypeCommand != null)
{
sourceTypeCommand.Dispose();
sourceTypeCommand = null;
}
}
} // GetSourceTypefromFeatureClass
public TList_Plantio Select(Utils.TpBusca[] vBusca, Int32 vTop, string vNm_Campo)
{
TList_Plantio lista = new TList_Plantio();
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_Plantio reg = new TRegistro_Plantio();
if (!reader.IsDBNull(reader.GetOrdinal("Id_Plantio")))
{
reg.Id_plantio = reader.GetDecimal(reader.GetOrdinal("Id_Plantio"));
}
if (!reader.IsDBNull(reader.GetOrdinal("id_cultura")))
{
reg.Id_cultura = reader.GetDecimal(reader.GetOrdinal("id_cultura"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_cultura")))
{
reg.Ds_cultura = reader.GetString(reader.GetOrdinal("ds_cultura"));
}
if (!reader.IsDBNull(reader.GetOrdinal("sigla_unidade")))
{
reg.Sigla_unidade = reader.GetString(reader.GetOrdinal("sigla_unidade"));
}
if (!reader.IsDBNull(reader.GetOrdinal("cd_produto")))
{
reg.Cd_produto = reader.GetString(reader.GetOrdinal("cd_produto"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ds_produto")))
{
reg.Ds_produto = reader.GetString(reader.GetOrdinal("ds_produto"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DS_Observacao")))
{
reg.Ds_observacao = reader.GetString(reader.GetOrdinal("DS_Observacao"));
}
if (!reader.IsDBNull(reader.GetOrdinal("AnoSafra")))
{
reg.Anosafra = reader.GetString(reader.GetOrdinal("AnoSafra"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DS_Safra")))
{
reg.Ds_safra = reader.GetString(reader.GetOrdinal("DS_Safra"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DT_IniPlantio")))
{
reg.Dt_iniplantio = reader.GetDateTime(reader.GetOrdinal("DT_IniPlantio"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DT_FinPlantio")))
{
reg.Dt_finplantio = reader.GetDateTime(reader.GetOrdinal("DT_FinPlantio"));
}
if (!reader.IsDBNull(reader.GetOrdinal("QT_SementesPorMetro")))
{
reg.Qt_sementespormetro = reader.GetDecimal(reader.GetOrdinal("QT_SementesPorMetro"));
}
if (!reader.IsDBNull(reader.GetOrdinal("EspacoSemente")))
{
reg.Espacosemente = reader.GetDecimal(reader.GetOrdinal("EspacoSemente"));
}
lista.Add(reg);
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
return(lista);
}
/// <summary>
/// Maps from data reader.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="reader">The reader.</param>
/// <param name="closeReader">if set to <c>true</c> [close reader].</param>
/// <returns></returns>
protected List <T> MapFromDataReader <T>(ref System.Data.SqlClient.SqlDataReader reader, bool closeReader)
{
method = MethodBase.GetCurrentMethod();
try
{
Type newType = typeof(T);
if (_currentType != newType)
{
_currentType = newType;
FindColumnNames(_currentType);
}
List <T> returnList = new List <T>();
object[] values = new object[reader.FieldCount];
Dictionary <string, int> columnsIds = new Dictionary <string, int>(reader.FieldCount);
List <string> schemaFieldsName = null;
while (reader.Read())
{
reader.GetValues(values);
T objTarget = Activator.CreateInstance <T>();
if (schemaFieldsName == null)
{
using (DataTable dtSchema = reader.GetSchemaTable())
{
schemaFieldsName = dtSchema.AsEnumerable().Select(x => x.Field <string>("ColumnName").ToUpper()).ToList();
}
}
foreach (MemberInfo member in _PropertiesToColumnsNames.Keys)
{
string columnName = _PropertiesToColumnsNames[member];
if (schemaFieldsName.Contains(columnName.ToUpper()))
{
int columnId = 0;
if (columnsIds.ContainsKey(columnName))
{
columnId = columnsIds[columnName];
}
else
{
columnId = reader.GetOrdinal(columnName);
columnsIds.Add(columnName, columnId);
}
PropertyInfo pMember = (PropertyInfo)member;
object value;
try
{ value = ConvertValueToType(values[columnId], pMember.PropertyType); }
catch { value = values[columnId]; }
pMember.SetValue(objTarget, value, null);
}
}
returnList.Add(objTarget);
}
if (closeReader)
{
reader.Dispose();
}
return(returnList.Count > 0 ? returnList : null);
}
catch (Exception ex)
{
log.escribirError(ex.Message, stackTrace.GetFrame(1).GetMethod().Name, method.Name, method.ReflectedType.Name);
throw new Exception(ex.InnerException.ToString());
}
finally
{
if (!reader.IsClosed)
{
reader.Close();
}
reader.Dispose();
}
}
public TList_Plantio_X_Talhoes Select(Utils.TpBusca[] vBusca, Int32 vTop, string vNm_Campo)
{
TList_Plantio_X_Talhoes lista = new TList_Plantio_X_Talhoes();
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_Plantio_X_Talhoes reg = new TRegistro_Plantio_X_Talhoes();
if (!reader.IsDBNull(reader.GetOrdinal("Id_Plantio")))
{
reg.Id_plantio = reader.GetDecimal(reader.GetOrdinal("Id_Plantio"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ID_Talhao")))
{
reg.Id_talhao = reader.GetDecimal(reader.GetOrdinal("ID_Talhao"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DS_Talhao")))
{
reg.Ds_talhao = reader.GetString(reader.GetOrdinal("DS_Talhao"));
}
if (!reader.IsDBNull(reader.GetOrdinal("area_talhao")))
{
reg.Area_talhao = reader.GetDecimal(reader.GetOrdinal("area_talhao"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ID_Area")))
{
reg.Id_area = reader.GetDecimal(reader.GetOrdinal("ID_Area"));
}
if (!reader.IsDBNull(reader.GetOrdinal("DS_Area")))
{
reg.Ds_area = reader.GetString(reader.GetOrdinal("DS_Area"));
}
if (!reader.IsDBNull(reader.GetOrdinal("CD_Fazenda")))
{
reg.Cd_fazenda = reader.GetString(reader.GetOrdinal("CD_Fazenda"));
}
if (!reader.IsDBNull(reader.GetOrdinal("NM_Fazenda")))
{
reg.Nm_fazenda = reader.GetString(reader.GetOrdinal("NM_Fazenda"));
}
if (!reader.IsDBNull(reader.GetOrdinal("sigla_unidade")))
{
reg.Sigla_unidade = reader.GetString(reader.GetOrdinal("sigla_unidade"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Area_Plantada")))
{
reg.Area_plantada = reader.GetDecimal(reader.GetOrdinal("Area_Plantada"));
}
if (!reader.IsDBNull(reader.GetOrdinal("producao_prevista")))
{
reg.Producao_prevista = reader.GetDecimal(reader.GetOrdinal("producao_prevista"));
}
lista.Add(reg);
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
return(lista);
}
} // ReadParameter
/// <summary>
/// Retrieves a list of Parameters based on the Parameter Prefix that is passed. If no prefix is passed to the method,
/// the names of all parameters in the Table are returned.
/// </summary>
/// <param name="ProcessParameterPrefix">
/// The prefix of the Parameter Names that are to be retrieved. If all Parameters for a process have a prefix on their names, this
/// value can be used to return just the parameters for that process.
/// </param>
/// <returns>
/// A String Collection of Parameter Names from the current parameter table.
/// </returns>
public System.Collections.Specialized.StringCollection RetrieveProcessParameterList(string ProcessParameterPrefix = "")
{
System.Data.SqlClient.SqlCommand parameterNameListCommand = null;
System.Data.SqlClient.SqlDataReader parameterNameListReader = null;
System.Collections.Specialized.StringCollection parameterNameCollection = null;
try
{
// Build the SQL Statement that will be used to retrieve the List of Parameters from the Parameter Table.
string parameterNamesSQLStatement = "SELECT [Parameter_Name] "
+ "FROM " + _parameterTableName + " "
+ "WHERE [Parameter_Name] LIKE '" + ProcessParameterPrefix + "%' "
+ "ORDER BY [Parameter_Name]";
// Create the command object and that will be used to retrieve the Parameter Name List from the Parameter Table.
parameterNameListCommand = new SqlCommand();
parameterNameListCommand.Connection = _parameterDatabaseConnection;
parameterNameListCommand.CommandType = System.Data.CommandType.Text;
parameterNameListCommand.CommandText = parameterNamesSQLStatement;
parameterNameListCommand.CommandTimeout = 20;
// Use the command that was just created to populate a SQL Data Reader Object with the Parameter Names.
parameterNameListReader = parameterNameListCommand.ExecuteReader();
// Instantiate the String Collection of Parameter Names that will be populated and returned to the calling method.
parameterNameCollection = new System.Collections.Specialized.StringCollection();
// If the Data Reader contains rows, the List of Parameter Names was retrieved so populate a String Collection with the names and
// return the List of Parameter Names retrieved from the Parameter Table to the calling routine. Otherwise, return an empty String
// Collection to the calling method to indicate that the names were not obtained.
if (parameterNameListReader.HasRows)
{
// Populate the Collection of Parameter Names.
while (parameterNameListReader.Read())
{
// Retrieve the Current Parameter Name from the SQL Data Reader Object.
string currentParameterName = (string)parameterNameListReader["Parameter_Name"];
// Add the Current Name to the Parameter Name Collection.
parameterNameCollection.Add(currentParameterName);
}
}
else
{
// Return a NULL Pointer to indicate that this method failed.
return(null);
}
// Return the List of Parameter Names to the calling method.
return(parameterNameCollection);
}
catch
{
// Return NULL to the calling routine to indicate that the process failed and that the parameter list was not successfully retrieved.
return(null);
}
finally
{
// If the Parameter Name String Collection Object has been instantiated, close it.
if (parameterNameCollection != null)
{
parameterNameCollection = null;
}
// If the Parameter Name List SQL Command Object has been isntantiated, close it.
if (parameterNameListCommand != null)
{
parameterNameListCommand.Dispose();
parameterNameListCommand = null;
}
// If the Parameter Name List SQL Data Reader Object has been instantiated, close it.
if (parameterNameListReader != null)
{
if (!parameterNameListReader.IsClosed)
{
parameterNameListReader.Close();
}
parameterNameListReader.Dispose();
parameterNameListReader = null;
}
}
} // RetrieveProcessParameterList
private void RefreshCache()
{
sql.SqlConnection sqlConn = null;
sql.SqlCommand sqlComm = null;
sql.SqlDataReader sqlReader = null;
Dictionary <string, object> formGroupAccessConfig = null;
List <string> groups = null;
string currentFormName = string.Empty;
try
{
log.WriteToLog(this._enableLogging, this._logfilePath, "DEBUG", "RefreshCache", "Start refreshing cache...");
formGroupAccessConfig = new Dictionary <string, object>();
sqlConn = new sql.SqlConnection();
sqlConn.ConnectionString = this._dbConnectionString;
sqlConn.Open();
log.WriteToLog(this._enableLogging, this._logfilePath, "DEBUG", "RefreshCache", "Connected to database: " + sqlConn.Database + " on server " + sqlConn.DataSource);
sqlComm = new sql.SqlCommand(this._accessControlStoredProcedure, sqlConn);
sqlComm.CommandType = System.Data.CommandType.StoredProcedure;
sqlReader = sqlComm.ExecuteReader(System.Data.CommandBehavior.Default);
if (sqlReader.HasRows == true)
{
while (sqlReader.Read() == true)
{
if (string.IsNullOrEmpty(currentFormName) == true)
{
currentFormName = sqlReader["FormName"].ToString().Trim().ToUpper();
groups = new List <string>();
groups.Add(sqlReader["GroupName"].ToString().Trim().ToUpper());
}
else if (currentFormName.Equals(sqlReader["FormName"].ToString().Trim().ToUpper(), StringComparison.InvariantCulture) == true)
{
groups.Add(sqlReader["GroupName"].ToString().Trim().ToUpper());
}
else
{
formGroupAccessConfig.Add(currentFormName, groups);
currentFormName = sqlReader["FormName"].ToString().Trim().ToUpper();
groups = new List <string>();
groups.Add(sqlReader["GroupName"].ToString().Trim().ToUpper());
}
log.WriteToLog(this._enableLogging, this._logfilePath, "DEBUG", "RefreshCache", "Adding form [" + sqlReader["FormName"].ToString() + "] and group [" + sqlReader["GroupName"].ToString() + "] to cache");
}
}
else
{
log.WriteToLog(this._enableLogging, this._logfilePath, "DEBUG", "RefreshCache", "Stored procedure [sp_GetFormGroupAccessList] returned no results");
}
cache.MemoryCache.Default.Add(_cacheName, formGroupAccessConfig, DateTime.Now.AddHours(this._cacheRefreshInterval));
log.WriteToLog(this._enableLogging, this._logfilePath, "DEBUG", "RefreshCache", "Cache updated successfully");
}
catch (Exception ex)
{
log.WriteToLog(this._enableLogging, this._logfilePath, "ERROR", "RefreshCache", ex.ToString());
throw;
}
finally
{
if (sqlConn != null)
{
sqlConn.Close();
sqlConn.Dispose();
sqlConn = null;
}
if (sqlComm != null)
{
sqlComm.Dispose();
sqlComm = null;
}
if (sqlReader != null)
{
sqlReader.Close();
sqlReader.Dispose();
sqlReader = null;
}
}
}
} // GetLayerSourceInfo()
public PDX.BTS.DataMaintenance.MaintTools.FeatureClassSourceInfo GetLayerSourceInfo(string FeatureClassName, string OutputGeodatabaseName)
{
System.Data.SqlClient.SqlCommand getSourceInfoSQLCommand = null;
System.Data.SqlClient.SqlDataReader getSourceInfoSQLDataReader = null;
ESRI.ArcGIS.esriSystem.IPropertySet sourcePropertySet = null;
PDX.BTS.DataMaintenance.MaintTools.GeneralUtilities maintenanceGeneralUtilities = null;
PDX.BTS.DataMaintenance.MaintTools.PathManager shapefilePathGenerator = null;
PDX.BTS.DataMaintenance.MaintTools.FeatureClassUtilities featureClassUtilities = null;
try
{
// Make sure there is a valid connection to the Load Metadata Database before attempting to update it.
if (!ConnecttoImportMonitorDatabase())
{
// Send a Message to the calling application to let the user know why this process failed.
if (ErrorMessage != null)
{
ErrorMessage(" Could not establish a connection to the Load Metadata Database! LoaderUtilities.DatabaseTools.GetLayerSourceInfo() Failed!");
}
// Return a NULL String to the calling routine to indicate that this process failed.
return(null);
}
// Build the SQL Statement that will be used to retrieve the Source Information for the Feature Class.
string getSourceInfoSQLStatement = "SELECT [Load_Source], [Data_Source], [Dataset], [GeoDB_File], [Source_Server_Name],"
+ " [Source_Instance], [Source_Database_Name], [Source_Database_User_Name],"
+ " [Input_Dataset_Name], [Output_Database] "
+ "FROM " + _monitorTableName + " "
+ "WHERE [Feature_Class_Name] = '" + FeatureClassName + "'";
// If the Output Geodatabase Name was passed to this method, include it in the query.
if (!System.String.IsNullOrEmpty(OutputGeodatabaseName))
{
// Add the Output Geodatabase Name to the SQL Statement.
getSourceInfoSQLStatement = getSourceInfoSQLStatement + " AND [Output_Database] = '" + OutputGeodatabaseName + "'";
}
// Build the SQL Command Object that will be used to retrieve the Source Data Info for the specified Feature Class.
getSourceInfoSQLCommand = new System.Data.SqlClient.SqlCommand();
getSourceInfoSQLCommand.Connection = _importMonitorDatabaseConnection;
getSourceInfoSQLCommand.CommandType = System.Data.CommandType.Text;
getSourceInfoSQLCommand.CommandText = getSourceInfoSQLStatement;
getSourceInfoSQLCommand.CommandTimeout = 30;
// Use the SQL Command Object that was just instantiated to populate a SQL Data Reader Object with the info about the specified
// Feature Class.
getSourceInfoSQLDataReader = getSourceInfoSQLCommand.ExecuteReader();
// Instantiate a Feature Class Source Info Object.
PDX.BTS.DataMaintenance.MaintTools.FeatureClassSourceInfo currentFeatureClassSourceInfo = new PDX.BTS.DataMaintenance.MaintTools.FeatureClassSourceInfo();
// If some information was retrieved, populate a Feature Class Source Info Object and return it to the calling method. If not,
// return a NULL Pointer to indicate that this method failed.
if (getSourceInfoSQLDataReader.HasRows)
{
if (getSourceInfoSQLDataReader.Read())
{
// Populate the New Feature Class Source Info Object with the Source Info for the specified Feature Class.
// Populate the Source Type Property.
if (!getSourceInfoSQLDataReader.IsDBNull(getSourceInfoSQLDataReader.GetOrdinal("Load_Source")))
{
currentFeatureClassSourceInfo.SourceType = (string)getSourceInfoSQLDataReader["Load_Source"];
}
else
{
currentFeatureClassSourceInfo.SourceType = "";
}
// Populate the Source Data Share Property.
if (!getSourceInfoSQLDataReader.IsDBNull(getSourceInfoSQLDataReader.GetOrdinal("Data_Source")))
{
currentFeatureClassSourceInfo.SourceDataShare = (string)getSourceInfoSQLDataReader["Data_Source"];
}
else
{
currentFeatureClassSourceInfo.SourceDataShare = "";
}
// Populate the Source Dataset Property.
if (!getSourceInfoSQLDataReader.IsDBNull(getSourceInfoSQLDataReader.GetOrdinal("Dataset")))
{
currentFeatureClassSourceInfo.SourceDataset = (string)getSourceInfoSQLDataReader["Dataset"];
}
else
{
currentFeatureClassSourceInfo.SourceDataset = "";
}
// Populate the Source Geodatabase File Property.
if (!getSourceInfoSQLDataReader.IsDBNull(getSourceInfoSQLDataReader.GetOrdinal("GeoDB_File")))
{
currentFeatureClassSourceInfo.SourceGeoDBFile = (string)getSourceInfoSQLDataReader["GeoDB_File"];
}
else
{
currentFeatureClassSourceInfo.SourceGeoDBFile = "";
}
// Populate the Source Server Name Property.
if (!getSourceInfoSQLDataReader.IsDBNull(getSourceInfoSQLDataReader.GetOrdinal("Source_Server_Name")))
{
currentFeatureClassSourceInfo.SourceServerName = (string)getSourceInfoSQLDataReader["Source_Server_Name"];
}
else
{
currentFeatureClassSourceInfo.SourceServerName = "";
}
// Populate the Source Instance Property.
if (!getSourceInfoSQLDataReader.IsDBNull(getSourceInfoSQLDataReader.GetOrdinal("Source_Instance")))
{
currentFeatureClassSourceInfo.SourceInstance = (string)getSourceInfoSQLDataReader["Source_Instance"];
}
else
{
currentFeatureClassSourceInfo.SourceInstance = "";
}
// Populate the Source Database Name Property.
if (!getSourceInfoSQLDataReader.IsDBNull(getSourceInfoSQLDataReader.GetOrdinal("Source_Database_Name")))
{
currentFeatureClassSourceInfo.SourceDatabaseName = (string)getSourceInfoSQLDataReader["Source_Database_Name"];
}
else
{
currentFeatureClassSourceInfo.SourceDatabaseName = "";
}
// Populate the Source Database User Name Property.
if (!getSourceInfoSQLDataReader.IsDBNull(getSourceInfoSQLDataReader.GetOrdinal("Source_Database_User_Name")))
{
currentFeatureClassSourceInfo.SourceDatabaseUser = (string)getSourceInfoSQLDataReader["Source_Database_User_Name"];
}
else
{
currentFeatureClassSourceInfo.SourceDatabaseUser = "";
}
// Populate the Input Feature Class Name Property.
if (!getSourceInfoSQLDataReader.IsDBNull(getSourceInfoSQLDataReader.GetOrdinal("Input_Dataset_Name")))
{
currentFeatureClassSourceInfo.SourceFeatureClassName = (string)getSourceInfoSQLDataReader["Input_Dataset_Name"];
}
else
{
currentFeatureClassSourceInfo.SourceFeatureClassName = "";
}
// Populate the Output Geodatabase Property.
if (!getSourceInfoSQLDataReader.IsDBNull(getSourceInfoSQLDataReader.GetOrdinal("Output_Database")))
{
currentFeatureClassSourceInfo.OutputGeodatabaseName = (string)getSourceInfoSQLDataReader["Output_Database"];
}
else
{
currentFeatureClassSourceInfo.OutputGeodatabaseName = "";
}
}
else
{
// Send a Message to the calling application to let the user know why this process failed.
if (ErrorMessage != null)
{
ErrorMessage(" No Source Information for the Specified Feature Class was found in the Load Metadata Database! LoaderUtilities.DatabaseTools.GetLayerSourceInfo() Failed!");
}
// Return a NULL String to the calling routine to indicate that this process failed.
return(null);
}
}
else
{
// Send a Message to the calling application to let the user know why this process failed.
if (ErrorMessage != null)
{
ErrorMessage(" The query to retrieve Source Information for the Specified Feature Class failed to retrieve any data from the Load Metadata Database! LoaderUtilities.DatabaseTools.GetLayerSourceInfo() Failed!");
}
// Return a NULL String to the calling routine to indicate that this process failed.
return(null);
}
// Instantiate a Feature Class Utilities Object.
featureClassUtilities = new PDX.BTS.DataMaintenance.MaintTools.FeatureClassUtilities();
// Build the Property Set for the Source Dataset and determine its Last Update Date.
sourcePropertySet = new ESRI.ArcGIS.esriSystem.PropertySet();
switch (currentFeatureClassSourceInfo.SourceType)
{
case "FILEGEODB":
// Build the File Geodatabase PropertySet.
sourcePropertySet.SetProperty("DATABASE", currentFeatureClassSourceInfo.SourceGeoDBFile);
// Determine the File Geodatabase Feature Class Last Update Date.
currentFeatureClassSourceInfo.SourceLastUpdateDate = featureClassUtilities.GetFileGeodatabaseFeatureClassLastUpdate(currentFeatureClassSourceInfo.SourceGeoDBFile, currentFeatureClassSourceInfo.SourceFeatureClassName);
// Exit this case.
break;
case "PERSONALGEODB":
// Build the Personal Geodatabase PropertySet.
sourcePropertySet.SetProperty("DATABASE", currentFeatureClassSourceInfo.SourceGeoDBFile);
// Determine the Source Personal Geodatabase Last Update Date.
currentFeatureClassSourceInfo.SourceLastUpdateDate = featureClassUtilities.GetPersonalGeoDBLastUpdateDate(currentFeatureClassSourceInfo.SourceGeoDBFile);
// Exit this case.
break;
case "GEODATABASE":
// Determine the Server User Password.
maintenanceGeneralUtilities = new PDX.BTS.DataMaintenance.MaintTools.GeneralUtilities();
string serverUserPassword = maintenanceGeneralUtilities.RetrieveParameterValue(currentFeatureClassSourceInfo.SourceDatabaseUser + "_Password", _parameterTableName, _parameterDatabaseConnectionString, true);
// Build the Enterprise Geodatabase Property Set.
sourcePropertySet.SetProperty("SERVER", currentFeatureClassSourceInfo.SourceServerName);
sourcePropertySet.SetProperty("INSTANCE", currentFeatureClassSourceInfo.SourceInstance);
sourcePropertySet.SetProperty("DATABASE", currentFeatureClassSourceInfo.SourceDatabaseName);
sourcePropertySet.SetProperty("USER", currentFeatureClassSourceInfo.SourceDatabaseUser);
sourcePropertySet.SetProperty("PASSWORD", serverUserPassword);
sourcePropertySet.SetProperty("VERSION", "SDE.Default");
// Determine the Enterprise Geodatabase Feature Class Last Udpate Date.
currentFeatureClassSourceInfo.SourceLastUpdateDate = featureClassUtilities.GetEntGeoDBFeatureClassLastUpdate(currentFeatureClassSourceInfo.SourceFeatureClassName, currentFeatureClassSourceInfo.SourceServerName, currentFeatureClassSourceInfo.SourceDatabaseName, currentFeatureClassSourceInfo.SourceDatabaseUser, serverUserPassword);
// Exit this case.
break;
case "SHAPEFILE":
// Instantiate a City of Portland Path Manager Object that will be used to determine the Path to the Source Shapefile.
shapefilePathGenerator = new PDX.BTS.DataMaintenance.MaintTools.PathManager();
string shapefileDirectoryPath = shapefilePathGenerator.BuildShapefileDirectoryPath(currentFeatureClassSourceInfo.SourceDataShare, currentFeatureClassSourceInfo.SourceDataset, currentFeatureClassSourceInfo.SourceFeatureClassName);
// Build the Shapefile PropertySet.
sourcePropertySet.SetProperty("DATABASE", shapefileDirectoryPath);
// Determine the Source Shapefile Last Update Date.
string fullShapefilePath = shapefilePathGenerator.BuildShapefilePath(currentFeatureClassSourceInfo.SourceDataShare, currentFeatureClassSourceInfo.SourceDataset, currentFeatureClassSourceInfo.SourceFeatureClassName);
currentFeatureClassSourceInfo.SourceLastUpdateDate = featureClassUtilities.GetShapefileLastUpdateDate(fullShapefilePath);
// Exit this case.
break;
default:
// Exit this case.
break;
}
// Set the Source PropertySet Property of the Current Source Feature Class Info Object.
currentFeatureClassSourceInfo.SourceDatasetPropertySet = sourcePropertySet;
// Return the populated Current Feature Class Source Info Object to the calling method.
return(currentFeatureClassSourceInfo);
}
catch (Exception caught)
{
// Send a Message to the calling application to let the user know why this process failed.
if (ErrorMessage != null)
{
ErrorMessage(" The LoaderUtilities.DatabaseTools.GetLayerSourceInfo() Method Failed with error message: " + caught.Message);
}
// Return a NULL String to the calling routine to indicate that this process failed.
return(null);
}
finally
{
// If the Get Source Info SQL Data Reader Object was instantiated, close it.
if (getSourceInfoSQLDataReader != null)
{
if (!getSourceInfoSQLDataReader.IsClosed)
{
getSourceInfoSQLDataReader.Close();
}
getSourceInfoSQLDataReader.Dispose();
getSourceInfoSQLDataReader = null;
}
// If the Get Source Info SQL Command Object was instantiated, close it.
if (getSourceInfoSQLCommand != null)
{
getSourceInfoSQLCommand.Dispose();
getSourceInfoSQLCommand = null;
}
}
} // GetLayerSourceInfo()
