public void ExecuteQuery()
{
gridResults.Columns.Clear();
lblCount.Text = "";
Properties.Settings.Default.SpreadSheetQuery = textQuery.Text;
Properties.Settings.Default.Save();
try
{
Cursor.Current = Cursors.WaitCursor;
string query;
if (textQuery.SelectionLength > 0)
{
query = textQuery.SelectedText;
}
else
{
query = textQuery.Text;
}
System.Data.Common.DbDataAdapter adapter = null;
if (_connection is OleDbConnection)
{
adapter = new OleDbDataAdapter(query, _connection as OleDbConnection);
}
else if (_connection is MySqlConnection)
{
adapter = new MySqlDataAdapter(query, _connection as MySqlConnection);
}
if (adapter == null)
{
return;
}
DataTable table = new DataTable();
adapter.Fill(table);
_connection.Close();
gridResults.DataSource = table;
tabControl.SelectedIndex = 0;
lblCount.Text = "Count: " + table.Rows.Count.ToString();
}
catch (Exception ex)
{
tabControl.SelectedIndex = 1;
textResults.Lines = new string[] { "There were errors executing your query.", "", ex.Message };
}
finally
{
Cursor.Current = Cursors.Default;
}
}
/*
* Public Sub Bind()
* If _Cmd Is Nothing Or _Ad Is Nothing Then
* Throw New Exception("The database connection is not set")
* End If
* _Ad.Fill(Me)
* End Sub
*/
/// <summary>
/// Executes the query and fills this datatable.
/// </summary>
public void Bind()
{
if (_Cmd == null || _Ad == null)
{
throw (new TenorException("The database connection is not set"));
}
bool exception = false;
DateTime sqlTime = DateTime.Now;
try
{
if (_ActiveConnection.State == ConnectionState.Closed)
{
Helper.OpenConnection(_ActiveConnection);
}
sqlTime = DateTime.Now;
//Diagnostics.Debug.DebugSQL("Tenor.Data.DataTable", this.CommandText, null, currentConnection);
_Ad.Fill(this);
}
catch (Exception up)
{
exception = true;
up.Data.Add("CommandText", CommandText);
throw;
}
finally
{
try
{
if (exception)
{
System.Diagnostics.Trace.TraceInformation("DataTable.Bind Time: " + (DateTime.Now - sqlTime).TotalSeconds.ToString() + " sec. (SqlException)");
}
else
{
System.Diagnostics.Trace.TraceInformation("DataTable.Bind Time: " + (DateTime.Now - sqlTime).TotalSeconds.ToString() + " sec.");
}
}
catch
{
}
if (_ActiveConnection.State != ConnectionState.Closed)
{
_ActiveConnection.Close();
}
}
}
static public void CloseDB()
{
if (DBConn.State == ConnectionState.Open)
{
DBConn.Close();
}
}
public ActionResult Get(string layer_name)
{
System.Data.Common.DbConnection conn = _context.Database.GetDbConnection();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//cmd.CommandText = "dbo.get_geojson";
cmd.CommandText = "dbo.get_geojson_2";
cmd.Connection = (SqlConnection)conn;
//cmd.Parameters.AddWithValue("@table_name", layer_name);
cmd.Parameters.AddWithValue("@layer_name", layer_name);
cmd.Parameters.Add("@geoJSON", System.Data.SqlDbType.NVarChar, -1);
cmd.Parameters["@geoJSON"].Direction = System.Data.ParameterDirection.Output;
string geoJSON = "";
try
{
conn.Open();
int i = cmd.ExecuteNonQuery();
geoJSON = Convert.ToString(cmd.Parameters["@geoJSON"].Value);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(Content(geoJSON, "application/json; charset=utf-8"));
}
public ActionResult Delete(string layer_name, int fid)
{
System.Data.Common.DbConnection conn = _context.Database.GetDbConnection();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//cmd.CommandText = "dbo.delete_feature";
cmd.CommandText = "dbo.delete_feature_3";
cmd.Connection = (SqlConnection)conn;
//cmd.Parameters.AddWithValue("@table_name", layer_name);
cmd.Parameters.AddWithValue("@fid", fid);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(Content("Success"));
}
public WorkspaceInstance()
{
if (System.ComponentModel.DesignerProperties.GetIsInDesignMode(new DependencyObject()))
{
Workspace.LoadInstance(this);
}
else
{
System.Data.Common.DbConnection connection = null;
try {
connection = ((IObjectContextAdapter)Workspace.NorthwindModel).ObjectContext.Connection;
connection.Open();
if (connection.State.HasFlag(System.Data.ConnectionState.Open))
{
_Employees = Workspace.Employees;
_Orders = Workspace.Orders;
_CustomersTreeList = Workspace.CustomersTreeList;
_Suppliers = Workspace.Suppliers;
_Customers = Workspace.Customers;
_Categories = Workspace.Categories;
_Products = Workspace.Products;
}
}
catch (Exception ex) {
App.LogException(ex);
Workspace.LoadInstance(this);
}
finally {
if (connection != null && connection.State.HasFlag(System.Data.ConnectionState.Open))
{
connection.Close();
}
}
}
}
/// <summary>
/// Closes a connection, swallowing all exceptions.
/// </summary>
/// <param name="connection">The connection to close.</param>
protected override void CloseConnection(System.Data.Common.DbConnection connection)
{
try {
connection.Close();
}
catch { }
}
public static bool SendClaimCodesList(CouponItemInfo couponItemInfo, int count)
{
bool result = false;
Database database = DatabaseFactory.CreateDatabase();
using (System.Data.Common.DbConnection dbConnection = database.CreateConnection())
{
dbConnection.Open();
System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction();
try
{
for (int i = 0; i < count; i++)
{
new CouponDao().SendClaimCodes(couponItemInfo, dbTransaction);
}
}
catch
{
dbTransaction.Rollback();
result = false;
}
finally
{
dbConnection.Close();
}
result = true;
}
return(result);
}
/// <summary>
/// [可重入]关闭连接
/// </summary>
/// <param name="Connection"></param>
public static void Close(System.Data.Common.DbConnection Connection)
{
if (Connection != null && Connection.State != System.Data.ConnectionState.Closed)
{
Connection.Close();
}
}
bool TestConnection(System.Data.Common.DbConnection connection)
{
try
{
connection.Open();
}
catch (System.Data.SqlClient.SqlException e)
{
if (e.Number == 53)
{
throw new Exception("Could not connect to the server. Please verify that the server information is correct and try again. If you are connecting to a remote server, please verify that you are properly connected to the internet.", e);
}
else if (e.Number == 258)
{
throw new Exception("Could not connect to the server using this port number. Please verify that this information is correct and try again.", e);
}
else if (e.Number == 18456)
{
throw new Exception("There was a problem with your user ID and/or password. Please verify that this information is correct and try again.", e);
}
else if (e.Number == 4060)
{
throw new Exception("There was a problem with the database name. Please verify that this information is correct and try again.", e);
}
else
{
throw;
}
}
finally
{
connection.Close();
}
return(true);
}
/// <summary>
/// Fills the wrongValues-table with all wrong values from the rule
/// </summary>
public void Validate(System.Data.Common.DbConnection connection, SqlServerValidationToolkitContext ctx)
{
if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
try
{
if (!IsActive)
{
SetAllWrongValuesToCorrected(connection);
}
else
{
UpdateWrongValues(connection, ctx);
}
}
catch (Exception e)
{
throw new Exception(string.Format("Exception occurred while executing '{0}'", CompiledQuery), e);
}
finally
{
connection.Close();
}
}
public static void ExecuteNonQuery(System.Data.IDbCommand cmd)
{
using (System.Data.Common.DbConnection conn = GetConnection())
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
using (System.Data.Common.DbTransaction transact = conn.BeginTransaction())
{
cmd.Transaction = transact;
try
{
cmd.ExecuteNonQuery();
transact.Commit();
} // End Try
catch (System.Exception)
{
transact.Rollback();
throw;
} // End Catch
finally
{
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
} // End Finally
} // End Using transact
} // End Using conn
} // End Sub
public void CloseConnection(System.Data.Common.DbConnection connect)
{
if (connect.State != System.Data.ConnectionState.Closed)
{
connect.Close();
}
}
public void Dispose()
{
if (InnerConnection != null && InnerConnection.State != ConnectionState.Closed)
{
InnerConnection.Close();
}
}
public static bool SendClaimCodes(IList <CouponItemInfo> listCouponItem)
{
bool Flag = false;
Database database = DatabaseFactory.CreateDatabase();
using (System.Data.Common.DbConnection dbCon = database.CreateConnection())
{
dbCon.Open();
System.Data.Common.DbTransaction dbTran = dbCon.BeginTransaction();
try
{
new CouponDao().SendClaimCodesByTran(listCouponItem, dbTran);
}
catch
{
dbTran.Rollback();
Flag = false;
}
finally
{
dbCon.Close();
}
Flag = true;
}
return(Flag);
}
/// <summary>
/// Insert Data
/// </summary>
/// <param name="pFileString">File String</param>
/// <param name="pSelectSQL">Select SQL</param>
/// <param name="pDataReader">Data Reader</param>
/// <returns>bool</returns>
public static bool InsertData(string pFileString, string pSelectSQL, System.Data.Common.DbDataReader pDataReader)
{
bool result = false;
System.Data.Common.DbConnection Conn = null;
System.Data.Common.DbDataAdapter Adapter = null;
System.Data.Common.DbCommandBuilder builder = null;
System.Data.Common.DbCommand cmd = null;
DataSet dataSet = new DataSet();
DataTable Temp = new DataTable();
string connString = ParseConnectionString(pFileString);
if (DataSource != null)
{
IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
driver.ConnectionString = connString;
Conn = (System.Data.Common.DbConnection)driver.GetConnection();
Adapter = driver.GetDbAdapter(pSelectSQL);
Adapter.FillSchema(dataSet, SchemaType.Source);
Adapter.Fill(Temp);
builder = driver.GetDbCommandBuilder(Adapter);
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
try
{
Conn.Open();
cmd = builder.GetInsertCommand(true);
cmd.CommandTimeout = 1500;
cmd.Connection = Conn;
//builder.GetInsertCommand((true);
while (pDataReader.Read())
{
foreach (System.Data.Common.DbParameter param in cmd.Parameters)
{
//string FieldName = param.ParameterName.TrimStart(new char[] { '@' });
string FieldName = param.SourceColumn;
if (FieldName.ToUpperInvariant() == "UNIQUEKEY")
{
param.Value = null;
}
else
{
param.Value = pDataReader[FieldName];
}
}
cmd.ExecuteNonQuery();
}
}
finally
{
Conn.Close();
}
}
result = true;
return(result);
}
// AnySqlWebAdmin.Code.Trash.PgIntegratedSecurityTest.TestNpgSql();
public static void TestNpgSql()
{
var a = new { Test = 5, Result = "Success" };
var b = new { Test = 3, Result = "foo" };
var c = new { Test1 = 3, Result = "foo" };
System.Type t = a.GetType();
System.Console.WriteLine(t);
if (object.ReferenceEquals(a.GetType(), b.GetType()))
{
System.Console.WriteLine("Two anony = equal");
}
Npgsql.NpgsqlConnectionStringBuilder csb = new Npgsql.NpgsqlConnectionStringBuilder();
csb.Database = "osm_test"; // must be set
csb.Host = "localhost";
// csb.Host = "127.0.0.1"; // doesn't work
// csb.Host = System.Environment.MachineName; // doesn't work
csb.Port = 5432;
csb.IntegratedSecurity = true;
csb.Username = System.Environment.UserName; // Works when user exists
// csb.Username = "******"; // works as root
bool ret = false;
string sql = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public'; ";
using (System.Data.Common.DbConnection conn = Npgsql.NpgsqlFactory.Instance.CreateConnection())
{
conn.ConnectionString = csb.ConnectionString;
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
using (System.Data.Common.DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
int?foo = (int?)cmd.ExecuteScalar();
if (foo.HasValue)
{
ret = foo.Value != 0;
}
} // End Using cmd
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
} // End Using conn
System.Console.WriteLine(ret);
} // End Sub TestNpgSql
public static byte[] GetFlagFromDb(string iso)
{
byte[] baResult = null;
using (System.Data.Common.DbConnection con = GetConnection())
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT SUBSTRING(b64, 23, 40000000000000000) AS b64 FROM flags WHERE flag = @iso";
System.Data.Common.DbParameter country = cmd.CreateParameter();
country.ParameterName = "@iso";
country.Value = iso;
country.DbType = System.Data.DbType.String;
cmd.Parameters.Add(country);
object obj = cmd.ExecuteScalar();
string b64 = System.Convert.ToString(obj);
baResult = System.Convert.FromBase64String(b64);
} // End Using cmd
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End Using con
return(baResult);
}
/// <summary>
/// Closes the connection.
/// </summary>
/// <param name="connection">The connection.</param>
public void CloseDbConnection(System.Data.Common.DbConnection connection)
{
try {
connection.Close();
}
catch { }
}
private DataTable RunSQLReturnTable(string sql, System.Data.Common.DbConnection conn, string dsn, CommandType cmdType)
{
if (conn is System.Data.SqlClient.SqlConnection)
{
return(BP.DA.DBAccess.RunSQLReturnTable(sql, (System.Data.SqlClient.SqlConnection)conn, dsn, cmdType));
}
if (conn is System.Data.OleDb.OleDbConnection)
{
return(BP.DA.DBAccess.RunSQLReturnTable(sql, (System.Data.OleDb.OleDbConnection)conn, cmdType));
}
if (conn is System.Data.OracleClient.OracleConnection)
{
return(BP.DA.DBAccess.RunSQLReturnTable(sql, (System.Data.OracleClient.OracleConnection)conn, cmdType, dsn));
}
if (conn is MySqlConnection)
{
var mySqlConn = (MySqlConnection)conn;
if (mySqlConn.State != ConnectionState.Open)
{
mySqlConn.Open();
}
var ada = new MySqlDataAdapter(sql, mySqlConn);
ada.SelectCommand.CommandType = CommandType.Text;
try
{
DataTable oratb = new DataTable("otb");
ada.Fill(oratb);
ada.Dispose();
conn.Close();
conn.Dispose();
return(oratb);
}
catch (Exception ex)
{
ada.Dispose();
conn.Close();
throw new Exception("SQL=" + sql + " Exception=" + ex.Message);
}
}
throw new Exception("没有涉及到的连接测试类型...");
return(null);
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close()
{
if (Conn != null)
{
Conn.Close();
Conn.Dispose();
}
}
private static bool CheckDB(object obj)
{
System.Data.Common.DbConnection conn_inst = (System.Data.Common.DbConnection)obj;
try
{
conn_inst.Open();
conn_inst.Close();
return(true);
}
catch
{
return(false);
}
finally
{
conn_inst.Close();
}
}
/// <summary>
///
/// </summary>
/// <param name="pFileString"></param>
/// <param name="pTableName"></param>
/// <param name="pIsConnectionString"></param>
/// <returns></returns>
public override bool CheckDatabaseExistance(string pFileString, string pTableName, bool pIsConnectionString = false)
{
if (pIsConnectionString)
{
System.Data.Common.DbConnection Conn = null;
//System.Data.Common.DbDataAdapter Adapter = null;
System.Data.DataTable DataTable = new System.Data.DataTable();
bool result = false;
string connString = pFileString;
string[] restrictions = new string[] { null, null, pTableName };
if (DataSource != null)
{
IDbDriver driver = this;
driver.ConnectionString = connString;
Conn = (System.Data.Common.DbConnection)driver.GetConnection();
try
{
Conn.Open();
DataTable = Conn.GetSchema("Tables", restrictions);
if (DataTable.Rows.Count == 0)
{
//Table does not exist
result = false;
}
else
{
//Table exists
result = true;
}
}
catch (Exception ex)
{
// do nothing
}
finally
{
Conn.Close();
}
}
return(result);
}
else
{
return(System.IO.File.Exists(pFileString));
}
}
private void CloseConnection(bool closeNonPersistent = false)
{
try
{
if (closeNonPersistent && UsePersistentConnection && PersistentConnection != null)
{
PersistentConnection.Close();
PersistentConnection = null;
}
}
catch { }
}
/// <summary>
/// Insert Data
/// </summary>
/// <param name="pFileString">File String</param>
/// <param name="pSelectSQL">Select Statement</param>
/// <param name="pDataRows">DataRows</param>
/// <returns>bool</returns>
public static bool InsertData(string pFileString, string pSelectSQL, System.Data.DataRow[] pDataRows)
{
bool result = false;
System.Data.Common.DbConnection Conn = null;
System.Data.Common.DbDataAdapter Adapter = null;
System.Data.Common.DbCommandBuilder builder = null;
System.Data.Common.DbCommand cmd = null;
DataSet dataSet = new DataSet();
DataTable Temp = new DataTable();
string connString = ParseConnectionString(pFileString);
if (DataSource != null)
{
IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
driver.ConnectionString = connString;
Conn = (System.Data.Common.DbConnection)driver.GetConnection();
Adapter = driver.GetDbAdapter(pSelectSQL);
Adapter.FillSchema(dataSet, SchemaType.Source);
Adapter.Fill(Temp);
builder = driver.GetDbCommandBuilder(Adapter);
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
try
{
Conn.Open();
//cmd = builder.GetInsertCommand(true);
cmd = Conn.CreateCommand();
cmd.CommandTimeout = 1500;
//builder.GetInsertCommand((true);
foreach (DataRow R in pDataRows)
{
//string SQL = GetInsertSQL(pSelectSQL.Substring(pSelectSQL.LastIndexOf (" From ") + 6, pSelectSQL.Length - pSelectSQL.LastIndexOf(" From ") - 6),R);
string SQL = GetInsertSQL(R.Table.TableName, R);
cmd.CommandText = SQL;
cmd.ExecuteNonQuery();
}
}
finally
{
Conn.Close();
}
}
result = true;
return(result);
}
public ConnectionServerStateEventArgs(System.Data.Common.DbConnection connection)
{
if (connection != null)
{
if (connection.State == System.Data.ConnectionState.Open)
{
connectionMessage = "Connection Status: OPEN";
statusConnection = ConnectionServerStateEnum.Open;
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
try
{
connection.Open();
statusConnection = ConnectionServerStateEnum.Open;
connectionMessage = "Connection Status Changed to: CLOSED";
}
catch (Exception e)
{
connection.Close();
statusConnection = ConnectionServerStateEnum.Closed;
throw new Exception(e.Message);
}
}
else
{
statusConnection = ConnectionServerStateEnum.Unknown;
connectionMessage = "Connection Status: UNKNOWN";
}
}
else
{
connection.Close();
statusConnection = ConnectionServerStateEnum.Closed;
connectionMessage = "Connection Status: CLOSED.";
}
}
public virtual DataTable GetSchema()
{
using (System.Data.Common.DbConnection conn = (System.Data.Common.DbConnection)_dialect.CreateConnection(_ConnectionString))
{
conn.Open();
try
{
return(conn.GetSchema());
}
finally
{
conn.Close();
}
}
}
public static void InsertList <T>(System.Data.IDbCommand cmd
, System.Collections.Generic.IEnumerable <T> listToInsert
, callbackAddData_t <T> addDataCallback)
{
using (System.Data.Common.DbConnection conn = GetConnection())
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
using (System.Data.Common.DbTransaction transact = conn.BeginTransaction())
{
cmd.Transaction = transact;
try
{
foreach (T thisItem in listToInsert)
{
addDataCallback(cmd, thisItem);
if (cmd.ExecuteNonQuery() != 1)
{
//'handled as needed,
//' but this snippet will throw an exception to force a rollback
throw new System.InvalidProgramException();
}
} // Next thisObject
transact.Commit();
} // End Try
catch (System.Exception)
{
transact.Rollback();
throw;
} // End Catch
finally
{
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
} // End Finally
} // End Using transact
} // End Using conn
} // End Sub
bool TestConnection(System.Data.Common.DbConnection connection)
{
try
{
connection.Open();
}
catch
{
throw;
}
finally
{
connection.Close();
}
return(true);
}
/// <summary>
///
/// </summary>
/// <param name="pFileString"></param>
/// <param name="pTableName"></param>
/// <param name="pIsConnectionString"></param>
/// <returns></returns>
public override bool CheckDatabaseExistance(string pFileString, string pTableName, bool pIsConnectionString = false)
{
System.Data.Common.DbConnection Conn = null;
//System.Data.Common.DbDataAdapter Adapter = null;
System.Data.DataTable DataTable = new System.Data.DataTable();
bool result = false;
string connString = pFileString;
string[] restrictions = new string[] { null, null, pTableName };
if (DataSource != null)
{
IDbDriver driver = this;
driver.ConnectionString = connString;
Conn = (System.Data.Common.DbConnection)driver.GetConnection();
try
{
Conn.Open();
Conn.ChangeDatabase("master");
System.Data.Common.DbCommand cmd = Conn.CreateCommand();
cmd.CommandText = "SELECT database_id FROM sys.databases WHERE Name = @name";
System.Data.Common.DbParameter parameter = cmd.CreateParameter();
parameter.ParameterName = "@name";
parameter.Value = driver.DbName;
cmd.Parameters.Add(parameter);
object ret = cmd.ExecuteScalar();
if (ret != null)
{
int databaseID = (int)ret;
result = databaseID > 0;
}
}
catch (Exception ex)
{
// do nothing
}
finally
{
Conn.Close();
}
}
return(result);
}
