Gs_Class.Gs_DataFunction数据操作类库20160225

using System;
using System.Data;
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.IO;
using System.Data.OleDb;
using System.Data.Common;
using System.Management; //添加引用
using System.Text.RegularExpressions;
using System.Collections;
using System.Collections.Generic;

namespace Gs_Class
{
public partial class gsDataFunction : Gs_DataFunction { }; //换个名好输入
public partial class Gs_DataFunction //: System.Web.UI.Page
{
public static bool DebugMode = false; //调试状态
public const int nXor = 5; //异或值
public const int nDefaultSessionTimeOut = 60; //默认超时
public static string default_Direct = "~/default.aspx";
public static string zhCN_Direct = "~/zh-CN/Login.aspx";
public static string enGB_Direct = "~/en-GB/Login.aspx";
public static string sConnectionString = "";

#region//下载文件，runDownLoad
protected static bool runDownLoad(HttpResponse Response, string fileName)
{
bool lResult = false;
try
{
if (!System.IO.File.Exists(fileName)) throw new Exception("Can not find this file!");
FileInfo file = new System.IO.FileInfo(fileName);
//
Response.Clear();
//设置输出流的HTPP字符集，确定字符的编码格式
Response.Charset = "utf-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
//Response.ContentEncoding = System.Text.Encoding.UTF8;
//下载attachment 参数表示作为附件下载，可以改成online在线打开。添加头信息，为"文件下载/另存为"对话框指定默认文件名
Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(file.Name));
// 添加头信息，指定文件大小，让浏览器能够显示下载进度
// Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流，必须被下载
Response.ContentType = "application/ms-word";
//this.EnableViewState = false;
Response.Filter.Close();
// 把文件流发送到客户端
Response.WriteFile(file.FullName);
// 停止页面的执行
Response.End();
lResult = true;
}
catch (Exception x)
{
throw new Exception("runDownload Error:" + x.Message);
}
finally
{
}
return lResult;
}
#endregion

#region//在GridView中根据字段查找列，findColumnByField。列必须是绑定列
/// <summary>
/// 在GridView中根据字段查找列，列必须是绑定列、超链接列 findColumnByField
/// </summary>
/// <param name="grd"></param>
/// <param name="sField"></param>
/// <returns></returns>
public static int findColumnByField(GridView grd, string sField)
{ //根据字段查找列
int result = -1;
int i = 0;
object obj = null;
for (i = 0; i < grd.Columns.Count; i++)
{
obj = grd.Columns[i];
if (!(obj is BoundField || obj is HyperLinkField)) continue;
//
if (obj is BoundField && (obj as BoundField).DataField.ToUpper() == sField.ToUpper()) return i;
if (obj is HyperLinkField && (obj as HyperLinkField).DataTextField.ToUpper() == sField.ToUpper()) return i;
}
return result;
}
public static DataControlField findColumnByFieldA(GridView grd, string sField)
{ //根据字段查找列 BoundField DataControlField
int n = findColumnByField(grd, sField);
if (n < 0) return null; else return grd.Columns[n];
}
#endregion

#region//生成新编号，buildNewID
/// <summary>
/// 生成新编号，以OleDbConnection作为参数注意：如果connection启动了事务，会返回DataReader错误。
/// </summary>
public static string buildNewID(string sTable, string sIDHead, int nIDLen, string sKeyField, OleDbConnection connection)
{
return buildNewID(sTable, sIDHead, nIDLen, sKeyField, "", connection);
}
public static string buildNewID(string sTable, string sIDHead, int nIDLen, string sKeyField, string sAddinFilter, OleDbConnection connection)
{
OleDbConnection conn = connection;
if (conn == null) conn = new OleDbConnection(ConfigurationManager.AppSettings["ConnectionString"]);
OleDbCommand cmd = new OleDbCommand("", conn);
return buildNewID(sTable, sIDHead, nIDLen, sKeyField, sAddinFilter, cmd);
}
/// <summary>
/// 生成新编号，以OleDbCommand作为参数，便于统一事务，注意：传入Command之前，请显式关闭基于此Command的DataReader，否则会出DataReader错误
/// </summary>
public static string buildNewID(string sTable, string sIDHead, int nIDLen, string sKeyField, OleDbCommand command)
{
return buildNewID(sTable, sIDHead, nIDLen, sKeyField, "", command);
}
public static string buildNewID(string sTable, string sIDHead, int nIDLen, string sKeyField, string sAddinFilter, OleDbCommand command)
{
return buildNewID(sTable, sIDHead, nIDLen, sKeyField, "", false, command);
}
/// <summary>
/// 生成新编号。从数据表中查出Top 1符合条件的值，＋１
/// 编号生成规则有3种：纯数字，前导0的数字，有编号头的：
/// * 纯数字：用dbo.isNumber函数检查
/// * 有编号头的：有头则like头，
/// * 没有编号头就是数字前导0，只判断首字母。
/// </summary>
/// <param name="sTable">要查找的表</param>
/// <param name="sIDHead">编号头</param>
/// <param name="nIDLen">编号长度</param>
/// <param name="sKeyField">关键字段，为空则默认为“sID”</param>
/// <param name="sAddinFilter">附加查询条件</param>
/// <param name="lIdIsNumber">此编号是纯数字，会将关键字转为Numeric来排序。是否纯数字通过dbo.isNumber函数判断，所以数据库必须有此函数</param>
/// <param name="command">通过此command执行，类似delphi的TAdoQuery，可以带事务</param>
/// <returns>返回字符串</returns>
public static string buildNewID(string sTable, string sIDHead, int nIDLen, string sKeyField, string sAddinFilter, bool lIdIsNumber, OleDbCommand command)
{
string result = "";
OleDbConnection conn = null;
OleDbCommand cmd = command;
getDataCommand(cmd, ref conn, ref cmd);
bool lConn = conn.State == ConnectionState.Open;
OleDbDataReader dr = null;
//
UInt64 nMax = 0; //int n = nIDLen + sIDHead.Length;
string s = "", sMax = "0", sHead = (string.IsNullOrEmpty(sIDHead) ? "" : sIDHead.Trim()), sIDField = (string.IsNullOrEmpty(sKeyField) ? "sID" : sKeyField);
string sql = "select top 1 " + sIDField + " as sKey from " + sTable + " where 1=1 ";
if (!string.IsNullOrEmpty(sAddinFilter)) sql += " And (" + sAddinFilter + ") ";
/* 编号生成规则有3种：纯数字，前导0的数字，有编号头的。
* 纯数字：用dbo.isNumber函数检查
* 有编号头的：有头则like头，
* 没有编号头就是数字前导0，只判断首字母。
*/
if (lIdIsNumber)
{ //纯数字
sql += " and dbo.isNumber(" + sIDField + ")=1 ";
//排序
sql += " order by convert(numeric(20,4)," + sIDField + ") desc ";
}
else
{ //非纯数字的
if (!string.IsNullOrEmpty(sIDHead))
sql += " And " + sIDField + " like '" + sIDHead + "%' "; //有编号头的
else
sql += " And (subString(" + sIDField + ",1,1) between '0' and '9') "; //无编号头，但是前导0的
//编号长度
if (nIDLen > 0) sql += " and len(" + sIDField + ")=" + (nIDLen + sIDHead.Length).ToString();
//排序
sql += " Order By " + sIDField + " Desc "; //正序
}
//
try
{
if (!lConn) conn.Open();
//从数据库中取出最大的号
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
dr = cmd.ExecuteReader();
if (dr.Read() && dr["sKey"] != null) sMax = dr["sKey"].ToString();
if (string.IsNullOrEmpty(sMax)) sMax = "0";
//去掉头部 sMax = sMax.Substring(sHead.Length + 1, sMax.Length - sHead.Length - 1).Trim();
//周承昊：2010-9-13：当sMax="YG46002"时，取出来总是"6002"
if (sHead != "" && sMax.Length > sHead.Length) sMax = sMax.Remove(0, sHead.Length).Trim();
//换算成数字
try { nMax = Convert.ToUInt64(sMax); }
catch { }
//增加1
nMax++;
//返回值
if (lIdIsNumber) //纯数字
result = nMax.ToString();
else
result = sHead + nMax.ToString().PadLeft(nIDLen, '0');
}
catch (Exception x)
{ globalErrorString = "[buildNewID]生成新编号出错！" + x.Message; throw new Exception(globalErrorString); }
finally
{ if (!lConn) conn.Close(); if (dr != null) dr.Close(); }
//
return result;
}
#endregion

#region//设置字段外观，setFieldView。 DataColumn只能设置readonly和Caption，别的参数先留着备用
/// <summary>
/// 设置字段外观，DataColumn只能设置readonly和Caption，别的参数先留着备用
/// </summary>
/// <param name="data"></param>
/// <param name="sField"></param>
/// <param name="sCaption"></param>
/// <returns></returns>
//(Data:tDataSet;sField,sCaption:String;nWidth:Integer=-1;lVisible:Boolean=True;lReadOnly:Boolean=False):Boolean;
public static bool setFieldView(DataTable data, string sField, string sCaption)
{ return setFieldView(data, sField, sCaption, -1, true, false); }
public static bool setFieldView(DataTable data, string sField, string sCaption, string sFormat)
{ return setFieldView(data, sField, sCaption, sFormat, -1, true, false, -1); }
public static bool setFieldView(DataTable data, string sField, string sCaption, bool lVisible)
{ return setFieldView(data, sField, sCaption, -1, lVisible, false); }
public static bool setFieldView(DataTable data, string sField, string sCaption, int nWidth)
{ return setFieldView(data, sField, sCaption, nWidth, true, false); }
public static bool setFieldView(DataTable data, string sField, string sCaption, int nWidth, bool lVisible, bool lReadonly)
{ return setFieldView(data, sField, sCaption, "", nWidth, lVisible, lReadonly, -1); }
/// <summary>
/// 设置一个字段的外观显示，包括Caption、是否只读、是否可见等
/// </summary>
/// <param name="data">数据源</param>
/// <param name="sField">字段</param>
/// <param name="sCaption">标题</param>
/// <param name="nWidth">宽度，col.ExtendedProperties["Width"] = nWidth</param>
/// <param name="lVisible">可见否，col.ExtendedProperties["Visible"] = lVisible</param>
/// <param name="lReadonly">是否只读，此参数忽略</param>
/// <param name="nIndex">位置序号，为负数或超出有效范围则忽略</param>
/// <returns>返回成功与否</returns>
public static bool setFieldView(DataTable data, string sField, string sCaption, string sFormat, int nWidth, bool lVisible, bool lReadonly, int nIndex)
{ //设置一个字段的外观，留着参数以后用
bool result = false;
//
DataTable dt = data;
DataColumn col = null;
int i = 0;
//
try
{
col = dt.Columns[sField.Trim()];
if (col == null) return false; //查找字段，找不到就忽略
//设置
if (!string.IsNullOrEmpty(sCaption)) col.Caption = sCaption;
col.ExtendedProperties["Visible"] = lVisible;
col.ExtendedProperties["Width"] = nWidth;
col.ExtendedProperties["Format"] = sFormat;
//if (nWidth > 0) col.MaxLength = nWidth;
//col.ReadOnly = lReadonly;
//序号
if (nIndex >= 0 && nIndex <= data.Columns.Count - 1) col.SetOrdinal(nIndex);
//完成
result = true;
}
catch (Exception x)
{ }
//
return result;
}
public static bool setFieldViewHide(DataTable data, params string[] sFields)
{
bool result = false;
DataTable dt = data;
try
{
foreach (string sField in sFields)
{
DataColumn col = dt.Columns[sField.Trim()];
if (col == null) return false; //查找字段，找不到就忽略
//设置
col.ExtendedProperties["Visible"] = false;
}
result = true;
}
catch (Exception x)
{ }
return result;
}
#endregion

#region//向数据控件中写入值 setDbValue，对象可以是OleDbCommand的Param、DataRow的字段、DataTable的行
/// <summary>
/// 向数据控件中写入值 setDbValue
/// </summary>
/// <param name="dbObject">对象可以是OleDbCommand的Param、DataRow的字段、DataTable的行</param>
/// <param name="sParName">Param名、字段名</param>
/// <param name="oValue">要写入的值</param>
/// <returns>返回成功与否</returns>
public static bool setDbValue(object dbObject, string sParName, object oValue)
{ //设置数据控件的值
bool result = false;
object db = dbObject;
try
{
if (db is OleDbCommand)
{ //命令对象
(db as OleDbCommand).Parameters.AddWithValue(sParName, oValue);
result = true;
}
else if (db is DataRow)
{ //DataRow对象
DataRow row = db as DataRow;
if (row.Table.Columns.Contains(sParName)) row[sParName] = oValue;
result = true;
}
else if (db is DataTable)
{ //数据表，写第一行，没有则初始化一行
DataTable dt = db as DataTable;
DataRow row = null;
//如有则取第一行，否则增加一行并初始化之
if (dt.Rows.Count > 0) row = dt.Rows[0]; else { row = dt.NewRow(); Gs_DataFunction.initializeRecord(row); dt.Rows.Add(row); }
result = setDbValue(row, sParName, oValue); //递归
}
else
throw new Exception("不支持的类型！" + db.GetType().ToString());
}
catch (Exception x)
{ throw new Exception("[setDbValue]异常：\r\n" + x.Message); }
return result;
}
#endregion

#region//输出到excel，exportToExcel、gridToExcel，参考网站：http://hi.baidu.com/zagelover/blog/item/61eadacb17869dfb52664ff7.html
/// <summary>
/// 表格到Excel，来源自GridView，并下载
/// </summary>
/// <param name="page"></param>
/// <param name="grd"></param>
/// <param name="sFileName"></param>
/// <returns></returns>
public static bool exportToExcel(Page Page, Control objFrom, string sFileName)
{ //grid导出excel
bool result = false;
Control obj = objFrom;
HttpResponse Response = HttpContext.Current.Response; //Page.Response HttpContext.Current.Response
//
try
{
//清除Response缓存内容
Response.Clear();
//缓存输出，并在完成整个响应之后将其发送
Response.Buffer = true;
//设置输出流的HTPP字符集，确定字符的编码格式
Response.Charset = "GB2312"; //质疑
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
//下面这行很重要， attachment 参数表示作为附件下载，您可以改成 online在线打开
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(sFileName, System.Text.Encoding.UTF8)); //sFileName
//Response.ContentType指定文件类型.可以为application/ms-excel,application/ms-word,application/ms-txt,application/ms-html或其他浏览器可直接支持文档
Response.ContentType = "application/ms-excel";
//用GridView输出
Page.EnableViewState = false;
System.Globalization.CultureInfo myclt = new System.Globalization.CultureInfo("ZH-CN", true);
//
System.IO.StringWriter swBody = new System.IO.StringWriter(myclt);
System.Web.UI.HtmlTextWriter hwBody = new HtmlTextWriter(swBody);
//dv表示输出GridView，你也可以绑定datagrid,或其他支持obj.RenderControl()属性的控件
if (obj != null) obj.RenderControl(hwBody);// else Page.RenderControl(hwBody); //grd.RenderControl(hw);
Response.Write(swBody.ToString());
//消除乱码特别设定，非常规方法
/*string strExcel = "";
strExcel = "";
strExcel += hwBody.InnerWriter.ToString();
HttpContext.Current.Response.Write(strExcel); */
//完成
Response.End();
//
result = true;
}
catch (Exception x)
{
result = true;
//throw new Exception("[gridToExcel]输出excel出错！"+x.Message);
}
//
return result;
}
/*覆盖系统的验证,输出xls时会出一个“需要runat=server”，此函数覆盖之
public override void VerifyRenderingInServerForm(Control control)
{
// Confirms that an HtmlForm control is rendered for
//导出列表到Excel,如果不过载这个虚函数，就会在试图将GRIDVIEW中的数据导出至EXCEL时抛出异常：
//类型“GridView”的控件“ctl00_content_gridView1”必须放在具有 runat=server 的窗体标记内。
//页面是从母版页继承的，而gridview所在的ContentPlaceHolder确定是放在form中的。以前只有控件未放在form中才会抛出同类异常。
} */
public static bool exportToExcel(string strFileName, DataTable data)
{ //用dataTable输出excel
bool result = false, lVisible = true;
object o = "", oValue = "";
string s = "", sFmt = "";
ArrayList cols = new ArrayList();
DataColumn col = null;
//
HttpResponse Response = HttpContext.Current.Response; //Page.Response HttpContext.Current.Response
//
try
{
//清除Response缓存内容
Response.Clear();
//缓存输出，并在完成整个响应之后将其发送
Response.Buffer = true;
//strFileName指定输出文件的名称，注意其扩展名和指定文件类型相符，可以为：.doc .xls .txt .htm
if (new System.IO.FileInfo(strFileName).Extension.ToLower() != ".xls") strFileName += ".xls"; //xls csv
//设置输出流的HTPP字符集，确定字符的编码格式
//HttpContext.Current.Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
//下面这行很重要， attachment 参数表示作为附件下载，您可以改成 online在线打开
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName));
//Response.ContentType指定文件类型.可以为application/ms-excel,application/ms-word,application/ms-txt,application/ms-html或其他浏览器可直接支持文档
Response.ContentType = "application/ms-excel";

