/// <summary>
/// Generates a Transact-SQL command batch that can be used to re-create the data in the SQL table.
/// </summary>
/// <param name="scriptType">
/// A SqlScriptType indicating what to include in the script.
/// </param>
/// <returns>
/// A string containing a Transact-SQL command batch that can be used to re-create the data in the SQL table.
/// </returns>
/// <remarks>
/// The valid SqlScriptType values are: Comments.
/// </remarks>
public string ScriptData(SqlScriptType scriptType)
{
// Grab data from table
DataTable[] tables = Database.Query("select * from [" + name + "]");
DataTable table = tables[0];
StringBuilder sb = new StringBuilder();
if ((scriptType & SqlScriptType.Comments) == SqlScriptType.Comments)
{
sb.Append(String.Format(SR.GetString("SqlTable_ExportComment") + "\r\n", name));
}
// If necessary, turn on identity insert
bool hasIdentity = false;
string[] columnNamesArray = new string[this.Columns.Count];
for (int i = 0; i < this.Columns.Count; i++)
{
columnNamesArray[i] = "[" + this.Columns[i].ColumnInformation.Name + "]";
if (this.Columns[i].ColumnInformation.Identity)
{
hasIdentity = true;
}
}
string columnNames = String.Join(", ", columnNamesArray);
if (hasIdentity)
{
sb.Append("SET identity_insert [" + this.Name + "] on\r\n\r\n");
}
// Go through each row
for (int i = 0; i < table.Rows.Count; i++)
{
object[] cols = table.Rows[i].ItemArray;
sb.Append(String.Format("INSERT [{0}] ({1}) VALUES (", name, columnNames));
// And through each column within the row
for (int j = 0; j < cols.Length; j++)
{
if (j > 0)
{
sb.Append(", ");
}
System.Type dataType = table.Columns[j].DataType;
// If null, print null, otherwise output data based on type
if (table.Rows[i].IsNull(j))
{
// Database Null is just NULL
sb.Append("NULL");
}
else if (dataType == typeof(System.Int32) ||
dataType == typeof(System.Int16) ||
dataType == typeof(System.Decimal) ||
dataType == typeof(System.Single))
{
// Numeric datatypes we just emit as-is
sb.Append(cols[j]);
}
else if (dataType == typeof(System.DateTime) ||
dataType == typeof(System.String))
{
// Strings and date/time's get quoted
// Escape single quotes in strings (replace with two single quotes)
sb.Append(String.Format("'{0}'", cols[j].ToString().Replace("'", "''")));
}
else if (dataType == typeof(System.Boolean))
{
// Booleans are false=0 and true=1
if ((System.Boolean)cols[j])
{
sb.Append("1");
}
else
{
sb.Append("0");
}
}
else if (dataType == typeof(System.Byte[]))
{
// Byte arrays are in the form 0x0123456789ABCDEF
System.Byte[] array = (System.Byte[])cols[j];
sb.Append("0x");
for (int a = 0; a < array.Length; a++)
{
sb.Append(array[a].ToString("X"));
}
}
else
{
// Default is to call ToString() and quote it
// Escape single quotes in strings (replace with two single quotes)
sb.Append(String.Format("'{0}'", cols[j].ToString().Replace("'", "''")));
}
}
sb.Append(")\r\n");
// Stick in a GO statement to make batches smaller
//if ((i + 1) % 10 == 0)
// sb.Append("GO\r\n");
}
//sb.Append("GO\r\n\r\n");
// If we turned on identity insert, turn it off now
if (hasIdentity)
{
sb.Append("SET identity_insert [" + this.Name + "] off\r\nGO\r\n");
}
return(sb.ToString());
}
/// <summary>
/// Adds a new table to the database with specified columns.
/// </summary>
/// <param name="name">
/// The name of the table to create.
/// </param>
/// <param name="columnInfos">
/// An array of at least one SqlColumnInformation object with the column information filled out.
/// </param>
/// <returns>
/// If the operation succeeded, the return value is the table created.
/// </returns>
public SqlTable Add(string name, SqlColumnInformation[] columnInfos)
{
if (name == null || name.Length == 0)
{
throw new ArgumentException(SR.GetString("SqlTableCollection_MustHaveValidName"));
}
if (this[name] != null)
{
throw new ArgumentException(String.Format(SR.GetString("SqlTableCollection_NameAlreadyExists"), name));
}
if (columnInfos == null || columnInfos.Length == 0)
{
throw new ArgumentException(SR.GetString("SqlTableCollection_AtLeastOneColumn"));
}
// Create new table
NativeMethods.ITable dmoTable = (NativeMethods.ITable) new NativeMethods.Table();
dmoTable.SetName(name);
// No need to clear out keys since this is a new table
// Create new primary key with list of columns
NativeMethods.IKey key = (NativeMethods.IKey) new NativeMethods.Key();
key.SetType(NativeMethods.SQLDMO_KEY_TYPE.SQLDMOKey_Primary);
// Add columns to table
for (int i = 0; i < columnInfos.Length; i++)
{
NativeMethods.IColumn dmoColumn = (NativeMethods.IColumn) new NativeMethods.Column();
dmoColumn.SetName(columnInfos[i].Name);
dmoColumn.SetDatatype(columnInfos[i].DataType);
dmoColumn.SetLength(columnInfos[i].Size);
dmoColumn.SetAllowNulls(columnInfos[i].Nulls);
dmoColumn.SetNumericPrecision(columnInfos[i].Precision);
dmoColumn.SetNumericScale(columnInfos[i].Scale);
dmoColumn.SetIdentity(columnInfos[i].Identity);
dmoColumn.SetIdentitySeed(columnInfos[i].IdentitySeed);
dmoColumn.SetIdentityIncrement(columnInfos[i].IdentityIncrement);
dmoColumn.SetIsRowGuidCol(columnInfos[i].IsRowGuid);
// According to SQL Server Books Online, a name for this default will be generated automatically
dmoColumn.GetDRIDefault().SetText(columnInfos[i].DefaultValue);
// Add the column on the DMO side
dmoTable.GetColumns().Add(dmoColumn);
// If this column is in the primary key, add it to the primary key column list
if (columnInfos[i].Key)
{
key.GetKeyColumns().Add(columnInfos[i].Name);
}
}
// If there is anything in the primary key, add it
if (key.GetKeyColumns().GetCount() > 0)
{
dmoTable.GetKeys().Add(key);
}
// Add table to database
database.dmoDatabase.GetTables().Add(dmoTable);
database.Server.Databases.Refresh();
this.Refresh();
return(this[name]);
}
