using
System;
using
System.IO ;
using
System.Data;
using
System.Data.OleDb ;
using
System.Web;
using
System.Web.UI;
namespace
data.common
{
///<summary>
/// DataFunc 的摘要说明。
///</summary>
public class DataFunc
{
public string ConnectStr=""; //连接数据库字符串,
public DataFunc()
{
System.Web.UI.Page newpage=new Page();
ConnectStr=newpage.Session["Connection"].ToString();
}
///<summary>
/// 生成数据连接
///</summary>
///<returns>数据连接</returns>
public System.Data.OleDb.OleDbConnection CreateConnection()
{
string connstr=ConnectStr;
OleDbConnection conn = new OleDbConnection(connstr);
return conn;
}
///<summary>
/// 根据sql语句生成数据表
///</summary>
///<param name="sqlstr">sql语句</param>
///<returns>数据表,如果sql语句执行异常,返回null</returns>
public System.Data.DataTable GetDtBySql(string sqlstr)
{
OleDbDataAdapter adapter=new OleDbDataAdapter(sqlstr,CreateConnection());
DataTable dt=new DataTable();
try
{
adapter.Fill(dt);
return dt;
}
catch (Exception e)
{
DBErrorLog("GetDtBySql",sqlstr,e.ToString());
return null;
}
}
///<summary>
/// 根据sql语句,指定其中若干条记录生成数据表
///</summary>
///<param name="sqlstr">sql语句</param>
///<param name="StartRecord">从其开始的从零开始的记录号</param>
///<param name="MaxRecord">要检索的最大记录数</param>
///<returns>数据表,如果sql语句执行异常,返回null</returns>
public System.Data.DataTable GetDtBySql(string sqlstr,int StartRecord,int MaxRecord)
{
OleDbDataAdapter adapter=new OleDbDataAdapter(sqlstr,CreateConnection());
DataSet ds=new DataSet();
try
{
adapter.Fill(ds,StartRecord,MaxRecord,"srcTable");
return ds.Tables["srcTable"];
}
catch (Exception e)
{
DBErrorLog("GetDtBySql",sqlstr,e.ToString());
return null;
}
}
///<summary>
/// 执行SQL语句
///</summary>
///<param name="sqlstr">sql语句</param>
///<returns>是否执行成功</returns>
public bool Execsql(string sqlstr)
{
System.Data.OleDb.OleDbConnection conn=CreateConnection();
conn.Open();
System.Data.OleDb.OleDbTransaction tr= conn.BeginTransaction();
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand(sqlstr);
myCommand.Connection=conn;
myCommand.Transaction=tr;
try
{
myCommand.ExecuteNonQuery();
tr.Commit();
return true;
}
catch (Exception e)
{
DBErrorLog("Execsql",sqlstr,e.ToString());
tr.Rollback();
return false;
}
finally
{
conn.Close();
}
}
///<summary>
/// 通过sql语句获得字段值
///</summary>
///<param name="sqlstr">sql语句</param>
///<returns>返回sql语句查询结果的第一记录的第一个字段内容
/// 如果sql语句执行异常或者没有记录,返回空字符串""
///</returns>
public string GetFieldValue(string sqlstr)
{
string result="";
object obj=null;
OleDbCommand oleDbCommand= new OleDbCommand();
try
{
oleDbCommand.Connection=CreateConnection();
oleDbCommand.Connection.Open();
oleDbCommand.CommandText = sqlstr;
obj=oleDbCommand.ExecuteScalar();
if(obj!=null)
result=(string)obj.ToString();
}
catch (Exception e)
{
DBErrorLog("GetFieldValue",sqlstr,e.ToString());
}
finally
{
oleDbCommand.Connection.Close();
}
return result;
}
///<summary>
/// 取系列号,根据不同的数据表,返回不同值,用于生成数据记录的ID字段内容
///</summary>
///<param name="TableName">数据表名</param>
///<returns>数据值</returns>
public int GetSerialNo(string TableName,string schoolcode)
{
int MaxValue=1;
DataTable dt =null;
string tname=TableName.ToUpper();
TrimChineseStr(ref tname, 60);
string SqlStr ="select top 1 * from " +tname+" where schoolcode="+schoolcode;
dt =GetDtBySql(SqlStr);
if (dt!=null&&dt.Rows.Count >0)
{
SqlStr ="select top 1 * from "+tname+" where schoolcode="+schoolcode+" order by "+dt.Columns[0].ColumnName +" desc";
dt =GetDtBySql(SqlStr);
MaxValue =Convert.ToInt32(dt.Rows[0][0]);
MaxValue=MaxValue+1;
}
return MaxValue;
}
}
}