private FieldArray RecuperaCampos()
{
FieldArray ret = new FieldArray();
foreach(ListViewItem lvi in lvCampos.Items)
{
ret.Add((Field)lvi.Tag);
}
return ret;
}
public static FieldArray GetTableFields(string tablename)
{
using(OleDbConnection cnn = new OleDbConnection("Provider=SQLOLEDB;" + Configuracion.valor("DSNBD")))
{
string[] strcnn = Configuracion.valor("DSNBD").Split(';');
string catalog = null;
foreach(string str in strcnn)
{
string[] subs = str.Split('=');
if(subs[0].ToLower() == "initial catalog")
{
catalog = subs[1];
break;
}
}
FieldArray ret = new FieldArray();
cnn.Open();
DataTable tbl = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,new object[]{ catalog, "dbo", tablename });
foreach(DataRow row in tbl.Rows)
{
Field fld = new Field();
SetOledbType(fld, row.ItemArray);
if(fld.Type != null)
{
fld.Name = (string)row.ItemArray[SCHEMA_COLUMN_NAME];
fld.Nullable = (bool)row.ItemArray[SCHEMA_IS_NULLABLE];
ret.Add(fld);
}
}
cnn.Close();
return ret;
}
}
public static FieldArray GetTableFields_old(string tablename)
{
using(SqlConnection cnn = new SqlConnection(Configuracion.valor("DSNBD")))
{
SqlCommand comm = new SqlCommand();
DataTable dtt;
comm.CommandText = "SELECT TOP 0 * FROM " + tablename + " FOR XML AUTO, XMLDATA";
comm.Connection = cnn;
comm.CommandType = CommandType.Text;
cnn.Open();
XmlReader xr = comm.ExecuteXmlReader();
DataSet ds = new DataSet();
ds.ReadXml(xr);
dtt = ds.Tables[0];
FieldArray ret = new FieldArray();
foreach(DataColumn dtc in dtt.Columns)
{
TypeEntry te = Pattern.GetBaseType(dtc.DataType.Name);
if(te != null)
{
Field fld = new Field();
fld.Name = dtc.ColumnName;
fld.Type = te;
ret.Add(fld);
}
}
cnn.Close();
return ret;
}
}
public static string CreateUpdateProcedure(string aplicacion, string tablename, FieldArray fields, string entidad)
{
/*
CREATE PROCEDURE [dbo].[RPOSXXX_entidad_I]
@alias1 AS dbotype,
@alias2 AS dbotype
AS
BEGIN
UPDATE tablename
SET field2 = @alias2,
field3 = @alias3
WHERE field1 = @alias1
END
*/
const string PARAM_FORMAT = "\n\t@{0} AS {1},";
const string SET_FORMAT = "\n\t\t\t\t{0} = @{1},";
const string WHERE_FORMAT = "{0} = @{1}\n\t\t\t AND ";
string spName = GetSPName(aplicacion,entidad,SPType.Update);
string SP_FORMAT =
"CREATE PROCEDURE [dbo].[" + spName + "] \n" +
"\t{0}\n" +
"AS\n" +
"BEGIN\n" +
"\tUPDATE " + tablename + " \n" +
"\t\t\tSET\n" +
"\t\t\t{1}\n" +
"\t\t\tWHERE {2} \n" +
"END;";
string paramPart = "";
string wherePart = "";
string setPart = "";
FieldArray nfields = new FieldArray();
foreach(Field fld in fields)
{
if(fld.Alias == null || fld.Alias == "") fld.Alias = fld.Name;
nfields.Add(fld);
}
string[] pks = GetPrimaryKeys(tablename);
foreach(string pk in pks)
{
Field field = null;
foreach(Field fld in fields)
{
if(fld.Name == pk)
{
nfields.Remove(fld);
field = fld;
break;
}
}
if(field != null)
{
string nParam = string.Format(PARAM_FORMAT, field.Alias, field.DBDefinition);
paramPart += nParam;
string nWhere = string.Format(WHERE_FORMAT, field.Name, field.Alias);
wherePart += nWhere;
}
}
foreach(Field fld in nfields)
{
string nParam = string.Format(PARAM_FORMAT, fld.Alias, fld.DBDefinition);
paramPart += nParam;
setPart += string.Format(SET_FORMAT, fld.Name, fld.Alias);
}
paramPart = CleanSPPart(paramPart);
setPart = CleanSPPart(setPart);
wherePart = CleanSPPart(wherePart);
if(wherePart.Length > 0)wherePart = wherePart.Remove(wherePart.Length - 4, 4);
return string.Format(SP_FORMAT, paramPart, setPart, wherePart);
}