Reusing An SQLconnection
Nov 12, 2003
Hi all,
I am accessing one database a bunch of different times all throughout my code...in various functions and different web pages. Is there a a way to create an sqlconnection that I can access all the time, instead of constanting hardcoding which database to go to? I've tried putting the info in another file and just including it where I want the database to open, but I can't use <!-- #INCLUDE --> inside of the server scripts.
Can anyone help
View 1 Replies
ADVERTISEMENT
Jan 2, 2006
Every time my asp.net app needs to open a connection, it tries to establish a new connection with the mssql server. I´ve already set the max pool size property in the connection string. After that, my app raises an "time out"error saying it couldn´t obtain a connection from the pool. The problem is that I have a lot of iddle connections. With the Enterprise Manager I can see the status of the connections. They´re all the same "awaiting command". How can I reuse this connections? I know that the connection string must be the same for all connections and it is. I´ve set it in the web.config file. If I remove the max pool size property from the connection string I get a lot, I mean A LOT of connections with the sql server. Any ideas?
View 1 Replies
View Related
Apr 19, 2008
Hi ,
I want to open and close sql connection only once and want to use in every function without open or close this connection in class file in asp.net 2003 .
how can it possible .
View 1 Replies
View Related
Feb 20, 2008
Okay, so yes, I am new to SQL server...
I have this SP below, and I am trying to reuse the value returned by the Dateofplanningdate column so that I don't have to enter the code for each additional column I create. I have tried temp tables and derived tables with no luck.
REATE Proc CreateMasterSchedule
as
Select
dbo.[MOP_Planning Overview].warehouse,
dbo.[MOP_Planning Overview].[Item Number],
dbo.[MOP_Planning Overview].[Planning Date],
CAST (Convert (char(10),[Planning Date], 110)as DateTime)as DateofPlanningDate,
(case when dbo.[MOP_Planning Overview].[Order Category]='101' AND CAST (Convert (char(10),[Planning Date], 110)as DateTime)
<= (CAST (Convert (char(10),(dateadd(day, 8 - DATEPART(dw, dateadd(d,@@DATEFIRST-8,getdate())) ,getdate())-7),110) as DateTime)-1)then dbo.[MOP_Planning Overview].[Transaction Quantity - Basic U/M] else 0 end)as PriorInProc,
If I try to use DateofPlanningDate in the above case statement, I get the invalid column name error.
Basically, I just need a way to reuse the value returned by this column.
Can anyone help?
View 6 Replies
View Related
Oct 23, 2006
I was looking at a means of reusing dialogs.
The attempt I tried was looking up an existing dialog in the conversation_endpoints.
However on doing a scale test I would that the non blocking I was hoping wasn't happening. Even through I was giving each spid a new dialog by using a conversation_group_id related to the spid. I found that the following SQL was blocked by a transaction that contains a begin dialog. This suggests the locking on conversation_endpoints is too excessive.
select top 1 conversation_handle
from sys.conversation_endpoints ce
join sys.services s on s.service_id = ce.service_id
join sys.service_contracts c on c.service_contract_id = ce.service_contract_id
where s.name = 'jobStats'
and ce.far_service = 'jobStats'
and (ce.far_broker_instance = @targetBroker OR @targetBroker = 'CURRENT DATABASE')
and ce.state IN ('SO','CO')
and ce.is_initiator = 1
and (ce.conversation_group_id = @conversation_group_id )--or @conversation_group_id is null)
and c.name = @contractName
View 2 Replies
View Related
Apr 30, 2008
In the Package configurations wizard, I am trying to edit an existing configuration using the edit button. In the Configuration Filter, I get the list of several filters (the filters which were used for other packages). Whe I try to reuse an used filter, it is forcing me to set a new value and when I go back to SQL Server tables , I see the old value has got erased.
Can I not use an existing filter?. Do I need to use new filters for every new package?.
Thanks.
View 1 Replies
View Related
Mar 23, 2004
Hi,
I'm trying to test some queries in SQL analyser without reusing the query plan (already cached). I know that there is a way to avoid that but I don't remember right now. Another option would be to restart MS SQL service but I don't want to do that.
Any thoughts...?
Thanks,
S.
View 7 Replies
View Related
Aug 30, 2007
Hi
I have a replicated table that has a trigger attached to the it. The trigger fires off a service broker message for inserts. Originally for every insert, I would begin a conversation, send, and end the conversation when target send an end conversation. Since replication process is only using a single spid, I would like to reuse 1 conversation. the following is what I have for the send procedure in the initiator. I check the conversation_endpoints for any open conversation, if it's null, I start a new conversation and send else just send with the existing conversation. Is there anything wrong with this code? What could cause the conversation on the initiator to be null if I never end the conversation on the initiator side? thanks
DECLARE @dialog_handle uniqueidentifier
select @dialog_handle = conversation_handle from sys.conversation_endpoints where state = 'CO'
IF @dialog_handle is NULL
BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [initiator]
TO SERVICE 'target'
ON CONTRACT [portcontract];
SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE [Port] (@msg)
View 1 Replies
View Related
May 10, 2006
Is it possible to reuse a Lookup component which is configured with Full chaching?
My requirement is as follows....
A input file have 2 columns called CurrentLocation and PreviousLocation. In the dataflow, values of these two columns needs to be replaced with values from a look up table called "Location".
In my package i have added two LookUp components which replaces values of CurrentLocation and PreviousLocation with the values available in the table "Location". Is there any way to reuse the cache of first lookup component for second column also?
View 9 Replies
View Related
Oct 22, 2007
Hi,I'm constructing a query that will performs a lot o datetimecalculumns to generate columns.All that operations are dependent of a base calculum that is performedon the query and its result is stored in a columna returned.I wanna find a way of reusing this generated column, to avoidreprocessing that calculumn to perform the other operations, causethat query will be used in a critical application, and all saving isfew.Thanks a lot.
View 2 Replies
View Related
Aug 5, 2007
Hi! I'm wondering why is my sys.conversation_endpoints table inserting a new row for each message i send even when i reuse conversations?
when i send the first message i get the first row in the sys.conversation_endpoints with a uniqueidentifier for the conversation_handle. this uniqueidentifier is then saved in the table which i query the next time i send a message to reuse the dialog conversation.
But even though it looks like the uniqueidentifier is reused i still get a new row for every message i send with a different conversation_handle?
this happens in both target and initator db.
I've tried to understand this by i don't.
Also for the moment i don't end conversations. But as i understand it this shouldn't matter.
Also the message successfully arives to the target and sys.transmission_queue is empty in both databases.
Neither queues have any error messages in them.
Thanx
View 1 Replies
View Related
Feb 1, 2007
I currently have multiple (parent and child) packages using the same config file. The config file has entries for connections to a number of systems. All of them are not used from the child packages. Hence, my child package throws an error when it tries to configure using the same config file because it can't find the extra connections in my connection collection.
Does anyone have any ideas on the best way to go about resolving this? Is multiple config files (one for each connection) the only way?
Sachin
View 4 Replies
View Related
Mar 3, 2004
Hello,
I have a real heartache with runtime parameter interogation on my DB.
Sure I get the latest and greatest and sure I don't have to type in all those lovely parameter types..but...the hit I take on performance for making no less then 3 DB hits for each SqlAdapter is unreasonable!
So ...I like the idea of maybe calling it once for all my stored procs on application startup...and then maybe saving this in CacheObject.
My problem is that I can't see where you can even serialize a SqlParametersCollection or even for that matter assign it to a Command object. Can you cache a command object ?
LOL
I think I may just have to write some generic routine for creating and populating my command objects based on a key (type) and then use that to fetch my command.Update,
command.Insert and command.
I would like to use the new AsynchBlock to do the fetching of the stored proc parameters and then just pull them from the Cache object....put a file watch so that if the DB's change my params it re-pulls them again.
*nice*.....
Then I get the best of both worlds...caching...and no parameter writing...
Eric
View 4 Replies
View Related
Oct 26, 2005
I have 5 packages in a solution.
View 19 Replies
View Related
Jul 27, 2006
Public Conn As New SqlConnection("Data Source=localhost;Initial Catalog=tblUsers;UID=XXXX;pwd=XXXX")Public Conn As New SqlConnection("Data Source=XX.XXX.XX.XXX;Initial Catalog=tblUsers;UID=XXXX;pwd=XXXX")
This two commands are in my application. I switch between the two so I can test my application.
When I put my application on the server, I use the localhost SQLCONNECTION. The other is commented out. However, the application continues to connect to the server with the address that is commented out.
THIS IS VERY FRUSTRATING... HELP!!!!
View 1 Replies
View Related
Oct 2, 2006
Hi, Someone can explain to me what happend in this situation...i have this and want to call a procedure with connection parameters protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection edo = new SqlConnection();
edo.ConnectionString = "server=myserver; uid=admin; pwd=; " +
"database=name1";
SqlCommand com = edo.CreateCommand();
com.CommandType = CommandType.Text;
My_proc(edo, com);
}
protected void My_proc(SqlConnection edo , SqlCommand com)
{
try
{edo.Open();
}
catch{}
finally{ edo.close();}
}
Well what happend with the SqlCommand and SqlConnection when return to Button_Click() Are they alive? Can be used in Buton_Click like parameters for another procedure? or i have to create it again?Thx in advance..
View 1 Replies
View Related
Oct 16, 2006
please help me !!!?I have a problem with sqlconnection definition when I add the sqlconnection from toolbox :like this: Imports System.Data.SqlClientPartial Class _Default Inherits System.Web.UI.Page Private Sub InitializeComponent() Me.sqlConnection1 = New System.Data.SqlClient.SqlConnection Me.sqlConnection1.ConnectionString = "Data Source=server;Initial Catalog=masterstd;User ID=sa" Me.sqlConnection1.FireInfoMessageEventOnUserErrors = False End Sub Private WithEvents sqlConnection1 As System.Data.SqlClient.SqlConnection Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not IsPostBack Then Dim cmduniversity As SqlCommand Dim dtruniversity As SqlDataReader cmduniversity = New SqlCommand("select * from university", sqlConnection1) sqlConnection1.Open() dtruniversity = cmduniversity.ExecuteReader dtruniversity.Close() sqlConnection1.Close() End If End SubEnd Classin the sqlconnection.open the nullrefrence exception occures butwhen i define myself this exception dosn't occure.like this: Imports System.Data.SqlClientPartial Class _Default Inherits System.Web.UI.Page Private Sub InitializeComponent() End Sub Private WithEvents sqlConnection1 As System.Data.SqlClient.SqlConnection Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not IsPostBack Then sqlConnection1 = New SqlConnection sqlConnection1.ConnectionString = "Data Source=server;Initial Catalog=masterstd;User ID=sa" sqlConnection1.FireInfoMessageEventOnUserErrors = False Dim cmduniversity As SqlCommand Dim dtruniversity As SqlDataReader cmduniversity = New SqlCommand("select * from university", sqlConnection1) sqlConnection1.Open() dtruniversity = cmduniversity.ExecuteReader dtruniversity.Close() sqlConnection1.Close() End If End Subwhere is the problem?
View 2 Replies
View Related
Apr 28, 2007
Can someone help me out? I am trying to establish a SQL server connection in my C# code. My C# code crasheds though. Can someone tell me what Iam doing wrong? Here is the C# code:
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["TheDividerConnectionString"]);
Here is the connection string as defined in the web.config file:
<connectionStrings> <add name="TheDividerConnectionString" connectionString="Data Source=BVCOMPUTERSQLEXPRESS;Initial Catalog=TheDivider;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings>
View 21 Replies
View Related
Jun 11, 2007
is their any code which can check my project for any connections which are open and never closed.
frm 1.1,sql server 2000
View 1 Replies
View Related
Jan 24, 2008
Would anyone ahve any ideas why when i debug this code, it stops and freezes at cn.open(); A raw sql query against the DB works in this format.
int top=10;SqlConnection cn = new SqlConnection("Data Source=blah;Initial Catalog=blah;Integrated Security=blah");
SqlCommand cmd = new SqlCommand("SELECT DISTINCT TOP (@nrows) [CustNu] FROM [Customer] WHERE [CustNu] like @term", cn);cmd.Parameters.AddWithValue("nrows", top);cmd.Parameters.AddWithValue("term", prefixText + "%");
List <string> suggestions = new List<string>();
cn.Open();
View 5 Replies
View Related
Mar 13, 2008
HiI would like to have some help for my problem.I'm trying to retreive the connectionString "MyConnectionString" from app.config using method 1 or 2 to get a connection to my DB (without any success.) --> Partial codepublic SqlConnection GetConnection() { // reference to System.Configuration Library is done 1 SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);--> thrown NullReferenceException on Conn.Open(); 2 SqlConnection Connection = new SqlConnection(ConfigurationSettings.AppSettings["MyConnectionString"]);--> thrown InvalidOperationException ConnectionString not initialised return Connection; } public Contest GetContestFromDB() { SqlConnection Conn = GetConnection(); Conn.Open(); ->> thrown Exception 1 or 2...Here my app.config :<?xml version="1.0" encoding="utf-8"?><configuration> <appSettings> <clear/> <add key="MyConnectionString" value="Server=.SQLExpress;AttachDbFilename=D:Documents and Settings0xMesdocumentsApp_DataShopperDB.MDF;Database=ShopperDB.MDF;Trusted_Connection=Yes;"></add> </appSettings> <connectionStrings> <clear/> --> using <clear/> to avoid to load from machine.config <add name="MyConnectionString" connectionString="Server=.SQLExpress;AttachDbFilename=D:Documents and Settings0xMes documentsApp_DataShopperDB.MDF;Database=ShopperDB.MDF;Trusted_Connection=Yes;"></add> </connectionStrings></configuration> I'm using Visual Studio 2008 with SQLServer 2005 Express Embedded and WindowsXP SP2 .Have you got any idea ?I'm not able to sleep anymore...And I've test this code in ASP.net with the same ConnectionString in the web.config file and it works !!I'm lost.By the way, thx if ou get an answer ww4ss
View 2 Replies
View Related
Aug 24, 2004
Hi
I have the next simple code, what do i have to change to match it to SQL?
The code:
Sub Page_Load (Source As Object, E as EventArgs)
dim strConn as string =("Provider=" & "Microsoft.Jet.OLEDB.4.0;" & _
"Data Source =C:webspacesegolforferaforfera.co.ildbguestbook.mdb")
Dim MySQL as string = "SELECT Name, EMail, URL, Comment FROM Guestbook"
Dim MyConn as New OleDBConnection (strConn)
Dim Cmd as New OleDBCommand (MySQL, MyConn)
MyConn.Open ()
rptGuestbook.DataSource = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
rptGuestbook.DataBind()
End Sub
Thank's and have a good day.
View 3 Replies
View Related
Jul 17, 2006
I have a database for each of my customers and want to connect to a database depending on who is logged in.
all my users have their database name in the user profile.
how can i use the database name in the user profile to change the initial catalog to connect to aaother database?
thanks
View 6 Replies
View Related
Nov 16, 2006
Hi there, I'm not sure if the way I handle SqlConnection in my apps is the most performant one.So my apps use db heavily and there are loads of classes with methods like 1 using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["myConn"].ConnectionString))
2
3 {conn.Open();
4 // more code here
5 }
6
7
As I use using the connection is closed and the object is disposed when leaving the code block.I know that the connection is taken from a pool and when closed it returns to the pool as though I'm not sure if it is better to have one "global" SqlConnection objectand to use that object for all classes or most of them. What about the ConfigurationManager, doesn't it slow down the performance as well when I'm accessing the ConnectionString value thousands of times?I hope someone can point out the issues.Many thanks in advance, Limbic
View 1 Replies
View Related
Jan 3, 2007
I need some information on sqlconnnection object. I see it referenced in some exercises, and also displayed in the toolbox on some articles, but I'm running .net 2.0 and I have no sqlconnection in my toolbox, but more importantly, I can not seem to drop the data connection from my database/server explorer to my form to create a sqlconn object. When I highlight the data connection (in my case it's machinenamesqlexpress.pubs.dbo) and try to drag it - it immediately goes to a NO icon (that is a circle with a slash thru it) and I can not drop it onto my form. I can see the database with no problems in explorer.
I have been able to create a sqlconnection programatically:
Dim sqldatasource1 As SqlConnection = New SqlConnection()
sqldatasource1.ConnectionString = "Data Source=localhostsqlexpress;Initial Catalog=pubs;Integrated Security=True"
but how can I create it on my form?? I would appreciate any help on this matter.
(p.s. I am using VWD 2005 and SQLExpress)
View 5 Replies
View Related
Jul 30, 2007
I have created an aspx page that uses the SqlConnection class to pull data from Microsoft CRM’s database, and we have placed this aspx inside CRM’s directory on the server so that it uses the same integrated security that CRM uses. This works fine on our test environment but on our live environment the Sql connection fails with the exception: "Not associated with a trusted SQL Server connection"
This is probably because our test CRM server has the database on the same machine as the web server, but the live environment has the database and the web server on separate machines.
So my question is how can I create an aspx page that takes the integrated security credentials that the user used to log into the website and uses them to access data from a database on a seperate machine?
View 2 Replies
View Related
Oct 26, 2007
Hi,When using the following controls....System.Data.SqlClient.SqlConnection System.Data.SqlClient.SqlCommandIf I want to change my SQL command and execute the query once again what cleanup do I need to do first?Do I need close and dispose the SqlConnection?Do I need to dispose the SqlCommand?Can I use the SqlConnection for more than one SqlCommand?Thanks,Scott
View 5 Replies
View Related
Dec 18, 2007
I am working on a set of webforms that insert user data into a set of db tables.
I set up a test of an approach using northwind and I'm having trouble getting the insert to work. When I open the form, input the name and phone, and submit there is no error, but no record inserted into the Shippers table.
You can see one of my approaches in the ASPX code. I don't like having to do the select in order to do the insert -- so that's commented off.
I'm stuck. Thoughts about what I'm missing appreciated...
Ray
ASPX code.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default66a.aspx.cs" Inherits="pages_audit_Default66a" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server"><title>Untitled Page</title>
</head><body>
<form id="form1" runat="server">
CompanyName:
<asp:textbox id="txtCompanyName" runat="server" /><br />
Phone:<asp:textbox id="txtPhone" runat="server" /><br />
<br />
<asp:button id="btnSubmit" runat="server" text="Submit" onclick="btnSubmit_Click" />
<br />
<br />
<br />
<br />
<asp:Label ID="awesomelbl" runat="server" Text="Label"></asp:Label><br />
<br />
<!--
<asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString %>"
insertcommand="INSERT INTO Shippers(CompanyName, Phone) VALUES (@CompanyName, @Phone)" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [Shippers]">
<insertparameters>
<asp:controlparameter controlid="txtCompanyName" name="CompanyName" />
<asp:controlparameter controlid="txtPhone" name="Phone" />
</insertparameters>
</asp:sqldatasource>
--></form>
</body>
</html>
c Sharp code
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class pages_audit_Default66a : System.Web.UI.Page
{protected void Page_Load(object sender, EventArgs e)
{
}protected void btnSubmit_Click(object sender, EventArgs e)
{SqlConnection con = new SqlConnection("Data Source=Chilibowl;Trusted_Connection=yes;DataBase=Northwind");
SqlCommand cmd = new SqlCommand("INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@CompanyName, @Phone)");SqlParameter cnameparam = new SqlParameter("@CompanyName", txtCompanyName.Text); SqlParameter phnparam = new SqlParameter("@Phone", txtPhone.Text);
cmd.Parameters.Add(cnameparam);
cmd.Parameters.Add(phnparam);
try
{
con.Open();if (cmd.ExecuteNonQuery() > 0)awesomelbl.Text = "successful insert";
}
catch
{
//handel
}
finally
{
con.Close();
}
}
}
View 2 Replies
View Related
Jan 4, 2008
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
this problem occured when i rum my program which contain database connection
any one there can help me please?
View 6 Replies
View Related
Jan 22, 2004
Hi,
How much of a performance difference is there between connecting to SQL Server 2000 using OleDbConnection or using SqlConnection?
The reason I'm asking is I am taking on the task of updating an older program that uses a Access Database to use SQL Server, but it has a Database Utility class that uses OleDbConnection. I'm just debating whether it would be worthwhile to upgrade the class to use the SQL objects rather than Oledb. Program does a lot of update and insert of invidual records, and a few select statements that usually return from 1 to 2000 records up to a maximum of 50,000 records
Thanks
View 2 Replies
View Related
Mar 2, 2004
I've an web app with impersonate windows authentication. IIS uses Windows authentication for this app & anonymous authentication is turned off.
This app have connectionString in Web.config where Windows autentication (SSPI) to SQL Server 2000 took place.
Now I've a piece of code:
public class SiteParameters
{
public static SqlConnection Connection
{
get
{
SqlConnection conn;
try
{
conn = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
if(conn.State != ConnectionState.Open)
conn.Open();
}
catch(Exception)
{
conn = null;
}
return conn;
}
}
}
No comments is needed, think...
Now, I'm going to use it in following way:
SqlConnection conn = SiteParameters.Connection;
SqlDataAdapter dta = new SqlDataAdapter();
DataSet ds = new DataSet();
SqlCommand sqlSelect = new SqlCommand("spPlanTransportSelect", conn);
sqlSelect.CommandType =CommandType.StoredProcedure;
dta.SelectCommand = sqlSelect;
dta.Fill(ds, "PlanTransport");
if(conn.State != ConnectionState.Closed)
conn.Close();
return ds;
It looks good & works good... on local machine only :( When I run app locally (doesn't matter if I call it by http://localhost/app, http://127.0.0.1/app or http://10.50.2.51/app I take proper data from database.
But when my buddy from another comp (e.g. 10.50.2.52) uses URL http://10.50.5.51/app he receives an error in line dta.Fill(ds, "PlanTransport");, but in fact conn variable is null & he receives "yellow" screen where is written that NT ANONYMOUS user is trying to log on to database...
It looks strange for me. Any tips?
View 2 Replies
View Related
Jul 10, 2004
I tried connected my web form with SQL server by drag a table from Server Explore onto the WebForm1.aspx. page. It created a sqlConnection1 and a sqlDataadapter object, but theses two objects seem not work. There is nothing displayed on property window and when I right-click sqlDataAdapter there is no "Preview Data", "Configure Data Adapter" and "Gnerate Dataset" items displayed as well.
I used this SQL server on Windows Form, it worked very well. Could anyone please help me figure it out. Thanks!
View 1 Replies
View Related
Sep 23, 2004
Hello all,
Actually i want to known, is their any method or property in SqlConnection class which will return some value, so that through which we become confirm that connection has established.
Thanks in advance!
View 2 Replies
View Related
