2140 lines
82 KiB
C#
2140 lines
82 KiB
C#
|
using System.Collections.Generic;
|
|||
|
|
using System.Collections;
|
|||
|
|
using System.ComponentModel;
|
|||
|
|
using System.Linq;
|
|||
|
|
using System.Text;
|
|||
|
|
using System.Drawing;
|
|||
|
|
using System;
|
|||
|
|
using System.IO;
|
|||
|
|
using System.Xml;
|
|||
|
|
using System.Data.OleDb;
|
|||
|
|
using System.Globalization;
|
|||
|
|
using System.Data.OracleClient;
|
|||
|
|
using System.Data;
|
|||
|
|
using Npgsql;
|
|||
|
|
|
|||
|
|
namespace CRVM.Utility
|
|||
|
|
{
|
|||
|
|
#region enumclass
|
|||
|
|
public enum ShowStyle
|
|||
|
|
{ horizontal, vertical }//, none
|
|||
|
|
public enum DataBaseType
|
|||
|
|
{ DB2, Oracle, other }
|
|||
|
|
public enum BehaviorState
|
|||
|
|
{ Add, Edit, none }
|
|||
|
|
public enum DataState
|
|||
|
|
{ ViewData, TableData }
|
|||
|
|
public enum RecordsetState
|
|||
|
|
{ name, sql }
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
[TypeConverter(typeof(ConnStringConverter))]
|
|||
|
|
public class ConnString
|
|||
|
|
{
|
|||
|
|
#region Define
|
|||
|
|
private string provider = "";
|
|||
|
|
private string dataSource = "";
|
|||
|
|
private string locationID = "";
|
|||
|
|
private string userId = "";
|
|||
|
|
private string password = "";
|
|||
|
|
private DataBaseType dBType = DataBaseType.DB2;
|
|||
|
|
#endregion
|
|||
|
|
public string Password
|
|||
|
|
{
|
|||
|
|
get
|
|||
|
|
{
|
|||
|
|
return password;
|
|||
|
|
}
|
|||
|
|
set
|
|||
|
|
{
|
|||
|
|
password = value;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
public string Provider
|
|||
|
|
{
|
|||
|
|
get
|
|||
|
|
{
|
|||
|
|
return provider;
|
|||
|
|
}
|
|||
|
|
set
|
|||
|
|
{
|
|||
|
|
this.provider = value;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
public string DataSource
|
|||
|
|
{
|
|||
|
|
get
|
|||
|
|
{
|
|||
|
|
return dataSource;
|
|||
|
|
}
|
|||
|
|
set
|
|||
|
|
{
|
|||
|
|
this.dataSource = value;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
public string LocationID
|
|||
|
|
{
|
|||
|
|
get
|
|||
|
|
{
|
|||
|
|
return locationID;
|
|||
|
|
}
|
|||
|
|
set
|
|||
|
|
{
|
|||
|
|
this.locationID = value;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
public string UserId
|
|||
|
|
{
|
|||
|
|
get
|
|||
|
|
{
|
|||
|
|
return userId;
|
|||
|
|
}
|
|||
|
|
set
|
|||
|
|
{
|
|||
|
|
this.userId = value;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
public DataBaseType DBType
|
|||
|
|
{
|
|||
|
|
get { return dBType; }
|
|||
|
|
set { dBType = value; }
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 字符类格式转换
|
|||
|
|
/// </summary>
|
|||
|
|
internal class ConnStringConverter : TypeConverter
|
|||
|
|
{
|
|||
|
|
public override PropertyDescriptorCollection
|
|||
|
|
GetProperties(ITypeDescriptorContext context, object value, Attribute[] filter)
|
|||
|
|
{
|
|||
|
|
return TypeDescriptor.GetProperties(value, filter);
|
|||
|
|
}
|
|||
|
|
public override bool GetPropertiesSupported(ITypeDescriptorContext context)
|
|||
|
|
{
|
|||
|
|
return true;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal class CanConnStringConverter : ExpandableObjectConverter
|
|||
|
|
{
|
|||
|
|
public override bool CanConvertFrom(ITypeDescriptorContext context, Type t)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
if (t == typeof(string))
|
|||
|
|
{
|
|||
|
|
return true;
|
|||
|
|
}
|
|||
|
|
return base.CanConvertFrom(context, t);
|
|||
|
|
}
|
|||
|
|
public override object ConvertFrom(ITypeDescriptorContext context, CultureInfo info, object value)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
if (value is string)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string s = (string)value;
|
|||
|
|
string[] Strarray = s.Split(',');
|
|||
|
|
ConnString p = new ConnString();
|
|||
|
|
p.LocationID = Strarray[0];
|
|||
|
|
p.DataSource = Strarray[1];
|
|||
|
|
p.Provider = Strarray[2];
|
|||
|
|
p.UserId = Strarray[3];
|
|||
|
|
p.Password = Strarray[4];
|
|||
|
|
return p;
|
|||
|
|
}
|
|||
|
|
catch
|
|||
|
|
{
|
|||
|
|
//throw;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//throw new ArgumentException("Can not convert '" + (string)value + "' to type ConnString");
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
return base.ConvertFrom(context, info, value);
|
|||
|
|
}
|
|||
|
|
public override object ConvertTo(ITypeDescriptorContext context, CultureInfo culture, object value, Type destType)
|
|||
|
|
{
|
|||
|
|
if (destType == typeof(string) && value is ConnString)
|
|||
|
|
{
|
|||
|
|
ConnString p = (ConnString)value;
|
|||
|
|
|
|||
|
|
return p.LocationID + "," + p.DataSource + "," + p.Provider + "," + p.UserId + "," + p.Password;
|
|||
|
|
}
|
|||
|
|
return base.ConvertTo(context, culture, value, destType);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public class Model
|
|||
|
|
{
|
|||
|
|
private static Model _instance;
|
|||
|
|
public static Model Instance
|
|||
|
|
{
|
|||
|
|
get
|
|||
|
|
{
|
|||
|
|
if (_instance == null)
|
|||
|
|
{ _instance = new Model(); }
|
|||
|
|
return _instance;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 从Recordset中取出表名
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="recordset">Recordset属性</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public static string SplitRecordset(string recordset)
|
|||
|
|
{
|
|||
|
|
string r_ecordset = "";
|
|||
|
|
if (recordset.Contains("from"))
|
|||
|
|
{
|
|||
|
|
r_ecordset = SplitSQL(recordset);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
r_ecordset = recordset;
|
|||
|
|
}
|
|||
|
|
return r_ecordset;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 拆分SQL语句，取出表名
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="SQL">SQL语句</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public static string SplitSQL(string aSQL)
|
|||
|
|
{
|
|||
|
|
string SQL = aSQL.ToLower(CultureInfo.InvariantCulture);
|
|||
|
|
string tablename = "";
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
//if (!SQL.Contains("select"))
|
|||
|
|
//{
|
|||
|
|
// throw new SQLException("SQL语句中必须为包含select");
|
|||
|
|
//}
|
|||
|
|
//else
|
|||
|
|
//{
|
|||
|
|
if (SQL.Contains("insert"))
|
|||
|
|
{
|
|||
|
|
int p1 = SQL.IndexOf("into");
|
|||
|
|
|
|||
|
|
if (SQL.Contains('('))
|
|||
|
|
{
|
|||
|
|
int p2 = SQL.IndexOf('(');
|
|||
|
|
tablename = SQL.Substring(p1 + 4, p2 - p1 - 4).Trim();
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
int p3 = SQL.IndexOf("select");
|
|||
|
|
tablename = SQL.Substring(p1 + 4, p3 - p1 - 4).Trim();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
else if (SQL.Contains("update"))
|
|||
|
|
{
|
|||
|
|
int p4 = SQL.IndexOf("update");
|
|||
|
|
int p5 = SQL.IndexOf("set");
|
|||
|
|
tablename = SQL.Substring(p4 + 6, p5 - p4 - 6).Trim();
|
|||
|
|
}
|
|||
|
|
else if (SQL.Contains("delete"))
|
|||
|
|
{
|
|||
|
|
int p6 = SQL.IndexOf("from");
|
|||
|
|
if (SQL.Contains("where"))
|
|||
|
|
{
|
|||
|
|
int p7 = SQL.IndexOf("where");
|
|||
|
|
tablename = SQL.Substring(p6 + 4, p7 - p6 - 4).Trim();
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
tablename = SQL.Substring(p6 + 4, SQL.Length - p6 - 4).Trim();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
int p8 = SQL.IndexOf("from");
|
|||
|
|
if (SQL.Contains("where"))
|
|||
|
|
{
|
|||
|
|
int p9 = SQL.IndexOf("where");
|
|||
|
|
tablename = SQL.Substring(p8 + 4, p9 - p8 - 4).Trim();
|
|||
|
|
}
|
|||
|
|
else if (SQL.Contains("order") && !SQL.Contains("where"))
|
|||
|
|
{
|
|||
|
|
int p10 = SQL.IndexOf("order");
|
|||
|
|
tablename = SQL.Substring(p8 + 4, p10 - p8 - 4).Trim();
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
tablename = SQL.Substring(p8 + 4, SQL.Length - p8 - 4).Trim();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
// }
|
|||
|
|
//catch (SQLException e)
|
|||
|
|
//{
|
|||
|
|
// MessageBox.Show(e.Message);
|
|||
|
|
//}
|
|||
|
|
catch (Exception)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
//MessageBox.Show(e.Message);
|
|||
|
|
}
|
|||
|
|
return tablename;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 表格数据显示转换
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="dt">表格数据</param>
|
|||
|
|
/// <param name="style">显示方式</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public static DataTable TransFormDataTable(DataTable dt, ShowStyle style)
|
|||
|
|
{
|
|||
|
|
DataTable dResult = new DataTable();
|
|||
|
|
if (style == ShowStyle.horizontal)
|
|||
|
|
{
|
|||
|
|
dResult.Columns.Add("Head");
|
|||
|
|
for (int i = 1; i <= dt.Rows.Count; i++)
|
|||
|
|
{
|
|||
|
|
dResult.Columns.Add("Data" + i.ToString());
|
|||
|
|
}
|
|||
|
|
for (int j = 0; j <= dt.Columns.Count - 1; j++)
|
|||
|
|
{
|
|||
|
|
DataRow dr = dResult.NewRow();
|
|||
|
|
dr["Head"] = dt.Columns[j].ColumnName;
|
|||
|
|
for (int n = 1; n <= dt.Rows.Count; n++)
|
|||
|
|
{
|
|||
|
|
dr["Data" + n.ToString()] = Convert.ToString(dt.Rows[n - 1][j]);
|
|||
|
|
}
|
|||
|
|
dResult.Rows.Add(dr);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
dResult = dt;
|
|||
|
|
}
|
|||
|
|
return dResult;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 更新SQL语句获取
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="tablename">表名</param>
|
|||
|
|
/// <param name="index">记录索引，水平行索引，垂直列索引</param>
|
|||
|
|
/// <param name="viewdtindex">数据库数据源水平列数，垂直行数</param>
|
|||
|
|
/// <param name="viewdt">数据源</param>
|
|||
|
|
/// <param name="colInfoDt">数据库表字段信息</param>
|
|||
|
|
/// <param name="name_value">编辑记录</param>
|
|||
|
|
/// <param name="EditPriColname_Colvalue">编辑前记录</param>
|
|||
|
|
/// <param name="style">显示方式</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public string UpdateInsertSQL(string tablename, int index, BehaviorState Bstate, DataTable viewdt, DataTable colInfoDt, string[] EditPriColname_Colvalue, ShowStyle style, DataBaseType dbtype)
|
|||
|
|
{
|
|||
|
|
string sql = "";
|
|||
|
|
string[] name_value = new string[2];
|
|||
|
|
|
|||
|
|
name_value = GetColNameColValue(viewdt, index, style);
|
|||
|
|
if (Bstate == BehaviorState.Add)
|
|||
|
|
{
|
|||
|
|
if (!UpdateInsertKeyValueCheckRepeat(viewdt, index, colInfoDt, style))
|
|||
|
|
{
|
|||
|
|
sql = InsertStr(name_value, tablename, colInfoDt, dbtype);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
sql = UpdateStr(name_value, EditPriColname_Colvalue, tablename, colInfoDt, dbtype);
|
|||
|
|
}
|
|||
|
|
return sql;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// Insert操作SQL获取
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="name_value"></param>
|
|||
|
|
/// <param name="tablename"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
private string InsertStr(string[] name_value, string tablename, DataTable colInfo, DataBaseType dbtype)
|
|||
|
|
{
|
|||
|
|
string sql = "";
|
|||
|
|
if (name_value[0] != null)
|
|||
|
|
{
|
|||
|
|
if (name_value[0].Contains(',') && name_value[1].Contains(','))
|
|||
|
|
{
|
|||
|
|
string InsertValue = "";
|
|||
|
|
string InsertName = "";
|
|||
|
|
string[] name = name_value[0].Split(',');
|
|||
|
|
string[] value = name_value[1].Split(',');
|
|||
|
|
if (name.Length == value.Length)
|
|||
|
|
{
|
|||
|
|
for (int i = 0; i < value.Length; i++)
|
|||
|
|
{
|
|||
|
|
if (value[i].ToString() != "''")
|
|||
|
|
{
|
|||
|
|
if (dbtype == DataBaseType.Oracle)
|
|||
|
|
{
|
|||
|
|
InsertName += name[i] + ",";
|
|||
|
|
for (int j = 0; j < colInfo.Rows.Count; j++)
|
|||
|
|
{
|
|||
|
|
if (name[i].ToString() == colInfo.Rows[j][0].ToString())
|
|||
|
|
{
|
|||
|
|
if (colInfo.Rows[j][1].ToString() == "DATE")
|
|||
|
|
{
|
|||
|
|
InsertValue += "to_date(" + value[i] + ",'yyyy-mm-dd hh24:mi:ss'),";
|
|||
|
|
break;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
InsertValue += value[i] + ","; break;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
InsertValue += value[i] + ",";
|
|||
|
|
InsertName += name[i] + ",";
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
InsertName = InsertName.Substring(0, InsertName.Length - 1);
|
|||
|
|
InsertValue = InsertValue.Substring(0, InsertValue.Length - 1);
|
|||
|
|
}
|
|||
|
|
sql = "insert into " + tablename + "(" + InsertName + ") values (" + InsertValue + ")";
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
sql = "insert into " + tablename + "(" + name_value[0] + ") values (" + name_value[1] + ")";
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
return sql;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// Update操作SQL获取
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="name_value"></param>
|
|||
|
|
/// <param name="EditPriColname_Colvalue"></param>
|
|||
|
|
/// <param name="tablename"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
private string UpdateStr(string[] name_value, string[] EditPriColname_Colvalue, string tablename, DataTable colInfo, DataBaseType dbtype)
|
|||
|
|
{
|
|||
|
|
string sql = "";
|
|||
|
|
if (name_value[0] != null && EditPriColname_Colvalue[0] != null)
|
|||
|
|
{
|
|||
|
|
if (name_value[1].Contains(',') && EditPriColname_Colvalue[1].Contains(','))
|
|||
|
|
{
|
|||
|
|
string[] Editingvalue = name_value[1].Split(',');
|
|||
|
|
string[] EditPrivalue = EditPriColname_Colvalue[1].Split(',');
|
|||
|
|
string[] ColName = name_value[0].Split(',');
|
|||
|
|
if (Editingvalue.Length == EditPrivalue.Length)
|
|||
|
|
{
|
|||
|
|
string set_value = "";
|
|||
|
|
string where_value = "";
|
|||
|
|
for (int i = 0; i < EditPrivalue.Length; i++)
|
|||
|
|
{
|
|||
|
|
if (dbtype == DataBaseType.Oracle)
|
|||
|
|
{
|
|||
|
|
if (!Editingvalue[i].Equals(EditPrivalue[i]))
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
for (int n = 0; n < colInfo.Rows.Count; n++)
|
|||
|
|
{
|
|||
|
|
if (colInfo.Rows[n][0].ToString() == ColName[i])
|
|||
|
|
{
|
|||
|
|
if (colInfo.Rows[n][1].ToString() == "DATE")
|
|||
|
|
{
|
|||
|
|
set_value += ColName[i] + "=to_date(" + Editingvalue[i] + ",'yyyy-mm-dd hh24:mi:ss'),"; break;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
set_value += ColName[i] + "=" + Editingvalue[i] + ","; break;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
if (EditPrivalue[i].ToString() != "''")
|
|||
|
|
{
|
|||
|
|
for (int m = 0; m < colInfo.Rows.Count; m++)
|
|||
|
|
{
|
|||
|
|
if (colInfo.Rows[m][0].ToString() == ColName[i])
|
|||
|
|
{
|
|||
|
|
if (colInfo.Rows[m][1].ToString() == "DATE")
|
|||
|
|
{
|
|||
|
|
where_value += ColName[i] + "=to_date(" + EditPrivalue[i] + ",'yyyy-mm-dd hh24:mi:ss') and ";
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
where_value += ColName[i] + "=" + EditPrivalue[i] + " and ";
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
if (!Editingvalue[i].Equals(EditPrivalue[i]))
|
|||
|
|
{
|
|||
|
|
set_value += ColName[i] + "=" + Editingvalue[i] + ",";
|
|||
|
|
}
|
|||
|
|
if (EditPrivalue[i].ToString() != "''")
|
|||
|
|
{
|
|||
|
|
where_value += ColName[i] + "=" + EditPrivalue[i] + " and ";
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
if (set_value.Length > 0 && where_value.Length > 0)
|
|||
|
|
{
|
|||
|
|
set_value = set_value.Substring(0, set_value.Length - 1);
|
|||
|
|
where_value = where_value.Substring(0, where_value.Length - 4).Trim();
|
|||
|
|
sql = "update " + tablename + " set " + set_value + " where " + where_value;
|
|||
|
|
}
|
|||
|
|
else if (set_value.Length > 0 && where_value.Length < 0)
|
|||
|
|
{
|
|||
|
|
set_value = set_value.Substring(0, set_value.Length - 1);
|
|||
|
|
where_value = ColName[0] + "=" + EditPrivalue[0];
|
|||
|
|
sql = "update " + tablename + " set " + set_value + " where " + where_value;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
sql = "Not Edit";
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
sql = "update " + tablename + " set " + name_value[0] + "=" + name_value[1] + " where " + EditPriColname_Colvalue[0] + "=" + EditPriColname_Colvalue[1];
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
return sql;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 当前记录信息获取，长度为2的字符串数组，分别为所有字段的字符串和所有数据值的字符串
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="ViewDt">数据源</param>
|
|||
|
|
/// <param name="rcex">选中行索引（垂直显示），选中列索引（水平显示）</param>
|
|||
|
|
/// <param name="style">数据显示方式</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public string[] GetColNameColValue(DataTable ViewDt, int rcex, ShowStyle style)
|
|||
|
|
{
|
|||
|
|
string cname = "";
|
|||
|
|
string cvalue = "";
|
|||
|
|
string[] Name_Value = new string[2];
|
|||
|
|
if (style == ShowStyle.horizontal)
|
|||
|
|
{
|
|||
|
|
if (rcex > 0)
|
|||
|
|
{
|
|||
|
|
for (int i = 0; i < ViewDt.Rows.Count; i++)
|
|||
|
|
{
|
|||
|
|
cname += ViewDt.Rows[i][0].ToString() + ",";
|
|||
|
|
cvalue += "'" + ViewDt.Rows[i][rcex].ToString() + "',";
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
for (int i = 0; i < ViewDt.Columns.Count; i++)
|
|||
|
|
{
|
|||
|
|
cname += ViewDt.Columns[i].ColumnName + ",";
|
|||
|
|
cvalue += "'" + ViewDt.Rows[rcex][i].ToString() + "',";
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
if (cname != "" && cvalue != "")
|
|||
|
|
{
|
|||
|
|
Name_Value[0] = cname.Substring(0, cname.Length - 1);
|
|||
|
|
Name_Value[1] = cvalue.Substring(0, cvalue.Length - 1);
|
|||
|
|
}
|
|||
|
|
return Name_Value;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 删除SQL语句获取
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="table_name">表名</param>
|
|||
|
|
/// <param name="rcex">记录中当前单元格的列索引</param>
|
|||
|
|
/// <param name="Ddata">DataTable数据源</param>
|
|||
|
|
/// <param name="style">数据源显示方式</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public string DeleteSQL(string table_name, int rcex, DataTable Ddata, ShowStyle style)
|
|||
|
|
{
|
|||
|
|
string ModName = "";
|
|||
|
|
string ModValue = "";
|
|||
|
|
string Dsql = "";
|
|||
|
|
int Dex = -1;
|
|||
|
|
StringBuilder StrB = new StringBuilder();
|
|||
|
|
if (style == ShowStyle.horizontal)
|
|||
|
|
{
|
|||
|
|
for (int i = 0; i < Ddata.Rows.Count; i++)
|
|||
|
|
{
|
|||
|
|
if (Ddata.Rows[i][rcex].ToString().Length > 0)
|
|||
|
|
{
|
|||
|
|
Dex = i; break;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
if (Dex > -1)
|
|||
|
|
{
|
|||
|
|
ModName = Convert.ToString(Ddata.Rows[Dex][0]);
|
|||
|
|
ModValue = Convert.ToString(Ddata.Rows[Dex][rcex]);
|
|||
|
|
Dsql = "delete from " + table_name + " where " + ModName + "='" + ModValue + "'";
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
for (int t1 = 0; t1 < Ddata.Rows.Count; t1++)
|
|||
|
|
{
|
|||
|
|
StrB.Append(Convert.ToString(Ddata.Rows[t1][0]) + " is null and ");
|
|||
|
|
}
|
|||
|
|
Dsql = "delete from " + table_name + " where " + StrB.Remove(StrB.Length - 4, 4);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
for (int n = 0; n < Ddata.Columns.Count; n++)
|
|||
|
|
{
|
|||
|
|
if (Convert.ToString(Ddata.Rows[rcex][n]).Length > 0)
|
|||
|
|
{
|
|||
|
|
Dex = n; break;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
if (Dex > -1)
|
|||
|
|
{
|
|||
|
|
ModName = Ddata.Columns[Dex].ColumnName;
|
|||
|
|
ModValue = Convert.ToString(Ddata.Rows[rcex][Dex]);
|
|||
|
|
Dsql = "delete from " + table_name + " where " + ModName + "='" + ModValue + "'";
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
for (int t3 = 0; t3 < Ddata.Columns.Count; t3++)
|
|||
|
|
{
|
|||
|
|
StrB.Append(Convert.ToString(Ddata.Columns[t3].ColumnName) + " is null and ");
|
|||
|
|
}
|
|||
|
|
Dsql = "delete from " + table_name + " where " + StrB.Remove(StrB.Length - 4, 4);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
return Dsql;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 编辑记录时检查记录中的主键值是否与原有主键值重复，true重复，false不重复
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="ViewDt">表中数据信息</param>
|
|||
|
|
/// <param name="rcex">记录所在的索引(水平显示为记录列索引cex)，(垂直显示为记录行索引rex)</param>
|
|||
|
|
/// <param name="ColInfoDt">表中字段信息，字段名，类型，非空，键值</param>
|
|||
|
|
/// <param name="style">显示方式</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
private bool UpdateInsertKeyValueCheckRepeat(DataTable ViewDt, int rcex, DataTable ColInfoDt, ShowStyle style)
|
|||
|
|
{
|
|||
|
|
string[,] colInfoArray = GetColInfoArray(ColInfoDt);
|
|||
|
|
string KeyColName = "";//主键字段名
|
|||
|
|
System.Collections.ArrayList KeyColValue = new ArrayList();
|
|||
|
|
string UpdateInsertValue = "";
|
|||
|
|
for (int i = 0; i < colInfoArray.GetLength(0); i++)
|
|||
|
|
{
|
|||
|
|
if (colInfoArray[i, 2].Length > 0)
|
|||
|
|
{
|
|||
|
|
KeyColName += colInfoArray[i, 0].ToString() + ",";
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
if (KeyColName != "")
|
|||
|
|
{
|
|||
|
|
if (KeyColName.Contains(','))
|
|||
|
|
{
|
|||
|
|
string[] keyname = KeyColName.Split(',');
|
|||
|
|
|
|||
|
|
if (keyname.Length > 0)
|
|||
|
|
{
|
|||
|
|
if (style == ShowStyle.horizontal)
|
|||
|
|
{
|
|||
|
|
List<int> index = new List<int>();//水平显示主键行索引
|
|||
|
|
for (int j = 0; j < keyname.Length - 1; j++)
|
|||
|
|
{
|
|||
|
|
for (int i = 0; i < ViewDt.Rows.Count; i++)
|
|||
|
|
{
|
|||
|
|
if (keyname[j].Equals(ViewDt.Rows[i][0]))
|
|||
|
|
{
|
|||
|
|
index.Add(i);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
for (int t = 1; t < ViewDt.Columns.Count; t++)//水平显示所有列主键元素组合串数组
|
|||
|
|
{
|
|||
|
|
string value = "";
|
|||
|
|
for (int m = 0; m < index.Count; m++)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
value += ViewDt.Rows[index[m]][t].ToString() + ";";
|
|||
|
|
}
|
|||
|
|
if (t != rcex)
|
|||
|
|
{
|
|||
|
|
KeyColValue.Add(value.Substring(0, value.Length - 1));
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
UpdateInsertValue = value.Substring(0, value.Length - 1);//更新插入主键组合串
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
if (KeyColValue.Count > 0)
|
|||
|
|
{
|
|||
|
|
for (int i = 0; i < KeyColValue.Count; i++)
|
|||
|
|
{
|
|||
|
|
if (KeyColValue[i].Equals(UpdateInsertValue))
|
|||
|
|
{
|
|||
|
|
return true;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
else//第2种显示风格垂直
|
|||
|
|
{
|
|||
|
|
List<int> index = new List<int>();
|
|||
|
|
for (int j = 0; j < keyname.Length - 1; j++)
|
|||
|
|
{
|
|||
|
|
for (int i = 0; i < ViewDt.Columns.Count; i++)
|
|||
|
|
{
|
|||
|
|
if (keyname[j].Equals(ViewDt.Columns[i].ColumnName))
|
|||
|
|
{
|
|||
|
|
index.Add(i);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
for (int t = 0; t < ViewDt.Rows.Count; t++)//none垂直显示所有行主键元素组合串数组
|
|||
|
|
{
|
|||
|
|
string value = "";
|
|||
|
|
for (int m = 0; m < index.Count; m++)
|
|||
|
|
{
|
|||
|
|
value += ViewDt.Rows[t][index[m]].ToString() + ";";
|
|||
|
|
}
|
|||
|
|
if (t != rcex)
|
|||
|
|
{
|
|||
|
|
KeyColValue.Add(value.Substring(0, value.Length - 1));
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
UpdateInsertValue = value.Substring(0, value.Length - 1);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
if (KeyColValue.Count > 0)
|
|||
|
|
{
|
|||
|
|
for (int i = 0; i < KeyColValue.Count; i++)
|
|||
|
|
{
|
|||
|
|
if (KeyColValue[i].Equals(UpdateInsertValue))
|
|||
|
|
{
|
|||
|
|
return true;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
return false;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 将数据库列字段非空属性及主键信息转成二维数组，返回一个(n,3)的二维数组，0列字段名，1列字段类型，2列字段主键信息（为空说明只是非空要求并非主键），
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="filepath">文件路径</param>
|
|||
|
|
/// <returns>将文件主要信息存储到二维数组中，并返回该二维数组</returns>
|
|||
|
|
public string[,] GetColInfoArray(DataTable colInfodt)
|
|||
|
|
{
|
|||
|
|
System.Collections.ArrayList colArray = new ArrayList();
|
|||
|
|
if (colInfodt == null)
|
|||
|
|
return null;
|
|||
|
|
for (int i = 0; i < colInfodt.Rows.Count; i++)
|
|||
|
|
{
|
|||
|
|
if (colInfodt.Rows[i][2].ToString() != "Y")
|
|||
|
|
{
|
|||
|
|
colArray.Add(Convert.ToString(colInfodt.Rows[i][0]) + ";" + Convert.ToString(colInfodt.Rows[i][1]) + ";" + Convert.ToString(colInfodt.Rows[i][3]));
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
string[,] Adata = new string[colArray.Count, 3];
|
|||
|
|
if (colArray.Count > 0)
|
|||
|
|
{
|
|||
|
|
for (int i = 0; i < colArray.Count; i++)
|
|||
|
|
{
|
|||
|
|
string StrSample = colArray[i].ToString();
|
|||
|
|
if (StrSample.Contains(';'))
|
|||
|
|
{
|
|||
|
|
string[] StrResult = StrSample.Split(';');
|
|||
|
|
if (StrResult.Length >= 3)
|
|||
|
|
{
|
|||
|
|
Adata[i, 0] = StrResult[0];
|
|||
|
|
Adata[i, 1] = StrResult[1];
|
|||
|
|
Adata[i, 2] = StrResult[2];
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
return Adata;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 文件操作类
|
|||
|
|
/// </summary>
|
|||
|
|
public class FileUse
|
|||
|
|
{
|
|||
|
|
/// <summary>
|
|||
|
|
/// 文件中存储的表名和显示方式的提取
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="filename"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public static string[] Load_NameStyle(string filename)
|
|||
|
|
{
|
|||
|
|
string[] data = new string[4];
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (File.Exists(filename))
|
|||
|
|
{
|
|||
|
|
XmlDocument doc = new XmlDocument();
|
|||
|
|
doc.Load(filename);
|
|||
|
|
XmlNodeList xn1 = doc.ChildNodes;
|
|||
|
|
foreach (XmlNode xn2 in xn1)
|
|||
|
|
{
|
|||
|
|
foreach (XmlNode xn3 in xn2.ChildNodes)
|
|||
|
|
{
|
|||
|
|
if (xn3.Name == "PriElement")
|
|||
|
|
{
|
|||
|
|
data[0] = xn3.Attributes["Recordset"].Value;
|
|||
|
|
data[1] = xn3.Attributes["ShowStyle"].Value;
|
|||
|
|
data[2] = xn3.Attributes["TableNameType"].Value;
|
|||
|
|
data[3] = xn3.Attributes["DataBaseType"].Value;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch
|
|||
|
|
{
|
|||
|
|
//throw;
|
|||
|
|
}
|
|||
|
|
return data;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// string到ShowStyle的转换
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="str">字符类型</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public static ShowStyle FileStringToEnum(string str)
|
|||
|
|
{
|
|||
|
|
if (str.Equals(ShowStyle.horizontal.ToString()))
|
|||
|
|
{
|
|||
|
|
return ShowStyle.horizontal;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
return ShowStyle.vertical;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 设置信息转换数组集合
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="setdata">设置信息集合</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public static string[,] GetSetInfo(System.Collections.ArrayList setdata)
|
|||
|
|
{
|
|||
|
|
string[,] SetInfo = new string[setdata.Count, 14];
|
|||
|
|
if (setdata.Count > 0)
|
|||
|
|
{
|
|||
|
|
for (int i = 0; i < setdata.Count; i++)
|
|||
|
|
{
|
|||
|
|
string setstr = setdata[i].ToString();
|
|||
|
|
if (setstr.Contains(';'))
|
|||
|
|
{
|
|||
|
|
string[] sdata = setstr.Split(';');
|
|||
|
|
if (sdata.Length >= 14)
|
|||
|
|
{
|
|||
|
|
for (int n = 0; n < sdata.Length - 1; n++)
|
|||
|
|
{
|
|||
|
|
SetInfo[i, n] = sdata[n];
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
return SetInfo;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 文件中设置信息提取
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="fileName"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public static System.Collections.ArrayList GetSetData(string fileName)
|
|||
|
|
{
|
|||
|
|
System.Collections.ArrayList SetData = new ArrayList();
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (File.Exists(fileName))
|
|||
|
|
{
|
|||
|
|
XmlDocument doc = new XmlDocument();
|
|||
|
|
doc.Load(fileName);
|
|||
|
|
XmlNodeList xn1 = doc.ChildNodes;
|
|||
|
|
foreach (XmlNode xn2 in xn1)
|
|||
|
|
{
|
|||
|
|
foreach (XmlNode xn3 in xn2.ChildNodes)
|
|||
|
|
{
|
|||
|
|
if (xn3.Name == "SetInfo")
|
|||
|
|
{
|
|||
|
|
foreach (XmlNode xn4 in xn3.ChildNodes)
|
|||
|
|
{
|
|||
|
|
if (xn4.Name == "Param")
|
|||
|
|
{
|
|||
|
|
string str = "";
|
|||
|
|
for (int i = 0; i < xn4.Attributes.Count; i++)
|
|||
|
|
{
|
|||
|
|
str += xn4.Attributes[i].Value + ";";
|
|||
|
|
}
|
|||
|
|
SetData.Add(str);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch
|
|||
|
|
{
|
|||
|
|
//throw;
|
|||
|
|
}
|
|||
|
|
return SetData;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取存储在文件里的上下限；
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="filename"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public static System.Collections.ArrayList GetUpDownLimit(string filename)
|
|||
|
|
{
|
|||
|
|
System.Collections.ArrayList UpDownLimt = new ArrayList();
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (File.Exists(filename))
|
|||
|
|
{
|
|||
|
|
XmlDocument doc = new XmlDocument();
|
|||
|
|
doc.Load(filename);
|
|||
|
|
XmlNodeList xn1 = doc.ChildNodes;
|
|||
|
|
foreach (XmlNode xn2 in xn1)
|
|||
|
|
{
|
|||
|
|
foreach (XmlNode xn3 in xn2.ChildNodes)
|
|||
|
|
{
|
|||
|
|
if (xn3.Name == "SetInfo")
|
|||
|
|
{
|
|||
|
|
foreach (XmlNode xn4 in xn3.ChildNodes)
|
|||
|
|
{
|
|||
|
|
if (xn4.Name == "Param")
|
|||
|
|
{
|
|||
|
|
string str = "";
|
|||
|
|
str += xn4.Attributes[0].Value + ";" + xn4.Attributes[7].Value + ";" + xn4.Attributes[8].Value + ";";
|
|||
|
|
UpDownLimt.Add(str);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch
|
|||
|
|
{
|
|||
|
|
//throw;
|
|||
|
|
}
|
|||
|
|
return UpDownLimt;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// DB2数据库操作类
|
|||
|
|
/// </summary>
|
|||
|
|
//public class DB2Utility
|
|||
|
|
//{
|
|||
|
|
// private static DB2Utility aInstance;
|
|||
|
|
// public string ConnectionString = "Provider=IBMDADB2";
|
|||
|
|
// private DB2Utility()
|
|||
|
|
// { }
|
|||
|
|
// public static DB2Utility Instance
|
|||
|
|
// {
|
|||
|
|
// get
|
|||
|
|
// {
|
|||
|
|
// if (aInstance == null)
|
|||
|
|
// aInstance = new DB2Utility();
|
|||
|
|
// return aInstance;
|
|||
|
|
// }
|
|||
|
|
|
|||
|
|
// }
|
|||
|
|
// private Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
|
|||
|
|
// public int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)
|
|||
|
|
// {
|
|||
|
|
// OleDbCommand cmd = new OleDbCommand();
|
|||
|
|
// using (OleDbConnection connection = new OleDbConnection(connectionString))
|
|||
|
|
// {
|
|||
|
|
// PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
|
|||
|
|
// int val = cmd.ExecuteNonQuery();
|
|||
|
|
// cmd.Parameters.Clear();
|
|||
|
|
// try
|
|||
|
|
// {
|
|||
|
|
// connection.Close();
|
|||
|
|
// }
|
|||
|
|
// catch
|
|||
|
|
// {
|
|||
|
|
// //throw;
|
|||
|
|
// }
|
|||
|
|
// return val;
|
|||
|
|
// }
|
|||
|
|
|
|||
|
|
|
|||
|
|
// }
|
|||
|
|
// public int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)
|
|||
|
|
// {
|
|||
|
|
// OleDbCommand cmd = new OleDbCommand();
|
|||
|
|
// PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
|
|||
|
|
// int val = cmd.ExecuteNonQuery();
|
|||
|
|
// cmd.Parameters.Clear();
|
|||
|
|
// return val;
|
|||
|
|
// }
|
|||
|
|
// public int ExecuteNonQuery(OleDbConnection connection, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)
|
|||
|
|
// {
|
|||
|
|
// try
|
|||
|
|
// {
|
|||
|
|
// OleDbCommand cmd = new OleDbCommand();
|
|||
|
|
// PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
|
|||
|
|
// int val = cmd.ExecuteNonQuery();
|
|||
|
|
// cmd.Parameters.Clear();
|
|||
|
|
// return val;
|
|||
|
|
// }
|
|||
|
|
|
|||
|
|
// catch
|
|||
|
|
// {
|
|||
|
|
// return -1;//throw;
|
|||
|
|
// }
|
|||
|
|
// finally
|
|||
|
|
// {
|
|||
|
|
// connection.Close();
|
|||
|
|
// }
|
|||
|
|
|
|||
|
|
// }
|
|||
|
|
// string str1;
|
|||
|
|
// public OleDbDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)
|
|||
|
|
// {
|
|||
|
|
// str1 = "DataAccess:OleDbDataReader1:" + System.DateTime.Now.Second + ":" + System.DateTime.Now.Millisecond + Environment.NewLine;
|
|||
|
|
// OleDbCommand cmd = new OleDbCommand();
|
|||
|
|
// OleDbConnection conn = new OleDbConnection(connectionString);
|
|||
|
|
// //try
|
|||
|
|
// //{
|
|||
|
|
// str1 += "DataAccess:OleDbDataReader2:" + System.DateTime.Now.Second + ":" + System.DateTime.Now.Millisecond + Environment.NewLine;
|
|||
|
|
// PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
|
|||
|
|
// str1 += "DataAccess:OleDbDataReader-->:" + System.DateTime.Now.Second + ": " + DateTime.Now.Millisecond + Environment.NewLine;
|
|||
|
|
// OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
|
|||
|
|
// str1 += "DataAccess:OleDbDataReader-->:" + System.DateTime.Now.Second + ": " + DateTime.Now.Millisecond + Environment.NewLine;
|
|||
|
|
// cmd.Parameters.Clear();
|
|||
|
|
// str1 += "DataAccess:OleDbDataReader3:" + System.DateTime.Now.Second + ": " + DateTime.Now.Millisecond + Environment.NewLine;
|
|||
|
|
// Console.WriteLine(str1);
|
|||
|
|
// return rdr;
|
|||
|
|
// //}
|
|||
|
|
// //catch
|
|||
|
|
// //{
|
|||
|
|
// conn.Close();
|
|||
|
|
// //throw;
|
|||
|
|
// //}
|
|||
|
|
// }
|
|||
|
|
// public object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)
|
|||
|
|
// {
|
|||
|
|
// OleDbCommand cmd = new OleDbCommand();
|
|||
|
|
// using (OleDbConnection conn = new OleDbConnection(connectionString))
|
|||
|
|
// {
|
|||
|
|
// PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
|
|||
|
|
// object val = cmd.ExecuteScalar();
|
|||
|
|
// cmd.Parameters.Clear();
|
|||
|
|
// return val;
|
|||
|
|
// }
|
|||
|
|
// }
|
|||
|
|
// public object ExecuteScalar(OleDbTransaction transaction, CommandType commandType, string commandText, params OleDbParameter[] commandParameters)
|
|||
|
|
// {
|
|||
|
|
// if (transaction == null)
|
|||
|
|
// throw new ArgumentNullException("transaction");
|
|||
|
|
// if (transaction != null && transaction.Connection == null)
|
|||
|
|
// throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
|
|||
|
|
|
|||
|
|
// OleDbCommand cmd = new OleDbCommand();
|
|||
|
|
// PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
|
|||
|
|
// object retval = cmd.ExecuteScalar();
|
|||
|
|
// cmd.Parameters.Clear();
|
|||
|
|
// return retval;
|
|||
|
|
// }
|
|||
|
|
// public object ExecuteScalar(OleDbConnection connectionString, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)
|
|||
|
|
// {
|
|||
|
|
// try
|
|||
|
|
// {
|
|||
|
|
// OleDbCommand cmd = new OleDbCommand();
|
|||
|
|
// PrepareCommand(cmd, connectionString, null, cmdType, cmdText, commandParameters);
|
|||
|
|
// object val = cmd.ExecuteScalar();
|
|||
|
|
// cmd.Parameters.Clear();
|
|||
|
|
// return val;
|
|||
|
|
// }
|
|||
|
|
// catch
|
|||
|
|
// {
|
|||
|
|
// return -1;
|
|||
|
|
// //throw;
|
|||
|
|
// }
|
|||
|
|
// finally
|
|||
|
|
// {
|
|||
|
|
// connectionString.Close();
|
|||
|
|
// }
|
|||
|
|
// }
|
|||
|
|
// public void CacheParameters(string cacheKey, params OleDbParameter[] commandParameters)
|
|||
|
|
// {
|
|||
|
|
// parmCache[cacheKey] = commandParameters;
|
|||
|
|
// }
|
|||
|
|
// public OleDbParameter[] GetCachedParameters(string cacheKey)
|
|||
|
|
// {
|
|||
|
|
// OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey];
|
|||
|
|
// if (cachedParms == null)
|
|||
|
|
// return null;
|
|||
|
|
// OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length];
|
|||
|
|
// for (int i = 0, j = cachedParms.Length; i < j; i++)
|
|||
|
|
// clonedParms[i] = (OleDbParameter)((ICloneable)cachedParms[i]).Clone();
|
|||
|
|
// return clonedParms;
|
|||
|
|
// }
|
|||
|
|
// private void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] commandParameters)
|
|||
|
|
// {
|
|||
|
|
// if (conn == null)
|
|||
|
|
// conn = new OleDbConnection(ConnectionString);
|
|||
|
|
// if (conn.State != ConnectionState.Open)
|
|||
|
|
// conn.Open();//打开数据源
|
|||
|
|
// cmd.Connection = conn;//将conn实例的数据传递给cmd实例
|
|||
|
|
|
|||
|
|
// cmd.CommandText = cmdText; //sql语句赋值给commandtext
|
|||
|
|
// cmd.CommandType = cmdType;
|
|||
|
|
|
|||
|
|
// if (trans != null)
|
|||
|
|
// cmd.Transaction = trans;
|
|||
|
|
|
|||
|
|
// if (commandParameters != null)
|
|||
|
|
// {
|
|||
|
|
// foreach (OleDbParameter parm in commandParameters)
|
|||
|
|
// cmd.Parameters.Add(parm);
|
|||
|
|
// }
|
|||
|
|
// }
|
|||
|
|
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 控件数据库连接DBString属性未初始化异常类
|
|||
|
|
/// </summary>
|
|||
|
|
public class DBstringNullException : ApplicationException
|
|||
|
|
{
|
|||
|
|
public DBstringNullException(string msg) : base(msg) { }
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 数据库数据读写实例类
|
|||
|
|
/// </summary>
|
|||
|
|
public class DB2Access : IDBAccess
|
|||
|
|
{
|
|||
|
|
private static DB2Access instance;
|
|||
|
|
public static DB2Access Instance
|
|||
|
|
{
|
|||
|
|
get
|
|||
|
|
{
|
|||
|
|
if (instance == null)
|
|||
|
|
{ instance = new DB2Access(); }
|
|||
|
|
return instance;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 表名带显示方式的数据查询(提供表名检测)
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="sql_tablename">表名</param>
|
|||
|
|
/// <param name="connectionstring">数据库连接字符串</param>
|
|||
|
|
/// <param name="style">数据显示方式</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataTable GetTableData(string tablename, string connectionstring, ShowStyle style)
|
|||
|
|
{
|
|||
|
|
string s_ql = "select * from " + tablename.ToUpper();
|
|||
|
|
DataTable dt = GetData(connectionstring, s_ql);
|
|||
|
|
dt = Model.TransFormDataTable(dt, style);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取表中各字段的信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="tablename">表名</param>
|
|||
|
|
/// <param name="connectionstring">数据库连接字符串</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataTable GetColumnInfo(string tablename, string connectionstring)
|
|||
|
|
{
|
|||
|
|
string user = "";
|
|||
|
|
string[] st = connectionstring.Split(new char[] { '=', ';' });
|
|||
|
|
user = st[5].TrimStart().TrimEnd();
|
|||
|
|
string s_ql = "select colname,typename,nulls,keyseq,length from syscat.columns where upper (tabschema) ='" + user.ToUpper() + "' and upper(tabname)='" + tablename.ToUpper() + "'order by COLNO asc";
|
|||
|
|
DataTable dt = GetData(connectionstring, s_ql);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public DataTable GetTableData(string tablename, string connectionstring)
|
|||
|
|
{
|
|||
|
|
string s_ql = "select * from " + tablename.ToUpper();
|
|||
|
|
DataTable dt = GetData(connectionstring, s_ql);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// SQL查询数据库数据
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="connectionstring">数据库连接字符串</param>
|
|||
|
|
/// <param name="sql">SQL语句</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
private DataTable GetData(string connectionstring, string sql)
|
|||
|
|
{
|
|||
|
|
DataTable dt = new DataTable();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (OleDbDataReader odr = DB2DBUtility.Instance.ExecuteReader(connectionstring, CommandType.Text, sql))
|
|||
|
|
{
|
|||
|
|
for (int i = 0; i < odr.FieldCount; i++)
|
|||
|
|
{
|
|||
|
|
DataColumn dc = new DataColumn();
|
|||
|
|
dc.ColumnName = odr.GetName(i);
|
|||
|
|
dt.Columns.Add(dc);
|
|||
|
|
}
|
|||
|
|
while (odr.Read())
|
|||
|
|
{
|
|||
|
|
DataRow dr = dt.NewRow();
|
|||
|
|
for (int i = 0; i < odr.FieldCount; i++)
|
|||
|
|
{
|
|||
|
|
dr[i] = odr[i];
|
|||
|
|
}
|
|||
|
|
dt.Rows.Add(dr);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw ex;
|
|||
|
|
}
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 带显示方式的SQL查询
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="connectionstring"></param>
|
|||
|
|
/// <param name="sql"></param>
|
|||
|
|
/// <param name="style"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataTable GetData(string connectionstring, string sql, ShowStyle style)
|
|||
|
|
{
|
|||
|
|
DataTable dt = GetData(connectionstring, sql);
|
|||
|
|
dt = Model.TransFormDataTable(dt, style);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取表的状态，是否可用
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="connectionstring"></param>
|
|||
|
|
/// <param name="sql"></param>
|
|||
|
|
/// <returns></returns>status 为0 时可用；
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 数据库中用户表表名的获取
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="connectiongstring"></param>
|
|||
|
|
/// <param name="TableSchema"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public Dictionary<string, string> GetTableName(string connectiongstring, string TableSchema)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
string sql = "select name from SYSIBM.SYSTABLES where CREATOR='" + TableSchema.ToUpper() + "' AND type = 'T' order by name";// "select TABLE_NAME from SYSIBM.TABLES where TABLE_SCHEMA='" + TableSchema.ToUpper() + "' order by TABLE_NAME";
|
|||
|
|
string sql1 = " select NAME from SYSIBM.SYSVIEWS where CREATOR='" + TableSchema.ToUpper() + "' order by name";
|
|||
|
|
Dictionary<string, string> dt = new Dictionary<string, string>();
|
|||
|
|
using (OleDbDataReader odr = DB2DBUtility.Instance.ExecuteReader(connectiongstring, CommandType.Text, sql))
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
while (odr.Read())
|
|||
|
|
{
|
|||
|
|
for (int i = 0; i < odr.FieldCount; i++)
|
|||
|
|
{
|
|||
|
|
dt.Add(odr[i].ToString(), "T");
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
using (OleDbDataReader odr = DB2DBUtility.Instance.ExecuteReader(connectiongstring, CommandType.Text, sql1))
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
while (odr.Read())
|
|||
|
|
{
|
|||
|
|
for (int i = 0; i < odr.FieldCount; i++)
|
|||
|
|
{
|
|||
|
|
dt.Add(odr[i].ToString(), "V");
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
return dt;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
public DataTable DataBase(string connectionstring, string sql)
|
|||
|
|
{
|
|||
|
|
//String.Format("update {0} SET EXTID= '{0} where SEQNO = 64479 ", tablename.ToUpper());
|
|||
|
|
DataTable dt = GetData(connectionstring, sql);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public DataTable SelTableData(string tablename, string connectionstring, ShowStyle style)
|
|||
|
|
{
|
|||
|
|
DataTable dt = new DataTable();
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public DataTable SelTableData(string tablename, string connectionstring)
|
|||
|
|
{
|
|||
|
|
//string s_ql = string.Format("select * from {0} order by PRODEND DESC fetch first 1 rows only", tablename.ToUpper());
|
|||
|
|
string s_ql = string.Format("select * from {0} fetch first 1 rows only", tablename.ToUpper());
|
|||
|
|
DataTable dt = GetData(connectionstring, s_ql);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public DataTable SelTableData(string tablename, string connectionstring, string sql)
|
|||
|
|
{
|
|||
|
|
DataTable dt = GetData(connectionstring, sql);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public DataTable SelStatusTable(string tablename, string connectionstring)
|
|||
|
|
{
|
|||
|
|
string s_ql = string.Format("select * from {0} where STATUS='0'", tablename.ToUpper());
|
|||
|
|
DataTable dt = GetData(connectionstring, s_ql);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// postgres数据库数据读写实例类
|
|||
|
|
/// </summary>
|
|||
|
|
public class PgAccess : IDBAccess
|
|||
|
|
{
|
|||
|
|
private static PgAccess instance;
|
|||
|
|
public static PgAccess Instance
|
|||
|
|
{
|
|||
|
|
get
|
|||
|
|
{
|
|||
|
|
if (instance == null)
|
|||
|
|
{ instance = new PgAccess(); }
|
|||
|
|
return instance;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 表名带显示方式的数据查询(提供表名检测)
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="sql_tablename">表名</param>
|
|||
|
|
/// <param name="connectionstring">数据库连接字符串</param>
|
|||
|
|
/// <param name="style">数据显示方式</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataTable GetTableData(string tablename, string connectionstring, ShowStyle style)
|
|||
|
|
{
|
|||
|
|
string s_ql = "select * from " + tablename.ToUpper();
|
|||
|
|
DataTable dt = GetData(connectionstring, s_ql);
|
|||
|
|
dt = Model.TransFormDataTable(dt, style);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取表中各字段的信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="tablename">表名</param>
|
|||
|
|
/// <param name="connectionstring">数据库连接字符串</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataTable GetColumnInfo(string tablename, string connectionstring)
|
|||
|
|
{
|
|||
|
|
string user = "";
|
|||
|
|
string[] st = connectionstring.Split(new char[] { '=', ';' });
|
|||
|
|
user = st[5].TrimStart().TrimEnd();
|
|||
|
|
string s_ql = "select colname,typename,nulls,keyseq,length from syscat.columns where upper (tabschema) ='" + user.ToUpper() + "' and upper(tabname)='" + tablename.ToUpper() + "'order by COLNO asc";
|
|||
|
|
DataTable dt = GetData(connectionstring, s_ql);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public DataTable GetTableData(string tablename, string connectionstring)
|
|||
|
|
{
|
|||
|
|
string s_ql = "select * from " + tablename.ToUpper();
|
|||
|
|
DataTable dt = GetData(connectionstring, s_ql);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// SQL查询数据库数据
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="connectionstring">数据库连接字符串</param>
|
|||
|
|
/// <param name="sql">SQL语句</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
private DataTable GetData(string connectionstring, string sql)
|
|||
|
|
{
|
|||
|
|
DataTable dt = new DataTable();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (NpgsqlDataReader odr = PgDBUtility.Instance.ExecuteReader(connectionstring, CommandType.Text, sql))
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
for (int i = 0; i < odr.FieldCount; i++)
|
|||
|
|
{
|
|||
|
|
DataColumn dc = new DataColumn();
|
|||
|
|
dc.ColumnName = odr.GetName(i);
|
|||
|
|
dt.Columns.Add(dc);
|
|||
|
|
}
|
|||
|
|
while (odr.Read())
|
|||
|
|
{
|
|||
|
|
DataRow dr = dt.NewRow();
|
|||
|
|
for (int i = 0; i < odr.FieldCount; i++)
|
|||
|
|
{
|
|||
|
|
dr[i] = odr[i];
|
|||
|
|
}
|
|||
|
|
dt.Rows.Add(dr);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
//throw ;
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 带显示方式的SQL查询
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="connectionstring"></param>
|
|||
|
|
/// <param name="sql"></param>
|
|||
|
|
/// <param name="style"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataTable GetData(string connectionstring, string sql, ShowStyle style)
|
|||
|
|
{
|
|||
|
|
DataTable dt = GetData(connectionstring, sql);
|
|||
|
|
dt = Model.TransFormDataTable(dt, style);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取表的状态，是否可用
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="connectionstring"></param>
|
|||
|
|
/// <param name="sql"></param>
|
|||
|
|
/// <returns></returns>status 为0 时可用；
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 数据库中用户表表名的获取
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="connectiongstring"></param>
|
|||
|
|
/// <param name="TableSchema"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public Dictionary<string, string> GetTableName(string connectiongstring, string TableSchema)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
//string sql = "select name from SYSIBM.SYSTABLES where CREATOR='" + TableSchema.ToUpper() + "' AND type = 'T' order by name";// "select TABLE_NAME from SYSIBM.TABLES where TABLE_SCHEMA='" + TableSchema.ToUpper() + "' order by TABLE_NAME";
|
|||
|
|
//string sql1 = " select NAME from SYSIBM.SYSVIEWS where CREATOR='" + TableSchema.ToUpper() + "' order by name";
|
|||
|
|
Dictionary<string, string> dt = new Dictionary<string, string>();
|
|||
|
|
//using (OleDbDataReader odr = DB2DBUtility.Instance.ExecuteReader(connectiongstring, CommandType.Text, sql))
|
|||
|
|
//{
|
|||
|
|
|
|||
|
|
// while (odr.Read())
|
|||
|
|
// {
|
|||
|
|
// for (int i = 0; i < odr.FieldCount; i++)
|
|||
|
|
// {
|
|||
|
|
// dt.Add(odr[i].ToString(), "T");
|
|||
|
|
// }
|
|||
|
|
// }
|
|||
|
|
//}
|
|||
|
|
//using (OleDbDataReader odr = DB2DBUtility.Instance.ExecuteReader(connectiongstring, CommandType.Text, sql1))
|
|||
|
|
//{
|
|||
|
|
|
|||
|
|
// while (odr.Read())
|
|||
|
|
// {
|
|||
|
|
// for (int i = 0; i < odr.FieldCount; i++)
|
|||
|
|
// {
|
|||
|
|
// dt.Add(odr[i].ToString(), "V");
|
|||
|
|
// }
|
|||
|
|
// }
|
|||
|
|
//}
|
|||
|
|
return dt;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
public DataTable DataBase(string connectionstring, string sql)
|
|||
|
|
{
|
|||
|
|
DataTable dt = GetData(connectionstring, sql);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public DataTable SelTableData(string tablename, string connectionstring, ShowStyle style)
|
|||
|
|
{
|
|||
|
|
DataTable dt = new DataTable();
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public DataTable SelTableData(string tablename, string connectionstring)
|
|||
|
|
{
|
|||
|
|
//string s_ql = string.Format("select * from {0} order by PRODEND DESC fetch first 1 rows only", tablename.ToUpper());
|
|||
|
|
string s_ql = string.Format("select * from {0} fetch first 1 rows only", tablename.ToUpper());
|
|||
|
|
DataTable dt = GetData(connectionstring, s_ql);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public DataTable SelTableData(string tablename, string connectionstring, string sql)
|
|||
|
|
{
|
|||
|
|
DataTable dt = GetData(connectionstring, sql);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public DataTable SelStatusTable(string tablename, string connectionstring)
|
|||
|
|
{
|
|||
|
|
string s_ql = string.Format("select * from {0} where STATUS='0'", tablename.ToUpper());
|
|||
|
|
DataTable dt = GetData(connectionstring, s_ql);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 接口类（数据库访问）
|
|||
|
|
/// </summary>
|
|||
|
|
public interface IDBAccess
|
|||
|
|
{
|
|||
|
|
/// <summary>
|
|||
|
|
/// 依据表名获取数据库中表数据
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="tablename">表名</param>
|
|||
|
|
/// <param name="connectionstring">数据库连接字符串</param>
|
|||
|
|
/// <param name="style">显示方式</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
DataTable GetTableData(string tablename, string connectionstring, ShowStyle style);
|
|||
|
|
/// <summary>
|
|||
|
|
/// 依据表名获取数据库中表字段的信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="tablename">表名</param>
|
|||
|
|
/// <param name="connectionstring">数据库连接字符串</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
DataTable GetColumnInfo(string tablename, string connectionstring);
|
|||
|
|
DataTable GetTableData(string tablename, string connectionstring);
|
|||
|
|
DataTable SelTableData(string tablename, string connectionstring, ShowStyle style);
|
|||
|
|
DataTable SelTableData(string tablename, string connectionstring);
|
|||
|
|
DataTable SelTableData(string tablename, string connectionstring, string sql);
|
|||
|
|
DataTable SelStatusTable(string tablename, string connectionstring);
|
|||
|
|
/// <summary>
|
|||
|
|
/// 带显示方式的SQL语句查询数据库信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="connectionstring">数据库连接字符串</param>
|
|||
|
|
/// <param name="sql">查询SQL语句</param>
|
|||
|
|
/// <param name="style">显示方式</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
DataTable GetData(string connectionstring, string sql, ShowStyle style);
|
|||
|
|
DataTable DataBase(string connectionstring, string sql);
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取数据库当前用户下所有用户表名
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="connectiongstring"></param>
|
|||
|
|
/// <param name="tableschema"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
Dictionary<string, string> GetTableName(string connectiongstring, string TableSchema);
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 工厂类（数据库访问）
|
|||
|
|
/// </summary>
|
|||
|
|
public class DBFactory
|
|||
|
|
{
|
|||
|
|
public static IDBAccess CreateAccess(string dbType)
|
|||
|
|
{
|
|||
|
|
if (dbType.ToUpper() == "DB2")
|
|||
|
|
{
|
|||
|
|
return new DB2Access();
|
|||
|
|
}
|
|||
|
|
else if (dbType.ToUpper() == "ORACLE")
|
|||
|
|
{
|
|||
|
|
return new OracleAccess();
|
|||
|
|
}
|
|||
|
|
else if (dbType.ToUpper() == "ACCESS")
|
|||
|
|
{
|
|||
|
|
return new AceAccess();
|
|||
|
|
}
|
|||
|
|
else if (dbType.ToUpper() == "POSTGRES")
|
|||
|
|
{
|
|||
|
|
return new PgAccess();
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
return new OtherAccess();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// Oracle数据库访问类
|
|||
|
|
/// </summary>
|
|||
|
|
public class OracleAccess : IDBAccess
|
|||
|
|
{
|
|||
|
|
///// <summary>
|
|||
|
|
///// Oracle数据库存储insert,delete,update
|
|||
|
|
///// </summary>
|
|||
|
|
///// <param name="connectionstring">连接字符串</param>
|
|||
|
|
///// <param name="sql">SQL语句</param>
|
|||
|
|
///// <returns></returns>
|
|||
|
|
//public int ExecuteNonQuery(string connectionstring, CommandType cmdType, string cmdText)
|
|||
|
|
//{
|
|||
|
|
// OracleConnection oracon = new OracleConnection(connectionstring);
|
|||
|
|
// try
|
|||
|
|
// {
|
|||
|
|
// OracleCommand cmd = new OracleCommand();
|
|||
|
|
// cmd.CommandType = cmdType;
|
|||
|
|
// cmd.CommandText = cmdText;
|
|||
|
|
// cmd.Connection = oracon;
|
|||
|
|
// if (oracon.State == ConnectionState.Closed)
|
|||
|
|
// {
|
|||
|
|
// oracon.Open();
|
|||
|
|
// }
|
|||
|
|
// int val = cmd.ExecuteNonQuery();
|
|||
|
|
// return val;
|
|||
|
|
// }
|
|||
|
|
// catch
|
|||
|
|
// {
|
|||
|
|
// return 0;
|
|||
|
|
// //throw;
|
|||
|
|
// }
|
|||
|
|
// finally
|
|||
|
|
// {
|
|||
|
|
// oracon.Close();
|
|||
|
|
// oracon.Dispose();
|
|||
|
|
// }
|
|||
|
|
//}
|
|||
|
|
/// <summary>
|
|||
|
|
/// Oracle数据库读取操作，select的SQL语句
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="connectionString">数据库连接字符串</param>
|
|||
|
|
/// <param name="cmdType">命令字符串</param>
|
|||
|
|
/// <param name="cmdText">SQL</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public OracleDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
OracleConnection oracon = new OracleConnection(connectionString);
|
|||
|
|
|
|||
|
|
//try
|
|||
|
|
//{
|
|||
|
|
OracleCommand cmd = new OracleCommand();
|
|||
|
|
cmd.CommandType = cmdType;
|
|||
|
|
cmd.CommandText = cmdText;
|
|||
|
|
cmd.Connection = oracon;
|
|||
|
|
if (oracon.State != ConnectionState.Open)
|
|||
|
|
{
|
|||
|
|
oracon.Open();
|
|||
|
|
}
|
|||
|
|
OracleDataReader odr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
|
|||
|
|
return odr;
|
|||
|
|
//}
|
|||
|
|
//catch
|
|||
|
|
//{
|
|||
|
|
// oracon.Close();
|
|||
|
|
// return null;
|
|||
|
|
//}
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 带显示方式的SQL语句查询
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="connectionstring"></param>
|
|||
|
|
/// <param name="sql"></param>
|
|||
|
|
/// <param name="style"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataTable GetData(string connectionstring, string sql, ShowStyle style)
|
|||
|
|
{
|
|||
|
|
DataTable dt = GetData(connectionstring, sql);
|
|||
|
|
dt = Model.TransFormDataTable(dt, style);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// SQL语句查询
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="connectionstring"></param>
|
|||
|
|
/// <param name="sql"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
private DataTable GetData(string connectionstring, string sql)
|
|||
|
|
{
|
|||
|
|
DataTable dt = new DataTable();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (OracleDataReader odr = ExecuteReader(connectionstring, CommandType.Text, sql))
|
|||
|
|
{
|
|||
|
|
for (int i = 0; i < odr.FieldCount; i++)
|
|||
|
|
{
|
|||
|
|
DataColumn dc = new DataColumn();
|
|||
|
|
dc.ColumnName = odr.GetName(i);
|
|||
|
|
dt.Columns.Add(dc);
|
|||
|
|
}
|
|||
|
|
while (odr.Read())
|
|||
|
|
{
|
|||
|
|
DataRow dr = dt.NewRow();
|
|||
|
|
for (int j = 0; j < odr.FieldCount; j++)
|
|||
|
|
{
|
|||
|
|
dr[j] = odr[j];
|
|||
|
|
}
|
|||
|
|
dt.Rows.Add(dr);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw ex;
|
|||
|
|
}
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取value值
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="connectionstring"></param>
|
|||
|
|
/// <param name="sql"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public string GetTableValue(string connectionstring, string sql)
|
|||
|
|
{
|
|||
|
|
string value = "";
|
|||
|
|
using (OracleDataReader odr = ExecuteReader(connectionstring, CommandType.Text, sql))
|
|||
|
|
{
|
|||
|
|
if (odr.Read())
|
|||
|
|
{
|
|||
|
|
value = Convert.ToString(odr[0]);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
return value;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取表字段信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="tablename"></param>
|
|||
|
|
/// <param name="connectionstring"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataTable GetColumnInfo(string tablename, string connectionstring)
|
|||
|
|
{
|
|||
|
|
string sql1 = "select COLUMN_NAME,DATA_TYPE, NUllABLE,DECODE (UPPER( data_type),'VARCHAR2',data_length,'VARCHAR',data_length,'CHAR',data_length, data_precision ) data_precision FROM user_tab_cols where table_name='" + tablename.ToUpper() + "'order by COLUMN_ID asc";
|
|||
|
|
string sql2 = "select col.COLUMN_NAME, col.POSITION from user_constraints con, user_cons_columns col where con.CONSTRAINT_NAME=col.CONSTRAINT_NAME and con.CONSTRAINT_TYPE='P' and col.table_name='" + tablename.ToUpper() + "' ";
|
|||
|
|
DataTable dt1 = GetData(connectionstring, sql1);
|
|||
|
|
DataTable dt2 = GetData(connectionstring, sql2);
|
|||
|
|
DataColumn dc = new DataColumn();
|
|||
|
|
dc.ColumnName = "KEYQE";
|
|||
|
|
dt1.Columns.Add(dc);
|
|||
|
|
for (int i = 0; i < dt1.Rows.Count; i++)
|
|||
|
|
{
|
|||
|
|
for (int j = 0; j < dt2.Rows.Count; j++)
|
|||
|
|
{
|
|||
|
|
if (dt1.Rows[i][0].Equals(dt2.Rows[j][0]))
|
|||
|
|
{
|
|||
|
|
dt1.Rows[i][4] = dt2.Rows[j][1];
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
dt1.Columns[3].SetOrdinal(4);
|
|||
|
|
return dt1;
|
|||
|
|
}
|
|||
|
|
public DataTable GetTableData(string tablename, string connectionstring)
|
|||
|
|
{
|
|||
|
|
string s_ql = "select * from " + tablename.ToUpper();
|
|||
|
|
DataTable dt = GetData(connectionstring, s_ql);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 依据表名获取表中数据
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="tablename"></param>
|
|||
|
|
/// <param name="connectionstring"></param>
|
|||
|
|
/// <param name="style"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataTable GetTableData(string tablename, string connectionstring, ShowStyle style)
|
|||
|
|
{
|
|||
|
|
string s_ql = "select * from " + tablename.ToUpper() + " where status='0'";
|
|||
|
|
DataTable dt = GetData(connectionstring, s_ql);
|
|||
|
|
dt = Model.TransFormDataTable(dt, style);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取数据库中所有的用户表
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="connectiongstring"></param>
|
|||
|
|
/// <param name="tableschema"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public Dictionary<string, string> GetTableName(string connectiongstring, string tableschema)
|
|||
|
|
{
|
|||
|
|
Dictionary<string, string> data = new Dictionary<string, string>();
|
|||
|
|
string sql = "select table_name from all_tables where owner='" + tableschema.ToUpper() + "' order by table_name";
|
|||
|
|
string sql1 = "select VIEW_NAME from ALL_VIEWS where OWNER='" + tableschema.ToUpper() + "' order by view_name";
|
|||
|
|
//try
|
|||
|
|
//{
|
|||
|
|
using (OracleDataReader odr = ExecuteReader(connectiongstring, CommandType.Text, sql))
|
|||
|
|
{
|
|||
|
|
while (odr.Read())
|
|||
|
|
{
|
|||
|
|
for (int i = 0; i < odr.FieldCount; i++)
|
|||
|
|
{
|
|||
|
|
data.Add(odr[i].ToString(), "T");
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
using (OracleDataReader odr = ExecuteReader(connectiongstring, CommandType.Text, sql1))
|
|||
|
|
{
|
|||
|
|
while (odr.Read())
|
|||
|
|
{
|
|||
|
|
for (int i = 0; i < odr.FieldCount; i++)
|
|||
|
|
{
|
|||
|
|
data.Add(odr[i].ToString(), "V");
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return data;
|
|||
|
|
//}
|
|||
|
|
//catch
|
|||
|
|
//{
|
|||
|
|
// return null;
|
|||
|
|
//}
|
|||
|
|
}
|
|||
|
|
public DataTable DataBase(string connectionstring, string sql)
|
|||
|
|
{
|
|||
|
|
DataTable dt = GetData(connectionstring, sql);
|
|||
|
|
return dt;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
public DataTable SelTableData(string tablename, string connectionstring, ShowStyle style)
|
|||
|
|
{
|
|||
|
|
string s_ql = "select * from " + tablename.ToUpper();
|
|||
|
|
DataTable dt = GetData(connectionstring, s_ql);
|
|||
|
|
dt = Model.TransFormDataTable(dt, style);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public DataTable SelTableData(string tablename, string connectionstring)
|
|||
|
|
{
|
|||
|
|
//string s_ql = string.Format("select HOTCOIL,COLDCOIL,STEELGREADE,ALLOYCODE,EXTWIDTH,ENTHICK,EXTHICK from (select * from {0} order by TOC DESC ) where rownum = 1 ", tablename.ToUpper());
|
|||
|
|
//string s_ql = string.Format("select HOD_COIL_ID,COLD_COIL_ID,ALLOY_CODE,ENTRY_THICKNESS,EXIT_THICKNESS,EXIT_WIDTH from {0} where rownum = 1 ", tablename.ToUpper());
|
|||
|
|
string s_ql = string.Format("select HOT_COIL_ID,COLD_COIL_ID,ALLOY_CODE,EXIT_WIDTH,ENTRY_THICKNESS,EXIT_THICKNESS,ORDER_QUALITY from {0} where rownum = 1 ", tablename.ToUpper());
|
|||
|
|
DataTable dt = GetData(connectionstring, s_ql);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public DataTable SelStatusTable(string tablename, string connectionstring)
|
|||
|
|
{
|
|||
|
|
string s_ql = string.Format("select * from {0} where STATUS='0'", tablename.ToUpper());
|
|||
|
|
DataTable dt = GetData(connectionstring, s_ql);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public DataTable SelTableData(string tablename, string connectionstring, string sql)
|
|||
|
|
{
|
|||
|
|
DataTable dt = GetData(connectionstring, sql);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// Oracle数据库访问类
|
|||
|
|
/// </summary>
|
|||
|
|
public class AceAccess : IDBAccess
|
|||
|
|
{
|
|||
|
|
///// <summary>
|
|||
|
|
///// Oracle数据库存储insert,delete,update
|
|||
|
|
///// </summary>
|
|||
|
|
///// <param name="connectionstring">连接字符串</param>
|
|||
|
|
///// <param name="sql">SQL语句</param>
|
|||
|
|
///// <returns></returns>
|
|||
|
|
//public int ExecuteNonQuery(string connectionstring, CommandType cmdType, string cmdText)
|
|||
|
|
//{
|
|||
|
|
// int ret = -1;
|
|||
|
|
// return ret;
|
|||
|
|
// OracleConnection oracon = new OracleConnection(connectionstring);
|
|||
|
|
// try
|
|||
|
|
// {
|
|||
|
|
// OracleCommand cmd = new OracleCommand();
|
|||
|
|
// cmd.CommandType = cmdType;
|
|||
|
|
// cmd.CommandText = cmdText;
|
|||
|
|
// cmd.Connection = oracon;
|
|||
|
|
// if (oracon.State == ConnectionState.Closed)
|
|||
|
|
// {
|
|||
|
|
// oracon.Open();
|
|||
|
|
// }
|
|||
|
|
// int val = cmd.ExecuteNonQuery();
|
|||
|
|
// return val;
|
|||
|
|
// }
|
|||
|
|
// catch
|
|||
|
|
// {
|
|||
|
|
// //throw;
|
|||
|
|
// }
|
|||
|
|
// finally
|
|||
|
|
// {
|
|||
|
|
// oracon.Close();
|
|||
|
|
// oracon.Dispose();
|
|||
|
|
// }
|
|||
|
|
//}
|
|||
|
|
///// <summary>
|
|||
|
|
///// Oracle数据库读取操作，select的SQL语句
|
|||
|
|
///// </summary>
|
|||
|
|
///// <param name="connectionString">数据库连接字符串</param>
|
|||
|
|
///// <param name="cmdType">命令字符串</param>
|
|||
|
|
///// <param name="cmdText">SQL</param>
|
|||
|
|
///// <returns></returns>
|
|||
|
|
//public OracleDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText)
|
|||
|
|
//{
|
|||
|
|
// return null;
|
|||
|
|
// //OracleConnection oracon = new OracleConnection(connectionString);
|
|||
|
|
// ////try
|
|||
|
|
// ////{
|
|||
|
|
// //OracleCommand cmd = new OracleCommand();
|
|||
|
|
// //cmd.CommandType = cmdType;
|
|||
|
|
// //cmd.CommandText = cmdText;
|
|||
|
|
// //cmd.Connection = oracon;
|
|||
|
|
// //if (oracon.State != ConnectionState.Open)
|
|||
|
|
// //{
|
|||
|
|
// // oracon.Open();
|
|||
|
|
// //}
|
|||
|
|
// //OracleDataReader odr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
|
|||
|
|
// //return odr;
|
|||
|
|
// ////}
|
|||
|
|
// ////catch
|
|||
|
|
// ////{
|
|||
|
|
// //// oracon.Close();
|
|||
|
|
// //// return null;
|
|||
|
|
// ////}
|
|||
|
|
//}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 带显示方式的SQL语句查询
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="connectionstring"></param>
|
|||
|
|
/// <param name="sql"></param>
|
|||
|
|
/// <param name="style"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataTable GetData(string connectionstring, string sql, ShowStyle style)
|
|||
|
|
{
|
|||
|
|
DataTable dt = GetData(connectionstring, sql);
|
|||
|
|
dt = Model.TransFormDataTable(dt, style);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// SQL语句查询
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="connectionstring"></param>
|
|||
|
|
/// <param name="sql"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
private DataTable GetData(string connectionstring, string sql)
|
|||
|
|
{
|
|||
|
|
DataTable dt = new DataTable();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (OleDbDataReader odr = DB2DBUtility.Instance.ExecuteReader(connectionstring, CommandType.Text, sql))
|
|||
|
|
{
|
|||
|
|
for (int i = 0; i < odr.FieldCount; i++)//odr为OleDbDataReader的实例，将access数据库查询的数据读去出来 fieldcount为列数,getname为名称
|
|||
|
|
{
|
|||
|
|
DataColumn dc = new DataColumn();
|
|||
|
|
dc.ColumnName = odr.GetName(i);
|
|||
|
|
dt.Columns.Add(dc);
|
|||
|
|
}
|
|||
|
|
while (odr.Read())
|
|||
|
|
{
|
|||
|
|
DataRow dr = dt.NewRow();
|
|||
|
|
for (int i = 0; i < odr.FieldCount; i++)
|
|||
|
|
{
|
|||
|
|
dr[i] = odr[i];
|
|||
|
|
}
|
|||
|
|
dt.Rows.Add(dr);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.ToString());
|
|||
|
|
}
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取表字段信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="tablename"></param>
|
|||
|
|
/// <param name="connectionstring"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataTable GetColumnInfo(string tablename, string connectionstring)
|
|||
|
|
{
|
|||
|
|
string sql1 = string.Format("SELECT NAME FROM SYSOBJECTS WHERE ID=OBJECT_ID('{0}')", tablename);
|
|||
|
|
DataTable dt1 = GetData(connectionstring, sql1);
|
|||
|
|
DataColumn dc = new DataColumn();
|
|||
|
|
//dc.ColumnName = "KEYQE";
|
|||
|
|
//dt1.Columns.Add(dc);
|
|||
|
|
//for (int i = 0; i < dt1.Rows.Count; i++)
|
|||
|
|
//{
|
|||
|
|
// for (int j = 0; j < dt2.Rows.Count; j++)
|
|||
|
|
// {
|
|||
|
|
// if (dt1.Rows[i][0].Equals(dt2.Rows[j][0]))
|
|||
|
|
// {
|
|||
|
|
// dt1.Rows[i][4] = dt2.Rows[j][1];
|
|||
|
|
// }
|
|||
|
|
// }
|
|||
|
|
//}
|
|||
|
|
//dt1.Columns[3].SetOrdinal(4);
|
|||
|
|
return dt1;
|
|||
|
|
}
|
|||
|
|
public DataTable GetTableData(string tablename, string connectionstring)
|
|||
|
|
{
|
|||
|
|
string s_ql = "select * from " + tablename.ToUpper();
|
|||
|
|
DataTable dt = GetData(connectionstring, s_ql);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public DataTable SelTableData(string tablename, string connectionstring, ShowStyle style)
|
|||
|
|
{
|
|||
|
|
string s_ql = "select * from " + tablename.ToUpper() + " where status='0'";
|
|||
|
|
DataTable dt = GetData(connectionstring, s_ql);
|
|||
|
|
dt = Model.TransFormDataTable(dt, style);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public DataTable SelTableData(string tablename, string connectionstring)
|
|||
|
|
{
|
|||
|
|
string s_ql = string.Format("select * from {0} ", tablename.ToUpper());
|
|||
|
|
DataTable dt = GetData(connectionstring, s_ql);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 依据表名获取表中数据
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="tablename"></param>
|
|||
|
|
/// <param name="connectionstring"></param>
|
|||
|
|
/// <param name="style"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataTable GetTableData(string tablename, string connectionstring, ShowStyle style)
|
|||
|
|
{
|
|||
|
|
string s_ql = "select * from " + tablename.ToUpper();
|
|||
|
|
DataTable dt = GetData(connectionstring, s_ql);
|
|||
|
|
dt = Model.TransFormDataTable(dt, style);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取数据库中所有的用户表
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="connectiongstring"></param>
|
|||
|
|
/// <param name="tableschema"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public Dictionary<string, string> GetTableName(string connectiongstring, string tableschema)
|
|||
|
|
{
|
|||
|
|
Dictionary<string, string> data = new Dictionary<string, string>();
|
|||
|
|
//string sql = "select table_name from all_tables where owner='" + tableschema.ToUpper() + "' order by table_name";
|
|||
|
|
//string sql1 = "select VIEW_NAME from ALL_VIEWS where OWNER='" + tableschema.ToUpper() + "' order by view_name";
|
|||
|
|
////try
|
|||
|
|
////{
|
|||
|
|
//using (OracleDataReader odr = ExecuteReader(connectiongstring, CommandType.Text, sql))
|
|||
|
|
//{
|
|||
|
|
// while (odr.Read())
|
|||
|
|
// {
|
|||
|
|
// for (int i = 0; i < odr.FieldCount; i++)
|
|||
|
|
// {
|
|||
|
|
// data.Add(odr[i].ToString(), "T");
|
|||
|
|
// }
|
|||
|
|
// }
|
|||
|
|
//}
|
|||
|
|
//using (OracleDataReader odr = ExecuteReader(connectiongstring, CommandType.Text, sql1))
|
|||
|
|
//{
|
|||
|
|
// while (odr.Read())
|
|||
|
|
// {
|
|||
|
|
// for (int i = 0; i < odr.FieldCount; i++)
|
|||
|
|
// {
|
|||
|
|
// data.Add(odr[i].ToString(), "V");
|
|||
|
|
// }
|
|||
|
|
// }
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
return data;
|
|||
|
|
//}
|
|||
|
|
//catch
|
|||
|
|
//{
|
|||
|
|
// return null;
|
|||
|
|
//}
|
|||
|
|
}
|
|||
|
|
public DataTable DataBase(string connectionstring, string sql)
|
|||
|
|
{
|
|||
|
|
DataTable dt = GetData(connectionstring, sql);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public DataTable SelStatusTable(string tablename, string connectionstring)
|
|||
|
|
{
|
|||
|
|
string s_ql = string.Format("select * from {0} where Alarmevent=2000 and AlarmStatus=0", tablename.ToUpper());
|
|||
|
|
DataTable dt = GetData(connectionstring, s_ql);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public DataTable SelTableData(string tablename, string connectionstring, string sql)
|
|||
|
|
{
|
|||
|
|
DataTable dt = GetData(connectionstring, sql);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 其他数据库
|
|||
|
|
/// </summary>
|
|||
|
|
public class OtherAccess : IDBAccess
|
|||
|
|
{
|
|||
|
|
public virtual DataTable GetColumnInfo(string tablename, string connectionstring)
|
|||
|
|
{
|
|||
|
|
DataTable dt = new DataTable();
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public virtual DataTable GetTableData(string recordset, string connectionstring, ShowStyle style)
|
|||
|
|
{
|
|||
|
|
DataTable dt = new DataTable();
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public virtual DataTable GetData(string connectionstring, string sql, ShowStyle style)
|
|||
|
|
{
|
|||
|
|
DataTable dt = new DataTable();
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public virtual DataTable GetUpdateTableData(string sql, string connectionstring, ShowStyle style)
|
|||
|
|
{
|
|||
|
|
DataTable dt = new DataTable();
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public virtual bool TableNameIsExist(string tablename, string connectionstring)
|
|||
|
|
{
|
|||
|
|
return true;
|
|||
|
|
}
|
|||
|
|
public int GetTableStatus(string connectionstring, string sql)
|
|||
|
|
{
|
|||
|
|
int flag = -1;
|
|||
|
|
|
|||
|
|
return flag;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取value值
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="connectionstring"></param>
|
|||
|
|
/// <param name="sql"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public string GetTableValue(string connectionstring, string sql)
|
|||
|
|
{
|
|||
|
|
string value = "";
|
|||
|
|
|
|||
|
|
return value;
|
|||
|
|
}
|
|||
|
|
public DataTable GetTableData(string tablename, string connectionstring)
|
|||
|
|
{
|
|||
|
|
DataTable dt = new DataTable();
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public Dictionary<string, string> GetTableName(string connectiongstring, string tableschema)
|
|||
|
|
{
|
|||
|
|
Dictionary<string, string> data = new Dictionary<string, string>();
|
|||
|
|
return data;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
public DataTable DataBase(string connectionstring, string sql)
|
|||
|
|
{
|
|||
|
|
DataTable dt = new DataTable();
|
|||
|
|
return dt;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
public DataTable SelTableData(string tablename, string connectionstring, ShowStyle style)
|
|||
|
|
{
|
|||
|
|
DataTable dt = new DataTable();
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public DataTable SelTableData(string tablename, string connectionstring)
|
|||
|
|
{
|
|||
|
|
DataTable dt = new DataTable();
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public DataTable SelStatusTable(string tablename, string connectionstring)
|
|||
|
|
{
|
|||
|
|
DataTable dt = new DataTable();
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
public DataTable SelTableData(string tablename, string connectionstring, string sql)
|
|||
|
|
{
|
|||
|
|
DataTable dt = new DataTable();
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|