Db_.cs 与 csharp_db.h功能是一样的。
Db_.cs
/****************************************************************************************
创建时间 ：2006年12月19日
文件名 ：Db_.cs
功能 ：数据库处理
作者 ：李锋
Email ：runzhilf@139.com
联系电话 ：13828778863
AdKeyPrimary 关键字是主关键字。
AdKeyForeign 关键字是外部关键字。
AdKeyUnique 关键字是唯一的。
需要添加的引用:
(1)Microsoft SQLDMO Object Library
(2)Microsoft ADO Ext. 6.0 for DDL and Security
(3)Microsoft ActiveX Data Objects 2.8 Library
(4)C:\Program Files\Common Files\System\ado\msado15.dll
----------------------------------------------------------------最后一次修改时间：2021年07月03日
*******************************************************************************************/
using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Generic;
using System.Reflection;
#if _WINDOWS_PLATFORM_
using ADOX;
using System.IO;
using System.Data.Common;
using System.Collections;
using System.Runtime.CompilerServices;
#if _WINDOWS_DESKTOP_
using System.Drawing;
using System.Windows.Forms;
using System.Data.OleDb;
using System.ComponentModel;
#elif _WINDOWS_WEB_
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
#endif
#elif _ANDROID_PLATFORM_
#elif _IOS_PLATFORM_
#endif
namespace lf
{
/// <summary>
/// 数据访问格式
/// </summary>
public enum DataFormat_
{
dfMDB, //Access2000,2003数据库
dfAccdb, //2007数据库
dfDBF,
dfDB,
dfInterBase,
dfSQLServer, //SQL数据库
dfOracle, //Oracle数据库
dfSybase,
dfInformix,
dfDB2,
dfSQLite, //Android数据库
dfMySQL
};
/// <summary>
/// 数据编缉状态
/// </summary>
public enum EditorStatus_
{
esView, //查看状态
esUpdate, //更新状态
esDelete, //删除状态
esAppend, //添加状态
esNull //未设置
};
/// <summary>
/// 记录改变状态
/// </summary>
public enum DataChange_
{
dcNot, //没有改变
dcUpdate, //记录已更新
dcDelete, //记录已删除
dcInsert, //记录已添加
dcSelect, //记录已选择
/// <summary>
/// 记录已改变，可能是删除，可以是添加，可能是修改
/// </summary>
dcChanged
};
/// <summary>
/// 数据类型
/// </summary>
public enum DataType_
{
//----------------------------------------------------------------C#数据类型
/// <summary>
/// 8位无符号整数
/// </summary>
dtByte = 1,
/// <summary>
///16位无符号整数
/// </summary>
dtInt16 = 2,
/// <summary>
///32位无符号整数
///</summary>
dtInt32 = 3,
/// <summary>
///64位无符号整数
///</summary>
dtInt64 = 4,
/// <summary>
/// 小数
/// </summary>
dtFloat = 5,
/// <summary>
/// 小数
/// </summary>
dtDouble = 6,
/// <summary>
/// 时间日期
/// </summary>
dtDateTime = 7,
/// <summary>
/// 字符串
/// </summary>
dtString = 8,
/// <summary>
/// 对象 例：Image数据
/// </summary>
dtObject = 9,
//--------------------------------------------------------------------自定义数据类型
/// <summary>
/// 正数或0
/// </summary>
dtPlusNumberOrZero = 21,
/// <summary>
/// 负数或0
/// </summary>
dtNegativeOrZero = 22,
/// <summary>
/// 正整数
/// </summary>
dtPositiveInteger = 23,
/// <summary>
/// 正整数或0
/// </summary>
dtPositiveIntegerOrZero = 24,
/// <summary>
/// 正数
/// </summary>
dtPlusNumber = 25,
/// <summary>
/// 整数
/// </summary>
dtJavaInteger,
/// <summary>
/// 小数
/// </summary>
dtJavaFloat,
/// <summary>
/// 双精度小数
/// </summary>
dtJavaDouble,
/// <summary>
/// 时间日期
/// </summary>
dtJavaDateTime,
/// <summary>
/// 字符串
/// </summary>
dtJavaString,
/// <summary>
/// 图片，二进制数据
/// </summary>
dtJavaBinaryStream,
/// <summary>
/// tinyint TINYINT 1字节 (-128，127) (0，255) 小整数值
/// </summary>
dtJavaBoolean,
/// <summary>
/// byte[]
/// </summary>
dtJavaByteArray,
/// <summary>
///未知数据类型
/// </summary>
dtNULL = -1,
};
public class Field_
{
/// <summary>
/// 字段名
/// </summary>
public string Name;
/// <summary>
/// 字段值
/// </summary>
public string Value;
/// <summary>
/// 字段类型
/// </summary>
public DataType_ DataType;
/// <summary>
/// 字段描述
/// </summary>
public string Desc;
public Field_(string sName, string sValue, DataType_ dt)
{
Name = sName;
Value = sValue;
DataType = dt;
Desc = "";
}
public Field_()
{
Name = "";
Value = "";
DataType = DataType_.dtNULL;
Desc = "";
}
public int GetSQLServerXType()
{
int iResult = -1;
switch (DataType)
{
case DataType_.dtDateTime:
iResult = -1;
break;
case DataType_.dtFloat:
iResult = -1;
break;
default:
iResult = -1;
break;
}
return iResult;
}
public void SetSQLServerXType(int iXTypeVale)
{
string sTypeName = SQLServerXTYPConverToCSharpTypeName(iXTypeVale);
if(sTypeName == "DateTime")
{
DataType = DataType_.dtDateTime;
}
else if(sTypeName == "Int32")
{
DataType = DataType_.dtInt32;
}
else if (sTypeName == "String")
{
DataType = DataType_.dtString;
}
else if (sTypeName == "Object")
{
DataType = DataType_.dtObject;
}
else if (sTypeName == "Double")
{
DataType = DataType_.dtDouble;
}
}
/// <summary>
/// 把SQLServer xtype值转换为 C# 数据类型
/// </summary>
/// <param name="iXTypeVale"></param>
/// <returns></returns>
public static Type SQLServerXTYPConverToCSharpType(int iXTypeVale)
{
string sXTypeString = GetSQLServerXTypeString(iXTypeVale);
SqlDbType sdtType = XTypeStringConverToSqlDbType(sXTypeString);
Type tType = SqlDbTypeConvertToCSharpType(sdtType);
return tType;
}
/// <summary>
/// 把SQLServer xtype值转换为 C# 数据类型名的字符串
/// </summary>
/// <param name="iXTypeVale"></param>
/// <returns></returns>
public static string SQLServerXTYPConverToCSharpTypeName(int iXTypeVale)
{
return SQLServerXTYPConverToCSharpType(iXTypeVale).Name;
}
/// <summary>
/// 以字符串表示的SQLServer数据类型
/// </summary>
/// <param name="iXTypeVale"></param>
/// <returns></returns>
public static string GetSQLServerXTypeString(int iXTypeVale)
{
/*
34 image
35 text
36 uniqueidentifier
48 tinyint
52 smallint
56 int
58 smalldatetime
59 real
60 money
61 datetime
62 float
98 sql_variant
99 ntext
104 bit
106 decimal
108 numeric
122 smallmoney
127 bigint
165 varbinary
167 varchar
173 binary
175 char
189 timestamp
231 sysname
231 nvarchar
239 nchar
*/
switch (iXTypeVale)
{
case 34:
return "image";
case 35:
return "text";
case 36:
return "uniqueidentifier";
case 48:
return "tinyint";
case 52:
return "smallint";
case 56:
return "int";
case 58:
return "smalldatetime";
case 59:
return "real";
case 60:
return "money";
case 61:
return "datetime";
case 62:
return "float";
case 98:
return "sql_variant";
case 99:
return "ntext";
case 104:
return "bit";
case 106:
return "decimal";
case 108:
return "numeric";
case 122:
return "smallmoney";
case 127:
return "bigint";
case 165:
return "varbinary";
case 167:
return "varchar";
case 173:
return "binary";
case 175:
return "char";
case 189:
return "timestamp";
case 231:
return "nvarchar";
//case 231:
//SQL Server 实例包括用户定义的名为 sysname 的数据类型。
//sysname 用于表列、变量以及用于存储对象名的存储过程参数。sysname 的精确定义与标识符规则相关；
//因此，SQL Server 的各个实例会有所不同。sysname 与 nvarchar(128) 作用相同。
//return "sysname";
case 239:
return "nchar";
case 241:
return "xml";
}
return "未知";
}
/// <summary>
/// SqlDbType转换为C#数据类型
/// </summary>
/// <param name="sqlType"></param>
/// <returns></returns>
public static Type SqlDbTypeConvertToCSharpType(SqlDbType sqlType)
{
switch (sqlType)
{
case SqlDbType.BigInt:
return typeof(Int64);
case SqlDbType.Binary:
return typeof(Object);
case SqlDbType.Bit:
return typeof(Boolean);
case SqlDbType.Char:
return typeof(String);
case SqlDbType.DateTime:
return typeof(DateTime);
case SqlDbType.Decimal:
return typeof(Decimal);
case SqlDbType.Float:
return typeof(Double);
case SqlDbType.Image:
return typeof(Object);
case SqlDbType.Int:
return typeof(Int32);
case SqlDbType.Money:
return typeof(Decimal);
case SqlDbType.NChar:
return typeof(String);
case SqlDbType.NText:
return typeof(String);
case SqlDbType.NVarChar:
return typeof(String);
case SqlDbType.Real:
return typeof(Single);
case SqlDbType.SmallDateTime:
return typeof(DateTime);
case SqlDbType.SmallInt:
return typeof(Int16);
case SqlDbType.SmallMoney:
return typeof(Decimal);
case SqlDbType.Text:
return typeof(String);
case SqlDbType.Timestamp:
return typeof(Object);
case SqlDbType.TinyInt:
return typeof(Byte);
case SqlDbType.Udt://自定义的数据类型
return typeof(Object);
case SqlDbType.UniqueIdentifier:
return typeof(Object);
case SqlDbType.VarBinary:
return typeof(Object);
case SqlDbType.VarChar:
return typeof(String);
case SqlDbType.Variant:
return typeof(Object);
case SqlDbType.Xml:
return typeof(Object);
default:
return null;
}
}
/// <summary>
/// sql server数据类型（如：varchar）, 转换为SqlDbType类型
/// </summary>
/// <param name="sqlTypeString"></param>
/// <returns></returns>
public static SqlDbType XTypeStringConverToSqlDbType(string sXTypeString)
{
SqlDbType dbType = SqlDbType.Variant;//默认为Object
switch (sXTypeString)
{
case "int":
dbType = SqlDbType.Int;
break;
case "varchar":
dbType = SqlDbType.VarChar;
break;
case "bit":
dbType = SqlDbType.Bit;
break;
case "datetime":
dbType = SqlDbType.DateTime;
break;
case "decimal":
dbType = SqlDbType.Decimal;
break;
case "float":
dbType = SqlDbType.Float;
break;
case "image":
dbType = SqlDbType.Image;
break;
case "money":
dbType = SqlDbType.Money;
break;
case "ntext":
dbType = SqlDbType.NText;
break;
case "nvarchar":
dbType = SqlDbType.NVarChar;
break;
case "smalldatetime":
dbType = SqlDbType.SmallDateTime;
break;
case "smallint":
dbType = SqlDbType.SmallInt;
break;
case "text":
dbType = SqlDbType.Text;
break;
case "bigint":
dbType = SqlDbType.BigInt;
break;
case "binary":
dbType = SqlDbType.Binary;
break;
case "char":
dbType = SqlDbType.Char;
break;
case "nchar":
dbType = SqlDbType.NChar;
break;
case "numeric":
dbType = SqlDbType.Decimal;
break;
case "real":
dbType = SqlDbType.Real;
break;
case "smallmoney":
dbType = SqlDbType.SmallMoney;
break;
case "sql_variant":
dbType = SqlDbType.Variant;
break;
case "timestamp":
dbType = SqlDbType.Timestamp;
break;
case "tinyint":
dbType = SqlDbType.TinyInt;
break;
case "uniqueidentifier":
dbType = SqlDbType.UniqueIdentifier;
break;
case "varbinary":
dbType = SqlDbType.VarBinary;
break;
case "xml":
dbType = SqlDbType.Xml;
break;
}
return dbType;
}
}
public class Db_
{
private DataFormat_ _df;
/// <summary>
/// 数据库名子
/// </summary>
private string _database_name;
/// <summary>
/// 数据库名子
/// </summary>
public virtual string database_name { get { return _database_name; } set { _database_name = value; } }
/// <summary>
///
/// </summary>
private string _user_name;
/// <summary>
/// 数据库用户
/// </summary>
public virtual string user_name { get { return _user_name; } set { _user_name = value; } }
/// <summary>
///
/// </summary>
private string _user_password;
/// <summary>
/// 数据库密码
/// </summary>
public virtual string user_password { get { return _user_password; } set { _user_password = value; } }
/// <summary>
///
/// </summary>
private string _database_source;
/// <summary>
/// 提供数据源的数据服务器名
/// </summary>
public virtual string database_source { get { return _database_source; } set { _database_source = value; } }
/// <summary>
/// DB-Engines 数据库流行度排行榜 9 月更新已发布，排名前二十如下：总体排名和上个月相比基本一致，
/// 其中排名前三的 Oracle、MySQL 和 Microsoft SQL Server 也是分数增加最多的三个数据库，对于
/// 很多做互联网的同学来说，Oracle和Microsoft SQL Server排名前
/// </summary>
public static StringList_ DbManufacturerList = new StringList_ {
"Oracle", "MySQL", "Microsoft SQL Server", "PostgreSQL", "MongoDB","Redis",
"IBM Db2","Elasticsearch","SQLite","Cassandra","Microsoft Access","MariaDB",
"Splunk","Hive","Microsoft Azure SQL Database","Amazon DynamoDB","Teradata",
"Neo4j","SAP HAHA","FileMaker"};
//-------------------------------------------------------------构造
public Db_(DataFormat_ df)
{
_df = df;
}
//-----------------------------------------------------------------------属性重写
//-------------------------------------------------------------方法重写
/// <summary>
/// 执行特定的SQL内容
/// </summary>
/// <param name="sCaptionName">标题名</param>
/// <param name="sCheckTableName">需要检查的表名</param>
/// <returns></returns>
public virtual bool exec_dict_sql_content(string sCaptionName, string sCheckTableName)
{
return false;
}
public virtual bool ExecSQLText(string sText)
{
return false;
}
public virtual bool ExecSQLFile(string sFileName)
{
return false;
}
public virtual DbConnection GetConnection()
{
return null;
}
public virtual DbDataAdapter GetViewDbDataAdapter()
{
return null;
}
public virtual int ExecNonSQL(string sSQL)
{
return -1;
}
/// <summary>
/// 返回记录条数
/// </summary>
/// <param name="sTableName">表句</param>
/// <returns></returns>
public int getRecordCount(string sTableName)
{
DataTable dt = ExecSQLQuery("SELECT Count(*) fd_sum FROM " + sTableName);
return (int)dt.Rows[0]["fd_sum"];
}
/// <summary>
/// 返回最后一条记录的某个字段值
/// </summary>
/// <param name="sFileName"></param>
/// <returns></returns>
public object getFieldValueForLastRecord(string sFieldName, string sTableName, string sCondition)
{
string ssql = "";
if (sCondition == "")
{
ssql = "SELECT TOP 1 " + sFieldName + " FROM " + sTableName + " ORDER BY " + sFieldName + " DESC";
}
else
{
ssql = "SELECT TOP 1 " + sFieldName + " FROM " + sTableName + " WHERE " + sCondition + " ORDER BY " + sFieldName + " DESC";
}
DataTable dt = ExecSQLQuery(ssql);
if (dt.Rows.Count > 0)
{
return dt.Rows[0][sFieldName];
}
else
{
return null;
}
}
/// <summary>
/// 获取最后一条记录。 创建时间：2014-04-16
/// </summary>
/// <param name="sTableName">表名</param>
/// <returns>如果成功，返回最后一记录，否则返回NULL</returns>
public DataRow getLastRecord(string sTableName)
{
string ssql = "SELECT TOP 1 * FROM " + sTableName + " ORDER BY fd_id DESC";
DataTable dt = ExecSQLQuery(ssql);
if (dt.Rows.Count > 0) return dt.Rows[0];
return null;
}
/// <summary>
/// 获取第一条记录。 创建时间：2014-04-16
/// </summary>
/// <param name="sTableName">表名</param>
/// <returns>如果成功，返回第一条记录，否则返回NULL</returns>
public DataRow getFirstRecord(string sTableName)
{
string ssql = "SELECT TOP 1 * FROM " + sTableName + " ORDER BY fd_id";
DataTable dt = ExecSQLQuery(ssql);
if (dt.Rows.Count > 0) return dt.Rows[0];
return null;
}
/// <summary>
/// 在目录sPath下创建一个数据库。
/// </summary>
/// <param name="sDatabaseName">数据库名</param>
/// <param name="sPath">路径名</param>
/// 创建时间：????-??-?? 最后一次修改时间：2020-04-03
/// <returns>如果成功，则返回空字符串，失败返回错误原因。</returns>
public virtual string CreateDatabase(string sDatabaseName, string sPath = "")
{
return "";
}
/// <summary>
/// 创建一个系统数据库，如果数据库存在或者创建成功，返回true
/// </summary>
/// <param name="sPath"></param>
/// <returns></returns>
public static bool createAppRepository(string sPath)
{
if (sPath.Trim().Length == 0)
return false;
#if _WINDOWS_PLATFORM_
if (File.Exists(sPath + "AppRepository" + ".accdb"))
{
return true;
}
//数据库密码 = lg.DES_Encrypt("lh",LDB_Global.ind_des_key);
AccessDB_ db = new AccessDB_(sPath + "AppRepository" + ".accdb", lg.DES_Encrypt("lh", LDB_Global.ind_des_key));
db.create_app_co_user();
db.create_app_ind_user();
db.create_app_module();
#endif
return true;
}
/// <summary>
/// 判断是否存在数据库sDatabaseName
/// </summary>
/// <param name="sDatabaseName">数据库名</param>
/// 创建时间：2020-03-03 最后一次修改时间： 2021-07-04
/// <returns></returns>
public static bool IsExistDatabase(string sDatabaseName)
{
#if _WINDOWS_PLATFORM_
SqlDb_ dbMaster = new SqlDb_("master", "sa", lg.TextDecrypt2(LDB_Global.m_db_pwd, LDB_Global.m_text_key), LDB_Global.m_IP);
string ssql = "SELECT * FROM master..sysdatabases where name = \'" + sDatabaseName.Trim() + "\'";
return dbMaster.ExecSQLQuery(ssql).Rows.Count != 0;
#else
throw new Exception(lg.OnCodeDidNotFinishError);
#endif
}
/// <summary>
/// 这个表的作用是保存软件使用者的公司的必要信息。
/// </summary>
public virtual bool create_app_co_user()
{
return false;
}
/// <summary>
/// 这个表的作用是保存软件使用者的个人必要信息。
/// </summary>
/// <returns></returns>
public virtual bool create_app_ind_user()
{
return false;
}
/// <summary>
/// 所有可用模块集合
/// </summary>
/// <param name="ConnectionString"></param>
public virtual bool create_app_module()
{
return false;
}
/// <summary>
/// 如果个人通信薄类型不存在，则创建
/// </summary>
/// <param name="ConnectionString"></param>
public virtual bool create_ind_individual_addressbook_type()
{
return false;
}
/// <summary>
/// 如模块表不存在，则自动创建
/// </summary>
/// <param name="ConnectionString"></param>
public virtual bool create_ind_module()
{
return false;
}
/// <summary>
/// 如临时模块不存在,则自动创建
/// </summary>
/// <returns></returns>
public virtual bool create_ind_module_tmp()
{
return false;
}
/// <summary>
/// 如果系统表不存在，则自动创建
/// </summary>
/// <returns></returns>
public virtual bool create_System()
{
return false;
}
/// <summary>
/// 创建审批流表
/// </summary>
/// <returns></returns>
public virtual bool create_co_approval_flow()
{
return true;
}
//------------------------------------------------------------------------------------个人相关模块
public virtual bool create_dict_notepad()
{
return true;
}
/// <summary>
/// 个人相关.sql(工作计划,工作日志,审批请求,审阅审批,使用设备,集团通迅录,个人通迅录,个人信息,记事本,建议与改进,使用帮助)
/// </summary>
/// <returns></returns>
public virtual bool create_个人相关()
{
return true;
}
/// <summary>
/// 如果ind_notepad表不存在，则创建ind_notepad表。
/// </summary>
/// <param name="cn"></param>
/// <returns></returns>
public virtual bool create_ind_notepad()
{
return false;
}
/// <summary>
/// 如果ind_payout表不存在，则创建ind_payout表。
/// </summary>
/// <param name="cn"></param>
/// <returns></returns>
public virtual bool create_ind_payout()
{
return false;
}
/// <summary>
/// 创建个人收入类型表
/// </summary>
/// <param name="ConnectionString"></param>
public virtual bool create_ind_payout_type()
{
return false;
}
/// <summary>
/// 创建银行存款数量表
/// </summary>
/// <param name="cn"></param>
/// <returns></returns>
public virtual bool create_ind_bank_cash()
{
return false;
}
/// <summary>
/// 银行取款表。
/// </summary>
/// <param name="cn"></param>
/// <returns></returns>
public virtual bool create_ind_bank_debits()
{
return false;
}
/// <summary>
/// 银行存款表。
/// </summary>
/// <param name="cn"></param>
/// <returns></returns>
public virtual bool create_ind_bank_deposit()
{
return false;
}
/// <summary>
/// 现金计数表
/// </summary>
/// <param name="cn"></param>
/// <returns></returns>
public virtual bool create_ind_cash()
{
return false;
}
/// <summary>
/// 创建客户表
/// </summary>
/// <param name="cn"></param>
/// <returns></returns>
public virtual bool create_co_customer()
{
return false;
}
/// <summary>
///创建每天收入表
/// </summary>
/// <param name="cn"></param>
/// <returns></returns>
public virtual bool create_ind_earning()
{
return false;
}
/// <summary>
/// 创建每天收入类型表
/// </summary>
/// <param name="ConnectionString"></param>
public virtual bool create_ind_earning_type()
{
return false;
}
/// <summary>
/// 创建个人通信薄
/// </summary>
/// <returns></returns>
public virtual bool create_ind_individual_addressbook()
{
return false;
}
/// <summary>
/// 创建自然人表
/// </summary>
/// <returns></returns>
public virtual bool create_crm_natural_person()
{
return false;
}
/// <summary>
/// 创建公司表
/// </summary>
/// <returns></returns>
public virtual bool create_crm_company()
{
return false;
}
/// <summary>
/// 创建关系表
/// </summary>
/// <returns></returns>
public virtual bool create_crm_relation()
{
return false;
}
/// <summary>
/// 创建银行帐户
/// </summary>
/// <returns></returns>
public virtual bool create_fc_bank()
{
return false;
}
/// <summary>
/// 创建项目表
/// </summary>
/// <returns></returns>
public virtual bool create_crm_project()
{
return false;
}
/// <summary>
/// 创建员工表
/// </summary>
/// <returns></returns>
public virtual bool create_crm_employee()
{
return false;
}
/// <summary>
///
/// </summary>
/// 创建时间： 2021-10-03 最后一次修改时间：2021-10-03
/// <returns></returns>
public virtual bool create_crm_rote()
{
return false;
}
/// <summary>
/// 个人通信录视图
/// </summary>
/// <returns></returns>
public virtual bool create_pro_crm_np_AddressBook_view()
{
return false;
}
/// <summary>
/// 创建职位表
/// </summary>
/// <returns></returns>
public virtual bool create_co_job()
{
return false;
}
/// <summary>
/// 创建部门表
/// </summary>
/// <returns></returns>
public virtual bool create_co_department()
{
return false;
}
/// <summary>
/// 创建供应商品信息
/// </summary>
/// <returns></returns>
public virtual bool create_co_supplier()
{
return false;
}
/// <summary>
/// 创建送货记录
/// </summary>
/// <returns></returns>
public virtual bool create_co_deliver_goods()
{
return false;
}
/// <summary>
/// 创建送货记录分析表
/// </summary>
/// <returns></returns>
public virtual bool create_co_deliver_goods_statistic()
{
return false;
}
/// <summary>
/// 创建拿货记录分析表
/// </summary>
/// <returns></returns>
public virtual bool create_co_stock_statistic()
{
return false;
}
/// <summary>
/// 创建存货记录
/// </summary>
/// <returns></returns>
public virtual bool create_co_stock()
{
return false;
}
/// <summary>
/// 创建新闻分类表
/// </summary>
/// <returns></returns>
public virtual bool create_co_news_class()
{
return false;
}
/// <summary>
/// 创建新闻信息表
/// </summary>
/// <returns></returns>
public virtual bool create_co_news_info()
{
return false;
}
/// <summary>
/// 资金借出记录
/// </summary>
/// <returns></returns>
public virtual bool create_co_loan()
{
return false;
}
/// <summary>
/// 资产管理 ------------------------商品表
/// </summary>
/// <returns></returns>
public virtual bool create_dict_merchandise()
{
return false;
}
/// <summary>
/// 资产管理 ------------------------资产管理
/// </summary>
/// <returns></returns>
public virtual bool create_资产管理()
{
return false;
}
/// <summary>
/// 资产视图
/// </summary>
/// <returns></returns>
public virtual bool create_pro_assets_view()
{
return false;
}
/// <summary>
/// 资产分类视图
/// </summary>
/// <returns></returns>
public virtual bool create_pro_assets_class_view()
{
return false;
}
//---------------------------------------------------------------------------------合同管理模块
/// <summary>
/// 创建合同管理模块
/// </summary>
/// <returns></returns>
public virtual bool create_crm_contract()
{
return false;
}
/// <summary>
/// 创建每天支出记录
/// </summary>
/// <returns></returns>
public virtual bool create_co_payout()
{
return false;
}
public virtual bool create_co_login_info()
{
return false;
}
public virtual bool create_co_runtime_parameter()
{
return false;
}
public virtual bool create_co_runtime_user()
{
return false;
}
/// <summary>
/// 创建每天支出记录的触发器
/// </summary>
/// <returns></returns>
public virtual bool create_co_payout_trigger()
{
return false;
}
/// <summary>
/// 创建现金记录
/// </summary>
/// <returns></returns>
public virtual bool create_co_cash()
{
return false;
}
/// <summary>
/// 创建定价表
/// </summary>
/// <returns></returns>
public virtual bool create_co_pricing_of_product()
{
return false;
}
/// <summary>
/// 数据库格式
/// </summary>
public DataFormat_ dataFormat
{
get
{
return _df;
}
}
/**
* 函数名：create_crm_natural_person
* 作用： 在数据库sDBName中创建表crm_natural_person
* 参数：[sDBName]数据库名
* 返回值：boolean
* 作者：李锋
* 创建时间：2020/1/26 22:21
* 最后一次修改日期：2020/1/26 22:21
*/
public static bool create_crm_natural_person(string sDBName)
{
if (sDBName.Trim().Length == 0)
return false;
string ssql = "SELECT [fd_content] FROM [dict_sql] WHERE [fd_caption] = \'crm_natural_person.sql\'";
//[MyFamily]
string sCreate = "";
DataTable dt = LDB_Global.db_repository.ExecSQLQuery(ssql);
if (dt.Rows.Count > 0)
sCreate = dt.Rows[0][0].ToString();
else
return false;
sCreate = sCreate.Replace("MyFamily", sDBName);
return LDB_Global.db_repository.ExecNonSQL(sCreate) != 0;
}
/// <summary>
/// 在数据库sDBName中创建表sTableName
/// 在AppRepository数据库中，必须存在dict_sql这个表，在这个表中保存有创建表的SQL语句。
/// </summary>
/// <param name="sTableName">表名</param>
/// <param name="sDBName">数据库名</param>
/// 创建时间：2020/02/09 最后一次修改时间：2020/02/09
/// <returns>如果成功，返回真</returns>
public static bool create_table(string sTableName, string sDatabaseName)
{
if (sDatabaseName.Trim() == "")
return false;
string ssql = "SELECT [fd_content] FROM [dict_sql] WHERE [fd_caption] = \'" + sTableName +".sql\'";
string sCreate = "";
DataTable dt = LDB_Global.db_repository.ExecSQLQuery(ssql);
if (dt.Rows.Count > 0)
sCreate = dt.Rows[0][0].ToString();
else
return false;
sCreate = sCreate.Replace("[MyFamily]", "[" + sDatabaseName + "]");
return LDB_Global.db_repository.ExecNonSQL(sCreate) != 0;
}
//--------------------------------------------------------------操作
/// <summary>
/// 把sSourceTableName的数据拷贝到sDestTable，并清空sDestTableName表的数据，注意，两个表结构必须一样的
/// </summary>
/// <param name="sDestTableName">目标表，这个表原有的数据会清空</param>
/// <param name="dbDest">目标数据库</param>
/// <param name="sSourceTableName">源数据库的表名</param>
/// 创建时间：2020-05-02 最后一次修改时间：2020-05-02
/// <param name="dbSource">源数据库</param>
public static void TableCopy(string sDestTableName, Db_ dbDest, string sSourceTableName, Db_ dbSource)
{
#if _WINDOWS_PLATFORM_
//清空原有表的数据
dbDest.ExecNonSQL("DELETE FROM [" + sDestTableName + "]");
//复制表数据
DataTable dtSource = dbSource.ExecSQLQuery("SELECT * FROM [" + sSourceTableName + "]");
SqlBulkCopy sbc = new SqlBulkCopy(dbDest.GetConnection().ConnectionString);
try
{
sbc.DestinationTableName = sDestTableName;
sbc.WriteToServer(dtSource); //写入数据库
sbc.Close();
}
finally
{
sbc.Close();
}
#else
throw new Exception(lg.OnCodeDidNotFinishError);
#endif
}
public DataTable ExecSQLQuery(string sSQL)
{
return SqlQuery_.QueryDB(sSQL, this);
}
/// <summary>
/// 返回最大的索引号，如果表中没有记录，则返回0
/// </summary>
/// <param name="sTableName"></param>
/// <param name="sCondition"></param>
/// <returns></returns>
public int GetMaxID(string sTableName, string sCondition = "")
{
string ssql = "";
if (sCondition.Length == 0)
ssql = "SELECT Max(fd_id) AS max_id FROM " + sTableName;
else
ssql = "SELECT Max(fd_id) AS max_id FROM " + sTableName + " WHERE " + sCondition;
DataTable dt = ExecSQLQuery(ssql);
//如果sTableName表中没有记录，Max(fd_id)返回null,dt.getRowsCount() = 1,不管
//怎样，dt.getRowsCount()都返回1
if (dt.Rows[0]["max_id"] == DBNull.Value)
{
return 0;
}
else
{
return (int)dt.Rows[0]["max_id"];
}
}
/*
AVG(column) 返回某列的平均值
BINARY_CHECKSUM
CHECKSUM
CHECKSUM_AGG
Count(column) 返回某列的行数（不包括NULL值）
Count(*) 返回被选行数
Count(DISTINCT column) 返回相异结果的数目
First(column) 返回在指定的域中第一个记录的值（SQLServer2000 不支持）
LAST(column) 返回在指定的域中最后一个记录的值（SQLServer2000 不支持）
MAX(column) 返回某列的最高值
MIN(column) 返回某列的最低值
STDEV(column)
STDEVP(column)
SUM(column) 返回某列的总和
VAR(column)
VARP(column)
*/
/// <summary>
/// SUM(column) 返回某列的总和 (创建于：2014-04-16）
/// </summary>
/// <param name="sFieldName">列名</param>
/// <param name="sTable">表名</param>
/// <param name="sCondition">条件</param>
/// <returns>返回值</returns>
public float fun_sum(string sFieldName, string sTable, string sCondition)
{
float f_sum = 0;
string ssql = "SELECT SUM(" + sFieldName + ") AS fd_sum FROM " + sTable;
if (sCondition.Trim().Length != 0)
{
ssql += " WHERE " + sCondition;
}
DataTable dt = ExecSQLQuery(ssql);
f_sum = System.Convert.ToSingle(dt.Rows[0]["fd_sum"]);
return f_sum;
}
/// <summary>
/// 从索引号号得到某个字段的值
/// </summary>
/// <param name="sIDValue">索引号</param>
/// <param name="sFieldName">字段名</param>
/// <param name="sTableName">表名</param>
/// <returns>如果不存在，则返回空值</returns>
public string GetValueFromID(string sIDValue, string sFieldName, string sTableName)
{
string ssql = "SELECT [" + sFieldName + "] FROM [" + sTableName + "] WHERE [fd_id] =" + sIDValue;
DataTable dt = ExecSQLQuery(ssql);
if (dt.Rows.Count > 0)
{
return dt.Rows[0][sFieldName].ToString().Trim();
}
else
{
return "";
}
}
/// <summary>
/// 创建时间： 2020-06-20 最后一次修改时间：2020-06-20
/// </summary>
/// <param name="sIDValue">索此号</param>
/// <param name="sFieldName">字段名</param>
/// <param name="sFieldValue">字段值</param>
/// <param name="sTableName">表名</param>
/// <returns></returns>
public bool UpdateValueFromID(string sIDValue, string sFieldName, string sFieldValue,string sTableName)
{
string ssql = "UPDATE [" + sTableName + "] SET [" + sFieldName + "] = \'" + lg.CheckSQLString(sFieldValue) + "\'" +
" WHERE [fd_id] =" + sIDValue;
return ExecNonSQL(ssql) != 0;
}
/// <summary>
/// 创建时间： 2020-06-25 最后一次修改时间：2020-06-25
/// 交换两条记录的ID号
/// </summary>
/// <param name="iID1"></param>
/// <param name="iID2"></param>
/// <param name="sTableName"></param>
/// <returns></returns>
public bool SwapID(int iID1, int iID2, string sTableName)
{
int iTempID = GetMaxID(sTableName) + 1;
//ID1变成iTempID
if (ExecNonSQL("UPDATE [" + sTableName + "] SET [fd_id] = " + iTempID.ToString() + " WHERE [fd_id] = " +
iID1.ToString()) != 0)
{
//ID2变成ID1
if (ExecNonSQL("UPDATE [" + sTableName + "] SET [fd_id] = " + iID1.ToString() + " WHERE [fd_id] = " +
iID2.ToString()) != 0)
{
//iTempID 变成 ID2
if (ExecNonSQL("UPDATE [" + sTableName + "] SET [fd_id] = " + iID2.ToString() + " WHERE [fd_id] = " +
iTempID.ToString()) != 0)
{
return true;
}
}
}
return false;
}
public bool SwapStringFieldValue(int iID1, int iID2, string sFieldName, string sTableName)
{
DataTable dt1 = ExecSQLQuery("SELECT [" + sFieldName + "] FROM [" + sTableName + "] WHERE fd_id = " + iID1.ToString());
object value1, value2;
if (dt1.Rows.Count > 0)
{
value1 = dt1.Rows[0][sFieldName];
DataTable dt2 = ExecSQLQuery("SELECT [" + sFieldName + "] FROM [" + sTableName + "] WHERE fd_id = " + iID2.ToString());
if (dt2.Rows.Count > 0)
{
value2 = dt2.Rows[0][sFieldName];
string ssql1 = "UPDATE [" + sTableName + "] SET [" + sFieldName + "]= \'" + value1.ToString() + "\' WHERE fd_id=" +
iID2.ToString();
string ssql2 = "UPDATE [" + sTableName + "] SET [" + sFieldName + "]= \'" + value2.ToString() + "\' WHERE fd_id=" +
iID1.ToString();
if (ExecNonSQL(ssql1) != 0)
{
return ExecNonSQL(ssql2) != 0;
}
}
}
return false;
}
/// <summary>
/// 创建时间： 2020-06-20 最后一次修改时间：2020-06-20
/// </summary>
/// <param name="sIDValue"></param>
/// <param name="sFieldName"></param>
/// <param name="sFieldValue"></param>
/// <param name="sTableName"></param>
/// <param name="sModuleName"></param>
/// <returns></returns>
public bool MIS_SetValueFromID(string sIDValue, string sFieldName, string sFieldValue, string sTableName,string sModuleName)
{
if(MIS_Global.LoginUser.CanModuleWrite(sModuleName))
{
return UpdateValueFromID(sIDValue,sFieldName, sFieldValue, sTableName);
}
return false;
}
/// <summary>
/// 创建时间： 2020-06-20 最后一次修改时间：2020-06-20
/// </summary>
/// <param name="sIDValue"></param>
/// <param name="sTableName"></param>
/// <returns></returns>
public bool DeleteFromID(string sIDValue, string sTableName)
{
string ssql = "DELETE FROM [" + sTableName + "] WHERE fd_id=" + sIDValue;
return ExecNonSQL(ssql) != 0;
}
/// <summary>
/// 创建时间： 2020-06-20 最后一次修改时间：2020-06-20
/// </summary>
/// <param name="sIDValue"></param>
/// <param name="sTableName"></param>
/// <param name="sModuleName"></param>
/// <returns></returns>
public bool MIS_DeleteFromID(string sIDValue, string sTableName, string sModuleName)
{
if (MIS_Global.LoginUser.CanModuleDelete(sModuleName))
{
return DeleteFromID(sIDValue, sTableName);
}
return false;
}
/// <summary>
/// 从索引号号得到某个字段的值
/// </summary>
/// <param name="sIDValue">索引号</param>
/// <param name="sFieldName">字段名</param>
/// <param name="sTableName">表名</param>
/// <returns>如果不存在，则返回空值</returns>
public string GetValueFromID(int iIDValue, string sFieldName, string sTableName)
{
return GetValueFromID(iIDValue.ToString(), sFieldName, sTableName);
}
/// <summary>
/// 同时近回两个字段的值
/// </summary>
/// <param name="sIDValue">记录ID</param>
/// <param name="sFieldName1">字段１</param>
/// <param name="sFieldName2">字段２</param>
/// <param name="sTableName">表名</param>
/// <returns></returns>
public Pair_<string, string> GetPairValueFormID(string sIDValue, string sFieldName1, string sFieldName2, string sTableName)
{
Pair_<string, string> lp = new Pair_<string, string> ();
DataTable dt = ExecSQLQuery("SELECT " + sFieldName1 + "," + sFieldName2 + " FROM " + sTableName +
" WHERE fd_id =" + sIDValue);
if (dt.Rows.Count > 0)
{
lp.First = dt.Rows[0][sFieldName1].ToString().Trim();
lp.Second = dt.Rows[0][sFieldName2].ToString().Trim();
}
return lp;
}
/// <summary>
/// 同时近回两个字段的值
/// </summary>
/// <param name="iIDValue">记录ID</param>
/// <param name="sFieldName1">字段１</param>
/// <param name="sFieldName2">字段２</param>
/// <param name="sTableName">表名</param>
/// <returns></returns>
public Pair_<string, string> GetPairValueFormID(int iIDValue, string sFieldName1, string sFieldName2, string sTableName)
{
return GetPairValueFormID(iIDValue.ToString(), sFieldName1, sFieldName2, sTableName);
}
/// <summary>
/// 从某个字段的值得到索引号，这个值必须是唯一的,字段的值必须是字符串，找到返回ID，否则返回－1
/// </summary>
/// <param name="sFieldName">字段名</param>
/// <param name="sValue">字段值</param>
/// <param name="sTableName">表名</param>
/// <returns>如找到，返回索引号，否则返回-1</returns>
public int GetIDFromValue(string sFieldName, string sValue, string sTableName)
{
DataTable dt = ExecSQLQuery("SELECT fd_id FROM " + sTableName +
" WHERE " + sFieldName + " = \'" + lg.CheckSQLString(sValue.Trim()) + "\'");
if (dt.Rows.Count > 0)
{
return (int)dt.Rows[0]["fd_id"];
}
else
{
return -1;
}
}
/// <summary>
/// 从某个字段的值得到索引号，这个值必须是唯一的,字段的值必须是字符串，找到返回ID，否则返回－1
/// </summary>
/// <param name="sFieldName"></param>
/// <param name="sValue"></param>
/// <param name="sTableName"></param>
/// <param name="sCondition">条件</param>
/// <returns></returns>
public int GetIDFromValue(string sFieldName, string sValue, string sTableName, string sCondition)
{
DataTable dt = ExecSQLQuery("SELECT fd_id FROM " + sTableName +
" WHERE " + sFieldName + " = \'" + lg.CheckSQLString(sValue.Trim()) + "\' AND " + sCondition);
if (dt.Rows.Count > 0)
{
return (int)dt.Rows[0]["fd_id"];
}
else
{
return -1;
}
}
/// <summary>
/// 如创建了一个字典值，数组第一个无素是１,第二无素是ID,如果没有创建字典值，第一个元素是０,第二个元素还是ＩＤ，不充许字符串都是空格。
/// </summary>
/// <param name="sFieldName">字段名</param>
/// <param name="sValue">字段值，必须是符串</param>
/// <param name="sTableName">表名</param>
/// <returns>返回字典ＩＤ</returns>
public int[] addName(string sFieldName, string sValue, string sTableName)
{
if (sValue.Trim().Length == 0)
{
throw new System.Exception("字段值不能为空值！");
}
int[] il = new int[2];
int iid = GetMaxID(sTableName, "") + 1;
if (sValue.Trim().Length != 0)
{
il[1] = GetIDFromValue(sFieldName, sValue, sTableName);
if (il[1] == -1)
{
string ssql = "INSERT INTO " + sTableName + "(fd_id," + sFieldName + ") VALUES(" + iid.ToString() + ",\'" + lg.CheckSQLString(sValue) + "\')";
if (ExecNonSQL(ssql) != 0)
{
il[0] = 1; il[1] = iid;
}
else { throw new Exception("无法创建字典值“" + sValue + "”"); }
}
else
{
il[0] = 0;
}
}
else
{
throw new Exception("字段值不能为空！");
}
return il;
}
#if _WINDOWS_DESKTOP_
/// <summary>
/// 添加一个字段值，返回字段值的ID，如果有添加动作，则会在列表框中添另一个项。
/// 创建时间： ????-??-?? 最后一次修改时间：2020-05-30
/// </summary>
/// <param name="sFieldName"></param>
/// <param name="sValue"></param>
/// <param name="sTableName"></param>
/// <param name="lic"></param>
/// <returns></returns>
public int addName2(string sFieldName, string sValue, ComboBox lic, string sTableName)
{
int[] il = addName(sFieldName, sValue, sTableName);
if (il[0] != 0)
{
Pair_<int, string> p = new Pair_<int, string>(il[1], sValue.Trim());
lic.Items.Add(p); //把字典值添加到选择框
lic.SelectedItem = p;
}
return il[1];
}
/// <summary>
/// 创建时间： 2020-05-30 最后一次修改时间：2020-05-30
/// </summary>
/// <param name="sOldNameCN"></param>
/// <param name="sNewNameCN"></param>
/// <param name="lic"></param>
/// <param name="sTableName"></param>
/// <returns></returns>
public bool ModifyNameCN(string sOldNameCN, string sNewNameCN,ComboBox cb, string sTableName)
{
if (sOldNameCN.Trim() == sNewNameCN.Trim())
return false;
int iFindID = find_s(sTableName, "fd_name_cn", sNewNameCN);
if (iFindID != -1)
return false;
dict_table dtTable = new dict_table(sTableName);
if(!dtTable.readDataFromName_CN(sOldNameCN))
{
return false;
}
dtTable.fd_name_cn = sNewNameCN;
if(MIS_Global.LoginUser != null)
{
dtTable.fd_ntext_ext1 = "此记录由用户(" + MIS_Global.LoginUser.fd_user_name + ")在" + System.DateTime.Now.ToString() + "修改过！";
}
int iFind = cb.FindString(sOldNameCN);
if(iFind != -1)
{
cb.Items[iFind] = new Pair_<int, string>(dtTable.ID, dtTable.fd_name_cn);
cb.SelectedIndex = iFind;
}
return dtTable.UpdateSQL() != 0;
}
#elif _WINDOWS_WEB_
/// <summary>
/// 添加一个字段值，返回字段值的ID，如果有添加动作，则会在列表框中添另一个项。
/// </summary>
/// <param name="sFieldName"></param>
/// <param name="sValue"></param>
/// <param name="sTableName"></param>
/// <param name="lic"></param>
/// <returns></returns>
public int addName2(string sFieldName, string sValue, ListItemCollection lic, string sTableName)
{
int[] il = addName(sFieldName, sValue, sTableName);
if (il[0] != 0 && lic != null)
lic.Add(new ListItem(sValue, il[1].ToString())); //把字典值添加到选择框
return il[1];
}
#endif
///<summary>
///得到某个字段值的不重复的列表
///</summary>
public string[] GetDistinctTrimFieldValueList(string sFieldName, string sTableName, string sCondition)
{
string tmp = "SELECT Distinct " + sFieldName + " FROM " + sTableName;
if (sCondition.Trim().Length != 0)
{
tmp += " WHERE ";
tmp += sCondition;
}
DataTable dt = this.ExecSQLQuery(tmp);
if (dt.Rows.Count > 0)
{
string[] sArray = new string[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; ++i)
{
sArray[i] = dt.Rows[i][sFieldName].ToString().Trim();
}
return sArray;
}
return new string[0];
}
/// <summary>
/// 读出所有字段
/// </summary>
/// <param name="sTableName"></param>
/// <returns></returns>
public string getTableReadAllForCSharp(string sTableName)
{
string tmp = "";
if (dataFormat == DataFormat_.dfAccdb || dataFormat == DataFormat_.dfMDB)
{
}
else if (dataFormat == DataFormat_.dfSQLServer)
{
DataTable dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt.Rows.Count > 0)
{
LPairList<string,string> ls = new LPairList<string,string>();
string sid = dt.Rows[0]["id"].ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt.Rows.Count; ++i)
{
ls.Add(dt.Rows[i]["name"].ToString(), dt.Rows[i]["xtype"].ToString());
}
for (int j = 0; j < ls.Count; j++)
{
Pair_<string, string> sf = ls.GetIndex(j);
if (sf.Second == "56")//INTEGER
{
tmp = tmp + sf.First + "= (int)dt.Rows[0][\"" + sf.First + "\"];" + "\n";
}
else if (sf.Second == "122" || sf.Second == "62" || sf.Second == "60") //SMALLMONEY,FLOAT,MONEY
{
tmp = tmp + sf.First + "=Convert.ToSingle(dt.Rows[0][\"" + sf.First + "\"]);" + "\n";
}
else if (sf.Second == "58" || sf.Second == "61") //SMALLDATETIME,DATETIME
{
tmp = tmp + sf.First + "=Convert.ToDateTime(dt.Rows[0][\"" + sf.First + "\"]);" + "\n";
}
else if (sf.Second == "239" || sf.Second == "99") //NCHAR,NTEXT
{
tmp = tmp + sf.First + "= dt.Rows[0][\"" + sf.First + "\"].ToString();" + "\n";
}
else if (sf.Second == "34") //Images
{
//tmp += "protected Image ";
}
}
}
}
return tmp;
}
/// <summary>
/// 读出所有字段
/// </summary>
/// <param name="sTableName"></param>
/// <returns></returns>
public string getTableReadAllForJava(string sTableName)
{
string tmp = "";
if (dataFormat == DataFormat_.dfAccdb || dataFormat == DataFormat_.dfMDB)
{
}
else if (dataFormat == DataFormat_.dfSQLServer)
{
DataTable dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt.Rows.Count > 0)
{
LPairList<string,string> ls = new LPairList<string, string>();
string sid = dt.Rows[0]["id"].ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt.Rows.Count; ++i)
{
ls.Add(dt.Rows[i]["name"].ToString(), dt.Rows[i]["xtype"].ToString());
}
for (int j = 0; j < ls.Count; j++)
{
Pair_<string, string> sf = ls.GetIndex(j);
if (sf.Second == "56")//INTEGER
{
tmp = tmp + sf.First + "= dt.getInt(0,\"" + sf.First + "\");" + "\n";
}
else if (sf.Second == "122" || sf.Second == "62" || sf.Second == "60") //SMALLMONEY,FLOAT,MONEY
{
tmp = tmp + sf.First + "= dt.getFloat(0,\"" + sf.First + "\");" + "\n";
}
else if (sf.Second == "58" || sf.Second == "61") //SMALLDATETIME,DATETIME
{
tmp = tmp + sf.First + "= dt.getDate(0,\"" + sf.First + "\");" + "\n";
}
else if (sf.Second == "239" || sf.Second == "99") //NCHAR,NTEXT
{
tmp = tmp + sf.First + "= dt.getString(0,\"" + sf.First + "\");" + "\n";
}
else if (sf.Second == "34") //Images
{
//tmp += "protected Image ";
}
}
}
}
return tmp;
}
/// <summary>
/// 自动创建CSharp Insert语句
/// </summary>
/// <param name="sTableName"></param>
/// <returns></returns>
public string getTableInsertSQLForCSharp(string sTableName)
{
string tmp = "fd_id = GetNewID();" + "\n";
tmp += "ssql = \"INSERT INTO \" + m_sTableName + " + "\n";
tmp += "\"(";
if (dataFormat == DataFormat_.dfAccdb || dataFormat == DataFormat_.dfMDB)
{
}
else if (dataFormat == DataFormat_.dfSQLServer)
{
DataTable dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt.Rows.Count > 0)
{
LPairList<string, string> ls = new LPairList<string, string>();
string sid = dt.Rows[0]["id"].ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt.Rows.Count; ++i)
{
ls.Add(dt.Rows[i]["name"].ToString(), dt.Rows[i]["xtype"].ToString());
}
for (int j = 0; j < ls.Count - 1; j++)
{
Pair_<string, string> sf = ls.GetIndex(j);
tmp = tmp + sf.First + ",";
if ((j + 1) == (j + 1) / 5 * 5)
{
tmp += "\"+\n\"";
}
}
tmp += ls.GetIndex(ls.Count - 1).First + ") VALUES(\" + " + "\n";
for (int j = 0; j < ls.Count; j++)
{
Pair_<string, string> sf = ls.GetIndex(j);
if (sf.Second == "56" || sf.Second == "122" || sf.Second == "62" || sf.Second == "60") //INTEGER,SMALLMONEY,FLOAT,MONEY
{
if (j != ls.Count - 1)
{
if (sf.First == "fd_create_author_id" || sf.First == "fd_modify_author_id")
{
tmp = tmp + "\"\"+" + " GetLoginID()" + "+\",\" +" + "\n";
}
else
{
tmp = tmp + "\"\"+" + sf.First + ".ToString()" + "+\",\" +" + "\n";
}
}
else
{
tmp = tmp + "\"\"+" + sf.First + ".ToString()" + "+\")\";" + "\n";
}
}
else if (sf.Second == "58" || sf.Second == "61") //SMALLDATETIME,DATETIME
{
if (j != ls.Count - 1)
{
if (sf.First == "fd_create_time" || sf.First == "fd_modify_time")
{
tmp = tmp + "\"\\\'\"+" + "System.DateTime.Now.ToString() " + "+\"\\',\"+" + "\n";
}
else
{
tmp = tmp + "\"\\\'\"+" + sf.First + ".ToShortDateString()" + "+\"\\',\"+" + "\n";
}
}
else
{
tmp = tmp + "\"\\\'\"+" + sf.First + ".ToShortDateString()" + "+\"\\',)\";" + "\n";
}
}
else if (sf.Second == "239" || sf.Second == "99" || sf.Second == "231") //NCHAR,NTEXT,NVARCHAR
{
if (j != ls.Count - 1)
{
tmp = tmp + "\"\\\'\"+" + "lg.CheckSQLString(" + sf.First + ")" + "+\"\\',\"+" + "\n";
}
else
{
tmp = tmp + "\"\\\'\"+" + "lg.CheckSQLString(" + sf.First + ")" + "+\"\\')\";" + "\n";
}
}
else if (sf.Second == "34") //Images
{
if (j != ls.Count - 1)
{
tmp = tmp + "\"\"+" + "请除去IMAGE数据" + "+\",\" +" + "\n";
}
else
{
tmp = tmp + "\"\"+" + "请除去IMAGE数据" + "+\")\";" + "\n";
}
}
else
{
throw new System.Exception(sf.Second.ToString());
}
}
}
}
return tmp;
}
/// <summary>
/// 自动创建Java Insert语句
/// </summary>
/// <param name="sTableName"></param>
/// <returns></returns>
public string getTableInsertSQLForJava(string sTableName)
{
string tmp = "fd_id = GetNewID();" + "\n";
tmp += "ssql = \"INSERT INTO \" + m_sTableName + " + "\n";
tmp += "\"(";
if (dataFormat == DataFormat_.dfAccdb || dataFormat == DataFormat_.dfMDB)
{
}
else if (dataFormat == DataFormat_.dfSQLServer)
{
DataTable dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt.Rows.Count > 0)
{
LPairList<string, string> ls = new LPairList<string, string>();
string sid = dt.Rows[0]["id"].ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt.Rows.Count; ++i)
{
ls.Add(dt.Rows[i]["name"].ToString(), dt.Rows[i]["xtype"].ToString());
}
for (int j = 0; j < ls.Count - 1; j++)
{
Pair_<string, string> sf = ls.GetIndex(j);
tmp = tmp + sf.First + ",";
if ((j + 1) == (j + 1) / 5 * 5)
{
tmp += "\"+\n\"";
}
}
tmp += ls.GetIndex(ls.Count - 1).First + ") VALUES(\" + " + "\n";
for (int j = 0; j < ls.Count; j++)
{
Pair_<string, string> sf = ls.GetIndex(j);
if (sf.Second == "56" || sf.Second == "122" || sf.Second == "62" || sf.Second == "60") //INTEGER,SMALLMONEY,FLOAT,MONEY
{
if (j != ls.Count - 1)
{
if (sf.First == "fd_create_author_id" || sf.First == "fd_modify_author_id")
{
tmp = tmp + "\"\"+" + "MIS_Global.getLoginID()" + "+\",\" +" + "\n";
}
else
{
tmp = tmp + "\"\"+" + sf.First + "+\",\" +" + "\n";
}
}
else
{
tmp = tmp + "\"\"+" + sf.First + "+\")\";" + "\n";
}
}
else if (sf.Second == "58" || sf.Second == "61") //SMALLDATETIME,DATETIME
{
if (j != ls.Count - 1)
{
if (sf.First == "fd_create_time" || sf.First == "fd_modify_time")
{
tmp = tmp + "\"\\\'\"+" + "MIS_Global.strCurrentTime() " + "+\"\\',\"+" + "\n";
}
else
{
tmp = tmp + "\"\\\'\"+ lg.strDate(" + sf.First + ")" + "+\"\\',\"+" + "\n";
}
}
else
{
tmp = tmp + "\"\\\'\"+ lg.strDate(" + sf.First + ")" + "+\"\\',)\";" + "\n";
}
}
else if (sf.Second == "239" || sf.Second == "99" || sf.Second == "231") //NCHAR,NTEXT,NVARCHAR
{
if (j != ls.Count - 1)
{
tmp = tmp + "\"\\\'\"+" + "lg.CheckSQLString(" + sf.First + ")" + "+\"\\',\"+" + "\n";
}
else
{
tmp = tmp + "\"\\\'\"+" + "lg.CheckSQLString(" + sf.First + ")" + "+\"\\')\";" + "\n";
}
}
else if (sf.Second == "34") //Images
{
if (j != ls.Count - 1)
{
tmp = tmp + "\"\"+" + "请除去IMAGE数据" + "+\",\" +" + "\n";
}
else
{
tmp = tmp + "\"\"+" + "请除去IMAGE数据" + "+\")\";" + "\n";
}
}
else
{
throw new System.Exception(sf.Second.ToString());
}
}
}
}
return tmp;
}
/// <summary>
/// 自动创建Create语句
/// </summary>
/// <param name="sTableName"></param>
/// <returns></returns>
public string getTableUpdateSQLForCSharp(string sTableName)
{
string tmp = "ssql =\"UPDATE \"+ m_sTableName + \" SET \";" + "\n";
if (dataFormat == DataFormat_.dfAccdb || dataFormat == DataFormat_.dfMDB)
{
}
else if (dataFormat == DataFormat_.dfSQLServer)
{
DataTable dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt.Rows.Count > 0)
{
LPairList<string, string> ls = new LPairList<string, string>();
string sid = dt.Rows[0]["id"].ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt.Rows.Count; ++i)
{
ls.Add(dt.Rows[i]["name"].ToString(), dt.Rows[i]["xtype"].ToString());
}
for (int j = 0; j < ls.Count; j++)
{
Pair_<string, string> sf = ls.GetIndex(j);
if (sf.Second == "56" || sf.Second == "122" || sf.Second == "62" || sf.Second == "60") //INTEGER,SMALLMONEY,FLOAT,MONEY
{
if (j != ls.Count - 1)
{
if (sf.First == "fd_create_author_id")
{
}
else if (sf.First == "fd_modify_author_id")
{
tmp = tmp + "ssql+= \"" + sf.First + "=\"+" + " GetLoginID()" + "+\",\";" + "\n";
}
else
{
tmp = tmp + "ssql+= \"" + sf.First + "=\"+" + sf.First + ".ToString()" + "+\",\";" + "\n";
}
}
else
{
tmp = tmp + "ssql+= \"" + sf.First + "=\"+" + sf.First + ".ToString()" + "+\"\";" + "\n";
}
}
else if (sf.Second == "58" || sf.Second == "61") //SMALLDATETIME,DATETIME
{
if (j != ls.Count - 1)
{
if (sf.First == "fd_create_time")
{
}
else if (sf.First == "fd_modify_time")
{
tmp = tmp + "ssql+= \"" + sf.First + "=\\\'\"+" + "System.DateTime.Now.ToString() " + "+\"\\',\";" + "\n";
}
else
{
tmp = tmp + "ssql+= \"" + sf.First + "=\\\'\"+" + sf.First + ".ToShortDateString()" + "+\"\\',\";" + "\n";
}
}
else
{
tmp = tmp + "ssql+= \"" + sf.First + "=\\\'\"+" + sf.First + ".ToShortDateString()" + "+\"\\'\";" + "\n";
}
}
else if (sf.Second == "239" || sf.Second == "99" || sf.Second == "231") //NCHAR,NTEXT,NVARCHAR
{
if (j != ls.Count - 1)
{
tmp = tmp + "ssql+= \"" + sf.First + "=\\\'\"+ lg.CheckSQLString(" + sf.First + ")+\"\\',\";" + "\n";
}
else
{
tmp = tmp + "ssql+= \"" + sf.First + "=\\\'\"+ lg.CheckSQLString(" + sf.First + ")+\"\\'\";" + "\n";
}
}
else if (sf.Second == "34") //Images
{
if (j != ls.Count - 1)
{
tmp = tmp + "ssql+= \"" + sf.First + "=\"+" + "请除去IMAGE数据" + "+\",\";" + "\n";
}
else
{
tmp = tmp + "ssql+= \"" + sf.First + "=\"+" + "请除去IMAGE数据" + "+\"\";" + "\n";
}
}
}
}
}
tmp += "ssql += \" WHERE fd_id=\" + " + "fd_id.ToString(); ";
return tmp;
}
/// <summary>
/// 自动创建Create语句
/// </summary>
/// <param name="sTableName"></param>
/// <returns></returns>
public string getTableUpdateSQLForJava(string sTableName)
{
string tmp = "ssql =\"UPDATE \"+ m_sTableName + \" SET \";" + "\n";
if (dataFormat == DataFormat_.dfAccdb || dataFormat == DataFormat_.dfMDB)
{
}
else if (dataFormat == DataFormat_.dfSQLServer)
{
DataTable dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt.Rows.Count > 0)
{
LPairList<string, string> ls = new LPairList<string, string>();
string sid = dt.Rows[0]["id"].ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt.Rows.Count; ++i)
{
ls.Add(dt.Rows[i]["name"].ToString(), dt.Rows[i]["xtype"].ToString());
}
for (int j = 0; j < ls.Count; j++)
{
Pair_<string, string> sf = ls.GetIndex(j);
if (sf.Second == "56" || sf.Second == "122" || sf.Second == "62" || sf.Second == "60") //INTEGER,SMALLMONEY,FLOAT,MONEY
{
if (j != ls.Count - 1)
{
if (sf.First == "fd_create_author_id")
{
}
else if (sf.First == "fd_modify_author_id")
{
tmp = tmp + "ssql+= \"" + sf.First + "=\"+" + "MIS_Global.getLoginID()" + "+\",\";" + "\n";
}
else
{
tmp = tmp + "ssql+= \"" + sf.First + "=\"+" + sf.First + "+\",\";" + "\n";
}
}
else
{
tmp = tmp + "ssql+= \"" + sf.First + "=\"+" + sf.First + "+\"\";" + "\n";
}
}
else if (sf.Second == "58" || sf.Second == "61") //SMALLDATETIME,DATETIME
{
if (j != ls.Count - 1)
{
if (sf.First == "fd_create_time")
{
}
else if (sf.First == "fd_modify_time")
{
tmp = tmp + "ssql+= \"" + sf.First + "=\\\'\"+" + " MIS_Global.strCurrentTime() " + "+\"\\',\";" + "\n";
}
else
{
tmp = tmp + "ssql+= \"" + sf.First + "=\\\'\"+" + "lg.strDate(" + sf.First + " ) " + " +\"\\',\";" + "\n";
}
}
else
{
tmp = tmp + "ssql+= \"" + sf.First + "=\\\'\"+" + "lg.strDate(" + sf.First + ")" + "+\"\\'\";" + "\n";
}
}
else if (sf.Second == "239" || sf.Second == "99" || sf.Second == "231") //NCHAR,NTEXT,NVARCHAR
{
if (j != ls.Count - 1)
{
tmp = tmp + "ssql+= \"" + sf.First + "=\\\'\"+ lg.CheckSQLString(" + sf.First + ")+\"\\',\";" + "\n";
}
else
{
tmp = tmp + "ssql+= \"" + sf.First + "=\\\'\"+ lg.CheckSQLString(" + sf.First + ")+\"\\'\";" + "\n";
}
}
else if (sf.Second == "34") //Images
{
if (j != ls.Count - 1)
{
tmp = tmp + "ssql+= \"" + sf.First + "=\"+" + "请除去IMAGE数据" + "+\",\";" + "\n";
}
else
{
tmp = tmp + "ssql+= \"" + sf.First + "=\"+" + "请除去IMAGE数据" + "+\"\";" + "\n";
}
}
}
}
}
tmp += "ssql += \" WHERE fd_id=\" + " + "fd_id; ";
return tmp;
}
/// <summary>
/// 自动创建Access数据表语句
/// </summary>
/// <param name="sTableName"></param>
/// <returns></returns>
public string getCreateAccessTableForCSharp(string sTableName)
{
string tmp = "";
if (dataFormat == DataFormat_.dfAccdb || dataFormat == DataFormat_.dfMDB)
{
}
else if (dataFormat == DataFormat_.dfSQLServer)
{
tmp += "ADOX.Catalog catalog = new Catalog();" + "\n";
tmp += "ADODB.Connection cn = new ADODB.Connection();" + "\n";
tmp += "cn.Open(getConnectString(), null, null, -1);" + "\n";
tmp += "catalog.ActiveConnection = cn;" + "\n";
tmp += "//---------------------------------------------------------------------创建表" + "\n";
tmp += "ADOX.Table table = new ADOX.Table();" + "\n";
tmp += "table.Name = \"" + sTableName + "\";" + "\n";
tmp += "for (int i = 0; i < catalog.Tables.Count; ++i)" + "\n";
tmp += "{";
tmp += " \tif (catalog.Tables[i].Name == table.Name)" + "\n";
tmp += " \t{" + "\n";
tmp += "\t\treturn false;" + "\n";
tmp += " \t}" + "\n";
tmp += "}" + "\n";
tmp += "//--------------------------------------------------------------------创建字段" + "\n";
DataTable dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt.Rows.Count > 0)
{
string sid = dt.Rows[0]["id"].ToString();
dt = ExecSQLQuery("select [name],[xtype],[prec] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 1; i < dt.Rows.Count; ++i)
{
string sFieldName = dt.Rows[i]["name"].ToString();
int xtype = System.Convert.ToInt32(dt.Rows[i]["xtype"]);
tmp += "ADOX.Column " + sFieldName + " = new ADOX.Column();" + "\n";
tmp += sFieldName + ".ParentCatalog = catalog;" + "\n";
tmp += sFieldName + ".Name = " + "\"" + sFieldName + "\";" + "\n";
if (xtype == 56) //INTEGER
{
tmp += sFieldName + ".Type = DataTypeEnum.adInteger;" + "\n";
tmp += sFieldName + ".Properties[\"Description\"].Value = \"INTEGER\";" + "\n";
tmp += sFieldName + ".Properties[\"Default\"].Value = 1;" + "\n";
tmp += "table.Columns.Append(" + sFieldName + ", DataTypeEnum.adInteger, 0);" + "\n\n";
}
else if (xtype == 122)//SMALLMONEY
{
tmp += sFieldName + ".Type = DataTypeEnum.adCurrency;" + "\n";
tmp += sFieldName + ".Properties[\"Description\"].Value = \"SMALLMONEY\";" + "\n";
tmp += sFieldName + ".Properties[\"Default\"].Value = 1;" + "\n";
tmp += "table.Columns.Append(" + sFieldName + ", DataTypeEnum.adCurrency, 0);" + "\n\n";
}
else if (xtype == 63)//FLOAT
{
}
else if (xtype == 58 || xtype == 61) //SMALLDATETIME,DATETIME
{
tmp += sFieldName + ".Type = DataTypeEnum.adDate;" + "\n";
tmp += sFieldName + ".Properties[\"Description\"].Value = \"时间\";" + "\n";
tmp += sFieldName + ".Properties[\"Default\"].Value = \"Now()\";" + "\n";
tmp += "table.Columns.Append(" + sFieldName + ", DataTypeEnum.adDate, 0);" + "\n\n";
}
else if (xtype == 239) //NCHAR
{
tmp += sFieldName + ".Type = DataTypeEnum.adVarWChar;" + "\n";
tmp += sFieldName + ".DefinedSize = " + dt.Rows[i]["prec"].ToString() + ";" + "\n";
tmp += sFieldName + ".Properties[\"Description\"].Value = \"NCHAR\";" + "\n";
tmp += sFieldName + ".Properties[\"Default\"].Value = \"\";" + "\n";
tmp += "table.Columns.Append(" + sFieldName + ", DataTypeEnum.adVarWChar, " + dt.Rows[i]["prec"].ToString() + ");" + "\n\n";
}
else if (xtype == 99)//NTEXT
{
tmp += sFieldName + ".Type = DataTypeEnum.adLongVarWChar;" + "\n";
tmp += sFieldName + ".Properties[\"Description\"].Value = \"NTEXT\";" + "\n";
tmp += sFieldName + ".Properties[\"Default\"].Value = \"\";" + "\n";
tmp += "table.Columns.Append(" + sFieldName + ", DataTypeEnum.adLongVarWChar, 0);" + "\n\n";
}
else if (xtype == 34) //Images
{
tmp += sFieldName + ".Type = DataTypeEnum.adLongVarBinary;" + "\n";
tmp += sFieldName + ".Properties[\"Description\"].Value = \"adLongVarBinary\";" + "\n";
tmp += "table.Columns.Append(" + sFieldName + ", DataTypeEnum.adLongVarBinary, 0);" + "\n\n";
}
}
}
}
return tmp;
}
/// <summary>
/// 获取protected的字段声明
/// </summary>
/// <param name="sTableName"></param>
/// <returns></returns>
public string getTableFieldDeclareForCSharpProtected(string sTableName)
{
string tmp = "";
if (dataFormat == DataFormat_.dfAccdb || dataFormat == DataFormat_.dfMDB)
{
}
else if (dataFormat == DataFormat_.dfSQLServer)
{
DataTable dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt.Rows.Count > 0)
{
LPairList<string, string> ls = new LPairList<string, string>();
string sid = dt.Rows[0]["id"].ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt.Rows.Count; ++i)
{
ls.Add(dt.Rows[i]["name"].ToString(), dt.Rows[i]["xtype"].ToString());
}
for (int j = 0; j < ls.Count; j++)
{
Pair_<string, string> sf = ls.GetIndex(j);
if (sf.Second == "56") //INTEGER IDENTITY (1, 1) PRIMARY KEY,
{
tmp += "protected int ";
}
else if (sf.Second == "58") //SMALLDATETIME
{
tmp += "protected DateTime ";
}
else if (sf.Second == "239") //NCHAR
{
tmp += "protected string ";
}
else if (sf.Second == "122") //SMALLMONEY
{
tmp += "protected float ";
}
else if (sf.Second == "99") //NTEXT
{
tmp += "protected string ";
}
else if (sf.Second == "61") //DATETIME
{
tmp += "protected DateTime ";
}
else if (sf.Second == "34") //Images
{
tmp += "protected Image ";
}
else if (sf.Second == "62" || sf.Second == "60") //FLOAT,MONEY
{
tmp += "protected float ";
}
tmp = tmp + " " + sf.First + ";\n";
}
}
}
return tmp;
}
/// <summary>
/// 获取public的字段声明
/// </summary>
/// <param name="sTableName"></param>
/// <returns></returns>
public string getTableFieldDeclareForCSharpPublic1(string sTableName)
{
string tmp = "";
if (dataFormat == DataFormat_.dfAccdb || dataFormat == DataFormat_.dfMDB)
{
}
else if (dataFormat == DataFormat_.dfSQLServer)
{
DataTable dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt.Rows.Count > 0)
{
LPairList<string, string> ls = new LPairList<string, string>();
string sid = dt.Rows[0]["id"].ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt.Rows.Count; ++i)
{
ls.Add(dt.Rows[i]["name"].ToString(), dt.Rows[i]["xtype"].ToString());
}
for (int j = 0; j < ls.Count; j++)
{
Pair_<string, string> sf = ls.GetIndex(j);
if (sf.First == "fd_id")
{
tmp += "//";
}
else if (sf.First == "fd_create_time")
{
tmp += "//";
}
else if (sf.First == "fd_modify_time")
{
tmp += "//";
}
else if (sf.First == "fd_create_author_id")
{
tmp += "//";
}
else if (sf.First == "fd_modify_author_id")
{
tmp += "//";
}
else if (sf.First == "fd_nchar_ext1")
{
tmp += "//";
}
else if (sf.First == "fd_nchar_ext2")
{
tmp += "//";
}
else if (sf.First == "fd_ntext_ext1")
{
tmp += "//";
}
else if (sf.First == "fd_ntext_ext2")
{
tmp += "//";
}
else if (sf.First == "fd_integer_ext1")
{
tmp += "//";
}
else if (sf.First == "fd_integer_ext2")
{
tmp += "//";
}
else if (sf.First == "fd_float_ext1")
{
tmp += "//";
}
else if (sf.First == "fd_float_ext2")
{
tmp += "//";
}
else if (sf.First == "fd_desc")
{
tmp += "//";
}
if (sf.Second == "56") //INTEGER IDENTITY (1, 1) PRIMARY KEY,
{
tmp += "public int " + sf.First.Substring(3, sf.First.Length - 3);
}
else if (sf.Second == "58") //SMALLDATETIME
{
tmp += "public DateTime " + sf.First.Substring(3, sf.First.Length - 3);
}
else if (sf.Second == "239") //NCHAR
{
tmp += "public string " + sf.First.Substring(3, sf.First.Length - 3);
}
else if (sf.Second == "122") //SMALLMONEY
{
tmp += "public float " + sf.First.Substring(3, sf.First.Length - 3);
}
else if (sf.Second == "99") //NTEXT
{
tmp += "public string " + sf.First.Substring(3, sf.First.Length - 3);
}
else if (sf.Second == "61") //DATETIME
{
tmp += "public DateTime " + sf.First.Substring(3, sf.First.Length - 3);
}
else if (sf.Second == "34") //Images
{
tmp += "public Image " + sf.First.Substring(3, sf.First.Length - 3);
}
else if (sf.Second == "62" || sf.Second == "60") //FLOAT,MONEY
{
tmp += "public float " + sf.First.Substring(3, sf.First.Length - 3);
}
tmp += "{ get{return " + sf.First + ";} set{" + sf.First + "=value;} }";
tmp += "\n";
}
}
}
return tmp;
}
/// <summary>
/// 获取public的字段声明
/// </summary>
/// <param name="sTableName"></param>
/// <returns></returns>
public string getTableFieldDeclareForCSharpPublic2(string sTableName)
{
string tmp = "";
if (dataFormat == DataFormat_.dfAccdb || dataFormat == DataFormat_.dfMDB)
{
}
else if (dataFormat == DataFormat_.dfSQLServer)
{
DataTable dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt.Rows.Count > 0)
{
LPairList<string, string> ls = new LPairList<string, string>();
string sid = dt.Rows[0]["id"].ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt.Rows.Count; ++i)
{
ls.Add(dt.Rows[i]["name"].ToString(), dt.Rows[i]["xtype"].ToString());
}
for (int j = 0; j < ls.Count; j++)
{
Pair_<string, string> sf = ls.GetIndex(j);
if (sf.Second == "56") //INTEGER IDENTITY (1, 1) PRIMARY KEY,
{
tmp += "public int " + sf.First.Substring(3, sf.First.Length - 3) + "\n";
}
else if (sf.Second == "58") //SMALLDATETIME
{
tmp += "public DateTime " + sf.First.Substring(3, sf.First.Length - 3) + "\n";
}
else if (sf.Second == "239") //NCHAR
{
tmp += "public string " + sf.First.Substring(3, sf.First.Length - 3) + "\n";
}
else if (sf.Second == "122") //SMALLMONEY
{
tmp += "public float " + sf.First.Substring(3, sf.First.Length - 3) + "\n";
}
else if (sf.Second == "99") //NTEXT
{
tmp += "public string " + sf.First.Substring(3, sf.First.Length - 3) + "\n";
}
else if (sf.Second == "61") //DATETIME
{
tmp += "public DateTime " + sf.First.Substring(3, sf.First.Length - 3) + "\n";
}
else if (sf.Second == "34") //Images
{
tmp += "public Image " + sf.First.Substring(3, sf.First.Length - 3) + "\n";
}
else if (sf.Second == "62" || sf.Second == "60") //FLOAT,MONEY
{
tmp += "public float " + sf.First.Substring(3, sf.First.Length - 3) + "\n";
}
tmp += "{\n";
tmp += "\tget\n";
tmp += "\t{\n";
tmp += "\t\treturn " + sf.First + ";" + "\n";
tmp += "\t}\n";
tmp += "\tset\n";
tmp += "\t{\n";
tmp += "\t\t" + sf.First + "=value;" + "\n";
tmp += "\t}\n";
tmp += "}\n";
tmp += "\n\n";
}
}
}
return tmp;
}
public string getTableFieldDeclareForCSharpPublic3(string sTableName)
{
string tmp = "";
if (dataFormat == DataFormat_.dfAccdb || dataFormat == DataFormat_.dfMDB)
{
}
else if (dataFormat == DataFormat_.dfSQLServer)
{
DataTable dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt.Rows.Count > 0)
{
LPairList<string, string> ls = new LPairList<string, string>();
string sid = dt.Rows[0]["id"].ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt.Rows.Count; ++i)
{
ls.Add(dt.Rows[i]["name"].ToString(), dt.Rows[i]["xtype"].ToString());
}
for (int j = 0; j < ls.Count; j++)
{
Pair_<string, string> sf = ls.GetIndex(j);
if (sf.Second == "56") //INTEGER IDENTITY (1, 1) PRIMARY KEY,
{
tmp += "public int ";
}
else if (sf.Second == "58") //SMALLDATETIME
{
tmp += "public DateTime ";
}
else if (sf.Second == "239") //NCHAR
{
tmp += "public string ";
}
else if (sf.Second == "122") //SMALLMONEY
{
tmp += "public float ";
}
else if (sf.Second == "99") //NTEXT
{
tmp += "public string ";
}
else if (sf.Second == "61") //DATETIME
{
tmp += "public DateTime ";
}
else if (sf.Second == "34") //Images
{
tmp += "public Image ";
}
else if (sf.Second == "62" || sf.Second == "60") //FLOAT,MONEY
{
tmp += "public float ";
}
tmp = tmp + " " + sf.First + ";\n";
}
}
}
return tmp;
}
//public List<Field_> GetFileds
/// <summary>
/// 获取字段信息描述
/// </summary>
/// <param name="sTableName">表名</param>
/// <returns></returns>
public string GetAllFieldDesc(string sTableName)
{
string tmp = "";
if (dataFormat == DataFormat_.dfAccdb || dataFormat == DataFormat_.dfMDB)
{
throw new Exception("函数未完成！");
}
else if (dataFormat == DataFormat_.dfSQLServer)
{
DataTable dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt.Rows.Count > 0)
{
LPairList<string, string> ls = new LPairList<string, string>();
string sid = dt.Rows[0]["id"].ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt.Rows.Count; ++i)
{
ls.Add(dt.Rows[i]["name"].ToString(), dt.Rows[i]["xtype"].ToString());
}
for (int j = 0; j < ls.Count; j++)
{
Pair_<string, string> sf = ls.GetIndex(j);
tmp += sf.First;
tmp += " ";
tmp += "SQLServer类型:" + Field_.GetSQLServerXTypeString(Convert.ToInt32(sf.Second)) + "(xtype值：" + sf.Second + ")";
tmp += "\t";
tmp += "C#类型:" + Field_.SQLServerXTYPConverToCSharpTypeName(Convert.ToInt32(sf.Second));
tmp += "\n";
}
}
}
else
{
throw new Exception("函数未完成！");
}
return tmp;
}
public string GetFieldDesc(string sFieldName, string sTableName)
{
if (dataFormat == DataFormat_.dfSQLServer)
{
string ssql = "select [syscolumns].[name],[syscolumns].[xtype] FROM [sysobjects],[syscolumns] where [syscolumns].[id] = [sysobjects].[id] AND [sysobjects].[name] = \'";
ssql += sTableName;
ssql += "\'";
ssql += " AND [syscolumns].[name] = \'";
ssql += sFieldName;
ssql += "\'";
DataTable dt = ExecSQLQuery(ssql);
string tmp = "";
foreach (DataRow dr in dt.Rows)
{
tmp += dr["name"].ToString();
tmp += "\t";
tmp += "SQLServer类型:" + Field_.GetSQLServerXTypeString(Convert.ToInt32(dr["xtype"])) + "(xtype值:" + dr["xtype"].ToString() + ")";
tmp += "\t";
tmp += "C#类型:" + Field_.SQLServerXTYPConverToCSharpTypeName(Convert.ToInt32(dr["xtype"]));
tmp += "\n";
}
return tmp;
}
else
{
throw new Exception("函数未完成！");
}
}
public List<Field_> GetAllFieldInfo(string sTableName)
{
List<Field_> lResult = new List<Field_>();
if (dataFormat == DataFormat_.dfSQLServer)
{
string ssql = "select [syscolumns].[name],[syscolumns].[xtype] FROM [sysobjects],[syscolumns] where [syscolumns].[id] = [sysobjects].[id] AND [sysobjects].[name] = \'";
ssql += sTableName;
ssql += "\'";
DataTable dt = ExecSQLQuery(ssql);
foreach (DataRow dr in dt.Rows)
{
Field_ tmpNew = new Field_();
tmpNew.Name = dr["name"].ToString();
tmpNew.SetSQLServerXType(System.Convert.ToInt32(dr["xtype"]));
lResult.Add(tmpNew);
}
}
else
{
throw new Exception("函数未完成！");
}
return lResult;
}
public Field_ GetFieldInfo(string sFieldName, string sTableName)
{
Field_ fResult = new Field_();
if (dataFormat == DataFormat_.dfSQLServer)
{
string ssql = "select [syscolumns].[name],[syscolumns].[xtype] FROM [sysobjects],[syscolumns] where [syscolumns].[id] = [sysobjects].[id] AND [sysobjects].[name] = \'";
ssql += sTableName;
ssql += "\'";
ssql += " AND [syscolumns].[name] = \'";
ssql += sFieldName;
ssql += "\'";
DataTable dt = ExecSQLQuery(ssql);
foreach (DataRow dr in dt.Rows)
{
Field_ tmpNew = new Field_();
tmpNew.Name = dr["name"].ToString();
tmpNew.SetSQLServerXType(System.Convert.ToInt32(dr["xtype"]));
return tmpNew;
}
}
else
{
throw new Exception("函数未完成！");
}
return fResult;
}
/*
///<summary>
///得到某个字段值的列表
///</summary>
public void GetTrimFieldValueList(string sFieldName, string sTableName, string sSQL, List<string> sl)
{
if (sl != null)
{
sl.Clear();
string tmp;
if (sSQL.Trim().Length == 0)
{
tmp = "SELECT " + sFieldName + " FROM " + sTableName;
}
else
{
tmp = sSQL;
}
DataTable dt = this.ExecSQLQuery(tmp, false);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
sl.Add(dt.Rows[i][sFieldName].ToString().Trim());
}
}
}
}
*/
#if _WINDOWS_WEB_
///<summary>
///得到某个字段值的列表
///</summary>
public void GetDistinctTrimFieldValueList(string sFieldName, string sTableName, string sCondition, ListItemCollection sList)
{
if (sList != null)
{
sList.Clear();
string tmp;
if (sCondition.Trim().Length == 0)
{
tmp = "SELECT Distinct " + sFieldName + " FROM " + sTableName;
}
else
{
tmp = "SELECT Distinct " + sFieldName + " FROM " + sTableName + " WHERE " + sCondition;
}
DataTable dt = this.ExecSQLQuery(tmp);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
sList.Add(dt.Rows[i][sFieldName].ToString().Trim());
}
}
}
}
///<summary>
///得到某个字段值的列表
///</summary>
public void GetTrimFieldValueList(string sFieldName, string sTableName, string sCondition, ListItemCollection sList)
{
if (sList != null)
{
sList.Clear();
string tmp;
if (sCondition.Trim().Length == 0)
{
tmp = "SELECT Distinct " + sFieldName + " FROM " + sTableName;
}
else
{
tmp = "SELECT Distinct " + sFieldName + " FROM " + sTableName + " WHERE " + sCondition;
}
DataTable dt = this.ExecSQLQuery(tmp);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
sList.Add(dt.Rows[i][sFieldName].ToString().Trim());
}
}
}
}
#endif
///<summary>
///得到某个字段值的列表
///</summary>
public StringList_ GetTrimFieldValueList(string sFieldName, string sTableName, string sCondition)
{
StringList_ sResult = new StringList_();
string tmp;
if (sCondition.Trim().Length == 0)
{
tmp = "SELECT Distinct " + sFieldName + " FROM " + sTableName;
}
else
{
tmp = "SELECT Distinct " + sFieldName + " FROM " + sTableName + " WHERE " + sCondition;
}
DataTable dt = this.ExecSQLQuery(tmp);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
sResult.Add(dt.Rows[i][sFieldName].ToString().Trim());
}
}
return sResult;
}
/// <summary>
/// 获取表的两个字段值
/// </summary>
/// <param name="sFieldName1"></param>
/// <param name="sFieldName2"></param>
/// <param name="sTableName"></param>
/// <param name="sCondition"></param>
/// 创建时间：2020-05-07 最后一次修改时间：2020-05-07
/// <returns></returns>
public LStringPairList GetTrimFieldValuePairList(string sFieldName1, string sFieldName2, string sTableName, string sCondition = "")
{
LStringPairList plResult = new LStringPairList();
string ssql = "SELECT [" + sFieldName1 + "],[" + sFieldName2 + "] FROM [" + sTableName + "]";
if (sCondition.Trim().Length != 0)
{
ssql += " WHERE " + sCondition;
}
DataTable dt = ExecSQLQuery(ssql);
foreach (DataRow dr in dt.Rows)
{
string s1 = dr[sFieldName1].ToString().Trim();
string s2 = dr[sFieldName2].ToString().Trim();
plResult.Add(s1, s2);
}
return plResult;
}
/// <summary>
/// 查字段值为oFieldValue的第一条记录，如果找到，则返回第一条记录的ID，否则返回－1；
/// 注意，字段类型必须为字符串
/// </summary>
/// <param name="sTableName">表名</param>
/// <param name="sFieldName">字段名</param>
/// <param name="sFileValue">字段值</param>
/// <param name="s_condition">条件,可以为空</param>
/// 创建时间： 约 2008-01-01 最后一次修改时间：2020-03-25
/// <returns>如果找到，则返回第一条记录的ID，否则返回－1，字符串字段</returns>
public int find_s(string sTableName, string sFieldName, string sFileValue, string s_condition = "")
{
string ssql = "SELECT [fd_id],[" + sFieldName + "] FROM [" + sTableName + "] WHERE ["
+ sFieldName + "] =\'" + lg.CheckSQLString(sFileValue) + "\'";
if (s_condition.Trim() != "")
ssql += " AND " + s_condition;
DataTable dt = ExecSQLQuery(ssql);
if (dt.Rows.Count > 0)
{
return (int)dt.Rows[0]["fd_id"];
}
return -1;
}
/// <summary>
/// 检查数据库是否存在表名sTableName
/// </summary>
/// <param name="sTableName">表名</param>
/// <returns></returns>
public bool IsExistTableName(string sTableName)
{
#if _WINDOWS_PLATFORM_
bool bResult = false;
if (dataFormat == DataFormat_.dfAccdb || dataFormat == DataFormat_.dfMDB)
{
ADOX.Catalog catalog = new Catalog();
ADODB.Connection cn = new ADODB.Connection();
cn.Open(GetConnection().ConnectionString, null, null, -1);
catalog.ActiveConnection = cn;
for (int i = 0; i < catalog.Tables.Count; ++i)
{
if (catalog.Tables[i].Name.ToLower() == sTableName.Trim().ToLower())
{
bResult = true;
break;
}
}
cn.Close();
catalog.ActiveConnection = null;
}
else if (dataFormat == DataFormat_.dfSQLServer)
{
DataTable dt = ExecSQLQuery("SELECT [name] FROM sysobjects WHERE type = \'U\' ORDER BY [name]");
for (int i = 0; i < dt.Rows.Count; ++i)
{
if (dt.Rows[i]["NAME"].ToString().ToLower() == sTableName.Trim().ToLower())
{
bResult = true;
break;
}
}
}
return bResult;
#else
throw new Exception(lg.OnCodeDidNotFinishError);
#endif
}
#if _WINDOWS_DESKTOP_
/// <summary>
/// 把执行的结果集在DataGridView中显示
/// </summary>
/// <param name="sSQL">SQL语句</param>
/// <param name="dg_view">DataGridView</param>
/// <returns>返回执行结果集</returns>
public DataTable ExecSQLView(string sSQL, System.Windows.Forms.DataGridView dg_view)
{
DataTable dt = new DataTable();
if (sSQL.Trim().Length == 0) return dt;
try
{
GetViewDbDataAdapter().SelectCommand.CommandText = sSQL;
GetViewDbDataAdapter().Fill(dt);
}
catch(Exception e)
{
lg.ShowError(e.ToString(), "Db_.ExecSQLView");
lg.ShowError("执行 SQL语句： \n " + sSQL + "\n出错！", "Db_.ExecSQLView");
}
if (dg_view != null)
{
dg_view.DataSource = dt;
//gvView.DataBind();
//for(int i=0; i<gvView.Columns.Count; ++i)
//{
// gvView.Columns[i].HeaderStyle.Width = 80;
//}
}
return dt;
}
#endif
#if _WINDOWS_WEB_
/// <summary>
/// 把执行的结果集在DataGridView中显示
/// </summary>
/// <param name="sSQL">SQL语句</param>
/// <param name="dg_view">DataGridView</param>
/// <returns>返回执行结果集</returns>
public DataTable ExecSQLView(string sSQL, System.Web.UI.WebControls.GridView dg_view)
{
DataTable dt = new DataTable();
if (sSQL.Trim().Length == 0) return dt;
getViewDbDataAdapter().SelectCommand.CommandText = sSQL;
getViewDbDataAdapter().Fill(dt);
if (dg_view != null)
{
dg_view.DataSource = dt;
//gvView.DataBind();
//for(int i=0; i<gvView.Columns.Count; ++i)
//{
// gvView.Columns[i].HeaderStyle.Width = 80;
//}
}
return dt;
}
#endif
#if _WINDOWS_DESKTOP_
///<summary>
///得到某个字段值的列表
///</summary>
public void GetTrimFieldValueList(string sFieldName, string sTableName, string sCondition, ComboBox cb)
{
cb.Items.Clear();
String ssql = "SELECT [" + sFieldName + "] FROM [" + sTableName + "]";
if (sCondition.Trim().Length != 0)
{
ssql += " WHERE " + sCondition;
}
DataTable dt = this.ExecSQLQuery(ssql);
foreach (DataRow dr in dt.Rows)
{
cb.Items.Add(dr[sFieldName].ToString().Trim());
}
}
/// <summary>
/// 创建时间： 2020-05-23 最后一次修改时间：2020-05-31
/// 获取两个字段的值，在ComboBox中显示第二个字段的字符串值，如果有第三个字段，则在括号显示第三个字段。
/// 显示格式：LPairt( FieldName1, FieldName2(FieldName3 + FieldName4 + .....) )
/// </summary>
/// <param name="sIntFieldName">字段1,字段1必须是int整</param>
/// <param name="sSplitFieldName">可以多个字段，用分隔符","表示</param>
/// <param name="sTableName">表名</param>
/// <param name="sCondition">条件</param>
/// <param name="cb">ComboBox</param>
public void GetPairFieldValueList(string sIntFieldName,string sSplitFieldName, string sTableName, string sCondition,ComboBox cb)
{
cb.Items.Clear();
UStringListCI_ sFileNameList = sSplitFieldName._SplitCI(",",true);
string ssql = "SELECT [" + sIntFieldName + "]";
if (sFileNameList.Count != 0)
{
foreach(string s in sFileNameList)
{
ssql += ",[" + s + "]";
}
}
ssql += " FROM[" + sTableName + "]";
if (sCondition.Trim().Length != 0)
{
ssql += " WHERE " + sCondition;
}
DataTable dt = this.ExecSQLQuery(ssql);
foreach (DataRow dr in dt.Rows)
{
Pair_<int, string> p = new Pair_<int, string>();
p.First = (int)dr[sIntFieldName];
p.Second = "";
if(sFileNameList.Count > 0)
{
p.Second += dr[sFileNameList[0]].ToString().Trim();
string sTemp = "";
for (int i = 1; i < sFileNameList.Count; ++i)
{
sTemp += dr[sFileNameList[i]].ToString().Trim();
}
if(sTemp != "")
{
p.Second = p.Second + "(" + sTemp + ")";
}
}
cb.Items.Add(p);
}
}
///<summary>
///得到某个字段值的列表
///</summary>
public void GetTrimFieldValueList(string sFieldName, string sTableName, string sSQL, ListBox lb)
{
if (lb == null || sTableName.Trim().Length == 0)
throw new Exception("lb == null || sTableName.Trim().Length == 0");
lb.Items.Clear();
string tmp;
if (sSQL.Trim().Length == 0)
{
tmp = "SELECT [" + sFieldName + "] FROM " + sTableName;
}
else
{
tmp = sSQL;
}
DataTable dt = this.ExecSQLQuery(tmp);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
lb.Items.Add(dt.Rows[i][sFieldName].ToString().Trim());
}
}
}
public void GetIDAndNameCNList(string sTableName,string sCondition, ListView lv)
{
if (lv == null || sTableName.Trim().Length == 0)
throw new Exception("lv == null || sTableName.Trim().Length == 0");
lv.Items.Clear();
string sSQL = "SELECT [fd_id],[fd_name_cn] FROM [" + sTableName + "] ";
if (sCondition.Trim().Length != 0)
{
sSQL += " WHERE ";
sSQL += sCondition;
}
DataTable dt = this.ExecSQLQuery(sSQL);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
ListViewItem lvi = new ListViewItem();
lvi.Text = dt.Rows[i]["fd_name_cn"].ToString().Trim();
lvi.Name = dt.Rows[i]["fd_id"].ToString();
lvi.StateImageIndex = i;
lvi.ImageIndex = i;
lv.Items.Add(lvi);
}
}
}
/// <summary>
/// 获取所有表
/// </summary>
/// <param name="il"></param>
public void getTableNames(IList il)
{
if (dataFormat == DataFormat_.dfAccdb || dataFormat == DataFormat_.dfMDB)
{
ADOX.Catalog catalog = new Catalog();
ADODB.Connection cn = new ADODB.Connection();
cn.Open(GetConnection().ConnectionString, null, null, -1);
catalog.ActiveConnection = cn;
for (int i = 0; i < catalog.Tables.Count; ++i)
{
il.Add(catalog.Tables[i].Name);
}
cn.Close();
catalog.ActiveConnection = null;
}
else if (dataFormat == DataFormat_.dfSQLServer)
{
DataTable dt = ExecSQLQuery("SELECT [name] FROM sysobjects WHERE type = \'U\' ORDER BY [name]");
for (int i = 0; i < dt.Rows.Count; ++i)
{
il.Add(dt.Rows[i]["NAME"].ToString());
}
}
}
public StringList_ getTableNames()
{
StringList_ ls = new StringList_();
if (dataFormat == DataFormat_.dfAccdb || dataFormat == DataFormat_.dfMDB)
{
ADOX.Catalog catalog = new Catalog();
ADODB.Connection cn = new ADODB.Connection();
cn.Open(GetConnection().ConnectionString, null, null, -1);
catalog.ActiveConnection = cn;
for (int i = 0; i < catalog.Tables.Count; ++i)
{
ls.Add(catalog.Tables[i].Name);
}
cn.Close();
catalog.ActiveConnection = null;
}
else if (dataFormat == DataFormat_.dfSQLServer)
{
DataTable dt = ExecSQLQuery("SELECT [name] FROM sysobjects WHERE type = \'U\' ORDER BY [name]");
for (int i = 0; i < dt.Rows.Count; ++i)
{
ls.Add(dt.Rows[i]["NAME"].ToString());
}
}
else if(dataFormat == DataFormat_.dfSQLite)
{
string ssql = "SELECT [name] FROM sqlite_master WHERE type = \'table\' ORDER BY [name]";
DataTable dt = ExecSQLQuery(ssql);
for (int i = 0; i < dt.Rows.Count; ++i)
{
ls.Add(dt.Rows[i]["name"].ToString());
}
}
return ls;
}
/// <summary>
/// 获取某个表所有的字段名
/// </summary>
/// <param name="sTableName"></param>
public void getFields(string sTableName, IList il)
{
if (dataFormat == DataFormat_.dfSQLServer)
{
DataTable dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt.Rows.Count > 0)
{
il.Clear();
string sid = dt.Rows[0]["id"].ToString();
dt = ExecSQLQuery("select [name] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt.Rows.Count; ++i)
{
il.Add(dt.Rows[i]["name"].ToString());
}
}
}
}
public StringList_ getFields(string sTableName)
{
StringList_ slResult = new StringList_();
string ssql = "";
if (dataFormat == DataFormat_.dfSQLServer)
{
ssql = "SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'";
DataTable dt = ExecSQLQuery(ssql);
if (dt.Rows.Count > 0)
{
string sid = dt.Rows[0]["id"].ToString();
dt = ExecSQLQuery("select [name] FROM [syscolumns] where [id]=\'" + sid + "\' ORDER BY [colorder]");
for (int i = 0; i < dt.Rows.Count; ++i)
{
slResult.Add(dt.Rows[i]["name"].ToString());
}
}
}
else if (dataFormat == DataFormat_.dfSQLite)
{
ssql = "PRAGMA table_info([" + sTableName + "])";
DataTable dt = ExecSQLQuery(ssql);
foreach(DataRow dr in dt.Rows)
{
slResult.Add(dr["name"].ToString());
}
}
return slResult;
}
///<summary>
///得到某个字段值的不重复的列表
///</summary>
public void GetDistinctTrimFieldValueList(string sFieldName, string sTableName, string sSQL, ComboBox cb)
{
if (cb != null)
{
cb.Items.Clear();
string tmp;
if (sSQL.Trim().Length == 0)
{
tmp = "SELECT DISTINCT " + sFieldName + " FROM " + sTableName;
}
else
{
tmp = sSQL;
}
DataTable dt = this.ExecSQLQuery(tmp);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
cb.Items.Add(dt.Rows[i][sFieldName].ToString().Trim());
}
}
}
}
#endif
public static string getColumnType(string sType)
{
string value = null;
if (sType == "string")
{
value = " text ";
}
else if (sType == "int")
{
value = " integer ";
}
else if (sType == "bool")
{
value = " boolean ";
}
else if (sType == "float")
{
value = " float ";
}
else if (sType == "double")
{
value = " double ";
}
else if (sType == "char")
{
value = " varchar ";
}
else if (sType == "long")
{
value = " long ";
}
/*
* SQLite 没有一个单独的存储类用于存储日期和/或时间，但SQLite是能够存储日期和时间为TEXT，REAL或INTEGER值。
日期格式
TEXT A date in a format like "YYYY-MM-DD HH:MM:SS.SSS".
REAL The number of days since noon in Greenwich on November 24, 4714 B.C.
INTEGER The number of seconds since 1970-01-01 00:00:00 UTC.
*/
else if (sType == "DateTime")
{
value = " text ";
}
return value;
}
/// <summary>
/// 创建时间：2016-11-25 最后一次修改时间：2016-11-25
/// 第一，以类名作为表名；第二，凡是以fd_开头定义的成员均作为字段名，第三、自动创建fd_id为AUTOINCREMENT
/// </summary>
/// <typeparam name="T">所有创建表的类，其中以类名作为表名</typeparam>
/// <param name="df">数据库格式</param>
/// <returns>返回创建表的的SQL语句</returns>
public static string getCreateTableSql<T>(DataFormat_ df)
{
StringBuilder sb = new StringBuilder();
if (df == DataFormat_.dfSQLite)
{
//将类名作为表名
string sTableName = typeof(T).Name; // Utils.getTableName(clazz);
sb.Append("create table ").Append(sTableName).Append(" (fd_id INTEGER PRIMARY KEY AUTOINCREMENT,\n ");
foreach (FieldInfo fi in typeof(T).GetFields(BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance))
{
string sFieldName = fi.Name;
string sFileType = fi.FieldType.Name;
if (sFieldName.IndexOf("fd_id") == -1 && sFieldName.IndexOf("fd_") != -1)
{
string sValue = "";
if (sFileType == "String") { sValue = " text "; }
else if (sFileType == "Int32") { sValue = " integer "; }
else if (sFileType == "Int32") { sValue = " integer "; }
else if (sFileType == "Int64") { sValue = " long "; }
/*
* SQLite 没有一个单独的存储类用于存储日期和/或时间，但SQLite是能够存储日期和时间为TEXT，REAL或INTEGER值。
日期格式
TEXT A date in a format like "YYYY-MM-DD HH:MM:SS.SSS".
REAL The number of days since noon in Greenwich on November 24, 4714 B.C.
INTEGER The number of seconds since 1970-01-01 00:00:00 UTC.
*/
else if (sFileType == "Single") { sValue = " float "; }
else if (sFileType == "DateTime") { sValue = " text "; }
else if (sFileType == "Boolean") { sValue = " boolean "; }
else if (sFileType == "Char") { sValue = " varchar "; }
else if (sFileType == "Double") { sValue = " double "; }
else if (sFileType == "MemoryStream") { sValue = " unknown "; }
if (sValue != "")
sb.Append(sFieldName).Append(sValue).Append(",\n ");
}
}//---------------foreach
sb.Replace(",\n", ")", sb.Length - 3, 3); //括号收尾
}
else if (df == DataFormat_.dfSQLServer)
{
}
else if (df == DataFormat_.dfAccdb)
{
}
return sb.ToString();
}//-------------------------------getCreateTableSql
}//--------------------------------------------------------------------Db_
}//------------------------------------------------------------------------------------------lf
csharp_db.h
/****************************************************************************************
创建时间 ：2006年12月19日
文件名 ：csharp_db.cs => csharp_db.h
功能 ：数据库处理
作者 ：李锋
Email ：runzhilf@139.com
联系电话 ：13828778863
AdKeyPrimary 关键字是主关键字。
AdKeyForeign 关键字是外部关键字。
AdKeyUnique 关键字是唯一的。
需要添加的引用:
(1)Microsoft SQLDMO Object Library
(2)Microsoft ADO Ext. 6.0 for DDL and Security
(3)Microsoft ActiveX Data Objects 2.8 Library
(4)C:\Program Files\Common Files\System\ado\msado15.dll
----------------------------------------------最后一次修改时间：2021年07月03日
*****************************************************************************/
#pragma once
///
#include "_old_mis_framework_in.h"
///
using namespace System::Collections;
using namespace ADOX;
///
_LF_BEGIN_
/
/// <summary>
/// 数据访问格式
/// </summary>
enum class csharp_DataFormat
{
dfMDB, //Access2000,2003数据库
dfAccdb, //2007数据库
dfDBF,
dfDB,
dfInterBase,
dfSQLServer, //SQL数据库
dfOracle, //Oracle数据库
dfSybase,
dfInformix,
dfDB2,
dfSQLite, //Android数据库
dfMySQL
};
/// <summary>
/// 数据编缉状态
/// </summary>
enum class csharp_EditorStatus
{
esView, //查看状态
esUpdate, //更新状态
esDelete, //删除状态
esAppend, //添加状态
esNull //未设置
};
/// <summary>
/// 记录改变状态
/// </summary>
enum class csharp_DataChange
{
dcNot, //没有改变
dcUpdate, //记录已更新
dcDelete, //记录已删除
dcInsert, //记录已添加
dcSelect, //记录已选择
/// <summary>
/// 记录已改变，可能是删除，可以是添加，可能是修改
/// </summary>
dcChanged
};
/// <summary>
/// 数据类型
/// </summary>
enum class csharp_DataType
{
//----------------------------------------------------------------C#数据类型
/// <summary>
/// 8位无符号整数
/// </summary>
dtByte = 1,
/// <summary>
///16位无符号整数
/// </summary>
dtInt16 = 2,
/// <summary>
///32位无符号整数
///</summary>
dtInt32 = 3,
/// <summary>
///64位无符号整数
///</summary>
dtInt64 = 4,
/// <summary>
/// 小数
/// </summary>
dtFloat = 5,
/// <summary>
/// 小数
/// </summary>
dtDouble = 6,
/// <summary>
/// 时间日期
/// </summary>
dtDateTime = 7,
/// <summary>
/// 字符串
/// </summary>
dtString = 8,
/// <summary>
/// 对象 例：Image数据
/// </summary>
dtObject = 9,
//--------------------------------------------------------------------自定义数据类型
/// <summary>
/// 正数或0
/// </summary>
dtPlusNumberOrZero = 21,
/// <summary>
/// 负数或0
/// </summary>
dtNegativeOrZero = 22,
/// <summary>
/// 正整数
/// </summary>
dtPositiveInteger = 23,
/// <summary>
/// 正整数或0
/// </summary>
dtPositiveIntegerOrZero = 24,
/// <summary>
/// 正数
/// </summary>
dtPlusNumber = 25,
/// <summary>
/// 整数
/// </summary>
dtJavaInteger,
/// <summary>
/// 小数
/// </summary>
dtJavaFloat,
/// <summary>
/// 双精度小数
/// </summary>
dtJavaDouble,
/// <summary>
/// 时间日期
/// </summary>
dtJavaDateTime,
/// <summary>
/// 字符串
/// </summary>
dtJavaString,
/// <summary>
/// 图片，二进制数据
/// </summary>
dtJavaBinaryStream,
/// <summary>
/// tinyint TINYINT 1字节 (-128，127) (0，255) 小整数值
/// </summary>
dtJavaBoolean,
/// <summary>
/// byte[]
/// </summary>
dtJavaByteArray,
/// <summary>
///未知数据类型
/// </summary>
dtNULL = -1,
};
ref class csharp_Field
{
public:
/// <summary>
/// 字段名
/// </summary>
String ^Name;
/// <summary>
/// 字段值
/// </summary>
String ^Value;
/// <summary>
/// 字段类型
/// </summary>
csharp_DataType DataType;
/// <summary>
/// 字段描述
/// </summary>
String ^Desc;
csharp_Field(String^ sName, String^ sValue, csharp_DataType dt);
csharp_Field();
int GetSQLServerXType();
void SetSQLServerXType(int iXTypeVale);
/// <summary>
/// 把SQLServer xtype值转换为 C# 数据类型
/// </summary>
/// <param name="iXTypeVale"></param>
/// <returns></returns>
static Type^ SQLServerXTYPConverToCSharpType(int iXTypeVale);
/// <summary>
/// 把SQLServer xtype值转换为 C# 数据类型名的字符串
/// </summary>
/// <param name="iXTypeVale"></param>
/// <returns></returns>
static String^ SQLServerXTYPConverToCSharpTypeName(int iXTypeVale);
/// <summary>
/// 以字符串表示的SQLServer数据类型
/// </summary>
/// <param name="iXTypeVale"></param>
/// <returns></returns>
static String^ GetSQLServerXTypeString(int iXTypeVale);
/// <summary>
/// SqlDbType转换为C#数据类型
/// </summary>
/// <param name="sqlType"></param>
/// <returns></returns>
static Type^ SqlDbTypeConvertToCSharpType(SqlDbType sqlType);
/// <summary>
/// sql server数据类型（如：varchar）, 转换为SqlDbType类型
/// </summary>
/// <param name="sqlTypeString"></param>
/// <returns></returns>
static SqlDbType XTypeStringConverToSqlDbType(String^ sXTypeString);
};
/
ref class csharp_db
{
public:
/// <summary>
/// 数据库格式
/// </summary>
csharp_DataFormat dataFormat;
/// <summary>
/// 用户名
/// </summary>
String^ user_name;
/// <summary>
/// 数据库名子
/// </summary>
String^ database_name;
/// <summary>
/// 数据库密码
/// </summary>
String^ user_password;
/// <summary>
/// 数据源
/// </summary>
String^ database_source;
public:
/// <summary>
/// DB-Engines 数据库流行度排行榜 9 月更新已发布，排名前二十如下：总体排名和上个月相比基本一致，
/// 其中排名前三的 Oracle、MySQL 和 Microsoft SQL Server 也是分数增加最多的三个数据库，对于
/// 很多做互联网的同学来说，Oracle和Microsoft SQL Server排名前
/// </summary>
static array<String^>^ DbManufacturerList = gcnew array<String^>{
_t("Oracle"), _t("MySQL"), _t("Microsoft SQL Server"), _t("PostgreSQL"), _t("MongoDB"), _t("Redis"),
_t("IBM Db2"), _t("Elasticsearch"), _t("SQLite"), _t("Cassandra"), _t("Microsoft Access"), _t("MariaDB"),
_t("Splunk"), _t("Hive"), _t("Microsoft Azure SQL Database"), _t("Amazon DynamoDB"), _t("Teradata"),
_t("Neo4j"), _t("SAP HAHA"), _t("FileMaker") };
//-------------------------------------------------------------构造
csharp_db(csharp_DataFormat df)
{
dataFormat = df;
}
//-----------------------------------------------------------------------属性重写
//-------------------------------------------------------------方法重写
/// <summary>
/// 执行特定的SQL内容
/// </summary>
/// <param name="sCaptionName">标题名</param>
/// <param name="sCheckTableName">需要检查的表名</param>
/// <returns></returns>
virtual bool exec_dict_sql_content(String^ sCaptionName, String^ sCheckTableName);
virtual bool ExecSQLText(String^ sText);
virtual bool ExecSQLFile(String^ sFileName);
virtual DbConnection^ GetConnection();
virtual DbDataAdapter^ GetViewDbDataAdapter();
virtual int ExecNonSQL(String^ sSQL);
/// <summary>
/// 返回记录条数
/// </summary>
/// <param name="sTableName">表句</param>
/// <returns></returns>
int getRecordCount(String^ sTableName);
/// <summary>
/// 返回最后一条记录的某个字段值
/// </summary>
/// <param name="sFileName"></param>
/// <returns></returns>
Object^ getFieldValueForLastRecord(String^ sFieldName, String^ sTableName, String^ sCondition);
/// <summary>
/// 获取最后一条记录。 创建时间：2014-04-16
/// </summary>
/// <param name="sTableName">表名</param>
/// <returns>如果成功，返回最后一记录，否则返回NULL</returns>
DataRow^ getLastRecord(String^ sTableName);
/// <summary>
/// 获取第一条记录。 创建时间：2014-04-16
/// </summary>
/// <param name="sTableName">表名</param>
/// <returns>如果成功，返回第一条记录，否则返回NULL</returns>
DataRow^ getFirstRecord(String^ sTableName);
/// <summary>
/// 在目录sPath下创建一个数据库。
/// </summary>
/// <param name="sDatabaseName">数据库名</param>
/// <param name="sPath">路径名</param>
/// 创建时间：????-??-?? 最后一次修改时间：2020-04-03
/// <returns>如果成功，则返回空字符串，失败返回错误原因。</returns>
virtual String^ CreateDatabase(String^ sDatabaseName, String^ sPath);
/// <summary>
/// 创建一个系统数据库，如果数据库存在或者创建成功，返回true
/// </summary>
/// <param name="sPath"></param>
/// <returns></returns>
static bool createAppRepository(String^ sPath);
/// <summary>
/// 判断是否存在数据库sDatabaseName
/// </summary>
/// <param name="sDatabaseName">数据库名</param>
/// 创建时间：2020-03-03 最后一次修改时间： 2021-07-04
/// <returns></returns>
static bool IsExistDatabase(String^ sDatabaseName);
/// <summary>
/// 这个表的作用是保存软件使用者的公司的必要信息。
/// </summary>
virtual bool create_app_co_user()
{
return false;
}
/// <summary>
/// 这个表的作用是保存软件使用者的个人必要信息。
/// </summary>
/// <returns></returns>
virtual bool create_app_ind_user()
{
return false;
}
/// <summary>
/// 所有可用模块集合
/// </summary>
/// <param name="ConnectionString"></param>
virtual bool create_app_module()
{
return false;
}
/// <summary>
/// 如果个人通信薄类型不存在，则创建
/// </summary>
/// <param name="ConnectionString"></param>
virtual bool create_ind_individual_addressbook_type()
{
return false;
}
/// <summary>
/// 如模块表不存在，则自动创建
/// </summary>
/// <param name="ConnectionString"></param>
virtual bool create_ind_module()
{
return false;
}
/// <summary>
/// 如临时模块不存在,则自动创建
/// </summary>
/// <returns></returns>
virtual bool create_ind_module_tmp()
{
return false;
}
/// <summary>
/// 如果系统表不存在，则自动创建
/// </summary>
/// <returns></returns>
virtual bool create_System()
{
return false;
}
/// <summary>
/// 创建审批流表
/// </summary>
/// <returns></returns>
virtual bool create_co_approval_flow()
{
return true;
}
//------------------------------------------------------------------------------------个人相关模块
virtual bool create_dict_notepad()
{
return true;
}
/// <summary>
/// 个人相关.sql(工作计划,工作日志,审批请求,审阅审批,使用设备,集团通迅录,个人通迅录,个人信息,记事本,建议与改进,使用帮助)
/// </summary>
/// <returns></returns>
virtual bool create_个人相关()
{
return true;
}
/// <summary>
/// 如果ind_notepad表不存在，则创建ind_notepad表。
/// </summary>
/// <param name="cn"></param>
/// <returns></returns>
virtual bool create_ind_notepad()
{
return false;
}
/// <summary>
/// 如果ind_payout表不存在，则创建ind_payout表。
/// </summary>
/// <param name="cn"></param>
/// <returns></returns>
virtual bool create_ind_payout()
{
return false;
}
/// <summary>
/// 创建个人收入类型表
/// </summary>
/// <param name="ConnectionString"></param>
virtual bool create_ind_payout_type()
{
return false;
}
/// <summary>
/// 创建银行存款数量表
/// </summary>
/// <param name="cn"></param>
/// <returns></returns>
virtual bool create_ind_bank_cash()
{
return false;
}
/// <summary>
/// 银行取款表。
/// </summary>
/// <param name="cn"></param>
/// <returns></returns>
virtual bool create_ind_bank_debits()
{
return false;
}
/// <summary>
/// 银行存款表。
/// </summary>
/// <param name="cn"></param>
/// <returns></returns>
virtual bool create_ind_bank_deposit()
{
return false;
}
/// <summary>
/// 现金计数表
/// </summary>
/// <param name="cn"></param>
/// <returns></returns>
virtual bool create_ind_cash()
{
return false;
}
/// <summary>
/// 创建客户表
/// </summary>
/// <param name="cn"></param>
/// <returns></returns>
virtual bool create_co_customer()
{
return false;
}
/// <summary>
///创建每天收入表
/// </summary>
/// <param name="cn"></param>
/// <returns></returns>
virtual bool create_ind_earning()
{
return false;
}
/// <summary>
/// 创建每天收入类型表
/// </summary>
/// <param name="ConnectionString"></param>
virtual bool create_ind_earning_type()
{
return false;
}
/// <summary>
/// 创建个人通信薄
/// </summary>
/// <returns></returns>
virtual bool create_ind_individual_addressbook()
{
return false;
}
/// <summary>
/// 创建自然人表
/// </summary>
/// <returns></returns>
virtual bool create_crm_natural_person()
{
return false;
}
/// <summary>
/// 创建公司表
/// </summary>
/// <returns></returns>
virtual bool create_crm_company()
{
return false;
}
/// <summary>
/// 创建关系表
/// </summary>
/// <returns></returns>
virtual bool create_crm_relation()
{
return false;
}
/// <summary>
/// 创建银行帐户
/// </summary>
/// <returns></returns>
virtual bool create_fc_bank()
{
return false;
}
/// <summary>
/// 创建项目表
/// </summary>
/// <returns></returns>
virtual bool create_crm_project()
{
return false;
}
/// <summary>
/// 创建员工表
/// </summary>
/// <returns></returns>
virtual bool create_crm_employee()
{
return false;
}
/// <summary>
///
/// </summary>
/// 创建时间： 2021-10-03 最后一次修改时间：2021-10-03
/// <returns></returns>
virtual bool create_crm_rote()
{
return false;
}
/// <summary>
/// 个人通信录视图
/// </summary>
/// <returns></returns>
virtual bool create_pro_crm_np_AddressBook_view()
{
return false;
}
/// <summary>
/// 创建职位表
/// </summary>
/// <returns></returns>
virtual bool create_co_job()
{
return false;
}
/// <summary>
/// 创建部门表
/// </summary>
/// <returns></returns>
virtual bool create_co_department()
{
return false;
}
/// <summary>
/// 创建供应商品信息
/// </summary>
/// <returns></returns>
virtual bool create_co_supplier()
{
return false;
}
/// <summary>
/// 创建送货记录
/// </summary>
/// <returns></returns>
virtual bool create_co_deliver_goods()
{
return false;
}
/// <summary>
/// 创建送货记录分析表
/// </summary>
/// <returns></returns>
virtual bool create_co_deliver_goods_statistic()
{
return false;
}
/// <summary>
/// 创建拿货记录分析表
/// </summary>
/// <returns></returns>
virtual bool create_co_stock_statistic()
{
return false;
}
/// <summary>
/// 创建存货记录
/// </summary>
/// <returns></returns>
virtual bool create_co_stock()
{
return false;
}
/// <summary>
/// 创建新闻分类表
/// </summary>
/// <returns></returns>
virtual bool create_co_news_class()
{
return false;
}
/// <summary>
/// 创建新闻信息表
/// </summary>
/// <returns></returns>
virtual bool create_co_news_info()
{
return false;
}
/// <summary>
/// 资金借出记录
/// </summary>
/// <returns></returns>
virtual bool create_co_loan()
{
return false;
}
/// <summary>
/// 资产管理 ------------------------商品表
/// </summary>
/// <returns></returns>
virtual bool create_dict_merchandise()
{
return false;
}
/// <summary>
/// 资产管理 ------------------------资产管理
/// </summary>
/// <returns></returns>
virtual bool create_资产管理()
{
return false;
}
/// <summary>
/// 资产视图
/// </summary>
/// <returns></returns>
virtual bool create_pro_assets_view()
{
return false;
}
/// <summary>
/// 资产分类视图
/// </summary>
/// <returns></returns>
virtual bool create_pro_assets_class_view()
{
return false;
}
//---------------------------------------------------------------------------------合同管理模块
/// <summary>
/// 创建合同管理模块
/// </summary>
/// <returns></returns>
virtual bool create_crm_contract()
{
return false;
}
/// <summary>
/// 创建每天支出记录
/// </summary>
/// <returns></returns>
virtual bool create_co_payout()
{
return false;
}
virtual bool create_co_login_info()
{
return false;
}
virtual bool create_co_runtime_parameter()
{
return false;
}
virtual bool create_co_runtime_user()
{
return false;
}
/// <summary>
/// 创建每天支出记录的触发器
/// </summary>
/// <returns></returns>
virtual bool create_co_payout_trigger()
{
return false;
}
/// <summary>
/// 创建现金记录
/// </summary>
/// <returns></returns>
virtual bool create_co_cash()
{
return false;
}
/// <summary>
/// 创建定价表
/// </summary>
/// <returns></returns>
virtual bool create_co_pricing_of_product()
{
return false;
}
/**
* 函数名：create_crm_natural_person
* 作用： 在数据库sDBName中创建表crm_natural_person
* 参数：[sDBName]数据库名
* 返回值：boolean
* 作者：李锋
* 创建时间：2020/1/26 22:21
* 最后一次修改日期：2020/1/26 22:21
*/
static bool create_crm_natural_person(String^ sDBName);
/// <summary>
/// 在数据库sDBName中创建表sTableName
/// 在AppRepository数据库中，必须存在dict_sql这个表，在这个表中保存有创建表的SQL语句。
/// </summary>
/// <param name="sTableName">表名</param>
/// <param name="sDBName">数据库名</param>
/// 创建时间：2020/02/09 最后一次修改时间：2020/02/09
/// <returns>如果成功，返回真</returns>
static bool create_table(String^ sTableName, String^ sDatabaseName);
//--------------------------------------------------------------操作
/// <summary>
/// 把sSourceTableName的数据拷贝到sDestTable，并清空sDestTableName表的数据，注意，两个表结构必须一样的
/// </summary>
/// <param name="sDestTableName">目标表，这个表原有的数据会清空</param>
/// <param name="dbDest">目标数据库</param>
/// <param name="sSourceTableName">源数据库的表名</param>
/// 创建时间：2020-05-02 最后一次修改时间：2020-05-02
/// <param name="dbSource">源数据库</param>
static void TableCopy(String^ sDestTableName, csharp_db^ dbDest, String^ sSourceTableName,
csharp_db^ dbSource);
DataTable^ ExecSQLQuery(String^ sSQL);
/// <summary>
/// 返回最大的索引号，如果表中没有记录，则返回0
/// </summary>
/// <param name="sTableName"></param>
/// <param name="sCondition"></param>
/// <returns></returns>
int GetMaxID(String^ sTableName, String^ sCondition);
/*
AVG(column) 返回某列的平均值
BINARY_CHECKSUM
CHECKSUM
CHECKSUM_AGG
Count(column) 返回某列的行数（不包括NULL值）
Count(*) 返回被选行数
Count(DISTINCT column) 返回相异结果的数目
First(column) 返回在指定的域中第一个记录的值（SQLServer2000 不支持）
LAST(column) 返回在指定的域中最后一个记录的值（SQLServer2000 不支持）
MAX(column) 返回某列的最高值
MIN(column) 返回某列的最低值
STDEV(column)
STDEVP(column)
SUM(column) 返回某列的总和
VAR(column)
VARP(column)
*/
/// <summary>
/// SUM(column) 返回某列的总和 (创建于：2014-04-16）
/// </summary>
/// <param name="sFieldName">列名</param>
/// <param name="sTable">表名</param>
/// <param name="sCondition">条件</param>
/// <returns>返回值</returns>
float fun_sum(String^ sFieldName, String^ sTable, String^ sCondition);
/// <summary>
/// 从索引号号得到某个字段的值
/// </summary>
/// <param name="sIDValue">索引号</param>
/// <param name="sFieldName">字段名</param>
/// <param name="sTableName">表名</param>
/// <returns>如果不存在，则返回空值</returns>
String^ GetValueFromID(String^ sIDValue, String^ sFieldName, String^ sTableName);
/// <summary>
/// 创建时间： 2020-06-20 最后一次修改时间：2020-06-20
/// </summary>
/// <param name="sIDValue">索此号</param>
/// <param name="sFieldName">字段名</param>
/// <param name="sFieldValue">字段值</param>
/// <param name="sTableName">表名</param>
/// <returns></returns>
bool UpdateValueFromID(String^ sIDValue, String^ sFieldName, String^ sFieldValue,
String^ sTableName);
/// <summary>
/// 创建时间： 2020-06-25 最后一次修改时间：2020-06-25
/// 交换两条记录的ID号
/// </summary>
/// <param name="iID1"></param>
/// <param name="iID2"></param>
/// <param name="sTableName"></param>
/// <returns></returns>
bool SwapID(int iID1, int iID2, String^ sTableName);
bool SwapStringFieldValue(int iID1, int iID2, String^ sFieldName, String^ sTableName);
/// <summary>
/// 创建时间： 2020-06-20 最后一次修改时间：2020-06-20
/// </summary>
/// <param name="sIDValue"></param>
/// <param name="sFieldName"></param>
/// <param name="sFieldValue"></param>
/// <param name="sTableName"></param>
/// <param name="sModuleName"></param>
/// <returns></returns>
bool MIS_SetValueFromID(String^ sIDValue, String^ sFieldName, String^ sFieldValue,
String^ sTableName, String^ sModuleName);
/// <summary>
/// 创建时间： 2020-06-20 最后一次修改时间：2020-06-20
/// </summary>
/// <param name="sIDValue"></param>
/// <param name="sTableName"></param>
/// <returns></returns>
bool DeleteFromID(String^ sIDValue, String^ sTableName);
/// <summary>
/// 创建时间： 2020-06-20 最后一次修改时间：2020-06-20
/// </summary>
/// <param name="sIDValue"></param>
/// <param name="sTableName"></param>
/// <param name="sModuleName"></param>
/// <returns></returns>
bool MIS_DeleteFromID(String^ sIDValue, String^ sTableName, String^ sModuleName);
/// <summary>
/// 从索引号号得到某个字段的值
/// </summary>
/// <param name="sIDValue">索引号</param>
/// <param name="sFieldName">字段名</param>
/// <param name="sTableName">表名</param>
/// <returns>如果不存在，则返回空值</returns>
String^ GetValueFromID(int iIDValue, String^ sFieldName, String^ sTableName);
/// <summary>
/// 同时近回两个字段的值
/// </summary>
/// <param name="sIDValue">记录ID</param>
/// <param name="sFieldName1">字段１</param>
/// <param name="sFieldName2">字段２</param>
/// <param name="sTableName">表名</param>
/// <returns></returns>
csharp_Pair<String^, String^>^ GetPairValueFormID(String^ sIDValue, String^ sFieldName1,
String^ sFieldName2, String^ sTableName);
/// <summary>
/// 同时近回两个字段的值
/// </summary>
/// <param name="iIDValue">记录ID</param>
/// <param name="sFieldName1">字段１</param>
/// <param name="sFieldName2">字段２</param>
/// <param name="sTableName">表名</param>
/// <returns></returns>
csharp_Pair<String^, String^>^ GetPairValueFormID(int iIDValue, String^ sFieldName1,
String^ sFieldName2, String^ sTableName);
/// <summary>
/// 从某个字段的值得到索引号，这个值必须是唯一的,字段的值必须是字符串，找到返回ID，否则返回－1
/// </summary>
/// <param name="sFieldName">字段名</param>
/// <param name="sValue">字段值</param>
/// <param name="sTableName">表名</param>
/// <returns>如找到，返回索引号，否则返回-1</returns>
int GetIDFromValue(String^ sFieldName, String^ sValue, String^ sTableName);
/// <summary>
/// 从某个字段的值得到索引号，这个值必须是唯一的,字段的值必须是字符串，找到返回ID，否则返回－1
/// </summary>
/// <param name="sFieldName"></param>
/// <param name="sValue"></param>
/// <param name="sTableName"></param>
/// <param name="sCondition">条件</param>
/// <returns></returns>
int GetIDFromValue(String^ sFieldName, String^ sValue, String^ sTableName, String^ sCondition);
/// <summary>
/// 如创建了一个字典值，数组第一个无素是１,第二无素是ID,如果没有创建字典值，第一个元素是０,第二个元素还是ＩＤ，不充许字符串都是空格。
/// </summary>
/// <param name="sFieldName">字段名</param>
/// <param name="sValue">字段值，必须是符串</param>
/// <param name="sTableName">表名</param>
/// <returns>返回字典ＩＤ</returns>
csharp_Pair<int,int>^ addName(String^ sFieldName, String^ sValue, String^ sTableName);
#ifdef _WINDOWS_
/// <summary>
/// 添加一个字段值，返回字段值的ID，如果有添加动作，则会在列表框中添另一个项。
/// 创建时间： ????-??-?? 最后一次修改时间：2020-05-30
/// </summary>
/// <param name="sFieldName"></param>
/// <param name="sValue"></param>
/// <param name="sTableName"></param>
/// <param name="lic"></param>
/// <returns></returns>
int addName2(String^ sFieldName, String^ sValue, ComboBox^ lic, String^ sTableName);
/// <summary>
/// 创建时间： 2020-05-30 最后一次修改时间：2020-05-30
/// </summary>
/// <param name="sOldNameCN"></param>
/// <param name="sNewNameCN"></param>
/// <param name="lic"></param>
/// <param name="sTableName"></param>
/// <returns></returns>
bool ModifyNameCN(String^ sOldNameCN, String^ sNewNameCN, ComboBox^ cb,
String^ sTableName);
#elif _WINDOWS_WEB_
/// <summary>
/// 添加一个字段值，返回字段值的ID，如果有添加动作，则会在列表框中添另一个项。
/// </summary>
/// <param name="sFieldName"></param>
/// <param name="sValue"></param>
/// <param name="sTableName"></param>
/// <param name="lic"></param>
/// <returns></returns>
public int addName2(String^ sFieldName, String^ sValue, ListItemCollection lic, String^ sTableName)
{
int[] il = addName(sFieldName, sValue, sTableName);
if (il[0] != 0 && lic != null)
lic.Add(gcnew ListItem(sValue, il[1]->ToString())); //把字典值添加到选择框
return il[1];
}
#endif
///<summary>
///得到某个字段值的不重复的列表
///</summary>
csharp_StringList^ GetDistinctTrimFieldValueList(String^ sFieldName, String^ sTableName,
String^ sCondition);
/// <summary>
/// 读出所有字段
/// </summary>
/// <param name="sTableName"></param>
/// <returns></returns>
String^ getTableReadAllForCSharp(String^ sTableName);
/// <summary>
/// 读出所有字段
/// </summary>
/// <param name="sTableName"></param>
/// <returns></returns>
String^ getTableReadAllForJava(String^ sTableName);
/// <summary>
/// 自动创建CSharp Insert语句
/// </summary>
/// <param name="sTableName"></param>
/// <returns></returns>
String^ getTableInsertSQLForCSharp(String^ sTableName);
/// <summary>
/// 自动创建Java Insert语句
/// </summary>
/// <param name="sTableName"></param>
/// <returns></returns>
String^ getTableInsertSQLForJava(String^ sTableName);
/// <summary>
/// 自动创建Create语句
/// </summary>
/// <param name="sTableName"></param>
/// <returns></returns>
String^ getTableUpdateSQLForCSharp(String^ sTableName);
/// <summary>
/// 自动创建Create语句
/// </summary>
/// <param name="sTableName"></param>
/// <returns></returns>
String^ getTableUpdateSQLForJava(String^ sTableName);
/// <summary>
/// 自动创建Access数据表语句
/// </summary>
/// <param name="sTableName"></param>
/// <returns></returns>
String^ getCreateAccessTableForCSharp(String^ sTableName);
/// <summary>
/// 获取protected的字段声明
/// </summary>
/// <param name="sTableName"></param>
/// <returns></returns>
String^ getTableFieldDeclareForCSharpProtected(String^ sTableName);
/// <summary>
/// 获取public的字段声明
/// </summary>
/// <param name="sTableName"></param>
/// <returns></returns>
String^ getTableFieldDeclareForCSharpPublic1(String^ sTableName);
/// <summary>
/// 获取public的字段声明
/// </summary>
/// <param name="sTableName"></param>
/// <returns></returns>
String^ getTableFieldDeclareForCSharpPublic2(String^ sTableName);
String^ getTableFieldDeclareForCSharpPublic3(String^ sTableName);
//public List<csharp_Field> GetFileds
/// <summary>
/// 获取字段信息描述
/// </summary>
/// <param name="sTableName">表名</param>
/// <returns></returns>
String^ GetAllFieldDesc(String^ sTableName);
String^ GetFieldDesc(String^ sFieldName, String^ sTableName);
csharp_DList<csharp_Field^>^ GetAllFieldInfo(String^ sTableName);
csharp_Field^ GetFieldInfo(String^ sFieldName, String^ sTableName);
/*
///<summary>
///得到某个字段值的列表
///</summary>
public void GetTrimFieldValueList(String^ sFieldName, String^ sTableName, String^ sSQL, List<String^> sl)
{
if (sl != null)
{
sl.Clear();
String^ tmp;
if (sSQL->Trim()->Length == 0)
{
tmp = "SELECT " + sFieldName + " FROM " + sTableName;
}
else
{
tmp = sSQL;
}
DataTable ^ dt = this->ExecSQLQuery(tmp, false);
if (dt->Rows->Count > 0)
{
for (int i = 0; i < dt->Rows->Count; i++)
{
sl.Add(dt->Rows[i][sFieldName]->ToString()->Trim());
}
}
}
}
*/
#if _WINDOWS_WEB_
///<summary>
///得到某个字段值的列表
///</summary>
public void GetDistinctTrimFieldValueList(String^ sFieldName, String^ sTableName, String^ sCondition, ListItemCollection sList)
{
if (sList != null)
{
sList.Clear();
String^ tmp;
if (sCondition->Trim()->Length == 0)
{
tmp = "SELECT Distinct " + sFieldName + " FROM " + sTableName;
}
else
{
tmp = "SELECT Distinct " + sFieldName + " FROM " + sTableName + " WHERE " + sCondition;
}
DataTable ^ dt = this->ExecSQLQuery(tmp);
if (dt->Rows->Count > 0)
{
for (int i = 0; i < dt->Rows->Count; i++)
{
sList.Add(dt->Rows[i][sFieldName]->ToString()->Trim());
}
}
}
}
///<summary>
///得到某个字段值的列表
///</summary>
public void GetTrimFieldValueList(String^ sFieldName, String^ sTableName, String^ sCondition, ListItemCollection sList)
{
if (sList != null)
{
sList.Clear();
String^ tmp;
if (sCondition->Trim()->Length == 0)
{
tmp = "SELECT Distinct " + sFieldName + " FROM " + sTableName;
}
else
{
tmp = "SELECT Distinct " + sFieldName + " FROM " + sTableName + " WHERE " + sCondition;
}
DataTable ^ dt = this->ExecSQLQuery(tmp);
if (dt->Rows->Count > 0)
{
for (int i = 0; i < dt->Rows->Count; i++)
{
sList.Add(dt->Rows[i][sFieldName]->ToString()->Trim());
}
}
}
}
#endif
///<summary>
///得到某个字段值的列表
///</summary>
csharp_StringList^ GetTrimFieldValueList(String^ sFieldName, String^ sTableName,
String^ sCondition);
/// <summary>
/// 获取表的两个字段值
/// </summary>
/// <param name="sFieldName1"></param>
/// <param name="sFieldName2"></param>
/// <param name="sTableName"></param>
/// <param name="sCondition"></param>
/// 创建时间：2020-05-07 最后一次修改时间：2020-05-07
/// <returns></returns>
csharp_StringPairList^ GetTrimFieldValuePairList(String^ sFieldName1, String^ sFieldName2,
String^ sTableName, String^ sCondition);
csharp_StringPairList^ GetTrimFieldValuePairList(String^ sFieldName1, String^ sFieldName2,
String^ sTableName);
/// <summary>
/// 查字段值为oFieldValue的第一条记录，如果找到，则返回第一条记录的ID，否则返回－1；
/// 注意，字段类型必须为字符串
/// </summary>
/// <param name="sTableName">表名</param>
/// <param name="sFieldName">字段名</param>
/// <param name="sFileValue">字段值</param>
/// <param name="s_condition">条件,可以为空</param>
/// 创建时间： 约 2008-01-01 最后一次修改时间：2020-03-25
/// <returns>如果找到，则返回第一条记录的ID，否则返回－1，字符串字段</returns>
int find_s(String^ sTableName, String^ sFieldName, String^ sFileValue, String^ s_condition);
int find_s(String^ sTableName, String^ sFieldName, String^ sFileValue);
/// <summary>
/// 检查数据库是否存在表名sTableName
/// </summary>
/// <param name="sTableName">表名</param>
/// <returns></returns>
bool IsExistTableName(String^ sTableName);
#ifdef _WINDOWS_
/// <summary>
/// 把执行的结果集在DataGridView中显示
/// </summary>
/// <param name="sSQL">SQL语句</param>
/// <param name="dg_view">DataGridView</param>
/// <returns>返回执行结果集</returns>
DataTable^ ExecSQLView(String^ sSQL, System::Windows::Forms::DataGridView^ dg_view);
#endif
#if _WINDOWS_WEB_
/// <summary>
/// 把执行的结果集在DataGridView中显示
/// </summary>
/// <param name="sSQL">SQL语句</param>
/// <param name="dg_view">DataGridView</param>
/// <returns>返回执行结果集</returns>
public DataTable ^ ExecSQLView(String^ sSQL, System.Web.UI.WebControls.GridView dg_view)
{
DataTable ^ dt = gcnew DataTable();
if (sSQL->Trim()->Length == 0) return dt;
getViewDbDataAdapter().SelectCommand.CommandText = sSQL;
getViewDbDataAdapter().Fill(dt);
if (dg_view != null)
{
dg_view.DataSource = dt;
//gvView.DataBind();
//for(int i=0; i<gvView.Columns.Count; ++i)
//{
// gvView.Columns[i].HeaderStyle.Width = 80;
//}
}
return dt;
}
#endif
#ifdef _WINDOWS_
///<summary>
///得到某个字段值的列表
///</summary>
void GetTrimFieldValueList(String^ sFieldName, String^ sTableName,
String^ sCondition, ComboBox^ cb);
/// <summary>
/// 创建时间： 2020-05-23 最后一次修改时间：2020-05-31
/// 获取两个字段的值，在ComboBox中显示第二个字段的字符串值，如果有第三个字段，则在括号显示第三个字段。
/// 显示格式：LPairt( FieldName1, FieldName2(FieldName3 + FieldName4 + .....) )
/// </summary>
/// <param name="sIntFieldName">字段1,字段1必须是int整</param>
/// <param name="sSplitFieldName">可以多个字段，用分隔符","表示</param>
/// <param name="sTableName">表名</param>
/// <param name="sCondition">条件</param>
/// <param name="cb">ComboBox</param>
void GetPairFieldValueList(String^ sIntFieldName, String^ sSplitFieldName,
String^ sTableName, String^ sCondition, ComboBox^ cb);
///<summary>
///得到某个字段值的列表
///</summary>
void GetTrimFieldValueList(String^ sFieldName, String^ sTableName,
String^ sSQL, ListBox^ lb);
void GetIDAndNameCNList(String^ sTableName, String^ sCondition, ListView^ lv);
/// <summary>
/// 获取所有表
/// </summary>
/// <param name="il"></param>
void getTableNames(System::Collections::IList^ il);
csharp_StringList^ getTableNames();
/// <summary>
/// 获取某个表所有的字段名
/// </summary>
/// <param name="sTableName"></param>
void getFields(String^ sTableName, System::Collections::IList^ il);
csharp_StringList^ getFields(String^ sTableName);
///<summary>
///得到某个字段值的不重复的列表
///</summary>
void GetDistinctTrimFieldValueList(String^ sFieldName, String^ sTableName,
String^ sSQL, ComboBox^ cb);
#endif
static String^ getColumnType(String^ sType);
/// <summary>
/// 创建时间：2016-11-25 最后一次修改时间：2016-11-25
/// 第一，以类名作为表名；第二，凡是以fd_开头定义的成员均作为字段名，第三、自动创建fd_id为AUTOINCREMENT
/// </summary>
/// <typeparam name="T">所有创建表的类，其中以类名作为表名</typeparam>
/// <param name="df">数据库格式</param>
/// <returns>返回创建表的的SQL语句</returns>
template<class T>
static String^ getCreateTableSql(csharp_DataFormat df)
{
StringBuilder^ sb = gcnew StringBuilder();
if (df == csharp_DataFormat::dfSQLite)
{
//将类名作为表名
String^ sTableName = typeof(T).Name; // Utils.getTableName(clazz);
sb.Append("create table ").Append(sTableName).Append(" (fd_id INTEGER PRIMARY KEY AUTOINCREMENT,\n ");
foreach(FieldInfo fi in typeof(T).GetFields(BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance))
{
String^ sFieldName = fi.Name;
String^ sFileType = fi.FieldType.Name;
if (sFieldName.IndexOf("fd_id") == -1 && sFieldName.IndexOf("fd_") != -1)
{
String^ sValue = "";
if (sFileType == "String") { sValue = " text "; }
else if (sFileType == "Int32") { sValue = " integer "; }
else if (sFileType == "Int32") { sValue = " integer "; }
else if (sFileType == "Int64") { sValue = " long "; }
/*
* SQLite 没有一个单独的存储类用于存储日期和/或时间，但SQLite是能够存储日期和时间为TEXT，REAL或INTEGER值。
日期格式
TEXT A date in a format like "YYYY-MM-DD HH:MM:SS.SSS".
REAL The number of days since noon in Greenwich on November 24, 4714 B.C.
INTEGER The number of seconds since 1970-01-01 00:00:00 UTC.
*/
else if (sFileType == "Single") { sValue = " float "; }
else if (sFileType == "DateTime") { sValue = " text "; }
else if (sFileType == "Boolean") { sValue = " boolean "; }
else if (sFileType == "Char") { sValue = " varchar "; }
else if (sFileType == "Double") { sValue = " double "; }
else if (sFileType == "MemoryStream") { sValue = " unknown "; }
if (sValue != "")
sb.Append(sFieldName).Append(sValue).Append(",\n ");
}
}//---------------foreach
sb.Replace(",\n", ")", sb.Length - 3, 3); //括号收尾
}
else if (df == csharp_DataFormat::dfSQLServer)
{
}
else if (df == csharp_DataFormat::dfAccdb)
{
}
return sb.ToString();
}//-------------------------------getCreateTableSql
};//--------------------------------------------------------------------csharp_db^
_LF_END_
/
csharp_db.cpp
#include "csharp_db.h"
#include "csharp_SqlQuery.h"
#include "csharp_DB_Global.h"
#include "csharp_SqlDb.h"
#include "csharp_MIS_Global.h"
#include "csharp_crm_login_member.h"
#include "csharp_dict_table.h"
_LF_BEGIN_
///
bool csharp_db::exec_dict_sql_content(String^ sCaptionName, String^ sCheckTableName)
{
return false;
}
bool csharp_db::ExecSQLText(String^ sText)
{
return false;
}
bool csharp_db::ExecSQLFile(String^ sFileName)
{
return false;
}
DbConnection^ csharp_db::GetConnection()
{
return null;
}
DbDataAdapter^ csharp_db::GetViewDbDataAdapter()
{
return null;
}
int csharp_db::ExecNonSQL(String^ sSQL)
{
return -1;
}
int csharp_db::getRecordCount(String^ sTableName)
{
DataTable^ dt = ExecSQLQuery("SELECT Count(*) fd_sum FROM " + sTableName);
return (int)dt->Rows[0]["fd_sum"];
}
Object^ csharp_db::getFieldValueForLastRecord(String^ sFieldName, String^ sTableName, String^ sCondition)
{
String^ ssql = gcnew String("");
if (sCondition == "")
{
ssql = "SELECT TOP 1 " + sFieldName + " FROM " + sTableName + " ORDER BY " + sFieldName + " DESC";
}
else
{
ssql = "SELECT TOP 1 " + sFieldName + " FROM " + sTableName + " WHERE " + sCondition + " ORDER BY " + sFieldName + " DESC";
}
DataTable^ dt = ExecSQLQuery(ssql);
if (dt->Rows->Count > 0)
{
return dt->Rows[0][sFieldName];
}
else
{
return null;
}
}
DataRow^ csharp_db::getLastRecord(String^ sTableName)
{
String^ ssql = "SELECT TOP 1 * FROM " + sTableName + " ORDER BY fd_id DESC";
DataTable^ dt = ExecSQLQuery(ssql);
if (dt->Rows->Count > 0) return dt->Rows[0];
return null;
}
DataRow^ csharp_db::getFirstRecord(String^ sTableName)
{
String^ ssql = "SELECT TOP 1 * FROM " + sTableName + " ORDER BY fd_id";
DataTable^ dt = ExecSQLQuery(ssql);
if (dt->Rows->Count > 0) return dt->Rows[0];
return null;
}
String^ csharp_db::CreateDatabase(String^ sDatabaseName, String^ sPath)
{
return "";
}
bool csharp_db::createAppRepository(String^ sPath)
{
/*
if (sPath->Trim()->Length == 0)
return false;
#if _WINDOWS_PLATFORM_
if (File->Exists(sPath + "AppRepository" + "->accdb"))
{
return true;
}
//数据库密码 = gce::DES_Encrypt("lh",csharp_DB_Global::ind_des_key);
AccessDB_ db = gcnew AccessDB_(sPath + "AppRepository" + "->accdb", gce::DES_Encrypt("lh", csharp_DB_Global::ind_des_key));
db->create_app_co_user();
db->create_app_ind_user();
db->create_app_module();
#endif
*/
return true;
}
bool csharp_db::IsExistDatabase(String^ sDatabaseName)
{
csharp_SqlDb^ dbMaster = gcnew csharp_SqlDb("master", "sa",
gca::s_TextDecrypt2(csharp_DB_Global::m_db_pwd, csharp_DB_Global::m_text_key), csharp_DB_Global::m_IP);
String^ ssql = "SELECT * FROM master->->sysdatabases where name = \'" + sDatabaseName->Trim() + "\'";
return dbMaster->ExecSQLQuery(ssql)->Rows->Count != 0;
}
bool csharp_db::create_crm_natural_person(String^ sDBName)
{
if (sDBName->Trim()->Length == 0)
return false;
String^ ssql = "SELECT [fd_content] FROM [dict_sql] WHERE [fd_caption] = \'crm_natural_person->sql\'";
//[MyFamily]
String^ sCreate = "";
DataTable^ dt = csharp_DB_Global::db_repository->ExecSQLQuery(ssql);
if (dt->Rows->Count > 0)
sCreate = dt->Rows[0][0]->ToString();
else
return false;
sCreate = sCreate->Replace("MyFamily", sDBName);
return csharp_DB_Global::db_repository->ExecNonSQL(sCreate) != 0;
}
bool csharp_db::create_table(String^ sTableName, String^ sDatabaseName)
{
if (sDatabaseName->Trim() == "")
return false;
String^ ssql = "SELECT [fd_content] FROM [dict_sql] WHERE [fd_caption] = \'" + sTableName + "->sql\'";
String^ sCreate = "";
DataTable^ dt = csharp_DB_Global::db_repository->ExecSQLQuery(ssql);
if (dt->Rows->Count > 0)
sCreate = dt->Rows[0][0]->ToString();
else
return false;
sCreate = sCreate->Replace("[MyFamily]", "[" + sDatabaseName + "]");
return csharp_DB_Global::db_repository->ExecNonSQL(sCreate) != 0;
}
void csharp_db::TableCopy(String^ sDestTableName, csharp_db^ dbDest, String^ sSourceTableName, csharp_db^ dbSource)
{
/*
#if _WINDOWS_PLATFORM_
//清空原有表的数据
dbDest->ExecNonSQL("DELETE FROM [" + sDestTableName + "]");
//复制表数据
DataTable ^ dtSource = dbSource->ExecSQLQuery("SELECT * FROM [" + sSourceTableName + "]");
SqlBulkCopy sbc = gcnew SqlBulkCopy(dbDest->GetConnection()->ConnectionString);
try
{
sbc->DestinationTableName = sDestTableName;
sbc->WriteToServer(dtSource); //写入数据库
sbc->Close();
}
finally
{
sbc->Close();
}
#else
throw gcnew Exception(gce::OnCodeDidNotFinishError);
#endif
*/
}
DataTable^ csharp_db::ExecSQLQuery(String^ sSQL)
{
return csharp_SqlQuery::QueryDB(sSQL, this);
}
int csharp_db::GetMaxID(String^ sTableName, String^ sCondition)
{
String^ ssql = "";
if (sCondition->Length == 0)
ssql = "SELECT Max(fd_id) AS max_id FROM " + sTableName;
else
ssql = "SELECT Max(fd_id) AS max_id FROM " + sTableName + " WHERE " + sCondition;
DataTable^ dt = ExecSQLQuery(ssql);
//如果sTableName表中没有记录，Max(fd_id)返回null,dt->getRowsCount() = 1,不管
//怎样，dt->getRowsCount()都返回1
if (dt->Rows[0]["max_id"] == DBNull::Value)
{
return 0;
}
else
{
return (int)dt->Rows[0]["max_id"];
}
}
float csharp_db::fun_sum(String^ sFieldName, String^ sTable, String^ sCondition)
{
float f_sum = 0;
String^ ssql = "SELECT SUM(" + sFieldName + ") AS fd_sum FROM " + sTable;
if (sCondition->Trim()->Length != 0)
{
ssql += " WHERE " + sCondition;
}
DataTable^ dt = ExecSQLQuery(ssql);
f_sum = System::Convert::ToSingle(dt->Rows[0]["fd_sum"]);
return f_sum;
}
String^ csharp_db::GetValueFromID(String^ sIDValue, String^ sFieldName, String^ sTableName)
{
String^ ssql = "SELECT [" + sFieldName + "] FROM [" + sTableName + "] WHERE [fd_id] =" + sIDValue;
DataTable^ dt = ExecSQLQuery(ssql);
if (dt->Rows->Count > 0)
{
return dt->Rows[0][sFieldName]->ToString()->Trim();
}
else
{
return "";
}
}
bool csharp_db::UpdateValueFromID(String^ sIDValue, String^ sFieldName, String^ sFieldValue,
String^ sTableName)
{
String^ ssql = "UPDATE [" + sTableName + "] SET [" + sFieldName + "] = \'" + gce::CheckSQLString(sFieldValue) + "\'" +
" WHERE [fd_id] =" + sIDValue;
return ExecNonSQL(ssql) != 0;
}
bool csharp_db::SwapID(int iID1, int iID2, String^ sTableName)
{
int iTempID = GetMaxID(sTableName,"") + 1;
//ID1变成iTempID
if (ExecNonSQL("UPDATE [" + sTableName + "] SET [fd_id] = " + iTempID.ToString() + " WHERE [fd_id] = " +
iID1.ToString()) != 0)
{
//ID2变成ID1
if (ExecNonSQL("UPDATE [" + sTableName + "] SET [fd_id] = " + iID1.ToString() + " WHERE [fd_id] = " +
iID2.ToString()) != 0)
{
//iTempID 变成 ID2
if (ExecNonSQL("UPDATE [" + sTableName + "] SET [fd_id] = " + iID2.ToString() + " WHERE [fd_id] = " +
iTempID.ToString()) != 0)
{
return true;
}
}
}
return false;
}
bool csharp_db::SwapStringFieldValue(int iID1, int iID2, String^ sFieldName, String^ sTableName)
{
DataTable^ dt1 = ExecSQLQuery("SELECT [" + sFieldName + "] FROM [" + sTableName + "] WHERE fd_id = " + iID1.ToString());
Object^ value1, ^value2;
if (dt1->Rows->Count > 0)
{
value1 = dt1->Rows[0][sFieldName];
DataTable^ dt2 = ExecSQLQuery("SELECT [" + sFieldName + "] FROM [" + sTableName + "] WHERE fd_id = " + iID2.ToString());
if (dt2->Rows->Count > 0)
{
value2 = dt2->Rows[0][sFieldName];
String^ ssql1 = "UPDATE [" + sTableName + "] SET [" + sFieldName + "]= \'" + value1->ToString() + "\' WHERE fd_id=" +
iID2.ToString();
String^ ssql2 = "UPDATE [" + sTableName + "] SET [" + sFieldName + "]= \'" + value2->ToString() + "\' WHERE fd_id=" +
iID1.ToString();
if (ExecNonSQL(ssql1) != 0)
{
return ExecNonSQL(ssql2) != 0;
}
}
}
return false;
}
bool csharp_db::MIS_SetValueFromID(String^ sIDValue, String^ sFieldName, String^ sFieldValue,
String^ sTableName, String^ sModuleName)
{
if (csharp_MIS_Global::LoginUser->CanModuleWrite(sModuleName))
{
return UpdateValueFromID(sIDValue, sFieldName, sFieldValue, sTableName);
}
return false;
}
bool csharp_db::DeleteFromID(String^ sIDValue, String^ sTableName)
{
String^ ssql = "DELETE FROM [" + sTableName + "] WHERE fd_id=" + sIDValue;
return ExecNonSQL(ssql) != 0;
}
bool csharp_db::MIS_DeleteFromID(String^ sIDValue, String^ sTableName, String^ sModuleName)
{
if (csharp_MIS_Global::LoginUser->CanModuleDelete(sModuleName))
{
return DeleteFromID(sIDValue, sTableName);
}
return false;
}
String^ csharp_db::GetValueFromID(int iIDValue, String^ sFieldName, String^ sTableName)
{
return GetValueFromID(iIDValue.ToString(), sFieldName, sTableName);
}
csharp_Pair<String^, String^>^ csharp_db::GetPairValueFormID(String^ sIDValue, String^ sFieldName1, String^ sFieldName2, String^ sTableName)
{
csharp_Pair<String^, String^>^ lp = gcnew csharp_Pair<String^, String^>();
DataTable^ dt = ExecSQLQuery("SELECT " + sFieldName1 + "," + sFieldName2 + " FROM " + sTableName +
" WHERE fd_id =" + sIDValue);
if (dt->Rows->Count > 0)
{
lp->First = dt->Rows[0][sFieldName1]->ToString()->Trim();
lp->Second = dt->Rows[0][sFieldName2]->ToString()->Trim();
}
return lp;
}
csharp_Pair<String^, String^>^ csharp_db::GetPairValueFormID(int iIDValue, String^ sFieldName1, String^ sFieldName2, String^ sTableName)
{
return GetPairValueFormID(iIDValue.ToString(), sFieldName1, sFieldName2, sTableName);
}
int csharp_db::GetIDFromValue(String^ sFieldName, String^ sValue, String^ sTableName)
{
DataTable^ dt = ExecSQLQuery("SELECT fd_id FROM " + sTableName +
" WHERE " + sFieldName + " = \'" + gce::CheckSQLString(sValue->Trim()) + "\'");
if (dt->Rows->Count > 0)
{
return (int)dt->Rows[0]["fd_id"];
}
else
{
return -1;
}
}
int csharp_db::GetIDFromValue(String^ sFieldName, String^ sValue, String^ sTableName, String^ sCondition)
{
DataTable^ dt = ExecSQLQuery("SELECT fd_id FROM " + sTableName +
" WHERE " + sFieldName + " = \'" + gce::CheckSQLString(sValue->Trim()) + "\' AND " + sCondition);
if (dt->Rows->Count > 0)
{
return (int)dt->Rows[0]["fd_id"];
}
else
{
return -1;
}
}
csharp_Pair<int, int>^ csharp_db::addName(String^ sFieldName, String^ sValue, String^ sTableName)
{
if (sValue->Trim()->Length == 0)
{
throw gcnew System::Exception("字段值不能为空值！");
}
csharp_Pair<int, int>^ p = gcnew csharp_Pair<int, int>();
int iid = GetMaxID(sTableName, "") + 1;
if (sValue->Trim()->Length != 0)
{
p->Second = GetIDFromValue(sFieldName, sValue, sTableName);
if (p->Second == -1)
{
String^ ssql = "INSERT INTO " + sTableName + "(fd_id," + sFieldName + ") VALUES(" + iid.ToString() + ",\'" + gce::CheckSQLString(sValue) + "\')";
if (ExecNonSQL(ssql) != 0)
{
p->First = 1; p->Second = iid;
}
else { throw gcnew Exception("无法创建字典值“" + sValue + "”"); }
}
else
{
p->First = 0;
}
}
else
{
throw gcnew Exception("字段值不能为空！");
}
return p;
}
int csharp_db::addName2(String^ sFieldName, String^ sValue, ComboBox^ lic, String^ sTableName)
{
auto il = addName(sFieldName, sValue, sTableName);
if (il->First != 0)
{
csharp_Pair<int, String^>^ p = gcnew csharp_Pair<int, String^>(il->Second, sValue->Trim());
lic->Items->Add(p); //把字典值添加到选择框
lic->SelectedItem = p;
}
return il->Second;
}
bool csharp_db::ModifyNameCN(String^ sOldNameCN, String^ sNewNameCN, ComboBox^ cb,
String^ sTableName)
{
if (sOldNameCN->Trim() == sNewNameCN->Trim())
return false;
int iFindID = find_s(sTableName, "fd_name_cn", sNewNameCN);
if (iFindID != -1)
return false;
csharp_dict_table^ dtTable = gcnew csharp_dict_table(sTableName);
if (!dtTable->readDataFromName_CN(sOldNameCN))
{
return false;
}
dtTable->fd_name_cn = sNewNameCN;
if (csharp_MIS_Global::LoginUser != null)
{
dtTable->fd_ntext_ext1 = "此记录由用户(" + csharp_MIS_Global::LoginUser->fd_user_name
+ ")在" + System::DateTime::Now.ToString() + "修改过！";
}
int iFind = cb->FindString(sOldNameCN);
if (iFind != -1)
{
cb->Items[iFind] = gcnew csharp_Pair<int, String^>(dtTable->ID, dtTable->fd_name_cn);
cb->SelectedIndex = iFind;
}
return dtTable->UpdateSQL() != 0;
}
csharp_StringList^ csharp_db::GetDistinctTrimFieldValueList(String^ sFieldName, String^ sTableName, String^ sCondition)
{
String^ tmp = "SELECT Distinct " + sFieldName + " FROM " + sTableName;
if (sCondition->Trim()->Length != 0)
{
tmp += " WHERE ";
tmp += sCondition;
}
DataTable^ dt = this->ExecSQLQuery(tmp);
csharp_StringList^ csResult = gcnew csharp_StringList();
if (dt->Rows->Count > 0)
{
for (int i = 0; i < dt->Rows->Count; ++i)
{
csResult->Add(dt->Rows[i][sFieldName]->ToString()->Trim());
}
}
return csResult;
}
String^ csharp_db::getTableReadAllForCSharp(String^ sTableName)
{
String^ tmp = "";
if (dataFormat == csharp_DataFormat::dfAccdb || dataFormat == csharp_DataFormat::dfMDB)
{
}
else if (dataFormat == csharp_DataFormat::dfSQLServer)
{
DataTable^ dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt->Rows->Count > 0)
{
csharp_StringPairList^ ls = gcnew csharp_StringPairList();
String^ sid = dt->Rows[0]["id"]->ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt->Rows->Count; ++i)
{
ls->Add(dt->Rows[i]["name"]->ToString(), dt->Rows[i]["xtype"]->ToString());
}
for (int j = 0; j < ls->Count; j++)
{
csharp_Pair<String^, String^>^ sf = ls->GetIndex(j);
if (sf->Second == "56")//INTEGER
{
tmp = tmp + sf->First + "= (int)dt->Rows[0][\"" + sf->First + "\"];" + "\n";
}
else if (sf->Second == "122" || sf->Second == "62" || sf->Second == "60") //SMALLMONEY,FLOAT,MONEY
{
tmp = tmp + sf->First + "=Convert::ToSingle(dt->Rows[0][\"" + sf->First + "\"]);" + "\n";
}
else if (sf->Second == "58" || sf->Second == "61") //SMALLDATETIME,DATETIME
{
tmp = tmp + sf->First + "=Convert::ToDateTime(dt->Rows[0][\"" + sf->First + "\"]);" + "\n";
}
else if (sf->Second == "239" || sf->Second == "99") //NCHAR,NTEXT
{
tmp = tmp + sf->First + "= dt->Rows[0][\"" + sf->First + "\"]->ToString();" + "\n";
}
else if (sf->Second == "34") //Images
{
//tmp += "protected Image ";
}
}
}
}
return tmp;
}
String^ csharp_db::getTableReadAllForJava(String^ sTableName)
{
String^ tmp = "";
if (dataFormat == csharp_DataFormat::dfAccdb || dataFormat == csharp_DataFormat::dfMDB)
{
}
else if (dataFormat == csharp_DataFormat::dfSQLServer)
{
DataTable^ dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt->Rows->Count > 0)
{
csharp_StringPairList^ ls = gcnew csharp_StringPairList();
String^ sid = dt->Rows[0]["id"]->ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt->Rows->Count; ++i)
{
ls->Add(dt->Rows[i]["name"]->ToString(), dt->Rows[i]["xtype"]->ToString());
}
for (int j = 0; j < ls->Count; j++)
{
csharp_Pair<String^, String^>^ sf = ls->GetIndex(j);
if (sf->Second == "56")//INTEGER
{
tmp = tmp + sf->First + "= dt->getInt(0,\"" + sf->First + "\");" + "\n";
}
else if (sf->Second == "122" || sf->Second == "62" || sf->Second == "60") //SMALLMONEY,FLOAT,MONEY
{
tmp = tmp + sf->First + "= dt->getFloat(0,\"" + sf->First + "\");" + "\n";
}
else if (sf->Second == "58" || sf->Second == "61") //SMALLDATETIME,DATETIME
{
tmp = tmp + sf->First + "= dt->getDate(0,\"" + sf->First + "\");" + "\n";
}
else if (sf->Second == "239" || sf->Second == "99") //NCHAR,NTEXT
{
tmp = tmp + sf->First + "= dt->getString(0,\"" + sf->First + "\");" + "\n";
}
else if (sf->Second == "34") //Images
{
//tmp += "protected Image ";
}
}
}
}
return tmp;
}
String^ csharp_db::getTableInsertSQLForCSharp(String^ sTableName)
{
String^ tmp = "fd_id = GetNewID();" + "\n";
tmp += "ssql = \"INSERT INTO \" + m_sTableName + " + "\n";
tmp += "\"(";
if (dataFormat == csharp_DataFormat::dfAccdb || dataFormat == csharp_DataFormat::dfMDB)
{
}
else if (dataFormat == csharp_DataFormat::dfSQLServer)
{
DataTable^ dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt->Rows->Count > 0)
{
csharp_StringPairList^ ls = gcnew csharp_StringPairList();
String^ sid = dt->Rows[0]["id"]->ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt->Rows->Count; ++i)
{
ls->Add(dt->Rows[i]["name"]->ToString(), dt->Rows[i]["xtype"]->ToString());
}
for (int j = 0; j < ls->Count - 1; j++)
{
csharp_Pair<String^, String^>^ sf = ls->GetIndex(j);
tmp = tmp + sf->First + ",";
if ((j + 1) == (j + 1) / 5 * 5)
{
tmp += "\"+\n\"";
}
}
tmp += ls->GetIndex(ls->Count - 1)->First + ") VALUES(\" + " + "\n";
for (int j = 0; j < ls->Count; j++)
{
csharp_Pair<String^, String^>^ sf = ls->GetIndex(j);
if (sf->Second == "56" || sf->Second == "122" || sf->Second == "62" || sf->Second == "60") //INTEGER,SMALLMONEY,FLOAT,MONEY
{
if (j != ls->Count - 1)
{
if (sf->First == "fd_create_author_id" || sf->First == "fd_modify_author_id")
{
tmp = tmp + "\"\"+" + " GetLoginID()" + "+\",\" +" + "\n";
}
else
{
tmp = tmp + "\"\"+" + sf->First + "->ToString()" + "+\",\" +" + "\n";
}
}
else
{
tmp = tmp + "\"\"+" + sf->First + "->ToString()" + "+\")\";" + "\n";
}
}
else if (sf->Second == "58" || sf->Second == "61") //SMALLDATETIME,DATETIME
{
if (j != ls->Count - 1)
{
if (sf->First == "fd_create_time" || sf->First == "fd_modify_time")
{
tmp = tmp + "\"\\\'\"+" + "System->DateTime->Now->ToString() " + "+\"\\',\"+" + "\n";
}
else
{
tmp = tmp + "\"\\\'\"+" + sf->First + "->ToShortDateString()" + "+\"\\',\"+" + "\n";
}
}
else
{
tmp = tmp + "\"\\\'\"+" + sf->First + "->ToShortDateString()" + "+\"\\',)\";" + "\n";
}
}
else if (sf->Second == "239" || sf->Second == "99" || sf->Second == "231") //NCHAR,NTEXT,NVARCHAR
{
if (j != ls->Count - 1)
{
tmp = tmp + "\"\\\'\"+" + "gce::CheckSQLString(" + sf->First + ")" + "+\"\\',\"+" + "\n";
}
else
{
tmp = tmp + "\"\\\'\"+" + "gce::CheckSQLString(" + sf->First + ")" + "+\"\\')\";" + "\n";
}
}
else if (sf->Second == "34") //Images
{
if (j != ls->Count - 1)
{
tmp = tmp + "\"\"+" + "请除去IMAGE数据" + "+\",\" +" + "\n";
}
else
{
tmp = tmp + "\"\"+" + "请除去IMAGE数据" + "+\")\";" + "\n";
}
}
else
{
throw gcnew System::Exception(sf->Second->ToString());
}
}
}
}
return tmp;
}
String^ csharp_db::getTableInsertSQLForJava(String^ sTableName)
{
String^ tmp = "fd_id = GetNewID();" + "\n";
tmp += "ssql = \"INSERT INTO \" + m_sTableName + " + "\n";
tmp += "\"(";
if (dataFormat == csharp_DataFormat::dfAccdb || dataFormat == csharp_DataFormat::dfMDB)
{
}
else if (dataFormat == csharp_DataFormat::dfSQLServer)
{
DataTable^ dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt->Rows->Count > 0)
{
csharp_StringPairList^ ls = gcnew csharp_StringPairList();
String^ sid = dt->Rows[0]["id"]->ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt->Rows->Count; ++i)
{
ls->Add(dt->Rows[i]["name"]->ToString(), dt->Rows[i]["xtype"]->ToString());
}
for (int j = 0; j < ls->Count - 1; j++)
{
csharp_Pair<String^, String^>^ sf = ls->GetIndex(j);
tmp = tmp + sf->First + ",";
if ((j + 1) == (j + 1) / 5 * 5)
{
tmp += "\"+\n\"";
}
}
tmp += ls->GetIndex(ls->Count - 1)->First + ") VALUES(\" + " + "\n";
for (int j = 0; j < ls->Count; j++)
{
csharp_Pair<String^, String^>^ sf = ls->GetIndex(j);
if (sf->Second == "56" || sf->Second == "122" || sf->Second == "62" || sf->Second == "60") //INTEGER,SMALLMONEY,FLOAT,MONEY
{
if (j != ls->Count - 1)
{
if (sf->First == "fd_create_author_id" || sf->First == "fd_modify_author_id")
{
tmp = tmp + "\"\"+" + "csharp_MIS_Global->getLoginID()" + "+\",\" +" + "\n";
}
else
{
tmp = tmp + "\"\"+" + sf->First + "+\",\" +" + "\n";
}
}
else
{
tmp = tmp + "\"\"+" + sf->First + "+\")\";" + "\n";
}
}
else if (sf->Second == "58" || sf->Second == "61") //SMALLDATETIME,DATETIME
{
if (j != ls->Count - 1)
{
if (sf->First == "fd_create_time" || sf->First == "fd_modify_time")
{
tmp = tmp + "\"\\\'\"+" + "csharp_MIS_Global->strCurrentTime() " + "+\"\\',\"+" + "\n";
}
else
{
tmp = tmp + "\"\\\'\"+ gce::strDate(" + sf->First + ")" + "+\"\\',\"+" + "\n";
}
}
else
{
tmp = tmp + "\"\\\'\"+ gce::strDate(" + sf->First + ")" + "+\"\\',)\";" + "\n";
}
}
else if (sf->Second == "239" || sf->Second == "99" || sf->Second == "231") //NCHAR,NTEXT,NVARCHAR
{
if (j != ls->Count - 1)
{
tmp = tmp + "\"\\\'\"+" + "gce::CheckSQLString(" + sf->First + ")" + "+\"\\',\"+" + "\n";
}
else
{
tmp = tmp + "\"\\\'\"+" + "gce::CheckSQLString(" + sf->First + ")" + "+\"\\')\";" + "\n";
}
}
else if (sf->Second == "34") //Images
{
if (j != ls->Count - 1)
{
tmp = tmp + "\"\"+" + "请除去IMAGE数据" + "+\",\" +" + "\n";
}
else
{
tmp = tmp + "\"\"+" + "请除去IMAGE数据" + "+\")\";" + "\n";
}
}
else
{
throw gcnew System::Exception(sf->Second->ToString());
}
}
}
}
return tmp;
}
String^ csharp_db::getTableUpdateSQLForCSharp(String^ sTableName)
{
String^ tmp = "ssql =\"UPDATE \"+ m_sTableName + \" SET \";" + "\n";
if (dataFormat == csharp_DataFormat::dfAccdb || dataFormat == csharp_DataFormat::dfMDB)
{
}
else if (dataFormat == csharp_DataFormat::dfSQLServer)
{
DataTable^ dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt->Rows->Count > 0)
{
csharp_StringPairList^ ls = gcnew csharp_StringPairList();
String^ sid = dt->Rows[0]["id"]->ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt->Rows->Count; ++i)
{
ls->Add(dt->Rows[i]["name"]->ToString(), dt->Rows[i]["xtype"]->ToString());
}
for (int j = 0; j < ls->Count; j++)
{
csharp_Pair<String^, String^>^ sf = ls->GetIndex(j);
if (sf->Second == "56" || sf->Second == "122" || sf->Second == "62" || sf->Second == "60") //INTEGER,SMALLMONEY,FLOAT,MONEY
{
if (j != ls->Count - 1)
{
if (sf->First == "fd_create_author_id")
{
}
else if (sf->First == "fd_modify_author_id")
{
tmp = tmp + "ssql+= \"" + sf->First + "=\"+" + " GetLoginID()" + "+\",\";" + "\n";
}
else
{
tmp = tmp + "ssql+= \"" + sf->First + "=\"+" + sf->First + "->ToString()" + "+\",\";" + "\n";
}
}
else
{
tmp = tmp + "ssql+= \"" + sf->First + "=\"+" + sf->First + "->ToString()" + "+\"\";" + "\n";
}
}
else if (sf->Second == "58" || sf->Second == "61") //SMALLDATETIME,DATETIME
{
if (j != ls->Count - 1)
{
if (sf->First == "fd_create_time")
{
}
else if (sf->First == "fd_modify_time")
{
tmp = tmp + "ssql+= \"" + sf->First + "=\\\'\"+" + "System->DateTime->Now->ToString() " + "+\"\\',\";" + "\n";
}
else
{
tmp = tmp + "ssql+= \"" + sf->First + "=\\\'\"+" + sf->First + "->ToShortDateString()" + "+\"\\',\";" + "\n";
}
}
else
{
tmp = tmp + "ssql+= \"" + sf->First + "=\\\'\"+" + sf->First + "->ToShortDateString()" + "+\"\\'\";" + "\n";
}
}
else if (sf->Second == "239" || sf->Second == "99" || sf->Second == "231") //NCHAR,NTEXT,NVARCHAR
{
if (j != ls->Count - 1)
{
tmp = tmp + "ssql+= \"" + sf->First + "=\\\'\"+ gce::CheckSQLString(" + sf->First + ")+\"\\',\";" + "\n";
}
else
{
tmp = tmp + "ssql+= \"" + sf->First + "=\\\'\"+ gce::CheckSQLString(" + sf->First + ")+\"\\'\";" + "\n";
}
}
else if (sf->Second == "34") //Images
{
if (j != ls->Count - 1)
{
tmp = tmp + "ssql+= \"" + sf->First + "=\"+" + "请除去IMAGE数据" + "+\",\";" + "\n";
}
else
{
tmp = tmp + "ssql+= \"" + sf->First + "=\"+" + "请除去IMAGE数据" + "+\"\";" + "\n";
}
}
}
}
}
tmp += "ssql += \" WHERE fd_id=\" + " + "fd_id.ToString(); ";
return tmp;
}
String^ csharp_db::getTableUpdateSQLForJava(String^ sTableName)
{
String^ tmp = "ssql =\"UPDATE \"+ m_sTableName + \" SET \";" + "\n";
if (dataFormat == csharp_DataFormat::dfAccdb || dataFormat == csharp_DataFormat::dfMDB)
{
}
else if (dataFormat == csharp_DataFormat::dfSQLServer)
{
DataTable^ dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt->Rows->Count > 0)
{
csharp_StringPairList^ ls = gcnew csharp_StringPairList();
String^ sid = dt->Rows[0]["id"]->ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt->Rows->Count; ++i)
{
ls->Add(dt->Rows[i]["name"]->ToString(), dt->Rows[i]["xtype"]->ToString());
}
for (int j = 0; j < ls->Count; j++)
{
csharp_Pair<String^, String^>^ sf = ls->GetIndex(j);
if (sf->Second == "56" || sf->Second == "122" || sf->Second == "62" || sf->Second == "60") //INTEGER,SMALLMONEY,FLOAT,MONEY
{
if (j != ls->Count - 1)
{
if (sf->First == "fd_create_author_id")
{
}
else if (sf->First == "fd_modify_author_id")
{
tmp = tmp + "ssql+= \"" + sf->First + "=\"+" + "csharp_MIS_Global->getLoginID()" + "+\",\";" + "\n";
}
else
{
tmp = tmp + "ssql+= \"" + sf->First + "=\"+" + sf->First + "+\",\";" + "\n";
}
}
else
{
tmp = tmp + "ssql+= \"" + sf->First + "=\"+" + sf->First + "+\"\";" + "\n";
}
}
else if (sf->Second == "58" || sf->Second == "61") //SMALLDATETIME,DATETIME
{
if (j != ls->Count - 1)
{
if (sf->First == "fd_create_time")
{
}
else if (sf->First == "fd_modify_time")
{
tmp = tmp + "ssql+= \"" + sf->First + "=\\\'\"+" + " csharp_MIS_Global->strCurrentTime() " + "+\"\\',\";" + "\n";
}
else
{
tmp = tmp + "ssql+= \"" + sf->First + "=\\\'\"+" + "gce::strDate(" + sf->First + " ) " + " +\"\\',\";" + "\n";
}
}
else
{
tmp = tmp + "ssql+= \"" + sf->First + "=\\\'\"+" + "gce::strDate(" + sf->First + ")" + "+\"\\'\";" + "\n";
}
}
else if (sf->Second == "239" || sf->Second == "99" || sf->Second == "231") //NCHAR,NTEXT,NVARCHAR
{
if (j != ls->Count - 1)
{
tmp = tmp + "ssql+= \"" + sf->First + "=\\\'\"+ gce::CheckSQLString(" + sf->First + ")+\"\\',\";" + "\n";
}
else
{
tmp = tmp + "ssql+= \"" + sf->First + "=\\\'\"+ gce::CheckSQLString(" + sf->First + ")+\"\\'\";" + "\n";
}
}
else if (sf->Second == "34") //Images
{
if (j != ls->Count - 1)
{
tmp = tmp + "ssql+= \"" + sf->First + "=\"+" + "请除去IMAGE数据" + "+\",\";" + "\n";
}
else
{
tmp = tmp + "ssql+= \"" + sf->First + "=\"+" + "请除去IMAGE数据" + "+\"\";" + "\n";
}
}
}
}
}
tmp += "ssql += \" WHERE fd_id=\" + " + "fd_id; ";
return tmp;
}
String^ csharp_db::getCreateAccessTableForCSharp(String^ sTableName)
{
String^ tmp = "";
if (dataFormat == csharp_DataFormat::dfAccdb || dataFormat == csharp_DataFormat::dfMDB)
{
}
else if (dataFormat == csharp_DataFormat::dfSQLServer)
{
tmp += "ADOX->Catalog catalog = gcnew Catalog();" + "\n";
tmp += "ADODB->Connection cn = gcnew ADODB->Connection();" + "\n";
tmp += "cn->Open(getConnectString(), null, null, -1);" + "\n";
tmp += "catalog->ActiveConnection = cn;" + "\n";
tmp += "//---------------------------------------------------------------------创建表" + "\n";
tmp += "ADOX->Table table = gcnew ADOX->Table();" + "\n";
tmp += "table->Name = \"" + sTableName + "\";" + "\n";
tmp += "for (int i = 0; i < catalog->Tables->Count; ++i)" + "\n";
tmp += "{";
tmp += " \tif (catalog->Tables[i]->Name == table->Name)" + "\n";
tmp += " \t{" + "\n";
tmp += "\t\treturn false;" + "\n";
tmp += " \t}" + "\n";
tmp += "}" + "\n";
tmp += "//--------------------------------------------------------------------创建字段" + "\n";
DataTable^ dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt->Rows->Count > 0)
{
String^ sid = dt->Rows[0]["id"]->ToString();
dt = ExecSQLQuery("select [name],[xtype],[prec] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 1; i < dt->Rows->Count; ++i)
{
String^ sFieldName = dt->Rows[i]["name"]->ToString();
int xtype = System::Convert::ToInt32(dt->Rows[i]["xtype"]);
tmp += "ADOX->Column " + sFieldName + " = gcnew ADOX->Column();" + "\n";
tmp += sFieldName + "->ParentCatalog = catalog;" + "\n";
tmp += sFieldName + "->Name = " + "\"" + sFieldName + "\";" + "\n";
if (xtype == 56) //INTEGER
{
tmp += sFieldName + "->Type = DataTypeEnum->adInteger;" + "\n";
tmp += sFieldName + "->Properties[\"Description\"]->Value = \"INTEGER\";" + "\n";
tmp += sFieldName + "->Properties[\"Default\"]->Value = 1;" + "\n";
tmp += "table->Columns->Append(" + sFieldName + ", DataTypeEnum->adInteger, 0);" + "\n\n";
}
else if (xtype == 122)//SMALLMONEY
{
tmp += sFieldName + "->Type = DataTypeEnum->adCurrency;" + "\n";
tmp += sFieldName + "->Properties[\"Description\"]->Value = \"SMALLMONEY\";" + "\n";
tmp += sFieldName + "->Properties[\"Default\"]->Value = 1;" + "\n";
tmp += "table->Columns->Append(" + sFieldName + ", DataTypeEnum->adCurrency, 0);" + "\n\n";
}
else if (xtype == 63)//FLOAT
{
}
else if (xtype == 58 || xtype == 61) //SMALLDATETIME,DATETIME
{
tmp += sFieldName + "->Type = DataTypeEnum->adDate;" + "\n";
tmp += sFieldName + "->Properties[\"Description\"]->Value = \"时间\";" + "\n";
tmp += sFieldName + "->Properties[\"Default\"]->Value = \"Now()\";" + "\n";
tmp += "table->Columns->Append(" + sFieldName + ", DataTypeEnum->adDate, 0);" + "\n\n";
}
else if (xtype == 239) //NCHAR
{
tmp += sFieldName + "->Type = DataTypeEnum->adVarWChar;" + "\n";
tmp += sFieldName + "->DefinedSize = " + dt->Rows[i]["prec"]->ToString() + ";" + "\n";
tmp += sFieldName + "->Properties[\"Description\"]->Value = \"NCHAR\";" + "\n";
tmp += sFieldName + "->Properties[\"Default\"]->Value = \"\";" + "\n";
tmp += "table->Columns->Append(" + sFieldName + ", DataTypeEnum->adVarWChar, " + dt->Rows[i]["prec"]->ToString() + ");" + "\n\n";
}
else if (xtype == 99)//NTEXT
{
tmp += sFieldName + "->Type = DataTypeEnum->adLongVarWChar;" + "\n";
tmp += sFieldName + "->Properties[\"Description\"]->Value = \"NTEXT\";" + "\n";
tmp += sFieldName + "->Properties[\"Default\"]->Value = \"\";" + "\n";
tmp += "table->Columns->Append(" + sFieldName + ", DataTypeEnum->adLongVarWChar, 0);" + "\n\n";
}
else if (xtype == 34) //Images
{
tmp += sFieldName + "->Type = DataTypeEnum->adLongVarBinary;" + "\n";
tmp += sFieldName + "->Properties[\"Description\"]->Value = \"adLongVarBinary\";" + "\n";
tmp += "table->Columns->Append(" + sFieldName + ", DataTypeEnum->adLongVarBinary, 0);" + "\n\n";
}
}
}
}
return tmp;
}
String^ csharp_db::getTableFieldDeclareForCSharpProtected(String^ sTableName)
{
String^ tmp = "";
if (dataFormat == csharp_DataFormat::dfAccdb || dataFormat == csharp_DataFormat::dfMDB)
{
}
else if (dataFormat == csharp_DataFormat::dfSQLServer)
{
DataTable^ dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt->Rows->Count > 0)
{
csharp_StringPairList^ ls = gcnew csharp_StringPairList();
String^ sid = dt->Rows[0]["id"]->ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt->Rows->Count; ++i)
{
ls->Add(dt->Rows[i]["name"]->ToString(), dt->Rows[i]["xtype"]->ToString());
}
for (int j = 0; j < ls->Count; j++)
{
csharp_Pair<String^, String^>^ sf = ls->GetIndex(j);
if (sf->Second == "56") //INTEGER IDENTITY (1, 1) PRIMARY KEY,
{
tmp += "protected int ";
}
else if (sf->Second == "58") //SMALLDATETIME
{
tmp += "protected DateTime ";
}
else if (sf->Second == "239") //NCHAR
{
tmp += "protected String^ ";
}
else if (sf->Second == "122") //SMALLMONEY
{
tmp += "protected float ";
}
else if (sf->Second == "99") //NTEXT
{
tmp += "protected String^ ";
}
else if (sf->Second == "61") //DATETIME
{
tmp += "protected DateTime ";
}
else if (sf->Second == "34") //Images
{
tmp += "protected Image ";
}
else if (sf->Second == "62" || sf->Second == "60") //FLOAT,MONEY
{
tmp += "protected float ";
}
tmp = tmp + " " + sf->First + ";\n";
}
}
}
return tmp;
}
String^ csharp_db::getTableFieldDeclareForCSharpPublic1(String^ sTableName)
{
String^ tmp = "";
if (dataFormat == csharp_DataFormat::dfAccdb || dataFormat == csharp_DataFormat::dfMDB)
{
}
else if (dataFormat == csharp_DataFormat::dfSQLServer)
{
DataTable^ dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt->Rows->Count > 0)
{
csharp_StringPairList^ ls = gcnew csharp_StringPairList();
String^ sid = dt->Rows[0]["id"]->ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt->Rows->Count; ++i)
{
ls->Add(dt->Rows[i]["name"]->ToString(), dt->Rows[i]["xtype"]->ToString());
}
for (int j = 0; j < ls->Count; j++)
{
csharp_Pair<String^, String^>^ sf = ls->GetIndex(j);
if (sf->First == "fd_id")
{
tmp += "//";
}
else if (sf->First == "fd_create_time")
{
tmp += "//";
}
else if (sf->First == "fd_modify_time")
{
tmp += "//";
}
else if (sf->First == "fd_create_author_id")
{
tmp += "//";
}
else if (sf->First == "fd_modify_author_id")
{
tmp += "//";
}
else if (sf->First == "fd_nchar_ext1")
{
tmp += "//";
}
else if (sf->First == "fd_nchar_ext2")
{
tmp += "//";
}
else if (sf->First == "fd_ntext_ext1")
{
tmp += "//";
}
else if (sf->First == "fd_ntext_ext2")
{
tmp += "//";
}
else if (sf->First == "fd_integer_ext1")
{
tmp += "//";
}
else if (sf->First == "fd_integer_ext2")
{
tmp += "//";
}
else if (sf->First == "fd_float_ext1")
{
tmp += "//";
}
else if (sf->First == "fd_float_ext2")
{
tmp += "//";
}
else if (sf->First == "fd_desc")
{
tmp += "//";
}
if (sf->Second == "56") //INTEGER IDENTITY (1, 1) PRIMARY KEY,
{
tmp += "public int " + sf->First->Substring(3, sf->First->Length - 3);
}
else if (sf->Second == "58") //SMALLDATETIME
{
tmp += "public DateTime " + sf->First->Substring(3, sf->First->Length - 3);
}
else if (sf->Second == "239") //NCHAR
{
tmp += "public String^ " + sf->First->Substring(3, sf->First->Length - 3);
}
else if (sf->Second == "122") //SMALLMONEY
{
tmp += "public float " + sf->First->Substring(3, sf->First->Length - 3);
}
else if (sf->Second == "99") //NTEXT
{
tmp += "public String^ " + sf->First->Substring(3, sf->First->Length - 3);
}
else if (sf->Second == "61") //DATETIME
{
tmp += "public DateTime " + sf->First->Substring(3, sf->First->Length - 3);
}
else if (sf->Second == "34") //Images
{
tmp += "public Image " + sf->First->Substring(3, sf->First->Length - 3);
}
else if (sf->Second == "62" || sf->Second == "60") //FLOAT,MONEY
{
tmp += "public float " + sf->First->Substring(3, sf->First->Length - 3);
}
tmp += "{ get{return " + sf->First + ";} set{" + sf->First + "=value;} }";
tmp += "\n";
}
}
}
return tmp;
}
String^ csharp_db::getTableFieldDeclareForCSharpPublic2(String^ sTableName)
{
String^ tmp = "";
if (dataFormat == csharp_DataFormat::dfAccdb || dataFormat == csharp_DataFormat::dfMDB)
{
}
else if (dataFormat == csharp_DataFormat::dfSQLServer)
{
DataTable^ dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt->Rows->Count > 0)
{
csharp_StringPairList^ ls = gcnew csharp_StringPairList();
String^ sid = dt->Rows[0]["id"]->ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt->Rows->Count; ++i)
{
ls->Add(dt->Rows[i]["name"]->ToString(), dt->Rows[i]["xtype"]->ToString());
}
for (int j = 0; j < ls->Count; j++)
{
csharp_Pair<String^, String^>^ sf = ls->GetIndex(j);
if (sf->Second == "56") //INTEGER IDENTITY (1, 1) PRIMARY KEY,
{
tmp += "public int " + sf->First->Substring(3, sf->First->Length - 3) + "\n";
}
else if (sf->Second == "58") //SMALLDATETIME
{
tmp += "public DateTime " + sf->First->Substring(3, sf->First->Length - 3) + "\n";
}
else if (sf->Second == "239") //NCHAR
{
tmp += "public String^ " + sf->First->Substring(3, sf->First->Length - 3) + "\n";
}
else if (sf->Second == "122") //SMALLMONEY
{
tmp += "public float " + sf->First->Substring(3, sf->First->Length - 3) + "\n";
}
else if (sf->Second == "99") //NTEXT
{
tmp += "public String^ " + sf->First->Substring(3, sf->First->Length - 3) + "\n";
}
else if (sf->Second == "61") //DATETIME
{
tmp += "public DateTime " + sf->First->Substring(3, sf->First->Length - 3) + "\n";
}
else if (sf->Second == "34") //Images
{
tmp += "public Image " + sf->First->Substring(3, sf->First->Length - 3) + "\n";
}
else if (sf->Second == "62" || sf->Second == "60") //FLOAT,MONEY
{
tmp += "public float " + sf->First->Substring(3, sf->First->Length - 3) + "\n";
}
tmp += "{\n";
tmp += "\tget\n";
tmp += "\t{\n";
tmp += "\t\treturn " + sf->First + ";" + "\n";
tmp += "\t}\n";
tmp += "\tset\n";
tmp += "\t{\n";
tmp += "\t\t" + sf->First + "=value;" + "\n";
tmp += "\t}\n";
tmp += "}\n";
tmp += "\n\n";
}
}
}
return tmp;
}
String^ csharp_db::getTableFieldDeclareForCSharpPublic3(String^ sTableName)
{
String^ tmp = "";
if (dataFormat == csharp_DataFormat::dfAccdb || dataFormat == csharp_DataFormat::dfMDB)
{
}
else if (dataFormat == csharp_DataFormat::dfSQLServer)
{
DataTable^ dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt->Rows->Count > 0)
{
csharp_StringPairList^ ls = gcnew csharp_StringPairList();
String^ sid = dt->Rows[0]["id"]->ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt->Rows->Count; ++i)
{
ls->Add(dt->Rows[i]["name"]->ToString(), dt->Rows[i]["xtype"]->ToString());
}
for (int j = 0; j < ls->Count; j++)
{
csharp_Pair<String^, String^>^ sf = ls->GetIndex(j);
if (sf->Second == "56") //INTEGER IDENTITY (1, 1) PRIMARY KEY,
{
tmp += "public int ";
}
else if (sf->Second == "58") //SMALLDATETIME
{
tmp += "public DateTime ";
}
else if (sf->Second == "239") //NCHAR
{
tmp += "public String^ ";
}
else if (sf->Second == "122") //SMALLMONEY
{
tmp += "public float ";
}
else if (sf->Second == "99") //NTEXT
{
tmp += "public String^ ";
}
else if (sf->Second == "61") //DATETIME
{
tmp += "public DateTime ";
}
else if (sf->Second == "34") //Images
{
tmp += "public Image ";
}
else if (sf->Second == "62" || sf->Second == "60") //FLOAT,MONEY
{
tmp += "public float ";
}
tmp = tmp + " " + sf->First + ";\n";
}
}
}
return tmp;
}
String^ csharp_db::GetAllFieldDesc(String^ sTableName)
{
String^ tmp = "";
if (dataFormat == csharp_DataFormat::dfAccdb || dataFormat == csharp_DataFormat::dfMDB)
{
throw gcnew Exception("函数未完成！");
}
else if (dataFormat == csharp_DataFormat::dfSQLServer)
{
DataTable^ dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt->Rows->Count > 0)
{
csharp_StringPairList^ ls = gcnew csharp_StringPairList();
String^ sid = dt->Rows[0]["id"]->ToString();
dt = ExecSQLQuery("select [name],[xtype] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt->Rows->Count; ++i)
{
ls->Add(dt->Rows[i]["name"]->ToString(), dt->Rows[i]["xtype"]->ToString());
}
for (int j = 0; j < ls->Count; j++)
{
csharp_Pair<String^, String^>^ sf = ls->GetIndex(j);
tmp += sf->First;
tmp += " ";
tmp += "SQLServer类型:" + csharp_Field::GetSQLServerXTypeString(Convert::ToInt32(sf->Second)) + "(xtype值：" + sf->Second + ")";
tmp += "\t";
tmp += "C#类型:" + csharp_Field::SQLServerXTYPConverToCSharpTypeName(Convert::ToInt32(sf->Second));
tmp += "\n";
}
}
}
else
{
throw gcnew Exception("函数未完成！");
}
return tmp;
}
String^ csharp_db::GetFieldDesc(String^ sFieldName, String^ sTableName)
{
if (dataFormat == csharp_DataFormat::dfSQLServer)
{
String^ ssql = "select [syscolumns]->[name],[syscolumns]->[xtype] FROM [sysobjects],[syscolumns] where [syscolumns]->[id] = [sysobjects]->[id] AND [sysobjects]->[name] = \'";
ssql += sTableName;
ssql += "\'";
ssql += " AND [syscolumns]->[name] = \'";
ssql += sFieldName;
ssql += "\'";
DataTable^ dt = ExecSQLQuery(ssql);
String^ tmp = "";
for each(DataRow^ dr in dt->Rows)
{
tmp += dr["name"]->ToString();
tmp += "\t";
tmp += "SQLServer类型:" + csharp_Field::GetSQLServerXTypeString(Convert::ToInt32(dr["xtype"])) + "(xtype值:" + dr["xtype"]->ToString() + ")";
tmp += "\t";
tmp += "C#类型:" + csharp_Field::SQLServerXTYPConverToCSharpTypeName(Convert::ToInt32(dr["xtype"]));
tmp += "\n";
}
return tmp;
}
else
{
throw gcnew Exception("函数未完成！");
}
}
csharp_DList<csharp_Field^>^ csharp_db::GetAllFieldInfo(String^ sTableName)
{
csharp_DList<csharp_Field^>^ lResult = gcnew csharp_DList<csharp_Field^>();
if (dataFormat == csharp_DataFormat::dfSQLServer)
{
String^ ssql = "select [syscolumns]->[name],[syscolumns]->[xtype] FROM [sysobjects],[syscolumns] where [syscolumns]->[id] = [sysobjects]->[id] AND [sysobjects]->[name] = \'";
ssql += sTableName;
ssql += "\'";
DataTable^ dt = ExecSQLQuery(ssql);
for each(DataRow^ dr in dt->Rows)
{
csharp_Field^ tmpNew = gcnew csharp_Field();
tmpNew->Name = dr["name"]->ToString();
tmpNew->SetSQLServerXType(System::Convert::ToInt32(dr["xtype"]));
lResult->Add(tmpNew);
}
}
else
{
throw gcnew Exception("函数未完成！");
}
return lResult;
}
csharp_Field^ csharp_db::GetFieldInfo(String^ sFieldName, String^ sTableName)
{
csharp_Field^ fResult = gcnew csharp_Field();
if (dataFormat == csharp_DataFormat::dfSQLServer)
{
String^ ssql = "select [syscolumns].[name],[syscolumns].[xtype] FROM [sysobjects],[syscolumns] where [syscolumns].[id] = [sysobjects].[id] AND [sysobjects].[name] = \'";
ssql += sTableName;
ssql += "\'";
ssql += " AND [syscolumns].[name] = \'";
ssql += sFieldName;
ssql += "\'";
DataTable^ dt = ExecSQLQuery(ssql);
for each(DataRow^ dr in dt->Rows)
{
csharp_Field^ tmpNew = gcnew csharp_Field();
tmpNew->Name = dr["name"]->ToString();
tmpNew->SetSQLServerXType(System::Convert::ToInt32(dr["xtype"]));
return tmpNew;
}
}
else
{
throw gcnew Exception("函数未完成！");
}
return fResult;
}
csharp_StringList^ csharp_db::GetTrimFieldValueList(String^ sFieldName, String^ sTableName, String^ sCondition)
{
csharp_StringList^ sResult = gcnew csharp_StringList();
String^ tmp;
if (sCondition->Trim()->Length == 0)
{
tmp = "SELECT Distinct " + sFieldName + " FROM " + sTableName;
}
else
{
tmp = "SELECT Distinct " + sFieldName + " FROM " + sTableName + " WHERE " + sCondition;
}
DataTable^ dt = this->ExecSQLQuery(tmp);
if (dt->Rows->Count > 0)
{
for (int i = 0; i < dt->Rows->Count; i++)
{
sResult->Add(dt->Rows[i][sFieldName]->ToString()->Trim());
}
}
return sResult;
}
csharp_StringPairList^ csharp_db::GetTrimFieldValuePairList(String^ sFieldName1, String^ sFieldName2, String^ sTableName, String^ sCondition)
{
csharp_StringPairList^ plResult = gcnew csharp_StringPairList();
String^ ssql = "SELECT [" + sFieldName1 + "],[" + sFieldName2 + "] FROM [" + sTableName + "]";
if (sCondition->Trim()->Length != 0)
{
ssql += " WHERE " + sCondition;
}
DataTable^ dt = ExecSQLQuery(ssql);
for each(DataRow^ dr in dt->Rows)
{
String^ s1 = dr[sFieldName1]->ToString()->Trim();
String^ s2 = dr[sFieldName2]->ToString()->Trim();
plResult->Add(s1, s2);
}
return plResult;
}
csharp_StringPairList^ csharp_db::GetTrimFieldValuePairList(String^ sFieldName1, String^ sFieldName2, String^ sTableName)
{
return GetTrimFieldValuePairList(sFieldName1, sFieldName2, sTableName,"");
}
int csharp_db::find_s(String^ sTableName, String^ sFieldName, String^ sFileValue, String^ s_condition)
{
String^ ssql = "SELECT [fd_id],[" + sFieldName + "] FROM [" + sTableName + "] WHERE ["
+ sFieldName + "] =\'" + gce::CheckSQLString(sFileValue) + "\'";
if (s_condition->Trim() != "")
ssql += " AND " + s_condition;
DataTable^ dt = ExecSQLQuery(ssql);
if (dt->Rows->Count > 0)
{
return (int)dt->Rows[0]["fd_id"];
}
return -1;
}
int csharp_db::find_s(String^ sTableName, String^ sFieldName, String^ sFileValue)
{
return find_s(sTableName, sFieldName, sFileValue);
}
bool csharp_db::IsExistTableName(String^ sTableName)
{
#ifdef _WINDOWS_
bool bResult = false;
if (dataFormat == csharp_DataFormat::dfAccdb || dataFormat == csharp_DataFormat::dfMDB)
{
ADOX::Catalog^ catalog = gcnew Catalog();
ADODB::Connection^ cn = gcnew ADODB::Connection();
cn->Open(GetConnection()->ConnectionString, null, null, -1);
catalog->ActiveConnection = cn;
for (int i = 0; i < catalog->Tables->Count; ++i)
{
if (catalog->Tables[i]->Name->ToLower() == sTableName->Trim()->ToLower())
{
bResult = true;
break;
}
}
cn->Close();
catalog->ActiveConnection = null;
}
else if (dataFormat == csharp_DataFormat::dfSQLServer)
{
DataTable^ dt = ExecSQLQuery("SELECT [name] FROM sysobjects WHERE type = \'U\' ORDER BY [name]");
for (int i = 0; i < dt->Rows->Count; ++i)
{
if (dt->Rows[i]["NAME"]->ToString()->ToLower() == sTableName->Trim()->ToLower())
{
bResult = true;
break;
}
}
}
return bResult;
#else
throw gcnew Exception("代码未完成！");
#endif
}
#ifdef _WINDOWS_
DataTable^ csharp_db::ExecSQLView(String^ sSQL, System::Windows::Forms::DataGridView^ dg_view)
{
DataTable^ dt = gcnew DataTable();
if (sSQL->Trim()->Length == 0) return dt;
try
{
GetViewDbDataAdapter()->SelectCommand->CommandText = sSQL;
GetViewDbDataAdapter()->Fill(dt);
}
catch (Exception^ e)
{
gce::ShowError(e->ToString(), "csharp_db^.ExecSQLView");
gce::ShowError("执行 SQL语句： \n " + sSQL + "\n出错！", "csharp_db^.ExecSQLView");
}
if (dg_view != null)
{
dg_view->DataSource = dt;
//gvView.DataBind();
//for(int i=0; i<gvView.Columns.Count; ++i)
//{
// gvView.Columns[i].HeaderStyle.Width = 80;
//}
}
return dt;
}
void csharp_db::GetTrimFieldValueList(String^ sFieldName, String^ sTableName,
String^ sCondition, ComboBox^ cb)
{
cb->Items->Clear();
String^ ssql = "SELECT [" + sFieldName + "] FROM [" + sTableName + "]";
if (sCondition->Trim()->Length != 0)
{
ssql += " WHERE " + sCondition;
}
DataTable^ dt = this->ExecSQLQuery(ssql);
for each(DataRow^ dr in dt->Rows)
{
cb->Items->Add(dr[sFieldName]->ToString()->Trim());
}
}
void csharp_db::GetPairFieldValueList(String^ sIntFieldName, String^ sSplitFieldName,
String^ sTableName, String^ sCondition, ComboBox^ cb)
{
cb->Items->Clear();
csharp_UStringListCI^ sFileNameList = gce::SplitCI(sSplitFieldName,",", true);
String^ ssql = "SELECT [" + sIntFieldName + "]";
if (sFileNameList->Count != 0)
{
for each(String^ s in sFileNameList)
{
ssql += ",[" + s + "]";
}
}
ssql += " FROM[" + sTableName + "]";
if (sCondition->Trim()->Length != 0)
{
ssql += " WHERE " + sCondition;
}
DataTable^ dt = this->ExecSQLQuery(ssql);
for each(DataRow^ dr in dt->Rows)
{
csharp_Pair<int, String^>^ p = gcnew csharp_Pair<int, String^>();
p->First = (int)dr[sIntFieldName];
p->Second = "";
if (sFileNameList->Count > 0)
{
p->Second += dr[sFileNameList[0]]->ToString()->Trim();
String^ sTemp = "";
for (int i = 1; i < sFileNameList->Count; ++i)
{
sTemp += dr[sFileNameList[i]]->ToString()->Trim();
}
if (sTemp != "")
{
p->Second = p->Second + "(" + sTemp + ")";
}
}
cb->Items->Add(p);
}
}
void csharp_db::GetTrimFieldValueList(String^ sFieldName, String^ sTableName,
String^ sSQL, ListBox^ lb)
{
if (lb == null || sTableName->Trim()->Length == 0)
throw gcnew Exception("lb == null || sTableName->Trim()->Length == 0");
lb->Items->Clear();
String^ tmp;
if (sSQL->Trim()->Length == 0)
{
tmp = "SELECT [" + sFieldName + "] FROM " + sTableName;
}
else
{
tmp = sSQL;
}
DataTable^ dt = this->ExecSQLQuery(tmp);
if (dt->Rows->Count > 0)
{
for (int i = 0; i < dt->Rows->Count; i++)
{
lb->Items->Add(dt->Rows[i][sFieldName]->ToString()->Trim());
}
}
}
void csharp_db::GetIDAndNameCNList(String^ sTableName, String^ sCondition, ListView^ lv)
{
if (lv == null || sTableName->Trim()->Length == 0)
throw gcnew Exception("lv == null || sTableName->Trim()->Length == 0");
lv->Items->Clear();
String^ sSQL = "SELECT [fd_id],[fd_name_cn] FROM [" + sTableName + "] ";
if (sCondition->Trim()->Length != 0)
{
sSQL += " WHERE ";
sSQL += sCondition;
}
DataTable^ dt = this->ExecSQLQuery(sSQL);
if (dt->Rows->Count > 0)
{
for (int i = 0; i < dt->Rows->Count; i++)
{
ListViewItem^ lvi = gcnew ListViewItem();
lvi->Text = dt->Rows[i]["fd_name_cn"]->ToString()->Trim();
lvi->Name = dt->Rows[i]["fd_id"]->ToString();
lvi->StateImageIndex = i;
lvi->ImageIndex = i;
lv->Items->Add(lvi);
}
}
}
void csharp_db::getTableNames(System::Collections::IList^ il)
{
if (dataFormat == csharp_DataFormat::dfAccdb || dataFormat == csharp_DataFormat::dfMDB)
{
ADOX::Catalog^ catalog = gcnew Catalog();
ADODB::Connection^ cn = gcnew ADODB::Connection();
cn->Open(GetConnection()->ConnectionString, null, null, -1);
catalog->ActiveConnection = cn;
for (int i = 0; i < catalog->Tables->Count; ++i)
{
il->Add(catalog->Tables[i]->Name);
}
cn->Close();
catalog->ActiveConnection = null;
}
else if (dataFormat == csharp_DataFormat::dfSQLServer)
{
DataTable^ dt = ExecSQLQuery("SELECT [name] FROM sysobjects WHERE type = \'U\' ORDER BY [name]");
for (int i = 0; i < dt->Rows->Count; ++i)
{
il->Add(dt->Rows[i]["NAME"]->ToString());
}
}
}
csharp_StringList^ csharp_db::getTableNames()
{
csharp_StringList^ ls = gcnew csharp_StringList();
if (dataFormat == csharp_DataFormat::dfAccdb || dataFormat == csharp_DataFormat::dfMDB)
{
ADOX::Catalog^ catalog = gcnew Catalog();
ADODB::Connection^ cn = gcnew ADODB::Connection();
cn->Open(GetConnection()->ConnectionString, null, null, -1);
catalog->ActiveConnection = cn;
for (int i = 0; i < catalog->Tables->Count; ++i)
{
ls->Add(catalog->Tables[i]->Name);
}
cn->Close();
catalog->ActiveConnection = null;
}
else if (dataFormat == csharp_DataFormat::dfSQLServer)
{
DataTable^ dt = ExecSQLQuery("SELECT [name] FROM sysobjects WHERE type = \'U\' ORDER BY [name]");
for (int i = 0; i < dt->Rows->Count; ++i)
{
ls->Add(dt->Rows[i]["NAME"]->ToString());
}
}
else if (dataFormat == csharp_DataFormat::dfSQLite)
{
String^ ssql = "SELECT [name] FROM sqlite_master WHERE type = \'table\' ORDER BY [name]";
DataTable^ dt = ExecSQLQuery(ssql);
for (int i = 0; i < dt->Rows->Count; ++i)
{
ls->Add(dt->Rows[i]["name"]->ToString());
}
}
return ls;
}
void csharp_db::getFields(String^ sTableName, System::Collections::IList^ il)
{
if (dataFormat == csharp_DataFormat::dfSQLServer)
{
DataTable^ dt = ExecSQLQuery("SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'");
if (dt->Rows->Count > 0)
{
il->Clear();
String^ sid = dt->Rows[0]["id"]->ToString();
dt = ExecSQLQuery("select [name] FROM [syscolumns] where [id]=\'" + sid + "\'");
for (int i = 0; i < dt->Rows->Count; ++i)
{
il->Add(dt->Rows[i]["name"]->ToString());
}
}
}
}
csharp_StringList^ csharp_db::getFields(String^ sTableName)
{
csharp_StringList^ slResult = gcnew csharp_StringList();
String^ ssql = "";
if (dataFormat == csharp_DataFormat::dfSQLServer)
{
ssql = "SELECT [id] FROM [sysobjects] where [name] =\'" + sTableName + "\'";
DataTable^ dt = ExecSQLQuery(ssql);
if (dt->Rows->Count > 0)
{
String^ sid = dt->Rows[0]["id"]->ToString();
dt = ExecSQLQuery("select [name] FROM [syscolumns] where [id]=\'" + sid + "\' ORDER BY [colorder]");
for (int i = 0; i < dt->Rows->Count; ++i)
{
slResult->Add(dt->Rows[i]["name"]->ToString());
}
}
}
else if (dataFormat == csharp_DataFormat::dfSQLite)
{
ssql = "PRAGMA table_info([" + sTableName + "])";
DataTable^ dt = ExecSQLQuery(ssql);
for each(DataRow^ dr in dt->Rows)
{
slResult->Add(dr["name"]->ToString());
}
}
return slResult;
}
#endif
void csharp_db::GetDistinctTrimFieldValueList(String^ sFieldName,
String^ sTableName, String^ sSQL, ComboBox^ cb)
{
if (cb != null)
{
cb->Items->Clear();
String^ tmp;
if (sSQL->Trim()->Length == 0)
{
tmp = "SELECT DISTINCT " + sFieldName + " FROM " + sTableName;
}
else
{
tmp = sSQL;
}
DataTable^ dt = this->ExecSQLQuery(tmp);
if (dt->Rows->Count > 0)
{
for (int i = 0; i < dt->Rows->Count; i++)
{
cb->Items->Add(dt->Rows[i][sFieldName]->ToString()->Trim());
}
}
}
}
String^ csharp_db::getColumnType(String^ sType)
{
String^ value = null;
if (sType == "String^")
{
value = " text ";
}
else if (sType == "int")
{
value = " integer ";
}
else if (sType == "bool")
{
value = " boolean ";
}
else if (sType == "float")
{
value = " float ";
}
else if (sType == "double")
{
value = " double ";
}
else if (sType == "char")
{
value = " varchar ";
}
else if (sType == "long")
{
value = " long ";
}
/*
* SQLite 没有一个单独的存储类用于存储日期和/或时间，但SQLite是能够存储日期和时间为TEXT，REAL或INTEGER值。
日期格式
TEXT A date in a format like "YYYY-MM-DD HH:MM:SS->SSS"->
REAL The number of days since noon in Greenwich on November 24, 4714 B->C->
INTEGER The number of seconds since 1970-01-01 00:00:00 UTC->
*/
else if (sType == "DateTime")
{
value = " text ";
}
return value;
}
//
int csharp_Field::GetSQLServerXType()
{
int iResult = -1;
switch (DataType)
{
case csharp_DataType::dtDateTime:
iResult = -1;
break;
case csharp_DataType::dtFloat:
iResult = -1;
break;
default:
iResult = -1;
break;
}
return iResult;
}
void csharp_Field::SetSQLServerXType(int iXTypeVale)
{
String^ sTypeName = SQLServerXTYPConverToCSharpTypeName(iXTypeVale);
if (sTypeName == "DateTime")
{
DataType = csharp_DataType::dtDateTime;
}
else if (sTypeName == "Int32")
{
DataType = csharp_DataType::dtInt32;
}
else if (sTypeName == "String")
{
DataType = csharp_DataType::dtString;
}
else if (sTypeName == "Object")
{
DataType = csharp_DataType::dtObject;
}
else if (sTypeName == "Double")
{
DataType = csharp_DataType::dtDouble;
}
}
Type^ csharp_Field::SQLServerXTYPConverToCSharpType(int iXTypeVale)
{
String^ sXTypeString = GetSQLServerXTypeString(iXTypeVale);
SqlDbType sdtType = XTypeStringConverToSqlDbType(sXTypeString);
Type^ tType = SqlDbTypeConvertToCSharpType(sdtType);
return tType;
}
String^ csharp_Field::SQLServerXTYPConverToCSharpTypeName(int iXTypeVale)
{
Type^ t = SQLServerXTYPConverToCSharpType(iXTypeVale);
if (t == null)
{
gce::ShowInfo("错误：未知SQLServer类型：" + iXTypeVale.ToString(),
"String^ csharp_Field::SQLServerXTYPConverToCSharpTypeName");
}
else
{
return t->Name;
}
}
String^ csharp_Field::GetSQLServerXTypeString(int iXTypeVale)
{
/*
34 image
35 text
36 uniqueidentifier
48 tinyint
52 smallint
56 int
58 smalldatetime
59 real
60 money
61 datetime
62 float
98 sql_variant
99 ntext
104 bit
106 decimal
108 numeric
122 smallmoney
127 bigint
165 varbinary
167 varchar
173 binary
175 char
189 timestamp
231 sysname
231 nvarchar
239 nchar
*/
switch (iXTypeVale)
{
case 34:
return L"image";
case 35:
return L"text";
case 36:
return L"uniqueidentifier";
case 48:
return L"tinyint";
case 52:
return L"smallint";
case 56:
return L"int";
case 58:
return L"smalldatetime";
case 59:
return L"real";
case 60:
return L"money";
case 61:
return L"datetime";
case 62:
return L"float";
case 98:
return L"sql_variant";
case 99:
return L"ntext";
case 104:
return L"bit";
case 106:
return L"decimal";
case 108:
return L"numeric";
case 122:
return L"smallmoney";
case 127:
return L"bigint";
case 165:
return L"varbinary";
case 167:
return L"varchar";
case 173:
return L"binary";
case 175:
return L"char";
case 189:
return L"timestamp";
case 231:
return L"nvarchar";
//case 231:
//SQL Server 实例包括用户定义的名为 sysname 的数据类型。
//sysname 用于表列、变量以及用于存储对象名的存储过程参数。sysname 的精确定义与标识符规则相关；
//因此，SQL Server 的各个实例会有所不同。sysname 与 nvarchar(128) 作用相同。
//return "sysname";
case 239:
return L"nchar";
case 241:
return L"xml";
}
return L"未知";
}
/// <summary>
/// https://blog.csdn.net/weixin_42944928/article/details/141575049?spm=1001.2014.3001.5502
/// </summary>
/// <param name="sqlType"></param>
/// <returns></returns>
/// 创建时间： ????-??-?? 最后一次修改时间：2024-08-26
Type^ csharp_Field::SqlDbTypeConvertToCSharpType(SqlDbType sqlType)
{
// Type::GetType(), C++ cli 永远的坑，连遇两次，上一次还是在二年前
// 错天：GetType("System") 错 GetType("System::Int64") 正确：GetType("System.Int64")
switch (sqlType)
{
case SqlDbType::BigInt:
return System::Type::GetType("System.Int64");
case SqlDbType::Binary:
return System::Type::GetType("System.Object");
case SqlDbType::Bit:
return System::Type::GetType("System.Boolean");
case SqlDbType::Char:
return System::Type::GetType("System.String");
case SqlDbType::DateTime:
return System::Type::GetType("System.DateTime");
case SqlDbType::Decimal:
return System::Type::GetType("System.Decimal");
case SqlDbType::Float:
return System::Type::GetType("System.Double");
case SqlDbType::Image:
return System::Type::GetType("System.Object");
case SqlDbType::Int:
return System::Type::GetType("System.Int32");
case SqlDbType::Money:
return System::Type::GetType("System.Decimal");
case SqlDbType::NChar:
return System::Type::GetType("System.String");
case SqlDbType::NText:
return System::Type::GetType("System.String");
case SqlDbType::NVarChar:
return System::Type::GetType("System.String");
case SqlDbType::Real:
return System::Type::GetType("System.Single");
case SqlDbType::SmallDateTime:
return System::Type::GetType("System.DateTime");
case SqlDbType::SmallInt:
return System::Type::GetType("System.Int16");
case SqlDbType::SmallMoney:
return System::Type::GetType("System.Decimal");
case SqlDbType::Text:
return System::Type::GetType("System.String");
case SqlDbType::Timestamp:
return System::Type::GetType("System.Object");
case SqlDbType::TinyInt:
return System::Type::GetType("System.Byte");
case SqlDbType::Udt://自定义的数据类型
return System::Type::GetType("System.Object");
case SqlDbType::UniqueIdentifier:
return System::Type::GetType("System.Object");
case SqlDbType::VarBinary:
return System::Type::GetType("System.Object");
case SqlDbType::VarChar:
return System::Type::GetType("System.String");
case SqlDbType::Variant:
return System::Type::GetType("System.Object");
case SqlDbType::Xml:
return System::Type::GetType("System.Object");
default:
return null;
break;
}
}
SqlDbType csharp_Field::XTypeStringConverToSqlDbType(String^ sXTypeString)
{
SqlDbType dbType = SqlDbType::Variant;//默认为Object
if (sXTypeString == "int") {
return SqlDbType::Int;
}
else if (sXTypeString == "varchar") {
return SqlDbType::VarChar;
}
else if (sXTypeString == "bit") {
return SqlDbType::Bit;
}
else if (sXTypeString == "datetime") {
return SqlDbType::DateTime;
}
else if (sXTypeString == "decimal") {
return SqlDbType::Decimal;
}
else if (sXTypeString == "float") {
return SqlDbType::Float;
}
else if (sXTypeString == "image") {
return SqlDbType::Image;
}
else if (sXTypeString == "money") {
return SqlDbType::Money;
}
else if (sXTypeString == "ntext") {
return SqlDbType::NText;
}
else if (sXTypeString == "nvarchar") {
return SqlDbType::NVarChar;
}
else if (sXTypeString == "smalldatetime") {
return SqlDbType::SmallDateTime;
}
else if (sXTypeString == "smallint") {
return SqlDbType::SmallInt;
}
else if (sXTypeString == "text") {
return SqlDbType::Text;
}
else if (sXTypeString == "bigint") {
return SqlDbType::BigInt;
}
else if (sXTypeString == "binary") {
return SqlDbType::Binary;
}
else if (sXTypeString == "char") {
return SqlDbType::Char;
}
else if (sXTypeString == "nchar") {
return SqlDbType::NChar;
}
else if (sXTypeString == "real") {
return SqlDbType::Real;
}
else if (sXTypeString == "smallmoney") {
return SqlDbType::SmallMoney;
}
else if (sXTypeString == "timestamp") {
return SqlDbType::Timestamp;
}
else if (sXTypeString == "tinyint") {
return SqlDbType::TinyInt;
}
else if (sXTypeString == "uniqueidentifier") {
return SqlDbType::UniqueIdentifier;
}
else if (sXTypeString == "varbinary") {
return SqlDbType::VarBinary;
}
else if (sXTypeString == "xml") {
return SqlDbType::Xml;
}
return dbType;
}
csharp_Field::csharp_Field(String^ sName, String^ sValue, csharp_DataType dt)
{
Name = sName;
Value = sValue;
DataType = dt;
Desc = "";
}
csharp_Field::csharp_Field()
{
Name = "";
Value = "";
DataType = csharp_DataType::dtNULL;
Desc = "";
}
//
_LF_END_
//
