using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;  
using System.Diagnostics;
using System.IO;
namespace LanguageFilter
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
Class1 oClass = new Class1();
try
{
oClass.Run();
}
catch (Exception e) { Console.WriteLine(e.Message); }
Console.WriteLine("");
}
  #region Run
  public void Run()
  {
    // Test class to translate English to Spanish and back.  I used language
    // identifiers that matched Google (en,es,etc...).  They purposely
    // don't match the .NET framework identifiers.  
    // Remember to adjust the Folder and ConStr variables accordingly.
    LanguageFilter.DataBaseFilter oFilter = new LanguageFilter.DataBaseFilter();
    SqlConnection oConn = new SqlConnection();
    DataSet oDataSet = new DataSet();
    SqlDataAdapter oAdapter;
    string Folder = @"C:\temp\";
    string FileName = Folder + "sample.txt";
    string ConStr="Data Source=(local);User ID=myuser;Password=mypassword;Initial Catalog=mydatabase";
    string Cols = " ID,Description,Message,ProductCode,LastUpdateTime,Amount,Quantity";
    string Sql=" Select top 3 " + Cols + " from sample";
    FileStream fs = new FileStream(Folder + "sample.txt",FileMode.Create,FileAccess.Write);
    StreamWriter w = new StreamWriter(fs);
    try
    {  
      oConn.ConnectionString = ConStr;
      oConn.Open();  
      oAdapter = new SqlDataAdapter(Sql,oConn);
      oAdapter.FillSchema(oDataSet,SchemaType.Mapped,"Sample");
      oAdapter.Fill(oDataSet,"Sample");
      oConn.Close();
      DataTable oTable = oDataSet.Tables[0];
  // Show the contents of the original DataSet diffgram
      oDataSet.WriteXml(Folder + "ds1.xml",XmlWriteMode.DiffGram); 
  // Process table through the filter assuming all columns
  // are in English to start from the database.
      oFilter.FilterDataSet(ref oTable,"en","en","es"); 
  // Show the current state of the DataSet diffgram for review
      oDataSet.WriteXml(Folder + "ds2.xml",XmlWriteMode.DiffGram); 
  // Write the contents of the table out to a file for review.
      this.ShowTable(ref w,"First Run After Translation",FileName,oTable);
 /* this.UpdateTable(ref oTable,"select " + Cols + " from Sample",ConStr); */
  // Run the translated DataSet back through the filter to "undo" the 
  // translation to Spanish".
      oFilter.FilterDataSet(ref oTable,"en","es","en"); 
  // Show the current state of the DataSet diffgram for review
      oDataSet.WriteXml(Folder + "ds3.xml",XmlWriteMode.DiffGram);
  // Append the translation "undo" to our existing file for review
      this.ShowTable(ref w,"Second Run After Translation",FileName,oTable);
    }
    catch (Exception) { throw; }
    finally { if (oConn.State == ConnectionState.Open) { oConn.Close(); } w.Close(); }
   }
   #endregion
 #region Show Table
  public void ShowTable(ref StreamWriter w, string FileDescription, string FileName, DataTable oTable)
  {
    w.WriteLine("**************************************");
    w.WriteLine(FileDescription);
    w.WriteLine("**************************************");
    try
    {
      foreach(DataRow oRow in oTable.Rows)
      {  
        if (oRow.RowState == DataRowState.Modified)
        {
          w.WriteLine("");
          w.WriteLine("Row Modified");
          w.WriteLine("");
        }
        foreach(DataColumn oColumn in oTable.Columns)
        {
          w.Write("   " + oColumn.ColumnName + ": ");
          w.WriteLine(oRow[oColumn.Ordinal].ToString());
          w.WriteLine("");
          if (oRow.GetColumnError(oColumn).Length > 0)
           {
             w.WriteLine("     " + oRow.GetColumnError(oColumn));
           }
           w.WriteLine(""); 
        }
      }
   }
   catch (Exception) { throw; } 
 }
 #endregion
 #region Update Table
 public void UpdateTable(ref DataTable oTable, string Query, string ConStr)
 {
    SqlConnection oConn = new SqlConnection();
    SqlDataAdapter oAdapter;
    SqlCommandBuilder oBuilder;
    try
    {
       oConn.ConnectionString = ConStr;
       oConn.Open();  
       oAdapter = new SqlDataAdapter(Query,oConn);
       oBuilder = new SqlCommandBuilder(oAdapter); 
       oAdapter.Update(oTable);
       oConn.Close();
    }
    catch (Exception) { throw; }
    finally { if (oConn.State == ConnectionState.Open) { oConn.Close(); }}
 }
 #endregion
 }
}
 

 
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;  
using System.Diagnostics;
using System.Web;
using System.IO;
using System.Net;
using System.Text;
namespace LanguageFilter
{
 public class DataBaseFilter
 {
public DataBaseFilter() { }
 #region Filter DataSet
  public void FilterDataSet(ref DataTable oTable, string StoredLanguage, string SourceLanguage, string TargetLanguage)
  {
    string Value="";
    string Translated="";
    string CurLanguage="";
    try
    { 
      foreach(DataRow oRow in oTable.Rows)
      { 
        foreach(DataColumn oColumn in oTable.Columns)
        {
          Translated = "";
          Value = oRow[oColumn.Ordinal].ToString();
          CurLanguage = oRow.GetColumnError(oColumn).Length;
          // Assume column has not been translated away from StoredLanguage
          // and set a flag indicating the language by default.
          if (CurLanguage.Length < 1) {oRow.SetColumnError(oColumn,StoredLanguage); }
          // If already translated to correct language, skip column.
          if (CurLanguage==TargetLanguage) {continue; }
          // No need to run expensive translation process if no value exists.
          if (Value.Length < 1) { continue; }
          // Let .NET Framework handle numeric, datetime, and currency
          // formatting via the Locale or CultureInfo classes outside
          // of this filter.  Also skip other non-string oriented data types.
          if(oColumn.DataType.ToString() != "System.String") {continue; }
          Translated = this.Translate(Value,SourceLanguage,TargetLanguage);     
          // If Translated is not populated, it will be empty.  We only
          // want to populate the column with a translated result and
          // set the current language used in this column.
          // This is where you may need to add more restrictions to ensure
          // that the translated value doesn't violate column constraints
          // other than size which is handled here.
          if ((Translated.Length >0) && (Translated.Length <= oColumn.MaxLength))
          {
            oRow[oColumn.Ordinal] = Translated;
            oRow.SetColumnError(oColumn,TargetLanguage);
          }
        }
      }
    }
catch (Exception) { throw; }
  }
 #endregion
 #region Translate 
  private string Translate(string Value, string SourceLanguage,string TargetLanguage)
  {
    // This method is used for demo purposes only.  It posts form values to
    // Google's translation form and parses the return values.  In a production
    // environment, you'd want a local piece of software to handle the language
    // translation for you.
       string StartText = "<textarea name=q rows=5 cols=45 wrap=PHYSICAL>";
       string StartText2 = "<textarea name=q rows=5 cols=45 wrap=PHYSICAL readonly>";
        string EndText = "</textarea>";
       string Url="http://translate.google.com/translate_t";
        string Form="";
        string H="";
        string Translated="";
try
{
  Value = Value.Trim();
   Form= "langpair=" + SourceLanguage + "|" + TargetLanguage;
  Form += "&text;=" + Value.Replace(" ","+");    
   WebRequest oRequest = WebRequest.Create(Url);
  oRequest.ContentType = "application/x-www-form-urlencoded";
  oRequest.Method = "POST";
  StreamWriter oWriter = new StreamWriter(oRequest.GetRequestStream());
   oWriter.Write(Form);
  oWriter.Close();
  WebResponse oResponse = oRequest.GetResponse();
  H = new StreamReader(oResponse.GetResponseStream(),Encoding.ASCII).ReadToEnd();
  oResponse.Close();
  int Start = H.IndexOf(StartText);
  if (Start <1) { Start = H.IndexOf(StartText2); }
  int End = H.IndexOf(EndText);
          if ((Start>0) && (End>0))
          {
            H = H.Substring(Start,End - Start);
           H = H.Replace(StartText,"");
            Translated = H.Replace(StartText2,"");
           Console.WriteLine("Translated: " + Translated);
          }
}
catch (Exception) { throw; }
    return Translated.Trim();
  }
 #endregion
 }
}