string colHeaders = "", ls_item = "";
int i = 0;
//定义表对象与行对像，同时用DataSet对其值进行初始化
DataRow[] myRow = data.Select("");
//取可用的字段列表
cols.Clear();
for (i = 0; i < data.Columns.Count; i++)
{
col = data.Columns[i];
lVisible = getColumnVisibleProperty(col);
if (lVisible) cols.Add(col.ColumnName); //不可见，则忽略之
}
//取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符
for (i = 0; i < cols.Count - 1; i++) colHeaders += data.Columns[cols[i].ToString()].Caption.ToString() + "\t";
colHeaders += data.Columns[cols[i].ToString()].Caption.ToString() + "\n"; //最后一行，换行符
Response.Write(colHeaders); //向HTTP输出流中写入取得的数据信息
//逐行处理数据
foreach (DataRow row in myRow)
{
//在当前行中，逐列获得数据，数据之间以\t分割，结束时加回车符\n。
for (i = 0; i < (cols.Count - 1); i++)
{
string sColName = cols[i].ToString(); //列英文名
col = data.Columns[sColName]; //引用此列
sFmt = getColumnFormatProperty(col).Trim(); //格式化字符串
//取值
o = row[sColName];
oValue = o;
if (!string.IsNullOrEmpty(sFmt)) oValue = string.Format(sFmt, oValue);
//文本之前加单引号区分开。
if (col.DataType == typeof(string))
{
s = oValue == null ? "" : oValue.ToString();
if (s.Length > 8 && s[0] >= '0' && s[0] <= '9') oValue = "'" + s;
}
//逻辑值
if (oValue is Boolean) oValue = (Convert.ToBoolean(oValue) ? "是" : "否");
//移除\r\n
s = exportExcelAddSplitChar(oValue.ToString());
ls_item += s + "\t";
}
//最后一列
sFmt = getColumnFormatProperty(data.Columns[cols[i].ToString()]).Trim(); //取出格式化字符串
oValue = row[cols[i].ToString()];
if (!string.IsNullOrEmpty(sFmt)) oValue = string.Format(sFmt, oValue); //转化成格式
s = exportExcelAddSplitChar(oValue.ToString());
//最后一列，要加回车符
ls_item += s + "\n";
/* for (i = 0; i < data.Columns.Count - 1; i++) ls_item += row[i].ToString() + "\t";
ls_item += row[i].ToString() + "\n"; */
//当前行数据写入HTTP输出流，并且置空ls_item以便返程数据
Response.Write(ls_item);
ls_item = "";
}
//写缓冲区中的数据到HTTP头文件中
Response.End();
//
result = true;
}
catch
{ }
//
return result;
}
private static string exportExcelAddSplitChar(string s)
{ //输出excel时，如果中间包含回车换行，则需要用双引号套起来
string result = s, sEnter = "\r\n"; //Convert.ToChar(13).ToString() + Convert.ToChar(10).ToString();
if (result.Contains("\r") || result.Contains("\n")) result = "\"" + result + "\"";
return result;
}
public static bool exportToExcel(Page Page, OleDbDataReader dataReader, string sFileName)
{ //输出到Excel，从DataReader中
return true;
}
public static bool getColumnVisibleProperty(DataColumn col)
{ //获取Column的Visible扩展属性
bool result = true;
if (col.ExtendedProperties.ContainsKey("Visible"))
try
{ result = Convert.ToBoolean(col.ExtendedProperties["Visible"]); }
catch { }
//
return result;
}
public static string getColumnFormatProperty(DataColumn col)
{ //获取col的格式设置
string result = "";
if (col.ExtendedProperties.ContainsKey("Format"))
try { result = Convert.ToString(col.ExtendedProperties["Format"]); }
catch { }
//完成
return result;
}
public static int getColumnWidthProperty(DataColumn col)
{ //获取col的宽度
int result = -1;
if (col.ExtendedProperties.ContainsKey("Width"))
try { result = Convert.ToInt16(col.ExtendedProperties["Width"]); }
catch { }
//完成
return result;
}
#endregion

