One Application With 2 DataBase

Hai All ,

Iam having the one Application in DotNet2005.But iam using the two database(SqlServer2005).If user's login the application it will check credentials in first database, if he is not having the login credentials it will cheek the second Database. How can I handle in the configuration file?. How can I change the connection string depending upon the Database in my application for database operation (like insert and update).Any one kindly provide the solution.

Thanks and regards,sureshK

you can have 2 separate connection string in web.config file, appsetting section like

<add key="loginConnectionString" value = "" />

<add key="ConnectionString" value="" />

you can check have a Session Variable named as "CurrentLogin" where you can store the ID or Name or anything user related information after successful login.. if user is logged in.. then that Session Variable must having Some Value.. you can check that value and;... set and use required Connection String by you... hope i got your situation...

|||

Hai Thank You. Your answer is exactly Correct. If their is any other way to achieve this?.

With Regards,

SureshK.|||

Hi mskumarmca,

Just askaushalparik27 has suggested above, you can use two different connection string to connect to 2 databases, and in your code behind, first build one connecton to the first database and execute your query. If the specific username/password has been found, it indicates that the current user is registered in the first dabase. And if not, you can build another connection to the second database and execute the same query likewise.

A small tip here: you can provide 2 check boxes in the page UI, giving users a change to select on which database they would like to verify their credential. This should be much helpful to enhance your site performance.

Hope my suggestion helps

|||

Hi thank for your tips.

Yes I get database name and passing the database name as parameter and finished.

But I am trying to keep the Database name in session. But I am not able to get that session value in my BL layer.

For change the connection string for further database operations. Below code was I am trying to get database value in BL Layer class file. But the session value is set as Null. What is the problem?

String DBName=(String)System.Web.HttpContext.Current.Session["DBNAME"];

Kindly give me the idea. Regards,SureshK|||

where and how did you store that DBNAME into your session variables?

|||

Hi thanks for your reply,

When the user log in the application. I will connect through default connection string with DB1.

I have one SP to check login cardinals in DB1 and DB2 and it will return the login cardinals and connection string name.

if user name is DB1 I am going to use ConsStr1 else Constr2. That Connection string value in login page.

Session["DBNAME"] = dsLogin.Tables[0].Rows[0][DBNAME].ToString();

Above session value only i need to access in my business logic layer.

Regards,

SureshK

|||

Hai all I Got the answer.

In BL Class file .

Import the namesapce.

using System.Web.SessionState;

And Following is to access the session Variable.

string SapId = (String)System.Web.HttpContext.Current.Session["session variale name in asp.cs"];

OlE Db Command

Hi,

Iam trying to generate the increment value for a column by taking Maximum value of that field by using OLE DB Command Transformation.

This is the query iam using for that :
select max(ApplicationId)+1 as APPLICATIONID from Source..Applications

Iam finding difficulty in getting the 'APPLICATIONID' as the out put from OLEDB Transformation.OLE DB is not going to work with out any parameters ?can you please throw some light on this .First time iam using this transformation.

Another alternate solution is generating through script task (Thanks Jamie Thomson for his article).But i dont know how i can modify this to get the Max value instead of declaring some constant value for increment

Public Class ScriptMain
Inherits UserComponent
Dim Counter As Int32
Public Sub New()
Counter = 1006
End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
Counter += 1
Row.APPLICATIONTASKID = Counter

End Sub

End Class
Please me help me in finding one solution from the above two

Thanks
Niru
No replies ..

Am I dint explained properly or nobody are aware of this?

Thanks
Niru
|||

If I understand your question correctly, you're asking to generate a surrogate key id (1,2,3,..) which key id is started from last key id + 1 from your source table. If yes, you can try this:

On control flow, create an Execute SQL task with your select statement:

select isnull(max(ApplicationId),1) as APPLICATIONID from Source..Applications

Set Result Set property = Single Row

Create a variable, on the Result Set assign your variable.

On data flow, create a column, ex. ApplicationKeyID set to null. Follow by script component task; in Input Columns, select the ApplicationKeyID and Usage type = ReadWrite. On Script, type in the variable name for ReadOnlyVariables property. And your script will look something like this:

Public Class ScriptMain

Inherits UserComponent

Dim Counter As Integer = 0

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Row.ApplicationKeyID = Variables.VariableName+ Counter

Counter = Counter + 1

End Sub

End Class

Hope this does not cause any more confusion for you...