private void StartUpdate(DataTable UserData)
{
try
{
SetCommand(true);
//dgvData.AllowUserToAddRows = false;
var userData = (DataTable)UserData;
userData = userData.GetChanges();
if (userData != null)
{
using (SqlConnection connection = SQLHelper.CreateNewConnection(Monitor.Instance.CurrentServerInfo))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
string schemaName;
var tableName = QueryEngine.ParseObjectName(userData.TableName, out schemaName);
tableName = QueryEngine.GetObjectName(schemaName, tableName);
using (SqlCommand command = new SqlCommand("SELECT TOP 1 * FROM " + tableName, connection))
{
command.Transaction = transaction;
SqlDataAdapter adapter = new SqlDataAdapter(command);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.InsertCommand = builder.GetInsertCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
//for (int i = adapter.DeleteCommand.Parameters.Count - 1; i >= 0; i--)
//{
// if (adapter.DeleteCommand.Parameters[i].SourceColumn != primaryKey)
// adapter.DeleteCommand.Parameters.RemoveAt(i);
//}
adapter.UpdateCommand = builder.GetUpdateCommand();
//for (int i = adapter.UpdateCommand.Parameters.Count - 1; i >= 0; i--)
//{
// if (adapter.UpdateCommand.Parameters[i].SourceColumn != primaryKey)
// adapter.UpdateCommand.Parameters.RemoveAt(i);
//}
adapter.Update(userData);
UserData.AcceptChanges();
}
transaction.Commit();
}
connection.Close();
}
}
}
catch (Exception ex)
{
ShowMessage(ex.Message);
}
finally
{
SetCommand(false);
//dgvData.AllowUserToAddRows = true;
}
}
internal static object ExecuteScalar(string SQL, ServerInfo Server)
{
object result;
using (SqlConnection connection = SQLHelper.CreateNewConnection(Server))
{
var command = new SqlCommand(SQL, connection);
connection.Open();
result = command.ExecuteScalar();
connection.Close();
}
return(result);
}
internal static string ExecuteNonQuery(string SQL, ServerInfo Server)
{
using (SqlConnection connection = SQLHelper.CreateNewConnection(Server))
{
var result = new StringBuilder();
connection.InfoMessage += (s, e) => { result.AppendLine(e.Message); };
var command = new SqlCommand(SQL, connection);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
return(result.ToString());
}
}
internal static DataSet QuerySet(string SQL, ServerInfo Info, out string Message)
{
using (SqlConnection connection = SQLHelper.CreateNewConnection(Info))
{
var result = new StringBuilder();
connection.InfoMessage += (s, e) => { result.AppendLine(e.Message); };
var command = new SqlCommand(SQL, connection);
command.StatementCompleted += (s, e) => { result.AppendLine(string.Format("{0} row(s) affected.", e.RecordCount)); };
var adapter = new SqlDataAdapter(command);
var data = new DataSet();
//adapter.FillSchema(data, SchemaType.Mapped);
adapter.Fill(data);
connection.Close();
Message = result.ToString();
return(data);
}
}