Hi all,
sorry if this is in another post - I did a few searches and found nothing.
We have a custom asp .net ui and are rendering our reports directly to pdf.
We are running sql server stored procedures to populate our reports. What I
have noticed, however, during the course of testing the reports, is that if
you look in SQL Server EM the connections (listed as .Net SQL Client
Provider) seem to stay open for quite some time. This is a concern, and I am
trying to figure out if there is a way that we can close the connection(s) as
soon as the data has been retrieved and the report populated. I believe this
must be the connection from RS directly calling the SP to populate - has
anyone else seen this?
Thanks,Perhaps you can set some timers in the rsconfig.
I've noticed that my oracle connenctions stay open relatively long to;
although it has something to do with congestion on either de DB-server or the
RS-server. I notice that when I automticly rennder a number of reports (to
make chached-reports for all parameter values) the RS-jobs sometimes heap up
and the open connections do also (although there are never more than tree
reports rendered at the same time). The problem solves itself automaticly
thoug, everything slows down and apperantly RS gets time to clean it up.
"Myles" wrote:
> Hi all,
> sorry if this is in another post - I did a few searches and found nothing.
> We have a custom asp .net ui and are rendering our reports directly to pdf.
> We are running sql server stored procedures to populate our reports. What I
> have noticed, however, during the course of testing the reports, is that if
> you look in SQL Server EM the connections (listed as .Net SQL Client
> Provider) seem to stay open for quite some time. This is a concern, and I am
> trying to figure out if there is a way that we can close the connection(s) as
> soon as the data has been retrieved and the report populated. I believe this
> must be the connection from RS directly calling the SP to populate - has
> anyone else seen this?
> Thanks,
Showing posts with label closing. Show all posts
Showing posts with label closing. Show all posts
Wednesday, March 7, 2012
Closing Tags
Is there a way to tell SQL Server not to contract closing tags for empty elements? I'm getting a conflict in an app in trying to determine changed data. I create an XML DOM document and write the xml to an xml column in the database.
If I write "<SomeOuterElement><SomeInnerElement></SomeInnerElement></SomeOuterElement>"
the column actually stores: "<SomeOuterElement><SomeInnerElement/></SomeOuterElement>"
Which is great for optimization, but the dom produces empty elements with separate closing tags. So when compared as strings, they appear to be different. (I don't have access to the code that's doing the actual comparison. It's a bit of a black box situation.)
So I need SQL Server to leave the XML alone, or I need a way for the DOM to produce the optimized XML. (This is VB. NET)
Any ideas? Thanks.
J
Whether an empty element is serialized as or should not matter as both ways are semantically equivalent, the element is empty both ways.
If you are using the .NET DOM implementation and want to serialize an empty element as then use the property IsEmpty as in this snippet:
Code Snippet
Dim XmlDoc As XmlDocument = New XmlDocument()
Dim Element As XmlElement = XmlDoc.CreateElement("root")
Dim Element1 As XmlElement = XmlDoc.CreateElement("element")
Element1.IsEmpty = True
Element.AppendChild(Element1)
XmlDoc.AppendChild(Element)
XmlDoc.Save(Console.Out)
But note that you get <element /> that way and not <element/> so string comparison might fail again. You will have to implement your own XmlWriter if you need more control over the serialization details. To implement your own XmlWriter you often subclass XmlTextWriter. However such attempts to make applications happy that don't understand XML fully is in my view an attempt to fix things on the wrong end, it should not matter to an application whether an empty element is marked up as <element/> or <element /> or <element></element>.
closing SQL Connection
hello to all
i am finding my self in confusing problem.
everyay log file of my application increase by 10 GB.
every time i found more than 100 connections open in SDQL Server 2005 under my database.
i an using SQLHELPER Class by microsoft.i ma not putting my code in try-catch block. example
of one of my frequently used function is as follows:
protected Int64 GetMemberID()
{
String SqlSelect = "SELECT * FROM MemberMaster WHERE MemberUserName= '" +
Session["UserName"].ToString() + "'";
SqlDataReader dr1 =
SqlHelper.ExecuteReader(ConfigurationManager.ConnectionStrings["CowcallConnectionString2"].T
oString(), CommandType.Text, SqlSelect);
while (dr1.Read())
{
CurrentMemberID = Int64.Parse(dr1["MemberID"].ToString().Trim());
}
return CurrentMemberID;
}
well i doubt i am opening connection and do not closing it. moreover i do not know how to close connection in above code when i am using sql helper class.
please give me yours suggestion regarding my code even if it is not solving above two problems.
Hi munishbhatia,
use theCommandBehavior to close the connection after use like: SqlHelper.ExecuteReader(CommandBehavior.CloseConnection);
closing pipelinebuffer
Is there a way to signal to the PipelineBuffer during ProcessInput that no more rows are necessary?
Say I have a component's output connected to a custom destination component... in the destination component's properties, I can set the number of rows I want to consume. (The first 50 rows for example.) If I do it this way, the source component continues delivering rows - I can set my destination to ignore all rows after 50, but would like a way to tell the component above it "no more!"
Is this possible?
Steve
I am not sure what effect it would have on the upstream component, probably none, but to signal you are adding now more rows in ProcessInput you would normally use buffer.SetEndOfRowSet()|||Tried this... Can't use buffer.SetEndOfRowSet() on an input buffer, it throws an exception.
Also tried using buffer.Discard() but it seems to not have any effect on stopping the rows from continuing into the component.
Steve|||Ahh.. I was looking at an asynch component, and have the output buffers being used in ProcessInput!
Say I have a component's output connected to a custom destination component... in the destination component's properties, I can set the number of rows I want to consume. (The first 50 rows for example.) If I do it this way, the source component continues delivering rows - I can set my destination to ignore all rows after 50, but would like a way to tell the component above it "no more!"
Is this possible?
Steve
I am not sure what effect it would have on the upstream component, probably none, but to signal you are adding now more rows in ProcessInput you would normally use buffer.SetEndOfRowSet()|||Tried this... Can't use buffer.SetEndOfRowSet() on an input buffer, it throws an exception.
Also tried using buffer.Discard() but it seems to not have any effect on stopping the rows from continuing into the component.
Steve|||Ahh.. I was looking at an asynch component, and have the output buffers being used in ProcessInput!
I don't think this can be done, and not sure that it is a good idea even if you could. It would be like cancelling the package, mid-flow.|||No, you can not signal upstream components that you are done processing. You would have to do this at the source.
Thanks,
Matt|||Why not put in an extra transform component that only provides a certain number of rows. Like this: http://blogs.conchango.com/jamiethomson/archive/2005/07/27/1877.aspx
-Jamie|||Actually, that's exactly what I'm doing. But what happens is that say I just want 10 rows, and the input has 1,000,000 rows... the input will continue sending all 1,000,000 rows, even though they're being ignored. It would be nice to have something that tells the upstream component, "Hey, I don't need you anymore, don't send anything else, report success!"|||
Steven Pasetti wrote:
Actually, that's exactly what I'm doing. But what happens is that say I just want 10 rows, and the input has 1,000,000 rows... the input will continue sending all 1,000,000 rows, even though they're being ignored. It would be nice to have something that tells the upstream component, "Hey, I don't need you anymore, don't send anything else, report success!"
This will be helpful for debugging too... I can debug with only 10 rows and if everything is fine, I can run the 1,000,000 rows later.
Closing open sessions
Hi All
Is there a way/sript that i can schedule to close open sessions at a
certain time. I do have some sessions that are left open and doing nothing o
n
my db. I would like to check for the open sessions that are not doing
anything and also to close all sessions at 3am M-F. Thank you in advance.The sp_who procedure will return all open connections and their current
status. The KILL command can be used to terminate a process. However, just
because a process is idle at the moment, does not mean that it is not in use
by an application.
There is a statement to set the database to single user mode and first close
all processes while rolling back open transactions:
alter database <dbname> set single_user with rollback immediate
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:486EA53E-8CD9-498E-8E50-34FCD18C95B5@.microsoft.com...
> Hi All
> Is there a way/sript that i can schedule to close open sessions at a
> certain time. I do have some sessions that are left open and doing nothing
> on
> my db. I would like to check for the open sessions that are not doing
> anything and also to close all sessions at 3am M-F. Thank you in advance.
>
Is there a way/sript that i can schedule to close open sessions at a
certain time. I do have some sessions that are left open and doing nothing o
n
my db. I would like to check for the open sessions that are not doing
anything and also to close all sessions at 3am M-F. Thank you in advance.The sp_who procedure will return all open connections and their current
status. The KILL command can be used to terminate a process. However, just
because a process is idle at the moment, does not mean that it is not in use
by an application.
There is a statement to set the database to single user mode and first close
all processes while rolling back open transactions:
alter database <dbname> set single_user with rollback immediate
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:486EA53E-8CD9-498E-8E50-34FCD18C95B5@.microsoft.com...
> Hi All
> Is there a way/sript that i can schedule to close open sessions at a
> certain time. I do have some sessions that are left open and doing nothing
> on
> my db. I would like to check for the open sessions that are not doing
> anything and also to close all sessions at 3am M-F. Thank you in advance.
>
Closing open sessions
Hi All
Is there a way/sript that i can schedule to close open sessions at a
certain time. I do have some sessions that are left open and doing nothing o
n
my db. I would like to check for the open sessions that are not doing
anything and also to close all sessions at 3am M-F. Thank you in advance.You can use something like this. Just schedule it in Job Scheduler to run at
3am, and change the > 10 to be the number of minutes that you want to
consider a connection to be idle.
-- Kill all spids that havent had a batch for X number of minutes
--
DECLARE cur_KILLSPID CURSOR
READ_ONLY
FOR select spid from master..sysprocesses
where cmd = 'AWAITING COMMAND' and datediff(mi,last_batch, getdate()) > 10
DECLARE @.spid smallint, @.sql varchar(100)
OPEN cur_KILLSPID
FETCH NEXT FROM cur_KILLSPID INTO @.spid
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
select @.sql = 'KILL ' + cast(@.spid as varchar(10))
exec (@.sql)
DECLARE @.message varchar(100)
SELECT @.message = 'Killing SPID: ' + cast(@.spid as varchar(10))
PRINT @.message
END
FETCH NEXT FROM cur_KILLSPID INTO @.spid
END
CLOSE cur_KILLSPID
DEALLOCATE cur_KILLSPID
GO
AndyP,
Sr. Database Administrator,
MCDBA 2003,
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"MittyKom" wrote:
> Hi All
> Is there a way/sript that i can schedule to close open sessions at a
> certain time. I do have some sessions that are left open and doing nothing
on
> my db. I would like to check for the open sessions that are not doing
> anything and also to close all sessions at 3am M-F. Thank you in advance.
Is there a way/sript that i can schedule to close open sessions at a
certain time. I do have some sessions that are left open and doing nothing o
n
my db. I would like to check for the open sessions that are not doing
anything and also to close all sessions at 3am M-F. Thank you in advance.You can use something like this. Just schedule it in Job Scheduler to run at
3am, and change the > 10 to be the number of minutes that you want to
consider a connection to be idle.
-- Kill all spids that havent had a batch for X number of minutes
--
DECLARE cur_KILLSPID CURSOR
READ_ONLY
FOR select spid from master..sysprocesses
where cmd = 'AWAITING COMMAND' and datediff(mi,last_batch, getdate()) > 10
DECLARE @.spid smallint, @.sql varchar(100)
OPEN cur_KILLSPID
FETCH NEXT FROM cur_KILLSPID INTO @.spid
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
select @.sql = 'KILL ' + cast(@.spid as varchar(10))
exec (@.sql)
DECLARE @.message varchar(100)
SELECT @.message = 'Killing SPID: ' + cast(@.spid as varchar(10))
PRINT @.message
END
FETCH NEXT FROM cur_KILLSPID INTO @.spid
END
CLOSE cur_KILLSPID
DEALLOCATE cur_KILLSPID
GO
AndyP,
Sr. Database Administrator,
MCDBA 2003,
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"MittyKom" wrote:
> Hi All
> Is there a way/sript that i can schedule to close open sessions at a
> certain time. I do have some sessions that are left open and doing nothing
on
> my db. I would like to check for the open sessions that are not doing
> anything and also to close all sessions at 3am M-F. Thank you in advance.
Closing open sessions
Hi All
Is there a way/sript that i can schedule to close open sessions at a
certain time. I do have some sessions that are left open and doing nothing on
my db. I would like to check for the open sessions that are not doing
anything and also to close all sessions at 3am M-F. Thank you in advance.
You can use something like this. Just schedule it in Job Scheduler to run at
3am, and change the > 10 to be the number of minutes that you want to
consider a connection to be idle.
-- Kill all spids that havent had a batch for X number of minutes
DECLARE cur_KILLSPID CURSOR
READ_ONLY
FOR select spid from master..sysprocesses
where cmd = 'AWAITING COMMAND' and datediff(mi,last_batch, getdate()) > 10
DECLARE @.spid smallint, @.sql varchar(100)
OPEN cur_KILLSPID
FETCH NEXT FROM cur_KILLSPID INTO @.spid
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
select @.sql = 'KILL ' + cast(@.spid as varchar(10))
exec (@.sql)
DECLARE @.message varchar(100)
SELECT @.message = 'Killing SPID: ' + cast(@.spid as varchar(10))
PRINT @.message
END
FETCH NEXT FROM cur_KILLSPID INTO @.spid
END
CLOSE cur_KILLSPID
DEALLOCATE cur_KILLSPID
GO
AndyP,
Sr. Database Administrator,
MCDBA 2003,
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"MittyKom" wrote:
> Hi All
> Is there a way/sript that i can schedule to close open sessions at a
> certain time. I do have some sessions that are left open and doing nothing on
> my db. I would like to check for the open sessions that are not doing
> anything and also to close all sessions at 3am M-F. Thank you in advance.
Is there a way/sript that i can schedule to close open sessions at a
certain time. I do have some sessions that are left open and doing nothing on
my db. I would like to check for the open sessions that are not doing
anything and also to close all sessions at 3am M-F. Thank you in advance.
You can use something like this. Just schedule it in Job Scheduler to run at
3am, and change the > 10 to be the number of minutes that you want to
consider a connection to be idle.
-- Kill all spids that havent had a batch for X number of minutes
DECLARE cur_KILLSPID CURSOR
READ_ONLY
FOR select spid from master..sysprocesses
where cmd = 'AWAITING COMMAND' and datediff(mi,last_batch, getdate()) > 10
DECLARE @.spid smallint, @.sql varchar(100)
OPEN cur_KILLSPID
FETCH NEXT FROM cur_KILLSPID INTO @.spid
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
select @.sql = 'KILL ' + cast(@.spid as varchar(10))
exec (@.sql)
DECLARE @.message varchar(100)
SELECT @.message = 'Killing SPID: ' + cast(@.spid as varchar(10))
PRINT @.message
END
FETCH NEXT FROM cur_KILLSPID INTO @.spid
END
CLOSE cur_KILLSPID
DEALLOCATE cur_KILLSPID
GO
AndyP,
Sr. Database Administrator,
MCDBA 2003,
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"MittyKom" wrote:
> Hi All
> Is there a way/sript that i can schedule to close open sessions at a
> certain time. I do have some sessions that are left open and doing nothing on
> my db. I would like to check for the open sessions that are not doing
> anything and also to close all sessions at 3am M-F. Thank you in advance.
Closing open sessions
Hi All
Is there a way/sript that i can schedule to close open sessions at a
certain time. I do have some sessions that are left open and doing nothing on
my db. I would like to check for the open sessions that are not doing
anything and also to close all sessions at 3am M-F. Thank you in advance.You can use something like this. Just schedule it in Job Scheduler to run at
3am, and change the > 10 to be the number of minutes that you want to
consider a connection to be idle.
-- Kill all spids that havent had a batch for X number of minutes
--
DECLARE cur_KILLSPID CURSOR
READ_ONLY
FOR select spid from master..sysprocesses
where cmd = 'AWAITING COMMAND' and datediff(mi,last_batch, getdate()) > 10
DECLARE @.spid smallint, @.sql varchar(100)
OPEN cur_KILLSPID
FETCH NEXT FROM cur_KILLSPID INTO @.spid
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
select @.sql = 'KILL ' + cast(@.spid as varchar(10))
exec (@.sql)
DECLARE @.message varchar(100)
SELECT @.message = 'Killing SPID: ' + cast(@.spid as varchar(10))
PRINT @.message
END
FETCH NEXT FROM cur_KILLSPID INTO @.spid
END
CLOSE cur_KILLSPID
DEALLOCATE cur_KILLSPID
GO
AndyP,
Sr. Database Administrator,
MCDBA 2003,
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"MittyKom" wrote:
> Hi All
> Is there a way/sript that i can schedule to close open sessions at a
> certain time. I do have some sessions that are left open and doing nothing on
> my db. I would like to check for the open sessions that are not doing
> anything and also to close all sessions at 3am M-F. Thank you in advance.
Is there a way/sript that i can schedule to close open sessions at a
certain time. I do have some sessions that are left open and doing nothing on
my db. I would like to check for the open sessions that are not doing
anything and also to close all sessions at 3am M-F. Thank you in advance.You can use something like this. Just schedule it in Job Scheduler to run at
3am, and change the > 10 to be the number of minutes that you want to
consider a connection to be idle.
-- Kill all spids that havent had a batch for X number of minutes
--
DECLARE cur_KILLSPID CURSOR
READ_ONLY
FOR select spid from master..sysprocesses
where cmd = 'AWAITING COMMAND' and datediff(mi,last_batch, getdate()) > 10
DECLARE @.spid smallint, @.sql varchar(100)
OPEN cur_KILLSPID
FETCH NEXT FROM cur_KILLSPID INTO @.spid
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
select @.sql = 'KILL ' + cast(@.spid as varchar(10))
exec (@.sql)
DECLARE @.message varchar(100)
SELECT @.message = 'Killing SPID: ' + cast(@.spid as varchar(10))
PRINT @.message
END
FETCH NEXT FROM cur_KILLSPID INTO @.spid
END
CLOSE cur_KILLSPID
DEALLOCATE cur_KILLSPID
GO
AndyP,
Sr. Database Administrator,
MCDBA 2003,
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"MittyKom" wrote:
> Hi All
> Is there a way/sript that i can schedule to close open sessions at a
> certain time. I do have some sessions that are left open and doing nothing on
> my db. I would like to check for the open sessions that are not doing
> anything and also to close all sessions at 3am M-F. Thank you in advance.
Closing IE Window used for exporting...
Taken from Reporting Services 2000 Help - 'When you click Export, the report
opens in a new browser window, in a viewer that is associated with the export
format. If in Step 2 you clicked TIFF, the report is displayed in a viewer
that supports TIFF file types.'
Is there any way to close this new browser window so it does not stay behind
and make the user close it?
Thanks for any input!Hi rheath,
Welcome to use MSDN Managed Newsgroup Support.
It is a by design issue in Reporting Service 2000 and was resolved in
Reporting Service 2005. If you try to export a report in Reporting Services
2005, the browser window will close and do not need to manually close it.
Thanks for your understanding.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
Do the reports have to be designed using Visual Studio 2005. I ask because
we are developing the reports using Visual Studio 2003 and depolying to a
Reporting Services 2005 server and I still get the browser window that does
not close when exporting.
Thanks,
Russ
"Wei Lu" wrote:
> Hi rheath,
> Welcome to use MSDN Managed Newsgroup Support.
> It is a by design issue in Reporting Service 2000 and was resolved in
> Reporting Service 2005. If you try to export a report in Reporting Services
> 2005, the browser window will close and do not need to manually close it.
> Thanks for your understanding.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Russ,
Thanks for the update.
I have tested on my side. I deploy a report to Report Services 2005 using
VS 2003. I get the browser closed when exporting.
Would you please test on another machine to access the report server and
try to export the report?
Does this issue occur?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
opens in a new browser window, in a viewer that is associated with the export
format. If in Step 2 you clicked TIFF, the report is displayed in a viewer
that supports TIFF file types.'
Is there any way to close this new browser window so it does not stay behind
and make the user close it?
Thanks for any input!Hi rheath,
Welcome to use MSDN Managed Newsgroup Support.
It is a by design issue in Reporting Service 2000 and was resolved in
Reporting Service 2005. If you try to export a report in Reporting Services
2005, the browser window will close and do not need to manually close it.
Thanks for your understanding.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
Do the reports have to be designed using Visual Studio 2005. I ask because
we are developing the reports using Visual Studio 2003 and depolying to a
Reporting Services 2005 server and I still get the browser window that does
not close when exporting.
Thanks,
Russ
"Wei Lu" wrote:
> Hi rheath,
> Welcome to use MSDN Managed Newsgroup Support.
> It is a by design issue in Reporting Service 2000 and was resolved in
> Reporting Service 2005. If you try to export a report in Reporting Services
> 2005, the browser window will close and do not need to manually close it.
> Thanks for your understanding.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Russ,
Thanks for the update.
I have tested on my side. I deploy a report to Report Services 2005 using
VS 2003. I get the browser closed when exporting.
Would you please test on another machine to access the report server and
try to export the report?
Does this issue occur?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Closing gaps in Celko-tree
Hello
I've been using that nested modeltree that Celko uses, and it's superb, but
I've encountered a problem with a table (some unforseen things happened when
i tried to move subtrees from A to B), and a really huge tree ended up with
gaps, structure of tree isnt messed up or anything, just has gaps here and
there.
I got SQL For Smarties (which is a great book btw..) but I cant get that
code to work no matter how I try in sql 2000.
Any of you guys got some neat update or something?
/LasseWhy does it matter if there are gaps?
In the one project I've used that technique on, we actually implemented gaps
on purpose in order to reduce the number of updates that would have to take
place any time a new row was inserted... Gaps do not change the behavior of
the mathematics.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23PZl6YfFGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Hello
> I've been using that nested modeltree that Celko uses, and it's superb,
> but
> I've encountered a problem with a table (some unforseen things happened
> when
> i tried to move subtrees from A to B), and a really huge tree ended up
> with
> gaps, structure of tree isnt messed up or anything, just has gaps here and
> there.
> I got SQL For Smarties (which is a great book btw..) but I cant get that
> code to work no matter how I try in sql 2000.
> Any of you guys got some neat update or something?
> /Lasse
>|||Adam,
i dont quite agree, first of all, its easier to see if the tree is "valid"
(for debugging), 2nd, you dont have to have some subquery to determine if
its the last node in tree since lft-rgt=1 then (or x0 and x1 as i call them
in my design). And 3rd when you acctually going to use the values to display
a graph, it'd looked messed up.
sure it may work, but i'm allergic to "works but not quite correct", i'm
sure a Dell server with 2 xeon cpu's and 4gb ram can handle a few updates to
fix the gaps.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:u2URrdfFGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Why does it matter if there are gaps?
> In the one project I've used that technique on, we actually implemented
gaps
> on purpose in order to reduce the number of updates that would have to
take
> place any time a new row was inserted... Gaps do not change the behavior
of
> the mathematics.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:%23PZl6YfFGHA.1288@.TK2MSFTNGP09.phx.gbl...
and
>|||Lasse Edsvik wrote:
> Adam,
> i dont quite agree, first of all, its easier to see if the tree is "valid"
> (for debugging), 2nd, you dont have to have some subquery to determine if
> its the last node in tree since lft-rgt=1 then (or x0 and x1 as i call the
m
> in my design). And 3rd when you acctually going to use the values to displ
ay
> a graph, it'd looked messed up.
> sure it may work, but i'm allergic to "works but not quite correct", i'm
> sure a Dell server with 2 xeon cpu's and 4gb ram can handle a few updates
to
> fix the gaps.
>
Hi Lasse,
Just thrown this together, test first:
create table Numbers (
Num int not null
)
go
insert into Numbers (Num)
select a.a * 100 + b.b * 10 + c.c
from
(select 0 as a union select 1 union select 2 union select 3 union
select 4 union select 5 union select 6 union select 7 union select 8
union select 9) a,
(select 0 as b union select 1 union select 2 union select 3 union
select 4 union select 5 union select 6 union select 7 union select 8
union select 9) b,
(select 0 as c union select 1 union select 2 union select 3 union
select 4 union select 5 union select 6 union select 7 union select 8
union select 9) c
go
create table Blah (
ID int not null,
X0 int not null,
X1 int not null,
CONSTRAINT CK_Valid CHECK(0 <= X0 and X0 < X1)
)
go
insert into Blah (ID,X0,X1)
select 1,0,500 union all
select 2,2,250 union all
select 3,251,499 union all
select 4,55,56 union all
select 5,90,94 union all
select 6,300,400
go
update
Blah
set X0 = b.X0 - decr.DecX0,
X1 = b.X1 - decr.DecX1
from
Blah b
inner join
(select
ID,X0,X1,
(select count(*) from Numbers where Num < b.X0 and Num not in (select
X0 from Blah) and Num not in (select X1 from Blah)) as DecX0,
(select count(*) from Numbers where Num < b.X1 and Num not in (select
X1 from Blah) and Num not in (select X0 from Blah)) as DecX1
from
Blah b) decr
on
b.ID = decr.ID
go
select * from Blah
go
drop table Blah
go
drop table Numbers
go
course if you've already got a numbers table, I'd take out the first
and last steps. If you don't, create one (course, you might need a
larger range, depending on your system). If you do have one, I'd
recommend removing some bits from my script (like the drop...)
Damien|||CREATE VIEW LftRgt(seq)
AS SELECT lft FROM Tree
UNION ALL
SELECT rgt FROM Tree;
UPDATE Tree
SET lft = (SELECT COUNT(*)
FROM LftRgt
WHERE seq <= lft),
rgt = (SELECT COUNT(*)
FROM LftRgt
WHERE seq <= rgt);
This will preserve the algebriac property that (rgt-lft +1)/2 = size of
subtree rooted at this node.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||>> Why does it matter if there are gaps? In the one project I've used
that technique on, we actually implemented gaps on purpose in order to
reduce the number of updates that would have to take place any time a
new row was inserted... Gaps do not change the behavior of
the mathematics <<
Not quite. The "between-ness" property is handy for structural queries.
But the "algebraic" property (contigous lft-rgt numbering) provides a
simple validation of the tree and give you the size of the subtree at
that node = (rgt-lft+1)/2.
I have some material on using gaps to speed up updates in TREES &
HIERARCHY IN SQL.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||--CELKO-- wrote:
> the "algebraic" property (contigous lft-rgt numbering) provides a
> simple validation of the tree
Can this constraint be written formally?|||Celko,
well, im not sure if i'm way off, but when I run that update lft and rgt
gets same values...
view looks like this:
seq
1
2
3
9
11
14
22
8
4
10
16
15
Not sure if you or me need a coffee here :) I'll have one extra atleast :)
"--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
news:epclMltFGHA.3100@.tk2msftngp13.phx.gbl...
> CREATE VIEW LftRgt(seq)
> AS SELECT lft FROM Tree
> UNION ALL
> SELECT rgt FROM Tree;
> UPDATE Tree
> SET lft = (SELECT COUNT(*)
> FROM LftRgt
> WHERE seq <= lft),
> rgt = (SELECT COUNT(*)
> FROM LftRgt
> WHERE seq <= rgt);
> This will preserve the algebriac property that (rgt-lft +1)/2 = size of
> subtree rooted at this node.
>
> --CELKO--
> Please post DDL in a human-readable format and not a machine-generated
> one. This way people do not have to guess what the keys, constraints,
> DRI, datatypes, etc. in your schema are. Sample data is also a good
> idea, along with clear specifications.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Did you select from the view with an ORDER BY clause?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23vtw$X1FGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Celko,
> well, im not sure if i'm way off, but when I run that update lft and rgt
> gets same values...
> view looks like this:
> seq
> 1
> 2
> 3
> 9
> 11
> 14
> 22
> 8
> 4
> 10
> 16
> 15
> Not sure if you or me need a coffee here :) I'll have one extra atleast :)
>
> "--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
> news:epclMltFGHA.3100@.tk2msftngp13.phx.gbl...
>|||Adam,
I dont see how:
SELECT COUNT(*)
FROM LftRgt
WHERE seq <= rgt
and...
SELECT COUNT(*)
FROM LftRgt
WHERE seq <= lft
would get different values with an order by clause
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O5A9Hr3FGHA.1760@.TK2MSFTNGP10.phx.gbl...
> Did you select from the view with an ORDER BY clause?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:%23vtw$X1FGHA.2300@.TK2MSFTNGP15.phx.gbl...
:)
>
I've been using that nested modeltree that Celko uses, and it's superb, but
I've encountered a problem with a table (some unforseen things happened when
i tried to move subtrees from A to B), and a really huge tree ended up with
gaps, structure of tree isnt messed up or anything, just has gaps here and
there.
I got SQL For Smarties (which is a great book btw..) but I cant get that
code to work no matter how I try in sql 2000.
Any of you guys got some neat update or something?
/LasseWhy does it matter if there are gaps?
In the one project I've used that technique on, we actually implemented gaps
on purpose in order to reduce the number of updates that would have to take
place any time a new row was inserted... Gaps do not change the behavior of
the mathematics.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23PZl6YfFGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Hello
> I've been using that nested modeltree that Celko uses, and it's superb,
> but
> I've encountered a problem with a table (some unforseen things happened
> when
> i tried to move subtrees from A to B), and a really huge tree ended up
> with
> gaps, structure of tree isnt messed up or anything, just has gaps here and
> there.
> I got SQL For Smarties (which is a great book btw..) but I cant get that
> code to work no matter how I try in sql 2000.
> Any of you guys got some neat update or something?
> /Lasse
>|||Adam,
i dont quite agree, first of all, its easier to see if the tree is "valid"
(for debugging), 2nd, you dont have to have some subquery to determine if
its the last node in tree since lft-rgt=1 then (or x0 and x1 as i call them
in my design). And 3rd when you acctually going to use the values to display
a graph, it'd looked messed up.
sure it may work, but i'm allergic to "works but not quite correct", i'm
sure a Dell server with 2 xeon cpu's and 4gb ram can handle a few updates to
fix the gaps.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:u2URrdfFGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Why does it matter if there are gaps?
> In the one project I've used that technique on, we actually implemented
gaps
> on purpose in order to reduce the number of updates that would have to
take
> place any time a new row was inserted... Gaps do not change the behavior
of
> the mathematics.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:%23PZl6YfFGHA.1288@.TK2MSFTNGP09.phx.gbl...
and
>|||Lasse Edsvik wrote:
> Adam,
> i dont quite agree, first of all, its easier to see if the tree is "valid"
> (for debugging), 2nd, you dont have to have some subquery to determine if
> its the last node in tree since lft-rgt=1 then (or x0 and x1 as i call the
m
> in my design). And 3rd when you acctually going to use the values to displ
ay
> a graph, it'd looked messed up.
> sure it may work, but i'm allergic to "works but not quite correct", i'm
> sure a Dell server with 2 xeon cpu's and 4gb ram can handle a few updates
to
> fix the gaps.
>
Hi Lasse,
Just thrown this together, test first:
create table Numbers (
Num int not null
)
go
insert into Numbers (Num)
select a.a * 100 + b.b * 10 + c.c
from
(select 0 as a union select 1 union select 2 union select 3 union
select 4 union select 5 union select 6 union select 7 union select 8
union select 9) a,
(select 0 as b union select 1 union select 2 union select 3 union
select 4 union select 5 union select 6 union select 7 union select 8
union select 9) b,
(select 0 as c union select 1 union select 2 union select 3 union
select 4 union select 5 union select 6 union select 7 union select 8
union select 9) c
go
create table Blah (
ID int not null,
X0 int not null,
X1 int not null,
CONSTRAINT CK_Valid CHECK(0 <= X0 and X0 < X1)
)
go
insert into Blah (ID,X0,X1)
select 1,0,500 union all
select 2,2,250 union all
select 3,251,499 union all
select 4,55,56 union all
select 5,90,94 union all
select 6,300,400
go
update
Blah
set X0 = b.X0 - decr.DecX0,
X1 = b.X1 - decr.DecX1
from
Blah b
inner join
(select
ID,X0,X1,
(select count(*) from Numbers where Num < b.X0 and Num not in (select
X0 from Blah) and Num not in (select X1 from Blah)) as DecX0,
(select count(*) from Numbers where Num < b.X1 and Num not in (select
X1 from Blah) and Num not in (select X0 from Blah)) as DecX1
from
Blah b) decr
on
b.ID = decr.ID
go
select * from Blah
go
drop table Blah
go
drop table Numbers
go
course if you've already got a numbers table, I'd take out the first
and last steps. If you don't, create one (course, you might need a
larger range, depending on your system). If you do have one, I'd
recommend removing some bits from my script (like the drop...)
Damien|||CREATE VIEW LftRgt(seq)
AS SELECT lft FROM Tree
UNION ALL
SELECT rgt FROM Tree;
UPDATE Tree
SET lft = (SELECT COUNT(*)
FROM LftRgt
WHERE seq <= lft),
rgt = (SELECT COUNT(*)
FROM LftRgt
WHERE seq <= rgt);
This will preserve the algebriac property that (rgt-lft +1)/2 = size of
subtree rooted at this node.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||>> Why does it matter if there are gaps? In the one project I've used
that technique on, we actually implemented gaps on purpose in order to
reduce the number of updates that would have to take place any time a
new row was inserted... Gaps do not change the behavior of
the mathematics <<
Not quite. The "between-ness" property is handy for structural queries.
But the "algebraic" property (contigous lft-rgt numbering) provides a
simple validation of the tree and give you the size of the subtree at
that node = (rgt-lft+1)/2.
I have some material on using gaps to speed up updates in TREES &
HIERARCHY IN SQL.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||--CELKO-- wrote:
> the "algebraic" property (contigous lft-rgt numbering) provides a
> simple validation of the tree
Can this constraint be written formally?|||Celko,
well, im not sure if i'm way off, but when I run that update lft and rgt
gets same values...
view looks like this:
seq
1
2
3
9
11
14
22
8
4
10
16
15
Not sure if you or me need a coffee here :) I'll have one extra atleast :)
"--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
news:epclMltFGHA.3100@.tk2msftngp13.phx.gbl...
> CREATE VIEW LftRgt(seq)
> AS SELECT lft FROM Tree
> UNION ALL
> SELECT rgt FROM Tree;
> UPDATE Tree
> SET lft = (SELECT COUNT(*)
> FROM LftRgt
> WHERE seq <= lft),
> rgt = (SELECT COUNT(*)
> FROM LftRgt
> WHERE seq <= rgt);
> This will preserve the algebriac property that (rgt-lft +1)/2 = size of
> subtree rooted at this node.
>
> --CELKO--
> Please post DDL in a human-readable format and not a machine-generated
> one. This way people do not have to guess what the keys, constraints,
> DRI, datatypes, etc. in your schema are. Sample data is also a good
> idea, along with clear specifications.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Did you select from the view with an ORDER BY clause?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23vtw$X1FGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Celko,
> well, im not sure if i'm way off, but when I run that update lft and rgt
> gets same values...
> view looks like this:
> seq
> 1
> 2
> 3
> 9
> 11
> 14
> 22
> 8
> 4
> 10
> 16
> 15
> Not sure if you or me need a coffee here :) I'll have one extra atleast :)
>
> "--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
> news:epclMltFGHA.3100@.tk2msftngp13.phx.gbl...
>|||Adam,
I dont see how:
SELECT COUNT(*)
FROM LftRgt
WHERE seq <= rgt
and...
SELECT COUNT(*)
FROM LftRgt
WHERE seq <= lft
would get different values with an order by clause
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O5A9Hr3FGHA.1760@.TK2MSFTNGP10.phx.gbl...
> Did you select from the view with an ORDER BY clause?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:%23vtw$X1FGHA.2300@.TK2MSFTNGP15.phx.gbl...
:)
>
closing connection problem
I have setup my loop to loop through textboxes and fill the according textboxes with data it retrives, if seems to work fine, but there is a problem with opening and closing the connection below is my code
int i = 0;for (i = 1; i <= 3; i++){
//This gets the stock ID from the textbox.
string stock_ID = ((TextBox)Panel1.FindControl("txtID" + i.ToString())).Text;//This is the sql statement.
string sql ="SELECT [n_or_sh], [title], [cost_price], [selling_price] FROM tbl_stock WHERE stock_ID = " + stock_ID;
//This creates a sql command which executes the sql statement.
SqlCommand sqlCmd =newSqlCommand(sql, myConn);
myConn.Open();
//This is a reader for the results to go in.
SqlDataReader dr = sqlCmd.ExecuteReader();//This reads the first result from the sqlReader
dr.Read();
//This sets the title label text to the value of the description column.
TextBox currentBox1 = (TextBox)Panel1.FindControl("txtDesc" + i);string strtxtDesc = currentBox1.Text;
strtxtDesc = dr["title"].ToString();
}// end of loop
myConn.Close();
}// end of button click
i have tried putting the myConn.Close() in different places but it dosnt seem to work!
any advice or tips ?
Regards
Jez
1. You should use SqlParameter object to pass your ID. This makes the sql query optimizer to keep your query optimized for future uses, and takes you out of the sql injection attack risk.
2. You should close the SqlDataReader before closing the connection. Try dr.Close() (or better, dr.Dispose() )
3. For best practice, you should also dispose your sql connection. Try myConn.Dispose() (after closing it)
Closing all open connections/sessions
Hi all
Is there a way to close all open connections/sessions to my db at a certain
time? I have a application that uses a web browser to access my database. At
the end of the day i will have loads of open connections/sessions to my db.
My plan was to run a job or script in the eveing to close all
connections/sessions. I have been doing it by going to EM and kill all the
open connections/sessions 1 by 1 but, it is tiring as at times i have 700
open connections/sessions and they keep accumulating everyday. Thank you in
advance.You should address the problem in the application itself and have it close
the unused connections, rather than kill them at the end of the day on the
server.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:DAF29302-3EE5-4E92-AE0F-CA59A1049BF6@.microsoft.com...
> Hi all
> Is there a way to close all open connections/sessions to my db at a
> certain
> time? I have a application that uses a web browser to access my database.
> At
> the end of the day i will have loads of open connections/sessions to my
> db.
> My plan was to run a job or script in the eveing to close all
> connections/sessions. I have been doing it by going to EM and kill all the
> open connections/sessions 1 by 1 but, it is tiring as at times i have 700
> open connections/sessions and they keep accumulating everyday. Thank you
> in
> advance.|||Try set db to single_user, then back to multi_user. Search BOL for details.
James
"MittyKom" wrote:
> Hi all
> Is there a way to close all open connections/sessions to my db at a certai
n
> time? I have a application that uses a web browser to access my database.
At
> the end of the day i will have loads of open connections/sessions to my db
.
> My plan was to run a job or script in the eveing to close all
> connections/sessions. I have been doing it by going to EM and kill all the
> open connections/sessions 1 by 1 but, it is tiring as at times i have 700
> open connections/sessions and they keep accumulating everyday. Thank you i
n
> advance.
Is there a way to close all open connections/sessions to my db at a certain
time? I have a application that uses a web browser to access my database. At
the end of the day i will have loads of open connections/sessions to my db.
My plan was to run a job or script in the eveing to close all
connections/sessions. I have been doing it by going to EM and kill all the
open connections/sessions 1 by 1 but, it is tiring as at times i have 700
open connections/sessions and they keep accumulating everyday. Thank you in
advance.You should address the problem in the application itself and have it close
the unused connections, rather than kill them at the end of the day on the
server.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:DAF29302-3EE5-4E92-AE0F-CA59A1049BF6@.microsoft.com...
> Hi all
> Is there a way to close all open connections/sessions to my db at a
> certain
> time? I have a application that uses a web browser to access my database.
> At
> the end of the day i will have loads of open connections/sessions to my
> db.
> My plan was to run a job or script in the eveing to close all
> connections/sessions. I have been doing it by going to EM and kill all the
> open connections/sessions 1 by 1 but, it is tiring as at times i have 700
> open connections/sessions and they keep accumulating everyday. Thank you
> in
> advance.|||Try set db to single_user, then back to multi_user. Search BOL for details.
James
"MittyKom" wrote:
> Hi all
> Is there a way to close all open connections/sessions to my db at a certai
n
> time? I have a application that uses a web browser to access my database.
At
> the end of the day i will have loads of open connections/sessions to my db
.
> My plan was to run a job or script in the eveing to close all
> connections/sessions. I have been doing it by going to EM and kill all the
> open connections/sessions 1 by 1 but, it is tiring as at times i have 700
> open connections/sessions and they keep accumulating everyday. Thank you i
n
> advance.
Closing all connections
How do I set up a job so that I close all open database connections? I think we have a leak in our code which causes our DB to go down (max connections used) roughly once every month, so we just restart the SQL server. Until we can find the exact problem I'd like to do this.
For simplicities sake let's say my database name is just "test."
Thank you.I've seen 2 ways to do this:
1) an ALTER DATABASE command, setting the database into single user mode and kicking out all connections
ALTER DATABASE Test
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
followed by a
ALTER DATABASE Test
SET MULTI_USER
2) a stored procedure which loops through a cursor of all connections and KILLs them. For example code, look here:Kill all the Active Connections to Your Database.
Terri|||Thank you for your help. Sorry for the immensely delayed response. Things have been far beyond hectic lately.
As far as method 2, do you know how to check how many connections are active? I wanna know if killing it actually DID close the connections.|||I believe you can use sp_who to see this information, and sp_who2 to see more detail.
Terri|||Thank you very much tmorton.
Closing ADO objects
I know it's "best practice" to dispose ado.net objects, but does it make a big difference if just the connection is closed?
In other words, is the code below good enough or should the DataAdapter & Command be explicitly closed?
using (SqlConneciton cn = new SqlConnection(connstr))
{
SqlDataAdapter da = new SqlDataAdapter(sql,cn);
DataSet ds = new DataSet();
da.Fill(ds);
SqlCommand cmd = new SqlCommand(someOtherSql,cn);
cmd.ExecuteNonQuery();
}
Hi John,
Actually, you don't need to call dispose. The Dispose method is used to release unmanaged resources. Since Close has already been called, the connection will be put back to pool automatically.
IMO, just call Close. That's enough. If you're using "using" statement, it's better. It will call dispose automatically.
HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!
Closing a SqlDataReader after calling from a DAL....?
I have a DAL that I'm trying to implement - the issue is that I want to call a reader from the DAL, but I'm not sure how to close it. I got best practices from MSDN (located here:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqldatareaderclasstopic.asp ) regarding the SqlDataReader, but can't seem to figure out how to close when using DAL.
Here is sample code in C#:
NOTE: Everything WORKS just fine, however in the DAL for GetRoutes, I can't seem to figure out how to close the reader (see comments in function):
mydatapage.aspx
.
.
.
//Populating some Drop Down List:
private void ddlRoutes_SelectedIndexChanged(object sender, System.EventArgs e) {
SqlDataReader dr = DAL.GetRoutes(ddlRoutes.SelectedValue.ToString());
while (dr.Read())
{
.
.
.
}
}
DataAccessLayer.cs
.
.
.
public static SqlDataReader GetRoutes(string sIdx){
cnn = new SqlConnection(ConnectionString);
cnn.Open();
SqlCommand cmd = new SqlCommand();
SqlParameter par = new SqlParameter();
cmd.Connection=cnn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_GetRoutes";
return cmd.ExecuteReader();
//Everything works above, however I would think that something
//like this SHOULD work, but doesn't:
//
// SqlDataReader dr;
// dr = cmd.ExecuteReader()
// return dr;
// cnn.close();
}
Right now, you pass the SqlDataReader to the method in your page. If you reverse this--andpass the method to the SqlDataReader--your DAL can maintain control of the reader and the database connection.
To learn how to do this, please read the following article:Using Delegates with Data Readers to Control DAL Responsibility
|||what you have to do is follwing
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
then when you close dr it will close the connection. However passing datareader around is not a good idea. It should stay in your DAL.
Closing a conversation later on and Microsoft Service Broker Interface (sample)
In a messaging application, a client sends a message to the server. The server uses Service Broker Interface, and is CLR based. On the server, the message has a life cycle - going throughout multiple transitions until is final (Pending, In Transit, Delivered, TimedOut, Undeliverable). As the server receives the message, it sends back to the client a reply status message of In Transit. It does so using the same message’s conversation. It also saves – along with the message – its conversation handle. The dialog is not closed. After a while, the server may decide that the message has gotten to a final state (Delivered or TimedOut). I would like at that time to send a reply status message (Final State) back to the client and also close the conversation. The conversation handle has been saved by both, client and server. Having the conversation handle available - how do I get back to the client later on?
Thanks,
I don't understand what you mean by 'get back to the client later on'. Do you want to send a message using the conversation handle which was saved earlier? That's simple, you only have to execute the SEND statement with the saved conversation handle (or call Conversation.Send(...) in the ServiceBrokerInterface sample). Note that you cannot send after the conversation has been closed at your endpoint.|||Yes, indeed, I would like to send a message using the conversation handle thant was saved earlier. This will be done by a different thread. All I have saved is the conversation hanlde and I am thinking to create a Conversation object based on the handle only. Can it be done?
Thanks,
|||I'll have to look it up if the Conversation class has a public constructor. If not, the ServiceBrokerInterface library is free for you to modify :-)|||That's right :). I am thinking about this way: (new Service(server service parameters)).GetConversation (mySavedConversationHandle). Thanks for following up.Closing a connection created from a SQLDataSource
I'm new to ASP.NET and I've searched before posting.
I have a simple form containing a FormView control. The FormView uses a SQLDataSource. This works fine and in 30 seconds I have a working form without writing any code.
A form built this way causes problems with connection pooling because I am not closing the connection. What is the best method for me to remedy this?
Every resource I've found explains that I have to explicitly close the connection. How do I do that in this case? Dispose the SQLDataSource in the FormView DataBound event? Rewrite the form so I can control opening and closing of the connection?
Thanks in advance.How about disconnecting on the page.OnUnload event?
Closing a Child Package After it Runs
Hello,
I have a package which runs several child packages. All works well and everything runs, but when it runs each of the children packages, it opens it, runs it and then it stays open. When the whole thing is done, there are about 25 or so open packages. Should they close after they run? Is there a setting I need to do this?
The point I am in SSIS is that I have gotten a decent feel for creating packages, but everything is still in debug mode. I need to take the next step to learn how to have this stuff run automatically or from a procedure outside the SSIS interface. Does that make any sense? If so, where can I learn about that.
Thanks for the help.
-Gumbatman
When you say the package remains open after execution, you are talking about the package designer window on Visual Studio (BIDS)? If so then don't worry it us not an issue.
When you execute a package in the designer it actually has an entirely different instance loaded for the execution. The debugger uses a special application, dtsdebughost.exe, to host the running package. This then hooks into the designer to give you progress information, colours and other messages.
The real execution package has closed you just see the static designer artefact, the open package window. Compare this with execution without debugging, (try Ctrl+F5 is it inside VS) which uses DTExec, a regular execution host.
|||DarrenSQLIS,
Thanks so much for the information. I didn't know about (or understand) that when running it in the BIDS.
-Gumbatman
Subscribe to:
Posts (Atom)