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); }