public bool DeleteUser(string username)
{
bool isOk = false;
OleDbConnection conn = DBHelper.getConn(); //得到连接对象
conn.Open();
try
{
String strCommand = "DELETE FROM TB_USER where username = ?";
OleDbCommand command = new OleDbCommand(strCommand, conn);
OleDbParameterCollection paramCollection = command.Parameters;
OleDbParameter param1 = paramCollection.Add("username", OleDbType.VarChar);
param1.Value = username;
command.ExecuteNonQuery();
conn.Close();
isOk = true;
}
catch (Exception e)
{
conn.Close();
throw new Exception(e.Message);
}
return(isOk);
}
/// <summary>
/// Creates insert command.
/// </summary>
/// <param name="tableName">Table name.</param>
/// <param name="tableDescription">Table Description.</param>
/// <param name="fields">Fields to export.</param>
/// <returns>Insert command.</returns>
private OleDbCommand _CreateInsertCommand(string tableName,
TableDescription tableDescription,
ICollection <string> fields)
{
var command = new OleDbCommand();
var valueNames = new StringBuilder();
var values = new StringBuilder();
OleDbParameterCollection parameters = command.Parameters;
foreach (string field in fields)
{
if (!string.IsNullOrEmpty(valueNames.ToString()))
{
valueNames.Append(SQL_PARAM_SEPARATOR);
values.Append(SQL_PARAM_SEPARATOR);
}
FieldInfo info = tableDescription.GetFieldInfo(field);
Debug.Assert(null != info);
string realName = _FormatFieldName(field);
valueNames.Append(realName);
values.Append(SQL_VALUE_SYMBOL);
parameters.Add(field, info.Type, info.Size, field);
}
command.CommandText = string.Format(SQL_INSERT_COMMAND_FORMAT, tableName,
valueNames.ToString(), values.ToString());
return(command);
}
public void AddItem(IStyleGalleryItem istyleGalleryItem_0)
{
Guid guid;
object obj;
if (this.string_0 != "")
{
string str = this.method_2(istyleGalleryItem_0);
if (str.Length != 0)
{
IPersistStream item = (IPersistStream)istyleGalleryItem_0.Item;
IMemoryBlobStream memoryBlobStreamClass = new MemoryBlobStream();
(new ObjectStream()).Stream = memoryBlobStreamClass;
item.GetClassID(out guid);
item.Save(memoryBlobStreamClass, 1);
((IMemoryBlobStreamVariant)memoryBlobStreamClass).ExportToVariant(out obj);
Array arrays = (Array)obj;
byte[] numArray = new byte[arrays.Length + 16];
guid.ToByteArray().CopyTo(numArray, 0);
arrays.CopyTo(numArray, 16);
obj = numArray;
OleDbConnection oleDbConnection =
new OleDbConnection(string.Concat("Provider=Microsoft.Jet.OLEDB.4.0;Data source= ",
this.string_0));
oleDbConnection.Open();
OleDbCommand oleDbCommand = this.method_5(str, oleDbConnection);
OleDbParameterCollection parameters = oleDbCommand.Parameters;
parameters["Name"].Value = istyleGalleryItem_0.Name;
parameters["Category"].Value = istyleGalleryItem_0.Category;
parameters["Object"].Value = obj;
oleDbCommand.ExecuteNonQuery();
oleDbConnection.Close();
}
}
}
public DataSet FindAllInstance(int systemid, int instanceid)
{
OleDbConnection conn = DBHelper.getConn(); //得到连接对象
String strCommand = "SELECT a.indexid,a.pid,a.indexname,b.indexvalue FROM TB_INDEX a LEFT JOIN TB_INDEX_INSTANCE b ON a.systemid = b.systemid AND a.indexid = b.indexid WHERE a.systemid = ? AND b.instanceid =?";
OleDbCommand command = new OleDbCommand(strCommand, conn);
OleDbParameterCollection paramCollection = command.Parameters;
OleDbParameter param1 = paramCollection.Add("systemid", OleDbType.Integer);
param1.Value = systemid;
OleDbParameter param2 = paramCollection.Add("instanceid", OleDbType.Integer);
param2.Value = instanceid;
conn.Open();
try
{
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
DataSet sourceDataSet = new DataSet();
adapter.Fill(sourceDataSet);
conn.Close();
return(sourceDataSet);
}
catch (Exception e)
{
conn.Close();
throw new Exception(e.Message);
}
}
public DataSet Fill(string strQuery, string StrAlias, DataSet dsDataSet,
OleDbParameter[] paramArray, CommandType cmdType, out OleDbParameterCollection paramCol)
{
paramCol = null;
try
{
OleDbDataAdapter DA = new OleDbDataAdapter(strQuery, Conn);
DA.SelectCommand.CommandType = cmdType;
if (dsDataSet == null)
{
dsDataSet = new DataSet();
}
if (paramArray != null)
{
foreach (OleDbParameter param in paramArray)
{
DA.SelectCommand.Parameters.Add(param);
}
}
DA.Fill(dsDataSet);
paramCol = DA.SelectCommand.Parameters;
}
catch
{
throw;
}
return(dsDataSet);
}
/// <summary>
/// Builds an OleDbParametersCollection for an INSERT command, according to the DbTypeParameters in this collection.
/// </summary>
/// <param name="a_oParams">The OleDbParameterCollection to be filled.</param>
private void AddInsertCommandParameters(OleDbParameterCollection a_oParams)
{
foreach (DbTypeParameter l_oCurrent in this)
{
a_oParams.Add(l_oCurrent.ParameterName, l_oCurrent.Value);
}
}
public int TransactionQuery(string strQuery, OleDbParameter[] paramArray,
CommandType cmdType, out OleDbParameterCollection col)
{
int Li_RowCnt = 0;
try
{
OleDbCommand cmd = new OleDbCommand(strQuery, Conn);
cmd.CommandType = cmdType;
if (paramArray != null)
{
foreach (OleDbParameter param in paramArray)
{
cmd.Parameters.Add(param);
}
}
if (Trans == null)
{
BeginTrans();
}
cmd.Transaction = Trans;
Li_RowCnt = cmd.ExecuteNonQuery();
col = cmd.Parameters;
}
catch (Exception)
{
Li_RowCnt = -1;
throw;
}
return(Li_RowCnt);
}
public DataSet TransactionFill(string strQuery, string strAlias, DataSet dsDataSet,
OleDbParameter[] paramArray)
{
OleDbParameterCollection col = null;
return(TransactionFill(strQuery, strAlias, dsDataSet, paramArray, CommandType.Text, out col));
}
public DataSet Fill(string strQuery, string strAlias, DataSet dsDataSet,
OleDbParameter[] paramArray, CommandType cmdType)
{
OleDbParameterCollection col = null;
return(Fill(strQuery, strAlias, dsDataSet, paramArray, cmdType, out col));
}
public List <IndexInstance> FindDistinctIndexInstance(int systemid)
{
OleDbConnection conn = DBHelper.getConn(); //得到连接对象
String strCommand = "SELECT DISTINCT(instanceid),instancename FROM TB_INDEX_INSTANCE where systemid = ?";
OleDbCommand command = new OleDbCommand(strCommand, conn);
OleDbParameterCollection paramCollection = command.Parameters;
OleDbParameter param1 = paramCollection.Add("systemid", OleDbType.Integer);
param1.Value = systemid;
conn.Open();
OleDbDataReader reader;
reader = command.ExecuteReader(); //执行command并得到相应的DataReader
List <IndexInstance> list = new List <IndexInstance>();
while (reader.Read())
{
IndexInstance indexInstance = new IndexInstance();
//indexInstance.Indexid = int.Parse(reader["indexid"].ToString());
indexInstance.Instanceid = int.Parse(reader["instanceid"].ToString());
//indexInstance.Indexvalue = double.Parse(reader["indexvalue"].ToString());
indexInstance.Instancename = reader["instancename"].ToString();
list.Add(indexInstance);
}
reader.Close();
conn.Close();
return(list);
}
public virtual void FillInsertCmd(OleDbCommand cmd)
{
OleDbParameterCollection pc = cmd.Parameters;
pc["logTime"].Value = _logTime;
pc["event"].Value = _ev;
}
protected override void setParameters(OleDbParameterCollection parameters, Floorplan entity)
{
parameters.Add("@project_id", OleDbType.VarWChar, 255).Value = entity.project_id;
parameters.Add("@building_id", OleDbType.VarWChar, 255).Value = entity.building_id;
parameters.Add("@picture_id", OleDbType.VarWChar, 255).Value = entity.picture_id;
parameters.Add("@sale_picture_id", OleDbType.VarWChar, 255).Value = entity.sale_picture_id;
parameters.Add("@description", OleDbType.LongVarWChar).Value = entity.description;
parameters.Add("@type", OleDbType.SmallInt).Value = entity.type;
parameters.Add("@is_split", OleDbType.UnsignedTinyInt).Value = Utils.boolToByte(entity.is_split);
parameters.Add("@is_studio", OleDbType.UnsignedTinyInt).Value = Utils.boolToByte(entity.is_studio);
parameters.Add("@bedroom_count", OleDbType.SmallInt).Value = entity.bedroom_count;
parameters.Add("@bathroom_count", OleDbType.SmallInt).Value = entity.bathroom_count;
parameters.Add("@studyroom_count", OleDbType.SmallInt).Value = entity.studyroom_count;
parameters.Add("@open_baclony_count", OleDbType.SmallInt).Value = entity.open_baclony_count;
parameters.Add("@enclosed_baclony_count", OleDbType.SmallInt).Value = entity.enclosed_baclony_count;
parameters.Add("@courtyard_count", OleDbType.SmallInt).Value = entity.courtyard_count;
parameters.Add("@orientation", OleDbType.SmallInt).Value = entity.orientation;
parameters.Add("@internal_size", OleDbType.Double).Value = entity.internal_size;
parameters.Add("@external_size", OleDbType.Double).Value = entity.external_size;
parameters.Add("@total_size", OleDbType.Double).Value = entity.total_size;
parameters.Add("@land_size", OleDbType.Double).Value = entity.land_size;
parameters.Add("@removed", OleDbType.UnsignedTinyInt).Value = Utils.boolToByte(entity.removed);
if (!string.IsNullOrWhiteSpace(entity.id))
{
parameters.Add("@id", OleDbType.VarWChar, 255).Value = entity.id;
}
}
protected override void setQueryParameters(OleDbParameterCollection parameters, QueryCondition queryCondition)
{
MediaQueryCondition qc = (MediaQueryCondition)queryCondition;
if (!string.IsNullOrWhiteSpace(qc.url))
{
parameters.Add("@url", OleDbType.VarWChar, 255).Value = "%" + qc.url + "%";
}
if (!string.IsNullOrWhiteSpace(qc.floorplan_id))
{
parameters.Add("@floorplan_id", OleDbType.VarWChar, 255).Value = qc.floorplan_id;
}
else if (!string.IsNullOrWhiteSpace(qc.project_id))
{
if (!qc.project_id.Equals("-1"))
{
parameters.Add("@project_id", OleDbType.VarWChar, 255).Value = qc.project_id;
}
}
if (!string.IsNullOrWhiteSpace(qc.overview_project_id))
{
parameters.Add("@overview_project_id", OleDbType.VarWChar, 255).Value = qc.overview_project_id;
}
if (qc.media_type != 222)
{
parameters.Add("@media_type", OleDbType.UnsignedTinyInt).Value = qc.media_type;
}
if (qc.content_type != 222)
{
parameters.Add("@content_type", OleDbType.UnsignedTinyInt).Value = qc.content_type;
}
}
protected override void setQueryParameters(OleDbParameterCollection parameters, QueryCondition queryCondition)
{
ApartmentQueryCondition qc = (ApartmentQueryCondition)queryCondition;
if (!string.IsNullOrWhiteSpace(qc.unit_number))
{
parameters.Add("@unit_number", OleDbType.VarWChar, 255).Value = "%" + qc.unit_number + "%";
}
if (!string.IsNullOrWhiteSpace(qc.floorplan_id))
{
parameters.Add("@floorplan_id", OleDbType.VarWChar, 255).Value = qc.floorplan_id;
}
if (!string.IsNullOrWhiteSpace(qc.building_id))
{
parameters.Add("@building_id", OleDbType.VarWChar, 255).Value = qc.building_id;
}
else if (!string.IsNullOrWhiteSpace(qc.stage_id))
{
parameters.Add("@stage_id", OleDbType.VarWChar, 255).Value = qc.stage_id;
}
else if (!string.IsNullOrWhiteSpace(qc.project_id))
{
parameters.Add("@project_id", OleDbType.VarWChar, 255).Value = qc.project_id;
}
}
public DataSet FillDataSet(string strQuery
, string strAlias
, DataSet dsDataSet
, OleDbParameter[] paramArray
, CommandType cmdType
, out OleDbParameterCollection paramCol)
{
paramCol = null;
if (dsDataSet == null)
{
dsDataSet = new DataSet();
}
if (this._sFilePath != "")
{
mObjAdapter = new OleDbDataAdapter(strQuery, this.GetConnStr());
if (paramArray != null)
{
foreach (OleDbParameter parameter in paramArray)
{
this.mObjAdapter.SelectCommand.Parameters.Add(parameter);
}
}
mObjAdapter.Fill(dsDataSet, strAlias);
paramCol = mObjAdapter.SelectCommand.Parameters;
}
return(dsDataSet);
}
public bool ChangePwd(String username, String password)
{
bool isOk = false;
OleDbConnection conn = DBHelper.getConn(); //得到连接对象
conn.Open();
try
{
String strCommand = "UPDATE TB_USER SET pwd = ? where username = ?";
OleDbCommand command = new OleDbCommand(strCommand, conn);
OleDbParameterCollection paramCollection = command.Parameters;
OleDbParameter param1 = paramCollection.Add("pwd", OleDbType.VarChar);
param1.Value = password;
OleDbParameter param2 = paramCollection.Add("username", OleDbType.VarChar);
param2.Value = username;
command.ExecuteNonQuery();
conn.Close();
isOk = true;
}
catch (Exception e)
{
conn.Close();
throw new Exception(e.Message);
}
return(isOk);
}
public int ExecuteNonQuery(string strQuery, OleDbParameter paramArray,
CommandType cmdType, out OleDbParameterCollection col)
{
int Li_RowCnt = 0;
col = null;
try
{
OleDbCommand cmd = new OleDbCommand(strQuery, Conn);
cmd.CommandType = cmdType;
if (paramArray != null)
{
cmd.Parameters.Add(paramArray);
}
//if (Conn.State == ConnectionState.Closed)
//{
// Conn.Open();
//}
Li_RowCnt = cmd.ExecuteNonQuery();
col = cmd.Parameters;
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
return(Li_RowCnt);
}
public DataSet FindAllIndex(int systemid)
{
OleDbConnection conn = DBHelper.getConn(); //得到连接对象
String strCommand = "SELECT * FROM TB_INDEX where systemid = ?";
OleDbCommand command = new OleDbCommand(strCommand, conn);
OleDbParameterCollection paramCollection = command.Parameters;
OleDbParameter param1 = paramCollection.Add("systemid", OleDbType.Integer);
param1.Value = systemid;
conn.Open();
try
{
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
DataSet sourceDataSet = new DataSet();
adapter.Fill(sourceDataSet);
conn.Close();
return(sourceDataSet);
}
catch (Exception e)
{
conn.Close();
throw new Exception(e.Message);
}
}
public bool AddUser(string username, string password)
{
bool isOk = false;
OleDbConnection conn = DBHelper.getConn(); //得到连接对象
conn.Open();
try
{
String strCommand = "INSERT INTO TB_USER (username,pwd) VALUES (?,?)";
OleDbCommand command = new OleDbCommand(strCommand, conn);
OleDbParameterCollection paramCollection = command.Parameters;
OleDbParameter param1 = paramCollection.Add("username", OleDbType.VarChar);
param1.Value = username;
OleDbParameter param2 = paramCollection.Add("pwd", OleDbType.VarChar);
param2.Value = password;
command.ExecuteNonQuery();
conn.Close();
isOk = true;
}
catch (Exception e)
{
conn.Close();
throw new Exception(e.Message);
}
return(isOk);
}
// <Snippet1>
public void CreateParameters(OleDbConnection connection)
{
OleDbCommand command = new OleDbCommand(
"SELECT * FROM Customers WHERE CustomerID = ?", connection);
OleDbParameterCollection paramCollection = command.Parameters;
OleDbParameter parameter = paramCollection.Add(
"CustomerID", OleDbType.VarChar, 5, "CustomerID");
}
protected override void setQueryParameters(OleDbParameterCollection parameters, QueryCondition queryCondition)
{
StageQueryCondition qc = (StageQueryCondition)queryCondition;
if (!string.IsNullOrWhiteSpace(qc.project_id))
{
parameters.Add("@project_id", OleDbType.VarWChar, 255).Value = qc.project_id;
}
}
public void FillInsertCmd(OleDbCommand cmd)
{
OleDbParameterCollection pc = cmd.Parameters;
pc["eventTime"].Value = _eventTime;
pc["system"].Value = _system;
pc["category"].Value = _category;
pc["message"].Value = _message;
}
private int GetCountOfSharedState(string path)
{
string getSharedStateCount =
"SELECT COUNT(*)" +
" FROM aspnet_PagePersonalizationAllUsers AllUsers, aspnet_Paths Paths" +
" WHERE AllUsers.PathId = Paths.PathId AND Paths.ApplicationId = @ApplicationId";
AccessConnectionHolder connectionHolder = null;
OleDbConnection connection = null;
int count = 0;
try
{
try
{
connectionHolder = GetConnectionHolder();
connection = connectionHolder.Connection;
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
OleDbParameterCollection parameters = command.Parameters;
int appId = GetApplicationID(connectionHolder);
parameters.AddWithValue("ApplicationId", appId);
if (path != null)
{
getSharedStateCount += " AND Paths.Path LIKE '%'+@Path+'%'";
OleDbParameter parameter = parameters.Add("Path", OleDbType.WChar);
parameter.Value = path;
}
command.CommandText = getSharedStateCount;
object result = command.ExecuteScalar();
if ((result != null) && (result is Int32))
{
count = (Int32)result;
}
}
finally
{
if (connectionHolder != null)
{
connectionHolder.Close();
connectionHolder = null;
}
}
}
catch
{
throw;
}
return(count);
}
// <summary>
// Calls Clear() on the OleDbParameterCollection and performs
// a check to make sure it succeeds. If it does not succeed
// an exception will be thrown.
// </summary>
public static void SafeClear(this OleDbParameterCollection @this)
{
// Call the method.
@this.Clear();
// Do the sanity check
if (@this.Count != 0)
{
throw new Exception(@"OleDbParameterCollection.Clear() failed.");
}
}
public static OleDbParameterCollection CreateParameterCollection(List <KeyValuePair <string, object> > parameters)
{
OleDbParameterCollection pc = OleDAL.Connection.CreateCommand().Parameters;
foreach (KeyValuePair <string, object> para in parameters)
{
pc.AddWithValue(para.Key, para.Value);
}
return(pc);
}
private void simpleButton1_Click(object sender, EventArgs e)
{
if (textEdit1.Text.Trim() == "")
{
MessageBox.Show("请输入对应名称", "错误", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//添加进数据库
OleDbConnection conn = DBHelper.getConn(); //得到连接对象
conn.Open();
int indexid1;
try
{
int tmp = int.Parse(MainForm.pCurrentWin.treeList1.FocusedNode.GetValue("indexid").ToString());
//根据指标体编号
String strCommand1 = "SELECT * FROM TB_INDEX t WHERE t.indexid = " + tmp;
OleDbCommand myCommand1 = new OleDbCommand(strCommand1, conn);
OleDbDataReader reader;
reader = myCommand1.ExecuteReader(); //执行command并得到相应的DataReader
reader.Read();
//int indexid = int.Parse(reader["indexid"].ToString());//指标编号
int indexsys = int.Parse(reader["systemid"].ToString());//指标体系编号
reader.Close();
//加入指标表
String strCommand = "INSERT INTO TB_INDEX (pid,indexname,systemid) VALUES (?,?,?)";
OleDbCommand command = new OleDbCommand(strCommand, conn);
OleDbParameterCollection paramCollection = command.Parameters;
OleDbParameter param1 = paramCollection.Add("pid", OleDbType.VarChar);
param1.Value = tmp;
OleDbParameter param2 = paramCollection.Add("indexname", OleDbType.VarChar);
param2.Value = textEdit1.Text;
OleDbParameter param3 = paramCollection.Add("systemid", OleDbType.VarChar);
param3.Value = indexsys;
command.ExecuteNonQuery();
//根据指标体系名查找指标体系编号
string strCommand2 = "select max(indexid) from TB_INDEX";
OleDbCommand myCommand2 = new OleDbCommand(strCommand2, conn);
object o = myCommand2.ExecuteScalar();
indexid1 = int.Parse(o.ToString());
}
catch (System.Exception ex)
{
conn.Close();
throw new Exception("[ERROR] 数据库操作出现异常：" + ex.Message);
}
MainForm.pCurrentWin.treeList1.BeginUnboundLoad();
MainForm.pCurrentWin.treeList1.AppendNode(new object[] { indexid1, MainForm.pCurrentWin.nodeid, textEdit1.Text }, MainForm.pCurrentWin.nodeid);
MainForm.pCurrentWin.treeList1.EndUnboundLoad();
MainForm.pCurrentWin.SetImageIndex(MainForm.pCurrentWin.treeList1, null, 20, 21);
conn.Close();
this.Close();
}
private OleDbCommand method_5(string string_1, OleDbConnection oleDbConnection_0)
{
string str = string.Concat("INSERT INTO [", string_1, "] ([Name], [Category], [Object]) Values (?, ?, ?)");
OleDbCommand oleDbCommand = new OleDbCommand(str, oleDbConnection_0);
OleDbParameterCollection parameters = oleDbCommand.Parameters;
parameters.Add("Name", OleDbType.Char);
parameters.Add("Category", OleDbType.Char);
parameters.Add("Object", OleDbType.Binary);
return(oleDbCommand);
}
public void FillInsertCmd(OleDbCommand cmd)
{
OleDbParameterCollection pc = cmd.Parameters;
pc["receivedTime"].Value = _e.Message.MessageTime;
pc["address"].Value = _e.SourceAddress.ToString();
pc["localTime"].Value = _e.Message.LocalTime;
pc["facility"].Value = (int)_e.Message.Facility;
pc["severity"].Value = (int)_e.Message.Severity;
pc["message"].Value = _e.Message.Message;
}
public static void AddNullableParam(OleDbParameterCollection parameters, long?value, string paramName)
{
if (value == null)
{
parameters.AddWithValue(paramName, DBNull.Value);
}
else
{
parameters.AddWithValue(paramName, value);
}
}
void SetParameterValue(OleDbParameterCollection Params, object value, OleDbType type)
{
if (value != null)
{
Params.Add("?", type).Value = value;
}
else
{
Params.AddWithValue("?", DBNull.Value);
}
}
