关于C# 数据库访问 转为 C++ CLI 数据库访问
Db_.cs 与 csharp_db.h功能是一样的。
Db_.cs
/****************************************************************************************
创建时间 ：2006年12月19日文件名 ：Db_.cs功能 ：数据库处理作者 ：李锋Email ：runzhilf@139.com 联系电话 ：13828778863AdKeyPrimary 关键字是主关键字。
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_#endifnamespace 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 image35 text36 uniqueidentifier48 tinyint52 smallint56 int58 smalldatetime59 real60 money61 datetime62 float98 sql_variant99 ntext104 bit106 decimal108 numeric122 smallmoney127 bigint165 varbinary167 varchar173 binary175 char189 timestamp231 sysname231 nvarchar239 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;//默认为Objectswitch (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();
#endifreturn 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;#elsethrow 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();elsereturn 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();elsereturn 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();}
#elsethrow 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;elsessql = "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()都返回1if (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变成iTempIDif (ExecNonSQL("UPDATE [" + sTableName + "] SET [fd_id] = " + iTempID.ToString() + " WHERE [fd_id] = " +iID1.ToString()) != 0){//ID2变成ID1if (ExecNonSQL("UPDATE [" + sTableName + "] SET [fd_id] = " + iID1.ToString() + " WHERE [fd_id] = " +iID2.ToString()) != 0){//iTempID 变成 ID2if (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;#elsethrow 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());}}}}#endifpublic 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 ");}}//---------------foreachsb.Replace(",\n", ")", sb.Length - 3, 3); //括号收尾}else if (df == DataFormat_.dfSQLServer){}else if (df == DataFormat_.dfAccdb){}return sb.ToString();}//-------------------------------getCreateTableSql}//--------------------------------------------------------------------Db_}//------------------------------------------------------------------------------------------lfcsharp_db.h
/****************************************************************************************
创建时间 ：2006年12月19日文件名 ：csharp_db.cs => csharp_db.h功能 ：数据库处理作者 ：李锋Email ：runzhilf@139.com联系电话 ：13828778863AdKeyPrimary 关键字是主关键字。
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_CHECKSUMCHECKSUMCHECKSUM_AGGCount(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);#endifstatic 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 ");}}//---------------foreachsb.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();elsereturn 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();elsereturn 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();}
#elsethrow 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;elsessql = "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()都返回1if (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变成iTempIDif (ExecNonSQL("UPDATE [" + sTableName + "] SET [fd_id] = " + iTempID.ToString() + " WHERE [fd_id] = " +iID1.ToString()) != 0){//ID2变成ID1if (ExecNonSQL("UPDATE [" + sTableName + "] SET [fd_id] = " + iID1.ToString() + " WHERE [fd_id] = " +iID2.ToString()) != 0){//iTempID 变成 ID2if (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;#elsethrow 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;
}
#endifvoid 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 image35 text36 uniqueidentifier48 tinyint52 smallint56 int58 smalldatetime59 real60 money61 datetime62 float98 sql_variant99 ntext104 bit106 decimal108 numeric122 smallmoney127 bigint165 varbinary167 varchar173 binary175 char189 timestamp231 sysname231 nvarchar239 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;//默认为Objectif (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_
//相关文章：
关于C# 数据库访问 转为 C++ CLI 数据库访问
Db_.cs 与 csharp_db.h功能是一样的。 Db_.cs /**************************************************************************************** 创建时间 ：2006年12月19日文件名 ：Db_.cs功能 ：数据库…...
百度移动刷下拉词工具：快速出下拉词的技术分析
都2024年了，你还在做SEO百度下拉？答案当然是肯定的，虽然百度的搜索流量不如从前，但移动端的流量依然是巨大的！除了百度SEO快排以外，下拉也是一大流量入口，尤其是在移动端搜索的流量越来越大时&a…...
学习笔记-Golang中的Context
文章目录 1、什么是Context2、Context的作用3、Context的解析3.1、Context的源码解析3.2、 context包中实现context接口的四种结构体类型3.2.1、emptyCtx3.2.2、cancelCtx3.2.3、timerCtx3.2.4、valueCtx 4、总结 1、什么是Context Context是 Go 语言中的一个标准库࿰…...
ArrayList 源码解析
ArrayList是Java集合框架中的一个动态数组实现，提供了可变大小的数组功能。它继承自AbstractList并实现了List接口，是顺序容器，即元素存放的数据与放进去的顺序相同，允许放入null元素，底层通过数组实现。除该类未实现同…...
libgit2编译
1. 源码下载 libgit2源码下载 2. 编译要求 CMake下载 CMake教程 3. 编译步骤 Prerequisites Make sure CMake on your %PATH% Build Create a build directory beneath the libgit2 source directory, and change into it: mkdir build && cd buildCreate the …...
mac新手入门（快捷键）
系统常用快捷键 基本操作 Command-Z 撤销Command-X 剪切 Command-C 拷贝（Copy） Option Shift Command V 纯文本拷贝 Command-V 粘贴 Command-A 全选（All）Command-S 保存（Save) Command-F 查找࿰…...
curl 的使用详解
curl 是一个非常强大的命令行工具，用于通过各种协议（如 HTTP、HTTPS、FTP 等）传输数据。它广泛应用于测试 API、下载文件、调试网络请求等。 下面是 curl 常用功能的详解及示例： 基本语法 curl [options] [URL]1. 基本请求 发起…...
从基础到进阶：利用EasyCVR安防视频汇聚平台实现高效视频监控系统的五步走
随着科技的飞速发展，视频监控技术在社会安全、企业管理、智慧城市构建等领域扮演着越来越重要的角色。一个高效智能的视频监控管理系统不仅能够提升监控效率，还能在预防犯罪、事故预警、数据分析等方面发挥巨大作用。 一、需求分析 在设计视频监控管理…...
CORS漏洞及其防御措施：保护Web应用免受攻击
1. 背景- 什么是CORS？ 在当今互联网时代，Web 应用程序的架构日益复杂。一个后端服务可能对应一个前端，也可能与多个前端进行交互。跨站资源共享（CORS）机制在这种复杂的架构中起着关键作用，但如果配置不当&…...
C语言自定义类型结构体(24)
文章目录 前言一、结构体类型的声明结构体回顾结构体的特殊声明结构体的自引用 二、结构体的内存对齐对齐规则为什么存在内存对齐？修改默认对齐数 三、结构体传参四、结构体实现位段什么是位段位段的内存分配位段的跨平台问题位段的应用位段使用的注意事项 总结 前言…...
补题篇--codeforces
传送门：Problem - 1881C - Codeforces 题目大意： 思路： 首先解决这个问题要知道 一个 ( x , y ) 顺时钟旋转 90 ， 180 ， 270可以得到 ( y , n - x 1 ) ， ( n - x 1 , n - y 1 ) ，( n - y …...
【字幕】恋上数据结构与算法之015动态数组03简单接口的实现
我们先来看一下，不要着急啊大家不要着急，这些东西我肯定会一点一点会给大家去实现，最终实现到跟Java官方版本差不多，只要我们自己实现了，偶尔类似的，你会发现你倒回去看Java官方的那个源码，你会…...
基于2023年网络赛赛题了解OpenCv
一、OpenCv图像读取与显示 1.图像的读取与显示 cv.imread() 图像读取，第一个参数是照片的位置一般是完整路径，第二个参数是指定图片输出的样式 cv.IMREAD_COLOR: 加载彩色图像。任何图像的透明度都会被忽视。（默认模式）。cv.I…...
你到底更适合买虚拟主机还是服务器？
前言 在当今数字化的时代，选择合适的网络服务平台对于个人和企业来说至关重要。无论是搭建个人博客、运营企业网站还是开发游戏，服务器的选择都会直接影响到项目的成本、性能以及用户体验。那么，你到底适合虚拟主机还是服务器呢？…...
linux手册翻译 addr2line
名称 addr2line 将地址转换为文件名和代码行数 简介 addr2line [-a|--addresses][-b bfdname|--targetbfdname][-C|--demangle[style]][-r|--no-recurse-limit][-R|--recurse-limit][-e filename|--exefilename][-f|--functions] [-s|--basename][-i|--inlines][-p|--pretty-…...
python-素数中的等差数列
题目描述 质数是在数论中很有意思的数，有很多题都可以围绕它来出，就如你眼前所见的这道题。 给定一个闭区间 [a,b] ,将此范围内的所有素数进行从小到大排序，对于连续的素数，我们可以发现很多等差数列(元素个数大于等于 3 )&#x…...
Unity3D 服务器AStar寻路客户端位置同步显示验证详解
在游戏开发中，经常需要在服务器和客户端之间同步玩家的位置信息，以便其他玩家可以看到他们的移动。本文将详细介绍如何在Unity 3D中使用AStar算法进行路径规划，并在服务器和客户端之间同步玩家的位置信息。 对惹，这里有一个游戏开…...
无人机之悬停精度篇
无人机的悬停精度是指无人机在无GPS信号或其他外部定位辅助下，能够保持在一个固定空间位置时的精度。这一精度受到多种因素的影响，包括但不限于风速、气压、温度、湿度以及无人机自身的姿态稳定性等。以下是对无人机悬停精度的详细分析： 一、…...
力扣题解2848
大家好，欢迎来到无限大的频道。 今日继续给大家带来力扣题解。 题目描述（简单）： 与车相交的点 给你一个下标从 0 开始的二维整数数组 nums 表示汽车停放在数轴上的坐标。对于任意下标 i，nums[i] [starti, endi] &…...
电子电气架构---智能汽车应该是怎么样的架构？
我是穿拖鞋的汉子，魔都中坚持长期主义的汽车电子工程师。 老规矩，分享一段喜欢的文字，避免自己成为高知识低文化的工程师： 屏蔽力是信息过载时代一个人的特殊竞争力，任何消耗你的人和事，多看一眼都是你的不…...
无心剑七绝《中秋相思》
七绝中秋相思 中秋月满意深长 百代江阳老窖香 莫道天涯情不尽 相思寸寸赋华章 2023年9月29日 平水韵七阳平韵 这首诗七绝《中秋相思》由无心剑所作，以其深情的笔触描绘了中秋夜的相思之情。 诗中首句“中秋月满意深长”即以中秋圆月为起点，勾勒出了一幅…...
Python画笔案例-051 绘制赵爽弦图
1、绘制赵爽弦图 通过 python 的turtle 库绘制 赵爽弦图，如下图： 2、实现代码 绘制 赵爽弦图，以下为实现代码： """赵爽弦图.py本程序演录了如何自定义形状，如何把它添加到造型字典。赵爽弦图是用来证明…...
SEGGERS实时系统embOS推出Linux端模拟器
SEGGER 发布了两个新的 embOS 仿真模拟器：embOS Sim Linux 和 embOS-MPU Sim Linux。 通过模拟 Linux 主机系统上的硬件，取代物理硬件，为开发人员提供了一种无缝的方式来构建原型和测试应用程序。 embOS Sim Linux 端口支持 32 位和 64 位系…...
HTML + CSS - 网页布局之一般布局浮动布局
1. 一般布局 1.1 一般布局相关参数 元素内容常常可以想像为放在一个盒子里，然后在周边加上内边距，边框和外边距，是盒子模型 默认一个块级区域会填充父类所有的行向空间，并且沿着块伸长容纳其内容，可以为块状体设置某…...
python定时任务，定时爬取水质和天气
定时爬取水质和天气 代码 代码 from apscheduler.schedulers.background import BackgroundScheduler import requests import datetimeurlweather "http://localhost:8000/CrwalingViewWeather" # 天气接口 urlwater "http://localhost:8000/CrwalingViewW…...
ARM驱动学习之基础小知识
ARM驱动学习之基础小知识 • sch原理图工程师工作内容 – 方案 – 元器件选型 – 采购（能不能买到，价格） – 原理图（涉及到稳定性） • layout画板工程师 – layout（封装、布局，布线，…...
【字幕】恋上数据结构与算法之019动态数组07打印数组
是吧？什么意思呢？你看啊我们刚刚已经加了三个东西了，我现在希望能够打印一下这个速度，希望能把它里面所有元素打出来，那我们试一下，看它默认是怎么打，这个时候我们右击你会发现它打出来长这样子…...
Python基础语法(3)下
列表和元组 列表是什么，元组是什么 编程中，经常需要使用变量，来保存/表示数据。变量就是内存空间，用来表示或者存储数据。 如果代码中需要表示的数据个数比较少，我们直接创建多个变量即可。 num1 10 num2 20 num3…...
数据稀缺条件下的时间序列微分：符号回归(Symbolic Regression)方法介绍与Python示例
时间序列概况在日常生活和专业研究中都很常见。简而言之,时间序列概况是一系列连续的数据点 y(0), y(1), …, y(t) ,其中时间 t 的点依赖于时间 t-1 的前一个点(或更早的时间点)。 在许多应用中,研究者致力于预测时间序列概况的未来行为。存在各种建模方法。这些模型通常基于过…...
XML_Tomcat_HTTP
第四章 XML_Tomcat10_HTTP 一 XML XML是EXtensible Markup Language的缩写，翻译过来就是可扩展标记语言。所以很明显，XML和HTML一样都是标记语言，也就是说它们的基本语法都是标签。 可扩展 三个字表面上的意思是XML允许自定义格式。但这不代…...