Re: Connecting to database [modified] - C# Discussion Boards

9-Feb-09 5:54

Hi all,

Could you please help me how to access a database from the following config file(web config).

<configuration>
<sqlserver>SERVER_BMRD</sqlserver>
<sqldatabase>B</sqldatabase>
<sqluser>BD_user</sqluser>
<sqlpassword>^1^vtqhCBOwbVJc+ODAe2Sm7A==</sqlpassword>
<sqltrusted>False</sqltrusted>
</configuration>

How will i be able to connect to the above database?

For example :

SqlDataAdapter sda = new SqlDataAdapter("Stored_procedure", Connectionstring);

Can you give me how my connection string should look like?

Thank you so much

Rob Philpott9-Feb-09 6:03

Re: Connecting to database [modified]

9-Feb-09 6:03

That looks like a slightly strange config section. Are you aware of the ConnectionStrings section which provides simple access for this stuff?

http://msdn.microsoft.com/en-us/library/ms178411.aspx[^]

Regards,
Rob Philpott.

kibromg9-Feb-09 6:16

9-Feb-09 6:16

Thanks for your response Rob.

It was given to me as a database credentials to be able to connect to the database.

You can see that they dont use connection string ?so that is my problem
How can i connect to a database using those credentials?

Regards,
kibrom

modified on Monday, February 9, 2009 12:23 PM

Rob Philpott9-Feb-09 6:27

9-Feb-09 6:27

OK, to my knowledge that won't be easy. Config files have to follow a certain format, and you define sections when you want to implement your own settings. Just placing individual bits in there like that will likely cause the ConfigurationManager to protest.

Your app.config file is invalid. As such, the best bet would be to bypass the ConfigurationManager altogether and process the file as any other xml file. One such way would be to use an XmlDocument and retrieve the settings using XPath.

Regards,
Rob Philpott.

kibromg9-Feb-09 6:33

9-Feb-09 6:33

Hi Rob ,

Thank you so much for your help.Sorry as i am new to this could you please give me a tip on how to use it with XMLReader or XMlDocument?

Thanks a lot.

Rob Philpott9-Feb-09 6:43

9-Feb-09 6:43

Sure. I'm just about to leave work now, so I'll put something together in a couple of hours once home. A few things to check - this file is called web.config and resides in the usual place with your application?

Also, you absolutely cannot change the format of this file? A simple inclusion of appSettings would make this a breeze.

I'd create a class which reads this file and then exposes each setting you need as a public string property. If I remember correctly the ASP.NET world watches web.config and updates automatically - you wouldn't get that with this approach.

More later...

Regards,
Rob Philpott.

kibromg9-Feb-09 6:57

9-Feb-09 6:57

Hi Rob,

Your help is Much appreciated Rob.Yes please its a config file.But its not a webconfig file.For instance the above can be called admin.config file.

The file doesnt have app setting? we can still include appSettings.However they dont have it in this file.

Thanks again for your time.

Rob Philpott9-Feb-09 8:54

9-Feb-09 8:54

The class below should do what you want, create a new instance passing in the config file as the parameter to the constructor.

public class DatabaseConfiguration
{
    public readonly string SqlServer;
    public readonly string Database;
    public readonly string User;
    public readonly string Password;
    public readonly bool Trusted;

    public DatabaseConfiguration(string path)
    {
        XmlDocument settings = new XmlDocument();
        settings.Load(path);

        SqlServer = settings.SelectSingleNode("/configuration/sqlserver").InnerText;
        Database = settings.SelectSingleNode("/configuration/sqldatabase").InnerText;
        User = settings.SelectSingleNode("/configuration/sqluser").InnerText;
        Password = settings.SelectSingleNode("/configuration/sqlpassword").InnerText;
        Trusted = settings.SelectSingleNode("/configuration/sqltrusted").InnerText.ToLower() == "true";
    }
}

Regards,
Rob Philpott.

kibromg9-Feb-09 23:41

9-Feb-09 23:41

Hi Rob,

You are a champion,thanks a lot.I have done it like you said.

Sorry one more question, Can you give me an example on how to execute a stored procedure with the class.

Like for instance :-

DataSet ds = new DataSet();

SqlDataAdapter sda = new SqlDataAdapter("Test", ???????????????);

sda.SelectCommand.CommandType = CommandType.StoredProcedure;

sda.SelectCommand.Parameters.Add("@timeInStart", SqlDbType.DateTime);
sda.SelectCommand.Parameters["@timeInStart"].Value = from;

sda.SelectCommand.Parameters.Add("@timeInEnd", SqlDbType.DateTime);
sda.SelectCommand.Parameters["@timeInEnd"].Value = to;

sda.Fill(ds);

????????- This is a place for connection string? How do i go about that?

Thanks a lot Rob

Rob Philpott9-Feb-09 23:50

9-Feb-09 23:50

Well, your connection string should look like this (from connectionstrings.com):

Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;

So, add a new string property to the class and construct that string using what you have. That's what you want for ?????????.

eg:

public string ConnectionString
{
    get
    {
        return string.Format("Server={0};Database={1};User ID={2};Password={3};Trusted_Connection={4};", SqlServer, Database, User, Password, Trusted ? "True" : "False");
    }
}

Regards,
Rob Philpott.

kibromg10-Feb-09 0:05

10-Feb-09 0:05

Hi Rob,

Thanks very much.Can we connect to the database with out using the connection string?
Just with the class you have given me? Can you give me just an example.

Sorry i am taking you time.

Thanks Rob

Rob Philpott10-Feb-09 0:19

10-Feb-09 0:19

Yeah, you could add some functionality to that class which returns a valid open connection, and just use that as the connection

Something like:

public SqlConnection CreateConnection()
{
    SqlConnection connnection = new SqlConnection(ConnectionString);
    connection.Open();
    return connection;
}

Remember to use the connection in a using bloke or call its dispose when you are done with it.

Regards,
Rob Philpott.

kibromg10-Feb-09 0:37

10-Feb-09 0:37

Do you think this should work to execute a stored procedure.

SqlDataAdapter sda = new SqlDataAdapter("Stored_proce", DatabaseConfiguration.connectionstring);

or simply use

SqlDataAdapter sda = new SqlDataAdapter("Stored_proce", DatabaseConfiguration.createConnection

I hope this will definely sort me out.

Thanks a million Rob.

Rob Philpott10-Feb-09 0:57

10-Feb-09 0:57

I think you can use the first approach. Personally I'd create a connection, then create a command, then attach the adapter to that but its just personal preference.

You can reuse your connection if doing lots of things that way.

Regards,
Rob Philpott.

kibromg10-Feb-09 1:09