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?
Web DevelopmentASP.NETC#

Avatar of undefined
Last Comment
ybt

8/22/2022 - Mon
Ryan Chong

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)
ybt

ASKER
In C# winforms  connection was setted in MDI Frame, I did not find similar in webforms
Kyle Abrahams

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
HainKurt

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
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
HainKurt

no such thing...

on every page:

create db
open db
create command
run command
consume data
close db
dispose all
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
HainKurt

I cannot log in on each page

yes you can login on every page :)
ybt

ASKER
well, to log in on each page, how should I pass ID and Password from page to page?
HainKurt

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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Kyle Abrahams

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

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?
Kyle Abrahams

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
Kyle Abrahams

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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;
Ryan Chong

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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;
Kyle Abrahams

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
Kyle Abrahams

Session["ls_user"] = TextBox1.Text.ToLower();
Session["ls_pwd"] = TextBox2.Text.ToLower();

Open in new window

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Kyle Abrahams

can you post your code as is?
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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Kyle Abrahams

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.
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>
Kyle Abrahams

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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?
Kyle Abrahams

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ybt

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