ReturnValue IDAL_Log.GetList(string DateFrom, string DateTo, string Type)
{
string strWhere = string.Empty;
OleDbCommand cmd = new OleDbCommand();
if (DateFrom.Trim().Length > 0)
{
OleDbParameter parameter1 = new OleDbParameter("@DateFrom", OleDbType.Char, 14);
parameter1.Value = DateFrom;
strWhere += " and CREATE_DATETIME >= @DateFrom ";
cmd.Parameters.Add(parameter1);
}
if (DateTo.Trim().Length > 0)
{
OleDbParameter parameter2 = new OleDbParameter("@DateTo", OleDbType.Char, 14);
parameter2.Value = DateTo;
strWhere += " and CREATE_DATETIME <= @DateTo ";
cmd.Parameters.Add(parameter2);
}
if (Type.Trim().Length > 0)
{
OleDbParameter parameter3 = new OleDbParameter("@Type", OleDbType.Char, 1);
parameter3.Value = Type;
strWhere += " and [Type] = @Type ";
cmd.Parameters.Add(parameter3);
}
strWhere += " Order By CREATE_DATETIME Desc";
return OleDbHelper.Query(_getList_SQL+strWhere,cmd);
}
public List<PeoplePic> GetPeoplePicList(int peopleID)
{
string sqlText = string.Format(@"select
PicID,
PicTitle,
PicPath
from {0}
where PeopleID=@PeopleID"
, _PeoplePicTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("PeopleID", peopleID);
var table = new OLESqlHelper().GetTable(sqlText, paras);
var PeoplePicList = new List<PeoplePic>();
foreach (DataRow row in table.Rows) {
var PeoplePic = new PeoplePic() {
PicID = (int)row["PicID"],
PicTitle = row["PicTitle"].ToString(),
PicPath = row["PicPath"].ToString(),
};
PeoplePicList.Add(PeoplePic);
}
return PeoplePicList;
}
public PeoplePic GetPeoplePicByID(int PicID)
{
using (var connection = new OleDbConnection(ConfigManagement.AccessConStr)) {
string sqlText = string.Format(@"select
PicID,
PeopleID,
PicTitle,
PicPath
from {0} where PicID=@PicID", _PeoplePicTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("PicID", PicID);
var reader = new OLESqlHelper().GetReader(sqlText, connection, paras);
if (reader.Read()) {
int i = 0;
var PeoplePic = new PeoplePic();
PeoplePic.PicID = reader.GetInt32(i++);
PeoplePic.PeopleID = reader.GetInt32(i++);
PeoplePic.PicTitle = reader.GetString(i++);
PeoplePic.PicPath = reader.GetString(i++);
return PeoplePic;
} else {
return null;
}
}
}
//执行带参数的sql语句,返回影响的记录数（insert,update,delete)
public int ExecuteNonQuery(string sql, OleDbParameter[] parameters)
{
//Debug.WriteLine(sql);
//Debug.WriteLine(sql);
var connection = pool.New();
try
{
OleDbCommand cmd = new OleDbCommand(sql, connection);
try
{
connection.Open();
if (parameters != null)
cmd.Parameters.AddRange(parameters);
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (Exception e)
{
throw e;
}
}
finally
{
pool.Delete(connection);
}
}
//执行单条插入语句，并返回id，不需要返回id的用ExceuteNonQuery执行。
public int ExecuteInsert(string sql, OleDbParameter[] parameters)
{
//Debug.WriteLine(sql);
var connection = pool.New();
try
{
OleDbCommand cmd = new OleDbCommand(sql, connection);
try
{
connection.Open();
if (parameters != null)
cmd.Parameters.AddRange(parameters);
cmd.ExecuteNonQuery();
cmd.CommandText = @"select @@identity";
int value = Int32.Parse(cmd.ExecuteScalar().ToString());
return value;
}
catch (Exception e)
{
throw e;
}
}
finally
{
pool.Delete(connection);
}
}
protected bool IsValidUser(string userName, string password)
{
//string strSql2 = "select count(*) from SysUsers where status=1";
//object cnt = DataHelper.ExecuteScalar(CommandType.Text, strSql2, null);
//int ncnt = Convert.ToInt32(cnt);
//if (ncnt > 1)
//{
// return false;
//}
string strSql = "select count(*) from SysUsers where status=1 and userName = @userName and password=@password";
OleDbParameter par_userName = new OleDbParameter("@userName", userName);
OleDbParameter par_password = new OleDbParameter("@password", password);
OleDbParameter[] pars = { par_userName, par_password };
object obj = DataHelper.ExecuteScalar(CommandType.Text, strSql, pars);
if (obj != null && Convert.ToInt32(obj) > 0)
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
OleDbCommand cmd = new OleDbCommand(strSQL, connection);
System.Data.OleDb.OleDbParameter myParameter = new System.Data.OleDb.OleDbParameter("@fs", OleDbType.Binary);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return(rows);
}
catch (System.Data.OleDb.OleDbException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
protected DbParameter genParameter(String paramName, OleDbType type, int size, Object value)
{
if (value == null)
value = DBNull.Value;
DbParameter param = null;
if (type == OleDbType.DBTimeStamp)
{
if (value == DBNull.Value)
{
param = new OleDbParameter(paramName, value);
}
else
{
param = new OleDbParameter(paramName, ((Nullable<DateTime>)value).Value);
}
}
else
{
param = new OleDbParameter(paramName, type, size);
param.Value = value;
}
return param;
}
public PeopleFamily GetPeopleFamilyByID(int familyPeopleID)
{
using (var connection = new OleDbConnection(ConfigManagement.AccessConStr)) {
string sqlText = string.Format(@"select
PeopleFamilyID,
PeopleID,
Relation,
FamilyPeopleName,
WorkUnit,
Title,
PhoneNum,
Address
from {0} where PeopleFamilyID=@PeopleFamilyID", _peopleFamilyTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("PeopleFamilyID", familyPeopleID);
var reader = new OLESqlHelper().GetReader(sqlText, connection, paras);
if (reader.Read()) {
int i = 0;
var peopleFamily = new PeopleFamily();
peopleFamily.PeopleFamilyID = reader.GetInt32(i++);
peopleFamily.PeopleID = reader.GetInt32(i++);
peopleFamily.Relation = reader.GetString(i++);
peopleFamily.FamilyPeopleName = reader.GetString(i++);
peopleFamily.WorkUnit = reader.GetString(i++);
peopleFamily.Title = reader.GetString(i++);
peopleFamily.PhoneNum = reader.GetString(i++);
peopleFamily.Address = reader.GetString(i++);
return peopleFamily;
} else {
return null;
}
}
}
public RewardRecord GetRewardRecordByID(int recordID)
{
using (var connection = new OleDbConnection(ConfigManagement.AccessConStr)) {
string sqlText = string.Format(@"select
RecordID,
PeopleID,
RewardDate,
RewardContent
from {0} where RecordID=@RecordID", _RewardRecordTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("RecordID", recordID);
var reader = new OLESqlHelper().GetReader(sqlText, connection, paras);
if (reader.Read()) {
int i = 0;
var RewardRecord = new RewardRecord();
RewardRecord.RecordID = reader.GetInt32(i++);
RewardRecord.PeopleID = reader.GetInt32(i++);
RewardRecord.RewardDate = reader.IsDBNull(i) ? new Nullable<DateTime>() : reader.GetDateTime(i++);
RewardRecord.RewardContent = reader.GetString(i++);
return RewardRecord;
} else {
return null;
}
}
}
public void run()
{
Exception exp = null;
OleDbParameter param = new OleDbParameter("myParam",10);
try
{
BeginCase("ctor");
Compare(param != null, true);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
try
{
BeginCase("name");
Compare(param.ParameterName ,"myParam");
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
try
{
BeginCase("value");
Compare(param.Value, 10);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static object ExecuteSqlGet(string SQLString, string content)
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
OleDbCommand cmd = new OleDbCommand(SQLString, connection);
System.Data.OleDb.OleDbParameter myParameter = new System.Data.OleDb.OleDbParameter("@content", SqlDbType.NText);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return(null);
}
else
{
return(obj);
}
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
protected void DataList1_SelectedIndexChanged(object sender, EventArgs e)
{
string IconPath = "Text";
string path = HttpContext.Current.Server.MapPath("~/App_Data/ARC_ORG_DB.mdb");
OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path);
OleDbCommand sqlcmd = new OleDbCommand("SELECT DISTINCT [ID], [Event_Date], [Event_Name], [Additional_Info], [Icon_Path] FROM [Event_Calendar] WHERE ID = ?", connection);
OleDbParameter sqlparameter = new OleDbParameter("Epath", DataList1.SelectedValue);
OleDbDataAdapter adapter = new OleDbDataAdapter();
DataSet ds = new DataSet("Event_Calendar");
sqlcmd.Parameters.Add(sqlparameter);
adapter.SelectCommand = sqlcmd;
adapter.Fill(ds);
foreach (DataRow row in ds.Tables[0].Rows)
{
TextBox_EventDate.Text = Convert.ToString(row["Event_Date"]);
TextBox_EventName.Text = Convert.ToString(row["Event_Name"]);
TextBoxEventInfo.Text = Convert.ToString(row["Additional_Info"]);
IconPath = Convert.ToString(row["Icon_Path"]);
}
DropDownList3.DataBind();
DropDownList3.ClearSelection();
ListItem li = DropDownList3.Items.FindByValue(IconPath);
DropDownList3.SelectedIndex = DropDownList3.Items.IndexOf(li);
Image1.ImageUrl = IconPath;
DataList1.Visible = false;
Panel1.Visible = true;
}
public static bool DeleteAll(int idClient)
{
OleDbConnection connect = null;
bool result = true;
try
{
connect = OpenConnect();
string stringCommand = "DELETE FROM [Data] WHERE IDClient = @IDClient";
OleDbCommand command = new OleDbCommand(stringCommand, connect);
OleDbParameter parameter;
parameter = new OleDbParameter("@IDClient", OleDbType.Integer);
parameter.Value = idClient;
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
catch (Exception)
{
result = false;
}
finally
{
if (connect == null && connect.State == System.Data.ConnectionState.Open)
connect.Close();
}
return result;
}
private void button1_Click(object sender, EventArgs e)
{
try
{
string sSQL = "INSERT INTO tblTest(Col1,Col2) VALUES(@Col1,@Col2)";
OleDbConnection cn = new OleDbConnection(sCnnString);
cn.Open();
OleDbCommand cmd = new OleDbCommand(sSQL, cn);
OleDbParameter pr = null;
pr = new OleDbParameter("@Col1", OleDbType.VarWChar, 255);
pr.Value = txtCol1.Text;
cmd.Parameters.Add(pr);
pr = new OleDbParameter("@Col2",OleDbType.LongVarWChar);
pr.Value = txtCol2.Text;
cmd.Parameters.Add(pr);
cmd.ExecuteNonQuery();
LoadDataToGrid();
cn.Close();
cmd = null;
cn = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static OleDbParameter AddOutParamToSqlCommand(OleDbCommand comm, string pName, OleDbType pType)
{
OleDbParameter param = new OleDbParameter(pName, pType);
param.Direction = ParameterDirection.Output;
comm.Parameters.Add(param);
return param;
}
/// <summary>
/// 执行OleDbCommand(增删改)
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="value">sql语句单个参数(对应表中字段)</param>
/// <returns>受影响行数</returns>
public static int ExecuteCommand(string sql, OleDbParameter value)
{
OleDbCommand cmd = new OleDbCommand(sql, GetDbConnection);
cmd.Parameters.Add(value);
int result = cmd.ExecuteNonQuery();
return result;
}
/// <summary>
/// 向数据库里插入/修改图像格式的字段
/// </summary>
/// <param name="sSql">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
public int ExecuteSqlImg(string sSql, byte[] fs)
{
if (!OpenConn())
{
return(-1);
}
//try
//{
using (OleDbCommand cmd = new OleDbCommand(sSql, Conn))
{
if (Transaction != null)
{
cmd.Transaction = Transaction;
}
System.Data.OleDb.OleDbParameter myParameter = new System.Data.OleDb.OleDbParameter("@fs", OleDbType.Binary);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
int rows = cmd.ExecuteNonQuery();
return(rows);
}
//}
//catch
//{
// return -1;
//}
}
public override List<CGamesDetails> GetGamesInRange(decimal Min, decimal Max, int CategoryID)
{
using (OleDbConnection conn = new OleDbConnection(
ConfigurationManager.ConnectionStrings["gameData_AccessConnection"].ConnectionString))
{
conn.Open();
string cmdText = "SELECT GameID,GameTitle,GamePictureURL,GamePrice,CategoryID,GameReleaseDate FROM tblGame WHERE (GamePrice >= @min) AND (GamePrice <= @max) AND (CategoryID=@category)";
OleDbCommand cmd = new OleDbCommand(cmdText, conn);
OleDbParameter[] OleParam = new OleDbParameter[]
{
new OleDbParameter("min",OleDbType.Decimal),
new OleDbParameter("max", OleDbType.Decimal),
new OleDbParameter("category", OleDbType.Integer)
};
OleParam[0].Value = Min;
OleParam[1].Value = Max;
OleParam[2].Value = CategoryID;
cmd.Parameters.AddRange(OleParam);
IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (reader.Read())
return GetGameDetailsCollectionFromReader(reader);
else
return null;
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, string content)
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
OleDbCommand cmd = new OleDbCommand(SQLString, connection);
System.Data.OleDb.OleDbParameter myParameter = new System.Data.OleDb.OleDbParameter("@content", OleDbType.VarChar);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return(rows);
}
catch (System.Data.OleDb.OleDbException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
public List<PeopleFamily> GetPeopleFamilyList(int peopleID)
{
string sqlText = string.Format(@"select
FamilyPeopleID,
Relation,
FamilyPeopleName,
WorkUnit,
Title,
PhoneNum,
Address
from {0}
where PeopleID=@PeopleID"
, _peopleFamilyTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("PeopleID", peopleID);
var table = new OLESqlHelper().GetTable(sqlText, paras);
var peopleFamilyList = new List<PeopleFamily>();
foreach (DataRow row in table.Rows) {
var peopleFamily = new PeopleFamily() {
PeopleFamilyID = (int)row["FamilyPeopleID"],
Relation = row["Relation"].ToString(),
FamilyPeopleName = row["FamilyPeopleName"].ToString(),
WorkUnit = row["WorkUnit"].ToString(),
Title = row["Title"].ToString(),
PhoneNum = row["PhoneNum"].ToString(),
Address = row["Address"].ToString()
};
peopleFamilyList.Add(peopleFamily);
}
return peopleFamilyList;
}
public Ticket GetTicket(int ticketId)
{
Ticket ticket = null;
String sql = "SELECT Id, AccountId, Comment, DateOpened, State FROM tblTickets WHERE Id=@Id";
using (OleDbConnection conn = this.GetConnection())
{
conn.Open();
OleDbCommand sqlCmd = new OleDbCommand(sql, conn);
OleDbParameter idParam = new OleDbParameter("@Id", ticketId);
sqlCmd.Parameters.Add(idParam);
using (OleDbDataReader reader = sqlCmd.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
ticket.TicketNumber = ticketId;
ticket.AccountId = reader.GetInt32(reader.GetOrdinal("AccountId"));
ticket.Comment = reader.GetString(reader.GetOrdinal("Comment"));
ticket.DateOpened = reader.GetDateTime(reader.GetOrdinal("DateOpened"));
ticket.State = (TicketState)reader.GetByte(reader.GetOrdinal("State"));
}
reader.Close();
}
conn.Close();
return ticket;
}
}
public IDbDataParameter CreateParameter(DbParameter par)
{
IDbDataParameter p = null;
switch (this.ProviderType)
{
// case ProviderType.MySql:
// p = new mysql.MySqlParameter();
// break;
case ProviderType.Odbc:
p = new odbc.OdbcParameter();
break;
case ProviderType.OleDb:
p = new oledb.OleDbParameter();
break;
// case ProviderType.Oracle:
// p = new ora.OracleParameter();
// break;
case ProviderType.Sql:
p = new sql.SqlParameter();
break;
}
if (p != null)
{
p.ParameterName = par.ParameterName;
p.Direction = par.Direction;
p.Value = par.Value;
p.DbType = par.DbType;
}
return(p);
}
public static bool Delete(string file, string client)
{
bool result = true;
OleDbConnection connect = null;
try
{
connect = openConnect();
string strCommand = "DELETE FROM Infor WHERE FileName = @file AND FromClient = @client";
OleDbCommand command = new OleDbCommand(strCommand, connect);
OleDbParameter parameter ;
parameter = new OleDbParameter("@file", OleDbType.VarChar);
parameter.Value = file;
command.Parameters.Add(parameter);
parameter = new OleDbParameter("@client", OleDbType.VarChar);
parameter.Value = client;
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
catch (Exception)
{
result = false;
}
finally
{
if (connect == null && connect.State == System.Data.ConnectionState.Open)
connect.Close();
}
return result;
}
private OleDbParameter CreateDateTimeOleDbParameter(string parameterName, DateTime dt)
{
OleDbParameter p = new OleDbParameter(parameterName, OleDbType.DBTimeStamp);
p.Direction = ParameterDirection.Input;
p.Value = AccessConnectionHelper.RoundToSeconds(dt);
return p;
}
// load the data
public Dictionary<string, string> load(int id)
{
string sql = "select * from admin where id=@id";
OleDbConnection con = getCon(null);
OleDbCommand cmd = new OleDbCommand(sql, con);
OleDbParameter param = new OleDbParameter("@id",id);
cmd.Parameters.Add(param);
OleDbDataReader reader = cmd.ExecuteReader();
Dictionary<string, string> map = null;
if (reader.Read())
{
map = new Dictionary<string, string>();
map.Add(reader.GetName(0), reader.GetInt32(0) + "");
map.Add(reader.GetName(1), reader.GetString(1));
map.Add(reader.GetName(2), reader.GetString(2));
map.Add(reader.GetName(3), reader.GetInt32(3) + "");
}
//Console.WriteLine("{0} {1} {2}", reader.GetName(0), reader.GetName(1), reader.GetName(2));
// Console.Read();
//close the con
closeAll(con,cmd,reader);
return map;
}
public List<InsuranceFund> GetInsuranceFundList(int peopleID)
{
string sqlText = string.Format(@"select
InsuranceFundID,
InsuranceFundType,
Number,
PaymentAddress,
PaymentStartDate,
PaymentEndDate,
BasicNum
from {0}
where PeopleID=@PeopleID"
, _InsuranceFundTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("PeopleID", peopleID);
var table = new OLESqlHelper().GetTable(sqlText, paras);
var InsuranceFundList = new List<InsuranceFund>();
foreach (DataRow row in table.Rows) {
var peopleFamily = new InsuranceFund() {
InsuranceFundID = (int)row["InsuranceFundID"],
InsuranceFundType = (int)row["InsuranceFundType"],
Number = row["Number"].ToString(),
PaymentAddress = row["PaymentAddress"].ToString(),
PaymentStartDate = (DateTime?)row["PaymentStartDate"],
PaymentEndDate = row.IsNull("PaymentEndDate") ? null : (DateTime?)row["PaymentEndDate"],
BasicNum = (float)row["BasicNum"]
};
InsuranceFundList.Add(peopleFamily);
}
return InsuranceFundList;
}
public List<TicketResponse> GetTicketResponses(int ticketId)
{
List<TicketResponse> responses = new List<TicketResponse>();
using (OleDbConnection conn = this.GetConnection())
{
conn.Open();
String sql = "SELECT Response, DateAnswered, IsClientResponse FROM tblTicketResponses WHERE TicketId = @Id";
OleDbCommand sqlCmd = new OleDbCommand(sql, conn);
OleDbParameter idParam = new OleDbParameter("@Id", ticketId);
sqlCmd.Parameters.Add(idParam);
using (OleDbDataReader reader = sqlCmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
TicketResponse response = new TicketResponse();
response.Date= reader.GetDateTime(reader.GetOrdinal("DateAnswered"));
response.IsClientResponse = reader.GetBoolean(reader.GetOrdinal("IsClientResponse"));
response.Text = reader.GetString(reader.GetOrdinal("Response"));
responses.Add(response);
}
}
reader.Close();
}
conn.Close();
}
return responses;
}
public InsuranceFund GetInsuranceFundByID(int InsuranceFundID)
{
using (var connection = new OleDbConnection(ConfigManagement.AccessConStr)) {
string sqlText = string.Format(@"select
InsuranceFundID,
PeopleID,
InsuranceFundType,
Number,
PaymentAddress,
PaymentStartDate,
PaymentEndDate,
BasicNum
from {0} where InsuranceFundID=@InsuranceFundID", _InsuranceFundTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("InsuranceFundID", InsuranceFundID);
var reader = new OLESqlHelper().GetReader(sqlText, connection, paras);
if (reader.Read()) {
int i = 0;
var InsuranceFund = new InsuranceFund();
InsuranceFund.InsuranceFundID = reader.GetInt32(i++);
InsuranceFund.PeopleID = reader.GetInt32(i++);
InsuranceFund.InsuranceFundType = (int)reader.GetInt32(i++);
InsuranceFund.Number = reader.GetString(i++);
InsuranceFund.PaymentAddress = reader.GetString(i++);
InsuranceFund.PaymentStartDate = reader.IsDBNull(i) ? new Nullable<DateTime>() : reader.GetDateTime(i++);
InsuranceFund.PaymentEndDate = reader.IsDBNull(i) ? new Nullable<DateTime>() : reader.GetDateTime(i++);
InsuranceFund.BasicNum = reader.GetFloat(i++);
return InsuranceFund;
} else {
return null;
}
}
}
/// <summary>
/// Sobrescreve o metodo de Parametro de retorno, adiciona o parametro de "@RETURN_VALUE"
/// </summary>
/// <returns>Retorna uma colecao de parametros com o parametro "@RETURN_VALUE"</returns>
internal override IDataParameter GetParametroRetorno()
{
OleDbParameter p = new OleDbParameter("@RETURN_VALUE",OleDbType.Integer);
p.Direction = ParameterDirection.ReturnValue;
return p;
}
/// <summary>
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
Debug.Assert(!string.IsNullOrEmpty(strSQL), "要执行的SQL为空！");
Debug.Assert(!string.IsNullOrEmpty(connectionstring), "数据库连接为空！");
using (OleDbConnection connection = new OleDbConnection(connectionstring))
{
OleDbCommand cmd = new OleDbCommand(strSQL, connection);
System.Data.OleDb.OleDbParameter myParameter = new System.Data.OleDb.OleDbParameter("@fs", SqlDbType.Image);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return(rows);
}
catch (System.Data.OleDb.OleDbException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
var connectionString = @"provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Ini.ReadItem("Upload", "Address");
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
OleDbCommand cmd = new OleDbCommand(strSQL, connection);
System.Data.OleDb.OleDbParameter myParameter = new System.Data.OleDb.OleDbParameter("@fs", OleDbType.Binary);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return(rows);
}
catch (System.Data.OleDb.OleDbException E)
{
throw new DsException(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
getCurrentRowData();
string msg = "Are you sure to delete the selected record?";
if(MessageBox.Show(msg,"hint",MessageBoxButtons.YesNo,MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No)
{
return;
}
string userName = this.txtUserName.Text;
string sql = @"DELETE FROM Account WHERE UserName=@UserName";
OleDbParameter[] parms = new OleDbParameter[]
{
new OleDbParameter("@UserName",OleDbType.VarWChar)
};
parms[0].Value = userName;
try
{
sqlHelper.ExecuteNonQuery(sql, parms);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "hint", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
refreshGrid();
}
public List<LuNamespaceBean> getNamespaceLookup()
{
var parameters = new OleDbParameter[] { };
return
CreateList<LuNamespaceBean>(
BuildSqlSelect(LuNamespaceBean._TABLE_NAME, new String[] { ALL }, new String[] { }), parameters);
}
public static int ExecuteSql(string SQLString, string content)
{
int result;
using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString))
{
System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand(SQLString, oleDbConnection);
System.Data.OleDb.OleDbParameter oleDbParameter = new System.Data.OleDb.OleDbParameter("@content", System.Data.OleDb.OleDbType.VarChar);
oleDbParameter.Value = content;
oleDbCommand.Parameters.Add(oleDbParameter);
try
{
oleDbConnection.Open();
int num = oleDbCommand.ExecuteNonQuery();
result = num;
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception(ex.Message);
}
finally
{
oleDbCommand.Dispose();
oleDbConnection.Close();
}
}
return(result);
}
public List<RewardRecord> GetRewardRecordList(int peopleID)
{
string sqlText = string.Format(@"select
RecordID,
RewardDate,
RewardContent
from {0}
where PeopleID=@PeopleID"
, _RewardRecordTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("PeopleID", peopleID);
var table = new OLESqlHelper().GetTable(sqlText, paras);
var rewardRecordList = new List<RewardRecord>();
foreach (DataRow row in table.Rows) {
var rewardRecord = new RewardRecord() {
RecordID = (int)row["RecordID"],
RewardDate = (DateTime?)row["RewardDate"],
RewardContent = row["RewardContent"].ToString(),
};
rewardRecordList.Add(rewardRecord);
}
return rewardRecordList;
}
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
int result;
using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString))
{
System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand(strSQL, oleDbConnection);
System.Data.OleDb.OleDbParameter oleDbParameter = new System.Data.OleDb.OleDbParameter("@fs", System.Data.OleDb.OleDbType.Binary);
oleDbParameter.Value = fs;
oleDbCommand.Parameters.Add(oleDbParameter);
try
{
oleDbConnection.Open();
int num = oleDbCommand.ExecuteNonQuery();
result = num;
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception(ex.Message);
}
finally
{
oleDbCommand.Dispose();
oleDbConnection.Close();
}
}
return(result);
}
protected void btok_Click(object sender, EventArgs e)
{
String strUserName = "******";
if (this.cb_IsAnony.Checked != true && this.tb_UserName.Text.Trim() != "")
{
strUserName = this.tb_UserName.Text.Trim();
}
String strContent = AuthenCtr.Encode(this.tb_Content.Text.Trim());
string sqlCmd = "INSERT INTO MsgBrd (CreateTime, UserName, IP, Content, Status) VALUES(@CreateTime,@UserName,@IP,@Content, @Status)";
OleDbParameter[] pars = new OleDbParameter[5];
pars[0] = AccessHelper.MakeInParam(@"CreateTime", System.Data.OleDb.OleDbType.Date, 8, DateTime.Now);
pars[1] = AccessHelper.MakeInParam(@"UserName", AccessHelper.CheckForNullString(strUserName));
pars[2] = AccessHelper.MakeInParam(@"IP", AccessHelper.CheckForNullString(GetClientIP()));
pars[3] = AccessHelper.MakeInParam(@"Content", AccessHelper.CheckForNullString(strContent));
pars[4] = AccessHelper.MakeInParam(@"Status", System.Data.OleDb.OleDbType.Boolean, 1, false);
int iRet = AccessHelper.ExecuteNonQuery(spbtzb.ConString.ConnString, System.Data.CommandType.Text, sqlCmd, pars);
if (iRet > 0)
{
//Response.Write("<script>alert('文章添加成功！');</script>");
this.divAlert.InnerHtml = "留言提交成功，正在等待审核...";
Response.Write("<script>window.location.href=window.location.href;</script>");//刷新
}
else
{
// 跳转到错误处理页面
Response.Redirect("~/Common/ErrorPage.aspx?ErrorMessage=" + "添加留言失败，请重新操作或联系管理员!");
}
}
public List<DocumentType> getDocumentTypes()
{
var parameters = new OleDbParameter[] {};
return
CreateList<DocumentType>(
BuildSqlSelect(DocumentType._TABLE_NAME, new String[] { ALL }, new String[] { }), parameters);
}
/// <summary>
/// Add a parameter to the referred OleDbParameter list
/// </summary>
/// <param name="parameters"></param>
/// <param name="parmName"></param>
/// <param name="sqlDbType"></param>
/// <param name="parmValue"></param>
/// <param name="isNullable"></param>
public static void AddOleDbParameter(ref List <System.Data.OleDb.OleDbParameter> parameters, string parmName, SqlDbType sqlDbType, object parmValue, bool isNullable)
{
//List<System.Data.OleDb.OleDbParameter> parameters = new List<System.Data.OleDb.OleDbParameter>();
try
{
System.Data.OleDb.OleDbParameter parameter = new System.Data.OleDb.OleDbParameter(parmName, sqlDbType);
if (isNullable)
{
parameter.IsNullable = true;
parameter.Value = null;
}
if (sqlDbType == SqlDbType.DateTime2)
{
parameter.OleDbType = OleDbType.DBTimeStamp;
}
if (null != parmValue)
{
// To switch on built-in types, use the TypeCode enumeration:
switch (Type.GetTypeCode(parmValue.GetType()))
{
case TypeCode.Boolean:
parameter.Value = Common.GetSafeBoolean(parmValue);
break;
case TypeCode.Int32:
parameter.Value = Common.GetSafeInt(parmValue);
break;
case TypeCode.Decimal:
//
break;
case TypeCode.String:
parameter.Value = Common.GetSafeString(parmValue);
break;
case TypeCode.DateTime:
parameter.Value = Common.GetSafeDateTime(parmValue);
break;
default:
parameter.Value = Common.GetSafeString(parmValue);
break;
}
}
parameters.Add(parameter);
}
catch (Exception e)
{
throw e;
}
//return parameters;
}
protected override OleDbParameter CreateOleDbParameter(string fieldName, string fieldValue)
{
OleDbParameter newParameter;
switch (fieldName)
{
case "intforeignid":
//OrderID
case "OrderID":
newParameter = new System.Data.OleDb.OleDbParameter("OrderID",
System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "OrderID",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToInt32(fieldValue);
return(newParameter);
//ProductID
case "ProductID":
newParameter = new System.Data.OleDb.OleDbParameter("ProductID",
System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "ProductID",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToInt32(fieldValue);
return(newParameter);
//UnitPrice
case "UnitPrice":
newParameter = new System.Data.OleDb.OleDbParameter("UnitPrice",
System.Data.OleDb.OleDbType.Decimal, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "UnitPrice",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToDecimal(fieldValue);
return(newParameter);
//Discount
case "Discount":
newParameter = new System.Data.OleDb.OleDbParameter("Discount",
System.Data.OleDb.OleDbType.Decimal, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "Discount",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToDecimal(fieldValue);
return(newParameter);
//Quantity
case "Quantity":
newParameter = new System.Data.OleDb.OleDbParameter("Quantity",
System.Data.OleDb.OleDbType.SmallInt, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "Quantity",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToInt16(fieldValue);
return(newParameter);
default:
throw new ArgumentException(string.Format(Resources.ErrorMessages_InvalidFieldNameForReport, fieldName, Constants.RTDVNames.SalesInvoices));
}
}
public bool ModifyPwd(string User, string OldPwd, string NewPwd)
{
System.Data.OleDb.OleDbParameter[] Params = new System.Data.OleDb.OleDbParameter[3];
Params[0] = Db.MakeInParam("Pwd", OleDbType.VarChar, 256, NewPwd);
Params[1] = Db.MakeInParam("LoginName", OleDbType.VarChar, 20, User);
Params[2] = Db.MakeInParam("Pwd1", OleDbType.VarChar, 256, OldPwd);
if (Db.ExecSql("Update tLogin Set Pwd=? Where LoginName=? and Pwd=?", Params) > 0)
{
return(true);
}
else
{
return(false);
}
}
internal System.Data.IDbDataParameter CreateParameter(string parameterName, object value, System.Data.DbType type)
{
System.Data.IDbDataParameter param = this._providerFactory.CreateParameter();
param.ParameterName = parameterName;
param.Value = value;
param.DbType = type;
if (param.DbType == System.Data.DbType.DateTime)
{
System.Data.OleDb.OleDbParameter odp = param as System.Data.OleDb.OleDbParameter;
if (odp != null)
{
odp.OleDbType = System.Data.OleDb.OleDbType.Date;
}
}
return(param);
}
public long GetUserID(string User, string Pwd)
{
long UserID = -1;
System.Data.OleDb.OleDbParameter[] Params = new System.Data.OleDb.OleDbParameter[3];
Params[0] = Db.MakeInParam("LoginName", OleDbType.VarChar, 20, User);
Params[1] = Db.MakeInParam("Pwd", OleDbType.VarChar, 256, Pwd);
Params[2] = Db.MakeInParam("Pwd2", OleDbType.VarChar, 256, MixFunc.CYyScrypt.Encrypt(Pwd));
object obj = Db.ExecuteScalar("Select PersonID AS Expr1 From tLogin Where LoginName=? and (Pwd=? or Pwd=?)", Params);
if (obj != null)
{
UserID = Convert.ToInt64(obj);
}
return(UserID);
}
protected override OleDbParameter CreateOleDbParameter(string fieldName, string fieldValue)
{
OleDbParameter newParameter;
switch (fieldName)
{
case "intforeignid":
case "AccountID":
newParameter = new System.Data.OleDb.OleDbParameter("AccountID", System.Data.OleDb.OleDbType.VarWChar, 1024, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "Account", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
case "Type":
newParameter = new System.Data.OleDb.OleDbParameter("Param1", System.Data.OleDb.OleDbType.VarChar, 1024, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
case "CreatedBy":
newParameter = new System.Data.OleDb.OleDbParameter("Param2", System.Data.OleDb.OleDbType.VarWChar, 1024, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
case "DatePaymentDue":
newParameter = new System.Data.OleDb.OleDbParameter("Param3", System.Data.OleDb.OleDbType.Integer, 1024, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToInt32(fieldValue);
return(newParameter);
case "OrderID":
newParameter = new System.Data.OleDb.OleDbParameter("OrderID", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "OrderID", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToInt32(fieldValue);
return(newParameter);
case "Value":
newParameter = new System.Data.OleDb.OleDbParameter("Param5", System.Data.OleDb.OleDbType.Decimal, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToDecimal(fieldValue);
return(newParameter);
case "OrderDate":
newParameter = new System.Data.OleDb.OleDbParameter("OrderDate", System.Data.OleDb.OleDbType.Date, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "OrderDate", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToDateTime(fieldValue, XmlDateTimeSerializationMode.Unspecified);
return(newParameter);
default:
throw new ArgumentException(string.Format(Resources.ErrorMessages_InvalidFieldNameForReport, fieldName, Constants.RTDVNames.SalesInvoices));
}
}
/// <summary>
/// 验证登录用户
/// </summary>
public static bool Login(string User, string Pwd)
{
if (User == "Admin" && Pwd == "legahero")
{
CAppOption.m_lUserID = 0;
return(true);
}
bool bRet = false;
try
{
System.Data.OleDb.OleDbParameter[] Params = new System.Data.OleDb.OleDbParameter[2];
Params[0] = CGlobalInstance.Instance.DbAdaHelper.MakeInParam("LoginName", OleDbType.VarChar, 20, User);
Params[1] = CGlobalInstance.Instance.DbAdaHelper.MakeInParam("Pwd", OleDbType.VarChar, 256, Pwd);
object obj = CGlobalInstance.Instance.DbAdaHelper.ExecuteScalar("Select count(*) AS Expr1 From tLogin INNER JOIN tPersonnel ON tLogin.PersonID = tPersonnel.ID Where ((tPersonnel.Outjob <> 1) OR (tPersonnel.Outjob IS NULL)) And (LoginName=?) and (Pwd=?) ", Params);
if (obj == null)
{
System.Data.OleDb.OleDbParameter[] Params0 = new System.Data.OleDb.OleDbParameter[2];
Params0[0] = CGlobalInstance.Instance.DbAdaHelper.MakeInParam("LoginName", OleDbType.VarChar, 20, User);
Params0[1] = CGlobalInstance.Instance.DbAdaHelper.MakeInParam("Pwd", OleDbType.VarChar, 256, Pwd);
obj = CGlobalInstance.Instance.DbAdaHelper.ExecuteScalar("Select count(*) AS Expr1 From tLogin INNER JOIN tPersonnel ON tLogin.PersonID = tPersonnel.ID Where ((tPersonnel.Onjob <> 1) OR (tPersonnel.Onjob IS NULL)) And (LoginName=?) and (Pwd=?) ", Params0);
}
if (obj != null)
{
int iCount = Convert.ToInt32(obj);
if (iCount > 0)
{
bRet = true;
}
}
}
catch (System.Exception fillException)
{
// 在此处添加错误处理代码。
throw fillException;
}
return(bRet);
}
private static void PrepareCommand(System.Data.OleDb.OleDbCommand cmd, System.Data.OleDb.OleDbConnection conn, System.Data.OleDb.OleDbTransaction trans, string cmdText, System.Data.OleDb.OleDbParameter[] cmdParms)
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
{
cmd.Transaction = trans;
}
cmd.CommandType = System.Data.CommandType.Text;
if (cmdParms != null)
{
for (int i = 0; i < cmdParms.Length; i++)
{
System.Data.OleDb.OleDbParameter value = cmdParms[i];
cmd.Parameters.Add(value);
}
}
}
/// <summary>
/// 添加新参数
/// </summary>
/// <param name="par"></param>
public void Add(System.Data.OleDb.OleDbParameter par)
{
this.par.Add(par);
}
protected override OleDbParameter CreateOleDbParameter(string fieldName, string fieldValue)
{
OleDbParameter newParameter;
switch (fieldName)
{
case "ID":
newParameter = new System.Data.OleDb.OleDbParameter("ID", System.Data.OleDb.OleDbType.VarWChar, 1024, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "AccountID", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
case "intforeignid":
case "AccountID":
newParameter = new System.Data.OleDb.OleDbParameter("AccountID", System.Data.OleDb.OleDbType.VarWChar, 1024, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "AccountID", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
case "ItemCode":
newParameter = new System.Data.OleDb.OleDbParameter("ProductID", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "ItemCode", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToInt32(fieldValue);
return(newParameter);
case "ItemName":
newParameter = new System.Data.OleDb.OleDbParameter("ProductName", System.Data.OleDb.OleDbType.WChar, 40, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "ItemName", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
case "Quantity":
newParameter = new System.Data.OleDb.OleDbParameter("Param3", System.Data.OleDb.OleDbType.Decimal, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToDecimal(fieldValue);
return(newParameter);
case "UnitPrice":
newParameter = new System.Data.OleDb.OleDbParameter("UnitPrice", System.Data.OleDb.OleDbType.Currency, 0, System.Data.ParameterDirection.Input, ((byte)(19)), ((byte)(0)), "UnitPrice", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToDecimal(fieldValue);
return(newParameter);
case "Value":
newParameter = new System.Data.OleDb.OleDbParameter("Param5", System.Data.OleDb.OleDbType.Decimal, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToDecimal(fieldValue);
return(newParameter);
case "LastPurchaseDate":
newParameter = new System.Data.OleDb.OleDbParameter("Param6", System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToDateTime(fieldValue, XmlDateTimeSerializationMode.Unspecified);
return(newParameter);
case "Description":
newParameter = new System.Data.OleDb.OleDbParameter("Param7", System.Data.OleDb.OleDbType.VarChar, 1024, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
case "Discount":
newParameter = new System.Data.OleDb.OleDbParameter("Param8", System.Data.OleDb.OleDbType.Decimal, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToDecimal(fieldValue);
return(newParameter);
default:
throw new ArgumentException(string.Format(Resources.ErrorMessages_InvalidFieldNameForReport, fieldName, Constants.RTDVNames.ProductsPurchased));
}
}
public bool Contains(OleDbParameter value)
{
return(-1 != IndexOf(value));
}
private System.ComponentModel.Design.Serialization.InstanceDescriptor ConvertToInstanceDescriptor(OleDbParameter p)
{
int flags = 0;
if (p.ShouldSerializeOleDbType())
{
flags |= 1;
}
if (p.ShouldSerializeSize())
{
flags |= 2;
}
if (!ADP.IsEmpty(p.SourceColumn))
{
flags |= 4;
}
if (null != p.Value)
{
flags |= 8;
}
if ((ParameterDirection.Input != p.Direction) || p.IsNullable ||
p.ShouldSerializePrecision() || p.ShouldSerializeScale() ||
(DataRowVersion.Current != p.SourceVersion))
{
flags |= 16; // V1.0 everything
}
if (p.SourceColumnNullMapping)
{
flags |= 32; // v2.0 everything
}
Type[] ctorParams;
object?[] ctorValues;
switch (flags)
{
case 0: // ParameterName
case 1: // OleDbType
ctorParams = new Type[] { typeof(string), typeof(OleDbType) };
ctorValues = new object[] { p.ParameterName, p.OleDbType };
break;
case 2: // Size
case 3: // Size, OleDbType
ctorParams = new Type[] { typeof(string), typeof(OleDbType), typeof(int) };
ctorValues = new object[] { p.ParameterName, p.OleDbType, p.Size };
break;
case 4: // SourceColumn
case 5: // SourceColumn, OleDbType
case 6: // SourceColumn, Size
case 7: // SourceColumn, Size, OleDbType
ctorParams = new Type[] { typeof(string), typeof(OleDbType), typeof(int), typeof(string) };
ctorValues = new object[] { p.ParameterName, p.OleDbType, p.Size, p.SourceColumn };
break;
case 8: // Value
ctorParams = new Type[] { typeof(string), typeof(object) };
ctorValues = new object?[] { p.ParameterName, p.Value };
break;
default: // everything else
if (0 == (32 & flags))
{ // V1.0 everything
ctorParams = new Type[] {
typeof(string), typeof(OleDbType), typeof(int), typeof(ParameterDirection),
typeof(bool), typeof(byte), typeof(byte), typeof(string),
typeof(DataRowVersion), typeof(object)
};
ctorValues = new object?[] {
p.ParameterName, p.OleDbType, p.Size, p.Direction,
p.IsNullable, p.PrecisionInternal, p.ScaleInternal, p.SourceColumn,
p.SourceVersion, p.Value
};
}
else
{ // v2.0 everything - round trip all browsable properties + precision/scale
ctorParams = new Type[] {
typeof(string), typeof(OleDbType), typeof(int), typeof(ParameterDirection),
typeof(byte), typeof(byte),
typeof(string), typeof(DataRowVersion), typeof(bool),
typeof(object)
};
ctorValues = new object?[] {
p.ParameterName, p.OleDbType, p.Size, p.Direction,
p.PrecisionInternal, p.ScaleInternal,
p.SourceColumn, p.SourceVersion, p.SourceColumnNullMapping,
p.Value
};
}
break;
}
System.Reflection.ConstructorInfo ctor = typeof(OleDbParameter).GetConstructor(ctorParams) !;
return(new System.ComponentModel.Design.Serialization.InstanceDescriptor(ctor, ctorValues));
}
public void Insert(int index, OleDbParameter value)
{
Insert(index, (object)value);
}
public void Remove(OleDbParameter value)
{
Remove((object)value);
}
public OleDbParameter Add(OleDbParameter value) // MDAC 59206
{
Add((object)value);
return(value);
}
// known difference: when getting the parameters for a sproc, the
// return value gets marked as a return value but for a sql stmt
// the return value gets marked as an output parameter.
private static OleDbParameter[] DeriveParametersFromStoredProcedure(OleDbConnection connection, OleDbCommand command)
{
OleDbParameter[] plist = Array.Empty <OleDbParameter>();
if (connection.SupportSchemaRowset(OleDbSchemaGuid.Procedure_Parameters))
{
string quotePrefix, quoteSuffix;
connection.GetLiteralQuotes(ADP.DeriveParameters, out quotePrefix, out quoteSuffix);
object?[] parsed = MultipartIdentifier.ParseMultipartIdentifier(command.CommandText, quotePrefix, quoteSuffix, '.', 4, true, SR.OLEDB_OLEDBCommandText, false);
if (null == parsed[3])
{
throw ADP.NoStoredProcedureExists(command.CommandText);
}
object?[] restrictions = new object[4];
object value;
// Parse returns an enforced 4 part array
// 0) Server - ignored but removal would be a run-time breaking change from V1.0
// 1) Catalog
// 2) Schema
// 3) ProcedureName
// Restrictions array which is passed to OleDb API expects:
// 0) Catalog
// 1) Schema
// 2) ProcedureName
// 3) ParameterName (leave null)
// Copy from Parse format to OleDb API format
Array.Copy(parsed, 1, restrictions, 0, 3);
//if (cmdConnection.IsServer_msdaora) {
// restrictions[1] = Convert.ToString(cmdConnection.UserId).ToUpper();
//}
DataTable?table = connection.GetSchemaRowset(OleDbSchemaGuid.Procedure_Parameters, restrictions);
if (null != table)
{
DataColumnCollection columns = table.Columns;
DataColumn?parameterName = null;
DataColumn?parameterDirection = null;
DataColumn?dataType = null;
DataColumn?maxLen = null;
DataColumn?numericPrecision = null;
DataColumn?numericScale = null;
DataColumn?backendtype = null;
int index = columns.IndexOf(ODB.PARAMETER_NAME);
if (-1 != index)
{
parameterName = columns[index];
}
index = columns.IndexOf(ODB.PARAMETER_TYPE);
if (-1 != index)
{
parameterDirection = columns[index];
}
index = columns.IndexOf(ODB.DATA_TYPE);
if (-1 != index)
{
dataType = columns[index];
}
index = columns.IndexOf(ODB.CHARACTER_MAXIMUM_LENGTH);
if (-1 != index)
{
maxLen = columns[index];
}
index = columns.IndexOf(ODB.NUMERIC_PRECISION);
if (-1 != index)
{
numericPrecision = columns[index];
}
index = columns.IndexOf(ODB.NUMERIC_SCALE);
if (-1 != index)
{
numericScale = columns[index];
}
index = columns.IndexOf(ODB.TYPE_NAME);
if (-1 != index)
{
backendtype = columns[index];
}
DataRow[] dataRows = table.Select(null, ODB.ORDINAL_POSITION_ASC, DataViewRowState.CurrentRows);
plist = new OleDbParameter[dataRows.Length];
for (index = 0; index < dataRows.Length; ++index)
{
DataRow dataRow = dataRows[index];
OleDbParameter parameter = new OleDbParameter();
if ((null != parameterName) && !dataRow.IsNull(parameterName, DataRowVersion.Default))
{
// $CONSIDER - not trimming the @ from the beginning but to left the designer do that
parameter.ParameterName = Convert.ToString(dataRow[parameterName, DataRowVersion.Default], CultureInfo.InvariantCulture) !.TrimStart(new char[] { '@', ' ', ':' });
}
if ((null != parameterDirection) && !dataRow.IsNull(parameterDirection, DataRowVersion.Default))
{
short direction = Convert.ToInt16(dataRow[parameterDirection, DataRowVersion.Default], CultureInfo.InvariantCulture);
parameter.Direction = ConvertToParameterDirection(direction);
}
if ((null != dataType) && !dataRow.IsNull(dataType, DataRowVersion.Default))
{
// need to ping FromDBType, otherwise WChar->WChar when the user really wants VarWChar
short wType = Convert.ToInt16(dataRow[dataType, DataRowVersion.Default], CultureInfo.InvariantCulture);
parameter.OleDbType = NativeDBType.FromDBType(wType, false, false).enumOleDbType;
}
if ((null != maxLen) && !dataRow.IsNull(maxLen, DataRowVersion.Default))
{
parameter.Size = Convert.ToInt32(dataRow[maxLen, DataRowVersion.Default], CultureInfo.InvariantCulture);
}
switch (parameter.OleDbType)
{
case OleDbType.Decimal:
case OleDbType.Numeric:
case OleDbType.VarNumeric:
if ((null != numericPrecision) && !dataRow.IsNull(numericPrecision, DataRowVersion.Default))
{
// @devnote: unguarded cast from Int16 to Byte
parameter.PrecisionInternal = (byte)Convert.ToInt16(dataRow[numericPrecision], CultureInfo.InvariantCulture);
}
if ((null != numericScale) && !dataRow.IsNull(numericScale, DataRowVersion.Default))
{
// @devnote: unguarded cast from Int16 to Byte
parameter.ScaleInternal = (byte)Convert.ToInt16(dataRow[numericScale], CultureInfo.InvariantCulture);
}
break;
case OleDbType.VarBinary:
case OleDbType.VarChar:
case OleDbType.VarWChar:
value = dataRow[backendtype !, DataRowVersion.Default];
public void Insert(int index, OleDbParameter value)
{
this.Insert(index, value);
}
protected override OleDbParameter CreateOleDbParameter(string fieldName, string fieldValue)
{
OleDbParameter newParameter;
switch (fieldName)
{
case "intforeignid":
return(null);
//BirthDate
case "BirthDate":
newParameter = new System.Data.OleDb.OleDbParameter("BirthDate",
System.Data.OleDb.OleDbType.Date, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "BirthDate",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToDateTime(fieldValue, XmlDateTimeSerializationMode.Unspecified);
return(newParameter);
//HireDate
case "HireDate":
newParameter = new System.Data.OleDb.OleDbParameter("HireDate",
System.Data.OleDb.OleDbType.Date, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "HireDate",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToDateTime(fieldValue, XmlDateTimeSerializationMode.Unspecified);
return(newParameter);
//EmployeeID
case "EmployeeID":
newParameter = new System.Data.OleDb.OleDbParameter("EmployeeID",
System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "EmployeeID",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToInt32(fieldValue);
return(newParameter);
//ReportsTo
case "ReportsTo":
newParameter = new System.Data.OleDb.OleDbParameter("ReportsTo",
System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "ReportsTo",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToInt32(fieldValue);
return(newParameter);
//Notes
case "Notes":
newParameter = new System.Data.OleDb.OleDbParameter("Notes",
System.Data.OleDb.OleDbType.VarChar, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "Notes",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
//HomePhone
case "HomePhone":
newParameter = new System.Data.OleDb.OleDbParameter("HomePhone",
System.Data.OleDb.OleDbType.VarChar, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "HomePhone",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
//Extension
case "Extension":
newParameter = new System.Data.OleDb.OleDbParameter("Extension",
System.Data.OleDb.OleDbType.VarChar, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "Extension",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
//PhotoPath
case "PhotoPath":
newParameter = new System.Data.OleDb.OleDbParameter("PhotoPath",
System.Data.OleDb.OleDbType.VarChar, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "PhotoPath",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
//Region
case "Region":
newParameter = new System.Data.OleDb.OleDbParameter("Region",
System.Data.OleDb.OleDbType.VarChar, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "Region",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
//PostalCode
case "PostalCode":
newParameter = new System.Data.OleDb.OleDbParameter("PostalCode",
System.Data.OleDb.OleDbType.VarChar, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "PostalCode",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
//Country
case "Country":
newParameter = new System.Data.OleDb.OleDbParameter("Country",
System.Data.OleDb.OleDbType.VarChar, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "Country",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
//TitleOfCourtesy
case "TitleOfCourtesy":
newParameter = new System.Data.OleDb.OleDbParameter("TitleOfCourtesy",
System.Data.OleDb.OleDbType.VarChar, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "TitleOfCourtesy",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
//Address
case "Address":
newParameter = new System.Data.OleDb.OleDbParameter("Address",
System.Data.OleDb.OleDbType.VarChar, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "Address",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
//City
case "City":
newParameter = new System.Data.OleDb.OleDbParameter("City",
System.Data.OleDb.OleDbType.VarChar, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "City",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
//LastName
case "LastName":
newParameter = new System.Data.OleDb.OleDbParameter("LastName",
System.Data.OleDb.OleDbType.VarChar, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "LastName",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
//FirstName
case "FirstName":
newParameter = new System.Data.OleDb.OleDbParameter("FirstName",
System.Data.OleDb.OleDbType.VarChar, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "FirstName",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
//Title
case "Title":
newParameter = new System.Data.OleDb.OleDbParameter("Title",
System.Data.OleDb.OleDbType.VarChar, 0, System.Data.ParameterDirection.Input,
((byte)(19)), ((byte)(0)), "Title",
System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
default:
throw new ArgumentException(string.Format(Resources.ErrorMessages_InvalidFieldNameForReport, fieldName, Constants.RTDVNames.SalesInvoices));
}
}
public int IndexOf(OleDbParameter value)
{
return(IndexOf((object)value));
}
public void Remove(OleDbParameter value)
{
this.Remove(value);
}
protected override OleDbParameter CreateOleDbParameter(string fieldName, string fieldValue)
{
OleDbParameter newParameter;
switch (fieldName)
{
case "ItemCode":
case "ProductID":
newParameter = new System.Data.OleDb.OleDbParameter("ProductID", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "ProductID", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToInt32(fieldValue);
return(newParameter);
case "ProductName":
newParameter = new System.Data.OleDb.OleDbParameter("ProductName", System.Data.OleDb.OleDbType.WChar, 40, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "ProductName", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
case "UnitsInStock":
newParameter = new System.Data.OleDb.OleDbParameter("UnitsInStock", System.Data.OleDb.OleDbType.SmallInt, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "UnitsInStock", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToInt32(fieldValue);
return(newParameter);
case "UnitsOnOrder":
newParameter = new System.Data.OleDb.OleDbParameter("UnitsOnOrder", System.Data.OleDb.OleDbType.SmallInt, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "UnitsOnOrder", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToInt32(fieldValue);
return(newParameter);
case "UnitsOnStockAfterOrder":
newParameter = new System.Data.OleDb.OleDbParameter("Param4", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToInt32(fieldValue);
return(newParameter);
case "ReorderLevel":
newParameter = new System.Data.OleDb.OleDbParameter("ReorderLevel", System.Data.OleDb.OleDbType.SmallInt, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "ReorderLevel", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = XmlConvert.ToInt32(fieldValue);
return(newParameter);
case "CompanyName":
newParameter = new System.Data.OleDb.OleDbParameter("CompanyName", System.Data.OleDb.OleDbType.WChar, 40, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "CompanyName", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
case "ContactName":
newParameter = new System.Data.OleDb.OleDbParameter("ContactName", System.Data.OleDb.OleDbType.WChar, 30, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "ContactName", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
case "Address":
newParameter = new System.Data.OleDb.OleDbParameter("Address", System.Data.OleDb.OleDbType.WChar, 60, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "Address", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
case "City":
newParameter = new System.Data.OleDb.OleDbParameter("City", System.Data.OleDb.OleDbType.WChar, 15, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "City", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
case "Region":
newParameter = new System.Data.OleDb.OleDbParameter("Region", System.Data.OleDb.OleDbType.WChar, 15, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "Region", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
case "PostalCode":
newParameter = new System.Data.OleDb.OleDbParameter("PostalCode", System.Data.OleDb.OleDbType.WChar, 10, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "PostalCode", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
case "Country":
newParameter = new System.Data.OleDb.OleDbParameter("Country", System.Data.OleDb.OleDbType.WChar, 15, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "Country", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
case "Phone":
newParameter = new System.Data.OleDb.OleDbParameter("Phone", System.Data.OleDb.OleDbType.WChar, 24, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "Phone", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
case "Fax":
newParameter = new System.Data.OleDb.OleDbParameter("Fax", System.Data.OleDb.OleDbType.WChar, 24, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "Fax", System.Data.DataRowVersion.Current, false, null);
newParameter.Value = fieldValue;
return(newParameter);
default:
throw new ArgumentException(string.Format(Resources.ErrorMessages_InvalidFieldNameForReport, fieldName, Constants.RTDVNames.AdditionalStockDetails));
}
}
