private void button3_Click(object sender, EventArgs e)
{
JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
List <estrutura> ls = new List <estrutura>();
string[] SQLTipo = new string[dataGridView2.Rows.Count];
for (int i = 0; i < dataGridView2.Rows.Count; i++)
{
estrutura estrut = new estrutura(dataGridView2.Rows[i].Cells[0].EditedFormattedValue.ToString(),
Convert.ToBoolean(dataGridView2.Rows[i].Cells[1].EditedFormattedValue.ToString()),
Convert.ToBoolean(dataGridView2.Rows[i].Cells[2].EditedFormattedValue.ToString()),
Convert.ToBoolean(dataGridView2.Rows[i].Cells[3].EditedFormattedValue.ToString()),
Convert.ToBoolean(dataGridView2.Rows[i].Cells[4].EditedFormattedValue.ToString()),
Convert.ToBoolean(dataGridView2.Rows[i].Cells[5].EditedFormattedValue.ToString()),
Convert.ToBoolean(dataGridView2.Rows[i].Cells[6].EditedFormattedValue.ToString()),
Convert.ToBoolean(dataGridView2.Rows[i].Cells[7].EditedFormattedValue.ToString()),
Convert.ToBoolean(dataGridView2.Rows[i].Cells[8].EditedFormattedValue.ToString()),
Convert.ToBoolean(dataGridView2.Rows[i].Cells[9].EditedFormattedValue.ToString()),
Convert.ToBoolean(dataGridView2.Rows[i].Cells[10].EditedFormattedValue.ToString()));
ls.Add(estrut);
}
resultado = serializer.Serialize(ls);
usuarioTableAdapter.UpdateQuery(textBox1.Text, textBox2.Text, textBox3.Text, resultado, Convert.ToInt32(textBox4.Text));
this.usuarioTableAdapter.Fill(this.d12rnams4f6a7nDataSet.usuario);
}
private void button2_Click(object sender, EventArgs e)
{
if (button2.Text == "Gerar Dados")
{
achouarquivo = false;
achoutabela = false;
id = 0;
this.dataGridView2.Columns.Clear();
this.dataGridView2.Rows.Clear();
this.dataGridView2.Columns.Add("Campo", "Campo");
DataGridViewComboBoxColumn tipos = new DataGridViewComboBoxColumn();
var tiposlst = new List <string>()
{
"Caracter", "Data", "Moeda", "Numero"
};
tipos.DataSource = tiposlst;
tipos.HeaderText = "Tipo";
tipos.DataPropertyName = "Tipo";
this.dataGridView2.Columns.Add(tipos);
this.dataGridView2.Columns.Add("Tamanho", "Tamanho");
this.dataGridView2.Columns.Add("Chave", "Chave");
if (ExcelSelector.FileName.Length == 0 || comboBox1.Text.Length == 0)
{
MessageBox.Show("Por favor, selecione algum arquivo\ne depois selecione a planilha ativa.");
return;
}
foreach (DataGridViewColumn coluna in dataGridView1.Columns)
{
this.dataGridView2.Rows.Add(coluna.HeaderText, "Caracter", 100, 0);
}
var table = layoutTableAdapter1.GetData();
// Print column 0 of each returned row.
foreach (DataRow linha in table)
{
if (linha[1].ToString() == comboBox1.Text.Replace("$", ""))
{
id = Convert.ToInt32(linha[0].ToString());
achoutabela = true;
}
if (linha[3].ToString() == ExcelSelector.FileName.ToString())
{
id = Convert.ToInt32(linha[0].ToString());
achouarquivo = true;
}
}
button2.Text = "Salvar Dados";
dataGridView1.Visible = false;
dataGridView2.Visible = true;
comboBox1.Visible = false;
button1.Visible = false;
}
else
{
button2.Text = "Gerar Dados";
dataGridView1.Visible = true;
dataGridView2.Visible = false;
comboBox1.Visible = true;
button1.Visible = true;
JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
List <estrutura> ls = new List <estrutura>();
var Campos = "";
string[] SQLTipo = new string[dataGridView2.Rows.Count];
for (int i = 0; i < dataGridView2.Rows.Count; i++)
{
estrutura estrut = new estrutura(dataGridView2.Rows[i].Cells[0].EditedFormattedValue.ToString(),
dataGridView2.Rows[i].Cells[1].EditedFormattedValue.ToString(),
Convert.ToInt32(dataGridView2.Rows[i].Cells[2].EditedFormattedValue.ToString()),
Convert.ToInt32(dataGridView2.Rows[i].Cells[3].EditedFormattedValue.ToString()));
ls.Add(estrut);
Campos = Campos + '"' + dataGridView2.Rows[i].Cells[0].EditedFormattedValue.ToString() + '"';
var Tipo = "";
switch (dataGridView2.Rows[i].Cells[1].EditedFormattedValue.ToString())
{
case "Caracter":
Tipo = " char(" + Convert.ToInt32(dataGridView2.Rows[i].Cells[2].EditedFormattedValue.ToString()) + ")";
SQLTipo[i] = "C";
break;
case "Data":
Tipo = " date";
SQLTipo[i] = "D";
break;
case "Moeda":
Tipo = " money";
SQLTipo[i] = "N";
break;
case "Numero":
Tipo = " bigint";
SQLTipo[i] = "N";
break;
}
Campos = Campos + Tipo;
if (i < dataGridView2.Rows.Count - 1)
{
Campos = Campos + ",";
}
}
resultado = serializer.Serialize(ls);
var table = layoutTableAdapter1.GetData();
var grava = false;
if (achouarquivo == true && achoutabela == true)
{
DialogResult Opcao = MessageBox.Show("Planiha já existe.Deseja regravar?", "ATENÇÃO", MessageBoxButtons.YesNo);
if (Opcao == DialogResult.No)
{
grava = false;
}
else
{
layoutTableAdapter1.UpdateQuery(comboBox1.Text.Replace("$", ""), resultado, ExcelSelector.FileName.ToString(), id);
d12rnams4f6a7nDataSet1.AcceptChanges();
grava = true;
}
}
else
{
layoutTableAdapter1.InsertQuery(comboBox1.Text.Replace("$", ""), resultado, ExcelSelector.FileName.ToString());
d12rnams4f6a7nDataSet1.AcceptChanges();
grava = true;
}
if (grava == true)
{
string connectionString = DoctorWeb.Properties.Settings.Default.DoctorMed;
using (OdbcConnection con = new OdbcConnection(connectionString))
{
try
{
con.Open();
try
{
using (OdbcCommand command = new OdbcCommand("Drop Table " + comboBox1.Text.Replace("$", ""), con))
{
command.ExecuteNonQuery();
}
}
catch
{
}
using (OdbcCommand command = new OdbcCommand("Create Table " + comboBox1.Text.Replace("$", "") + "(" + Campos + ")", con))
{
command.ExecuteNonQuery();
}
for (int i = 1; i < dataGridView1.Rows.Count; i++)
{
var Valores = "";
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
if (SQLTipo[j] == "C")
{
Valores += "'" + dataGridView1.Rows[i].Cells[j].EditedFormattedValue.ToString() + "'";
}
else if (SQLTipo[j] == "D")
{
if (dataGridView1.Rows[i].Cells[j].EditedFormattedValue.ToString().Trim().Length == 0)
{
Valores += "'01/01/1901'";
}
else
{
Valores += "TO_DATE('" + dataGridView1.Rows[i].Cells[j].EditedFormattedValue.ToString() + "','MM/DD/YYYY')";
}
}
else if (SQLTipo[j] == "N")
{
if (dataGridView1.Rows[i].Cells[j].EditedFormattedValue.ToString().Trim().Length == 0)
{
Valores += "0";
}
else
{
Valores += dataGridView1.Rows[i].Cells[j].EditedFormattedValue.ToString();
}
}
else
{
if (dataGridView1.Rows[i].Cells[j].EditedFormattedValue.ToString().Trim().Length == 0)
{
Valores += "'00:00:00'";
}
else
{
Valores += "'" + dataGridView1.Rows[i].Cells[j].EditedFormattedValue.ToString() + "'";
}
}
if (j < dataGridView1.Columns.Count - 1)
{
Valores += ",";
}
}
using (OdbcCommand command = new OdbcCommand("Insert Into " + comboBox1.Text.Replace("$", "") + " Values (" + Valores + ")", con))
{
command.ExecuteNonQuery();
}
}
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
}