/// <summary>
/// 将数据导出至Excel文件
/// </summary>
/// <param name="Table">DataTable对象</param>
/// <param name="ExcelFilePath">Excel文件路径</param>
public static bool OutputToExcel(DataTable Table, string ExcelFilePath)
{
if (File.Exists(ExcelFilePath))
{
throw new Exception("该文件已经存在！");
}
if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table"))
{
Table.TableName = "Sheet1";
}
//数据表的列数
int ColCount = Table.Columns.Count;
//用于记数，实例化参数时的序号
int i = 0;
//创建参数
OleDbParameter[] para = new OleDbParameter[ColCount];
//创建表结构的SQL语句
string TableStructStr = @"Create Table " + Table.TableName + "(";
//连接字符串
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(connString);
//创建表结构
OleDbCommand objCmd = new OleDbCommand();
//数据类型集合
ArrayList DataTypeList = new ArrayList();
DataTypeList.Add("System.Decimal");
DataTypeList.Add("System.Double");
DataTypeList.Add("System.Int16");
DataTypeList.Add("System.Int32");
DataTypeList.Add("System.Int64");
DataTypeList.Add("System.Single");
//遍历数据表的所有列，用于创建表结构
foreach (DataColumn col in Table.Columns)
{
//如果列属于数字列，则设置该列的数据类型为double
if (DataTypeList.IndexOf(col.DataType.ToString()) >= 0)
{
para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.Double);
objCmd.Parameters.Add(para[i]);
//如果是最后一列
if (i + 1 == ColCount)
{
TableStructStr += col.ColumnName + " double)";
}
else
{
TableStructStr += col.ColumnName + " double,";
}
}
else
{
para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.VarChar);
objCmd.Parameters.Add(para[i]);
//如果是最后一列
if (i + 1 == ColCount)
{
TableStructStr += col.ColumnName + " varchar)";
}
else
{
TableStructStr += col.ColumnName + " varchar,";
}
}
i++;
}
//创建Excel文件及文件结构
try
{
objCmd.Connection = objConn;
objCmd.CommandText = TableStructStr;
if (objConn.State == ConnectionState.Closed)
{
objConn.Open();
}
objCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
throw exp;
}
//插入记录的SQL语句
string InsertSql_1 = "Insert into " + Table.TableName + " (";
string InsertSql_2 = " Values (";
string InsertSql = "";
//遍历所有列，用于插入记录，在此创建插入记录的SQL语句
for (int colID = 0; colID < ColCount; colID++)
{
if (colID + 1 == ColCount) //最后一列
{
InsertSql_1 += Table.Columns[colID].ColumnName + ")";
InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ")";
}
else
{
InsertSql_1 += Table.Columns[colID].ColumnName + ",";
InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ",";
}
}
InsertSql = InsertSql_1 + InsertSql_2;
//遍历数据表的所有数据行
for (int rowID = 0; rowID < Table.Rows.Count; rowID++)
{
for (int colID = 0; colID < ColCount; colID++)
{
if (para[colID].DbType == DbType.Double && Table.Rows[rowID][colID].ToString().Trim() == "")
{
para[colID].Value = 0;
}
else
{
para[colID].Value = Table.Rows[rowID][colID].ToString().Trim();
}
}
try
{
objCmd.CommandText = InsertSql;
objCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
string str = exp.Message;
}
}
try
{
if (objConn.State == ConnectionState.Open)
{
objConn.Close();
}
}
catch (Exception exp)
{
throw exp;
}
return(true);
}
protected string UpdateFuelData(string vin, string status, DataRow drMain, DataTable dtPam, OleDbConnection con)
{
string message = string.Empty;
OleDbTransaction tra = null; //创建事务，开始执行事务
try
{
tra = con.BeginTransaction();
#region 更新基本信息表
string sqlUpdateBasic = @"UPDATE FC_CLJBXX SET
USER_ID=@USER_ID,QCSCQY=@QCSCQY,JKQCZJXS=@JKQCZJXS,CLXH=@CLXH,CLZL=@CLZL,
RLLX=@RLLX,ZCZBZL=@ZCZBZL,ZGCS=@ZGCS,LTGG=@LTGG,ZJ=@ZJ,
TYMC=@TYMC,YYC=@YYC,ZWPS=@ZWPS,ZDSJZZL=@ZDSJZZL,EDZK=@EDZK,LJ=@LJ,
QDXS=@QDXS,JYJGMC=@JYJGMC,JYBGBH=@JYBGBH,STATUS=@STATUS,UPDATETIME=@UPDATETIME,
HGSPBM=@HGSPBM,QTXX=@QTXX
WHERE VIN=@VIN";
OleDbParameter upTime = new OleDbParameter("@UPDATETIME", DateTime.Now);
upTime.OleDbType = OleDbType.DBDate;
OleDbParameter[] parameters =
{
new OleDbParameter("@USER_ID", drMain["UPDATE_BY"].ToString()),
new OleDbParameter("@QCSCQY", drMain["QCSCQY"].ToString()),
new OleDbParameter("@JKQCZJXS", drMain["JKQCZJXS"].ToString()),
new OleDbParameter("@CLXH", drMain["CLXH"].ToString()),
new OleDbParameter("@CLZL", drMain["CLZL"].ToString()),
new OleDbParameter("@RLLX", drMain["RLLX"].ToString()),
new OleDbParameter("@ZCZBZL", drMain["ZCZBZL"].ToString()),
new OleDbParameter("@ZGCS", drMain["ZGCS"].ToString()),
new OleDbParameter("@LTGG", drMain["LTGG"].ToString()),
new OleDbParameter("@ZJ", drMain["ZJ"].ToString()),
new OleDbParameter("@TYMC", drMain["TYMC"].ToString()),
new OleDbParameter("@YYC", drMain["YYC"].ToString()),
new OleDbParameter("@ZWPS", drMain["ZWPS"].ToString()),
new OleDbParameter("@ZDSJZZL", drMain["ZDSJZZL"].ToString()),
new OleDbParameter("@EDZK", drMain["EDZK"].ToString()),
new OleDbParameter("@LJ", drMain["LJ"].ToString()),
new OleDbParameter("@QDXS", drMain["QDXS"].ToString()),
new OleDbParameter("@JYJGMC", drMain["JYJGMC"].ToString()),
new OleDbParameter("@JYBGBH", drMain["JYBGBH"].ToString()),
new OleDbParameter("@STATUS", status),
upTime,
new OleDbParameter("@HGSPBM", drMain["HGSPBM"].ToString()),
new OleDbParameter("@QTXX", drMain["CT_QTXX"].ToString()),
new OleDbParameter("@VIN", vin)
};
AccessHelper.ExecuteNonQuery(tra, sqlUpdateBasic, parameters);
#endregion
#region 插入参数信息
// 更新燃料参数表
foreach (DataRow drParam in dtPam.Rows)
{
string paramCode = drParam["PARAM_CODE"].ToString().Trim();
string sqlUpdateParam = @"UPDATE RLLX_PARAM_ENTITY
SET PARAM_VALUE=@PARAM_VALUE
WHERE VIN=@VIN AND PARAM_CODE=@PARAM_CODE";
OleDbParameter[] paramList =
{
new OleDbParameter("@PARAM_VALUE", drMain[paramCode]),
new OleDbParameter("@VIN", vin),
new OleDbParameter("@PARAM_CODE", paramCode)
};
AccessHelper.ExecuteNonQuery(tra, sqlUpdateParam, paramList);
}
tra.Commit();
#endregion
}
catch (Exception ex)
{
tra.Rollback();
message = ex.Message + "\r\n";
}
return(message);
}
private void button1_Click(object sender, EventArgs e)
{
try{
String NombreCadena, NombreSucursal;
//1- Hcer la conexión a la BD de Oracle
cnOracle = new OleDbConnection("Provider=MSDAORA; Data Source=xe;" +
"User ID=System;Password=gonbar");
/*
* cnOracle = new OleDbConnection("Provider=MSDAORA; Data Source=oracle;" +
* "User ID=BD03;Password=gonbar");
*/
cnOracle.Open();
OleDbCommand funcionAlmacenado;
OleDbParameter salida, parametro1, parametro2;
int cant;
//1- Abrir la conexión a la BD.
cnOracle = new OleDbConnection("Provider=MSDAORA; Data Source=xe;" +
"User ID=System;Password=gonbar");
/*
* cnOracle = new OleDbConnection("Provider=MSDAORA; Data Source=oracle;" +
* "User ID=BD03;Password=gonbar");
*/
cnOracle.Open();
funcionAlmacenado = new OleDbCommand();
funcionAlmacenado.Connection = cnOracle;
//2- Especificar el llamado a la función (en general: al subprograma).
funcionAlmacenado.CommandText = "CantidadProductos";
funcionAlmacenado.CommandType = CommandType.StoredProcedure;
//3- Especificar los parámetros:
//a) primero todos los de salida (uno en este caso):
salida = new OleDbParameter("RETURN_VALUE", OleDbType.Integer,
4, ParameterDirection.ReturnValue, false, 4, 0, "NombreCadena" + "NombreSucursal", DataRowVersion.Current, 0);
funcionAlmacenado.Parameters.Add(salida);
//b) Luego todos los de entrada:
NombreCadena = comboBox1.SelectedItem.ToString();
parametro1 = new OleDbParameter("NombreCad", NombreCadena);
NombreSucursal = comboBox2.SelectedItem.ToString();
parametro2 = new OleDbParameter("NombreSuc", NombreSucursal);
funcionAlmacenado.Parameters.Add(parametro1);
funcionAlmacenado.Parameters.Add(parametro2);
//4- Ejecutar la función (en general: el subprograma).
funcionAlmacenado.ExecuteNonQuery();
//5- Recuperar el (los) valor(es) regresado(s) por medio del (de los)
// parámetro(s) de salida.
cant = Convert.ToInt16(funcionAlmacenado.Parameters["RETURN_VALUE"].Value);
MessageBox.Show("Cadena: " + NombreCadena + ", Sucursal: " + NombreSucursal +
", Cantidad de Articulos: " + cant);
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
comboBox1.SelectedIndex = -1;
comboBox2.SelectedIndex = -1;
//6- Cerrar la conexión a la BD.
cnOracle.Close();
}
internal void povecaj(string slovo, string stupac)
{
try
{
connection.Open();
OleDbCommand command = new OleDbCommand("select * from Slova where Slovo = @slovo", connection);
OleDbParameter parameter = new OleDbParameter();
parameter.ParameterName = "@slovo";
if (slovo == " ")
{
parameter.Value = "space";
}
else
{
parameter.Value = slovo;
}
command.Parameters.Add(parameter);
reader = command.ExecuteReader();
while (reader.Read())
{
procitano = (int)reader[stupac];
}
procitano++;
OleDbCommand command1 = new OleDbCommand("update Slova set " + stupac + " = @broj where Slovo = @slovo", connection);
OleDbParameter parameter2 = new OleDbParameter();
parameter2.ParameterName = "@broj";
parameter2.Value = procitano;
command1.Parameters.Add(parameter2);
OleDbParameter parameter1 = new OleDbParameter();
parameter1.ParameterName = "@slovo";
if (slovo == " ")
{
parameter1.Value = "space";
}
else
{
parameter1.Value = slovo;
}
command1.Parameters.Add(parameter1);
command1.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
finally
{
if (reader != null)
{
reader.Close();
}
if (connection != null)
{
connection.Close();
}
}
//trebam srediti i omjer sada
try
{
int tocno = 0, netocno = 0;
double omjer;
connection.Open();
OleDbCommand command = new OleDbCommand("select * from Slova where Slovo = @slovo", connection);
OleDbParameter parameter = new OleDbParameter();
parameter.ParameterName = "@slovo";
if (slovo == " ")
{
parameter.Value = "space";
}
else
{
parameter.Value = slovo;
}
command.Parameters.Add(parameter);
reader = command.ExecuteReader();
while (reader.Read())
{
tocno = (int)reader["Tocno"];
netocno = (int)reader["Netocno"];
}
if (tocno + netocno != 0)
{
omjer = Math.Round((double)(tocno * 100) / (tocno + netocno), 2);
}
else
{
omjer = -1;
}
OleDbCommand command2 = new OleDbCommand("update Slova set omjer = @omjer where Slovo = @slovo", connection);
OleDbParameter parameter3 = new OleDbParameter();
parameter3.ParameterName = "@omjer";
parameter3.Value = omjer;
command2.Parameters.Add(parameter3);
OleDbParameter parameter4 = new OleDbParameter();
parameter4.ParameterName = "@slovo";
if (slovo == " ")
{
parameter4.Value = "space";
}
else
{
parameter4.Value = slovo;
}
command2.Parameters.Add(parameter4);
command2.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
finally
{
if (reader != null)
{
reader.Close();
}
if (connection != null)
{
connection.Close();
}
}
}
///<summary>
/// Execute a stocked procedure.
/// <param name="schema">
/// <see cref="SharpQuery.SchemaClass">SchemaClass</see> object.
/// </param>
/// <param name="rows">
/// Maximum number of row to extract. If is "0" then all rows are extracted.
/// </param>
/// <returns> return a <see cref="System.Data.DataTable">DataTable</see>
///or a <see cref="System.Data.DataSet">DataSet</see> object.
/// </returns> /// </summary>
public override object ExecuteProcedure(ISchemaClass schema, int rows, SharpQuerySchemaClassCollection parameters)
{
DataSet returnValues = null;
if (schema == null)
{
throw new System.ArgumentNullException("schema");
}
OleDbCommand command = new OleDbCommand();
OleDbParameter para = null;
returnValues = new DataSet();
command.Connection = this.pOLEConnection;
command.CommandText = schema.Name;
command.CommandType = System.Data.CommandType.StoredProcedure;
if (parameters != null)
{
foreach (SharpQueryParameter classParam in parameters)
{
para = new OleDbParameter();
para.DbType = classParam.DataType;
para.Direction = (ParameterDirection)classParam.Type;
para.ParameterName = classParam.Name;
if (para.ParameterName.StartsWith("["))
{
para.ParameterName = para.ParameterName.Remove(0, 1);
}
if (para.ParameterName.EndsWith("]"))
{
para.ParameterName = para.ParameterName.Remove(para.ParameterName.Length - 1, 1);
}
para.Value = classParam.Value;
command.Parameters.Add(para);
}
}
// command.Prepare();
command.Transaction = this.pOLEConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
try
{
this.pOLEAdapter.SelectCommand = command;
this.pOLEAdapter.Fill(returnValues);
}
catch (OleDbException e)
{
command.Transaction.Rollback();
string mes = schema.Name + "\n\r";
foreach (OleDbError err in e.Errors)
{
mes += "-----------------\n\r";
mes += err.Message + "\n\r";
mes += err.NativeError + "\n\r";
}
throw new ExecuteProcedureException(mes);
}
catch (System.Exception e)
{
command.Transaction.Rollback();
throw new ExecuteProcedureException(e.Message);
}
command.Transaction.Commit();
foreach (DataTable table in returnValues.Tables)
{
//readonly
table.DefaultView.AllowDelete = false;
table.DefaultView.AllowEdit = false;
table.DefaultView.AllowNew = false;
}
return(returnValues);
}
/// <summary>
/// 将列表数据导入到Excel中
/// </summary>
/// <param name="templateExcelFileName"></param>
/// <param name="sheetName"></param>
/// <param name="fieldNames"></param>
/// <param name="datas"></param>
/// <param name="destExcelFileName"></param>
public static void Export2Excel(string templateExcelFileName, string sheetName, string[] fieldNames, IList <string[]> datas, string destExcelFileName)
{
if (null == fieldNames ||
fieldNames.Length == 0 ||
null == datas ||
datas.Count == 0)
{
return;
}
if (fieldNames.Length > datas[0].Length)
{
throw new ArgumentOutOfRangeException("fieldNames", "数据的字段数不能小于模板的字段数");
}
System.IO.File.Copy(templateExcelFileName, destExcelFileName, true);
string strConn = String.Format(EXCEL_WRITE_CONN_STR, destExcelFileName);
OleDbConnection conn = new OleDbConnection(strConn);
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
int fieldLength = fieldNames.Length;
string[] arrParms = new string[fieldLength];
for (int i = 0; i < fieldLength; i++)
{
arrParms[i] = "@" + i.ToString();
OleDbParameter pa = new OleDbParameter();
pa.ParameterName = arrParms[i];
cmd.Parameters.Add(pa);
}
string fields = StringUtils.Join(fieldNames, "[", "]", ",");
string parms = StringUtils.Join(arrParms, ",");
cmd.CommandText = String.Format("insert into [{0}$] ({1}) values ({2})", sheetName, fields, parms);
using (conn)
{
conn.Open();
using (OleDbTransaction trans = conn.BeginTransaction())
{
cmd.Transaction = trans;
try
{
for (int i = 0, icount = datas.Count; i < icount; i++)
{
string[] arr = datas[i];
for (int j = 0; j < fieldLength; j++)
{
cmd.Parameters[arrParms[j]].Value = arr[j];
}
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (SqlException ex)
{
if (null != trans)
{
trans.Rollback();
}
throw ex;
}
}
}
}
public override void PrepareCommand(DbCommand cmd)
{
base.PrepareCommand(cmd);
foreach (DbParameter p in cmd.Parameters)
{
if (p.Direction == ParameterDirection.Output || p.Direction == ParameterDirection.ReturnValue)
{
continue;
}
object value = p.Value;
if (value == DBNull.Value)
{
continue;
}
Type type = value.GetType();
OleDbParameter oleDbParam = (OleDbParameter)p;
if (oleDbParam.DbType != DbType.Guid && type == typeof(Guid))
{
oleDbParam.OleDbType = OleDbType.Char;
oleDbParam.Size = 36;
continue;
}
if ((p.DbType == DbType.Time || p.DbType == DbType.DateTime) && type == typeof(TimeSpan))
{
oleDbParam.OleDbType = OleDbType.Double;
oleDbParam.Value = ((TimeSpan)value).TotalDays;
continue;
}
if (type == typeof(Boolean))
{
p.Value = ((bool)value).ToString();
continue;
}
switch (p.DbType)
{
case DbType.Binary:
if (((byte[])value).Length > 2000)
{
oleDbParam.OleDbType = OleDbType.LongVarBinary;
}
break;
case DbType.Time:
oleDbParam.OleDbType = OleDbType.LongVarWChar;
p.Value = value.ToString();
break;
case DbType.DateTime:
oleDbParam.OleDbType = OleDbType.LongVarWChar;
p.Value = value.ToString();
break;
case DbType.AnsiString:
if (value.ToString().Length > 4000)
{
oleDbParam.OleDbType = OleDbType.LongVarChar;
}
break;
case DbType.String:
if (value.ToString().Length > 2000)
{
oleDbParam.OleDbType = OleDbType.LongVarWChar;
}
break;
case DbType.Object:
oleDbParam.OleDbType = OleDbType.LongVarWChar;
p.Value = SerializationManager.Serialize(value);
break;
}
}
//replace "N'" to "'"
cmd.CommandText = cmd.CommandText.Replace("N'", "'");
//replace msaccess specific function names in cmdText
cmd.CommandText = cmd.CommandText.Replace("upper(", "ucase(")
.Replace("lower(", "lcase(")
.Replace("substring(", "mid(")
.Replace("getdate()", "date() + time()")
.Replace("datepart(year", "datepart('yyyy'")
.Replace("datepart(month", "datepart('m'")
.Replace("datepart(day", "datepart('d'");
//replace CHARINDEX with INSTR and reverse seqeunce of param items in CHARINDEX()
int startIndexOfCharIndex = cmd.CommandText.IndexOf("charindex(");
while (startIndexOfCharIndex > 0)
{
int endIndexOfCharIndex = DataUtils.GetEndIndexOfMethod(cmd.CommandText, startIndexOfCharIndex + "charindex(".Length);
string[] itemsInCharIndex = DataUtils.SplitTwoParamsOfMethodBody(
cmd.CommandText.Substring(startIndexOfCharIndex + "charindex(".Length,
endIndexOfCharIndex - startIndexOfCharIndex - "charindex(".Length));
cmd.CommandText = cmd.CommandText.Substring(0, startIndexOfCharIndex)
+ "instr(" + itemsInCharIndex[1] + "," + itemsInCharIndex[0] + ")"
+ (cmd.CommandText.Length - 1 > endIndexOfCharIndex ?
cmd.CommandText.Substring(endIndexOfCharIndex + 1) : "");
startIndexOfCharIndex = cmd.CommandText.IndexOf("charindex(");
}
}
/// <summary>
/// 返回人员统计列表
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public List <PersonPlanStatisticData> GetPersonRPStatByDate(string fromDate, string toDate)
{
// SELECT nameid, name, INCIDENT as type, sum(FRACTION) as statisticcount FROM PersonRewardPunishInfo
//where Format(RPTIME,"yyyy-mm-dd") <= "2012/10/12"
//group by NAMEID, NAME, INCIDENT order by NAME, INCIDENT
if (string.IsNullOrEmpty(fromDate) == false)
{
GetPersonRPStatByDateSql += " and Format(RPTIME,\"yyyy-mm-dd\") >= \'" + fromDate + "'";
}
if (string.IsNullOrEmpty(toDate) == false)
{
GetPersonRPStatByDateSql += " and Format(RPTIME,\"yyyy-mm-dd\") <= \'" + toDate + "'";
}
GetPersonRPStatByDateSql += " group by NAMEID, NAME, INCIDENT order by NAME, INCIDENT ";
DataSet ds = DbHelperACE.Query(GetPersonRPStatByDateSql);
List <PersonPlanStatisticData> PersonPlanList = new List <PersonPlanStatisticData>();
DataRowCollection drs = ds.Tables[0].Rows;
for (int i = 0; i < drs.Count; i++)
{
PersonPlanStatisticData item = new PersonPlanStatisticData();
item.AddressGuid = drs[i]["nameid"].ToString().Trim();
item.Address = drs[i]["name"].ToString().Trim();
item.Type = drs[i]["type"].ToString().Trim();
if (drs[i]["statisticcount"] != DBNull.Value)
{
item.Count = Double.Parse(drs[i]["statisticcount"].ToString().Trim());
}
PersonPlanList.Add(item);
}
// and Format(RPTIME,'yyyy - mm - dd') >= @fromDate
//and Format(RPTIME,'yyyy - mm - dd') <= @toDate
List <OleDbParameter> paramList = new List <OleDbParameter>();
OleDbParameter param = new OleDbParameter("@fromDate", OleDbType.LongVarWChar);
param.Value = fromDate;
paramList.Add(param);
OleDbParameter toDateparam = new OleDbParameter("@toDate", OleDbType.LongVarWChar);
toDateparam.Value = toDate;
paramList.Add(toDateparam);
DataSet dshj = DbHelperACE.Query(GetPersonRPStatByDateHJSql, paramList.ToArray());
DataRowCollection drshj = dshj.Tables[0].Rows;
for (int i = 0; i < drshj.Count; i++)
{
PersonPlanStatisticData item = new PersonPlanStatisticData();
item.AddressGuid = drshj[i]["name"].ToString().Trim();
item.Address = drshj[i]["name"].ToString().Trim();
item.Type = drshj[i]["type"].ToString().Trim();
if (drshj[i]["statisticcount"] != DBNull.Value)
{
item.Count = Double.Parse(drshj[i]["statisticcount"].ToString().Trim());
}
PersonPlanList.Add(item);
}
return(PersonPlanList);
}
private void btnOK_Click(object sender, EventArgs e)
{
if (txtUserName.Text == "")
{
MessageBox.Show("Please enter user name", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtUserName.Focus();
return;
}
if (txtPassword.Text == "")
{
MessageBox.Show("Please enter password", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtPassword.Focus();
return;
}
try
{
OleDbConnection myConnection = default(OleDbConnection);
myConnection = new OleDbConnection(cs);
OleDbCommand myCommand = default(OleDbCommand);
myCommand = new OleDbCommand("SELECT Username,User_password FROM Users WHERE Username = @username AND User_password = @UserPassword", myConnection);
OleDbParameter uName = new OleDbParameter("@username", OleDbType.VarChar);
OleDbParameter uPassword = new OleDbParameter("@UserPassword", OleDbType.VarChar);
uName.Value = txtUserName.Text;
uPassword.Value = txtPassword.Text;
myCommand.Parameters.Add(uName);
myCommand.Parameters.Add(uPassword);
myCommand.Connection.Open();
OleDbDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
if (myReader.Read() == true)
{
int i;
ProgressBar1.Visible = true;
ProgressBar1.Maximum = 5000;
ProgressBar1.Minimum = 0;
ProgressBar1.Value = 4;
ProgressBar1.Step = 1;
for (i = 0; i <= 5000; i++)
{
ProgressBar1.PerformStep();
}
this.Hide();
frmMainMenu frm = new frmMainMenu();
frm.Show();
frm.lblUser.Text = txtUserName.Text;
}
else
{
MessageBox.Show("Login is Failed...Try again !", "Login Denied", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtUserName.Clear();
txtPassword.Clear();
txtUserName.Focus();
}
if (myConnection.State == ConnectionState.Open)
{
myConnection.Dispose();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void sendUpdateBtn_Click(object sender, EventArgs e)
{
List <Student.Mark> marks = new List <Student.Mark>();
List <Student.Miss> misses = new List <Student.Miss>();
int resUpdMark, resAddMark, resDelMark, resAddSkip, resDelSkip;
resUpdMark = resAddMark = resDelMark = resAddSkip = resDelSkip = 0;
for (int i = 0; i < journalGrid.RowCount; i++)
{
for (int j = 2; j < journalGrid.ColumnCount; j++)
{
if (journalGrid[j, i].Value != null)
{
int idStudent = (int)journalGrid.Rows[i].Cells[0].Value;
string date = journalGrid.Columns[j].HeaderText;
string queryMark = "Select * " +
"From Marks " +
$"Where idStudent = {idStudent} And Marks.dateMark = @date And idSub = {subjectBox.SelectedValue}";
OleDbCommand commandMark = new OleDbCommand(queryMark, _connection);
commandMark.Parameters.Add(new OleDbParameter("@data", date));
var readerMark = commandMark.ExecuteReader();
string querySkip = "Select * " +
"From SkipLesson " +
$"Where idStudent = {idStudent} And dateSkip = @date And idSub = {subjectBox.SelectedValue}";
OleDbCommand commandSkip = new OleDbCommand(querySkip, _connection);
var paramDateSkip = new OleDbParameter("@date", date);
commandSkip.Parameters.Add(paramDateSkip);
var readerSkip = commandSkip.ExecuteReader();
if (Char.IsDigit(journalGrid[j, i].Value.ToString()[0]))
{
string queryCheckMark = "Select * " +
"From Marks " +
$"Where idStudent = {idStudent} And Marks.dateMark = @date And Marks.valueMark = {journalGrid[j, i].Value} And idSub = {subjectBox.SelectedValue}";
OleDbCommand cmdCheckMark = new OleDbCommand(queryCheckMark, _connection);
OleDbParameter param = new OleDbParameter("@date", date);
cmdCheckMark.Parameters.Add(param);
var checker = cmdCheckMark.ExecuteReader();
cmdCheckMark.Parameters.Clear();
if (checker.HasRows)
{
continue;
}
if (readerMark.HasRows)
{
string query = $"Update Marks " +
$"Set Marks.valueMark = {journalGrid[j, i].Value} " +
$"Where idStudent = { journalGrid.Rows[i].Cells[0].Value } And Marks.dateMark = @date And idSub = { subjectBox.SelectedValue }";
OleDbCommand updateCmd = new OleDbCommand(query, _connection);
OleDbParameter paramUpdateMark =
updateCmd.Parameters.Add(new OleDbParameter("@date", date));
resUpdMark += updateCmd.ExecuteNonQuery();
}
else if (readerSkip.HasRows)
{
readerSkip.Read();
string query = $"Delete From SkipLesson Where SkipLesson.id = {readerSkip["id"]}";
OleDbCommand deleteSkip = new OleDbCommand(query, _connection);
resDelSkip += deleteSkip.ExecuteNonQuery();
}
else
{
string query = $"Insert Into Marks(idStudent, idSub, dateMark, valueMark) " +
$"Values({idStudent}, {subjectBox.SelectedValue}, '{journalGrid.Columns[j].HeaderText}', {journalGrid[j, i].Value})";
OleDbCommand addCommand = new OleDbCommand(query, _connection);
resAddMark += addCommand.ExecuteNonQuery();
}
}
else if (journalGrid[j, i].Value.ToString() == "Н")
{
if (readerMark.HasRows)
{
string query = $"Delete From Marks " +
$"Where idStudent = {idStudent} And Marks.dateMark = @date And idSub = { subjectBox.SelectedValue }";
OleDbCommand deleteMarkCmd = new OleDbCommand(query, _connection);
deleteMarkCmd.Parameters.Add(new OleDbParameter("@date", date));
resDelMark += deleteMarkCmd.ExecuteNonQuery();
string addSkipQuery = "Insert Into SkipLesson(idSub, idStudent, dateSkip) " +
$"Values( {subjectBox.SelectedValue}, {idStudent}, '{date}')";
OleDbCommand addSkipCmd = new OleDbCommand(addSkipQuery, _connection);
resAddSkip += addSkipCmd.ExecuteNonQuery();
}
else if (readerSkip.HasRows)
{
continue;
}
else
{
string query = $"Insert Into SkipLesson(idStudent, idSub, dateSkip) " +
$"Values({idStudent}, {subjectBox.SelectedValue}, '{date}')";
OleDbCommand addCommand = new OleDbCommand(query, _connection);
resAddSkip += addCommand.ExecuteNonQuery();
}
}
else if (journalGrid[j, i].Value.ToString() == "none")
{
if (readerMark.HasRows)
{
readerMark.Read();
string query = $"Delete From Marks Where id = {readerMark["id"]}";
OleDbCommand deleteMarkCmd = new OleDbCommand(query, _connection);
resDelMark = deleteMarkCmd.ExecuteNonQuery();
}
if (readerSkip.HasRows)
{
readerSkip.Read();
string query = $"Delete From SkipLesson Where id = {readerSkip["id"]}";
OleDbCommand deleteSkipCmd = new OleDbCommand(query, _connection);
resDelSkip = deleteSkipCmd.ExecuteNonQuery();
}
}
}
}
}
MessageBox.Show($@"
Обновлено отметок: {resUpdMark}
Добавлено отметок: {resAddMark}
Удалено отметок : {resDelMark}
Добавлено пропусков: {resAddSkip}
Удалено пропусков: {resDelSkip}
", "Статистика");
}
private OleDbDataAdapter SetSheetQueryAdapter(DataTable dt)
{
// Deleting in Excel workbook is not possible
//So this command is not defined
try
{
if (_oleConn == null)
{
throw new Exception("Connection is unassigned or closed.");
}
if (_strSheetName.Length == 0)
{
throw new Exception("Sheetname was not assigned.");
}
if (PKCols == null)
{
throw new Exception("Cannot update excel sheet with no primarykey set.");
}
if (PKCols.Length < 1)
{
throw new Exception("Cannot update excel sheet with no primarykey set.");
}
OleDbDataAdapter oleda = new OleDbDataAdapter(_oleCmdSelect);
string strUpdate = "";
string strInsertPar = "";
string strInsert = "";
string strWhere = "";
for (int iPK = 0; iPK < PKCols.Length; iPK++)
{
strWhere = AddWithAnd(strWhere, dt.Columns[iPK].ColumnName + "=?");
}
strWhere = " Where " + strWhere;
for (int iCol = 0; iCol < dt.Columns.Count; iCol++)
{
strInsert = AddWithComma(strInsert, dt.Columns[iCol].ColumnName);
strInsertPar = AddWithComma(strInsertPar, "?");
strUpdate = AddWithComma(strUpdate, dt.Columns[iCol].ColumnName) + "=?";
}
string strTable = "[" + this.SheetName + "$" + this.SheetRange + "]";
strInsert = "INSERT INTO " + strTable + "(" + strInsert + ") Values (" + strInsertPar + ")";
strUpdate = "Update " + strTable + " Set " + strUpdate + strWhere;
oleda.InsertCommand = new OleDbCommand(strInsert, _oleConn);
oleda.UpdateCommand = new OleDbCommand(strUpdate, _oleConn);
OleDbParameter oleParIns = null;
OleDbParameter oleParUpd = null;
for (int iCol = 0; iCol < dt.Columns.Count; iCol++)
{
oleParIns = new OleDbParameter("?", dt.Columns[iCol].DataType.ToString());
oleParUpd = new OleDbParameter("?", dt.Columns[iCol].DataType.ToString());
oleParIns.SourceColumn = dt.Columns[iCol].ColumnName;
oleParUpd.SourceColumn = dt.Columns[iCol].ColumnName;
oleda.InsertCommand.Parameters.Add(oleParIns);
oleda.UpdateCommand.Parameters.Add(oleParUpd);
oleParIns = null;
oleParUpd = null;
}
for (int iPK = 0; iPK < PKCols.Length; iPK++)
{
oleParUpd = new OleDbParameter("?", dt.Columns[iPK].DataType.ToString());
oleParUpd.SourceColumn = dt.Columns[iPK].ColumnName;
oleParUpd.SourceVersion = DataRowVersion.Original;
oleda.UpdateCommand.Parameters.Add(oleParUpd);
}
return(oleda);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 函数： execWithOutPuts(string sSql,string[] arrParameter,int[] arrReturnParaIndex,out string[] arrOutputs)
/// Description： 执行带output参数的sSql，并返回output参数数组
/// Coder： kyq
/// </summary>
/// <param name="sSql">sql语句</param>
/// <param name="arrParameter">一维数组表示的参数列表</param>
/// <param name="arrReturnParaIndex">需要返回的参数索引，从零开始计数</param>
/// <param name="arrOutputs">输出的一维数组</param>
public void execWithOutPuts(string sSql, string[] arrParameter, int[] arrReturnParaIndex, out string[] arrOutputs)
{
checkConn();
OleDbCommand _cmd = new OleDbCommand();
_cmd.CommandTimeout = 0;
_cmd.Connection = _conn;
_cmd.CommandText = SqlCompat(sSql);
_cmd.Prepare();
int iCountReturnPara = 0;
string[] arrTemp = null;
if (arrReturnParaIndex != null)
{
iCountReturnPara = arrReturnParaIndex.GetLength(0);
arrTemp = new string[iCountReturnPara];
}
try
{
if (arrParameter != null)
{
for (int i = 0; i < arrParameter.Length; i++)
{
for (int m = 0; m < iCountReturnPara; m++)
{
OleDbParameter myParameter = new OleDbParameter();
myParameter.ParameterName = "?P" + (i + 1);
if (i == arrReturnParaIndex[m])
{
myParameter.Value = arrParameter.GetValue(i);
myParameter.Direction = ParameterDirection.Output;
myParameter.Size = 100;
}
else
{
myParameter.Direction = ParameterDirection.Input;
myParameter.Value = arrParameter.GetValue(i);
}
_cmd.Parameters.Add(myParameter);
}
}
}
_cmd.ExecuteNonQuery();
//返回数据库输出数据数组，前提是_cmd已经执行
if (arrReturnParaIndex != null)
{
for (int i = 0; i < arrParameter.Length; i++)
{
for (int m = 0; m < iCountReturnPara; m++)
{
if (i == arrReturnParaIndex[m])
{
arrTemp[m] = _cmd.Parameters[i].Value.ToString();
}
}
}
}
arrOutputs = arrTemp;
}
catch (Exception e)
{
arrOutputs = null;
this.ErrHandle("execWithOutPuts方法出错：" + e.Message + "\r\n" + e.StackTrace);
}
_cmd.Parameters.Clear();
}
/// <summary>
/// 新增或修改数据表数据
/// </summary>
/// <typeparam name="T">对应要修改或新增的实体类型</typeparam>
/// <param name="_myclass">对应要修改或新增的实体</param>
/// <returns></returns>
public static bool InsertOrUpdateData <T>(T _myclass)
{
string sqlstr = "";
object objDataFieldAttribute = null;
Type _type = typeof(T);
object objtable = _type.GetCustomAttributes(typeof(DataFieldAttribute), false);
string tablename = "";
if (objtable != null)
{
DataFieldAttribute[] tableattrib = (DataFieldAttribute[])objtable;
if (tableattrib.Length > 0)
{
tablename = tableattrib[0].FieldName;
}
else
{
tablename = _type.Name;
}
}
else
{
tablename = _type.Name;
}
PropertyInfo[] _infolist = _type.GetProperties();
ArrayList ORMap = new ArrayList();
bool isinsert = true;
string strpk = "";
object _obj = null;
//获得实体和数据库对应关系
foreach (PropertyInfo _info in _infolist)
{
_obj = _type.GetProperty(_info.Name).GetValue(_myclass, null);
if (_obj != null && _obj.ToString() != "")
{
objDataFieldAttribute = _info.GetCustomAttributes(typeof(DataFieldAttribute), false);
if (objDataFieldAttribute != null)
{
DataFieldAttribute[] colattrib = (DataFieldAttribute[])objDataFieldAttribute;
if (colattrib.Length > 0)
{
DataFieldAttribute _df = colattrib[0];
ORMap.Add(new string[] { _df.FieldName, _info.Name });
if (_df.PK == "pk")
{
strpk = _df.FieldName;
if (((int)_type.GetProperty(_info.Name).GetValue(_myclass, null)) > 0)
{
isinsert = false;
}
}
}
else
{
ORMap.Add(new string[] { _info.Name, _info.Name });
}
}
else
{
ORMap.Add(new string[] { _info.Name, _info.Name });
}
}
}
//整理入参
OleDbParameter[] pams = new OleDbParameter[ORMap.Count];
string insertsql1 = "", insertsql2 = "", updatesql = "";
int i = 0;
foreach (string[] mapstr in ORMap)
{
pams[i] = new OleDbParameter("@" + mapstr[0], (_type.GetProperty(mapstr[1]).GetValue(_myclass, null)).ToString());
if (strpk != mapstr[0])
{
if (isinsert)
{
if (insertsql1 != "")
{
insertsql1 += ",";
insertsql2 += ",";
}
insertsql1 += mapstr[0];
insertsql2 += "@" + mapstr[0];
}
else
{
if (updatesql != "")
{
updatesql += ",";
}
updatesql += mapstr[0] + "=@" + mapstr[0];
}
}
i++;
}
if (isinsert)
{
sqlstr = "insert into " + tablename + "(" + insertsql1 + ") values(" + insertsql2 + ")";
}
else
{
if (strpk == "")//如果没有主键，则不能进行更新操作
{
return(false);
}
sqlstr = "update " + tablename + " set " + updatesql + " where " + strpk + "=@" + strpk;
}
//执行查询
if (SqlExecNoquery(sqlstr, pams) > 0)
{
return(true);
}
else
{
return(false);
}
}
// 保存
private void saveParam(bool flag)
{
string strCon = AccessHelper.conn;
OleDbConnection con = new OleDbConnection(strCon);
con.Open();
OleDbTransaction tra = con.BeginTransaction(); //创建事务，开始执行事务
try
{
string strCreater = Utils.userId;
string strBah = this.tbvin.Text.Trim();
string sqlJbxx = "DELETE FROM FC_CLJBXX WHERE VIN = '" + strBah + "'";
string sqlParam = "DELETE FROM RLLX_PARAM_ENTITY WHERE VIN ='" + strBah + "'";
if (!flag)
{
AccessHelper.ExecuteNonQuery(tra, sqlJbxx, null);
AccessHelper.ExecuteNonQuery(tra, sqlParam, null);
}
string mainId = this.GetMainId(strBah);
ArrayList sqlList = new ArrayList();
#region 遍历参数,保存
foreach (Control c in this.tlp.Controls)
{
if (c is TextEdit || c is DevExpress.XtraEditors.ComboBoxEdit)
{
//Control[] lblc = clj.Controls.Find("lbl" + c.Name, true);
string paramCode = c.Name;
string paramValue = c.Text;
string strSQL = @"INSERT INTO RLLX_PARAM_ENTITY (PARAM_CODE,VIN,PARAM_VALUE,V_ID)
VALUES(@PARAM_CODE,@VIN,@PARAM_VALUE,@V_ID)";
OleDbParameter[] paramList =
{
new OleDbParameter("@PARAM_CODE", paramCode),
new OleDbParameter("@VIN", strBah),
new OleDbParameter("@PARAM_VALUE", paramValue),
new OleDbParameter("@V_ID", "")
};
AccessHelper.ExecuteNonQuery(tra, strSQL, paramList);
}
}
#endregion
#region 保存车辆基本信息
// 保存车辆基本信息
string sqlStr = @"INSERT INTO FC_CLJBXX
( VIN,
HGSPBM,
USER_ID,
QCSCQY,
JKQCZJXS,
CLXH,
CLZL,
RLLX,
ZCZBZL,
ZGCS,
LTGG,
ZJ,
CLZZRQ,
UPLOADDEADLINE,
TYMC,
YYC,
ZWPS,
ZDSJZZL,
EDZK,
LJ,
QDXS,
STATUS,
JYJGMC,
JYBGBH,
QTXX,
CREATETIME,
UPDATETIME
) VALUES
( @VIN,
@HGSPBM,
@USER_ID,
@QCSCQY,
@JKQCZJXS,
@CLXH,
@CLZL,
@RLLX,
@ZCZBZL,
@ZGCS,
@LTGG,
@ZJ,
@CLZZRQ,
@UPLOADDEADLINE,
@TYMC,
@YYC,
@ZWPS,
@ZDSJZZL,
@EDZK,
@LJ,
@QDXS,
@STATUS,
@JYJGMC,
@JYBGBH,
@QTXX,
@CREATETIME,
@UPDATETIME)";
DateTime clzzrqDate = DateTime.Parse(this.tbclzzrq.Text.Trim());
OleDbParameter clzzrq = new OleDbParameter("@CLZZRQ", clzzrqDate);
clzzrq.OleDbType = OleDbType.DBDate;
DateTime uploadDeadlineDate = Utils.QueryUploadDeadLine(clzzrqDate);
OleDbParameter uploadDeadline = new OleDbParameter("@UPLOADDEADLINE", uploadDeadlineDate);
uploadDeadline.OleDbType = OleDbType.DBDate;
OleDbParameter creTime = new OleDbParameter("@CREATETIME", DateTime.Now);
creTime.OleDbType = OleDbType.DBDate;
OleDbParameter upTime = new OleDbParameter("@UPDATETIME", DateTime.Now);
upTime.OleDbType = OleDbType.DBDate;
OleDbParameter[] param =
{
new OleDbParameter("@VIN", this.tbvin.Text.Trim().ToUpper()),
new OleDbParameter("@HGSPBM", this.tbHgspbm.Text.Trim().ToUpper()),
new OleDbParameter("@USER_ID", strCreater),
new OleDbParameter("@QCSCQY", this.tbqcscqy.Text.Trim()),
new OleDbParameter("@JKQCZJXS", this.tbjkqczjxs.Text.Trim()),
new OleDbParameter("@CLXH", this.tbclxh.Text.Trim()),
new OleDbParameter("@CLZL", this.tbclzl.Text.Trim()),
new OleDbParameter("@RLLX", this.tbrllx.Text.Trim()),
new OleDbParameter("@ZCZBZL", this.tbzczbzl.Text.Trim()),
new OleDbParameter("@ZGCS", this.tbzgcs.Text.Trim()),
new OleDbParameter("@LTGG", this.tbltgg.Text.Trim()),
new OleDbParameter("@ZJ", this.tbzj.Text.Trim()),
clzzrq,
uploadDeadline,
new OleDbParameter("@TYMC", this.tbtymc.Text.Trim()),
new OleDbParameter("@YYC", this.tbyyc.Text.Trim()),
new OleDbParameter("@ZWPS", this.tbzwps.Text.Trim()),
new OleDbParameter("@ZDSJZZL", this.tbzdsjzzl.Text.Trim()),
new OleDbParameter("@EDZK", this.tbedzk.Text.Trim()),
new OleDbParameter("@LJ", this.tblj.Text.Trim()),
new OleDbParameter("@QDXS", this.tbqdxs.Text.Trim()),
new OleDbParameter("@STATUS", this.status),
new OleDbParameter("@JYJGMC", this.tbjyjgmc.Text.Trim()),
new OleDbParameter("@JYBGBH", this.tbjybgbh.Text.Trim()),
new OleDbParameter("@QTXX", this.tbQtxx.Text.Trim()),
creTime,
upTime
};
#endregion
AccessHelper.ExecuteNonQuery(tra, sqlStr, param);
tra.Commit();
strVin = strBah; //备案号
}
catch (Exception ex)
{
// MessageBox.Show("保存失败!");
tra.Rollback();
throw ex;
}
finally
{
con.Close();
}
}
public int Reinspect(string from, string to, string qcn, int amount)
{
this.Connection.Open();
OleDbTransaction transaction = this.Connection.BeginTransaction();
int result = 0;
try
{
string cmdText = "SELECT 送檢次數 FROM 產品檢驗 WHERE 工時資料編號 = ?";
OleDbCommand cmd = new OleDbCommand(cmdText, this.Connection);
cmd.Transaction = transaction;
cmd.Parameters.Add(new OleDbParameter("工時資料編號", from));
int num = Convert.ToInt32(cmd.ExecuteScalar());
cmdText = "INSERT INTO 產品檢驗 (工時資料編號,QCN,待驗數量,送檢次數,最後送檢編號,送檢日期) VALUES (?,?,?,?,?,?)";
cmd = new OleDbCommand(cmdText, this.Connection);
cmd.Transaction = transaction;
cmd.Parameters.Add(new OleDbParameter("工時資料編號1", to));
cmd.Parameters.Add(new OleDbParameter("QCN", qcn));
cmd.Parameters.Add(new OleDbParameter("待驗數量", amount));
cmd.Parameters.Add(new OleDbParameter("送檢次數", num + 1));
cmd.Parameters.Add(new OleDbParameter("最後送檢編號", to));
//cmd.Parameters.Add(new OleDbParameter("送檢日期", DateTime.Today));
OleDbParameter paramDate = new OleDbParameter();
paramDate.OleDbType = OleDbType.DBTimeStamp;
paramDate.Value = DateTime.Now.ToString("s");
cmd.Parameters.Add(paramDate);
result = cmd.ExecuteNonQuery();
cmdText = "UPDATE 產品檢驗 SET 重驗 = True WHERE 工時資料編號 = ?";
cmd = new OleDbCommand(cmdText, this.Connection);
cmd.Transaction = transaction;
//cmd.Parameters.Add(new OleDbParameter("最後送檢編號", to));
cmd.Parameters.Add(new OleDbParameter("工時資料編號", from));
cmd.ExecuteNonQuery();
cmdText = "UPDATE 產品檢驗 SET 最後送檢編號 = ? WHERE 最後送檢編號 = ?";
cmd = new OleDbCommand(cmdText, this.Connection);
cmd.Transaction = transaction;
cmd.Parameters.Add(new OleDbParameter("最後送檢編號1", to));
cmd.Parameters.Add(new OleDbParameter("最後送檢編號2", from));
cmd.ExecuteNonQuery();
cmdText = "INSERT INTO NG原因 (工時資料編號, 原因, 來源編號) SELECT '" + to + "' as 工時資料編號, 原因, 來源編號 FROM NG原因 WHERE 工時資料編號 = ?";
cmd = new OleDbCommand(cmdText, this.Connection);
cmd.Transaction = transaction;
cmd.Parameters.Add(new OleDbParameter("工時資料編號", from));
cmd.ExecuteNonQuery();
transaction.Commit();
this.Connection.Close();
return result;
}
catch (Exception ex)
{
transaction.Rollback();
this.Connection.Close();
throw ex;
}
}
private void Button1_Click(System.Object sender, System.EventArgs e)
{
if (Strings.Len(Strings.Trim(cmbUserType.Text)) == 0)
{
MessageBox.Show("Please select user type", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
cmbUserType.Focus();
return;
}
if (Strings.Len(Strings.Trim(txtUsername.Text)) == 0)
{
MessageBox.Show("Please enter user name", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtUsername.Focus();
return;
}
if (Strings.Len(Strings.Trim(txtPassword.Text)) == 0)
{
MessageBox.Show("Please enter password", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtPassword.Focus();
return;
}
try {
OleDbConnection myConnection = null;
myConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\\LMS_DB.accdb;Persist Security Info=False;");
OleDbCommand myCommand = null;
myCommand = new OleDbCommand("SELECT users.username,User_password FROM Users,Registration where Registration.Username=users.username and Users.Username = @Username and user_password = @UserPassword", myConnection);
OleDbParameter uName = new OleDbParameter("@Username", SqlDbType.VarChar);
OleDbParameter uPassword = new OleDbParameter("@UserPassword", SqlDbType.VarChar);
uName.Value = txtUsername.Text;
uPassword.Value = txtPassword.Text;
myCommand.Parameters.Add(uName);
myCommand.Parameters.Add(uPassword);
myCommand.Connection.Open();
OleDbDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
object Login = 0;
if (myReader.HasRows)
{
myReader.Read();
Login = myReader[Login];
}
if (Login == null)
{
Interaction.MsgBox("Login is Failed...Try again !", MsgBoxStyle.Critical, "Login Denied");
txtUsername.Clear();
txtPassword.Clear();
txtUsername.Focus();
}
else
{
ProgressBar1.Visible = true;
ProgressBar1.Maximum = 5000;
ProgressBar1.Minimum = 0;
ProgressBar1.Value = 4;
ProgressBar1.Step = 1;
for (i = 0; i <= 5000; i++)
{
ProgressBar1.PerformStep();
}
this.Hide();
My.MyProject.Forms.frmMain.lblUser.Text = txtUsername.Text;
My.MyProject.Forms.frmMain.Show();
}
myCommand.Dispose();
myConnection.Close();
} catch (Exception ex) {
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// 返回id的insert方法
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public static int insertReturnID(object obj)
{
List <OleDbParameter> para = new List <OleDbParameter>();
//反射出类型
Type objclass = obj.GetType();
//拼SQL开始
string sqltou = "insert into " + objclass.Name + "(";
string sqlval = "values('";
//验证是否为pojo类。
PropertyInfo ispojo = objclass.GetProperty("Px");
if (ispojo == null)
{
throw new Exception("验证pojo类失败！");
}
//获取所有属性
PropertyInfo[] fields = objclass.GetProperties();
foreach (PropertyInfo field in fields)
{
//去掉验证属性及主键
if (field.Name != "Px" && field.Name != ispojo.GetValue(obj, null).ToString())
{
object fvalue = field.GetValue(obj, null);
OleDbParameter tt = new OleDbParameter();
tt.ParameterName = "@" + field.Name;
tt.Value = fvalue;
//验证整形
if (field.PropertyType.Name == "Int32")
{
tt.OleDbType = OleDbType.Integer;
}
if (field.PropertyType.Name == "String")
{
tt.OleDbType = OleDbType.VarChar;
}
if (field.PropertyType.Name == "Boolean")
{
tt.OleDbType = OleDbType.Boolean;
}
if (field.PropertyType.Name == "DateTime")
{
tt.OleDbType = OleDbType.Date;
}
//大字段
if (field.PropertyType.Name == "Byte[]")
{
tt.OleDbType = OleDbType.VarBinary;
}
if (fvalue != null)
{
para.Add(tt);
sqltou += field.Name + ",";
sqlval = sqlval.Substring(0, sqlval.Length - 1) + tt.ParameterName + ",'";
}
}
}
String sql = sqltou.Substring(0, sqltou.Length - 1) + ")" + sqlval.Substring(0, sqlval.Length - 2) + ");";
//拼SQL结束。
return(ExecuteInsert(sql, para.ToArray()));
}
/// <summary>
/// 执行返回受的行数的TSQL语句
/// </summary>
/// <param name="sql">TSQL语句</param>
/// <param name="dbtype">数据库类型</param>
/// <param name="comtype">命令执行器类型</param>
/// <param name="param">参数数组</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, string dbtype, CommandType comtype, params object[] param)
{
int result = 0;
switch (dbtype)
{
case "SQL2005":
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand com = con.CreateCommand())
{
com.CommandType = comtype;
com.CommandText = sql;
if (param != null && param.Length > 0)
{
for (int i = 0; i < param.Length; i++)
{
SqlParameter p = new SqlParameter("@" + i, param[i]);
com.Parameters.Add(p);
}
}
if (con.State == ConnectionState.Closed)
{
con.Open();
}
result = com.ExecuteNonQuery();
con.Close();
com.Dispose();
}
}
break;
case "OleDB":
using (OleDbConnection con = new OleDbConnection(ConnectionString))
{
using (OleDbCommand com = con.CreateCommand())
{
com.CommandText = sql;
if (param != null && param.Length > 0)
{
for (int i = 0; i < param.Length; i++)
{
OleDbParameter p = new OleDbParameter("@" + i, param[i]);
com.Parameters.Add(p);
}
}
if (con.State == ConnectionState.Closed)
{
con.Open();
}
result = com.ExecuteNonQuery();
con.Close();
com.Dispose();
}
}
break;
default:
break;
}
return(result);
}
private void toolStripButton1_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "Export Excel File";
saveFileDialog.ShowDialog();
if (saveFileDialog.FileName == "")
{
return;
}
System.Data.DataTable Table = ds.Tables[0];
string ExcelFilePath = saveFileDialog.FileName;
WaitFormService.CreateWaitForm();
WaitFormService.SetWaitFormCaption(" 正在导出，请稍候......");
try
{
if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table"))
{
Table.TableName = "Sheet1";
}
//数据表的列数
int ColCount = Table.Columns.Count;
//用于记数，实例化参数时的序号
int i = 0;
//创建参数
OleDbParameter[] para = new OleDbParameter[ColCount];
//创建表结构的SQL语句
string TableStructStr = @"Create Table " + Table.TableName + "(";
//连接字符串
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(connString);
//创建表结构
OleDbCommand objCmd = new OleDbCommand();
//数据类型集合
ArrayList DataTypeList = new ArrayList();
DataTypeList.Add("System.Decimal");
DataTypeList.Add("System.Double");
DataTypeList.Add("System.Int16");
DataTypeList.Add("System.Int32");
DataTypeList.Add("System.Int64");
DataTypeList.Add("System.Single");
//遍历数据表的所有列，用于创建表结构
foreach (DataColumn col in Table.Columns)
{
//如果列属于数字列，则设置该列的数据类型为double
if (DataTypeList.IndexOf(col.DataType.ToString()) >= 0)
{
para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.Double);
objCmd.Parameters.Add(para[i]);
//如果是最后一列
if (i + 1 == ColCount)
{
TableStructStr += col.ColumnName + " double)";
}
else
{
TableStructStr += col.ColumnName + " double,";
}
}
else
{
para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.VarChar);
objCmd.Parameters.Add(para[i]);
//如果是最后一列
if (i + 1 == ColCount)
{
TableStructStr += col.ColumnName + " varchar)";
}
else
{
TableStructStr += col.ColumnName + " varchar,";
}
}
i++;
}
//创建Excel文件及文件结构
try
{
objCmd.Connection = objConn;
objCmd.CommandText = TableStructStr;
if (objConn.State == ConnectionState.Closed)
{
objConn.Open();
}
objCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
throw exp;
}
//插入记录的SQL语句
string InsertSql_1 = "Insert into " + Table.TableName + " (";
string InsertSql_2 = " Values (";
string InsertSql = "";
//遍历所有列，用于插入记录，在此创建插入记录的SQL语句
for (int colID = 0; colID < ColCount; colID++)
{
if (colID + 1 == ColCount) //最后一列
{
InsertSql_1 += Table.Columns[colID].ColumnName + ")";
InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ")";
}
else
{
InsertSql_1 += Table.Columns[colID].ColumnName + ",";
InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ",";
}
}
InsertSql = InsertSql_1 + InsertSql_2;
//遍历数据表的所有数据行
for (int rowID = 0; rowID < Table.Rows.Count; rowID++)
{
for (int colID = 0; colID < ColCount; colID++)
{
if (para[colID].DbType == DbType.Double && Table.Rows[rowID][colID].ToString().Trim() == "")
{
para[colID].Value = 0;
}
else
{
para[colID].Value = Table.Rows[rowID][colID].ToString().Trim();
}
}
try
{
objCmd.CommandText = InsertSql;
objCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
string str = exp.Message;
}
}
try
{
if (objConn.State == ConnectionState.Open)
{
objConn.Close();
}
}
catch (Exception exp)
{
WaitFormService.CloseWaitForm();
throw exp;
}
WaitFormService.CloseWaitForm();
MessageBox.Show("导出完成!", "软件提示");
}
catch
{
WaitFormService.CloseWaitForm();
MessageBox.Show("导出失败!", "软件提示");
}
}
public DataTable GetResultsTable(Query query, out List <string> messages)
{
messages = new List <string>();
DataTable readerTable = null;
_connection.Open();
using (OleDbCommand command = new OleDbCommand())
{
command.Connection = _connection;
command.CommandType = query.QueryCommandType;
if (query.QueryCommandType == CommandType.Text)
{
command.CommandText = query.QueryDefinition;
}
if (query.QueryCommandType == CommandType.TableDirect)
{
command.CommandText = "[" + query.QueryName + "]";
}
if (query.QueryCommandType == CommandType.StoredProcedure)
{
command.CommandText = "[" + query.QueryName + "]";
if (query.QueryParameters.Entities.Count > 0)
{
foreach (QueryParameter parameter in query.QueryParameters.Entities)
{
OleDbParameter p = new OleDbParameter(parameter.ParameterName, parameter.Type);
if (parameter.Value != null)
{
Type type = TypeMapper.MapOleDbTypeToCLR(parameter.Type);
try
{
p.Value = Convert.ChangeType(parameter.Value, type);
}
catch (Exception ex)
{
messages.Add(string.Format("Invalid data specified for parameter {0}. No value passed to query. ({1})", p.ParameterName, ex.Message));
p.Value = DBNull.Value;
}
}
else
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
try
{
using (OleDbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
if (reader.HasRows)
{
readerTable = new DataTable(query.QueryName);
for (int count = 0; count < reader.FieldCount; count++)
{
DataColumn column = new DataColumn(reader.GetName(count), reader.GetFieldType(count));
readerTable.Columns.Add(column);
}
while (reader.Read())
{
DataRow dr = readerTable.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
{
dr[i] = reader.GetValue(reader.GetOrdinal(reader.GetName(i)));
}
readerTable.Rows.Add(dr);
}
}
reader.Close();
}
}
catch (Exception ex)
{
messages.Add(ex.Message);
}
finally
{
_connection.Close();
}
command.Parameters.Clear();
messages.Add(string.Format("The '{0}' query was processed.", query.QueryName));
}
return(readerTable);
}
// <Snippet1>
public void CreateOleDbParameter()
{
OleDbParameter parameter = new OleDbParameter("Description", OleDbType.VarChar, 88);
parameter.Direction = ParameterDirection.Output;
}
/// <summary>
/// 向数据库插入行
/// </summary>
/// <param name="table">表</param>
/// <param name="value">插入行的字段值，用","隔开,形如"FS-CH-001,1758.1,2016-4-28 10:11:58,0"</param>
public bool OrclUpdate(string table, string value, SqlType sqlType)
{
int count = 0;
string sqlstr = "";
OleDbParameter[] parameters = null;
try {
string[] values = value.Split(new char[] { ',' });
if (table.Contains("PH"))
{
sqlstr = "insert into [" + table + "](OBJID, OBJSTATUS, MODIFYDATE, phValue, decTime, decpointCode) " +
"values(" + GetID() + ", @values0,@values1,@values2,@values3,@values4)";
parameters = new OleDbParameter[5];
parameters[0] = new OleDbParameter("@values0", System.Data.OleDb.OleDbType.Integer);
parameters[1] = new OleDbParameter("@values1", System.Data.OleDb.OleDbType.Date);
parameters[2] = new OleDbParameter("@values2", System.Data.OleDb.OleDbType.Single);
parameters[3] = new OleDbParameter("@values3", System.Data.OleDb.OleDbType.Date);
parameters[4] = new OleDbParameter("@values4", System.Data.OleDb.OleDbType.VarChar, 200);
parameters[0].Value = 0;
parameters[1].Value = System.DateTime.Parse(values[1]);
parameters[2].Value = values[3];
parameters[3].Value = System.DateTime.Parse(values[1]);
int cutIndex = table.IndexOf("_PH");
parameters[4].Value = table.Remove(cutIndex, table.Length - cutIndex).Remove(0, PreTableName.Length);
Log.logger.Info("decpointCode:" + parameters[4].Value.ToString() + "|" + table + "|" + cutIndex.ToString() + "|" + PreTableName);
count = MssqlClient.ExecuteNonQuery(sqlstr, parameters);
//OrclUpdateStatus("corr_measurepoint", values[0] + ",0", sqlType);//同步一次status
}
else if (table.Contains("Ind"))
{
sqlstr = "insert into [" + table + "](OBJID, OBJSTATUS, MODIFYDATE, lossValue, decTime, decpointCode) " +
"values(" + GetID() + ", @values0,@values1,@values2,@values3,@values4)";
parameters = new OleDbParameter[5];
parameters[0] = new OleDbParameter("@values0", System.Data.OleDb.OleDbType.Integer);
parameters[1] = new OleDbParameter("@values1", System.Data.OleDb.OleDbType.Date);
parameters[2] = new OleDbParameter("@values2", System.Data.OleDb.OleDbType.Single);
parameters[3] = new OleDbParameter("@values3", System.Data.OleDb.OleDbType.Date);
parameters[4] = new OleDbParameter("@values4", System.Data.OleDb.OleDbType.VarChar, 200);
parameters[0].Value = 0;
parameters[1].Value = System.DateTime.Parse(values[1]);
parameters[2].Value = values[3];
parameters[3].Value = System.DateTime.Parse(values[1]);
int cutIndex = table.IndexOf("_Ind");
parameters[4].Value = table.Remove(cutIndex, table.Length - cutIndex).Remove(0, PreTableName.Length);
Log.logger.Info("decpointCode:" + parameters[4].Value.ToString() + "|" + table + "|" + cutIndex.ToString() + "|" + PreTableName);
count = MssqlClient.ExecuteNonQuery(sqlstr, parameters);
}
Log.logger.Info(table + "上传成功：" + sqlstr); //debug
if (count > 0)
{
lastStatus = true;
return(true);
}
else
{
lastStatus = false;
return(false);
}
}
catch (Exception ex)
{
Log.logger.Error("数据库插入失败", ex);
lastStatus = false;
return(false);
}
}
protected void gvNews_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.ToString() == "DelCommand")
{
int indes = (int)gvNews.DataKeys[Convert.ToInt16(e.CommandArgument)].Value;
string delsqlstr = "delete from tb_news where n_ID=@id";
OleDbConnection conn = GetOleDbConnection();
OleDbCommand delcmd = new OleDbCommand(delsqlstr, conn);
OleDbParameter para = new OleDbParameter("@id", indes);
delcmd.Parameters.Add(para);
conn.Open();
int rows = delcmd.ExecuteNonQuery();
conn.Close();
if (rows > 0)
{
DisplayData(AspNetNews.CurrentPageIndex, AspNetNews.PageSize);
}
else
{
return;
}
}
else if (e.CommandName.ToString() == "AuditCommand")
{
int indes = (int)gvNews.DataKeys[Convert.ToInt16(e.CommandArgument)].Value;
string updatesqlstr = "update tb_news set n_audit=1 where n_id=@id";
OleDbConnection conn = GetOleDbConnection();
OleDbCommand updatecmd = new OleDbCommand(updatesqlstr, conn);
OleDbParameter para = new OleDbParameter("@id", indes);
updatecmd.Parameters.Add(para);
conn.Open();
int rows = updatecmd.ExecuteNonQuery();
conn.Close();
if (rows > 0)
{
DisplayData(AspNetNews.CurrentPageIndex, AspNetNews.PageSize);
}
else
{
return;
}
}
else if (e.CommandName.ToString() == "CancelAuditCommand")
{
int indes = (int)gvNews.DataKeys[Convert.ToInt16(e.CommandArgument)].Value;
string updatesqlstr = "update tb_news set n_audit=0 where n_id=@id";
OleDbConnection conn = GetOleDbConnection();
OleDbCommand updatecmd = new OleDbCommand(updatesqlstr, conn);
OleDbParameter para = new OleDbParameter("@id", indes);
updatecmd.Parameters.Add(para);
conn.Open();
int rows = updatecmd.ExecuteNonQuery();
conn.Close();
if (rows > 0)
{
DisplayData(AspNetNews.CurrentPageIndex, AspNetNews.PageSize);
}
else
{
return;
}
}
}
public ListDictionary AdvSearch_ListDictionary(int aot, string jf, int state, int city, int Travel, string Lang, string fullPart, string Shift, int PostDate, string keywrd, int PageNumber, int RowPerPage, string SortExp, string SortOrder)
{
if (SortExp == null)
{
SortExp = "";
}
if (SortOrder == null)
{
SortOrder = "";
}
OleDbConnection con = new OleDbConnection(constring);
con.Open();
OleDbCommand cmd = new OleDbCommand("p_boaJobSearchAdvanced", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@aot", aot);
cmd.Parameters.AddWithValue("@Family", jf.TrimEnd(",".ToCharArray()));
cmd.Parameters.AddWithValue("@State", state);
cmd.Parameters.AddWithValue("@City", city);
cmd.Parameters.AddWithValue("@Travel", Travel);
cmd.Parameters.AddWithValue("@Lang", Lang.TrimEnd(",".ToCharArray()));
cmd.Parameters.AddWithValue("@fullPart", fullPart.TrimEnd(",".ToCharArray()));
cmd.Parameters.AddWithValue("@Shift", Shift.TrimEnd(",".ToCharArray()));
cmd.Parameters.AddWithValue("@PostDate", PostDate);
cmd.Parameters.AddWithValue("@KeyWords", keywrd);
cmd.Parameters.AddWithValue("@SortExp", SortExp);
cmd.Parameters.AddWithValue("@SortOrder", SortOrder);
OleDbParameter trows = cmd.Parameters.Add("@totalrows", OleDbType.Integer);
trows.Direction = ParameterDirection.Output;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, (PageNumber - 1) * RowPerPage, RowPerPage, "SearchResults");
int TotalRow = Convert.ToInt32(trows.Value);
int partialpagefactor;
partialpagefactor = TotalRow % RowPerPage > 0 ? 1 : 0;
int TotalPage = (TotalRow / RowPerPage) + partialpagefactor;
ListDictionary MyListDictionary = new ListDictionary();
MyListDictionary.Add("RecordCount", TotalRow);
MyListDictionary.Add("JobSearchResults", ds.Tables[0]);
MyListDictionary.Add("NextButton", ShowNextButton(TotalRow, PageNumber, TotalPage));
MyListDictionary.Add("PrevButton", ShowPrevButton(TotalRow, PageNumber, TotalPage));
if (TotalRow == 0)
{
MyListDictionary.Add("PageOfPages", "Page 0 of 0");
}
else
{
MyListDictionary.Add("PageOfPages", "Page " + Convert.ToString(PageNumber) + " of " + Convert.ToString(TotalPage));
}
int startpage;
int endpage;
startpage = 1 + (RowPerPage * (PageNumber - 1));
endpage = startpage + ds.Tables[0].Rows.Count - 1;
MyListDictionary.Add("JobToJobs", "Showing " + Convert.ToString(startpage) + " to " + Convert.ToString(endpage) + " of " + TotalRow + " jobs.");
con.Close();
return(MyListDictionary);
}
/// <summary>
/// 将数据导出至Excel文件
/// </summary>
/// <param name="table">DataTable对象</param>
/// <param name="columns">要导出的数据列集合</param>
/// <param name="excelFilePath">Excel文件路径</param>
public static bool OutputToExcel(DataTable table, ArrayList columns, string excelFilePath)
{
if (File.Exists(excelFilePath))
{
throw new Exception("该文件已经存在！");
}
//如果数据列数大于表的列数，取数据表的所有列
if (columns.Count > table.Columns.Count)
{
for (var s = table.Columns.Count + 1; s <= columns.Count; s++)
{
columns.RemoveAt(s); //移除数据表列数后的所有列
}
}
//遍历所有的数据列，如果有数据列的数据类型不是 DataColumn，则将它移除
var column = new DataColumn();
for (var j = 0; j < columns.Count; j++)
{
try
{
column = (DataColumn)columns[j];
}
catch (Exception)
{
columns.RemoveAt(j);
}
}
if ((table.TableName.Trim().Length == 0) || (table.TableName.ToLower() == "table"))
{
table.TableName = "Sheet1";
}
//数据表的列数
var colCount = columns.Count;
//创建参数
var para = new OleDbParameter[colCount];
//创建表结构的SQL语句
var tableStructStr = @"Create Table " + table.TableName + "(";
//连接字符串
var connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilePath + ";Extended Properties=Excel 8.0;";
var objConn = new OleDbConnection(connString);
//创建表结构
var objCmd = new OleDbCommand();
//数据类型集合
var dataTypeList = new ArrayList();
dataTypeList.Add("System.Decimal");
dataTypeList.Add("System.Double");
dataTypeList.Add("System.Int16");
dataTypeList.Add("System.Int32");
dataTypeList.Add("System.Int64");
dataTypeList.Add("System.Single");
var col = new DataColumn();
//遍历数据表的所有列，用于创建表结构
for (var k = 0; k < colCount; k++)
{
col = (DataColumn)columns[k];
//列的数据类型是数字型
if (dataTypeList.IndexOf(col.DataType.ToString().Trim()) >= 0)
{
para[k] = new OleDbParameter("@" + col.Caption.Trim(), OleDbType.Double);
objCmd.Parameters.Add(para[k]);
//如果是最后一列
if (k + 1 == colCount)
{
tableStructStr += col.Caption.Trim() + " Double)";
}
else
{
tableStructStr += col.Caption.Trim() + " Double,";
}
}
else
{
para[k] = new OleDbParameter("@" + col.Caption.Trim(), OleDbType.VarChar);
objCmd.Parameters.Add(para[k]);
//如果是最后一列
if (k + 1 == colCount)
{
tableStructStr += col.Caption.Trim() + " VarChar)";
}
else
{
tableStructStr += col.Caption.Trim() + " VarChar,";
}
}
}
//创建Excel文件及文件结构
try
{
objCmd.Connection = objConn;
objCmd.CommandText = tableStructStr;
if (objConn.State == ConnectionState.Closed)
{
objConn.Open();
}
objCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
throw exp;
}
//插入记录的SQL语句
var insertSql1 = "Insert into " + table.TableName + " (";
var insertSql2 = " Values (";
var insertSql = "";
//遍历所有列，用于插入记录，在此创建插入记录的SQL语句
for (var colId = 0; colId < colCount; colId++)
{
if (colId + 1 == colCount) //最后一列
{
insertSql1 += columns[colId].ToString().Trim() + ")";
insertSql2 += "@" + columns[colId].ToString().Trim() + ")";
}
else
{
insertSql1 += columns[colId].ToString().Trim() + ",";
insertSql2 += "@" + columns[colId].ToString().Trim() + ",";
}
}
insertSql = insertSql1 + insertSql2;
//遍历数据表的所有数据行
var dataCol = new DataColumn();
for (var rowId = 0; rowId < table.Rows.Count; rowId++)
{
for (var colId = 0; colId < colCount; colId++)
{
//因为列不连续，所以在取得单元格时不能用行列编号，列需得用列的名称
dataCol = (DataColumn)columns[colId];
if (para[colId].DbType == DbType.Double && table.Rows[rowId][dataCol.Caption].ToString().Trim() == "")
{
para[colId].Value = 0;
}
else
{
para[colId].Value = table.Rows[rowId][dataCol.Caption].ToString().Trim();
}
}
try
{
objCmd.CommandText = insertSql;
objCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
var str = exp.Message;
}
}
try
{
if (objConn.State == ConnectionState.Open)
{
objConn.Close();
}
}
catch (Exception exp)
{
throw exp;
}
return(true);
}
public ListDictionary AdvSearch_ListDictionaryInternational(string CountryID, string LocationID, string keywrd, string FamilyID, int PageNumber, int RowPerPage, string SortExp, string SortOrder, string BOAFeedName, string City)
{
if (SortExp == null)
{
SortExp = "";
}
if (SortOrder == null)
{
SortOrder = "";
}
OleDbConnection con = new OleDbConnection(constring);
con.Open();
OleDbCommand cmd = new OleDbCommand("p_boaJobSearchAdvancedInternational", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CountryID", CountryID);
cmd.Parameters.AddWithValue("@LocationID", LocationID);
cmd.Parameters.AddWithValue("@FamiyID", FamilyID);
cmd.Parameters.AddWithValue("@KeyWords", keywrd);
cmd.Parameters.AddWithValue("@SortExp", SortExp);
cmd.Parameters.AddWithValue("@SortOrder", SortOrder);
cmd.Parameters.AddWithValue("@BOAFeedName", BOAFeedName);
cmd.Parameters.AddWithValue("@City", City);
OleDbParameter trows = cmd.Parameters.Add("@totalrows", OleDbType.Integer);
trows.Direction = ParameterDirection.Output;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, (PageNumber - 1) * RowPerPage, RowPerPage, "SearchResults");
int TotalRow = Convert.ToInt32(trows.Value);
int partialpagefactor;
partialpagefactor = TotalRow % RowPerPage > 0 ? 1 : 0;
int TotalPage = (TotalRow / RowPerPage) + partialpagefactor;
ListDictionary MyListDictionary = new ListDictionary();
MyListDictionary.Add("RecordCount", TotalRow);
MyListDictionary.Add("JobSearchResults", ds.Tables[0]);
MyListDictionary.Add("NextButton", ShowNextButton(TotalRow, PageNumber, TotalPage));
MyListDictionary.Add("PrevButton", ShowPrevButton(TotalRow, PageNumber, TotalPage));
if (TotalRow == 0)
{
MyListDictionary.Add("PageOfPages", "Page 0 of 0");
}
else
{
MyListDictionary.Add("PageOfPages", "Page " + Convert.ToString(PageNumber) + " of " + Convert.ToString(TotalPage));
}
int startpage;
int endpage;
startpage = 1 + (RowPerPage * (PageNumber - 1));
endpage = startpage + ds.Tables[0].Rows.Count - 1;
MyListDictionary.Add("JobToJobs", "Showing " + Convert.ToString(startpage) + " to " + Convert.ToString(endpage) + " of " + TotalRow + " jobs.");
con.Close();
return(MyListDictionary);
}
public void TestMultipleResultsets()
{
#if !JAVA
if (ConnectedDataProvider.GetDbType(con) == DataBaseServer.Oracle)
{
//In .NET there is a bug when calling a SP with multiple REFCURSORS, the workaround is to use OracleClient and not OleDb.
//In GH we are not bug complient in this issue, because there is no workaround (We do not support the OracleClient namespace.
this.Log("Not testing multi result set Oracle on .NET");
return;
}
if (ConnectedDataProvider.GetDbType(con) == DataBaseServer.PostgreSQL)
{
// fail to work on .NET OLEDB
//reader = Microsoft.ApplicationBlocks.Data.PostgresOleDbHelper.ADOExecuteReader(cmd1);
this.Log("Not testing PostgreSQL CommandType.StoredProcedure which return SETOF");
return;
}
#endif
Exception exp = null;
BeginCase("Test multi result set from stored procedure");
OleDbDataReader reader = null;
OleDbTransaction tr = null;
try
{
//Check SP with the structre : insert Select + update Select + delete Select
if (con.State != ConnectionState.Open)
{
con.Open();
}
// transaction use was add for PostgreSQL
tr = con.BeginTransaction();
OleDbCommand cmd1 = new OleDbCommand("GHSP_TYPES_SIMPLE_4", con, tr);
cmd1.CommandType = CommandType.StoredProcedure;
OleDbParameter param = new OleDbParameter();
param.ParameterName = "ID1";
param.Value = string.Format("13268_{0}", this.TestCaseNumber);
param.OleDbType = OleDbType.VarWChar;
cmd1.Parameters.Add(param);
reader = cmd1.ExecuteReader();
//Count the number of result sets.
int resultSetCount = 0;
//Count the number of the records
int recordCounter = 0;
do
{
//this.Log(string.Format("resultSetCount:{0}",resultSetCount));
while (reader.Read())
{
recordCounter++;
}
//this.Log(string.Format("recordCounter:{0}",recordCounter));
if (resultSetCount != 2)
{
Compare(recordCounter, 1); //Insert + update
}
else
{
Compare(recordCounter, 0); //Delete
}
recordCounter = 0;
resultSetCount++;
}while (reader.NextResult());
Compare(resultSetCount, 3);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
if (reader != null)
{
reader.Close();
}
tr.Commit();
con.Close();
EndCase(exp);
}
}
public static bool UnloadAnswer()
{
if (!(TeacherID != -1 && StudentID != -1 && TaskID != -1 && AnswerText != ""))
{
return(false);
}
string connectString = "provider=Microsoft.ACE.Oledb.12.0;Data Source= " + System.AppDomain.CurrentDomain.BaseDirectory + "\\AducationBase.accdb;";
OleDbConnection connection = new OleDbConnection(connectString);
string query = " SELECT Ответы.ID " +
" FROM Ответы " +
" WHERE( ((Ответы.ЗаданиеID) = " + TaskID + " ) AND ( (Ответы.УченикID) = " + StudentID + " ));";
OleDbCommand command = new OleDbCommand(query, connection);
connection.Open();
string answerID = "";
try
{
answerID = command.ExecuteScalar().ToString();
}
catch
{}
if (answerID != "")
{
query = "DELETE Ответы.ID" +
" FROM Ответы" +
" WHERE((Ответы.ID) = " + answerID + ");";
command = new OleDbCommand(query, connection);
command.ExecuteScalar();
}
connection.Close();
if (AnswerFiles.Count > 0)
{
string Date = Convert.ToString(DateTime.Today.ToString("dd.MM.yyyy"));
query = "INSERT INTO Ответы(ЗаданиеID, УчительID, УченикID, Дата, ТекстОтвета, ДопФайлы) " +
"values( '" + TaskID + "', '" + TeacherID + "', '" + StudentID + "', '" + Date + "', '" + AnswerText + "' , ? )";
if (answerID == "")
{
}
command = new OleDbCommand(query, connection);
OleDbParameter oleDbParameter = new OleDbParameter("ДопФайлы", OleDbType.VarBinary);
string fileName = AnswerFiles[0].directory; //Путь к файлу
System.Drawing.Image image = System.Drawing.Image.FromFile(fileName); //Изображение из файла.
System.IO.MemoryStream memoryStream = new System.IO.MemoryStream(); //Поток в который запишем изображение
image.Save(memoryStream, System.Drawing.Imaging.ImageFormat.Bmp); //Сохраняем изображение в поток.
oleDbParameter.Value = memoryStream.ToArray(); //Устанавливаем значение параметра
command.Parameters.Add(oleDbParameter);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
memoryStream.Dispose();
}
else
{
string Date = Convert.ToString(DateTime.Today.ToString("dd.MM.yyyy"));
query = "INSERT INTO Ответы(ЗаданиеID, УчительID, УченикID, Дата, ТекстОтвета) " +
"values( '" + TaskID + "', '" + TeacherID + "', '" + StudentID + "', '" + Date + "', '" + AnswerText + "' )";
command = new OleDbCommand(query, connection);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
return(true);
}
private void button2_Click(object sender, EventArgs e)
{
try{
String nombreP, sucursal; int cant, monto;
OleDbCommand procedimientoAlmacenado;
OleDbParameter parametro1, parametro2, salida1, salida2;
//1- Abrir la conexión a la BD.
cnOracle = new OleDbConnection("Provider=MSDAORA; Data Source=xe;" +
"User ID=System;Password=gonbar");
/*
* cnOracle = new OleDbConnection("Provider=MSDAORA; Data Source=oracle;" +
* "User ID=BD03;Password=gonbar");
*/
cnOracle.Open();
procedimientoAlmacenado = new OleDbCommand();
procedimientoAlmacenado.Connection = cnOracle;
//2- Especificar el llamado al procedimiento (en general: al subprograma).
procedimientoAlmacenado.CommandText = "SucursalesConProducto";
procedimientoAlmacenado.CommandType = CommandType.StoredProcedure;
//3- Especificar los parámetros:
//a) primero todos los de entrada:
nombreP = comboBox3.SelectedItem.ToString();
parametro1 = new OleDbParameter("NombreArticulo", nombreP);
procedimientoAlmacenado.Parameters.Add(parametro1);
monto = Convert.ToInt32(textBox1.Text);
parametro2 = new OleDbParameter("Monto", monto);
procedimientoAlmacenado.Parameters.Add(parametro2);
//b) luego todos los de salida (uno en este caso):
salida1 = new OleDbParameter("cant", OleDbType.Integer,
4, ParameterDirection.Output, false, 4, 0, "nombreP", DataRowVersion.Current, 0);
salida2 = new OleDbParameter("sucursal", OleDbType.VarChar,
20, ParameterDirection.Output, false, 4, 0, "nombreP", DataRowVersion.Current, 0);
procedimientoAlmacenado.Parameters.Add(salida1);
procedimientoAlmacenado.Parameters.Add(salida2);
//4- Ejecutar el procedimiento (en general: el subprograma).
procedimientoAlmacenado.ExecuteNonQuery();
//5- Recuperar el (los) valor(es) regresado(s) por medio del (de los)
// parámetro(s) de salida.
cant = Convert.ToInt16(procedimientoAlmacenado.Parameters["cant"].Value);
sucursal = procedimientoAlmacenado.Parameters["sucursal"].Value.ToString();
MessageBox.Show("Nombre del Articulo: " + nombreP + ", Precio: " + monto +
", Cantidad de Sucursales: " + cant + ", Sucursal: " + sucursal);
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
comboBox3.SelectedIndex = -1;
textBox1.Text = "";
//6- Cerrar la conexión a la BD.
cnOracle.Close();
}
public static bool update(object obj)
{
List <OleDbParameter> para = new List <OleDbParameter>();
try {
//反射出类型
Type objclass = obj.GetType();
//拼SQL开始
string sqltou = "update " + objclass.Name + " set ";
//验证是否为pojo类。
PropertyInfo ispojo = objclass.GetProperty("Px");
if (ispojo == null)
{
return(false);
}
PropertyInfo px = objclass.GetProperty(ispojo.GetValue(obj, null).ToString());
if (px == null || px.GetValue(obj, null) == null)
{
return(false);
}
//获取所有属性
PropertyInfo[] fields = objclass.GetProperties();
string tempsql = "";
foreach (PropertyInfo field in fields)
{
//去掉验证属性及主键
if (field.Name != "Px" && field.Name != ispojo.GetValue(obj, null).ToString())
{
object fvalue = field.GetValue(obj, null);
OleDbParameter tt = new OleDbParameter();
tt.ParameterName = "@" + field.Name;
tt.IsNullable = true;
if (fvalue != null)
{
tt.Value = fvalue;
}
else
{
tt.Value = DBNull.Value;
}
//验证整形
if (field.PropertyType.Name == "Int32")
{
tt.OleDbType = OleDbType.Integer;
}
if (field.PropertyType.Name == "String")
{
tt.OleDbType = OleDbType.VarChar;
}
if (field.PropertyType.Name == "Boolean")
{
tt.OleDbType = OleDbType.Boolean;
}
if (field.PropertyType.Name == "DateTime")
{
tt.OleDbType = OleDbType.Date;
}
//大字段
if (field.PropertyType.Name == "Byte[]")
{
tt.OleDbType = OleDbType.VarBinary;
}
para.Add(tt);
tempsql = tempsql + field.Name + "=" + tt.ParameterName + ",";
}
}
String sql = sqltou + tempsql.Substring(0, tempsql.Length - 1) + " where " + px.Name + "=" + px.GetValue(obj, null).ToString() + ";";
//拼SQL结束。
ExecuteNonQuery(sql, para.ToArray());
return(true);
} catch (Exception e) {
MessageBox.Show(e.ToString());
return(false);
}
}
/// <summary>
/// 将数据导出至Excel文件
/// </summary>
/// <param name="Table">DataTable对象</param>
/// <param name="Columns">要导出的数据列集合</param>
/// <param name="ExcelFilePath">Excel文件路径</param>
public static bool OutputToExcel(DataTable Table, ArrayList Columns, string ExcelFilePath)
{
if (File.Exists(ExcelFilePath))
{
throw new Exception("该文件已经存在！");
}
//如果数据列数大于表的列数，取数据表的所有列
if (Columns.Count > Table.Columns.Count)
{
for (int s = Table.Columns.Count + 1; s <= Columns.Count; s++)
{
Columns.RemoveAt(s); //移除数据表列数后的所有列
}
}
//遍历所有的数据列，如果有数据列的数据类型不是 DataColumn，则将它移除
DataColumn column = new DataColumn();
for (int j = 0; j < Columns.Count; j++)
{
try
{
column = (DataColumn)Columns[j];
}
catch (Exception)
{
Columns.RemoveAt(j);
}
}
if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table"))
{
Table.TableName = "Sheet1";
}
//数据表的列数
int ColCount = Columns.Count;
//创建参数
OleDbParameter[] para = new OleDbParameter[ColCount];
//创建表结构的SQL语句
string TableStructStr = @"Create Table " + Table.TableName + "(";
//连接字符串
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(connString);
//创建表结构
OleDbCommand objCmd = new OleDbCommand();
//数据类型集合
ArrayList DataTypeList = new ArrayList();
DataTypeList.Add("System.Decimal");
DataTypeList.Add("System.Double");
DataTypeList.Add("System.Int16");
DataTypeList.Add("System.Int32");
DataTypeList.Add("System.Int64");
DataTypeList.Add("System.Single");
DataColumn col = new DataColumn();
//遍历数据表的所有列，用于创建表结构
for (int k = 0; k < ColCount; k++)
{
col = (DataColumn)Columns[k];
//列的数据类型是数字型
if (DataTypeList.IndexOf(col.DataType.ToString().Trim()) >= 0)
{
para[k] = new OleDbParameter("@" + col.Caption.Trim(), OleDbType.Double);
objCmd.Parameters.Add(para[k]);
//如果是最后一列
if (k + 1 == ColCount)
{
TableStructStr += col.Caption.Trim() + " Double)";
}
else
{
TableStructStr += col.Caption.Trim() + " Double,";
}
}
else
{
para[k] = new OleDbParameter("@" + col.Caption.Trim(), OleDbType.VarChar);
objCmd.Parameters.Add(para[k]);
//如果是最后一列
if (k + 1 == ColCount)
{
TableStructStr += col.Caption.Trim() + " VarChar)";
}
else
{
TableStructStr += col.Caption.Trim() + " VarChar,";
}
}
}
//创建Excel文件及文件结构
try
{
objCmd.Connection = objConn;
objCmd.CommandText = TableStructStr;
if (objConn.State == ConnectionState.Closed)
{
objConn.Open();
}
objCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
throw exp;
}
//插入记录的SQL语句
string InsertSql_1 = "Insert into " + Table.TableName + " (";
string InsertSql_2 = " Values (";
string InsertSql = "";
//遍历所有列，用于插入记录，在此创建插入记录的SQL语句
for (int colID = 0; colID < ColCount; colID++)
{
if (colID + 1 == ColCount) //最后一列
{
InsertSql_1 += Columns[colID].ToString().Trim() + ")";
InsertSql_2 += "@" + Columns[colID].ToString().Trim() + ")";
}
else
{
InsertSql_1 += Columns[colID].ToString().Trim() + ",";
InsertSql_2 += "@" + Columns[colID].ToString().Trim() + ",";
}
}
InsertSql = InsertSql_1 + InsertSql_2;
//遍历数据表的所有数据行
DataColumn DataCol = new DataColumn();
for (int rowID = 0; rowID < Table.Rows.Count; rowID++)
{
for (int colID = 0; colID < ColCount; colID++)
{
//因为列不连续，所以在取得单元格时不能用行列编号，列需得用列的名称
DataCol = (DataColumn)Columns[colID];
if (para[colID].DbType == DbType.Double && Table.Rows[rowID][DataCol.Caption].ToString().Trim() == "")
{
para[colID].Value = 0;
}
else
{
para[colID].Value = Table.Rows[rowID][DataCol.Caption].ToString().Trim();
}
}
try
{
objCmd.CommandText = InsertSql;
objCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
string str = exp.Message;
}
}
try
{
if (objConn.State == ConnectionState.Open)
{
objConn.Close();
}
}
catch (Exception exp)
{
throw exp;
}
return(true);
}
/// <summary>
/// Returns this instance as a OleDbParameter.
/// </summary>
/// <returns></returns>
protected internal OleDbParameter getOleDbParameter()
{
OleDbParameter res = new OleDbParameter();
res.DbType = DbType;
res.Direction = Direction;
res.IsNullable = IsNullable;
res.ParameterName = ParameterName;
res.SourceColumn = SourceColumn;
res.SourceVersion = SourceVersion;
res.Value = Value;
res.SourceColumnNullMapping = SourceColumnNullMapping;
return res;
}
