private void DoFormXML()
{
if (_columnsTable != null)
{
StringWriter sw = new StringWriter();
XmlTextWriter w = new XmlTextWriter(sw);
w.WriteStartDocument();
w.WriteStartElement("SPSActionEditConfig");
w.WriteStartElement("DataBase");
w.WriteAttributeString("ConnectionString", Generator.GetGenerator().ConnectionString);
w.WriteStartElement("Table");
w.WriteAttributeString("Name", _selectedTable);
//w.WriteAttributeString("IdentityField", GetPrimaryKey());
//w.WriteAttributeString("IdentityType", GetTypeForField(GetPrimaryKey()));
// IdentityColumns
foreach (DataRow row in _columnsTable.Rows)
{
if ((bool)row["IsKey"])
{
SqlDbType sqlDbType =
(SqlDbType)Enum.Parse(typeof(SqlDbType), row["DataTypeName"].ToString(), true);
string datatype = sqlDbType.ToString();
w.WriteStartElement("IdentityColumn");
w.WriteAttributeString("Name", row["ColumnName"].ToString());
w.WriteAttributeString("Type", datatype);
w.WriteEndElement();
}
}
w.WriteEndElement();
w.WriteEndElement();
#region ActionToolBars Sample
w.WriteStartElement("ActionToolBars");
w.WriteStartElement("ActionToolBar");
w.WriteAttributeString("Name", "View");
w.WriteStartElement("Option");
w.WriteAttributeString("Name", "Edit");
w.WriteAttributeString("ImageUrl", "/_layouts/images/edit.gif");
w.WriteAttributeString("Action", "Edit");
w.WriteEndElement();
w.WriteStartElement("Option");
w.WriteAttributeString("Name", "Delete");
w.WriteAttributeString("ImageUrl", "/_layouts/images/delete.gif");
w.WriteAttributeString("Action", "Delete");
w.WriteEndElement();
w.WriteStartElement("Option");
w.WriteAttributeString("Name", "New");
w.WriteAttributeString("ImageUrl", "/_layouts/images/newrole.gif");
w.WriteAttributeString("Action", "New");
w.WriteEndElement();
w.WriteEndElement();
w.WriteStartElement("ActionToolBar");
w.WriteAttributeString("Name", "Edit");
w.WriteStartElement("Option");
w.WriteAttributeString("Name", "Update");
w.WriteAttributeString("ImageUrl", "/_layouts/images/save.gif");
w.WriteAttributeString("Action", "Update");
w.WriteEndElement();
w.WriteStartElement("Option");
w.WriteAttributeString("Name", "Back");
w.WriteAttributeString("ImageUrl", "/_layouts/images/back.gif");
w.WriteAttributeString("Action", "Back");
w.WriteEndElement();
w.WriteStartElement("Option");
w.WriteAttributeString("Name", "Delete");
w.WriteAttributeString("ImageUrl", "/_layouts/images/delete.gif");
w.WriteAttributeString("Action", "Delete");
w.WriteEndElement();
w.WriteEndElement();
w.WriteStartElement("ActionToolBar");
w.WriteAttributeString("Name", "New");
w.WriteStartElement("Option");
w.WriteAttributeString("Name", "Save New");
w.WriteAttributeString("ImageUrl", "/_layouts/images/save.gif");
w.WriteAttributeString("Action", "Create");
w.WriteEndElement();
w.WriteStartElement("Option");
w.WriteAttributeString("Name", "Back");
w.WriteAttributeString("ImageUrl", "/_layouts/images/back.gif");
w.WriteAttributeString("Action", "Back");
w.WriteEndElement();
w.WriteEndElement();
w.WriteEndElement();
#endregion
w.WriteStartElement("Fields");
foreach (DataRow row in _columnsTable.Rows)
{
string columnName = row["ColumnName"].ToString();
string dataType = row["DataType"].ToString();
bool isReadOnly = (bool)row["IsReadOnly"];
bool isKey = (bool)row["IsKey"];
bool isAutoIncrement = (bool)row["IsAutoIncrement"];
bool allowDbNull = ((bool)row["AllowDBNull"]);
string newRecord = (isReadOnly || isAutoIncrement ? "Disabled" : "Enabled");
string editRecord = newRecord;
int maxLength = Int32.Parse(row["ColumnSize"].ToString());
int columns = maxLength > 40 ? 40 : maxLength;
string control = ApropiatedControl(dataType, maxLength);
SqlFKPKInfo sqlFkPkInfo = SPSDbTools.GetFkPkInfoVersion(
Generator.GetGenerator().Connection,
columnName,
_selectedTable);
string required = (isAutoIncrement || !allowDbNull || sqlFkPkInfo != null).ToString().ToLower();
SqlDbType sqlDbType =
(SqlDbType)Enum.Parse(typeof(SqlDbType), row["DataTypeName"].ToString(), true);
string datatype = sqlDbType.ToString();
// Types not supported
if (datatype == "Xml")
{
w.WriteComment(string.Format("Field {0} type {1} is not supported by ActionDataBase",
columnName,
datatype));
}
else
{
Status(string.Format("Generating {0}", columnName));
w.WriteStartElement("Field");
w.WriteAttributeString("Name", columnName);
w.WriteAttributeString("Title", columnName);
w.WriteAttributeString("Type", datatype);
w.WriteAttributeString("Control", control);
w.WriteAttributeString("Required", required);
w.WriteAttributeString("DefaultValue", "");
if (dataType.Contains("Date"))
{
w.WriteAttributeString("DisplayFormat", "{0:d}");
}
else
{
w.WriteAttributeString("DisplayFormat", "");
}
w.WriteAttributeString("New", newRecord);
w.WriteAttributeString("Edit", editRecord);
w.WriteAttributeString("View", "Enabled");
if (control == "TextBox" && dataType.Contains("String") && maxLength <= 256)
{
w.WriteStartElement("TextBox");
w.WriteAttributeString("Columns", columns.ToString());
w.WriteAttributeString("MaxLength", maxLength.ToString());
w.WriteEndElement();
}
if (control == "CheckBox")
{
w.WriteStartElement("CheckBox");
w.WriteAttributeString("TextChecked", "Chekced");
w.WriteAttributeString("TextUnChecked", "UnChecked");
w.WriteEndElement();
}
if (control == "Memo")
{
w.WriteStartElement("Memo");
w.WriteAttributeString("Columns", "40");
w.WriteAttributeString("Rows", "15");
//w.WriteAttributeString("MaxLength", maxLength.ToString());
w.WriteEndElement();
}
if (control == "TextBox" &&
(!dataType.Contains("String") &&
!dataType.Contains("Date")))
{
w.WriteStartElement("TextBox");
w.WriteAttributeString("Columns", "20");
w.WriteAttributeString("RightToLeft", "true");
w.WriteEndElement();
}
if (sqlFkPkInfo != null)
{
// TextField="CompanyName" ValueField="CustomerID" Table="TabelName"
w.WriteStartElement("Lookup");
w.WriteAttributeString("ControlEditor", "DropDownList");
w.WriteAttributeString("TextField", sqlFkPkInfo.PKColumnName);
w.WriteAttributeString("ValueField", sqlFkPkInfo.PKColumnName);
w.WriteAttributeString("ValueFieldType", datatype);
w.WriteAttributeString("Table", sqlFkPkInfo.PKTable);
w.WriteEndElement();
}
w.WriteEndElement();
}
}
w.WriteEndElement();
w.WriteEndElement();
w.WriteEndDocument();
txtFormXML.Text = FormatXML(sw.ToString());
LoadXMLFormEditor();
Status("ActionBase XML Form generated.");
}
else
{
Error("Run column info, prior to generate a form.");
}
}
public static SqlFKPKInfo GetFkPkInfoVersion(SqlConnection connection, string column, string table)
{
SqlFKPKInfo sqlFkPkInfo = null;
bool sql2000 = true;
string[] schemaTable = null;
using (connection)
{
connection.Open();
if (!connection.ServerVersion.StartsWith("08"))
{
sql2000 = false;
schemaTable = table.Split('.');
}
// get the constraint - if one exists
int id = 0;
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandType = CommandType.Text;
if (sql2000)
{
command.CommandText =
"SELECT sysobjects.id FROM sysobjects INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE " +
"ON sysobjects.name = INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.Constraint_Name WHERE " +
"(column_name = @p1) AND (Table_Name = @p2) AND (sysobjects.xtype = 'F')";
}
else
{
command.CommandText =
"SELECT object_id FROM sys.objects WHERE (name = (SELECT Constraint_Name FROM " +
"INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE (column_name=@p1) AND (Table_Name=@p2) AND (Table_Schema=@p3) )) AND (type='F')";
}
command.Parameters.Add(new SqlParameter("@p1", column));
if (sql2000)
{
command.Parameters.Add(new SqlParameter("@p2", table));
}
else
{
if (schemaTable != null)
{
command.Parameters.Add(new SqlParameter("@p2", schemaTable[1]));
command.Parameters.Add(new SqlParameter("@p3", schemaTable[0]));
}
}
using (SqlDataReader dataReader = command.ExecuteReader())
{
try
{
if (dataReader.Read())
{
id = dataReader.GetInt32(0);
}
}
catch (SqlException)
{
}
}
}
if (id != 0)
{
sqlFkPkInfo = new SqlFKPKInfo();
// ok, we have it - go get it.
int rkeyid, rkey;
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandType = CommandType.Text;
if (sql2000)
{
command.CommandText = "SELECT rkeyid, rkey FROM sysforeignkeys WHERE constid = @p1";
}
else
{
command.CommandText =
"SELECT referenced_object_id, referenced_column_id FROM sys.foreign_key_columns WHERE constraint_object_id = @p1";
}
command.Parameters.Add(new SqlParameter("@p1", id));
using (SqlDataReader dataReader = command.ExecuteReader())
{
dataReader.Read();
rkeyid = dataReader.GetInt32(0);
rkey = (sql2000 ? dataReader.GetInt16(1) : dataReader.GetInt32(1));
}
}
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandType = CommandType.Text;
if (sql2000)
{
command.CommandText = "select name from sysobjects where id = @p1";
}
else
{
command.CommandText = "select name from sys.objects where object_id = @p1";
}
command.Parameters.Add(new SqlParameter("@p1", rkeyid));
using (SqlDataReader dataReader = command.ExecuteReader())
{
dataReader.Read();
sqlFkPkInfo.PKTable = dataReader.GetString(0);
}
}
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandType = CommandType.Text;
if (sql2000)
{
command.CommandText =
"select Column_Name from INFORMATION_SCHEMA.COLUMNS where table_name=@p1 and Ordinal_position=@p3";
}
else
{
command.CommandText =
"select Column_Name from INFORMATION_SCHEMA.COLUMNS where table_name=@p1 and table_schema=@p2 and Ordinal_position=@p3";
}
if (sql2000)
{
command.Parameters.Add(new SqlParameter("@p1", sqlFkPkInfo.PKTable));
}
else
{
if (schemaTable != null)
{
command.Parameters.Add(new SqlParameter("@p1", schemaTable[1]));
command.Parameters.Add(new SqlParameter("@p2", schemaTable[0]));
}
}
command.Parameters.Add(new SqlParameter("@p3", rkey));
using (SqlDataReader dataReader = command.ExecuteReader())
{
dataReader.Read();
sqlFkPkInfo.PKColumnName = dataReader.GetString(0);
}
}
}
}
return(sqlFkPkInfo);
}