/// <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;
}