|
using FunApplication.AttributeExtend;
using FunApplication.IDAL;
using FunApplication.Model;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
namespace FunApplication.DAL
{
public class FunBaseDAL : IBaseDAL
{
// 数据库链接字符串
private static string strConn = ConfigurationManager.ConnectionStrings["DbConnection"].ConnectionString;
public int Delete<T>(int id) where T : BaseModel
{
Type type = typeof(T);
string sql = $"delete from {type.Name} where Id=@Id";
Func<SqlCommand, int> func = (SqlCommand command) =>
{
SqlParameter para = new SqlParameter("Id", id);
command.Parameters.Add(para);
return command.ExecuteNonQuery();
};
return ExcuteSql<int>(sql, func);
}
public int Insert<T>(T t) where T : BaseModel
{
int result = 0;
Type type = typeof(T);
var propArray = type.GetProperties().Where(p => p.Name != "Id");
string strSQL = "insert into Student Values (@Name,@Age,@Sex,@Email) ";
var parameters = propArray.Select(p => new SqlParameter($"@{p.GetColumnName()}", p.GetValue(t) ?? DBNull.Value)).ToArray();
Func<SqlCommand, int> func = (SqlCommand command) =>
{
command.Parameters.AddRange(parameters);
return command.ExecuteNonQuery();
};
result = ExcuteSql<int>(strSQL, func);
return result;
}
public T Query<T>(int id) where T : BaseModel
{
Type type = typeof(T);
string columnString = string.Join(",", type.GetProperties().Select(p => $"[{p.GetColumnName()}]"));
string sql = $"SELECT {columnString} FROM [{type.Name}] WHERE Id=@Id";
T t = null;
DataTable dt = new DataTable();
Func<SqlCommand, T> func = (SqlCommand command) =>
{
SqlParameter para = new SqlParameter("@Id", id);
command.Parameters.Add(para);
SqlDataAdapter adapter = new SqlDataAdapter(command);
//SqlDataReader reader = command.ExecuteReader();
//List<T> list = this.ReaderToList<T>(reader);
adapter.Fill(dt);
List<T> list = ConvertToList<T>(dt);
T tResult = list.FirstOrDefault();
return tResult;
};
t = ExcuteSql<T>(sql, func);
return t;
}
public List<T> QueryAll<T>() where T : BaseModel
{
Type type = typeof(T);
string columnString = string.Join(",", type.GetProperties().Select(p => $"[{p.GetColumnName()}]"));
string sql = $"SELECT {columnString} FROM [{type.Name}] ";
T t = null;
Func<SqlCommand, List<T>> func = (SqlCommand command) =>
{
SqlDataReader reader = command.ExecuteReader();
List<T> list = this.ReaderToList<T>(reader);
return list;
};
return ExcuteSql<List<T>>(sql, func);
}
public int Update<T>(T t) where T : BaseModel
{
int result = 0;
Type type = typeof(T);
var propArray = type.GetProperties().Where(p => p.Name != "Id");
string columnString = string.Join(",", propArray.Select(p => $"[{p.GetColumnName()}]=@{p.GetColumnName()}"));
var parameters = propArray.Select(p => new SqlParameter($"@{p.GetColumnName()}", p.GetValue(t) ?? DBNull.Value)).ToArray();
//必须参数化 否则引号？ 或者值里面还有引号
string strSQL = $"UPDATE [{type.Name}] SET {columnString} WHERE Id={t.Id}";
Func<SqlCommand, int> func = (SqlCommand command) =>
{
command.Parameters.AddRange(parameters);
return command.ExecuteNonQuery();
};
result = ExcuteSql<int>(strSQL, func);
return result;
}
//多个方法里面重复对数据库的访问 想通过委托解耦，去掉重复代码
private T ExcuteSql<T>(string sql, Func<SqlCommand, T> func)
{
using (SqlConnection conn = new SqlConnection(strConn))
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
conn.Open();
SqlTransaction sqlTransaction = conn.BeginTransaction();
try
{
command.Transaction = sqlTransaction;
T tResult = func.Invoke(command);
sqlTransaction.Commit();
return tResult;
}
catch (Exception ex)
{
sqlTransaction.Rollback();
throw;
}
}
}
}
private List<T> ReaderToList<T>(SqlDataReader reader) where T : BaseModel
{
Type type = typeof(T);
List<T> list = new List<T>();
while (reader.Read())//表示有数据 开始读
{
T t = (T)Activator.CreateInstance(type);
foreach (var prop in type.GetProperties())
{
object oValue = reader[prop.GetColumnName()];
if (oValue is DBNull)
oValue = null;
prop.SetValue(t, oValue);//除了guid和枚举
}
list.Add(t);
}
reader.Close();
return list;
}
}
}
|
