MySql to Tally Import Ledgers

Hi Team, I wrote following C# code to import Ledgers from MySql Data Base. If Group doesn't exist it has to prompt and on saying yes group has to be created in Tally then Ledgers should be imported. Database connection is established properly but group checking is not done properly always giving message Parent des not exit do you want to create. if i say yes then it gives message Failed to create group. Please help me to over come my problem. Following is the code snippet.

using System;
using System.Data;
using System.IO;
using System.Net;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
public partial class TallyIntegrationForm : Form
{
// MySQL connection string
private string connStr = "server=localhost;user=root;password=root;";
// UI components
private Label lblSelectDatabase;
private ComboBox cmbDatabases;
private Button btnSendToTally;
private TextBox txtResponse;
private Button btnTestConnection;
public TallyIntegrationForm()
{
InitializeComponent();
PopulateDatabases();
this.btnSendToTally.Click += new EventHandler(this.btnSendToTally_Click);
this.btnTestConnection.Click += new EventHandler(this.btnTestConnection_Click);
}
private void InitializeComponent()
{
this.lblSelectDatabase = new Label();
this.cmbDatabases = new ComboBox();
this.btnSendToTally = new Button();
this.txtResponse = new TextBox();
this.btnTestConnection = new Button();
//
// lblSelectDatabase
//
this.lblSelectDatabase.AutoSize = true;
this.lblSelectDatabase.Location = new System.Drawing.Point(12, 20);
this.lblSelectDatabase.Name = "lblSelectDatabase";
this.lblSelectDatabase.Size = new System.Drawing.Size(90, 13);
this.lblSelectDatabase.Text = "Select Database:";
//
// cmbDatabases
//
this.cmbDatabases.DropDownStyle = ComboBoxStyle.DropDownList;
this.cmbDatabases.FormattingEnabled = true;
this.cmbDatabases.Location = new System.Drawing.Point(108, 17);
this.cmbDatabases.Name = "cmbDatabases";
this.cmbDatabases.Size = new System.Drawing.Size(164, 21);
//
// btnSendToTally
//
this.btnSendToTally.Location = new System.Drawing.Point(15, 226);
this.btnSendToTally.Name = "btnSendToTally";
this.btnSendToTally.Size = new System.Drawing.Size(100, 23);
this.btnSendToTally.Text = "Send to Tally";
this.btnSendToTally.UseVisualStyleBackColor = true;
//
// txtResponse
//
this.txtResponse.Location = new System.Drawing.Point(15, 44);
this.txtResponse.Multiline = true;
this.txtResponse.Name = "txtResponse";
this.txtResponse.Size = new System.Drawing.Size(257, 176);
this.txtResponse.ScrollBars = ScrollBars.Vertical;
//
// btnTestConnection
//
this.btnTestConnection.Location = new System.Drawing.Point(172, 226);
this.btnTestConnection.Name = "btnTestConnection";
this.btnTestConnection.Size = new System.Drawing.Size(100, 23);
this.btnTestConnection.Text = "Test Connection";
this.btnTestConnection.UseVisualStyleBackColor = true;
//
// TallyIntegrationForm
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(284, 261);
this.Controls.Add(this.btnTestConnection);
this.Controls.Add(this.txtResponse);
this.Controls.Add(this.btnSendToTally);
this.Controls.Add(this.cmbDatabases);
this.Controls.Add(this.lblSelectDatabase);
this.Name = "TallyIntegrationForm";
this.Text = "Tally Integration";
this.ResumeLayout(false);
this.PerformLayout();
}
private void PopulateDatabases()
{
try
{
using (MySqlConnection conn = new MySqlConnection(connStr))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("SHOW DATABASES;", conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
cmbDatabases.Items.Add(reader[0].ToString());
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("An error occurred: " + ex.Message);
}
}
private void btnTestConnection_Click(object sender, EventArgs e)
{
if (cmbDatabases.SelectedItem == null)
{
MessageBox.Show("Please select a database from the dropdown list.");
return;
}
string selectedDatabase = cmbDatabases.SelectedItem.ToString();
string connectionString = $"{connStr}database={selectedDatabase};";
try
{
string database = cmbDatabases.SelectedItem.ToString();
// string connectionString = $"{connStr}database={database};";
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
MessageBox.Show("Connection successful!");
}
}
catch (Exception ex)
{
MessageBox.Show("Connection failed: " + ex.Message);
}
}
private void btnSendToTally_Click(object sender, EventArgs e)
{
// Fetch data from MySQL and construct the XML request
string xmlRequest = ConstructXMLRequestFromMySQL();
// Send the XML request to Tally
string response = SendRequestToTally(xmlRequest);
// Display the response in a TextBox
txtResponse.Text = response;
}
private string ConstructXMLRequestFromMySQL()
{
StringBuilder xmlContent = new StringBuilder();
// Check if a database is selected
if (cmbDatabases.SelectedItem == null)
{
MessageBox.Show("Please select a database from the dropdown list.");
return string.Empty; // Return an empty string to indicate failure
}
// Get the selected database
string selectedDatabase = cmbDatabases.SelectedItem.ToString();
string connectionString = $"{connStr}database={selectedDatabase};";
// Start the XML envelope
xmlContent.Append("<ENVELOPE>");
xmlContent.Append("<HEADER><TALLYREQUEST>Import Data</TALLYREQUEST></HEADER>");
xmlContent.Append("<BODY><IMPORTDATA><REQUESTDESC><REPORTNAME>All Masters</REPORTNAME></REQUESTDESC>");
xmlContent.Append("<REQUESTDATA>");
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
string query = "SELECT customerName, contactFirstName, customerNumber FROM customers";
MySqlCommand cmd = new MySqlCommand(query, conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string parentName = reader["contactFirstName"].ToString();
// Check if the parent exists in Tally
if (!ParentExistsInTally(parentName))
{
// Ask the user if they want to create the parent
DialogResult dialogResult = MessageBox.Show($"Parent '{parentName}' does not exist. Do you want to create it?", "Parent Not Exists", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
// Create the parent group in Tally
string createGroupResponse = CreateGroupInTally(parentName);
// Check the response to see if the group was created successfully
if (!createGroupResponse.Contains("<CREATED>1</CREATED>"))
{
MessageBox.Show("Failed to create the parent group in Tally.");
return string.Empty; // Return an empty string to indicate failure
}
}
else
{
return string.Empty; // Return an empty string to indicate failure
}
}
// Construct the XML for each ledger entry
xmlContent.Append("<TALLYMESSAGE xmlns:UDF=\"TallyUDF\">");
xmlContent.Append("<LEDGER NAME=\"" + reader["customerName"].ToString() + "\" ACTION=\"Create\">");
xmlContent.Append("<PARENT>" + parentName + "</PARENT>");
xmlContent.Append("<OPENINGBALANCE>" + reader["customerNumber"].ToString() + "</OPENINGBALANCE>");
xmlContent.Append("</LEDGER>");
xmlContent.Append("</TALLYMESSAGE>");
}
}
}
// Close the XML envelope
xmlContent.Append("</REQUESTDATA></IMPORTDATA></BODY></ENVELOPE>");
return xmlContent.ToString();
}
private string SendRequestToTally(string xmlContent)
{
// Define the URL of the Tally server
string tallyUrl = "http://localhost:9000";
// Create a web request using the tally URL
WebRequest tallyRequest = WebRequest.Create(tallyUrl);
tallyRequest.Method = "POST";
tallyRequest.ContentType = "text/xml";
byte[] byteArray = Encoding.UTF8.GetBytes(xmlContent);
// Set the ContentLength property of the WebRequest
tallyRequest.ContentLength = byteArray.Length;
// Get the request stream
using (Stream dataStream = tallyRequest.GetRequestStream())
{
// Write the data to the request stream
dataStream.Write(byteArray, 0, byteArray.Length);
}
// Get the response
WebResponse tallyResponse = tallyRequest.GetResponse();
// Display the status
Console.WriteLine(((HttpWebResponse)tallyResponse).StatusDescription);
// Get the stream containing content returned by the server
using (Stream dataStream = tallyResponse.GetResponseStream())
{
// Open the stream using a StreamReader for easy access
StreamReader reader = new StreamReader(dataStream);
// Read the content
string responseFromServer = reader.ReadToEnd();
// Display the content
Console.WriteLine(responseFromServer);
return responseFromServer;
}
}
private bool ParentExistsInTally(string parentName)
{
// XML request to check if a group exists in Tally
string xmlCheckGroup = $"<ENVELOPE>" +
$"<HEADER><VERSION>1</VERSION>" +
$"<TALLYREQUEST>Export</TALLYREQUEST>" +
$"<TYPE>Collection</TYPE><ID>Group</ID>" +
$"</HEADER>" +
$"<BODY><DESC><STATICVARIABLES><SVEXPORTFORMAT>$$SysName:XML</SVEXPORTFORMAT><SV CURRENT COMPANY>Demo C Sharp</SV CURRENT COMPANY></STATICVARIABLES></DESC></BODY></ENVELOPE>";
// Send request to Tally and get the response
string response = SendRequestToTally(xmlCheckGroup);
// Parse the response to check if the group exists
return response.Contains($"<NAME>{parentName}</NAME>");
}
private string CreateGroupInTally(string groupName)
{
// XML request to create a group in Tally
string xmlCreateGroup = $"<ENVELOPE><HEADER><VERSION>1</VERSION><TALLYREQUEST>Import</TALLYREQUEST><TYPE>Data</TYPE><ID>All Masters</ID></HEADER><BODY><DESC><STATICVARIABLES><SVCURRENTCOMPANY>Demo C Sharp</SVCURRENTCOMPANY></STATICVARIABLES></DESC><DATA><TALLYMESSAGE xmlns:UDF=\"TallyUDF\"><GROUP NAME=\"{groupName}\" ACTION=\"Create\"><PARENT>Sundry Debtors</PARENT></GROUP></TALLYMESSAGE></DATA></BODY></ENVELOPE>";
// Send request to Tally to create the group
return SendRequestToTally(xmlCreateGroup);
}
}
// The main entry point for the application
static class Program
{
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new TallyIntegrationForm());
}
}

 

Use TallyConnector

Code:

TallyService tally = new();

Ledger ledg = new Ledger("TestLedger Name","New Group");
ledg.OpeningBal = 500;
// You can configure other values refer this - https://docs.accountingcompanion.com/Tallyconnector/api/TallyConnector.Core.Models.Masters.Ledger.html
try{
await tally.GetGroupAsync(ledg.Group,new MasterRequestOptions(){LookupField=MasterLookupField.Name,FetchList=new (){"Name"}});
}
catch (ObjectDoesNotExist ex){
var grpresp = await tally.PostGroupAsync(new Group(ledg.Group,"Sundry Debtors"));
if(grpresp.Status == RespStatus.Failure){
   MessageBox.Show("Failed to create the parent group in Tally.");
}
var resp = await tally.PostLedgerAsync(ledg);
}

Required NameSpaces

Code:

using TallyConnector.Core.Models;
using TallyConnector.Core.Models.Masters;
using TallyConnector.Core.Exceptions;
using TallyConnector.Services;