コード例 #1
0
        public DataTable GetTableDataList(string dataBaseLinkId, string tableName)
        {
            DataBaseLinkService link = new DataBaseLinkService();
            DataBaseLinkEntity  dataBaseLinkEntity = link.GetEntity(dataBaseLinkId);

            if (dataBaseLinkEntity != null)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT * FROM " + tableName + "");
                return(this.BaseRepository(dataBaseLinkEntity.DbConnection).FindTable(strSql.ToString()));
            }
            return(null);
        }
コード例 #2
0
        /// <summary>
        /// 判断是否数据有重复
        /// </summary>
        /// <param name="dbService"></param>
        /// <param name="col"></param>
        /// <param name="sqlonly"></param>
        /// <param name="value"></param>
        /// <param name="dbId"></param>
        private void IsOnlyOne(DataBaseLinkService dbService, System_SetExcelImportFiledEntity col, string sqlonly, string value, string dbId)
        {
            if (col.F_OnlyOne == 1)
            {
                var parameter = new List <DbParameter>();

                sqlonly += col.F_FliedName + " = @" + col.F_FliedName;
                parameter.Add(DbParameters.CreateDbParameter("@" + col.F_FliedName, value));

                var d = dbService.FindTable(dbId, sqlonly, parameter.ToArray());
                if (d.Rows.Count > 0)
                {
                    throw new Exception("【" + col.F_ColName + "】此项数据不能重复");
                }
            }
        }
コード例 #3
0
        /// <summary>
        /// 执行excel模板数据导入
        /// </summary>
        /// <param name="keyValue"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public DataTable ExcelImport(string keyValue, DataTable dt)
        {
            System_SetExcelImportFiledService filedService    = new System_SetExcelImportFiledService();
            DataItemDetailService             dataItemService = new DataItemDetailService();
            DataBaseLinkService dbService = new DataBaseLinkService();

            try
            {
                System_SetExcelImprotEntity templateInfo = this.BaseRepository().FindEntity <System_SetExcelImprotEntity>(keyValue);
                IEnumerable <System_SetExcelImportFiledEntity> filedsInfo = filedService.GetList("{\"F_ImportTemplateId\":\"" + templateInfo.F_Id + "\"}");

                Dictionary <string, IEnumerable <DataItemDetailEntity> > dicDataItem = new Dictionary <string, IEnumerable <DataItemDetailEntity> >();

                string sql      = " INSERT INTO " + templateInfo.F_DbTable + " (";
                string sqlValue = "(";
                bool   isfirt   = true;
                //组织sql语句
                //INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
                foreach (var col in filedsInfo)
                {
                    if (!isfirt)
                    {
                        sql      += ",";
                        sqlValue += ",";
                    }
                    sql      += col.F_FliedName;
                    sqlValue += "@" + col.F_FliedName;
                    isfirt    = false;
                }
                sql += " ) VALUES " + sqlValue + ")";

                string sqlonly = " select * from " + templateInfo.F_DbTable + " where ";

                dt.Columns.Add("learunColOk", typeof(string));
                dt.Columns.Add("learunColError", typeof(string));

                foreach (DataRow dr in dt.Rows)
                {
                    try
                    {
                        var parameter = new List <DbParameter>();
                        foreach (var col in filedsInfo)
                        {
                            string paramName = "@" + col.F_FliedName;

                            switch (col.F_RelationType)
                            {
                            case 0:    //无关联
                                if (col.F_FiledType == "日期")
                                {
                                    parameter.Add(DbParameters.CreateDbParameter(paramName, DateTime.ParseExact(dr[col.F_ColName].ToString(), "m/d/yy", System.Globalization.CultureInfo.CurrentCulture)));
                                }
                                else
                                {
                                    parameter.Add(DbParameters.CreateDbParameter(paramName, dr[col.F_ColName].ToString()));
                                }
                                IsOnlyOne(dbService, col, sqlonly, dr[col.F_ColName].ToString(), templateInfo.F_DbId);
                                break;

                            case 1:    //GUID
                                parameter.Add(DbParameters.CreateDbParameter(paramName, Guid.NewGuid().ToString()));
                                break;

                            case 2:    //数据字典
                                string dataItemName = "";
                                if (!dicDataItem.ContainsKey(col.F_FliedName))
                                {
                                    IEnumerable <DataItemDetailEntity> dataItemList = dataItemService.GetList(col.F_DataItemEncode);
                                    dicDataItem.Add(col.F_FliedName, dataItemList);
                                }
                                dataItemName = FindDataItemName(dicDataItem[col.F_FliedName], dr[col.F_ColName].ToString(), col.F_ColName);
                                parameter.Add(DbParameters.CreateDbParameter(paramName, dataItemName));

                                IsOnlyOne(dbService, col, sqlonly, dataItemName, templateInfo.F_DbId);
                                break;

                            case 3:    //数据表
                                string v = "";
                                try
                                {
                                    string strSql     = "select " + col.F_DbSaveFlied + " from " + col.F_DbTable + " where " + col.F_DbRelationFlied + " = @" + col.F_DbRelationFlied;
                                    var    parameter2 = new List <DbParameter>();
                                    parameter2.Add(DbParameters.CreateDbParameter("@" + col.F_DbRelationFlied, dr[col.F_ColName].ToString()));
                                    DataTable dt2 = dbService.FindTable(col.F_DbId, strSql, parameter2.ToArray());
                                    v = dt2.Rows[0][0].ToString();
                                    parameter.Add(DbParameters.CreateDbParameter(paramName, dt2.Rows[0][0].ToString()));
                                }
                                catch (Exception)
                                {
                                    throw (new Exception("【" + col.F_ColName + "】 找不到对应的数据"));
                                }
                                IsOnlyOne(dbService, col, sqlonly, v, templateInfo.F_DbId);
                                break;

                            case 4:    //固定值
                                parameter.Add(DbParameters.CreateDbParameter(paramName, col.F_Value));
                                break;

                            case 5:    //操作人ID
                                parameter.Add(DbParameters.CreateDbParameter(paramName, OperatorProvider.Provider.Current().UserId));
                                break;

                            case 6:    //操作人名字
                                parameter.Add(DbParameters.CreateDbParameter(paramName, OperatorProvider.Provider.Current().UserName));
                                break;

                            case 7:    //操作时间
                                parameter.Add(DbParameters.CreateDbParameter(paramName, DateTime.Now));
                                break;
                            }
                        }
                        dbService.ExecuteBySql(templateInfo.F_DbId, sql, parameter.ToArray());
                        dr["learunColOk"] = "1";
                    }
                    catch (Exception ex)
                    {
                        if (templateInfo.F_ErrorType == 0)
                        {
                            break;
                        }
                        dr["learunColOk"]    = "0";
                        dr["learunColError"] = ex.Message;
                    }
                }

                return(dt);
            }
            catch (System.Exception)
            {
                throw;
            }
        }