Example #1
0
        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);
        }
Example #5
0
        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("验证此测试方法的正确性。");
        }
Example #7
0
        /// <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();
            }
        }
Example #8
0
        /// <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);
 }
Example #10
0
        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);
        }
Example #11
0
        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);
        }
Example #12
0
 /// <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[])));
 }
Example #13
0
 ////////////////////////////////////////////////////////////////////////////
 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);
 }
Example #16
0
 protected WrappedDataSet(SerializationInfo info, StreamingContext context)
 {
     dataSet = AdoNetHelper.DeserializeDataSet((byte[])info.GetValue("_", typeof(byte[])));
 }
Example #17
0
 /// <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));
 }
Example #18
0
 protected FastSerializableDataSet(SerializationInfo info, StreamingContext context)
 {
     AdoNetHelper.DeserializeDataSet(this, (byte[])info.GetValue("_", typeof(byte[])));
 }
Example #19
0
 ////////////////////////////////////////////////////////////////////////////
 ////////////////////////////////////////////////////////////////////////////
 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();
             *          }
             *      }
             *  }
             * }
             */
        }
Example #22
0
        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);
        }
Example #23
0
        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);
        }
Example #24
0
        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();
                }
            }
        }
Example #25
0
        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);
 }
Example #27
0
 public override void GetObjectData(SerializationInfo info, StreamingContext context)
 {
     info.AddValue("_", AdoNetHelper.SerializeDataSet(this));
 }
Example #28
0
        /// <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();
                    }
                }
            }
        }