private void OpenOrCreate() { System.Data.IDbCommand cmd = Connection.CreateCommand(); string version = null; try { cmd.CommandText = "Select Version from DbConfig"; version = cmd.ExecuteScalar().ToString(); } catch (Exception sqEx) { // System.Diagnostics.Debug.Print(sqEx.Message.ToString()); } if (String.IsNullOrEmpty(version)) { // no database exists, or database was deleted. create tables and populate Db.DbUpgrader upgrader = new Db.DbUpgrader(); upgrader.RunUpgradeScripts(Connection); int versionNumber; if (int.TryParse(cmd.ExecuteScalar().ToString(), out versionNumber)) { DatabaseVersion = versionNumber; } else { throw new ApplicationException("Error while accessing or creating the database"); } } else { int versionNumber = -1; int.TryParse(version, out versionNumber); DatabaseVersion = versionNumber; } }
/// <summary> /// Executes a DataBase Stored Procedure Command Scalar. Gets the first column of the first /// row in the result set returned by the query. Additional columns or rows are ignored. /// </summary> /// <param name="oFuncCmd">The DataBase Function Command</param> /// <returns>The DataBase Stored Procedure returns true or false</returns> public static bool ExecProcedureCommandScalar(System.Data.IDbCommand oCmd, out object oScalar) { long lRet = 0; bool bLocal = false; if (oCmd.Connection.State != System.Data.ConnectionState.Open) { oCmd.Connection.Open(); bLocal = true; } oScalar = oCmd.ExecuteScalar(); if (bLocal) { oCmd.Connection.Close(); } lRet = (int)X.Data.DB.DbFactory.GetParameterValue(oCmd, "RETURN_VALUE"); if (lRet > 0) { return(false); } return(true); }
public virtual long GenSequenceNumber(Context context) { if (context.PersistenceSession.Connection.State != ConnectionState.Open) { context.PersistenceSession.Connection.Open(); } if (context.PersistenceSession.Connection.State != ConnectionState.Open) { throw new iSabayaException("Can't open connection using the given session."); } System.Data.IDbConnection adoCon = context.PersistenceSession.Connection; System.Data.IDbCommand genSeqNoCommand = adoCon.CreateCommand(); genSeqNoCommand.CommandText = "declare @seqNo bigint; exec dbo.usp_GenSequenceNo " + ((int)context.MySystem.SystemID).ToString() + "," + SequenceType.ToString() + "," + SubsequenceType.ToString() + ", " + seed.ToString() + ", " + increment.ToString() + ",@seqNo output; select @seqNo"; if (context.PersistenceSession.Transaction.IsActive) { context.PersistenceSession.Transaction.Enlist(genSeqNoCommand); } if (genSeqNoCommand.Connection.State != ConnectionState.Open) { genSeqNoCommand.Connection.Open(); } return((long)genSeqNoCommand.ExecuteScalar()); }
public override int InsertRecord(System.Data.IDbCommand cmd) { try { if (connection.State == ConnectionState.Closed) { connection.Open(); } if (isInTransaction) { cmd.Transaction = (OracleTransaction)transaction; } cmd.Connection = connection; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT @@IDENTITY"; object obj = cmd.ExecuteScalar(); return(Convert.ToInt32(obj == DBNull.Value ? 0 : obj)); } catch (Exception e) { throw new Exception("操作数据库失败!参考:" + e.Message); } finally { if (connection.State != ConnectionState.Closed && isInTransaction == false) { connection.Close(); } cmd.Dispose(); cmd = null; } }
public override object GetDataResult(System.Data.IDbCommand cmd) { if (connection.State == ConnectionState.Closed) { connection.Open(); } try { object Result = null; if (isInTransaction) { cmd.Transaction = transaction; } cmd.Connection = connection; Result = cmd.ExecuteScalar(); return(Result); } catch (Exception e) { throw new Exception("操作数据库失败!参考:" + e.Message); } finally { if (connection.State != ConnectionState.Closed && isInTransaction == false) { connection.Close(); } cmd.Dispose(); cmd = null; } }
public DateTime GetLatestDate(ObjectType objType, string code) { using (var entity = new StockManDBEntities()) { string tableName = string.Format("data_{0}_{1}_latest", objType.ToString(), TechCycle.day); string where = string.Empty; string sql = string.Format(@"SELECT date FROM {0} where object_code='{1}' {2} order by date desc limit 1", tableName, code, where); entity.Database.Connection.Open(); using (entity.Database.Connection) { System.Data.IDbCommand commond = entity.Database.Connection.CreateCommand(); commond.CommandText = sql; object val = commond.ExecuteScalar(); entity.Database.Connection.Close(); if (string.IsNullOrEmpty(val + "")) { return(DateTime.Now); } return(DateTime.Parse(val + "")); } } }
/**/ /// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param> public void ExecuteSqlTran(Hashtable SQLStringList) { using (System.Data.IDbConnection iConn = this.GetConnection()) { iConn.Open(); using (IDbTransaction iTrans = iConn.BeginTransaction()) { System.Data.IDbCommand iCmd = GetCommand(); try { //循环 foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = myDE.Key.ToString(); IDataParameter[] iParms = (IDataParameter[])myDE.Value; PrepareCommand(out iCmd, iConn, iTrans, cmdText, iParms); int val = iCmd.ExecuteNonQuery(); iCmd.Parameters.Clear(); } iTrans.Commit(); } catch { iTrans.Rollback(); throw; } finally { iCmd.Dispose(); if (iConn.State != ConnectionState.Closed) { iConn.Close(); } } } } } /**/ /// <summary> /// 执行一条计算查询结果语句,返回查询结果(object)。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果(object)</returns> public object GetSingle(string SQLString, params IDataParameter[] iParms) { using (System.Data.IDbConnection iConn = this.GetConnection()) { System.Data.IDbCommand iCmd = GetCommand(); { try { PrepareCommand(out iCmd, iConn, null, SQLString, iParms); object obj = iCmd.ExecuteScalar(); iCmd.Parameters.Clear(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return(null); } else { return(obj); } } catch (System.Exception e) { throw new Exception(e.Message); } finally { iCmd.Dispose(); if (iConn.State != ConnectionState.Closed) { iConn.Close(); } } } } }
public bool UpdateFile(ServerUser user, MyFile thisFile) { string path = "/" + user.id + "/" + thisFile.name; DbCommand command_checkExists = dbConnection.CreateCommand(); command_checkExists.CommandText = "SELECT count(id) FROM files WHERE path='" + path + "'"; int checkFound = Convert.ToInt32(command_checkExists.ExecuteScalar()); DbCommand command = dbConnection.CreateCommand(); if (checkFound > 0) { command.CommandText = "UPDATE files SET modtime='" + thisFile.modtime + "' WHERE path='" + path + "'"; } else { // if the entry does not exist, insert it instead of updating it DbCommand command_getParent = dbConnection.CreateCommand(); command_getParent.CommandText = "SELECT id FROM files WHERE path='" + path.Substring(0, path.LastIndexOf('/')) + "'"; int parentId = Convert.ToInt32(command_getParent.ExecuteScalar()); command.CommandText = String.Format("INSERT INTO files (parent, path, size, modtime, type, `user`, checksum) " + "VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}')", parentId, path, thisFile.size, thisFile.modtime, thisFile.type, user.id, thisFile.checksum); } return(command.ExecuteNonQuery() == 1); }
/**/ /// <summary> /// 执行查询语句 /// </summary> /// <param name="SqlString">查询语句</param> /// <returns>DataTable </returns> public DataTable ExecuteQuery(string sqlString) { using (System.Data.IDbConnection iConn = this.GetConnection()) { //System.Data.IDbCommand iCmd = GetCommand(sqlString,iConn); DataSet ds = new DataSet(); try { System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString, iConn); iAdapter.Fill(ds); } catch (System.Exception e) { throw new Exception(e.Message); } finally { if (iConn.State != ConnectionState.Closed) { iConn.Close(); } } return(ds.Tables[0]); } } /**/ /// <summary> /// 执行一条计算查询结果语句,返回查询结果(object)。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果(object)</returns> public object GetSingle(string SqlString) { using (System.Data.IDbConnection iConn = GetConnection()) { using (System.Data.IDbCommand iCmd = GetCommand(SqlString, iConn)) { iConn.Open(); try { object obj = iCmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return(null); } else { return(obj); } } catch (System.Exception e) { throw new Exception(e.Message); } finally { if (iConn.State != ConnectionState.Closed) { iConn.Close(); } } } } }
protected virtual object QueryScalarExec(QueryContext query) { using (System.Data.IDbCommand cmd = this.InitCommand(query)) { return(cmd.ExecuteScalar()); } }
public object ExecuteScalar(string sSQL, CommandType oType) { ValidateConnection(); m_oCommand.CommandText = sSQL; m_oCommand.CommandType = oType; return(m_oCommand.ExecuteScalar()); }
public bool UseBlock(string hash, long size, System.Data.IDbTransaction transaction) { m_command.Transaction = transaction; m_command.SetParameterValue(0, hash); m_command.SetParameterValue(1, size); var r = m_command.ExecuteScalar(); return(r != null && r != DBNull.Value); }
public object ExecuteScalar(string sSQL, CommandType oType) { object o; ValidateConnection(); m_oCommand.CommandText = sSQL; m_oCommand.CommandType = oType; o = m_oCommand.ExecuteScalar(); Disconnect(); return(o); }
public void Execute(System.Data.IDbConnection MyConnection, string strquery) { //使用 CreateCommand() 方法生成 Command 对象 System.Data.IDbCommand MyCommand = MyConnection.CreateCommand(); //执行定义的SQL查询语句 MyCommand.CommandText = strquery; try { //打开数据库连接 MyConnection.Open(); //定义查询的结果信息 String MyInfo = "测试连接成功!符合查询要求的记录共有:" + MyCommand.ExecuteScalar().ToString() + "条!"; //输出查询结果信息 lblMessage.Text = MyInfo; DataSet ds = new DataSet(); //这里是你的数据,我就不写了 string str = "server='qds157513275.my3w.com,1433';database='qds157513275_db';uid='qds157513275';pwd='313801120'"; SqlConnection con = new SqlConnection(str); con.Open(); //打开连接 string strsql = "select * from admin"; //SQL查询语句 SqlCommand cmd = new SqlCommand(strsql, con); //初始化Command对象 SqlDataReader rd = cmd.ExecuteReader(); //初始化DataReader对象 while (rd.Read()) { Response.Write(rd[0].ToString() + "、" + rd["username"].ToString() + "<hr>"); //通过索引获取列 } Response.Write("打开数据库成功!" + ds.Tables["product1sdfasd"]); strsql = "select count(1) from xy_webdomain"; SqlCommand cmd = new SqlCommand(strsql, con); Response.Write("共" + cmd.ExecuteScalar() + "条"); } catch (Exception ex) { //输出错误异常 Response.Write(ex.ToString()); } finally { //关闭数据库连接 MyConnection.Close(); } }
/// <summary> /// Get the number of entries in the accounts table /// </summary> /// <returns></returns> public int UsersCount() { int count = 0; try { DbCommand command = dbConnection.CreateCommand(); command.CommandText = "select count(uid) from oc_users"; count = Convert.ToInt32(command.ExecuteScalar()); } catch (Exception) { return(-1); } return(count); }
/// <summary> /// Get the number of entries in the accounts table /// </summary> /// <returns></returns> public int UsersCount() { int count = 0; try { DbCommand command = dbConnection.CreateCommand(); command.CommandText = "SELECT COUNT(id) FROM users"; count = Convert.ToInt32(command.ExecuteScalar()); } catch (Exception) { return(-1); } return(count); }
/// <summary> /// Update or insert a new entry for the file into the database /// </summary> /// <returns> /// Flase if there was a problem during the update /// </returns> /// <param name='user'></param> /// <param name='thisFile'></param> public bool UpdateFile(ServerUser user, MyFile thisFile) { // TODO: use OwnCloud API calls if possible perhaps: http://owncloud.org/dev/apps/database/ string path = "/" + user.id + "/files/" + thisFile.name; string absPath = GetDataDir(user) + thisFile.name; //Server.baseDataDir + path; FileInfo f = new FileInfo(absPath); long mtime = Common.DateTimeToUnixTimestamp(f.LastWriteTimeUtc); DbCommand command_checkExists = dbConnection.CreateCommand(); command_checkExists.CommandText = "SELECT count(id) FROM oc_fscache WHERE path='" + path + "'"; int checkFound = Convert.ToInt32(command_checkExists.ExecuteScalar()); DbCommand command = dbConnection.CreateCommand(); if (checkFound > 0) { command.CommandText = "UPDATE oc_fscache SET mtime='" + mtime + "' WHERE path='" + path + "'"; } else { // if the entry does not exist, insert it instead of updating it long ctime = Common.DateTimeToUnixTimestamp(f.CreationTimeUtc); DbCommand command_getParent = dbConnection.CreateCommand(); command_getParent.CommandText = "SELECT id FROM oc_fscache WHERE path_hash='" + Common.Md5Hash(path.Substring(0, path.LastIndexOf('/'))) + "'"; int parentId = Convert.ToInt32(command_getParent.ExecuteScalar()); string mimetype = MIMEAssistant.GetMIMEType(f.Name); string mimepart = mimetype.Substring(0, mimetype.LastIndexOf('/')); bool writable = true; //!f.IsReadOnly; bool encrypted = false; // ? bool versioned = false; // ? command.CommandText = String.Format("INSERT INTO oc_fscache (parent, name, path, path_hash, size, mtime, ctime, mimetype, mimepart,`user`,writable,encrypted,versioned) " + "VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}')", parentId, f.Name, path, Common.Md5Hash(path), f.Length, mtime, ctime, mimetype, mimepart, user.id, writable, encrypted, versioned); } return(command.ExecuteNonQuery() == 1); }
public object ExecuteScalar(string commandText, IDataParameter[] parameters) { Connect(); Utility.WriteTrace(commandText); comm.Parameters.Clear(); comm.CommandText = commandText; if (parameters != null) { for (int loop = 0; loop < parameters.Length; loop++) { comm.Parameters.Add(parameters[loop]); } } return(comm.ExecuteScalar()); }
public object ExecuteScalar(string commandText, IDataParameter[] parameters) { Connect(); Logger.I(string.Concat(commandText, "\tPrm:", ParametersToString(parameters))); comm.Parameters.Clear(); comm.CommandText = commandText; if (parameters != null) { for (int loop = 0; loop < parameters.Length; loop++) { comm.Parameters.Add(parameters[loop]); } } return(comm.ExecuteScalar()); }
public virtual Object ExecuteScalar(System.Data.IDbCommand command) { try { Open(); Close(); return(command.ExecuteScalar()); } catch (Exception ex) { Stoca.Log.LogManager.GetLog().Error(Stoca.Common.ExceptionManager.EXCEPTION_EXECUTESCARLAR, ex, command); throw ex; } finally { Dispose(); } }
public T SelectScalar <T>(string selectCommand) { if (this.ReadOnly) { throw new InvalidOperationException("No se pueden realizar cambios en la conexión de lectura"); } if (this.IsOpen() == false) { this.Open(); } System.Data.IDbCommand Cmd = this.GetCommand(selectCommand); if (this.Trace) { Log.Debug(this.Handle.ToString() + ": " + Cmd.CommandText); } int Intentos = 3; while (true) { try { object Res = Cmd.ExecuteScalar(); if (Res == null) { return(default(T)); } else { return((T)Convert.ChangeType(Res, typeof(T))); } } catch (Exception ex) { if (this.TryToRecover(ex) || Intentos-- <= 0) { Log.Error(selectCommand, ex); ex.Data.Add("Command", selectCommand); throw ex; } } } }
//------------------------------------------------------------------------------------------------------------------------------------------------------- public object ExecuteScalar(string sSQL, CommandType oType) { ValidateConnection(); try { dbCommand.CommandText = sSQL; dbCommand.CommandType = oType; return(dbCommand.ExecuteScalar()); // } catch ( Exception ex ) { // dodgy connection, so reconnect } catch (Exception ex) { // dodgy connection, so reconnect this.errors = ex.ToString(); // m_bConnected = false; //Disconnect(); //ValidateConnection(); //m_oCommand.CommandText = sSQL; //m_oCommand.CommandType = oType; //return m_oCommand.ExecuteScalar(); } return(null); }
public object Scalar(string Query) { //This method should handle all scalar queries such as COUNT or AVG //Open connection to database. this.Open(); //Instantiate object to hold the result of the query. object result; using (System.Data.IDbCommand command = this._conn.CreateCommand()) { command.CommandText = Query; //Execute the query result = command.ExecuteScalar(); } //Close connection to database this.Close(); //return result object return(result); }
public int EjecutarConsultaEscalar(System.Data.IDbCommand command) { command.Connection = this.ValidarConnection(); command.Transaction = this.transaction; return(System.Convert.ToInt32(command.ExecuteScalar())); }