Responsive Ads Here

Friday, June 1, 2018

Core FTP LT Commands

1:15 AM command , Core 0 Comments
Core FTP LT Commands

Command Line

Download a file via a FTP URL (see FTP URL's for more information).

From the command line type:


  • drive:\(coreftp path)\coreftp.exe -d ftp://ftp.yoursite.com/directory/yourfile.dat

or

  • drive:\(coreftp path)\coreftp.exe ftp://ftp.yoursite.com/directory/yourfile.dat


This will download the file into the default download directory specified in options, or to the last local directory that was used while in the program.

If you don't want the GUI to pop up, add the -s (silent) option to your command line parameters.

To use a different destination directory, include: -p drive:\directory


  • drive:\(coreftp path)\coreftp.exe -d ftp://ftp.yoursite.com/directory/yourfile.dat -p drive:\path\


Wildcard support was added as of build 1250.


  • drive:\(coreftp path)\coreftp.exe -d ftp://user:password@ftp.yoursite.com/directory/*.jpg -p drive:\path\


To rename the destination filename, use -FN <newfilename>

Upload a file:

From the command line, type:


  • drive:\(coreftp path)\coreftp.exe -u c:\path\yourfileorpath\filename ftp://ftp.yoursite.com/directory/yourfileorpath/
  • drive:\(coreftp path)\coreftp.exe -u c:\path\yourfileorpath\*.jpg ftp://user:password@ftp.yoursite.com/directory/yourfileorpath/


This will upload the file or path specified into the directory specified by the URL. Make sure the URL has all appropriate userid/password/permissions for the remote FTP server.

To use a different desination directory, use the -p /directory/: (example with site profile).

  • drive:\(coreftp path)\coreftp.exe -site mysite -u c:\path\yourfile\file.dat -p /remotedir/

Blocking Transfers:

By default Core FTP command line works asynchronously. 

To block transfers during processing, use the 'corecmd.exe' utility with the normal command line parameters.

SSL Options:

-ssl-- AUTH SSL

-tls-- AUTH TLS

-ssldirect-- Direct SSL (implicit)

-winssl-- Use Windows security layer (OpenSSL by default)

SSH/SFTP Options:

-SSH or sftp:// in the URL..

Log screen output to a file:

-log <outputfile>

Log directory information to a file:

  • -dir </remote_path/> <outputfile>

Process a template:

  • drive:\(coreftp path\coreftp.exe -t templatename

Add -refresh to reload template file sizes.
Transferring using a site profile:

If you need to use special settings like SSL or SSH, create a site profile in the Site Manager and then use the following command line parameter.
Use: -site profilename

Examples:

Download:

  • drive:\(coreftp path)\coreftp.exe -site mysite -d /directory/yourfile.dat -p c:\download\
Upload:

  • drive:\(coreftp path)\coreftp.exe -site mysite -u c:\myfile.dat -p /public_htm/mydirectory/

Overwrite and resume options:

Use:-O-- Overwrite if file exists

-OA-- Overwrite *all* if transfering a folder or directory of files.

-ON-- Overwrite files that are newer than existing files.

-OG-- Overwrite destination file if source file size is greater.

-OL-- Overwrite destination file if source file size is less (smaller).

-OS-- Skip file if exists

-R -- Resume if file exists

-RA-- Resume *all* files that exist.

Transfer oldest/newest files

-newest -- Transfer the newest file in a directory.

-oldest -- Transfer the newest file in a directory.

ie:

  • c:\(coreftp path)\corecmd.exe -s -oldest -site mysite -d /remote_path/*.txt -p c:\localdir\ To download the oldest file in a directory. 

Compress and upload a file (Pro only):

  • drive:\(coreftp path)\coreftp.exe -O -ZIP -AUTONAME -u c:\IBM -site siteprofile -p /public_html/uploadpath/

-AUTONAME automatically names the file. Without this parameter the file will be named IBM.zip. Use -FN to specify destination.

Local compressing(zip) and decompressing(unzip) of files (Pro only):

-ZIPONLY <optional password> <Full path desination .zip> <Full path to source files>

ie:

  • coreftp.exe -ZIPONLY password c:\path\zippedfiles.zip c:\source\*.dat


-UNZIPONLY <optional password> <Full path source .zip> <Full path desination path>

ie:

  • coreftp.exe -UNZIPONLY password c:\path\source.zip c:\desination\


Override Ascii or Binary mode for transfers:

-A -- for ascii mode

-B-- for binary mode

By default, files are always transfered in binary mode unless specifed in the ascii extensions.

Silent Mode:

Use:-s -- Will transfer file with no GUI except for a transfer dialog and overwrite prompts. In the Pro version, there is no splash screen.

Delete Source File:

Use:-delsrc-- Delete the source file (upload or download) after successful transfer.

-del filespec-- Delete files. Command used alone (no transfers) with site profile or FTP url.

-purge filespec days-- Purge files of x days old. Common used alone with site profile or FTP url.

-rmdir -- To delete a directory

Rename Destination File:

Use:-FN newfilename-- use this to transfer a file to a different filename, used in conjunction with the -p parameter.

ie: 

  • coreftp.exe -u c:\myfile -p /path/ -FN newfilename
Miscellaneous Commands:


Use:-hide-- Hide the transfer window when not in silent mode.

-show-- Show the transfer window when not in silent mode.

-dir -- Dump directory information into a file. -dir <remote_directory> <output_filename>

-dira -- Dump directory information into a file forcing ascii format.

-refresh-- Refresh file sizes in gui if size has changed.

-zip password-- Compress files into destination file with optional password.

-unzip password-- Decompress files into desination directory with optional password.

-ignorenotfound-- Returns no error if a file is not found.

-flash-- Run from an external drive.

-chmod <value>-- Change permissions on a file.

-pasv-- Set transfer channel to passive mode.

-active-- Set transfer channel to active mode.

-nocase -- Ignore case

-strip -- strips spaces from filename, or specify optional string to strip

-ignorenotfound-- Ignores no files found problems on some servers

-recurse-- Recurse subdirectories.

-remotemove /oldpath/*.ext /newpath/ -- Move files remotely from one folder/directory to another

-exit-- Exit override

ie: 

  • "c:\program files\coreftp\coreftp.exe" -O -zip -u c:\IBM\*.dat -site siteprofile -p /remote_directory/ -fn myfiles.zip


Script Options:

-scbl <filename>-- Run script from file before logon

-scal <filename>-- Run script from file after logon

-scbt <filename>-- Run script from file before transfer

-scat <filename>-- Run script from file after transfer

Install Options:

Use:-lic <filename> -- To install license file automatically

-Import <filename>-- To import a file of sites (previously exported from within the site manager)

-ImportGlobal <filename>-- To import a file of sites to be usable by all users (and not editable or deletable).


Output File:

The output file can be used for batch or external program monitoring of transfers.

Use-output \path\filename-- Will output a log file for each transfer.

Format: transfer type (1-download, 2-upload,...), transfer status(1-success, 2-failure, 30-file not found), start time, end time, dest, source.

Environment Variables:

In batch files, the environment variable will contain a transfer's return value in %ERRORLEVEL% (0 or 1, or 30 for file not found). This value is only reliable within a .bat file.

By at No comments:
Labels: , , , , , , , , , , ,

Friday, March 30, 2018

Exporting data from SQL Server to CSV file in ASP.Net using C#

11:18 PM Asp.net , c sharp 0 Comments
Exporting data from SQL Server to CSV file in ASP.Net using C#

Exporting data from SQL Server to CSV file in ASP.Net using C# 

Namespaces

You will need to import the following namespaces.


  • using System.Data;
  • using System.Configuration;
  • using System.Data.SqlClient;

Code For Export DataTable to CSV


protected void ExportCSV(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers"))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    //Build the CSV file data as a Comma separated string.
                    string csv = string.Empty;
                    foreach (DataColumn column in dt.Columns)
                    {
                        //Add the Header row for CSV file.
                        csv += column.ColumnName + ',';
                    }
                    //Add new line.
                    csv += "\r\n";
                    foreach (DataRow row in dt.Rows)
                    {
                        foreach (DataColumn column in dt.Columns)
                        {
                            //Add the Data rows.
                            csv += row[column.ColumnName].ToString().Replace(",", ";") + ',';
                        }
                        //Add new line.
                        csv += "\r\n";
                    }
                    //Download the CSV file.
                    Response.Clear();
                    Response.Buffer = true;
                    Response.AddHeader("content-disposition", "attachment;filename=SqlExport.csv");
                    Response.Charset = "";
                    Response.ContentType = "application/text";
                    Response.Output.Write(csv);
                    Response.Flush();
                    Response.End();
                }
            }
        }
    }
}
By at No comments:
Labels: , , , , , ,

Saturday, November 18, 2017

How to create a Linked Server to an Azure SQL database

5:16 AM Azure , Azure SQL database 1 Comments
Hi friends, today I will explain how to create and configure a linked server to retrieve data from an Azure SQL database.

Few points are describe in this article for configure link server with SSMS

  1. How to configure link server step by step in SSMS
  2. Solve some common Problem/Issue  when create link server
  3. Using the Query/Script base to create link server
How to configure link server step by step in SSMS

        To create link server via SSMS, go to Object Explorer under the Server Objects folder, right click on the Linked Servers folder and from the context menu choose the New Linked Server Command:

Fig 1.1

The New Lined Server window will be opened:

Fig 1.1

Put your Linked Server Name, then
Select data source: Microsoft OLE DB Provider for SQL Server
Then click on security tab on left side

Under the Security tab, select the Be made using this security context radio button and enter user credentials this exist on Azure server
Fig 1.2

Under the Security tab, use the same setting that we used in the previous example and press the OK button.
This will create a linked server to an Azure SQL Database(Azure Database). And when the plus(+) sign next to the table folder is pressed, the Tables folder will expand and show all tables for the selected Azure database:

Fig 1.3
To retrieve data from the SQL Azure table (e.g [dbo].[Actions]), type the following code:
SELECT Top 10 * FROM [LinkedServerName].[AzureDataBaseName].[dbo].[TableName]

Solve some common Problem/Issue  when create link server

When we create linked server we facing some common issue like Linked server is showing created but we can’t able to access the linked database. It’s given error which is mention below image:

Fig 2.1


Above mention error, Fail to retrive data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

          ADDITIONAL INFORMATION:
                         An exception occurred while executing a Transact-SQL statement or batch.  (Microsoft SQL Server, Error: 40515)

Using the Query/Script base to create link server

How to create linked server for SQL Database on Azure from local SQL Server
Step by step Azure SQL Database Linked Server
Step 1: Go to SQL Server Management Studio
Step 2: Run the below scripts. This script using a stored procedure "SP_addlinkedserver".
EXEC sp_addlinkedserver
@server='Azure', -–Provide Linked Server Name
@srvproduct='',    
@provider='sqlncli',
@datasrc='AzureDataServerName', -–provide Azure SQl Server name
@location='',
@provstr='',
@catalog='AzureDatabaseName' -–Provide azure database name




Step 3: Run the below script to provide the SQL Server login for the above created linked server.


Exec sp_addlinkedsrvlogin 'Azure', 'FALSE', NULL, '[azure sql server login]', '[sql server password]';




Step 4: Verify the created linked server, under the Databases -> Server Objects -> Linked Servers -> Your Linked Server Name





Step 5: Access the azure SQL database table. You need to use 4 part query. [linked server name].[database name].[schema name].[table name]




SELECT Top 10 * 
FROM [linked server name].[database name].[schema name].[table name]























By



at







1 comment:
  



















Labels:
,
,
,
,
,
,

















          

        

          

        

Wednesday, November 1, 2017


          

        

























SSIS FOR BEGINNERS











2:12 AM


basic about ssis
,
Janak Goti

0
 Comments



























SSIS FOR BEGINNERS















Hi friend's, today we are going to understand about SSIS. i collect many information about SSIS blob and try to understand how to use SSIS. i hope it is useful for you.












Now first try to understand about SSIS.








    

  1. What IS SSIS?
    2. 

  2. Why SSIS?
    3. 

  3. SSIS Variable.
    4. 

  4. SSIS Connection Manager.
    5. 

  5. SSIS ToolBox.
    6. 

  6. Data Flow Task.
    7. 





1. What Is SSIS?




    

  • It is a powerfull ETL tool, which is used for building enrterprise level data transformation, and dara integration solutions.
    • 





2. Why SSIS?






    

  • Extract, Transform, and Load (ETL) data from SQL Server to a file  anf also from file to SQL.
    • 

  • Sending and email.
    • 

  • Download the File from FTP.
    • 

  • Rename,Delete,Move File from Defined Path.
    • 

  • It allows you to join tables from different database (SQL, Oracle, ect..) and from potentially different servers.
    • 




Please refer Install SQL Server Data Tools article to understand the steps involved in installing the SQL Server Data Tools or Business Intelligence Development Studio(BIDS).









We will see following contents in this article.




    

  • Variables
    • 

  • Connection Manager 
    • 

  • SSIS Toolbox
    • 

      

    • Contaniner
      • 

    • Tasks
      • 

    

  • Data Flow Task
    • 




Variable:







Variables store values that a SSIS package and its containers, task, and event handlers can use at runtime.







System variables: Defined by Integration Services








    

  • SSIS provides a set of system variables that store information about the running package and its objects. These variables can be used in expressions and property expressions to customize packages, containers, tasks, and event handlers.
    • 






You can go Through this link for more details about System variable (https://docs.microsoft.com/en-us/sql/integration-services/system-variables)























User-Defined variables: Defined by Package Developers













How to create user define variable that shown in below image.












Fig.1









How to set expression for variable that shown in below image.











Fig.2







Connection Manager:







SSIS provides different types of connection managers that enable packages to connect to a variety of data source and servers:






    

  • There are built-in connection managers that setup installs when toy install Integration services.
    • 

  • There are connection managers that are available for download from the Microsoft website.
    • 

  • You can create your own custom connection manager if the existing connection managers do not meet your needs.
    • 






Let's see, step by step how we can add Connection Manager.








    

  1. Solution Explorer > Connection Managers > New Connection Manager.
    2. 




You can see the list of connection managers for different type of connections.













Fig.3














    

  •  Add connection manager.
    • 








Fig.4










    

  • After adding your connection. you can see the all connection here.
    • 








Fig.5











SSIS Toolbox:







Steps: Menu bar > SSIS > Select SSIS Toolbox.







Now,you can see SSIS toolbox on the left side.







SSIS toolbox have list of tasks and containers that you can perform.







List of Containers:




    

  • For each Loop Container:  Runs a control flow repeatedly by using an enumerator.
    • 

  • For Loop Container:  Runs a control flow repeatedly by testing a condition.
    • 

  • Sequence Container: Groups tasks and containers into control flows that are subsets of the package control flow.
    • 




List of Tasks:




    

  • Data Flow Task
    • 

      

    • The tasks runs data flows to extract data, apply column level transformations, and load data.
      • 

    

  • Data Preparation Task
    • 

      

    • These tasks do the following processes: copy file and directories, download files and data, run web methods, apply oprations to XML documents, and profile data for cleansing. 
      • 

    

  • Work Flow Tasks
    • 

      

    • The tasks that communications with other processes to run package, run program or batch file, send and receive messages between packages,send e-mail messages, read Windows Management Instrumentation(WMI) data, and watch for WMI events.
      • 

    

  • SQL Sever Tasks
    • 

      

    • The tasks that access, copy, insert, delete, and modify SQL Server objects and data.
      • 

    

  • Scripting Tasks
    • 

      

    • The tasks that extend package functionality by using scripts.
      • 

    

  • Analysis Services Tasks
    • 

      

    • The tasks crreate, modify, delete and process Analysis Services objects.
      • 

    

  • Maintenance Tasks
    • 

      

    • The tasks that perform administrative functions such as backing up and shrinking SQL Server databases, rebuilding and reorganizing indexes, and running SQL Server Agent jobs.
      • 

    




you can add task/container by drag the task/Container from SSIS toolbox to design area.







Data Flow Task:







Drag the Data Flow task from SSIS Toolbox to design area and double click on it.



you are now in Data flow tab.



now you can see that SSIS Toolbox has different components.







Type:








    

  • Source: From where you want your data.
    • 

  • Destination: It is where you want to move your data.
    • 

  • Transformation: It is operation that perform ETL(Extract, Transform, Load)
    • 




So, that's all guys i hope These will you to understand basic SSIS.


































By



at







No comments:
  



















Labels:
,
,

















          

        

          

        

Friday, October 6, 2017


          

        

























DataTable To Xml Genrate











11:16 PM


c#
,
data table to XML convert

0
 Comments









DataTable To Xml Genrate






















Data Table to XML Genrate



 /// 










      /// Write out XML to a DataTable to a file in a controlled manner.

      /// Use a XmlWriterSettings to control the output formatting.

      /// 

      public static string DataTableToXML(DataTable table)

      {

         XmlWriterSettings settings = new XmlWriterSettings();

         settings.CheckCharacters = true;

         settings.CloseOutput = true;

         settings.ConformanceLevel = ConformanceLevel.Document;

         settings.Encoding = Encoding.UTF8;

         settings.Indent = false;

         settings.NewLineHandling = NewLineHandling.Replace;

         settings.NewLineOnAttributes = true;

         settings.OmitXmlDeclaration = true;



         using (var sw = new StringWriter())

         {

            using (var writer = XmlWriter.Create(sw))

            {

               // Build Xml with xw.

               writer.WriteStartDocument(true);

               WriteTable(writer, table);

               writer.WriteEndDocument();

            }

            return sw.ToString();

         }

      }



      private static void WriteTable(XmlWriter writer, DataTable table)

      {

         //     

         writer.WriteStartElement("DocumentElement");



         foreach (DataRow row in table.Rows)

         {

            // 



            writer.WriteStartElement(table.TableName);



            foreach (DataColumn column in table.Columns)

            {

               writer.WriteStartElement(column.ColumnName);

               writer.WriteValue(row[column].ToString());

               writer.WriteEndElement();

            }

            // 

            writer.WriteEndElement();



         }



         // 

             writer.WriteEndElement();

      }


















By



at







No comments:
  



















Labels:
,
,
,
,

















          

        

          

        

Tuesday, September 26, 2017


          

        

























Generic Code For XML Generate c#











6:27 AM


Generic Code For XML Generate in .net
,
Generic Code For XML Generate in c#

0
 Comments



























XML Helper for convert class object/List to xml and xml to class object/List conversion









Hi friend's, today we are going to understand ubout XMl Helper for convert class object/List to xml and xml to class object/List convertion.









let's start with example,









Create class for EmployeeDetail













public class EmployeeDetails



    {



        [XmlAttribute]



        public int EmployeeID { get; set; }



        [XmlAttribute]



        public string EmployeeName { get; set; }



        [XmlAttribute]



        public string EmailID { get; set; }



        [XmlAttribute]



        public List lstEmployeeDetails { get; set; }



    }



Now, we have one class XMLHandler that contains two methods that make our work easy and clean.




    

  • Method ConvertToXML for Generic code to convert Xml to class object or list.
    • 


  • Method Convert for Generic code to convert class object or list to Xml.
    • 






public static class XMLHandler



    {



        public static T Convert(string inputString)



        {



              XmlSerializer serializer = new XmlSerializer(typeof(T), string.Empty);



MemoryStream memStream = new MemoryStream(Encoding.UTF8.GetBytes(inputString));



              T resultingMessage = (T)(serializer.Deserialize(memStream));



              return resultingMessage;



        }



        public static string ConvertToXML(T obj)



        {



            if (obj == null)



            {



                return string.Empty;



            }



            XmlSerializer serializer = new XmlSerializer(typeof(T));



            StringWriter sww = new StringWriter();



            using (XmlWriter writer = XmlWriter.Create(sww))



            {



                serializer.Serialize(writer, obj);



                return sww.ToString().Replace("", "").Replace(" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"", ""); // Your XML



            }



        }



    }













To convert List to xml:



Now we will create instance for EmployeeDetails



EmployeeDetails objEmployeeDetails = new EmployeeDetails();



string xml = XMLHandler>.ConvertToXML(objEmployeeDetails. lstEmployeeDetails);



It will return xml as below Figure,











So, that's all guys i hope these will help you to create xml using object/class.

please share this blog if this helpful you. https://dotnetupdatedtechnology.blogspot.com/




























By



at







No comments:
  



















Labels:
,
,
,
,

















          

        

          

        

Thursday, September 21, 2017


          

        

























Improve SQL Query Performance











3:37 AM


Improve SQL Query Performance
,
Janak Goti

0
 Comments









Improve SQL Query Performance




















Improve SQL Query Performance



Hi friend's, today we are going to understand step to improve t-sql query perfomance step by step mention below.








    

  1. Use EXISTS instead of IN to check existence of data.
    2. 

  2. Avoid * in SELECT statement. Give the name of columns which you require.
    3. 

  3. Choose appropriate Data Type. E.g. To store strings use varchar in place of text data type. Use text data type, whenever you need to store large data (more than 8000 characters).
    4. 

  4. Avoid nchar and nvarchar if possible since both the data types takes just double memory as char and varchar.
    5. 

  5. Avoid NULL in fixed-length field. In case of requirement of NULL, use variable-length (varchar) field that takes less space for NULL.
    6. 

  6. Avoid Having Clause. Having clause is required if you further wish to filter the result of an aggregations.
    7. 

  7. Create Clustered and Non-Clustered Indexes.
    8. 

  8. Keep clustered index small since the fields used in clustered index may also used in non-clustered index.
    9. 

  9. Most selective columns should be placed leftmost in the key of a non-clustered index.
    10. 

  10. Drop unused Indexes.
    11. 

  11. Better to create indexes on columns that have integer values instead of characters. Integer values use less overhead than character values.
    12. 

  12. Use joins instead of sub-queries.
    13. 

  13. Use WHERE expressions to limit the size of result tables that are created with joins.
    14. 

  14. Use TABLOCKX while inserting into a table and TABLOCK while merging.
    15. 

  15. Use WITH (NOLOCK) while querying the data from any table.
    16. 

  16. Use SET NOCOUNT ON and use TRY- CATCH to avoid deadlock condition.
    17. 

  17. Avoid Cursors since cursor are very slow in performance.
    18. 

  18. Use Table variable in place of Temp table. Use of Temp tables required interaction with TempDb database which is a time taking task.
    19. 

  19. Use UNION ALL in place of UNION if possible.
    20. 

  20. Use Schema name before SQL objects name.
    21. 

  21. Use Stored Procedure for frequently used data and more complex queries.
    22. 

  22. Keep transaction as small as possible since transaction lock the processing tables data and may results into deadlocks.
    23. 

  23. Avoid prefix “sp_” with user defined stored procedure name because SQL server first search the user defined procedure in the master database and after that in the current session database.
    24. 

  24. Avoid use of Non-correlated Scalar Sub Query. Use this query as a separate query instead of part of the main query and store the output in a variable, which can be referred to in the main query or later part of the batch.
    25. 

  25. Avoid Multi-statement Table Valued Functions (TVFs). Multi-statement TVFs are more costly than inline TVFs.
    26. 




So, that's all guys i hope these will help you to easy understand this post.






















By



at







No comments:
  



















Labels:
,

















        

      




























{
  "@context": "http://schema.org",
  "@type": "Organization",
  "url": "http://c-sharpnets.blogspot.com/",
  "logo": "http://c-sharpnets.blogspot.com/images/logo.png"
}