private void AddSocio(Socio S)
{
{
if (!verifySGBDConnection())
{
return;
}
String d = dateTimePicker2.Text.Substring(6, 4) + dateTimePicker2.Text.Substring(3, 2) + dateTimePicker2.Text.Substring(0, 2);
var date = DateTime.ParseExact(d, "yyyymmdd", null);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
if (string.IsNullOrEmpty(comboBox1.Text))
{
cmd.CommandText = "INSERT INTO Clube.Socio ([id_pessoa],[data_inscricao]) VALUES (@id_pessoa,@data_inscricao);";
cmd.Parameters.Clear();
cmd.Parameters.Add("@id_pessoa", SqlDbType.Int).Value = S.PessoaID;
cmd.Parameters.Add("@data_inscricao", SqlDbType.Date).Value = date;
cmd.Connection = cn;
}
else
{
cmd.CommandText = "Select id_claque from Clube.Claque where nome = @nome";
cmd.Parameters.Add("nome", SqlDbType.VarChar, 100).Value = comboBox1.Text;
var id_claque = cmd.ExecuteScalar();
cmd.CommandText = "INSERT INTO Clube.Socio ([id_pessoa],[data_inscricao], [id_claque]) VALUES (@id_pessoa,@data_inscricao,@id_claque);";
cmd.Parameters.Clear();
cmd.Parameters.Add("@id_pessoa", SqlDbType.Int).Value = S.PessoaID;
cmd.Parameters.Add("@data_inscricao", SqlDbType.Date).Value = date;
cmd.Parameters.Add("@id_claque", SqlDbType.Int).Value = id_claque;
cmd.Connection = cn;
}
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception("Failed to update contact in database. \n ERROR MESSAGE: \n" + ex.Message);
}
finally
{
cn.Close();
}
this.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
if (!verifySGBDConnection())
{
return;
}
Socio S = new Socio();
String commandText = "select * FROM Clube.Pessoa WHERE nif=@getnif";
SqlCommand cmd = new SqlCommand(commandText, cn);
cmd.Parameters.AddWithValue("@getnif", SqlDbType.Int).Value = nif;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
S.PessoaID = reader["id_pessoa"].ToString();
}
cn.Close();
AddSocio(S);
}
private void loadSocios()
{
string filtro = " where 1=1 ";
currentselected = "socio";
if (!verifySGBDConnection())
{
return;
}
if (comboBox6.Items.Count < 1)
{
SqlCommand cmd1 = new SqlCommand("select distinct claque from socios where claque is not null ", cn);
cmd1.ExecuteNonQuery();
DataTable dt1 = new DataTable();
SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
da1.Fill(dt1);
comboBox6.Items.Clear();
foreach (DataRow dr1 in dt1.Rows)
{
comboBox6.Items.Add(dr1["claque"].ToString());
}
cmd1.Dispose();
}
if (!String.IsNullOrEmpty(textBox2.Text))
{
filtro += " and nome like '%'+@nome+'%'";
}
if (comboBox6.SelectedIndex > -1)
{
filtro += " and claque = '" + comboBox6.SelectedItem + "'";
}
filtro += " and data_inscricao >= '" + dateTimePicker7.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'";
filtro += " and data_inscricao <= '" + dateTimePicker6.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'";
filtro += " and data_nasc >= '" + dateTimePicker5.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'";
filtro += " and data_nasc <= '" + dateTimePicker4.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'";
SqlCommand cmd = new SqlCommand("select * from socios" + filtro, cn);
if (!String.IsNullOrEmpty(textBox2.Text))
{
cmd.Parameters.AddWithValue("nome", textBox2.Text);
}
SqlDataReader reader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Columns.Add("ID");
dt.Columns.Add("Nome");
dt.Columns.Add("Data Inscrição");
dt.Columns.Add("Data Nascimento");
dt.Columns.Add("Claque");
while (reader.Read())
{
Socio S = new Socio();
S.SocioID = reader["id_socio"].ToString();
S.Nome = reader["nome"].ToString();
S.DataInsc = reader["data_inscricao"].ToString().Substring(0, 10);
S.DataNasc = reader["data_nasc"].ToString().Substring(0, 10);
S.Claque = reader["claque"].ToString();
dt.Rows.Add(S.SocioID, S.Nome, S.DataInsc, S.DataNasc, S.Claque);
}
dataGridView1.DataSource = dt;
reader.Close();
cmd.Dispose();
cn.Close();
}