public int GetThreeLuck(string opid) { string sql = String.Format("where WechatId='{0}' And convert(varchar(10), getdate(), 23)=convert(varchar(10), AddTime, 23) ", opid); List <LuckyUserList> returl = DbBase.Fetch <LuckyUserList>(sql); return(returl.Count); }
public static string Get(string hanzichuan) { DbBase db = new DbBase(); db.ChangeShop(); return((string)db.ExecuteScalar(string.Format("select dbo.fun_getPY('{0}')", hanzichuan))); }
public RecordIds(DbBase db, HluDataSet hluDataset, TableAdapterManager hluTableAdapterMgr, ViewModelWindowMain.GeometryTypes gisLayerType) { if (db == null) { throw new ArgumentException("db"); } if (hluDataset == null) { throw new ArgumentException("hluDataset"); } if (hluTableAdapterMgr == null) { throw new ArgumentException("hluTableAdapterMgr"); } _db = db; _hluDataset = hluDataset; _hluTableAdapterMgr = hluTableAdapterMgr; _gisLayerType = gisLayerType; if (_hluDataset.lut_last_incid.IsInitialized && _hluDataset.lut_last_incid.Count == 0) { if (_hluTableAdapterMgr.lut_last_incidTableAdapter == null) { _hluTableAdapterMgr.lut_last_incidTableAdapter = new HluTableAdapter <HluDataSet.lut_last_incidDataTable, HluDataSet.lut_last_incidRow>(_db); } _hluTableAdapterMgr.Fill(_hluDataset, new Type[] { typeof(HluDataSet.lut_last_incidDataTable) }, false); } _incidCurrentNumber = CurrentMaxIncidNumber(false); InitializeIncidChildRecordIds(); }
/// <summary> /// 移除一张表 /// <param name="conn">数据库链接</param> /// </summary> public static bool DropTable(string tableName, string conn) { bool result = false; string key = string.Empty; using (DbBase helper = DalCreate.CreateDal(conn)) { key = TableSchema.GetTableCacheKey(helper); DalType dalType = helper.dalType; switch (dalType) { case DalType.Txt: case DalType.Xml: string folder = helper.Con.DataSource + Path.GetFileNameWithoutExtension(tableName); string path = folder + ".ts"; try { if (File.Exists(path)) { result = IOHelper.Delete(path); } path = folder + (dalType == DalType.Txt ? ".txt" : ".xml"); if (File.Exists(path)) { result = IOHelper.Delete(path); } } catch { } break; default: result = helper.ExeNonQuery("drop table " + Keyword(tableName, dalType), false) != -2; if (result) { //处理表相关的元数据和数据缓存。 RemoveCache(tableName, helper.DataBase, dalType); } break; } if (helper.recordsAffected == -2) { _ErrorMsg.AppendLine(helper.debugInfo.ToString()); } } if (result) { //处理数据库表字典缓存 if (TableSchema.tableCache.ContainsKey(key)) { Dictionary <string, string> tableDic = TableSchema.tableCache[key]; if (tableDic.ContainsKey(tableName)) { tableDic.Remove(tableName); } } } return(result); }
public Response Add(LuckyUserList entity) { var response = new Response(); try { if (entity == null) { throw new ArgumentNullException(nameof(entity)); } //ThrowExceptionIfEntityIsInvalid(entity); var result = DbBase.Insert(entity); if (IsInsertSuccess(result)) { response.IsSuccess = true; response.Message = "抽奖成功,星哩伴你成长哟!"; return(response); } response.Message = "抽奖失败,请联系星哩!"; } catch (Exception ex) { throw ex; } return(response); }
internal bool BulkCopyDelete() { int count = 0, pageSize = 5000; MDataTable dt = null; bool result = false; using (MAction action = new MAction(mdt.TableName, _Conn)) { action.SetAopState(Aop.AopOp.CloseAll); if (action.DalVersion.StartsWith("08")) { pageSize = 1000; } count = mdt.Rows.Count / pageSize + 1; DbBase sourceHelper = action.dalHelper; if (_dalHelper != null) { action.dalHelper = _dalHelper; } else { action.BeginTransation(); } MCellStruct keyColumn = jointPrimaryIndex != null ? mdt.Columns[jointPrimaryIndex[0]] : mdt.Columns.FirstPrimary; string columnName = keyColumn.ColumnName; for (int i = 0; i < count; i++) { dt = mdt.Select(i + 1, pageSize, null);//分页读取 if (dt != null && dt.Rows.Count > 0) { #region 核心逻辑 string whereIn = SqlCreate.GetWhereIn(keyColumn, dt.GetColumnItems <string>(columnName, BreakOp.NullOrEmpty, true), action.DalType); result = action.Delete(whereIn) || action.RecordsAffected == 0; if (result) { sourceTable.RecordsAffected += action.RecordsAffected;//记录总删除的数量。 } else { sourceTable.RecordsAffected = 0; string msg = "Error On : MDataTable.AcceptChanges.Delete." + mdt.TableName + " : where (" + whereIn + ") : " + action.DebugInfo; sourceTable.DynamicData = msg; Log.WriteLogToTxt(msg); break; } #endregion } } if (_dalHelper == null) { action.EndTransation(); } else { action.dalHelper = sourceHelper;//还原。 } } return(result); }
public MessageSender(DbBase database, Int64 messageId) : base() { this.messageId = messageId; this.database = database; //Busca no banco a mensage DataTable dtMessage = database.ExecuteDataTable("select * from st_messages where id = " + messageId); if ((dtMessage == null) || (dtMessage.Rows.Count == 0)) { throw new Exception("Message not found"); } this.enterpriseId = (Int64)dtMessage.Rows[0]["enterprise_id"]; this.isHtml = (Boolean)dtMessage.Rows[0]["html"]; this.mailSubject = dtMessage.Rows[0]["subject"].ToString(); this.mailBody = dtMessage.Rows[0]["body"].ToString(); this.messageKey = dtMessage.Rows[0]["key"].ToString(); this.mailTo = new List <MailAddress>(); try { foreach (String m in dtMessage.Rows[0]["to"].ToString().Split(",".ToCharArray())) { mailTo.Add(new MailAddress(m)); } } catch (Exception ex) { throw new Exception("Erro parsing recipient", ex); } }
/// <summary> /// 获取评论信息 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="postId"></param> /// <returns></returns> public GetPagingResponse <Comment> GetPageCommentsByPostId(int pageIndex, int pageSize, int postId) { var response = new GetPagingResponse <Comment>(); try { var result = DbBase.Query <Comment>().Include(p => p.Users) .Where(p => p.PostId == postId) .OrderBy(p => p.Id) .ToPage(pageIndex, pageSize); if (result.Items != null && result.Items.Count > 0) { response.IsSuccess = true; response.Message = "获取成功"; response.Pages = result; return(response); } response.Message = "暂无数据"; } catch (Exception ex) { throw ex; } return(response); }
/// <summary> /// 获取车辆信息 /// </summary> /// <param name="plateNo"></param> /// <returns></returns> public static List <VehicleModel> GetAll() { DbBase <VehicleModel> db = new DbBase <VehicleModel>(); var data = db.GetAll("PlateNo"); return(data); }
public void UpdateToSql(string category, decimal price_plus) { BLL.DbBase db = new DbBase(); string SqlStr = string.Format("SELECT ID FROM Price WHERE category='{0}' AND Price_Plus=0", category); DataTable dt = db.ExecuteTable(SqlStr); if (dt != null && dt.Rows.Count > 0) { //更新MongoDatabase var UpdateObj = GetList().Find(o => o.name == category); UpdateObj.Price_Plus = (float)price_plus; using (MDbBase mdb = new MDbBase()) { mdb.GetCollection <ConfigPriceMe>().Update(UpdateObj, o => o.id == UpdateObj.id); } //阻塞500毫秒(预防) Thread.Sleep(500); Price price = null; foreach (DataRow item in dt.Rows) { string ID = item["ID"].ToString(); price = new Price(); //价格更新 price.Update(ID, category); } } }
/// <summary> /// 获取上一篇和下一篇 /// </summary> /// <param name="id"></param> /// <param name="type">0:上一篇 1:下一篇</param> /// <returns></returns> public GetListsResponse <Articles> GetPageArticleByOne(int id, int type = 0) { var response = new GetListsResponse <Articles>(); var result = DbBase.Query <Articles>() .Where(p => p.IsShow == 1 && p.Id < id) .OrderBy(p => p.Id) .ThenByDescending(p => p.IsTop) .ThenByDescending(p => p.AddTime) .ThenByDescending(p => p.Sort).ToPage(1, 1); if (type == 1) { result = DbBase.Query <Articles>() .Where(p => p.IsShow == 1 && p.Id > id) .OrderBy(p => p.Id) .ThenByDescending(p => p.IsTop) .ThenByDescending(p => p.AddTime) .ThenByDescending(p => p.Sort).ToPage(1, 1); } if (result.Items != null && result.Items.Count > 0) { response.IsSuccess = true; response.Message = "获取成功!"; response.Items = result.Items; return(response); } response.Message = "暂无数据!"; return(response); }
internal static void CheckAndCreateOracleSequence(string seqName, string conn, string primaryKey, string tableName) { seqName = seqName.ToUpper(); using (DbBase db = DalCreate.CreateDal(conn)) { object o = db.ExeScalar(string.Format(TableSchema.ExistOracleSequence, seqName), false); if (db.recordsAffected != -2 && (o == null || Convert.ToString(o) == "0")) { int startWith = 1; if (!string.IsNullOrEmpty(primaryKey)) { o = db.ExeScalar(string.Format(TableSchema.GetOracleMaxID, primaryKey, tableName), false); if (db.recordsAffected != -2) { if (!int.TryParse(Convert.ToString(o), out startWith) || startWith < 1) { startWith = 1; } else { startWith++; } } } db.ExeNonQuery(string.Format(TableSchema.CreateOracleSequence, seqName, startWith), false); } if (db.recordsAffected == -2) { _ErrorMsg.AppendLine("CheckAndCreateOracleSequence:" + db.debugInfo.ToString()); } } }
private static bool FillSchemaFromCache(ref MDataRow row, ref DbBase dbBase, string tableName, string sourceTableName) { bool returnResult = false; string key = GetSchemaKey(tableName, dbBase.DataBase, dbBase.dalType); if (CacheManage.LocalInstance.Contains(key))//缓存里获取 { try { row = ((MDataColumn)CacheManage.LocalInstance.Get(key)).ToRow(sourceTableName); returnResult = row.Count > 0; } catch (Exception err) { Log.WriteLogToTxt(err); } } else if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath)) { string fullPath = AppDomain.CurrentDomain.BaseDirectory + AppConfig.DB.SchemaMapPath + key + ".ts"; if (System.IO.File.Exists(fullPath)) { MDataColumn mdcs = MDataColumn.CreateFrom(fullPath); if (mdcs.Count > 0) { row = mdcs.ToRow(sourceTableName); returnResult = row.Count > 0; CacheManage.LocalInstance.Add(key, mdcs.Clone(), null, 1440); } } } return(returnResult); }
public static bool tran_common(Func <DbBase, IDbTransaction, bool> func) { bool isSuccess = true; //打开数据库 using (DbBase DbContext = new DbBase(Config.connectionStringsName)) { using (IDbTransaction tran = DbContext.DbTransaction) { try { isSuccess = func(DbContext, tran); } catch (Exception e) { isSuccess = false; throw e; } finally { if (isSuccess) { tran.Commit(); } else { tran.Rollback(); } } } return(isSuccess); } }
/// <summary> /// 获取表列架构(链接错误时,抛异常) /// </summary> /// <param name="tableName">表名</param> /// <param name="conn">数据库链接</param> /// <param name="errInfo">出错时的错误信息</param> /// <returns></returns> public static MDataColumn GetColumns(string tableName, string conn, out string errInfo) { errInfo = string.Empty; DbBase helper = DalCreate.CreateDal(conn); helper.ChangeDatabaseWithCheck(tableName);//检测dbname.dbo.tablename的情况 if (!helper.TestConn()) { errInfo = helper.debugInfo.ToString(); if (string.IsNullOrEmpty(errInfo)) { errInfo = "Open database fail : " + tableName; } helper.Dispose(); Error.Throw(errInfo); return(null); } if (!tableName.Contains(" ")) { tableName = GetMapTableName(conn, tableName); } MDataColumn mdc = TableSchema.GetColumns(tableName, ref helper); helper.Dispose(); return(mdc); }
public override void LoadModel(DbBase entity) { DbSkill skill = entity as DbSkill; this.id = skill.id; this.name = skill.name; }
/// <summary> /// 获取评论信息 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="postId"></param> /// <returns></returns> public GetPagingResponse <CartoonComment> GetPageCommentsByArticleId(int pageIndex, int pageSize, int articleid) { var response = new GetPagingResponse <CartoonComment>(); try { var result = DbBase.Query <CartoonComment>().Include(p => p.Users) .Where(p => p.ArticleId == articleid) .OrderByDescending(p => p.CreateTime) .ToPage(pageIndex, pageSize); if (result.Items != null && result.Items.Count > 0) { response.IsSuccess = true; response.Message = "获取成功"; response.Pages = result; return(response); } response.Message = "暂无数据"; } catch (Exception ex) { throw ex; } return(response); }
private void CheckForNewServices(DbBase ctx, String configPath) { string[] confFiles = Directory.GetFiles(configPath, "*.conf"); foreach (string file in confFiles) { try { CASPluginConfig cfg = new CASPluginConfig(); try { cfg.LoadFromXML(new FileInfo(file)); } catch (Exception ex) { throw new Exception("Error parsing config file '" + file + "'", ex); } Uri svc = CASPluginService.Normalize(cfg.Service); //Verifica se o contexto é novo if (ctx.ExecuteScalar<Int64>("select count(*) from [CAS_Context] where [Name] = '" + cfg.Context + "'") == 0) { //Adiciona ctx.ExecuteNonQuery("INSERT INTO [CAS_Context] ([Name],[Host]) VALUES ('" + cfg.Context + "','" + Environment.MachineName + "');"); } //Verifica se o serviço é novo if (ctx.ExecuteScalar<Int64>("select count(*) from [CAS_Service] where [Uri] = '" + svc.AbsoluteUri + "'") == 0) { //Adiciona o serviço ctx.ExecuteNonQuery("INSERT INTO [CAS_Service] ([Context_Name],[Uri],[Plugin_Assembly],[Permit_Password_Recover],[External_Password_Recover],[Password_RecoverUri],[Permit_Change_Password],[Admin]) VALUES ('" + cfg.Context + "','" + svc.AbsoluteUri + "','" + cfg.PluginAssembly + "'," + (cfg.PermitPasswordRecover ? 1 : 0) + "," + (cfg.ExternalPasswordRecover ? 1 : 0) + ",'" + cfg.PasswordRecoverUri + "'," + (cfg.PermitChangePassword ? 1 : 0) + ",'" + cfg.Admin + "');"); } else { //Atualiza o serviço ctx.ExecuteNonQuery("update [CAS_Service] set [Context_Name] = '" + cfg.Context + "', [Plugin_Assembly] = '" + cfg.PluginAssembly + "',[Permit_Password_Recover] = " + (cfg.PermitPasswordRecover ? 1 : 0) + ",[External_Password_Recover] = " + (cfg.ExternalPasswordRecover ? 1 : 0) + ",[Password_RecoverUri] = '" + cfg.PasswordRecoverUri + "',[Permit_Change_Password] = " + (cfg.PermitChangePassword ? 1 : 0) + ",[Admin] = '" + cfg.Admin + "' where [Uri] = '" + svc.AbsoluteUri + "'"); //Apaga as propriedades ctx.ExecuteNonQuery("delete from [CAS_Service_Attributes] where [Service_Uri] = '" + svc.AbsoluteUri + "'"); } //Adiciona as propriedades foreach(String key in cfg.Attributes.Keys) ctx.ExecuteNonQuery("INSERT INTO [CAS_Service_Attributes] ([Service_Uri],[Key],[Value]) VALUES ('" + svc.AbsoluteUri + "','" + key + "','" + (cfg.Attributes[key] is DateTime ? ((DateTime)cfg.Attributes[key]).ToString("o") : cfg.Attributes[key].ToString()) + "');"); } catch(Exception ex) { throw ex; } } }
/// <summary> /// 删除帖子 /// </summary> /// <param name="id"></param> /// <returns></returns> public Response DeletePost(int id) { if (id == 0) { throw new ArgumentNullException(nameof(id)); } var response = new Response(); using (var tran = DbBase.GetTransaction()) { //删除帖子 var r1 = DbBase.Delete <Posts>(id); //删除评论 var r2 = DbBase.DeleteMany <Comment>().Where(p => p.PostId == id).Execute(); tran.Complete(); if (IsUpdateSuccess(r1)) { response.IsSuccess = true; response.Message = "操作成功"; return(response); } } response.Message = "操作失败"; return(response); }
public GetListsResponse <arts> GetArticlesToType() { var response = new GetListsResponse <arts>(); var typelist = DbBase.Query <ArticleCategory>().Where(ac => ac.ParentId == 0).ToList(); IList <arts> artslist = new List <arts>(); foreach (var item in typelist) { var result = DbBase.Query <Articles>().Include(p => p.ArticleCategory) .Include(p => p.ArticleCategorys).OrderByDescending(p => p.Hits).Where(ac => ac.ArticleParentcategoryId == item.Id).ToPage(1, 2); artslist.Add(new arts { categoryid = item.Id, categoryname = item.CategoryName, articlelist = result.Items }); } if (artslist != null && artslist.Count > 0) { response.IsSuccess = true; response.Message = "获取成功"; response.Items = artslist; return(response); } response.Message = "暂无数据"; return(response); }
public override Boolean UserCan(DbBase database, Int64 entityId, Int64 enterpriseId, String module, String permission) { if ((!(database is IAMDatabase)) && (!(database is SqlBase))) { throw new Exception("Invalid database type. Expected IAMDatabase or SqlBase"); } DbParameterCollection par = null; try { String[] parts = permission.ToLower().Split(".".ToCharArray(), 2); par = new DbParameterCollection(); par.Add("@entity_id", typeof(Int64)).Value = entityId; par.Add("@enterprise_id", typeof(Int64)).Value = enterpriseId; par.Add("@submodule", typeof(String)).Value = parts[0]; par.Add("@permission", typeof(String)).Value = parts[1]; return(database.ExecuteScalar <Boolean>("sp_sys_rbac", CommandType.StoredProcedure, par, null)); } catch (Exception ex) { return(false); } finally { par = null; } }
public override Boolean UserAdmin(DbBase database, Int64 entityId, Int64 enterpriseId) { if ((!(database is IAMDatabase)) && (!(database is SqlBase))) { throw new Exception("Invalid database type. Expected IAMDatabase or SqlBase"); } DbParameterCollection par = null; try { par = new DbParameterCollection(); par.Add("@entity_id", typeof(Int64)).Value = entityId; par.Add("@enterprise_id", typeof(Int64)).Value = enterpriseId; return(database.ExecuteScalar <Boolean>("sp_sys_rbac_admin", CommandType.StoredProcedure, par, null)); } catch (Exception ex) { return(false); } finally { par = null; } }
internal bool BulkCopyUpdate() { int count = 0, pageSize = 5000; MDataTable dt = null; using (MAction action = new MAction(mdt.TableName, _Conn)) { action.SetAopState(Aop.AopOp.CloseAll); if (action.DalVersion.StartsWith("08")) { pageSize = 1000; } count = mdt.Rows.Count / pageSize + 1; DbBase sourceHelper = action.dalHelper; if (_dalHelper != null) { action.dalHelper = _dalHelper; } else { action.BeginTransation(); } bool result = false; MCellStruct keyColumn = jointPrimaryIndex != null ? mdt.Columns[jointPrimaryIndex[0]] : mdt.Columns.FirstPrimary; string columnName = keyColumn.ColumnName; for (int i = 0; i < count; i++) { dt = mdt.Select(i + 1, pageSize, null);//分页读取 if (dt != null && dt.Rows.Count > 0) { #region 核心逻辑 string whereIn = SqlCreate.GetWhereIn(keyColumn, dt.GetColumnItems <string>(columnName, BreakOp.NullOrEmpty, true), action.DalType); MDataTable dtData = action.Select(whereIn); //获取远程数据。 dtData.Load(dt); //重新加载赋值。 result = action.Delete(whereIn); //如果存在IsDeleted,会被转Update(导致后续无法Insert) if (result) { dtData.DynamicData = action; result = dtData.AcceptChanges(AcceptOp.InsertWithID); } if (!result) { break; } #endregion } } if (_dalHelper == null) { action.BeginTransation(); } else { action.dalHelper = sourceHelper;//还原。 } } return(true); }
static public CASTicketResult GetToken(DbBase database, Uri service, String grantTicket, String username) { CASTicketResult ret = null; if (service != null) { DataTable dtTickets = database.ExecuteDataTable(String.Format("select * from [CAS_Ticket] where [Service_Uri] = '{0}' and (Grant_Ticket = '{1}' or User_Name = '{2}')", CASPluginService.Normalize(service).AbsoluteUri, grantTicket, username)); if ((dtTickets != null) && (dtTickets.Rows.Count > 0)) { ret = new CASTicketResult(); ret.Success = true; ret.Service = CASPluginService.Normalize(dtTickets.Rows[0]["Service_Uri"].ToString()); ret.UserId = dtTickets.Rows[0]["User_Id"].ToString(); ret.UserName = dtTickets.Rows[0]["User_Name"].ToString(); ret.GrantTicket = dtTickets.Rows[0]["Grant_Ticket"].ToString(); ret.LongTicket = dtTickets.Rows[0]["Long_Ticket"].ToString(); ret.ProxyTicket = dtTickets.Rows[0]["Proxy_Ticket"].ToString(); ret.CreateDate = (DateTime)dtTickets.Rows[0]["Create_Date"]; ret.Expires = (DateTime)dtTickets.Rows[0]["Expires"]; ret.CreateByCredentials = (Boolean)dtTickets.Rows[0]["Create_By_Credentials"]; } } if (ret == null) { ret = new CASTicketResult(); } if (ret.Success) { if (service == null) { ret.Success = false; return(ret); } //Verifica se o ticket pode ser validado no serviço atual if ((ret.Service == null) || (!ret.Service.Equals(service))) { if (database.ExecuteScalar <Int64>(String.Format("select COUNT(*) from [CAS_Service] where Uri = '{0}' and Context_Name = (select Context_Name from [CAS_Service] where Uri = '{1}')", CASPluginService.Normalize(service).AbsoluteUri, ret.Service.AbsoluteUri)) > 0) { ret.CreateByCredentials = false; //Define que as informações foram copiadas de outro token e não a partir de uma autenticação usuário/senha } else { ret.Success = false; } } //Define o serviço atual ret.Service = service; //Salva o token copiado //ret.SaveToFile(basePath); ret.SaveToDb(database); } return(ret); }
/// <summary> /// �Ƿ���ڱ����ͼ /// </summary> /// <param name="type">"U"��"V"</param> /// <param name="name">��������ͼ��</param> public static bool Exists(string type, string name, ref DbBase helper) { int result = 0; string exist = string.Empty; helper.IsAllowRecordSql = false; DalType dalType = helper.dalType; name = SqlFormat.Keyword(name, helper.dalType); switch (dalType) { case DalType.Access: try { System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(helper.Con.ConnectionString); con.Open(); result = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, SqlFormat.NotKeyword(name), "Table" }).Rows.Count; con.Close(); } catch (Exception err) { Log.WriteLogToTxt(err); } break; case DalType.MySql: if (type != "V" || (type == "V" && name.ToLower().StartsWith("v_")))//��ͼ����v_��ͷ { exist = string.Format(ExistMySql, SqlFormat.NotKeyword(name), helper.DataBase); } break; case DalType.Oracle: exist = string.Format(ExistOracle, (type == "U" ? "TABLE" : "VIEW"), name); break; case DalType.MsSql: exist = string.Format(helper.Version.StartsWith("08") ? Exist2000 : Exist2005, name, type); break; case DalType.SQLite: exist = string.Format(ExistSqlite, (type == "U" ? "table" : "view"), SqlFormat.NotKeyword(name)); break; case DalType.Sybase: exist = string.Format(ExistSybase, SqlFormat.NotKeyword(name), type); break; case DalType.Txt: case DalType.Xml: string folder = helper.Con.DataSource + Path.GetFileNameWithoutExtension(name); FileInfo info = new FileInfo(folder + ".ts"); result = (info.Exists && info.Length > 10) ? 1 : 0; if (result == 0) { info = new FileInfo(folder + (dalType == DalType.Txt ? ".txt" : ".xml")); result = (info.Exists && info.Length > 10) ? 1 : 0; } break; } if (exist != string.Empty) { helper.IsAllowRecordSql = false; result = Convert.ToInt32(helper.ExeScalar(exist, false)); } return result > 0; }
private void LoadSchemata() { List <String> schemaList = new List <String>(); NpgsqlConnection cn = null; try { if ((_connStrBuilder != null) && !String.IsNullOrEmpty(_connStrBuilder.Host)) { cn = new NpgsqlConnection(_connStrBuilder.ConnectionString); cn.Open(); NpgsqlCommand cmd = cn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT schema_name FROM information_schema.schemata" + " WHERE schema_name !~* '^(pg|information)_'" + " AND catalog_name = '" + _connStrBuilder.Database + "'"; NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(cmd); DataTable dbTable = new DataTable(); try { adapter.Fill(dbTable); schemaList = (from r in dbTable.AsEnumerable() let schemaName = r.Field <string>("schema_name") select schemaName).OrderBy(s => s).ToList(); _connStrBuilder.SearchPath = DbBase.GetDefaultSchema(Backends.PostgreSql, _connStrBuilder, schemaList); } catch { } } } catch (Exception ex) { if (ex is NpgsqlException) { MessageBox.Show(ex.Message, "PostgreSQL Error", MessageBoxButton.OK, MessageBoxImage.Error); } else { MessageBox.Show(ex.Message, "HBIC Error", MessageBoxButton.OK, MessageBoxImage.Error); } } finally { if ((cn != null) && (cn.State != ConnectionState.Closed)) { cn.Close(); } _schemata = schemaList; OnPropertyChanged("Schemata"); if (_schemata.Count == 1) { _connStrBuilder.SearchPath = _schemata[0]; } OnPropertyChanged("SearchPath"); } }
public override void LoadModel(DbBase entity) { DbSubject subj = entity as DbSubject; this.name = subj.name; this.id = subj.id; this.facultyId = subj.facultyId; }
public void GetListTest() { DbBase <Data_SysUser> dbBase = new DbBase <Data_SysUser>(); var aaa = dbBase.GetList(m => m.IsDeleted != true, m => m.IsDeleted); var bbb = dbBase.GetList(m => m.IsDeleted != true, m => m.OID); Assert.IsNull(aaa); }
/// <summary> /// 检查昵称是否已存在 /// </summary> /// <param name="nickname"></param> /// <returns></returns> public bool CheckNicknameIsExists(string nickname, int?userId = null) { if (userId != null) { return(DbBase.Query <W_Users>().Any(p => p.NickName == nickname && p.Id != userId)); } return(DbBase.Query <W_Users>().Any(p => p.NickName == nickname)); }
public void HitsPlusOne(StarCartoon entity) { if (entity != null) { entity.Hits++; DbBase.UpdateMany <StarCartoon>().OnlyFields(p => p.Hits).Where(p => p.Id == entity.Id).Execute(entity); } }
/// <summary> /// 检查手机号是否已存在 /// </summary> /// <param name="PhoneNumber"></param> /// <returns></returns> public bool CheckPhoneExists(string phone, int?userId = null) { if (userId != null) { return(DbBase.Query <W_Users>().Any(p => p.PhoneNumber == phone && p.Id != userId)); } return(DbBase.Query <W_Users>().Any(p => p.PhoneNumber == phone)); }
/// <summary> /// Builds a UNION query from a list of SqlFilterCondition. /// All constituent SELECT statement use the same target list and FROM clause but different WHERE clauses. /// </summary> /// <param name="targetList">Target list for union query. Same for each select query.</param> /// <param name="fromClause">From clause for union query. Same for each select query.</param> /// <param name="orderByOrdinal">Ordinal of column by which to order otput.</param> /// <param name="IncidSelectionWhereClause">List of where clauses from which to build UNION query. /// Input is assumed to be 0 based.</param> /// <param name="db">Database against which UNION query will be run.</param> /// <returns></returns> public static string UnionQuery(string targetList, string fromClause, int orderByOrdinal, List<List<SqlFilterCondition>> IncidSelectionWhereClause, DbBase db) { return IncidSelectionWhereClause.Select(w => db.WhereClause(true, true, true, w)) .Aggregate(new StringBuilder(), (sb, s) => sb.Append(String.Format( "\nUNION\nSELECT {0} FROM {1}{2}", targetList, fromClause, s))).Remove(0, 7) .Append(orderByOrdinal > 0 ? String.Format(" ORDER BY {0}", orderByOrdinal + 1) : String.Empty) .ToString(); }
private string ExportMdb(string targetListStr, string fromClauseStr, List<List<SqlFilterCondition>> exportFilter, DbBase dataBase, List<ExportField> exportFields, DataTable exportTable, int[] sortOrdinals, int[] matrixOrdinals, int[] formationOrdinals, int[] managementOrdinals, int[] complexOrdinals, int[] bapOrdinals, int[] sourceOrdinals, int[][] fieldMap, out int exportRowCount) { exportRowCount = -1; int outputRowCount = 0; DbOleDb dbOut = null; string tempPath = String.Empty; try { tempPath = Path.GetTempPath(); } catch { tempPath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData); } tempPath = Path.Combine(tempPath, Path.GetFileNameWithoutExtension(Path.GetRandomFileName()) + ".mdb"); try { if (File.Exists(tempPath)) File.Delete(tempPath); OdbcCP32 odbc = new OdbcCP32(); odbc.CreateDatabase(tempPath); string connString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};", tempPath); string defaultSchema = ""; bool promptPwd = false; dbOut = new DbOleDb(ref connString, ref defaultSchema, ref promptPwd, Properties.Resources.PasswordMaskString, Settings.Default.UseAutomaticCommandBuilders, true, Settings.Default.DbIsUnicode, Settings.Default.DbUseTimeZone, 255, Settings.Default.DbBinaryLength, Settings.Default.DbTimePrecision, Settings.Default.DbNumericPrecision, Settings.Default.DbNumericScale); //--------------------------------------------------------------------- // FIX: 054 Improve error reporting during exports. // // Throw an error if the table cannot be created. if (!dbOut.CreateTable(exportTable)) throw new Exception("Error creating the export table"); //--------------------------------------------------------------------- DataSet datasetOut = new DataSet("Export"); IDbDataAdapter adapterOut = dbOut.CreateAdapter(exportTable); adapterOut.MissingSchemaAction = MissingSchemaAction.AddWithKey; adapterOut.Fill(datasetOut); int[] pkOrdinals = exportTable.PrimaryKey.Select(c => c.Ordinal).ToArray(); exportTable.PrimaryKey = pkOrdinals.Select(o => exportTable.Columns[o]).ToArray(); adapterOut.TableMappings.Clear(); adapterOut.TableMappings.Add(exportTable.TableName, datasetOut.Tables[0].TableName); // Save the export table structure so that the field // properties can be examined during the export. DataTable exportTable2 = exportTable; exportTable = datasetOut.Tables[0]; //--------------------------------------------------------------------- // CHANGED: CR13 (Export features performance) // Improve the performance as much as possible when // creating the attribute database. // // Turn off notifications and index maintenance whilst // inserting the records. exportTable.BeginLoadData(); //--------------------------------------------------------------------- // If there is only one long list then chunk // it up into smaller lists. if (exportFilter.Count == 1) { try { List<SqlFilterCondition> whereCond = new List<SqlFilterCondition>(); whereCond = exportFilter[0]; exportFilter = whereCond.ChunkClause(240).ToList(); } catch { } } //--------------------------------------------------------------------- // FIX: 047 Break exporting attributes into chunks to avoid errors // with excessive sql lengths. // outputRowCount = 0; exportRowCount = 0; //--------------------------------------------------------------------- // FIX: 046 Don't export duplicate record details for the // same incid. // // Set the field map indexes to the start of the array. int[] fieldMapIndex = new int[fieldMap.Length]; for (int k = 0; k < fieldMap.Length; k++) { fieldMapIndex[k] = 1; } //--------------------------------------------------------------------- for (int j = 0; j < exportFilter.Count; j++) { DataRow exportRow = exportTable.NewRow(); bool rowAdded = false; // Union the constituent parts of the export query // together into a single SQL string. string sql = ScratchDb.UnionQuery(targetListStr, fromClauseStr, sortOrdinals, exportFilter[j], dataBase); // Execute the sql to retrieve the records. using (IDataReader reader = _viewModelMain.DataBase.ExecuteReader(sql, _viewModelMain.DataBase.Connection.ConnectionTimeout, CommandType.Text)) { string currIncid = String.Empty; string prevIncid = String.Empty; int currMatrixId = -1; int currFormationId = -1; int currManagementId = -1; int currComplexId = -1; int currBapId = -1; int currSourceId = -1; int currSourceDateStart = 0; int currSourceDateEnd = 0; List<int> matrixIds = null; List<int> formationIds = null; List<int> managementIds = null; List<int> complexIds = null; List<int> bapIds = null; List<int> sourceIds = null; string currSourceDateType = String.Empty; int exportColumn; // Read each record and process the contents. while (reader.Read()) { // Get the current incid. currIncid = reader.GetString(_incidOrdinal); //--------------------------------------------------------------------- // FIX: 046 Don't export duplicate record details for the // same incid. // // Get the current matrix id. if (_matrixIdOrdinal != -1) { object matrixIdValue = reader.GetValue(_matrixIdOrdinal); if (matrixIdValue != DBNull.Value) currMatrixId = (int)matrixIdValue; else currMatrixId = -1; } // Get the current formation id. if (_formationIdOrdinal != -1) { object FormationIdValue = reader.GetValue(_formationIdOrdinal); if (FormationIdValue != DBNull.Value) currFormationId = (int)FormationIdValue; else currFormationId = -1; } // Get the current Management id. if (_managementIdOrdinal != -1) { object ManagementIdValue = reader.GetValue(_managementIdOrdinal); if (ManagementIdValue != DBNull.Value) currManagementId = (int)ManagementIdValue; else currManagementId = -1; } // Get the current Complex id. if (_complexIdOrdinal != -1) { object ComplexIdValue = reader.GetValue(_complexIdOrdinal); if (ComplexIdValue != DBNull.Value) currComplexId = (int)ComplexIdValue; else currComplexId = -1; } // Get the current bap id (or equivalent lookup table field). if (_bapIdOrdinal != -1) { object bapIdValue = reader.GetValue(_bapIdOrdinal); if (bapIdValue != DBNull.Value) currBapId = (int)bapIdValue; else currBapId = -1; } // Get the current source id (or equivalent lookup table field). if (_sourceIdOrdinal != -1) { object sourceIdValue = reader.GetValue(_sourceIdOrdinal); if (sourceIdValue != DBNull.Value) currSourceId = (int)sourceIdValue; else currSourceId = -1; } //--------------------------------------------------------------------- //--------------------------------------------------------------------- // CHANGED: CR17 (Exporting date fields) // Store all of the source date fields for use later when // formatting the attribute data. // // Get the current source date start. if ((_sourceDateStartOrdinals.Count() > 0) && !reader.IsDBNull(_sourceDateStartOrdinals[0])) currSourceDateStart = reader.GetInt32(_sourceDateStartOrdinals[0]); // Get the current source date type. if ((_sourceDateEndOrdinals.Count() > 0) && !reader.IsDBNull(_sourceDateEndOrdinals[0])) currSourceDateEnd = reader.GetInt32(_sourceDateEndOrdinals[0]); // Get the current source date type. if ((_sourceDateTypeOrdinals.Count() > 0) && !reader.IsDBNull(_sourceDateTypeOrdinals[0])) currSourceDateType = reader.GetString(_sourceDateTypeOrdinals[0]); //--------------------------------------------------------------------- // If this incid is different to the last record's incid // then process all the fields. if (currIncid != prevIncid) { // If the last export row has not been added then // add it now. if (!exportRow.IsNull(fieldMap[0][1])) { exportTable.Rows.Add(exportRow); rowAdded = true; // Increment the output row count. outputRowCount += 1; // Commit the outputs and update the export row count // every 10,000 records to avoid excessive memory use. if (outputRowCount >= 10000) { exportRowCount += adapterOut.Update(datasetOut); outputRowCount = 0; } } // Store the last incid. prevIncid = currIncid; //--------------------------------------------------------------------- // FIX: 046 Don't export duplicate record details for the // same incid. // matrixIds = new List<int>(); formationIds = new List<int>(); managementIds = new List<int>(); complexIds = new List<int>(); bapIds = new List<int>(); sourceIds = new List<int>(); // Reset the field map indexes to the start of the array. for (int k = 0; k < fieldMap.Length; k++) { fieldMapIndex[k] = 1; } //--------------------------------------------------------------------- // Create a new export row ready for the next values. exportRow = exportTable.NewRow(); rowAdded = false; // Loop through all the fields in the field map // to transfer the values from the input reader // to the correct field in the export row. for (int i = 0; i < fieldMap.GetLength(0); i++) { //--------------------------------------------------------------------- // FIX: 046 Don't export duplicate record details for the // same incid. // // Set the export column ordinal from the current // field map index for this field. exportColumn = fieldMap[i][fieldMapIndex[i]]; // Increment the field map index for this field. fieldMapIndex[i] += 1; //--------------------------------------------------------------------- // If this field is not mapped from the input reader // set the export table value to null. if (fieldMap[i][0] == -1) continue; // Store the input value of the current column. object inValue = reader.GetValue(fieldMap[i][0]); // If the value is null then skip this field. if (inValue == DBNull.Value) continue; // Get the properties for the current export field. ExportField exportField = exportFields.Find(f => f.FieldOrdinal == i); //--------------------------------------------------------------------- // FIX: 048 Enable fields to be exported using a different // data type. // // Convert the input value to the output value data type and format. object outValue; outValue = ConvertInput(fieldMap[i][0], inValue, reader.GetFieldType(fieldMap[i][0]), exportTable2.Columns[exportColumn].DataType, (exportField != null) ? exportField.FieldFormat : null, currSourceDateStart, currSourceDateEnd, currSourceDateType); //--------------------------------------------------------------------- // If the value is not null. if (outValue != null) { // Get the maximum length of the column. int fieldLength = exportTable2.Columns[exportColumn].MaxLength; // If the maximum length of the column is shorter // than the value then truncate the value as it // is transferred to the export row. if ((fieldLength != -1) && (fieldLength < outValue.ToString().Length)) exportRow[exportColumn] = outValue.ToString().Substring(0, fieldLength); else exportRow[exportColumn] = outValue; } } } else { // Loop through all the fields in the field map // to transfer the values from the input reader // to the correct field in the export row. for (int i = 0; i < fieldMap.GetLength(0); i++) { // Only process fields that have multiple outputs // specified in the field map. if (fieldMapIndex[i] < fieldMap[i].Length) { //--------------------------------------------------------------------- // FIX: 046 Don't export duplicate record details for the // same incid. // // Set the export column ordinal from the current // field map index for this field. exportColumn = fieldMap[i][fieldMapIndex[i]]; // If the value is not null and the string value is different // to the last string value for this incid, or, the column is // allowed to have duplicates and the bap or source is different // to the last bap or source, then output the value. if (Array.IndexOf(matrixOrdinals, exportColumn) != -1) { if (matrixIds.Contains(currMatrixId)) continue; } else if (Array.IndexOf(formationOrdinals, exportColumn) != -1) { if (formationIds.Contains(currFormationId)) continue; } else if (Array.IndexOf(managementOrdinals, exportColumn) != -1) { if (managementIds.Contains(currManagementId)) continue; } else if (Array.IndexOf(complexOrdinals, exportColumn) != -1) { if (complexIds.Contains(currComplexId)) continue; } else if (Array.IndexOf(bapOrdinals, exportColumn) != -1) { if (bapIds.Contains(currBapId)) continue; } else if (Array.IndexOf(sourceOrdinals, exportColumn) != -1) { if (sourceIds.Contains(currSourceId)) continue; } // Increment the field map index for this field. fieldMapIndex[i] += 1; //--------------------------------------------------------------------- // If this field is not mapped from the input reader // set the export table value to null. if (fieldMap[i][0] == -1) continue; // Store the input value of the current column. object inValue = reader.GetValue(fieldMap[i][0]); // If the value is null then skip this field. if (inValue == DBNull.Value) continue; // Get the properties for the current export field. ExportField exportField = exportFields.Find(f => f.FieldOrdinal == i); //--------------------------------------------------------------------- // FIX: 048 Enable fields to be exported using a different // data type. // // Convert the input value to the output value data type and format. object outValue; outValue = ConvertInput(fieldMap[i][0], inValue, reader.GetFieldType(fieldMap[i][0]), exportTable2.Columns[exportColumn].DataType, (exportField != null) ? exportField.FieldFormat : null, currSourceDateStart, currSourceDateEnd, currSourceDateType); //--------------------------------------------------------------------- // If the value is not null. if (outValue != null) { // Get the maximum length of the output column. int fieldLength = exportTable2.Columns[exportColumn].MaxLength; // If the maximum length of the column is shorter // than the value then truncate the value as it // is transferred to the export row. if ((fieldLength != -1) && (fieldLength < outValue.ToString().Length)) exportRow[exportColumn] = outValue.ToString().Substring(0, fieldLength); else exportRow[exportColumn] = outValue; } } } } //--------------------------------------------------------------------- // FIX: 046 Don't export duplicate record details for the // same incid. // // Store the current ids so that they are not output again. matrixIds.Add(currMatrixId); formationIds.Add(currFormationId); managementIds.Add(currManagementId); complexIds.Add(currComplexId); bapIds.Add(currBapId); sourceIds.Add(currSourceId); //--------------------------------------------------------------------- } } // If the last export row has not been saved then // save it now. if (!rowAdded && (!exportRow.IsNull(fieldMap[0][1]))) { exportTable.Rows.Add(exportRow); rowAdded = true; // Increment the output row count. outputRowCount += 1; } } //--------------------------------------------------------------------- // Commit any remaining outputs and update the export row count. exportRowCount += adapterOut.Update(datasetOut); // Turn notifications and index maintenance back on again. exportTable.EndLoadData(); //--------------------------------------------------------------------- // FIX: 054 Improve error reporting during exports. // // Exit if no records were exported. if (exportRowCount < 1) throw new Exception("Export query did not retrieve any rows"); return tempPath; //--------------------------------------------------------------------- } //--------------------------------------------------------------------- // FIX: 054 Improve error reporting during exports. // catch (Exception ex) { MessageBox.Show(String.Format("Export failed. The error message was:\n\n{0}.", ex.Message), "HLU: Export", MessageBoxButton.OK, MessageBoxImage.Error); // Delete the temporary database if it was created. if (File.Exists(tempPath)) { try { File.Delete(tempPath); } catch { _viewModelMain.ExportMdbs.Add(tempPath); } } // Return a null database path as the export didn't finish. return null; } //--------------------------------------------------------------------- finally { if ((dbOut != null) && (dbOut.Connection.State != ConnectionState.Closed)) { try { dbOut.Connection.Close(); } catch { } } } }
private static MDataColumn GetViewColumns(string sqlText, ref DbBase helper) { helper.OpenCon(null); helper.Com.CommandText = sqlText; DbDataReader sdr = helper.Com.ExecuteReader(CommandBehavior.KeyInfo); DataTable keyDt = null; if (sdr != null) { keyDt = sdr.GetSchemaTable(); sdr.Close(); } return GetColumns(keyDt); }
private static bool FillSchemaFromDb(ref MDataRow row, ref DbBase dbBase, string tableName, string sourceTableName) { try { MDataColumn mdcs = null; //if (tableName.IndexOf('(') > -1 && tableName.IndexOf(')') > -1)//�Զ�����ͼtable //{ // dbBase.tempSql = "view";//ʹ��access��ʽ������ //} mdcs = GetColumns(tableName, ref dbBase); if (mdcs.Count == 0) { return false; } row = mdcs.ToRow(sourceTableName); row.TableName = sourceTableName; string key = GetSchemaKey(tableName, dbBase.DataBase, dbBase.dalType); CacheManage.LocalInstance.Add(key, mdcs.Clone(), null, 1440); switch (dbBase.dalType)//�ı����ݿⲻ���档 { case DalType.Access: case DalType.SQLite: case DalType.MsSql: case DalType.MySql: case DalType.Oracle: if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath)) { string folderPath = AppDomain.CurrentDomain.BaseDirectory + AppConfig.DB.SchemaMapPath; if (System.IO.Directory.Exists(folderPath)) { mdcs.WriteSchema(folderPath + key + ".ts"); } } break; } return true; } catch (Exception err) { Log.WriteLogToTxt(err); return false; } }
private static bool FillSchemaFromCache(ref MDataRow row, ref DbBase dbBase, string tableName, string sourceTableName) { bool returnResult = false; string key = GetSchemaKey(tableName, dbBase.DataBase, dbBase.dalType); if (CacheManage.LocalInstance.Contains(key))//�������ȡ { try { row = ((MDataColumn)CacheManage.LocalInstance.Get(key)).ToRow(sourceTableName); returnResult = row.Count > 0; } catch (Exception err) { Log.WriteLogToTxt(err); } } else if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath)) { string fullPath = AppDomain.CurrentDomain.BaseDirectory + AppConfig.DB.SchemaMapPath + key + ".ts"; if (System.IO.File.Exists(fullPath)) { MDataColumn mdcs = MDataColumn.CreateFrom(fullPath); if (mdcs.Count > 0) { row = mdcs.ToRow(sourceTableName); returnResult = row.Count > 0; CacheManage.LocalInstance.Add(key, mdcs.Clone(), null, 1440); } } } return returnResult; }
// private static CacheManage _SchemaCache = CacheManage.Instance;//Cache���� internal static bool FillTableSchema(ref MDataRow row, ref DbBase dbBase, string tableName, string sourceTableName) { if (FillSchemaFromCache(ref row, ref dbBase, tableName, sourceTableName)) { return true; } else//��Cache����ʧ�� { return FillSchemaFromDb(ref row, ref dbBase, tableName, sourceTableName); } }
/// <summary> /// ��ȡ��������棩 /// </summary> public static Dictionary<string, string> GetTables(ref DbBase helper) { string key = "TableCache:" + helper.dalType + "." + helper.DataBase; if (tableCache.ContainsKey(key)) { return tableCache[key]; } helper.IsAllowRecordSql = false; string sql = string.Empty; Dictionary<string, string> tables = null; switch (helper.dalType) { case DalType.MsSql: sql = GetMSSQLTables(helper.Version.StartsWith("08")); break; case DalType.Oracle: sql = GetOracleTables(); break; case DalType.MySql: sql = GetMySqlTables(helper.DataBase); break; case DalType.Txt: case DalType.Xml: case DalType.Access: case DalType.SQLite: case DalType.Sybase: string restrict = "TABLE"; if (helper.dalType == DalType.Sybase) { restrict = "BASE " + restrict; } tables = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase); helper.Con.Open(); DataTable dt = helper.Con.GetSchema("Tables", new string[] { null, null, null, restrict }); helper.Con.Close(); if (dt != null && dt.Rows.Count > 0) { string tableName = string.Empty; foreach (DataRow row in dt.Rows) { tableName = Convert.ToString(row["TABLE_NAME"]); if (!tables.ContainsKey(tableName)) { tables.Add(tableName, string.Empty); } else { Log.WriteLogToTxt("Dictionary Has The Same TableName��" + tableName); } } dt = null; } return tables; } if (tables == null) { tables = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase); DbDataReader sdr = helper.ExeDataReader(sql, false); if (sdr != null) { string tableName = string.Empty; while (sdr.Read()) { tableName = Convert.ToString(sdr["TableName"]); if (!tables.ContainsKey(tableName)) { tables.Add(tableName, Convert.ToString(sdr["Description"])); } else { Log.WriteLogToTxt("Dictionary Has The Same TableName��" + tableName); } } sdr.Close(); sdr = null; } } if (!tableCache.ContainsKey(key)) { tableCache.Add(key, tables); } return tables; }
public static MDataColumn GetColumns(string tableName, ref DbBase dbHelper) { tableName = Convert.ToString(SqlCreate.SqlToViewSql(tableName)); DalType dalType = dbHelper.dalType; tableName = SqlFormat.Keyword(tableName, dbHelper.dalType); string key = GetSchemaKey(tableName, dbHelper.DataBase, dbHelper.dalType); if (CacheManage.LocalInstance.Contains(key))//�������ȡ { return CacheManage.LocalInstance.Get<MDataColumn>(key).Clone(); } switch (dalType) { case DalType.SQLite: case DalType.MySql: tableName = SqlFormat.NotKeyword(tableName); break; case DalType.Txt: case DalType.Xml: tableName = Path.GetFileNameWithoutExtension(tableName);//��ͼ�������.���ģ�������� string fileName = dbHelper.Con.DataSource + tableName + (dalType == DalType.Txt ? ".txt" : ".xml"); return MDataColumn.CreateFrom(fileName); } MDataColumn mdcs = new MDataColumn(); mdcs.dalType = dbHelper.dalType; //���table��helper����ͬһ���� DbBase helper = dbHelper.ResetDbBase(tableName); helper.IsAllowRecordSql = false;//�ڲ�ϵͳ������¼SQL��ṹ��䡣 try { bool isView = tableName.Contains(" ");//�Ƿ���ͼ�� if (!isView) { isView = Exists("V", tableName, ref helper); } MCellStruct mStruct = null; SqlDbType sqlType = SqlDbType.NVarChar; if (isView) { string sqlText = SqlFormat.BuildSqlWithWhereOneEqualsTow(tableName);// string.Format("select * from {0} where 1=2", tableName); mdcs = GetViewColumns(sqlText, ref helper); } else { mdcs.AddRelateionTableName(SqlFormat.NotKeyword(tableName)); switch (dalType) { case DalType.MsSql: case DalType.Oracle: case DalType.MySql: case DalType.Sybase: #region Sql string sql = string.Empty; if (dalType == DalType.MsSql) { string dbName = null; if (!helper.Version.StartsWith("08")) { //�Ȼ�ȡͬ��ʣ�����Ƿ��� string realTableName = Convert.ToString(helper.ExeScalar(string.Format(SynonymsName, SqlFormat.NotKeyword(tableName)), false)); if (!string.IsNullOrEmpty(realTableName)) { string[] items = realTableName.Split('.'); tableName = realTableName; if (items.Length > 0)//����� { dbName = realTableName.Split('.')[0]; } } } sql = GetMSSQLColumns(helper.Version.StartsWith("08"), dbName ?? helper.DataBase); } else if (dalType == DalType.MySql) { sql = GetMySqlColumns(helper.DataBase); } else if (dalType == DalType.Oracle) { sql = GetOracleColumns(); } else if (dalType == DalType.Sybase) { tableName = SqlFormat.NotKeyword(tableName); sql = GetSybaseColumns(); } helper.AddParameters("TableName", tableName, DbType.String, 150, ParameterDirection.Input); DbDataReader sdr = helper.ExeDataReader(sql, false); if (sdr != null) { long maxLength; bool isAutoIncrement = false; short scale = 0; string sqlTypeName = string.Empty; while (sdr.Read()) { short.TryParse(Convert.ToString(sdr["Scale"]), out scale); if (!long.TryParse(Convert.ToString(sdr["MaxSize"]), out maxLength))//mysql�ij��ȿ��ܴ���int.MaxValue { maxLength = -1; } else if (maxLength > int.MaxValue) { maxLength = int.MaxValue; } sqlTypeName = Convert.ToString(sdr["SqlType"]); sqlType = DataType.GetSqlType(sqlTypeName); isAutoIncrement = Convert.ToBoolean(sdr["IsAutoIncrement"]); mStruct = new MCellStruct(mdcs.dalType); mStruct.ColumnName = Convert.ToString(sdr["ColumnName"]).Trim(); mStruct.OldName = mStruct.ColumnName; mStruct.SqlType = sqlType; mStruct.IsAutoIncrement = isAutoIncrement; mStruct.IsCanNull = Convert.ToBoolean(sdr["IsNullable"]); mStruct.MaxSize = (int)maxLength; mStruct.Scale = scale; mStruct.Description = Convert.ToString(sdr["Description"]); mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, sdr["DefaultValue"], 0, sqlType); mStruct.IsPrimaryKey = Convert.ToString(sdr["IsPrimaryKey"]) == "1"; switch (dalType) { case DalType.MsSql: case DalType.MySql: case DalType.Oracle: mStruct.IsUniqueKey = Convert.ToString(sdr["IsUniqueKey"]) == "1"; mStruct.IsForeignKey = Convert.ToString(sdr["IsForeignKey"]) == "1"; mStruct.FKTableName = Convert.ToString(sdr["FKTableName"]); break; } mStruct.SqlTypeName = sqlTypeName; mStruct.TableName = SqlFormat.NotKeyword(tableName); mdcs.Add(mStruct); } sdr.Close(); if (dalType == DalType.Oracle && mdcs.Count > 0)//Ĭ��û���������ֻ�ܸ�������жϡ� { MCellStruct firstColumn = mdcs[0]; if (firstColumn.IsPrimaryKey && firstColumn.ColumnName.ToLower().Contains("id") && firstColumn.Scale == 0 && DataType.GetGroup(firstColumn.SqlType) == 1 && mdcs.JointPrimary.Count == 1) { firstColumn.IsAutoIncrement = true; } } } #endregion break; case DalType.SQLite: #region SQlite if (helper.Con.State != ConnectionState.Open) { helper.Con.Open(); } DataTable sqliteDt = helper.Con.GetSchema("Columns", new string[] { null, null, tableName }); if (!helper.isOpenTrans) { helper.Con.Close(); } int size; short sizeScale; string dataTypeName = string.Empty; foreach (DataRow row in sqliteDt.Rows) { object len = row["NUMERIC_PRECISION"]; if (len == null) { len = row["CHARACTER_MAXIMUM_LENGTH"]; } short.TryParse(Convert.ToString(row["NUMERIC_SCALE"]), out sizeScale); if (!int.TryParse(Convert.ToString(len), out size))//mysql�ij��ȿ��ܴ���int.MaxValue { size = -1; } dataTypeName = Convert.ToString(row["DATA_TYPE"]); if (dataTypeName == "text" && size > 0) { sqlType = DataType.GetSqlType("varchar"); } else { sqlType = DataType.GetSqlType(dataTypeName); } //COLUMN_NAME,DATA_TYPE,PRIMARY_KEY,IS_NULLABLE,CHARACTER_MAXIMUM_LENGTH AUTOINCREMENT mStruct = new MCellStruct(row["COLUMN_NAME"].ToString(), sqlType, Convert.ToBoolean(row["AUTOINCREMENT"]), Convert.ToBoolean(row["IS_NULLABLE"]), size); mStruct.Scale = sizeScale; mStruct.Description = Convert.ToString(row["DESCRIPTION"]); mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, row["COLUMN_DEFAULT"], 0, sqlType);//"COLUMN_DEFAULT" mStruct.IsPrimaryKey = Convert.ToBoolean(row["PRIMARY_KEY"]); mStruct.SqlTypeName = dataTypeName; mStruct.TableName = SqlFormat.NotKeyword(tableName); mdcs.Add(mStruct); } #endregion break; case DalType.Access: #region Access DataTable keyDt, valueDt; string sqlText = SqlFormat.BuildSqlWithWhereOneEqualsTow(tableName);// string.Format("select * from {0} where 1=2", tableName); OleDbConnection con = new OleDbConnection(helper.Con.ConnectionString); OleDbCommand com = new OleDbCommand(sqlText, con); con.Open(); keyDt = com.ExecuteReader(CommandBehavior.KeyInfo).GetSchemaTable(); valueDt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, SqlFormat.NotKeyword(tableName) }); con.Close(); con.Dispose(); if (keyDt != null && valueDt != null) { string columnName = string.Empty, sqlTypeName = string.Empty; bool isKey = false, isCanNull = true, isAutoIncrement = false; int maxSize = -1; short maxSizeScale = 0; SqlDbType sqlDbType; foreach (DataRow row in keyDt.Rows) { columnName = row["ColumnName"].ToString(); isKey = Convert.ToBoolean(row["IsKey"]);//IsKey isCanNull = Convert.ToBoolean(row["AllowDBNull"]);//AllowDBNull isAutoIncrement = Convert.ToBoolean(row["IsAutoIncrement"]); sqlTypeName = Convert.ToString(row["DataType"]); sqlDbType = DataType.GetSqlType(sqlTypeName); short.TryParse(Convert.ToString(row["NumericScale"]), out maxSizeScale); if (Convert.ToInt32(row["NumericPrecision"]) > 0)//NumericPrecision { maxSize = Convert.ToInt32(row["NumericPrecision"]); } else { long len = Convert.ToInt64(row["ColumnSize"]); if (len > int.MaxValue) { maxSize = int.MaxValue; } else { maxSize = (int)len; } } mStruct = new MCellStruct(columnName, sqlDbType, isAutoIncrement, isCanNull, maxSize); mStruct.Scale = maxSizeScale; mStruct.IsPrimaryKey = isKey; mStruct.SqlTypeName = sqlTypeName; mStruct.TableName = SqlFormat.NotKeyword(tableName); foreach (DataRow item in valueDt.Rows) { if (columnName == item[3].ToString())//COLUMN_NAME { if (item[8].ToString() != "") { mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, item[8], 0, sqlDbType);//"COLUMN_DEFAULT" } break; } } mdcs.Add(mStruct); } } #endregion break; } } helper.ClearParameters(); } catch (Exception err) { helper.debugInfo.Append(err.Message); } finally { helper.IsAllowRecordSql = true;//�ָ���¼SQL��ṹ��䡣 if (helper != dbHelper) { helper.Dispose(); } } if (mdcs.Count > 0) { //�Ƴ�����־���У� string[] fields = AppConfig.DB.HiddenFields.Split(','); foreach (string item in fields) { string field = item.Trim(); if (!string.IsNullOrEmpty(field) & mdcs.Contains(field)) { mdcs.Remove(field); } } } if (!CacheManage.LocalInstance.Contains(key)) { CacheManage.LocalInstance.Add(key, mdcs.Clone()); } return mdcs; }
private void SetDbBaseForTransaction() { if (mdt.DynamicData != null) { if (mdt.DynamicData is MProc) { _dalHelper = ((MProc)mdt.DynamicData).dalHelper; } else if (mdt.DynamicData is MAction) { _dalHelper = ((MAction)mdt.DynamicData).dalHelper; } } }
internal bool MySqlBulkCopyInsert(bool keepID) { bool fillGUID = CheckGUIDAndDateTime(DalType.MySql); string conn = DalCreate.FormatConn(DalType.MySql, AppConfig.GetConn(_Conn)); bool isNeedCreateDal = (_dalHelper == null); if (isNeedCreateDal) { _dalHelper = DalCreate.CreateDal(conn); _dalHelper.isAllowInterWriteLog = false; } string path = MDataTableToFile(mdt, fillGUID ? true : keepID); string sql = string.Format(SqlCreate.MySqlBulkCopySql, path, SqlFormat.Keyword(mdt.TableName, DalType.MySql), AppConst.SplitChar, SqlCreate.GetColumnName(mdt.Columns, keepID, DalType.MySql)); try { if (_dalHelper.ExeNonQuery(sql, false) != -2) { return true; } } catch (Exception err) { if (err.InnerException != null) { err = err.InnerException; } sourceTable.DynamicData = err; Log.WriteLogToTxt(err); } finally { if (isNeedCreateDal) { _dalHelper.Dispose(); _dalHelper = null; } // File.Delete(path); } return false; }
private static void GetViewColumns(string sqlText, MDataColumn mdcs, ref DbBase helper) { helper.OpenCon(null); helper.Com.CommandText = sqlText; DbDataReader sdr = helper.Com.ExecuteReader(CommandBehavior.KeyInfo); DataTable keyDt = null; if (sdr != null) { keyDt = sdr.GetSchemaTable(); sdr.Close(); } //helper.CloseCon(); if (keyDt != null && keyDt.Rows.Count > 0) { mdcs.isViewOwner = true; //DataColumnCollection dcList = helper.ExeDataTable(sqlText, false).Columns; string columnName = string.Empty, sqlTypeName = string.Empty, tableName = string.Empty; bool isKey = false, isCanNull = true, isAutoIncrement = false; int maxSize = -1; short maxSizeScale = 0; SqlDbType sqlDbType; string dataTypeName = "DataTypeName"; if (!keyDt.Columns.Contains(dataTypeName)) { dataTypeName = "DataType"; } bool isHasAutoIncrement = keyDt.Columns.Contains("IsAutoIncrement"); bool isHasHidden = keyDt.Columns.Contains("IsHidden"); foreach (DataRow row in keyDt.Rows) { if (isHasHidden && Convert.ToString(row["IsHidden"]) == "True")// !dcList.Contains(columnName)) { continue;//�����Ǹ����������ֶΡ� } columnName = row["ColumnName"].ToString(); isKey = Convert.ToBoolean(row["IsKey"]);//IsKey isCanNull = Convert.ToBoolean(row["AllowDBNull"]);//AllowDBNull if (isHasAutoIncrement) { isAutoIncrement = Convert.ToBoolean(row["IsAutoIncrement"]); } sqlTypeName = Convert.ToString(row[dataTypeName]); sqlDbType = DataType.GetSqlType(sqlTypeName); tableName = Convert.ToString(row["BaseTableName"]); if (short.TryParse(Convert.ToString(row["NumericScale"]), out maxSizeScale) && maxSizeScale == 255) { maxSizeScale = 0; } if (!int.TryParse(Convert.ToString(row["NumericPrecision"]), out maxSize) || maxSize == 255)//NumericPrecision { long len; if (long.TryParse(Convert.ToString(row["ColumnSize"]), out len)) { if (len > int.MaxValue) { maxSize = int.MaxValue; } else { maxSize = (int)len; } } } MCellStruct mStruct = new MCellStruct(columnName, sqlDbType, isAutoIncrement, isCanNull, maxSize); mStruct.Scale = maxSizeScale; mStruct.IsPrimaryKey = isKey; mStruct.SqlTypeName = sqlTypeName; mStruct.TableName = tableName; mStruct.OldName = mStruct.ColumnName; mdcs.Add(mStruct); } keyDt = null; } }
/// <summary> /// Builds a UNION query from a list of SqlFilterCondition. /// All constituent SELECT statement use the same target list and FROM clause but different WHERE clauses. /// </summary> /// <param name="targetList">Target list for union query. Same for each select query.</param> /// <param name="fromClause">From clause for union query. Same for each select query.</param> /// <param name="orderByOrdinal">Ordinal of column by which to order otput.</param> /// <param name="IncidSelectionWhereClause">List of where clauses from which to build UNION query. /// Input is assumed to be 0 based.</param> /// <param name="db">Database against which UNION query will be run.</param> /// <returns></returns> public static string UnionQuery(string targetList, string fromClause, int[] sortOrdinals, List<SqlFilterCondition> IncidSelectionWhereClause, DbBase db) { //--------------------------------------------------------------------- // CHANGED: CR43 (Sort multiple fields in exports) // // Add order by from list of sort ordinals. StringBuilder sql = new StringBuilder(); sql.Append(String.Format("SELECT {0} FROM {1}{2}", targetList, fromClause, db.WhereClause(true, true, true, IncidSelectionWhereClause))) .Append(sortOrdinals != null ? String.Format(" ORDER BY {0}", string.Join(", ", sortOrdinals.Select(x => x.ToString()).ToArray())) : String.Empty); //--------------------------------------------------------------------- return sql.ToString(); //return db.WhereClause(true, true, true, IncidSelectionWhereClause) // .Aggregate(new StringBuilder(), (sb, s) => sb.Append(String.Format( // "\nUNION\nSELECT {0} FROM {1}{2}", targetList, fromClause, s))).Remove(0, 7) // .Append(orderByOrdinal > 0 ? String.Format(" ORDER BY {0}", orderByOrdinal + 1) : String.Empty) // .ToString(); }
public static bool CreateScratchMdb(HluDataSet.incidDataTable incidTable, HluDataSet.incid_mm_polygonsDataTable incidMMTable) { try { _incidTable = incidTable; _incidMMTable = incidMMTable; _scratchMdbPath = String.Empty; try { _scratchMdbPath = Path.GetTempPath(); } catch { _scratchMdbPath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData); _scratchMdbPath += Path.DirectorySeparatorChar.ToString(); } _scratchMdbPath += Path.GetFileNameWithoutExtension(Path.GetRandomFileName()) + ".mdb"; OdbcCP32 odbc = new OdbcCP32(); odbc.CreateDatabase(_scratchMdbPath); string connString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};", _scratchMdbPath); string defaultSchema = ""; bool promptPwd = false; _scratchDb = new DbOleDb(ref connString, ref defaultSchema, ref promptPwd, Properties.Resources.PasswordMaskString, Settings.Default.UseAutomaticCommandBuilders, true, Settings.Default.DbIsUnicode, Settings.Default.DbUseTimeZone, 255, Settings.Default.DbBinaryLength, Settings.Default.DbTimePrecision, Settings.Default.DbNumericPrecision, Settings.Default.DbNumericScale); return true; } catch { if (File.Exists(_scratchMdbPath)) { try { if ((_scratchDb != null) && (_scratchDb.Connection.State != ConnectionState.Closed)) _scratchDb.Connection.Close(); File.Delete(_scratchMdbPath); } catch { } } return false; } }
public ViewModelWindowSelectQuery(HluDataSet hluDataset, DbBase hluDatabase) { HluDatasetStatic = hluDataset; _db = hluDatabase; }