Click here to Skip to main content
15,867,488 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hey Fellow Coders,
I want to open a dialog to find out what the community does in this type of situation. I need to connect to the SQL Server I have for this Window. The code below works just fine, but I don't like it. What I really want is a way to "make a connection" without a connection string in the code. Either make a file (.txt, .properties, .settings, .whatever) and read the connection string from there, or create a single class and set the connection string (or strings) one time.

P.S. I don't want this:

C#
using (
    var conn =
        new SqlConnection(@"Data Source=server_name;Initial Catalog=DB;Integrated Security=True;Persist Security Info=False;Encrypt=True;TrustServerCertificate=True;"))


I would rather use a resource, reference, class, whatever. I don't want to change this every time I release this code to someone new. I guess I want a "set it once and forget it" method.

P.S. Very Important - Releasing the code via ClickOnce in Visual Studio. Therefore, whatever method needs to work with ClickOnce.

C#
public static void GetPatient()
        {
            try
            {
                using (
                    var conn =
                        new SqlConnection(@"Data Source=server_name;Initial Catalog=DB;Integrated Security=True;Persist Security Info=False;Encrypt=True;TrustServerCertificate=True;"))
                {
                    var preQuery = Settings.Default.SqlGetPatient;
                    var queryString = preQuery + "@PatId = '" + PatId + "'";
                    conn.Open();
                    Log.Info("Established a SQL Connection and opened communication to database for patient: " + PatId);
                    using (var command = new
                        SqlCommand(queryString, conn))
                    {
                        var reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            PatName = reader[0] as string;
                            PatId = reader[1] as string;
                            PatSys = reader[2] as string;
                            PatAge = reader[3] as string;
                            break;
                        }
                    }
                    Log.Debug("Note Lookup as: " + queryString);
                    Log.Debug("DB Connection as:" + conn);
                    conn.Close();
                }
            }
            catch (SqlException ex)
            {
                // Build custom message box for exception upload
                var msg = ex.ToString();
                var title = Resources.CustomMsgBxTitle;
                MessageBoxManager.Ok = "Upload";
                MessageBoxManager.Cancel = "Ignore";
                MessageBoxManager.Register();
                var result = MessageBox.Show(msg, title, MessageBoxButtons.OKCancel, MessageBoxIcon.Hand);
                switch (result)
                {
                    case DialogResult.OK:
                        Clipboard.SetText(msg);
                        Process.Start("http://weburl.com/open.php");
                        break;
                    case DialogResult.Cancel:
                        break;
                }
                MessageBoxManager.Unregister();
            }
            Log.Info("Passing values to MainWindow: Patient ID: " + PatId + ", Patient Name: " + PatName +
                     ", Patient SYS_ID: " +
                     PatSys + ", Patient Age: " + PatAge + " - END");
        }
Posted

1 solution

 
Share this answer
 
Comments
Derek Kennard 23-Feb-15 20:38pm    
These dont seem to work. When I add the code in first article, it throws an "Object reference not set to an instance of an object"... (geeze, thanks for the helpful error message :))

If it matters or helps, I have the SQL connection and code in a DataLogic project and the form in a UserInterface project.

So, here's the code. Do you see anything wrong? - After the final {, the code is the same as above.

public static void GetPatient()
{
try
{
var dbcon = ConfigurationManager.ConnectionStrings["Dbconn"].ConnectionString;
var conn = new SqlConnection(dbcon);
{

I've also tried this and the same result.

public static void GetPatient()
{
try
{
string dbcon = ConfigurationManager.ConnectionStrings["Dbconn"].ConnectionString;
using (SqlConnection conn = new SqlConnection(dbcon))
{
Derek Kennard 24-Feb-15 0:58am    
I figured it out...

I used

using (
var conn =
new SqlConnection(ConfigurationManager.ConnectionStrings["Dbconn"].ConnectionString))

More importantly, App.config is not to be included in the installation, but the MyApp.exe.config...
Thank you jmcilhinney @ http://www.vbforums.com/showthread.php?551760-2005-app.config-included-in-setup

and

Thank you Abhinav

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900