public void ProcedureSelect(string servidor, string baseDatos, string tabla)
{
string text = "";
foreach (DataGridViewRow elemento in dgvListaWhere.Rows)
{
if ((bool)(elemento.Cells[4].Value) == true)
{
listaBuscar.Add(new Atributos(elemento.Cells[0].Value.ToString(), "nvarchar", elemento.Cells[2].Value.ToString(), elemento.Cells[3].Value.ToString()));
}
}
int maxBuscar = listaBuscar.Count;
txtS.AppendText("USE " + cboBD.SelectedItem.ToString() + " \r\n");
txtS.AppendText("GO " + " \r\n");
txtS.AppendText("CREATE PROCEDURE SPU_BUSCAR_" + tabla + "\r\n");
foreach (var item in listaBuscar)
{
maxBuscar--;
if (maxBuscar == 0)
{
text = $"@{Regex.Replace(item.Nombre, @"[.]", "")} {item.tipoDato} ({item.length}) ";
}
else
{
text = $"@{Regex.Replace(item.Nombre, @"[.]", "")} {item.tipoDato}({item.length}), ";
}
txtS.AppendText(text + "\r\n");
}
txtS.AppendText("AS" + "\r\n");
txtS.AppendText("Select ");
string query = string.Format("Select i.COLUMN_NAME,CONVERT(text,i.DATA_TYPE),CONVERT(text,i.IS_NULLABLE),CONVERT(nvarchar,i.CHARACTER_MAXIMUM_LENGTH) from information_schema.columns i WHERE TABLE_NAME='{0}'", tabla);
string cadenaConexion = string.Format(@"Data Source={0};Initial Catalog={1};Integrated Security=True", servidor, baseDatos);
SqlConnection cn = new SqlConnection(cadenaConexion);
SqlCommand cmd = new SqlCommand(query, cn);
cn.Open();
var lista = cmd.ExecuteReader();
text = "";
foreach (DataGridViewRow elemento in dgvAtributos.Rows)
{
if ((bool)(elemento.Cells[4].Value) == true)
{
listaAtrib.Add(new Atributos(elemento.Cells[0].Value.ToString(), elemento.Cells[1].Value.ToString(), elemento.Cells[2].Value.ToString(), elemento.Cells[3].Value.ToString()));
}
}
int max = listaAtrib.Count;
maxBuscar = listaBuscar.Count;
foreach (var item in listaAtrib)
{
max--;
if (max == 0)
{
text = item.Nombre;
}
else
{
text = item.Nombre + ", ";
}
txtS.AppendText(text);
}
txtS.AppendText(" From " + tabla);
//Crear InnerJoin
Consultas aux = new Consultas();
var listaInner = aux.getGenerarRelacion(tabla);
foreach (var item in listaInner)
{
txtS.AppendText($@" INNER JOIN {item.PK_Tabla} ON {item.PK_Tabla}.{item.PK_Columna} = {tabla}.{item.FK_Columna} ");
}
txtS.AppendText(" WHERE ");
foreach (var item in listaBuscar)
{
maxBuscar--;
if (maxBuscar == 0)
{
text = item.Nombre + $" like @{Regex.Replace(item.Nombre, @"[.]", "")} + '%' ";
}
else
{
text = item.Nombre + $" like @{Regex.Replace(item.Nombre, @"[.]", "")}+ '%' AND ";
}
txtS.AppendText(text);
}
txtS.AppendText($" AND {tabla}.Estado='1' ");
}
