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