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