Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Scope problem. Reuse SQL connection
Message
From
23/05/2005 08:45:55
 
 
To
All
General information
Forum:
ASP.NET
Category:
Other
Title:
Scope problem. Reuse SQL connection
Environment versions
Environment:
C# 1.1
OS:
Windows XP SP2
Network:
Windows XP
Database:
MS SQL Server
Miscellaneous
Thread ID:
01016693
Message ID:
01016693
Views:
56
I am having a basic scope problem that I don't yet grok completely.
I created a sample winform as proof of concept. It worked so well that I basically finished the working portion of the program right there. The gist is a basic program to periodically read ASCII data and insert into SQL Server. No big difficulty as a learning project (C#). As this will be run from a task scheduler, it needs no interface. I created a Console project (my first .Net console app) and copied everything but the interface from the Winform. Changed all MessageBox.Show() with a log to xml routine and changed the getDir() to selecxt a sample file to a routine that reads all files in a folder, process them and moves them to archive folder. Each routine works fine.

Putting it all together I find a scope problem the Winform example did not have and it is basically the same code!

I want to establish a SQLConnection and reuse it through the whole program. The basic program skeleton is as follows (most of the unimportant code and comments are stripped):
using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
namespace ConsoleSample
{
  class MainDQMClass
  {
    public const string INPUTFOLDER = @"c:\temp\";

    // here's the one I want to define globally to be reused
    // but I can't get to work. Is this the correct way?
    public SqlConnection oConnection;

    [STAThread]
    public static int Main(string[] args)
    {
      int nReturnCode = 0;

      // global error handler
      try
      {
        // reads input folder (dir)
        // process each file found
        ListFiles(INPUTFOLDER);
      }
      catch (Exception e)
      {
        nReturnCode = -1;
        // call a method to log error
      }
      return nReturnCode;
    }

    public static void ListFiles(string cFolder)
    {
      DirectoryInfo dir = new DirectoryInfo(cFolder);
      foreach (FileInfo f in dir.GetFiles("*.txt")) 
      {
        // for test purposes, list filename to console
        Console.Write(f.FullName+"\n");

        // process each file
        bool lWasProcessed=ProcessInput(f);

        // move processed file to archive folder
        if (lWasProcessed)
        {
          MoveToArchive(f);
        }
      }
    }

    public static bool ProcessInput(FileInfo f)
    {
      bool lError=false;

      // create a connection, keep open while processing batch
      try 
      {
        oConnection = ConnectToSqlServer();
      }
      catch (Exception oErr)
      {
        lError=true;
        // log error
      }

      // if connection is open here is where we do the whole
      // data processing...
      // oConnection is used extensively in the many methods that follow (not shown)
      // all fail to compile as oConnection sems to be out of scope
    }

    public static SqlConnection ConnectToSqlServer()
    {
      string cUID="test";
      string cPWD="PASSWORD";
      
      // Create the connection
      SqlConnection conn = new SqlConnection();
      conn.ConnectionString = "server=xxxx;"+
        "uid="+cUID+
        ";pwd="+cPWD+
        ";database=DataQuality";

      try
      {
        conn.Open();
      }
      catch (Exception oErr)
      {
        //"Failed to connect to data source.\n"+oErr.Message
      }
      return conn;
    }
  }
}
The compiler error I keep getting is:
An object reference is required for the nonstatic field, method or property 'xxx'
This happens on every method that uses the connection. What am I missing that is so basic as scope?
How do I define and establish the connection to be reused in a Console exe?

TIA


Alex Feldstein, MCP, Microsoft MVP
VFP Tips: English - Spanish
Website - Blog - Photo Gallery


"Once again, we come to the Holiday Season, a deeply religious time that each of us observes, in his own way, by going to the mall of his choice." -- Dave Barry
Next
Reply
Map
View

Click here to load this message in the networking platform