#region//从dataReader中查找字段的序号，没有返回－1，getFieldIndex。
/// <summary>
/// //从dataReader中查找字段的序号，没有返回－1
/// </summary>
public static int getFieldIndex(OleDbDataReader db, string sField)
{ //从dataReader中查找字段的序号，没有返回－1
int result = -1, i = 0;
string s = "";
for (i = 0; i < db.FieldCount; i++) if (db.GetName(i).Trim().ToUpper() == sField.Trim().ToUpper()) return i;
return result;
}
public static int getFieldIndex(DataTable db, string sField)
{ //从dataReader中查找字段的序号，没有返回－1
int result = -1, i = 0;
string s = sField.ToUpper().Trim();
for (i = 0; i < db.Columns.Count; i++) if (db.Columns[i].ColumnName.Trim().ToUpper() == s) return i;
return result;
}
#endregion

#region //设置字段的位置
/// <summary>
/// 设置字段的位置
/// </summary>
/// <param name="db">数据源</param>
/// <param name="sField">字段</param>
/// <param name="nIndex">位置</param>
/// <returns>非法返回-1；否则返回列位置</returns>
public static int setFieldIndex(DataTable db, string sField, int nIndex)
{ //设置字段列的位置
int result = -1;
if (getFieldIndex(db, sField) > -1 && db.Columns.Count>nIndex ) { db.Columns[sField].SetOrdinal(nIndex); result = nIndex; }
return result;
}
public static int setFieldIndexAfter(DataTable db, string sField, string sAfterThisField)
{
int result = -1;
int /*n1 = getFieldIndex(db, sField),*/ n2 = getFieldIndex(db, sAfterThisField);
if (/*n1 < 0 ||*/ n2 < 0) return result; //字段无效
result = setFieldIndex(db, sField, n2 + 1); //移动
return result;
}
public static int moveFieldAfter(DataTable db, string sField, string sAfterThisField)
{
return setFieldIndexAfter(db, sField, sAfterThisField);
}
#endregion

#region //填充数据到下拉列表 fillComboBox fillComboBoxWithBlank
/// <summary>
/// 填充数据到下拉列表
/// </summary>
public static bool fillComboBox(DropDownList cbx, string sTableName)
{
return fillComboBox(null as OleDbConnection, cbx, "select * from " + sTableName, "", "");
}
public static bool fillComboBox(OleDbConnection connection, DropDownList cbx, string sScript, string sValueField, string sCaptionField)
{ //填充dropDown，用connection读取数据
OleDbCommand cmd = new OleDbCommand(sScript, connection);
return fillComboBox(cmd, cbx, sScript, sValueField, sCaptionField);
}
/// <summary>
/// 填充数据到下拉列表DropdownList，用command做数据访问。填充前不清空dropdownList的原有项目。
/// </summary>
/// <param name="command">用此command访问数据库，类似delphi的TAdoQuery</param>
/// <param name="cbx">填充到此控件中，填充前不清空原有项目</param>
/// <param name="sScript">用此脚本读取数据</param>
/// <param name="sValueField">填充项的Value字段，默认为sID</param>
/// <param name="sCaptionField">填充项的Text字段，默认为sName</param>
/// <returns>返回成功与否</returns>
public static bool fillComboBox(OleDbCommand command, DropDownList cbx, string sScript, string sValueField, string sCaptionField)
{ //填充dropDown，用Command读取数据
bool result = false;
string slCapField = sCaptionField.Trim(), slIDField = sValueField.Trim(), sConn = "";
if (slCapField == "") slCapField = "sName";
if (slIDField == "") slIDField = "sID";
//
OleDbConnection conn = null;
OleDbCommand cmd = command;
getDataCommand(cmd, ref conn, ref cmd);
OleDbDataReader dr = null;
bool lConn = conn.State == ConnectionState.Open;
//
try
{
if (!lConn) conn.Open();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sScript;
//读取，并循环加入
dr = cmd.ExecuteReader();
while (dr.Read())
cbx.Items.Add(new ListItem(dr[slCapField].ToString(), dr[slIDField].ToString()));
//完成
result = true;
}
catch (Exception x)
{ throw new Exception("[fillComboBox]填充内容错误！" + x.Message); }
finally
{ if (dr != null) dr.Close(); if (!lConn) conn.Close(); } //必须显式关闭DataReader，如果传来的连接不是打开的或没有传来，则关闭之
//完成，返回
return result;
}
/// <summary>
/// 填充数据到下拉列表，填充前加一个空“”值的项目。
/// </summary>
public static bool fillComboBoxWithBlank(OleDbConnection connection, DropDownList cbx, string sScript, string sValueField, string sCaptionField)
{ //带空行
if (cbx.Items.FindByValue("") == null) cbx.Items.Add(new ListItem("", ""));
return Gs_DataFunction.fillComboBox(connection, cbx, sScript, sValueField, sCaptionField);
}
public static bool fillComboBoxWithBlank(OleDbCommand command, DropDownList cbx, string sScript, string sValueField, string sCaptionField)
{ //带空行添加
if (cbx.Items.FindByValue("") == null) cbx.Items.Add(new ListItem("", ""));
return Gs_DataFunction.fillComboBox(command, cbx, sScript, sValueField, sCaptionField);
}
/// <summary>
/// 填充Combobox的递归执行
/// </summary>
public static bool fillComboBoxRecursive(OleDbCommand command, DropDownList cbx, string sTable, string sParent, string sAddinFilter, int nLevel)
{ return fillComboBoxRecursive(command, cbx, sTable, sParent, sAddinFilter, "", nLevel); }
/// <summary>
/// 填充Combobox的递归执行
/// </summary>
/// <param name="command">ole数据组件</param>
/// <param name="cbx">下拉框</param>
/// <param name="sTable">表名，数据来源</param>
/// <param name="sParent">上记号sParent字段</param>
/// <param name="sWhere">SQL的Where条件</param>
/// <param name="sOrderBy">SQL的OrderBy字段</param>
/// <param name="nLevel">级别，备用的，意义不大</param>
/// <returns>返回成功与否</returns>
public static bool fillComboBoxRecursive(OleDbCommand command, DropDownList cbx, string sTable, string sParent, string sWhere, string sOrderBy, int nLevel)
{ //填充的执行过程
bool lResult = false, lConn = true;
if (cbx == null) return false;
string s = "", sID = "", sName = "", sF = "", sql = "select sID, sName, sParent from " + sTable + " as Sections where lEnable=1 ";
sql += " and sParent='" + sParent + "' ";
if (!string.IsNullOrEmpty(sWhere)) sql += " And (" + sWhere + ") ";
if (!string.IsNullOrEmpty(sOrderBy)) sql += " order by (" + sOrderBy + ") ";
for (int i = 0; i < nLevel; i++) s += "　"; //nLevel-1
//if (s != "") s += "┗";
//
OleDbCommand cmd = command;
DataTable dt = null;
try
{
Gs_DataFunction.getSqlResult(sql, ref dt, cmd);
//
foreach (DataRow db in dt.Rows)
{
sID = db["sID"].ToString();
sName = s + db["sName"].ToString();
cbx.Items.Add(new ListItem(sName, sID));
//递归调用
fillComboBoxRecursive(cmd, cbx, sTable, sID, sWhere, nLevel + 1);
}
//完毕
lResult = true;
}
catch (Exception x)
{ Gs_DataFunction.globalErrorString = x.Message; throw new Exception("[fillComboBoxRecursive]递归填充出错！" + x.Message); }
finally
{
if (command == null && cmd != null) cmd.Dispose(); //释放资源
if (dt != null) dt.Dispose();
}
//
return lResult;
}
#endregion

