private static DbCommand CreateCommandImpl(DbCommandBuilder commandBuilder, DbCommand command, string commandText, params object[] parameters)
{
if (commandBuilder == null) throw new ArgumentNullException("commandBuilder");
if (command == null) throw new ArgumentNullException("command");
if (commandText == null) throw new ArgumentNullException("commandText");
if (parameters == null || parameters.Length == 0)
{
command.CommandText = commandText;
return command;
}
object[] paramPlaceholders = new object[parameters.Length];
for (int i = 0; i < paramPlaceholders.Length; i++)
{
DbParameter dbParam = command.CreateParameter();
dbParam.ParameterName = getParameterName(commandBuilder, i);
dbParam.Value = parameters[i] ?? DBNull.Value;
command.Parameters.Add(dbParam);
paramPlaceholders[i] = getParameterPlaceholder(commandBuilder, i);
}
command.CommandText = String.Format(CultureInfo.InvariantCulture, commandText, paramPlaceholders);
return command;
}
/// <summary>
/// 更新数据库
/// </summary>
/// <param name="ds">DataSet</param>
/// <param name="tableName">需要更新的数据库表名</param>
public void UpdateData(DataTable datatable)
{
using (DbConnection connection = provider.CreateConnection())
{
connection.ConnectionString = connectionString;
using (DbCommand cmd = provider.CreateCommand())
{
cmd.Connection = connection;
cmd.CommandText = "select * from " + datatable.TableName;
try
{
DbDataAdapter adapter = provider.CreateDataAdapter();
System.Data.Common.DbCommandBuilder dcb = provider.CreateCommandBuilder();
adapter.SelectCommand = cmd;
dcb.DataAdapter = adapter;
adapter.Update(datatable);
datatable.AcceptChanges();
}
catch (DbException ex)
{
connection.Close();
connection.Dispose();
throw new Exception(ex.Message);
}
}
}
}
protected void Initialize (string databaseName, string querySelectRowString)
{
string providerName = GetProviderNameByDBName(databaseName);
string connectionString = GetConnectionStringByDBName(databaseName);
// Create the DbProviderFactory and DbConnection.
factory = DbProviderFactories.GetFactory(providerName);
connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
// Create the DbCommand.
DbCommand SelectTableCommand = factory.CreateCommand();
SelectTableCommand.CommandText = querySelectRowString;
SelectTableCommand.Connection = connection;
adapter = factory.CreateDataAdapter();
adapter.SelectCommand = SelectTableCommand;
// Create the DbCommandBuilder.
builder = factory.CreateCommandBuilder();
builder.DataAdapter = adapter;
adapter.ContinueUpdateOnError = true;
}
private void OnDbConnectionReset()
{
if (this.IdentifierFormatter != null)
{
this.IdentifierFormatter.Dispose();
}
this.IdentifierFormatter = null;
this.ConnectionDbProviderFactory = null;
}
public ReplicateToSqlIndexUpdateBatcher(
DbProviderFactory providerFactory,
string connectionString,
IndexReplicationDestination destination)
{
_providerFactory = providerFactory;
_commandBuilder = providerFactory.CreateCommandBuilder();
_connectionString = connectionString;
this.destination = destination;
}
public DapperRowContainer(string tableName, SqlConnection connection)
{
_tableName = tableName;
_connection = connection;
columns = new HashSet<string>();
DbProviderFactory factory = DbProviderFactories.GetFactory(_connection);
commandBuilder = factory.CreateCommandBuilder();
CreateTableIfNotExists();
GetExistingColumns()
.ToList()
.ForEach(x => columns.Add(x));
}
public RelationalDatabaseWriterSimulator( DocumentDatabase database, SqlReplicationConfig cfg, SqlReplicationStatistics replicationStatistics)
{
this.database = database;
this.cfg = cfg;
this.replicationStatistics = replicationStatistics;
providerFactory = DbProviderFactories.GetFactory(cfg.FactoryName);
commandBuilder = providerFactory.CreateCommandBuilder();
if (SqlServerFactoryNames.Contains(cfg.FactoryName))
{
IsSqlServerFactoryType = true;
}
}
private System.Data.Common.DbCommandBuilder getDbCommandBuilder(string providerName)
{
System.Data.Common.DbCommandBuilder returnValue = null;
if (!String.IsNullOrEmpty(providerName))
{
System.Data.Common.DbProviderFactory factory = System.Data.Common.DbProviderFactories.GetFactory(providerName);
if (factory != null)
{
returnValue = factory.CreateCommandBuilder();
}
}
return(returnValue);
}
private System.Data.Common.DbCommandBuilder GetIdentifierFormatter()
{
if (this.IdentifierFormatter == null)
{
DbProviderFactory dbProviderFactory = this.GetDbProviderFactory();
this.IdentifierFormatter = dbProviderFactory.CreateCommandBuilder();
this.IdentifierFormatter.DataAdapter = dbProviderFactory.CreateDataAdapter();
this.IdentifierFormatter.DataAdapter.SelectCommand = this.CreateDbCommand();
}
DbCommand command = (this.IdentifierFormatter.DataAdapter == null) ? null : this.IdentifierFormatter.DataAdapter.SelectCommand;
if (((command != null) && (command.Connection != null)) && (typeof(OleDbConnection).IsInstanceOfType(command.Connection) || typeof(OdbcConnection).IsInstanceOfType(command.Connection)))
{
this.OpenConnection(command.Connection);
}
return(this.IdentifierFormatter);
}
public RelationalDatabaseWriter( DocumentDatabase database, SqlReplicationConfig cfg, SqlReplicationStatistics replicationStatistics)
{
this.database = database;
this.cfg = cfg;
this.replicationStatistics = replicationStatistics;
providerFactory = GetDbProviderFactory(cfg);
commandBuilder = providerFactory.CreateCommandBuilder();
connection = providerFactory.CreateConnection();
Debug.Assert(connection != null);
Debug.Assert(commandBuilder != null);
connection.ConnectionString = cfg.ConnectionString;
if (SqlServerFactoryNames.Contains(cfg.FactoryName))
{
IsSqlServerFactoryType = true;
}
try
{
connection.Open();
}
catch (Exception e)
{
database.AddAlert(new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Exception = e.ToString(),
Title = "Sql Replication could not open connection",
Message = "Sql Replication could not open connection to " + connection.ConnectionString,
UniqueKey = "Sql Replication Connection Error: " + connection.ConnectionString
});
throw;
}
tx = connection.BeginTransaction();
stringParserList = GenerateStringParsers();
sqlReplicationMetrics = database.StartupTasks.OfType<SqlReplicationTask>().FirstOrDefault().GetSqlReplicationMetricsManager(cfg);
}
void CreateDataAdapter()
{
_dataAdapter = _query.CreateDataAdapter();
_commandBuilder = _query.CreateCommandBuilder();
_commandBuilder.DataAdapter = _dataAdapter;
// Select * from the entity table
if (SelectCommand == null)
{
_dataAdapter.SelectCommand = _query.CreateTextCommand("SELECT * FROM " + this.EntityTableName);
}
else
{
_dataAdapter.SelectCommand = SelectCommand;
}
// Set the connection string on the select so that CommandBuilder GetxxCommand() calls work
if (_dataAdapter.SelectCommand.Connection == null)
{
_dataAdapter.SelectCommand.Connection = _query.CreateConnection();
}
}
public RelationalDatabaseWriter( DocumentDatabase database, SqlReplicationConfig cfg, SqlReplicationStatistics replicationStatistics)
{
this.database = database;
this.cfg = cfg;
this.replicationStatistics = replicationStatistics;
providerFactory = GetDbProviderFactory(cfg);
commandBuilder = providerFactory.CreateCommandBuilder();
connection = providerFactory.CreateConnection();
Debug.Assert(connection != null);
Debug.Assert(commandBuilder != null);
connection.ConnectionString = cfg.ConnectionString;
try
{
connection.Open();
}
catch (Exception e)
{
database.AddAlert(new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Exception = e.ToString(),
Title = "Sql Replication could not open connection",
Message = "Sql Replication could not open connection to " + connection.ConnectionString,
UniqueKey = "Sql Replication Connection Error: " + connection.ConnectionString
});
throw;
}
tx = connection.BeginTransaction();
}
/// <summary>
/// 以正确的目录大小写给定一个不带引号的标识符，返回该标识符的带引号的正确形式，包括正确转义该标识符中嵌入的任何引号。
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public virtual string QuoteIdentifier(string name)
{
if(cmdBuilder == null) cmdBuilder = this.DbProvider.DbProviderFactory.CreateCommandBuilder();
string s = string.Concat(cmdBuilder.QuotePrefix, name, cmdBuilder.QuoteSuffix);
return s;
}
private void Base_InitializeCommandBuilder(ref System.Data.Common.DbCommandBuilder Value)
{
Value = new MySqlCommandBuilder();
}
internal ParameterNames(DbCommandBuilder dbCommandBuilder, DbSchemaRow[] schemaRows) {
_dbCommandBuilder = dbCommandBuilder;
_baseParameterNames = new string[schemaRows.Length];
_originalParameterNames = new string[schemaRows.Length];
_nullParameterNames = new string[schemaRows.Length];
_isMutatedName = new bool[schemaRows.Length];
_count = schemaRows.Length;
_parameterNameParser = new Regex(_dbCommandBuilder.ParameterNamePattern, RegexOptions.ExplicitCapture | RegexOptions.Singleline);
SetAndValidateNamePrefixes();
_adjustedParameterNameMaxLength = GetAdjustedParameterNameMaxLength();
// Generate the baseparameter names and remove conflicting names
// No names will be generated for any name that is rejected due to invalid prefix, regex violation or
// name conflict after mutation.
// All null values will be replaced with generic parameter names
//
for (int i = 0; i < schemaRows.Length; i++) {
if (null == schemaRows[i]) {
continue;
}
bool isMutatedName = false;
string columnName = schemaRows[i].ColumnName;
// all names that start with original- or isNullPrefix are invalid
if (null != _originalPrefix) {
if (columnName.StartsWith(_originalPrefix, StringComparison.OrdinalIgnoreCase)) {
continue;
}
}
if (null != _isNullPrefix) {
if (columnName.StartsWith(_isNullPrefix, StringComparison.OrdinalIgnoreCase)) {
continue;
}
}
// Mutate name if it contains space(s)
if (columnName.IndexOf(' ') >= 0) {
columnName = columnName.Replace(' ', '_');
isMutatedName = true;
}
// Validate name against regular expression
if (!_parameterNameParser.IsMatch(columnName)) {
continue;
}
// Validate name against adjusted max parametername length
if (columnName.Length > _adjustedParameterNameMaxLength) {
continue;
}
_baseParameterNames[i] = columnName;
_isMutatedName[i] = isMutatedName;
}
EliminateConflictingNames();
// Generate names for original- and isNullparameters
// no names will be generated if the prefix failed parametername validation
for (int i = 0; i < schemaRows.Length; i++) {
if (null != _baseParameterNames[i]) {
if (null != _originalPrefix) {
_originalParameterNames[i] = _originalPrefix + _baseParameterNames[i];
}
if (null != _isNullPrefix) {
// don't bother generating an 'IsNull' name if it's not used
if (schemaRows[i].AllowDBNull) {
_nullParameterNames[i] = _isNullPrefix + _baseParameterNames[i];
}
}
}
}
ApplyProviderSpecificFormat();
GenerateMissingNames(schemaRows);
}
internal ParameterNames(DbCommandBuilder dbCommandBuilder, DbSchemaRow[] schemaRows)
{
this._dbCommandBuilder = dbCommandBuilder;
this._baseParameterNames = new string[schemaRows.Length];
this._originalParameterNames = new string[schemaRows.Length];
this._nullParameterNames = new string[schemaRows.Length];
this._isMutatedName = new bool[schemaRows.Length];
this._count = schemaRows.Length;
this._parameterNameParser = new Regex(this._dbCommandBuilder.ParameterNamePattern, RegexOptions.Singleline | RegexOptions.ExplicitCapture);
this.SetAndValidateNamePrefixes();
this._adjustedParameterNameMaxLength = this.GetAdjustedParameterNameMaxLength();
for (int i = 0; i < schemaRows.Length; i++)
{
if (schemaRows[i] != null)
{
bool flag = false;
string columnName = schemaRows[i].ColumnName;
if (((this._originalPrefix == null) || !columnName.StartsWith(this._originalPrefix, StringComparison.OrdinalIgnoreCase)) && ((this._isNullPrefix == null) || !columnName.StartsWith(this._isNullPrefix, StringComparison.OrdinalIgnoreCase)))
{
if (columnName.IndexOf(' ') >= 0)
{
columnName = columnName.Replace(' ', '_');
flag = true;
}
if (this._parameterNameParser.IsMatch(columnName) && (columnName.Length <= this._adjustedParameterNameMaxLength))
{
this._baseParameterNames[i] = columnName;
this._isMutatedName[i] = flag;
}
}
}
}
this.EliminateConflictingNames();
for (int j = 0; j < schemaRows.Length; j++)
{
if (this._baseParameterNames[j] != null)
{
if (this._originalPrefix != null)
{
this._originalParameterNames[j] = this._originalPrefix + this._baseParameterNames[j];
}
if ((this._isNullPrefix != null) && schemaRows[j].AllowDBNull)
{
this._nullParameterNames[j] = this._isNullPrefix + this._baseParameterNames[j];
}
}
}
this.ApplyProviderSpecificFormat();
this.GenerateMissingNames(schemaRows);
}
private static string CreateColumnList(DbCommandBuilder commandBuilder, IEnumerable<string> columns)
{
var builder = new StringBuilder();
foreach (string column in columns)
{
builder.Append(commandBuilder.QuoteIdentifier(column));
builder.Append(',');
}
builder.Remove(builder.Length - 1, 1);
return builder.ToString();
}
protected void Dispose(bool disposing)
{
dbFactory = null;
dbBuilder = null;
dbParameterMarkerFormat = null;
}
/// <summary>
/// 为数据适配器创建自动命令
/// </summary>
/// <remarks>
/// 自动创建的命令不覆盖自定义的插入、删除、更新命令
/// </remarks>
public DataTableAdapter BuildCommands()
{
if (this._builder == null)
{
if (this.InsertCommand == null || this.UpdateCommand == null || this.DeleteCommand == null)
{
this._builder = this.dbProviderFactory.CreateCommandBuilder();
this._builder.DataAdapter = this.Adapter;
}
}
return this;
}
private void DoSaveDataRow(DbDataAdapter adap, DbCommandBuilder dbCmdBuilder, string tablename, DataRow row)
{
if (row == null
|| row.RowState == DataRowState.Unchanged
|| row.RowState == DataRowState.Detached)
{
return;
}
try
{
dbCmdBuilder.ConflictOption = ConflictOption.OverwriteChanges;
dbCmdBuilder.DataAdapter = adap;
adap.SelectCommand.CommandText = string.Format("SELECT TOP 1 * FROM [{0}]", tablename);
adap.Update(new DataRow[] { row });
}
catch (Exception ex)
{
throw new DbAccessException(adap.SelectCommand.Connection, "Save DataRow error.", ex, adap.SelectCommand, adap.InsertCommand, adap.UpdateCommand, adap.DeleteCommand);
}
}
protected override DbDataAdapter GetDataAdapter(string tableName, out DbCommandBuilder builder)
{
DbDataAdapter adapter = (DbDataAdapter)CreateInstance("SqlCeDataAdapter", string.Format(CultureInfo.InvariantCulture, "SELECT * FROM \"{0}\"", tableName), ConnectionString);
builder = (DbCommandBuilder)CreateInstance("SqlCeCommandBuilder", adapter);
return adapter;
}
/// <summary>
/// Initializes a new adapter with given properties.
/// </summary>
public void InitAdapter(IDbConnection conn, IDbDataAdapter da, DbCommandBuilder builder) {
Adapter = new DataGridAdapter(this, conn, da, builder);
}
public RelationalDatabaseWriter( DocumentDatabase database, SqlReplicationConfig cfg, SqlReplicationStatistics replicationStatistics)
{
this.database = database;
this.cfg = cfg;
this.replicationStatistics = replicationStatistics;
providerFactory = GetDbProviderFactory(cfg);
commandBuilder = providerFactory.CreateCommandBuilder();
connection = providerFactory.CreateConnection();
Debug.Assert(connection != null);
Debug.Assert(commandBuilder != null);
connection.ConnectionString = cfg.ConnectionString;
try
{
connection.Open();
}
catch (Exception e)
{
database.AddAlert(new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Exception = e.ToString(),
Title = "Sql Replication could not open connection",
Message = "Sql Replication could not open connection to " + connection.ConnectionString,
UniqueKey = "Sql Replication Connection Error: " + connection.ConnectionString
});
throw;
}
tx = connection.BeginTransaction();
stringParserList = new List<Func<DbParameter, string, bool>> {
(colParam, value) => {
if( char.IsDigit( value[ 0 ] ) ) {
DateTime dateTime;
if (DateTime.TryParseExact(value, Default.OnlyDateTimeFormat, CultureInfo.InvariantCulture,
DateTimeStyles.RoundtripKind, out dateTime))
{
switch( providerFactory.GetType( ).Name ) {
case "MySqlClientFactory":
colParam.Value = dateTime.ToString( "yyyy-MM-dd HH:mm:ss.ffffff" );
break;
default:
colParam.Value = dateTime;
break;
}
return true;
}
}
return false;
},
(colParam, value) => {
if( char.IsDigit( value[ 0 ] ) ) {
DateTimeOffset dateTimeOffset;
if( DateTimeOffset.TryParseExact( value, Default.DateTimeFormatsToRead, CultureInfo.InvariantCulture,
DateTimeStyles.RoundtripKind, out dateTimeOffset ) ) {
switch( providerFactory.GetType( ).Name ) {
case "MySqlClientFactory":
colParam.Value = dateTimeOffset.ToUniversalTime().ToString( "yyyy-MM-dd HH:mm:ss.ffffff" );
break;
default:
colParam.Value = dateTimeOffset;
break;
}
return true;
}
}
return false;
}
};
}
public DBManager(Databases db)
{
switch (ConType)
{
case SqlEngine.MsSql:
dbFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");
break;
case SqlEngine.MySql:
dbFactory = DbProviderFactories.GetFactory("MySql.Data.MySqlClient");
break;
default:
throw new ArgumentOutOfRangeException("Database type isn't within range.");
}
switch (db)
{
case Databases.Auth:
dbConString = AuthConString;
break;
case Databases.Game:
dbConString = GameConString;
break;
case Databases.User:
dbConString = UserConString;
break;
default:
ConsoleUtils.ShowError("Invalid database type.");
break;
}
targetDb = db;
if (dbFactory != null)
{
using (DbConnection dbCon = dbFactory.CreateConnection())
{
dbCon.ConnectionString = dbConString;
dbBuilder = dbFactory.CreateCommandBuilder();
try
{
dbCon.Open();
using (DataTable tbl = dbCon.GetSchema(DbMetaDataCollectionNames.DataSourceInformation))
{
dbParameterMarkerFormat = tbl.Rows[0][DbMetaDataColumnNames.ParameterMarkerFormat] as string;
}
dbCon.Close();
if (!string.IsNullOrEmpty(dbParameterMarkerFormat)) { dbParameterMarkerFormat = "{0}"; }
ConsoleUtils.ShowDebug("DBManager Instance Initialized.");
}
catch (Exception ex) { ConsoleUtils.ShowError(ex.Message); }
}
}
}
public DbHelper(ConnectionStringSettings connectionString)
{
var file = connectionString.ElementInformation.Source;
if ("web.connections.config".Equals(Path.GetFileName(file), StringComparison.InvariantCultureIgnoreCase))
{
file = Path.Combine(Path.GetDirectoryName(file), "web.config");
}
var xconfig = XDocument.Load(file);
var provider = xconfig.XPathSelectElement("/configuration/system.data/DbProviderFactories/add[@invariant='" + connectionString.ProviderName + "']");
factory = (DbProviderFactory)Activator.CreateInstance(Type.GetType(provider.Attribute("type").Value, true));
builder = factory.CreateCommandBuilder();
connect = factory.CreateConnection();
connect.ConnectionString = connectionString.ConnectionString;
connect.Open();
mysql = connectionString.ProviderName.ToLower().Contains("mysql");
if (mysql)
{
CreateCommand("set @@session.sql_mode = concat(@@session.sql_mode, ',NO_AUTO_VALUE_ON_ZERO')").ExecuteNonQuery();
}
columns = connect.GetSchema("Columns");
whereExceptions["calendar_calendar_item"] = " where calendar_id in (select id from calendar_calendars where tenant = {0}) ";
whereExceptions["calendar_calendar_user"] = "******";
whereExceptions["calendar_event_item"] = " inner join calendar_events on calendar_event_item.event_id = calendar_events.id where calendar_events.tenant = {0} ";
whereExceptions["calendar_event_user"] = "******";
whereExceptions["crm_entity_contact"] = " inner join crm_contact on crm_entity_contact.contact_id = crm_contact.id where crm_contact.tenant_id = {0} ";
whereExceptions["crm_entity_tag"] = " inner join crm_tag on crm_entity_tag.tag_id = crm_tag.id where crm_tag.tenant_id = {0} ";
whereExceptions["files_folder_tree"] = " inner join files_folder on folder_id = id where tenant_id = {0} ";
whereExceptions["forum_answer_variant"] = " where answer_id in (select id from forum_answer where tenantid = {0})";
whereExceptions["forum_topic_tag"] = " where topic_id in (select id from forum_topic where tenantid = {0})";
whereExceptions["forum_variant"] = " where question_id in (select id from forum_question where tenantid = {0})";
whereExceptions["projects_project_participant"] = " inner join projects_projects on projects_project_participant.project_id = projects_projects.id where projects_projects.tenant_id = {0} ";
whereExceptions["projects_following_project_participant"] = " inner join projects_projects on projects_following_project_participant.project_id = projects_projects.id where projects_projects.tenant_id = {0} ";
whereExceptions["projects_project_tag"] = " inner join projects_projects on projects_project_tag.project_id = projects_projects.id where projects_projects.tenant_id = {0} ";
whereExceptions["tenants_tenants"] = " where id = {0}";
whereExceptions["core_acl"] = " where tenant = {0} or tenant = -1";
whereExceptions["core_subscription"] = " where tenant = {0} or tenant = -1";
whereExceptions["core_subscriptionmethod"] = " where tenant = {0} or tenant = -1";
}
void EnsureBuilder()
{
if (_Builder != null)
return;
if (Factory == null)
throw new RuntimeException("Cannot create a command builder because this.Factory is null.");
if (Adapter == null)
Adapter = Factory.CreateDataAdapter();
_Builder = Factory.CreateCommandBuilder();
_Builder.DataAdapter = Adapter;
}
protected virtual string GenerateQuery (DbCommandBuilder cmdBuilder)
{
throw new NotImplementedException ();
}
protected abstract DbDataAdapter GetDataAdapter(string tableName, out DbCommandBuilder builder);
/// <summary>
/// Initializes a new instance of the <see cref="DbQueryTranslator"/> class.
/// </summary>
/// <param name="providerFactory"></param>
protected DbQueryTranslator(DbProviderFactory providerFactory)
{
_providerFactory = providerFactory;
_commandBuilder = _providerFactory.CreateCommandBuilder();
Parameters = new List<IDataParameter>();
}
protected override DbDataAdapter GetDataAdapter(string tableName, string schemaName, out DbCommandBuilder builder)
{
var adapter = new SQLiteDataAdapter(string.Format(CultureInfo.InvariantCulture, "SELECT * FROM \"{0}\"", tableName), _connection);
builder = new SQLiteCommandBuilder(adapter);
return adapter;
}
public static Message Create(XmlReader xmlReader, DbConnection connection, string operationType, DbCommandBuilder commandBuilder, string action)
{
int count = 0;
string baseSelect = commandBuilder.DataAdapter.SelectCommand.CommandText;
var baseCommand = commandBuilder.GetInsertCommand();
var commandCache = new Dictionary<string, DbCommand>();
while (xmlReader.ReadToFollowing("Row", AdoNetAdapter.MESSAGENAMESPACE))
{
var values = GetParameterValues(xmlReader.ReadSubtree(), baseCommand.Parameters);
string columns = CreateColumnList(commandBuilder, values.Keys);
DbCommand command = null;
if (commandCache.ContainsKey(columns))
command = commandCache[columns];
else
{
commandBuilder.DataAdapter.SelectCommand.CommandText = baseSelect.Replace("*", columns);
commandBuilder.RefreshSchema();
command = commandBuilder.GetInsertCommand();
commandCache[columns] = command;
}
DbHelpers.SetTargetParameters(values, command.Parameters);
count += command.ExecuteNonQuery();
}
return DbHelpers.CreateMessage(operationType, count, action);
}
protected virtual string GenerateQueryForHierarchy (DbCommandBuilder builder, DataTable[] tableList)
{
throw new NotImplementedException ();
}
protected override DbDataAdapter GetDataAdapter(string tableName, out DbCommandBuilder builder)
{
var adapter = new SqlDataAdapter(string.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}]", tableName), ConnectionString);
builder = new SqlCommandBuilder(adapter);
return adapter;
}
private static string GetParameterName(DbProviderFactory providerFactory, DbCommandBuilder commandBuilder, string paramName)
{
switch (providerFactory.GetType().Name)
{
case "SqlClientFactory":
case "MySqlClientFactory":
return "@" + paramName;
case "OracleClientFactory":
case "NpgsqlFactory":
return ":" + paramName;
default:
// If we don't know, try to get it from the CommandBuilder.
return getParameterNameFromBuilder(commandBuilder, paramName);
}
}
public static Message Update(XmlReader xmlReader, DbConnection connection, string operationType, DbCommandBuilder commandBuilder, string action)
{
string baseSelect = commandBuilder.DataAdapter.SelectCommand.CommandText;
var baseCommand = commandBuilder.GetUpdateCommand();
var commandCache = new Dictionary<string, DbCommand>();
int count = 0;
while (xmlReader.ReadToFollowing("Pair", AdoNetAdapter.MESSAGENAMESPACE))
{
xmlReader.ReadToFollowing("Before", AdoNetAdapter.MESSAGENAMESPACE);
var beforeValues = GetParameterValues(xmlReader.ReadSubtree(), baseCommand.Parameters);
string beforeColumns = CreateColumnList(commandBuilder, beforeValues.Keys);
xmlReader.ReadToFollowing("After", AdoNetAdapter.MESSAGENAMESPACE);
var afterValues = GetParameterValues(xmlReader.ReadSubtree(), baseCommand.Parameters);
string afterColumns = CreateColumnList(commandBuilder, afterValues.Keys);
string columns = string.Format("B:{0}|A:{1}", beforeColumns, afterColumns);
DbCommand command = null;
if (commandCache.ContainsKey(columns))
command = commandCache[columns];
else
{
commandBuilder.DataAdapter.SelectCommand.CommandText = baseSelect.Replace("*", columns);
commandBuilder.RefreshSchema();
command = commandBuilder.GetUpdateCommand();
commandCache[columns] = command;
}
DbHelpers.SetSourceParameters(beforeValues, command.Parameters);
DbHelpers.SetTargetParameters(afterValues, command.Parameters);
count += command.ExecuteNonQuery();
}
return DbHelpers.CreateMessage(operationType, count, action);
}
protected override DbDataAdapter GetDataAdapter(string tableName, string schemaName, out DbCommandBuilder builder)
{
var adapter = new OdbcDataAdapter(string.Format(CultureInfo.InvariantCulture, "SELECT * FROM \"{0}\" ORDER BY 1", tableName), OdbcConnectionString);
builder = new OdbcCommandBuilder(adapter);
return adapter;
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
// By default (false) it uses SQLite database (Database.db). You can switch to MS Access database (Database.mdb) by setting UseMDB = true
// The SQLite loads dynamically its DLL from TreeGrid distribution, it chooses 32bit or 64bit assembly
// The MDB can be used only on 32bit IIS mode !!! The ASP.NET service program must have write access to the Database.mdb file !!!
bool UseMDB = false;
// --- Response initialization ---
Response.ContentType = "text/xml";
Response.Charset = "utf-8";
Response.AppendHeader("Cache-Control", "max-age=1, must-revalidate");
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
// --- Database initialization ---
string Path = System.IO.Path.GetDirectoryName(Context.Request.PhysicalPath);
System.Data.IDbConnection Conn = null;
System.Data.Common.DbDataAdapter Sql = null;
System.Data.Common.DbCommandBuilder Bld = null;
string SqlStr = "SELECT * FROM TableData";
System.Reflection.Assembly SQLite = null; // Required only for SQLite database
if (UseMDB) // For MS Acess database
{
Conn = new System.Data.OleDb.OleDbConnection("Data Source=\"" + Path + "\\..\\Database.mdb\";Mode=Share Deny None;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Engine Type=5;Provider=\"Microsoft.Jet.OLEDB.4.0\";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1");
Sql = new System.Data.OleDb.OleDbDataAdapter(SqlStr, (System.Data.OleDb.OleDbConnection)Conn);
}
else // For SQLite database
{
SQLite = System.Reflection.Assembly.LoadFrom(Path + "\\..\\..\\..\\Server\\SQLite" + (IntPtr.Size == 4 ? "32" : "64") + "\\System.Data.SQLite.DLL");
Conn = (System.Data.IDbConnection)Activator.CreateInstance(SQLite.GetType("System.Data.SQLite.SQLiteConnection"), "Data Source=" + Path + "\\..\\Database.db");
Sql = (System.Data.Common.DbDataAdapter)Activator.CreateInstance(SQLite.GetType("System.Data.SQLite.SQLiteDataAdapter"), SqlStr, Conn); //*/
}
System.Data.DataTable D = new System.Data.DataTable();
Sql.Fill(D);
// --- Save data to database ---
System.Xml.XmlDocument X = new System.Xml.XmlDocument();
string XML = Request["TGData"];
if (XML != "" && XML != null)
{
X.LoadXml(HttpUtility.HtmlDecode(XML));
System.Xml.XmlNodeList Ch = X.GetElementsByTagName("Changes");
if (Ch.Count > 0)
{
foreach (System.Xml.XmlElement I in Ch[0])
{
string id = I.GetAttribute("id");
System.Data.DataRow R;
if (I.GetAttribute("Added") == "1")
{
R = D.NewRow();
R["ID"] = id;
D.Rows.Add(R);
}
else
{
R = D.Select("[ID]='" + id + "'")[0];
}
if (I.GetAttribute("Deleted") == "1")
{
R.Delete();
}
else if (I.GetAttribute("Added") == "1" || I.GetAttribute("Changed") == "1")
{
if (I.HasAttribute("Project"))
{
R["Project"] = I.GetAttribute("Project");
}
if (I.HasAttribute("Resource"))
{
R["Resource"] = I.GetAttribute("Resource");
}
if (I.HasAttribute("Week"))
{
R["Week"] = System.Double.Parse(I.GetAttribute("Week"));
}
if (I.HasAttribute("Hours"))
{
R["Hours"] = System.Double.Parse(I.GetAttribute("Hours"));
}
}
}
}
if (UseMDB)
{
new System.Data.OleDb.OleDbCommandBuilder((System.Data.OleDb.OleDbDataAdapter)Sql); // For MS Acess database
}
else
{
Activator.CreateInstance(SQLite.GetType("System.Data.SQLite.SQLiteCommandBuilder"), Sql); // For SQLite database
}
Sql.Update(D); // Updates changed to database
D.AcceptChanges();
X.RemoveAll();
Response.Write("<Grid><IO Result='0'/></Grid>");
}
// --- Load data from database ---
else
{
System.Xml.XmlElement G, BB, B, I;
G = X.CreateElement("Grid"); X.AppendChild(G);
BB = X.CreateElement("Body"); G.AppendChild(BB);
B = X.CreateElement("B"); BB.AppendChild(B);
foreach (System.Data.DataRow R in D.Rows)
{
I = X.CreateElement("I");
B.AppendChild(I);
I.SetAttribute("id", R[0].ToString());
I.SetAttribute("Project", R[1].ToString());
I.SetAttribute("Resource", R[2].ToString());
I.SetAttribute("Week", R[3].ToString());
I.SetAttribute("Hours", R[4].ToString());
}
Response.Write(X.InnerXml);
}
} catch (Exception E)
{
Response.Write("<Grid><IO Result=\"-1\" Message=\"Error in TreeGrid example:

" + E.Message.Replace("&", "&").Replace("<", "<").Replace("\"", """) + "\"/></Grid>");
}
}
