EXCEL - C# Discussion Boards

Re: TCPListener/Many TCPClients -> Performance Issues?

Matt Meyer18-Nov-09 4:43

18-Nov-09 4:43

Here's a simple server program demonstrating how the ports work. It'll accept 10 connections, hang onto each one for 30 seconds, then shut itself down. Telnet into port 2323 with a command prompt to connect. If you connect 3 clients, netstat will yield a similar output to this (client ports are random, so it's not exact):

Proto  Local Address           Foreign Address        State
TCP    workstation:1161        localhost:2323         ESTABLISHED
TCP    workstation:1162        localhost:2323         ESTABLISHED
TCP    workstation:1163        localhost:2323         ESTABLISHED
TCP    workstation:2323        localhost:1161         ESTABLISHED
TCP    workstation:2323        localhost:1162         ESTABLISHED
TCP    workstation:2323        localhost:1163         ESTABLISHED

The client's ports are randomly assigned to a number over 1024, but note that the server will always use port 2323 for its communication. When a client connects, the server does not create a new connection on a secondary port for communications.

However, some protocols do work like you have described above, randomly using an additional port for a secondary connection (IE, FTP does for data transfers, which is why FTP servers behind firewalls are a pain in the butt Smile | :)

).

Server Code -

[STAThread]
public static void Main(string[] Args) {
  TcpListener listener = null;
  try {
    listener = new TcpListener(IPAddress.Loopback, 2323);
    listener.Start();
    int connection_count = 0;
    while(connection_count<10) {
      if(listener.Pending()) {
        TcpClient connection = listener.AcceptTcpClient();
        ThreadPool.QueueUserWorkItem(new WaitCallback(Communicate), connection);
        Console.WriteLine("Connection Accepted ({0})", connection_count++);
      }
      Thread.Sleep(1);
    }
    Console.WriteLine("Waiting 30 seconds for last connection to close.");
    Thread.Sleep(30005);
    Console.WriteLine("Shutting Down.");
  } finally {
    if(listener!=null) {
      listener.Stop();
    }
  }
}

private static void Communicate(object Connection) {
  if(Connection==null) return;
  TcpClient client = (TcpClient)Connection;
  StreamWriter writer = null;
  try {
    if(client.Connected) {
      writer = new StreamWriter(client.GetStream());
      writer.WriteLine("Connected.  You will be disconnected in 30 seconds.");
      writer.Flush();
      Thread.Sleep(30000);
      writer.WriteLine("Bye!");
      writer.Flush();
    }
  } catch {
    if(writer!=null) {
      writer.WriteLine("Connection error.");
      writer.Flush();
    }
  } finally {
    Console.WriteLine("Closing Client.");
    if(client.Connected) {
      client.Close();
    }
  }
}

Re: TCPListener/Many TCPClients -> Performance Issues?

Paulo Zemek18-Nov-09 11:18

Paulo Zemek

18-Nov-09 11:18

You are wrong.
Maybe the results are as you showed, but try showing the IP address of this:
TcpClient connection = listener.AcceptTcpClient();

This connection does not use the same port as the listener port. At least not in my computer.

Re: TCPListener/Many TCPClients -> Performance Issues?

Matt Meyer18-Nov-09 11:33

Matt Meyer

18-Nov-09 11:33

connection.Client.LocalEndPoint shows the servers IP address and listening port (in my example, 127.0.0.1:2323). The connection.Client.RemoteEndPoint is the client address/port that is connecting to the server, which does have a randomly selected port above 1024.

export to excel in C# windows application

FEMDEV17-Nov-09 4:01

FEMDEV

17-Nov-09 4:01

Hi ,
I have data coming from a stored procedure in sql server. I was able to export the results to excel in asp.net page using a grid on the page i.e. if user wants to download it into excel file.

now I want same functionality, i.e exporting the results from query/stored procedure directly to excel in C# windows application. How can I do this.
I don't want to use a grid on my page.

Cross Post

dan!sh 17-Nov-09 4:15

dan!sh

17-Nov-09 4:15

Do not cross post.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

excel

FEMDEV17-Nov-09 4:17

FEMDEV

17-Nov-09 4:17

That was deleted as i posted in wrong forum.

Re: Cross Post

Abhishek Sur17-Nov-09 4:21

Abhishek Sur

17-Nov-09 4:21

I think I have already suggested one link to do this.

Abhishek Sur
Don't forget to click "Good Answer" if you like this Solution.
My Latest Articles-->
Simplify Code Using NDepend
Basics of Bing Search API using .NET
Microsoft Bing MAP using Javascript

EXCEL

FEMDEV17-Nov-09 4:22

FEMDEV

17-Nov-09 4:22

Thanx Abhishek Sur

modified on Friday, November 20, 2009 10:44 AM

Excel Formatting

FEMDEV20-Nov-09 4:17

FEMDEV

20-Nov-09 4:17

