Advance programmers only

hi ,
I am using ASP.NET with SQL Server. I have a function ABC() which creates,open ,and then dispose sqlserver's connection .
I am using ABC() twice in one .aspx page ,my friend who build this function argued that this function will open only one connection in everypage ,no matter how many times we call function ABC() in a single page ,while i denies .
Please tell me ,because our whole company database acces relies on this single ABC() function.

Thanks in AdvanceYou don't need an advanced programmer for this one. Open() and Close() rely on Connection Pooling, and you'd be surprised how many connections "open" but really share one that's already opened.

If you have a function that's ABC(), you should really have a means of OpenConnection() and CloseConnection() available that calls the Connection.Dispose() method to clear out your memory also.

You should be ok to open/close the connection a few times in an aspx page.|||::while i denies .

Read the documentation :-)

::my friend who build this function argued that this function will open only one connection in
::everypage

Your friend is wrong.

You are wrong.

:-)

ABC opens, uses, then disposes the method.

Now, if you did a decent job with the connection string (a.k.a. as astandard connection string), then basically SQL Connections are pooled. This means when YOU close the SQL Connection, it will NOT be closed, but will go back into the pool, ready for reuse (and being closed a couple of minutes later).

The idea behind this is - rightly - that opening a NEW connection is pretty slow (password authentication, setting up streams etc.). So, when you open / close connections often, keeping it around is much more efficient. Sadly, managing this from an application's point of view is hard (has to be obeyed everywhere etc.), so this went into the system. Connection Pooling was part of the infrastructure for a long time before .NET came around.

So, when this is in place, then basically ABC () may NOT OPEN a connection, but return one already stored. Basically for this the connection string has to be identical :-) Note that this pool is cross page if the connection strings are identical - which is why I sy your friend is wrong. Because the one connection you "opened" further up in the page may be reused right now by another page, resulting in your page openring a second connection and expanding the pool.

NOW - when you have Transactions enabled on the bpage, and the page is thus running under COM# guidance, then things are a little harder. Because COM+ spawns a transaction, a disposed connection can ntot go back into the general pool befor ethe page completes - it thus stays bound to the page (actually to the transaction context the page runs in). So the second call to ABC () would not get A connection back (note the emphasis on "A"), but THE connection - the same connection, as it basically is unused at the moment and still bound to the transactional context.

To read up in the dcumentation:

* For the non COM+ cas: look for "Connection pooling".
* For the COM+ case - well, read the COM+ documentation. Note that ServicedComponent subclasses just implement COM+ for .NET, so you better go back to the original COM+ documentation if you want to get details of inner workings. A third party book is strongly advisable.

::Please tell me ,because our whole company database acces relies on this single ABC()
::function.

Given that the reuse of existing connections is totally transparent and does not change anything on the level of your appplication, could you elaborate how you think this can break your application?

ADO Disconnected Recordset

Hi ...

This is a C++ / ADO / SQL question. Maybe not the right forum but I am guessing there are some programmers out there ...

I am trying to use ADO disconnected recordset to insert data into a sql table. I am using AddNew(vField, vValue) with UpdateBatch(). The code below does not throw any exceptions ... but does not add data to the table.

Any comments are appreciated,
Thanks,
Chris

