/// <summary>
/// Retorna uma lista de um objeto T
/// Pode retornar uma lista com 1 só informação
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Proc"></param>
/// <param name="cmd"></param>
/// <returns></returns>
public async Task <List <T> > ExecuteProcWithReturnAsync <T>(string Proc, OracleCommand cmd) where T : new()
{
List <T> result = new List <T>();
try
{
using (var conn = new OracleConnection(connectionString))
{
using (var command = new OracleCommand(Proc, conn)
{
CommandType = System.Data.CommandType.StoredProcedure
})
{
foreach (var param in cmd.Parameters)
{
command.Parameters.Add(param);
}
conn.Open();
using (var reader = await command.ExecuteReaderAsync())
{
result = Map <T>(reader);
}
}
}
}
catch (Exception x)
{
}
finally
{
}
return(result);
}
/// <summary>
/// Partly completed template for reading data.
/// * Replace Task<bool> with the proper return type
///
/// </summary>
/// <param name="ct"></param>
/// <returns></returns>
public static async Task <bool> Read(CancellationToken ct)
{
mHasException = false;
const string SelectStatement = "TODO";
// ReSharper disable once MethodSupportsCancellation
return(await Task.Run(async() =>
{
await using var cn = new OracleConnection(ConnectionString);
await using var cmd = new OracleCommand { Connection = cn, CommandText = SelectStatement };
try
{
await cn.OpenAsync(ct);
// ReSharper disable once MethodSupportsCancellation
var reader = await cmd.ExecuteReaderAsync();
return true;
}
catch (Exception exception)
{
mHasException = true;
mLastException = exception;
return false;
}
}));
}
protected async override Task <StringBuilder> DoWorkOracle(SqlTester data, CancellationToken token)
{
if (token.IsCancellationRequested)
{
return(new StringBuilder("cancelled"));
}
if (data._initialSleep.HasValue)
{
await Task.Delay(data._initialSleep.Value);
}
using (var conn = new OracleConnection(ConfigurationManager.ConnectionStrings["OracleDbContext"].ConnectionString))
{
await conn.OpenAsync(token);
var sb = new StringBuilder(1000);
using (var cmd = new OracleCommand(data._sqlQuery, conn))
{
if (token.IsCancellationRequested)
{
return(new StringBuilder("cancelled"));
}
cmd.Prepare();
cmd.CommandType = CommandType.Text;
using (var rdr = await cmd.ExecuteReaderAsync(token))
{
ProcessReader(rdr, sb);
return(sb);
}
}
}
}
public async Task <object> ExecuteProcWithReturnAsync(string Proc, OracleCommand cmd)
{
object returnObject = null;
List <object> resultObjectList = new List <object>();
List <List <object> > resultListOfObjects = new List <List <object> >();
try
{
using (var conn = new OracleConnection(connectionString))
{
using (var command = new OracleCommand(Proc, conn)
{
CommandType = System.Data.CommandType.StoredProcedure
})
{
foreach (var param in cmd.Parameters)
{
command.Parameters.Add(param);
}
conn.Open();
using (var reader = await command.ExecuteReaderAsync())
{
}
}
}
}
catch (Exception x)
{
}
finally
{
}
return(null);
}
public async Task <DataTable> ObtieneDato(string sql, short tipoConexion, OracleParameter[] parameters = null, int timmer = 0)
{
DataTable Tabla = new DataTable();
using (conn = new OracleConnection(CadenaConexion(tipoConexion)))
{
conn.Open();
using (var cmd = new OracleCommand())
{
if (timmer != 0)
{
cmd.CommandTimeout = timmer;
}
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = sql;
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}
using (var reader = await cmd.ExecuteReaderAsync())
{
Tabla.Load(reader);
}
}
}
return(Tabla);
}
public async Task <IEnumerable <DetalleOperadores> > OperadoresDetalle(int?codigoParroquia = null)
{
List <DetalleOperadores> operadoresDetalles = null;
using (OracleConnection con = new OracleConnection(_conn))
{
using (OracleCommand cmd = new OracleCommand())
{
try
{
con.Open();
cmd.Connection = con;
//cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandType = CommandType.Text;
//cmd.CommandText = "PKG_CONTEO_RAPIDO.CONSULTA_USUARIO";
if (codigoParroquia.HasValue)
{
sOperadoresDetalle += " WHERE CODIGO = " + codigoParroquia.ToString();
}
cmd.CommandText = string.Format(sOperadoresDetalle);
OracleDataReader odr = (OracleDataReader)await cmd.ExecuteReaderAsync();
if (odr.HasRows)
{
operadoresDetalles = new List <DetalleOperadores>();
while (odr.Read())
{
DetalleOperadores operador = new DetalleOperadores
{
CODIGO = Convert.ToInt32(odr["CODIGO"]),
PARROQUIA = Convert.ToString(odr["PARROQUIA"]),
CCANTON = Convert.ToInt32(odr["CCANTON"]),
CANTON = Convert.ToString(odr["CANTON"]),
PROVINCIA = Convert.ToString(odr["PROVINCIA"]),
JUNTA = Convert.ToInt32(odr["JUNTA"]),
USUARIO = Convert.ToString(odr["USUARIO"]),
TELEFONO = Convert.ToString(odr["TELEFONO"])
};
operadoresDetalles.Add(operador);
}
}
}
catch (Exception ex)
{
return(operadoresDetalles);
}
finally
{
con.Close();
con.Dispose();
cmd.Dispose();
}
}
}
return(operadoresDetalles);
}
public async Task <List <Post> > GetThreeMostLikedPostsAsync(int currentUserId, int roleId)
{
var postsIds = new List <int>();
using (var connection = GetConnection()) {
connection.Open();
var cmdTxt = @$ "select post_id
from (select post_id,
row_number() over(order by (select count(1) from table(like_list)) desc) as rn
from s_user
join s_post using(user_id)
where user_id = :currentUserId)
where rn < 4";
var cmd = new OracleCommand(cmdTxt, connection);
cmd.Parameters.Add("currentUserId", currentUserId);
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
postsIds.Add(reader.GetInt32(0));
}
connection.Close();
}
var posts = new List <Post>();
foreach (var postId in postsIds)
{
var post = await GetPostAsync(currentUserId, roleId, postId);
posts.Add(post);
}
return(posts);
}
public async Task <List <Post> > GetThreeMostCommentedPostsOfMyFollowersAsync(int currentUserId, int roleId)
{
var postsIds = new List <int>();
using (var connection = GetConnection()) {
connection.Open();
var cmdTxt = @$ "select post_id from (select p.post_id, row_number() over(order by count(*) desc) as rn
from s_user_follower uf join s_user u on u.user_id = uf.follower_id
join s_post p on p.user_id = u.user_id join s_comment c on c.post_id = p.post_id
where uf.user_id = :currentUserId group by p.post_id)
where rn < 4";
var cmd = new OracleCommand(cmdTxt, connection);
cmd.Parameters.Add("currentUserId", currentUserId);
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
postsIds.Add(reader.GetInt32(0));
}
connection.Close();
}
var posts = new List <Post>();
foreach (var postId in postsIds)
{
var post = await GetPostAsync(currentUserId, roleId, postId);
posts.Add(post);
}
return(posts);
}
static public async Task <int> GetCollectionCount(int user_id, OracleConnection conn)
{
string procudureName = "FUNC_GET_COLLECTION_NUM";
OracleCommand cmd = new OracleCommand(procudureName, conn);
cmd.CommandType = CommandType.StoredProcedure;
//Add return value
OracleParameter p1 = new OracleParameter();
p1 = cmd.Parameters.Add("state", OracleDbType.Int32);
p1.Direction = ParameterDirection.ReturnValue;
//Add input parameter user_id
OracleParameter p2 = new OracleParameter();
p2 = cmd.Parameters.Add("user_id", OracleDbType.Int32);
p2.Direction = ParameterDirection.Input;
p2.Value = user_id;
OracleParameter p3 = new OracleParameter();
//Add input parameter message_id
p3 = cmd.Parameters.Add("message_id", OracleDbType.Int32);
p3.Direction = ParameterDirection.Output;
await cmd.ExecuteReaderAsync();
return(int.Parse(p3.Value.ToString()));
}
private async void GetDataRefCursor1(OracleCommand cmd)
{
try
{
using (var reader = await cmd.ExecuteReaderAsync())
{
if (reader.HasRows)
{
int rowNumber = 1;
while (await reader.ReadAsync())
{
Console.WriteLine($"------------ Строка # {rowNumber++} " + new string('-', 90));
for (int i = 0; i < reader.FieldCount; i++)
{
Console.WriteLine($"Field{i} Name: {reader.GetName(i),20} \t " +
$"Value: {reader[i],20} \t " +
$"DataType: {reader[i].GetType().Name,20} \t ");
}
Console.WriteLine(new string('-', 115));
}
}
}
}
catch (Exception exc)
{
Console.WriteLine("Произошла ошибка при вызове процедуры: \n" + exc.Message);
}
}
public static async Task <int> addAvatarAndGetAvatarID(int user_id)
{
return(await Wrapper.wrap(async (OracleConnection conn) =>
{
//ADD_AVATAR(userid in INTEGER, avatarid out INTEGER)
//return INGETER
string procedureName = "ADD_AVATAR";
OracleCommand cmd = new OracleCommand(procedureName, conn);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p1 = new OracleParameter();
p1 = cmd.Parameters.Add("state", OracleDbType.Int32);
p1.Direction = ParameterDirection.ReturnValue;
OracleParameter p2 = new OracleParameter();
p2 = cmd.Parameters.Add("userid", OracleDbType.Int32);
p2.Direction = ParameterDirection.Input;
p2.Value = user_id;
OracleParameter p3 = new OracleParameter();
p3 = cmd.Parameters.Add("avatarid", OracleDbType.Int32);
p3.Direction = ParameterDirection.Output;
var reader = await cmd.ExecuteReaderAsync();
if (int.Parse(p1.Value.ToString()) == 0)
{
throw new Exception("failed");
}
else
{
return int.Parse(p3.Value.ToString());
}
}));
}
public async Task <IEnumerable <ATransmitidasParroquias> > TransmitidasParroquia(int?codigoCanton = null)
{
List <ATransmitidasParroquias> transmitidasParroquia = null;
using (OracleConnection con = new OracleConnection(_conn))
{
using (OracleCommand cmd = new OracleCommand())
{
try
{
con.Open();
cmd.Connection = con;
//cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandType = CommandType.Text;
//cmd.CommandText = "PKG_CONTEO_RAPIDO.CONSULTA_USUARIO";
if (codigoCanton.HasValue)
{
sTransmitidasParroquia += " WHERE CCANTON = " + codigoCanton.ToString();
}
cmd.CommandText = string.Format(sTransmitidasParroquia);
OracleDataReader odr = (OracleDataReader)await cmd.ExecuteReaderAsync();
if (odr.HasRows)
{
transmitidasParroquia = new List <ATransmitidasParroquias>();
while (odr.Read())
{
ATransmitidasParroquias tParroquia = new ATransmitidasParroquias
{
CCANTON = Convert.ToInt32(odr["CCANTON"]),
PARROQUIA = Convert.ToString(odr["NOM_PARROQUIA"]),
CODIGO = Convert.ToInt32(odr["CODIGO"]),
PROVINCIA = Convert.ToString(odr["PROVINCIA"]),
CANTON = Convert.ToString(odr["CANTON"]),
JUNTAS = Convert.ToInt32(odr["JUNTAS"]),
TRANSMITIDAS = Convert.ToInt32(odr["TRANSMITIDAS"])
};
transmitidasParroquia.Add(tParroquia);
}
}
}
catch (Exception ex)
{
return(transmitidasParroquia);
}
finally
{
con.Close();
con.Dispose();
cmd.Dispose();
}
}
}
return(transmitidasParroquia);
}
public async Task <IActionResult> Send([Required] int user_id, [Required][FromBody] SendingPrivateLetter letterInfo)
{
//TODO 需要验证登录态
//返回成功与否
int my_user_id = -1;
if (HttpContext.User.Identity.IsAuthenticated)
{
my_user_id = int.Parse(HttpContext.User.Claims.ElementAt(0).Value);
}
else
{
//进入到这部分意味着用户登录态已经失效，需要返回给客户端信息，即需要登录。
RestfulResult.RestfulData rr = new RestfulResult.RestfulData();
rr.Code = 200;
rr.Message = "Need Authentication";
return(new JsonResult(rr));
}
return(await Wrapper.wrap(async (OracleConnection conn) => {
//FUNC_ADD_PRIVATE_LETTER(sender_user_id in INTEGER, receiver_user_id in INTEGER, content in VARCHAR2(255))
//return INTEGER
string procudureName = "FUNC_ADD_PRIVATE_LETTER";
OracleCommand cmd = new OracleCommand(procudureName, conn);
cmd.CommandType = CommandType.StoredProcedure;
//Add return value
OracleParameter p1 = new OracleParameter();
p1 = cmd.Parameters.Add("state", OracleDbType.Int32);
p1.Direction = ParameterDirection.ReturnValue;
//Add input parameter sender_user_id
OracleParameter p2 = new OracleParameter();
p2 = cmd.Parameters.Add("sender_user_id", OracleDbType.Int32);
p2.Direction = ParameterDirection.Input;
p2.Value = my_user_id;
OracleParameter p3 = new OracleParameter();
//Add input parameter receiver_user_id
p3 = cmd.Parameters.Add("receiver_user_id", OracleDbType.Int32);
p3.Value = user_id;
p3.Direction = ParameterDirection.Input;
OracleParameter p4 = new OracleParameter();
//Add input parameter content
p4 = cmd.Parameters.Add("content", OracleDbType.Varchar2);
p4.Value = letterInfo.private_letter_content;
p4.Direction = ParameterDirection.Input;
await cmd.ExecuteReaderAsync();
Console.WriteLine(p1.Value);
if (int.Parse(p1.Value.ToString()) == 0)
{
throw new Exception("failed");
}
RestfulResult.RestfulData rr = new RestfulResult.RestfulData(200, "success");
return new JsonResult(rr);
}));
}
public async Task <IEnumerable <AOperadoresProvincia> > OperadoresProvincia(int?codigoProvincia = null)
{
List <AOperadoresProvincia> operadoresProv = null;
using (OracleConnection con = new OracleConnection(_conn))
{
using (OracleCommand cmd = new OracleCommand())
{
try
{
con.Open();
cmd.Connection = con;
//cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandType = CommandType.Text;
//cmd.CommandText = "PKG_CONTEO_RAPIDO.CONSULTA_USUARIO";
if (codigoProvincia.HasValue)
{
sOperadoresProvincia += " WHERE CODIGO = " + codigoProvincia.ToString();
}
cmd.CommandText = string.Format(sOperadoresProvincia);
OracleDataReader odr = (OracleDataReader)await cmd.ExecuteReaderAsync();
if (odr.HasRows)
{
operadoresProv = new List <AOperadoresProvincia>();
while (odr.Read())
{
AOperadoresProvincia operadorProvincia = new AOperadoresProvincia
{
COD_PROV = Convert.ToInt32(odr["CODIGO"]),
PROVINCIA = Convert.ToString(odr["PROVINCIA"]),
JUNTAS = Convert.ToInt32(odr["JUNTAS"]),
OPERADORES = Convert.ToInt32(odr["OPERADORES"])
};
operadoresProv.Add(operadorProvincia);
}
}
}
catch (Exception ex)
{
return(operadoresProv);
}
finally
{
con.Close();
con.Dispose();
cmd.Dispose();
}
}
}
return(operadoresProv);
}
public static async Task <UserPublicInfo> getUserPublicInfo(int user_id)
{
return(await Wrapper.wrap(async (OracleConnection conn) =>
{
//FUNC_GET_USER_PUBLIC_INFO(user_id in INTEGER, info out sys_refcursor)
//return INGETER
string procedureName = "FUNC_GET_USER_PUBLIC_INFO";
OracleCommand cmd = new OracleCommand(procedureName, conn);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p1 = new OracleParameter();
p1 = cmd.Parameters.Add("state", OracleDbType.Int32);
p1.Direction = ParameterDirection.ReturnValue;
OracleParameter p2 = new OracleParameter();
p2 = cmd.Parameters.Add("user_id", OracleDbType.Int32);
p2.Direction = ParameterDirection.Input;
p2.Value = user_id;
OracleParameter p3 = new OracleParameter();
p3 = cmd.Parameters.Add("info", OracleDbType.RefCursor);
p3.Direction = ParameterDirection.Output;
var reader = await cmd.ExecuteReaderAsync();
if (int.Parse(p1.Value.ToString()) == 0)
{
throw new Exception("failed");
}
else
{
if (reader.Read())
{
RestfulResult.RestfulData <UserPublicInfo> rr = new RestfulResult.RestfulData <UserPublicInfo>();
string[] temp = new string[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; ++i)
{
temp[i] = reader.GetValue(i).ToString();
}
rr.Data = new UserPublicInfo();
rr.Data.user_id = int.Parse(reader["USER_ID"].ToString());
rr.Data.nickname = reader["USER_NICKNAME"].ToString();
rr.Data.self_introction = reader["USER_SELF_INTRODUCTION"].ToString();
rr.Data.register_time = reader["USER_REGISTER_TIME"].ToString();
rr.Data.followers_num = int.Parse(reader["USER_FOLLOWERS_NUM"].ToString());
rr.Data.follows_num = int.Parse(reader["USER_FOLLOWS_NUM"].ToString());
rr.Data.messages_num = await getUserMessageNum(rr.Data.user_id);
rr.Data.avatar_url = await getAvatarUrl(user_id);
rr.Data.collection_num = await CollectionController.GetCollectionCount(user_id, conn);
return rr.Data;
}
else
{
throw new Exception("failed");
}
}
}));
}
public async Task <DataTable> QueryAsync(UserData userData, string sql, List <DatabaseParameter> parameters = null)
{
try
{
var connString =
BuildConnectionString(
Properties.AppSettings.ConnectionString,
userData.Username,
PasswordHelper.Decrypt(userData.EncryptedPassword));
using var conn = new OracleConnection(connString);
try
{
await conn.OpenAsync();
var tx = (OracleTransaction)await conn.BeginTransactionAsync();
DataTable result = new DataTable("DATA");
sql = sql.Replace("&AO", Properties.AppSettings.ApplicationOwner);
using var cmd = new OracleCommand(sql, conn);
cmd.Transaction = tx;
if (parameters != null && parameters.Count > 0)
{
foreach (var paremeter in parameters)
{
if (sql.IndexOf(":" + paremeter.Name) > -1)
{
cmd.Parameters.Add(GetParameter(paremeter.Name, paremeter.Value, paremeter.DataType, paremeter.Direction, paremeter.Size));
}
}
}
var reader = await cmd.ExecuteReaderAsync();
result.Load(reader);
return(result);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
await conn.CloseAsync();
}
}
}
catch (Exception ex)
{
throw ExceptionHelper.Throw(ex, ClassName, "QueryAsync");
}
}
public async Task <IActionResult> CancelLike([Required] int message_id)
{
//TODO 给某个推特取消点赞
// 需要身份验证
// 返回成功与否
int my_user_id = -1;
if (HttpContext.User.Identity.IsAuthenticated)
{
my_user_id = int.Parse(HttpContext.User.Claims.ElementAt(0).Value);
}
else
{
//进入到这部分意味着用户登录态已经失效，需要返回给客户端信息，即需要登录。
RestfulResult.RestfulData rr = new RestfulResult.RestfulData();
rr.Code = 200;
rr.Message = "Need Authentication";
return(new JsonResult(rr));
}
return(await Wrapper.wrap(async (OracleConnection conn) => {
//FUNC_DELETE_LIKE(user_id in INTEGER, like_message_id in INTEGER)
//return INTEGER
string procudureName = "FUNC_DELETE_LIKE";
OracleCommand cmd = new OracleCommand(procudureName, conn);
cmd.CommandType = CommandType.StoredProcedure;
//Add return value
OracleParameter p1 = new OracleParameter();
p1 = cmd.Parameters.Add("state", OracleDbType.Int32);
p1.Direction = ParameterDirection.ReturnValue;
//Add input parameter user_id
OracleParameter p2 = new OracleParameter();
p2 = cmd.Parameters.Add("user_id", OracleDbType.Int32);
p2.Direction = ParameterDirection.Input;
p2.Value = my_user_id;
OracleParameter p3 = new OracleParameter();
//Add input parameter message_id
p3 = cmd.Parameters.Add("like_message_id", OracleDbType.Int32);
p3.Value = message_id;
p3.Direction = ParameterDirection.Input;
await cmd.ExecuteReaderAsync();
Console.WriteLine(p1.Value);
if (int.Parse(p1.Value.ToString()) == 0)
{
throw new Exception("failed");
}
RestfulResult.RestfulData rr = new RestfulResult.RestfulData(200, "success");
return new JsonResult(rr);
}));
}
public async Task <IActionResult> FollowUser([Required] int user_id)
{
//TODO getAuthentication and do the CURD
//Authentication
int my_user_id = -1;
if (HttpContext.User.Identity.IsAuthenticated)
{
my_user_id = int.Parse(HttpContext.User.Claims.ElementAt(0).Value);
}
else
{
//进入到这部分意味着用户登录态已经失效，需要返回给客户端信息，即需要登录。
RestfulResult.RestfulData rr = new RestfulResult.RestfulData();
rr.Code = 200;
rr.Message = "Need Authentication";
return(new JsonResult(rr));
}
return(await Wrapper.wrap(async (OracleConnection conn) =>
{
//FUNC_Add_Relation(follower_id in INTEGER, be_followed_id in INTEGER)
//return INTEGER
string procudureName = "FUNC_ADD_RELATION";
OracleCommand cmd = new OracleCommand(procudureName, conn);
cmd.CommandType = CommandType.StoredProcedure;
//Add return value
OracleParameter p1 = new OracleParameter();
p1 = cmd.Parameters.Add("state", OracleDbType.Int32);
p1.Direction = ParameterDirection.ReturnValue;
//Add first parameter follower_id
OracleParameter p2 = new OracleParameter();
p2 = cmd.Parameters.Add("follower_id", OracleDbType.Int32);
p2.Direction = ParameterDirection.Input;
p2.Value = my_user_id;
OracleParameter p3 = new OracleParameter();
//Add second parameter be_followed_id
p3 = cmd.Parameters.Add("be_followed_id", OracleDbType.Int32);
p3.Value = user_id;
p3.Direction = ParameterDirection.Input;
await cmd.ExecuteReaderAsync();
Console.WriteLine(p1.Value);
if (int.Parse(p1.Value.ToString()) != 1)
{
throw new Exception("failed");
}
RestfulResult.RestfulData rr = new RestfulResult.RestfulData(200, "success");
return new JsonResult(rr);
}));
}
public async Task <IActionResult> SignUp([FromBody] UserInfoForSignUp userInfoForSignUp)
{
//TODO 注册啦
//返回是否注册成功
return(await Wrapper.wrap(async (OracleConnection conn) =>
{
if (!(ParameterChecker.CheckPara(userInfoForSignUp.email, ParameterChecker.ParaTpye.Email) &&
ParameterChecker.CheckPara(userInfoForSignUp.password, ParameterChecker.ParaTpye.Password)))
{
return new JsonResult(new RestfulResult.RestfulData(200, "Invalid Email or Password"));
}
if (await CheckUserEamil(userInfoForSignUp.email, conn))
{
return new JsonResult(new RestfulResult.RestfulData(200, "The email is used"));
}
//FUNC_USER_SIGN_UP(email in VARCHAR, nickname in VARCHAR, password in VARCHAR)
//return INGETER
string procedureName = "FUNC_USER_SIGN_UP";
OracleCommand cmd = new OracleCommand(procedureName, conn);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p1 = new OracleParameter();
p1 = cmd.Parameters.Add("state", OracleDbType.Int32);
p1.Direction = ParameterDirection.ReturnValue;
OracleParameter p2 = new OracleParameter();
p2 = cmd.Parameters.Add("email", OracleDbType.Varchar2);
p2.Direction = ParameterDirection.Input;
p2.Value = userInfoForSignUp.email;
OracleParameter p3 = new OracleParameter();
p3 = cmd.Parameters.Add("nickname", OracleDbType.Varchar2);
p3.Direction = ParameterDirection.Input;
p3.Value = userInfoForSignUp.nickname;
OracleParameter p4 = new OracleParameter();
p4 = cmd.Parameters.Add("password", OracleDbType.Varchar2);
p4.Direction = ParameterDirection.Input;
p4.Value = userInfoForSignUp.password;
await cmd.ExecuteReaderAsync();
if (int.Parse(p1.Value.ToString()) == 0)
{
throw new Exception("failed");
}
RestfulResult.RestfulData rr = new RestfulResult.RestfulData(200, "success");
return new JsonResult(rr);
}));
}
public async Task <DataTable> FetchAsync(string query, CancellationToken cancellationToken, params object[] args)
{
if (this.IsDisposed)
{
throw new Exception("Connection is disposed. Please open new one.");
}
//this.cnn.ActionName = this.GetStacktraceInfo();
this.SynchronizeSCN();
var myTable = new DataTable();
if (this.cnn.State.ToString() == "Closed")
{
this.cnn.Open();
}
using (var cmd = new OracleCommand(query, this.cnn)
{
InitialLONGFetchSize = -1
})
{
RunningCommand = cmd;
cmd.CommandTimeout = CommandTimeout;
this.ProcessParameters(cmd, args);
using (var reader = await Task.Run(async() => await cmd.ExecuteReaderAsync(cancellationToken), cancellationToken).ConfigureAwait(false))
{
if (reader.HasRows)
{
var schemaTable = reader.GetSchemaTable();
foreach (DataRow schemarow in schemaTable.Rows)
{
myTable.Columns.Add(schemarow.ItemArray[0].ToString(), Type.GetType(schemarow.ItemArray[11].ToString()));
}
while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
{
var row = myTable.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
{
row[i] = reader[i];
}
myTable.Rows.Add(row);
}
}
RunningCommand = null;
}
}
return(myTable);
}
public async Task <IActionResult> QueryUnreadAt()
{
//TODO 需要身份验证
//查找At自己的在range范围内的message_id
//按照时间排序
//返回包含这些id的Json对象
int my_user_id = -1;
if (HttpContext.User.Identity.IsAuthenticated)
{
my_user_id = int.Parse(HttpContext.User.Claims.ElementAt(0).Value);
}
else
{
//进入到这部分意味着用户登录态已经失效，需要返回给客户端信息，即需要登录。
RestfulResult.RestfulData rr = new RestfulResult.RestfulData();
rr.Code = 200;
rr.Message = "Need Authentication";
return(new JsonResult(rr));
}
return(await Wrapper.wrap(async (OracleConnection conn) =>
{
//FUNC_QUERY_UNREAD_AT(userid in INTEGER, unread_count out INTEGER)
//return INTEGER
string procudureName = "FUNC_QUERY_UNREAD_AT";
OracleCommand cmd = new OracleCommand(procudureName, conn);
cmd.CommandType = CommandType.StoredProcedure;
//Add return value
OracleParameter p1 = new OracleParameter();
p1 = cmd.Parameters.Add("state", OracleDbType.Int32);
p1.Direction = ParameterDirection.ReturnValue;
//Add input parameter follower_id
OracleParameter p2 = new OracleParameter();
p2 = cmd.Parameters.Add("userid", OracleDbType.Int32);
p2.Direction = ParameterDirection.Input;
p2.Value = my_user_id;
OracleParameter p3 = new OracleParameter();
//Add input parameter be_followed_id
p3 = cmd.Parameters.Add("unread_count", OracleDbType.Int32);
p3.Direction = ParameterDirection.Output;
await cmd.ExecuteReaderAsync();
RestfulResult.RestfulData <int> rr = new RestfulResult.RestfulData <int>();
rr.Code = 200;
rr.Message = "success";
rr.Data = int.Parse(p3.Value.ToString());
return new JsonResult(rr);
}));
}
/// <summary>
/// Write query results to csv string or file
/// </summary>
/// <param name="command"></param>
/// <param name="output"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public static async Task <string> ToXmlAsync(this OracleCommand command, OutputProperties output, CancellationToken cancellationToken)
{
command.CommandType = CommandType.Text;
// utf-8 as default encoding
Encoding encoding = string.IsNullOrWhiteSpace(output.OutputFile?.Encoding) ? Encoding.UTF8 : Encoding.GetEncoding(output.OutputFile.Encoding);
using (TextWriter writer = output.OutputToFile ? new StreamWriter(output.OutputFile.Path, false, encoding) : new StringWriter() as TextWriter)
using (OracleDataReader reader = await command.ExecuteReaderAsync(cancellationToken) as OracleDataReader)
{
using (XmlWriter xmlWriter = XmlWriter.Create(writer, new XmlWriterSettings {
Async = true, Indent = true
}))
{
await xmlWriter.WriteStartDocumentAsync();
await xmlWriter.WriteStartElementAsync("", output.XmlOutput.RootElementName, "");
while (await reader.ReadAsync(cancellationToken))
{
// single row element container
await xmlWriter.WriteStartElementAsync("", output.XmlOutput.RowElementName, "");
for (int i = 0; i < reader.FieldCount; i++)
{
await xmlWriter.WriteElementStringAsync("", reader.GetName(i), "", reader.GetValue(i).ToString());
}
// close single row element container
await xmlWriter.WriteEndElementAsync();
// write only complete elements, but stop if process was terminated
cancellationToken.ThrowIfCancellationRequested();
}
await xmlWriter.WriteEndElementAsync();
await xmlWriter.WriteEndDocumentAsync();
}
if (output.OutputToFile)
{
return(output.OutputFile.Path);
}
else
{
return(writer.ToString());
}
}
}
/// <summary>
/// Write query results to csv string or file.
/// </summary>
/// <param name="command"></param>
/// <param name="queryOutput"></param>
/// <param name="cancellationToken"></param>
/// <returns>String</returns>
public static async Task <string> MultiQueryToXmlAsync(this OracleCommand command, QueryOutputProperties queryOutput, CancellationToken cancellationToken)
{
using (var writer = new StringWriter())
{
using (var reader = await command.ExecuteReaderAsync(cancellationToken) as OracleDataReader)
{
using (var xmlWriter = XmlWriter.Create(writer, new XmlWriterSettings {
Async = true, Indent = true
}))
{
await xmlWriter.WriteStartDocumentAsync();
await xmlWriter.WriteStartElementAsync("", queryOutput.XmlOutput.RootElementName, "");
while (await reader.ReadAsync(cancellationToken))
{
// Single row element container.
await xmlWriter.WriteStartElementAsync("", queryOutput.XmlOutput.RowElementName, "");
for (var i = 0; i < reader.FieldCount; i++)
{
if (reader.GetDataTypeName(i).Equals("Decimal"))
{
var v = reader.GetOracleDecimal(i);
OracleDecimal.SetPrecision(v, 28);
await xmlWriter.WriteElementStringAsync("", reader.GetName(i), "", v.ToString());
}
else
{
await xmlWriter.WriteElementStringAsync("", reader.GetName(i), "", reader.GetValue(i).ToString());
}
cancellationToken.ThrowIfCancellationRequested();
}
// Close single row element container.
await xmlWriter.WriteEndElementAsync();
// Write only complete elements, but stop if process was terminated.
cancellationToken.ThrowIfCancellationRequested();
}
await xmlWriter.WriteEndElementAsync();
await xmlWriter.WriteEndDocumentAsync();
}
return(writer.ToString());
}
}
}
public async Task <IActionResult> ChangeAvatar([Required] int avatar_id)
{
string userId;
if (HttpContext.User.Identity.IsAuthenticated)
{
//这里通过 HttpContext.User.Claims 可以将我们在Login这个Action中存储到cookie中的所有
//claims键值对都读出来
userId = HttpContext.User.Claims.First().Value;
}
else
{
RestfulResult.RestfulData rr = new RestfulResult.RestfulData();
rr.Code = 200;
rr.Message = "Need Authentication";
return(new JsonResult(rr));
}
return(await Wrapper.wrap(async (OracleConnection conn) =>
{
//FUNC_SET_MAIN_AVATAR(user_id in INTEGER, avatar_id in INTEGER)
//return INGETER
string procedureName = "FUNC_SET_MAIN_AVATAR";
OracleCommand cmd = new OracleCommand(procedureName, conn);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p1 = new OracleParameter();
p1 = cmd.Parameters.Add("state", OracleDbType.Int32);
p1.Direction = ParameterDirection.ReturnValue;
OracleParameter p2 = new OracleParameter();
p2 = cmd.Parameters.Add("user_id", OracleDbType.Int32);
p2.Direction = ParameterDirection.Input;
p2.Value = userId;
OracleParameter p3 = new OracleParameter();
p3 = cmd.Parameters.Add("avatar_id", OracleDbType.Int32);
p3.Direction = ParameterDirection.Input;
p3.Value = avatar_id;
await cmd.ExecuteReaderAsync();
if (int.Parse(p1.Value.ToString()) == 0)
{
throw new Exception("failed");
}
RestfulResult.RestfulData rr = new RestfulResult.RestfulData(200, "success");
return new JsonResult(rr);
}));
}
public void SendSmsByConnId(m_SMS010 value)
{
var context = GlobalHost.ConnectionManager.GetHubContext <ChatHub>();
using (OracleConnection conn = new OracleConnection(Database.conString))
{
try
{
//if (conn.State == System.Data.ConnectionState.Open)
// conn.Close();
conn.Open();
//conn.ConnectionString = Constants.OracleDb.Development.conString;
using (var cmd = new OracleCommand(SqlCmd.User.getConnIdByCustNo, conn)
{
CommandType = System.Data.CommandType.Text
})
{
cmd.CommandTimeout = 30;
cmd.Parameters.Add(new OracleParameter("cust_no", value.CUST_NO));
var reader = cmd.ExecuteReaderAsync();
reader.Result.Read();
if (reader.Result.HasRows)
{
var connectionId = reader.Result["CONN_ID"] == DBNull.Value ? string.Empty : (string)reader.Result["CONN_ID"];
context.Clients.Client(connectionId).sms(value);
}
reader.Dispose();
}
}
finally
{
conn.Close();
conn.Dispose();
}
}
//var context = GlobalHost.ConnectionManager.GetHubContext<ChatHub>();
//oracle = new Database();
//List<OracleParameter> parameter = new List<OracleParameter>
//{
// new OracleParameter("cust_no", value.CUST_NO)
//};
//var reader = oracle.SqlQueryWithParams(SqlCmd.User.getConnIdByCustNo, parameter);
//reader.Read();
//var connectionId = (string)reader["CONN_ID"];
//context.Clients.Client(connectionId).sms(value);
//reader.Dispose();
//oracle.OracleDisconnect();
}
public async Task <Post> GetPostAsync(int currentUserId, int roleId, int postId)
{
using (var connection = GetConnection()) {
connection.Open();
var cmdTxt = @"select user_id, nickname, post_id, description, picture, created_at, (select count(1) from table(like_list)) as like_count,
(select count(1) from table(like_list) where column_value = :currentUserId) as liked_by_user,
(case when :roleId > 1 or user_id = :currentUserId then 1 else 0 end) as able_to_edit
from
(select user_id, nickname, role_id, post_id, description, picture, created_at, like_list,
row_number() over(order by created_at desc) as rn from
( select user_id, nickname, role_id from s_user su
join s_user_follower suf using(user_id)
where follower_id = :currentUserId
union
select user_id, nickname, role_id from s_user
where user_id = :currentUserId) interesting_posts
join s_post post using(user_id))
where post_id = :postId";
var cmd = new OracleCommand(cmdTxt, connection);
cmd.Parameters.Add("currentUserId", currentUserId);
cmd.Parameters.Add("roleId", roleId);
cmd.Parameters.Add("currentUserId", currentUserId);
cmd.Parameters.Add("currentUserId", currentUserId);
cmd.Parameters.Add("currentUserId", currentUserId);
cmd.Parameters.Add("postId", postId);
var reader = await cmd.ExecuteReaderAsync();
Post post = null;
if (reader.Read())
{
post = new Post {
UserId = reader.GetInt32(0),
Nickname = reader.GetString(1),
PostId = reader.GetInt32(2),
Description = reader.IsDBNull(3) ? "" : reader.GetString(3),
PictureSource = $"data:{"image/png"};base64,{Convert.ToBase64String((byte[])reader.GetValue(4))}",
CreationDate = reader.GetDateTime(5),
Likes = reader.GetInt32(6),
LikedByMe = reader.GetInt32(7) > 0,
EditableByMe = reader.GetInt32(8) > 0
};
}
connection.Close();
return(post);
}
}
/// <summary>
/// Write query results to csv string or file
/// </summary>
/// <param name="command"></param>
/// <param name="output"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public static async Task <string> ToCsvAsync(this OracleCommand command, OutputProperties output, CancellationToken cancellationToken)
{
command.CommandType = CommandType.Text;
// utf-8 as default encoding
Encoding encoding = string.IsNullOrWhiteSpace(output.OutputFile?.Encoding) ? Encoding.UTF8 : Encoding.GetEncoding(output.OutputFile.Encoding);
using (OracleDataReader reader = await command.ExecuteReaderAsync(cancellationToken) as OracleDataReader)
using (TextWriter w = output.OutputToFile ? new StreamWriter(output.OutputFile.Path, false, encoding) : new StringWriter() as TextWriter)
{
bool headerWritten = false;
while (await reader.ReadAsync(cancellationToken))
{
// write csv header if necessary
if (!headerWritten && output.CsvOutput.IncludeHeaders)
{
var fieldNames = new object[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
fieldNames[i] = reader.GetName(i);
}
await w.WriteLineAsync(string.Join(output.CsvOutput.CsvSeparator, fieldNames));
headerWritten = true;
}
var fieldValues = new object[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
fieldValues[i] = reader.GetValue(i);
}
await w.WriteLineAsync(string.Join(output.CsvOutput.CsvSeparator, fieldValues));
// write only complete rows, but stop if process was terminated
cancellationToken.ThrowIfCancellationRequested();
}
if (output.OutputToFile)
{
return(output.OutputFile.Path);
}
else
{
return(w.ToString());
}
}
}
private async Task <DataTable> LoadDataAsync(string query, OracleConnection connection, string tablename)
{
var table = new DataTable();
using (var com = new OracleCommand(query, connection))
{
var res = await com.ExecuteReaderAsync();
table.Load(res);
if (!string.IsNullOrEmpty(tablename))
{
table.TableName = tablename;
}
return(table);
}
}
private static async Task <DbDataReader> QueryForReaderAsync(string sql)
{
try
{
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
DbDataReader dtr = await cmd.ExecuteReaderAsync();
return(dtr);
}
catch (Exception ex)
{
throw ex;
}
}
// private because it does not make sense to use it if it only falls back to the sync version
private static async Task <Field[][]> ReadAsync(string ExtractSql)
{
var connStr = ConfigurationManager.ConnectionStrings["EndurDB"].ConnectionString;
// https://stackoverflow.com/questions/63907271/is-oracle-openasync-etc-not-a-truly-async-method
List <Field[]> ret = new List <Field[]>();
await Task.Run(async() =>
{
using (OracleConnection conn = new OracleConnection(connStr))
{
await conn.OpenAsync();
string viewschema = ConfigurationManager.AppSettings["ViewSchema"];
using (OracleCommand cmd = new OracleCommand("alter session set CURRENT_SCHEMA = " + viewschema, conn))
{
int res = await cmd.ExecuteNonQueryAsync(); // -1 is ok
//Console.WriteLine("session set result: " + res);
}
using (OracleCommand cmd = new OracleCommand(ExtractSql, conn))
{
// https://stackoverflow.com/questions/63907271/is-oracle-openasync-etc-not-a-truly-async-method
OracleDataReader DR = (OracleDataReader)await cmd.ExecuteReaderAsync();
int count = 0;
while (await DR.ReadAsync())
{
count++;
Field[] fields = new Field[DR.FieldCount];
for (int i = 0; i < DR.FieldCount; i++)
{
Field field = new Field();
field.RowCount = count;
field.FieldCount = i;
field.Name = DR.GetName(i);
field.DataType = DR.GetDataTypeName(i);
field.ObjValue =
field.DataType == "Decimal" ?
DR.GetDouble(i) :
DR.GetValue(i);
fields[i] = field;
}
ret.Add(fields);
}
}
}
});
return(ret.ToArray());
}