#region //填充DataGrid表格，FillDataGrid。默认通过DataAdapter填充DataSet，然后用DataSet填充表格
/// <summary>
/// 填充DataGrid表格，FillDataGrid。默认通过DataAdapter填充DataSet，然后用DataSet填充表格
/// </summary>
public static bool FillDataGrid(OleDbConnection connection, DataGrid grd, string sScript)
{
string sConn = "";
DataSet dt = new DataSet();
OleDbConnection conn = connection;
if (conn == null)
conn = new OleDbConnection(ConfigurationManager.AppSettings["ConnectionString"]); //_Default.sConnectionString
OleDbDataAdapter da = new OleDbDataAdapter(sScript, conn); //脚本
try
{
da.Fill(dt, "TableByFillDataGrid");
grd.DataSource = dt;
grd.DataBind();
//
return true;
}
catch (Exception x)
{
throw new Exception("[FillDataGrid]填充数据表出错！\n " + x.Message + "\n " + sScript);
return false;
}
finally
{
if (connection == null) conn.Close();
}
}
#endregion

#region //getDataCommand：获取可用的command和connection，getDataCommand。在许多处理中，传入oledbCommand作为参数，参数可以为null，自动处理
/// <summary>
/// 获取可用的command和connection。在许多处理中，传入oledbCommand作为参数，参数可以为null，自动处理
/// </summary>
/// <param name="commandFromParam">作为参数传入的Command</param>
/// <param name="connection">返回一个可用的command实例</param>
/// <param name="command">返回一个可用的connection实例</param>
/// <returns>成功与否</returns>
public static bool getDataCommand(OleDbCommand commandFromParam, ref OleDbConnection connection, ref OleDbCommand command)
{
command = commandFromParam;
if (command == null)
{
connection = getNewConnection();
command = new OleDbCommand("", connection);
}
else
{
connection = command.Connection;
}
if (connection == null) connection = getNewConnection();
if (command == null) command = new OleDbCommand("", connection); //20130606周承昊加入
if (command.Connection == null) command.Connection = connection;
//
return true;
}
/// <summary>
/// 获取可用的command和connection、Transaction事务。在许多处理中，传入oledbCommand作为参数，参数可以为null，自动处理
/// </summary>
/// <param name="commandFromParam">作为参数传入的Command</param>
/// <param name="connection">返回一个可用的command实例</param>
/// <param name="command">返回一个可用的connection实例</param>
/// <param name="transaction">返回一个可用的transaction事务实例</param>
/// <returns>成功与否</returns>
public static bool getDataCommand(OleDbCommand commandFromParam, ref OleDbConnection connection, ref OleDbCommand command, ref OleDbTransaction transaction)
{
bool result = false;
try
{
if (!getDataCommand(commandFromParam, ref connection, ref command)) return false;
//事务
transaction = command.Transaction;
if (transaction == null)
{ //事务启动，前提是打开连接
if (connection.State != ConnectionState.Open) connection.Open();
transaction = connection.BeginTransaction();
command.Transaction = transaction;
}
//完成
result = true;
}
catch (Exception x)
{ throw new Exception("[getDataCommand-Transaction]获取可用的事务、命令、连接出错！\r\n" + x.Message); }
//返回
return result;
}
#endregion

#region //getDataAdapter：生成可以OleDbDataAdapter.Update(DataSet)更新的DataSet和OleDbDataAdapter
/// <summary>
/// 生成可用于update的adapter，修改data的数据后，调用adapter.update(data)即可更新数据，等效于delphi的dataSet。
/// 辅助处理有： Gs_DataFunction.initializeRecord初始化数据，Gs_Class.getDefaultValueOfType
/// </summary>
/// <param name="commandPar">用来取参数包括connection和transaction的命令，执行后其commandText会变成读取数据的脚本</param>
/// <param name="sSelectScript">读取数据的脚本</param>
/// <param name="da">返回的OleDbDataAdapter数据适配器，传入null用commandPar生成，否则将其SelectCommand设置成commandPar</param>
/// <param name="data">返回数据集，处理完后，可以用OleDbDataAdapter.Update(DataSet)更新数据</param>
/// <returns>成功与否</returns>
public static bool getDataAdapter(OleDbCommand commandPar, string sSelectScript, ref OleDbDataAdapter da, ref DataSet data)
{
bool result = false;
OleDbCommand cmd = null;
OleDbConnection conn = null;
OleDbCommandBuilder builder = null;
Gs_DataFunction.getDataCommand(commandPar, ref conn, ref cmd); //确保实例化
bool lConn = conn.State == ConnectionState.Open;
try
{
cmd.CommandText = sSelectScript;
if (da == null)
da = new OleDbDataAdapter(cmd); //没有则生成一个
else
da.SelectCommand = cmd; //用这个comman
if (data == null) data = new DataSet("queryResult");
//生成相应脚本，此步忽略
builder = new OleDbCommandBuilder(da);
if (conn.State != ConnectionState.Open) conn.Open();
if (da.InsertCommand == null) da.InsertCommand = builder.GetInsertCommand();
if (da.DeleteCommand == null) da.DeleteCommand = builder.GetDeleteCommand();
if (da.UpdateCommand == null) da.UpdateCommand = builder.GetUpdateCommand();
//设置关键属性，command的事务需要是统一的，要不不受transaction控制
da.InsertCommand.Transaction = cmd.Transaction;
da.DeleteCommand.Transaction = cmd.Transaction;
da.UpdateCommand.Transaction = cmd.Transaction;
//读取数据
da.Fill(data);
//完成
result = true;
}
catch (Exception x)
{ //捕获错误
if (!lConn) conn.Close();
Gs_DataFunction.globalErrorString = "[getDataAdapter]生成数据和数据适配器出错！\r\n" + x.Message;
throw new Exception(Gs_DataFunction.globalErrorString);
}
return result; //完成
}
/// <summary>
/// 生成可用于update的adapter，修改data的数据后，调用adapter.update(data)即可更新数据，等效于delphi的dataSet。
/// 辅助处理有： Gs_DataFunction.initializeRecord初始化数据，Gs_Class.getDefaultValueOfType
/// </summary>
/// <param name="commandPar">用来取参数包括connection和transaction的命令，执行后其commandText会变成读取数据的脚本</param>
/// <param name="sSelectScript">读取数据的脚本</param>
/// <param name="da">返回的OleDbDataAdapter数据适配器，传入null用commandPar生成，否则将其SelectCommand设置成commandPar</param>
/// <param name="data">返回DataTable，处理完后，可以用OleDbDataAdapter.Update(DataTable)</param>
/// <returns>成功与否</returns>
public static bool getDataAdapter(OleDbCommand commandPar, string sSelectScript, ref OleDbDataAdapter da, ref DataTable data)
{ //用table返回
DataSet ds = null;
bool result = getDataAdapter(commandPar, sSelectScript, ref da, ref ds);
if (result) data = ds.Tables[0];
return result;
}
/// <summary>
/// 生成可用于update的adapter，修改data的数据后，调用adapter.update(data)即可更新数据，等效于delphi的dataSet。
/// 辅助处理有： Gs_DataFunction.initializeRecord初始化数据，Gs_Class.getDefaultValueOfType
/// </summary>
/// <param name="commandPar">用来取参数包括connection和transaction的命令，执行后其commandText会变成读取数据的脚本</param>
/// <param name="sSelectScript">读取数据的脚本</param>
/// <param name="da">返回的OleDbDataAdapter数据适配器，传入null用commandPar生成，否则将其SelectCommand设置成commandPar</param>
/// <param name="data">返回DataRow，有记录则直接取，无则添加后初始化</param>
/// <returns>成功与否，处理完后，可以用OleDbDataAdapter.Update(DataRow)</returns>
public static bool getDataAdapter(OleDbCommand commandPar, string sSelectScript, ref OleDbDataAdapter da, ref DataTable dt, ref DataRow data)
{
bool result = getDataAdapter(commandPar, sSelectScript, ref da, ref dt);
if (!result) throw new Exception("生成出错！");
//有则直接取，无则添加后初始化
if (dt.Rows.Count > 0) data = dt.Rows[0]; else { data = dt.NewRow(); initializeRecord(data); dt.Rows.Add(data); }
return true;
}
#endregion

#region //设置字段的默认值，setFieldsDefaultValue
/// <summary>
/// 设置字段的默认值
/// </summary>
/// <param name="db">要设置的数据表</param>
/// <returns>成功与否</returns>
public static bool setFieldsDefaultValue(DataTable db)
{
bool result = false;
DataTable dt = db;
DataColumn col = null;
object oValue = null;
int i = 0;
for (i = 0; i < dt.Columns.Count; i++)
{ //遍历所有列
col = dt.Columns[i];
if (col.DefaultValue != null || col.AutoIncrement || col.ReadOnly) continue; //不用设置：已经有了、自动增长、只读
col.DefaultValue = Gs_Class.getDefaultValueOfType(col.DataType);
}
//
return true;
}
#endregion

#region //initializeRecord：初始化数据
/// <summary>
/// 初始化数据，此处为设置DataTable里DataColumn.DefaultValue
/// </summary>
/// <param name="db">要设置的数据表</param>
/// <returns>返回成功与否</returns>
public static bool initializeRecord(DataTable db)
{ //设置默认值
return setFieldsDefaultValue(db);
}
/// <summary>
/// 初始化数据，此处为设置DataRow的值
/// </summary>
/// <param name="db">数据DataRow</param>
/// <returns>成功与否</returns>
public static bool initializeRecord(DataRow db)
{ //设置默认值
bool result = false;
DataTable dt = db.Table;
DataColumn col = null;
object oValue = null;
int i = 0;
for (i = 0; i < dt.Columns.Count; i++)
{ //遍历所有列
col = dt.Columns[i];
if (col.AutoIncrement || col.ReadOnly) continue; //不用设置：已经有了、自动增长、只读
db[i] = Gs_Class.getDefaultValueOfType(col.DataType);
}
//
return true;
}
#endregion

