How to create connection string using parameter C#

Question

I am a newbie. Sorry!

My Windows Form App has 3 layer. Presentation has Form_Login with textEdit_Name and textEdit_Pass.

My Connection class:

public class _Connection
{
    public OleDbConnection GetConn(string _name, string _pass)
    {
        OleDbConnection _Conn = new OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User ID={1};Password={2};", @"C:\Test\Test.mdb", _name, _pass));
        return _Conn;
    }
}

My Data Access Layer:

public class getDAL : IDisposable
{
    private _Connection getConn = new _Connection();
    OleDbConnection _Conn = new OleDbConnection();
    public DataTable getDatatable()
    {
        _Conn = getConn.GetConn();
        //Do something
    }
}

How can I get _Conn with:

_name = textEdit_Name.Text and _pass = textEdit_Pass.Text

when user login through Form_Login

Do you create instance of DAL in Business Layer or Presentation Layer? — Hassan Nisar
– Hassan Nisar, Commented Jun 25, 2014 at 4:30
That means in Form_Login you make instance of Business Layer and then in Business Layer you are creating instance of DAL? If yes then have to pass your name and pass to other layers. — Hassan Nisar
– Hassan Nisar, Commented Jun 25, 2014 at 4:33
Have a look at the OleDbConnectionStringBuilder class - this can help you create valid connection strings very easily — marc_s
– marc_s, Commented Jun 25, 2014 at 5:04

Corey · Accepted Answer · 2014-06-25 04:46:09Z

1

Perhaps the simplest method would be to set up the ConnectionString details in a static class in your DAL. Something like this:

public static class ConnectionDetails
{
    public static string UserName;
    public static string Password;

    public static string GetAccessConnectionString(string filepath)
    {
        StringBuilder sb = new StringBuilder();
        sb.AppendFormat("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};", filepath);
        if (string.IsNullOrEmpty(UserName) || string.IsNullOrEmpty(Password))
            sb.Append("User Id=admin;Password=;");
        else
            sb.AppendFormat("User Id={0};Password={1};", UserName, Password);

        return sb.ToString();
    }
}

Your business layer needs to set the UserName and Password fields of the ConnectionDetails static class whenever they change in your presentation layer, and your DAL classes that need connection strings should get them by calling ConnectionDetails.GetAccessConnectionString with the appropriate file path. Or add FilePath as one of the static fields in that class so that you can set all three from wherever.

If you're intending to use more than one database file then a dictionary of connection data keyed off the file name would probably be a good idea.

answered Jun 25, 2014 at 4:46

Corey

16.6k2 gold badges38 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HackSlash Over a year ago

ConnectionStringBuilder is what you want here.

Corey Over a year ago

@HackSlash Yeah, true. But I did write that 4 years ago. I've moved on since then.

Nitish Katare · Accepted Answer · 2014-06-25 05:29:39Z

1

You can use the ConnectionStringBuilder Class

public static class BuildConnection()
{    
    public static GetConnectionString(var userName, var Password)
    {
          OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder();
          builder.Driver = "Microsoft Access Driver (*.mdb)";        
          builder.Add("Dbq", @"C:\Test\Test.mdb");
          builder.Add("User Id", userName);
          builder.Add("Password", Password);
          return builder.ConnectionString; // Here is your connection String 
    }
}

answered Jun 25, 2014 at 5:29

Nitish Katare

1,1653 gold badges12 silver badges22 bronze badges

Comments

Hassan Nisar · Accepted Answer · 2014-06-25 08:55:11Z

0

Here is example how you can pass LoginForm TextBox .Text to Business Layer and Data Access Layer.

Data Access Layer:

class DALClass : IDisposable
{
    private _Connection getConn;
    OleDbConnection _Conn;

    public DALClass()
    {
         getConn = new _Connection();
    }

    public DataTable getDatatable(string sUserName, string sUserPass)
    {
        //pass on user name and password
        _Conn = getConn.GetConn(sUserName, sUserPass);
        //Do something

        return default(DataTable);  //return datatable 
    }
}

public class _Connection
{
    public OleDbConnection GetConn(string _name, string _pass)
    {
        OleDbConnection _Conn = new OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User ID={1};Password={2};", @"C:\Test\Test.mdb", _name, _pass));
        return _Conn;
    }
}

Business Layer:

class BLClass
{
    DALClass _dal;       

    public BLClass()
    {
        _dal = new DALClass();            
    }

    public DataTable GetDataTable(string sUserName, string sUserPass)
    {
        return _dal.getDatatable(sUserName, sUserPass);
    }
}

Login Form:

 public partial class LoginForm : Form
 {
    BLClass _bl;

    public LoginForm()
    {
        InitializeComponent();
        //object of business layer
        _bl = new BLClass();
    }

    private void button1_Click(object sender, EventArgs e)
    {    
        //pass text boxes values to Business Layer      
        DataTable dt = _bl.GetDataTable(textEdit_Name.Text, textEdit_Pass.Text);            
    }
 }

edited Jun 25, 2014 at 8:55

answered Jun 25, 2014 at 5:33

Hassan Nisar

5,4502 gold badges26 silver badges35 bronze badges

9 Comments

Camambeou Over a year ago

No, it does not work... bl.sUserName and _bl.sUserPass is null when run to get connection string

Hassan Nisar Over a year ago

@Camambeou. Sorry, I missed something obvious. Can you check my updated code?

Camambeou Over a year ago

Thanks for your idear. But it still doesn't work correctly. I don't know why. I think when we call the class is call a new one so the parameter is new too (get default value <null>)

Hassan Nisar Over a year ago

Instances of business class and dal access are called once in the respective constructors. Notice in LoginForm() constructor BLClass instance _bl is instantiated.

Camambeou Over a year ago

I'm stuck, string _name and _pass always get null and application throw error "Invalid user name or password"... Do you have any idea?!

|

Collectives™ on Stack Overflow

How to create connection string using parameter C#

3 Answers 3

2 Comments

Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related