/// <summary>
/// 创建新的表
/// </summary>
/// <typeparam name="T"></typeparam>
public static void CreateNewTable <T>(string ConStrName = null)
{
TSQLModel tsqlM = new TSQLModel();
Type tType = typeof(T);
TableModelAttribute[] tableMAtts = (TableModelAttribute[])tType.GetCustomAttributes(typeof(TableModelAttribute), false);
tsqlM.SQLStr = string.Format("create table {0} (", tableMAtts[0].DBTableName);
PropertyInfo[] props = tType.GetProperties();
ColumnModelAttribute cma;
foreach (PropertyInfo prop in props)
{
foreach (Attribute attr in Attribute.GetCustomAttributes(prop))
{
if (attr.GetType() == typeof(ColumnModelAttribute))
{
cma = attr as ColumnModelAttribute;
tsqlM.SQLStr += string.Format("{0} {1}, ", cma.DBColumnName, cma.DBType);
}
}
}
int SQLLength = tsqlM.SQLStr.Length;
tsqlM.SQLStr = tsqlM.SQLStr.Remove(SQLLength - 2) + ")";
ExecuteNonQuery(tsqlM.SQLStr, null, ConStrName);
}
/// <summary>
/// 添加
/// </summary>
/// <typeparam name="T">要添加的类型</typeparam>
/// <param name="model">要添加的实体</param>
/// <param name="returnInserID">返回刚刚添加的ID</param>
/// <returns>T-SQL对象</returns>
public static TSQLModel InsertTSQL <T>(T model, bool returnInserID = false)
{
TSQLModel tsqlM = new TSQLModel();
Type tType = typeof(T);
TableModelAttribute[] tableMAtts = (TableModelAttribute[])tType.GetCustomAttributes(typeof(TableModelAttribute), false);
tsqlM.SQLStr = string.Format("Insert into {0} (", tableMAtts[0].DBTableName);
string parameterName = "";
string valuesStr = "values(";
PropertyInfo[] props = tType.GetProperties();
ColumnModelAttribute cma;
foreach (PropertyInfo prop in props)
{
foreach (Attribute attr in Attribute.GetCustomAttributes(prop))
{
if (attr.GetType() == typeof(ColumnModelAttribute))
{
cma = attr as ColumnModelAttribute;
parameterName = string.Format("@{0}", cma.DBColumnName);
tsqlM.SQLParameters.Add(new TSQLParameter(parameterName, prop.GetValue(model)));
if (!cma.AutoNumber)
{
tsqlM.SQLStr += string.Format("[{0}], ", cma.DBColumnName);
valuesStr += parameterName + ", ";
}
}
}
}
int SQLLength = tsqlM.SQLStr.Length;
int valuesLength = valuesStr.Length;
tsqlM.SQLStr = tsqlM.SQLStr.Remove(SQLLength - 2) + ") " + valuesStr.Remove(valuesLength - 2) + ");";
if (returnInserID)
{
tsqlM.SQLStr += "select @@IDENTITY;";
}
return(tsqlM);
}
/// <summary>
/// 修改
/// </summary>
/// <typeparam name="T">要修改的类型</typeparam>
/// <param name="model">要修改的实体</param>
/// <returns>T-SQL对象</returns>
public static TSQLModel UpdateTSQL <T>(T model)
{
TSQLModel tsqlM = new TSQLModel();
Type tType = typeof(T);
TableModelAttribute[] tableMAtts = (TableModelAttribute[])tType.GetCustomAttributes(typeof(TableModelAttribute), false);
tsqlM.SQLStr = string.Format("Update {0} set ", tableMAtts[0].DBTableName);
string parameterName = "";
string whereStr = " where";
PropertyInfo[] props = tType.GetProperties();
ColumnModelAttribute cma;
foreach (PropertyInfo prop in props)
{
foreach (Attribute attr in Attribute.GetCustomAttributes(prop))
{
if (attr.GetType() == typeof(ColumnModelAttribute))
{
cma = attr as ColumnModelAttribute;
parameterName = string.Format("@{0}", cma.DBColumnName);
tsqlM.SQLParameters.Add(new TSQLParameter(parameterName, prop.GetValue(model)));
if (tableMAtts[0].PrimaryKey != cma.DBColumnName)
{
tsqlM.SQLStr += string.Format("[{0}] = ", cma.DBColumnName) + parameterName + ", ";
}
else
{
whereStr += string.Format("[{0}] = ", cma.DBColumnName) + parameterName;
}
}
}
}
int Length = tsqlM.SQLStr.Length;
tsqlM.SQLStr = tsqlM.SQLStr.Remove(Length - 2) + whereStr;
return(tsqlM);
}
/// <summary>
/// 删除
/// 模型需要特性 TableModelAttribute ColumnModelAttribute
/// </summary>
/// <typeparam name="T">要删除的类型</typeparam>
/// <param name="model">要删除的实体</param>
/// <returns>T-SQL对象</returns>
public static TSQLModel DeleteTSQL <T>(T model)
{
TSQLModel tsqlM = new TSQLModel();
Type tType = typeof(T);
TableModelAttribute[] tableMAtts = (TableModelAttribute[])tType.GetCustomAttributes(typeof(TableModelAttribute), false);
tsqlM.SQLStr = string.Format("Delete from {0} where ", tableMAtts[0].DBTableName);
string parameterName = "";
PropertyInfo[] props = tType.GetProperties();
ColumnModelAttribute cma;
bool isReady = false;
foreach (PropertyInfo prop in props)
{
if (!isReady)
{
foreach (Attribute attr in Attribute.GetCustomAttributes(prop))
{
if (attr.GetType() == typeof(ColumnModelAttribute))
{
cma = attr as ColumnModelAttribute;
if (tableMAtts[0].PrimaryKey == cma.DBColumnName)
{
parameterName = string.Format("@{0}", cma.DBColumnName);
tsqlM.SQLParameters.Add(new TSQLParameter(parameterName, prop.GetValue(model)));
tsqlM.SQLStr += string.Format("[{0}] = ", cma.DBColumnName) + parameterName;
isReady = true;
break;
}
}
}
}
}
return(tsqlM);
}
/// <summary>
/// 执行查询语句或存储过程
/// </summary>
/// <param name="tsqM">T-SQL对象</param>
/// <param name="ConStrName">链接字符串</param>
/// <returns>查询结果</returns>
public static DataSet ExecuteQuery(TSQLModel tsqM, string ConStrName = null)
{
return(ExecuteQuery(tsqM.SQLStr, tsqM.GetMSSQLParameter()));
}
/// <summary>
/// 删除
/// </summary>
/// <typeparam name="T">要删除的类型</typeparam>
/// <param name="model">要删除的实体</param>
/// <param name="ConStrName">链接字符串</param>
/// <returns>影响的行数</returns>
public static int Delete <T>(T model, string ConStrName = null)
{
TSQLModel tsqlM = TSQLManager.DeleteTSQL(model);
return(ExecuteNonQuery(tsqlM.SQLStr, tsqlM.GetSQLParameters <SqlParameter>(), ConStrName));
}
/// <summary>
/// 添加
/// </summary>
/// <typeparam name="T">要添加的类型</typeparam>
/// <param name="model">要添加的实体</param>
/// <param name="ConStrName">链接字符串</param>
/// <returns>影响的行数</returns>
public static int Insert <T>(T model, string ConStrName = null)
{
TSQLModel tsqlM = TSQLManager.InsertTSQL(model);
return(ExecuteNonQuery(tsqlM.SQLStr, tsqlM.GetSQLParameters <SQLiteParameter>(), ConStrName));
}
