// -------------------------------------------------------
// SqlBuilder by Elm�Soft
// www.netcult.ch/elmue
// www.codeproject.com/KB/database/SqlBuilder.aspx
// -------------------------------------------------------
using System;
using System.Xml;
using System.Data;
using System.Text;
using System.Collections;
namespace SqlBuilder
{
// Abstraction classes which store table column definitions
#region class TableCol
/// <summary>
/// Defines details about one column in a table
/// </summary>
public class TableCol
{
#region class TableKeys
/// <summary>
/// Defines details about one foreign key constraint of a column
/// </summary>
public class TableKeys
{
public struct kConstraint
{
public string s_Table;
public string s_Columns; // comma separated (up to 16 columns per constraint)
}
TableCol mi_Column; // The column to which the foreign key is assigned
string ms_Type; // "ForeignKeyTo" or "ReferencedBy"
Hashtable mi_Keys; // Key= Name, Value= kConstraint(Table, Columns)
/// <summary>
/// Constructor
/// </summary>
public TableKeys(TableCol i_Column, string s_Type)
{
mi_Column = i_Column;
ms_Type = s_Type;
mi_Keys = new Hashtable();
}
/// <summary>
/// returns the names of the constraints
/// </summary>
public ArrayList Keys
{
get { return new ArrayList(mi_Keys.Keys); }
}
public kConstraint this[string s_Key]
{
get { return (kConstraint) mi_Keys[s_Key]; }
}
public void AddKey(string s_Name, string s_Table, string s_Columns)
{
kConstraint k_Const = new kConstraint();
k_Const.s_Table = s_Table;
k_Const.s_Columns = s_Columns.TrimEnd(',');
mi_Keys[s_Name] = k_Const;
}
/// <summary>
/// returns the string to be displayed in the table designer
/// "Table1 (Col1, Col2)\r\nTable2 (Col1)"
/// </summary>
public string Display
{
get
{
string s_Disp = "";
ArrayList i_Keys = new ArrayList(mi_Keys.Keys);
i_Keys.Sort();
foreach (string s_Name in i_Keys)
{
kConstraint k_Const = (kConstraint)mi_Keys[s_Name];
if (s_Disp.Length > 0) s_Disp += "\r\n";
s_Disp += k_Const.s_Table + " (";
bool b_First = true;
foreach (string s_Col in k_Const.s_Columns.Split(','))
{
if (!b_First) s_Disp += ", ";
b_First = false;
s_Disp += s_Col; // Column names
}
s_Disp += ")";
}
return s_Disp;
}
}
public void Serialize(XmlNode x_Node)
{
if (mi_Keys.Count == 0)
return;
XmlNode x_Foreign = XML.CreateSubNode(x_Node, ms_Type);
int i=0;
foreach (string s_Name in mi_Keys.Keys)
{
XmlNode x_Const = XML.CreateSubNode(x_Foreign, "Constraint_" + (i++));
kConstraint k_Const = (kConstraint) mi_Keys[s_Name];
XML.WriteSubNode(x_Const, "Name", s_Name);
XML.WriteSubNode(x_Const, "Table", k_Const.s_Table);
XML.WriteSubNode(x_Const, "Columns", k_Const.s_Columns);
}
}
public void Deserialize(XmlNode x_Node)
{
mi_Keys.Clear();
XmlNode x_Foreign = XML.FindSubNode(x_Node, ms_Type);
if (x_Foreign == null)
return;
for (int i=0; true; i++)
{
XmlNode x_Const = XML.FindSubNode(x_Foreign, "Constraint_" + i);
if (x_Const == null)
break;
kConstraint k_Const = new kConstraint();
k_Const.s_Table = XML.ReadSubNodeStr(x_Const, "Table", "");
k_Const.s_Columns = XML.ReadSubNodeStr(x_Const, "Columns", "");
string s_Name = XML.ReadSubNodeStr(x_Const, "Name", "");
mi_Keys[s_Name] = k_Const;
}
}
}
#endregion
#region classes for Indexes
public class Index
{
public string s_Name = null; // name of index
public bool b_Unique = false;
public bool b_Primary = false;
public ArrayList i_Columns = new ArrayList(); // a TableCol for each column which is indexed
public Index(string sName, bool bUnique, bool bPrimary)
{
s_Name = sName;
b_Unique = bUnique;
b_Primary = bPrimary;
}
public void AddColumn(TableCol i_Col)
{
if (!i_Columns.Contains(i_Col))
i_Columns.Add(i_Col);
}
}
/// <summary>
/// All indexes for one column
/// </summary>
public class ColIndexes
{
ArrayList mi_Indexes = new ArrayList();
public void AddIndex(Index i_Index)
{
if (!mi_Indexes.Contains(i_Index))
mi_Indexes.Add(i_Index);
}
/// <summary>
/// returns an arraylist with all primary or unique keys (or both) assigned to this column
/// </summary>
public ArrayList GetByType(bool b_Unique, bool b_Primary)
{
ArrayList i_Found = new ArrayList();
foreach (Index i_Index in mi_Indexes)
{
if ((b_Primary && i_Index.b_Primary) ||
(b_Unique && i_Index.b_Unique))
i_Found.Add(i_Index);
}
return i_Found;
}
/// <summary>
/// returns tre if the column has a primary key
/// </summary>
public bool Primary
{
get { return GetByType(false, true).Count > 0; }
}
/// <summary>
/// returns tre if the column has a unique key
/// </summary>
public bool Unique
{
get { return GetByType(true, false).Count > 0; }
}
public ArrayListEx Names
{
get
{
ArrayListEx i_Names = new ArrayListEx();
foreach (Index i_Index in mi_Indexes)
{
i_Names.Add(i_Index.s_Name);
}
i_Names.Sort();
return i_Names;
}
}
public string Display
{
get { return Names.ToList("\r\n"); }
}
public void Serialize(XmlNode x_Node)
{
ArrayListEx i_Primary = new ArrayListEx();
ArrayListEx i_Unique = new ArrayListEx();
foreach (Index i_Index in mi_Indexes)
{
if (i_Index.b_Primary) i_Primary.Add(i_Index.s_Name);
if (i_Index.b_Unique) i_Unique. Add(i_Index.s_Name);
}
if (i_Primary.Count > 0) XML.WriteSubNode(x_Node, "Primary", i_Primary.ToList(","));
if (i_Unique. Count > 0) XML.WriteSubNode(x_Node, "Unique", i_Unique. ToList(","));
}
public void Deserialize(XmlNode x_Node)
{
throw new Exception("Deserialization not possible here. Deserialization takes place in TableDef!");
}
}
#endregion
public int s32_ColIndex = -1; // zero based position in the table
public string s_ColName = null; // name of column
public string s_BaseType = null; // e.g. "int", "varchar",...
public int s32_Bytes = 0; // Length in Bytes: 4 for integer, character count for varchar
public int s32_Precision = 0; // for data types "decimal", "numeric"
public int s32_Scale = 0; // for data types "decimal", "numeric"
public string s_Default = null; // the column's default value
public string s_DefConstr = null; // the Default constraint's name
public bool b_Nullable = false; // allows NULL
public bool b_Identity = false; // defines Seed and Increment
public int s32_Seed = 0; // used with IDENTITY
public int s32_Increment = 0; // used with IDENTITY
public Type t_ManagedType = typeof(DBNull); // required for Import
public TableKeys i_FkOut = null; // Foreign keys from this table to other tables
public TableKeys i_FkIn = null; // Foreign keys from other tables to this table
public ColIndexes i_Indexes = null; // all indexes for this column
// The following values are generated automatically from the above variables or they
// are set manually when the user adds a new column
public bool b_Primary = false; // Column has a primary key
public bool b_Unique = false; // Column has a unique key
public string s_FullType = null; // e.g. "int", "varchar(255)", ...
// The following values are for internal use only :
private TableDef i_Table;
private string s_Table;
// Constructor with restricted functionality (for adding new user columns)
public TableCol(string sTable)
{
s_Table = sTable;
}
// Constructor with full functionality
public TableCol(TableDef iTable)
{
i_Table = iTable;
s_Table = iTable.Name;
i_FkOut = new TableKeys(this, "ForeignKeyTo");
i_FkIn = new TableKeys(this, "ReferencedBy");
i_Indexes = new ColIndexes();
}
/// <summary>
/// Fill this class with data from a DataRow
/// This function may be called multiple times to add more than one foreign key
/// </summary>
public void InsertData(DataRow i_Row, int s32_Column)
{
if (i_Row["FkName"] != DBNull.Value)
i_FkOut.AddKey((string)i_Row["FkName"], (string)i_Row["FkTable"], (string)i_Row["FkCols"]);
if (i_Row["RefName"] != DBNull.Value)
i_FkIn.AddKey((string)i_Row["RefName"], (string)i_Row["RefTable"], (string)i_Row["RefCols"]);
int s32_IdxStatus = Functions.ToInt(i_Row["IndexStatus"]);
// ignore all hypothetical and statistical indexes (Bit 0x20 set)
if ((s32_IdxStatus & 0x0020) == 0)
{
bool b_Uniq = ((s32_IdxStatus & 0x1000) > 0);
bool b_Prim = ((s32_IdxStatus & 0x0800) > 0);
// Use an exsiting index ore create a new index and
// add the index to the table's index list and to the column's index list
i_Table.AddIndex(Functions.ToStr(i_Row["IndexName"]), b_Uniq, b_Prim, this);
// AFTER AddIndex()!!
b_Primary |= b_Prim;
b_Unique |= b_Uniq;
}
// The data for this column has already been set (constructor sets s32_ColIndex = -1)
// This function is called the second time and only an IndexStatus or ForeignKey has been added
if (s32_ColIndex >= 0)
return;
s32_ColIndex = s32_Column;
s_ColName = Functions.ToStr(i_Row["ColName"]);
s_BaseType = Functions.ToStr(i_Row["BaseType"]).ToLower();
s32_Bytes = Functions.ToInt(i_Row["DataLen"]);
s32_Precision = Functions.ToInt(i_Row["PrecVal"]);
s32_Scale = Functions.ToInt(i_Row["ScaleVal"]);
s_Default = Functions.ToStr(i_Row["DefaultVal"]);
s_DefConstr = Functions.ToStr(i_Row["DefConstr"]);
b_Nullable =(Functions.ToInt(i_Row["ColStatus"]) & 0x0008) > 0;
b_Identity =(Functions.ToInt(i_Row["ColStatus"]) & 0x0080) > 0;
if (b_Identity)
{
s32_Seed = Functions.ToInt(i_Row["Seed"]);
s32_Increment = Functions.ToInt(i_Row["Increment"]);
}
// Last command !!!
s_FullType = GetFullType();
}
/// <summary>
/// returns the datatype as required for CREATE or ALTER TABLE
/// e.g. returns "varchar(255)" or "int"
/// </summary>
private string GetFullType()
{
switch (s_BaseType)
{
// See column "CREATE_PARAMS" in table "master.dbo.spt_datatype_info"
case "char":
case "binary":
case "varchar":
case "varbinary":
return string.Format("{0} ({1})", s_BaseType, s32_Bytes);
// "nvarchar(100)" (Unicode) occupies the double amount of memory on the server as "varchar(100)"
case "nchar":
case "nvarchar":
return string.Format("{0} ({1})", s_BaseType, s32_Bytes / 2);
case "decimal":
case "numeric":
return string.Format("{0} ({1},{2})", s_BaseType, s32_Precision, s32_Scale);
default:
return s_BaseType;
}
}
/// <summary>
/// Builds the SQL command to CREATE or ALTER this column
/// The ALTER COLUMN command is very primitive: it does not allow to set IDENTITY, DEFAULT, UNIQUE, PRIMARY
/// b_New = true --> a new column is to be created (ADD column or CREATE TABLE)
/// b_New = false --> an existing column is to be modified (ALTER COLUMN)
/// </summary>
public string BuildCreateCommand(bool b_New)
{
string s_Sql = string.Format("[{0}] {1}", s_ColName, s_FullType);
if (b_New)
{
if (b_Identity) s_Sql += string.Format(" IDENTITY({0}, {1})", s32_Seed, s32_Increment);
if (b_Primary) s_Sql += " PRIMARY KEY";
if (b_Unique) s_Sql += " UNIQUE";
if (s_Default != "") s_Sql += string.Format(" DEFAULT {0}", s_Default);
}
if (b_Nullable) s_Sql += " NULL";
else s_Sql += " NOT NULL";
return s_Sql;
}
/// <summary>
/// Create a SQL command which adds a table column if it does not yet exist
/// </summary>
public string BuildAddColumnCommand()
{
// col_length() returns the number of BYTES occupied on the server.
// Example: nvarchar(100) --> 200
string s_Sql = string.Format("IF col_length('{0}', '{1}') IS NULL\n", s_Table, s_ColName);
s_Sql += string.Format("ALTER TABLE [{0}] ADD {1}", s_Table, BuildCreateCommand(true));
s_Sql += "\nELSE\n";
s_Sql += string.Format("ALTER TABLE [{0}] ALTER COLUMN {1}", s_Table, BuildCreateCommand(false));
return s_Sql;
}
/// <summary>
/// Exports the content of the columen represented by this class into a XML node
/// </summary>
public void Serialize(XmlNode x_Node)
{
XML.WriteSubNode(x_Node, "BaseType", s_BaseType);
XML.WriteSubNode(x_Node, "ColName", s_ColName);
XML.WriteSubNode(x_Node, "Bytes", s32_Bytes);
XML.WriteSubNode(x_Node, "Default", s_Default);
XML.WriteSubNode(x_Node, "DefConstraint", s_DefConstr);
XML.WriteSubNode(x_Node, "Identity", b_Identity);
XML.WriteSubNode(x_Node, "Increment", s32_Increment);
XML.WriteSubNode(x_Node, "Nullable", b_Nullable);
XML.WriteSubNode(x_Node, "Precision", s32_Precision);
XML.WriteSubNode(x_Node, "Scale", s32_Scale);
XML.WriteSubNode(x_Node, "Seed", s32_Seed);
XML.WriteSubNode(x_Node, "ManagedType", t_ManagedType);
i_Indexes.Serialize(x_Node);
i_FkOut. Serialize(x_Node);
i_FkIn. Serialize(x_Node);
}
/// <summary>
/// Fills this class with data from the XML node
/// returns false on parsing error
/// </summary>
public bool Deserialize(XmlNode x_Node)
{
try
{
s_BaseType = XML.ReadSubNodeStr (x_Node, "BaseType", "");
s_ColName = XML.ReadSubNodeStr (x_Node, "ColName", "");
s32_Bytes = XML.ReadSubNodeInt (x_Node, "Bytes", 0);
s_Default = XML.ReadSubNodeStr (x_Node, "Default", "");
s_DefConstr = XML.ReadSubNodeStr (x_Node, "DefConstraint", "");
b_Identity = XML.ReadSubNodeBool(x_Node, "Identity", false);
s32_Increment = XML.ReadSubNodeInt (x_Node, "Increment", 0);
b_Nullable = XML.ReadSubNodeBool(x_Node, "Nullable", false);
s32_Precision = XML.ReadSubNodeInt (x_Node, "Precision", 0);
s32_Scale = XML.ReadSubNodeInt (x_Node, "Scale", 0);
s32_Seed = XML.ReadSubNodeInt (x_Node, "Seed", 0);
t_ManagedType = Type.GetType(XML.ReadSubNodeStr(x_Node, "ManagedType", ""), true);
i_FkOut.Deserialize(x_Node);
i_FkIn. Deserialize(x_Node);
string s_Primary = XML.ReadSubNodeStr(x_Node, "Primary", "");
foreach (string s_Name in Functions.SplitEx(s_Primary, ','))
{
i_Table.AddIndex(s_Name, false, true, this);
}
string s_Unique = XML.ReadSubNodeStr(x_Node, "Unique", "");
foreach (string s_Name in Functions.SplitEx(s_Unique, ','))
{
i_Table.AddIndex(s_Name, true, false, this);
}
// Last commands !!!
s_FullType = GetFullType();
b_Primary = i_Indexes.Primary;
b_Unique = i_Indexes.Unique;
return true;
}
catch
{
return false;
}
}
}
#endregion
#region class TableDef
/// <summary>
/// Defines details about all columns in a table
/// </summary>
public class TableDef
{
string ms_Name = null;
ArrayList mi_Columns = new ArrayList(); // List of TableCol
ArrayList mi_Indexes = new ArrayList(); // List of Index
public string Name
{
get { return ms_Name; }
}
public ArrayList Columns
{
get { return mi_Columns; }
}
/// <summary>
/// Adds a column to an exsiting index or creates a new index
/// Adds the Index to the table's index list and to the column's index list
/// </summary>
public void AddIndex(string s_Name, bool b_Unique, bool b_Primary, TableCol i_Col)
{
if (s_Name.Length == 0)
return;
TableCol.Index i_Index = null;
foreach (TableCol.Index Idx in mi_Indexes)
{
if (Idx.s_Name == s_Name)
{
i_Index = Idx;
break;
}
}
if (i_Index == null)
{
i_Index = new TableCol.Index(s_Name, b_Unique, b_Primary);
mi_Indexes.Add(i_Index);
}
// Add the column to the index
i_Index.AddColumn(i_Col);
// Add the index to the column
i_Col.i_Indexes.AddIndex(i_Index);
}
/// <summary>
/// Builds the Sql "CREATE TABLE" command
/// </summary>
public string BuildCreateCommand()
{
string s_Sql = string.Format("IF objectproperty(object_id('{0}'), 'IsTable') != 1\n", ms_Name)
+ string.Format("CREATE TABLE [{0}]\n(\n", ms_Name);
for (int C=0; C<mi_Columns.Count; C++)
{
TableCol i_Col = (TableCol) mi_Columns[C];
if (C>0) s_Sql += ",\n";
s_Sql += i_Col.BuildCreateCommand(true);
}
s_Sql += "\n)";
return s_Sql;
}
/// <summary>
/// This function may be called multiple times for the same column
/// for example when adding multiple foreign keys for one column
/// </summary>
private void AppendColumn(DataRow i_Row)
{
ms_Name = Functions.ToStr (i_Row["TblName"]);
string s_ColName = (string)i_Row["ColName"];
// Add data to an already existing column
if (mi_Columns.Count > 0)
{
TableCol i_LastCol = (TableCol)mi_Columns[mi_Columns.Count-1];
if (s_ColName == i_LastCol.s_ColName)
{
// The column index has already been set
i_LastCol.InsertData(i_Row, -1);
return;
}
}
// Add a new column
TableCol i_Col = new TableCol(this);
i_Col.InsertData(i_Row, mi_Columns.Count);
mi_Columns.Add(i_Col);
}
/// <summary>
/// Loads the table definition from the datatable
/// </summary>
public static TableDef FromDataTable(DataTable i_DataTable)
{
TableDef i_TableDef = new TableDef();
foreach (DataRow i_Row in i_DataTable.Rows)
{
i_TableDef.AppendColumn(i_Row);
}
return i_TableDef;
}
/// <summary>
/// returns an Hashtable with
/// key = table name
/// value = a TableDef instance for each table
/// </summary>
public static Hashtable TableFactory(DataTable i_DataTable)
{
Hashtable i_List = new Hashtable();
TableDef i_Table = new TableDef();
for (int R=0; R<i_DataTable.Rows.Count; R++)
{
DataRow i_Row = i_DataTable.Rows[R];
string s_TblName = Functions.ToStr(i_Row["TblName"]);
// Store into the list if we are in the last row or another table follows
if (R == i_DataTable.Rows.Count-1 || i_Table.Name != s_TblName)
{
if (i_Table.Columns.Count > 0)
i_List[i_Table.Name] = i_Table;
i_Table = new TableDef();
}
i_Table.AppendColumn(i_Row);
}
return i_List;
}
/// <summary>
/// Searches a column with the same ColName and ManagedType as the given column.
/// returns the index of this column or -1 if not found
/// </summary>
public int FindColumnByTypeAndName(TableCol i_Col)
{
foreach (TableCol i_MyCol in mi_Columns)
{
if (i_MyCol.t_ManagedType == i_Col.t_ManagedType &&
string.Compare(i_MyCol.s_ColName, i_Col.s_ColName, true) == 0)
return i_MyCol.s32_ColIndex;
}
return -1;
}
/// <summary>
/// Input: An arraylist of TableCol's
/// Output: A Hashtable with
/// Key = column index in "this" table
/// Value = column index in i_External
/// </summary>
public Hashtable FindMatchingColumns(ArrayList i_External)
{
Hashtable i_Match = new Hashtable();
foreach (TableCol i_Col in i_External)
{
int Idx = FindColumnByTypeAndName(i_Col);
if (Idx < 0)
continue;
i_Match[Idx] = i_Col.s32_ColIndex;
}
return i_Match;
}
/// <summary>
/// Exports the column definitions into an XML node
/// </summary>
public void Serialize(XmlNode x_Node)
{
XML.WriteSubNode(x_Node, "Name", ms_Name);
foreach (TableCol i_Col in mi_Columns)
{
XmlNode x_Col = XML.CreateSubNode(x_Node, "Column_" + i_Col.s32_ColIndex);
i_Col.Serialize(x_Col);
}
}
/// <summary>
/// Reads the column definitions from the XML node into an Arraylist of TableCol
/// returns null on invalid XML data
/// </summary>
public static TableDef Deserialize(XmlNode x_Node)
{
TableDef i_TableDef = new TableDef();
if (x_Node == null)
return null;
i_TableDef.ms_Name = XML.ReadSubNodeStr(x_Node, "Name", "");
for (int C=0; true; C++)
{
XmlNode x_Col = x_Node.SelectSingleNode("Column_" + C);
if (x_Col == null)
break;
TableCol i_Col = new TableCol(i_TableDef);
if (!i_Col.Deserialize(x_Col))
return null;
i_Col.s32_ColIndex = C;
i_TableDef.mi_Columns.Add(i_Col);
}
return i_TableDef;
}
}
#endregion
}
Submit an article or tip