private void TvpSetup()
{
if (tvpSetupDone)
{
return;
}
tvpSetupDone = true;
const string makeTVP = "CREATE TYPE dbo.TVP_INT AS TABLE(id INT); ";
const string makeTVPNVarChar = "CREATE TYPE dbo.TVP_NVARCHAR AS TABLE(name NVARCHAR(255)); ";
var db = new DBTableMaker();
//if (!db.IfExists(tvpTypeInt))
if (!db.IfTypeExists(tvpTypeInt))
{
var handlerBase = new SQLHandlerBase();
handlerBase.DoQueryNonScaler(makeTVP);
}
if (!db.IfTypeExists(tvpTypeNVarChar))
{
var handlerBase = new SQLHandlerBase();
handlerBase.DoQueryNonScaler(makeTVPNVarChar);
}
}
//const string monsterACLInsert1 = @" "; // insert into acl table (acl, acel-list-hash)
//const string monsterACLInsert2 = @" "; // insert into acl-ace table
private int AclID(List <int> aceList, byte[] aclHash)
{
var sqlbase = new SQLHandlerBase();
var connectStr = sqlbase.ConnectionString;
int ID = -1;
//bool aclExists = false;
using (var connection = new SqlConnection(connectStr))
{
connection.Open();
using (SqlCommand cmd = new SqlCommand(monsterACLQuery, connection))
{
//IEnumerable<int> aceEnumerable = aceList.ToArray().AsEnumerable();
//IEnumerable<int> aceEnumerable = GetEnumerable<int>(aceList).c.ToList<int>();
var aceTable = aceList.ToDataTable("tvp");
var pList = new SqlParameter("@tvp", SqlDbType.Structured)
{
TypeName = tvpTypeInt,
Value = aceTable
};
var pHash = new SqlParameter("@hash", SqlDbType.VarBinary, aclHash.Length)
{
Value = aclHash
};
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(pList);
cmd.Parameters.Add(pHash);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
ID = dr.GetInt32(0);
//aclExists = true;
}
}
}
//if (!aclExists)
//{
// using (SqlCommand cmd = new SqlCommand(monsterACLInsert1, connection))
// {
// var pList = new SqlParameter("@tvp", SqlDbType.Structured)
// {
// TypeName = tvpTypeInt,
// Value = aceList
// };
// var pHash = new SqlParameter("@hash", SqlDbType.VarBinary, aclHash.Length)
// {
// Value = aclHash
// };
// cmd.Parameters.Add(pList);
// cmd.Parameters.Add(pHash);
// using (var dr = cmd.ExecuteReader())
// {
// while (dr.Read())
// {
// ID = dr.GetInt32(0);
// aclExists = true;
// }
// }
// }
//}
return(ID);
}
}
