using FreeSql;
|
using Newtonsoft.Json;
|
using OfficeOpenXml.FormulaParsing.Excel.Functions.DateTime;
|
using OfficeOpenXml.FormulaParsing.Excel.Functions.Text;
|
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 Dapper.SqlMapper;
|
|
namespace WIDESEA_WCS.JobsPart.Common
|
{
|
public class StoredProcedure
|
{
|
/// <summary>
|
/// 创建存储过程
|
/// </summary>
|
public static void Main<T>(T entity, string MethodName) where T : class
|
{
|
//string storedProcedureName = "MyNewStoredProcedure";
|
|
using (SqlConnection connection = new SqlConnection(DBServerProvider.GetConnectionString()))
|
{
|
|
string ProcedureName = MethodName + entity.GetType().Name;
|
string sqlCommand = $"CREATE PROCEDURE {ProcedureName} ";
|
connection.Open();
|
|
if (MethodName == "Add")
|
sqlCommand += Add(entity);
|
if (MethodName == "Delete")
|
sqlCommand += Delete(entity);
|
// 创建存储过程的SQL脚本
|
//string sqlCommand = $@"
|
//CREATE PROCEDURE {ProcedureName}
|
//AS
|
//BEGIN
|
//SELECT * FROM {TableName}
|
//END";
|
|
using (SqlCommand command = new SqlCommand(sqlCommand, connection))
|
{
|
command.ExecuteNonQuery();
|
Console.WriteLine($"存储过程 {ProcedureName} 已创建。");
|
}
|
}
|
}
|
|
#region 添加的存储过程
|
public static string Add<T>(T entity) where T : class
|
{
|
|
//string sqlCommand = $"CREATE PROCEDURE {ProcedureName} ";
|
|
string sql = string.Empty;
|
string values = string.Empty;
|
string Params = string.Empty;
|
|
PropertyInfo[] propertyInfos = typeof(T).GetProperties();
|
foreach (PropertyInfo propertyInfo in propertyInfos)
|
{
|
var ValueType = propertyInfo.GetColumnType(true).Value;
|
//sqlCommand += $@"@{propertyInfo.Name} {value}";
|
sql += sql == string.Empty ? $"@{propertyInfo.Name} {ValueType}" : $",@{propertyInfo.Name} {ValueType}";
|
values += values == string.Empty ? propertyInfo.Name : "," + propertyInfo.Name;
|
Params += Params == string.Empty ? "@" + propertyInfo.Name : ",@" + propertyInfo.Name;
|
//var name = "@" + propertyInfo.Name;
|
//var value = propertyInfo.GetValue(entity, null);
|
}
|
//sqlCommand += $"{sql} AS\nBEGIN\nINSERT INTO {entity.GetType().Name} ({values}) VALUES ({Params}) END GO";
|
|
|
return $"{sql} AS\nBEGIN\nINSERT INTO {entity.GetType().Name} ({values}) VALUES ({Params}) END";
|
}
|
#endregion
|
|
#region 删除的存储过程
|
public static string Delete<T>(T entity) where T : class
|
{
|
PropertyInfo[] propertyInfos = typeof(T).GetProperties();
|
var key = propertyInfos.GetKeyName(true);
|
var name = propertyInfos.GetKeyProperty().Name;
|
var value = propertyInfos.GetKeyProperty().GetValue(entity, null);
|
string sql = $"@{name} {key} AS\nBEGIN\nDELETE FROM {entity.GetType().Name} WHERE {name}='{value}' END GO";
|
return sql;
|
}
|
#endregion
|
|
#region 调用存储过程
|
/// <summary>
|
/// 调用存储过程
|
/// </summary>
|
/// <param name="ProcedureName">存储过程名称</param>
|
/// <param name="Param1">参数1</param>
|
public static void EXEC(string ProcedureName, object Param1)
|
{
|
using (SqlConnection conn = new SqlConnection(DBServerProvider.GetConnectionString()))
|
{
|
SqlCommand cmd = new SqlCommand("DeleteOldRecords", conn);//存储过程名称
|
cmd.CommandType = CommandType.StoredProcedure;
|
//PropertyInfo[] propertyInfos = typeof(Param1).GetProperties();
|
//cmd.Parameters.AddWithValue("@Param1", "Value1");//参数1
|
cmd.Parameters.AddWithValue("@MonthsAgo", 6);//参数2
|
conn.Open();
|
var count = cmd.ExecuteNonQuery();
|
if (count > 0)
|
{
|
|
}
|
}
|
}
|
#endregion
|
|
/// <summary>
|
/// 调用添加存储过程
|
/// </summary>
|
/// <typeparam name="T"></typeparam>
|
/// <param name="entity"></param>
|
/// <param name="ProcedureName">存储过程名称</param>
|
public static void EXECAdd<T>(T entity, string MethodName) where T : class
|
{
|
string ProcedureName = MethodName + entity.GetType().Name;
|
using (SqlConnection conn = new SqlConnection(DBServerProvider.GetConnectionString()))
|
{
|
SqlCommand cmd = new SqlCommand(ProcedureName, conn);//存储过程名称"Add_inventory"
|
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();
|
//var count = cmd.ExecuteNonQuery();
|
//if (count > 0)
|
//{
|
// Console.WriteLine($"存储过程 {ProcedureName} 调用成功。");
|
//}
|
}
|
}
|
}
|
}
|