Hi abhishek,
I also need to format my excel sheet as follows:
the data has different values in 5 columns for the same values in the first 10 columns.
I need to show the columns with same data only once and then just populate the columns whuich have different data for the same value in first 10 columns.

Re: Excel Formatting

Abhishek Sur21-Nov-09 8:49

Abhishek Sur

21-Nov-09 8:49

FEMDEV wrote:
I need to show the columns with same data only once and then just populate the columns whuich have different data for the same value in first 10 columns.

I am totally stumped.... I am confused. Please let me clear this a bit. Roll eyes | :rolleyes:

Abhishek Sur
Don't forget to click "Good Answer" if you like this Solution.
My Latest Articles-->
Simplify Code Using NDepend
Basics of Bing Search API using .NET
Microsoft Bing MAP using Javascript

ExecuteReader requires an open and available Connection. The connection's current state is closed.

Vimalsoft(Pty) Ltd17-Nov-09 3:54

Vimalsoft(Pty) Ltd

17-Nov-09 3:54

Good Day All

i have the Following Function

private static void OMEGA_calcActvEqv()
  {
      SqlConnection conn1 = CommonFunctions.getSQLConnectionForThread(THREAD_DATA[0].ToString());
      string SQL = "SELECT MAX(D.OFFS + 1) * MAX(R.OFFS + 1) FROM TBL_ROWS R, TBL_CLMN D; SELECT MAX(OFFS + 1) FROM TBL_ROWS;";
      SqlCommand cmd = new SqlCommand(SQL, conn1);
      SqlDataReader rdr = cmd.ExecuteReader();

      int size = 0;
      if (rdr.Read())
      {
          size = Convert.ToInt32(rdr[0]);
      }

      int maxPeriods = 0;
      if (rdr.NextResult())
      {
          if (rdr.Read())
          {
              maxPeriods = Convert.ToInt32(rdr[0]);
          }
      }
      rdr.Close();

      SQL = "SELECT MEA.EQV [EQV], MEA.ACTV [ACTV] FROM MTM_EQV_ACTV MEA ORDER BY MEA.EQV, MEA.ACTV ";

      if ((THREAD_DATA.Length > 3) && (THREAD_DATA[3] != null))
      {
          string EQV = CommonFunctions.ExecuteScalarInThread("SELECT MEA.EQV FROM MTM_EQV_ACTV MEA WHERE ACTV=" + THREAD_DATA[3], conn1).ToString();
          SQL = "SELECT MEA.EQV [EQV], MEA.ACTV [ACTV] FROM MTM_EQV_ACTV MEA WHERE EQV='" + EQV + "' ORDER BY MEA.EQV, MEA.ACTV ";
      }

      cmd = new SqlCommand(SQL, conn1);
      rdr = cmd.ExecuteReader();

      string eqvSet = string.Empty;
      List<int> actvs = new List<int>();

      while (rdr.Read())
      {
          if (eqvSet != rdr["EQV"].ToString())
          {
              if (actvs.Count > 1)
              {
                  OMEGA_intersectDomns(actvs, size, maxPeriods);
              }

              actvs.Clear();
              eqvSet = rdr["EQV"].ToString();
              if (!DBNull.Value.Equals(rdr["ACTV"]))
              {
                  actvs.Add(Convert.ToInt32(rdr["ACTV"]));
              }
          }
          else
          {
              if (!DBNull.Value.Equals(rdr["ACTV"]))
              {
                  actvs.Add(Convert.ToInt32(rdr["ACTV"]));
              }
          }
      }

      rdr.Close();

      if (actvs.Count > 1)
      {
          OMEGA_intersectDomns(actvs, size, maxPeriods);
          actvs.Clear();
      }

      conn1.Close();
      conn1.Dispose();
  }

and the Definition of function ExecuteScalarInThread

is

public static object ExecuteScalarInThread(string sql, SqlConnection conn1)
{

    SqlCommand sqlcommand = new SqlCommand(sql, conn1);
    //You need to apply the the connection to the command after connection open
    sqlcommand.CommandType = CommandType.Text;

    object ret = new object();

    if (conn1.State != ConnectionState.Open)
    {
        conn1.Open();
    }
    try
    {
        ret = sqlcommand.ExecuteScalar();
    }
    catch (Exception e)
    {
        ret = e.Message;
    }

    conn1.Close();
    return ret;
}

i get an Error in the bolded line of the Function OMEGA_calcActvEqv() on this line

rdr = cmd.ExecuteReader();

that says

ExecuteReader requires an open and available Connection. The connection's current state is closed.

Thanks

Vuyiswa Maseko,

Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Re: ExecuteReader requires an open and available Connection. The connection's current state is closed.

dan!sh 17-Nov-09 4:24

dan!sh

17-Nov-09 4:24

You are closing the connection inside ExecuteScalarInThread method. That should be the problem.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

Re: ExecuteReader requires an open and available Connection. The connection's current state is closed.

