Link to home
Start Free TrialLog in
Avatar of ybt
ybt

asked on

ASP.NET data base connection

Hello, I'm just switch from C# winform to webforms and do not know how to make DB connection created in login page accessible from other pages, in login I created:
SqlConnection DbConn = new SqlConnection("user id='"+ls_user+"';password='"+ls_pwd+"';server=server_nameL;database=db_name; Trusted_Connection = False;connection timeout=30;");

To get a data from DB in other pages< I need to use DbConn.
What should I do?
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

for C# winform to webforms, they're pretty much the same in terms of using the database objects, just that you may want to switch to other grid control if you meant to display the records, etc.

you can migrate your existing db scripts from your C# winform to webforms and amend accordingly (if necessary)
Avatar of ybt
ybt

ASKER

In C# winforms  connection was setted in MDI Frame, I did not find similar in webforms
In webforms you create in on the page when you need it.

EG:

On a button_click postback you would open the connection, do your work, and then close / dispose the connection.

Also you should store the connection string in the web.config, and read the string from there.  If you're worried about security it can be encrypted, but you only need to worry about that when moving to production.
on every page, you will create db
connect to db
run some commands/queries
use the result
close the connection and all resources

there is no such thing like:
- connect to db and leave it somewhere
- use it on every page
Avatar of ybt

ASKER

I understand that I need open and close connection on each page, but connection as is suppose to be a global, I cannot log in on each page
no such thing...

on every page:

create db
open db
create command
run command
consume data
close db
dispose all
I cannot log in on each page

yes you can login on every page :)
Avatar of ybt

ASKER

well, to log in on each page, how should I pass ID and Password from page to page?
you use web.config for this

you store connection string in web.config and on every page, get it from here and connect to db

<connectionStrings>
  <add name="NorthindConnectionString" 
   connectionString=" Server=MyDataServer;Integrated Security=SSPI;Database=Northwind;"
   providerName="System.Data.SqlClient" />
</connectionStrings>

Open in new window


then use it
// Add a using directive at the top of your code file    
using System.Configuration;

// Within the code body set your variable    
string cs = ConfigurationManager.ConnectionStrings["NorthindConnectionString"].ConnectionString;

Open in new window

You login on one page.  

Add this to your web.config under system.web:
    <authentication mode="Forms">
      <forms loginUrl="~/Login.aspx" timeout="2880" />
    </authentication>

Open in new window


This says if you're not logged in, go to login.aspx in the root directory.


You use the session to store the userID once they successfully login.

So the login form would be  your typical username password.

Upon Success:
 
  int CustID = ValidateUser(sUserName, sPassWord);  //returns the customer ID if a valid username / password found, else 0
   if (custId > 0)
   {
       FormsAuthentication.SetAuthCookie(sUserName, false);  // set the cookie so the website knows not to 
       Session["CustomerID"] = CustID;  // now all pages can access Session["CustomerID"] and know the customer.
   }
  else 
   {
     DisplayError();
   }

Open in new window

Avatar of ybt

ASKER

Our recent security configured that way, that I have to send id and password with connection string, is it a way to resolve this or security suppose to be recreated?
Each user has their own sql login?  

The way our applications are built is that we have one sql account that the application uses, and security is controlled by the app itself.
Avatar of ybt

ASKER

Yes, this way dba did this and with winforms for local network it was working, now I need to switch to a webform and looking a way
ASKER CERTIFIED SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ybt

ASKER

the name "ValidateUser" not exist in the current context

Did I miss something?
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Configuration;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Web.Security;
try use: Membership.ValidateUser, it is in System.Web.Security

Membership.ValidateUser Method (String, String)
https://msdn.microsoft.com/en-us/library/system.web.security.membership.validateuser(v=vs.110).aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-2
Avatar of ybt

ASKER

when I try to use this in other pages:
SqlConnection DbConn = new SqlConnection(String.Format(ConnectionStrings["NorthindConnectionString"].ConnectionString, Session["ls_user"], Session["ls_pwd"]));
I got : The Name "ConnectionStrings"  does not exist in the current context
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ybt

ASKER

Session["ls_user"], Session["ls_pwd"]

gives an error "an object eference is required for the non ctatiic fieild...

what reference did I miss?
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Configuration;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Web.Security;
This builds for me:
SqlConnection DbConn = new SqlConnection(String.Format(ConfigurationManager.ConnectionStrings["NorthindConnectionString"].ConnectionString, Session["ls_user"], Session["ls_pwd"]));

Did you set the ls_user / ls_pwd before referring to it?  When do you get that error?
Avatar of ybt

