Loading data to Sybase tables in C#

  If a sybase connection is to be established in C# then the AseConnection object can be used to accomplish it successfully.

  First the sybase reference should be added to the project solution by choosing Sybase.Data.AseClient component from the .Net Tab. This component doesn't comes free with .Net installation and it requires the shareware Sybase SQL client has to be installed. Once this is done, the below code can help in establishing a sybase connection.

using Sybase.Data.AseClient; string connection_string = "Data Source=<datasource name>;Port=<port no>; UID=<userid>; PWD=<password>;"; AseConnection oCon = new AseConnection(); oCon.ConnectionString = connection_string; oCon.Open();

Once the connection is established you can execute the queries by using a AseCommand object as below.

AseCommand cmd = new AseCommand("Select * from ...", oCon); cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 0; cmd.ExecuteNonQuery();

  Use the ExecuteScalar, ExecuteReader or ExecuteNonQuery appropriately as required. Finally close the connection by using connection.Close() command.

oCon.Close();

  Thats it and you may use them as per your requirement.


Example:
The below code takes a folder name from a form. The folder contains files each of which in turn contains thousands of insert statements that are to be inserted to a sybase tables. Once the user chooses the folder path and click the load command button the insert statements are fetched from each files and executed in batches of 5000. This is because in this case of  huge volume of rows to be inserted if the insertion is done one by one it would consume more time than the manual process and it also cannot be done in bulk, because once the operation fails it would be difficult to identify the rows that are missed. So it is convenient to carry out the insertion in terms of 5 thousands or lesser.

Form1.cs
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using Sybase.Data.AseClient; using System.IO; namespace InsertPad { public partial class Form1 : Form { private string folderPath = ""; public Form1() { InitializeComponent(); } private void openToolStripMenuItem_Click(object sender, EventArgs e) { FolderBrowserDialog folderbrowser = new FolderBrowserDialog(); if (folderbrowser.ShowDialog() == DialogResult.OK) folderPath = folderbrowser.SelectedPath; DevTemp_Inserter(); } private void DevTemp_Inserter() { if (folderPath != "") { string[] filePaths = Directory.GetFiles(textBox1.Text, "*.txt", SearchOption.AllDirectories); int tot_files = filePaths.Length, i = 0; string connection_string = "Data Source=<data source>;Port=<port no>;UID=<user id>;PWD=<password>;"; AseConnection oCon = new AseConnection(); oCon.ConnectionString = connection_string; oCon.Open(); AseCommand cmd; textBox1.AppendText(DateTime.Now.ToString("hh:mm:ss:ff tt") + " >> Started"); try { foreach (string filepath in filePaths) { if (filepath != "") { string[] filelines = File.ReadAllLines(filepath); { while (i < filelines.Length) { string qry = null; for (int j = i; j < (i + 5000); j++) { qry += filelines[i] + "\r\n"; i++; } cmd = new AseCommand(qry, oCon); cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 0; cmd.ExecuteNonQuery(); textBox1.AppendText(DateTime.Now.ToString("hh:mm:ss:ff tt") + " >> " + filepath + " >> Completed: " + ((int)(((float)i / filelines.Length) * 100) + " %")); Application.DoEvents(); } } } } } catch (Exception e) { MessageBox.Show(e.Message.ToString()); oCon.Close(); } finally { if (oCon.State == ConnectionState.Open) oCon.Close(); textBox1.AppendText(DateTime.Now.ToString("hh:mm:ss:ff tt") + " >> Completed"); } } } } }

