public int GetExperimentsTotalCount(string experimentName, string experimentDate, string userName)
{
int count = 0;
try
{
string connectionString = WanTai.Common.Configuration.GetConnectionString();
///todo: add control,each role user only can see the specific experiments
string commandText = "SELECT count(ExperimentID) FROM ExperimentsInfo";
StringBuilder sbWhere = new StringBuilder();
if (!string.IsNullOrEmpty(experimentName))
{
string experimentName2 = experimentName.Replace("'", "''").Replace("[","[[]");
sbWhere.Append(" WHERE ExperimentName like '%" + experimentName2 + "%'");
}
if (!string.IsNullOrEmpty(experimentDate))
{
sbWhere.Append(sbWhere.Length > 0 ? " AND " : " WHERE ");
sbWhere.Append(" StartTime between CONVERT(datetime,'" + experimentDate + "',101) and DATEADD(dd, 1, CONVERT(datetime,'" + experimentDate + "',101))");
}
if (!string.IsNullOrEmpty(userName))
{
sbWhere.Append(sbWhere.Length > 0 ? " AND " : " WHERE ");
sbWhere.Append(" LoginName='"+userName+"'");
}
// UserInfoController userInfoController = new UserInfoController();
// userInfoController.GetRoleByUserName(SessionInfo.LoginName);
// RoleInfo userRole = userInfoController.GetRoleByUserName(SessionInfo.LoginName);
// sbWhere.Append(sbWhere.Length > 0 ? " AND " : " WHERE ");
// if (userInfoController.GetAuthorize(AccessAuthority.ExperimentHistory) == AccessAuthority.Self)
// {
// sbWhere.Append(" LoginName='" + SessionInfo.LoginName + "'");
// }
// else
// sbWhere.Append(@" 1=1 ");
//// {
//// sbWhere.Append(@" (LoginName is null or LoginName in
//// (select LoginName from UserInfo u left join roleinfo r on r.RoleName=u.RoleName where r.RoleLevel<=" + userRole.RoleLevel+ " ))");
//// }
commandText += sbWhere.ToString();
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default))
{
if (reader.Read())
{
count = (int)reader.GetValue(0);
}
}
}
}
}
catch (Exception e)
{
string errorMessage = e.Message + System.Environment.NewLine + e.StackTrace;
LogInfoController.AddLogInfo(LogInfoLevelEnum.Error, errorMessage, SessionInfo.LoginName, this.GetType().ToString() + "->GetExperimentsTotalCount", SessionInfo.ExperimentID);
throw;
}
return count;
}
/// <summary>
/// Carga el esquema de vacunacion y las aplicaciones
/// </summary>
/// <param name="vacunas"></param>
/// <param name="aplicaciones"></param>
/// <param name="idExpediente"></param>
/// <returns>Retorna un mensaje de confirmacion indicando si se realizo la transaccion</returns>
public string CargarAplicaciones(List<TOAplicacionVacuna> aplicaciones, string idExpediente)
{
string confirmacion = "El esquema de vacunación se cargó exitosamente";
// Se abre la conexión
if (conexion != null)
{
try
{
if (conexion.State != ConnectionState.Open)
{
conexion.Open();
}
}
catch (Exception)
{
confirmacion = "Ocurrió un error y no se pudo cargar el esquema de vacunación";
return confirmacion;
}
}
else
{
confirmacion = "Ocurrió un error y no se pudo cargar el esquema de vacunación";
return confirmacion;
}
// Se inicia una nueva transacción
SqlTransaction transaccion = null;
try
{
transaccion = conexion.BeginTransaction("Cargar aplicaciones de vacuna");
// Se crea un nuevo comando con la secuencia SQL y el objeto de conexión
SqlCommand comando = new SqlCommand("SELECT * FROM APLICACION_VACUNA WHERE ID_EXPEDIENTE = @idExpediente", conexion);
comando.Transaction = transaccion;
comando.Parameters.AddWithValue("@idExpediente", idExpediente);
// Se ejecuta el comando
SqlDataReader lector = comando.ExecuteReader();
// Se lee el dataReader con los registros obtenidos y se cargan los datos en la lista de vacunas
if (lector.HasRows)
{
while (lector.Read())
{
TOAplicacionVacuna aplicacion = new TOAplicacionVacuna(lector["ID_EXPEDIENTE"].ToString(), lector["NOMBRE_VACUNA"].ToString(), lector["APLICACION1"].ToString(),
lector["APLICACION2"].ToString(), lector["APLICACION3"].ToString(),
lector["REFUERZO1"].ToString(), lector["REFUERZO2"].ToString(),
lector["REFUERZO3"].ToString());
aplicaciones.Add(aplicacion);
}
}
lector.Close();
transaccion.Commit();
}
catch (Exception)
{
try
{
// En caso de un error se realiza un rollback a la transacción
transaccion.Rollback();
}
catch (Exception)
{
}
finally
{
confirmacion = "Ocurrió un error y no se pudo cargar el esquema de vacunación";
}
}
finally
{
if (conexion.State != ConnectionState.Closed)
{
conexion.Close();
}
}
return confirmacion;
}
public StringBuilder UpdateOrInsertItemBySupplierCodeInHouse(List<SupplierItem> list, int supplierId)
{
StringBuilder sb = new StringBuilder();
foreach (var item in list)
{
SqlConnection conn = new SqlConnection();
String companyNote = String.Empty;
conn.ConnectionString = CONNSTRING;
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = " SELECT ItemID from Items where SupplierItemCode=@SupplierItemCode and SupplierID=@suppId ";
cmd.Parameters.AddWithValue("@SupplierItemCode", item.SupplierItemCode);
cmd.Parameters.AddWithValue("@suppId", supplierId);
double resellprice = 0;
conn.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
if (sdr.HasRows)
{
var itemId = 0;
while (sdr.Read())
{
if (sdr["ItemID"] != DBNull.Value)
itemId = Convert.ToInt32(sdr["ItemID"].ToString());
}
conn.Close();
var productPrice = Convert.ToDouble(item.PriceUpdate);
if (productPrice > 0)
resellprice = productPrice * 4;
String strSQLUpdateStmt = @"update Items set COG=@cog, ManagerUnitPrice=@ResellPrice,
RepUnitPrice=@ResellPrice,AlterationDate=CURRENT_TIMESTAMP , Quantity=@Quantity where ItemID=@ItemID";
SqlCommand cmd2 = new SqlCommand(strSQLUpdateStmt, conn);
cmd2.Parameters.AddWithValue("@cog", Convert.ToDouble(item.PriceUpdate));
cmd2.Parameters.AddWithValue("@ResellPrice", resellprice);
cmd2.Parameters.AddWithValue("@ItemID", itemId);
cmd2.Parameters.AddWithValue("@Quantity", item.Quantity);
try
{
sb.Append(String.Format("Updated Code = {0}: Price = {1} Description = {2}", item.SupplierItemCode, item.PriceUpdate, item.Description));
sb.Append(Environment.NewLine);
conn.Open();
cmd2.ExecuteNonQuery().ToString();
conn.Close();
}
catch (Exception ex)
{
if (conn != null) { conn.Close(); } sb.Append(ex.Message.ToString()); sb.Append(Environment.NewLine);
}
}
else
{
conn.Close();
var BestPrice = 'Y';
var Faulty = 'N';
var productPrice = Convert.ToDouble(item.PriceUpdate);
if (productPrice > 0)
resellprice = productPrice * 4;
String strSQLInsertStmt = @"insert into dbo.Items(SupplierID,SupplierItemCode, Description," +
" COG, ManagerUnitPrice, RepUnitPrice, Active,CreatedBy,CreatedDateTime, PriceLock, ReportedFaulty,Quantity,AlterationDate) " +
" values (@SupplierID,@ItemCode,@Description, @COG, @ResellPRice, @ResellPrice,'Y','SYSTEM',CURRENT_TIMESTAMP, @PriceLock, @ReportedFaulty,@Quantity,CURRENT_TIMESTAMP);";
SqlCommand cmd3 = new SqlCommand(strSQLInsertStmt, conn);
cmd3.Parameters.AddWithValue("@SupplierID", supplierId);
cmd3.Parameters.AddWithValue("@ItemCode", item.SupplierItemCode);
cmd3.Parameters.AddWithValue("@Description", item.Description);
cmd3.Parameters.AddWithValue("@COG", Convert.ToDouble(item.PriceUpdate));
cmd3.Parameters.AddWithValue("@ResellPrice", resellprice);
cmd3.Parameters.AddWithValue("@PriceLock", BestPrice);
cmd3.Parameters.AddWithValue("@ReportedFaulty", Faulty);
cmd3.Parameters.AddWithValue("@Quantity", item.Quantity);
try
{
sb.Append(String.Format("Inserted newly Code = {0}: Price = {1} Description = {2}", item.SupplierItemCode, item.PriceUpdate, item.Description));
sb.Append(Environment.NewLine);
conn.Open();
cmd3.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
if (conn != null) { conn.Close(); }
sb.Append(ex.Message.ToString()); sb.Append(Environment.NewLine);
}
}
}
}
return sb;
}
// GET: TrainingPrograms/Details/5
public ActionResult Details(int id)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT tp.Id AS TrainingId, tp.[Name] AS TrainingName, tp.StartDate, tp.EndDate, tp.MaxAttendees, e.FirstName, e.LastName, e.Id AS EmployeeId
FROM TrainingProgram tp
LEFT JOIN EmployeeTraining et ON et.TrainingProgramId = tp.Id
LEFT JOIN Employee e ON et.EmployeeId = e.Id
WHERE tp.Id = @id";
cmd.Parameters.Add(new SqlParameter("@id", id));
SqlDataReader reader = cmd.ExecuteReader();
TrainingWithEmployees trainingProgram = null;
while (reader.Read())
{
if (trainingProgram == null)
{
trainingProgram = new TrainingWithEmployees
{
Id = reader.GetInt32(reader.GetOrdinal("TrainingId")),
Name = reader.GetString(reader.GetOrdinal("TrainingName")),
StartDate = reader.GetDateTime(reader.GetOrdinal("StartDate")),
EndDate = reader.GetDateTime(reader.GetOrdinal("EndDate")),
MaxAttendees = reader.GetInt32(reader.GetOrdinal("MaxAttendees"))
};
if (!reader.IsDBNull(reader.GetOrdinal("FirstName")))
{
var employee = new BasicEmployee
{
Id = reader.GetInt32(reader.GetOrdinal("EmployeeId")),
FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
LastName = reader.GetString(reader.GetOrdinal("LastName")),
};
trainingProgram.EmployeesAttending.Add(employee);
}
} else if (!reader.IsDBNull(reader.GetOrdinal("FirstName")))
{
var employee = new BasicEmployee
{
Id = reader.GetInt32(reader.GetOrdinal("EmployeeId")),
FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
LastName = reader.GetString(reader.GetOrdinal("LastName")),
};
trainingProgram.EmployeesAttending.Add(employee);
}
}
reader.Close();
if (trainingProgram == null)
{
return NotFound($"No Training Program found with the ID of {id}");
}
return View(trainingProgram);
}
}
}
public async Task Retrieve()
{
var tableName = Guid.NewGuid().ToString();
var random = new Random();
var definitions = new List<IDefinition>();
var variables = new List<IVariable>();
variables.Add(new Variable() { IsPrimaryKey = true, ParameterName = Guid.NewGuid().ToString() });
var def = new Definition()
{
Name = Guid.NewGuid().ToString(),
Preface = Guid.NewGuid().ToString(),
Variables = variables,
};
definitions.Add(def);
var ds = new DataSet();
var sql = string.Format(SqlStatements.SelectDataFormat, def.Preface, def.Name);
var executor = Substitute.For<IExecutor>();
executor.Query(sql).Returns(Task.FromResult(ds));
var schemaReader = Substitute.For<ISchemaReader>();
var loader = Substitute.For<IDynamicLoader>();
loader.Dictionaries(ds).Returns(new List<IDictionary<string, object>>());
var reader = new SqlDataReader(executor, schemaReader, loader, tableName);
var result = await reader.Retrieve(definitions);
Assert.IsNotNull(result);
Assert.AreEqual(definitions.Count(), result.Count());
executor.Received().Query(sql);
loader.Received().Dictionaries(ds);
}
private static void AddToListString(ref List<string> list, SqlDataReader dr)
{
list.Add(dr.GetString(0));
}
static void Main(string[] args)
{
using SqlConnection sqlConnection = new SqlConnection(ConnectionString);
sqlConnection.Open();
StringBuilder result = new StringBuilder();
string countryName = Console.ReadLine();
string getCountryIdQueryText = @"SELECT Id
FROM Countries
WHERE [Name] = @countryName";
using SqlCommand getCountryIdCmd = new SqlCommand(getCountryIdQueryText, sqlConnection);
getCountryIdCmd.Parameters.AddWithValue("@countryName", countryName);
string countryId = getCountryIdCmd.ExecuteScalar()?.ToString();
if (countryId == null)
{
Console.WriteLine("No town names were affected.");
return;
}
string updateTownNamesToUpperCaseQueryText = @"UPDATE Towns
SET [Name] = UPPER([Name])
WHERE CountryCode = @countryId";
using SqlCommand updateTownNameToUpperCaseCmd = new SqlCommand(updateTownNamesToUpperCaseQueryText, sqlConnection);
updateTownNameToUpperCaseCmd.Parameters.AddWithValue("@countryId", countryId);
int rowAffected = updateTownNameToUpperCaseCmd.ExecuteNonQuery();
if (rowAffected < 1)
{
Console.WriteLine("No town names were affected.");
return;
}
result.AppendLine($"{rowAffected} town names were affected.");
string getTownsFromCountryQueryText = @"SELECT *
FROM Towns
WHERE CountryCode = @countryId";
using SqlCommand getTownsFromCountryCmd = new SqlCommand(getTownsFromCountryQueryText, sqlConnection);
getTownsFromCountryCmd.Parameters.AddWithValue("@countryId", countryId);
using SqlDataReader reader = getTownsFromCountryCmd.ExecuteReader();
if (!reader.HasRows)
{
Console.WriteLine("No town names were affected.");
return;
}
result.Append("[");
for (int i = 0; i < rowAffected; i++)
{
reader.Read();
if (i + 1 == rowAffected)
{
result.Append(reader["Name"]);
}
else
{
result.Append($"{reader["Name"]}, ");
}
}
result.Append("]");
Console.WriteLine(result.ToString().TrimEnd());
}
public object Read(SqlDataReader reader, int ordinal, Type asType) { return TypeInfo.Read(reader, ordinal, this, asType); }
public ActionResult SelectData()
{
string taskcode = "";
string worktype = "";
string bugtype = "";
string buglevel = "";
string taskstatus = "";
string time1 = "";
string time2 = "";
int pageindex = int.Parse(Request.Params["pageindex"]);
int skip = (pageindex - 1) * 6;
string sql = "";
var user = (from u in medc.ps_user
where u.id == int.Parse(Session["userid"].ToString())
select u).FirstOrDefault();
var role = (from r in medc.ps_role
where r.id == user.roleId
select r).FirstOrDefault();
if (role.roleName == "系统管理员" || role.roleName == "线路管理员")
{
sql = "select a.solveTaskCode,a.taskStatusName,a.workDocTypeName,c.lineCode,c.poleCode,c.bugLevelName,c.bugTypeName,c.discovererName,c.discoverTime,a.issuedByName,a.issuedTime,c.intactRate,c.bugDesc from ps_solvetask_main a inner join ps_solvetask_detail b on a.id=b.taskid inner join ps_inspectiontask_detail c on b.inspectionTaskDelId=c.id where 1=1 and (a.taskstatus=3 or a.taskstatus=6)";
}
else
{
sql = "select a.solveTaskCode,a.taskStatusName,a.workDocTypeName,c.lineCode,c.poleCode,c.bugLevelName,c.bugTypeName,c.discovererName,c.discoverTime,a.issuedByName,a.issuedTime,c.intactRate,c.bugDesc from ps_solvetask_main a inner join ps_solvetask_detail b on a.id=b.taskid inner join ps_inspectiontask_detail c on b.inspectionTaskDelId=c.id inner join ps_solver_detail d on a.id=d.taskid where 1=1 and d.solverCode='"+user.userCode+ "' and (a.taskstatus=3 or a.taskstatus=6) ";
}
if (Request.Params["taskcode"] != "")
{
taskcode = Request.Params["taskcode"];
sql += " and a.solveTaskCode like '%" + taskcode + "%' ";
}
if (Request.Params["taskstatus"] != "")
{
taskstatus = Request.Params["taskstatus"];
sql += " and a.taskstatus='" + taskstatus + "'";
}
if (Request.Params["buglevel"] != "")
{
buglevel = Request.Params["buglevel"];
sql += " and c.bugLevel=" + buglevel;
}
if (Request.Params["bugtype"] != "")
{
bugtype = Request.Params["bugtype"];
sql += " and c.bugType" + bugtype;
}
if (Request.Params["worktype"] != "")
{
worktype = Request.Params["worktype"];
sql += " and a.workDocType =" + worktype;
}
if (Request.Params["time1"] != null)
{
time1 = Request.Params["time1"];
time2 = Request.Params["time2"];
sql += " and a.issuedTime>= '" + time1 + "' and a.issuedTime<='" + time2 + "'";
}
SqlConnection con = new SqlConnection("server=.;database=MyElectrCheck_DB;Integrated Security=true;");
if (con.State != ConnectionState.Open)
{
con.Open();
}
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
List<SolveRecore> list = new List<SolveRecore>();
while (dr.Read())
{
SolveRecore main = new SolveRecore();
main.taskcode = dr["solveTaskCode"].ToString();
main.taskstatusname = dr["taskStatusName"].ToString();
main.worktypename = dr["workDocTypeName"].ToString();
main.issuedname = dr["issuedByName"].ToString();
main.issuedtime = DateTime.Parse(dr["issuedTime"].ToString());
main.linecode = dr["lineCode"].ToString();
main.polecode = dr["poleCode"].ToString();
main.discovername = dr["discovererName"].ToString();
main.discovertime = DateTime.Parse( dr["discoverTime"].ToString());
main.intactrate = int.Parse(dr["intactRate"].ToString());
main.bugdesc = dr["bugDesc"].ToString();
main.buglevelname = dr["bugLevelName"].ToString();
main.bugtypename = dr["bugTypeName"].ToString();
list.Add(main);
}
dr.Close();
con.Close();
DataSource ds = new DataSource();
if (list != null)
{
ds.srlist = list.Skip(skip).Take(6).ToList();
ds.datacount = list.Count();
Session["solvelist"] = ds.srlist;
}
string jsonstr = JsonConvert.SerializeObject(ds);
return Content(jsonstr);
}
private void dashboard_Load(object sender, EventArgs e)
{
con = new SqlConnection("Data Source=TAYYAB\\SQLEXPRESS;Initial Catalog=Lab;User ID=sa;Password=1234");
con.Open();
string query1 = "select * from book where id=1";
SqlCommand cmd1 = new SqlCommand(query1, con);
SqlDataReader reader1 = cmd1.ExecuteReader();
if (reader1.Read())
{
textBox1.Text = reader1[2].ToString();
textBox26.Text = reader1[4].ToString();
textBox39.Text = reader1[3].ToString();
}
con.Close();
con = new SqlConnection("Data Source=TAYYAB\\SQLEXPRESS;Initial Catalog=Lab;User ID=sa;Password=1234");
con.Open();
string query2 = "select * from book where id=2";
SqlCommand cmd2 = new SqlCommand(query2, con);
SqlDataReader reader2 = cmd2.ExecuteReader();
if (reader2.Read())
{
textBox2.Text = reader2[2].ToString();
textBox25.Text = reader2[4].ToString();
textBox38.Text = reader2[3].ToString();
}
con.Close();
con = new SqlConnection("Data Source=TAYYAB\\SQLEXPRESS;Initial Catalog=Lab;User ID=sa;Password=1234");
con.Open();
string query3 = "select * from book where id=3";
SqlCommand cmd3 = new SqlCommand(query3, con);
SqlDataReader reader3 = cmd3.ExecuteReader();
if (reader3.Read())
{
textBox3.Text = reader3[2].ToString();
textBox24.Text = reader3[4].ToString();
textBox37.Text = reader3[3].ToString();
}
con.Close();
con = new SqlConnection("Data Source=TAYYAB\\SQLEXPRESS;Initial Catalog=Lab;User ID=sa;Password=1234");
con.Open();
string query4 = "select * from book where id=4";
SqlCommand cmd4 = new SqlCommand(query4, con);
SqlDataReader reader4 = cmd4.ExecuteReader();
if (reader4.Read())
{
textBox4.Text = reader4[2].ToString();
textBox23.Text = reader4[4].ToString();
textBox36.Text = reader4[3].ToString();
}
con.Close();
con = new SqlConnection("Data Source=TAYYAB\\SQLEXPRESS;Initial Catalog=Lab;User ID=sa;Password=1234");
con.Open();
string query5 = "select * from book where id=5";
SqlCommand cmd5 = new SqlCommand(query5, con);
SqlDataReader reader5 = cmd5.ExecuteReader();
if (reader5.Read())
{
textBox5.Text = reader5[2].ToString();
textBox22.Text = reader5[4].ToString();
textBox35.Text = reader5[3].ToString();
}
con.Close();
con = new SqlConnection("Data Source=TAYYAB\\SQLEXPRESS;Initial Catalog=Lab;User ID=sa;Password=1234");
con.Open();
string query6 = "select * from book where id=6";
SqlCommand cmd6 = new SqlCommand(query6, con);
SqlDataReader reader6 = cmd6.ExecuteReader();
if (reader6.Read())
{
textBox6.Text = reader6[2].ToString();
textBox21.Text = reader6[4].ToString();
textBox34.Text = reader6[3].ToString();
}
con.Close();
con = new SqlConnection("Data Source=TAYYAB\\SQLEXPRESS;Initial Catalog=Lab;User ID=sa;Password=1234");
con.Open();
string query7 = "select * from book where id=7";
SqlCommand cmd7 = new SqlCommand(query7, con);
SqlDataReader reader7 = cmd7.ExecuteReader();
if (reader7.Read())
{
textBox7.Text = reader7[2].ToString();
textBox20.Text = reader7[4].ToString();
textBox33.Text = reader7[3].ToString();
}
con.Close();
con = new SqlConnection("Data Source=TAYYAB\\SQLEXPRESS;Initial Catalog=Lab;User ID=sa;Password=1234");
con.Open();
string query8 = "select * from book where id=8";
SqlCommand cmd8 = new SqlCommand(query8, con);
SqlDataReader reader8 = cmd8.ExecuteReader();
if (reader8.Read())
{
textBox8.Text = reader8[2].ToString();
textBox19.Text = reader8[4].ToString();
textBox32.Text = reader8[3].ToString();
}
con.Close();
con = new SqlConnection("Data Source=TAYYAB\\SQLEXPRESS;Initial Catalog=Lab;User ID=sa;Password=1234");
con.Open();
string query9 = "select * from book where id=9";
SqlCommand cmd9 = new SqlCommand(query9, con);
SqlDataReader reader9 = cmd9.ExecuteReader();
if (reader9.Read())
{
textBox9.Text = reader9[2].ToString();
textBox18.Text = reader9[4].ToString();
textBox31.Text = reader9[3].ToString();
}
con.Close();
con = new SqlConnection("Data Source=TAYYAB\\SQLEXPRESS;Initial Catalog=Lab;User ID=sa;Password=1234");
con.Open();
string query10 = "select * from book where id=10";
SqlCommand cmd10 = new SqlCommand(query10, con);
SqlDataReader reader10 = cmd10.ExecuteReader();
if (reader10.Read())
{
textBox10.Text = reader10[2].ToString();
textBox17.Text = reader10[4].ToString();
textBox30.Text = reader10[3].ToString();
}
con.Close();
con = new SqlConnection("Data Source=TAYYAB\\SQLEXPRESS;Initial Catalog=Lab;User ID=sa;Password=1234");
con.Open();
string query11 = "select * from book where id=11";
SqlCommand cmd11 = new SqlCommand(query11, con);
SqlDataReader reader11 = cmd11.ExecuteReader();
if (reader11.Read())
{
textBox11.Text = reader11[2].ToString();
textBox16.Text = reader11[4].ToString();
textBox29.Text = reader11[3].ToString();
}
con.Close();
con = new SqlConnection("Data Source=TAYYAB\\SQLEXPRESS;Initial Catalog=Lab;User ID=sa;Password=1234");
con.Open();
string query12 = "select * from book where id=12";
SqlCommand cmd12 = new SqlCommand(query12, con);
SqlDataReader reader12 = cmd12.ExecuteReader();
if (reader12.Read())
{
textBox12.Text = reader12[2].ToString();
textBox15.Text = reader12[4].ToString();
textBox28.Text = reader12[3].ToString();
}
con.Close();
con = new SqlConnection("Data Source=TAYYAB\\SQLEXPRESS;Initial Catalog=Lab;User ID=sa;Password=1234");
con.Open();
string query13 = "select * from book where id=13";
SqlCommand cmd13 = new SqlCommand(query13, con);
SqlDataReader reader13 = cmd13.ExecuteReader();
if (reader13.Read())
{
textBox13.Text = reader13[2].ToString();
textBox14.Text = reader13[4].ToString();
textBox27.Text = reader13[3].ToString();
}
con.Close();
textBox1.Enabled = false;
textBox2.Enabled = false;
textBox3.Enabled = false;
textBox4.Enabled = false;
textBox5.Enabled = false;
textBox6.Enabled = false;
textBox7.Enabled = false;
textBox8.Enabled = false;
textBox9.Enabled = false;
textBox10.Enabled = false;
textBox11.Enabled = false;
textBox12.Enabled = false;
textBox13.Enabled = false;
textBox14.Enabled = false;
textBox15.Enabled = false;
textBox16.Enabled = false;
textBox17.Enabled = false;
textBox18.Enabled = false;
textBox19.Enabled = false;
textBox20.Enabled = false;
textBox21.Enabled = false;
textBox22.Enabled = false;
textBox23.Enabled = false;
textBox24.Enabled = false;
textBox25.Enabled = false;
textBox26.Enabled = false;
textBox27.Enabled = false;
textBox28.Enabled = false;
textBox29.Enabled = false;
textBox30.Enabled = false;
textBox31.Enabled = false;
textBox32.Enabled = false;
textBox33.Enabled = false;
textBox34.Enabled = false;
textBox35.Enabled = false;
textBox36.Enabled = false;
textBox37.Enabled = false;
textBox38.Enabled = false;
textBox39.Enabled = false;
}
private void btnConsultarMotorista_Click(object sender, EventArgs e)
{
// Conexao objMotorista = new Conexao();
incluirMotorista _model = new incluirMotorista();
List<incluirMotorista> _lstMotorista = new List<incluirMotorista>();
string strSql = "SELECT * FROM[dbo].[Motoristas] with(nolock) where Matricula = @Matricula";
SqlConnection sqlCon = new SqlConnection(strCon);
SqlCommand comando = new SqlCommand(strSql, sqlCon);
comando.Parameters.Add("@Matricula", SqlDbType.VarChar).Value = txbConsultarMotorista.Text;
try
{
if (txbConsultarMotorista.Text == string.Empty)
{
throw new Exception("Você precisa digitar uma matricula!");
}
sqlCon.Open();
SqlDataReader dr = comando.ExecuteReader();
if (dr.HasRows == false)
{
throw new Exception("Matricula não encontrada!");
}
else
{
while (dr.Read())
{
_lstMotorista.Add(new incluirMotorista(
_model.Matricula = Convert.ToString(dr["Matricula"]),
_model.Nome = Convert.ToString(dr["Nome"]),
_model.Cargo = Convert.ToString(dr["Cargo"]),
_model.DataNascimento = dr["Data_nascimento"] != DBNull.Value ? Convert.ToDateTime(dr["Data_nascimento"]) : DateTime.MinValue,
_model.Rg = Convert.ToString(dr["RG"]),
_model.Cpf = Convert.ToString(dr["CPF"]),
_model.Cnh = Convert.ToString(dr["CNH"]),
_model.Categoria = Convert.ToString(dr["Categoria"]),
_model.Cep = Convert.ToString(dr["CEP"]),
_model.Endereco = Convert.ToString(dr["Endereco"]),
_model.Uf = Convert.ToString(dr["UF"]),
_model.Cidade = Convert.ToString(dr["Cidade"]),
_model.Bairro = Convert.ToString(dr["Bairro"]),
_model.DataAdmissao = dr["Data_admissao"] != DBNull.Value ? Convert.ToDateTime(dr["Data_admissao"]) : DateTime.MinValue,
_model.DataExame = dr["Data_exame"] != DBNull.Value ? Convert.ToDateTime(dr["Data_exame"]) : DateTime.MinValue,
_model.AntCriminais = Convert.ToString(dr["Antecedentes_Criminais"])
));
}
}
if (_lstMotorista.Count > 0)
{
dgvConsultarMotorista.DataSource = _lstMotorista;
txbConsultarMotorista.Text = string.Empty;
}
else
{
MessageBox.Show("Nenhum registro encontrado!");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
sqlCon.Close();
}
}
//Loading notifications
protected void Load_Notifications()
{
//getting empID
String username = HttpContext.Current.User.Identity.Name;
String query = "SELECT empID FROM SystemUser WHERE username='******'";
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SystemUserConnectionString"].ConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand(query, conn);
String empID = (cmd.ExecuteScalar().ToString()).Trim();
//selecting relevant notifications
query = "SELECT notID, action, type, a.name AS assetName, notContent, e.firstName, e.lastname, date, n.status " +
"FROM Notification n INNER JOIN Employee e " +
"ON n.sendUser=e.empID " +
"JOIN Asset a ON n.assetID=a.assetID " +
"WHERE receiveUser='******' " +
"ORDER BY date DESC";
cmd = new SqlCommand(query, conn);
SqlDataReader dr = cmd.ExecuteReader();
int count = 0;
String output = "";
while (dr.Read())
{
output +=
"<div id='" + dr["notID"].ToString().Trim() + "' class='notification";
//Add background color if not-seen
if (dr["status"].ToString().Trim() == "not-seen")
{
output += " not-seen";
count += 1;
}
//setting action
string action = "None";
if (dr["action"].ToString().Trim() == "Recommend")
{
action = "requested";
}
else if (dr["action"].ToString().Trim() == "Approve")
{
action = "recommended";
}
else if (dr["action"].ToString().Trim() == "Cancel")
{
action = "rejected";
}
//setting type
string type = dr["type"].ToString().Trim();
if (type == "AddNew")
{
type = "Register";
}
//setting asset name
string assetName = dr["assetName"].ToString().Trim();
if (assetName.Length > 15)
{
assetName = assetName.Substring(0, 12);
assetName += "...";
}
output +=
"'>" +
" <img class='col-md-3' src='img/" + dr["type"].ToString().Trim() + "Icon.png'/>" +
" <div class='not-content-box col-md-10'>" +
" Asset <strong>" + assetName + "</strong> has been " + action + " to " + type +
" by <strong>" + dr["firstName"].ToString().Trim() + " " + dr["lastName"].ToString().Trim() + "</strong>." +
" <div class='not-date col-md-offset-5 col-md-7'>" + dr["date"].ToString().Trim() + "</div>" +
" </div>" +
"</div>";
}
//set notifications
notificationsBody.InnerHtml = output;
//set count
if (count > 0)
{
notification_count.InnerHtml = Convert.ToString(count);
}
else
{
notification_count.Style.Add("display", "none");
}
dr.Close();
conn.Close();
}
// GET: Cohorts/Delete/5
public ActionResult Delete(int id)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT s.Id AS StudentId, s.FirstName AS StudentFirstName,
s.LastName AS StudentLastName, s.SlackHandle AS StudentSlackHandle,
i.Id AS InstructorId, i.FirstName AS InstructorFirstName,
i.LastName AS InstructorLastName, i.SlackHandle AS InstructorSlackHandle,
c.Id AS CohortId, c.Name AS CohortName
FROM Students s
FULL OUTER JOIN Instructors i ON s.CohortId = i.CohortId
LEFT JOIN Cohorts c ON c.Id = s.CohortId OR c.Id = i.CohortId
WHERE s.CohortId = @id OR i.CohortId = @id
";
cmd.Parameters.Add(new SqlParameter("@id", id));
SqlDataReader reader = cmd.ExecuteReader();
Cohort cohort = null;
if (reader.Read())
{
cohort = new Cohort()
{
Id = reader.GetInt32(reader.GetOrdinal("CohortId")),
Name = reader.GetString(reader.GetOrdinal("CohortName"))
};
if (!reader.IsDBNull(reader.GetOrdinal("StudentId")))
{
Student student = new Student()
{
Id = reader.GetInt32(reader.GetOrdinal("StudentId")),
FirstName = reader.GetString(reader.GetOrdinal("StudentFirstName")),
LastName = reader.GetString(reader.GetOrdinal("StudentLastName")),
SlackHandle = reader.GetString(reader.GetOrdinal("StudentSlackHandle")),
CohortId = reader.GetInt32(reader.GetOrdinal("CohortId"))
};
cohort.StudentList.Add(student);
}
if (!reader.IsDBNull(reader.GetOrdinal("InstructorId")))
{
Instructor instructor = new Instructor()
{
Id = reader.GetInt32(reader.GetOrdinal("InstructorId")),
FirstName = reader.GetString(reader.GetOrdinal("InstructorFirstName")),
LastName = reader.GetString(reader.GetOrdinal("InstructorLastName")),
SlackHandle = reader.GetString(reader.GetOrdinal("InstructorSlackHandle")),
CohortId = reader.GetInt32(reader.GetOrdinal("CohortId"))
};
cohort.InstructorList.Add(instructor);
}
reader.Close();
}
else
{
cohort = GetCohortById(id);
}
return View(cohort);
}
}
}
/// <summary>
/// Performs validation and retrieves data from a client database using the System.Data.SqlClient.SqlConnection using a System.Data.SqlClient.SqlCommand object
/// </summary>
/// <param name="value">System.Data.SqlClient.SqlCommand class containing data retrieval instructions</param>
/// <param name="reader">System.Data.SqlDataReader class to populate with the selected data</param>
public static void SelectData(this SqlCommand value, ref SqlDataReader reader)
{
// initialize a message variable to hold a sql validation message
string validateMessage = string.Empty;
// check to ensure that the stored procedure exists in the database
if (!value.ProcedureExists(false, ref validateMessage))
{
// throw the returned message
throw new ArgumentException(value.CommandText, "StoredProcedure");
}
// check the command text
if (string.IsNullOrWhiteSpace(value.CommandText))
{
throw new System.ArgumentException("SqlCommand parameter has an invalid CommandText value");
}
// check the command type, only System.Data.CommandType.StoredProcedure is allowed
if (value.CommandType != System.Data.CommandType.StoredProcedure)
{
// throw an invalidoperation exception
throw new System.InvalidOperationException("CommandType of the SqlCommand, must be set to System.Data.CommandType.StoredProcedure!");
}
// check to ensure that the connection has been set
if (value.Connection == null)
{
// throw a code to the calling class
throw new System.InvalidOperationException("The SqlCommand object database connection property has not been set.");
}
try
{
// check the command and open it after other resources have been commited, this keeps the connection open only as long as is needed
if (value.Connection.State != System.Data.ConnectionState.Open)
{
value.Connection.Open();
}
// return the datareader
reader = value.ExecuteReader();
}
catch (System.Data.SqlClient.SqlException ex)
{
// check for the error 26, this is the Server/Instance related error indicating that the connections values are incorrect
// or the servicer is down or Microsoft SQL server is not running on the machine
if (ex.Number.Equals(26))
{
throw new ArgumentException("The database endpoint does not appear to have an instance of Microsoft SQL Server running.", "InstanceError");
}
else if (ex.Number == 16 || ex.Number == 201 || ex.Number == 214)
{
throw new ArgumentException(value.CommandText, "StoredProcedureInvalid");
}
else
{
throw ex;
}
}
finally
{
// in case there was an error before the closing of the connection was complete, close the connection
if (value.Connection.State != System.Data.ConnectionState.Closed)
{
value.Connection.Close();
}
}
}
private static void TestReaderMarsCase(string caseName, string connectionString, ReaderTestType testType, ReaderVerificationType verificationType)
{
WeakReference weak = null;
SqlCommand[] cmd = new SqlCommand[CONCURRENT_COMMANDS];
SqlDataReader[] gch = new SqlDataReader[CONCURRENT_COMMANDS];
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
for (int i = 0; i < CONCURRENT_COMMANDS; i++)
{
cmd[i] = con.CreateCommand();
cmd[i].CommandText = COMMAND_TEXT_1;
if ((testType != ReaderTestType.ReaderGC) && (testType != ReaderTestType.ReaderGCConnectionClose))
gch[i] = cmd[i].ExecuteReader();
else
gch[i] = null;
}
for (int i = 0; i < CONCURRENT_COMMANDS; i++)
{
switch (testType)
{
case ReaderTestType.ReaderClose:
gch[i].Dispose();
break;
case ReaderTestType.ReaderDispose:
gch[i].Dispose();
break;
case ReaderTestType.ReaderGC:
weak = OpenNullifyReader(cmd[i]);
GC.Collect();
GC.WaitForPendingFinalizers();
Assert.False(weak.IsAlive, "Transaction is still alive on TestReaderMars: ReaderGC");
break;
case ReaderTestType.ConnectionClose:
GC.SuppressFinalize(gch[i]);
con.Close();
con.Open();
break;
case ReaderTestType.ReaderGCConnectionClose:
weak = OpenNullifyReader(cmd[i]);
GC.Collect();
GC.WaitForPendingFinalizers();
Assert.False(weak.IsAlive, "Transaction is still alive on TestReaderMars: ReaderGCConnectionClose");
con.Close();
con.Open();
break;
}
cmd[i].Dispose();
}
SqlCommand verificationCmd = con.CreateCommand();
switch (verificationType)
{
case ReaderVerificationType.ExecuteReader:
verificationCmd.CommandText = COMMAND_TEXT_2;
using (SqlDataReader rdr = verificationCmd.ExecuteReader())
{
rdr.Read();
DataTestClass.AssertEqualsWithDescription(1, rdr.FieldCount, "Execute Reader should return expected Field count");
DataTestClass.AssertEqualsWithDescription(COLUMN_NAME_2, rdr.GetName(0), "Execute Reader should return expected Field name");
}
break;
case ReaderVerificationType.ChangeDatabase:
con.ChangeDatabase(DATABASE_NAME);
DataTestClass.AssertEqualsWithDescription(DATABASE_NAME, con.Database, "Change Database should return expected Database Name");
break;
case ReaderVerificationType.BeginTransaction:
verificationCmd.Transaction = con.BeginTransaction();
verificationCmd.CommandText = "select @@trancount";
int tranCount = (int)verificationCmd.ExecuteScalar();
DataTestClass.AssertEqualsWithDescription(1, tranCount, "Begin Transaction should return expected Transaction count");
break;
}
verificationCmd.Dispose();
}
}
public List <TRegistro_ViewSaldoCarencia> BuscarSaldoCarencia(string vCD_Empresa,
string vID_Taxa,
string vNR_Contrato,
string vCD_Produto)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("select CD_Empresa, ID_Taxa, Nr_Contrato, dt_saldo, cd_produto, Tot_Saldo");
sql.AppendLine("from VTB_GRO_SaldoCarencia");
sql.AppendLine("where CD_Empresa = '" + vCD_Empresa + "'");
if (!string.IsNullOrEmpty(vID_Taxa))
{
sql.AppendLine(" and ID_Taxa = " + vID_Taxa);
}
if (!string.IsNullOrEmpty(vNR_Contrato))
{
sql.AppendLine(" and Nr_Contrato = " + vNR_Contrato);
}
if (vCD_Produto != "")
{
sql.AppendLine(" and CD_Produto = '" + vCD_Produto + "'");
}
sql.AppendLine(" Order by DT_Saldo ");
bool podeFecharBco = false;
if (Banco_Dados == null)
{
this.CriarBanco_Dados(false);
podeFecharBco = true;
}
List <TRegistro_ViewSaldoCarencia> lista = new List <TRegistro_ViewSaldoCarencia>();
SqlDataReader reader = this.ExecutarBusca(sql.ToString());
try
{
while (reader.Read())
{
lista.Add(new TRegistro_ViewSaldoCarencia()
{
CD_Empresa = reader.GetString(reader.GetOrdinal("CD_Empresa")),
CD_Produto = reader.GetString(reader.GetOrdinal("CD_Produto")),
DT_Saldo = reader.GetDateTime(reader.GetOrdinal("DT_Saldo")),
ID_Taxa = reader.GetDecimal(reader.GetOrdinal("ID_Taxa")),
NR_Contrato = reader.GetDecimal(reader.GetOrdinal("NR_Contrato")),
Tot_Saldo = reader.GetDecimal(reader.GetOrdinal("TOT_SALDO"))
});
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
this.deletarBanco_Dados();
}
}
return(lista);
}
/// <summary>
/// 执行分页查询
/// </summary>
/// <param name="pWhereConditions">筛选条件</param>
/// <param name="pOrderBys">排序</param>
/// <param name="pPageSize">每页的记录数</param>
/// <param name="pCurrentPageIndex">以0开始的当前页码</param>
/// <returns></returns>
public PagedQueryResult <T_SuperRetailTraderProfitDetailEntity> PagedQuery(IWhereCondition[] pWhereConditions, OrderBy[] pOrderBys, int pPageSize, int pCurrentPageIndex)
{
//组织SQL
StringBuilder pagedSql = new StringBuilder();
StringBuilder totalCountSql = new StringBuilder();
//分页SQL
pagedSql.AppendFormat("select * from (select row_number()over( order by ");
if (pOrderBys != null && pOrderBys.Length > 0)
{
foreach (var item in pOrderBys)
{
if (item != null)
{
pagedSql.AppendFormat(" {0} {1},", StringUtils.WrapperSQLServerObject(item.FieldName), item.Direction == OrderByDirections.Asc ? "asc" : "desc");
}
}
pagedSql.Remove(pagedSql.Length - 1, 1);
}
else
{
pagedSql.AppendFormat(" [Id] desc"); //默认为主键值倒序
}
pagedSql.AppendFormat(") as ___rn,* from [T_SuperRetailTraderProfitDetail] where 1=1 and isdelete=0 ");
//总记录数SQL
totalCountSql.AppendFormat("select count(1) from [T_SuperRetailTraderProfitDetail] where 1=1 and isdelete=0 ");
//过滤条件
if (pWhereConditions != null)
{
foreach (var item in pWhereConditions)
{
if (item != null)
{
pagedSql.AppendFormat(" and {0}", item.GetExpression());
totalCountSql.AppendFormat(" and {0}", item.GetExpression());
}
}
}
pagedSql.AppendFormat(") as A ");
//取指定页的数据
pagedSql.AppendFormat(" where ___rn >{0} and ___rn <={1}", pPageSize * (pCurrentPageIndex - 1), pPageSize * (pCurrentPageIndex));
//执行语句并返回结果
PagedQueryResult <T_SuperRetailTraderProfitDetailEntity> result = new PagedQueryResult <T_SuperRetailTraderProfitDetailEntity>();
List <T_SuperRetailTraderProfitDetailEntity> list = new List <T_SuperRetailTraderProfitDetailEntity>();
using (SqlDataReader rdr = this.SQLHelper.ExecuteReader(pagedSql.ToString()))
{
while (rdr.Read())
{
T_SuperRetailTraderProfitDetailEntity m;
this.Load(rdr, out m);
list.Add(m);
}
}
result.Entities = list.ToArray();
int totalCount = Convert.ToInt32(this.SQLHelper.ExecuteScalar(totalCountSql.ToString())); //计算总行数
result.RowCount = totalCount;
int remainder = 0;
result.PageCount = Math.DivRem(totalCount, pPageSize, out remainder);
if (remainder > 0)
{
result.PageCount++;
}
return(result);
}
private static bool IsNotString(SqlDataReader rdr, int column)
{
if (!rdr.IsDBNull(column))
{
try
{
rdr.GetString(column);
return false;
}
catch (InvalidCastException)
{
}
}
try
{
rdr.GetSqlString(column);
return false;
}
catch (InvalidCastException)
{
}
try
{
rdr.GetSqlChars(column);
return false;
}
catch (InvalidCastException)
{
}
object o = rdr.GetValue(column);
if (o is string)
{
return false;
}
o = rdr.GetSqlValue(column);
if (o is SqlString)
{
return false;
}
return true;
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["email"] == null)
{
Response.Redirect("home.aspx");
}
try
{
con.Open();
cmd = new SqlCommand("select * from registration where email='" + Session["email"].ToString() + "'", con);
dr = cmd.ExecuteReader();
dr.Read();
Label1.Text = dr["username"].ToString();
con.Close();
con.Open();
cmd = new SqlCommand("WITH myTableWithRows AS (SELECT (ROW_NUMBER() OVER (ORDER BY sell.id)) as row,* FROM sell where email='" + Session["email"] + "') SELECT * FROM myTableWithRows WHERE row = 1", con);
dr = cmd.ExecuteReader();
dr.Read();
Image3.ImageUrl = dr["image1"].ToString();
Label11.Text = dr["property_name"].ToString();
Label12.Text = dr["city"].ToString();
Label13.Text = dr["area"].ToString();
Label14.Text = dr["price"].ToString();
Label15.Text = dr["bed_rooms"].ToString();
Label16.Text = dr["description"].ToString();
con.Close();
con.Open();
cmd = new SqlCommand("WITH myTableWithRows AS (SELECT (ROW_NUMBER() OVER (ORDER BY sell.id)) as row,* FROM sell where email='" + Session["email"] + "') SELECT * FROM myTableWithRows WHERE row = 2", con);
dr = cmd.ExecuteReader();
dr.Read();
Image4.ImageUrl = dr["image1"].ToString();
Label17.Text = dr["property_name"].ToString();
Label18.Text = dr["city"].ToString();
Label19.Text = dr["area"].ToString();
Label20.Text = dr["price"].ToString();
Label21.Text = dr["bed_rooms"].ToString();
Label22.Text = dr["description"].ToString();
con.Close();
con.Open();
cmd = new SqlCommand("WITH myTableWithRows AS (SELECT (ROW_NUMBER() OVER (ORDER BY sell.id)) as row,* FROM sell where email='" + Session["email"] + "') SELECT * FROM myTableWithRows WHERE row = 3", con);
dr = cmd.ExecuteReader();
dr.Read();
Image5.ImageUrl = dr["image1"].ToString();
Label23.Text = dr["property_name"].ToString();
Label24.Text = dr["city"].ToString();
Label25.Text = dr["area"].ToString();
Label26.Text = dr["price"].ToString();
Label27.Text = dr["bed_rooms"].ToString();
Label28.Text = dr["description"].ToString();
con.Close();
con.Open();
cmd = new SqlCommand("WITH myTableWithRows AS (SELECT (ROW_NUMBER() OVER (ORDER BY sell.id)) as row,* FROM sell where email='" + Session["email"] + "') SELECT * FROM myTableWithRows WHERE row = 4", con);
dr = cmd.ExecuteReader();
dr.Read();
Image6.ImageUrl = dr["image1"].ToString();
Label29.Text = dr["property_name"].ToString();
Label30.Text = dr["city"].ToString();
Label31.Text = dr["area"].ToString();
Label32.Text = dr["price"].ToString();
Label33.Text = dr["bed_rooms"].ToString();
Label34.Text = dr["description"].ToString();
con.Close();
con.Open();
cmd = new SqlCommand("WITH myTableWithRows AS (SELECT (ROW_NUMBER() OVER (ORDER BY sell.id)) as row,* FROM sell where email='" + Session["email"] + "') SELECT * FROM myTableWithRows WHERE row = 5", con);
dr = cmd.ExecuteReader();
dr.Read();
Image7.ImageUrl = dr["image1"].ToString();
Label35.Text = dr["property_name"].ToString();
Label36.Text = dr["city"].ToString();
Label37.Text = dr["area"].ToString();
Label38.Text = dr["price"].ToString();
Label39.Text = dr["bed_rooms"].ToString();
Label40.Text = dr["description"].ToString();
con.Close();
}
catch (Exception) { }
}
private void AddNodesFromSQL(TreeNode baseNode)
{
try
{
sqlCon.Open();
sqlQuery = new SqlCommand("SELECT * FROM tblCons ORDER BY PositionID ASC", sqlCon);
sqlRd = sqlQuery.ExecuteReader(CommandBehavior.CloseConnection);
if (sqlRd.HasRows == false)
{
return;
}
TreeNode tNode;
while (sqlRd.Read())
{
tNode = new TreeNode((string)(sqlRd["Name"]));
//baseNode.Nodes.Add(tNode)
if (Tree.Node.GetNodeTypeFromString((string)(sqlRd["Type"])) == Tree.Node.Type.Connection)
{
Connection.Info conI = GetConnectionInfoFromSQL();
conI.TreeNode = tNode;
//conI.Parent = _previousContainer 'NEW
this._ConnectionList.Add(conI);
tNode.Tag = conI;
if (SQLUpdate == true)
{
Connection.Info prevCon = PreviousConnectionList.FindByConstantID(conI.ConstantID);
if (prevCon != null)
{
foreach (mRemoteNC.Base prot in prevCon.OpenConnections)
{
prot.InterfaceControl.Info = conI;
conI.OpenConnections.Add(prot);
}
if (conI.OpenConnections.Count > 0)
{
tNode.ImageIndex = System.Convert.ToInt32(Images.Enums.TreeImage.ConnectionOpen);
tNode.SelectedImageIndex =
System.Convert.ToInt32(Images.Enums.TreeImage.ConnectionOpen);
}
else
{
tNode.ImageIndex =
System.Convert.ToInt32(Images.Enums.TreeImage.ConnectionClosed);
tNode.SelectedImageIndex =
System.Convert.ToInt32(Images.Enums.TreeImage.ConnectionClosed);
}
}
else
{
tNode.ImageIndex = System.Convert.ToInt32(Images.Enums.TreeImage.ConnectionClosed);
tNode.SelectedImageIndex =
System.Convert.ToInt32(Images.Enums.TreeImage.ConnectionClosed);
}
if (conI.ConstantID == _PreviousSelected)
{
selNode = tNode;
}
}
else
{
tNode.ImageIndex = System.Convert.ToInt32(Images.Enums.TreeImage.ConnectionClosed);
tNode.SelectedImageIndex =
System.Convert.ToInt32(Images.Enums.TreeImage.ConnectionClosed);
}
}
else if (Tree.Node.GetNodeTypeFromString((string)(sqlRd["Type"])) == Tree.Node.Type.Container)
{
Container.Info contI = new Container.Info();
//If tNode.Parent IsNot Nothing Then
// If Tree.Node.GetNodeType(tNode.Parent) = Tree.Node.Type.Container Then
// contI.Parent = tNode.Parent.Tag
// End If
//End If
//_previousContainer = contI 'NEW
contI.TreeNode = tNode;
contI.Name = (string)(sqlRd["Name"]);
Connection.Info conI;
conI = GetConnectionInfoFromSQL();
conI.Parent = contI;
conI.IsContainer = true;
contI.ConnectionInfo = conI;
if (SQLUpdate == true)
{
Container.Info prevCont = PreviousContainerList.FindByConstantID(conI.ConstantID);
if (prevCont != null)
{
contI.IsExpanded = prevCont.IsExpanded;
}
if (conI.ConstantID == _PreviousSelected)
{
selNode = tNode;
}
}
else
{
if (Convert.ToBoolean(sqlRd["Expanded"]) == true)
{
contI.IsExpanded = true;
}
else
{
contI.IsExpanded = false;
}
}
this._ContainerList.Add(contI);
this._ConnectionList.Add(conI);
tNode.Tag = contI;
tNode.ImageIndex = System.Convert.ToInt32(Images.Enums.TreeImage.Container);
tNode.SelectedImageIndex = System.Convert.ToInt32(Images.Enums.TreeImage.Container);
}
if (Convert.ToInt32(sqlRd["ParentID"]) != 0)
{
TreeNode pNode = Tree.Node.GetNodeFromConstantID((string)(sqlRd["ParentID"]));
if (pNode != null)
{
pNode.Nodes.Add(tNode);
if (Tree.Node.GetNodeType(tNode) == Tree.Node.Type.Connection)
{
(tNode.Tag as Connection.Info).Parent = pNode.Tag;
}
else if (Tree.Node.GetNodeType(tNode) == Tree.Node.Type.Container)
{
(tNode.Tag as Container.Info).Parent = pNode.Tag;
}
}
else
{
baseNode.Nodes.Add(tNode);
}
}
else
{
baseNode.Nodes.Add(tNode);
}
//AddNodesFromSQL(tNode)
}
}
catch (Exception ex)
{
Runtime.MessageCollector.AddMessage(Messages.MessageClass.ErrorMsg,
Language.strAddNodesFromSqlFailed + Constants.vbNewLine +
ex.Message, true);
}
}
protected void page_load(object sender, EventArgs e)
{
mydb hkdb = new mydb();
hkdb.VeriSess("1");
if (!IsPostBack)
{
//公共信息
if (this.Session["systype"].ToString() == "A") //主登录页
{
}
else //子登录页
{
if (this.Session["systype"].ToString() == "R")
{
mydb.Alert("提示:首次点击【报表示例】中的报表时，需要1分钟左右的加载时间，还请耐心等待！");
}
}
//物业
if (bd.CheckRepeat("SELECT Xh FROM hk_Init WHERE ProduID='F' AND ChildVersion='0'") == "true")
{
lblpms.Text = "0";
}
else
{
lblpms.Text = "1";
}
//资产
if (bd.CheckRepeat("SELECT Xh FROM hk_Init WHERE ProduID='E' AND ChildVersion='0'") == "true")
{
lbleam.Text = "0";
}
else
{
lbleam.Text = "1";
}
GridViewBind();
}
string strPath = "";
SqlDataReader datarTB = SqlHelper.ExecuteReader("select a.*,b.name,b.id as bid,b.Path,b.ProduID from hk_MyDesk a left outer join hk_DeskTop b on (a.ssdmid=b.id) where username='******' order by orderid asc");
this.Label.Text = null;
int glTMP1 = 0;
this.Label.Text += "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\" >";
this.Label.Text += "<tr>";
while (datarTB.Read())
{
string lstr = null;
if (datarTB["bid"].ToString() == "yj")
{
SqlDataReader datar = SqlHelper.ExecuteReader("select top " + datarTB["topnum"] + " jsid,jsbt,fssj,fsrName from hk_Mail_JS where jsr='" + this.Session["userid"] + "' and scbz='否' and jszt='0' order by fssj desc");
lstr += "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\">";
while (datar.Read())
{
lstr += "<tr><td> ·<a class=\"gvlink\" href=portal/yjgl/Viewyj.aspx?type=s&id=" + datar["jsid"] + " title=\"发送人：" + datar["fsrName"] + " 发送时间：" + datar["fssj"] + "\">" + datar["jsbt"] + "</a></td><td>" + datar["fsrName"] + "</td><td>" + string.Format("{0:d}", datar["fssj"]) + "</td></tr>";
}
lstr += "</table>";
datar.Close();
}
//P未读邮件
if (datarTB["bid"].ToString() == "gg")
{
SqlDataReader datar = SqlHelper.ExecuteReader("select top " + datarTB["topnum"] + " ggid,ggzt,lrr,lrsj from hk_QYGG where zt='已发布' and lx='企业' and gglb='1' and getdate() between kssj and jssj order by lrsj desc");
lstr += "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\">";
while (datar.Read())
{
lstr += "<tr><td > ·<a class=\"gvlink\" href=# onclick=javascript:popMod('portal/xzgl/qygg_view.aspx?id=" + datar["ggid"] + "') title=\"录入人：" + datar["lrr"] + " \">" + datar["ggzt"] + "</a></td><td>" + string.Format("{0:d}", datar["lrsj"]) + "</td></tr>";
}
lstr += "</table>";
datar.Close();
}
//P最新公告
if (datarTB["bid"].ToString() == "gz")
{
SqlDataReader datar = SqlHelper.ExecuteReader("select top " + datarTB["topnum"] + " * from hk_GZZD where zt='已发布' and lx='企业' order by cjsj desc");
lstr += "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\">";
while (datar.Read())
{
lstr += "<tr><td> ·<a class=\"gvlink\" href=# onclick=javascript:popMod('portal/xzgl/gzzd_view.aspx?id=" + datar["zdid"] + "') title=\"创建人：" + datar["cjr"] + " 发文单位：" + datar["fwdw"] + " \">" + datar["zdbt"] + "</a></td><td>" + string.Format("{0:d}", datar["cjsj"]) + "</td></tr>";
}
lstr += "</table>";
datar.Close();
}
//P规章制度
if (datarTB["bid"].ToString() == "jh")
{
SqlDataReader datar = SqlHelper.ExecuteReader("select top " + datarTB["topnum"] + " jhid,ztmb,jhyf,jhnf,zt,jhz from OA_GZJH01 where jhz='" + this.Session["userid"] + "' and jhsx='0' order by jhnf desc,jhyf desc");
lstr += "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\">";
while (datar.Read())
{
lstr += "<tr><td > ·<a class=\"gvlink\" href=# onclick=javascript:popMod('oa/jhgl/wdjh_mod.aspx?id=" + datar["jhid"] + "') title=\"计划者：" + datar["jhz"] + " \">" + datar["ztmb"] + "</a></td><td>" + datar["zt"] + "</td><td>" + datar["jhnf"] + "-" + datar["jhyf"] + "</td></tr>";
}
lstr += "</table>";
datar.Close();
}
//O我的计划
if (datarTB["bid"].ToString() == "hy")
{
SqlDataReader datar = SqlHelper.ExecuteReader("select top " + datarTB["topnum"] + " a.id,a.hyzt,a.kssj,a.fqr from oa_hyqc a where zt='已审批' and a.kssj>=GETDATE() and cjry like '%" + this.Session["userid"].ToString() + ",%' order by a.fqsj desc");
lstr += "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\">";
while (datar.Read())
{
lstr += "<tr><td > ·<a class=\"gvlink\" href=# onclick=javascript:popMod('oa/hygl/hycx_view.aspx?id=" + datar["id"] + "') title=\"发起人：" + datar["fqr"] + " \">" + datar["hyzt"] + "</a></td><td>" + string.Format("{0:d}", datar["kssj"]) + "</td></tr>";
}
lstr += "</table>";
datar.Close();
}
//O我的会议
if (datarTB["bid"].ToString() == "zs")
{
SqlDataReader datar = SqlHelper.ExecuteReader("select top " + datarTB["topnum"] + " * from OA_KnowLedge where State= '正常' order by LastTime desc");
lstr += "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\">";
while (datar.Read())
{
lstr += "<tr><td > ·<a class=\"gvlink\" href=# onclick=javascript:popMod('oa/zsgl/zsshow.aspx?id=" + datar["id"] + "') title=\"状态：" + datar["state"] + " \">" + datar["Title"] + "</a></td><td>" + string.Format("{0:d}", datar["LastTime"]) + "</td></tr>";
}
lstr += "</table>";
datar.Close();
}
//O知识中心
if (datarTB["bid"].ToString() == "xw")
{
SqlDataReader datar = SqlHelper.ExecuteReader("select top " + datarTB["topnum"] + " * from OA_XWNR where zt='已发布' order by cjsj desc");
lstr += "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\">";
while (datar.Read())
{
lstr += "<tr><td > ·<a class=\"gvlink\" href=# onclick=javascript:popMod('oa/xwgl/xwcx_view.aspx?id=" + datar["xwid"] + "') title=\"作者：" + datar["xwzz"] + " 新闻来源：" + datar["xwly"] + " \">" + datar["xwbt"] + "</a></td><td>" + string.Format("{0:d}", datar["cjsj"]) + "</td></tr>";
}
lstr += "</table>";
datar.Close();
}
//O新闻中心
if (datarTB["bid"].ToString() == "zc")
{
SqlDataReader datar = SqlHelper.ExecuteReader("select top " + datarTB["topnum"] + " wpid,wpmc,wpzt,wptxm from as_wp where bgrid= '" + this.Session["userid"] + "' order by wptxm desc");
lstr += "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\">";
while (datar.Read())
{
if (lbleam.Text == "0")
{
lstr += "<tr><td > ·<a class=\"gvlink\" href=# onclick=javascript:popMod('eam/zcbb/Zcll_View.aspx?id=" + datar["wpid"] + "')>" + datar["wpmc"] + "+(" + datar["wptxm"] + ")</a></td><td>" + datar["wpzt"] + "</td></tr>";
}
else
{
lstr += "<tr><td > ·<a class=\"gvlink\" href=# onclick=javascript:popMod('eam2/zcbb/Zcll_View.aspx?id=" + datar["wpid"] + "')>" + datar["wpmc"] + "+(" + datar["wptxm"] + ")</a></td><td>" + datar["wpzt"] + "</td></tr>";
}
}
lstr += "</table>";
datar.Close();
}
//E我保管的资产
if (datarTB["bid"].ToString() == "wp")
{
SqlDataReader datar = SqlHelper.ExecuteReader("select top " + datarTB["topnum"] + " lyid,lydh,lyzt,sqrmc,sqsj from as_lyd01 where sqrid= '" + this.Session["userid"] + "' order by sqsj desc");
lstr += "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\">";
while (datar.Read())
{
if (lbleam.Text == "0")
{
lstr += "<tr><td > ·<a class=\"gvlink\" href=# onclick=javascript:popWin_w('eam/wply/lydlr_mod.aspx?id=" + datar["lyid"] + "') title=\"申请人：" + datar["sqrmc"] + " \">" + datar["lydh"] + "</a></td><td>" + datar["lyzt"] + "</td><td>" + string.Format("{0:d}", datar["sqsj"]) + "</td></tr>";
}
else
{
lstr += "<tr><td > ·<a class=\"gvlink\" href=# onclick=javascript:popWin_w('eam2/wply/lydlr_mod.aspx?id=" + datar["lyid"] + "') title=\"申请人：" + datar["sqrmc"] + " \">" + datar["lydh"] + "</a></td><td>" + datar["lyzt"] + "</td><td>" + string.Format("{0:d}", datar["sqsj"]) + "</td></tr>";
}
}
lstr += "</table>";
datar.Close();
}
//E我领用的物品
if (datarTB["bid"].ToString() == "cx")
{
SqlDataReader datar = SqlHelper.ExecuteReader("select top " + datarTB["topnum"] + " cx_id,cx_zt,cx_fbr,cx_sdate from SC_CXHD where cx_sdate<=GETDATE() and cx_edate >=GETDATE()");
lstr += "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\">";
while (datar.Read())
{
lstr += "<tr><td > ·<a class=\"gvlink\" href=# onclick=javascript:popMod('scm/cxtj/cxhdcx_view.aspx?url=index&id=" + datar["cx_id"] + "') title=\"发布人：" + datar["cx_fbr"] + " \">" + datar["cx_zt"] + "</a></td><td>" + string.Format("{0:d}", datar["cx_sdate"]) + "</td></tr>";
}
lstr += "</table>";
datar.Close();
}
//G促销活动
if (datarTB["bid"].ToString() == "bd")
{
SqlDataReader datar = SqlHelper.ExecuteReader("select top " + datarTB["topnum"] + " bd_id,bd_bt,bd_zt,bd_sdate,bd_scr from SC_BD01 where bd_zt='正常' and bd_sdate<=GETDATE() and bd_edate>=GETDATE()");
lstr += "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\">";
while (datar.Read())
{
lstr += "<tr><td > ·<a class=\"gvlink\" href=# onclick=javascript:popMod('scm/bdgl/bdcx_view.aspx?id=" + datar["bd_id"] + "') title=\"生成人：" + datar["bd_scr"] + " \">" + datar["bd_bt"] + "</a></td><td>" + datar["bd_zt"] + "</td><td>" + string.Format("{0:d}", datar["bd_sdate"]) + "</td></tr>";
}
lstr += "</table>";
datar.Close();
}
//G排行榜单
if (datarTB["bid"].ToString() == "mb")
{
SqlDataReader datar = SqlHelper.ExecuteReader("select top " + datarTB["topnum"] + " mbid,mbmc,jdsj from VI_MBDA where mbzt='已启用'");
lstr += "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\">";
while (datar.Read())
{
lstr += "<tr><td > ·<a class=\"gvlink\" href=# onclick=javascript:popMod('vip/mbgl/mbcx_view.aspx?id=" + datar["mbid"] + "')>" + datar["mbmc"] + "</a></td><td>" + string.Format("{0:d}", datar["jdsj"]) + "</td></tr>";
}
lstr += "</table>";
datar.Close();
}
//V模板中心
if (datarTB["bid"].ToString() == "lp")
{
if (lblpms.Text == "0")
{
string str = "";
//判断公司
if (this.Session["qyid"].ToString() != "001")
{
str = str + " where ssgs= '" + this.Session["qyid"] + "'";
}
SqlDataReader datar = SqlHelper.ExecuteReader("select top " + datarTB["topnum"] + " lpid,name,qy_jc from fc_lp a left outer JOIN QY b on(a.ssgs=b.qy_id)" + str + " order by lpid desc");
lstr += "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\">";
while (datar.Read())
{
lstr += "<tr><td > ·<a class=\"gvlink\" href=# onclick=javascript:popMod('pms/zzgl/xqxx_view.aspx?id=" + datar["lpid"] + "&type=r')>" + datar["name"] + "</a></td><td>" + datar["qy_jc"] + "</td></tr>";
}
lstr += "</table>";
datar.Close();
}
else
{
SqlDataReader datar = SqlHelper.ExecuteReader("select top " + datarTB["topnum"] + " lpid,name from fc_lp a order by lpid desc");
lstr += "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\">";
while (datar.Read())
{
lstr += "<tr><td > ·<a class=\"gvlink\" href=# onclick=javascript:popMod('pms2/zzgl/xqxx_view.aspx?id=" + datar["lpid"] + "&type=r')>" + datar["name"] + "</a></td></tr>";
}
lstr += "</table>";
datar.Close();
}
}//F最新房产
if (datarTB["bid"].ToString() == "ts")
{
if (lblpms.Text == "0")
{
string str = "";
//判断公司
if (this.Session["qyid"].ToString() != "001")
{
str = str + " where e.ssgs= '" + this.Session["qyid"] + "'";
}
SqlDataReader datar = SqlHelper.ExecuteReader("select top " + datarTB["topnum"] + " a.id,a.cellcode,a.tsnr,a.tsdate from zh_zhts a left outer join fc_cell b on a.cellcode=b.cellid left outer join FC_DY c on (b.ssdyid=c.dyid) left outer join FC_LG d on (c.sslgid=d.lgid) left outer join FC_LP e on (d.sslpid=e.lpid) left outer JOIN QY f on (e.ssgs=f.qy_id)" + str + "order by a.id desc");
lstr += "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\">";
while (datar.Read())
{
lstr += "<tr><td > ·<a class=\"gvlink\" href=# onclick=javascript:popMod('pms/yzgl/zhtscx_show.aspx?id=" + datar["id"] + "&cellid=" + datar["cellcode"] + "')>" + datar["tsnr"] + "</a></td><td>" + string.Format("{0:d}", datar["tsdate"]) + "</td></tr>";
}
lstr += "</table>";
datar.Close();
}
else
{
SqlDataReader datar = SqlHelper.ExecuteReader("select top " + datarTB["topnum"] + " a.id,a.cellcode,a.tsnr,a.tsdate from zh_zhts a order by a.id desc");
lstr += "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\">";
while (datar.Read())
{
lstr += "<tr><td > ·<a class=\"gvlink\" href=# onclick=javascript:popMod('pms2/yzgl/zhtscx_show.aspx?id=" + datar["id"] + "&cellid=" + datar["cellcode"] + "')>" + datar["tsnr"] + "</a></td><td>" + string.Format("{0:d}", datar["tsdate"]) + "</td></tr>";
}
lstr += "</table>";
datar.Close();
}
}//F业主投诉
if (datarTB["bid"].ToString() == "yw")
{
if (lblpms.Text == "0")
{
string str = "";
//判断公司
if (this.Session["qyid"].ToString() != "001")
{
str = str + " and ssgs= '" + this.Session["qyid"] + "'";
}
SqlDataReader datar = SqlHelper.ExecuteReader("select top " + datarTB["topnum"] + " id,title,ddate from WY_YWHHY where DDATE>=getdate() " + str + " order by ddate");
lstr += "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\">";
while (datar.Read())
{
lstr += "<tr><td > ·<a class=\"gvlink\" href=# onclick=javascript:popMod('pms/ywhgl/ywhhycx_view.aspx?id=" + datar["id"] + "')>" + datar["title"] + "</a></td><td>" + string.Format("{0:d}", datar["ddate"]) + "</td></tr>";
}
lstr += "</table>";
datar.Close();
}
else
{
SqlDataReader datar = SqlHelper.ExecuteReader("select top " + datarTB["topnum"] + " id,title,ddate from WY_YWHHY where DDATE>=getdate() order by ddate");
lstr += "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\">";
while (datar.Read())
{
lstr += "<tr><td > ·<a class=\"gvlink\" href=# onclick=javascript:popMod('pms2/ywhgl/ywhhycx_view.aspx?id=" + datar["id"] + "')>" + datar["title"] + "</a></td><td>" + string.Format("{0:d}", datar["ddate"]) + "</td></tr>";
}
lstr += "</table>";
datar.Close();
}
}//F待开会议
switch (datarTB["ProduID"].ToString())
{
case "F":
if (lblpms.Text == "0")
{
strPath = "pms/" + datarTB["Path"].ToString();
}
else
{
strPath = "pms2/" + datarTB["Path"].ToString();
}
break;
case "E":
if (lbleam.Text == "0")
{
strPath = "eam/" + datarTB["Path"].ToString();
}
else
{
strPath = "eam2/" + datarTB["Path"].ToString();
}
break;
default:
strPath = datarTB["Path"].ToString();
break;
}
this.Label.Text += " <td width=\"49%\" valign=\"top\"> <table width=\"100%\" height=\"12\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\"><tr><td></td> </tr></table><table width=\"100%\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\"><tr><td valign=\"top\" ><table width=\"100%\" height=\"5\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\"><tr> <td width=\"5\" background=\"images/head_bg_m.gif\"><img src=\"images/head_bg_l.gif\" /></td> <td width=\"142\" background=\"images/head_bg_m.gif\"><img src=\"images/arrow.gif\" /> <a href=\"" + strPath + "\" class=\"top_text\" title=\"查看全部\" target=\"_blank\"><strong><u>" + datarTB["Name"].ToString() + "</u></strong></a></td><td background=\"images/head_bg_m.gif\" align=\"right\"><A href=\"javascript:_update(" + datarTB["id"].ToString() + ");\"><img src=\"images/index_set.gif\" border=\"0\" /></a> <A href=\"javascript:_del(" + datarTB["id"].ToString() + ");\"><img src=\"images/index_del.gif\" border=\"0\" /></a></td><td width=\"12\" background=\"images/head_bg_m.gif\" align=\"right\"><img src=\"images/head_bg_r.gif\" /></td></tr></table><table width=\"100%\" height=\"" + int.Parse(datarTB["topnum"].ToString()) * 25 + "px\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\" bgcolor=\"#FFFFFF\" style=\"border-right: #6795B4 1px solid;border-left: #6795B4 1px solid;\"><tr ><td valign=\"top\">" + lstr + "</td></tr></table><table width=\"100%\" height=\"1\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\" style=\"border-bottom: #6795B4 1px solid;\" ><tr> <td ></td></tr></table></td></tr> </table></td><td width=\"2%\"> </td>";
//this.Label.Text += " <td width=\"49%\" valign=\"top\"> <table width=\"100%\" height=\"12\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\"><tr><td></td> </tr></table><table width=\"100%\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\"><tr><td valign=\"top\" ><table width=\"100%\" height=\"5\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\"><tr> <td width=\"5\" background=\"images/head_bg_m.gif\"><img src=\"images/head_bg_l.gif\" /></td> <td width=\"142\" background=\"images/head_bg_m.gif\"><img src=\"images/arrow.gif\" /> <strong>" + datarTB["Name"].ToString() + "</strong></td><td background=\"images/head_bg_m.gif\" align=\"right\"><A href=\"javascript:_update(" + datarTB["id"].ToString() + ");\"><img src=\"images/index_set.gif\" border=\"0\" /></a> <A href=\"javascript:_del(" + datarTB["id"].ToString() + ");\"><img src=\"images/index_del.gif\" border=\"0\" /></a></td><td width=\"12\" background=\"images/head_bg_m.gif\" align=\"right\"><img src=\"images/head_bg_r.gif\" /></td></tr></table><table width=\"100%\" height=\"" + int.Parse(datarTB["topnum"].ToString()) * 25 + "px\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\" bgcolor=\"#FFFFFF\" style=\"border-right: #6795B4 1px solid;border-left: #6795B4 1px solid;\"><tr ><td valign=\"top\">" + lstr + "</td></tr></table><table width=\"100%\" height=\"1\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\" style=\"border-bottom: #6795B4 1px solid;\" ><tr> <td ></td></tr></table></td></tr> </table></td><td width=\"2%\"> </td>";
glTMP1 = glTMP1 + 1;
if (glTMP1 == 2)
{
Label.Text += "</tr><TR>";
glTMP1 = 0;
}
}
this.Label.Text += " </table>";
datarTB.Close();
}
private void button6_Click(object sender, EventArgs e)
{
SqlConnection.OpenAsync();
SqlDataReader sqlReader = null;
if ((textBox2.Text != "" && textBox3.Text != "") || (textBox2.Text != "" && textBox4.Text != "") || (textBox3.Text != "" && textBox4.Text != ""))
{
MessageBox.Show("Доступен только один вариант поиска!\nпожалуйста, оставьте данные только в одной ячейке поиска");
textBox4.Text = "";
textBox3.Text = "";
textBox2.Text = "";
return;
}
listBox1.Items.Clear();
if (textBox2.Text != "")
{
SqlCommand command = new SqlCommand("SELECT * FROM [Students] WHERE зачёты='" + Convert.ToString(5 - int.Parse(textBox2.Text)) + "'", SqlConnection);
try
{
sqlReader = command.ExecuteReader();
while (sqlReader.Read())
{
String student = String.Format("ID: {0,2} | ФИО {1,-35} | группа-{2,-6} | зачёты-{3,-1} из 5 |средний балл-{4,-3} ", sqlReader["Id"], sqlReader["ФИО"], sqlReader["группа"], sqlReader["зачёты"], sqlReader["экзамены"]);
listBox1.Items.Add(student);
}
}
catch (System.Data.SqlClient.SqlException)
{
MessageBox.Show("Имя отсутствует");
}
finally
{
if (sqlReader != null)
{
sqlReader.Close();
}
}
textBox2.Text = "";
}
else if (textBox3.Text != "")
{
SqlCommand command = new SqlCommand("SELECT * FROM [Students] WHERE экзамены='" + textBox3.Text + "'", SqlConnection);
try
{
sqlReader = command.ExecuteReader();
while (sqlReader.Read())
{
String student = String.Format("ID: {0,2} | ФИО {1,-35} | группа-{2,-6} | зачёты-{3,-1} из 5 |средний балл-{4,-3} ", sqlReader["Id"], sqlReader["ФИО"], sqlReader["группа"], sqlReader["зачёты"], sqlReader["экзамены"]);
listBox1.Items.Add(student);
}
}
catch (System.Data.SqlClient.SqlException)
{
MessageBox.Show("Неверный формат ввода.\nСредний балл вводится через точку!");
}
finally
{
if (sqlReader != null)
{
sqlReader.Close();
}
}
textBox3.Text = "";
}
else if (textBox4.Text != "")
{
SqlCommand command = new SqlCommand("SELECT * FROM [Students] WHERE зачёты='" + textBox4.Text + "'", SqlConnection);
try
{
sqlReader = command.ExecuteReader();
while (sqlReader.Read())
{
String student = String.Format("ID: {0,2} | ФИО {1,-35} | группа-{2,-6} | зачёты-{3,-1} из 5 |средний балл-{4,-3} ", sqlReader["Id"], sqlReader["ФИО"], sqlReader["группа"], sqlReader["зачёты"], sqlReader["экзамены"]);
listBox1.Items.Add(student);
}
}
catch (System.Data.SqlClient.SqlException)
{
MessageBox.Show("Неверный формат ввода.\n количество зачётов-целое число");
}
finally
{
if (sqlReader != null)
{
sqlReader.Close();
}
}
textBox4.Text = "";
}
}
public string Show1()
{
try
{
//從配置檔讀取字串
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
//格式化字串
string connStrFormat = string.Format(connStr, "localhost", "1433", "Northwind", "sa", "sa");
using (SqlConnection conn = new SqlConnection(connStrFormat))
{
string str = "";
string sql = "select * from dbo.Employees where TitleOfCourtesy=@titleOfCourtesy and Country=@country";
SqlCommand cmd = new SqlCommand(sql, conn);
//方式一:
//cmd.Parameters.Add(new SqlParameter("@titleOfCourtesy", "Mr."));
//cmd.Parameters.Add(new SqlParameter("@country", "UK"));
//方式二:
//SqlParameter titleOfCourtesy = cmd.Parameters.Add("@titleOfCourtesy", SqlDbType.NVarChar, 25);
//titleOfCourtesy.Value = "Mr.";
//SqlParameter country = cmd.Parameters.Add("@country", SqlDbType.NVarChar, 15);
//country.Value = "UK";
//方式三:**
cmd.Parameters.AddWithValue("@titleOfCourtesy", "Mr.");
cmd.Parameters.AddWithValue("@country", "UK");
//方式四:
//SqlParameter titleOfCourtesy = new SqlParameter("@titleOfCourtesy", "Mr.");
//SqlParameter country = new SqlParameter("@country", "UK");
//List<SqlParameter> lists = new List<SqlParameter>();
//lists.Add(titleOfCourtesy);
//lists.Add(country);
// cmd.Parameters.AddRange(lists.ToArray<SqlParameter>());
//方式五:
//SqlParameter titleOfCourtesy = new SqlParameter("@titleOfCourtesy", "Mr.");
//SqlParameter country = new SqlParameter("@country", "UK");
//SqlParameter[] parameters = { titleOfCourtesy, country };
//cmd.Parameters.AddRange(parameters);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (dr.HasRows)
{
while (dr.Read())
{
str += string.Format("TitleOfCourtesy= {0}, Country= {1}{2}", dr[0], dr[1], "\r\n");
}
}
return(str);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
}
protected void Page_Load(object sender, EventArgs e)
{
RSORequest request;
RSOResponse response = new RSOResponse();
string email = String.Empty;
int rsoID = -1;
response.error = String.Empty;
// Need passed in store id and number of requested results.
// 1. Deserialize the incoming Json.
try
{
request = GetRequestInfo();
}
catch (Exception ex)
{
response.error = ex.Message.ToString();
// Return the results as Json.
SendInfoAsJson(response);
return;
}
// Do stuff here.
//Get the User's email
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
try
{
connection.Open();
string sql = String.Format("SELECT * FROM EventUser WHERE userID=@un");
SqlCommand command = new SqlCommand( sql, connection );
command.Parameters.Add(new SqlParameter("@un", request.userID));
SqlDataReader reader = command.ExecuteReader();
if( reader.Read() )
{
email = Convert.ToString( reader["email"] );
}
if( response.error != "")
{
SendInfoAsJson(response);
return;
}
}
catch (Exception ex)
{
response.error = ex.Message.ToString();
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
//Get the rso's ID
try
{
connection.Open();
string sql = String.Format("SELECT * FROM StudentOrg WHERE name=@rs");
SqlCommand command2 = new SqlCommand( sql, connection );
command2.Parameters.Add(new SqlParameter("@rs", request.rsoName));
SqlDataReader reader2 = command2.ExecuteReader();
if( reader2.Read() )
{
rsoID = Convert.ToInt32( reader2["rsoID"] );
}
if( response.error != "")
{
SendInfoAsJson(response);
return;
}
}
catch (Exception ex)
{
response.error = ex.Message.ToString();
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
//Add this user to the rsoToUsers table but not as a full member yet
try
{
connection.Open();
string sql = String.Format("INSERT into rsoToUsers (rsoID, userID, groupMember, userEmail) VALUES ('{0}', '{1}', '{2}', '{3}')", rsoID, request.userID, 0, email);
SqlCommand command3 = new SqlCommand( sql, connection );
command3.ExecuteNonQuery();
}
catch (Exception ex)
{
response.error = ex.Message.ToString();
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
response.message = "Request sent!";
SendInfoAsJson(response);
}
public override void runQuery()
{
if (KeziAzonositas) // munkáltató rész
{
// A LEKÉRDEZÉS FELÉPÍTÉSE
string query1 = "SELECT count(*)";
string query2 = "SELECT pnr_id,adoszam,adoazonosito_jel,megnevezes,nev,ir_szam,helyseg,cim";
string querystring = " FROM partnerek WHERE (pnr_tipus='GTG' or (pnr_tipus='SZMLY' and egyeni_vall='I')) and ";
querystring = (txPnrid.Text != string.Empty ? querystring + "pnr_id like " + txPnrid.Text + " and " : querystring);
querystring = (txAdoszam.Text != string.Empty ? querystring + "adoszam like '" + txAdoszam.Text + "' and " : querystring);
querystring = (txAdoazon.Text != string.Empty ? querystring + "adoazonosito_jel like '" + txAdoazon.Text + "' and " : querystring);
querystring = txMegnev.Text != string.Empty ? querystring + "megnevezes like '" + txMegnev.Text + "' and " : querystring;
querystring = txEV.Text != string.Empty ? querystring + "nev like '" + txEV.Text + "' and " : querystring;
querystring = (txIrszam.Text != string.Empty ? querystring + "ir_szam like '" + txIrszam.Text + "' and " : querystring);
querystring = (txHelyseg.Text != string.Empty ? querystring + "helyseg like '" + txHelyseg.Text + "' and " : querystring);
querystring = (txCim.Text != string.Empty ? querystring + "cim like '" + txCim.Text + "' and " : querystring);
querystring += "(ervenytelen is null or ervenytelen!='I')";
// mennyiségi ellenőrzés
query1 += querystring;
scommand = new SqlCommand(query1, sconn);
int Mcount = (int)scommand.ExecuteScalar();
// A munkáltatói adatok lekérdezése és betöltése
querystring += " order by nev;";
query2 += querystring;
scommand = new SqlCommand(query2, sconn);
if (Mcount > 0)
{
try { da.Dispose(); }
catch { } // első keresés
da = new SqlDataAdapter(scommand);
dt = new DataTable();
try
{
da.Fill(dt);
}
catch
{
//TraceBejegyzes(ex.Message);
}
BeazonFoglKivalaszt bfk = new BeazonFoglKivalaszt(dt, this);
bfk.ShowDialog(this);
txPnrid.Text = this.FoglPnrid.ToString();
txAdoszam.Text = this.adoszam;
txAdoazon.Text = this.adoazon;
txMegnev.Text = this.megnev;
txEV.Text = this.ev;
txHelyseg.Text = this.helyseg;
txCim.Text = this.cim;
txIrszam.Text = this.irszam;
}
else
{
SqlDataReader myReader = null;
try
{
if (sconn.State == ConnectionState.Closed) sconn.Open();
myReader = scommand.ExecuteReader();
if (myReader.HasRows)
{
while (myReader.Read())
{
txPnrid.Text = myReader["pnr_id"].ToString();
txMegnev.Text = myReader["megnevezes"].ToString();
txEV.Text = myReader["nev"].ToString();
txAdoszam.Text = myReader["adoszam"].ToString();
txEV.Text = myReader["nev"].ToString();
txHelyseg.Text = myReader["helyseg"].ToString();
txCim.Text = myReader["cim"].ToString();
txIrszam.Text = myReader["ir_szam"].ToString();
}
}
else
{
MessageBox.Show("Nincs találat!");
}
}
catch (Exception ex)
{
MessageBox.Show("Hibás adat!" + ex.Message);
//TraceBejegyzes(ex.Message);
}
myReader.Close();
}
txPnrid.ReadOnly = true;
txAdoszam.ReadOnly = true;
txAdoazon.ReadOnly = true;
txMegnev.ReadOnly = true;
txEV.ReadOnly = true;
txHelyseg.ReadOnly = true;
txCim.ReadOnly = true;
txIrszam.ReadOnly = true;
txPnrid.ReadOnly = true;
txAdoszam.ReadOnly = true;
txAdoazon.ReadOnly = true;
txMegnev.ReadOnly = true;
txEV.ReadOnly = true;
txHelyseg.ReadOnly = true;
txCim.ReadOnly = true;
txIrszam.ReadOnly = true;
save(); // automatikus mentés
KeziAzonositas = false;
}
else // banki tétel rész
{
DateTime erteknap, letrehozas;
string erteknapstr = string.Empty, letrehozasstr = string.Empty;
if (datErteknap.Text != string.Empty)
{
try
{
DateTransformA();
erteknap = DateTime.Parse(datErteknap.Text);
erteknapstr = erteknap.ToShortDateString();
erteknapstr = erteknapstr.Substring(0, 4) + "-" + erteknapstr.Substring(5, 2) + "-" + erteknapstr.Substring(8, 2);
}
catch
{
MessageBox.Show("Dátum formátum nem megfelelő!");
return;
}
}
if (datLetrehoz.Text != string.Empty)
{
try
{
DateTransformB();
letrehozas = DateTime.Parse(datLetrehoz.Text);
letrehozasstr = letrehozas.ToShortDateString();
letrehozasstr = letrehozasstr.Substring(0, 4) + "-" + letrehozasstr.Substring(5, 2) + "-" + letrehozasstr.Substring(8, 2);
}
catch
{
MessageBox.Show("Dátum formátum nem megfelelő!");
return;
}
}
// A LEKÉRDEZÉS FELÉPÍTÉSE
string querystring1 = "SELECT count(bkt_id) FROM banki_kivonatok WHERE ";
querystring1 = datErteknap.Text != string.Empty ? querystring1 + "bizonylat_kelte like '" + erteknapstr + "' and " : querystring1;
querystring1 = (datLetrehoz.Text != string.Empty ? querystring1 + "letrehozas_datuma like '" + letrehozasstr + "' and " : querystring1);
querystring1 = (txKivonatSzama.Value != 0 ? querystring1 + "kivonat_szama = " + txKivonatSzama.Value + " and " : querystring1);
querystring1 = (cbIrany.Text != string.Empty ? querystring1 + "irany like '" + cbIrany.Text + "' and " : querystring1);
querystring1 = (txMegjegyzes.Text != string.Empty ? querystring1 + "megjegyzes like '" + txMegjegyzes.Text + "' and " : querystring1);
querystring1 += "bizonylat_kelte is not null;";
int darab = 0;
try
{
scommand = new SqlCommand(querystring1, sconn);
darab = (int)scommand.ExecuteScalar();
}
catch (Exception ex)
{
MessageBox.Show("SQL Hiba " + ex.Message);
}
string querystring2 = "SELECT bkt_id,bizonylat_kelte,kivonat_szama,irany,letrehozas_datuma,megjegyzes FROM banki_kivonatok WHERE ";
querystring2 = datErteknap.Text != string.Empty ? querystring2 + "bizonylat_kelte like '" + erteknapstr + "' and " : querystring2;
querystring2 = (datLetrehoz.Text != string.Empty ? querystring2 + "letrehozas_datuma like '" + letrehozasstr + "' and " : querystring2);
querystring2 = (txKivonatSzama.Value != 0 ? querystring2 + "kivonat_szama = " + txKivonatSzama.Value + " and " : querystring2);
querystring2 = (cbIrany.Text != string.Empty ? querystring2 + "irany like '" + cbIrany.Text + "' and " : querystring2);
querystring2 = (txMegjegyzes.Text != string.Empty ? querystring2 + "megjegyzes like '" + txMegjegyzes.Text + "' and " : querystring2);
querystring2 += "bizonylat_kelte is not null order by 1,2;";
if (darab > 1)
{
scommand = new SqlCommand(querystring2, sconn);
try { da.Dispose(); }
catch { } // első keresés
da = new SqlDataAdapter(scommand);
dt = new DataTable();
try
{
da.Fill(dt);
}
catch
{
//TraceBejegyzes(ex.Message);
}
BankiKivonKivalasztB bkk = new BankiKivonKivalasztB(dt, this);
bkk.ShowDialog(this);
datErteknap.Text = DateTime.Parse(this.erteknap).ToShortDateString();
txKivonatSzama.Value = this.kivonatSzama;
cbIrany.Text = this.irany;
datLetrehoz.Text = DateTime.Parse(this.letrehozas).ToShortDateString();
txId.Text = this.bkt_id.ToString();
}
else
{
SqlDataReader myReader = null;
scommand = new SqlCommand(querystring2, sconn);
try
{
if (sconn.State == ConnectionState.Closed) sconn.Open();
myReader = scommand.ExecuteReader();
while (myReader.Read())
{
datErteknap.Text = DateTime.Parse(myReader["bizonylat_kelte"].ToString()).ToShortDateString();
txKivonatSzama.Text = myReader["kivonat_szama"].ToString();
cbIrany.Text = myReader["irany"].ToString();
datLetrehoz.Text = DateTime.Parse(myReader["letrehozas_datuma"].ToString()).ToShortDateString();
txId.Text = myReader["bkt_id"].ToString();
}
}
catch (Exception ex)
{
MessageBox.Show("SQL Hiba " + ex.Message);
TraceBejegyzes(ex.Message);
}
myReader.Close();
}
// datagrid betöltése
dgrLoad();
txOsszeg.Focus();
datErteknap.ReadOnly = true;
datLetrehoz.ReadOnly = true;
txKivonatSzama.Enabled = false;
cbIrany.Enabled = false;
tsFind.Enabled = false;
tsSearch.Enabled = true;
tsNew.Enabled = true;
tsSave.Enabled = false;
if (dgvBankKiv.RowCount > 0)
{
tsUpdate.Enabled = true;
tsDelete.Enabled = true;
}
}
}
// GET: TrainingPrograms
public ActionResult IndexPastTraining()
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @" SELECT tp.Id AS TrainingId, tp.[Name] AS TrainingName, tp.StartDate, tp.EndDate, tp.MaxAttendees, e.FirstName, e.LastName, e.Id AS EmployeeId
FROM TrainingProgram tp
LEFT JOIN EmployeeTraining et ON et.TrainingProgramId = tp.Id
LEFT JOIN Employee e ON et.EmployeeId = e.Id
WHERE EndDate < @today";
//
cmd.Parameters.Add(new SqlParameter("@today", DateTime.Now));
SqlDataReader reader = cmd.ExecuteReader();
List<TrainingWithEmployees> trainingPrograms = new List<TrainingWithEmployees>();
int IdOrdinal = reader.GetOrdinal("TrainingId");
int NameOrdinal = reader.GetOrdinal("TrainingName");
int StartDateOrdinal = reader.GetOrdinal("StartDate");
int EndDateOrdinal = reader.GetOrdinal("EndDate");
int MaxAttendeesOrdinal = reader.GetOrdinal("MaxAttendees");
while (reader.Read())
{
var trainingId = reader.GetInt32(reader.GetOrdinal("TrainingId"));
var trainingAlreadyAdded = trainingPrograms.FirstOrDefault(d => d.Id == trainingId);
if (trainingAlreadyAdded == null)
{
TrainingWithEmployees trainingProgram = new TrainingWithEmployees
{
Id = reader.GetInt32(IdOrdinal),
Name = reader.GetString(NameOrdinal),
StartDate = reader.GetDateTime(StartDateOrdinal),
EndDate = reader.GetDateTime(EndDateOrdinal),
MaxAttendees = reader.GetInt32(MaxAttendeesOrdinal)
};
trainingPrograms.Add(trainingProgram);
var hasEmployee = !reader.IsDBNull(reader.GetOrdinal("EmployeeId"));
if (hasEmployee)
{
trainingProgram.EmployeesAttending.Add(new BasicEmployee()
{
Id = reader.GetInt32(reader.GetOrdinal("EmployeeId")),
FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
LastName = reader.GetString(reader.GetOrdinal("LastName")),
});
}
}
else
{
var hasEmployee = !reader.IsDBNull(reader.GetOrdinal("EmployeeId"));
if (hasEmployee)
{
trainingAlreadyAdded.EmployeesAttending.Add(new BasicEmployee()
{
Id = reader.GetInt32(reader.GetOrdinal("EmployeeId")),
FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
LastName = reader.GetString(reader.GetOrdinal("LastName")),
});
}
}
}
reader.Close();
return View(trainingPrograms);
}
}
}
private void bAutoAzon_Click(object sender, EventArgs e)
{
// Automatikus azonosítás
if (txPnrid.Text != string.Empty) { MessageBox.Show("A tétel már azonosításra került. Kézi azonosítással felülbírálható."); return; }
// adószám keresése
string kozlemenyString = txKozlemeny.Text;
string adoszamstr = string.Empty;
int adoszam;
bool talalt = false;
foreach (char x in kozlemenyString)
{
if (x <= '9' && x >= '0')
{
adoszamstr += x;
if (adoszamstr.Length == 8)
{
talalt = true;
break;
}
}
else
{
// ha nincs 8 egybefüggő számjegy, akkor nem lesz jó
if (adoszamstr.Length < 8)
{
adoszamstr = string.Empty;
}
}
}
// ha találtunk egy 8 számjegy hosszú egybefüggő számsort, megpróbálom beazonosítani a munkáltatót
int PartnerId = 0;
if (talalt)
{
adoszam = int.Parse(adoszamstr);
// egyértelműen beazonosítható-e?
string query = "select count(pnr_id) from partnerek where adoszam like '" + adoszamstr + "%';";
scommand = new SqlCommand(query, sconn);
try
{
PartnerId = (int)scommand.ExecuteScalar();
}
catch (Exception ex)
{
TraceBejegyzes(ex.Message);
}
if (PartnerId == 1)
{
// A munkáltatói adatok lekérdezése és betöltése
SqlDataReader myReader = null;
scommand = new SqlCommand("spFoglAdatai2", sconn);
scommand.CommandType = CommandType.StoredProcedure;
scommand.Parameters.Add(new SqlParameter("@adoszam", SqlDbType.VarChar, 15)).Value = adoszamstr + "%";
try
{
if (sconn.State == ConnectionState.Closed) sconn.Open();
myReader = scommand.ExecuteReader();
while (myReader.Read())
{
txPnrid.Text = myReader["pnr_id"].ToString();
//this.FoglId = (int)myReader["pnr_id"];
txMegnev.Text = myReader["megnevezes"].ToString();
txEV.Text = myReader["nev"].ToString();
txAdoszam.Text = myReader["adoszam"].ToString();
txEV.Text = myReader["nev"].ToString();
txHelyseg.Text = myReader["helyseg"].ToString();
txCim.Text = myReader["cim"].ToString();
txIrszam.Text = myReader["ir_szam"].ToString();
}
}
catch
{
MessageBox.Show("Hibás adat!");
//TraceBejegyzes(ex.Message);
}
myReader.Close();
// Az adatok mentése
// update bankkivonat_tetelek set pnr_id=txPnrid.Text where btl_id=txBtlId.Text
query = "UPDATE bankkivonat_tetelek SET pnr_id=" + txPnrid.Text + " WHERE btl_id=" + txBtlId.Text;
scommand = new SqlCommand(query, sconn);
try
{
scommand.ExecuteNonQuery();
}
catch (Exception ex)
{
TraceBejegyzes(ex.Message);
}
}
else if (PartnerId > 1)
{
MessageBox.Show("Partner nem azonosítható egyértelműen!");
}
else if (PartnerId == 0)
{
MessageBox.Show("Partner nem azonosítható!");
}
}
else
{
MessageBox.Show("Partner nem azonosítható!");
}
txPnrid.ReadOnly = true;
txAdoszam.ReadOnly = true;
txAdoazon.ReadOnly = true;
txMegnev.ReadOnly = true;
txEV.ReadOnly = true;
txHelyseg.ReadOnly = true;
txCim.ReadOnly = true;
txIrszam.ReadOnly = true;
tsSave.Enabled = false;
}
public List<ExperimentsInfo> GetNextExperiments(string experimentName, string experimentDate, string userName, int startRowIndex, int rowNumber, string sortColumnName, string sortDirection)
{
List<ExperimentsInfo> recordList = new List<ExperimentsInfo>();
try
{
string connectionString = WanTai.Common.Configuration.GetConnectionString();
///todo: add control,each role user only can see the specific experiments
string commandText = "SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY @orderBy) AS [ROW_NUMBER], [t0].*"
+ " FROM [dbo].[ExperimentsInfo] AS [t0] {0}) AS [t1] WHERE [t1].[ROW_NUMBER] BETWEEN @startIndex AND @endIndex"
+ " ORDER BY [t1].[ROW_NUMBER]";
string defaulOrderBy = "StartTime desc";
if (!string.IsNullOrEmpty(sortColumnName) && !string.IsNullOrEmpty(sortDirection))
{
defaulOrderBy = sortColumnName + " " + sortDirection;
}
commandText = commandText.Replace("@orderBy", defaulOrderBy);
StringBuilder sbWhere = new StringBuilder();
if (!string.IsNullOrEmpty(experimentName))
{
string experimentName2 = experimentName.Replace("'", "''").Replace("[", "[[]");
sbWhere.Append(" WHERE [t0].ExperimentName like '%" + experimentName2 + "%'");
}
if (!string.IsNullOrEmpty(experimentDate))
{
sbWhere.Append(sbWhere.Length > 0 ? " AND " : " WHERE ");
sbWhere.Append(" StartTime between CONVERT(datetime,'" + experimentDate + "',101) and DATEADD(dd, 1, CONVERT(datetime,'" + experimentDate + "',101))");
}
if (!string.IsNullOrEmpty(userName))
{
sbWhere.Append(sbWhere.Length > 0 ? " AND " : " WHERE ");
sbWhere.Append(" LoginName='" + userName + "'");
}
// UserInfoController userInfoController = new UserInfoController();
// userInfoController.GetRoleByUserName(SessionInfo.LoginName);
// RoleInfo userRole = userInfoController.GetRoleByUserName(SessionInfo.LoginName);
// sbWhere.Append(sbWhere.Length > 0 ? " AND " : " WHERE ");
// if (userInfoController.GetAuthorize(AccessAuthority.ExperimentHistory) == AccessAuthority.Self)
// {
// sbWhere.Append(" LoginName='" + SessionInfo.LoginName + "'");
// }
// else
// sbWhere.Append(@" 1=1 ");
//// {
//// sbWhere.Append(@" (LoginName is null or LoginName in
//// (select LoginName from UserInfo u left join roleinfo r on r.RoleName=u.RoleName where r.RoleLevel<=" + userRole.RoleLevel + " ))");
//// }
commandText = String.Format(commandText, sbWhere.ToString());
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
cmd.Parameters.AddWithValue("@startIndex", startRowIndex);
cmd.Parameters.AddWithValue("@endIndex", startRowIndex + rowNumber - 1);
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default))
{
while (reader.Read())
{
ExperimentsInfo info = new ExperimentsInfo();
info.ExperimentID = (Guid)reader.GetValue(1);
info.ExperimentName = reader.GetValue(2).ToString();
info.StartTime = (DateTime)reader.GetValue(3);
if (reader.GetValue(4) != DBNull.Value)
{
info.EndTime = (DateTime)reader.GetValue(4);
}
info.LoginName = reader.GetValue(5).ToString();
if (reader.GetValue(6) != DBNull.Value)
{
info.Remark = reader.GetValue(6).ToString();
}
info.State = (short)reader.GetValue(7);
recordList.Add(info);
}
}
}
}
}
catch (Exception e)
{
string errorMessage = e.Message + System.Environment.NewLine + e.StackTrace;
LogInfoController.AddLogInfo(LogInfoLevelEnum.Error, errorMessage, SessionInfo.LoginName, this.GetType().ToString() + "->GetNextExperiments", SessionInfo.ExperimentID);
throw;
}
return recordList;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
lbl.Text = "Book Id : " + Session["id"];
SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select Name from Authors a join BookAuth ba ON a.Author_id = ba.Author_id " +
"where Book_id = @Book_id;";
cmd.Parameters.AddWithValue("@Book_id", Session["id"]);
cn.Open();
SqlDataReader drEmps = cmd.ExecuteReader();
List <string> authors = new List <string>();
while (drEmps.Read())
{
authors.Add(drEmps["Name"].ToString());
}
drEmps.Close();
string authorList = "";
foreach (string author in authors)
{
authorList += author + ",";
}
lbl2.Text = authorList;
//For Owner
cmd.CommandText = "select Name, Email, Mobileno from UserInfo u join Books b on b.User_id = u.User_id " +
"where Book_id = @Bookid;";
cmd.Parameters.AddWithValue("@Bookid", Session["id"]);
drEmps = cmd.ExecuteReader();
while (drEmps.Read())
{
lbl5.Text = drEmps["Name"].ToString();
lbl3.Text = drEmps["Email"].ToString();
lbl4.Text = drEmps["Mobileno"].ToString();
}
drEmps.Close();
cn.Close();
SqlConnection con = new SqlConnection();
con.ConnectionString = @"Data Source=(LocalDb)\MSSqlLocalDb;Initial Catalog=Suraj;Integrated Security=True;Pooling=False";
SqlCommand cmdgrid = new SqlCommand();
cmdgrid.Connection = con;
cmdgrid.CommandType = CommandType.Text;
cmdgrid.CommandText = "select Pic ,Title,Original_price,Selling_price, No_of_pages,Publication, Category, Exchange,Status_book from Books where Book_id = @Bookid1;";
cmdgrid.Parameters.AddWithValue("@Bookid1", Session["id"]);
con.Open();
SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = cmdgrid;
DataSet ds = new DataSet();
sda.Fill(ds, "Books");
GridView1.DataSource = ds.Tables["Books"];
GridView1.DataBind();
}
}
public override void Bad()
{
double data;
data = double.MinValue; /* Initialize data */
/* Read data from a database */
{
SqlConnection connection = null;
SqlDataReader dr = null;
try
{
/* setup the connection */
using (connection = IO.GetDBConnection())
{
connection.Open();
/* prepare and execute a (hardcoded) query */
SqlCommand command = new SqlCommand(null, connection);
command.CommandText = "select name from users where id=0";
command.Prepare();
dr = command.ExecuteReader();
/* FLAW: Read data from a database query SqlDataReader */
string stringNumber = dr.GetString(1);
if (stringNumber != null) /* avoid NPD incidental warnings */
{
try
{
data = double.Parse(stringNumber.Trim());
}
catch (FormatException exceptNumberFormat)
{
IO.Logger.Log(NLog.LogLevel.Warn, exceptNumberFormat, "Number format exception parsing data from string");
}
}
}
}
catch (SqlException exceptSql)
{
IO.Logger.Log(NLog.LogLevel.Warn, exceptSql, "Error with SQL statement");
}
finally
{
/* Close database objects */
try
{
if (dr != null)
{
dr.Close();
}
}
catch (Exception except) /* INCIDENTAL: CWE 396 Catch Generic Exception */
{
IO.Logger.Log(NLog.LogLevel.Warn, except, "Error closing SqlDataReader");
}
try
{
if (connection != null)
{
connection.Close();
}
}
catch (SqlException exceptSql)
{
IO.Logger.Log(NLog.LogLevel.Warn, exceptSql, "Error closing Connection");
}
}
}
Container dataContainer = new Container();
dataContainer.containerOne = data;
CWE197_Numeric_Truncation_Error__double_database_to_int_67b.BadSink(dataContainer);
}
private void worker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
if (e.Error != null)
{
System.Windows.Forms.MessageBox.Show(e.Error.ToString(), "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
}
else if (e.Cancelled)
{
System.Windows.Forms.MessageBox.Show("Operation cancelled.", "Information");
}
bool busy = false;
foreach (KeyValuePair <int, SubmissionWorker> q in workers)
{
if (q.Value.IsBusy)
{
busy = true;
break;
}
}
if (!busy)
{
if (createJobgroup)
{
SqlConnection sql = new SqlConnection(db);
sql.Open();
string[] cats = categories.Split(',');
foreach (string cat in cats)
{
SqlCommand cmd = new SqlCommand("INSERT INTO JobGroups (Name,Creator,Category,Note) VALUES (" +
"'" + jobgroupName + "'," +
"'" + username + "'," +
"'" + cat + "'," +
"''); SELECT SCOPE_IDENTITY() as NewID;", sql);
SqlDataReader r = cmd.ExecuteReader();
int gid = 0;
if (r.Read())
{
gid = (int)r[0];
}
else
{
System.Windows.Forms.MessageBox.Show("Jobgroup creation failed.", "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
r.Close();
goto JGCF;
}
r.Close();
foreach (KeyValuePair <int, SubmissionWorker> q in workers)
{
if (q.Value.haveJobId && q.Value.cat == cat)
{
cmd = new SqlCommand("INSERT INTO JobGroupData (JobID,GroupID) VALUES (" + q.Value.JobId + "," + gid + ");", sql);
cmd.ExecuteNonQuery();
}
}
}
}
JGCF:
if (this.IsVisible)
{
DialogResult = true;
}
Mouse.OverrideCursor = null;
Close();
}
}
protected void btnLogin_Click(object sender, EventArgs e)
{
if (Gender.SelectedItem.Value == "Admin")
{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("select * from [loginadmin] where adminid=@name and password=@pass", conn);
cmd.Parameters.AddWithValue("@name", txtBoxUsername.Text);
cmd.Parameters.AddWithValue("@pass", txtBoxPass.Text);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
//Response.Write("<script>alert('You Are Succesfully Logged In!!');</script>");
//Server.Transfer("Admin_Dashboard.aspx", true);
//Response.Redirect("Admin_Dashboard.aspx");
HttpCookie cookie = Request.Cookies["Preferences"];
if (cookie == null)
{
cookie = new HttpCookie("Preferences");
}
cookie["username"] = "******";
cookie.Expires = DateTime.Now.AddYears(1);
Response.Cookies.Add(cookie);
Response.Write("Cookie created: " + cookie["username"]);
///////////////////////////////////////////////////////////
Session["user"] = txtBoxUsername.Text;
ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('You Are Succesfully Logged In!!');window.location ='Admin_Dashboard.aspx';", true);
rdr.Close();
conn.Close();
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Invalid Login!! Try Again');window.location ='Login.aspx';", true);
// Response.Redirect(Request.RawUrl);
conn.Close();
}
} //End of Try
catch (Exception ex)
{
Response.Write("<script>alert('" + Server.HtmlEncode(ex.Message) + "')</script>");
conn.Close();
}
finally
{
conn.Close();
}
}
else if (Gender.SelectedItem.Value == "Customer")
{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("select * from [loginuser] where username=@name and password=@pass", conn);
cmd.Parameters.AddWithValue("@name", txtBoxUsername.Text);
cmd.Parameters.AddWithValue("@pass", txtBoxPass.Text);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
//Server.Transfer("Admin_Dashboard.aspx", true);
Session["user"] = txtBoxUsername.Text;
Response.Redirect("Customer_Dashboard.aspx?id=" + txtBoxUsername.Text);
//ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('You Are Succesfully Logged In!!');window.location ='Customer_Dashboard.aspx';", true);
rdr.Close();
conn.Close();
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Invalid Login!! Try Again');window.location ='Login.aspx';", true);
// Response.Redirect(Request.RawUrl);
conn.Close();
}
} //End of Try
catch (Exception ex)
{
Response.Write("<script>alert('" + Server.HtmlEncode(ex.Message) + "')</script>");
conn.Close();
}
finally
{
conn.Close();
}
}
else
{
Response.Write("<script>alert('You Have to Select Either Admin or Customer!!');</script>");
}
}
private static int IsRecordsAffected(SqlDataReader DBreader)
{
return(DBreader.RecordsAffected);
}
public PendAsyncReadsScope(SqlDataReader reader, int? errorCode = null)
{
_reader = reader;
_errorCode = errorCode;
TdsParserStateObjectHelper.ForcePendingReadsToWaitForUser = true;
}
public void GetTableDetail(string tableName)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(@"SELECT
tableName = case when a.colorder=1 then d.name else '' end,
tableNameDes = case when a.colorder=1 then isnull(f.value,'') else '' end,
columName = a.name,
IsIdentity = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '0' end,
IsIdentity_PK = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '1' else '0' end,
TYPE = b.name,
length = a.length,
长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
IsNULL = case when a.isnullable=1 then '1'else '0' end,
defaultValue = isnull(e.text,''),
columNameDes = isnull(g.[value],'')
FROM
syscolumns a
left join
systypes b
on
a.xusertype=b.xusertype
inner join
sysobjects d
on
a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join
syscomments e
on
a.cdefault=e.id
left join
sys.extended_properties g
on
a.id=G.major_id and a.colid=g.minor_id
left join
sys.extended_properties f
on
d.id=f.major_id and f.minor_id=0
where
d.name='{0}' --如果只查询指定表,加上此红色where条件，tablename是要查询的表名；去除红色where条件查询说有的表信息
order by
a.id,a.colorder", tableName);
string connectionString = "Data Source=.;Initial Catalog=VisualSmart;Persist Security Info=True;User ID=sa;Password=Fengjian1234.;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand sqlCom = new SqlCommand(sb.ToString());
using (SqlDataReader objReader = sqlCom.ExecuteReader())
{
if (objReader.Read())
{
TableDetail model = new TableDetail();
model.columName = objReader["columName"].ToString();
model.tableName = objReader["tableName"].ToString();
model.tableNameDes = objReader["tableNameDes"].ToString();
model.IsIdentity = objReader["IsIdentity"].ToString();
model.IsIdentity_PK = objReader["IsIdentity_PK"].ToString();
model.TYPE = objReader["TYPE"].ToString();
model.length = objReader["length"].ToString();
model.IsNULL = objReader["IsNULL"].ToString();
model.defaultValue = objReader["defaultValue"].ToString();
model.columNameDes = objReader["columNameDes"].ToString();
models.Add(model);
}
}
conn.Close();
}
}
private static void TestMARSSessionPooling(string caseName, string connectionString, CommandType commandType,
ExecuteType executeType, ReaderTestType readerTestType, GCType gcType)
{
SqlCommand[] cmd = new SqlCommand[CONCURRENT_COMMANDS];
SqlDataReader[] gch = new SqlDataReader[CONCURRENT_COMMANDS];
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
for (int i = 0; i < CONCURRENT_COMMANDS; i++)
{
// Prepare all commands
cmd[i] = con.CreateCommand();
switch (commandType)
{
case CommandType.Text:
cmd[i].CommandText = _COMMAND_SQL;
cmd[i].CommandTimeout = 120;
break;
case CommandType.StoredProcedure:
cmd[i].CommandText = _COMMAND_RPC;
cmd[i].CommandTimeout = 120;
cmd[i].CommandType = CommandType.StoredProcedure;
break;
}
}
for (int i = 0; i < CONCURRENT_COMMANDS; i++)
{
switch (executeType)
{
case ExecuteType.ExecuteScalar:
cmd[i].ExecuteScalar();
break;
case ExecuteType.ExecuteNonQuery:
cmd[i].ExecuteNonQuery();
break;
case ExecuteType.ExecuteReader:
if (readerTestType != ReaderTestType.ReaderGC)
gch[i] = cmd[i].ExecuteReader();
switch (readerTestType)
{
case ReaderTestType.ReaderClose:
{
gch[i].Dispose();
break;
}
case ReaderTestType.ReaderDispose:
gch[i].Dispose();
break;
case ReaderTestType.ReaderGC:
gch[i] = null;
WeakReference weak = OpenReaderThenNullify(cmd[i]);
GC.Collect();
if (gcType == GCType.Wait)
{
GC.WaitForPendingFinalizers();
Assert.False(weak.IsAlive, "Error - target still alive!");
}
break;
case ReaderTestType.ConnectionClose:
GC.SuppressFinalize(gch[i]);
con.Close();
con.Open();
break;
case ReaderTestType.NoCloses:
GC.SuppressFinalize(gch[i]);
break;
}
break;
}
if (readerTestType != ReaderTestType.NoCloses)
{
con.Close();
con.Open(); // Close and open, to re-assure collection!
}
SqlCommand verificationCmd = con.CreateCommand();
verificationCmd.CommandText = COMMAND_STATUS;
using (SqlDataReader rdr = verificationCmd.ExecuteReader())
{
rdr.Read();
int connections = (int)rdr.GetValue(0);
rdr.NextResult();
rdr.Read();
int requests = (int)rdr.GetValue(0);
switch (executeType)
{
case ExecuteType.ExecuteScalar:
case ExecuteType.ExecuteNonQuery:
// 1 for connection, 1 for command
Assert.True(connections == 2, "Failure - incorrect number of connections for ExecuteScalar! #connections: " + connections);
// only 1 executing
Assert.True(requests == 1, "Failure - incorrect number of requests for ExecuteScalar! #requests: " + requests);
break;
case ExecuteType.ExecuteReader:
switch (readerTestType)
{
case ReaderTestType.ReaderClose:
case ReaderTestType.ReaderDispose:
case ReaderTestType.ConnectionClose:
// 1 for connection, 1 for command
Assert.True(connections == 2, "Failure - Incorrect number of connections for ReaderClose / ReaderDispose / ConnectionClose! #connections: " + connections);
// only 1 executing
Assert.True(requests == 1, "Failure - incorrect number of requests for ReaderClose/ReaderDispose/ConnectionClose! #requests: " + requests);
break;
case ReaderTestType.ReaderGC:
switch (gcType)
{
case GCType.Wait:
// 1 for connection, 1 for open reader
Assert.True(connections == 2, "Failure - incorrect number of connections for ReaderGCWait! #connections: " + connections);
// only 1 executing
Assert.True(requests == 1, "Failure - incorrect number of requests for ReaderGCWait! #requests: " + requests);
break;
case GCType.NoWait:
// 1 for connection, 1 for open reader
Assert.True(connections == 2, "Failure - incorrect number of connections for ReaderGCNoWait! #connections: " + connections);
// only 1 executing
Assert.True(requests == 1, "Failure - incorrect number of requests for ReaderGCNoWait! #requests: " + requests);
break;
}
break;
case ReaderTestType.NoCloses:
// 1 for connection, 1 for current command, 1 for 0 based array offset, plus i for open readers
Assert.True(connections == (3 + i), "Failure - incorrect number of connections for NoCloses: " + connections);
// 1 for current command, 1 for 0 based array offset, plus i open readers
Assert.True(requests == (2 + i), "Failure - incorrect number of requests for NoCloses: " + requests);
break;
}
break;
}
}
}
}
}
static void RegisterUser()
{
string nombre, apellido, correo, usuario, passw, fechaNacimiento;
bool userLibre = false;
bool mailLibre = false;
Console.WriteLine("Ingresa tu nombre");
nombre = Console.ReadLine();
Console.WriteLine("Ingresa tu apellido");
apellido = Console.ReadLine();
Console.WriteLine("Ingresa tu correo");
correo = Console.ReadLine();
Console.WriteLine("Ingresa tu nombre de usuario");
usuario = Console.ReadLine();
Console.WriteLine("Ingresa el password ");
passw = Console.ReadLine();
Console.WriteLine("Ingresa fecha de nacimiento como Mes/Dia/Año ");
fechaNacimiento = Console.ReadLine();
//comprobar
//comprueba si existe
conexion.Open();
cadena = "SELECT UserName From USUARIO WHERE UserName like '" + usuario + "'";
comando = new SqlCommand(cadena, conexion);
SqlDataReader comparar = comando.ExecuteReader();
if (comparar.Read())
{
userLibre = false;
}
else
{
userLibre = true;
}
conexion.Close();
conexion.Open();
cadena = "SELECT Email From USUARIO WHERE Email like '" + correo + "'";
comando = new SqlCommand(cadena, conexion);
SqlDataReader compararmail = comando.ExecuteReader();
if (compararmail.Read())
{
mailLibre = false;
}
else
{
mailLibre = true;
}
conexion.Close();
Console.ReadLine();
// se crea
if (mailLibre && userLibre)
{
conexion.Open();
cadena = "INSERT INTO USUARIO VALUES ('" + usuario + "','" + nombre + "','" + apellido + "','" + correo + "','" + passw + "','" + fechaNacimiento + "')";
comando = new SqlCommand(cadena, conexion);
comando.ExecuteNonQuery();
conexion.Close();
Console.WriteLine("Usuario creado");
}
else
{
Console.WriteLine("Error usuario ya registrado");
}
}
private static void ValidateReader(SqlDataReader rdr, int column)
{
bool validateSucceeded = false;
Action[] nonDbNullActions =
{
() => rdr.GetDateTime(column),
() => rdr.GetTimeSpan(column),
() => rdr.GetDateTimeOffset(column),
() => rdr.GetString(column)
};
Action[] genericActions =
{
() => rdr.GetSqlString(column),
() => rdr.GetSqlChars(column),
() => rdr.GetSqlDateTime(column)
};
Action<Action[]> validateParsingActions =
(testActions) =>
{
foreach (Action testAction in testActions)
{
try
{
testAction();
validateSucceeded = true;
}
catch (InvalidCastException)
{
}
}
};
if (!rdr.IsDBNull(column))
{
validateParsingActions(nonDbNullActions);
}
validateParsingActions(genericActions);
// Server 2008 & 2005 seem to represent DBNull slightly differently. Might be related to a Timestamp IsDBNull bug
// in SqlDataReader, which requires different server versions to handle NULLs differently.
// Empty string is expected for DBNull SqlValue (as per API), but SqlServer 2005 returns "Null" for it.
// See GetSqlValue code path in SqlDataReader for more details
if (!validateSucceeded && rdr.IsDBNull(column) && rdr.GetValue(column).ToString().Equals(""))
{
validateSucceeded = true;
}
Assert.True(validateSucceeded, string.Format("FAILED: SqlDataReader failed reader validation for column: {0}. Column literal value: {1}", column, rdr.GetSqlValue(column)));
}
private void AddToListColumns(ref List<Column> list, SqlDataReader dr)
{
//0 COLUMN_NAME
//1 IS_NULLABLE
//2 DATA_TYPE
//3 CHARACTER_MAXIMUM_LENGTH
//4 NUMERIC_PRECISION
//5 AUTOINC_INCREMENT
//6 AUTOINC_SEED
//7 COLUMN_HASDEFAULT
//8 COLUMN_FLAGS
//9 NUMERIC_SCALE
//10 TABLE_NAME
//11 AUTOINC_NEXT
//12 TABLE_SCHEMA
//13 ORDINAL_POSITION
//14 CASE_SENSITIVE
string defValue = string.Empty;
bool hasDefault = false;
if (!dr.IsDBNull(8))
{
var t = dr.GetString(8);
if (t.ToUpperInvariant().Contains("GETUTCDATE()"))
{
t = "(GETDATE())";
}
if (t.ToUpperInvariant().Contains("NEWSEQUENTIALID()"))
{
t = "(NEWID())";
}
if (t.ToUpperInvariant().ContainsAny(_sqlCeFunctions.ToArray()))
{
defValue = t;
}
t = t.Replace("(", string.Empty);
t = t.Replace(")", string.Empty);
if (t.Length > 0)
{
var arr = t.ToCharArray();
if (Char.IsNumber(arr[0]))
{
defValue = t;
}
if (arr[0] == '\'')
{
defValue = t;
}
if (t.StartsWith("N'"))
{
defValue = t;
}
if (t.StartsWith("-"))
{
defValue = t;
}
}
if (!string.IsNullOrEmpty(defValue))
hasDefault = true;
}
string table = dr.GetString(11);
if (_keepSchemaName)
table = dr.GetString(13) + "." + table;
list.Add(new Column
{
ColumnName = dr.GetString(0),
IsNullable = (YesNoOption)Enum.Parse(typeof(YesNoOption), dr.GetString(1)),
DataType = dr.GetString(2),
CharacterMaxLength = (dr.IsDBNull(3) ? 0 : dr.GetInt32(3)),
NumericPrecision = (dr.IsDBNull(4) ? 0 : Convert.ToInt32(dr[4], System.Globalization.CultureInfo.InvariantCulture)),
AutoIncrementBy = (dr.IsDBNull(5) ? 0 : Convert.ToInt64(dr[5], System.Globalization.CultureInfo.InvariantCulture)),
AutoIncrementSeed = (dr.IsDBNull(6) ? 0 : Convert.ToInt64(dr[6], System.Globalization.CultureInfo.InvariantCulture)),
AutoIncrementNext = (dr.IsDBNull(12) ? 0 : Convert.ToInt64(dr[12], System.Globalization.CultureInfo.InvariantCulture)),
ColumnHasDefault = hasDefault,
ColumnDefault = defValue,
RowGuidCol = (dr.IsDBNull(9) ? false : dr.GetInt32(9) == 378 || dr.GetInt32(9) == 282),
NumericScale = (dr.IsDBNull(10) ? 0 : Convert.ToInt32(dr[10], System.Globalization.CultureInfo.InvariantCulture)),
TableName = table,
IsCaseSensitivite = dr.IsDBNull(14) ? false : dr.GetInt32(14) == 1
});
}
public async Task Load()
{
var tableName = Guid.NewGuid().ToString();
var random = new Random();
var count = random.Next(1, 25);
var definitions = new List<IDefinition>();
for (var i = 0; i < count; i++)
{
var d = new Definition()
{
Name = Guid.NewGuid().ToString(),
Preface = Guid.NewGuid().ToString(),
};
definitions.Add(d);
}
var def = new Definition()
{
Name = tableName,
Preface = SqlStatements.Schema,
};
definitions.Add(def);
var executor = Substitute.For<IExecutor>();
var schemaReader = Substitute.For<ISchemaReader>();
schemaReader.Load(SchemaTypes.Table).Returns(Task.FromResult<IEnumerable<IDefinition>>(definitions));
var loader = Substitute.For<IDynamicLoader>();
var reader = new SqlDataReader(executor, schemaReader, loader, tableName);
var result = await reader.Load();
Assert.IsNotNull(result);
Assert.AreEqual(count, result.Count());
schemaReader.Received().Load(SchemaTypes.Table);
}
private void LoadFromSQL()
{
try
{
Runtime.IsConnectionsFileLoaded = false;
if (_SQLUsername != "")
{
sqlCon =
new SqlConnection(
(string)
("Data Source=" + _SQLHost + ";Initial Catalog=" + _SQLDatabaseName + ";User Id=" +
_SQLUsername + ";Password="******"Data Source=" + _SQLHost + ";Initial Catalog=" + _SQLDatabaseName +
";Integrated Security=True");
}
sqlCon.Open();
sqlQuery = new SqlCommand("SELECT * FROM tblRoot", sqlCon);
sqlRd = sqlQuery.ExecuteReader(CommandBehavior.CloseConnection);
sqlRd.Read();
if (sqlRd.HasRows == false)
{
Runtime.SaveConnections();
sqlQuery = new SqlCommand("SELECT * FROM tblRoot", sqlCon);
sqlRd = sqlQuery.ExecuteReader(CommandBehavior.CloseConnection);
sqlRd.Read();
}
this.confVersion = Convert.ToDouble(sqlRd["confVersion"], CultureInfo.InvariantCulture);
TreeNode rootNode;
rootNode = new TreeNode((string)(sqlRd["Name"]));
Root.Info rInfo = new Root.Info(Root.Info.RootType.Connection);
rInfo.Name = rootNode.Text;
rInfo.TreeNode = rootNode;
rootNode.Tag = rInfo;
rootNode.ImageIndex = System.Convert.ToInt32(Images.Enums.TreeImage.Root);
rootNode.SelectedImageIndex = System.Convert.ToInt32(Images.Enums.TreeImage.Root);
if (Security.Crypt.Decrypt((string)(sqlRd["Protected"]), pW) != "ThisIsNotProtected")
{
if (Authenticate((string)(sqlRd["Protected"]), false, rInfo) == false)
{
Settings.Default.LoadConsFromCustomLocation = false;
Settings.Default.CustomConsPath = "";
rootNode.Remove();
return;
}
}
//Me._RootTreeNode.Text = rootNode.Text
//Me._RootTreeNode.Tag = rootNode.Tag
//Me._RootTreeNode.ImageIndex = Images.Enums.TreeImage.Root
//Me._RootTreeNode.SelectedImageIndex = Images.Enums.TreeImage.Root
sqlRd.Close();
// SECTION 3. Populate the TreeView with the DOM nodes.
AddNodesFromSQL(rootNode);
//AddNodeFromXml(xDom.DocumentElement, Me._RootTreeNode)
rootNode.Expand();
//expand containers
foreach (Container.Info contI in this._ContainerList)
{
if (contI.IsExpanded == true)
{
contI.TreeNode.Expand();
}
}
//open connections from last mremote session
if (Settings.Default.OpenConsFromLastSession == true && Settings.Default.NoReconnect == false)
{
foreach (Connection.Info conI in this._ConnectionList)
{
if (conI.PleaseConnect == true)
{
Runtime.OpenConnection(conI);
}
}
}
//Tree.Node.TreeView.Nodes.Clear()
//Tree.Node.TreeView.Nodes.Add(rootNode)
AddNodeToTree(rootNode);
SetSelectedNode(selNode);
Runtime.IsConnectionsFileLoaded = true;
//Runtime.Windows.treeForm.InitialRefresh()
sqlCon.Close();
}
catch (Exception ex)
{
Runtime.MessageCollector.AddMessage(Messages.MessageClass.ErrorMsg,
Language.strLoadFromSqlFailed + Constants.vbNewLine +
ex.Message, true);
}
}
