/// <summary> /// HowTO: 3. 基本数据库操作(执行存储过程) /// </summary> static void HowTO_3() { Console.WriteLine("HowTO: 3. 调用存储过程, 返回DataSet"); TestAbstractDA da = new TestAbstractDA(); StoreProcedureInfo info = da.TestStoreProcedure("p_test", 2, DateTime.Now); Console.WriteLine("存储过程p_test的返回值:{0}", info.ReturnCode); foreach (string s in info.OutputParameters.Keys) { Console.WriteLine("\t输出参数:{0}:{1}", s, info.OutputParameters[s]); } if (info.DataSet != null && info.DataSet.Tables.Count > 0) { Console.WriteLine("\tDataSet:"); foreach (DataColumn c in info.DataSet.Tables[0].Columns) { Console.Write("\t{0}", c.ColumnName); } Console.WriteLine(); foreach (DataRow r in info.DataSet.Tables[0].Rows) { foreach (DataColumn c in info.DataSet.Tables[0].Columns) { Console.Write("\t{0}", r[c]); } Console.WriteLine(); } } }
public (DynamicResult, IList) Execute(StoreProcedureInfo storeProcedureInfo, string ConnectionString = null) { using (SqlConnection sqlConnection = new SqlConnection(ConnectionString ?? this.ConnectionString)) { using (SqlCommand sqlCommand = new SqlCommand(storeProcedureInfo.StoreProcedureName, sqlConnection)) { sqlCommand.CommandType = System.Data.CommandType.StoredProcedure; foreach (KeyValuePair <string, SqlParameter> param in storeProcedureInfo.Parameters) { sqlCommand.Parameters.Add(param.Value); } try { sqlConnection.Open(); SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(); DataTable schemaTable = sqlDataReader.GetSchemaTable(); Dictionary <string, object> mapper = new Dictionary <string, object>(); foreach (var parameterInfo in storeProcedureInfo.Parameters) { if (sqlCommand.Parameters[parameterInfo.Key].Direction > ParameterDirection.Input) { var vl = sqlCommand.Parameters[parameterInfo.Key].Value; mapper[parameterInfo.Key] = vl == DBNull.Value ? null : vl; } } IList ls = new ArrayList(); object value = null; for (; sqlDataReader.Read();) { Dictionary <string, object> _entity = new Dictionary <string, object>(); for (int i = 0; i < sqlDataReader.FieldCount; ++i) { if (sqlDataReader.GetValue(i) is DBNull) { value = null; } else { value = Convert.ChangeType(sqlDataReader.GetValue(i), sqlDataReader.GetFieldType(i)); } _entity[sqlDataReader.GetName(i)] = value; } ls.Add(new DynamicResult(_entity)); } return(new DynamicResult(mapper), ls); } catch (Exception ex) { throw ex; } } } }
private bool CheckList ( IDictionary <string, StoreProcedureInfo> allowExecuteWhiteList , string storeProcedureAliasOrName , string httpMethod , out StoreProcedureInfo storeProcedureInfo ) { var r = false; HttpMethodsFlags httpMethodsFlag; storeProcedureInfo = null; r = Enum .TryParse <HttpMethodsFlags> ( httpMethod , true , out httpMethodsFlag ); if (r) { r = allowExecuteWhiteList .TryGetValue ( storeProcedureAliasOrName , out storeProcedureInfo ); if (r) { r = storeProcedureInfo .AllowedHttpMethods .HasFlag(httpMethodsFlag); } } return(r); }
/// <summary> /// 测试存储过程调用 /// </summary> public StoreProcedureInfo TestStoreProcedure(string ProcedureName, params object[] values) { StoreProcedureInfo info = ExecuteProcedureDataSet(ProcedureName, values); return(info); }
GetDataBasesConnectionsInfoProcess ( string dbConnectionsJsonFile = "dbConnections.json" ) { var configurationBuilder = new ConfigurationBuilder() .AddJsonFile(dbConnectionsJsonFile); var configuration = configurationBuilder.Build(); var result = configuration .GetSection("Connections") .AsEnumerable() .Where ( (x) => { return (!x .Value .IsNullOrEmptyOrWhiteSpace()); } ) .GroupBy ( (x) => { var key = x.Key; var i = key.FindIndex(":", 2); var rr = key.Substring(0, i); return(rr); } ) .ToDictionary ( (x) => { var r = configuration[$"{x.Key}ConnectionID"]; return(r); } , (x) => { var allowExecuteWhiteList = configuration .GetSection($"{x.Key}WhiteList") .AsEnumerable() .Where ( (xx) => { var v = xx.Value; var rr = !v.IsNullOrEmptyOrWhiteSpace(); return(rr); } ) .GroupBy ( (xx) => { var key = xx.Key; var i = key.FindIndex(":", 4); var rr = key.Substring(0, i); return(rr); } ) .ToDictionary ( (xx) => { var key = configuration[$"{xx.Key}StoreProcedureAlias"]; var storeProcedureName = configuration[$"{xx.Key}StoreProcedureName"]; if (key.IsNullOrEmptyOrWhiteSpace()) { key = storeProcedureName; } return(key); } , (xx) => { var storeProcedureName = configuration[$"{xx.Key}StoreProcedureName"]; var s = configuration[$"{xx.Key}AllowedHttpMethods"]; var allowedHttpMethods = Enum .Parse <HttpMethodsFlags> ( s , true ); var rr = new StoreProcedureInfo() { Alias = xx.Key , Name = storeProcedureName , AllowedHttpMethods = allowedHttpMethods }; return (rr); } , StringComparer .OrdinalIgnoreCase ); //var connectionTimeoutInSeconds = 120; //int.TryParse // ( // configuration[$"{x.Key}ConnectionTimeoutInSeconds"] // , out connectionTimeoutInSeconds // ); var r = new DataBaseConnectionInfo() { ConnectionID = configuration[$"{x.Key}ConnectionID"] , ConnectionString = configuration[$"{x.Key}ConnectionString"] //, ConnectionTimeoutInSeconds = connectionTimeoutInSeconds , DataBaseType = Enum.Parse <DataBasesType>(configuration[$"{x.Key}DataBaseType"], true) , AllowExecuteWhiteList = allowExecuteWhiteList }; return(r); } , StringComparer .OrdinalIgnoreCase ); return(result); }