测试代码:控制台应用
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
namespace XF.ADO.DB
{
public class T2
{
public int? fId { get; set; }
public string fname { get; set; }
public string fpass { get; set; }
public DateTime? faddtime { get; set; }
public string ftimespan { get; set; }
}
public class XFDB
{
static void Main()
{
//查询
string connstr = "Data Source = 127.0.0.1; Initial Catalog = Test; Persist Security Info = True; User ID = sa; Password = xinfu978";
XFDB db = new XFDB(connstr,false);
Console.WriteLine(DateTime.Now.ToString());
try
{
T2 d = new T2();
//d.fId = 1058585;
d.fname = "kkkkkkkkkk";
d.faddtime = DateTime.Now;
for (int i = 0; i < 1000000; i++)
{
db.FnAdd(d);
}
//int i = db.FnUpdate(d, "fId", "T2");
//d.fname = "qqqqqqqqq";
//i = db.FnUpdate(d, "fId", "T2");
//db.Commit();
}
catch (Exception e)
{
db.Rollback();
}
Console.WriteLine(DateTime.Now.ToString());
db.FnDispost();
Console.ReadKey();
}
string connectString = "";
public XFDB(string connectString,bool isRran = false)
{
this.connectString = connectString;
conn = new SqlConnection(connectString);
conn.Open();
if (isRran)
{
tran = conn.BeginTransaction();
}
}
public void Commit()
{
if (tran != null)
{
tran.Commit();
}
}
public void Rollback()
{
if (tran != null)
{
tran.Rollback();
}
}
public void FnDispost()
{
try
{
conn.Dispose();
tran.Dispose();
}
catch (Exception)
{
}
}
SqlConnection conn = null;
SqlTransaction tran = null;
public DataTable FnSelect(string sqlStr)
{
SqlDataAdapter myDataAdapter = new SqlDataAdapter(sqlStr, conn);
DataSet myDataSet = new DataSet(); // 创建DataSet
myDataAdapter.Fill(myDataSet);
return myDataSet.Tables[0];
}
public T FnSelect<T>(string sqlStr)
{
SqlDataAdapter myDataAdapter = new SqlDataAdapter(sqlStr, conn);
DataSet myDataSet = new DataSet(); // 创建DataSet
myDataAdapter.Fill(myDataSet);
return FnDataTableToList<T>(myDataSet.Tables[0]);
}
public int FnExecute(string sqlStr)
{
SqlCommand cmd = conn.CreateCommand();
if (tran != null)
{
cmd.Transaction = tran;
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlStr; //sql语句
return cmd.ExecuteNonQuery();
}
public int FnAdd<T>(T obj)
{
string classname = typeof(T).ToString();
classname = classname.Substring(classname.LastIndexOf(".") + 1);
return FnAdd<T>(obj, classname);
}
public int FnAdd<T>(T obj, string mTableName)
{
string key = "";
string val = "";
Type type = obj.GetType();
PropertyInfo[] ps = type.GetProperties();
bool addDo = true;
foreach (PropertyInfo i in ps)
{
Object objq = i.GetValue(obj, null);
if (objq != null)
{
if (addDo)
{
key += $"[{i.Name}]";
val += $"'{objq}'";
addDo = false;
}
else
{
key += $",[{i.Name}]";
val += $",'{objq}'";
}
}
}
return FnExecute($"INSERT INTO [dbo].[{mTableName}] ({key}) VALUES ({val})");
}
public int FnUpdate<T>(T obj, string PId, string mTableName)
{
string changes = "";
string where = "";
Type type = obj.GetType();
PropertyInfo[] ps = type.GetProperties();
bool addDo = true;
foreach (PropertyInfo i in ps)
{
Object objq = i.GetValue(obj, null);
if (i.Name == PId.ToString())
{
if (objq.GetType().ToString().IndexOf("Int") >= 0)
{
where = $" WHERE [{PId}] = {objq}";
}
else
{
where = $" WHERE [{PId}] = '{objq}'";
}
}
else
{
if (objq != null)
{
if (addDo)
{
changes += $"[{i.Name}] = '{objq}'";
addDo = false;
}
else
{
changes += $",[{i.Name}] = '{objq}'";
}
}
}
}
return FnExecute($"UPDATE [dbo].[{mTableName}] set {changes} {where}");
}
public int FnUpdate<T>(T obj, int PId, string mTableName)
{
string changes = "";
string where = "";
Type type = obj.GetType();
PropertyInfo[] ps = type.GetProperties();
bool addDo = true;
foreach (PropertyInfo i in ps)
{
Object objq = i.GetValue(obj, null);
if (i.Name == PId + "")
{
where = $" WHERE [{PId}] = {objq}";
continue;
}
if (objq != null)
{
if (addDo)
{
changes += $"[{i.Name}] = '{objq}'";
addDo = false;
}
else
{
changes += $",[{i.Name}] = '{objq}'";
}
}
}
return FnExecute($"UPDATE [dbo].[{mTableName}] set {changes} {where}");
}
public string FnAdd2<T>(T obj, string mTableName)
{
string key = "";
string val = "";
Type type = obj.GetType();
PropertyInfo[] ps = type.GetProperties();
bool addDo = true;
foreach (PropertyInfo i in ps)
{
Object objq = i.GetValue(obj, null);
if (objq != null)
{
if (addDo)
{
key += $"[{i.Name}]";
val += $"'{objq}'";
addDo = false;
}
else
{
key += $",[{i.Name}]";
val += $",'{objq}'";
}
}
}
return $"INSERT INTO [dbo].[{mTableName}] ({key}) VALUES ({val})";
}
public T FnDataTableToList<T>(DataTable dt)
{
return JsonConvert.DeserializeObject<T>(JsonConvert.SerializeObject(dt, new DataTableConverter()));
}
public static object FnOutObjExitPar(object obj, string mOutPar)
{
Type type = obj.GetType();
PropertyInfo[] ps = type.GetProperties();
foreach (PropertyInfo i in ps)
{
if (mOutPar.IndexOf(i.Name) < 0)
{
i.SetValue(obj, null);
}
}
return obj;
}
static JsonSerializerSettings mJsonSettings = new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore };
public static string ObjectToJson(object obj)
{
return JsonConvert.SerializeObject(obj, Formatting.None, mJsonSettings);
}
public static T JsonToObject<T>(string json)
{
return JsonConvert.DeserializeObject<T>(json);
}
}
}