public static void SqlCmdExample()
{
var transaction = new SqlTrans();
var cmd1 = new SqlCmd("UPDATE ... ");
var cmd2 = new SqlCmd("INSERT ...");
transaction.Add(cmd1);
transaction.Add(cmd2);
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
transaction.Commit();
}
public string Proc(string nameSpace, string className, string sa, string password)
{
string SQL = @"
USE [{0}]
DECLARE @objid INT
SELECT @objid = object_id
FROM sys.all_objects
WHERE object_id = OBJECT_ID('{1}')
SELECT name,
'type' = TYPE_NAME(user_type_id),
max_length,
precision,
scale,
is_output
FROM sys.all_parameters
WHERE object_id = @objid
ORDER BY parameter_id
";
SqlCmd cmd = new SqlCmd(databaseName.Provider, string.Format(SQL, databaseName.Name, spName));
// cmd.ChangeConnection(sa,password);
DataTable dt = cmd.FillDataTable();
DPCollection<SpParamDpo> parameters = new DPCollection<SpParamDpo>(dt);
string comment = string.Format("//Machine Generated Code by {0} at {1}", ActiveAccount.Account.UserName, DateTime.Today);
string usingString = @"{0}
using System;
using System.Data;
using Sys.Data;
";
comment = string.Format(usingString, comment);
string clss = @"{0}
namespace {1}
{{
public partial class {2}
{{
{3}
}}
}}
";
string func = @"
private static object ___{0}(int __xtype{6}{1})
{{
SqlCmd cmd = new SqlCmd(""{2}..[{0}]"");
{3}
object __result = null;
if(__xtype == 1)
cmd.ExecuteNonQuery();
else if(__xtype == 2)
__result = cmd.FillDataTable();
else if(__xtype == 3)
__result = cmd.FillDataSet();
{4}
return __result;
}}
public static void {0}({1})
{{
___{0}(1{5});
}}
public static DataTable dt_{0}({1})
{{
return (DataTable)___{0}(2{5});
}}
public static DataSet ds_{0}({1})
{{
return (DataSet)___{0}(3{5});
}}
public const string {7} = @""{8}"";
";
string signuture1 = "";
string code1 = "";
string code2 = "";
string signuture2 = "";
string tab = " ";
foreach (SpParamDpo param in parameters)
{
SpParam p = new SpParam(param);
if (signuture1 != "")
signuture1 += ", ";
if (signuture2 != "")
signuture2 += ", ";
signuture1 += p.signuture1();
signuture2 += p.signuture2();
code1 += tab + p.param1();
code2 += tab + p.param2();
}
string method = string.Format(func,
spName,
signuture1,
databaseName.Name,
code1,
code2,
signuture2 == "" ? "" : ", " + signuture2,
signuture1 == "" ? "" : ", ",
this.spDefVariable,
escapeSpDef()
);
return string.Format(clss, comment, nameSpace, className, method);
}
private bool ExecuteSql(StringBuffer buffer)
{
if (buffer.BatchSize == 0)
{
return(true);
}
string sql = buffer.AllText;
try
{
var cmd = new SqlCmd(provider, sql);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
OnError(new SqlExceptionEventArgs(sql, ex)
{
Line = buffer.BatchLine
});
return(false);
}
OnReported(new SqlExecutionEventArgs(sql)
{
BatchLine = buffer.BatchLine,
BatchSize = buffer.BatchSize,
Line = buffer.Line,
TotalSize = buffer.TotalSize
});;
return(true);
}
protected bool RefreshRow()
{
SqlCmd sqlCmd = new SqlCmd(this.tableName.Provider, selectQuery());
foreach (ColumnAdapter column in columns)
{
column.AddParameter(sqlCmd);
}
DataTable dt = sqlCmd.FillDataTable();
if (dt.Rows.Count == 0)
{
this.loadedRow = dt.NewRow();
this.exists = false;
return(false);
}
if (dt.Rows.Count > 1 && this.locator.Unique)
{
throw new ApplicationException("ERROR: Row is not unique.");
}
this.loadedRow = dt.Rows[0];
this.exists = true;
return(true);
}
public void Add(SqlCmd cmd)
{
if (cmd.DbProvider.DbCommand.Connection.State != ConnectionState.Closed)
cmd.DbProvider.DbCommand.Connection.Close();
cmd.DbProvider.DbCommand.Transaction = sqlTransaction;
cmd.DbProvider.DbCommand.Connection = this.connection;
}
public bool Update()
{
RefreshRow();
UpdateOriginValue(this.Row1);
if (this.Row1.EqualTo(dataRow))
{
return(false); //Nothing is changed
}
if (!OnRowChanged(ObjectState.Modified, false))
{
return(false);
}
string SQL;
tryUpdateQuery(out SQL);
#if DEBUG
Validate();
#endif
SqlCmd sqlCmd = new SqlCmd(this.TableName.Provider, SQL);
foreach (ColumnAdapter column in columns)
{
if (column.Field.Saved || column.Field.Identity || column.Field.Primary)
{
column.AddParameter(sqlCmd);
if (column.IsValueChanged)
{
column.OnVauleChanged();
}
}
}
if (this.transaction != null)
{
transaction.Add(sqlCmd);
}
sqlCmd.ExecuteNonQuery();
//Identity Columns
foreach (ColumnAdapter column in columns)
{
if (column.Field.Identity)
{
dataRow[column.Field.Name] = this.Row1[column.Field.Name];
column.UpdateValue(dataRow);
}
}
return(true);
}
public void Add(SqlCmd cmd)
{
if (cmd.DbProvider.DbCommand.Connection.State != ConnectionState.Closed)
{
cmd.DbProvider.DbCommand.Connection.Close();
}
cmd.DbProvider.DbCommand.Transaction = sqlTransaction;
cmd.DbProvider.DbCommand.Connection = this.connection;
}
public void InsertRow(DataRow row)
{
List <string> columns = new List <string>();
List <object> values = new List <object>();
List <SqlExpr> where = new List <SqlExpr>();
var _columns = TableName.GetTableSchema().Columns;
foreach (DataColumn column in table.Columns)
{
object value = row[column];
string name = column.ColumnName;
IColumn _column = _columns[column.ColumnName];
if (column == colRowID || _column.IsIdentity)
{
continue;
}
if (value != DBNull.Value)
{
columns.Add(name);
values.Add(value);
where.Add(name.Equal(value));
}
else if (!_column.Nullable) //add default value to COLUMN NOT NULL
{
Type type = _column.CType.ToType();
value = GetDefault(type);
columns.Add(name);
values.Add(value);
where.Add(name.Equal(value));
}
}
var builder = new SqlBuilder().INSERT(TableName, columns.ToArray()).VALUES(values.ToArray());
new SqlCmd(builder).ExecuteNonQuery();
if (colLoc != null)
{
builder = new SqlBuilder().SELECT().COLUMNS(PHYSLOC).FROM(TableName).WHERE(where.AND());
var loc = new SqlCmd(builder).FillObject <byte[]>();
LOC.Add(loc);
//todo: load identity
}
row[colRowID] = table.Rows.Count - 1; //this will trigger events ColumnChanged or RowChanged
row.AcceptChanges();
}
/// <summary>
/// Instantiate an instant from select a record from database
/// </summary>
/// <param name="where"></param>
public void UpdateObject(SqlExpr where)
{
DataRow row = new SqlCmd(this.TableName.Provider, new SqlBuilder().SELECT().COLUMNS().FROM(TableName).WHERE(where).Clause).FillDataRow();
this.exists = row != null;
if (exists)
{
this.UpdateObject(row);
}
}
private void Generate(SqlCmd cmd, DbDataReader reader, TextWriter writer, IProgress <int> progress)
{
if (reader != null)
{
GenerateByDbReader(reader, writer, progress);
return;
}
var dt = cmd.FillDataTable();
GenerateByDbTable(dt, writer);
}
public static void SqlCmdExample()
{
var transaction = new SqlTrans();
var cmd1 = new SqlCmd("UPDATE ... ");
var cmd2 = new SqlCmd("INSERT ...");
transaction.Add(cmd1);
transaction.Add(cmd2);
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
transaction.Commit();
}
public virtual DbParameter AddIdentityParameter(SqlCmd sqlCmd)
{
DbParameter param = sqlCmd.DbProvider.AddParameter(field.ParameterName, field.DataType);
param.Value = value;
if (value == System.DBNull.Value)
{
param.Value = 0;
}
param.Direction = ParameterDirection.Output;
return(param);
}
public override TableName[] GetTableNames(DatabaseName dname)
{
var table = new SqlCmd(dname.Provider, $"SELECT NULL AS SchemaName, NAME as TableName FROM sqlite_master WHERE TYPE='table' AND NOT (name LIKE 'sqlite_%') ORDER BY NAME")
.FillDataTable();
if (table != null)
{
return(table
.AsEnumerable()
.Select(row => new TableName(dname, row["SchemaName"].IsNull(string.Empty), row.Field <string>("TableName")))
.ToArray());
}
return(new TableName[] { });
}
public override TableName[] GetTableNames(DatabaseName dname)
{
var table = new SqlCmd(dname.Provider, $"SELECT TABLE_SCHEMA AS SchemaName, TABLE_NAME as TableName FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='TABLE' ORDER BY TABLE_SCHEMA,TABLE_NAME")
.FillDataTable();
if (table != null)
{
return(table
.AsEnumerable()
.Select(row => new TableName(dname, row["SchemaName"].IsNull(string.Empty), row.Field <string>("TableName")))
.ToArray());
}
return(new TableName[] { });
}
public int Generate(StreamWriter writer, IProgress <int> progress)
{
TableName tableName = schema.TableName;
string sql = string.Format("SELECT * FROM {0}", tableName);
if (Where != null)
{
sql = string.Format("SELECT * FROM {0} WHERE {1}", tableName, Where);
}
SqlCmd cmd = new SqlCmd(tableName.Provider, sql);
count = 0;
cmd.Read(reader => Generate(cmd, reader, writer, progress));
return(count);
}
private static void LoadForeignKeys(List <SchemaRow> rows, TableName tname)
{
string SQL = $"SELECT * FROM PRAGMA_FOREIGN_KEY_LIST('{tname.Name}')";
var dt = new SqlCmd(tname.Provider, SQL).FillDataTable();
foreach (DataRow row in dt.Rows)
{
string columnName = row.GetField <string>("from");
SchemaRow _row = rows.Where(x => x.TableName == tname.Name && x.ColumnName == columnName).SingleOrDefault();
_row.PK_Schema = SchemaName.empty;
_row.PK_Table = row.GetField <string>("table");
_row.PK_Column = row.GetField <string>("to");
_row.FKContraintName = $"FK_{tname.Name}_{_row.PK_Table}";
}
}
public static DataSet SqlServerSchema(ServerName sname, IEnumerable<DatabaseName> dnames)
{
StringBuilder builder = new StringBuilder();
foreach (DatabaseName dname in dnames)
{
builder.AppendLine(SqlDatabaseSchema(dname));
}
DataSet ds = new SqlCmd(sname.Provider, builder.ToString()).FillDataSet();
ds.DataSetName = sname.Path;
int i = 0;
foreach (DatabaseName dname in dnames)
{
ds.Tables[i++].TableName = dname.Name;
}
return ds;
}
public void SetValue()
{
if (association == null)
{
return;
}
object value1 = propertyInfo1.GetValue(dpoInstance, null);
SqlCmd cmd = new SqlCmd(this.clause2);
cmd.AddParameter(association.Column1.SqlParameterName(), value1);
DataTable dataTable = cmd.FillDataTable();
if (mappingType == MappingType.One2One)
{
//if association object was not instatiated
if (propertyInfo2.GetValue(this, null) == null)
{
PersistentObject dpo = (PersistentObject)Activator.CreateInstance(propertyInfo2.PropertyType, null);
dpo.FillObject(dataTable.Rows[0]);
propertyInfo2.SetValue(this, dpo, null);
}
else
{
IDPObject dpo = (IDPObject)propertyInfo2.GetValue(this, null);
dpo.FillObject(dataTable.Rows[0]);
}
}
else
{
//if association collection was not instatiated
if (propertyInfo2.GetValue(this, null) == null)
{
propertyInfo2.SetValue(this, Activator.CreateInstance(propertyInfo2.PropertyType, new object[] { dataTable }), null);
}
else
{
IPersistentCollection collection = (IPersistentCollection)propertyInfo2.GetValue(this, null);
collection.Table = dataTable;
}
}
}
public bool Invalid()
{
bool result = false;
SqlCmd.Error += (sender, e) =>
{
result = true;
};
try
{
SqlCmd.ExecuteScalar();
return(result);
}
catch (Exception)
{
return(true);
}
}
public virtual bool Delete()
{
DataRow r = this.Row1;
if (!Exists)
{
return(false);
}
else
{
UpdateOriginValue(r);
}
if (!OnRowChanged(ObjectState.Deleted, false))
{
return(false);
}
SqlCmd sqlCmd = new SqlCmd(this.TableName.Provider, deleteQuery());
foreach (ColumnAdapter column in columns)
{
column.AddParameter(sqlCmd);
}
if (this.transaction != null)
{
transaction.Add(sqlCmd);
}
sqlCmd.ExecuteNonQuery();
Clear();
dataRow.Delete();
return(true);
}
public static DataSet LoadDatabaseSchema(ServerName sname, IEnumerable <DatabaseName> dnames, Func <DatabaseName, string> sqlOfDatabaseSchema)
{
StringBuilder builder = new StringBuilder();
foreach (DatabaseName dname in dnames)
{
builder.AppendLine(sqlOfDatabaseSchema(dname));
}
DataSet ds = new SqlCmd(sname.Provider, builder.ToString()).FillDataSet();
ds.DataSetName = sname.Path;
int i = 0;
foreach (DatabaseName dname in dnames)
{
ds.Tables[i++].TableName = dname.Name;
}
return(ds);
}
public virtual void AddParameter(SqlCmd sqlCmd)
{
DbParameter param = sqlCmd.DbProvider.AddParameter(field.ParameterName, field.DataType);
if (value is DateTime)
{
DateTime SqlMinValue = new DateTime(1900, 1, 1);
DateTime SqlMaxValue = new DateTime(9999, 12, 31);
if ((DateTime)value < SqlMinValue)
{
value = SqlMinValue;
}
else if ((DateTime)value > SqlMaxValue)
{
value = SqlMaxValue;
}
}
param.Value = value;
param.Direction = ParameterDirection.Input;
}
public override DependencyInfo[] GetDependencySchema(DatabaseName dname)
{
const string sql = @"
SELECT
FK.TABLE_SCHEMA AS FK_SCHEMA,
FK.TABLE_NAME AS FK_Table,
PK.TABLE_SCHEMA AS PK_SCHEMA,
PK.TABLE_NAME AS PK_Table,
PT.COLUMN_NAME AS PK_Column,
CU.COLUMN_NAME AS FK_Column
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN ( SELECT i1.TABLE_NAME ,
i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE FK.TABLE_NAME <> PK.TABLE_NAME
";
var dt = new SqlCmd(dname.Provider, sql).FillDataTable();
DependencyInfo[] rows = dt.AsEnumerable().Select(
row => new DependencyInfo
{
FkTable = new TableName(dname, row["FK_SCHEMA"].IsNull(string.Empty), (string)row["FK_Table"]),
PkTable = new TableName(dname, row["PK_SCHEMA"].IsNull(string.Empty), (string)row["PK_Table"]),
PkColumn = (string)row["PK_Column"],
FkColumn = (string)row["FK_Column"]
})
.ToArray();
return(rows);
}
private static void LoadColumns(List <SchemaRow> rows, TableName tname)
{
string SQL = $"SELECT * FROM PRAGMA_TABLE_INFO('{tname.Name}')";
var dt = new SqlCmd(tname.Provider, SQL).FillDataTable();
foreach (DataRow row in dt.Rows)
{
string columnName = row.GetField <string>("name");
SchemaRow _row = rows.Where(x => x.TableName == tname.Name && x.ColumnName == columnName).SingleOrDefault();
if (_row == null)
{
_row = new SchemaRow
{
SchemaName = SchemaName.empty,
TableName = tname.Name,
ColumnName = row.GetField <string>("name"),
};
}
_row.DataType = row.GetField <string>("type");
_row.Length = 0;
_row.Nullable = row.GetField <long>("notnull") == 0;
_row.precision = 0;
_row.scale = 0;
_row.IsPrimary = row.GetField <long>("pk") == 1;
_row.IsIdentity = _row.DataType.ToLower().Contains("identity");
_row.IsComputed = false;
_row.definition = null;
_row.PKContraintName = _row.IsPrimary ? $"PK_{tname.Name}" : null;
ParseType(_row, _row.DataType);
rows.Add(_row);
}
}
public int Generate(string nameSpace, string sa, string password)
{
string SQL = @"
USE [{0}]
SELECT name, OBJECT_DEFINITION(OBJECT_ID) AS sp
FROM sys.procedures
WHERE is_ms_shipped <> 1
ORDER BY name
";
SqlCmd cmd = new SqlCmd(databaseName.Provider, string.Format(SQL, databaseName.Name));
// cmd.ChangeConnection(sa, password);
DataTable dt = cmd.FillDataTable();
foreach (DataRow row in dt.Rows)
{
SpProc proc = new SpProc(databaseName, (string)row[SP_NAME], row[SP_DEFINITION].IsNull<string>(""));
string sourceCode = proc.Proc(nameSpace, databaseName.Name, sa, password);
WriteFile(proc.SpName, sourceCode, nameSpace, proc.IsSpChanged(nameSpace, databaseName.Name));
}
return dt.Rows.Count;
}
private bool ExecuteSql(int i, StringBuilder builder)
{
string sql = builder.ToString();
if (string.IsNullOrEmpty(sql))
return true;
try
{
var cmd = new SqlCmd(provider, sql);
//cmd.Error += (sender, e) => { OnError(e); };
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
OnError(new SqlExceptionEventArgs(sql, ex) { Line = i });
return false;
}
OnReported(new EventArgs<int, string>(i, sql));
return true;
}
private void DoMultipleLineCommand(string text)
{
text = text.Trim();
if (text == string.Empty)
return;
string[] A = text.Split(' ', '\r');
string cmd = null;
string arg1 = null;
string arg2 = null;
int n = A.Length;
if (n > 0)
cmd = A[0].ToLower();
if (n > 1)
arg1 = A[1].Trim();
if (n > 2)
arg2 = A[2].Trim();
switch (cmd)
{
case "use":
case "select":
if (!Context.GetValue<bool>(Context.DATAREADER))
{
DataSet ds = new SqlCmd(theSide.Provider, text).FillDataSet();
if (ds != null)
{
foreach (DataTable dt in ds.Tables)
dt.ToConsole();
}
}
else
new SqlCmd(theSide.Provider, text).Read(reader => reader.ToConsole(Context.GetValue<int>(Context.MAXROWS, 100)));
break;
case "update":
case "delete":
case "insert":
case "exec":
case "create":
case "alter":
case "drop":
try
{
int count = new SqlCmd(theSide.Provider, text).ExecuteNonQuery();
if (count > 0)
stdio.WriteLine("{0} of row(s) affected", count);
else if (count == 0)
stdio.WriteLine("nothing affected");
else
stdio.WriteLine("command(s) completed successfully");
}
catch (Exception ex)
{
stdio.ErrorFormat(ex.Message);
}
break;
default:
if (text.EndsWith(";"))
{
try
{
Script.Execute(text, Context.DS);
}
catch (Exception ex)
{
stdio.ErrorFormat("execute error: {0}", ex.Message);
}
}
else
{
try
{
var val = Script.Evaluate(text, Context.DS);
stdio.WriteLine(string.Format(" results {0}", val));
}
catch (Exception ex)
{
stdio.ErrorFormat("evaluate error: {0}", ex.Message);
}
}
break;
}
}
protected bool RefreshRow()
{
SqlCmd sqlCmd = new SqlCmd(this.tableName.Provider, selectQuery());
foreach (ColumnAdapter column in columns)
{
column.AddParameter(sqlCmd);
}
DataTable dt = sqlCmd.FillDataTable();
if (dt.Rows.Count == 0)
{
this.loadedRow = dt.NewRow();
this.exists = false;
return false;
}
if (dt.Rows.Count > 1 && this.locator.Unique)
throw new ApplicationException("ERROR: Row is not unique.");
this.loadedRow = dt.Rows[0];
this.exists = true;
return true;
}
public void del(Command cmd)
{
if (cmd.HasHelp)
{
stdio.WriteLine("command del or erase: drop tables or delete data rows");
stdio.WriteLine("del tablename : drop table");
stdio.WriteLine("del [sql where clause] : delete current table filtered rows");
stdio.WriteLine("example:");
stdio.WriteLine(@"local> del Northwind\Products : drop table [Products]");
stdio.WriteLine(@"local\Northwind\Products> del : delete all rows of table [Products]");
stdio.WriteLine(@"local\Northwind\Products> del col1=1 and col2='match' : del rows matched on columns:c1 or c2");
return;
}
var pt = mgr.current;
if (!(pt.Item is Locator) && !(pt.Item is TableName))
{
TableName[] T = null;
if (cmd.arg1 != null)
{
PathName path = new PathName(cmd.arg1);
var node = mgr.Navigate(path);
if (node != null)
{
var dname = mgr.GetPathFrom<DatabaseName>(node);
if (dname != null)
{
if (cmd.wildcard != null)
{
var m = new MatchedDatabase(dname, cmd.wildcard, new string[] { });
T = m.MatchedTableNames;
}
else
{
var _tname = mgr.GetPathFrom<TableName>(node);
if (_tname != null)
T = new TableName[] { _tname };
else
{
stdio.ErrorFormat("invalid path");
return;
}
}
}
else
{
stdio.ErrorFormat("database is unavailable");
return;
}
}
else
{
stdio.ErrorFormat("invalid path");
return;
}
}
if (T != null && T.Length > 0)
{
if (!stdio.YesOrNo("are you sure to drop {0} tables (y/n)?", T.Length))
return;
try
{
var sqlcmd = new SqlCmd(T[0].Provider, string.Empty);
sqlcmd.ExecuteNonQueryTransaction(T.Select(row => string.Format("DROP TABLE {0}", row)));
stdio.ErrorFormat("completed to drop table(s):\n{0}", string.Join<TableName>("\n", T));
}
catch (Exception ex)
{
stdio.ErrorFormat(ex.Message);
}
}
else
stdio.ErrorFormat("table is not selected");
return;
}
TableName tname = null;
Locator locator = null;
if (pt.Item is Locator)
{
locator = mgr.GetCombinedLocator(pt);
tname = mgr.GetCurrentPath<TableName>();
if (!string.IsNullOrEmpty(cmd.args))
locator.And(new Locator(cmd.args));
}
if (pt.Item is TableName)
{
tname = (TableName)pt.Item;
if (!string.IsNullOrEmpty(cmd.args))
locator = new Locator(cmd.args);
}
if (locator == null)
stdio.Write("are you sure to delete all rows (y/n)?");
else
stdio.Write("are you sure to delete (y/n)?");
if (stdio.ReadKey() != ConsoleKey.Y)
return;
stdio.WriteLine();
try
{
int count;
if (locator == null)
count = new SqlBuilder().DELETE(tname).SqlCmd.ExecuteNonQuery();
else
count = new SqlBuilder().DELETE(tname).WHERE(locator).SqlCmd.ExecuteNonQuery();
stdio.WriteLine("{0} of row(s) affected", count);
}
catch (Exception ex)
{
stdio.ErrorFormat(ex.Message);
}
}
internal TableReader(TableName tableName, string sql)
{
this.sql = sql;
this.tableName = tableName;
this.cmd = new SqlCmd(tableName.Provider, sql);
}
public bool Update()
{
RefreshRow();
UpdateOriginValue(this.Row1);
if (this.Row1.EqualTo(dataRow))
return false; //Nothing is changed
if (!OnRowChanged(ObjectState.Modified, false))
return false;
string SQL;
tryUpdateQuery(out SQL);
#if DEBUG
Validate();
#endif
SqlCmd sqlCmd = new SqlCmd(this.TableName.Provider, SQL);
foreach (ColumnAdapter column in columns)
{
if (column.Field.Saved || column.Field.Identity || column.Field.Primary)
{
column.AddParameter(sqlCmd);
if (column.IsValueChanged)
column.OnVauleChanged();
}
}
if (this.transaction != null)
transaction.Add(sqlCmd);
sqlCmd.ExecuteNonQuery();
//Identity Columns
foreach (ColumnAdapter column in columns)
{
if (column.Field.Identity)
{
dataRow[column.Field.Name] = this.Row1[column.Field.Name];
column.UpdateValue(dataRow);
}
}
return true;
}
public virtual bool Delete()
{
DataRow r = this.Row1;
if (!Exists)
return false;
else
UpdateOriginValue(r);
if (!OnRowChanged(ObjectState.Deleted, false))
return false;
SqlCmd sqlCmd = new SqlCmd(this.TableName.Provider, deleteQuery());
foreach (ColumnAdapter column in columns)
{
column.AddParameter(sqlCmd);
}
if (this.transaction != null)
transaction.Add(sqlCmd);
sqlCmd.ExecuteNonQuery();
Clear();
dataRow.Delete();
return true;
}
public void ExportDataContract(Command cmd, int version)
{
DataTable dt = null;
if (SqlShell.LastResult is DataTable)
{
dt = SqlShell.LastResult as DataTable;
}
if (dt == null)
{
if (tname != null)
{
dt = new SqlCmd(tname.Provider, $"SELECT TOP 1 * FROM {tname.FormalName}").FillDataTable();
}
else
{
stdio.ErrorFormat("data table cannot find, use command type or select first");
return;
}
}
string path = cmd.GetValue("out") ?? cfg.GetValue<string>("dc.path", $"{Configuration.MyDocuments}\\dc");
string ns = cmd.GetValue("ns") ?? cfg.GetValue<string>("dc.ns", "Sys.DataModel.DataContracts");
string clss = cmd.GetValue("class") ?? cfg.GetValue<string>("dc.class", "DataContract");
string mtd = cmd.GetValue("method");
string[] keys = cmd.Columns;
if (version == 1)
{
var builder = new DataContractClassBuilder(ns, cmd, dt)
{
cname = clss,
mtd = mtd,
keys = keys
};
string file = builder.WriteFile(path);
stdio.WriteLine("code generated on {0}", file);
}
else
{
var builder = new DataContract2ClassBuilder(ns, cmd, dt)
{
cname = clss,
mtd = mtd
};
string file = builder.WriteFile(path);
stdio.WriteLine("code generated on {0}", file);
}
}
public override DataTable GetDependencySchema(DatabaseName dname)
{
const string sql = @"
SELECT
FK.TABLE_SCHEMA AS FK_SCHEMA,
FK.TABLE_NAME AS FK_Table,
PK.TABLE_SCHEMA AS PK_SCHEMA,
PK.TABLE_NAME AS PK_Table,
PT.COLUMN_NAME AS PK_Column,
CU.COLUMN_NAME AS FK_Column
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN ( SELECT i1.TABLE_NAME ,
i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE FK.TABLE_NAME <> PK.TABLE_NAME
";
var dt = new SqlCmd(dname.Provider, sql).FillDataTable();
return dt;
}
public void InsertRow(DataRow row)
{
List<string> columns = new List<string>();
List<object> values = new List<object>();
List<SqlExpr> where = new List<SqlExpr>();
var _columns = TableName.GetTableSchema().Columns;
foreach (DataColumn column in table.Columns)
{
object value = row[column];
string name = column.ColumnName;
IColumn _column = _columns[column.ColumnName];
if (column == colRowID)
continue;
if (value != DBNull.Value)
{
columns.Add(name);
values.Add(value);
where.Add(name.Equal(value));
}
else if (!_column.Nullable) //add default value to COLUMN NOT NULL
{
Type type = _column.CType.ToType();
value = GetDefault(type);
columns.Add(name);
values.Add(value);
where.Add(name.Equal(value));
}
}
var builder = new SqlBuilder().INSERT(TableName, columns.ToArray()).VALUES(values.ToArray());
new SqlCmd(builder).ExecuteNonQuery();
builder = new SqlBuilder().SELECT.COLUMNS(SqlExpr.PHYSLOC).FROM(TableName).WHERE(where.AND());
var loc = new SqlCmd(builder).FillObject<byte[]>();
LOC.Add(loc);
row[colRowID] = table.Rows.Count - 1; //this will trigger events ColumnChanged or RowChanged
row.AcceptChanges();
}
public static void FindName(this Side side, string match)
{
bool found = false;
string sql = "SELECT name AS TableName FROM sys.tables";
var dt = new SqlCmd(side.Provider, sql).FillDataTable();
Search(match, dt, "TableName");
if (dt.Rows.Count != 0)
{
found = true;
stdio.DisplayTitle("Table Names");
dt.ToConsole();
};
sql = @"
SELECT
s.name as SchemaName,
t.name as TableName,
c.name AS ColumnName,
ty.name AS DataType,
c.max_length AS Length,
CASE c.is_nullable WHEN 0 THEN 'NOT NULL' WHEN 1 THEN 'NULL' END AS Nullable
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types ty ON ty.system_type_id =c.system_type_id AND ty.name<>'sysname'
LEFT JOIN sys.Computed_columns d ON t.object_id = d.object_id AND c.name = d.name
INNER JOIN sys.schemas s ON s.schema_id=t.schema_id
ORDER BY c.name, c.column_id
";
dt = new SqlCmd(side.Provider, sql).FillDataTable();
Search(match, dt, "ColumnName");
if (dt.Rows.Count != 0)
{
found = true;
stdio.DisplayTitle("Table Columns");
dt.ToConsole();
};
sql = @"SELECT SCHEMA_NAME(schema_id) SchemaName, name AS ViewName FROM sys.views ORDER BY name";
dt = new SqlCmd(side.Provider, sql).FillDataTable();
Search(match, dt, "ViewName");
if (dt.Rows.Count != 0)
{
found = true;
stdio.DisplayTitle("View Names");
dt.ToConsole();
}
sql = @"
SELECT
VCU.TABLE_NAME AS ViewName,
COL.COLUMN_NAME AS ColumnName,
COL.DATA_TYPE,
COL.IS_NULLABLE
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS VCU
JOIN INFORMATION_SCHEMA.COLUMNS AS COL
ON COL.TABLE_SCHEMA = VCU.TABLE_SCHEMA
AND COL.TABLE_CATALOG = VCU.TABLE_CATALOG
AND COL.TABLE_NAME = VCU.TABLE_NAME
AND COL.COLUMN_NAME = VCU.COLUMN_NAME";
dt = new SqlCmd(side.Provider, sql).FillDataTable();
Search(match, dt, "ColumnName");
if (dt.Rows.Count != 0)
{
found = true;
stdio.DisplayTitle("View Columns");
dt.ToConsole();
}
if (!found)
stdio.WriteLine("nothing is found");
}
public static TableName[] TableWithPrimaryKey(this DatabaseName dname)
{
const string SQL = @"
SELECT DISTINCT pk.TABLE_SCHEMA, pk.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE
CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME";
var list = new SqlCmd(dname.Provider, SQL)
.FillDataTable()
.AsEnumerable()
.Select(row => new TableName(dname, row.Field<string>("TABLE_SCHEMA"), row.Field<string>("TABLE_NAME")))
.ToArray();
return list;
}
private bool DoSingleLineCommand(string text)
{
text = text.Trim();
if (text == string.Empty)
return false;
Command cmd = new Command(text, cfg);
switch (cmd.Action)
{
case "set":
commandee.set(cmd);
return true;
case "let":
commandee.let(cmd);
return true;
case "md":
case "mkdir":
commandee.mkdir(cmd);
return true;
case "rd":
case "rmdir":
commandee.rmdir(cmd);
return true;
}
switch (cmd.Action)
{
case "ls":
case "dir":
commandee.dir(cmd);
return true;
case "cd":
case "chdir":
if (cmd.arg1 != null || cmd.HasHelp)
chdir(cmd);
else
stdio.WriteLine(mgr.ToString());
return true;
case "type":
commandee.type(cmd);
return true;
case "del":
case "erase":
commandee.del(cmd);
return true;
case "ren":
case "rename":
commandee.rename(cmd);
return true;
case "attrib":
commandee.attrib(cmd);
return true;
case "echo":
stdio.WriteLine(text);
return true;
case "rem":
return true;
case "ver":
stdio.WriteLine("sqlcon [Version {0}]", SysExtension.ApplicationVerison);
return true;
case "show":
if (cmd.arg1 != null)
Show(cmd.arg1.ToLower(), cmd.arg2);
else
stdio.ErrorFormat("invalid argument");
return true;
case "find":
if (cmd.arg1 != null)
theSide.FindName(cmd.arg1);
else
stdio.ErrorFormat("find object undefined");
return true;
case "save":
if (cmd.arg1 == "output")
{
if (!File.Exists(this.cfg.OutputFile))
{
stdio.ErrorFormat("no output file found : {0}", this.cfg.OutputFile);
break;
}
using (var reader = new StreamReader(this.cfg.OutputFile))
{
string data = reader.ReadToEnd();
System.Windows.Clipboard.SetText(data);
stdio.WriteLine("copied to clipboard");
}
}
return true;
case "execute":
commandee.execute(cmd, theSide);
return true;
case "open":
commandee.open(cmd, cfg);
return true;
case "compare":
{
PathBothSide both = new PathBothSide(mgr, cmd);
using (var writer = cfg.OutputFile.NewStreamWriter())
{
ActionType type;
if (cmd.IsSchema)
type = ActionType.CompareSchema;
else
type = ActionType.CompareData;
if (both.Invalid)
{
return true;
}
var adapter = new CompareAdapter(both.ps1.side, both.ps2.side);
var sql = adapter.Run(type, both.ps1.MatchedTables, both.ps2.MatchedTables, cfg, cmd.Columns);
writer.Write(sql);
}
stdio.WriteLine("completed");
return true;
}
case "copy":
commandee.copy(cmd, CompareSideType.copy);
return true;
case "sync":
commandee.copy(cmd, CompareSideType.sync);
return true;
case "comp":
commandee.copy(cmd, CompareSideType.compare);
return true;
case "xcopy":
commandee.xcopy(cmd);
return true;
//example: run func(id=20)
case "run":
{
VAL result = Context.Evaluate(cmd.args);
if (result.IsNull)
stdio.ErrorFormat("undefined query function");
else if (result.IsInt)
{
//show error code
}
else
{
if (!result.IsList && result.Size != 2)
{
stdio.ErrorFormat("invalid format, run query like >run query(id=1)");
return true;
}
try
{
DataSet ds = new SqlCmd(theSide.Provider, (string)result[0]).ParseParameters(result[1]).FillDataSet();
if (ds != null)
{
foreach (DataTable dt in ds.Tables)
dt.ToConsole();
}
else
stdio.ErrorFormat("cannot retrieve data from server");
}
catch (Exception ex)
{
stdio.ErrorFormat("{0}", ex.Message);
return true;
}
}
}
return true;
case "export":
commandee.export(cmd, cfg, this);
return true;
case "clean":
commandee.clean(cmd, cfg);
return true;
case "mount":
commandee.mount(cmd, cfg);
return true;
case "umount":
commandee.umount(cmd, cfg);
return true;
case "edit":
commandee.edit(cmd, theSide);
return true;
default:
break;
}
return false;
}
public void ExportInsert(Command cmd)
{
if (tname != null)
{
if (cmd.IsSchema)
{
using (var writer = fileName.NewStreamWriter())
{
string sql = Compare.GenerateTemplate(new TableSchema(tname), SqlScriptType.INSERT);
stdio.WriteLine(sql);
writer.WriteLine(sql);
}
}
else
{
var node = mgr.GetCurrentNode<Locator>();
int count;
using (var writer = fileName.NewStreamWriter())
{
if (node != null)
{
stdio.WriteLine("start to generate {0} INSERT script to file: {1}", tname, fileName);
Locator locator = mgr.GetCombinedLocator(node);
count = Compare.GenerateRows(writer, new TableSchema(tname), locator, cmd.HasIfExists);
stdio.WriteLine("insert clauses (SELECT * FROM {0} WHERE {1}) generated to {2}", tname, locator, fileName);
}
else
{
count = Compare.GenerateRows(writer, new TableSchema(tname), null, cmd.HasIfExists);
stdio.WriteLine("insert clauses (SELECT * FROM {0}) generated to {1}", tname, fileName);
}
}
}
}
else if (dname != null)
{
stdio.WriteLine("start to generate {0} script to file: {1}", dname, fileName);
using (var writer = fileName.NewStreamWriter())
{
var md = new MatchedDatabase(dname, cmd.wildcard, cfg.exportExcludedTables);
TableName[] tnames = md.MatchedTableNames;
CancelableWork.CanCancel(cts =>
{
foreach (var tn in tnames)
{
if (cts.IsCancellationRequested)
return;
if (!cfg.exportExcludedTables.IsMatch(tn.ShortName))
{
int count = new SqlCmd(tn.Provider, string.Format("SELECT COUNT(*) FROM {0}", tn)).FillObject<int>();
if (count > cfg.Export_Max_Count)
{
if (!stdio.YesOrNo("are you sure to export {0} rows on {1} (y/n)?", count, tn.ShortName))
{
stdio.WriteLine("\n{0,10} skipped", tn.ShortName);
continue;
}
}
count = Compare.GenerateRows(writer, new TableSchema(tn), null, cmd.HasIfExists);
stdio.WriteLine("{0,10} row(s) generated on {1}", count, tn.ShortName);
}
else
stdio.WriteLine("{0,10} skipped", tn.ShortName);
}
stdio.WriteLine("completed");
});
}
}
else
stdio.ErrorFormat("warning: table or database is not selected");
}
public void SetValue()
{
if (association == null)
return ;
object value1 = propertyInfo1.GetValue(dpoInstance, null);
SqlCmd cmd = new SqlCmd(this.clause2);
cmd.AddParameter(association.Column1.SqlParameterName(), value1);
DataTable dataTable = cmd.FillDataTable();
if (mappingType == MappingType.One2One)
{
//if association object was not instatiated
if (propertyInfo2.GetValue(this, null) == null)
{
PersistentObject dpo = (PersistentObject)Activator.CreateInstance(propertyInfo2.PropertyType, null);
dpo.FillObject(dataTable.Rows[0]);
propertyInfo2.SetValue(this, dpo, null);
}
else
{
IDPObject dpo = (IDPObject)propertyInfo2.GetValue(this, null);
dpo.FillObject(dataTable.Rows[0]);
}
}
else
{
//if association collection was not instatiated
if (propertyInfo2.GetValue(this, null) == null)
propertyInfo2.SetValue(this, Activator.CreateInstance(propertyInfo2.PropertyType, new object[] { dataTable }), null);
else
{
IPersistentCollection collection = (IPersistentCollection)propertyInfo2.GetValue(this, null);
collection.Table = dataTable;
}
}
}
public static object ExecuteScalar(this ConnectionProvider provider, string script)
{
SqlCmd cmd = new SqlCmd(provider, script);
return(cmd.ExecuteScalar());
}
public bool Insert()
{
if (!OnRowChanged(ObjectState.Added, false))
return false;
string SQL = insertQuery();
#if DEBUG
Validate();
#endif
SqlCmd sqlCmd = new SqlCmd(this.TableName.Provider, SQL);
foreach (ColumnAdapter column in columns)
{
if (column.Field.Identity)
{
if (!this.InsertIdentityOn)
column.AddIdentityParameter(sqlCmd);
else
column.AddParameter(sqlCmd);
}
else if (column.Field.Saved || column.Field.Primary)
{
column.AddParameter(sqlCmd);
if (column.IsValueChanged)
column.OnVauleChanged();
}
}
if (this.transaction != null)
transaction.Add(sqlCmd);
sqlCmd.ExecuteNonQuery();
//Identity Columns
if (!this.InsertIdentityOn)
{
bool hasIdentity = false;
foreach (ColumnAdapter column in columns)
{
if (column.Field.Identity)
{
hasIdentity = true;
dataRow[column.Field.Name] = sqlCmd.GetReturnValue(column.Field.ParameterName);
column.UpdateValue(dataRow);
}
}
if (hasIdentity)
OnRowChanged(ObjectState.Added, true);
}
return true;
}
public virtual void AddParameter(SqlCmd sqlCmd)
{
DbParameter param = sqlCmd.DbProvider.AddParameter(field.ParameterName, field.DataType);
if (value is DateTime)
{
DateTime SqlMinValue = new DateTime(1900, 1, 1);
DateTime SqlMaxValue = new DateTime(9999, 12, 31);
if ((DateTime)value < SqlMinValue)
value = SqlMinValue;
else if ((DateTime)value > SqlMaxValue)
value = SqlMaxValue;
}
param.Value = value;
param.Direction = ParameterDirection.Input;
}
private void Execute()
{
string text = textBox.GetSelectionOrAllText();
if (text == string.Empty)
return;
tabControl.Items.Clear();
var cmd = new SqlCmd(provider, text);
if (text.IndexOf("select", StringComparison.CurrentCultureIgnoreCase) >= 0
&& text.IndexOf("insert", StringComparison.CurrentCultureIgnoreCase) < 0
&& text.IndexOf("update", StringComparison.CurrentCultureIgnoreCase) < 0
&& text.IndexOf("delete", StringComparison.CurrentCultureIgnoreCase) < 0
)
{
try
{
StringBuilder builder = new StringBuilder();
var ds = cmd.FillDataSet();
int i = 1;
foreach (DataTable dt in ds.Tables)
{
var tab = new TabItem { Header = $"Table {i++}", Content = DisplayTable(dt) };
tabControl.Items.Add(tab);
builder.AppendLine($"{dt.Rows.Count} row(s) affected");
}
DisplayMessage(builder.ToString());
}
catch (SqlException ex)
{
DisplayMessage(ex.Message());
}
catch (Exception ex)
{
DisplayMessage(ex.Message);
}
}
else
{
try
{
int count = cmd.ExecuteNonQuery();
string message = $"{count} row(s) affected";
DisplayMessage(message);
}
catch (SqlException ex)
{
DisplayMessage(ex.Message());
}
catch (Exception ex)
{
DisplayMessage(ex.Message);
}
}
if (tabControl.HasItems)
(tabControl.Items[0] as TabItem).Focus();
}
public bool Insert()
{
if (!OnRowChanged(ObjectState.Added, false))
{
return(false);
}
string SQL = insertQuery();
#if DEBUG
Validate();
#endif
SqlCmd sqlCmd = new SqlCmd(this.TableName.Provider, SQL);
foreach (ColumnAdapter column in columns)
{
if (column.Field.Identity)
{
if (!this.InsertIdentityOn)
{
column.AddIdentityParameter(sqlCmd);
}
else
{
column.AddParameter(sqlCmd);
}
}
else if (column.Field.Saved || column.Field.Primary)
{
column.AddParameter(sqlCmd);
if (column.IsValueChanged)
{
column.OnVauleChanged();
}
}
}
if (this.transaction != null)
{
transaction.Add(sqlCmd);
}
sqlCmd.ExecuteNonQuery();
//Identity Columns
if (!this.InsertIdentityOn)
{
bool hasIdentity = false;
foreach (ColumnAdapter column in columns)
{
if (column.Field.Identity)
{
hasIdentity = true;
dataRow[column.Field.Name] = sqlCmd.GetReturnValue(column.Field.ParameterName);
column.UpdateValue(dataRow);
}
}
if (hasIdentity)
{
OnRowChanged(ObjectState.Added, true);
}
}
return(true);
}
public void let(Command cmd)
{
if (cmd.HasHelp)
{
stdio.WriteLine("let assignment : update key-value table row, key-value table must be defined on the sqlcon.cfg or user.cfg");
stdio.WriteLine("let key=value : update column by current table or locator");
stdio.WriteLine("example:");
stdio.WriteLine("let Smtp.Host=\"127.0.0.1\" : update key-value row, it's equivalent to UPDATE table SET [Value]='\"127.0.0.1\"' WHERE [Key]='Smtp.Host'");
return;
}
if (string.IsNullOrEmpty(cmd.args))
{
stdio.ErrorFormat("argument cannot be empty");
return;
}
var pt = mgr.current;
if (!(pt.Item is Locator) && !(pt.Item is TableName))
{
stdio.ErrorFormat("table is not selected");
return;
}
if (this.mgr.Configuration.dictionarytables.Count == 0)
{
stdio.ErrorFormat("key-value tables is undefined");
return;
}
TableName tname = mgr.GetCurrentPath<TableName>();
var setting = this.mgr.Configuration.dictionarytables.FirstOrDefault(row => row.TableName.ToUpper() == tname.Name.ToUpper());
if (setting == null)
{
stdio.ErrorFormat("current table is not key-value tables");
return;
}
string[] kvp = cmd.args.Split('=');
string key = null;
string value = null;
if (kvp.Length == 1)
{
key = kvp[0].Trim();
}
else if (kvp.Length == 2)
{
key = kvp[0].Trim();
value = kvp[1].Trim();
}
if (string.IsNullOrEmpty(key))
{
stdio.ErrorFormat("invalid assignment");
return;
}
Locator locator = new Locator(setting.KeyName.ColumnName() == key);
SqlBuilder builder = new SqlBuilder().SELECT.COLUMNS(setting.ValueName.ColumnName()).FROM(tname).WHERE(locator);
var L = new SqlCmd(builder).FillDataColumn<string>(0);
if (L.Count() == 0)
{
stdio.ErrorFormat("undefined key: {0}", key);
return;
}
if (kvp.Length == 1)
{
stdio.ErrorFormat("{0} = {1}", key, L.First());
return;
}
builder = new SqlBuilder()
.UPDATE(tname)
.SET(setting.ValueName.ColumnName() == value)
.WHERE(locator);
try
{
int count = builder.SqlCmd.ExecuteNonQuery();
stdio.WriteLine("{0} of row(s) affected", count);
}
catch (Exception ex)
{
stdio.ErrorFormat(ex.Message);
}
}
public static DataTable FillDataTable(this TableName tname, string script)
{
SqlCmd cmd = new SqlCmd(tname.Provider, script);
return(cmd.FillDataTable());
}
public static int ExecuteNonQuery(this ConnectionProvider provider, string script)
{
SqlCmd cmd = new SqlCmd(provider, script);
return(cmd.ExecuteNonQuery());
}
public virtual DbParameter AddIdentityParameter(SqlCmd sqlCmd)
{
DbParameter param = sqlCmd.DbProvider.AddParameter(field.ParameterName, field.DataType);
param.Value = value;
if (value == System.DBNull.Value)
param.Value = 0;
param.Direction = ParameterDirection.Output;
return param;
}
public static DataTable FillDataTable(this ConnectionProvider provider, string script)
{
SqlCmd cmd = new SqlCmd(provider, script);
return(cmd.FillDataTable());
}
public void copy(Command cmd, CompareSideType sideType)
{
if (cmd.HasHelp)
{
if (sideType == CompareSideType.copy)
{
stdio.WriteLine("copy schema or records from table1 to table2, support table name wildcards");
stdio.WriteLine("copy table1 [table2] [/s]");
}
else if (sideType == CompareSideType.sync)
{
stdio.WriteLine("synchronize schema or records from table1 to table2");
stdio.WriteLine("sync table1 [table2] [/s] : sync table1' records to table2");
}
else if (sideType == CompareSideType.compare)
{
stdio.WriteLine("compare schema or records from table1 to table2");
stdio.WriteLine("comp table1 [table2] [/s] : sync table1' records to table2");
}
stdio.WriteLine("support table name wildcards");
stdio.WriteLine("[/s] : table schema, default table records");
return;
}
CancelableWork.CanCancel(cts =>
{
PathBothSide both = new PathBothSide(mgr, cmd);
var dname2 = mgr.GetPathFrom<DatabaseName>(both.ps2.Node);
if (both.ps1.MatchedTables == null)
return;
foreach (var tname1 in both.ps1.MatchedTables)
{
if (cts.IsCancellationRequested)
return;
TableName tname2 = mgr.GetPathFrom<TableName>(both.ps2.Node);
if (tname2 == null)
{
tname2 = new TableName(dname2, tname1.SchemaName, tname1.ShortName);
}
var adapter = new CompareAdapter(both.ps1.side, both.ps2.side);
//stdio.WriteLine("start to {0} from {1} to {2}", sideType, tname1, tname2);
var sql = adapter.CompareTable(cmd.IsSchema ? ActionType.CompareSchema : ActionType.CompareData,
sideType, tname1, tname2, mgr.Configuration.PK, cmd.Columns);
if (sideType == CompareSideType.compare)
{
if (sql == string.Empty)
{
stdio.WriteLine("source {0} and destination {1} are identical", tname1, tname2);
}
continue;
}
if (sql == string.Empty)
{
stdio.WriteLine("nothing changes made on destination {0}", tname2);
}
else
{
bool exists = tname2.Exists();
try
{
var sqlcmd = new SqlCmd(both.ps2.side.Provider, sql);
int count = sqlcmd.ExecuteNonQueryTransaction();
if (exists)
{
if (count >= 0)
stdio.WriteLine("{0} row(s) changed at destination {1}", count, tname2);
else
stdio.WriteLine("command(s) completed successfully at destination {1}", count, tname2);
}
else
stdio.WriteLine("table {0} created at destination", tname2);
}
catch (Exception ex)
{
stdio.ErrorFormat(ex.Message);
return;
}
}
} // loop for
return;
});
}
