/// <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);
}
