public async Task <OperationResult> WriteDatabaseMetaToFile(string workingDirectory) { try { var prevMetaFile = Path.Join(workingDirectory, options.PreviousResultFile); //build index var idx = new ContractOrderDictionary(File.Exists(prevMetaFile) ? JsonConvert.DeserializeObject <DatabaseMeta>(File.ReadAllText(prevMetaFile)) : null); var resp = await GetDatabaseMeta(idx).ConfigureAwait(false); if (resp.Fail()) { return(new OperationResult(resp)); } var jsonFile = Path.Join(workingDirectory, options.ResultFile); File.WriteAllText(jsonFile, JsonConvert.SerializeObject(resp.Data, Formatting.Indented)); return(new OperationResult { StatusMessage = $"write succesfully database meta to: {jsonFile}" }); } catch (Exception ex) { return(new OperationResult(ex)); } }
public async Task <OperationResult <DatabaseMeta> > GetDatabaseMeta(ContractOrderDictionary idx) { var procsResult = await GetProcedures(idx).ConfigureAwait(false); if (procsResult.Fail() || procsResult.Data.EmptyIfNull().Any(r => r.Fail())) { return(new OperationResult <DatabaseMeta>($"Errors on collect procedures data: [{ string.Join(",", (procsResult as IOperationResult).Yield().Union(procsResult.Data.EmptyIfNull()).Where(i => i.Fail()).Select(i => i.StatusMessage).ToArray()) }]")); } else { SqlConnectionStringBuilder conBuilder = new SqlConnectionStringBuilder(options.ConnectionString); return(new OperationResult <DatabaseMeta>(new DatabaseMeta() { Name = DalUtils.StripUnderscorePrefix(conBuilder.InitialCatalog), Procedures = procsResult.Data.Select(i => i.Data).ToArray() })); } }
public async Task <OperationResult <List <OperationResult <ProcedureMeta> > > > GetProcedures(ContractOrderDictionary idx) { try { var rg = new Regex(options.ProcedurePattern); var procList = new List <string>(); using (var dr = new DataReader(options.ConnectionString)) { var dt = dr.GetSchema("Procedures", new string[] { null, null, null, "PROCEDURE" }); foreach (DataRow row in dt.Rows) { procList.Add(row["SPECIFIC_NAME"].ToString()); } } var list = new List <OperationResult <ProcedureMeta> >(); foreach (var procedureName in procList) { if (!rg.IsMatch(procedureName)) { continue; } list.Add(await GetProcedureParams(procedureName, idx).ConfigureAwait(false)); } var successfullSPs = list.Where(op => !op.IsFail()).Select((op, i) => op.Data).ToList(); return(new OperationResult <List <OperationResult <ProcedureMeta> > >(list)); } catch (Exception ex) { return(new OperationResult <List <OperationResult <ProcedureMeta> > >(ex.Message)); } }
public async Task <OperationResult <List <ResponseItem> > > GetProcedureResponseParams(List <ParamMeta> request, string spName, ContractOrderDictionary idx) { try { using (DataReader dr = new DataReader(options.ConnectionString)) { var responses = new List <ResponseItem>(); var cmd = GenerateProcedureRequestCommand(request, spName); await dr.ExecuteSpAsync(cmd).ConfigureAwait(false); var resultSetCount = 1; do { var resultName = $"ResultSet{resultSetCount}"; var currentRespones = GetProcedureResultSetParams(dr.Reader, spName); // Order = if (currentRespones.Any()) { var item = new ResponseItem { Name = "ResultSet0", Params = currentRespones, }; responses.Add(item); if (resultSetCount == 1 && item.IsOperationResult()) { continue; } item.Order = idx.GetOrAddResponseIndex(spName, resultName); item.Name = resultName; //fix items order foreach (var x in item.Params) { x.Order = idx.GetOrAddResponseResultSetIndex(spName, resultName, x.Name); } //increment resultset counter resultSetCount++; } }while (await dr.NextResultAsync().ConfigureAwait(false)); return(new OperationResult <List <ResponseItem> >(responses)); } } catch (Exception ex) { return(new OperationResult <List <ResponseItem> >($"{spName} get output params {ex.Message}")); } }
public static List <TvpParamMeta> GetTvp(string spName, string tvpName, string connectionString, ContractOrderDictionary idx) { int tvpId = 0; var list = new List <TvpParamMeta>(); using (DataReader dr = new DataReader(connectionString)) { using (SqlCommand command = new SqlCommand()) { command.CommandText = @" select tvp_schema = ss.name, tvp_name = stt.name, stt.type_table_object_id from sys.table_types stt inner join sys.schemas ss on stt.schema_id = ss.schema_id where stt.name=@tvpName "; command.Parameters.Add("@tvpName", SqlDbType.VarChar).Value = tvpName; command.CommandType = CommandType.Text; command.CommandTimeout = 60; dr.Execute(command); if (!dr.Reader.HasRows) { throw new Exception($@"could not found tvp {tvpName}"); } dr.Reader.Read(); tvpId = dr.Reader.GetInt32(dr.Reader.GetOrdinal("type_table_object_id")); } } using (DataReader dr = new DataReader(connectionString)) { using (SqlCommand command = new SqlCommand()) { command.CommandText = @" select sc.name, st.name as data_type, sc.is_nullable from sys.columns sc inner join sys.types st on sc.system_type_id = st.system_type_id and sc.user_type_id = st.user_type_id where sc.object_id = @tvp_id "; command.Parameters.Add("@tvp_id", SqlDbType.Int).Value = tvpId; command.CommandType = CommandType.Text; command.CommandTimeout = 60; dr.Execute(command); if (!dr.Reader.HasRows) { throw new Exception($@"could not found tvp props {tvpName} type_table_object_id:{tvpId}"); } while (dr.Reader.Read()) { var paramName = (string)dr.Reader.GetSqlString(dr.Reader.GetOrdinal("name")); list.Add(new TvpParamMeta { Name = paramName, SqlType = (string)dr.Reader.GetSqlString(dr.Reader.GetOrdinal("data_type")), IsNullable = (bool)dr.Reader.GetSqlBoolean(dr.Reader.GetOrdinal("is_nullable")), Order = idx.GetOrAddRequestTvpIndex(spName, tvpName, paramName) }); } } } return(list); }
public OperationResult <(bool hasSchemaParam, List <ParamMeta> data)> GetProcedureRequestParams(string spName, ContractOrderDictionary idx) { try { var schema = new DataTable(); bool hasSchemaParam = false; using (SqlConnection connection = new SqlConnection(options.ConnectionString)) { connection.Open(); SqlCommand getSchemaCmd = new SqlCommand(@" SELECT params.name AS PARAMETER_NAME , t.name AS SQL_TYPE , params.is_nullable AS HAS_DEFAULT FROM sys.parameters params INNER JOIN sys.procedures procs ON params.object_id = procs.object_id INNER JOIN sys.types t ON t.system_type_id = params.system_type_id AND params.user_type_id = t.user_type_id WHERE procs.name = @procName", connection); getSchemaCmd.Parameters.AddWithValue("@procName", spName); schema.Load(getSchemaCmd.ExecuteReader()); } var list = new List <ParamMeta>(); foreach (DataRow row in schema.Rows) { var paramName = row["PARAMETER_NAME"].ToString().Replace("@", ""); if (paramName.StartsWith("_schema", StringComparison.InvariantCultureIgnoreCase)) { hasSchemaParam = true; continue; } list.Add(new ParamMeta { Name = paramName, SqlType = row["SQL_TYPE"].ToString(), HasDefaultValue = (bool)row["HAS_DEFAULT"], Order = idx.GetOrAddRequestIndex(spName, paramName) }); } //check for tvp foreach (var x in list) { if (GetSlqDbType(x.SqlType) == SqlDbType.Structured) { x.TVP = GetTvp(spName, x.SqlType, options.ConnectionString, idx); } } return(new OperationResult <(bool, List <ParamMeta>)>((hasSchemaParam, list))); } catch (Exception ex) { return(new OperationResult <(bool, List <ParamMeta>)>($"[{spName}] {ex.Message}")); } }
public async Task <OperationResult <ProcedureMeta> > GetProcedureParams(string spName, ContractOrderDictionary idx) { var spmeta = new ProcedureMeta { SpName = spName }; try { var opReq = GetProcedureRequestParams(spName, idx); if (opReq.Fail()) { return(new OperationResult <ProcedureMeta>(opReq)); } spmeta.Request = opReq.Data.data; if (options.SkipOutputParams) { return(new OperationResult <ProcedureMeta>(spmeta)); } if (!opReq.Data.hasSchemaParam) { spmeta.Errors.Add($"{spName} do not have _schema in the input parameters, skip harvesting output params"); return(new OperationResult <ProcedureMeta> { Data = spmeta, StatusCode = (int)EOperationCode.Error, StatusMessage = $"{spName} do not have _schema in the input parameters, skip harvesting output params" }); } var opResp = await GetProcedureResponseParams(spmeta.Request, spName, idx).ConfigureAwait(false); if (opResp.Fail()) { return(new OperationResult <ProcedureMeta>(opResp.StatusCode, opResp.StatusMessage, spmeta)); } spmeta.Responses = opResp.Data; return(new OperationResult <ProcedureMeta>(spmeta)); } catch (Exception ex) { return(new OperationResult <ProcedureMeta>($"[{spName}] {ex.Message}")); } }