110,538
社区成员
发帖
与我相关
我的任务
分享
//接楼上
/// <summary>
/// 按照给定的记录行索引、字段名称获取单元格的值
/// </summary>
/// <param name="paraDtObj"></param>
/// <param name="paraRowPos"></param>
/// <param name="paraFieldName"></param>
/// <returns></returns>
public static object getItmValue(DataTable paraDtObj, int paraRowPos, string paraFieldName)
{
object rto = null;
if (paraDtObj.Rows.Count > 0)
{
try
{
rto = paraDtObj.Rows[paraRowPos][paraFieldName];
}
catch
{
}
}
return rto;
}
/// <summary>
/// 按照给定的key和key值、字段名称获取满足体检的第一行记录单元格的值
/// </summary>
/// <param name="paraDtObj"></param>
/// <param name="paraKey"></param>
/// <param name="paraID"></param>
/// <param name="paraFieldName"></param>
/// <returns></returns>
public static object getItmValue(DataTable paraDtObj, string paraKey, string paraID, string paraFieldName)
{
object rto = null;
if (paraDtObj.Rows.Count > 0)
{
try
{
DataRow[] drs;
drs = paraDtObj.Select(paraKey + "=" + paraID);
if (drs.Length > 0)
{
rto = drs[0][paraFieldName];
}
}
catch
{
}
}
return rto;
}
/// <summary>
/// 按照给定的行索引和数据集获取行记录到HashTable中
/// </summary>
/// <param name="paraDtObj"></param>
/// <param name="iRow"></param>
/// <returns></returns>
public static Hashtable getItm(DataTable paraDtObj,int iRow)
{
Hashtable rtHT = new Hashtable();
for (int iPos = 0; iPos < paraDtObj.Columns.Count; iPos++)
{
if (paraDtObj.Columns[iPos] != null)
{
object o = paraDtObj.Rows[iRow][iPos];
string tmp = paraDtObj.Columns[iPos].ColumnName;
if (paraDtObj.Columns[iPos].DataType == System.Type.GetType("System.DateTime"))
{
if (o != null)
{
if (o.ToString() != "")
{
DateTime dt = (DateTime)o;
rtHT.Add(tmp, dt.ToString("yyyy-MM-dd hh:mm:ss"));
}
else
{
rtHT.Add(tmp, "");
}
}
else
{
rtHT.Add(tmp,"");
}
}
else //if (paraDtObj.Columns[iPos].DataType == System.Type.GetType("System.String"))
{
rtHT.Add(tmp, o.ToString().Trim());
}
}
}
return rtHT;
}
public static Hashtable getItm(DataTable paraDtObj, string paraKey, string paraID)
{
int iRow = -1;
iRow = getItmIndex(paraDtObj,paraKey,paraID);
Hashtable rtHT = new Hashtable();
for (int iPos = 0; iPos < paraDtObj.Columns.Count; iPos++)
{
if (paraDtObj.Columns[iPos] != null)
{
object o = paraDtObj.Rows[iRow][iPos];
string tmp = paraDtObj.Columns[iPos].ColumnName;
if (paraDtObj.Columns[iPos].DataType == System.Type.GetType("System.DateTime"))
{
if (o != null)
{
if (o.ToString() != "")
{
DateTime dt = (DateTime)o;
rtHT.Add(tmp, dt.ToString("yyyy-MM-dd hh:mm:ss"));
}
else
{
rtHT.Add(tmp, "");
}
}
else
{
rtHT.Add(tmp, "");
}
}
else //if (paraDtObj.Columns[iPos].DataType == System.Type.GetType("System.String"))
{
rtHT.Add(tmp, o.ToString().Trim());
}
}
}
return rtHT;
}
}
///强大的发个数据库连接处理通用类*********************
/// <summary>
/// DB 的说明
/// 该类封装了system.data.sqlceilt下的数据库连接方法
/// </summary>
public class DB : IDisposable
{
#region//字段
/// <summary>
/// 数据库的连接对象
/// </summary>
public System.Data.SqlClient.SqlConnection conn;
#endregion
#region//属性
/// <summary>
/// 数据库类的数据库连接字符串
/// </summary>
public string sConnString;
#endregion
#region//方法
/// <summary>
/// DB类的构造函数
/// </summary>
/// <param name="sConString">数据的连接字符串</param>
public DB(string sConString)
{
this.sConnString = sConString;
if (this.conn == null)
conn = new SqlConnection(this.sConnString);
}
/// <summary>
/// DB类的构造函数
/// </summary>
public DB()
{
System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();
this.sConnString = ((string)(configurationAppSettings.GetValue("ConnectionStringCop", typeof(string))));
if (this.conn == null)
conn = new SqlConnection(this.sConnString);
}
/// <summary>
/// 打开数据库连接
/// </summary>
public void Open()
{
if (this.conn.State != ConnectionState.Open)
this.conn.Open();
}
/// <summary>
/// //关闭数据的连接
/// </summary>
public void Close()
{
if (this.conn.State == ConnectionState.Open)
this.conn.Close();
//this.conn.Dispose() ;
}
/// <summary>
/// 释放资源
/// </summary>
public void Dispose()
{
// 确认连接是否已经关闭
if (conn != null)
{
conn.Dispose();
conn = null;
}
}
#endregion
}
public class DbAccess
{
//SQL区域
static DB CommDB;
public static DB GetDB()
{
if (CommDB == null)
{
CommDB = new DB();
}
return CommDB;
}
public static SqlCommand GetSqlCommand(string sql)
{
GetDB();
CommDB.Open();
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand sc = new SqlCommand(sql, CommDB.conn);
return sc;
}
public static SqlDataReader GetSqlDataReader(string strSqlText)
{
SqlDataReader sqlDReader = null;
GetDB();
CommDB.Open();
SqlCommand sqlCmd = new SqlCommand(strSqlText, CommDB.conn);
sqlDReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
return sqlDReader;
}
/// <summary>
/// 执行数据库操作
/// </summary>
/// <param name="sql">执行的操作语句参数</param>
/// <returns>成功返回为1,否则返回0</returns>
public static int Execute(string sql)
{
// 数据库操作返回成功标志
GetDB();
int ExeFlag = 0;
CommDB.Open();
SqlCommand sc = new SqlCommand(sql, CommDB.conn);
ExeFlag = sc.ExecuteNonQuery();
CommDB.Close();
if (ExeFlag > 0)
{
ExeFlag = 1;
}
else
{
ExeFlag = 0;
}
return ExeFlag;
}
/// <summary>
/// 依据sql字符串得到数据集
/// </summary>
/// <param name="sql">要查询的字符串参数</param>
public static DataSet GetDS(string sql)
{
GetDB();
CommDB.Open();
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand sc = new SqlCommand(sql, CommDB.conn);
da.SelectCommand = sc;
DataSet ds = new DataSet();
da.Fill(ds);
CommDB.Close();
return ds;
}
public static DataTable getTable(string ParaStr, string ParaTableName)
{
DataTable dtObj = new DataTable();
DataSet dsObj = GetDS(ParaStr);
if (ParaTableName.Trim() == "")
{
dtObj = dsObj.Tables[0];
}
else
{
dtObj = dsObj.Tables[ParaTableName];
}
return dtObj;
}
public static DataTable getTableQuick(string ParaStr)
{
SqlDataReader sdrObj;
DataTable dtObj;
sdrObj = null;
try
{
GetDB();
CommDB.Open();
dtObj = new DataTable();
SqlCommand cmd = new SqlCommand(ParaStr, CommDB.conn);
sdrObj = cmd.ExecuteReader();
dtObj = UDS.Components.Tools.ConvertDataReaderToDataTable(sdrObj);
}
catch (System.Exception e)
{
dtObj = null;
CommDB.Close();
}
finally
{
if (sdrObj != null)
{
sdrObj.Close();
}
CommDB.Close();
}
return dtObj;
}
/// <summary>
/// 执行SQL查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行
/// </summary>
/// <param name="ParaStr"></param>
/// <returns></returns>
public static string getFieldValue(string ParaStr)
{
string oRvt;
try
{
GetDB();
CommDB.Open();
SqlCommand cmd = new SqlCommand(ParaStr, CommDB.conn);
oRvt = cmd.ExecuteScalar().ToString().Trim();
CommDB.Close();
}
catch (System.Exception e)
{
oRvt = "";
}
return oRvt;
}
/// <summary>
/// 按照查询SQL获取第一行记录的第一列值
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static string getFieldValueBySql(string sql)
{
if (sql == "")
{
return "";
}
else
{
GetDB();
CommDB.Open();
SqlCommand cmd = new SqlCommand(sql, CommDB.conn);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
string tmp = dr.GetValue(0).ToString();
dr.Close();
CommDB.Close();
return tmp;
}
else
{
dr.Close();
CommDB.Close();
return "";
}
}
}
//缓存区域
/// <summary>
/// 按照给定的key和key值获取记录所在数据集中的Pos
/// </summary>
/// <param name="paraDtObj"></param>
/// <param name="paraKey"></param>
/// <param name="paraID"></param>
/// <returns></returns>
public static int getItmIndex(DataTable paraDtObj, string paraKey, string paraID)
{
int rtv =-1;
int iCount = 0;
if (paraDtObj.Rows.Count > 0)
{
foreach (DataRow dr in paraDtObj.Rows)
{
if (dr[paraKey].ToString().Trim() == paraID)
{
rtv++;
break;
}
iCount++;
}
}
if (rtv != -1)
{
rtv = iCount;
}
return rtv;
}
/// <summary>
/// 按照给定的字段名称获取单行记录单元格的值
/// </summary>
/// <param name="paraDtObj"></param>
/// <param name="paraFieldName"></param>
/// <returns></returns>
public static object getItmValue(DataTable paraDtObj, string paraFieldName)
{
object rto = null;
if (paraDtObj.Rows.Count > 0)
{
try
{
rto = paraDtObj.Rows[0][paraFieldName];
}
catch
{
}
}
return rto;
}