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(); }
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); }
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 static int ExecuteNonQuery(this ConnectionProvider provider, string script) { SqlCmd cmd = new SqlCmd(provider, script); return(cmd.ExecuteNonQuery()); }
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 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 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 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 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); }
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; }