using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using InsApp.word;
using InsApp.log4;
namespace InsApp.Linkdata
{
/// <summary>
/// static OleDbConnection CreateConn() 创建数据库联接
/// string[] GetSqlCmd_Array(string Db_Sql) 得到sql数据，返回数组
/// bool GetSqlCmd_bool(string Db_Sql) 接收sql语句，返回bool结果
/// string GetSqlCmd_String(string ReturnSql) 接受一个查询语句，返回一个需要的字段数据
/// void GetQryString(ref String fString, String column, String val, String Or_And, String exp)查询字符串组合 Where (....) 数据
/// </summary>
public class DBdata
{
InsApp.word.CreateCode Ck_word = new CreateCode();
Type type = System.Reflection.MethodBase.GetCurrentMethod().DeclaringType;
#region CreateConn 创建数据库联接
// 创建数据库联接
public static OleDbConnection CreateConn()
{
string Cstr = System.Configuration.ConfigurationManager.AppSettings["strConnection"];
string MdbPath = System.Configuration.ConfigurationManager.AppSettings["MdbPath"];
Cstr += System.Web.HttpContext.Current.Server.MapPath(MdbPath);
OleDbConnection Conn_Sql = new OleDbConnection(Cstr);
return Conn_Sql;
}
#endregion
#region 得到sql数据，返回数组
/// <summary>
/// 得到sql数据，返回数组
/// </summary>
/// <param name="Db_Sql">sql数据</param>
/// <returns></returns>
public string[] GetSqlCmd_Array(string Db_Sql)
{
string[] GetdataArray = null;
ArrayList myAL = new ArrayList();
OleDbConnection Conn = CreateConn();
Conn.Open();
OleDbCommand myCmd = new OleDbCommand(Db_Sql, Conn);
OleDbDataReader myReader = myCmd.ExecuteReader(CommandBehavior.CloseConnection);
try
{
if (myReader.HasRows)
{
while (myReader.Read())
{
for (int i = 0; i < myReader.FieldCount; i++)
{
myAL.Add(myReader[i].ToString());
}
}
GetdataArray = (string[])myAL.ToArray(typeof(string));
return GetdataArray;
}
else
{
return GetdataArray;
}
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
finally
{
myCmd.Dispose();
myReader.Close();
Conn.Close();
}
}
#endregion
#region 接收sql语句，返回bool结果
/// <summary>
/// 接收sql语句，返回bool结果
/// </summary>
/// <param name="Db_Sql">接收sql语句</param>
/// <returns></returns>
public bool GetSqlCmd_bool(string Db_Sql)
{
try
{
using (OleDbConnection Conn = CreateConn())
{
Conn.Open();
OleDbCommand myCmd = new OleDbCommand(Db_Sql, Conn);
myCmd.ExecuteNonQuery();
myCmd.Dispose();
Conn.Close();
return true;
}
}
catch (Exception ex)
{
LogUtil.ERROR(type, ex.Message);
return false;
}
}
#endregion
#region GetSqlCmd_String接受一个查询语句，返回一个需要的字段数据
/// <summary>
/// ReturnSql接受一个查询语句，返回一个需要的字段数据
/// 返回需要的字段 2006-11-24
/// </summary>
/// <param name="ReturnSql"></param>
/// <returns></returns>
public string GetSqlCmd_String(string ReturnSql)
{
string GetDate = string.Empty;
try
{
if (Ck_word.CheckNullstr(ReturnSql) == false)
{
throw new Exception("未找到该行参数");
}
else
{
using (OleDbConnection Conn = CreateConn())
{
OleDbCommand command = new OleDbCommand(ReturnSql, Conn);
Conn.Open();
OleDbDataReader M_reader = command.ExecuteReader();
if (M_reader.HasRows)
{
while (M_reader.Read())
{
GetDate += M_reader[0].ToString();
}
}
command.Dispose();
M_reader.Close();
Conn.Close();
}
return GetDate;
}
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}
#endregion
#region 查询字符串组合 Where (....) 数据
/// <summary>
/// 类型未String的查询参数组合
/// 使用方法：王新 time 2006-11-25
/// 首先需要一个完整的sql语句，例如select * from tablename
/// ref String fString一个空的字符串按照地址传入，返回后和sql语句合并
/// </summary>
/// <param name="fString">需要记录的字符串 可以是String,起止日期等于终止日期的date,decimal</param>
/// <param name="column">数据库字段</param>
/// <param name="val">参数,多个用";"隔开</param>
/// <param name="Or_And">Or || And 第一个表达式不需要</param>
/// <param name="exp">有效的表达式 例如:"like",">=d" ,"=c"等于</param>
public void GetQryString(ref String fString, String column, String val, String Or_And, String exp)
{
if (val != null && !val.Equals(""))
{
val = val.Trim();
if (!fString.Equals(""))
fString += " " + Or_And + " ";
else
fString += " Where ";
int i = 0;
foreach (String Str in val.Split(';'))
{
if (i++ > 0)
fString += " Or ";//里面循环规定必须是或者
else
fString += " (";
fString += String.Format(ReturnSQL(column, exp), Str);
if (i == val.Split(';').Length) fString += " )";
}
}
}
protected void GetQryString(ref String fString, String column, String val, String exp)
{
GetQryString(ref fString, column, val, "", exp);
}
/// <summary>
/// 时间段查询，接收日期参数
/// </summary>
/// <param name="fString"></param>
/// <param name="column"></param>
/// <param name="dt_start"></param>
/// <param name="dt_end"></param>
/// <param name="Or_And"></param>
public void GetQryDateTime(ref String fString, String column, string dt_start, string dt_end, String Or_And)
{
/*if (dt_start.StartsWith("1900-01-01 12:00:00")
&& dt_end.StartsWith("1900-01-01 12:00:00"))
return;
* //2006-12-11
*
*/
if (dt_start.StartsWith("1900-01-01") && dt_end.StartsWith("1900-01-01"))
return;
if (dt_start.StartsWith(dt_end.Substring(0, 10))) //(0,10) 10表示1900-01-01数据的长度
{
GetQryString(ref fString,
column,
dt_start.ToString(),
Or_And,
"date"
);
}
else
{
if (!fString.Equals(""))
fString += " " + Or_And + " ";
else
fString += " Where ";
fString += "("+ column + ">='" + dt_start + "' AND "+ column+ "<= '" + dt_end + "' )";
}
}
String ReturnSQL(String column, String exp)
{
string tempString = "";
exp = exp.Trim().ToLower();
switch (exp)
{
case "like":
case "not like":
tempString = " " + column + " " + exp + "'%{0}%'";
break;
case ">d":
case "<d":
case ">=d":
case "<=d":
case "=d":
case "<>d":
tempString = " " + column + " " + exp.Substring(0, exp.Length - 1) + "{0}";
break;
case "=c":
case "<>c":
tempString = " " + column + " " + exp.Substring(0, exp.Length - 1) + "'{0}'";
break;
case "monthdate":
tempString = " datediff(month," + column + ",'{0}')=0 ";
break;
case "date":
tempString = " datediff(d," + column + ",'{0}')=0 ";
break;
case "in":
case "not in":
tempString = " " + column + " " + exp + " ({0}) ";
break;
default:
break;
}
return tempString;
}
#endregion
}
}