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?
ybtAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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)
0
ybtAuthor Commented:
In C# winforms  connection was setted in MDI Frame, I did not find similar in webforms
0
Kyle AbrahamsSenior .Net DeveloperCommented:
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.
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

HainKurtSr. System AnalystCommented:
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
0
ybtAuthor Commented:
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
0
HainKurtSr. System AnalystCommented:
no such thing...

on every page:

create db
open db
create command
run command
consume data
close db
dispose all
0
HainKurtSr. System AnalystCommented:
I cannot log in on each page

yes you can login on every page :)
0
ybtAuthor Commented:
well, to log in on each page, how should I pass ID and Password from page to page?
0
HainKurtSr. System AnalystCommented:
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

0
Kyle AbrahamsSenior .Net DeveloperCommented:
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

0
ybtAuthor Commented:
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?
0
Kyle AbrahamsSenior .Net DeveloperCommented:
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.
0
ybtAuthor Commented:
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
0
Kyle AbrahamsSenior .Net DeveloperCommented:
What I would do in that case is store the following in your web.config:
(BE sure to REPLACE SERVER AND DB . . . leave user id and password alone, that'll be formatted later.  (see below).
  
<connectionStrings>
<add name="DbConnString" connectionString="server=<SERVER>;persist security info=False;user id={0};password={1};database=<DB>" providerName="System.Data.SqlClient" />
</connectionStrings>

Open in new window


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

Open in new window


Login.aspx:
if (ValidateUser(sUserName, sPassWord))  // verify you can connect.
{
   Session["ls_user"] = sUserName;  // save user
   Session["ls_pwd"] = sPassword;  // save password
   FormsAuthentication.SetAuthCookie(sUserName, false); // set cookie so you don't get challenged again
   Response.Redirect("~/Default.aspx", true);  // redirect to default.aspx
}

Open in new window



In other pages:
SqlConnection DbConn = new SqlConnection(String.Format(ConnectionStrings["NorthindConnectionString"].ConnectionString, Session["ls_user"], Session["ls_pwd"]));

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ybtAuthor Commented:
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;
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
0
ybtAuthor Commented:
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
0
Kyle AbrahamsSenior .Net DeveloperCommented:
ValidateUser() was just a sample function.  You would need to write that yourself to verify the username / password is a successful combo.

ConnectionStrings was a typo . . . should be :
ConfigurationManager.ConnectionStrings

Open in new window

0
ybtAuthor Commented:
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;
0
Kyle AbrahamsSenior .Net DeveloperCommented:
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?
0
ybtAuthor Commented:
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
0
Kyle AbrahamsSenior .Net DeveloperCommented:
Session["ls_user"] = TextBox1.Text.ToLower();
Session["ls_pwd"] = TextBox2.Text.ToLower();

Open in new window

0
ybtAuthor Commented:
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
0
Kyle AbrahamsSenior .Net DeveloperCommented:
can you post your code as is?
0
ybtAuthor Commented:
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);
            }
         
        }
0
Kyle AbrahamsSenior .Net DeveloperCommented:
No reason to have anything in the pageLoad on the first page.

Also are you using DbConnString in the web.config as the name?  If so then that should be the connectionStringName used on the 2nd page.

EG:
ConfigurationManager.ConnectionStrings["DbConnString"].ConnectionString

The names have to match.  The point is you can have multiple connection strings defined, and then you can code which one you want.
0
ybtAuthor Commented:
I corrected, but still error:
Error Code         0x80070032
Config Error         The configuration section 'authentication' cannot be read because it is missing a section declaration
0
Kyle AbrahamsSenior .Net DeveloperCommented:
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.
0
ybtAuthor Commented:
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>
0
Kyle AbrahamsSenior .Net DeveloperCommented:
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.
0
ybtAuthor Commented:
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?
0
Kyle AbrahamsSenior .Net DeveloperCommented:
in the web.config:

    <system.web>
      <compilation debug="true" targetFramework="4.5" />
      <httpRuntime targetFramework="4.5" />
   <authentication mode="Forms">
      <forms loginUrl="~/Login.aspx" timeout="2880" />
    </authentication>

    </system.web>
0
ybtAuthor Commented:
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?
0
Kyle AbrahamsSenior .Net DeveloperCommented:
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.
0
ybtAuthor Commented:
Thank you very much, you spent a lot of time with me
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.