#region//执行SQL脚本，DoSQL，无返回，无事务 DoSQL(OleDbConnection connection, string sScriptNonReturn)
/// <summary>
/// 执行SQL脚本。注意：可以用lock锁定线程防止多人操作。 #if checked unchecked fixed lock System.Diagnostics.Process.Start("ipconfig /all");
/// </summary>
public static int DoSQL(OleDbConnection connection, string sScriptNonReturn)
{
int result = -1;
//
OleDbConnection conn = connection;
if (conn == null)
conn = new OleDbConnection(ConfigurationManager.AppSettings["ConnectionString"]); //_Default.sConnectionString
OleDbCommand cmd = new OleDbCommand(sScriptNonReturn, conn);
ConnectionState cs = conn.State; //状态
try
{
if (conn.State == ConnectionState.Closed) conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (Exception x)
{
globalErrorString = "[DoSQL]执行SQL脚本出错！\n " + x.Message + "\n " + sScriptNonReturn;
throw new Exception(globalErrorString);
}
finally
{ if (connection == null) conn.Close(); }
//
return result;
}
#endregion

#region//执行SQL脚本，DoSQL，传来统一事务 DoSQL(string sScript, OleDbTransaction trans)
public static int DoSQL(string sScript, OleDbTransaction trans)
{
int result = -1;
OleDbConnection conn = null;
OleDbCommand cmd = null;
if (trans == null) conn = new OleDbConnection(ConfigurationManager.AppSettings["ConnectionString"]); else conn = trans.Connection;
cmd = new OleDbCommand(sScript, conn);
if (trans != null) cmd.Transaction = trans;
//
try
{
if (trans == null) conn.Open(); //无传入，自己打开
cmd.CommandText = sScript;
result = cmd.ExecuteNonQuery();
}
finally
{
if (trans == null) conn.Close(); //无传入，自己关闭
}
//
return result;
}
#endregion

#region//执行SQL脚本，DoSQL，返回受影响的行数。command传送来后可以启动外部统一事务 DoSQL(string sScript, OleDbCommand command)
/// <summary>
/// 执行SQL脚本，返回受影响的行数。command可以带事务，也可以通过lTransaction参数请求启动事务。
/// </summary>
public static int DoSQL(string sScript, OleDbCommand command)
{
return DoSQL(sScript, command, false);
}
/// <summary>
/// 执行SQL脚本，DoSQL，返回受影响的行数。command可以带事务，也可以通过lTransaction参数请求启动事务
/// </summary>
/// <param name="sScript">要执行的脚本，无返回值，执行后返回受此脚本影响的行数</param>
/// <param name="command">通过此command执行，如果是null则内部创建</param>
/// <param name="lTransaction">是否启动事务，如果command带事务，则忽略此参数</param>
/// <returns>返回受脚本影响的行数</returns>
public static int DoSQL(string sScript, OleDbCommand command, bool lTransaction)
{
if (sScript == "") return 0;
int result = -1;
//容错处理，若无参数，则创建
OleDbCommand cmd = command;
OleDbConnection conn = null;
getDataCommand(cmd, ref conn, ref cmd);
OleDbTransaction trans = cmd.Transaction;
//保存原状态
bool lConn = conn.State == ConnectionState.Open, lTrans = trans != null;
CommandType ct = cmd.CommandType;
//
try
{
if (!lConn) conn.Open();
//事务，当要求自动启动事务，且事务没有启动时
if (lTransaction && !lTrans) { trans = conn.BeginTransaction(); cmd.Transaction = trans; }
//
cmd.CommandType = CommandType.Text;
cmd.CommandText = sScript;
result = cmd.ExecuteNonQuery();
//完成，事务
if (lTransaction && !lTrans) trans.Commit(); //要求事务，且内部启动的事务
}
catch (Exception x)
{
if (lTrans && !lTrans && trans != null) trans.Rollback(); //要求事务，且是内部启动了事务
globalErrorString = "[DoSQL]执行脚本出错！" + (char)13 + x.Message + (char)13 + sScript;
throw new Exception(globalErrorString);
}
finally
{ //复原其状态
if (!lConn) conn.Close();
if ((command != null) && (cmd.CommandType != ct)) cmd.CommandType = ct;
}
return result;
}
#endregion

#region//获取SQl返回值，getSqlResult，获取返回的一个字段值，可以取默认值
/// <summary>
/// 获取SQl返回值，sql脚本必须包含select @XXX as nResult结尾，内部用DataReader读取第一个数字字段。
/// 注意：可以用lock锁定线程防止多人操作。 #if checked unchecked fixed lock System.Diagnostics.Process.Start("ipconfig /all");
/// </summary>
/// <param name="sql">sql脚本必须包含select @XXX as nResult结尾，内部用DataReader读取第一个数字字段</param>
/// <param name="lTransaction">是否启动事务</param>
/// <returns></returns>
public static object getSqlResult(string sql, OleDbCommand command, bool lTransaction)
{ return Gs_DataFunction.getSqlResult(sql, null, command, lTransaction); }
public static object getSqlResult(string sScript, object defaultValue, OleDbConnection connection)
{ return getSqlResult(sScript, defaultValue, connection, false); }
public static object getSqlResult(string sScript, object defaultValue, OleDbConnection connection, bool lTransaction)
{ //取返回值
OleDbConnection conn = connection;
if (conn == null) conn = new OleDbConnection(ConfigurationManager.AppSettings["ConnectionString"]);
OleDbCommand cmd = new OleDbCommand("", conn);
//
return getSqlResult(sScript, defaultValue, cmd, lTransaction);
}
//////////
public static object getSqlResult(string sScript, object defaultValue, OleDbCommand command)
{ return getSqlResult(sScript, defaultValue, "", command); }
public static object getSqlResult(string sScript, object defaultValue, string sField, OleDbCommand command)
{ return getSqlResult(sScript, defaultValue, sField, command, false); }
/// <summary>
/// 获取SQl返回值，getSqlResult，获取返回的一个字段值，可以取默认值。sql脚本必须包含select @XXX as YYY结尾，内部用DataReader读取第一个数字字段
/// </summary>
/// <param name="sScript">要执行的脚本，必须包含select @XXX as YYY结尾，内部用DataReader读取第一个数字字段</param>
/// <param name="defaultValue">默认值</param>
/// <param name="sField">要取的字段名</param>
/// <param name="command">使用的command执行，如果是null则内部创建</param>
/// <param name="lTransaction">是否启动事务，如果command带了事务则忽略此参数</param>
/// <returns>返回脚本读取的值</returns>
public static object getSqlResult(string sScript, object defaultValue, string sField, OleDbCommand command, bool lTransaction)
{ //取返回值
object result = defaultValue;
//
OleDbCommand cmd = command;
OleDbConnection conn = null;
getDataCommand(cmd, ref conn, ref cmd);
DataTable dt = null;
DataRow row = null;
bool lConn = conn.State == ConnectionState.Open;
//
try
{
if (!lConn) conn.Open();
//执行读取
if (getSqlResult(sScript, ref dt, cmd, lTransaction) && dt.Rows.Count > 0) row = dt.Rows[0]; //读取出来
if (row != null) { if (string.IsNullOrEmpty(sField)) result = row[0]; else result = row[sField]; }
//完成
if (result == null) result = defaultValue;
}
catch (Exception x)
{ throw new Exception("[getSqlResult-Object]获取SQL返回值出错！\r\n" + x.Message+" \r\n"+sScript); }
finally
{ if (!lConn) conn.Close(); dt = null; }
//返回值
return result;
}
public static object getSqlResult(string sScript, object defaultValue, OleDbCommand command, bool lTransaction)
{ return getSqlResult(sScript, defaultValue, "", command, false); }
public static double getSqlResultFloat(string sScript, double defaultValue, OleDbCommand command)
{ return gsClass.varToFloatDef(getSqlResult(sScript, defaultValue, command), defaultValue); }
#endregion

#region//获取SQl返回值，getSqlResult，返回DataTable，可以通过command带事务或通过lTransaction参数请求启动事务
/// <summary>
/// 获取SQl返回值，返回DataTable，可以通过command带事务或通过lTransaction参数请求启动事务。
/// </summary>
public static bool getSqlResult(string sScript, ref DataTable table, OleDbCommand command)
{ return getSqlResult(sScript, ref table, command, false); }
/// <summary>
/// 获取SQl返回值，返回值放入dataTable，可以内部启动事务
/// </summary>
/// <param name="sScript">要执行的脚本，包含返回table</param>
/// <param name="table">返回值存入此table</param>
/// <param name="command">用此命令控件执行，如果是null则内部创建。</param>
/// <param name="lTransaction">是否启动事务，如果command带事务则忽略参数</param>
/// <returns>成功与否</returns>
public static bool getSqlResult(string sScript, ref DataTable table, OleDbCommand command, bool lTransaction)
{ //返回记录集
bool result = false, vR = false;
if (table == null) table = new DataTable();
//
OleDbCommand cmd = command;
OleDbConnection conn = null;
getDataCommand(cmd, ref conn, ref cmd);
OleDbTransaction trans = cmd.Transaction;
OleDbDataReader dr = null;
bool lConn = conn.State == ConnectionState.Open, lTrans = trans != null;
//
try
{
if (!lConn) conn.Open();
if (lTransaction && !lTrans) { trans = conn.BeginTransaction(); cmd.Transaction = trans; } //启动事务
//读取数据
if (!getSqlResult(sScript, ref dr, cmd)) throw new Exception("读取数据出错！");
//把数据读入到DataTable中：读取字段列表
vR = dataReader2DataTable(dr, ref table);
//完成
if (lTransaction && !lTrans) trans.Commit(); //要求启动事务，且内部事务
result = vR;
}
catch (Exception x)
{
if (lTransaction && !lTrans && trans != null) trans.Rollback(); //内部事务，撤消之
throw new Exception("[getSqlResult-DataTable]获取DataTable出错！\r\n" + x.Message);
}
finally
{ if (!lConn) conn.Close(); if (dr != null)dr.Close(); }
return result;
}
/// <summary>
/// 获取SQl返回值，返回DataRow，可以通过command带事务或通过lTransaction参数请求启动事务。
/// </summary>
/// <param name="sScript"></param>
/// <param name="db"></param>
/// <param name="command"></param>
/// <returns></returns>
public static bool getSqlResult(string sScript, ref DataRow db, OleDbCommand command)
{ return getSqlResult(sScript, ref db, command, false); }
/// <summary>
/// 获取SQl返回值，返回值放入DataRow，可以内部启动事务
/// </summary>
/// <param name="sScript">要执行的脚本，包含返回table</param>
/// <param name="table">返回值存入此DataRow</param>
/// <param name="command">用此命令控件执行，如果是null则内部创建。</param>
/// <param name="lTransaction">是否启动事务，如果command带事务则忽略参数</param>
/// <returns>成功与否</returns>
public static bool getSqlResult(string sScript, ref DataRow db, OleDbCommand command, bool lTransaction)
{
DataTable dt = null;
bool result = getSqlResult(sScript, ref dt, command, lTransaction);
if (result && dt != null && dt.Rows.Count > 0)
{
db = dt.Rows[0];
result = true;
}
return result; //完成
}
#endregion

#region//获取SQl返回值，getSqlResult，返回DataReader。警告：为了让DataReader可以在函数外边继续Read，将不会关闭Connection。
/// <summary>
/// 获取SQl返回值，返回DataTable。警告：为了让DataReader可以在函数外边继续Read，将不会关闭Connection
/// </summary>
public static bool getSqlResult(string sScript, ref OleDbDataReader dr, OleDbConnection connection)
{ //返回记录集
OleDbConnection conn = connection;
if (conn == null) conn = new OleDbConnection(ConfigurationManager.AppSettings["ConnectionString"]);
OleDbCommand cmd = new OleDbCommand(sScript, conn);
return getSqlResult(sScript, ref dr, cmd);
}
/// <summary>
/// 获取SQl返回值，返回DataTable。
/// 警告：为了让DataReader可以在函数外边继续Read，将不会关闭Connection。
/// 可通过command本身带事务，command的事务提交或撤消后，其DataReader读取数据会出错！！！！！所以此模块不支持lTransaction事务请求
/// </summary>
/// <param name="sScript">脚本，可用dataReader读取</param>
/// <param name="dr">返回dataReader</param>
/// <param name="command">使用此command执行，如果是null则内部创建</param>
/// <returns>成功与否</returns>
public static bool getSqlResult(string sScript, ref OleDbDataReader dr, OleDbCommand command)
{ //返回记录集
if (sScript == "") return false;
bool result = false, lTransaction = false;
//容错处理，若无参数，则创建
OleDbCommand cmd = command;
OleDbConnection conn = null;
getDataCommand(cmd, ref conn, ref cmd);
OleDbTransaction trans = cmd.Transaction;
//保存原状态
bool lConn = conn.State == ConnectionState.Open, lTrans = trans != null;
CommandType ct = cmd.CommandType;
//
try
{
if (!lConn) conn.Open();
if (lTransaction && !lTrans) { trans = conn.BeginTransaction(); cmd.Transaction = trans; }
//执行
cmd.CommandType = CommandType.Text;
cmd.CommandText = sScript;
dr = cmd.ExecuteReader();
//完成返回
if (lTransaction && !lTrans)
{
trans.Commit(); //要求启动事务，且事务是从内部启动的，则提交
cmd.Transaction = null;
}
result = true;
}
catch (Exception x)
{
if (lTransaction && !lTrans && trans != null) trans.Rollback(); //内部启动的事务，撤消
globalErrorString = "[getSqlResult-DataReader]获取SQl返回值出错！\r\n" + x.Message;
throw new Exception(globalErrorString);
}
finally
{ //复原其状态
if ((command != null) && (cmd.CommandType != ct)) cmd.CommandType = ct;
//if (!lConn) conn.Close(); //关闭之后，DataReader将无法读取，不知DataReader是不是会被释放？
}
//完成，返回
return result;
}
#endregion

#region//获取SQl返回值：数据集，getSqlResultDataSet，返回DataSet或DataReader
/// <summary>
/// 获取SQl返回值：数据集，返回OleDbDataReader。注意：可以用lock锁定线程防止多人操作。 #if checked unchecked fixed lock System.Diagnostics.Process.Start("ipconfig /all");
/// </summary>
public static OleDbDataReader getSqlResultDataReader(OleDbCommand command, string sqlScript)
{
OleDbDataReader result = null;
if (!getSqlResult(sqlScript, ref result, command))
throw new Exception("[getSqlResultDataSet-DataReader]获取SQl返回值出错！" + globalErrorString);
return result;
}
public static DataSet getSqlResultDataSet(OleDbCommand command, string sqlScript)
{ //获取sql结果
if (sqlScript == "") return null;
//
OleDbCommand cmd = command;
if (cmd == null) cmd = getNewCommand();
OleDbTransaction trans = cmd.Transaction;
DataSet result = new DataSet();
OleDbDataAdapter da = null;
try
{
cmd.CommandText = sqlScript;
cmd.CommandType = CommandType.Text;
da = new OleDbDataAdapter(cmd);
//
da.Fill(result);
}
catch (Exception x)
{
globalErrorString = "[GetSqlResultDataSet-DataSet-command-1]获取SQl返回值出错！\r\n" + x.Message;
throw new Exception(globalErrorString);
result = null;
}
return result;
}
/// <summary>
/// 获取SQl返回值：数据集，返回DataSet。通过connection访问数据，用DataAdapter读取数据，填充到DataSet中
/// </summary>
/// <param name="connection">使用此连接</param>
/// <param name="sqlScript"></param>
/// <returns></returns>
public static DataSet getSqlResultDataSet(OleDbConnection connection, string sqlScript)
{ //获取sql结果
if (sqlScript == "") return null;
//
OleDbConnection conn = connection;
if (conn == null) conn = new OleDbConnection(ConfigurationManager.AppSettings["ConnectionString"]);
OleDbTransaction trans = null;
DataSet result = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(sqlScript, conn);
try
{
da.SelectCommand.Transaction = trans;
da.Fill(result);
}
catch (Exception x)
{
globalErrorString = "[GetSqlResultDataSet-DataSet-connection-2]获取SQl返回值出错！\r\n" + x.Message;
throw new Exception(globalErrorString);
result = null;
}
return result;
}
#endregion

#region//将DataReader的数据全部读入dataTable，dataReader2DataTabl
/// <summary>
/// 将DataReader的数据全部读入dataTable。注意：可以用lock锁定线程防止多人操作。 #if checked unchecked fixed lock System.Diagnostics.Process.Start("ipconfig /all");
/// </summary>
public static bool dataReader2DataTable(DbDataReader reader, ref DataTable table)
{ return dataReader2DataTable(reader, ref table, true); }
public static bool dataReader2DataTable(DbDataReader reader, ref DataTable table, bool lGatherData)
{
bool result = false;
int i = 0;
//
DbDataReader dr = reader;
DataRow row = null;
try
{
if (table == null) table = new DataTable();
//把数据读入到DataTable中：读取字段列表
table.Rows.Clear();
table.Columns.Clear();
for (i = 0; i < dr.FieldCount; i++)
table.Columns.Add(new DataColumn(dr.GetName(i), dr.GetFieldType(i)));
//把数据读入到DataTable中：读取字段值
if (lGatherData)
while (dr.Read())
{
row = table.NewRow();
for (i = 0; i < dr.FieldCount; i++) row[i] = dr[i];
table.Rows.Add(row);
}
//读取完毕
result = true;
}
catch (Exception x)
{ throw new Exception("[dataReader2DataTable]将OleDbDataReader值存入DataTable出错！" + x.Message); }
//
return result;
}
#endregion

#region //复制数据 copyDB
/// <summary>
/// 复制数据。
/// </summary>
/// <param name="dbFrom">数据来源</param>
/// <param name="dbTo">复制到哪里去</param>
/// <param name="lCheckColumn">是否检查有没有Column字段</param>
/// <returns>返回成功与否</returns>
public static bool copyDB(DataRow dbFrom, DataRow dbTo, bool lCheckColumn)
{ //复制
bool result = false, lHas = false;
DataColumnCollection colsFrom = null, colsTo = null;
try
{
//if (dbTo == null) { dbTo = dbTo.Table.NewRow(); dbTo.Table.Rows.Add(dbTo); }
//获取行的列清单
colsFrom = dbFrom.Table.Columns;
colsTo = dbTo.Table.Columns;
foreach (DataColumn colFrom in colsFrom)
{
lHas = !lCheckColumn;
if (lCheckColumn) foreach (DataColumn colTo in colsTo)
{ //循环检查目标有没有
lHas = (colTo.ColumnName.ToUpper() == colFrom.ColumnName.ToUpper());
if (lHas) break;
}
if (!lHas) continue; //忽略没有的字段
dbTo[colFrom.ColumnName] = dbFrom[colFrom.ColumnName]; //复制过去
}
//完成
result = true;
}
catch (Exception x)
{ throw new Exception("[copyDB]复制数据出错！\r\n" + x.Message); }
//返回
return result;
}
#endregion

#region //取传入数据对象的值，getFieldValue。数据对象可以是：DataReader、DataTable、DataRow、DataRowView
/// <summary>
/// 取传入对象的值，对象可以是：DataReader、DataTable、DataRow、DataRowView，会自动判断处理
/// </summary>
/// <param name="dbSource">数据源，类型可以是DataReader、DataTable、DataRow、DataRowView</param>
/// <param name="sField">字段</param>
/// <param name="oDefault">默认值</param>
/// <returns></returns>
public static object getFieldValue(object dbSource, string sField, object oDefault)
{ return getFieldValue(dbSource, sField, oDefault, false); }
public static object getFieldValue(object dbSource, string sField, object oDefault, bool lCheckFieldExist)
{ //取传入对象的值
object result = oDefault;
if (dbSource == null || string.IsNullOrEmpty(sField)) return oDefault;
//
object db = dbSource;
OleDbDataReader dr = null;
DataTable table = null;
DataRow row = null;
DataRowView drv = null;
//判断处理
try
{
if (db is DataTable)
{ //是dataTable，取第一行
table = db as DataTable;
if (table.Rows.Count > 0) row = table.Rows[0];
}
else if (db is DataRow)
{ //dataRow
row = db as DataRow;
}
else if (db is OleDbDataReader)
{ //是dataReader
dr = db as OleDbDataReader;
if (!dr.HasRows) return oDefault;
//dataReader2DataTable(dr, ref table);
//if (table.Columns.IndexOf(sField) == null) return oDefault;
return Gs_Class.tryNullTo(dr[sField], oDefault);
}
else if (db is DataRowView)
{ //DataRowView drv
drv = db as DataRowView;
return Gs_Class.tryNullTo(drv[sField], oDefault);
}

//取row值，取不到字段值不报错
if (row != null) return Gs_Class.tryNullTo(row[sField], oDefault);
}
catch (Exception x)
{
if (!lCheckFieldExist) throw; //不用检查字段是否存在，则报错
}
//
return result;
}
public static double getFieldValueNumber(object dbSource, string sField, double oDefault)
{
object o = getFieldValue(dbSource, sField, oDefault);
if (o == null || o is DBNull) return oDefault; else return Convert.ToDouble(o);
}
public static DateTime getFieldValueDateTime(object dbSource, string sField, DateTime oDefault)
{
object o = getFieldValue(dbSource, sField, oDefault);
if (o == null || o is DBNull) return oDefault; else return Convert.ToDateTime(o);
}
public static bool getFieldValueBool(object dbSource, string sField, bool oDefault)
{
object o = getFieldValue(dbSource, sField, oDefault);
if (o == null || o is DBNull) return oDefault; else return Convert.ToBoolean(o);
}
#endregion

#region //关联查询条件 linkFilter
/// <summary>
/// 关联查询条件
/// </summary>
/// <param name="sBaseFilter">原来的查询条件</param>
/// <param name="sFilterToAdd">要加到原来条件中的新条件</param>
/// <returns></returns>
public static bool linkFilter(ref string sBaseFilter, string sFilterToAdd)
{
if (string.IsNullOrEmpty(sFilterToAdd)) return true;
sBaseFilter += (string.IsNullOrEmpty(sBaseFilter) ? "" : " And ") + sFilterToAdd;
return true;
}
#endregion

#region//加密解密字符串，encryptString/decryptString
//加密字符串
public static string encryptString(string str)
{
return encryptString(str, nXor);
}
public static string encryptString(string str, int nXorParam)
{
return Gs_Class.encryptXor(str, nXorParam);
}
//解密字符串
public static string decryptString(string str)
{
return decryptString(str, nXor); //异或可逆
}
public static string decryptString(string str, int nXorParam)
{
return encryptString(str, nXorParam); //异或可逆
}
#endregion

#region//获取Request的值 getRequestValue
/// <summary>
/// 获取Request值，Request有明的和暗的Request.Form两种，此处首先尝试明的，再尝试暗的，都找不到则返回默认值""。
/// </summary>
/// <param name="sRequest">Request的名称</param>
/// <returns>返回Request值，为Null则返回默认值""</returns>
public static string getRequestValue(string sRequest)
{ //获取Request的值
return getRequestValue(sRequest, "");
}
/// <summary>
/// 获取Request值，Request有明的和暗的Request.Form两种，此处首先尝试明的，再尝试暗的，都找不到则返回默认值""。
/// </summary>
/// <param name="sRequest">Request的名称</param>
/// <param name="sDefault">默认值</param>
/// <returns>返回Request值，为Null则返回默认值""</returns>
public static string getRequestValue(string sRequest, string sDefault)
{ //获取Request的值
return getRequestValue(null, sRequest, sDefault);
}
/// <summary>
/// 获取Request值，Request有明的和暗的Request.Form两种，此处首先尝试明的，再尝试暗的，都找不到则返回默认值""。
/// </summary>
/// <param name="Request">HttpRequest，为null则默认为取HttpContext.Current.Request</param>
/// <param name="sRequest">参数名</param>
/// <param name="sDefault">默认值</param>
/// <returns>返回Request值，为Null则返回默认值""</returns>
public static string getRequestValue(HttpRequest Request, string sRequest, string sDefault)
{ //获取Request的值
if (Request == null) Request = HttpContext.Current.Request;
string result = Request[sRequest]; //明Request
if (string.IsNullOrEmpty(result)) result = Request.Form[sRequest]; //暗Request
if (string.IsNullOrEmpty(result)) result = sDefault;
if (string.IsNullOrEmpty(result)) result = "";
//转成中文处理，toChina
//byte[] buffer = Encoding.UTF8.GetBytes(result);
//result= Encoding.GetEncoding("utf-8").GetString(buffer);
//
return result;
}
/// <summary>
/// 获取Request值，包括明的和暗的，可以传入多个Request名字，比如参数命名不规范，有时写sShop=001,有时写Shop=001，用法：
/// sShop = Gs_DataFunction.getRequestValue(new string[] { "Shop", "sShop", "myShop" }, "")
/// </summary>
public static string getRequestValue(string[] requestArray, string sDefault) { return getRequestValueFirst(sDefault, requestArray); }
public static string getRequestValue(string[] requestArray) { return getRequestValue(requestArray, ""); }
/// <summary>
/// 获取Request值，包括明的和暗的，可以传入多个Request名字，比如参数命名不规范，有时写sShop=001,有时写Shop=001
/// 注意：params参数是活的，写几个都行，不写也行
/// </summary>
/// <param name="requestArray">可以传入多个Request名字，比如参数命名不规范，有时写sShop=001，有时写Shop=001</param>
/// <param name="sDefault">默认值</param>
/// <returns>返回获取到的值，取不到则返回默认值Default</returns>
public static string getRequestValueFirst(string sDefault, params string[] requestArray) { return getRequestValueFirst(null, sDefault, requestArray); }
public static string getRequestValueFirst(HttpRequest Request, string sDefault, params string[] requestArray)
{
string result = sDefault;
foreach (string sR in requestArray)
{
string s = getRequestValue(Request, sR, "");
if (!string.IsNullOrEmpty(s)) { result = s; break; } //找到了一个值
}
return result;
}
#endregion

#region //获取session值，GetSession，如果不存在 跳转到登录页重新登录
/// <summary> 获取session值 </summary>
/// <param name="key">Session的名称</param>
/// <returns>返回值</returns>
public static object GetSession(string key, string culture)
{
if (zhCN_Direct == "" && default_Direct != "") zhCN_Direct = default_Direct;
if (enGB_Direct == "" && default_Direct != "") enGB_Direct = default_Direct;
//
System.Web.SessionState.HttpSessionState Session = HttpContext.Current.Session;
object result = null;
result = Session[key];
//if (result==null && DebugMode) result = "Debug";
if (result == null && (!DebugMode))
{
// FormsAuthentication.SignOut();
if (string.IsNullOrEmpty(culture))
HttpContext.Current.Response.Redirect(default_Direct);
else if (culture.Contains("中文"))
HttpContext.Current.Response.Redirect(zhCN_Direct);
else
HttpContext.Current.Response.Redirect(enGB_Direct);
}
//
return result;
}

public static object GetSession(string key)
{
return GetSession(key, "");
}
public static string getSessionStr(string sKey) { return getSessionStr(sKey, ""); }
public static string getSessionStr(string sKey, string sDefault) { return Gs_Class.tryNullToString(HttpContext.Current.Session[sKey], sDefault).ToString(); }
public static string getUserID() { return getUserID("sUserID", ""); }
public static string getUserID(string sSessionKey, string culture)
{ //取用户号
object result = GetSession(sSessionKey, culture);
if (result == null) result = "";
return result.ToString();
}
#endregion

#region //读取系统设置 readSysConfig
/// <summary>
/// 读取系统设置，表GsConfig
/// </summary>
/// <param name="db">数据表</param>
/// <param name="sConfigName">配置名，对应sName字段</param>
/// <param name="sValueType">值类型，有S、N、D、O、L五种类型</param>
/// <param name="oDefault">返回值的默认值</param>
/// <returns>返回值，是sValue、nValue、dValue、lValue、oValue之一</returns>
public static object readSysConfig(DataTable db, string sConfigName, string sValueType, object oDefault)
{
object result = oDefault;
if (string.IsNullOrEmpty(sValueType)) sValueType = "";
//查找
foreach (DataRow dr in db.Rows) if (dr["sName"].ToString().ToUpper() == sConfigName.ToUpper())
{
result = getFieldValue(dr, sValueType + "Value", oDefault);
break;
}
//完成
return result;
}
/// <summary>
/// 读取系统设置，表GsConfig
/// </summary>
/// <param name="cmd">ole命令组件，可以携带事务</param>
/// <param name="sConfigName">配置名，对应sName自动</param>
/// <param name="sValueType">值类型，有S、N、D、O、L五种类型</param>
/// <param name="oDefault">默认值</param>
/// <returns>返回值，是sValue、nValue、dValue、lValue、oValue之一</returns>
public static object readSysConfig(OleDbCommand cmd, string sConfigName, string sValueType, object oDefault)
{
object result = oDefault;
if (string.IsNullOrEmpty(sValueType)) sValueType = "S";
result = Gs_DataFunction.getSqlResult("select " + sValueType + "Value from gsConfig where sName='" + Gs_Class.removeSQLAttachStr(sConfigName) + "' ", oDefault, cmd);
if (result == null) return oDefault; else return result;
}
#endregion

#region //写入系统配置 writeSysConfig
/// <summary>
/// 写入系统配置
/// </summary>
/// <param name="db">数据表</param>
/// <param name="sConfigName">配置名，对应sName字段</param>
/// <param name="oValue">值，是sValue、nValue、dValue、lValue、oValue字段之一</param>
/// <param name="sValueType">值类型，对应sType字段，有S、N、D、O、L五种类型</param>
/// <returns>返回成功与否</returns>
public static bool writeSysConfig(DataTable db, string sConfigName, object oValue, string sValueType)
{
bool result = false;
DataRow dr = null;
if (string.IsNullOrEmpty(sValueType)) sValueType = "s";
//查找有无此配置
foreach (DataRow r in db.Rows) if (r["sName"].ToString().ToUpper() == sConfigName.ToUpper())
{ //查找到了
dr = r;
break;
}
//找不到
if (dr == null)
{
dr = db.NewRow(); //加一个行
initializeRecord(dr); //初始化新增加的行
db.Rows.Add(dr);
dr["sName"] = sConfigName;
setDbValue(dr, "lEnable", true);
setDbValue(dr, "dCreate", DateTime.Now);
}
//写入
dr["sType"] = sValueType;
dr[sValueType + "Value"] = oValue;
//完成
return result;
}
/// <summary>
/// 写入系统配置
/// </summary>
/// <param name="cmd">ole命令组件</param>
/// <param name="sConfigName">配置名，对应sName字段</param>
/// <param name="oValue">值，是sValue、nValue、dValue、lValue、oValue字段之一</param>
/// <param name="sValueType">值类型，对应sType字段，有S、N、D、O、L五种类型</param>
/// <returns>返回成功与否</returns>
public static bool writeSysConfig(OleDbCommand cmd, string sConfigName, object oValue, string sValueType)
{
bool result = false;
//准备
OleDbDataAdapter da = null;
DataTable db = null;
string sql = "select * from gsConfig where sName='" + Gs_Class.removeSQLAttachStr(sConfigName) + "' ";
Gs_DataFunction.getDataAdapter(cmd, sql, ref da, ref db);
//写入
result = writeSysConfig(db, sConfigName, oValue, sValueType);
da.Update(db);
//完成
return result;
}
#endregion

#region//获取语言标志 getLanguageSign
public static string getLanguageSign(string Culture)
{
if (Culture.Contains("中文")) return ""; else return "En";
}
#endregion

#region //CShare的Asc函数。高级AscAdv
/// <summary>
/// C#的Asc函数。高级AscAdv
/// </summary>
/// <param name="s">字符Char</param>
/// <returns>其Asc值</returns>
public static short AscAdv(char s)
{
byte[] bytes = System.Text.Encoding.GetEncoding("gb2312").GetBytes(s.ToString());
if (bytes.Length == 2)
{
return (short)((bytes[0] << 8) + bytes[1]);
}
else
return bytes[0];
}
#endregion

#region//setDropdownValue 页面上的dropDown，选择之后，检查如果有SelectedID就选上，没有则首先检查大小写，大小写也没有，再送数据库中搜索一遍，找到了加上listItem并选定
/// <summary>
/// 页面上的dropDown，选择之后，检查如果有SelectedID就选上，没有则首先检查大小写，大小写也没有，再送数据库中搜索一遍，找到了加上listItem并选定
/// </summary>
public static bool setDropdownValue(DropDownList cbx, string sIDValue)
{
bool result = false, lEnable = cbx.Enabled;
if (!lEnable) cbx.Enabled = true;
string sID = sIDValue;
int i = 0;
//是空值，直接选择上
if (string.IsNullOrEmpty(sIDValue) || sIDValue.Trim() == "")
{
if (cbx.Items.FindByValue("") == null) cbx.Items.Insert(0, new ListItem("", ""));
cbx.SelectedValue = ""; //直接选择上
result = true;
}
else if (cbx.Items.FindByValue(sID) != null) { cbx.SelectedValue = sID; result = true; }
else if (cbx.Items.FindByValue(sID.ToUpper()) != null) { cbx.SelectedValue = sID.ToUpper(); result = true; }
else if (cbx.Items.FindByValue(sID.ToLower()) != null) { cbx.SelectedValue = sID.ToLower(); result = true; }
else //逐个检查大小写
{
for (i = 0; i < cbx.Items.Count; i++) if (cbx.Items[i].Value.ToString().ToUpper() == sID.ToUpper()) { cbx.SelectedIndex = i; result = true; break; }
}
if (!result) //还是没有
{
sID = sID.ToUpper();
if (sID == "TRUE" || sID == "FALSE")
{ //如果是逻辑型的
if (sID.ToUpper() == "TRUE") sID = "1"; else if (sID.ToUpper() == "FALSE") sID = "0";
if (cbx.Items.FindByValue(sID) != null) { cbx.SelectedValue = sID; result = true; }
}
}
//
if (cbx.Enabled != lEnable) cbx.Enabled = lEnable;
return result;
}
public static bool setDropdownValue(DropDownList cbx, string sIDValue, string sTableToFind, OleDbConnection connection)
{
return setDropdownValue(cbx, sIDValue, sTableToFind, "", "", connection);
}
public static bool setDropdownValue(DropDownList cbx, string sIDValue, string sTableToFind, string sIDField, string sNameField, OleDbConnection connection)
{
OleDbConnection conn = connection;
/*if (conn == null)*/
conn = new OleDbConnection(ConfigurationManager.AppSettings["ConnectionString"]);
OleDbCommand cmd = new OleDbCommand("", conn);
return setDropdownValue(cbx, sIDValue, sTableToFind, sIDField, sNameField, cmd);
}
/// <summary>
/// 页面上的dropDown，选择之后，检查如果有SelectedID就选上，没有则首先检查大小写，大小写也没有，再送数据库中搜索一遍，找到了加上listItem并选定
/// </summary>
public static bool setDropdownValue(DropDownList cbx, string sIDValue, string sTableToFind, string sIDField, string sNameField, OleDbCommand command)
{
if (cbx == null) return false;
bool result = false, lConn = false, lEnable = cbx.Enabled;
//
int i = 0;
string s = Gs_Class.removeSQLAttachStr(sIDValue), sID = sIDField, sName = sNameField, sql = "", sObjName = cbx.ID;
if (string.IsNullOrEmpty(sID)) sID = "sID";
if (string.IsNullOrEmpty(sName)) sName = "sName";
sql = "select " + sID + " as sID, " + sName + " as sName from " + sTableToFind + " where 1=1 And " + sID + "='" + s + "' ";
sID = s;
//先调用大小写检查的
if (setDropdownValue(cbx, sID)) return true;
//
ListItem itm = null;
OleDbCommand cmd = command;
OleDbConnection conn = null;
OleDbDataReader dr = null;
//第一步：检查是否有了，有则直接选上
try
{
if (!lEnable) cbx.Enabled = true; //临时设置为有效
//////////////////从数据库中查找//////////////////
if (string.IsNullOrEmpty(sTableToFind)) goto lbl_iNone; //throw new Exception("找不到编号[" + sIDValue + "]，查找表名非法！中止。");
//
Gs_DataFunction.getDataCommand(cmd, ref conn, ref cmd);
cmd.CommandText = sql;
lConn = conn.State == ConnectionState.Open;
if (!lConn) conn.Open();
//打开数据集
dr = cmd.ExecuteReader();
if (dr.Read())
{ //找到了，则加上此项，并选择上
cbx.Items.Add(new ListItem(dr["sName"].ToString(), sID.ToUpper()));
cbx.SelectedIndex = cbx.Items.Count - 1;
result = true;
goto lbl_iEnd;
}
lbl_iNone:
//throw new Exception("尝试大小写、数据库都找不到编号[" + sID + "]");
itm = new ListItem("【" + sID + "】", sID); //找不到此编号
cbx.Items.Add(itm);
cbx.SelectedValue = sID;
lbl_iEnd:
i++;
}
catch (Exception x)
{
Gs_DataFunction.globalErrorString = "[setDropdownValue]处理" + sObjName + "选取值出错！\r\n" + x.Message;
throw new Exception(Gs_DataFunction.globalErrorString);
}
finally
{
if (cbx.Enabled != lEnable) cbx.Enabled = lEnable;
if (dr != null) dr.Close();
if (!lConn && conn!=null) conn.Close();
}
//完毕，返回值
return result;
}
#endregion

#region//生成一个新的连接 getNewConnection，连接字符串默认为sConnectionString
public static OleDbConnection getNewConnection(string sConfigName)
{ //生成一个新的连接
string sCon = sConfigName;
if (string.IsNullOrEmpty(sCon)) sCon = "ConnectionString";
/*if (string.IsNullOrEmpty(sConnectionString))*/
sConnectionString = Gs_Class.tryNullToString(ConfigurationManager.AppSettings[sCon], "");
return new OleDbConnection(sConnectionString);
}
public static OleDbConnection getNewConnection() { return getNewConnection(""); }
#endregion

#region //生成一个新的数据命令组件，getNewCommand
public static OleDbCommand getNewCommand() { return new OleDbCommand("", getNewConnection()); }
#endregion

}
}

转载于:https://www.cnblogs.com/HaiHong/p/5217486.html