public object GetSingle(string sql, int commandTimeout = 0, params MySQLParameter[] cmdParms)
{
try
{
using (MySQLCommand cmd = new MySQLCommand(sql, this.DbConn))
{
if (commandTimeout > 0)
{
cmd.CommandTimeout = commandTimeout;
}
if (cmdParms.Length > 0)
{
MyDbConnection3.PrepareCommand(cmd, this.DbConn, null, sql, cmdParms);
}
object obj = cmd.ExecuteScalar();
if (cmdParms.Length > 0)
{
cmd.Parameters.Clear();
}
this.LogSql(sql);
if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
{
return(null);
}
return(obj);
}
}
catch (Exception ex)
{
LogManager.WriteLog(LogTypes.Exception, string.Format("执行SQL异常: {0}\r\n{1}", sql, ex.ToString()), null, true);
LogManager.WriteLog(LogTypes.Error, string.Format("写入数据库失败: {0}", sql), null, true);
}
return(null);
}
public void ExecuteScalarForAnyFieldType(string mySqlTypeDeclaration, string insertedLiteral, object expectedValue)
{
var c = CachedConnection;
using (var cmd1 = new MySQLCommand("DROP TABLE IF EXISTS number_type_test", c))
cmd1.ExecuteNonQuery();
using (var cmd2 = new MySQLCommand($@"CREATE TABLE number_type_test ( id INT NOT NULL,COL_VALUE {mySqlTypeDeclaration},PRIMARY KEY (id));", c))
cmd2.ExecuteNonQuery();
using (var cmd3 = new MySQLCommand($@"INSERT INTO number_type_test ( id ,COL_VALUE)values(0,{insertedLiteral});", c)) cmd3.ExecuteNonQuery();
using (var cmd = new MySQLCommand("select COL_VALUE from number_type_test where id=0", c))
{
object executeScalar = cmd.ExecuteScalar();
Assert.NotNull(executeScalar);
if (expectedValue == null)
{
Assert.Null(executeScalar);
}
else
{
Assert.NotNull(executeScalar);
Assert.IsType(expectedValue.GetType(), executeScalar);
Assert.Equal(expectedValue, executeScalar);
}
}
}
/// <summary>
/// Get the volunteer id from name. @deprecated Uses unstable algorithms
/// </summary>
/// <param name="FirstName">The first name</param>
/// <param name="LastName">The last name</param>
/// <returns>A volunteer id</returns>
public int? getVolunteerIDFromName_old(string FirstName,string LastName) {
MySQLCommand mc = new MySQLCommand(String.Format("SELECT VolunteerID FROM Volunteer WHERE FirstName='{0}' AND LastName='{1}' LIMIT 1;", FirstName, LastName), vlcon);
int? rval;
try
{
object obj = mc.ExecuteScalar();
rval = (int)mc.ExecuteScalar();
}
catch (Exception)
{
System.Console.WriteLine(mc.ExecuteScalar());
rval= null;
}
mc.Dispose();
return rval;
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public object ExecuteScalar(string SQLString)
{
using (MySQLConnection connection = new MySQLConnection(connectionString))
{
using (MySQLCommand cmd = new MySQLCommand(SQLString, connection))
{
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (MySQLException e)
{
connection.Close();
throw e;
}
}
}
}
public void SelectFunction()
{
using (var c = new MySQLConnection(ConnectionString))
{
c.Open();
using (var cmd = new MySQLCommand("select now();", c))
{
var time = cmd.ExecuteScalar();
}
}
}
/// <summary>
/// Gets a volunteer id from a concatenated full name string
/// </summary>
/// <param name="FULL_NAME">The full name of the users in the format{FirstName LastName}</param>
/// <returns>A volunteerID or null</returns>
public uint? getVolunteerIDFromName(string FULL_NAME){
MySQLCommand mc = new MySQLCommand(String.Format("SELECT VolunteerID FROM Volunteer WHERE CONCAT(FirstName,\" \",LastName)='{0}' LIMIT 1;", FULL_NAME), vlcon);
object qres;
try { qres = mc.ExecuteScalar(); }
catch (MySQLException) { throw new DatabaseNotOpenException(); }
mc.Dispose();
if(qres==null){
return null;
}else{
return uint.Parse(qres.ToString());
}
}
/// <summary>
/// Gets a key for a new field(max+1)
/// </summary>
/// <param name="TableName">The name of the table</param>
/// <param name="IndexFieldName">The name of the index field(LogID,VolunteerID,TaskID)</param>
/// <returns>A key or null</returns>
private int? getNewKey(string TableName, string IndexFieldName)
{
MySQLCommand mc = new MySQLCommand(String.Format("SELECT MAX({1}) FROM {0};", TableName, IndexFieldName), vlcon);
int? rval;
try{
object qres = mc.ExecuteScalar();
rval = qres.ToString() != "" ? (int?)qres : 0;
}catch (MySQLException) {
throw new DatabaseNotOpenException();
}catch (Exception ex){
rval = null;
System.Console.WriteLine(ex.Message);
}
mc.Dispose();
return rval >= 0 && rval != null ? rval + 1 : null;
}
public object ExecuteSqlGet(string sql, string content)
{
try
{
using (MySQLCommand cmd = new MySQLCommand(sql, this.DbConn))
{
MySQLParameter myParameter = new MySQLParameter("@content", content);
cmd.Parameters.Add(myParameter);
object obj = cmd.ExecuteScalar();
this.LogSql(sql);
if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
{
return(null);
}
return(obj);
}
}
catch (Exception ex)
{
LogManager.WriteLog(LogTypes.Exception, string.Format("执行SQL异常: {0}\r\n{1}", sql, ex.ToString()), null, true);
LogManager.WriteLog(LogTypes.Error, string.Format("写入数据库失败: {0}", sql), null, true);
}
return(null);
}
public static object ExecuteScalar(string SQLString, params MySQLParameter[] cmdParms)
{
using (MySQLConnection connection = new MySQLConnection(connectionString))
{
using (MySQLCommand cmd = new MySQLCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
object obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (MySQLException e)
{
throw e;
}
}
}
}
public int ExecuteSqlTran(List <CommandInfo> cmdList)
{
int result;
using (DbTransaction trans = this.DbConn.BeginTransaction())
{
using (MySQLCommand cmd = new MySQLCommand())
{
try
{
int count = 0;
foreach (CommandInfo myDE in cmdList)
{
string cmdText = myDE.CommandText;
MySQLParameter[] cmdParms = myDE.Parameters;
MyDbConnection3.PrepareCommand(cmd, this.DbConn, trans, cmdText, cmdParms);
if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
trans.Rollback();
return(0);
}
object obj = cmd.ExecuteScalar();
if (obj == null && obj == DBNull.Value)
{
}
bool isHave = Convert.ToInt32(obj) > 0;
if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
trans.Rollback();
return(0);
}
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
trans.Rollback();
return(0);
}
}
else
{
int val = cmd.ExecuteNonQuery();
count += val;
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
trans.Rollback();
return(0);
}
cmd.Parameters.Clear();
this.LogSql(cmdText);
}
}
trans.Commit();
result = count;
}
catch
{
trans.Rollback();
throw;
}
}
}
return(result);
}
public int ExecuteSqlTran(List <CommandInfo> list, List <CommandInfo> oracleCmdSqlList)
{
MySQLConnection connection = this.DbConn;
int result;
using (MySQLCommand cmd = new MySQLCommand())
{
cmd.Connection = connection;
DbTransaction tx = connection.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (CommandInfo myDE in list)
{
string cmdText = myDE.CommandText;
MySQLParameter[] cmdParms = myDE.Parameters;
MyDbConnection3.PrepareCommand(cmd, connection, tx, cmdText, cmdParms);
if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式");
}
object obj = cmd.ExecuteScalar();
if (obj == null && obj == DBNull.Value)
{
}
bool isHave = Convert.ToInt32(obj) > 0;
if (isHave)
{
myDE.OnSolicitationEvent();
}
}
if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
}
object obj = cmd.ExecuteScalar();
if (obj == null && obj == DBNull.Value)
{
}
bool isHave = Convert.ToInt32(obj) > 0;
if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
}
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
}
}
else
{
int val = cmd.ExecuteNonQuery();
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
}
cmd.Parameters.Clear();
}
}
tx.Commit();
result = 1;
}
catch (MySQLException e)
{
tx.Rollback();
throw e;
}
}
return(result);
}
public static long DoSqlcount(MySQLConnection cnn, string sql)
{
MySQLCommand command = new MySQLCommand(sql, cnn);
return((long)Convert.ToInt32(command.ExecuteScalar()));
}
