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;" + connectionString)) { string[] strcnn = connectionString.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(connectionString)) { 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)); }