private static async Task <IReadOnlyCollection <string> > GetSqlitePrimaryKeys(DbConnection dbConnection, DBObjectName tableOrView) { await dbConnection.AssureOpenAsync(); string sql = $"PRAGMA table_info({tableOrView.ToString(false, true)}) "; var cmd = dbConnection.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = System.Data.CommandType.Text; using (var rdr = await cmd.ExecuteReaderAsync()) { if (!rdr.HasRows) { return(Array.Empty <string>()); } List <string> list = new List <string>(); while (rdr.Read()) { bool isPk = rdr.GetBoolean("pk"); if (isPk) { list.Add(rdr.GetNString("name") !); } } return(list); } }
public async Task <SqlFieldDescription[]> GetSPResultSetByUsingExecute(DbConnection dbConnection, DBObjectName model, IReadOnlyDictionary <string, object?> fallBackExecutionParameters) { await dbConnection.AssureOpenAsync(); ValidateNoSuspicousSql(model.Schema, false); ValidateNoSuspicousSql(model.Name, false); string procName = model.ToString(false, true); string paramText = string.Join(", ", fallBackExecutionParameters.Select(s => "@" + ValidateNoSuspicousSql(s.Key, true) + "=@" + s.Key)); string commandText = $@"set xact_abort on begin tran exec {procName} {paramText} rollback"; var cmd = dbConnection.CreateCommand(); cmd.CommandText = commandText; cmd.CommandType = System.Data.CommandType.Text; foreach (var prms in fallBackExecutionParameters) { cmd.AddCommandParameter(prms.Key, prms.Value); } using (var res = await cmd.ExecuteReaderAsync()) { res.Read(); return(Enumerable.Range(0, res.FieldCount) .Select(i => new SqlFieldDescription( dbType: SqlType.GetSomeSqlType(res.GetFieldType(i)), name: res.GetName(i), isNullable: true, maxLength: null )).ToArray()); } }
private static async Task <IReadOnlyCollection <SqlFieldDescription> > GetSqliteColumns(DbConnection dbConnection, DBObjectName tableOrView) { await dbConnection.AssureOpenAsync(); string sql = $"PRAGMA table_info({tableOrView.ToString(false, true)}) "; var cmd = dbConnection.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = System.Data.CommandType.Text; using (var rdr = await cmd.ExecuteReaderAsync()) { if (!rdr.HasRows) { return(Array.Empty <SqlFieldDescription>()); } List <SqlFieldDescription> list = new List <SqlFieldDescription>(); while (rdr.Read()) { list.Add(new SqlFieldDescription( isNullable: !rdr.GetBoolean("notnull"), name: rdr.GetNString("name") !, dbType: SqlType.GetSqlType(rdr.GetNString("type") !), maxLength: -1 )); } return(list); } }
/// <summary> /// Get all parameter names of a Stored Procedure /// </summary> /// <returns></returns> public async Task <IReadOnlyCollection <SPParameter> > GetSPParameters(DBObjectName storedProcedure, DbConnection con) { var cachedValue = await cache.TryGetValue(storedProcedure); if (cachedValue != null) { return(cachedValue); } string command = @"SELECT PARAMETER_NAME, DATA_TYPE, USER_DEFINED_TYPE_SCHEMA, USER_DEFINED_TYPE_NAME, PARAMETER_MODE, CHARACTER_MAXIMUM_LENGTH FROM information_schema.parameters WHERE SPECIFIC_NAME = @SPName AND SPECIFIC_SCHEMA=isnull(@Schema, schema_NAME()) AND PARAMETER_NAME<>''"; await con.AssureOpenAsync(); DbCommand cmd = con.CreateCommand(); cmd.CommandText = command; cmd.AddCommandParameter("@SPName", storedProcedure.Name) .AddCommandParameter("@Schema", storedProcedure.Schema); List <SPParameter> resultL = new List <SPParameter>(); using (var reader = await cmd.ExecuteReaderAsync()) { if (reader.HasRows) { while (reader.Read()) { var name = reader.GetString(0); name = name.StartsWith("@") ? name.Substring(1) : name; string type = reader.GetString(1); (string userDefinedSchema, string userDefinedName) = (reader.GetNString(2) !, reader.GetNString(3) !); DBObjectName?userDefinedType = type == "table type" ? new DBObjectName(userDefinedSchema, userDefinedName) : null; string parameterMode = reader.GetString(4); System.Data.ParameterDirection?parameterDirection = parameterMode.Equals("IN", System.StringComparison.CurrentCultureIgnoreCase) ? System.Data.ParameterDirection.Input: parameterMode.Equals("OUT", System.StringComparison.CurrentCultureIgnoreCase) ? System.Data.ParameterDirection.Output: parameterMode.Equals("INOUT", System.StringComparison.CurrentCultureIgnoreCase) ? System.Data.ParameterDirection.InputOutput : (System.Data.ParameterDirection?)null; if (parameterDirection == null) { logger.LogWarning($"Cannot parse Parameter mode {parameterMode} of {name} of {storedProcedure}"); } int?maxLength = reader.GetNInt32(5); resultL.Add(new SPParameter(name, type, userDefinedType, parameterDirection ?? System.Data.ParameterDirection.Input, maxLength)); } } } var result = resultL.ToArray(); await cache.TryAdd(storedProcedure.ToString(), result); return(result); }
public async Task <IReadOnlyCollection <SqlFieldDescription> > GetTableTypeFields(DbConnection dbConnection, DBObjectName tableType) { string sql = $@" SELECT c.name as ColumnName, CASE WHEN t.name ='sysname' THEN 'nvarchar' ELSE t.name END AS TypeName, c.is_nullable, c.max_length FROM sys.columns c inner join sys.types t ON t.user_type_id=c.user_type_id WHERE object_id IN ( SELECT tt.type_table_object_id FROM sys.table_types tt inner join sys.schemas sc ON sc.schema_id=tt.schema_id WHERE tt.name = @Name and sc.name=isnull(@Schema, schema_NAME()) );"; await dbConnection.AssureOpenAsync(); var cmd = dbConnection.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = System.Data.CommandType.Text; cmd.AddCommandParameter("@Name", tableType.Name); cmd.AddCommandParameter("@Schema", tableType.Schema); List <SqlFieldDescription> list = new List <SqlFieldDescription>(); using (var rdr = await cmd.ExecuteReaderAsync()) { while (rdr.Read()) { list.Add(new SqlFieldDescription( isNullable: rdr.GetBoolean("is_nullable"), name: rdr.GetNString("ColumnName") !, dbType: SqlType.GetSqlType(rdr.GetNString("TypeName") !), maxLength: getMaxLength(Convert.ToInt32(rdr.GetValue(3)), rdr.GetNString("TypeName")) )); } } return(list.ToArray()); }
public async Task <IReadOnlyCollection <(DBObjectName Name, TableOrViewType Type)> > GetTablesAndViews(DbConnection dbConnection, TableOrViewType?typeFilter = null) { await dbConnection.AssureOpenAsync(); bool sqlite = dbConnection.IsSQLite(); string sql = sqlite ? $"SELECT name, type from sqlite_master" : "SELECT TABLE_NAME, TABLE_TYPE, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES"; if (sqlite && typeFilter == null) { sql += " WHERE type = 'view' OR type = 'table'"; } else if (sqlite) { sql += " WHERE type = @type"; } if (!sqlite && typeFilter != null) { sql += " WHERE TABLE_TYPE=@Type"; } string?filterType = null; if (sqlite && typeFilter == TableOrViewType.Table) { filterType = "table"; } else if (sqlite && typeFilter == TableOrViewType.View) { filterType = "view"; } else if (typeFilter == TableOrViewType.Table) { filterType = "BASE TABLE"; } else if (typeFilter == TableOrViewType.View) { filterType = "VIEW"; } var cmd = dbConnection.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = System.Data.CommandType.Text; cmd.AddCommandParameter("@type", filterType); using (var rdr = await cmd.ExecuteReaderAsync()) { if (!rdr.HasRows) { return(Array.Empty <(DBObjectName name, TableOrViewType type)>()); } List <(DBObjectName name, TableOrViewType type)> list = new(); while (rdr.Read()) { string name = rdr.GetString(0); string type = rdr.GetString(1).ToUpper(); string? schema = sqlite ? null : rdr.GetString(2); TableOrViewType typeT = type == "VIEW" ? TableOrViewType.View : TableOrViewType.Table; list.Add((new DBObjectName(schema, name), typeT)); } return(list); } }
/// <summary> /// Gets the return fields of the first result set of a procecure /// </summary> /// <param name="model">The procedure</param> /// <param name="persistResultSets">True to save those result sets in ResultSets Folder</param> /// <param name="fallBackExecutionParameters">If you set this parameter and sp_describe_first_result_set does not work, /// the procedure will get executed to retrieve results. Pay attention to provide wise options!</param> /// <returns></returns> public async Task <IReadOnlyCollection <SqlFieldDescription> > GetSPResultSet(DbConnection dbConnection, DBObjectName model, string?persistResultSetPath, IReadOnlyDictionary <string, object?>?fallBackExecutionParameters = null) { SqlFieldDescription[]? dataToWrite = null; var cachejsonFile = persistResultSetPath != null?System.IO.Path.Combine(persistResultSetPath, model.ToString() + ".json") : null; try { List <SqlFieldDescription> resultSet = new List <SqlFieldDescription>(); await dbConnection.AssureOpenAsync(); using (var rdr = await dbConnection.CreateSPCommand("sp_describe_first_result_set") .AddCommandParameter("tsql", model.ToString()) .ExecuteReaderAsync()) { while (rdr.Read()) { resultSet.Add(new SqlFieldDescription( name: rdr.GetNString("name") !, dbType: SqlType.GetSqlType(rdr.GetNString("system_type_name") !), isNullable: rdr.GetBoolean("is_nullable"), maxLength: getMaxLength(Convert.ToInt32(rdr.GetValue(rdr.GetOrdinal("max_length")) !), SqlType.GetSqlType(rdr.GetNString("system_type_name") !).DbType) )); } } if (persistResultSetPath != null) { if (resultSet.Count > 0) { try { if (!System.IO.Directory.Exists(persistResultSetPath)) { System.IO.Directory.CreateDirectory(persistResultSetPath); } var jsAr = resultSet.Select(s => s.Serialize()).ToArray(); string json = SerializeJson(jsAr); System.IO.File.WriteAllText(cachejsonFile !, json); } catch (Exception ercache) { logger.LogWarning("Could not cache Results set of {0}. Reason:\r\n{1}", model, ercache); } } } dataToWrite = resultSet .Cast <SqlFieldDescription>() .ToArray(); } catch (Exception err) { logger.LogError(err, $"Error getting result set from {model}"); if (fallBackExecutionParameters != null) { dataToWrite = await GetSPResultSetByUsingExecute(dbConnection, model, fallBackExecutionParameters); if (cachejsonFile != null) { if (!System.IO.Directory.Exists(persistResultSetPath)) { System.IO.Directory.CreateDirectory(persistResultSetPath !); } var jsAr = dataToWrite.Select(s => s.Serialize()).ToArray(); var json = SerializeJson(jsAr); try { System.IO.File.WriteAllText(cachejsonFile, json); } catch { logger.LogWarning($"Cound not write cache file {cachejsonFile}"); } } } else { dataToWrite = null; } } if (dataToWrite == null && persistResultSetPath != null && System.IO.File.Exists(cachejsonFile)) { try { // Not Sucessfully gotten data var json = System.IO.File.ReadAllText(cachejsonFile); var resJS = DeserializeJson <List <Dictionary <string, object> > >(json); var res = resJS.Select(s => SqlFieldDescription.FromJson((IReadOnlyDictionary <string, object?>)s)).ToArray(); return(res.Cast <SqlFieldDescription>().ToArray()); } catch (Exception err) { logger.LogWarning("Could not get cache {0}. Reason:\r\n{1}", model, err); } } return(dataToWrite ?? new SqlFieldDescription[] { }); }
private static async Task <IReadOnlyCollection <SqlFieldDescription> > GetDatabaseColumnMetadata(DbConnection dbConnection, DBObjectName tableOrView, string informationschema_table) { string sql = $@" SELEcT IS_NULLABLE AS is_nullable, COLUMN_NAME as ColumnName, DATA_TYPE as TypeName, CHARACTER_MAXIMUM_LENGTH as MaxLength, TABLE_SCHEMA FROM INFORMATION_SCHEMA.{informationschema_table} WHERE TABLE_NAME= @Name AND (TABLE_SCHEMA=@Schema OR @Schema is null)" ; await dbConnection.AssureOpenAsync(); var cmd = dbConnection.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = System.Data.CommandType.Text; cmd.AddCommandParameter("@Name", tableOrView.Name); cmd.AddCommandParameter("@Schema", tableOrView.Schema); using (var rdr = await cmd.ExecuteReaderAsync()) { if (!rdr.HasRows) { return(Array.Empty <SqlFieldDescription>()); } List <SqlFieldDescription> list = new List <SqlFieldDescription>(); int schemaOrdinal = rdr.GetOrdinal("TABLE_SCHEMA"); int maxLengthOrdinal = rdr.GetOrdinal("MaxLength"); string?lastSchema = null; while (rdr.Read()) { string?schema = rdr.GetString(schemaOrdinal); if (lastSchema == null) { lastSchema = schema; } else if (schema != lastSchema) { throw new InvalidOperationException("Schema Name is not given"); } object nullable = rdr.GetValue(rdr.GetOrdinal("is_nullable")); object?maxLength = rdr.IsDBNull(maxLengthOrdinal) ? null : rdr.GetValue(maxLengthOrdinal); list.Add(new SqlFieldDescription( isNullable: (nullable as string)?.ToUpperInvariant().Trim() == "YES" || nullable as Boolean? == true || nullable as int? == 1, name: rdr.GetNString("ColumnName") !, dbType: SqlType.GetSqlType(rdr.GetNString("TypeName") !), maxLength: maxLength == null ? null : maxLength is int i ? i : maxLength is long l && l > (long)int.MaxValue ? null : maxLength is ulong l2 && l2 > (ulong)int.MaxValue ? null : Convert.ToInt32(maxLength) )); } return(list); } }