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