Hi,
Here is the code to integrate tally with ASP.net using C#
//Note:-Condition Apply : Tally Erp 9.0 must be in running mode.
BulkCopyCS objBlk = new BulkCopyCS();
if (!IsPostBack)
{
string source = "PORT=9000;DRIVER=Tally ODBC Driver;SERVER={(local)}";
OdbcConnection con = new OdbcConnection(source);
con.Open();
DataTable dt = new DataTable();
string[] restrictions = new string[1];
dt = con.GetSchema("Tables");
for (int i = 0; i < dt.Rows.Count; i++)
{
ListBox1.Items.Add(dt.Rows[i][2].ToString());
//FETCH TALLY TABLES TO SQL : this line is commented due all tables are now in sql
#region
//DataSet objds = fetchTallyTableContent(dt.Rows[i][2].ToString());
//objBlk.CreateTable(objds.Tables[0], Convert.ToString(dt.Rows[i][2]));
//objBlk.BulkLoad(objds.Tables[0], Convert.ToString(dt.Rows[i][2]));
#endregion
}
Label1.Text = "Total No. of tables in Tally : " + Convert.ToString(dt.Rows.Count);
con.Close();
}
}
protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
{
Label2.Text = "Table Name : " + Convert.ToString(ListBox1.SelectedItem.Text);
DataSet objds = fetchTallyTableContent(Convert.ToString(ListBox1.SelectedItem.Text));
GridView2.DataSource = objds;
GridView2.DataBind();
}
public DataSet fetchTallyTableContent(string tblName)
{
string query = "SELECT * FROM Techweb.TallyUser." + tblName + " " + tblName;
string source = "PORT=9000;DRIVER=Tally ODBC Driver;SERVER={(local)}";
OdbcConnection con = new OdbcConnection(source);
OdbcCommand cmd = new OdbcCommand(query, con);
con.Open();
OdbcDataAdapter da = new OdbcDataAdapter(query, con);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
return ds;
}
==============================================================
//CLASS FILE CONTENT
public class BulkCopyCS
{
public BulkCopyCS()
{
//
// TODO: Add constructor logic here
//
}
public void CreateTable(DataTable dt, string tblName)
{
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["TestDatabaseConnectionString"].ConnectionString);
string tblStr = string.Empty;
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Columns.Count; i++)
tblStr += Convert.ToString(dt.Columns[i].ColumnName) + " VARCHAR(255),";
tblStr = tblStr.Remove(tblStr.Length - 1, 1);
tblStr = tblStr.Replace("$_", "Second");
tblStr = tblStr.Replace("'", "");
tblStr = tblStr.Replace("`", "");
tblStr = tblStr.Replace("$", "");
}
string st = "CREATE TABLE " + tblName + " (" + tblStr + " )";
SqlCommand cmd = new SqlCommand(st, con);
con.Open();
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
//MessageBox.Show(ex.Message.ToString());
}
con.Close();
}
public void BulkLoad(DataTable tbl,string tblName)
{
string conn = System.Configuration.ConfigurationManager.ConnectionStrings["TestDatabaseConnectionString"].ConnectionString;
System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(conn);
bulkCopy.DestinationTableName = tblName;
try
{
bulkCopy.WriteToServer(tbl);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
bulkCopy.Close();
}
}
}