/// <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 List <string>(); bool haveIdentity = false; foreach (PropertyInfo 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())); }