/// <summary>
/// 创建新表，返回影响的记录数
/// 本方法只用于创建包含联合主键的表
/// </summary>
/// <typeparam name="TableClass">自定义表格类</typeparam>
/// <param name="sugarClient">SqlSugarClient</param>
/// <param name="tableName">表名，为空值则为默认值</param>
/// <returns>影响的记录数</returns>
public static int CreateTable <TableClass>(SqlSugarClient sugarClient, string tableName = null)
{
if (sugarClient == null)
{
throw new NullReferenceException("null SqlSugarClient");
}
using IDbCommand cmd = sugarClient.Ado.Connection.CreateCommand();
//检查表名
if (string.IsNullOrEmpty(tableName))
{
tableName = SugarTableUtils.GetTableName <TableClass>();
}
//写入创建新表指令
cmd.CommandText = $"CREATE TABLE {tableName} (";
PropertyInfo[] properties = typeof(TableClass).GetProperties();
int i = 0;
List <string> primaryKeys = new();
bool haveIdentity = false;
foreach (var colInfo in properties)
{
i++;
//写入字段信息
cmd.CommandText +=
$"{SugarColUtils.GetColName(colInfo)} " +
$"{SugarColUtils.GetColType(colInfo)} " +
$"{SugarColUtils.ColIsNullable(colInfo)} " +
$"{SugarColUtils.ColIsIdentity(colInfo)}";
if (i != properties.Length)
{
cmd.CommandText += ",";
}
if (SugarColUtils.ColIsPrimaryKey(colInfo) && string.IsNullOrEmpty(SugarColUtils.ColIsIdentity(colInfo))
)
{
primaryKeys.Add(SugarColUtils.GetColName(colInfo));
}
if (!string.IsNullOrEmpty(SugarColUtils.ColIsIdentity(colInfo)))
{
haveIdentity = true;
}
}
if (primaryKeys.Count != 0 && !haveIdentity) //当有多主键时
{
cmd.CommandText +=
$",PRIMARY KEY({string.Join(",", primaryKeys)})";
}
cmd.CommandText += ")";
//检查数据库链接
sugarClient.Ado.CheckConnection();
int ret = cmd.ExecuteNonQuery();
if (!sugarClient.CurrentConnectionConfig.IsAutoCloseConnection)
{
sugarClient.Close();
}
return(ret);
}
/// <summary>
/// 删除表
/// </summary>
/// <typeparam name="TableClass">自定义表格类</typeparam>
/// <param name="sugarClient">SqlSugarClient</param>
/// <param name="tableName">表名，为空值则为默认值</param>
/// <returns>影响的记录数</returns>
public static int DeletTable <TableClass>(SqlSugarClient sugarClient, string tableName = null)
{
if (sugarClient == null)
{
throw new NullReferenceException("null SqlSugarClient");
}
using IDbCommand cmd = sugarClient.Ado.Connection.CreateCommand();
//检查表名
if (string.IsNullOrEmpty(tableName))
{
tableName = SugarTableUtils.GetTableName <TableClass>();
}
cmd.CommandText = $"DROP TABLE {tableName}";
//检查数据库链接
sugarClient.Ado.CheckConnection();
int ret = cmd.ExecuteNonQuery();
if (sugarClient.CurrentConnectionConfig.IsAutoCloseConnection)
{
sugarClient.Close();
}
return(ret);
}
/// <summary>
/// 查找是否存在同名表
/// </summary>
/// <typeparam name="TableClass">自定义表格类</typeparam>
/// <param name="sugarClient">SqlSugarClient</param>
/// <param name="tableName">表名可为空</param>
/// <returns>返回True/False代表是否存在</returns>
public static bool TableExists <TableClass>(SqlSugarClient sugarClient, string tableName = null)
{
if (sugarClient == null)
{
throw new NullReferenceException("null SqlSugarClient");
}
//检查表名
if (string.IsNullOrEmpty(tableName))
{
tableName = SugarTableUtils.GetTableName <TableClass>();
}
//获取所有表的信息
//ORM的返回值会返回不存在的表，暂时弃用
// List<DbTableInfo> tableInfos = sugarClient.DbMaintenance.GetTableInfoList();
// return tableInfos.Exists(table => table.Name == tableName);
//检查数据库链接
sugarClient.Ado.CheckConnection();
using IDbCommand cmd = sugarClient.Ado.Connection.CreateCommand();
cmd.CommandText = $"SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name = '{tableName}'";
return(Convert.ToBoolean(cmd.ExecuteScalar()));
}