We help IT Professionals succeed at work.

ASP.NET data base connection

ybt
ybt asked
on
309 Views
Last Modified: 2017-04-24
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?
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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

Author

Commented:
In C# winforms  connection was setted in MDI Frame, I did not find similar in webforms
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
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.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
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

Author

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
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
no such thing...

on every page:

create db
open db
create command
run command
consume data
close db
dispose all
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
I cannot log in on each page

yes you can login on every page :)
ybt

Author

Commented:
well, to log in on each page, how should I pass ID and Password from page to page?
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
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

Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
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

Author

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?
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
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.
ybt

Author

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
Senior .Net Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
ybt

Author

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;
CERTIFIED EXPERT

Commented:
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
ybt

Author

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
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
ybt

Author

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;
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
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?
ybt

Author

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
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

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

Open in new window

ybt

Author

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
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
can you post your code as is?
ybt

Author

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);
            }
         
        }
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
ybt

Author

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
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
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

Author

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>
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
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.
ybt

Author

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?
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
ybt

Author

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?
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
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.
ybt

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions