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