TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
ahmed elbarbary
NA
1.6k
275.3k
How to run query based on multi thread using clr function sp
Dec 4 2019 9:09 PM
I work on visual studio 2019 asp.net core 2.2 and sql server 2012
I need to run query every 2 seconds work on background based on 4 threads only
below is clr function but i need to apply below code on asp.net core
so that How to call function spExecuteParallel under action controller
like that
public Actionresult executequery()
{
write here calling clr function
}
so that my question
How to apply clr function on asp.net core 2
or there are any thing can do that on asp.net core
what i try
class
ExecuteSQL
{
private
List<
string
> oExecuteErrors;
private
object
oExecuteLocker;
private
string
sExecuteServer;
private
string
sExecuteDB;
private
string
sExecuteTSQL;
private
int
iExecuteRetries;
public
ExecuteSQL(
string
sServer,
string
sDB,
string
sTSQL,
int
iRetries,
ref
List<
string
> oErrors,
ref
object
oLocker)
{
this
.sExecuteServer = sServer;
this
.sExecuteDB = sDB;
this
.sExecuteTSQL = sTSQL;
this
.iExecuteRetries = iRetries;
this
.oExecuteErrors = oErrors;
this
.oExecuteLocker = oLocker;
}
public
void
Process()
{
int
iTries = 1;
SqlConnection oConn =
new
SqlConnection();
Retry:
oConn =
new
SqlConnection(
"Data Source="
+ sExecuteServer +
";Initial Catalog="
+ sExecuteDB +
";Integrated Security=SSPI;"
);
try
{
oConn.Open();
if
(oConn.State == ConnectionState.Open)
{
SqlCommand oCmd = oConn.CreateCommand();
oCmd.CommandText = sExecuteTSQL;
oCmd.CommandTimeout = 0;
oCmd.ExecuteNonQuery();
oCmd.Dispose();
oConn.Close();
oConn.Dispose();
}
else
{
throw
new
Exception(
"SQL Server not Found or Unable to Connect to SQL Server"
);
}
}
catch
(Exception ex)
{
if
(oConn.State != ConnectionState.Closed) oConn.Close();
oConn.Dispose();
if
(iTries <= iExecuteRetries)
{
Thread.Sleep(5000);
iTries += 1;
goto
Retry;
}
else
{
lock
(oExecuteLocker)
{
char
cSpace =
char
.Parse(
" "
);
oExecuteErrors.Add(
this
.sExecuteDB.PadRight(16, cSpace) +
" : "
+ ex.Message);
}
}
}
}
}
}
namespace
SqlServerProjectSp
{
public
partial
class
StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public
static
SqlInt32 spExecuteParallel(
string
DB,
int
MaxDOP,
string
TSQL,
int
msDelay,
int
Retries)
{
SqlConnection oConn =
new
SqlConnection();
SqlCommand oCmd =
new
SqlCommand();
List<
string
> oErrorString =
new
List<
string
>();
object
oLocker =
new
object
();
string
sServer =
null
;
List<Thread> oThread =
new
List<Thread>();
StringCollection sStopped =
new
StringCollection();
if
(
string
.IsNullOrEmpty(TSQL))
{
return
0;
}
// Get Server Instance Name
oConn =
new
SqlConnection(
"context connection = true;"
);
oConn.Open();
oCmd = oConn.CreateCommand();
oCmd.CommandText =
"SELECT @@SERVERNAME"
;
sServer = oCmd.ExecuteScalar().ToString();
oCmd.Dispose();
oConn.Close();
oConn.Dispose();
// Execute Threads
int
iCurrentThread = 0;
while
(iCurrentThread < MaxDOP)
{
ExecuteSQL Executer =
new
ExecuteSQL
(sServer, DB, TSQL.Replace(
"?"
, DB.ToString().Trim()), Retries,
ref
oErrorString,
ref
oLocker);
Thread oItem =
new
Thread(Executer.Process);
oItem.Name =
"ExecuteSQL "
+ DB.ToString().Trim();
oItem.Start();
oThread.Add(oItem);
SqlContext.Pipe.Send(DateTime.Now.ToLongTimeString() +
" : Start : "
+ oItem.Name.Replace(
"ExecuteSQL "
,
""
));
Thread.Sleep(msDelay);
while
(RunningThreads(
ref
oThread,
ref
sStopped) >= MaxDOP)
{
Thread.Sleep(1000);
}
iCurrentThread++;
}
// Wait for all Threads to Stop
while
(RunningThreads(
ref
oThread,
ref
sStopped) > 0)
{
Thread.Sleep(1000);
}
SqlContext.Pipe.Send(
"All Thread have Stopped with "
+
oErrorString.Count.ToString() +
" Error/s "
);
if
(oErrorString.Count > 0)
{
foreach
(
string
sIndividualErrors
in
oErrorString)
{
SqlContext.Pipe.Send(sIndividualErrors.ToString());
}
throw
new
Exception(
"Error Occurred."
);
}
return
0 - oErrorString.Count;
}
public
static
int
RunningThreads(
ref
List<Thread> oThread,
ref
StringCollection oStops)
{
int
iRunningCount = 0;
foreach
(Thread oIndividualThread
in
oThread)
{
if
(oIndividualThread.IsAlive)
{
iRunningCount += 1;
}
else
if
(!oStops.Contains(oIndividualThread.Name))
{
oStops.Add(oIndividualThread.Name);
SqlContext.Pipe.Send(DateTime.Now.ToLongTimeString() +
" : Stop : "
+ oIndividualThread.Name.Replace(
"ExecuteSQL "
,
""
));
}
}
return
iRunningCount;
}
}
}
Reply
Answers (
1
)
Implement map in GrideView
autofac with webapi controller registry issue