using Newtonsoft.Json;
|
using OfficeOpenXml.FormulaParsing.Excel.Functions.Math;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Reflection;
|
using System.Text;
|
using System.Threading.Tasks;
|
using WIDESEA_Comm.DBItem;
|
using WIDESEA_Core.DBManager;
|
using WIDESEA_Core.Extensions;
|
using static Microsoft.EntityFrameworkCore.DbLoggerCategory.Database;
|
|
namespace WIDESEA_Comm.StoredProcedure
|
{
|
public class EXECStoredProcedure
|
{
|
#region 调用添加的存储过程
|
public static void EXECAdd<T>(T entity, string MethodName) where T : class
|
{
|
string ProcedureName = MethodName + entity.GetType().Name;
|
if (StoredProcedureExists(DBServerProvider.GetConnectionString(), ProcedureName))
|
{
|
CreateStoredProcedure.Main(entity, MethodName);
|
}
|
using (SqlConnection conn = new SqlConnection(DBServerProvider.GetConnectionString()))
|
{
|
SqlCommand cmd = new SqlCommand(ProcedureName, conn);
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
PropertyInfo[] propertyInfos = typeof(T).GetProperties();
|
foreach (PropertyInfo propertyInfo in propertyInfos)
|
{
|
var value = propertyInfo.GetValue(entity, null);
|
cmd.Parameters.AddWithValue("@" + propertyInfo.Name, value);
|
}
|
conn.Open();
|
cmd.ExecuteNonQuery();
|
}
|
}
|
#endregion
|
|
#region 调用修改的存储过程
|
public static void EXECUpdate<T>(T entity, string MethodName) where T : class
|
{
|
string ProcedureName = MethodName + entity.GetType().Name;
|
|
if (StoredProcedureExists(DBServerProvider.GetConnectionString(), ProcedureName))
|
{
|
CreateStoredProcedure.Main(entity, MethodName);
|
}
|
using (SqlConnection conn = new SqlConnection(DBServerProvider.GetConnectionString()))
|
{
|
SqlCommand cmd = new SqlCommand(ProcedureName, conn);
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
PropertyInfo[] propertyInfos = typeof(T).GetProperties();
|
foreach (PropertyInfo propertyInfo in propertyInfos)
|
{
|
var value = propertyInfo.GetValue(entity, null);
|
cmd.Parameters.AddWithValue("@" + propertyInfo.Name, value);
|
}
|
conn.Open();
|
cmd.ExecuteNonQuery();
|
}
|
}
|
#endregion
|
|
|
#region 查询存储过程是否存在
|
public static bool StoredProcedureExists(string connectionString, string storedProcedureName)
|
{
|
using (SqlConnection connection = new SqlConnection(connectionString))
|
{
|
connection.Open();
|
|
// 构建查询存储过程的SQL查询
|
string query = @"SELECT COUNT(*)
|
FROM INFORMATION_SCHEMA.ROUTINES
|
WHERE SPECIFIC_NAME = @procedureName";
|
|
using (SqlCommand command = new SqlCommand(query, connection))
|
{
|
command.Parameters.AddWithValue("@procedureName", storedProcedureName);
|
int result = Convert.ToInt32(command.ExecuteScalar());
|
|
return result < 1;
|
}
|
}
|
}
|
#endregion
|
|
#region 调用查询的存储过程 问题点！！！！！
|
public static string GetUsersFromStoredProcedure<T>(T entity, string MethodName) where T : class
|
{
|
#region MyRegion
|
List<T> users = new List<T>();
|
List<string> roles = new List<string>();
|
string ProcedureName = MethodName + entity.GetType().Name;
|
if (StoredProcedureExists(DBServerProvider.GetConnectionString(), ProcedureName))
|
{
|
CreateStoredProcedure.Main(entity, MethodName);
|
}
|
using (SqlConnection conn = new SqlConnection(DBServerProvider.GetConnectionString()))
|
{
|
conn.Open();
|
SqlCommand cmd = new SqlCommand(ProcedureName, conn);
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
PropertyInfo[] propertyInfos = typeof(T).GetProperties();
|
foreach (PropertyInfo propertyInfo in propertyInfos)
|
{
|
var ValueType = propertyInfo.GetColumnType(true).Value;
|
if (ValueType != "uniqueidentifier" && ValueType != "datetime")
|
{
|
var value = propertyInfo.GetValue(entity, null);
|
cmd.Parameters.AddWithValue("@" + propertyInfo.Name, value);
|
}
|
}
|
SqlDataReader reader = cmd.ExecuteReader();
|
|
while (reader.Read())
|
{
|
|
var a = reader.GetProviderSpecificValue(propertyInfos.Count() - 1);
|
for (int i = 0; i < propertyInfos.Count(); i++)
|
{
|
roles.Add(reader.GetValue(i).ToString());
|
}
|
|
return roles.ToString();
|
// 处理每一条记录
|
//var column1Value = reader["Column1"];
|
//var column2Value = reader["Column2"];
|
//var columnValues = reader.GetValues;
|
//var columnValue = reader.GetValue(0);
|
//var column1Value = reader.GetString(1);
|
//var column2Value = reader.GetString(2);
|
|
//Console.WriteLine($"Column1: {column1Value}, Column2: {column2Value}");
|
}
|
try
|
{
|
//conn.Open();
|
|
//return cmd.ExecuteReader();
|
//using (var reader = cmd.ExecuteReader())
|
//{
|
// //var readers = reader.ToString();
|
// while (reader.Read())
|
// {
|
// var a = reader[$"id"];
|
// var b = reader["describe"];
|
// }
|
//}
|
}
|
catch (Exception)
|
{
|
|
throw;
|
}
|
//var count = cmd.ExecuteNonQuery();
|
//var dates = cmd.ExecuteReader();
|
}
|
#endregion
|
|
//string ProcedureName = MethodName + entity.GetType().Name;//存储过程名称
|
object data = entity.GetType();
|
//var datas= JsonConvert.DeserializeObject<data>(reader);
|
|
using (var connection = new SqlConnection(DBServerProvider.GetConnectionString()))
|
{
|
var command = new SqlCommand(ProcedureName, connection);
|
command.CommandType = CommandType.StoredProcedure;
|
|
try
|
{
|
connection.Open();
|
using (var reader = command.ExecuteReader())
|
{
|
//return reader;
|
//return JsonConvert.DeserializeObject <data> (reader);
|
//return ((IObjectContextAdapter)connection).ObjectContext.Translate < entity.GetType().Name > (reader);//.ToList();
|
}
|
}
|
catch (Exception ex)
|
{
|
// 处理异常
|
Console.WriteLine(ex.Message);
|
throw;
|
}
|
}
|
|
return roles.ToString();
|
}
|
#endregion
|
}
|
}
|