void CTestApp::TestDatabaseUpdateBatch1a(void)
{
int nDataCount = 0;
long nIndex = 0;
long nIndex2 = 0;

CString csMessage;
CString csErrorMessage;
CString csTemp;
CString csSQL;

BOOL bIsOpen;
BOOL bIsEmpty;

long nCount = 0;
int nTemp = 0;
int nLimit = 0;

int nTempInt = 0;
long nTempLong = 0;
double nTempDouble = 0;

HRESULT hResult;

SYSTEMTIME st;

int i = 0;

string strTemp;

_variant_t sval;

_variant_t vNull;
vNull.vt = VT_ERROR;
vNull.scode = DISP_E_PARAMNOTFOUND;

COleSafeArray colesaFieldList;
COleSafeArray colesaDataList;

vector<COleSafeArray> *pvecDataList;

pvecDataList = new vector<COleSafeArray>;

COleDateTime oledtCurrentDate = COleDateTime::GetCurrentTime();

// Convert the OleDateTime to the varient
// COleVariant vCurrentDateTime(oledtCurrentDate);
COleVariant vCurrentDateTime;

CMxTextParse *pMxTextParse = NULL;

CMainFrame *pMainFrame = (CMainFrame *)AfxGetMainWnd();
CFrameWnd* pChild = pMainFrame->GetActiveFrame();
CTestMeteorlogixView* pView = (CTestMeteorlogixView*)pChild->GetActiveView();

pView->WriteLog("Start TestDatabaseUpdateBatch1a.");
pView->WriteLog("Load table using AddNew() and UpdateBatch().");

// Define ADO connection pointers
_ConnectionPtr pConnection = NULL;
_RecordsetPtr pRecordset = NULL;

try
{
// When we open the application we will open the ADO connection
pConnection.CreateInstance(__uuidof(Connection));

// Replace Data Source value with your server name.
bstr_t bstrConnect("Provider='sqloledb';Data Source='SQLDEV';"
"Initial Catalog='AlphaNumericData';"
"User Id=cmacgowan;Password=cmacgowan");

// Open the ado connection
pConnection->Open(bstrConnect,"","",adConnectUnspecified);

// Create an instance of the database
pRecordset.CreateInstance(__uuidof(Recordset));

// Select the correct sql string. Note that we are creating an
// empty string by doing a select on the primary key. We are only
// doing inserts and we do not want to bring data back from the
// server

csSQL = "SELECT * FROM dbo.AAMacgowanTest WHERE RecordId IS NULL";
// csSQL = "SELECT * FROM dbo.DICastRaw1Hr";

pRecordset->PutRefActiveConnection(pConnection);
pRecordset->CursorLocation = adUseClient;


pRecordset->Open(csSQL.AllocSysString(), vNull, adOpenStatic, adLockOptimistic, -1);

// Test to see if the recordset is connected
if(pRecordset->GetState() != adStateClosed)
{
// The recordset is connected, we will see if we are
// at the end

if((pRecordset->BOF) && (pRecordset->GetadoEOF()))
{
// The recordset is empty
bIsEmpty = false;
}

if(pRecordset->GetadoEOF())
{
bIsOpen = false;
}
else
{
// disconnect the database
pRecordset->PutRefActiveConnection(NULL);
}
}

// disconnect the database
// pRecordset->PutRefActiveConnection(NULL);

// Disassociate the connection from the recordset.
pRecordset->PutRefActiveConnection(NULL);

// Set the count
nCount = 1;

// now we will scroll through the file
while(nCount > 0)
{
nCount--;

nDataCount = 10;

// test that we got some data
if (nDataCount >= 0)
{
// Start the insert process
// m_pRecordset->AddNew();

COleSafeArray warningList;
//int index, listIndex = -1, bitIndex; // indexing variables
// long lowIndex, highIndex, arrayIndex[2];

VARIANT vFieldList[25];
VARIANT vValueList[25];

int nFieldIndex = 0;
int nValueIndex = 0;

// Setup the fields
vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Name");
nFieldIndex++;

vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Section");
nFieldIndex++;

vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Code");
nFieldIndex++;

vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Latitude");
nFieldIndex++;

vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Longitude");
nFieldIndex++;

pView->WriteLog("Set data using AddNew() ...");

// COleDateTime is a wrapper for VARIANT's DATE type. COleVariant is
// a wrapper for VARIANTs themselves. If you need to create a
// variant, you can say:
COleDateTime oledtCurrentDate2 = COleDateTime::GetCurrentTime();

// Convert the OleDateTime to the varient
COleVariant vCurrentDateTime2(oledtCurrentDate2);

//Set the DATE variant data type.
memset(&st, 0, sizeof(SYSTEMTIME));
st.wYear = 2000;
st.wMonth = 1;
st.wDay = 1;
st.wHour = 12;

// vect is a vector of COleSafeArrays containing the records
for(i = 0; i < 10; i++)
{

// Setup the data
nValueIndex = 0;
vValueList[nValueIndex].vt = VT_BSTR;
vValueList[nValueIndex].bstrVal = ::SysAllocString(L"BLUE");
nValueIndex++;

vValueList[nValueIndex].vt = VT_BSTR;
vValueList[nValueIndex].bstrVal = ::SysAllocString(L"KSTP");
nValueIndex++;

vValueList[nValueIndex].vt = VT_I4;
vValueList[nValueIndex].dblVal = 100 + nFieldIndex;
nValueIndex++;

vValueList[nValueIndex].vt = VT_R8;
vValueList[nValueIndex].dblVal = 11.11 + nFieldIndex;
nValueIndex++;

vValueList[nValueIndex].vt = VT_R8;
vValueList[nValueIndex].dblVal = 22.22 + nFieldIndex;
nValueIndex++;

// Add the record to the recordset
pRecordset->AddNew(vFieldList, vValueList);
}

pView->WriteLog("Call UpdateBatch().");

// Re-connect.
pRecordset->PutRefActiveConnection(pConnection);

// Send updates.
pRecordset->UpdateBatch(adAffectAll);

// Close the recordset and the connection
pRecordset->Close();
pConnection->Close();

}
}
}
catch(_com_error *e)
{
CString Error = e->ErrorMessage();
AfxMessageBox(e->ErrorMessage());
pView->WriteLog("Error processing TestDatabase().");
}
catch(...)
{
csMessage = "Undefined exception handled. Error message details \n\n";

hResult = GetAdoErrorMessage(m_pConnection,
&csErrorMessage);

csMessage += csErrorMessage;
csMessage += "\nmethod: CTestMeteorlogixApp::OnTestDatabaseAdoBulkload()";

AfxMessageBox(csMessage);

}

csTemp.Format("Last Row %03d DIcastId = %s ", nIndex, strTemp.c_str());
pView->WriteLog(csTemp);

pView->WriteLog("End TestDatabaseUpdateBatch1.");

}Yup...wrong forum...

Why not use Stored procedures though?|||Have you checked the errors (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcolerrors.asp) collection after calling UpdateBatch (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthupdatebatch.asp)? Check out the VC example (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthupdatebatchxvc.asp) to see how they handle the errors.

-PatP|||Originally posted by Brett Kaiser
Yup...wrong forum...

Why not use Stored procedures though?

Thanks for the response. Currently we are using a BULK INSERT to get about 100,000 rows / 30 columns into a table ... then calling a Stored Procedure to do some derivations on the data and populating another table with the results.

We have lots of 'stuff' working on the SQL server and are looking to move some of the business rules from the server to the middle tier. The entire process described above takes about 10-15 minutes and if we could get that type of performace in ADO on the C++ side then we would be happy.

Any comments / suggestions are appreciated
Thanks,
Chris