// execute a select command, return single result as a string
public static string ExecuteScalar(DbCommand command)
{
string result = "";
command.Connection.Open();
result = command.ExecuteScalar().ToString();
return result;
}
public object ExecuteWithChanges(DbCommand cmd, ChangeInfo changeInfo)
{
object result;
using (var conn = DB.OpenConnection())
using (var tx = conn.BeginTransaction())
{
try
{
cmd.Connection = conn;
cmd.Transaction = tx;
result = cmd.ExecuteScalar();
if (AdminInitialise.IsChangesEnabled)
{
var changeCmd = CreateChangeCommand(changeInfo, result.ToString());
changeCmd.Connection = conn;
changeCmd.Transaction = tx;
changeCmd.ExecuteNonQuery();
}
tx.Commit();
}
catch (Exception ex)
{
result = null;
_notificator.Error(ex.Message);
tx.Rollback();
}
}
return result;
}
/// <summary>
/// Executes a command and returns the scalar result
/// </summary>
/// <typeparam name="T">Type of scalar result to return</typeparam>
/// <returns>The scalar result of executing the command</returns>
public T ExecuteScalar <T>()
{
using (db.DbCommand cmd = ToDbCommand())
{
return((T)cmd.ExecuteScalar());
}
}
/// <summary>
/// Executes a query that returns a single value.
/// </summary>
/// <param name="command">The raw command object.</param>
/// <returns>The return value as an object.</returns>
public string ExecuteScalarQueryToString(System.Data.Common.DbCommand command)
{
command.Connection = Conn;
string returnVal = "";
command.CommandTimeout = 0;
try
{
if (ConnectionState.Closed == Conn.State)
{
Conn.Open();
}
returnVal = command.ExecuteScalar().ToString();
}
finally
{
if (ConnectionState.Open == Conn.State)
{
Conn.Close();
}
}
return(returnVal);
}
public int ExecuteScalarQuery(System.Data.Common.DbCommand command)
{
command.Connection = Conn;
int returnVal = 0;
command.CommandTimeout = 0;
try
{
if (ConnectionState.Closed == Conn.State)
{
Conn.Open();
}
returnVal = Convert.ToInt32(command.ExecuteScalar());
}
finally
{
if (ConnectionState.Open == Conn.State)
{
Conn.Close();
}
}
return(returnVal);
}
/// <summary>
/// Executes a query that returns a single value.
/// </summary>
/// <param name="command">The raw command object.</param>
/// <returns>The return value as an object.</returns>
public bool ExecuteScalarBoolean(System.Data.Common.DbCommand command)
{
command.Connection = Conn;
bool returnVal = false;
command.CommandTimeout = 0;
try
{
if (ConnectionState.Closed == Conn.State)
{
Conn.Open();
}
returnVal = Convert.ToBoolean(command.ExecuteScalar());
}
finally
{
if (ConnectionState.Open == Conn.State)
{
Conn.Close();
}
}
return(returnVal);
}
public override LastInsertId ExecuteInsert(DbCommand cmd, string idKey)
{
cmd.Execute();
cmd.Reset();
cmd.CommandText = "select @@IDENTITY as id";
var rez = cmd.ExecuteScalar();
return new LastInsertId(rez);
}
public override LastInsertId ExecuteInsert(DbCommand cmd, string idKey)
{
if (!string.IsNullOrEmpty(idKey))
{
cmd.CommandText += (" returning " + EscapeName(idKey));
}
return new LastInsertId(cmd.ExecuteScalar());
}
public static int ExecuteScalar(DbCommand comm)
{
comm.Connection.Open();
int i = Convert.ToInt32(comm.ExecuteScalar());
comm.Connection.Close();
return i;
}
private static long GetTheCurrentVersionFromTheDatabase(DbCommand cmd, string sqlStatement)
{
long currentVersion;
cmd.CommandText = sqlStatement;
var version = cmd.ExecuteScalar<string>();
if (version != null)
version = version.Trim();
long.TryParse(version, out currentVersion);
return currentVersion;
}
public object ExecScalar(DbCommand cm)
{
//OnExecuting(ExecTypes.Scalar, cm);
object r = cm.ExecuteScalar();
//OnExecuted(ExecTypes.Scalar, cm, r);
return r;
}
public virtual object ExecuteScalar(System.Data.Common.DbCommand command, System.Data.Common.DbTransaction dbTransaction)
{
if (command == null)
{
return(null);
}
PrepareCommand(command, dbTransaction);
return(command.ExecuteScalar());
}
public override LastInsertId ExecuteInsert(DbCommand cmd, string idKey)
{
if (idKey != null)
{
cmd.CommandText += ";SELECT last_insert_rowid()";
var rez = cmd.ExecuteScalar();
return new LastInsertId(rez);
}
cmd.Execute();
return LastInsertId.Empty;
}
protected override object ExecuteResultInternal(IDbExecuteContext ctx, DbCommand command, Type resultType, object resultInstance)
{
var type = this.GetResultType(ctx, resultType, resultInstance, null);
if(type != null && !(type.IsValueType || type == typeof(string))) {
throw new DataException("要求返回的类型不正确");
}
var result = command.ExecuteScalar();
if(!Convert.IsDBNull(result) && result != null && type != null) {
var ut = Nullable.GetUnderlyingType(type) ?? type;
result = Convert.ChangeType(result, ut);
}
return Convert.IsDBNull(result) ? null : result;
}
public virtual object ExecuteScalar(System.Data.Common.DbCommand command)
{
if (command == null)
{
return(null);
}
using (var conn = GetWrapperedConnection())
{
PrepareCommand(command, conn);
return(command.ExecuteScalar());
}
}
public object ExecuteWithChanges(
DbCommand cmd,
EntityRecord entityRecord,
EntityChangeType changeType,
Func<string> changeDescriber = null)
{
_log.DebugFormat("Executing command: \r\n {0}", cmd.CommandText);
object result;
using (var conn = DB.OpenConnection(_admin.ConnectionStringName))
using (var tx = conn.BeginTransaction())
{
try
{
cmd.Connection = conn;
cmd.Transaction = tx;
result = cmd.ExecuteScalar();
if (result != null)
{
if (_admin.IsChangesEnabled)
{
var changeCmd = CreateChangeCommand(entityRecord, changeType, result.ToString(), changeDescriber);
_log.DebugFormat("Executing change command: \r\n {0}", changeCmd.CommandText);
changeCmd.Connection = conn;
changeCmd.Transaction = tx;
changeCmd.ExecuteNonQuery();
}
_log.Debug("Commit transaction");
tx.Commit();
}
else
{
Rollback(tx);
}
}
catch (Exception ex)
{
_log.Error(ex);
Rollback(tx);
throw;
}
}
return result;
}
public static void ExecuteScalarCommand(DbCommand command)
{
try
{
command.Connection.Open();
command.ExecuteScalar();
}
catch (Exception ex)
{
Utilities.LogError(ex);
throw;
}
finally
{
command.Connection.Close();
}
}
public static string ExcuteScalar(DbCommand cmd)
{
string value = "";
try
{
cmd.Connection.Open();
value = cmd.ExecuteScalar().ToString();
}
catch (Exception ex)
{
Utilities.LogError(ex);
throw;
}
finally { cmd.Connection.Close(); }
return value;
}
// execute a select command and return a single result as a string
public static string ExecuteScalar(DbCommand command)
{
string value = "";
try
{
command.Connection.Open();
value = command.ExecuteScalar().ToString();
}
catch (Exception ex)
{
throw ex;
}
finally
{
command.Connection.Close();
}
return value;
}
public static string ExecuteScalar(DbCommand comm)
{
string retrievedRows = "";
try
{
comm.Connection.Open();
retrievedRows = comm.ExecuteScalar().ToString();
}
catch (Exception ex)
{
throw;
}
finally
{
comm.Connection.Close();
}
return retrievedRows;
}
private string CreateAdministrator(string connectionstr)
{
System.Data.Common.DbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionstr);
dbConnection.Open();
System.Data.Common.DbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.Connection = dbConnection;
dbCommand.CommandType = System.Data.CommandType.Text;
dbCommand.CommandText = "INSERT INTO aspnet_Roles(RoleName,IsDefault) VALUES('超级管理员',1); SELECT @@IDENTITY";
int num = System.Convert.ToInt32(dbCommand.ExecuteScalar());
dbCommand.CommandText = "INSERT INTO aspnet_Managers(RoleId, UserName, Password, Email, CreateDate) VALUES (@RoleId, @UserName, @Password, @Email, getdate())";
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RoleId", num));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Username", "admin"));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Password", HiCryptographer.Md5Encrypt("123456")));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Email", "*****@*****.**"));
dbCommand.ExecuteNonQuery();
dbConnection.Close();
return("");
}
public int ExecuteInsertAndReturnId(DbCommand command)
{
DbConnection connection = null;
try
{
connection = CreateOpenConnection();
command.Connection = connection;
command.ExecuteNonQuery();
command.Parameters.Clear();
command.CommandText = "SELECT @@IDENTITY";
return Convert.ToInt32(command.ExecuteScalar());
//return
}
finally // is executed in case of exceptions as well
{
ReleaseConnection(connection);
}
}
private bool CreateAdministrator(out string errorMsg)
{
System.Data.Common.DbConnection dbConnection = new System.Data.SqlClient.SqlConnection(this.GetConnectionString());
dbConnection.Open();
System.Data.Common.DbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.Connection = dbConnection;
dbCommand.CommandType = System.Data.CommandType.Text;
dbCommand.CommandText = "INSERT INTO aspnet_Roles(RoleName,IsDefault) VALUES('超级管理员',1); SELECT @@IDENTITY";
int num = System.Convert.ToInt32(dbCommand.ExecuteScalar());
dbCommand.CommandText = "INSERT INTO aspnet_Managers(RoleId, UserName, Password, Email, CreateDate) VALUES (@RoleId, @UserName, @Password, @Email, getdate())";
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RoleId", num));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Username", this.username));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Password", HiCryptographer.Md5Encrypt(this.password)));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Email", this.email));
dbCommand.ExecuteNonQuery();
dbConnection.Close();
errorMsg = null;
return(true);
}
/// <summary>
/// 返回第一列第一行
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
public static string ExecuteScalar(DbCommand command)
{
string value = "";
try
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
value = command.ExecuteScalar().ToString();
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
command.Connection.Close();
}
return value;
}
private bool CreateAdministrator(out int newUserId, out string errorMsg)
{
System.Data.Common.DbConnection dbConnection = null;
System.Data.Common.DbTransaction dbTransaction = null;
bool result;
try
{
System.Data.Common.DbConnection dbConnection2;
dbConnection = (dbConnection2 = new System.Data.SqlClient.SqlConnection(this.GetConnectionString()));
try
{
dbConnection.Open();
RolesConfiguration rolesConfiguration = HiConfiguration.GetConfig().RolesConfiguration;
System.Data.Common.DbCommand dbCommand = dbConnection.CreateCommand();
dbTransaction = dbConnection.BeginTransaction();
dbCommand.Connection = dbConnection;
dbCommand.Transaction = dbTransaction;
dbCommand.CommandType = System.Data.CommandType.Text;
dbCommand.CommandText = "SELECT RoleId FROM aspnet_Roles WHERE [LoweredRoleName] = LOWER(@RoleName)";
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RoleName", rolesConfiguration.SystemAdministrator));
System.Guid guid = (System.Guid)dbCommand.ExecuteScalar();
dbCommand.Parameters["@RoleName"].Value = rolesConfiguration.Manager;
System.Guid guid2 = (System.Guid)dbCommand.ExecuteScalar();
dbCommand.Parameters.Clear();
dbCommand.CommandText = "INSERT INTO aspnet_Users (UserName, LoweredUserName, IsAnonymous, UserRole, LastActivityDate, Password, PasswordFormat, PasswordSalt, IsApproved, IsLockedOut, CreateDate, LastLoginDate, LastPasswordChangedDate, LastLockoutDate, FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart, Email, LoweredEmail) VALUES (@Username, LOWER(@Username), 0, @UserRole, @CreateDate, @Password, @PasswordFormat, @PasswordSalt, 1, 0, @CreateDate, @CreateDate, @CreateDate, CONVERT( datetime, '17540101', 112 ), 0, CONVERT( datetime, '17540101', 112 ), 0, CONVERT( datetime, '17540101', 112 ), @Email, LOWER(@Email));SELECT @@IDENTITY";
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Username", this.username));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserRole", UserRole.SiteManager));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CreateDate", System.DateTime.Now));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Password", this.password));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PasswordFormat", System.Web.Security.MembershipPasswordFormat.Clear));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PasswordSalt", ""));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Email", this.email));
newUserId = System.Convert.ToInt32(dbCommand.ExecuteScalar());
dbCommand.Parameters.Clear();
dbCommand.CommandText = "INSERT INTO aspnet_Managers(UserId) VALUES(@UserId)";
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserId", newUserId));
dbCommand.ExecuteNonQuery();
dbCommand.CommandText = "INSERT INTO aspnet_UsersInRoles(UserId, RoleId) VALUES(@UserId, @RoleId)";
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RoleId", guid2));
dbCommand.ExecuteNonQuery();
dbCommand.Parameters["@RoleId"].Value = guid;
dbCommand.ExecuteNonQuery();
dbTransaction.Commit();
dbConnection.Close();
}
finally
{
if (dbConnection2 != null)
{
((System.IDisposable)dbConnection2).Dispose();
}
}
errorMsg = null;
result = true;
}
catch (System.Data.SqlClient.SqlException ex)
{
errorMsg = ex.Message;
newUserId = 0;
if (dbTransaction != null)
{
try
{
dbTransaction.Rollback();
}
catch (System.Exception ex2)
{
errorMsg = ex2.Message;
}
}
if (dbConnection != null && dbConnection.State != System.Data.ConnectionState.Closed)
{
dbConnection.Close();
dbConnection.Dispose();
}
result = false;
}
return(result);
}
public object ExecuteScalarCommand(System.Data.Common.DbCommand com, System.Data.Common.DbTransaction trans)
{
Connect();
//com.Transaction = trans;
return(com.ExecuteScalar());
}
/// <summary>
/// 执行sql或者存储过程返回一个object对象+事务
/// </summary>
/// <param name="cmd">cmd对象</param>
/// <param name="t">事务执行操作</param>
/// <returns></returns>
public object ExecuteScalar(DbCommand cmd, Trans t)
{
cmd.Connection.Close();
cmd.Connection = t.DbConnection;
cmd.Transaction = t.DbTrans;
object ret = cmd.ExecuteScalar();
return ret;
}
private object DoExecuteScalar(DbCommand command)
{
if (IsBatchConnection && batchCommander.batchSize > 1)
{
batchCommander.Process(command);
return null;
}
try
{
WriteLog(command);
object returnValue = command.ExecuteScalar();
return returnValue;
}
catch
{
throw;
}
finally
{
CloseConnection(command);
}
}
/// <summary>
/// Execute a select command and return a single result as a string
/// </summary>
/// <param name="command">database command</param>
public static string ExecuteScalar(DbCommand command)
{
var value = string.Empty;
using (command)
{
try
{
command.Connection.Open();
var result = command.ExecuteScalar();
value = result != null ? Convert.ToString(result) : string.Empty;
}
catch (Exception ex)
{
Utilities.LogError(ex);
}
finally
{
if (command != null)
{
command.Parameters.Clear();
command.Connection.Close();
}
}
return value;
}
}
public string FbExecuteScalar(string sql)
{
string output;
try
{
OpenFbData();
//Usirng detroy value of time out
Com = GetCommandDb(sql);
output = Convert.ToString(Com.ExecuteScalar());
}
finally
{
ReleaseResource();
}
return output;
}
public virtual object ExecuteScalar(DbCommand dm)
{
using (DbConnection conn = CreateConnection())
{
dm.Connection = conn;
if (dm.CommandType == CommandType.Text)
dm.CommandText = StrSetIsoLevelReadUnCommited + dm.CommandText;
return dm.ExecuteScalar();
}
}
public object EjecutarValor(DbCommand cmdProxy, DbConnection conProxy, DbTransaction trnProxy)
{
cmdProxy.Connection = conProxy;
cmdProxy.Transaction = trnProxy;
return cmdProxy.ExecuteScalar();
}
// execute a select command and return a single result as a string
public static string ExecuteScalar(DbCommand command)
{
// The value to be returned
string value = "";
// Execute the command making sure the connection gets closed in the end
try
{
// Open the connection of the command
command.Connection.Open();
// Execute the command and get the number of affected rows
value = command.ExecuteScalar().ToString();
}
catch (Exception ex)
{
// Log eventual errors and rethrow them
//Utilities.LogError(ex);
throw ex;
}
finally
{
// Close the connection
command.Connection.Close();
}
// return the result
return value;
}
private object DoExecuteScalar(DbCommand command)
{
try
{
return command.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
this.CloseConnection(command);
}
}
/// <summary>
/// 取得分页的SQL语句
/// </summary>
/// <param name="connection"></param>
/// <param name="cmd"></param>
/// <param name="SqlAllFields"></param>
/// <param name="SqlTablesAndWhere"></param>
/// <param name="IndexField"></param>
/// <param name="OrderFields"></param>
/// <param name="PageIndex"></param>
/// <param name="PageSize"></param>
/// <param name="RecordCount"></param>
/// <param name="PageCount"></param>
/// <returns></returns>
private static string GetPageSql(DbConnection connection, DbCommand cmd, string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount)
{
RecordCount = 0;
PageCount = 0;
if (PageSize <= 0)
{
PageSize = 10;
}
string SqlCount = "select count(" + IndexField + ") from " + SqlTablesAndWhere;
cmd.CommandText = SqlCount;
RecordCount = (int)cmd.ExecuteScalar();
if (RecordCount % PageSize == 0)
{
PageCount = RecordCount / PageSize;
}
else
{
PageCount = RecordCount / PageSize + 1;
}
if (PageIndex > PageCount)
PageIndex = PageCount;
if (PageIndex < 1)
PageIndex = 1;
string Sql = null;
if (PageIndex == 1)
{
Sql = "select top " + PageSize + " " + SqlAllFields + " from " + SqlTablesAndWhere + " " + OrderFields;
}
else
{
Sql = "select top " + PageSize + " " + SqlAllFields + " from ";
if (SqlTablesAndWhere.ToLower().IndexOf(" where ") > 0)
{
string _where = Regex.Replace(SqlTablesAndWhere, @"\ where\ ", " where (", RegexOptions.IgnoreCase | RegexOptions.Compiled);
Sql += _where + ") and (";
}
else
{
Sql += SqlTablesAndWhere + " where (";
}
Sql += IndexField + " not in (select top " + (PageIndex - 1) * PageSize + " " + IndexField + " from " + SqlTablesAndWhere + " " + OrderFields;
Sql += ")) " + OrderFields;
}
return Sql;
}
protected object ExecuteScalar(DbCommand cmd)
{
return cmd.ExecuteScalar();
}
public static object ExecuteScalar(DbCommand dbCommand)
{
if (dbConnection.State != ConnectionState.Open)
dbConnection.Open();
object o = dbCommand.ExecuteScalar();
dbConnection.Close();
return o;
}
public object ExecuteScalar(DbCommand cmd)
{
cmd.Connection.Open();
object ret = cmd.ExecuteScalar();
cmd.Connection.Close();
return ret;
}
public string FbExecuteScalar(string sql, List<IDataParameter> parms)
{
string output;
try
{
OpenFbData();
//Using not work wit transaction
Com = GetCommandDb(sql);
SetParameter(parms, ref Com);
output = Com.ExecuteScalar().ToString();
}
finally
{
ReleaseResource();
}
return output;
}
public static object SQLScalar(SQLiteConnectionED cn, DbCommand cmd)
{
object ret = null;
try
{
ret = cmd.ExecuteScalar();
return ret;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("SqlNonQuery Exception: " + ex.Message);
throw;
}
}
