public List <FileUploadInfo> QueryFileUploadInfo(QueryConditionBase queryInfo)
{
int res = 0;
List <FileUploadInfo> tempFileUploadInfoList = new List <FileUploadInfo>();
String quserSql = GenerateQuerySql(queryInfo);
MySqlConnectHelper mysqlCnn = MySqlConnectPoolHelper.getPool().getConnection();
res = mysqlCnn.SelectDB(dbName);
MySqlDataReader dataReader = mysqlCnn.ExecuteReader(CommandType.Text, quserSql, null);
while (dataReader.Read())
{
FileUploadInfo tempFileUploadInfo = new FileUploadInfo();
tempFileUploadInfo.ID = (int)dataReader["ID"];
tempFileUploadInfo.DateTime = (string)dataReader["DateTime"];
tempFileUploadInfo.Content = (string)dataReader["Content"];
tempFileUploadInfo.UserName = (string)dataReader["UserName"];
string tempDeviceList = (string)dataReader["FilePathList"];
tempFileUploadInfo.FilePathList = tempDeviceList.Split(',');
tempFileUploadInfoList.Add(tempFileUploadInfo);
}
dataReader.Close();
MySqlConnectPoolHelper.getPool().closeConnection(mysqlCnn);
mysqlCnn = null;
return(tempFileUploadInfoList);
}
//关闭连接,加连接回到池中
public void closeConnection(MySqlConnectHelper con)
{
lock (pool)
{
if (con != null)
{
pool.Add(con);
}
}
}
//获取池中的连接
public MySqlConnectHelper getConnection()
{
lock (pool)
{
MySqlConnectHelper tmp = null;
if (pool.Count > 0)
{
tmp = (MySqlConnectHelper)pool[0];
pool.RemoveAt(0);
//不成功
if (!isUserful(tmp))
{
//可用的连接数据已去掉一个
useCount--;
tmp = getConnection();
}
}
else
{
//可使用的连接小于连接数量
if (useCount < PoolSize)
{
try
{
//创建连接
MySqlConnectHelper conn = new MySqlConnectHelper(ConnectionStr);
conn.OpenConnect();
useCount++;
tmp = conn;
}
catch (Exception e)
{
}
}
}
return(tmp);
}
}
public bool DeleteFileUploadInfo(FileUploadInfo deleteFileUploadInfo)
{
int res = 0;
String deleteSql = $"delete from {tableName} where id={deleteFileUploadInfo.ID}";
MySqlConnectHelper mysqlCnn = MySqlConnectPoolHelper.getPool().getConnection();
res = mysqlCnn.SelectDB(dbName);
res = mysqlCnn.ExecuteNonQuery(CommandType.Text, deleteSql, null);
MySqlConnectPoolHelper.getPool().closeConnection(mysqlCnn);
mysqlCnn = null;
if (res > 0)
{
return(true);
}
return(false);
}
public bool InsertFileUploadInfo(FileUploadInfo newFileUploadInfo)
{
int res = 0;
String currentDate = DateTime.Now.ToLocalTime().ToString();
String FilePathList = string.Join(",", newFileUploadInfo.FilePathList);
String deleteSql = $"insert into {tableName} VALUES(NULL,'{currentDate}','{newFileUploadInfo.Content}','{newFileUploadInfo.UserName}','{FilePathList}')";
MySqlConnectHelper mysqlCnn = MySqlConnectPoolHelper.getPool().getConnection();
res = mysqlCnn.SelectDB(dbName);
res = mysqlCnn.ExecuteNonQuery(CommandType.Text, deleteSql, null);
MySqlConnectPoolHelper.getPool().closeConnection(mysqlCnn);
mysqlCnn = null;
if (res > 0)
{
return(true);
}
return(false);
}
public int InitMySqlConnectPool()
{
int ConnectSize = 0;
for (; ConnectSize < PoolSize; ConnectSize++)
{
MySqlConnectHelper newConnect = new MySqlConnectHelper(ConnectionStr);
if (newConnect.OpenConnect() == true)
{
pool.Add(newConnect);
}
else
{
break;
}
}
if (ConnectSize < 1)
{
return(-1);
}
return(ConnectSize);
}
//目的保证所创连接成功,测试池中连接
private bool isUserful(MySqlConnectHelper con)
{
return(con.isUserful());
}
