public void TestSql()
{
AdoNetHelper helper = null;
DbConnection conn = null;
DbConnection conn2 = null;
using (helper = AdoNetHelper.ThreadInstance("data source=10.14.0.*;user id=sa;password=*;Initial Catalog=*;", DbProviderType.SqlServer))
{
//创建获取由OdbcHelper管理的连接。
conn = helper.OpenNewConnection();
Assert.AreEqual(conn.State, ConnectionState.Open);
//创建一个新连接
conn2 = helper.OpenNewConnection();
Assert.AreEqual(conn2.State, ConnectionState.Open);
Assert.AreNotEqual(conn, conn2);
//测试ThreadInstance
using (AdoNetHelper helper2 = AdoNetHelper.ThreadInstance("data source=10.14.0.*;user id=sa;password=*;Initial Catalog=*;", DbProviderType.SqlServer))
{
Assert.AreEqual(helper, helper2);
}
//测试trans
conn = helper.OpenNewConnection();
DbTransaction trans = helper.BeginNewTrans(conn);
string sql = "**";
DbCommand cmd = new SqlCommand(sql, conn as SqlConnection);
cmd.Transaction = trans;
//用户权限设置有问题。
cmd.ExecuteReader();
cmd.Transaction.Commit();
}
//测试连接能否正常关闭。
Assert.AreEqual(conn.State, ConnectionState.Closed);
Assert.AreEqual(conn2.State, ConnectionState.Closed);
}
/// <summary>
/// Serializes the contents of the object into a byte[] and stores in the SerializationInfo block.
/// </summary>
/// <param name="obj">The object to serialize.</param>
/// <param name="info">See .Net serialization.</param>
/// <param name="context">See .Net serialization.</param>
public void GetObjectData(object obj, SerializationInfo info, StreamingContext context)
{
byte[] data;
if (obj.GetType() == typeof(DataSet) || obj is IModifiedTypedDataSet)
{
data = AdoNetHelper.SerializeDataSet(obj as DataSet);
}
else if (obj.GetType() == typeof(DataTable))
{
data = AdoNetHelper.SerializeDataTable(obj as DataTable);
}
else if (obj is DataSet)
{
data = AdoNetHelper.SerializeTypedDataSet(obj as DataSet);
}
else if (obj is DataTable)
{
data = AdoNetHelper.SerializeTypedDataTable(obj as DataTable);
}
else
{
throw new InvalidOperationException("Not a supported Ado.Net object");
}
info.AddValue("_", data);
}
/// <summary>
/// Creates a new instance of the object being deserialized (since no constructors are called)
/// then deserializes data into it.
/// </summary>
/// <param name="obj">The DataSet or DataTable to serialize.</param>
/// <param name="info">See .Net serialization.</param>
/// <param name="context">See .Net serialization.</param>
/// <param name="selector">The selector which selected this surrogate.</param>
/// <returns></returns>
public object SetObjectData(object obj, SerializationInfo info, StreamingContext context, ISurrogateSelector selector)
{
obj = createNewInstance(obj);
byte[] data = (byte[])info.GetValue("_", typeof(byte[]));
if (obj.GetType() == typeof(DataSet) || obj is IModifiedTypedDataSet)
{
return(AdoNetHelper.DeserializeDataSet(obj as DataSet, data));
}
else if (obj.GetType() == typeof(DataTable))
{
return(AdoNetHelper.DeserializeDataTable(obj as DataTable, data));
}
else if (obj is DataSet)
{
return(AdoNetHelper.DeserializeTypedDataSet(obj as DataSet, data));
}
else if (obj is DataTable)
{
return(AdoNetHelper.DeserializeTypedDataTable(obj as DataTable, data));
}
else
{
throw new InvalidOperationException("Not a supported Ado.Net object");
}
}
internal virtual AdoNetHelper CreateAdoNetHelper()
{
// TODO: 实例化相应的具体类。
AdoNetHelper target = AdoNetHelper.ThreadInstance("", DbProviderType.SqlServer);
return(target);
}
public void TestOracle()
{
AdoNetHelper helper = null;
DbConnection conn = null;
DbConnection conn2 = null;
using (helper = AdoNetHelper.ThreadInstance("data source=10.14.0.*:1521/orcl;user id=*;password=*;", DbProviderType.Oracle_ManagedODP))
{
//创建获取由OdbcHelper管理的连接。
conn = helper.OpenNewConnection();
Assert.AreEqual(conn.State, ConnectionState.Open);
//创建一个新连接
conn2 = helper.OpenNewConnection();
Assert.AreEqual(conn2.State, ConnectionState.Open);
Assert.AreNotEqual(conn, conn2);
//测试ThreadInstance
using (AdoNetHelper helper2 = AdoNetHelper.ThreadInstance("data source=10.14.0.*:1521/orcl;user id=*;password=*;", DbProviderType.Oracle_ManagedODP))
{
Assert.AreEqual(helper, helper2);
}
//测试trans
conn = helper.OpenNewConnection();
DbTransaction trans = helper.BeginNewTrans(conn);
string sql = "update **";
helper.ExecuteNonQuery(trans, CommandType.Text, sql);
trans.Commit();
}
//测试连接能否正常关闭。
Assert.AreEqual(conn.State, ConnectionState.Closed);
Assert.AreEqual(conn2.State, ConnectionState.Closed);
}
public void OpenNewConnectionTest()
{
AdoNetHelper target = CreateAdoNetHelper(); // TODO: 初始化为适当的值
DbConnection expected = null; // TODO: 初始化为适当的值
DbConnection actual;
actual = target.OpenNewConnection();
Assert.AreEqual(expected, actual);
Assert.Inconclusive("验证此测试方法的正确性。");
}
/// <summary>
/// 构造函数
/// </summary>
public AutoRunProgram()
{
if (int.TryParse(ConfigHelper.AppSettings("AutoUpdateAttStatusInterval"), out _interval))
{
cnnStr = "Server=.;Database=PinkOneMngSys;user id=admin;password=Pinkone_2019;";
#if DEBUG
cnnStr = "Server=212.64.49.60;Database=PinkOneMngSys;user id=admin;password=Pinkone_2019;";
#endif
adoHelper = new AdoNetHelper(cnnStr);
//更新数据
updateTask = new Task(Update_Handle);
updateTask.Start();
}
}
/// <summary>
/// Report Initialization method that is triggered when report begin processed.
/// </summary>
/// <param name="reportOptions">The ReportViewer options.</param>
public void OnInitReportOptions(ReportViewerOptions reportOptions)
{
//throw new NotImplementedException();
// Add RDL Server and database credentials here
// Report Server
string user = LibraryHelper.AppSettings <string>("Report.RDLUser");
string password = LibraryHelper.AppSettings <string>("Report.RDLPassword");
reportOptions.ReportModel.ReportServerCredential = new System.Net.NetworkCredential(user, password);
// Data Source
string connectionName = "Chinook";
string[] userPassword = AdoNetHelper.GetUserPassword(connectionName);
DataSourceCredentials dataSourceCredentials = new DataSourceCredentials(connectionName, userPassword[0], userPassword[1]);
reportOptions.ReportModel.DataSourceCredentials.Add(dataSourceCredentials);
}
//Serialization
public CDataSetFastSerialize(SerializationInfo info, StreamingContext context)
{
byte[] data = (byte[])info.GetValue("DATASET_DATA", typeof(byte[]));
m_dataset = AdoNetHelper.DeserializeDataSet(data);
}
public static bool ExportRDLC(ZOperationResult operationResult, ref string exportPath, string exportFormat,
string rdlcDirectory, string reportDirectory, string reportName, IDictionary <string, string> reportParameters)
{
try
{
string reportPath = Path.Combine(rdlcDirectory,
MultiTenantHelper.Tenant.Name,
reportDirectory,
reportName + ".rdl");
ReportWriter reportWriter = new ReportWriter(reportPath);
reportWriter.ReportProcessingMode = ProcessingMode.Local;
// Parameter(s)
IList <ReportParameter> reportWriterParameters = new List <ReportParameter>(); // Syncfusion.Reports.EJ.ReportParameter
foreach (KeyValuePair <string, string> pair in reportParameters)
{
ReportParameter reportParameter = new ReportParameter();
reportParameter.Name = pair.Key;
reportParameter.Values.Add(pair.Value);
reportWriterParameters.Add(reportParameter);
}
//reportWriter.SetParameters(reportWriterParameters); // ???
// Data Source(s) & Data Set(s)
global::Syncfusion.RDL.DOM.ReportDefinition reportDefinition = DeSerializeReport(reportPath);
IList <string> dataSetNames = reportWriter.GetDataSetNames();
reportWriter.DataSources.Clear();
foreach (var dataSetName in dataSetNames)
{
var dataSet = reportDefinition.DataSets.Where(x => x.Name == dataSetName);
var connectionName = MultiTenantHelper.GetConnectionName(dataSet.First().Query.DataSourceName);
var sql = dataSet.First().Query.CommandText;
DbConnection connection = null;
try
{
DbProviderFactory provider;
provider = AdoNetHelper.GetProvider(connectionName);
connection = provider.CreateConnection();
connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
connection.Open();
DbCommand command;
DbDataReader reader;
command = provider.CreateCommand();
command.Connection = connection;
command.CommandTimeout = 600;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = sql;
command.Parameters.Clear();
//foreach (var reportParameter in reportWriter.GetParameters()) // ???
foreach (var reportParameter in reportWriterParameters)
{
var defaultValue = reportParameter.Values.First() == null ? null : reportParameter.Values.First();
DbParameter parameter = command.CreateParameter();
//parameter.DbType = GetDbType(reportParameter.DataType); // ???
parameter.DbType = DbType.String;
parameter.ParameterName = "@" + reportParameter.Name;
if (defaultValue == null)
{
parameter.Value = DBNull.Value;
}
else
{
parameter.Value = defaultValue;
}
command.Parameters.Add(parameter);
//command.AddParameterWithValue("@" + reportParameter.Name, defaultValue);
}
reader = command.ExecuteReader();
DataTable table = new DataTable();
table.Load(reader);
// System.Data.EnumerableRowCollection<System.Data.DataRow>
// https://msdn.microsoft.com/en-us/library/system.data.enumerablerowcollection%28v=vs.110%29.aspx
// System.Data.DataRow
// https://msdn.microsoft.com/en-us/library/system.data.datarow%28v=vs.110%29.aspx
reportWriter.DataSources.Add(new ReportDataSource {
Name = dataSetName, Value = table.AsEnumerable()
});
}
catch (Exception exception)
{
operationResult.ParseException(exception);
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
if (operationResult.Ok)
{
// Export
string fileExtension = ".pdf";
WriterFormat writerFormat = WriterFormat.PDF;
switch (exportFormat.ToLower())
{
case "doc":
fileExtension = ".doc";
writerFormat = WriterFormat.Word;
break;
case "xls":
fileExtension = ".xls";
writerFormat = WriterFormat.Excel;
break;
}
//PageSettings pageSettings = new PageSettings();
//pageSettings.LeftMargin = 0.39; // 10mm
//pageSettings.RightMargin = 0.39; // 10mm
//pageSettings.TopMargin = 0.39; // 10mm
//pageSettings.BottomMargin = 0.39; // 10mm
//pageSettings.PageWidth = 8.27; // 210mm
//pageSettings.PageHeight = 11.69; // 297mm
//reportWriter.PageSettings = pageSettings;
exportPath = exportPath.Trim() + fileExtension;
FileStream fileStream = new FileStream(exportPath, FileMode.Create);
reportWriter.Save(fileStream, writerFormat);
fileStream.Close();
operationResult.StatusMessage = exportPath;
}
}
catch (Exception exception)
{
operationResult.ParseException(exception);
}
return(operationResult.Ok);
}
public static bool ExportRDL(ZOperationResult operationResult, ref string exportPath, string exportFormat,
string reportDirectory, string reportName, IDictionary <string, string> reportParameters)
{
try
{
ReportWriter reportWriter = new ReportWriter();
reportWriter.ReportPath = "/" + MultiTenantHelper.Tenant.Name +
(String.IsNullOrEmpty(reportDirectory) ? "" : "/" + reportDirectory) +
"/" + reportName;
reportWriter.ReportProcessingMode = ProcessingMode.Remote;
// Report Credentials
string user = ConfigurationHelper.AppSettings <string>("Report.RDL.User");
string password = ConfigurationHelper.AppSettings <string>("Report.RDL.Password");
reportWriter.ReportServerCredential = new System.Net.NetworkCredential(user, password);
reportWriter.ReportServerUrl = ConfigurationHelper.AppSettings <string>("Report.RDL.Url");
// Data Source Credentials
string connection = reportDirectory;
string[] userPassword = AdoNetHelper.GetUserPassword(connection);
DataSourceCredentials dataSourceCredentials = new DataSourceCredentials(connection, userPassword[0], userPassword[1]);
reportWriter.SetDataSourceCredentials(new DataSourceCredentials[] { dataSourceCredentials });
// Parameter(s)
IList <ReportParameter> reportWriterParameters = new List <ReportParameter>(); // Syncfusion.Reports.EJ.ReportParameter
foreach (KeyValuePair <string, string> pair in reportParameters)
{
ReportParameter reportParameter = new ReportParameter();
reportParameter.Name = pair.Key;
reportParameter.Values.Add(pair.Value);
reportWriterParameters.Add(reportParameter);
}
reportWriter.SetParameters(reportWriterParameters);
// Data Source(s) & DataSet(s)
// Export
string fileExtension = ".pdf";
WriterFormat writerFormat = WriterFormat.PDF;
switch (exportFormat.ToLower())
{
case "doc":
fileExtension = ".doc";
writerFormat = WriterFormat.Word;
break;
case "xls":
fileExtension = ".xls";
writerFormat = WriterFormat.Excel;
break;
}
exportPath = exportPath.Trim() + fileExtension;
FileStream fileStream = new FileStream(exportPath, FileMode.Create);
reportWriter.Save(fileStream, writerFormat);
fileStream.Close();
operationResult.StatusMessage = exportPath;
}
catch (Exception exception)
{
operationResult.ParseException(exception);
}
return(operationResult.Ok);
}
/// <summary> Constructor for a new instance of the SobekCM_Items_In_Title class </summary>
/// <param name="info"> Serialization information object, from which the data of this class is read </param>
/// <param name="context"> Context of the deserialization request </param>
/// <remarks> This constructor is used to build this object during a deserialization request, such as when
/// an object of this type is pulled from the remote caching server </remarks>
public SobekCM_Items_In_Title(SerializationInfo info, StreamingContext context)
{
innerData = new DataTable();
AdoNetHelper.DeserializeDataTable(innerData, (byte[])info.GetValue("data", typeof(byte[])));
}
////////////////////////////////////////////////////////////////////////////
public CMemoryDb(System.Runtime.Serialization.SerializationInfo info, System.Runtime.Serialization.StreamingContext contexte)
{
AdoNetHelper.DeserializeDataSet(this, (byte[])info.GetValue("DATASET_DATA", typeof(byte[])));
m_dicPrivateNomTableToType = info.GetValue("DIC_TABLE_TO_TYPE", typeof(Dictionary <string, Type>)) as Dictionary <string, Type>;
m_dicPrivateTypeToNomTable = info.GetValue("DIC_TYPE_TO_TABLE", typeof(Dictionary <Type, string>)) as Dictionary <Type, string>;
}
/// <summary>
///
/// </summary>
/// <param name="info"></param>
/// <param name="context"></param>
protected RecordSetHelper(SerializationInfo info, StreamingContext context)
{
AdoNetHelper.DeserializeDataSet(this, (byte[])info.GetValue("_", typeof(byte[])));
_opened = info.GetBoolean("Opened");
_dbType = (DatabaseType)info.GetUInt16("DatabaseType");
}
/// <summary>
///
/// </summary>
/// <param name="info"></param>
/// <param name="context"></param>
public override void GetObjectData(SerializationInfo info, StreamingContext context)
{
info.AddValue("_", AdoNetHelper.SerializeDataSet(this));
info.AddValue("Opened", _opened);
info.AddValue("DatabaseType", _dbType);
}
protected WrappedDataSet(SerializationInfo info, StreamingContext context)
{
dataSet = AdoNetHelper.DeserializeDataSet((byte[])info.GetValue("_", typeof(byte[])));
}
/// <summary> Method is used to customize the serialization of this object and returns all the
/// pertinent serialized data, including the main dataset serialized to binary (rather than XML)</summary>
/// <param name="info"> Serialization information object, to which the data of this class is added </param>
/// <param name="context"> Context of the serialization request </param>
public void GetObjectData(SerializationInfo info, StreamingContext context)
{
info.AddValue("data", AdoNetHelper.SerializeDataTable(innerData));
}
protected FastSerializableDataSet(SerializationInfo info, StreamingContext context)
{
AdoNetHelper.DeserializeDataSet(this, (byte[])info.GetValue("_", typeof(byte[])));
}
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
void ISerializable.GetObjectData(SerializationInfo info, StreamingContext context)
{
info.AddValue("DATASET_DATA", AdoNetHelper.SerializeDataSet(this));
info.AddValue("DIC_TABLE_TO_TYPE", m_dicPrivateNomTableToType);
info.AddValue("DIC_TYPE_TO_TABLE", m_dicPrivateTypeToNomTable);
}
public void GetObjectData(SerializationInfo info, StreamingContext context)
{
byte[] data = AdoNetHelper.SerializeDataSet(m_dataset);
info.AddValue("DATASET_DATA", data);
}
/// <summary>
/// Report loaded method that is triggered when report and sub report begins to be loaded.
/// </summary>
/// <param name="reportOptions">The ReportViewer options.</param>
public void OnReportLoaded(ReportViewerOptions reportOptions)
{
ReportDefinition reportDefinition = DeSerializeReport(reportOptions.ReportModel.ReportPath);
ReportParameters reportParameters = reportDefinition.ReportParameters;
IList <string> dataSetNames = ReportHelper.GetDataSetNames();
reportOptions.ReportModel.DataSources.Clear();
// User Parameters
if (HttpContext.Current.Items.Contains("userParameters"))
{
reportOptions.ReportModel.Parameters =
new JavaScriptSerializer().Deserialize <List <global::Syncfusion.Reports.EJ.ReportParameter> >(HttpContext.Current.Items["userParameters"].ToString());
HttpContext.Current.Items.Remove("userParameters");
}
foreach (var dataSetName in dataSetNames)
{
var dataSet = reportDefinition.DataSets.Where(x => x.Name == dataSetName);
var connectionName = dataSet.First().Query.DataSourceName;
var sql = dataSet.First().Query.CommandText;
DbConnection connection = null;
try
{
DbProviderFactory provider;
provider = AdoNetHelper.GetProvider(connectionName);
connection = provider.CreateConnection();
connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
connection.Open();
DbCommand command;
DbDataReader reader;
command = provider.CreateCommand();
command.Connection = connection;
command.CommandTimeout = 600;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = sql;
command.Parameters.Clear();
if (reportOptions.ReportModel.Parameters != null && reportOptions.ReportModel.Parameters.Count() > 0)
{
int index = 0;
foreach (var userParameter in reportOptions.ReportModel.Parameters)
{
DbParameter parameter = command.CreateParameter();
parameter.DbType = GetDbType(reportParameters[index].DataType);
parameter.ParameterName = "@" + userParameter.Name;
parameter.Value = userParameter.Values.First();
command.Parameters.Add(parameter);
//command.AddParameterWithValue("@" + userParameter.Name, userParameter.Values.First()); // DbType.String
index++;
}
}
else
{
foreach (var reportParameter in reportParameters)
{
var defaultValue = reportParameter.DefaultValue == null ? null : reportParameter.DefaultValue.Values.First();
DbParameter parameter = command.CreateParameter();
parameter.DbType = GetDbType(reportParameter.DataType);
parameter.ParameterName = "@" + reportParameter.Name;
parameter.Value = defaultValue;
command.Parameters.Add(parameter);
//command.AddParameterWithValue("@" + reportParameter.Name, defaultValue);
}
}
reader = command.ExecuteReader();
DataTable table = new DataTable();
table.Load(reader);
// System.Data.EnumerableRowCollection<System.Data.DataRow>
// https://msdn.microsoft.com/en-us/library/system.data.enumerablerowcollection%28v=vs.110%29.aspx
// System.Data.DataRow
// https://msdn.microsoft.com/en-us/library/system.data.datarow%28v=vs.110%29.aspx
reportOptions.ReportModel.DataSources.Add(new ReportDataSource {
Name = dataSetName, Value = table.AsEnumerable()
});
}
catch // (Exception exception)
{
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
/*
* XmlDocument xml = new XmlDocument();
* string rdlcFileName = reportOptions.ReportModel.ReportPath;
* xml.Load(rdlcFileName);
* xml.InnerXml = xml.InnerXml.Replace("xmlns=", "xmlns:ns=");
*
* string connectionName =
* xml.DocumentElement.SelectSingleNode("/Report/DataSets/DataSet[@Name='Query']/Query/DataSourceName").InnerText;
* if (!String.IsNullOrEmpty(connectionName))
* {
* string sql =
* xml.DocumentElement.SelectSingleNode("/Report/DataSets/DataSet[@Name='Query']/Query/CommandText").InnerText;
* if (!String.IsNullOrEmpty(sql))
* {
* DbConnection connection = null;
*
* try
* {
* DbProviderFactory provider;
*
* provider = AdoNetHelper.GetProvider(connectionName);
* connection = provider.CreateConnection();
* connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
* connection.Open();
*
* DbCommand command;
* DbDataReader reader;
*
* command = provider.CreateCommand();
* command.Connection = connection;
* command.CommandTimeout = 600;
* command.CommandType = System.Data.CommandType.Text;
* command.CommandText = sql;
*
* reader = command.ExecuteReader();
*
* DataTable table = new DataTable();
* table.Load(reader);
*
* // System.Data.EnumerableRowCollection<System.Data.DataRow>
* // https://msdn.microsoft.com/en-us/library/system.data.enumerablerowcollection%28v=vs.110%29.aspx
* // System.Data.DataRow
* // https://msdn.microsoft.com/en-us/library/system.data.datarow%28v=vs.110%29.aspx
*
* reportOptions.ReportModel.DataSources.Clear();
* reportOptions.ReportModel.DataSources.Add(new ReportDataSource { Name = "Query", Value = table.AsEnumerable() });
* }
* catch // (Exception exception)
* {
* }
* finally
* {
* if (connection != null)
* {
* connection.Close();
* }
* }
* }
* }
*/
}
public bool ExportAlbumByArtistXLSX(ZOperationResult operationResult, string templateDirectory, string fileDirectory,
out string filePath)
{
string template = "AlbumByArtist";
string templatePath = LibraryHelper.AddDirectorySeparator(templateDirectory) + template + ".xlsx";
string file = template + "." + String.Format("{0:yyyyMMdd.HHmmssfff}", DateTime.Now) + ".xlsx";
filePath = Path.Combine(fileDirectory, file);
DbConnection connection = null;
ExcelEngine excelEngine = null;
try
{
DbProviderFactory provider;
string connectionName = "Chinook";
provider = AdoNetHelper.GetProvider(connectionName);
connection = provider.CreateConnection();
connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
connection.Open();
DbCommand command;
DbDataReader reader;
//DbParameter parameter;
command = provider.CreateCommand();
command.Connection = connection;
command.CommandTimeout = 600;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = @"
SELECT
Artist.ArtistId
,Artist.Name ArtistName
,Album.AlbumId
,Album.Title AlbumTitle
FROM
Artist
LEFT JOIN Album ON
Album.ArtistId = Artist.ArtistId
ORDER BY
Artist.Name
,Album.Title
";
//parameter = command.CreateParameter();
//parameter.DbType = DbType.DateTime;
//parameter.ParameterName = "@Data";
//parameter.Value = viewModel.XDateTime;
//command.Parameters.Add(parameter);
File.Copy(templatePath, filePath);
excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open(filePath);
//workbook.Version = ExcelVersion.Excel2013;
IWorksheet worksheet;
// Sintético
worksheet = workbook.Worksheets[0];
worksheet.Range[1, 4].Value2 = String.Format("{0:dd/MM/yyyy}", DateTime.Today);
// Analítico
worksheet = workbook.Worksheets[2];
reader = command.ExecuteReader();
int row = 2, column;
while (reader.Read())
{
column = 1;
worksheet.Range[row, column++].Value2 = reader.ToInt32("ArtistId");
worksheet.Range[row, column++].Value2 = reader.ToString("ArtistName");
worksheet.Range[row, column++].Value2 = reader.ToInt32("AlbumId");
worksheet.Range[row, column++].Value2 = reader.ToString("AlbumTitle");
row++;
}
reader.Close();
worksheet.AutoAlign(1, 4);
workbook.Save();
workbook.Close();
}
catch (Exception exception)
{
operationResult.ParseException(exception);
}
finally
{
if (excelEngine != null)
{
excelEngine.Dispose();
}
if (connection != null)
{
connection.Close();
}
}
return(operationResult.Ok);
}
public bool ExportGenreXLSX(ZOperationResult operationResult, string fileDirectory,
out string filePath)
{
string template = "Genre";
string file = template + "." + String.Format("{0:yyyyMMdd.HHmmssfff}", DateTime.Now) + ".xlsx";
filePath = Path.Combine(fileDirectory, file);
DbConnection connection = null;
ExcelEngine excelEngine = null;
try
{
DbProviderFactory provider;
string connectionName = "Chinook";
provider = AdoNetHelper.GetProvider(connectionName);
connection = provider.CreateConnection();
connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
connection.Open();
DbCommand command;
DbDataReader reader;
//DbParameter parameter;
command = provider.CreateCommand();
command.Connection = connection;
command.CommandTimeout = 600;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = @"
SELECT
GenreId
,Name
FROM
Genre
ORDER BY
Genre.Name
";
//parameter = command.CreateParameter();
//parameter.DbType = DbType.DateTime;
//parameter.ParameterName = "@Data";
//parameter.Value = viewModel.XDateTime;
//command.Parameters.Add(parameter);
excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
//workbook.Version = ExcelVersion.Excel2013;
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range[1, 1].Text = "GenreId";
worksheet.Range[1, 2].Text = "Name";
reader = command.ExecuteReader();
int row = 2;
while (reader.Read())
{
int column = 1;
worksheet.Range[row, column++].Value2 = reader.ToInt32("GenreId");
worksheet.Range[row, column++].Value2 = reader.ToString("Name");
row++;
}
reader.Close();
worksheet.AutoAlign(1, 2);
workbook.SaveAs(filePath);
workbook.Close();
}
catch (Exception exception)
{
operationResult.ParseException(exception);
}
finally
{
if (excelEngine != null)
{
excelEngine.Dispose();
}
if (connection != null)
{
connection.Close();
}
}
return(operationResult.Ok);
}
private static void SQLServer2MongoDB()
{
Console.WriteLine("\nChinook SQL Server => MongoDB");
Console.Write("\nPress <Y> to execute... ");
ConsoleKeyInfo key = Console.ReadKey();
if (key.KeyChar != 'y' && key.KeyChar != 'Y')
{
return;
}
Console.WriteLine();
DbConnection connection = null;
ChinookMongoDB chinook = new ChinookMongoDB();
try
{
DbProviderFactory provider;
string connectionName = "Chinook";
provider = AdoNetHelper.GetProvider(connectionName);
connection = provider.CreateConnection();
connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
connection.Open();
DbCommand command;
command = provider.CreateCommand();
command.Connection = connection;
command.CommandTimeout = 600;
command.CommandType = System.Data.CommandType.Text;
// Chinook
DbCommand2MongoDB <Album>(command, chinook);
DbCommand2MongoDB <Artist>(command, chinook);
DbCommand2MongoDB <Customer>(command, chinook);
DbCommand2MongoDB <Employee>(command, chinook);
DbCommand2MongoDB <Genre>(command, chinook);
DbCommand2MongoDB <Invoice>(command, chinook);
DbCommand2MongoDB <InvoiceLine>(command, chinook);
DbCommand2MongoDB <MediaType>(command, chinook);
DbCommand2MongoDB <Playlist>(command, chinook);
DbCommand2MongoDB <PlaylistTrack>(command, chinook);
DbCommand2MongoDB <Track>(command, chinook);
//DbCommand2MongoDB<CustomerDocument>(command, chinook);
// Sequence
IUnitOfWork unitOfWork = new ChinookUnitOfWorkMongoDB();
command.CommandText = "SELECT MAX(AlbumId) FROM Album";
unitOfWork.GetRepository <Album>().SetSequence((int)command.ExecuteScalar());
command.CommandText = "SELECT MAX(ArtistId) FROM Artist";
unitOfWork.GetRepository <Artist>().SetSequence((int)command.ExecuteScalar());
command.CommandText = "SELECT MAX(CustomerId) FROM Customer";
unitOfWork.GetRepository <Customer>().SetSequence((int)command.ExecuteScalar());
command.CommandText = "SELECT MAX(EmployeeId) FROM Employee";
unitOfWork.GetRepository <Employee>().SetSequence((int)command.ExecuteScalar());
command.CommandText = "SELECT MAX(GenreId) FROM Genre";
unitOfWork.GetRepository <Genre>().SetSequence((int)command.ExecuteScalar());
command.CommandText = "SELECT MAX(InvoiceId) FROM Invoice";
unitOfWork.GetRepository <Invoice>().SetSequence((int)command.ExecuteScalar());
command.CommandText = "SELECT MAX(InvoiceLineId) FROM InvoiceLine";
unitOfWork.GetRepository <InvoiceLine>().SetSequence((int)command.ExecuteScalar());
command.CommandText = "SELECT MAX(MediaTypeId) FROM MediaType";
unitOfWork.GetRepository <MediaType>().SetSequence((int)command.ExecuteScalar());
command.CommandText = "SELECT MAX(PlaylistId) FROM Playlist";
unitOfWork.GetRepository <Playlist>().SetSequence((int)command.ExecuteScalar());
command.CommandText = "SELECT MAX(PlaylistId * 1000000 + TrackId) FROM PlaylistTrack"; // !!!
unitOfWork.GetRepository <PlaylistTrack>().SetSequence((int)command.ExecuteScalar());
command.CommandText = "SELECT MAX(TrackId) FROM Track";
unitOfWork.GetRepository <Track>().SetSequence((int)command.ExecuteScalar());
//command.CommandText = "SELECT MAX(CustomerDocumentId) FROM CustomerDocument";
//unitOfWork.GetRepository<CustomerDocument>().SetSequence((int)command.ExecuteScalar());
unitOfWork.GetRepository <CustomerDocument>().SetSequence(1);
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
private bool ExportSecurity(ZOperationResult operationResult, string templateDirectory, string fileDirectory,
out string filePath)
{
string template = "Security";
string templatePath = LibraryHelper.AddDirectorySeparator(templateDirectory) + template + ".xlsx";
string file = template + "." + String.Format("{0:yyyyMMdd.HHmmssfff}", DateTime.Now) + ".xlsx";
filePath = Path.Combine(fileDirectory, file);
DbConnection connection = null;
ExcelEngine excelEngine = null;
try
{
DbProviderFactory provider;
DbCommand command;
DbDataReader reader;
string connectionName;
System.IO.File.Copy(templatePath, filePath);
excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open(filePath);
//workbook.Version = ExcelVersion.Excel2013;
IWorksheet worksheet;
int row, columns;
// Identity ////////////////////////////////////////////////////////////////////////
connectionName = MultiTenantHelper.GetConnectionName("Identity");
provider = AdoNetHelper.GetProvider(connectionName);
connection = provider.CreateConnection();
connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
connection.Open();
//DbParameter parameter;
command = provider.CreateCommand();
command.Connection = connection;
command.CommandTimeout = 600;
command.CommandType = System.Data.CommandType.Text;
// Users
worksheet = workbook.Worksheets["Users"];
command.CommandText = @"
SELECT
UserName UserName,
Email EMail,
LockoutEnabled Lockout,
LockoutEndDateUtc LockoutEndDate
FROM
AspNetUsers
ORDER BY
UserName
";
reader = command.ExecuteReader(IsolationLevel.ReadUncommitted);
columns = reader.FieldCount;
row = 2;
while (reader.Read())
{
int column = 1;
worksheet.Range[row, column++].Value2 = reader.ToString("UserName");
worksheet.Range[row, column++].Value2 = reader.ToString("EMail");
worksheet.Range[row, column++].Value2 = reader.ToBoolean("Lockout") ? EasyLOB.Resources.PresentationResources.Yes : EasyLOB.Resources.PresentationResources.No;
worksheet.Range[row, column++].ClearDateTime(reader.ToDateTimeNullable("LockoutEndDate"));
row++;
}
reader.Close();
worksheet.AutoAlign(1, columns);
// Roles
worksheet = workbook.Worksheets["Roles"];
command.CommandText = @"
SELECT
Name RoleName
FROM
AspNetRoles
ORDER BY
RoleName
";
reader = command.ExecuteReader(IsolationLevel.ReadUncommitted);
columns = reader.FieldCount;
row = 2;
while (reader.Read())
{
int column = 1;
worksheet.Range[row, column++].Value2 = reader.ToString("RoleName");
row++;
}
reader.Close();
worksheet.AutoAlign(1, columns);
// Roles by User
worksheet = workbook.Worksheets["Roles by User"];
command.CommandText = @"
SELECT
AspNetUsers.UserName UserName,
AspNetRoles.Name RoleName
FROM
AspNetUserRoles
INNER JOIN AspNetUsers ON
AspNetUsers.Id = AspNetUserRoles.UserId
INNER JOIN AspNetRoles ON
AspNetRoles.Id = AspNetUserRoles.RoleId
ORDER BY
1,2
";
reader = command.ExecuteReader(IsolationLevel.ReadUncommitted);
columns = reader.FieldCount;
row = 2;
while (reader.Read())
{
int column = 1;
worksheet.Range[row, column++].Value2 = reader.ToString("UserName");
worksheet.Range[row, column++].Value2 = reader.ToString("RoleName");
row++;
}
reader.Close();
worksheet.AutoAlign(1, columns);
connection.Close();
// Activity ////////////////////////////////////////////////////////////////////////
connectionName = MultiTenantHelper.GetConnectionName("Activity");
provider = AdoNetHelper.GetProvider(connectionName);
connection = provider.CreateConnection();
connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
connection.Open();
//DbParameter parameter;
command = provider.CreateCommand();
command.Connection = connection;
command.CommandTimeout = 600;
command.CommandType = System.Data.CommandType.Text;
// Activities
worksheet = workbook.Worksheets["Activities"];
command.CommandText = @"
SELECT
Name ActivityName
FROM
EasyLOBActivity
ORDER BY
Name
";
reader = command.ExecuteReader(IsolationLevel.ReadUncommitted);
columns = reader.FieldCount;
row = 2;
while (reader.Read())
{
int column = 1;
worksheet.Range[row, column++].Value2 = reader.ToString("ActivityName");
row++;
}
reader.Close();
worksheet.AutoAlign(1, columns);
// Roles by Activity
worksheet = workbook.Worksheets["Roles by Activity"];
command.CommandText = @"
SELECT
EasyLOBActivity.Name ActivityName,
RoleName,
Operations
FROM
EasyLOBActivityRole
INNER JOIN EasyLOBActivity ON
EasyLOBActivity.Id = EasyLOBActivityRole.ActivityId
ORDER BY
Name
";
reader = command.ExecuteReader(IsolationLevel.ReadUncommitted);
columns = reader.FieldCount;
row = 2;
while (reader.Read())
{
int column = 1;
worksheet.Range[row, column++].Value2 = reader.ToString("ActivityName");
worksheet.Range[row, column++].Value2 = reader.ToString("RoleName");
worksheet.Range[row, column++].Value2 = reader.ToString("Operations");
row++;
}
reader.Close();
worksheet.AutoAlign(1, columns);
workbook.Save();
workbook.Close();
}
catch (Exception exception)
{
operationResult.ParseException(exception);
}
finally
{
if (excelEngine != null)
{
excelEngine.Dispose();
}
if (connection != null)
{
connection.Close();
}
}
return(operationResult.Ok);
}
//Serialization
public CDataTableFastSerialize(SerializationInfo info, StreamingContext context)
{
byte[] data = (byte[])info.GetValue("DataTable_DATA", typeof(byte[]));
m_dataTable = AdoNetHelper.DeserializeDataTable(data);
}
public override void GetObjectData(SerializationInfo info, StreamingContext context)
{
info.AddValue("_", AdoNetHelper.SerializeDataSet(this));
}
/// <summary>
/// Report loaded method that is triggered when report and sub report begins to be loaded.
/// </summary>
/// <param name="reportOptions">The ReportViewer options.</param>
public void OnReportLoaded(ReportViewerOptions reportOptions)
{
// User Parameters
if (HttpContext.Current.Items.Contains("userParameters"))
{
reportOptions.ReportModel.Parameters =
new JavaScriptSerializer().Deserialize <List <global::Syncfusion.Reports.EJ.ReportParameter> >(HttpContext.Current.Items["userParameters"].ToString());
HttpContext.Current.Items.Remove("userParameters");
}
// DataSet(s)
reportOptions.ReportModel.DataSources.Clear();
ReportDefinition reportDefinition = DeSerializeReport(reportOptions.ReportModel.ReportPath);
ReportParameters reportParameters = reportDefinition.ReportParameters;
IList <string> dataSetNames = ReportHelper.GetDataSetNames();
foreach (var dataSetName in dataSetNames)
{
var dataSet = reportDefinition.DataSets.Where(x => x.Name == dataSetName);
var connectionName = MultiTenantHelper.GetConnectionName(dataSet.First().Query.DataSourceName);
var sql = dataSet.First().Query.CommandText;
DbConnection connection = null;
try
{
DbProviderFactory provider;
provider = AdoNetHelper.GetProvider(connectionName);
connection = provider.CreateConnection();
connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
connection.Open();
DbCommand command;
DbDataReader reader;
command = provider.CreateCommand();
command.Connection = connection;
command.CommandTimeout = 600;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = sql;
command.Parameters.Clear();
if (reportOptions.ReportModel.Parameters != null && reportOptions.ReportModel.Parameters.Count() > 0)
{
int index = 0;
foreach (var userParameter in reportOptions.ReportModel.Parameters)
{
DbParameter parameter = command.CreateParameter();
parameter.DbType = GetDbType(reportParameters[index].DataType);
parameter.ParameterName = "@" + userParameter.Name;
parameter.Value = userParameter.Values.First();
command.Parameters.Add(parameter);
//command.AddParameterWithValue("@" + userParameter.Name, userParameter.Values.First()); // DbType.String
index++;
}
}
else
{
foreach (var reportParameter in reportParameters)
{
var defaultValue = reportParameter.DefaultValue == null ? null : reportParameter.DefaultValue.Values.First();
DbParameter parameter = command.CreateParameter();
parameter.DbType = GetDbType(reportParameter.DataType);
parameter.ParameterName = "@" + reportParameter.Name;
if (defaultValue == null)
{
parameter.Value = DBNull.Value;
}
else
{
parameter.Value = defaultValue;
}
command.Parameters.Add(parameter);
//command.AddParameterWithValue("@" + reportParameter.Name, defaultValue);
}
}
reader = command.ExecuteReader(IsolationLevel.ReadUncommitted);
DataTable table = new DataTable();
table.Load(reader);
// System.Data.EnumerableRowCollection<System.Data.DataRow>
// https://msdn.microsoft.com/en-us/library/system.data.enumerablerowcollection%28v=vs.110%29.aspx
// System.Data.DataRow
// https://msdn.microsoft.com/en-us/library/system.data.datarow%28v=vs.110%29.aspx
reportOptions.ReportModel.DataSources.Add(new ReportDataSource {
Name = dataSetName, Value = table.AsEnumerable()
});
}
catch // (Exception exception)
{
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
}