Form1.Designer.cs
namespace InsertPad { partial class Form1 { /// <summary> /// Required designer variable. /// </summary> private System.ComponentModel.IContainer components = null; /// <summary> /// Clean up any resources being used. /// </summary> /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param> protected override void Dispose(bool disposing) { if (disposing && (components != null)) { components.Dispose(); } base.Dispose(disposing); } #region Windows Form Designer generated code /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.textBox1 = new System.Windows.Forms.TextBox(); this.menuStrip1 = new System.Windows.Forms.MenuStrip(); this.fileToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem(); this.newToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem(); this.openToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem(); this.saveToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem(); this.saveAsToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem(); this.toolStripSeparator1 = new System.Windows.Forms.ToolStripSeparator(); this.pageSetupToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem(); this.printToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem(); this.toolStripSeparator2 = new System.Windows.Forms.ToolStripSeparator(); this.exitToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem(); this.editToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem(); this.formatToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem(); this.viewToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem(); this.helpToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem(); this.viewHelpToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem(); this.toolStripSeparator3 = new System.Windows.Forms.ToolStripSeparator(); this.aboutToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem(); this.menuStrip1.SuspendLayout(); this.SuspendLayout(); // // textBox1 // this.textBox1.Dock = System.Windows.Forms.DockStyle.Fill; this.textBox1.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0))); this.textBox1.Location = new System.Drawing.Point(0, 24); this.textBox1.Multiline = true; this.textBox1.Name = "textBox1"; this.textBox1.Size = new System.Drawing.Size(587, 353); this.textBox1.TabIndex = 0; // // menuStrip1 // this.menuStrip1.Items.AddRange(new System.Windows.Forms.ToolStripItem[] { this.fileToolStripMenuItem, this.editToolStripMenuItem, this.formatToolStripMenuItem, this.viewToolStripMenuItem, this.helpToolStripMenuItem}); this.menuStrip1.Location = new System.Drawing.Point(0, 0); this.menuStrip1.Name = "menuStrip1"; this.menuStrip1.Size = new System.Drawing.Size(587, 24); this.menuStrip1.TabIndex = 1; this.menuStrip1.Text = "menuStrip1"; // // fileToolStripMenuItem // this.fileToolStripMenuItem.DropDownItems.AddRange(new System.Windows.Forms.ToolStripItem[] { this.newToolStripMenuItem, this.openToolStripMenuItem, this.saveToolStripMenuItem, this.saveAsToolStripMenuItem, this.toolStripSeparator1, this.pageSetupToolStripMenuItem, this.printToolStripMenuItem, this.toolStripSeparator2, this.exitToolStripMenuItem}); this.fileToolStripMenuItem.Name = "fileToolStripMenuItem"; this.fileToolStripMenuItem.Size = new System.Drawing.Size(37, 20); this.fileToolStripMenuItem.Text = "&File"; // // newToolStripMenuItem // this.newToolStripMenuItem.Name = "newToolStripMenuItem"; this.newToolStripMenuItem.Size = new System.Drawing.Size(139, 22); this.newToolStripMenuItem.Text = "New"; // // openToolStripMenuItem // this.openToolStripMenuItem.Name = "openToolStripMenuItem"; this.openToolStripMenuItem.Size = new System.Drawing.Size(152, 22); this.openToolStripMenuItem.Text = "Open.."; this.openToolStripMenuItem.Click += new System.EventHandler(this.openToolStripMenuItem_Click); // // saveToolStripMenuItem // this.saveToolStripMenuItem.Name = "saveToolStripMenuItem"; this.saveToolStripMenuItem.Size = new System.Drawing.Size(139, 22); this.saveToolStripMenuItem.Text = "Save"; // // saveAsToolStripMenuItem // this.saveAsToolStripMenuItem.Name = "saveAsToolStripMenuItem"; this.saveAsToolStripMenuItem.Size = new System.Drawing.Size(139, 22); this.saveAsToolStripMenuItem.Text = "Save As.."; // // toolStripSeparator1 // this.toolStripSeparator1.Name = "toolStripSeparator1"; this.toolStripSeparator1.Size = new System.Drawing.Size(136, 6); // // pageSetupToolStripMenuItem // this.pageSetupToolStripMenuItem.Name = "pageSetupToolStripMenuItem"; this.pageSetupToolStripMenuItem.Size = new System.Drawing.Size(139, 22); this.pageSetupToolStripMenuItem.Text = "Page Setup.."; // // printToolStripMenuItem // this.printToolStripMenuItem.Name = "printToolStripMenuItem"; this.printToolStripMenuItem.Size = new System.Drawing.Size(139, 22); this.printToolStripMenuItem.Text = "Print.."; // // toolStripSeparator2 // this.toolStripSeparator2.Name = "toolStripSeparator2"; this.toolStripSeparator2.Size = new System.Drawing.Size(136, 6); // // exitToolStripMenuItem // this.exitToolStripMenuItem.Name = "exitToolStripMenuItem"; this.exitToolStripMenuItem.Size = new System.Drawing.Size(139, 22); this.exitToolStripMenuItem.Text = "Exit"; // // editToolStripMenuItem // this.editToolStripMenuItem.Name = "editToolStripMenuItem"; this.editToolStripMenuItem.Size = new System.Drawing.Size(39, 20); this.editToolStripMenuItem.Text = "&Edit"; // // formatToolStripMenuItem // this.formatToolStripMenuItem.Name = "formatToolStripMenuItem"; this.formatToolStripMenuItem.Size = new System.Drawing.Size(57, 20); this.formatToolStripMenuItem.Text = "F&ormat"; // // viewToolStripMenuItem // this.viewToolStripMenuItem.Name = "viewToolStripMenuItem"; this.viewToolStripMenuItem.Size = new System.Drawing.Size(44, 20); this.viewToolStripMenuItem.Text = "&View"; // // helpToolStripMenuItem // this.helpToolStripMenuItem.DropDownItems.AddRange(new System.Windows.Forms.ToolStripItem[] { this.viewHelpToolStripMenuItem, this.toolStripSeparator3, this.aboutToolStripMenuItem}); this.helpToolStripMenuItem.Name = "helpToolStripMenuItem"; this.helpToolStripMenuItem.Size = new System.Drawing.Size(44, 20); this.helpToolStripMenuItem.Text = "&Help"; // // viewHelpToolStripMenuItem // this.viewHelpToolStripMenuItem.Name = "viewHelpToolStripMenuItem"; this.viewHelpToolStripMenuItem.Size = new System.Drawing.Size(154, 22); this.viewHelpToolStripMenuItem.Text = "View Help"; // // toolStripSeparator3 // this.toolStripSeparator3.Name = "toolStripSeparator3"; this.toolStripSeparator3.Size = new System.Drawing.Size(151, 6); // // aboutToolStripMenuItem // this.aboutToolStripMenuItem.Name = "aboutToolStripMenuItem"; this.aboutToolStripMenuItem.Size = new System.Drawing.Size(154, 22); this.aboutToolStripMenuItem.Text = "About notepad"; // // Form1 // this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F); this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font; this.ClientSize = new System.Drawing.Size(587, 377); this.Controls.Add(this.textBox1); this.Controls.Add(this.menuStrip1); this.MainMenuStrip = this.menuStrip1; this.Name = "Form1"; this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen; this.Text = "Untitled - Notepad"; this.menuStrip1.ResumeLayout(false); this.menuStrip1.PerformLayout(); this.ResumeLayout(false); this.PerformLayout(); } #endregion private System.Windows.Forms.TextBox textBox1; private System.Windows.Forms.MenuStrip menuStrip1; private System.Windows.Forms.ToolStripMenuItem fileToolStripMenuItem; private System.Windows.Forms.ToolStripMenuItem newToolStripMenuItem; private System.Windows.Forms.ToolStripMenuItem openToolStripMenuItem; private System.Windows.Forms.ToolStripMenuItem saveToolStripMenuItem; private System.Windows.Forms.ToolStripMenuItem saveAsToolStripMenuItem; private System.Windows.Forms.ToolStripSeparator toolStripSeparator1; private System.Windows.Forms.ToolStripMenuItem pageSetupToolStripMenuItem; private System.Windows.Forms.ToolStripMenuItem printToolStripMenuItem; private System.Windows.Forms.ToolStripSeparator toolStripSeparator2; private System.Windows.Forms.ToolStripMenuItem exitToolStripMenuItem; private System.Windows.Forms.ToolStripMenuItem editToolStripMenuItem; private System.Windows.Forms.ToolStripMenuItem formatToolStripMenuItem; private System.Windows.Forms.ToolStripMenuItem viewToolStripMenuItem; private System.Windows.Forms.ToolStripMenuItem helpToolStripMenuItem; private System.Windows.Forms.ToolStripMenuItem viewHelpToolStripMenuItem; private System.Windows.Forms.ToolStripSeparator toolStripSeparator3; private System.Windows.Forms.ToolStripMenuItem aboutToolStripMenuItem; } }


Make modifications to the above and use try catch block and test it for your connection string parameters. So basically this is just a simple guideline and cannot be used as such. In addition to this multi-threading can also be used if your server allows upload from multiple connections. In that case each thread opens a separate sql connections and executes rows insertions.

This example might sound to be a simple solution for the real programmers around the world. But some fools in a reputed MNC that works for a leading US company couldn't figure out this simple automation and opted to perform this tedious operations for months by torturing the innocent new joiners. While this is nothing but a simple sybase connection example.

If you have any queries and suggestion please leave your comments and more working references will be shared if necessary.

Note:
The example given here resembles a notepad when generated as a form and if this means something else to you, then it could be only you. Only you in the whole world can understand the reason why this app resembles a notepad's look and you will also know that it is me. I am sharing this code in public with a hope that one day you might come across this and realize that how much I cared for you. I had even more better automation to save you from those manual tasks. I hated to watch you work for those fools. Darling, I am more than what I did there. I thought that one day I will own you  and take you home as my princess. But I failed and lost you. I cannot take this separation from you and I will soon end my life, but not without proving myself to the world. I just wanted you to understand that I cared about you so much and I will always love you. Where ever you are be happy, because as you believe it is one way to be wise and also beautiful.