I recently working in .Net field and under circumstances had to write a program to execute sql from external source file.I have written the code and works fine but when it goes to Production server,It crashes becasuse of too amny files to handle.Can someone just look and guide the improvement areas.Input Params-Mode-Inline /file(if it is inline,the user gives entire sql command and if its file,user give path of .sql file)SqlData-sql comand or .sql file name.    static void Main(string[] args)    {           string Mode = args[0].ToString().Trim();            string sqlData = args[1].ToString().Trim();            string ServerName = args[2].ToString().Trim();            string DbName = args[3].ToString().Trim();            string Uid = args[4].ToString().Trim();            string Password = args[5].ToString().Trim();            string CsvLocation = args[6].ToString().Trim();            string CsvName = args[7].ToString().Trim();            loglocation = args[8].ToString().Trim();            try            {                string connectionString = @Data Source= + ServerName + ;Initial Catalog= + DbName + ;User ID= + Uid + ;Password= + Password;                using (SqlConnection conn = new SqlConnection(connectionString))                {                    Task<int> wakeUp = DoWorkAsync(conn, Mode, sqlData, CsvLocation, CsvName);                    Task.WaitAll(wakeUp);                }            }            catch (CustomException e)            {                Console.WriteLine(e.ToString());                CustomException.Write(CustomException.CreateExceptionString(e, e.p));                Console.ReadLine();            }            catch (Exception e)            {                Console.WriteLine(e.ToString());                CustomException.Write(CustomException.CreateExceptionString(e));                Console.ReadLine();            }        }    }    static Task<int> DoWorkAsync(System.Data.SqlClient.SqlConnection conn, string Mode, string sqlData, string CsvLocation, string CsvName)    {     return Task.Factory.StartNew<int>(() =>        {        try        {            string script = string.Empty;            string timestamp = DateTime.Now.ToString(yyyyMMddHHmmss);            SqlCommand cmd = new SqlCommand();            SqlDataReader reader;            string modefinal = Mode.ToLower();            if (modefinal == inline)            {                script = sqlData;            }            else if (modefinal == file)            {                script = File.ReadAllText(sqlData);            }            else            {                throw (new CustomException(wrong input Parameter: MODE));            }            //  Server server = new Server(new ServerConnection(conn));            //   server.ConnectionContext.ExecuteNonQuery(script);            cmd.CommandText = script;            cmd.Connection = conn;            conn.Open();            reader = cmd.ExecuteReader();            string desiredFilename = CsvLocation + \\ + CsvName + .csv;            if (File.Exists(desiredFilename))            {                desiredFilename = CsvLocation + \\ + CsvName + _ + timestamp + .csv;            }            StreamWriter writer = new StreamWriter(desiredFilename);            string Delimiter = \;            string Separator = ,;            // write header row            for (int columnCounter = 0; columnCounter < reader.FieldCount; columnCounter++)            {                if (columnCounter > 0)                {                    writer.Write(Separator);                }                writer.Write(Delimiter + reader.GetName(columnCounter) + Delimiter);            }            writer.WriteLine(string.Empty);            // data loop            while (reader.Read())            {                // column loop                for (int columnCounter = 0; columnCounter < reader.FieldCount; columnCounter++)                {                    if (columnCounter > 0)                    {                        writer.Write(Separator);                    }                    writer.Write(Delimiter + reader.GetValue(columnCounter).ToString().Replace('', '\'') + Delimiter);                }   // end of column loop                writer.WriteLine(string.Empty);            }   // data loop            writer.Flush();            conn.Close();            return 1;        }      catch(CustomException a)          {           return 0;          throw;          }        catch (Exception e)        {            return 0;            throw ;        }        });    }}
Performance issue in generic sql utility
c#;multithreading
I would strongly advise you use something like NDesk.Options when you need to deal with so many parameters in a console application.Your casing style is inconsistent: Mode, ServerName, DbName, etc. should all be camelCase when they're local variables or parameters of methods.Avoid unreadable concatenation:@Data Source= + ServerName + ;Initial Catalog= + DbName + ;User ID= + Uid + ;Password= + Password;This is why string.Format() exists.DoWorkAsync is a really generic method name. Be more precise.Don't have all your code in Program.cs. Instead, construct a class (or multiple if necessary) and call that class from the Main of Program.cs.You correctly use using (SqlConnection conn = new SqlConnection(connectionString)) to dispose of conn, yet you don't do the same for SqlCommand, SqlDataReader, etc. Your performance issue probably stems in part from not properly disposing of these.Code like this strongly suggests that Mode should be an enum.string modefinal = Mode.ToLower();if (modefinal == inline)throw (new CustomException(wrong input Parameter: MODE)); doesn't tell me which invalid value of Mode triggered it.You already define these three variables:string timestamp = DateTime.Now.ToString(yyyyMMddHHmmss);SqlCommand cmd = new SqlCommand();SqlDataReader reader;Yet before they can be used it is perfectly possible the method will exit due to throw (new CustomException(wrong input Parameter: MODE));. Keep such definitions closer to where these variables are actually used.DoWorkAsync is nearly 100 lines long and does far too much. Split it up into smaller specialized methods, e.g. a method to construct the file name, etc.