ASKER

I set it in login page
 ls_user = TextBox1.Text.ToLower();
 ls_pwd = TextBox2.Text.ToLower();
and error comes on redirected page
and then followed your sample code
Session["ls_user"] = TextBox1.Text.ToLower();
Session["ls_pwd"] = TextBox2.Text.ToLower();

Open in new window

Avatar of ybt

ASKER

I did the same just in two steps
 ls_user = TextBox1.Text.ToLower();
 ls_pwd = TextBox2.Text.ToLower();
Session["ls_user"] = ls_user;
 Session["ls_pwd"] = ls_pwd;  
a problem somewhere else
can you post your code as is?
Avatar of ybt

ASKER

web.config:
<connectionStrings>
   <add name="DbConnString" connectionString="server=<SERVER>;persist security info=False;user id={0};password={1};database=<DB>" providerName="System.Data.SqlClient" />
  </connectionStrings>

login:
public partial class Login: System.Web.UI.Page
    {
        string ls_user = "";
        string ls_pwd = "";
        protected void Page_Load(object sender, EventArgs e)
        {
            var connectionFromConfig = WebConfigurationManager.ConnectionStrings["DbConnString"];
            SqlConnection dbConn = new SqlConnection(connectionFromConfig.ConnectionString);
        }
        protected void Button1_Click(object sender, EventArgs e)
        {

           ls_user = TextBox1.Text.ToLower();
            ls_pwd = TextBox2.Text.ToLower();
         
            try
            {
                if (Membership.ValidateUser(ls_user, ls_pwd))  // verify you can connect.
                {
                    Session["ls_user"] = ls_user;  // save user
                    Session["ls_pwd"] = ls_pwd;  // save password
                   
                    FormsAuthentication.SetAuthCookie(ls_user, false); // set cookie so you don't get challenged again
                    Response.Redirect("~/CustReview.aspx", true);  // redirect to default.aspx
DbConn.Open();
                }
          
            }
            catch(Exception ex)
            {
                Response.Write(ex.Message +"AAA");
            }
      }
     }

second page:
protected void Page_Load(object sender, EventArgs e)
        {
       
            SqlConnection DbConnString = new SqlConnection(String.Format(ConfigurationManager.ConnectionStrings["NorthindConnectionString"].ConnectionString, Session["ls_user"], Session["ls_pwd"]));
                SqlCommand com = new SqlCommand();
                string ls_select_group = "*******************";
           
                SqlDataAdapter dataAdapterUser = new SqlDataAdapter(ls_select_group, DbConnString);
            DataTable dt = new DataTable();
         

           
            try
            {
                if (DbConnString.State.Equals(ConnectionState.Closed))
                {
                    DbConnString.Open();
                }
           
                dataAdapterUser.Fill(dt);
                if (DbConnString.State.Equals(ConnectionState.Open))
                {
                    DbConnString.Close();
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
         
        }
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ybt

ASKER

I corrected, but still error:
Error Code         0x80070032
Config Error         The configuration section 'authentication' cannot be read because it is missing a section declaration
That's in your web.config.  Post the full thing?  Also do NOT use Membership.ValidateUser. My ValidateUser was just a function that showed you should attempt a login.  Membership.ValidateUser is using the built in membership for .net.  

For now just try to open a connection to the db, if it succeeds return true, catch any exceptions then return false.
Avatar of ybt

ASKER

Web.config
<configuration>
   <appSettings>
      <add key="ValidationSettings:UnobtrusiveValidationMode" value="None" />
    </appSettings>
    <system.web>
      <compilation debug="true" targetFramework="4.5" />
      <httpRuntime targetFramework="4.5" />
    </system.web>
   <authentication mode="Forms">
      <forms loginUrl="~/Login.aspx" timeout="2880" />
    </authentication>
  <connectionStrings>
    <add name="DbConnString" connectionString="server=SQLSERVER;persist security info=False;user id={0};password={1};database=mydb" providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>
Authentication goes inside System.Web (By under I meant "is a child of").  I get notifications when you respond to the question, please stop messaging me with the same reply.
Avatar of ybt

ASKER

Sorry, I did not know that you get notification from here and I tried to get your attention.
I have System.Web included, what should I do?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ybt

ASKER

At final step I got this error:"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified"

For winforms we used network connection anyway, is it some suppose to be some special settings on server side?
Can you ping the sql server from your webserver?  Ensure you have the name typed correctly?  That's just saying what it is, you can't connect to the sql server for some reason.
Avatar of ybt

ASKER

Thank you very much, you spent a lot of time with me