private void CreateTable(string applicationName, string tableName)
{
try {
string commandText = string.Empty;
if (tableName == RxlTableName.DeliveryValidationHeader)
{
commandText = Resources.CreateDeliveryValidationHeaderTableCommandText;
}
if (tableName == RxlTableName.DeliveryValidationDetail)
{
commandText = Resources.CreateDeliveryValidationDetailTableCommandText;
}
if (tableName == RxlTableName.GeneralDescription)
{
commandText = Resources.CreateGeneralDescriptionTableCommandText;
}
_SingleLock[tableName].WaitOne();
using (SqlConnection connection = new SqlConnection(RxlConfiguration.GetCurrent().GetConnectionString(applicationName))) {
using (SqlCommand command = new SqlCommand(commandText, connection)) {
command.CommandTimeout = 0;
connection.Open();
command.ExecuteNonQuery();
}
}
} catch {
throw;
} finally {
_SingleLock[tableName].Set();
}
}
public void Update <TDataTable>(string applicationName, UpdateErrorHandler errorHandler)
where TDataTable : DataTable, new()
{
try {
string tableName = typeof(TDataTable).Name.Replace("DataTable", string.Empty);
_SingleLock[tableName].WaitOne();
try {
TDataTable sourceTable = _RxlDataSet.Tables[tableName] as TDataTable;
if (sourceTable == null)
{
return;
}
string selectCommandText = string.Format("SELECT * FROM {0}", tableName);
if (selectCommandText.Length == 0)
{
throw new ApplicationException(string.Format("Command text for table '{0}' has not yot been set in the design time.", tableName));
}
try {
CreateTable(applicationName, tableName);
using (SqlConnection connection = new SqlConnection(RxlConfiguration.GetCurrent().GetConnectionString(applicationName))) {
using (SqlCommand selectCommand = new SqlCommand(selectCommandText, connection)) {
selectCommand.CommandTimeout = 0;
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(selectCommand)) {
using (SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter)) {
dataAdapter.InsertCommand = commandBuilder.GetInsertCommand(true).Clone();
dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand(true).Clone();
dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand(true).Clone();
}
connection.Open();
UpdateInternal(dataAdapter, DataViewRowState.Deleted, sourceTable, errorHandler);
UpdateInternal(dataAdapter, DataViewRowState.Added, sourceTable, errorHandler);
UpdateInternal(dataAdapter, DataViewRowState.ModifiedCurrent, sourceTable, errorHandler);
}
}
}
sourceTable.AcceptChanges();
} catch {
sourceTable.RejectChanges();
throw;
}
} catch {
throw;
} finally {
_SingleLock[tableName].Set();
}
} catch {
throw;
}
}
public int FillTable <TDataTable>(string applicationName, bool refresh, int commandTextIndex, params IConvertible[] paramValues)
where TDataTable : DataTable, new()
{
try {
string tableName = typeof(TDataTable).Name.Replace("DataTable", string.Empty);
_SingleLock[tableName].WaitOne();
try {
TDataTable realTable = _RxlDataSet.Tables[tableName] as TDataTable;
if (realTable == null)
{
return(0);
}
if (!refresh && realTable.Rows.Count > 0)
{
return(realTable.Rows.Count);
}
using (TDataTable testTable = new TDataTable()) {
using (DataTable loaderTable = new DataTable()) {
using (SqlConnection connection = new SqlConnection(RxlConfiguration.GetCurrent().GetConnectionString(applicationName))) {
string commandText = GetCommandText(tableName, commandTextIndex);
if (commandText.Length == 0)
{
throw new ApplicationException(string.Format("Command text for table '{0}' has not yot been set in the design time.", tableName));
}
using (SqlCommand command = new SqlCommand(commandText, connection)) {
if (paramValues != null && paramValues.Length > 0)
{
var paramNames = Regex.Matches(commandText, @"(?<paramName>(=|<>|!=|>|>=|<|<=|LIKE)\s*@\w+)", RegexOptions.IgnoreCase)
.Cast <Match>().Select(t => new { ParamName = Regex.Replace(t.Value, @"(?<operator>(=|<>|!=|>|>=|<|<=|LIKE))", string.Empty, RegexOptions.IgnoreCase).Trim() }).Distinct()
.Select((t, i) => new { Index = i, ParamName = t.ParamName });
if ((paramNames == null) || (paramNames.Count() == 0) || (paramNames.Count() != paramValues.Length))
{
throw new ArgumentException("paramValues count did not match with paramNames count in command text.");
}
var q = from a in paramNames
join b in paramValues.Select((t, i) => new { Index = i, ParamValue = t })
on a.Index equals b.Index
select new { ParamName = a.ParamName, ParamValue = b.ParamValue };
foreach (var i in q)
{
command.Parameters.AddWithValue(i.ParamName, i.ParamValue);
}
}
command.CommandTimeout = 0;
connection.Open();
using (SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.KeyInfo)) {
loaderTable.Load(dataReader, LoadOption.OverwriteChanges);
}
}
}
testTable.Merge(loaderTable, false, MissingSchemaAction.Ignore);
}
realTable.Clear();
realTable.Merge(testTable, false, MissingSchemaAction.Ignore);
}
return(realTable.Rows.Count);
} catch {
throw;
} finally {
_SingleLock[tableName].Set();
}
} catch {
throw;
}
}
