protected DbConnection CreateConnection(string ConnectionString)
{
if (IsSingleton)
{
if (dbConn == null)
{
dbConn = dbFactory.CreateConnection();
}
}
else
{
if (dbConn != null)
{
dbConn.Dispose();
}
dbConn = dbFactory.CreateConnection();
}
if (dbConn.ConnectionString != ConnectionString)
{
if (dbConn.State != ConnectionState.Closed)
{
dbConn.Close();
}
dbConn.ConnectionString = (this.ConnectionString = ConnectionString);
}
return(dbConn);
}
private static object CreateObject(DbProviderFactory factory, ProviderSupportedClasses kindOfObject, string providerName)
{
switch (kindOfObject)
{
case ProviderSupportedClasses.DbConnection:
return factory.CreateConnection();
case ProviderSupportedClasses.DbDataAdapter:
return factory.CreateDataAdapter();
case ProviderSupportedClasses.DbParameter:
return factory.CreateParameter();
case ProviderSupportedClasses.DbCommand:
return factory.CreateCommand();
case ProviderSupportedClasses.DbCommandBuilder:
return factory.CreateCommandBuilder();
case ProviderSupportedClasses.DbDataSourceEnumerator:
return factory.CreateDataSourceEnumerator();
case ProviderSupportedClasses.CodeAccessPermission:
return factory.CreatePermission(PermissionState.None);
}
throw new InternalException(string.Format(CultureInfo.CurrentCulture, "Cannot create object of provider class identified by enum {0} for provider {1}", new object[] { Enum.GetName(typeof(ProviderSupportedClasses), kindOfObject), providerName }));
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="dbConnectString">链接字符串</param>
/// <param name="dbProviderNamespace">数据提供者的命名空间,例如:"System.Data.SqlClient"</param>
public DBHelper(string dbConnectString, string dbProviderNamespace)
{
dbProviderFactory = DbProviderFactories.GetFactory(dbProviderNamespace);
dbConnection = dbProviderFactory.CreateConnection();
dbConnection.ConnectionString = dbConnectString;
dbCommand = dbConnection.CreateCommand();
}
//internal static IDbConnection CreateConnection(this DbProviderFactory factory,
// Action<IDbConnection> setup)
//{
// IDbConnection connection = factory.CreateConnection();
// setup(connection);
// return connection;
//}
//internal static DbParameter CreateParameter(this DbProviderFactory factory,
// Action<DbParameter> setup)
//{
// DbParameter parameter = factory.CreateParameter();
// setup(parameter);
// return parameter;
//}
//internal static DbCommand CreateCommand(this DbProviderFactory factory,
// Action<DbCommand> setup)
//{
// DbCommand command = factory.CreateCommand();
// setup(command);
// return command;
//}
internal static IDbConnection CreateConnection(DbProviderFactory factory,
Action<IDbConnection> setup)
{
IDbConnection connection = factory.CreateConnection();
setup(connection);
return connection;
}
/// <summary>
/// 构造函数--重载方法二
/// </summary>
/// <param name="connectionstring">数据库链接</param>
/// <param name="databasetype">数据库的类型</param>
public DBHelper(string connectionstring, string databasetype)
{
MyFactory = DbProviderFactories.GetFactory(databasetype);
MyConnection = MyFactory.CreateConnection();
MyConnection.ConnectionString = (databasetype.ToString() == "System.Data.OleDb") ? ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + GetDataPath(connectionstring) + ";") : (connectionstring);
MyCommand = MyConnection.CreateCommand();
}
private static async Task<World> LoadRow(string connectionString, DbProviderFactory dbProviderFactory)
{
using (var db = dbProviderFactory.CreateConnection())
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "SELECT [Id], [RandomNumber] FROM [World] WHERE [Id] = @Id";
var id = cmd.CreateParameter();
id.ParameterName = "@Id";
id.DbType = DbType.Int32;
id.Value = _random.Next(1, 10001);
cmd.Parameters.Add(id);
db.ConnectionString = connectionString;
await db.OpenAsync();
using (var rdr = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection))
{
await rdr.ReadAsync();
return new World
{
Id = rdr.GetInt32(0),
RandomNumber = rdr.GetInt32(1)
};
}
}
}
/// <summary>Instancia a DbFactory "setando a string de conexao"
/// </summary>
public DAOFactory(string connectionStringValue, string provider)
{
AppSettingsReader apps = new AppSettingsReader();
objProvider = DbProviderFactories.GetFactory(provider);
connectionString = connectionStringValue;
objConn = objProvider.CreateConnection();
}
private static async Task<IEnumerable<Fortune>> LoadRows(string connectionString, DbProviderFactory dbProviderFactory)
{
var result = new List<Fortune>();
using (var db = dbProviderFactory.CreateConnection())
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "SELECT [Id], [Message] FROM [Fortune]";
db.ConnectionString = connectionString;
await db.OpenAsync();
using (var rdr = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection))
{
while (await rdr.ReadAsync())
{
result.Add(new Fortune
{
Id = rdr.GetInt32(0),
Message = rdr.GetString(1)
});
}
}
}
result.Add(new Fortune { Message = "Additional fortune added at request time." });
result.Sort();
return result;
}
/// <summary>
/// Returns a connection for the current transaction. This will be an existing <see cref="DbConnection"/>
/// instance or a new one if there is a <see cref="TransactionScope"/> active. Otherwise this method
/// returns null.
/// </summary>
/// <param name="db"></param>
/// <returns>Either a <see cref="DbConnection"/> instance or null.</returns>
public static DbConnectionWrapper GetConnection(string connectionString, DbProviderFactory factory)
{
var currentTransaction = System.Transactions.Transaction.Current;
if (currentTransaction == null)
{
return null;
}
var connectionList = _transactionConnections.GetOrAdd(currentTransaction, (t) =>
{
t.TransactionCompleted += OnTransactionCompleted; return new ConcurrentDictionary<string, DbConnectionWrapper>();
});
var connection = connectionList.GetOrAdd(connectionString, (s) =>
{
var dbConnection = factory.CreateConnection();
dbConnection.ConnectionString = connectionString;
dbConnection.Open();
return new DbConnectionWrapper(dbConnection, factory);
});
connection.AddRef();
return connection;
}
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;
}
/// <summary>
/// 构造函数
/// </summary>
public DBHelper(string providerName, string connectionString, string sqlType)
{
this._sqlType = sqlType;
if (string.Compare(sqlType, "Mysql", true) == 0)
{
_connection = new MySqlConnection();
_command = new MySqlCommand();
this._paraPrefix = "?";
}
else
{
_provider = GetDbProvider(providerName);
_connection = _provider.CreateConnection();
_command = _provider.CreateCommand();
if (string.Compare(sqlType, "Oracle", true) == 0)
{
this._paraPrefix = ":";
}
else if (string.Compare(sqlType, "SQLServer", true) == 0)
{
this._paraPrefix = "@";
}
}
_connection.ConnectionString = connectionString;
_command.Connection = _connection;
}
public IDbConnection GetConnection(string connectionString, string provider)
{
System.Data.Common.DbProviderFactory factory = System.Data.Common.DbProviderFactories.GetFactory(provider);
System.Data.Common.DbConnection conn = factory.CreateConnection();
conn.ConnectionString = connectionString;
return(conn);
}
/*
* Method to get a database connection from the server or local embedded database.
* built on generic ADO.Net
*/
public static DbConnection GetUasAdoConnection()
{
DbConnection dbConn = null;
try
{
System.Configuration.ConnectionStringSettings connectionString = new System.Configuration.ConnectionStringSettings();
if (SessionHelper.IsVentureMode)
{
connectionString = DataContext.getUASEntitiesAdoVenture();
}
else
{
connectionString = System.Configuration.ConfigurationManager.ConnectionStrings[DataContext.GetUasConnectionStringName()];
}
// Loads the factory
System.Data.Common.DbProviderFactory factory = System.Data.Common.DbProviderFactories.GetFactory(connectionString.ProviderName);
dbConn = factory.CreateConnection();
dbConn.ConnectionString = connectionString.ConnectionString;
}
catch (Exception ex)
{
Debug.WriteLine("* * * GetUasAdoConnection: " + ex.Message);
// GetProviderFactoryClasses();
}
return(dbConn);
}
private int LastInsertedId()
{
string lib = GetProviderFactoryLib(providerName);
System.Data.Common.DbProviderFactory provider = RegisterProvider(providerName);
DbConnection con = provider.CreateConnection();
con.ConnectionString = BuildConnectionString(providerName, this.dbName);
DbCommand com = provider.CreateCommand();
com.CommandTimeout = 30;
com.Connection = con;
com.CommandText = LastInsertIdCommand(providerName);
com.CommandType = CommandType.Text;
int lastId = 0;
try
{
con.Open();
com.Prepare();
lastId = Convert.ToInt32(com.ExecuteScalar());
}
catch (Exception ex)
{
throw ex;
}
con.Close();
return(lastId);
}
public TableAdapterSchemaInfo (DbProviderFactory provider) {
this.Provider = provider;
this.Adapter = provider.CreateDataAdapter ();
this.Connection = provider.CreateConnection ();
this.Commands = new ArrayList ();
this.ShortCommands = false;
}
/// <summary>
/// Ham nay de mo ket noi den database
/// </summary>
/// <param name="ServerName"></param>
/// <param name="DatabaseName"></param>
/// <param name="UserId"></param>
/// <param name="Password"></param>
/// <returns></returns>
public static bool DB_OpenConnection(string ServerName, string DatabaseName, string UserId, string Password)
{
DbProviderFactory = System.Data.SqlClient.SqlClientFactory.Instance;
DbConnectionStringBuilder DbConnectionStringBuilder = DbProviderFactory.CreateConnectionStringBuilder();
DbConnectionStringBuilder.Add("Data Source", ServerName);
DbConnectionStringBuilder.Add("User ID", UserId);
DbConnectionStringBuilder.Add("Password", Password);
DbConnectionStringBuilder.Add("Initial Catalog", DatabaseName);
bool IsConnected;
try
{
if (DbConnection == null)
{
DbConnection = DbProviderFactory.CreateConnection();
}
if (DbConnection.State != ConnectionState.Open)
{
DbConnection.ConnectionString = DbConnectionStringBuilder.ConnectionString;
DbConnection.Open();
}
IsConnected = true;
}
catch
{
IsConnected = false;
}
return(IsConnected);
}
public DbBase(ConnObject co)
{
this.connObject = co;
this.conn = co.Master.Conn;
this.providerName = co.Master.ProviderName;
dalType = co.Master.ConnDalType;
_fac = GetFactory(providerName);
_con = _fac.CreateConnection();
_con.ConnectionString = DalCreate.FormatConn(dalType, conn);
_com = _con.CreateCommand();
if (_com != null)//Txt| Xml 时返回Null
{
_com.Connection = _con;
_com.CommandTimeout = AppConfig.DB.CommandTimeout;
}
if (IsAllowRecordSql)//开启秒表计算
{
_watch = new Stopwatch();
}
//if (AppConfig.DB.LockOnDbExe && dalType == DalType.Access)
//{
// string dbName = DataBase;
// if (!_dbOperator.ContainsKey(dbName))
// {
// try
// {
// _dbOperator.Add(dbName, false);
// }
// catch
// {
// }
// }
//}
//_com.CommandTimeout = 1;
}
public int CreateDatabase(String queryString)
{
string lib = GetProviderFactoryLib(providerName);
System.Data.Common.DbProviderFactory provider = RegisterProvider(providerName);
DbConnection con = provider.CreateConnection();
con.ConnectionString = BuildConnectionString(providerName, "");
int rowsAffected = 0;
try
{
con.Open();
DbCommand com = provider.CreateCommand();
com.CommandTimeout = 30;
com.Connection = con;
com.CommandText = queryString;
com.CommandType = CommandType.Text;
object result = com.ExecuteNonQuery();
if (result != null)
{
rowsAffected = Convert.ToInt32(result);
}
}
catch (Exception ex)
{
throw ex;
}
con.Close();
return(rowsAffected);
}
public Connection(string providerName, string ConnectionString)
{
this._fabrique = DbProviderFactories.GetFactory(providerName);
this._connection = _fabrique.CreateConnection();
this._connection.ConnectionString = ConnectionString;
this._connection.Open();
this._connection.Close();
}
//public static DbConnection CreateConnection(this DbProviderFactory dbFactory, string connectionString)
/// <summary>
/// Create a DbConnection with the specified connection string set.
/// </summary>
public static DbConnection CreateConnection(DbProviderFactory dbFactory, string connectionString)
{
if (dbFactory == null) throw new ArgumentNullException("dbFactory");
var connection = dbFactory.CreateConnection();
connection.ConnectionString = connectionString;
return connection;
}
public SQLEditor()
{
InitializeComponent();
_providerFactory = DbProviderFactories.GetFactory("NuoDb.Data.Client");
if (_providerFactory == null)
throw new Exception("NuoDB Data Provider is not correctly registered");
_connection = _providerFactory.CreateConnection();
}
// Constructor
public DatabaseHelperExtension(string ConnectionString)
{
SqlBulkCopy = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.KeepIdentity & SqlBulkCopyOptions.KeepNulls & SqlBulkCopyOptions.TableLock);
objFactory = SqlClientFactory.Instance;
objConnection = objFactory.CreateConnection();
//objConnection.ConnectionString = ConnectionStringHelper.ConStr;
objConnection.ConnectionString = ConnectionString;
}
public Database(string connectionString, string providerName)
{
_factory = DbProviderFactories.GetFactory(providerName);
_com = _factory.CreateCommand();
_con = _factory.CreateConnection();
_con.ConnectionString = connectionString;
_com.Connection = _con;
}
static DbConnection CreateConnection (DbProviderFactory factory, string connStr)
{
DbConnection connection = factory.CreateConnection ();
connection.ConnectionString = connStr;
connection.Open ();
return connection;
}
public IDbConnection CreateConnection()
{
//return connection object of the correct type
IDbConnection conn = _frameworkProviderFactory.CreateConnection();
conn.ConnectionString = _dataOrder.ConnectionString;
return(conn);
}
public DBHelper()
{
strConnectionString = ConfigurationManager.ConnectionStrings["Connectionstringname"].ConnectionString;
objFactory = SqlClientFactory.Instance;
objConnection = objFactory.CreateConnection();
objCommand = objFactory.CreateCommand();
objConnection.ConnectionString = strConnectionString;
objCommand.Connection = objConnection;
}
public DbBase(string conn, string providerName)
{
dalType = DalAction.GetDalType(providerName);
_fac = DbProviderFactories.GetFactory(providerName);
_con = _fac.CreateConnection();
_con.ConnectionString = FormatConn(conn);
_com = _con.CreateCommand();
_com.Connection = _con;
}
private static IDbConnection Conn(DbProviderFactory factory)
{
var connection = factory.CreateConnection();
if (connection != null)
connection.ConnectionString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
//"server=mysql302.1gb.ua;User Id=gbua_kulumu2013;password=3dd4af67sgh;Persist Security Info=True;database=gbua_kulumu2013";
return connection;
}
public DataTable RetrieveTableStructure(string tableName)
{
DataTable tableStruct = new DataTable();
string lib = GetProviderFactoryLib(providerName);
System.Data.Common.DbProviderFactory provider = RegisterProvider(providerName);
DbConnection con = provider.CreateConnection();
con.ConnectionString = BuildConnectionString(providerName, "");
DbCommand com = provider.CreateCommand();
com.CommandTimeout = 30;
com.Connection = con;
string queryString = "";
if (providerName == ProviderFactoryType.MySql.ToString())
{
queryString = string.Format(@"
SELECT
lower( Column_name) as FieldName,
Column_name as FieldCaption,
Data_type As ValueType,
0 As ColumnIsHide,
0 As FieldIsHide,
0 As IsFake,
0 As IsReadOnly,
0 As IsRequiered,
'' As FieldCategory,
'' As UserHelp,
Extra As Extra,
if( isnull( Character_Maximum_Length),Numeric_Precision, Character_Maximum_Length) As Length,
if( isnull(Numeric_Scale), 000, Numeric_Scale) As DecimalPosition
From information_schema.COLUMNS
Where Table_Schema = '{0}'
And LOWER(Table_Name) = '{1}'" , this.dbName, tableName.ToLower()).Replace("\r", "").Replace("\n", " ").Replace("\t", " ").Replace(" ", " ");
}
com.CommandText = queryString;
com.CommandType = CommandType.Text;
DataSet ds = new DataSet();
DbDataAdapter da = provider.CreateDataAdapter();
da.SelectCommand = com;
try
{
con.Open();
da.Fill(ds);
tableStruct = ds.Tables[0];
}
catch (Exception ex)
{
throw ex;
}
con.Close();
return(tableStruct);
}
public static void CreateDatabase(DbProviderFactory factory)
{
using (var conn = factory.CreateConnection())
using (var comm = conn.CreateCommand())
{
comm.CommandText = @"CREATE DATABASE '|DataDirectory|\Database.vdb5', PAGE SIZE 4, LCID 1033, CASE SENSITIVE FALSE;";
comm.ExecuteNonQuery();
}
}
private void ClickSelectedIndexChanged(object sender, EventArgs e)
{
if (comboBoxProviders.SelectedItem != null)
{
factory = DbProviderFactories.GetFactory(comboBoxProviders.SelectedItem.ToString());
connection = factory.CreateConnection();
providerName = GetConnectionStringByProvider(comboBoxProviders.SelectedItem.ToString());
textBoxConnectionString.Text = providerName;
}
}
///// <summary>
/////
///// </summary>
//public SQLiteHelper()
// : this(s_DefaultConnectionString)
//{
//}
/// <summary>
///
/// </summary>
/// <param name="provider"></param>
/// <param name="connectionString"></param>
public SQLiteHelper(string connectionString)
{
objFactory = System.Data.SQLite.SQLiteFactory.Instance;
objConnection = objFactory.CreateConnection();
objCommand = new DbCommandWrapper(objFactory.CreateCommand());
objConnection.ConnectionString = connectionString;
objCommand.Connection = objConnection;
}
public DAOFactory()
{
AppSettingsReader apps = new AppSettingsReader();
objProvider = DbProviderFactories.GetFactory(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionStringOracle"].ProviderName);
connectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionStringOracle"].ConnectionString;
objConn = objProvider.CreateConnection();
}
public static void PinFor()
{
string tblname = "RDBMS_Table_ADONET_RegressionTest_" + Guid.NewGuid().ToString().Replace("-", "");
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory("DSpace_DataProvider");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
DbCommand cmd = conn.CreateCommand();
//Create table
{
cmd.CommandText = "CREATE TABLE " + tblname + " (num INT, str CHAR(40), num2 LONG, num3 DOUBLE)";
int rows = cmd.ExecuteNonQuery();
}
//Select from table
{
cmd.CommandText = "SELECT PIN FOR 10 * FROM " + tblname;
DbDataReader reader = cmd.ExecuteReader();
reader.Close();
}
//Drop table
{
cmd.CommandText = "DROP PIN FOR 20 TABLE " + tblname + "";
cmd.ExecuteNonQuery();
}
//Select from table
bool ok = false;
{
try
{
cmd.CommandText = "SELECT PIN FOR 10 * FROM " + tblname;
DbDataReader reader = cmd.ExecuteReader();
reader.Close();
}
catch
{
ok = true;
}
}
conn.Close();
if (!ok)
{
throw new Exception("Table didn't get dropped.");
}
}
private static async Task<World> LoadRow(string connectionString, DbProviderFactory dbProviderFactory)
{
using (var db = dbProviderFactory.CreateConnection())
{
db.ConnectionString = connectionString;
// note: don't need to open connection if only doing one thing; let dapper do it
return await db.QueryFirstOrDefaultAsync<World>(
"SELECT [Id], [RandomNumber] FROM [World] WHERE [Id] = @Id",
new { Id = _random.Next(1, 10001) });
}
}
private void db_connect(string dbfilename)
{
//if (System.IO.File.Exists(dbfilename)) System.IO.File.Delete(dbfilename);
makeProvider();
_dbfact = DbProviderFactories.GetFactory("System.Data.SQLite");
_dbcon = _dbfact.CreateConnection();
_dbcon.ConnectionString = "Data Source=" + dbfilename + ";Pooling=true;FailIfMissing=false";
_dbconstring = _dbfact.CreateConnectionStringBuilder();
_dbconstring.ConnectionString = "Data Source=" + dbfilename + ";Pooling=true;FailIfMissing=false";
_dbcon.Open();
}
/// <summary>
/// Basic DAL object to communicate with data source
/// </summary>
/// <param name="ConnectionString">Data source connection string</param>
/// <param name="ProviderName">Data source provider name</param>
public DataAccess(string ConnectionString, string ProviderName)
{
this.conStr = ConnectionString;
this.prName = ProviderName;
factory = DbProviderFactories.GetFactory(prName);
cn = factory.CreateConnection();
com = factory.CreateCommand();
com.Connection = cn;
cn.ConnectionString = conStr;
}
public string CreateConnection()
{
string connStr = ConfigurationManager.ConnectionStrings["Kraka"].ConnectionString;
string provider = ConfigurationManager.ConnectionStrings["Kraka"].ProviderName;
dbFactory = DbProviderFactories.GetFactory(provider);
cn = dbFactory.CreateConnection();
cn.ConnectionString = connStr;
cn.Open();
return cn.State.ToString();
}
/// <summary>
/// Initializes a new instance of the OKHOSTING.Data.SqlBases.BaseExecuter class
/// </summary>
/// <param name="ConnectionString">
/// The connection string to use to connect to the DataBase
/// </param>
protected DataBase(DbProviderFactory providerFactory)
{
if (providerFactory == null)
{
throw new ArgumentNullException("providerFactory");
}
//Assigning the Connection String
ProviderFactory = providerFactory;
Connection = providerFactory.CreateConnection();
}
private DbService()
{
conStr = @"Data Source=localhost; Initial Catalog=timereg; user id=sa; password=1234";
//conStr = @"Data Source=localhost; user id=system; password=1234";
dbFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");
//dbFactory = DbProviderFactories.GetFactory("System.Data.OracleClient");
dbCon = dbFactory.CreateConnection();
dbCon.ConnectionString = conStr;
}
public DbConnection CreateConnection()
{
DbConnection dbconn = dbFactory.CreateConnection();
if (handler != null)
{
dbConnectionString = handler(dbConnectionString);
handler = null;
}
dbconn.ConnectionString = dbConnectionString;
return(dbconn);
}
public static void DbAggregators_DropTable(string tablename)
{
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory("DSpace_DataProvider");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "drop table " + tablename;
cmd.ExecuteNonQuery();
conn.Close();
}
public static void ConnClose()
{
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory("DSpace_DataProvider");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
conn.Close();
if (conn.State != ConnectionState.Closed)
{
throw new Exception("FAILED: State = {0}" + conn.State.ToString());
}
}
//private void trace(object[] data)
//{
// try
// {
// System.Diagnostics.TraceSource trace = new System.Diagnostics.TraceSource("DataIntegratorTraceSource");
// trace.TraceData(System.Diagnostics.TraceEventType.Information, new Random().Next(), data);
// trace.Flush();
// }
// catch (Exception)
// {
// //If you want to handle this exception, add your exception handling code here, else you may uncomment the following line to throw this exception out.
// throw;
// }
//}
private System.Data.Common.DbConnection getDbConnection(string providerName)
{
System.Data.Common.DbConnection returnValue = null;
if (!String.IsNullOrEmpty(providerName))
{
System.Data.Common.DbProviderFactory factory = System.Data.Common.DbProviderFactories.GetFactory(providerName);
if (factory != null)
{
returnValue = factory.CreateConnection();
}
}
return(returnValue);
}
public void RecordUpdate <T>(out int rowsAffected, T row)
{
rowsAffected = 0;
readConectionSettings();
string lib = GetProviderFactoryLib(providerName);
provider = RegisterProvider(providerName);
con = provider.CreateConnection();
con.ConnectionString = BuildConnectionString(providerName, dbName);
tableStruct = RetrieveTableStructure(tableName);
if (tableStruct.Columns.Count == 0)
{
return;
}
dataAdapter = provider.CreateDataAdapter();
dataAdapter.UpdateCommand = BuildUpdateCommand(provider, con);
dataAdapter.AcceptChangesDuringFill = true;
dataAdapter.AcceptChangesDuringUpdate = true;
dataAdapter.ContinueUpdateOnError = false;
DbCommand updCmd = dataAdapter.UpdateCommand;
Console.WriteLine("Update cmd: " + updCmd.CommandText);
try
{
updCmd.Connection.Open();
foreach (DbParameter parameter in updCmd.Parameters)
{
string fieldName = parameter.SourceColumn;
parameter.Value = row.GetType().GetProperty(fieldName).GetValue(row, null);
if (fieldName == identityColumn)
{
parameter.SourceVersion = DataRowVersion.Original;
}
}
object returnVal = updCmd.ExecuteNonQuery();
rowsAffected = Convert.ToInt16(returnVal);
}
catch (Exception ex)
{
throw ex;
}
finally
{ updCmd.Connection.Close(); }
}
/*
* Method to test the database provider version (only use for testing)
*/
public static string GetProviderVersion()
{
string versionInfo = String.Empty;
try
{
// SELECT COUNT(*)
// VistaDBConnection sqlConn = new VistaDBConnection("data source='|DataDirectory|\forms.vdb5';Password=aj80995");
System.Configuration.ConnectionStringSettings connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["UASEntitiesVenture"];
// Loads the factory
System.Data.Common.DbProviderFactory factory = System.Data.Common.DbProviderFactories.GetFactory(connectionString.ProviderName);
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString.ConnectionString;
connection.Open();
Debug.WriteLine("* * * GetProviderVersion ServerVersion: " + connection.ServerVersion);
Debug.WriteLine("* * * GetProviderVersion DataSource: " + connection.DataSource);
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT @@VERSION;";
command.CommandType = CommandType.Text;
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string result = reader.GetString(0);
if (!reader.IsDBNull(0))
{
versionInfo = result;
Debug.WriteLine("* * * GetProviderVersion versionInfo: " + versionInfo);
}
}
}
}
}
}
catch (Exception ex)
{
versionInfo = "Exception: " + ex.Message;
Debug.WriteLine("* * * AuthenticateLocalUser uas_User row count error: " + ex.Message);
}
return(versionInfo);
}
private bool TestConnection(string cs, out string reason)
{
reason = "";
try
{
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory("Qizmt_DataProvider");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = txtConnStr.Text.Trim();
conn.Open();
conn.Close();
return(true);
}
catch (Exception e)
{
reason = e.ToString();
return(false);
}
}
public bool LoadConnection()
{
if (connectionName == null)
{
return(false);
}
if (providerName == null)
{
this.readConectionSettings();
}
string lib = GetProviderFactoryLib(providerName);
provider = RegisterProvider(providerName);
DbConnection con = provider.CreateConnection();
con.ConnectionString = BuildConnectionString(providerName, this.dbName);
this.con = con;
return(true);
}
public static string GetRIndexConnStr(string host)
{
string _host = host.ToLower();
lock (hostToRIndexConnStr)
{
if (hostToRIndexConnStr.ContainsKey(_host))
{
return(hostToRIndexConnStr[_host]);
}
else
{
StringBuilder sb = new StringBuilder();
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory("Qizmt_DataProvider");
using (DbConnection conn = fact.CreateConnection())
{
//Get all host names of the Qizmt cluster
conn.ConnectionString = "data source = " + _host;
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SHELL 'Qizmt slaveinstalls' ";
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
sb.Append(reader.GetString(0));
}
conn.Close();
}
string[] lines = sb.ToString().Split(new char[] { '\n' }, StringSplitOptions.RemoveEmptyEntries);
string hosts = "";
for (int i = 0; i < lines.Length; i++)
{
string line = lines[i].Trim();
hosts += "," + line.Split(' ')[0];
}
hosts = hosts.Substring(1);
string connstr = "data source = " + hosts + "; rindex=pooled";
hostToRIndexConnStr.Add(_host, connstr);
return(connstr);
}
}
}
public int ExecuteNonQuery(String queryString, bool silence, params dynamic[] args)
{
string lib = GetProviderFactoryLib(providerName);
System.Data.Common.DbProviderFactory provider = RegisterProvider(providerName);
DbConnection con = provider.CreateConnection();
con.ConnectionString = BuildConnectionString(providerName, this.dbName);
DbCommand com = provider.CreateCommand();
com.CommandTimeout = 30;
com.Connection = con;
com.CommandText = queryString;
com.CommandType = CommandType.Text;
foreach (var param in args)
{
DbParameter par = provider.CreateParameter();
par.Direction = ParameterDirection.Input;
par.ParameterName = param.Name;
par.Value = param.Value;
com.Parameters.Add(par);
}
int rowsAffected = 0;
try
{
con.Open();
object result = com.ExecuteNonQuery();
if (result != null)
{
rowsAffected = Convert.ToInt32(result);
}
}
catch (Exception ex)
{
throw ex;
}
con.Close();
return(rowsAffected);
}
public DataTable ExecuteQuery(String queryString, params dynamic[] args)
{
readConectionSettings();
string lib = GetProviderFactoryLib(providerName);
provider = RegisterProvider(providerName);
con = provider.CreateConnection();
con.ConnectionString = BuildConnectionString(providerName, this.dbName);
DataTable table = null;
DbCommand com = provider.CreateCommand();
com.CommandTimeout = 30;
com.Connection = con;
com.CommandText = queryString;
com.CommandType = CommandType.Text;
dataSet = new DataSet();
dataAdapter = provider.CreateDataAdapter();
dataAdapter.SelectCommand = com;
foreach (var param in args)
{
DbParameter par = provider.CreateParameter();
par.Direction = ParameterDirection.Input;
par.ParameterName = param.Name;
par.Value = param.Value;
dataAdapter.SelectCommand.Parameters.Add(par);
}
try
{
con.Open();
dataAdapter.Fill(dataSet);
table = dataSet.Tables[0];
}
catch (Exception ex)
{
Console.Write(ex);
throw ex;
}
con.Close();
return(table);
}
///// <summary>
// /// 得到连接字符串
///// </summary>
// /// <returns>连接字符串</returns>
// static private string getConnString(string key)
// {
// string connStr = System.Configuration.ConfigurationManager.ConnectionStrings[key].ToString();
// if (connStr == null || connStr == "")
// {
// DBClassHelper.ErrLog("DBClassHelper.getConnString(string key):["+key+"]所指定的连接类型为空");
// }
// return connStr;
// }
//public static string GetConnectionEntityString(ref string prividerName)
//{
// var conn = System.Configuration.ConfigurationManager.ConnectionStrings[1].ConnectionString;
// // var esb = new System.Data.EntityClient.EntityConnectionStringBuilder(conn);
// // prividerName = esb.Provider;
// // return esb.ProviderConnectionString;
// //}
// return conn;
//}
//public static string GetConnectionEntityString(ref string prividerName, string connStr)
//{
// if (string.IsNullOrEmpty(connStr))
// return GetConnectionEntityString(ref prividerName);
// var conn = System.Configuration.ConfigurationManager.ConnectionStrings[connStr].ConnectionString;
// //var esb = new System.Data.EntityClient.EntityConnectionStringBuilder(conn);
// //prividerName = esb.Provider;
// //return esb.ProviderConnectionString;
// return conn;
//}
//public static string GetConnectionString(ref string prividerName, string connStr)
//{
// string conn = "";
// if (string.IsNullOrEmpty(connStr))
// conn = GetConnectionEntityString(ref prividerName);
// conn = System.Configuration.ConfigurationManager.ConnectionStrings[connStr].ConnectionString;
// var esb = new System.Data.EntityClient.EntityConnectionStringBuilder(conn);
// prividerName = esb.Provider;
// return esb.ProviderConnectionString;
//}
/// <summary>
/// 打开数据库连接
/// </summary>
/// <param name="connName">连接名</param>
/// <returns></returns>
public static System.Data.Common.DbConnection OpenConnect(string connectionStr, string providerName)
{
System.Data.Common.DbConnection Conn;
if (providerName.Contains("SQLite"))
{
Conn = new SQLiteConnection();
}
else
if (providerName.Contains("MySqlClient"))
{
Conn = new MySql.Data.MySqlClient.MySqlConnection();
}
else
{
System.Data.Common.DbProviderFactory f = System.Data.Common.DbProviderFactories.GetFactory(providerName);
Conn = f.CreateConnection();
}
//得到连接字符串
Conn.ConnectionString = connectionStr;
Conn.Open();
return(Conn);
}
private static void Health(string[] args)
{
string[] hosts = Utils.GetQizmtHosts();
List <string> badhosts = new List <string>();
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory("DSpace_DataProvider");
foreach (string host in hosts)
{
try
{
using (DbConnection conn = fact.CreateConnection())
{
conn.ConnectionString = "data source = " + host;
conn.Open();
conn.Close();
}
}
catch (Exception e)
{
Console.WriteLine("Error while connecting to " + host + ": " + e.ToString());
badhosts.Add(host);
}
}
if (badhosts.Count == 0)
{
Console.WriteLine("All healthy");
}
else
{
Console.WriteLine("Bad hosts:");
foreach (string bad in badhosts)
{
Console.WriteLine(bad);
}
}
}
private static OneRow[] RIndexOnLongPinHash_PrepareSourceTable(string sourcetablename, int testsize)
{
string[] words = @"Apple's good for you. $apple$ with 2 dollar signs. Whether you want to build an audience or
communicate with your closest friends, the MySpace Privacy Control Panel allows you to control what
information is shared, who can contact you, how you are notified and the tools to eliminate unwanted
spam. Here you control who can see you are online, who knows your birthday, who can view your profile,
who can share your photos and how to block specific users and users by age. Here you control spam security
levels, who can message you, who can request to be friends, approval of comments, group invitations, event and
IM invitations. Here you control your subscription to the MySpace newsletter featuring exclusive content and
promotions and email notifications about friend requests, comments, blog subscriptions and event invitations.
Here you control SMS (text message) alerts for MySpace messages, friend requests, content and when you
receive mobile content subscriptions. Here you control how your calendar is set up and how you are reminded
of important events. Here you control how groups are displayed on your profile, if and where you accept HTML
comments, how your music player works and your away message when you are on vacation or are
taking a break.".Split(new string[] { " ", Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
for (int wi = 0; wi < words.Length; wi++)
{
words[wi] = words[wi].Trim();
if (words[wi].Length == 0)
{
words[wi] = "xx";
}
}
Dictionary <long, OneRow> testrows = new Dictionary <long, OneRow>();
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory("DSpace_DataProvider");
Console.WriteLine("Preparing source table...");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE " + sourcetablename + " (num1 INT, num2 DOUBLE, str CHAR(200), dt DATETIME, num3 LONG)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO " + sourcetablename + " VALUES (@num1, @num2, @str, @dt, @num3)";
DbParameter num1 = cmd.CreateParameter();
num1.ParameterName = "@num1";
num1.DbType = DbType.Int32;
DbParameter num2 = cmd.CreateParameter();
num2.ParameterName = "@num2";
num2.DbType = DbType.Double;
DbParameter str = cmd.CreateParameter();
str.ParameterName = "@str";
str.DbType = DbType.String;
str.Size = 200;
DbParameter dt = cmd.CreateParameter();
dt.ParameterName = "@dt";
dt.DbType = DbType.DateTime;
DbParameter num3 = cmd.CreateParameter();
num3.ParameterName = "@num3";
num3.DbType = DbType.Int64;
Random rnd = new Random(System.DateTime.Now.Millisecond / 2 + System.Diagnostics.Process.GetCurrentProcess().Id / 2);
int rowcount = 50000;// (1024 * 1024 * 10) / (9 * 3);
int min = -5000;
int max = 5000;
DateTime dtseed = DateTime.Now;
for (int i = 0; i < rowcount; i++)
{
OneRow row;
row.num1 = rnd.Next(min, max);
row.num2 = rnd.Next(min, max);
row.num3 = rnd.Next(min, max);
row.dt = dtseed.AddDays(rnd.Next(min, max));
row.str = words[rnd.Next() % words.Length];
if (testrows.Count < testsize && !testrows.ContainsKey(row.num3))
{
testrows.Add(row.num3, row);
}
num1.Value = row.num1;
num2.Value = row.num2;
num3.Value = row.num3;
str.Value = row.str;
dt.Value = row.dt;
cmd.ExecuteNonQuery();
}
conn.Close();
Console.WriteLine("Source table created.");
return((new List <OneRow>(testrows.Values)).ToArray());
}
public static void RIndexOnLongPinHash()
{
string tablename = "rselect_test_" + Guid.NewGuid().ToString().Replace("-", "");
string tablenameSorted = "rselect_test_sorted_" + Guid.NewGuid().ToString().Replace("-", "");
string indexname = Guid.NewGuid().ToString().Replace("-", "") + "apple";
const int TESTSIZE = 10;
OneRow[] testrows = null;
testrows = RIndexOnLongPinHash_PrepareSourceTable(tablename, TESTSIZE);
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory("DSpace_DataProvider");
//Key column = string.
{
Console.WriteLine("Sorting table by key column long...");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE " + tablenameSorted + " (num1 INT, num2 DOUBLE, str CHAR(200), dt DATETIME, num3 LONG)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO " + tablenameSorted + " SELECT * FROM " + tablename + " ORDER BY num3";
cmd.ExecuteNonQuery();
}
conn.Close();
Console.WriteLine("Table sorted by long");
}
{
Console.WriteLine("Creating RIndexes pinmemoryhash on key column = long...");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE RINDEX " + indexname + " FROM " + tablenameSorted + " pinmemoryhash ON NUM3";
cmd.ExecuteNonQuery();
conn.Close();
Console.WriteLine("RIndexes created.");
}
Dictionary <long, List <OneRow> > expected = new Dictionary <long, List <OneRow> >();
{
Console.WriteLine("Querying data using SELECT...");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
DbCommand cmd = conn.CreateCommand();
foreach (OneRow row in testrows)
{
cmd.CommandText = "select * from " + tablenameSorted + " where num3 = " + row.num3.ToString();
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
OneRow r;
r.num1 = reader.GetInt32(0);
r.num2 = reader.GetDouble(1);
r.str = reader.GetString(2);
r.dt = reader.GetDateTime(3);
r.num3 = reader.GetInt64(4);
if (!expected.ContainsKey(r.num3))
{
expected.Add(r.num3, new List <OneRow>());
}
expected[r.num3].Add(r);
}
reader.Close();
}
conn.Close();
Console.WriteLine("SELECT completed.");
}
{
Console.WriteLine("Querying data using RSELECT NOPOOL...");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost; rindex=nopool";
conn.Open();
DbCommand cmd = conn.CreateCommand();
foreach (long key in expected.Keys)
{
List <OneRow> results = new List <OneRow>();
cmd.CommandText = "rselect * from " + indexname.ToUpper() + " where key = " + key.ToString();
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
OneRow r;
r.num1 = reader.GetInt32(0);
r.num2 = reader.GetDouble(1);
r.str = reader.GetString(2);
r.dt = reader.GetDateTime(3);
r.num3 = reader.GetInt64(4);
results.Add(r);
}
reader.Close();
//compare results
List <OneRow> xlist = expected[key];
if (xlist.Count != results.Count)
{
throw new Exception("Result count: " + results.Count.ToString() + " is different from that of expected: " + xlist.Count.ToString() + ". Query= " + cmd.CommandText);
}
foreach (OneRow r in results)
{
bool found = false;
foreach (OneRow x in xlist)
{
if (r.num1 == x.num1 && r.num2 == x.num2 && r.num3 == x.num3 && r.str == x.str && r.dt == x.dt)
{
found = true;
break;
}
}
if (!found)
{
throw new Exception("RSelect returned a row which was not located in expected results. num2=" + r.num2.ToString() + " num3=" + r.num3.ToString() + ". Query= " + cmd.CommandText);
}
}
foreach (OneRow x in xlist)
{
bool found = false;
foreach (OneRow r in results)
{
if (r.num1 == x.num1 && r.num2 == x.num2 && r.num3 == x.num3 && r.str == x.str && r.dt == x.dt)
{
found = true;
break;
}
}
if (!found)
{
throw new Exception("RSelect did not return an expected row. num2=" + x.num2.ToString() + " num3=" + x.num3.ToString() + ". Query= " + cmd.CommandText);
}
}
}
conn.Close();
Console.WriteLine("RSelect NOPOOL completed.");
}
{
Console.WriteLine("Querying data using RSELECT POOL/OR");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost; rindex=pooled";
conn.Open();
DbCommand cmd = conn.CreateCommand();
string whereor = "";
foreach (long key in expected.Keys)
{
if (whereor.Length > 0)
{
whereor += " OR ";
}
whereor += " key = " + key.ToString() + " ";
}
{
Dictionary <long, List <OneRow> > results = new Dictionary <long, List <OneRow> >();
cmd.CommandText = "rselect * from " + indexname.ToUpper() + " where " + whereor;
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
OneRow r;
r.num1 = reader.GetInt32(0);
r.num2 = reader.GetDouble(1);
r.str = reader.GetString(2);
r.dt = reader.GetDateTime(3);
r.num3 = reader.GetInt64(4);
if (!results.ContainsKey(r.num3))
{
results.Add(r.num3, new List <OneRow>());
}
results[r.num3].Add(r);
}
reader.Close();
//compare results
foreach (long key in expected.Keys)
{
List <OneRow> xlist = expected[key];
if (xlist.Count != results[key].Count)
{
throw new Exception("Result count: " + results[key].Count.ToString() + " is different from that of expected: " + xlist.Count.ToString() + ". Key=" + key.ToString() + ". Query:" + cmd.CommandText);
}
foreach (OneRow r in results[key])
{
bool found = false;
foreach (OneRow x in xlist)
{
if (r.num1 == x.num1 && r.num2 == x.num2 && r.num3 == x.num3 && r.str == x.str && r.dt == x.dt)
{
found = true;
break;
}
}
if (!found)
{
throw new Exception("RSelect returned a row which was not located in expected results. num2=" + r.num2.ToString() + " num3=" + r.num3.ToString() + ". Key=" + key.ToString() + ". Query:" + cmd.CommandText);
}
}
foreach (OneRow x in xlist)
{
bool found = false;
foreach (OneRow r in results[key])
{
if (r.num1 == x.num1 && r.num2 == x.num2 && r.num3 == x.num3 && r.str == x.str && r.dt == x.dt)
{
found = true;
break;
}
}
if (!found)
{
throw new Exception("RSelect did not return an expected row. num2=" + x.num2.ToString() + " num3=" + x.num3.ToString() + ". Key=" + key.ToString() + ". Query:" + cmd.CommandText);
}
}
}
}
conn.Close();
Console.WriteLine("RSelect POOL/OR completed.");
}
{
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "drop table " + tablename;
cmd.ExecuteNonQuery();
cmd.CommandText = "drop table " + tablenameSorted;
cmd.ExecuteNonQuery();
cmd.CommandText = "drop rindex " + indexname.ToUpper();
cmd.ExecuteNonQuery();
conn.Close();
}
}
public static void RSelectThorough()
{
string tablename = "regression_test_" + Guid.NewGuid().ToString().Replace("-", "");
string tablenameSorted = "regression_test_" + Guid.NewGuid().ToString().Replace("-", "");
string indexname = Guid.NewGuid().ToString().Replace("-", "") + "apple";
Dictionary <long, List <KeyValuePair <long, long> > > expected = new Dictionary <long, List <KeyValuePair <long, long> > >();
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory("DSpace_DataProvider");
{
Console.WriteLine("Preparing data...");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE " + tablename + " (num1 LONG, num2 LONG, num3 LONG)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO " + tablename + " VALUES (@num1, @num2, @num3)";
DbParameter num1 = cmd.CreateParameter();
num1.ParameterName = "@num1";
num1.DbType = DbType.Int64;
DbParameter num2 = cmd.CreateParameter();
num2.ParameterName = "@num2";
num2.DbType = DbType.Int64;
DbParameter num3 = cmd.CreateParameter();
num3.ParameterName = "@num3";
num3.DbType = DbType.Int64;
Random rnd = new Random();
int rowcount = 5000;
int min = -1000;
int max = 1000;
for (int i = 0; i < rowcount; i++)
{
num1.Value = rnd.Next(min, max);
num2.Value = rnd.Next(min, max);
num3.Value = rnd.Next(min, max);
cmd.ExecuteNonQuery();
}
}
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE " + tablenameSorted + " (num1 LONG, num2 LONG, num3 LONG)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO " + tablenameSorted + " SELECT * FROM " + tablename + " ORDER BY num1";
cmd.ExecuteNonQuery();
}
///////! cannot create index here.
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from " + tablenameSorted;
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
long num1 = reader.GetInt64(0);
long num2 = reader.GetInt64(1);
long num3 = reader.GetInt64(2);
if (!expected.ContainsKey(num1))
{
expected.Add(num1, new List <KeyValuePair <long, long> >());
}
expected[num1].Add(new KeyValuePair <long, long>(num2, num3));
}
reader.Close();
}
conn.Close();
Console.WriteLine("Completed preparing data.");
}
{
Console.WriteLine("Creating RIndexes...");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE RINDEX " + indexname + " FROM " + tablenameSorted;
cmd.ExecuteNonQuery();
conn.Close();
Console.WriteLine("RIndexes created.");
}
{
Console.WriteLine("Querying data using RSELECT...");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost; rindex=nopool";
conn.Open();
foreach (long key in expected.Keys)
{
Dictionary <long, List <KeyValuePair <long, long> > > results = new Dictionary <long, List <KeyValuePair <long, long> > >();
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "rselect * from " + indexname.ToUpper() + " where key = " + key.ToString();
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
long num1 = reader.GetInt64(0);
long num2 = reader.GetInt64(1);
long num3 = reader.GetInt64(2);
if (!results.ContainsKey(num1))
{
results.Add(num1, new List <KeyValuePair <long, long> >());
}
results[num1].Add(new KeyValuePair <long, long>(num2, num3));
}
reader.Close();
if (!results.ContainsKey(key))
{
throw new Exception("RSelect did not return rows with key = " + key.ToString());
}
List <KeyValuePair <long, long> > resultlist = results[key];
List <KeyValuePair <long, long> > expectedlist = expected[key];
if (resultlist.Count != expectedlist.Count)
{
throw new Exception(resultlist.Count.ToString() + " number of rows are returned from RSELECT. This number did not match that of expected results: " + expectedlist.Count.ToString());
}
foreach (KeyValuePair <long, long> rpair in resultlist)
{
bool found = false;
foreach (KeyValuePair <long, long> xpair in expectedlist)
{
if (rpair.Key == xpair.Key && rpair.Value == xpair.Value)
{
found = true;
break;
}
}
if (!found)
{
throw new Exception("RSelect returned a row which was not located in expected results. num2=" + rpair.Key.ToString() + " num3=" + rpair.Value.ToString());
}
}
foreach (KeyValuePair <long, long> xpair in expectedlist)
{
bool found = false;
foreach (KeyValuePair <long, long> rpair in resultlist)
{
if (rpair.Key == xpair.Key && rpair.Value == xpair.Value)
{
found = true;
break;
}
}
if (!found)
{
throw new Exception("RSelect did not return an expected row. num2=" + xpair.Key.ToString() + " num3=" + xpair.Value.ToString());
}
}
}
}
conn.Close();
}
Console.WriteLine("RSelectThorough passed");
{
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "drop table " + tablename;
cmd.ExecuteNonQuery();
cmd.CommandText = "drop table " + tablenameSorted;
cmd.ExecuteNonQuery();
cmd.CommandText = "drop rindex " + indexname;
cmd.ExecuteNonQuery();
conn.Close();
}
}
public void RunQuery()
{
ClearResults();
string fquery = txtQuery.Text.Trim();
bool hasgo = false;
if (fquery.StartsWith("GO", StringComparison.OrdinalIgnoreCase))
{
for (int i = 2; ; i++)
{
if (i >= fquery.Length)
{
//hasgo = false;
break;
}
if ('\n' == fquery[i])
{
hasgo = true;
fquery = fquery.Substring(i + 1).TrimStart();
break;
}
if (!char.IsWhiteSpace(fquery[i]))
{
//hasgo = false;
break;
}
}
}
List <string> queries = new List <string>();
if (hasgo)
{
string nsqueries = _CleanQuery(fquery, "\0");
queries.AddRange(nsqueries.Split(new char[] { '\0' },
StringSplitOptions.RemoveEmptyEntries));
if (0 == queries.Count)
{
MessageBox.Show("Expected query after GO");
return;
}
}
else
{
string cleanedQuery = _CleanQuery(fquery, " ");
if (cleanedQuery.Length == 0)
{
MessageBox.Show("Query is empty.");
return;
}
queries.Add(cleanedQuery);
}
button1.Enabled = false;
ShowStatus("Executing query...");
this.Update();
System.Threading.Thread thd = new System.Threading.Thread(new System.Threading.ThreadStart(delegate()
{
string status = "";
DbConnection conn = null;
DateTime starttime = DateTime.MinValue;
bool completed = false;
bool needsaycompleted = true;
try
{
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory("Qizmt_DataProvider");
conn = fact.CreateConnection();
if (IsRIndexQuery(queries[0])) //just check the first query
{
conn.ConnectionString = Utils.GetRIndexConnStr(firsthost);
}
else
{
conn.ConnectionString = ConnectionString;
}
conn.Open();
DbCommand cmd = conn.CreateCommand();
starttime = DateTime.Now;
for (int onquery = 0; onquery < queries.Count; onquery++)
{
string thisquery = queries[onquery].Trim();
if (0 == thisquery.Length)
{
continue;
}
if (onquery > 0)
{
TSafe(new Action(delegate()
{
ShowStatus("Executing next query...");
}));
}
TSafe(new Action(delegate()
{
this.Update();
}));
cmd.CommandText = thisquery;
if (thisquery.StartsWith("select", StringComparison.OrdinalIgnoreCase) ||
thisquery.StartsWith("shell", StringComparison.OrdinalIgnoreCase) ||
thisquery.StartsWith("rselect", StringComparison.OrdinalIgnoreCase))
{
needsaycompleted = false;
DbDataReader reader = cmd.ExecuteReader();
TSafe(new Action(delegate()
{
ClearResults();
}));
DataSet ds = new DataSet();
DataTable dt = new DataTable();
//Get column meta data.
int columnCount = reader.FieldCount;
for (int i = 0; i < columnCount; i++)
{
dt.Columns.Add(reader.GetName(i));
}
#if DEBUG
try
{
string computer_name = System.Environment.GetEnvironmentVariable("COMPUTERNAME");
if (computer_name == "MAPDCMILLER" ||
computer_name == "MAPDDRULE1")
{
StringBuilder sblastschema = new StringBuilder();
for (int i = 0; i < columnCount; i++)
{
if (sblastschema.Length != 0)
{
sblastschema.Append(", ");
}
sblastschema.AppendFormat("{0} {1}",
reader.GetDataTypeName(i), reader.GetName(i));
}
sblastschema.AppendLine();
System.IO.File.AppendAllText(@"C:\temp\lastschema.txt", sblastschema.ToString());
}
}
catch
{
}
#endif
ds.Tables.Add(dt);
while (reader.Read())
{
object[] row = new object[columnCount];
reader.GetValues(row);
for (int ir = 0; ir < row.Length; ir++)
{
if (DBNull.Value == row[ir])
{
row[ir] = "NULL";
}
}
dt.Rows.Add(row);
}
//Get result table schema before closing the reader.
DataTable schema = reader.GetSchemaTable();
reader.Close();
TSafe(new Action(delegate()
{
dgvResults.DataSource = ds.Tables[0];
SetGridWidths(schema);
}));
}
else
{
cmd.ExecuteNonQuery();
TSafe(new Action(delegate()
{
ClearResults();
needsaycompleted = true;
}));
}
}
completed = true;
}
catch (Exception e)
{
TSafe(new Action(delegate()
{
AddRow("Exception Details", e.ToString());
}));
status = "Exceptions occurred";
}
finally
{
try
{
conn.Close();
}
catch (Exception e)
{
completed = false;
TSafe(new Action(delegate()
{
AddRow("Exception Details", e.ToString());
}));
status = "Exceptions occurred";
}
}
if (completed)
{
if (needsaycompleted)
{
AddRow("Query results", "Completed");
}
long tsecs = (long)(DateTime.Now - starttime).TotalSeconds;
StringBuilder sb = new StringBuilder();
if (tsecs >= 60 * 60 * 24)
{
long x = tsecs / (60 * 60 * 24);
sb.AppendFormat(" {0} {1}", x, 1 == x ? "day" : "days");
tsecs %= 60 * 60 * 24;
}
if (tsecs >= 60 * 60)
{
long x = tsecs / (60 * 60);
sb.AppendFormat(" {0} {1}", x, 1 == x ? "hour" : "hours");
tsecs %= 60 * 60;
}
if (tsecs >= 60)
{
long x = tsecs / 60;
sb.AppendFormat(" {0} {1}", x, 1 == x ? "minute" : "minutes");
tsecs %= 60;
}
if (tsecs > 0 || 0 == sb.Length)
{
long x = tsecs / 1;
sb.AppendFormat(" {0} {1}", x, 1 == x ? "second" : "seconds");
tsecs %= 1;
}
status = "Execution completed in" + sb.ToString();
}
TSafe(new Action(delegate()
{
EnableEditMenutItems(true);
ShowStatus(status);
button1.Enabled = true;
}));
}));
thd.Start();
}
public static void RIndexPin()
{
string tablename = "rselect_test_" + Guid.NewGuid().ToString().Replace("-", "");
string tablenameSorted = "rselect_test_sorted" + Guid.NewGuid().ToString().Replace("-", "");
string indexname = Guid.NewGuid().ToString().Replace("-", "") + "apple";
Dictionary <long, List <KeyValuePair <long, long> > > expected = new Dictionary <long, List <KeyValuePair <long, long> > >();
Dictionary <long, int> testvalues = new Dictionary <long, int>(5);
const int TESTSIZE = 5;
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory("DSpace_DataProvider");
{
Console.WriteLine("Preparing data...");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE " + tablename + " (num1 LONG, num2 LONG, num3 LONG)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO " + tablename + " VALUES (@num1, @num2, @num3)";
DbParameter num1 = cmd.CreateParameter();
num1.ParameterName = "@num1";
num1.DbType = DbType.Int64;
DbParameter num2 = cmd.CreateParameter();
num2.ParameterName = "@num2";
num2.DbType = DbType.Int64;
DbParameter num3 = cmd.CreateParameter();
num3.ParameterName = "@num3";
num3.DbType = DbType.Int64;
Random rnd = new Random(System.DateTime.Now.Millisecond / 2 + System.Diagnostics.Process.GetCurrentProcess().Id / 2);
int rowcount = 5000;// (1024 * 1024 * 10) / (9 * 3);
int min = -100;
int max = 100;
for (int i = 0; i < rowcount; i++)
{
long key = rnd.Next(min, max);
if (testvalues.Count < TESTSIZE && !testvalues.ContainsKey(key))
{
testvalues.Add(key, 0);
}
num1.Value = key;
num2.Value = rnd.Next(min, max);
num3.Value = rnd.Next(min, max);
cmd.ExecuteNonQuery();
}
}
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE " + tablenameSorted + " (num1 LONG, num2 LONG, num3 LONG)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO " + tablenameSorted + " SELECT * FROM " + tablename + " ORDER BY num1";
cmd.ExecuteNonQuery();
}
///////! cannot create index here.
conn.Close();
Console.WriteLine("Data prepared.");
}
{
Console.WriteLine("Querying data using SELECT...");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
DbCommand cmd = conn.CreateCommand();
foreach (long key in testvalues.Keys)
{
cmd.CommandText = "select * from " + tablenameSorted + " where num1 = " + key.ToString();
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
long num1 = reader.GetInt64(0);
long num2 = reader.GetInt64(1);
long num3 = reader.GetInt64(2);
if (!expected.ContainsKey(num1))
{
expected.Add(num1, new List <KeyValuePair <long, long> >());
}
expected[num1].Add(new KeyValuePair <long, long>(num2, num3));
}
reader.Close();
}
conn.Close();
Console.WriteLine("SELECT completed.");
}
{
Console.WriteLine("Creating RIndexes...");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE RINDEX " + indexname + " FROM " + tablenameSorted + " PINMEMORY";
cmd.ExecuteNonQuery();
conn.Close();
Console.WriteLine("RIndexes created pinned.");
}
Console.WriteLine("Querying data using RSELECT...");
{
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost; rindex=nopool";
conn.Open();
DbCommand cmd = conn.CreateCommand();
foreach (long key in expected.Keys)
{
List <KeyValuePair <long, long> > results = new List <KeyValuePair <long, long> >();
cmd.CommandText = "rselect * from " + indexname.ToUpper() + " where key = " + key.ToString();
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
long num1 = reader.GetInt64(0);
long num2 = reader.GetInt64(1);
long num3 = reader.GetInt64(2);
results.Add(new KeyValuePair <long, long>(num2, num3));
}
reader.Close();
//compare results
List <KeyValuePair <long, long> > xlist = expected[key];
if (xlist.Count != results.Count)
{
throw new Exception("Result count: " + results.Count.ToString() + " is different from that of expected: " + xlist.Count.ToString());
}
foreach (KeyValuePair <long, long> rpair in results)
{
bool found = false;
foreach (KeyValuePair <long, long> xpair in xlist)
{
if (rpair.Key == xpair.Key && rpair.Value == xpair.Value)
{
found = true;
break;
}
}
if (!found)
{
throw new Exception("RSelect returned a row which was not located in expected results. num2=" + rpair.Key.ToString() + " num3=" + rpair.Value.ToString());
}
}
foreach (KeyValuePair <long, long> xpair in xlist)
{
bool found = false;
foreach (KeyValuePair <long, long> rpair in results)
{
if (rpair.Key == xpair.Key && rpair.Value == xpair.Value)
{
found = true;
break;
}
}
if (!found)
{
throw new Exception("RSelect did not return an expected row. num2=" + xpair.Key.ToString() + " num3=" + xpair.Value.ToString());
}
}
}
conn.Close();
}
Console.WriteLine("RIndexPin completed.");
{
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "drop table " + tablename;
cmd.ExecuteNonQuery();
cmd.CommandText = "drop table " + tablenameSorted;
cmd.ExecuteNonQuery();
cmd.CommandText = "drop rindex " + indexname;
cmd.ExecuteNonQuery();
conn.Close();
}
}
public static void ShellCmdLossless()
{
{
string guid = "{FFA94574-D63F-44ae-920E-5DFA841F6A69}";
while (guid.Length < 300)
{
guid += "a";
}
string job = (@"<SourceCode>
<Jobs>
<Job Name=`` Custodian=`` Email=``>
<IOSettings>
<JobType>local</JobType>
</IOSettings>
<Local>
<![CDATA[
public virtual void Local()
{
DSpace_Log(`" + guid + @"`);
}
]]>
</Local>
</Job>
</Jobs>
</SourceCode>").Replace('`', '"');
string tempdir = @"\\" + System.Net.Dns.GetHostName() + @"\" + Environment.CurrentDirectory.Replace(':', '$') + @"\" + Guid.NewGuid().ToString().Replace("-", "");
if (System.IO.Directory.Exists(tempdir))
{
System.IO.Directory.Delete(tempdir, true);
}
System.IO.Directory.CreateDirectory(tempdir);
string tempjobname = Guid.NewGuid().ToString();
System.IO.File.WriteAllText(tempdir + @"\" + tempjobname, job);
Exec.Shell("Qizmt importdir \"" + tempdir + "\"");
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory("DSpace_DataProvider");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SHELL 'Qizmt exec " + tempjobname + "'";
DbDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.Append(reader.GetString(0));
}
reader.Close();
conn.Close();
//Clean up
Exec.Shell(@"Qizmt del " + tempjobname);
System.IO.Directory.Delete(tempdir, true);
if (sb.ToString().IndexOf(guid) == -1)
{
throw new Exception("Shell command failed. Not lossless.");
}
}
{
string expected = Exec.Shell("Qizmt ls");
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory("DSpace_DataProvider");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SHELL 'Qizmt ls'";
DbDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.Append(reader.GetString(0));
}
reader.Close();
conn.Close();
if (expected != sb.ToString())
{
throw new Exception("Shell command failed. Not lossless.");
}
}
}
public static void Truncate()
{
string tblname = "RDBMS_Table_ADONET_RegressionTest_" + Guid.NewGuid().ToString().Replace("-", "");
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory("DSpace_DataProvider");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
DbCommand cmd = conn.CreateCommand();
//Create table
{
cmd.CommandText = "CREATE TABLE " + tblname + " (num INT, str CHAR(40), num2 LONG, num3 DOUBLE)";
int rows = cmd.ExecuteNonQuery();
}
//Insert data
{
cmd.CommandText = "INSERT INTO " + tblname + " VALUES (1, 'PPP', 2, 3.14)";
cmd.ExecuteNonQuery();
}
bool ok = false;
{
cmd.CommandText = "SELECT TOP 100 * FROM " + tblname + " ORDER BY num";
DbDataReader reader = cmd.ExecuteReader();
int rowcount = 0;
while (reader.Read())
{
rowcount++;
}
reader.Close();
ok = (rowcount == 1);
}
if (ok)
{
cmd.CommandText = "TRUNCATE TABLE " + tblname;
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT TOP 100 * FROM " + tblname + " ORDER BY num";
DbDataReader reader = cmd.ExecuteReader();
int rowcount = 0;
while (reader.Read())
{
rowcount++;
}
if (rowcount > 0)
{
ok = false;
}
reader.Close();
}
{
cmd.CommandText = "drop table " + tblname;
cmd.ExecuteNonQuery();
}
conn.Close();
if (!ok)
{
throw new Exception("Didn't truncate table.");
}
}