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 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; }); }