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