Vimalsoft(Pty) Ltd17-Nov-09 4:35

Vimalsoft(Pty) Ltd

17-Nov-09 4:35

Thank you very much. i had a LongDay.

that solved my Problem i can go home now

Re: ExecuteReader requires an open and available Connection. The connection's current state is closed.

Abhijit Jana17-Nov-09 4:48

Abhijit Jana

17-Nov-09 4:48

Vuyiswa Maseko wrote:
Thank you very much. i had a LongDay.

that solved my Problem i can go home now

Then you should mark that post as helful. Smile | :)

Abhijit Jana | Codeproject MVP
Web Site : abhijitjana.net
Don't forget to click "Good Answer" on the post(s) that helped you.

Re: ExecuteReader requires an open and available Connection. The connection's current state is closed.

Vimalsoft(Pty) Ltd17-Nov-09 4:50

Vimalsoft(Pty) Ltd

17-Nov-09 4:50

How by Voting for it ? i have already done that. or did i miss something Smile | :)

Re: ExecuteReader requires an open and available Connection. The connection's current state is closed.

Abhijit Jana17-Nov-09 5:58

Abhijit Jana

17-Nov-09 5:58

Vuyiswa Maseko wrote:
or did i miss something

No !

Your are done ! Thumbs Up | :thumbsup:

Thanks !

Abhijit Jana | Codeproject MVP
Web Site : abhijitjana.net
Don't forget to click "Good Answer" on the post(s) that helped you.

Re: ExecuteReader requires an open and available Connection. The connection's current state is closed.

dan!sh 17-Nov-09 5:58

dan!sh

17-Nov-09 5:58

Enjoy your evening.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

Re: ExecuteReader requires an open and available Connection. The connection's current state is closed.

Dave Kreskowiak17-Nov-09 4:51

Dave Kreskowiak

17-Nov-09 4:51

OK, you didn't open the connection before you tried to Execute the Reader. So, put a line in before the ExecuteReader to open the conn1 connection object.

conn1.Open();

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008
But no longer in 2009...

Re: ExecuteReader requires an open and available Connection. The connection's current state is closed.

Vimalsoft(Pty) Ltd17-Nov-09 4:54

Vimalsoft(Pty) Ltd

17-Nov-09 4:54

hi Dave long time. I have a Function that opens the Connection. This is Where i open the connection

<br />
SqlConnection conn1 = CommonFunctions.getSQLConnectionForThread(THREAD_DATA[0].ToString());

but now my mistake was that in another function i closed this Global Connection and it gave a problem when i tried to execute the reader again later. So d@nish saw it and pointed it out.

Thanks Dave

Re: ExecuteReader requires an open and available Connection. The connection's current state is closed.

PIEBALDconsult17-Nov-09 5:40

PIEBALDconsult

17-Nov-09 5:40

Yeah, what they said.

You should go further and Open the Connection before each use and Close it afterward (preferably in a finally).

You can also check the Connection's State and Open it if needed -- that's what I do.

(Working with DataReaders makes this a little more difficult because you have to hold the Connection open and remember to Close it later.)

Re: ExecuteReader requires an open and available Connection. The connection's current state is closed.

Vimalsoft(Pty) Ltd18-Nov-09 20:14

Vimalsoft(Pty) Ltd

18-Nov-09 20:14

You are Right. i have Opened the Connection. i have a Global Connection Module. And when it moved to another Function that function closed that Connection. So that where was the the Problem. By Commenting that part it worked.

Thanks

Problem while Parsing DateTime.

Sunil G 317-Nov-09 3:33

Sunil G 3

17-Nov-09 3:33

Hi,

I am collecting OS info using Win32_OperatingSystem(WMI).
its one property(InstallDate) which gives Installation Date of the OS, but datetime is in 20091117110355.375000+330 format. I want to convert this into actual DateTime format.

Thanks,
Sunil G.

Re: Problem while Parsing DateTime.

musefan17-Nov-09 3:40

musefan

17-Nov-09 3:40

Try this...

DateTime GetMeTheDateAndTime(string s)
{
     return DateTime.ParseExact(s.Substring(0, 14), "yyyyMMddHHmmss", System.Globalization.CultureInfo.InvarientCulture);
}

...you may wan't to apply some validation thou

Life goes very fast. Tomorrow, today is already yesterday.

Re: Problem while Parsing DateTime.

Sunil G 317-Nov-09 3:52

Sunil G 3

17-Nov-09 3:52

Thank You Very Much

Re: Problem while Parsing DateTime.

Luc Pattyn17-Nov-09 3:49

Luc Pattyn

17-Nov-09 3:49

This works for sure, there may be something more compact:

string s="20091117110355.375000+330";
DateTime.ParseExact(s.Substring(0,14), "yyyyMMddHHmmss",null);

Luc Pattyn [Forum Guidelines] [My Articles]

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.