/// <summary> /// 加载sql 语句 /// </summary> /// <returns>0 成功 -1 失败</returns> public int LoadSql() { switch (mode) { case 0://表示sql语句存放在xml文件中。 #region XML配置文件读取 System.Xml.Linq.XElement xeSqls = null; try { xeSqls = XElement.Load(sqlXmlFileName); } catch (Exception ex) { this.Err = ex.Message; this.ErrCode = "-1"; this.WriteErr(); return -1; } IEnumerable<XElement> sqlCollect = xeSqls.Elements("sql"); try { foreach (XElement xeSql in sqlCollect) { Mder.FC.Entities.BaseObject objValue = new Mder.FC.Entities.BaseObject(); objValue.ID = xeSql.Element("id").Value; objValue.Name = xeSql.Element("content").Value; objValue.Type = xeSql.Element("type").Value; objValue.Remark = xeSql.Element("comments").Value; //objValue.Name=objValue.Name.Replace("\n"," "); objValue.Name = objValue.Name.Replace("\t", " "); this.alSql.Add(objValue); } } catch (Exception ex) { this.Err = ex.Message; this.ErrCode = "-1"; this.WriteErr(); return -1; } #endregion break; default: for (int i = 0; i < table_name.Count; i++) { Mder.FC.Entities.BaseObject obj = table_name[i] as Mder.FC.Entities.BaseObject; if (obj.ID == "1") //开始时候加载 { //因为要增加对不同数据库的支持,不同数据库里的SQL语句存储的字段不同, 所以才这样 //{476364C9-195A-4ca8-A2D7-6782088016FA} string mySQL = string.Empty; if (Mder.FC.DB.Conn.Instance.GetType() == typeof(System.Data.OracleClient.OracleConnection)) { mySQL = string.Format("select id,name,memo from {0}", table_name[i].ToString()); } else//以后再用 { mySQL = string.Format("select id,name,memo from {0}", table_name[i].ToString()); } if (this.Query(mySQL) == -1) { return -1;//表有问题 } try { while (this.Reader.Read()) { Mder.FC.Entities.BaseObject objValue = new Mder.FC.Entities.BaseObject(); objValue.ID = this.Reader[0].ToString(); objValue.Name = this.Reader[1].ToString(); objValue.Name = objValue.Name.Replace("\r", " "); objValue.Name = objValue.Name.Replace("\t", " "); try { objValue.Remark = this.Reader[2].ToString(); } catch { } //this.alSql.Add(objValue); this.hsSqlValue.Add(objValue.ID, objValue.Name); } } catch { } finally { this.Reader.Close(); } } } break; } return 0; }
/// <summary> /// 获取存放SQL语句的表信息, /// </summary> /// <returns></returns> private int GetSQLTable() { string strSql = "SELECT TABLE_NAME,IS_BEGIN_LOAD FROM COMM_SQL_SETTING "; //取加载的SQL语句 if (this.Query(strSql) == -1) { //没有这个表,只取COMM_SQL table_name.Add(new Mder.FC.Entities.BaseObject("1", "COMM_SQL", "")); return -1; } try { while (this.Reader.Read()) { Mder.FC.Entities.BaseObject obj = new Mder.FC.Entities.BaseObject(); obj.ID = this.Reader[1].ToString(); obj.Name = this.Reader[0].ToString(); table_name.Add(obj); } } catch (Exception ex) { this.Err = ex.Message; } finally { this.Reader.Close(); } return 0; }
/// <summary> /// 获得sql语句 /// </summary> /// <param name="index"></param> /// <param name="Sql"></param> /// <returns></returns> public int GetSql(string index, ref string Sql) { for (int i = 0; i < this.alSql.Count; i++) { if (((Mder.FC.Entities.BaseObject)this.alSql[i]).ID.Trim() == index.Trim()) { Sql = ((Mder.FC.Entities.BaseObject)this.alSql[i]).Name; this.Err = "获得Sql语句,索引为:" + index + "\n Sql为:" + Sql; this.WriteDebug(this.Err); return 0; } } //HashTable 方式读取 if (this.hsSqlValue.ContainsKey(index)) { Sql = this.hsSqlValue[index].ToString(); this.Err = "获得Sql语句,索引为:" + index + "\n Sql为:" + Sql; this.WriteDebug(this.Err); return 0; } for (int i = 0; i < table_name.Count; i++) { Mder.FC.Entities.BaseObject obj = table_name[i] as Mder.FC.Entities.BaseObject; if (obj.ID == "0")//开始时候加载 { //因为要增加对不同数据库的支持,不同数据库里的SQL语句存储的字段不同, 所以才这样 //{844EC201-D874-4d1e-B2B3-DBC61DA21599} //string mySQL = string.Format("select id,name,memo from {0} where id='{1}'", table_name[i].ToString(), index);//原来的程序 string mySQL = string.Empty; if (Mder.FC.DB.Conn.Instance.GetType() == typeof(System.Data.OracleClient.OracleConnection)) { mySQL = string.Format("select id,name,memo from {0} where id='{1}'", table_name[i].ToString(), index); } else//以后再用 { mySQL = string.Format("select id,name,memo from {0} where id='{1}'", table_name[i].ToString(), index); } //end ; if (this.Query(mySQL) == -1) { return -1;//表有问题 } if (this.Reader.Read()) { Mder.FC.Entities.BaseObject objValue = new Mder.FC.Entities.BaseObject(); objValue.ID = this.Reader[0].ToString(); objValue.Name = this.Reader[1].ToString(); objValue.Name = objValue.Name.Replace("\r", " "); objValue.Name = objValue.Name.Replace("\t", " "); try { objValue.Remark = this.Reader[2].ToString(); } catch { } //this.alSql.Add(objValue); this.hsSqlValue.Add(objValue.ID, objValue.Name); Sql = objValue.Name; this.Err = "获得Sql语句,索引为:" + index + "\n Sql为:" + Sql; this.WriteDebug(this.Err); this.Reader.Close(); return 0; } else { this.Reader.Close(); } } } this.Err = "没找到Sql语句!" + index; this.WriteErr(); return -1; }