static void Main(string[] args)
{
Program program = new Program();
string SQLStatement = "SELECT * FROM SCHOOLS";
int pageNumber = 1;
int pageSize = 1000;
CustomException serviceError;
Console.WriteLine("Connection String: ");
Console.WriteLine(connectionString);
Console.WriteLine("SQL Statement: ");
Console.WriteLine(SQLStatement);
Console.WriteLine("Calling GetDataSetData()...");
DataSetData dsd = program.GetDataSetData(SQLStatement, pageNumber, pageSize, out serviceError);
if (dsd == null || serviceError != null)
{
Console.WriteLine("There was a problem.");
Console.WriteLine(serviceError.Message);
}
else
{
Console.WriteLine("Response:");
Console.WriteLine(dsd.DataXML);
}
Console.WriteLine("Press any key to close...");
Console.ReadLine();
}
public static DataSetData FromDataSet(DataSet ds)
{
DataSetData dsd = new DataSetData();
dsd.Tables = new ObservableCollection <DataTableInfo>();
foreach (DataTable t in ds.Tables)
{
DataTableInfo tableInfo = new DataTableInfo {
TableName = t.TableName
};
dsd.Tables.Add(tableInfo);
tableInfo.Columns = new ObservableCollection <DataColumnInfo>();
foreach (DataColumn c in t.Columns)
{
DataColumnInfo col = new DataColumnInfo {
ColumnName = c.ColumnName, ColumnTitle = c.ColumnName, DataTypeName = c.DataType.FullName, MaxLength = c.MaxLength, IsKey = c.Unique, IsReadOnly = (c.Unique || c.ReadOnly), IsRequired = !c.AllowDBNull
};
if (c.DataType == typeof(System.Guid))
{
col.IsReadOnly = true;
col.DisplayIndex = -1;
}
tableInfo.Columns.Add(col);
}
}
dsd.DataXML = ds.GetXml();
return(dsd);
}
public DataSetData GetDataSetData(string SQL, int PageNumber, int PageSize, out CustomException ServiceError)
{
try
{
DataSet ds = GetDataSet(SQL, PageNumber, PageSize);
ServiceError = null;
return(DataSetData.FromDataSet(ds));
}
catch (Exception ex)
{
ServiceError = new CustomException(ex);
}
return(null);
}
public static DataSet ToDataSet(DataSetData dsd)
{
DataSet ds = new DataSet();
UTF8Encoding encoding = new UTF8Encoding();
Byte[] byteArray = encoding.GetBytes(dsd.DataXML);
MemoryStream stream = new MemoryStream(byteArray);
XmlReader reader = new XmlTextReader(stream);
ds.ReadXml(reader);
XDocument xd = XDocument.Parse(dsd.DataXML);
foreach (DataTable dt in ds.Tables)
{
var rs = from row in xd.Descendants(dt.TableName)
select row;
int i = 0;
foreach (var r in rs)
{
DataRowState state = (DataRowState)Enum.Parse(typeof(DataRowState), r.Attribute("RowState").Value);
DataRow dr = dt.Rows[i];
dr.AcceptChanges();
if (state == DataRowState.Deleted)
{
dr.Delete();
}
else if (state == DataRowState.Added)
{
dr.SetAdded();
}
else if (state == DataRowState.Modified)
{
dr.SetModified();
}
i++;
}
}
return(ds);
}
