public RequestsProcessedByEnum GetLeaveStatus(int leaveRequestId)
{
SqlConnection conn = null;
SqlCommand cmd = null;
try
{
conn = DALHelper.CreateSqlDbConnection();
cmd = new SqlCommand("usp_GetLeaveStatus", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@LeaveRequestId", leaveRequestId);
if (cmd.ExecuteScalar() != DBNull.Value)
{
return (RequestsProcessedByEnum)Convert.ToInt32(cmd.ExecuteScalar());
}
else
{
return RequestsProcessedByEnum.NotDefined;
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
cmd.Dispose();
conn.Dispose();
}
}
protected void Tmbtn_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("select MAX(wjh) from wj", cn);
cn.Open();
cmd.ExecuteScalar();
//cn.Close();
SqlCommand com = new SqlCommand("INSERT INTO Tm(Wjh,Tm,Tixing) VALUES (" +cmd.ExecuteScalar()+ ",'" + Tmtxt.Text.Trim() + "','" + TXDropDownList.Text.Trim() + "')", cn);
//com.ExecuteNonQuery();
//cn.Close();
try
{
//cn.Open();
int val = com.ExecuteNonQuery();
cn.Close();
this.NRListBox.Items.Add(this.Tmtxt.Text);
if (val <= 0)
ClientScript.RegisterStartupScript(this.GetType(), "", "alert('插入数据失败!')");
else
ClientScript.RegisterStartupScript(this.GetType(), "", "alert('插入数据成功!')");
}
//捕获异常
catch (Exception exp)
{
//处理异常.......
ClientScript.RegisterStartupScript(this.GetType(), "", "alert('插入数据失败! 详情:" + exp.Message + "')");
}
}
protected void Button3_Click(object sender, EventArgs e)
{
String s = ConfigurationManager.ConnectionStrings["MediDB"].ConnectionString;
SqlConnection con = new SqlConnection(s);
SqlCommand cmd = new SqlCommand(" Select Count(*) from Admin where name='" + TextBox1.Text + "' and Password='******'", con);
con.Open();
int m = (int)cmd.ExecuteScalar();
if (m == 1)
{
Button1.Enabled = true ;
Button2.Enabled = true ;
Button4.Enabled = true ;
Button5.Enabled = true ;
}
else
{
Response.Write("<Script>alert('Name or the password entered by you is incorrect. please try again!!')</Script>");
}
cmd.ExecuteScalar();
con.Close();
TextBox1.Text = "";
TextBox2.Text = "";
}
public List<TableInfo> LoadTableList(string databaseConnectionString)
{
List<TableInfo> tables;
using (var conn = new SqlConnection(databaseConnectionString))
{
conn.Open();
var cmd = new SqlCommand(SqlQueries.QUERY_ALL_TABLES, conn);
using (var reader = cmd.ExecuteReader())
{
tables = new List<TableInfo>();
while (reader.Read())
{
tables.Add(new TableInfo(reader.GetString(0), reader.GetString(1)));
}
}
int tableCount = 0;
foreach (var tableInfo in tables)
{
cmd = new SqlCommand(string.Format(SqlQueries.QUERY_DATA_SIZE, tableInfo.Name, tableInfo.Schema), conn);
cmd.CommandTimeout = 60000;
tableInfo.DataSizeBytes = (double) cmd.ExecuteScalar();
cmd.CommandText = string.Format(SqlQueries.QUERY_INDEX_SIZE, tableInfo.Name, tableInfo.Schema);
tableInfo.IndexSizeBytes = (double) cmd.ExecuteScalar();
cmd.CommandText = string.Format(SqlQueries.QUERY_ROW_COUNT, tableInfo.Name, tableInfo.Schema);
tableInfo.RowCount = (long) cmd.ExecuteScalar();
OnTableLoadProgressChanged(new ProgressEventArgs(tableCount++*100 / tables.Count));
}
}
return tables;
}
public string generatestudentcode()
{
string regcode = "";
string query = "select RegistrationCode from StudentPersonalInformation where ID =(select Max(ID) from StudentPersonalInformation)";
try
{
SqlCommand cmd = new SqlCommand(query, con);
if (con.State == ConnectionState.Closed)
con.Open();
if (cmd.ExecuteScalar() != null)
{
//regcode = cmd.ExecuteScalar().ToString();
//regcode= regcode.Substring(0,regcode.LastIndexOf('/')-1) + Convert.ToInt32(regcode.Substring(regcode.LastIndexOf('/'), (regcode.Length - (regcode.LastIndexOf('/'))) + 1)) +1;
regcode = (Convert.ToInt32(cmd.ExecuteScalar()) + 1).ToString();
}
else
regcode = "1";
// regcode = "ASTM/" + DropDownListCenter.SelectedValue + "/" + DateTime.Now.Year + "/1";
// regcode = "ASTM/" + DropDownListCenter.Items[0].Text + "/" + DropDownListCourse.SelectedValue + "/" + DateTime.Now.Year + "/1";
}
catch (Exception ex)
{ }
finally
{
con.Close();
}
return regcode;
}
public static int? getMax()
{
int? id = null;
string conexionCadena = ConfigurationManager.ConnectionStrings["ConexionComplejo"].ConnectionString;
SqlConnection con = new SqlConnection();
try
{
con.ConnectionString = conexionCadena;
con.Open();
string sql = "SELECT MAX(id_fact) from facturas";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.Connection = con;
if(!cmd.ExecuteScalar().Equals(null))
{
id =(int) cmd.ExecuteScalar();
}
else
{
id = null;
}
}
catch (SqlException ex)
{
throw new ApplicationException("Error al traer max id cliente" + ex.Message);
}
finally
{
con.Close();
}
return id;
}
/// <summary>
/// Authenticates the user
/// </summary>
/// <param name="user">user from login form</param>
/// <param name="password">password from login form</param>
public static bool Authenticate(String user,String password)
{
var positiveIntRegex = new Regex(@"^\w+$");
if (!positiveIntRegex.IsMatch(user))
{
return false;
}
if (!positiveIntRegex.IsMatch(password))
{
return false;
}
String encryptedPass = Encrypt(password);
string constr = Settings.Default.UserDbConnectionString;
SqlConnection con = new SqlConnection(constr);
SqlCommand command = new SqlCommand();
command.Connection = con;
command.Parameters.AddWithValue("@Username", user);
command.CommandText = "SELECT Password FROM Users WHERE Name = @Username";
command.CommandType = CommandType.Text;
con.Open();
string _password = "";
if (command.ExecuteScalar() != null)
_password = command.ExecuteScalar().ToString();
else
return false;
con.Close();
if (encryptedPass.Equals(_password))
{
return true;
}
return false;
}
protected void TxtAccountName_TextChanged(object sender, EventArgs e)
{
try
{
System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
con.Open();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("select ACODE from ACCOUNTS where ANAME = '" + TxtAccountName.Text + "' and COMP_CODE = " + Session["COMP_CODE"].ToString(), con);
int x = Convert.ToInt32(cmd.ExecuteScalar());
if (x == 0)
{
TxtAccountName.BackColor = Color.Red;
}
else
{
HfACODE.Value = cmd.ExecuteScalar().ToString();
TxtAccountName.BackColor = Color.White;
con.Close();
getAccountGroupName();
FillProductDetailsGrid();
}
}
catch (Exception)
{
throw;
}
}
protected void login_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("server=(localdb)\\v11.0;Initial Catalog=WebApplication2;Integrated Security=true");
con.Open();
string str = "select count(*) from Users where userName='******'";
SqlCommand command = new SqlCommand(str, con);
int temp = Convert.ToInt32(command.ExecuteScalar().ToString());
if (temp == 1)
{
string str2 = "select password from Users where userName='******'";
SqlCommand command2 = new SqlCommand(str2, con);
string tempPass = command2.ExecuteScalar().ToString().ToLower().Trim();
if (tempPass==pass.Text.ToLower().Trim())
{
string str3 = "select user_id from Users where userName='******'";
SqlCommand command3 = new SqlCommand(str3, con);
temp = Convert.ToInt32(command.ExecuteScalar().ToString());
con.Close();
Session["new"] = temp;
Response.Redirect("Home.aspx");
}
else
{
Label1.Visible = true;
Label1.Text = "Wrong password!";
}
}
else
{
Label1.Visible = true;
Label1.Text = "Invalid user name!";
}
}
public static string ExecuteScalar(string strSql)
{
try
{
iniCon();
SqlConnection sCon=new SqlConnection(m_strCon);
sCon.Open();
SqlCommand sCmd=new SqlCommand();
sCmd.Connection =sCon ;
sCmd.CommandText =strSql;
if(sCmd.ExecuteScalar()!=null)
{
string strRet=sCmd.ExecuteScalar().ToString();
sCon.Close();
return strRet;
}
else
{
sCon.Close();
return "";
}
}catch(Exception ex)
{
MessageBox.Show(ex.ToString());
Application.Exit();
//MessageBox.Show(ex.ToString());
return "";
}
}
void GetCurrentMembership()
{
string userID = Session["UserID"].ToString();
if (CheckMembership())
{
using (SqlConnection con = new SqlConnection(Helper.GetCon()))
using (SqlCommand cmd = new SqlCommand())
{
con.Open();
cmd.Connection = con;
cmd.CommandText = "SELECT SUM(Length) FROM Memberships INNER JOIN Payments ON " +
"Memberships.MembershipID=Payments.MembershipID WHERE UserID=@UserID " +
"AND MembershipStatus='Active' AND PaymentStatus='Paid'";
cmd.Parameters.AddWithValue("@UserID", userID);
int totalYears = (int)cmd.ExecuteScalar();
cmd.CommandText = "SELECT TOP 1 StartDate FROM Memberships INNER JOIN Payments ON " +
"Memberships.MembershipID=Payments.MembershipID WHERE UserID=@UserID " +
"AND MembershipStatus='Active' AND PaymentStatus='Paid'";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@UserID", userID);
DateTime endDate = (DateTime)cmd.ExecuteScalar();
DateTime totalEndDate = endDate.AddYears(totalYears);
txtEndDate.Text = totalEndDate.ToString("D");
}
}
else
{
txtEndDate.Text = "N/A";
nomem.Visible = true;
btnDisable.Visible = false;
btnEnable.Visible = false;
}
}
/// <summary>
/// Check Employee whether exist
/// </summary>
/// <param name="employeeId"></param>
/// <returns></returns>
public bool IsEmployeeIdExist(string employeeId)
{
using (SqlConnection conn = new SqlConnection(DBHelper.GetConnection()))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"select EmployeeId
from dbo.Employee
where EmployeeId = @EmployeeId";
cmd.Parameters.AddWithValue("@EmployeeId", employeeId);
bool result = false;
try
{
conn.Open();
cmd.ExecuteScalar();
if (cmd.ExecuteScalar() != null)
{
result = true;
}
}
catch (Exception ex)
{
}
finally
{
cmd.Dispose();
conn.Close();
}
return result;
}
}
/// <summary>
/// This method Returns the object after executing the execute scalar method
/// </summary>
/// <param name="cmdClass"></param>
/// <param name="objExp"></param>
public int ExecuteScalar(SqlCommand cmdClass, out Object obj, out Exception objExp)
{
int intStatus = 0;
obj = null;
objExp = null;
try
{
SqlConnection conClass = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString);
cmdClass.Connection = conClass;
if (conClass.State == ConnectionState.Closed)
conClass.Open();
if (cmdClass.ExecuteScalar() != DBNull.Value)
obj = cmdClass.ExecuteScalar();
else
obj = null;
conClass.Close();
conClass.Dispose();
cmdClass.Dispose();
if (obj != null)
intStatus = 1;
}
catch (Exception exp)
{
objExp = exp;
}
return (intStatus);
}
public CanUploadStatus CanUpload(string filename, int size)
{
bool extAllowed = FileExtensionAllowed(filename);
if (!extAllowed)
return CanUploadStatus.FileTypeNotAllowed;
else
{
int uploaded = 0;
using (var dbContext = _dbService.GetDatabaseContext(false))
{
SqlCommand cmd = new SqlCommand(@"
select SUM(f.Size)
from [File] f
inner join [File_User_Access] as fua ON fua.lid = f.Id
inner join [User] u on u.Id = fua.rid
where u.Id = @userId", dbContext.Connection);
cmd.Parameters.AddWithValue("userId", _securityService.CurrentUser.Id);
var raw = cmd.ExecuteScalar();
if (raw != DBNull.Value)
uploaded = (int)cmd.ExecuteScalar();
else
uploaded = 0;
}
if (uploaded > _securityService.CurrentUser.GetData<int>("DiskUsageLimit"))
return CanUploadStatus.DiskUsageLimitExceeded;
else
return CanUploadStatus.Yes;
}
}
public int GetInformationAssistantCount(string employeeId)
{
using (SqlConnection conn = new SqlConnection(DBHelper.GetConnection()))
{
SqlCommand cmd = new SqlCommand();
int result = 0;
cmd.Connection = conn;
cmd.CommandText = @"select count(*) as InformationAssistantCount,
e.Name as EmployeeName
from Employee e ,InformationAssistant i
where e.EmployeeId = i.EmployeeId
and e.EmployeeId = @EmployeeId
and CONVERT(varchar(100), i.RecordDate, 111)=CONVERT(varchar(100), getdate(), 111)
group by e.Name";
cmd.Parameters.AddWithValue("@EmployeeId", employeeId);
try
{
conn.Open();
result = DBNull.Value == cmd.ExecuteScalar() ? 0 : Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
cmd.Dispose();
}
return result;
}
}
public static bool Grabar(Entidades.Distrito pEntidad)
{
using (var cn = new SqlConnection(conexion.LeerCC))
{
// Contamos cuantos distritos existen segun el coddistrito o nomdistrito
using (var cmd = new SqlCommand(@"select isnull(count(coddistrito),0) from distritos where coddistrito=@cod or nomdistrito=@nom", cn))
{
cmd.Parameters.AddWithValue("cod", pEntidad.coddistrito);
cmd.Parameters.AddWithValue("nom", pEntidad.nomdistrito);
cn.Open();
// Ejecutamos el comando y verificamos si el resultado es mayor a cero actualizar, caso contrario insertar
if (Convert.ToInt32(cmd.ExecuteScalar()) > 0)
{
// Si es mayor a cero, quiere decir que existe al menos un registro con los datos ingresados
// Entonces antes de actualizar, hacer las siguientes comprobaciones
if (pEntidad.coddistrito == 0)
throw new Exception("El distrito ya esta registrado en el sistema, verifique los datos por favor!...");
// Verifica si ya existe un registro con el mismo nombre del distrito
cmd.CommandText = @"select isnull(count(coddistrito),0) from distritos where coddistrito<>@cod and nomdistrito=@nom";
if (Convert.ToInt32(cmd.ExecuteScalar()) > 0)
throw new Exception("No se puede grabar un valor duplicado, verifique los datos por favor!...");
// Si las comprobaciones anteriores resultaron ser falsa, entonces actualizar
cmd.CommandText = @"update distritos set nomdistrito=@nom where coddistrito=@cod";
}
else
cmd.CommandText = @"insert into distritos (nomdistrito) values (@nom)";
// Ejecutamos el comando que puede ser para update o insert
return Convert.ToBoolean(cmd.ExecuteNonQuery());
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlCommand cmd=new SqlCommand("select password from consumer where email='"+TextBox1.Text+"'",con);
con.Open();
if (cmd.ExecuteScalar()!=null)
{
string s = cmd.ExecuteScalar().ToString();
MailMessage mail = new MailMessage();
mail.To.Add(TextBox1.Text);
mail.From = new MailAddress("*****@*****.**");
mail.Subject = "Remember Mail";
string Body = "Password is " + s;
mail.Body = Body;
SmtpClient smtp = new SmtpClient();
smtp.Host = "smtp.gmail.com";
smtp.Port = 587;
smtp.UseDefaultCredentials = false;
smtp.Credentials = new System.Net.NetworkCredential("*****@*****.**", "9232663223");
smtp.EnableSsl = true;
smtp.Send(mail);
Label1.Text = "PASSWORD SENT TO YOUR EMAIL ADDRESS";
con.Close();
}
else
Label1.Text = "No such email exists ";
}
protected void btnEdit_Click(object sender, EventArgs e)
{
string connectionstring = WebConfigurationManager.ConnectionStrings["SCSDataBase"].ConnectionString;
SqlConnection con = new SqlConnection(connectionstring);
try
{
con.Open();
SqlCommand cmd = new SqlCommand("exec getUserControlledPupilId @lg", con);
cmd.Parameters.Add("@lg", login);
string pId = ((int)cmd.ExecuteScalar()).ToString();
cmd.CommandText = "exec getUserAccess @lg";
int access = (int)cmd.ExecuteScalar();
if (access >= 2)
{
cmd.CommandText = "exec getPupilFLF @pid";
cmd.Parameters.Add("@pid", pId);
string flf = (string)cmd.ExecuteScalar();
con.Close();
Session["SCSTitle"] = flf;
Session["SCSParameter1"] = pId;
Response.Redirect("Redactor.aspx");
}
else
{
Response.Write("<div align=center>Вашего уровня допуска недостаточно для этой операции <BR> <a href='ChangeAccess.aspx'>Как изменить уровень допуска?</A> " +
"<BR>Ваш текущий уровень допуска : " + access.ToString() + " Требуемый : 2</div>");
}
}
catch (Exception err)
{
Response.Write(err.Message);
}
}
/// <summary>
/// Attempts to purchase a movie for a user
/// </summary>
/// <param name="movieId">The movie to purchase</param>
/// <param name="userId">The user purchasing the movie</param>
/// <returns>true if the movie is bought, false if the movie could not be bought (f.ex. due to insufficient funds)</returns>
public bool PurchaseMovie(int movieId, int userId) {
//Ensure that the id is valid for a movie
SqlCommand command = new SqlCommand("SELECT id FROM Movie WHERE id=" + movieId, connection);
if (command.ExecuteScalar() == null) return false;
//Get the price of the movie
command.CommandText = "SELECT buyPrice FROM Files WHERE id =" + movieId;
Object pric = command.ExecuteScalar();
if (pric == null) return false;
int price = (int)pric;
//Get the balance of the user
command.CommandText = "SELECT balance FROM Users WHERE id =" + userId;
Object bal = command.ExecuteScalar();
if (bal == null) return false;
int balance = (int)bal;
if (balance - price >= 0) {
//Withdraw the amount from the users balance and only continue if it is successful
command.CommandText = "UPDATE Users " +
"SET balance = balance - " + price +
"WHERE id = " + userId;
if (command.ExecuteNonQuery() > 0) {
command.CommandText = "INSERT INTO UserFile " +
"VALUES(" + userId +
", " + movieId +
", '" + DateTime.MaxValue.ToString("yyyy-MM-dd HH:mm:ss") + "' )";
return command.ExecuteNonQuery() > 0;
}
}
return false;
}
protected void Button1_Click(object sender, EventArgs e)
{
if(TextBox3.Text==TextBox4.Text && TextBox1.Text!=""&&TextBox2.Text!=""&&TextBox5.Text!="")
{
cmd=new SqlCommand("select did from cities where city=@city",con);
con.Open();
cmd.Parameters.AddWithValue("@city",TextBox5.Text);
if(cmd.ExecuteScalar()==null)
Label2.Text="City not available";
else
{
int did=int.Parse(cmd.ExecuteScalar().ToString());
cmd=new SqlCommand("insert into distributorapplication values(@d,@did,@u,@p,@c)",con);
cmd.Parameters.AddWithValue("@d",TextBox1.Text);
cmd.Parameters.AddWithValue("@did",did);
cmd.Parameters.AddWithValue("@u",TextBox2.Text);
cmd.Parameters.AddWithValue("@p",TextBox3.Text);
cmd.Parameters.AddWithValue("@c", TextBox5.Text);
cmd.ExecuteNonQuery();
Label2.Text="Request for registration sent to administrator. You will be notified within few dayd";
TextBox1.Text = TextBox2.Text = TextBox5.Text = "";
}
con.Close();
}
else if(TextBox3.Text!=TextBox4.Text)
Label2.Text="Password doesn't match";
else
{
Label2.Text="Please fill in all the details";
}
}
public int GetCustomerId()
{
using (SqlConnection conn = new SqlConnection(DBHelper.GetConnection()))
{
int result = 0;
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"select max(CustomerId) from Customer";
try
{
conn.Open();
result = cmd.ExecuteScalar() == DBNull.Value ? 0 : Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
cmd.Dispose();
}
return result + 1;
}
}
/// <summary>
/// Performs a scalar select and returns the value as a string
/// </summary>
/// <param name="qS"></param>
/// <returns></returns>
public static String ScalarString(String qS)
{
object returnValue = "";
SqlConnection con = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(qS, con);
using (con)
{
if (con.State == ConnectionState.Open)
{
returnValue = cmd.ExecuteScalar();
con.Close();
}
else
{
con.Open();
returnValue = cmd.ExecuteScalar();
con.Close();
}
}
if (returnValue == null)
{
return "";
}
else
return returnValue.ToString();
}
//-----------------------------------
public void favoritecontrol()
{
try
{
if (Session["userid"] != null)
{
myconnection.Open();
SqlCommand com = new SqlCommand("SPfavoritecontrol", myconnection);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add("@movieid", SqlDbType.Int);
com.Parameters["@movieid"].Value = Convert.ToInt32(Request.QueryString["movieid"]);
com.Parameters.Add("@userid", SqlDbType.Int);
com.Parameters["@userid"].Value = Convert.ToInt32(Session["userid"]);
if (com.ExecuteScalar() != null)
{
if (Convert.ToInt32(com.ExecuteScalar().ToString()) != 0)
{
LinkButtonaddtofavorites.Text = "Added to favorites";
}
}
}
}
catch (Exception ex)
{
Labelerror.Text = "error in controlling favorites" + ex.Message;
}
finally
{
myconnection.Close();
}
}
/// <inheritdoc/>
public bool CorrelativeConfigurationExists(string facilityId, string correlativeId)
{
if (string.IsNullOrWhiteSpace(facilityId)) return false;
if (string.IsNullOrWhiteSpace(correlativeId)) return false;
if (string.IsNullOrWhiteSpace(_connectionString)) return false;
int count;
using (var conexionSp = new SqlConnection(_connectionString))
{
const string query = "SELECT COUNT(PaqueteId) FROM Comunes.CorrelativosPaquetes " +
"WHERE PaqueteId = @correlativeId AND PlantaId = @facilityId";
using (var comandoSp = new SqlCommand(query, conexionSp))
{
comandoSp.CommandType = CommandType.Text;
comandoSp.Parameters.AddWithValue("@correlativeId", correlativeId).Direction = ParameterDirection.Input;
comandoSp.Parameters.AddWithValue("@facilityId", facilityId).Direction = ParameterDirection.Input;
conexionSp.Open();
count = comandoSp.ExecuteScalar() is int ? (int)comandoSp.ExecuteScalar() : 0;
conexionSp.Close();
}
}
return count > 0;
}
protected void btnAccept_Click(object sender, EventArgs e)
{
string constr = WebConfigurationManager.ConnectionStrings["SCSDataBaseAdminLogin"].ConnectionString;
constr = constr.Replace("ID=admin;Password=;", "ID=SCSAdmin;Password="******";");
SqlConnection con = new SqlConnection(constr);
try
{
con.Open();
SqlCommand cmd = new SqlCommand("exec ChangeAccess @lg, @na", con);
cmd.Parameters.Add("@lg", lblLogin.Text);
cmd.Parameters.Add("@na", Convert.ToInt32(lblNeedAccess.Text));
cmd.ExecuteScalar();
cmd.CommandText = "exec CreateNewMessage @lgself, @lg, 'Запрос выполнен'";
cmd.Parameters.Add("@lgself", login);
cmd.ExecuteScalar();
con.Close();
Response.Redirect("Cabinet.aspx");
}
catch (Exception err)
{
//Response.Clear();
Response.Write(err.Message);
}
finally
{
con.Close();
}
}
public static void insertarReserva(Reserva res, DetalleReserva det)
{
string connStr = ConfigurationManager.ConnectionStrings["ConexionComplejo"].ConnectionString;
SqlConnection cn = new SqlConnection();
SqlTransaction tran = null;
try
{
cn.ConnectionString = connStr;
cn.Open();
tran = cn.BeginTransaction();
string sql = "INSERT INTO RESERVAS (fecha_res,cli_id,estado,monto) values (@FechaRes,@Cliente,@Estado,@Monto); SELECT @@Identity as ID;";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.Connection = cn;
cmd.Transaction = tran;
cmd.Parameters.AddWithValue("@FechaRes",res.Fecha);
cmd.Parameters.AddWithValue("@Cliente", res.Cli.IdCliente);
cmd.Parameters.AddWithValue("@Estado", res.Estado);
cmd.Parameters.AddWithValue("@Monto", res.Monto);
res.Id = Convert.ToInt32(cmd.ExecuteScalar());
//int idCabania = Convert.ToInt32(cmd.ExecuteScalar()); //Recupero Id de a cabania insertada
det.Res.Id = res.Id;
string sql2 = "INSERT INTO DETALLE_RESERVA (res_id,cab_id,precio,subtotal,fecha_desde,fecha_hasta,cant_dias,facturada) values (@ResId, @CabId,@Precio,@Subtotal,@FechaDesde,@FechaHasta,@CantDias,@Facturada); SELECT @@Identity as ID;";
cmd.CommandText = sql2;
cmd.Parameters.AddWithValue("@ResId", det.Res.Id);
cmd.Parameters.AddWithValue("@CabId", det.Cab.IdCabania);
cmd.Parameters.AddWithValue("@Precio", det.Precio);
cmd.Parameters.AddWithValue("@Subtotal", det.Subtotal);
cmd.Parameters.AddWithValue("@FechaDesde", det.FechaDesde);
cmd.Parameters.AddWithValue("@FechaHasta", det.FechaHasta);
cmd.Parameters.AddWithValue("@CantDias", det.CantDias);
cmd.Parameters.AddWithValue("@Facturada", det.Facturada);
det.Id = Convert.ToInt32(cmd.ExecuteScalar());
tran.Commit();
//cabania.IdCabania = idCabania; //Seteo Id de la cabania insertada
}
catch (SqlException ex)
{
if (cn.State == ConnectionState.Open)
tran.Rollback(); //Vuelvo atras los cambios
throw new ApplicationException("Error al guardar la cabaña." + ex.Message);
}
finally
{
if (cn.State == ConnectionState.Open)
cn.Close();
}
}
public string Scalar(string sqlstr)
{
string msg = "";
object result = "";
try
{
if (CONN.State != ConnectionState.Open)
{
CONN.Open();
}
CMD.Connection = CONN;
CMD.CommandText = sqlstr;
CMD.CommandType = CommandType.Text;
result = CMD.ExecuteScalar();
CONN.Close();
}
catch (Exception ex)
{
result = "";
msg = ex.Message;
}
finally
{
if (CONN.State == ConnectionState.Open)
{
CONN.Close();
}
}
try //log用另外的try防止檔案被咬
{
if (msg != "")
{
WriteCMD();
WriteLog("[" + DateTime.Now.ToLongTimeString() + "][Main.Scalar.Error]:" + msg + sqlstr);
}
else
{
if (LogStat >= 1)
{
WriteCMD();
}
WriteLogselect(sqlstr);
}
}
catch { }
if (result == null)
{
return("");
}
else
{
return(result.ToString());
}
}
private void CreateCapacities(int id, int userId, System.Data.SqlClient.SqlCommand command)
{
List <int> capacities = new List <int>();
// get capacities to create that not already exists
string sql = "SELECT PORProductClassCapacity.ProductCapacityId as CapacityId " +
"FROM PORProductClassCapacity WITH (NOLOCK) LEFT OUTER JOIN " +
"PORCapacity WITH (NOLOCK) ON PORProductClassCapacity.PORId = PORCapacity.PORId AND PORProductClassCapacity.ProductCapacityId = PORCapacity.CapacityId " +
"WHERE (PORCapacity.CapacityId IS NULL) AND (PORProductClassCapacity.PORId = " + id.ToString() + ")";
command.CommandText = sql;
System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
capacities.Add(reader.GetInt32(0));
}
reader.Close();
foreach (int capacity in capacities)
{
sql = "insert into PORCapacity (PORId, CapacityID) values (" + id + ", " + capacity + "); SELECT IDENT_CURRENT('[PORCapacity]') AS ID";
command.CommandText = sql;
int PORCapacityId = Convert.ToInt32(command.ExecuteScalar());
sql = "insert into durados_ChangeHistory (ViewName, PK, ActionId, UpdateUserId) values ('v_PORCapacity', '" + PORCapacityId + "', 1, " + userId + ")";
command.CommandText = sql;
command.ExecuteNonQuery();
sql = "insert into PMM (PORCapacityId) values (" + PORCapacityId + "); SELECT IDENT_CURRENT('[PMM]') AS ID";
command.CommandText = sql;
int PMMId = Convert.ToInt32(command.ExecuteScalar());
sql = "insert into durados_ChangeHistory (ViewName, PK, ActionId, UpdateUserId) values ('v_SamplesRequest', '" + PMMId + "', 1, " + userId + ")";
command.CommandText = sql;
command.ExecuteNonQuery();
sql = "insert into PLM (PORCapacityId,PLMBEStatusId) values (" + PORCapacityId + ",5); SELECT IDENT_CURRENT('[PLM]') AS ID";
command.CommandText = sql;
int PLMId = Convert.ToInt32(command.ExecuteScalar());
sql = "insert into durados_ChangeHistory (ViewName, PK, ActionId, UpdateUserId) values ('v_PLM', '" + PLMId + "', 1, " + userId + ")";
command.CommandText = sql;
command.ExecuteNonQuery();
CreatePLMParameterModes(PLMId, userId, command);
sql = "insert into Test (PORCapacity) values (" + PORCapacityId + "); SELECT IDENT_CURRENT('[Test]') AS ID";
command.CommandText = sql;
int TestId = Convert.ToInt32(command.ExecuteScalar());
sql = "insert into durados_ChangeHistory (ViewName, PK, ActionId, UpdateUserId) values ('v_TEST', '" + TestId + "', 1, " + userId + ")";
command.CommandText = sql;
command.ExecuteNonQuery();
}
}
protected void BtnNext_Click(object sender, EventArgs e)
{
try
{
con = new SqlConnection("integrated security=sspi;Server=.;Database=Jobs");
con.Open();
if (txtUname.Text == "" || txtPwd.Text=="")
{
lblmsg.Text = "sorry pls enter the required fileds";
}
else
{
cmd = new SqlCommand("select count(*) from users where uname = @uname", con);
cmd.Parameters.Add("@uname", SqlDbType.VarChar, 10).Value = txtUname.Text;
int cnt = (int)cmd.ExecuteScalar();
if (cnt == 1)
{
lblmsg.Text = "Sorry! Username is already present. Try another name!";
return;
}
cmd.CommandText = "select count(*) from users where email = @email";
cmd.Parameters.Add("@email", SqlDbType.VarChar, 50).Value = txtEmail.Text;
cnt = (int)cmd.ExecuteScalar();
if (cnt == 1)
{
lblmsg.Text = "Sorry! Email address is already present.";
return;
}
//cmd = new SqlCommand("insert into Users values(@p1,@p2,@p3,@p4,@p5,@p6,@p7)", con);
//cmd.Parameters.Add(new SqlParameter("@p1", txtUname.Text));
//cmd.Parameters.Add(new SqlParameter("@p2", txtPwd.Text));
//cmd.Parameters.Add(new SqlParameter("@p4", dt));
//cmd.Parameters.Add(new SqlParameter("@p3", txtEmail.Text));
//cmd.Parameters.Add(new SqlParameter("@p6", txtAddress.Text));
//cmd.Parameters.Add(new SqlParameter("@p7", txtPhone.Text));
//cmd.Parameters.Add(new SqlParameter("@p5", ddlUtype.SelectedItem.Text));
//cmd.ExecuteNonQuery();
//con.Close();
//hPwd.Text = txtPwd.Text;
hPwd.Text = txtPwd.Text;
MultiView1.ActiveViewIndex = ddlUtype.SelectedIndex + 1;
}
}
catch (Exception ex)
{
lblmsg.Text = "Error :" + ex.Message;
}
finally
{
con.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
//必要信息填写验证
if (textBoxXINGZHI.Text == "" || textBoxBUMEN.Text == "" || textBoxWEITUOREN.Text == "" )
{
MessageBox.Show("必要信息填写不完全");
}
else
{
try
{
//申请估价编号算法，从新写，你妈的
SqlConnection conn = new SqlConnection(Jingtai.ConnectionString);
conn.Open();//打开链接
//创建命令对象
SqlCommand cmd = null;
//创建语句字符串
string sql = null;
//到 table_ku 中获取指定年份的报告编号最大的数字和ID
int baogaohao;
sql = @"select MAX(报告编号) from Table_Ku where 报告年份='" + textBoxNIAN.Text + "'";
cmd = new SqlCommand(sql, conn);
label3.Text = cmd.ExecuteScalar().ToString();
baogaohao = int.Parse(cmd.ExecuteScalar().ToString());
//得到最大的报告号
baogaohao++;
sql = @"INSERT INTO Table_Ku (报告编号申请人,报告性质,相关部门,委托人,报告年份,报告编号,备注,编号申请时间) VALUES ('" + label2.Text +
"','" + textBoxXINGZHI.Text +
"','" + textBoxBUMEN.Text +
"','" + textBoxWEITUOREN.Text +
"','" + textBoxNIAN.Text +
"','" + baogaohao +
"','" + textBoxBEIZHU.Text +
"','" + DateTime.Now + "')";
cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
sql = @"select id from Table_Ku where 报告年份='" + textBoxNIAN.Text + "' and 报告编号='" + baogaohao.ToString() + "'";
cmd = new SqlCommand(sql, conn);
int myid = int.Parse(cmd.ExecuteScalar().ToString());
Jingtai.myid = myid;
conn.Close();//关闭连接
Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
MessageBox.Show("fenpeiyichang");
}
}
}
static void Main(string[] args)
{
var time= new DateTime();
string connectionString = GetConnectionString();
// Open a connection to the AdventureWorks database.
using (SqlConnection connection =
new SqlConnection(connectionString))
{
connection.Open();
// Perform an initial count on the destination table.
SqlCommand commandRowCount = new SqlCommand(
"SELECT COUNT(*) FROM " +
"dbo.BulkCopyDemoMatchingColumns;",
connection);
long countStart = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// Create a table with some rows.
DataTable newProducts = MakeTable();
// Create the SqlBulkCopy object.
// Note that the column positions in the source DataTable
// match the column positions in the destination table so
// there is no need to map columns.
using (var bulkCopy = new System.Data.SqlClient.SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoMatchingColumns";
try
{
// Write from the source to the destination.
var b = DateTime.Now.Second;
bulkCopy.WriteToServer(newProducts);
var a = DateTime.Now.Second;
Console.WriteLine("bat dau:"+b);
Console.WriteLine("Tổng thời gian Lưu:"+a);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
// Perform a final count on the destination
// table to see how many rows were added.
long countEnd = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Ending row count = {0}", countEnd);
Console.WriteLine("{0} rows were added.", countEnd - countStart);
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
}
public int ktra(string sql)
{
SqlConnection conn = new SqlConnection(kn);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.ExecuteScalar();
int i =(int)cmd.ExecuteScalar();
conn.Close();
return i;
}
protected void Page_Load(object sender, EventArgs e)
{
if ((Session["SCSLogin"] != null) & (Session["SCSDate"] != null))
{
if ((DateTime)Session["SCSDate"] <= DateTime.Now)
Response.Redirect("Default.aspx");
login = (string)Session["SCSLogin"];
expDate = ((DateTime)Session["SCSDate"]).ToString();
Response.Write("<div align=center> В системе как: " + login + "<BR> В системе до: " + expDate + "<BR></div>");
Session["SCSDate"] = DateTime.Now.AddMinutes(10);
Title = Title + " : " + login;
}
else Response.Redirect("Default.aspx");
string constr = WebConfigurationManager.ConnectionStrings["SCSDataBase"].ConnectionString;
SqlConnection con = new SqlConnection(constr);
try
{
con.Open();
SqlCommand cmd = new SqlCommand("exec ShowNewMessages @lg", con);
cmd.Parameters.Add("@lg", login);
SqlDataReader dr = cmd.ExecuteReader();
gvMessageViewer.DataSource = dr;
gvMessageViewer.DataBind();
dr.Close();
for (int i = 0; i < gvMessageViewer.Rows.Count; i++)
{
string mes = gvMessageViewer.Rows[i].Cells[2].Text;
if (mes.StartsWith("[REQUEST MESSAGE]") == true)
{
gvMessageViewer.Rows[i].Cells[2].Text = "[запрос]";
mes = mes.Replace("[REQUEST MESSAGE]", "");
cmd.CommandText = "exec getUserAccess @lg";
int acc = (int)cmd.ExecuteScalar();
if (acc == 4)
{
Response.Write("<div align=center>");
Response.Write("<a href=" + mes + "> подтвердить " + gvMessageViewer.Rows[i].Cells[1].Text + "</a>");
Response.Write("<BR>");
Response.Write("</div>");
}
}
}
cmd.CommandText = "exec NewMessagesReadedAlready @lg";
cmd.ExecuteScalar();
}
catch (Exception err)
{
Response.Write(err.Message);
}
finally
{
con.Close();
}
}
public static string fnGetValue(string sSql, string sConn)
{
string sValue = null;
System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(fnGetConStr(sConn));
sqlConn.Open();
System.Data.SqlClient.SqlCommand sqlComm = null;
sqlComm = new System.Data.SqlClient.SqlCommand(sSql, sqlConn);
sValue = (sqlComm.ExecuteScalar() == null) ? " " : sqlComm.ExecuteScalar().ToString();
sqlConn.Close();
return(sValue);
}
public int Ejecutar_Sentencia(string strSQL)
{
_cmd.Connection = _cn;
_cmd.CommandText = strSQL;
_cmd.ExecuteNonQuery();
if (strSQL.ToLower().Contains("INSERT"))
{
_cmd.CommandText = "SELECT @@IDENTITY";
return(int.Parse(_cmd.ExecuteScalar().ToString()));
}
return(0);
}
protected void appStatus()
{
System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
insert.Connection = dbConnection;
System.Data.SqlClient.SqlCommand getstatus = new System.Data.SqlClient.SqlCommand();
getstatus.Connection = dbConnection;
System.Data.SqlClient.SqlCommand getaddress = new System.Data.SqlClient.SqlCommand();
getaddress.Connection = dbConnection;
try { dbConnection.Open(); }
catch { }
try
{
foreach (RepeaterItem item in rptrTenant.Items)
{
var tenid = ((Label)item.FindControl("userid")).Text;
int number = int.Parse(tenid);
getaddress.CommandText = "select accomodationid from application where tenantid = " + number;
int address = Int32.Parse(getaddress.ExecuteScalar().ToString());
getstatus.CommandText = "select status from application where tenantid = " + number + " and accomodationid = " + address;
int id = Int32.Parse(getaddress.ExecuteScalar().ToString());
bool status = (bool)getstatus.ExecuteScalar();
if (status)
{
Label lblstat = (Label)item.FindControl("lblstatus");
lblstat.Text = "Accepted";
DropDownList ddl = (DropDownList)item.FindControl("ddlStatus");
ddl.SelectedValue = "1";
}
else
{
Label lblstat = (Label)item.FindControl("lblstatus");
lblstat.Text = "Rejected";
DropDownList ddl = (DropDownList)item.FindControl("ddlStatus");
ddl.SelectedValue = "0";
}
}
}
catch { }
finally
{
dbConnection.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
sc.Close();
sc.Open();
System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
insert.Connection = sc;
email = txtEmail.Text;
documentID = Convert.ToInt32(ddlRecommendation.SelectedItem.Value);// get document ID
insert.CommandText = "SELECT accountID from LoginInfo where email ='" + email + "'";
int receiverAccountID = Convert.ToInt32(insert.ExecuteScalar());// get receiver ID
insert.CommandText = "SELECT username,accountID from LoginInfo where email = '" + Session["email"] + "'";
insert.ExecuteNonQuery();
SqlDataReader reader = insert.ExecuteReader();
while (reader.Read())
{
string user = reader["username"].ToString();
Session["user"] = user;
string ID = reader["accountID"].ToString();
Session["accountID"] = ID;
}
reader.Close();
System.Data.SqlClient.SqlCommand test = new System.Data.SqlClient.SqlCommand();
test.Connection = sc;
test.CommandText = "INSERT INTO [Recommendations] VALUES('" + documentID + "','"
+ receiverAccountID + "','"
+ Session["user"] + "','"
+ DateTime.Now.ToString() + "','"
+ DateTime.Now.ToString() + "','"
+ Convert.ToString(Session["user"]) + "')";
test.ExecuteNonQuery();
txtEmail.Text = " ";
insert.CommandText = "SELECT accountID from [dbo].[LoginInfo] where accountID = '" + Session["accountID"] + "'";
int accountID = Convert.ToInt32(insert.ExecuteScalar());// get receiver ID
TextBox1.Text = accountID.ToString();
//TextBox1.Text = Session["accountID"].ToString();
//int accountID = Convert.ToInt32(TextBox1.Text);
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (localDB.State != ConnectionState.Open)
{
localDB.Open();
}
System.Data.SqlClient.SqlCommand getApplicants = new System.Data.SqlClient.SqlCommand();
getApplicants.Connection = localDB;
getApplicants.CommandText = "Select count(AppID) From App where(Opened like '*')";
string app = getApplicants.ExecuteScalar().ToString();
lblapps.Text = app;
lblApplicants.Text = app;
System.Data.SqlClient.SqlCommand getSch = new System.Data.SqlClient.SqlCommand();
getSch.Connection = localDB;
getSch.CommandText = "Select count(PostID) From Scholarship where(Opened like '*')";
lblsch.Text = getSch.ExecuteScalar().ToString();
string sch = getSch.ExecuteScalar().ToString();
lblScholarship.Text = sch;
System.Data.SqlClient.SqlCommand getEvents = new System.Data.SqlClient.SqlCommand();
getEvents.Connection = localDB;
getEvents.CommandText = "Select count(PostID) From Event where(Opened like '*')";
string even = getEvents.ExecuteScalar().ToString();
lblEvents.Text = even;
lblEv.Text = even;
int not = Convert.ToInt32(app) + Convert.ToInt32(sch) + Convert.ToInt32(even);
if (not == 0)
{
lblNotification.Text = "";
}
else
{
lblNotification.Text = not.ToString();
}
//System.Data.SqlClient.SqlCommand getMess = new System.Data.SqlClient.SqlCommand();
//getMess.Connection = localDB;
//getMess.CommandText = "Select count(MessageID) From Messages1 where(HasSeen like '*')";
//string messages = getMess.ExecuteScalar().ToString();
//lblMessage.Text = messages;
//localDB.Close();
}
protected void btnSelect3_Click(object sender, EventArgs e)
{
string subscription = "Monthly Subscription";
localDB.Open();
System.Data.SqlClient.SqlCommand getUserID = new System.Data.SqlClient.SqlCommand();
getUserID.Connection = localDB;
//Find UserID for most recently entered User
getUserID.CommandText = "select MAX(UserID) from USERS";
string returnID = getUserID.ExecuteScalar().ToString();
int idUser = Int32.Parse(returnID);
getUserID.ExecuteNonQuery();
//Update user table
SqlCommand update = new SqlCommand("Update Users SET Subscription=@Subscription WHERE UserId=@MaxID;", localDB);
update.Parameters.Add(new SqlParameter("@Subscription", subscription));
update.Parameters.Add(new SqlParameter("@MaxID", idUser));
update.ExecuteNonQuery();
localDB.Close();
Response.Redirect("Registration4.aspx");
}
/// <summary>
/// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。
/// </summary>
/// <param name="sql">SELECT 语句</param>
/// <returns>.NET Framework 数据类型形式的结果集第一行的第一列；如果结果集为空或结果为 REF CURSOR，则为空引用</returns>
public object ExecuteScalar(string sql)
{
using (sqlConnection = this.GetSqlConnection())
{
if (sqlConnection == null)
{
return(null);
}
try
{
if (sqlConnection.State == System.Data.ConnectionState.Closed)
{
sqlConnection.Open();
}
sqlCommand = new SqlCommand(sql, sqlConnection);
return(sqlCommand.ExecuteScalar());
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
return(null);
}
}
}
//Команда SELECT для суммирования ячеек
public decimal SelectSum(String col, String tablename)
{
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
decimal ret = 0;
try
{
string sql = "Select sum(" + col + ") from " + tablename;
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
ret = ((decimal)cmd.ExecuteScalar());
}
catch (Exception e)
{
MessageBox.Show("Error: " + e);
MessageBox.Show(e.StackTrace);
}
finally
{
conn.Close();
conn.Dispose();
}
Console.Read();
return(ret);
}
//Loads the Employer's summary onto the page with the most recent description
protected void ShowSummary(object sender, EventArgs e)
{
sc.Open();
System.Data.SqlClient.SqlCommand getdbPersonID = new System.Data.SqlClient.SqlCommand();
getdbPersonID.Connection = sc;
//Gets the personid for the username
getdbPersonID.CommandText = "SELECT PersonID from Account where Username = '******'";
int accountID = (int)getdbPersonID.ExecuteScalar();
ProfileSummary.Visible = true;
subheader.Visible = true;
BtnEdit.Visible = true;
System.Data.SqlClient.SqlCommand getEmpSummary = new System.Data.SqlClient.SqlCommand();
getEmpSummary.Connection = sc;
getEmpSummary.CommandText = "Select EmployerSummary from Employer where PersonID = @SummaryPersonID";
getEmpSummary.Parameters.Add(new SqlParameter("@SummaryPersonID", accountID));
String EmpSum = (String)getEmpSummary.ExecuteScalar();
ProfileSummary.InnerText = EmpSum;
sc.Close();
}
private static MemoryStream GetStreamFromDB(string typeName)
{
//SqlConnection conn = new SqlConnection(MySpace.Configuration.ConnectionStringProvider.ConnectionStrings["TestAssistants"].ConnectionString);
SqlConnection conn = new SqlConnection(@"server=devsrv\sql2005;database=TestAssistants;uid=ASPADONet;pwd=12345;Connect Timeout=20;Integrated Security=false;");
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand();
sqlCommand.Connection = conn;
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
sqlCommand.CommandText = "getSerializedObject";
SqlParameter paramClassName = new SqlParameter("@className", SqlDbType.NVarChar, 100);
paramClassName.Value = typeName;
sqlCommand.Parameters.Add(paramClassName);
object returnValue;
try
{
conn.Open();
returnValue = sqlCommand.ExecuteScalar();
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
MemoryStream stream = new MemoryStream((byte[])returnValue);
return(stream);
}
/// <summary>
/// Determines whether [is A member of group] [the specified group type].
/// </summary>
/// <param name="groupType">if set to <c>true</c> [group type].</param>
/// <param name="GroupSid">The group sid.</param>
/// <param name="netSqlAzManMode">if set to <c>true</c> [net SQL az man mode].</param>
/// <param name="rootDsePath">The root dse path.</param>
/// <param name="token">The token.</param>
/// <param name="userGroupsCount">The user groups count.</param>
/// <returns>
/// <c>true</c> if [is A member of group] [the specified group type]; otherwise, <c>false</c>.
/// </returns>
internal bool isAMemberOfGroup(bool groupType, byte[] GroupSid, bool netSqlAzManMode, string rootDsePath, byte[] token, int userGroupsCount)
{
SqlConnection conn = new SqlConnection(this.store.Storage.ConnectionString);
conn.Open();
bool result = false;
try
{
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("dbo.netsqlazman_IsAMemberOfGroup", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@GROUPTYPE", groupType);
cmd.Parameters.AddWithValue("@GROUPOBJECTSID", GroupSid);
cmd.Parameters.AddWithValue("@NETSQLAZMANMODE", netSqlAzManMode);
cmd.Parameters.AddWithValue("@LDAPPATH", rootDsePath);
cmd.Parameters.AddWithValue("@TOKEN", token);
cmd.Parameters.AddWithValue("@USERGROUPSCOUNT", userGroupsCount);
result = (bool)cmd.ExecuteScalar();
}
finally
{
conn.Close();
}
return(result);
}
protected void ddlProgramType_SelectedIndexChanged(object sender, EventArgs e)
{
System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
// sc.ConnectionString = @"Server=localhost;Database=WildTek;Trusted_Connection=Yes;";
String cs = ConfigurationManager.ConnectionStrings["WildTekConnectionString"].ConnectionString;
sc.ConnectionString = cs;
sc.Open();
System.Data.SqlClient.SqlCommand selectOrganization = new System.Data.SqlClient.SqlCommand();
selectOrganization.Connection = sc;
selectOrganization.Parameters.Clear();
selectOrganization.CommandText = "SELECT OrgID From Program WHERE ProgramID = @progamID";
selectOrganization.Parameters.AddWithValue("@programID", ddlProgramType.SelectedItem.Value);
String tempOrg = (String)selectOrganization.ExecuteScalar();
ddlOrganization.ClearSelection();
for (int i = 0; i < ddlOrganization.Items.Count; i++)
{
if (ddlOrganization.Items[i].Value == tempOrg)
{
ddlOrganization.Items[i].Selected = true;
}
}
}
public static object RetrieveSingleValue(string Sql)
{
System.Data.SqlClient.SqlCommand objCom = new System.Data.SqlClient.SqlCommand();
object ld_Result;
if (!OpenConnection())
{
return(0);
}
try
{
objCom.Connection = lo_Connection;
objCom.CommandTimeout = CommandTimeOutSeconds;
objCom.CommandText = Sql;
ld_Result = objCom.ExecuteScalar();
return(ld_Result);
}
catch
{
return(0);
}
finally
{
lo_Connection.Close();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserName"] == null || HttpContext.Current.Request.UrlReferrer == null)
{
Response.Redirect("Login.aspx");
}
localDB.Open();
System.Data.SqlClient.SqlCommand getApplicants = new System.Data.SqlClient.SqlCommand();
getApplicants.Connection = localDB;
getApplicants.CommandText = "Select count(AppID) From App";
lblApplicants.Text = getApplicants.ExecuteScalar().ToString();
System.Data.SqlClient.SqlCommand getjobs = new System.Data.SqlClient.SqlCommand();
getjobs.Connection = localDB;
getjobs.CommandText = "Select count(PostID) From Job";
lblJobs.Text = getjobs.ExecuteScalar().ToString();
System.Data.SqlClient.SqlCommand getscholarships = new System.Data.SqlClient.SqlCommand();
getscholarships.Connection = localDB;
getscholarships.CommandText = "Select count(PostID) From Scholarship";
lblScholarships.InnerText = getscholarships.ExecuteScalar().ToString();
System.Data.SqlClient.SqlCommand getEvent = new System.Data.SqlClient.SqlCommand();
getEvent.Connection = localDB;
getEvent.CommandText = "Select count(PostID) From Event";
lblEvent.InnerText = getEvent.ExecuteScalar().ToString();
localDB.Close();
}
public bool UserExists(string slack_id)
{
QC.SqlCommand exists = new QC.SqlCommand();
exists.CommandType = System.Data.CommandType.Text;
exists.Connection = this.conn;
exists.CommandText = @"SELECT * FROM users WHERE slack_id = @User";
var param1 = new QC.SqlParameter("User", slack_id);
exists.Parameters.Add(param1);
try
{
int id = (int)exists.ExecuteScalar();
if (id > 0)
{
return(true);
}
}
catch (Exception e)
{
return(false);
}
return(false);
}
public bool seachingIfSameValueExists(string whichColumnToCheck, string givenValue)
{
bool doesExist = true;
SqlConnection con = new SqlConnection(connectionString);
try
{
con.Open();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
cmd.Parameters.AddWithValue("@" + whichColumnToCheck, givenValue);
cmd.CommandText = "SELECT isActive FROM [ManagementToolDatabase].[dbo].[UserRegistrationTable]" +
" WHERE " + whichColumnToCheck + " = @" + whichColumnToCheck;
try
{
string isActive = cmd.ExecuteScalar().ToString();
if (isActive.Equals(true) || isActive.Equals(false))
{
doesExist = true;
}
}
catch (Exception)
{
doesExist = false;
}
con.Close();
}
catch (Exception)
{
MessageBox.Show("Unable to open database");
}
return(doesExist);
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserName"] == null || HttpContext.Current.Request.UrlReferrer == null)
{
Response.Redirect("Login.aspx");
}
sendHide.Visible = false;
responseHide.Visible = false;
noMessagesAlert.Visible = false;
localDB.Open();
//Select User's school name
System.Data.SqlClient.SqlCommand selectSchoolName = new System.Data.SqlClient.SqlCommand();
selectSchoolName.Connection = localDB;
selectSchoolName.CommandText = "select schoolName from school s inner join schoolemployee se on s.schoolID = se.schoolID";
string schoolName = selectSchoolName.ExecuteScalar().ToString();
mainViewUserName.InnerHtml = schoolName;
sidebarContactName1.InnerText = schoolName;
//Select User's First and Last Name
System.Data.SqlClient.SqlCommand selectUserName = new System.Data.SqlClient.SqlCommand();
selectUserName.Connection = localDB;
selectUserName.CommandText = "select concat(firstname, ' ', lastname) as userName from users inner join messages on userID = messagetoid AND messageid = (SELECT MAX(messageID) FROM messages)";
string userName = selectUserName.ExecuteScalar().ToString();
mainViewSpeakingWith.InnerHtml = "Speaking with: " + userName;
//Display sidebar users
}
public override object ConsultaDato(string SQL)
{
//Datos.connectionString.
try
{
using (System.Data.SqlClient.SqlConnection Cnn = new System.Data.SqlClient.SqlConnection(connectionString))
{
using (System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(SQL, Cnn))
{
try
{
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = SQL;
Cnn.Open();
return(Cmd.ExecuteScalar());
}
catch (Exception ex)
{
throw new Exception("Error de acceso a datos, Error: " + ex.ToString());
}
finally
{
if (Cnn.State == ConnectionState.Open)
{
Cnn.Close();
}
}
}
}
}
catch (Exception ex)
{
throw new Exception("Error de acceso a datos, Error: " + ex.ToString());
}
}
public int Insert(System.Data.SqlClient.SqlConnection sqlConnection, System.Data.SqlClient.SqlTransaction sqlTran)
{
System.Data.SqlClient.SqlCommand sqlcmd = new System.Data.SqlClient.SqlCommand("Insert into ASBLOB(FileContent,FileName,FileLength,FileType,FileExtName,AddUserNo,AddDate,IPAddress) Values(@FileContent,@FileName,@FileLength,@FileType,@FileExtName,@AddUserNo,@AddDate,@IPAddress);", sqlConnection);
sqlcmd.Transaction = sqlTran;
List <SqlParameter> _p = new List <SqlParameter>();
_p.Add(new SqlParameter("@FileContent", this.FileContent));
_p.Add(new SqlParameter("@FileName", this.FileName));
_p.Add(new SqlParameter("@FileLength", this.FileLength));
_p.Add(new SqlParameter("@FileType", this.FileType));
_p.Add(new SqlParameter("@FileExtName", this.FileExtension));
_p.Add(new SqlParameter("@AddUserNo", this.AddUser));
_p.Add(new SqlParameter("@AddDate", this.AddDate));
_p.Add(new SqlParameter("@IPAddress", this.IPAddress));
sqlcmd.Parameters.AddRange(_p.ToArray());
try
{
sqlcmd.ExecuteNonQuery();
sqlcmd.CommandText = "select @@IDENTITY";
int blobId = Convert.ToInt32(sqlcmd.ExecuteScalar());
return(blobId);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public int Add(Person person)
{
try
{
_connection.Open();
using (System.Data.SqlClient.SqlCommand sqlcommand = new System.Data.SqlClient.SqlCommand())
{
sqlcommand.CommandText = @"
INSERT INTO [dbo].[Person] ([FirstName] ,[LastName] ,[Phone] ,[Email] ,[CreateStamp] ,[ModifStamp])
OUTPUT INSERTED.ID
VALUES (
'" + person.FirstName + @"',
'" + person.LastName + @"',
'" + person.PhoneNumber + @"',
'" + person.Email + @"',
'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + @"',
'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + @"');";
sqlcommand.Connection = _connection; int count = (int)sqlcommand.ExecuteScalar(); return(count);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
_connection.Close();
}
return(-1);
}
public bool CheckIsRecordExist(string a_strChkSqlCommand)
{
System.Data.SqlClient.SqlCommand objCommand;
bool RecordExists = false;
try
{
bool AttemptToConnect = OpenSqlConnection();
objCommand = new System.Data.SqlClient.SqlCommand(a_strChkSqlCommand);
objCommand.Connection = myConn;
//objCommand.CommandTimeout=ConnectionTimeOut;
objCommand.CommandType = CommandType.Text;
string result = Convert.ToString(objCommand.ExecuteScalar());
objCommand.Dispose();
if (result != "")
{
RecordExists = true;
}
return(RecordExists);
}
catch (SqlException ex)
{
ex.Data.Clear();
return(RecordExists);
}
catch (Exception ex)
{
ex.Data.Clear();
return(RecordExists);
}
finally
{ CloseConnection(); }
}
//Use method in order to select the accommodationID from the Accommodation table.
public int returnAccomIDFromDB(Accommodation newAccom)
{
int accomID;
try
{
sc1.Open();
System.Data.SqlClient.SqlCommand getAccomID = new System.Data.SqlClient.SqlCommand();
getAccomID.Connection = sc1;
getAccomID.CommandText = "SELECT AccommodationID FROM ACCOMMODATION WHERE HostID=@hostID AND Street=@address AND CityCo=@city AND AccomState=@state AND Zip=@zip AND Price=@price AND RoomType=@roomType AND Neighborhood=@neighborhood AND Description=@description";
getAccomID.Parameters.Add(new SqlParameter("@address", newAccom.getAddress()));
getAccomID.Parameters.Add(new SqlParameter("@city", newAccom.getAccomCity()));
getAccomID.Parameters.Add(new SqlParameter("@state", newAccom.getAccomState()));
getAccomID.Parameters.Add(new SqlParameter("@zip", newAccom.getAccomZip()));
getAccomID.Parameters.Add(new SqlParameter("@price", newAccom.getAccomPrice()));
getAccomID.Parameters.Add(new SqlParameter("@roomType", newAccom.getAccomRoomType()));
getAccomID.Parameters.Add(new SqlParameter("@neighborhood", newAccom.getAccomNeighborhood()));
getAccomID.Parameters.Add(new SqlParameter("@description", newAccom.getAccomDescription()));
getAccomID.Parameters.Add(new SqlParameter("@hostID", newAccom.getAccomHostID()));
getAccomID.ExecuteNonQuery();
accomID = Convert.ToInt32(getAccomID.ExecuteScalar());
sc1.Close();
return(accomID);
}
catch
{
return(0);
}
}
protected object ScalarSQLCommand(System.Data.SqlClient.SqlCommand sqlcommand)
{
object ret = sqlcommand.ExecuteScalar();
DisposeCommand(sqlcommand);
return(ret);
}
/// <summary>
/// 返回一行一列的数据
/// </summary>
/// <param name="ConnStr">连接字符串</param>
/// <param name="strSQL">SQL语句</param>
/// <returns></returns>
public virtual object GetScalarData(string ConnStr, string strSQL)
{
{
try
{
using (sqlconn = new SDC.SqlConnection(ConnStr))
{
sqlconn.Open();
using (sqlcmd = new SDC.SqlCommand(strSQL, sqlconn))
{
return(sqlcmd.ExecuteScalar());
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlconn != null)
{
if (sqlconn.State != SD.ConnectionState.Closed)
{
sqlconn.Close();
}
sqlconn.Dispose();
}
}
}
}
public override Object CreateSqlInsert(string _tableName, bool autoincr)
{
System.Text.StringBuilder _sb = new System.Text.StringBuilder();
_sb.Append("INSERT INTO ");
_sb.Append("[" + _tableName + "]");
_sb.Append(" (");
_sb.Append(_sb_insertCol);
_sb.Append(") VALUES(");
_sb.Append(_sb_insertVal);
_sb.Append(") ");
if (autoincr)
{
_sb.AppendLine("SELECT SCOPE_IDENTITY()");
}
this._command.CommandText = _sb.ToString();
Settings.Log.LogSQL(_command.CommandText, _command.Parameters);
Connect();
var ret = _command.ExecuteScalar();
Disconnect();
return(ret);
}
public void SqlConnection(String Statement)
{
try
{
System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
System.Data.SqlClient.SqlDataReader DataReader = null;
sc.ConnectionString = @"Server=SILAS-PC\LOCALHOST; Database=WLS;Trusted_Connection=Yes;";
sc.Open();
System.Diagnostics.Debug.WriteLine("databse connection opened");
System.Data.SqlClient.SqlCommand sendComm = new System.Data.SqlClient.SqlCommand();
sendComm.Connection = sc;
sendComm.CommandText = Statement;
if (Statement.Contains("Delete") || (Statement.Contains("Insert")))
{
sendComm.ExecuteNonQuery();
}
if ((Statement.Contains("Select")))
{
String totSalary = sendComm.ExecuteScalar().ToString();
}
if (Statement.Contains("Equip") && (Statement.Contains("Select")))
{
}
sc.Close();
}
catch (System.Data.SqlClient.SqlException sqlException)
{
System.Diagnostics.Debug.WriteLine(sqlException.Message);
}
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
SqlConnection sqlCon = new SqlConnection(str);
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("select photo from test where name=@name", sqlCon);
command.Parameters.AddWithValue("@name", comboBox1.SelectedValue.ToString());
sqlCon.Open();
byte[] image = (byte[])command.ExecuteScalar();
if (image != null)
{
sqlCon.Close();
//Save the picture from Db in the memory stream
MemoryStream ms = new MemoryStream(image);
//Load the picture from memory stream to picture box
//pictureBox1.Image = Image.FromStream(ms);
foreach (PictureBox pb in listPB)
{
//Find an empty picture box
if (pb.Image == null)
{
pb.Image = Image.FromStream(ms);
break;
}
}
}
}
