public void CreateGetClothingProcedureIfNull()
{
using (SqlConnection con = new SqlConnection(Settings.AdventureWorksConStr))
{
SqlCommand checkIfProcExists = new SqlCommand();
checkIfProcExists.CommandText = "IF (OBJECT_ID('GetProductsByGender') IS NOT NULL) " +
"DROP PROCEDURE GetProductsByGender " +
"";
checkIfProcExists.Connection = con;
con.Open();
using (SqlDataReader dr = checkIfProcExists.ExecuteReader())
{
}
con.Close();
SqlCommand command = new SqlCommand();
command.CommandText = "CREATE PROCEDURE GetProductsByGender (@strGender varchar(5)) " +
"AS " +
"SELECT ProductModelID, Name " +
"FROM Production.ProductModel " +
"WHERE Name LIKE @strGender";
command.Connection = con;
con.Open();
using (SqlDataReader dr = command.ExecuteReader())
{
}
}
}
public requirements Insert(requirements id)
{
string ConnectionString = IDManager.connection();
SqlConnection con = new SqlConnection(ConnectionString);
try
{
con.Open();
SqlCommand cmd = new SqlCommand("SP_DMCS_INSERT_REQUIREMENTS", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@requirement_desc", id.requirement_desc);
cmd.ExecuteReader();
con.Close();
con.Open();
cmd = new SqlCommand("SP_DMCS_GET_REQUIREMENTS", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@requirement_desc", id.requirement_desc);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
rdr.Read();
id.req_id = rdr.GetInt32(0);
}
}
catch (Exception ex)
{
id.SetColumnDefaults();
}
finally
{
con.Close();
}
return id;
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
string flag = context.Request["flag"].ToString();
string struid = context.Session["ID"].ToString();
string strSQL = "update dbo.tabUsers set ";
string strDataConn = ConfigurationManager.ConnectionStrings["SQLDataConnStr"].ConnectionString;
SqlConnection dataConn = new SqlConnection(strDataConn);
if (flag == "1")
{
string strusername = context.Request["username"].ToString();
string strutell = context.Request["utell"].ToString();
string struemail = context.Request["uemail"].ToString();
strSQL += "UserName = '******',"+" UserTel = '"+strutell+"',"+"UserEmail = '"+struemail+"' where ID = '"+struid+"'";
SqlCommand command = new SqlCommand(strSQL, dataConn);
dataConn.Open();
command.ExecuteNonQuery();
dataConn.Close();
context.Response.Write("OK");
}
else if (flag == "2")
{
string strpw = context.Request["upassword"].ToString();
strSQL += "UserPW = '" + strpw + "' where UserID = '" + struid + "'";
SqlCommand command = new SqlCommand(strSQL, dataConn);
dataConn.Open();
command.ExecuteNonQuery();
dataConn.Close();
context.Response.Write("OK");
}
}
public void RegisterDiscussMessages()
{
using (var connection = new SqlConnection(_connString))
{
connection.Open();
using (var command = new SqlCommand(@"SELECT [Id]
,[Username]
,[Message]
,[FilePath]
,[Status]
,[CreatedDate]
FROM [dbo].[Discuss] ORDER BY [dbo].[Discuss].CreatedDate desc", connection))
{
command.Notification = null;
var dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
if (connection.State == ConnectionState.Closed)
connection.Open();
var reader = command.ExecuteReader();
}
}
}
public static string ConfirmQrCode(int qrCode, string newUserOpenId)
{
string originalUserOpenId = "";
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["constr"].Trim());
SqlCommand cmd = new SqlCommand(" select qr_invite_list_owner from qr_invite_list where qr_invite_list_id = " + qrCode.ToString(), conn);
conn.Open();
SqlDataReader drd = cmd.ExecuteReader();
if (drd.Read())
{
originalUserOpenId = drd.GetString(0);
}
drd.Close();
conn.Close();
cmd.CommandText = " insert into qr_invite_list_detail ( qr_invite_list_detail_id , qr_invite_list_detail_openid ) "
+ " values ('" + qrCode.ToString() + "' , '" + newUserOpenId.Replace("'", "").Trim() + "' ) ";
conn.Open();
try
{
cmd.ExecuteNonQuery();
}
catch
{
}
conn.Close();
return originalUserOpenId.Trim();
}
protected void ButtonLog_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["RegConnectionString"].ConnectionString);
con.Open();
string checkUser = "******" + TextBoxUser.Text + "'";
SqlCommand com = new SqlCommand(checkUser, con);
int temp = Convert.ToInt32(com.ExecuteScalar().ToString());
con.Close();
if (temp == 1)
{
con.Open();
string checkPass = "******" + TextBoxUser.Text + "'";
SqlCommand passCom = new SqlCommand(checkPass, con);
string password = passCom.ExecuteScalar().ToString().Replace(" ","");
if (password == TextBoxPW.Text)
{
Session["new"] = TextBoxUser.Text;
Response.Write("Password correct");
Response.Redirect("Claim.aspx");
}
else {
Response.Write("Password incorrect");
}
}
else {
Response.Write("Username is incorrect");
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
DataTable dtNew = new DataTable();
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("select * from all_triathlon_timing", con))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
con.Close();
int count = dt.Rows.Count;
if (count != 0)
{
using (SqlCommand cmdNew = new SqlCommand("select GUID from all_triathlon_timing where BIBNumber='" + txt123Bib.Text.Trim() + "'", con))
{
con.Open();
SqlDataAdapter daNew = new SqlDataAdapter(cmdNew);
daNew.Fill(dtNew);
string abc = dtNew.Rows[0][0].ToString();
Response.Redirect("GetCertificate.aspx?abc=" + abc);
}
}
}
}
}
protected void btnChk_Click(object sender, EventArgs e)
{
string constr = WebConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
SqlConnection conn = new SqlConnection(constr);
conn.Open();
String Checkuser = "******" + TextUser.Text + "'";
SqlCommand com = new SqlCommand(Checkuser, conn);
int temp = Convert.ToInt32(com.ExecuteScalar().ToString());
conn.Close();
if (temp == 1)
{
conn.Open();
string checkPasswordQuery = "select passWord from person where Username ='******'";
string test = "select Fname from person where Username ='******'";
string id = "select PersID from person where Username ='******'";
SqlCommand passComm = new SqlCommand(checkPasswordQuery, conn);
SqlCommand testComm = new SqlCommand(test, conn);
SqlCommand idComm = new SqlCommand(id,conn);
string password = passComm.ExecuteScalar().ToString().Replace(" ", "");
string testtest = testComm.ExecuteScalar().ToString().Replace(" ", "");
string persid = idComm.ExecuteScalar().ToString().Replace(" ", "");
if (password == TextPass.Text)
{
Session["New"] = testtest;
Session["status"] = true;
Session["ID"] = persid;
Response.Redirect("~/form");
}
}
}
protected void ButtonEnviar_Click(object sender, EventArgs e)
{
try
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistroConnectionString"].ConnectionString);
conn.Open();
String contar_mensajes = "select count(*) from Mensaje_privado";
SqlCommand command = new SqlCommand(contar_mensajes, conn);
int contador = Convert.ToInt32(command.ExecuteScalar().ToString());
conn.Close();
conn.Open();
String enviar_mensaje = "insert into Mensaje_privado (id_mensaje, id_remitente, id_buzon, leido, mensaje, asunto, fecha_de_envio) values (" + contador + ", " + Bandeja_Entrada.id_usuario + ", " + Bandeja_Entrada.id_destino + ", " + 0 + ", '" + TextBoxEM.Text + "', '" + LabelAsunto.Text + "', CURRENT_TIMESTAMP)";
command = new SqlCommand(enviar_mensaje, conn);
command.ExecuteNonQuery();
Response.Write("Mensaje enviado!");
Panel1.Visible = false;
ButtonVolver.Visible = true;
conn.Close();
}
catch (Exception ex)
{
}
}
protected void Button4_Click(object sender, EventArgs e)//开启或关闭聊天室
{
if (!DataAccess.chat.IsOpen())
{
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["HomeChatConnectionString"].ToString());
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "UPDATE [chat] SET isopen=0;UPDATE [chat] SET IsOpen=1 WHERE id=@id";
cmd.Parameters.AddWithValue("@id", chatnum);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
Application["IsOpen"] = 1;
Response.Redirect("manage.aspx");
}
else
{
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["HomeChatConnectionString"].ToString());
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "UPDATE [chat] SET IsOpen=0 delete from KickedUser";
cmd.Parameters.AddWithValue("@id", chatnum);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
cmd.CommandText = "UPDATE [user] SET IsOnline=0 WHERE UserID<>1";//UserID=1的是用户名为‘大家’的用户，将永远开启
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
Application["IsOpen"] = 0;
Response.Redirect("manage.aspx");
}
}
private void onload()
{
string strSQLconnection = @"Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aviation.mdf; Trusted_Connection=True;User Instance=True";
SqlConnection sqlConnection = new SqlConnection(strSQLconnection);
SqlCommand sqlCommand = new SqlCommand("SELECT p.username,p.nama,p.jawatan,p.bahagian,p.telefon_bimbit,p.telefon_pejabat,p.email FROM profil_pegawai p, supervisor s WHERE p.username = s.ic ORDER BY p.nama", sqlConnection);
try
{
if (sqlConnection.State.Equals(ConnectionState.Open))
sqlConnection.Close();
if (sqlConnection.State.Equals(ConnectionState.Closed))
sqlConnection.Open();
SqlDataAdapter mySqlAdapter = new SqlDataAdapter(sqlCommand);
DataSet myDataSet = new DataSet();
mySqlAdapter.Fill(myDataSet);
gridpenyelia.DataSource = myDataSet;
gridpenyelia.DataBind();
if (sqlConnection.State.Equals(ConnectionState.Open))
sqlConnection.Close();
if (sqlConnection.State.Equals(ConnectionState.Closed))
sqlConnection.Open();
}
catch (SqlException ex)
{
}
finally
{
}
}
public List<DiscussMessageModel> RegisterDiscussMessages()
{
var messageChat = new List<DiscussMessageModel>();
using (var connection = new SqlConnection(_connString))
{
connection.Open();
using (var command = new SqlCommand(@"SELECT *
FROM [dbo].[Comments]", connection))
{
command.Notification = null;
var dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
if (connection.State == ConnectionState.Closed)
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
messageChat.Add(item: new DiscussMessageModel
{
Id = reader["Id"].ToString(),
Username = reader["Username"].ToString(),
Message = reader["Message"].ToString(),
FilePath = reader["FilePath"].ToString(),
Avatar = "dist/img/avatar.png",
Status = reader["Status"].ToString(),
CreatedDate = DateTime.Parse(reader["CreatedDate"].ToString())
});
}
}
}
return messageChat;
}
protected void Button_Login_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString);
conn.Open();
string checkuser = "******" + TextBoxUserName.Text + "'";
SqlCommand con = new SqlCommand(checkuser, conn);
int temp = Convert.ToInt32(con.ExecuteScalar().ToString());
conn.Close();
if (temp == 1)
{
conn.Open();
string checkPasswordQuery = "select password from User_Data where UserName='******'";
SqlCommand passComm = new SqlCommand(checkPasswordQuery, conn);
string password = passComm.ExecuteScalar().ToString().Replace(" ","");
if (password == TextBoxPassword.Text)
{
Session["New"] = TextBoxUserName.Text;
Response.Write("Login Successful");
Response.Redirect("ReportACrime.aspx");
}
else
{
Response.Write("Password incorrect");
}
}
else
{
Response.Write("User does not exist ");
}
}
public IEnumerable<Message> GetData()
{
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand("SELECT [ID], [Date], [Message] FROM [dbo].[Messages]", connection))
{
command.Notification = null;
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += DependencyOnChange;
if (connection.State == ConnectionState.Closed)
connection.Open();
using (var reader = command.ExecuteReader())
return reader.Cast<IDataRecord>()
.Select(x => new Message()
{
ID = x.GetInt32(0),
Date = x.GetDateTime(1),
Text = x.GetString(2)
}).ToList();
}
}
}
public job_category Insert(job_category id)
{
string ConnectionString = IDManager.connection();
SqlConnection con = new SqlConnection(ConnectionString);
try
{
con.Open();
SqlCommand cmd = new SqlCommand("SP_DMCS_INSERT_JOB_CATEGORY", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@job_category_name", id.job_category_name);
cmd.ExecuteReader();
con.Close();
con.Open();
cmd = new SqlCommand("SP_DMCS_GET_JOB_CATEGORY", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@job_category_name", id.job_category_name);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
rdr.Read();
id.job_cat_id = rdr.GetInt32(0);
}
}
catch (Exception ex)
{
id.SetColumnDefaults();
}
finally
{
con.Close();
}
return id;
}
//The DAL managers contain methods that assign parameters to Procedures Stored
//in the Database. each method return relevant infromation - dataSets that binds
//to grids, objects and values to be assigned and used and true/false values so
//show if information in the DB has been entered/replaced/updated
public bool CreateAccount(Customer newCustomer, Account newAccount)
{
bool userCreated = false, accountCreated = false;
using (cxn = new SqlConnection(this.ConnectionString))
{
using (cmd = new SqlCommand("spCreateCustomer", cxn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = newCustomer.FirstName;
cmd.Parameters.Add("@Surname", SqlDbType.NVarChar, 50).Value = newCustomer.Surname;
cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 50).Value = newCustomer.Email;
cmd.Parameters.Add("@Phone", SqlDbType.NVarChar, 50).Value = newCustomer.Phone;
cmd.Parameters.Add("@AddressLine1", SqlDbType.NVarChar, 50).Value = newCustomer.AddressLine1;
cmd.Parameters.Add("@AddressLine2", SqlDbType.NVarChar, 50).Value = newCustomer.AddressLine2;
cmd.Parameters.Add("@City", SqlDbType.NVarChar, 50).Value = newCustomer.City;
cmd.Parameters.Add("@County", SqlDbType.NVarChar, 50).Value = newCustomer.County;
cmd.Parameters.Add("@CustomerID", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
cmd.Parameters.Add("@OnlineCustomer", SqlDbType.Bit).Value = newCustomer.OnlineCustomer;
cxn.Open();
int rows = cmd.ExecuteNonQuery();
cxn.Close();
if (rows > 0)
{
newCustomer.CustomerID = Convert.ToInt32(cmd.Parameters["@CustomerID"].Value);
newAccount.CustomerID = newCustomer.CustomerID;
userCreated = true;
}
}
using (cmd = new SqlCommand("spCreateAccount", cxn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@AccountType", SqlDbType.NVarChar, 50).Value = newAccount.AccountType;
cmd.Parameters.Add("@SortCode", SqlDbType.Int, 8).Value = newAccount.SortCode;
cmd.Parameters.Add("@InitialBalance", SqlDbType.Int, 8).Value = newAccount.Balance;
cmd.Parameters.Add("@OverDraft", SqlDbType.Int, 8).Value = newAccount.OverDraftLimit;
cmd.Parameters.Add("@CustomerID", SqlDbType.Int, 4).Value = newAccount.CustomerID;
cmd.Parameters.Add("@AccountNumber", SqlDbType.Int, 8).Direction = ParameterDirection.Output;
cxn.Open();
int rows = cmd.ExecuteNonQuery();
cxn.Close();
if (rows > 0)
{
newAccount.AccountNumber = Convert.ToInt32(cmd.Parameters["@AccountNumber"].Value);
accountCreated = true;
}
}
if (userCreated && accountCreated)
return true;
else
return false;
}
}
/// <summary>
/// Добавление наград
/// </summary>
/// <param name="newR">Новая награда</param>
/// <returns></returns>
public bool AddReward(Reward newR)
{
using (var connection = new SqlConnection(ConnectionString()))
{
if (newR.Image == null)
{
var command = new SqlCommand(
"INSERT INTO dbo.Rewards (Title, Description) " +
"VALUES (@Title, @Description)", connection);
command.Parameters.AddWithValue("@Title", newR.Title);
command.Parameters.AddWithValue("@Description", newR.Description);
connection.Open();
return command.ExecuteNonQuery() == 1;
}
else
{
var command = new SqlCommand(
"INSERT INTO dbo.Rewards (Title, Description, Image) " +
"VALUES (@Title, @Description, @Image)", connection);
command.Parameters.AddWithValue("@Title", newR.Title);
command.Parameters.AddWithValue("@Description", newR.Description);
command.Parameters.AddWithValue("@Image", newR.Image);
connection.Open();
return command.ExecuteNonQuery() == 1;
}
}
}
protected void loginButton_Click(object sender, EventArgs e)
{
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
string checkUser = "******"+usernameTextBox.Text+"' ";
SqlCommand command = new SqlCommand(checkUser,connection);
int temp = Convert.ToInt32(command.ExecuteScalar().ToString());
connection.Close();
if (temp==1)
{
connection.Open();
string checkPass = "******" + usernameTextBox.Text + "'";
SqlCommand passCommand = new SqlCommand(checkPass,connection);
string password = passCommand.ExecuteScalar().ToString();
if (password==passTextBox.Text)
{
Session["loginSession"] = usernameTextBox.Text;
msgLabel.Text="Login Successfull";
Response.Redirect("Home.aspx");
}
else
{
msgLabel.Text = "Incorrect password !";
}
}
else
{
msgLabel.Text="UserName Is not Correct";
}
}
protected void btn_Aemp_Click(object sender, EventArgs e)
{
string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
if (rd_Male.Checked == true)
{
SqlCommand cmd1 = new SqlCommand("select count(*) from Retail1_emp where Eid ='" + txt_Eid.Text + "'and Store='" + storeName + "'", con);
SqlCommand cmd = new SqlCommand("insert into Retail1_emp(Eid,Ssn,Name,Sex,Title,Edate,Salary,Store) values ('" + txt_Eid.Text + "','" + txt_Ssn.Text + "','" + txt_Name.Text + "','" + rd_Male.Text + "','" + txt_Title.Text + "','" + txt_Edate.Text + "','" + txt_Salary.Text + "','" + storeName +"')", con);
con.Open();
int count = Convert.ToInt32(cmd1.ExecuteScalar().ToString());
if (count == 1)
{
lbl_Aemp.Text = "EMPLOYEE ALREADY EXISTS!!";
}
else
{
cmd.ExecuteNonQuery();
lbl_Aemp.Text = "EMPLOYEE ADDED SUCCESSFULLY!!";
}
//cmd.ExecuteNonQuery();
// MultiView1.ActiveViewIndex = 5;
txt_Eid.Text = "";
txt_Name.Text = "";
txt_Title.Text = "";
txt_Salary.Text = "";
txt_Edate.Text = "";
txt_Ssn.Text = "";
}
else if (rd_Female.Checked == true)
{
SqlCommand cmd2 = new SqlCommand("select count(*) from Retail1_emp where Eid ='" + txt_Eid.Text + "'and Store='" + storeName + "'", con);
SqlCommand cmd = new SqlCommand("insert into Retail1_emp(Eid,Ssn,Name,Sex,Title,Edate,Salary,Store) values ('" + txt_Eid.Text + "','" + txt_Ssn.Text + "','" + txt_Name.Text + "','" + rd_Female.Text + "','" + txt_Title.Text + "','" + txt_Edate.Text + "','" + txt_Salary.Text + "','" + storeName + "')", con);
con.Open();
int count = Convert.ToInt32(cmd2.ExecuteScalar().ToString());
if (count == 1)
{
lbl_Aemp.Text = "EMPLOYEE ALREADY EXISTS!!";
}
else
{
cmd.ExecuteNonQuery();
lbl_Aemp.Text = "EMPLOYEE ADDED SUCCESSFULLY!!";
}
// cmd.ExecuteNonQuery();
// MultiView1.ActiveViewIndex = 5;
txt_Eid.Text = "";
txt_Name.Text = "";
txt_Title.Text = "";
txt_Salary.Text = "";
txt_Edate.Text = "";
txt_Ssn.Text = "";
}
}
MultiView1.ActiveViewIndex = 5;
lbl_Aemp.Visible = true;
}
protected void LoginButton_Click(object sender, EventArgs e)
{
PasswordErrorLB.Text = "";
UserNameErrorLB.Text = "";
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString);
conn.Open();
string checkUser = "******" + UserNameTB.Text + "'";
SqlCommand com = new SqlCommand(checkUser, conn);
int numUsers = Convert.ToInt32(com.ExecuteScalar().ToString());
conn.Close();
if (numUsers == 1)
{
conn.Open();
string checkPassword = "******" + UserNameTB.Text + "'";
SqlCommand com2 = new SqlCommand(checkPassword, conn);
string password = com2.ExecuteScalar().ToString().Replace(" ", "");
conn.Close();
if(password == PasswordTB.Text)
{
Session["New"] = UserNameTB.Text;
Response.Redirect("MainMenuPage.aspx");
}
else
{
PasswordErrorLB.Text = "You Must Enter a Correct Password";
}
}
else
{
UserNameErrorLB.Text = "You Must Enter a Correct Username";
}
}
/// <summary>
/// Kiem tra ket noi DataBase
/// </summary>
/// <returns></returns>
public bool CheckConnection()
{
// Adapter and DataSet
conn = Utils.getConnection();
strSQL = "SELECT Count(EmployeeID) FROM tblEmployee";
sqlCommand = new SqlCommand(strSQL, conn);
try
{
if (conn.State == ConnectionState.Broken)
{
conn.Close();
conn.Open();
}
else if(conn.State == ConnectionState.Closed)
{
conn.Open();
}
sqlCommand.ExecuteNonQuery();
return true;
}
catch(Exception)
{
return false;
}
finally
{
conn.Close();
}
}
public demobilization Insert(demobilization id)
{
string ConnectionString = IDManager.connection();
SqlConnection con = new SqlConnection(ConnectionString);
try
{
con.Open();
SqlCommand cmd = new SqlCommand("SP_DMCS_INSERT_DEMOBILIZATION", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.ExecuteReader();
con.Close();
con.Open();
cmd = new SqlCommand("SP_DMCS_GET_DEMOBILIZATION", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
rdr.Read();
id.demo_id = rdr.GetInt32(0);
}
}
catch (Exception ex)
{
id.SetColumnDefaults();
}
finally
{
con.Close();
}
return id;
}
protected void Button2_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(constr);
con.Open();
SqlCommand cmd2 = new SqlCommand(" UPDATE CPE02 SET Approve='1' WHERE ProjectID = '" + DropDownList2.SelectedValue.ToString() + "' ", con);
cmd2.ExecuteNonQuery();
con.Close();
Button2.Visible = true;
string text = "select * from StudentProject s join CPE02 c on s.ProjectID = c.ProjectID where s.Advisorusername='******' and s.ProjectID = @ProjectID";
if (DropDownList1.SelectedValue.ToString() == "approved")
{
Button2.Visible = false;
text = "select * from StudentProject s join CPE02 c on s.ProjectID = c.ProjectID where s.Advisorusername='******' and s.ProjectID = @ProjectID and c.Approve = '1'";
}
else if (DropDownList1.SelectedValue.ToString() == "waiting")
{
text = "select * from StudentProject s join CPE02 c on s.ProjectID = c.ProjectID where s.Advisorusername='******' and s.ProjectID = @ProjectID and c.Approve = '0'";
}
if (DropDownList2.SelectedValue != "choose")
{
con.Open();
SqlCommand cmd = new SqlCommand(text, con);
cmd.Parameters.AddWithValue("@ProjectID", DropDownList2.SelectedValue.ToString());
SqlDataReader dr = cmd.ExecuteReader();
GridView1.DataSource = dr;
GridView1.DataBind();
}
else
{
Button2.Visible = false;
}
}
public void run()
{
string liveConString = ConfigurationManager.AppSettings["dailyconstring"];
string reportConString = ConfigurationManager.AppSettings["dailyconstringReportingServer"];
string EngageConString = ConfigurationManager.AppSettings["engageconstring"];
string VidConString = ConfigurationManager.AppSettings["dailyconstringReportingServer"];
string targetMonth = ConfigurationManager.AppSettings["TargetMonth"];
string previousMonth = ConfigurationManager.AppSettings["PreviousMonth"];
string monthEndSubsUpdateQueries = ConfigurationManager.AppSettings["MonthEndSubsUpdateQueries"];//'|' delimited, string replace targetmonth,previousmonth
//string NowConString = ConfigurationManager.AppSettings[""];
try
{
SqlConnection con = new SqlConnection();
DataSet dsResult = new DataSet();
con.ConnectionString = reportConString;
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = 1200;
//cmd.CommandText = @"select case when count(*) =0 then 0 else sum(t.numbers) end from (select isnull(count(distinct u.userid),0) as 'numbers' from users u inner join country c on u.countrycode=c.code inner join gomssubsidiary g on c.gomssubsidiaryid=g.gomssubsidiaryid where cast(registrationdate as date) = '" + traverserDate.Date.ToString("yyyy-MM-dd") + @"' and case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end = 'GLB : " + region + @"' group by case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end) as t union all select case when count(*) =0 then 0 else sum(t.numbers) end from (select isnull(count(distinct er.userid),0) as 'numbers' from entitlementrequests er inner join users u on u.userid=er.userid inner join country c on u.countrycode=c.code inner join gomssubsidiary g on c.gomssubsidiaryid=g.gomssubsidiaryid inner join transactions t on er.userid=t.userid left outer join ppctype ppc on left(t.reference,2)=ppc.ppcproductcode left join purchaseitems pu on pu.entitlementrequestid = er.entitlementrequestid where er.productid in (1,3,4,5,6,7,8,9) and er.source <>'TFC.tv Everywhere' and cast(er.enddate as date)>='" + traverserDate.AddDays(1).ToString("yyyy-MM-dd") + @"' and er.daterequested<='" + traverserDate.AddDays(1).ToString("yyyy-MM-dd") + @"' and (subscriptionppctypeid is null or subscriptionppctypeid not in (2,3,4)) and case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end = 'GLB : " + region + @"' group by case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end) as t union all select case when count(*) =0 then 0 else sum(t.numbers) end from (select isnull(count(distinct er.userid),0) as 'numbers' from entitlementrequests er inner join users u on u.userid=er.userid inner join country c on u.countrycode=c.code inner join gomssubsidiary g on c.gomssubsidiaryid=g.gomssubsidiaryid inner join transactions t on er.userid=t.userid left outer join ppctype ppc on left(t.reference,2)=ppc.ppcproductcode left join purchaseitems pu on pu.entitlementrequestid = er.entitlementrequestid where er.productid in (1,3,4,5,6,7,8,9) and er.source <>'TFC.tv Everywhere' and cast(er.enddate as date)='" + traverserDate.ToString("yyyy-MM-dd") + @"' and er.daterequested<='" + traverserDate.AddDays(1).ToString("yyyy-MM-dd") + @"' and (subscriptionppctypeid is null or subscriptionppctypeid not in (2,3,4)) and case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end = 'GLB : " + region + @"' group by case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end) as t union all select case when count(*) =0 then 0 else sum(t.numbers) end from (select isnull(count(distinct userid),0) as 'numbers' from ( select u.email, er.userid,u.firstname,u.lastname,er.productid,er.enddate,u.countrycode from entitlementrequests er inner join users u on u.userid=er.userid inner join transactions t on er.userid=t.userid left outer join ppctype ppc on left(t.reference,2)=ppc.ppcproductcode left outer join purchaseitems pu on pu.entitlementrequestid = er.entitlementrequestid where er.productid in (1,3,4,5,6,7,8,9) and er.source <>'TFC.tv Everywhere' and (subscriptionppctypeid is null or subscriptionppctypeid not in (2,3,4)) and cast(er.daterequested as date)= '" + traverserDate.ToString("yyyy-MM-dd") + @"' and er.UserId not in( select er.userid from entitlementrequests er inner join users u on u.userid=er.userid inner join transactions t on er.userid=t.userid left outer join ppctype ppc on left(t.reference,2)=ppc.ppcproductcode left outer join purchaseitems pu on pu.entitlementrequestid = er.entitlementrequestid where er.productid in (1,3,4,5,6,7,8,9) and er.source <>'TFC.tv Everywhere' and (subscriptionppctypeid is null or subscriptionppctypeid not in (2,3,4)) and cast(er.daterequested as date)< '" + traverserDate.ToString("yyyy-MM-dd") + @"' ) ) as temp inner join country c on c.code=temp.countrycode inner join gomssubsidiary g on c.gomssubsidiaryid=g.gomssubsidiaryid and case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end = 'GLB : " + region + @"' group by case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end) as t union all select case when count(*) =0 then 0 else sum(t.numbers) end from (select isnull(count(distinct userid),0) as 'numbers' from ( select u.email, er.userid,u.firstname,u.lastname,er.productid,er.enddate,u.countrycode from entitlementrequests er inner join users u on u.userid=er.userid inner join transactions t on er.userid=t.userid left outer join ppctype ppc on left(t.reference,2)=ppc.ppcproductcode left outer join purchaseitems pu on pu.entitlementrequestid = er.entitlementrequestid where er.productid in (1,3,4,5,6,7,8,9) and er.source <>'TFC.tv Everywhere' and (subscriptionppctypeid is null or subscriptionppctypeid not in (2,3,4)) and cast(er.daterequested as date)= '" + traverserDate.ToString("yyyy-MM-dd") + @"' and er.UserId in( select er.userid from entitlementrequests er inner join users u on u.userid=er.userid inner join transactions t on er.userid=t.userid left outer join ppctype ppc on left(t.reference,2)=ppc.ppcproductcode left outer join purchaseitems pu on pu.entitlementrequestid = er.entitlementrequestid where er.productid in (1,3,4,5,6,7,8,9) and er.source <>'TFC.tv Everywhere' and (subscriptionppctypeid is null or subscriptionppctypeid not in (2,3,4)) and cast(er.daterequested as date)< '" + traverserDate.ToString("yyyy-MM-dd") + @"' ) ) as temp inner join country c on c.code=temp.countrycode inner join gomssubsidiary g on c.gomssubsidiaryid=g.gomssubsidiaryid and case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end = 'GLB : " + region + @"' group by case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end) as t union all select case when count(*) =0 then 0 else sum(t.numbers) end from (select isnull(count(distinct er.userid),0) as 'numbers' from entitlementrequests er inner join users u on u.userid=er.userid inner join country c on c.code=u.countrycode inner join gomssubsidiary g on c.gomssubsidiaryid=g.gomssubsidiaryid inner join products p on p.productid=er.productid inner join transactions t on er.userid=t.userid left outer join ppctype ppc on left(t.reference,2)=ppc.ppcproductcode left join purchaseitems pu on pu.entitlementrequestid = er.entitlementrequestid where (p.alacartesubscriptiontypeid is not null or er.productid=2) and er.source <>'TFC.tv Everywhere' and cast(er.enddate as date)>='" + traverserDate.AddDays(1).ToString("yyyy-MM-dd") + @"' and er.daterequested<='" + traverserDate.AddDays(1).ToString("yyyy-MM-dd") + @"' and (subscriptionppctypeid is null or subscriptionppctypeid not in (2,3,4)) and case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end = 'GLB : " + region + @"' group by case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end) as t union all select case when count(*) =0 then 0 else sum(t.numbers) end from (select isnull(count(distinct userid),0) as 'numbers' from ( select u.email, er.userid,u.firstname,u.lastname,er.productid,er.enddate,u.countrycode from entitlementrequests er inner join users u on u.userid=er.userid inner join products p on p.productid=er.productid inner join transactions t on er.userid=t.userid left outer join ppctype ppc on left(t.reference,2)=ppc.ppcproductcode left outer join purchaseitems pu on pu.entitlementrequestid = er.entitlementrequestid where (p.alacartesubscriptiontypeid is not null or er.productid=2) and er.source <>'TFC.tv Everywhere' and (subscriptionppctypeid is null or subscriptionppctypeid not in (2,3,4)) and cast(er.daterequested as date)= '" + traverserDate.ToString("yyyy-MM-dd") + @"' ) as temp inner join country c on c.code=temp.countrycode inner join gomssubsidiary g on c.gomssubsidiaryid=g.gomssubsidiaryid where case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end = 'GLB : " + region + @"' group by case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end) as t union all select case when count(*) =0 then 0 else sum(t.numbers) end from (select isnull(count(distinct er.userid),0) as 'numbers' from entitlementrequests er inner join users u on u.userid=er.userid inner join country c on c.code=u.countrycode inner join gomssubsidiary g on c.gomssubsidiaryid=g.gomssubsidiaryid inner join products p on p.productid=er.productid inner join transactions t on er.userid=t.userid left outer join ppctype ppc on left(t.reference,2)=ppc.ppcproductcode left outer join purchaseitems pu on pu.entitlementrequestid = er.entitlementrequestid where (p.alacartesubscriptiontypeid is not null or er.productid=2) and er.source <>'TFC.tv Everywhere' and cast(er.enddate as date)='" + traverserDate.ToString("yyyy-MM-dd") + @"' and er.daterequested<='" + traverserDate.AddDays(1).ToString("yyyy-MM-dd") + @"' and (subscriptionppctypeid is null or subscriptionppctypeid not in (2,3,4)) and case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end = 'GLB : " + region + @"' group by case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end) as t";
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
protected void BindDDL2()
{
SqlConnection con = new SqlConnection(constr);
con.Open();
SqlCommand cmd1 = new SqlCommand("select DISTINCT s.ProjectID,s.ProjectnameTH from StudentProject s join CPE02 c on s.ProjectID = c.ProjectID where s.Advisorusername='******'", con);
SqlDataReader reader1 = cmd1.ExecuteReader();
int count = 0;
while (reader1.Read())
{
count++;
}
con.Close();
if (count == 0)
{
DropDownList2.Items.Add(new ListItem("ไม่มีข้อมูล", "none"));
DropDownList2.Items.FindByValue("none").Selected = true;
}
else
{
DataTable dt = new DataTable();
SqlCommand cmdxx = new SqlCommand("select DISTINCT s.ProjectID,s.ProjectnameTH from StudentProject s join CPE02 c on s.ProjectID = c.ProjectID where s.Advisorusername='******'", con);
con.Open();
dt.Load(cmdxx.ExecuteReader());
con.Close();
DropDownList2.DataSource = dt;
DropDownList2.DataTextField = "ProjectnameTH";
DropDownList2.DataValueField = "ProjectID";
DropDownList2.DataBind();
DropDownList2.Items.Add(new ListItem("Choose", "choose"));
DropDownList2.Items.FindByValue("choose").Selected = true;
}
}
private void listBox1_Click(object sender, EventArgs e)
{
string strTableName = this.listBox1.SelectedValue.ToString();//获取数据表名称
using (SqlConnection con = new SqlConnection(//创建数据库连接对象
@"server=(local);uid=sa;Pwd=6221131;database='" + strDatabase + "'"))
{
string strSql =//创建SQL字符串
@"select name 字段名, xusertype 类型编号, length 长度 into hy_Linshibiao
from syscolumns where id=object_id('" + strTableName + "') ";
strSql +=//添加字符串信息
@"select name 类型,xusertype 类型编号 into angel_Linshibiao from
systypes where xusertype in (select xusertype
from syscolumns where id=object_id('" + strTableName + "'))";
con.Open();//打开数据库连接
SqlCommand cmd = new SqlCommand(strSql, con);//创建命令对象
cmd.ExecuteNonQuery();//执行SQL命令
con.Close();//关闭数据库连接
SqlDataAdapter da = new SqlDataAdapter(//创建数据适配器
@"select 字段名,类型,长度 from
hy_Linshibiao t,angel_Linshibiao b where t.类型编号=b.类型编号", con);
DataTable dt = new DataTable();//创建数据表
da.Fill(dt);//填充数据表
this.dataGridView1.DataSource = dt.DefaultView;//设置数据源
SqlCommand cmdnew = new SqlCommand(//创建命令对象
"drop table hy_Linshibiao,angel_Linshibiao", con);
con.Open();//打开数据库连接
cmdnew.ExecuteNonQuery();//执行SQL命令
con.Close();//关闭数据库连接
}
}
// Lấy Messages cần gửi
public List<DiscussMessage> GetMessagesChat()
{
var messageChat = new List<DiscussMessage>();
using (var connection = new SqlConnection(_connString))
{
connection.Open();
using (var command = new SqlCommand(@"SELECT dbo.Messages.Id, dbo.Messages.[Content], dbo.Messages.SenderId, dbo.Messages.SendDate, dbo.Messages.Type
FROM dbo.Messages
WHERE dbo.Messages.Sent = 'N' ORDER BY dbo.Messages.SendDate ASC", connection))
{
command.Notification = null;
if (connection.State == ConnectionState.Closed)
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
messageChat.Add(item: new DiscussMessage
{
Id = reader["Id"].ToString(),
Content = reader["Content"].ToString(),
SenderId = reader["SenderId"].ToString(),
UserName = "",
SendDate = reader["SendDate"].ToString(),
Type = reader["Type"].ToString(),
GroupId = ""
});
}
}
}
return messageChat;
}
public mobile_employees Insert(mobile_employees id)
{
string ConnectionString = IDManager.connection();
SqlConnection con = new SqlConnection(ConnectionString);
try
{
con.Open();
SqlCommand cmd = new SqlCommand("SP_DMCS_INSERT_MOBILE_EMPLOYEES", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@mobile_unit_id", id.mobile_unit_id);
cmd.Parameters.AddWithValue("@employee_id", id.employee_id);
cmd.ExecuteReader();
con.Close();
con.Open();
cmd = new SqlCommand("SP_DMCS_GET_MOBILE_EMPLOYEES", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@mobile_unit_id", id.mobile_unit_id);
cmd.Parameters.AddWithValue("@employee_id", id.employee_id);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
rdr.Read();
id.mobile_employee_id = rdr.GetInt32(0);
}
}
catch (Exception ex)
{
id.SetColumnDefaults();
}
finally
{
con.Close();
}
return id;
}
protected void Page_Load(object sender, EventArgs e)
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["devi"].ToString());
if (Request.QueryString["t"] != null)
{
temp = Convert.ToInt16(Request.QueryString["t"].ToString());
uid = Convert.ToInt16(Request.QueryString["id"].ToString());
if (temp == 1)
{
con.Open();
cmd = new SqlCommand("update user1_reg set userkey='1' where id='"+uid+"'",con);
cmd.ExecuteNonQuery();
con.Close();
}
if (temp == 2)
{
con.Open();
cmd = new SqlCommand("update user1_reg set userkey='2' where id='" + uid + "'", con);
cmd.ExecuteNonQuery();
con.Close();
}
}
}
static void Main(string[] args)
{
String sc = @"Data Source=.\sqlexpress;Initial Catalog=MASTER;Integrated Security=true;Connect Timeout=1;Pooling=false";
SqlConnection c = null;
try
{
c = new SqlConnection(sc);
c.Open();
c.Open();
}
catch (Exception ex)
{
Console.WriteLine(ex.GetType().Name);
Console.WriteLine(ex.Message);
Console.WriteLine(ex.Source);
}
finally
{
if (/*c != null &&*/ c.State != ConnectionState.Closed)
c.Close();
Console.ReadKey();
}
}
public static void TryOpen(this sql.SqlConnection connection)
{
if (connection?.State == System.Data.ConnectionState.Closed)
{
connection?.Open();
}
}
public IEnumerable GetListD(ReportSearchViewModel model)
{
var List = new List <ReportInforViewModel>();
try
{
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.CommandText = "usp_BaoCaoDoanhThuTheoNhanVien";
cmd.Parameters.AddWithValue("@StoreId", model.StoreId);
cmd.Parameters.AddWithValue("@StaffId", model.StaffId);
cmd.Parameters.AddWithValue("@CashierUserId", model.CashierUserId);
cmd.Parameters.AddWithValue("@CurrentSaleId", currentEmployee.EmployeeId);
cmd.Parameters.AddWithValue("@FromDate", model.FromDate);
cmd.Parameters.AddWithValue("@ToDate", model.ToDate);
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var v = ListView(dr);
List.Add(v);
}
}
conn.Close();
}
}
return(List);
}
catch
{
return(List);
}
}
} //========== end of the mtd
public IEnumerable <Models.OPEX_DETAIL_SHARE_BUD> GetAllOPEXDetailShareBUDMtd_2(string svalue)
{
List <OPEX_DETAIL_SHARE_BUD> oDetailList = new List <OPEX_DETAIL_SHARE_BUD>();
using (var con = new System.Data.SqlClient.SqlConnection(connectionString))
{
var cmd = new System.Data.SqlClient.SqlCommand("spp_proc_sharedcost_2", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.Parameters.Add(new SqlParameter
{
ParameterName = "SearchValue",
Value = svalue,
});
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var op = new OPEX_DETAIL_SHARE_BUD();
op.TEAM_CODE = reader["TEAM_CODE"] != DBNull.Value ? reader["TEAM_CODE"].ToString() : "null";
op.BranchName = reader["BranchName"] != DBNull.Value ? reader["BranchName"].ToString() : "null";
op.RegionName = reader["RegionName"] != DBNull.Value ? reader["RegionName"].ToString() : "null";
op.DIRECTORATENAME = reader["DIRECTORATENAME"] != DBNull.Value ? reader["DIRECTORATENAME"].ToString() : "null";
op.CAPTION = reader["CAPTION"] != DBNull.Value ? reader["CAPTION"].ToString() : "null";
op.AMOUNT = reader["AMOUNT"] != DBNull.Value ? double.Parse(reader["AMOUNT"].ToString()) : 0;
oDetailList.Add(op);
}
con.Close();
//System.Web.HttpContext.Current.Session["opexstaffexpdiff"] = opexstaffexpdiffList;
}
return(oDetailList);
} //========== end of the mtd
public String insertRegistrationDetails(String details)
{
String str = "xyz";
stringmessage message = new stringmessage();
String cs = System.Configuration.ConfigurationManager.ConnectionStrings["dbString"].ConnectionString;
System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(cs);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
//SqlDataReader rdr = null;
str = str + "1";
try
{
con.Open();
str = str + "2";
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "INSERT REGISTRATION (FIRSTNAME,LASTNAME,USERNAME,PASSWORD,EMAILADDRESS,PHONENUMBER,DATEOFBIRTH,ADDRESS,ZIPCODE) VALUES (" + details + ")";
//cmd.CommandText = "INSERT login (email,password) VALUES (" + userDetails + ")";
str = str + "3";
/*cmd.CommandText = "INSERT UserDetails (email,password,firstName,lastName,phno,address,city,state,zipCode) VALUES ('" + userDetails.getEmail() + "'," +
* userDetails.getZipCode() + ")"; */
cmd.Connection = con;
cmd.ExecuteNonQuery();
str = str + "4";
con.Close();
str = str + "Inserted data";
}
catch (Exception e)
{
str = str + "Exception in insert" + e;
}
message.msg = str;
return(str);
}
private void button1_Click(object sender, EventArgs e)
{
System.Data.SqlClient.SqlConnection conn = konn.GetConn();
try
{
conn.Open();
SqlDataReader reader = null;
string sql = "SELECT * FROM Customer WHERE Nomor_Telepon_Customer = '" + textBox1.Text + "'";
SqlCommand command = new SqlCommand(sql, conn);
command.ExecuteNonQuery();
reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
telp = reader["Nomor_Telepon_Customer"].ToString();
}
reader.Close();
}
if (textBox1.Text.Equals(telp))
{
idCus = textBox1.Text;
list_Restoran a = new list_Restoran();
a.Show();
this.Hide();
}
}
catch (Exception ex)
{
MessageBox.Show("Customer Tidak Ada Pada Database");
}
finally
{
conn.Close();
}
}
public void verificarBloqueo()
{
DataTable dt = new DataTable();
DataSet ds = new DataSet();
ds.Tables.Add(dt);
System.Data.SqlClient.SqlConnection conn =
new System.Data.SqlClient.SqlConnection();
// TODO: Modify the connection string and include any
// additional required properties for your database.
conn.ConnectionString =
"integrated security=SSPI;data source=DESKTOP-5NFCGTG\\RONELCRUZ;" +
"persist security info=False;initial catalog=sisfax";
try
{
String Consulta = "select estado from usuarios where contraseña='" + label4.Text + "' and usuario ='" + label2.Text + "' and tipo = '" + label3.Text + "' ";
conn.Open();
SqlCommand Comando = new SqlCommand(Consulta, conn);
SqlDataReader LectorDatos;
LectorDatos = Comando.ExecuteReader();
if (LectorDatos.Read())
{
label7.Text = Convert.ToString(LectorDatos["estado"]);
string cc = label6.Text;
if (label7.Text == "bloqueada")
{
notificacionBloqueo();
}
}
conn.Close();
// MessageBox.Show("La lista de usuarios se ha actualizado ");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public IEnumerable <IncomeAccountsTreeMisCodesTEMP> IncomeAccountsTreeMisCodesTEMPusingparams(string status, string search)
{
List <IncomeAccountsTreeMisCodesTEMP> obuList = new List <IncomeAccountsTreeMisCodesTEMP>();
using (var con = new System.Data.SqlClient.SqlConnection(connectionString))
{
var cmd = new System.Data.SqlClient.SqlCommand("", con);
//cmd.CommandText = "select * from Names where Id=@Id";
//cmd.Parameters.AddWithValue("@Id", id);
con.Open();
//cmd.CommandText = "select top 500 Id, accountnumber, mis, AccountOfficer_Code, ApprovalStatus from Income_accountMIS_Override_TEMP " +
// "where ApprovalStatus=@STATUS and (accountnumber like @searchval OR mis like @searchval OR AccountOfficer_Code like @searchval)";
cmd.CommandText = "select top 1000 * from Income_AccountsTree_MISCodes_TEMP " +
"where ApprovalStatus=@STATUS and (accountnumber like @searchval OR AccountOfficerName like @searchval OR AccountOfficer_Code like @searchval OR Team_Code like @searchval)";
cmd.Parameters.AddWithValue("@STATUS", status);
cmd.Parameters.AddWithValue("@searchval", "%" + search + "%");
System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var obu = new IncomeAccountsTreeMisCodesTEMP();
obu.ID = reader["Id"] != DBNull.Value ? int.Parse(reader["Id"].ToString()) : 0;
obu.AccountNumber = reader["AccountNumber"] != DBNull.Value ? reader["AccountNumber"].ToString() : "";
obu.AccountOfficer_Code = reader["AccountOfficer_Code"] != DBNull.Value ? reader["AccountOfficer_Code"].ToString() : "";
obu.AccountOfficerName = reader["AccountOfficerName"] != DBNull.Value ? reader["AccountOfficerName"].ToString() : "";
obu.ShareRatio = reader["ShareRatio"] != DBNull.Value ? decimal.Parse(reader["ShareRatio"].ToString()) : 0;
obu.Team_Code = reader["Team_Code"] != DBNull.Value ? reader["Team_Code"].ToString() : "";
obu.ApprovalStatus = reader["ApprovalStatus"] != DBNull.Value ? reader["ApprovalStatus"].ToString() : "";
obuList.Add(obu);
}
con.Close();
}
return(obuList);
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
try
{
System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
sc.ConnectionString = ConfigurationManager.ConnectionStrings["RoomMagnet"].ConnectionString;
sc.Open();
SqlCommand update = new SqlCommand();
update.Connection = sc;
update.CommandText = "update RMUser set FirstName = @FirstName, " +
"LastName = @LastName, " +
"Email = @Email, " +
"PhoneNumber = @PhoneNumber," +
"HouseNumber = @HouseNumber," +
"Street = @Street," +
"City = @City," +
"State = @State," +
"Zip = @Zip where UserID = " + Session["USERID"];
update.Parameters.Add(new SqlParameter("@FirstName", HttpUtility.HtmlEncode(tbName.Text)));
update.Parameters.Add(new SqlParameter("@LastName", HttpUtility.HtmlEncode(tbName1.Text)));
update.Parameters.Add(new SqlParameter("@PhoneNumber", HttpUtility.HtmlEncode(tbPhone.Text)));
update.Parameters.Add(new SqlParameter("@Email", HttpUtility.HtmlEncode(tbUname.Text)));
update.Parameters.Add(new SqlParameter("@HouseNumber", HttpUtility.HtmlEncode(tbHouseNumber.Text)));
update.Parameters.Add(new SqlParameter("@Street", HttpUtility.HtmlEncode(tbAddress.Text)));
update.Parameters.Add(new SqlParameter("@City", HttpUtility.HtmlEncode(tbCity.Text)));
update.Parameters.Add(new SqlParameter("@Zip", HttpUtility.HtmlEncode(tbZip.Text)));
update.Parameters.Add(new SqlParameter("@State", HttpUtility.HtmlEncode(ddlState.SelectedValue)));
update.ExecuteNonQuery();
ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('Update Successful!');", true);
}
catch
{
ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('Failed to update');", true);
}
}
public void updateRestoran()
{
String query = "select * from Restoran";
SqlDataReader reader = null;
System.Data.SqlClient.SqlConnection conn = konn.GetConn();
listView1.Items.Clear();
try
{
conn.Open();
SqlCommand command = new SqlCommand(query, conn);
command.ExecuteNonQuery();
reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
if (!reader["ID_Restoran"].ToString().Equals("0"))
{
ListViewItem listViewItem = new ListViewItem(reader["ID_Restoran"].ToString());
listViewItem.SubItems.Add(reader["Nama_Restoran"].ToString());
listViewItem.SubItems.Add(reader["Alamat_Resto"].ToString());
listViewItem.SubItems.Add(reader["Jadwal_Restoran"].ToString());
listView1.Items.Add(listViewItem);
}
}
reader.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
}
public void keranjang()
{
String query = "select * from Mengambil_Data m join Menu_Makanan e on e.ID_Makanan = m.ID_Makanan where ID_Transaksi = '" + list_Restoran.id + "'";
SqlDataReader reader = null;
System.Data.SqlClient.SqlConnection conn = konn.GetConn();
try
{
conn.Open();
SqlCommand command = new SqlCommand(query, conn);
command.ExecuteNonQuery();
reader = command.ExecuteReader();
listView9.Items.Clear();
if (reader.HasRows)
{
while (reader.Read())
{
ListViewItem listViewItem = new ListViewItem(reader["ID_Makanan"].ToString());
listViewItem.SubItems.Add(reader["Nama_Makanan"].ToString());
listViewItem.SubItems.Add(reader["Harga"].ToString());
listViewItem.SubItems.Add(reader["Jumlah_Makanan"].ToString());
listView9.Items.Add(listViewItem);
jum += int.Parse(reader["Harga"].ToString()) * int.Parse(reader["Jumlah_Makanan"].ToString());
}
reader.Close();
label2.Text = jum.ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
}
public static void UpdateProduct(string theID, string newName, double newPrice, string newDesc)
{
prodCmd.CommandText = "Update Product "
+ "Set ProductName = '" + newName + "', "
+ "UnitPrice = " + newPrice + ", "
+ "Description = '" + newDesc + "' "
+ "Where ProductID = '" + theID + "'";
try
{
con.Open();
prodCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
}
public IEnumerable <IncomeRetailProductOverrideTEMP> IncomeRetailProductOverrideTEMPusingparams(string status, string search)
{
List <IncomeRetailProductOverrideTEMP> obuList = new List <IncomeRetailProductOverrideTEMP>();
using (var con = new System.Data.SqlClient.SqlConnection(connectionString))
{
var cmd = new System.Data.SqlClient.SqlCommand("", con);
//cmd.CommandText = "select * from Names where Id=@Id";
//cmd.Parameters.AddWithValue("@Id", id);
con.Open();
cmd.CommandText = "select * from Income_RetailProduct_Override_TEMP " +
"where ApprovalStatus=@STATUS and (Customerid like @searchval OR Mis_code like @searchval OR AccountOfficer_Code like @searchval)";
//cmd.CommandText = "select Id, Customerid, Bank, Mis_code, AccountOfficer_Code, ApprovalStatus from Income_RetailProduct_Override_TEMP " +
// "where ApprovalStatus=@STATUS and (Customerid like @searchval OR Mis_code like @searchval OR AccountOfficer_Code like @searchval)";
cmd.Parameters.AddWithValue("@STATUS", status);
cmd.Parameters.AddWithValue("@searchval", "%" + search + "%");
System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var obu = new IncomeRetailProductOverrideTEMP();
obu.Id = reader["Id"] != DBNull.Value ? int.Parse(reader["Id"].ToString()) : 0;
obu.Customerid = reader["Customerid"] != DBNull.Value ? int.Parse(reader["Customerid"].ToString()) : 0;
obu.Bank = reader["Bank"] != DBNull.Value ? reader["Bank"].ToString() : "";
obu.Mis_code = reader["Mis_code"] != DBNull.Value ? reader["Mis_code"].ToString() : "";
obu.AccountOfficer_Code = reader["AccountOfficer_Code"] != DBNull.Value ? reader["AccountOfficer_Code"].ToString() : "";
obu.ApprovalStatus = reader["ApprovalStatus"] != DBNull.Value ? reader["ApprovalStatus"].ToString() : "";
obuList.Add(obu);
}
con.Close();
}
return(obuList);
}
public Manager_client()
{
InitializeComponent();
FileInfo fi1 = new FileInfo("bank.txt");
using (StreamReader sr = fi1.OpenText())
{
string s = "";
s = sr.ReadLine();
sr.Close();
Id_bank = Convert.ToInt32(s);
}
using (SqlConnection cn = new System.Data.SqlClient.SqlConnection())
{
cn.ConnectionString = address;
try
{
cn.Open();
}
catch
{
MessageBox.Show(@"Нет соединения с базой данных. Повторите запрос позднее!", @"Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//string strSql = "SELECT Surname, Name, Patronymic, Age, Id_bank, Id_manager FROM Manager"
string strSql = String.Format(@"SELECT Surname, Name, Patronymic, Age, Id_manager FROM Manager WHERE Id_bank = '{0}'", Id_bank);
SqlCommand cmd = new SqlCommand(strSql, cn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dataset);
dataGridView1.AutoGenerateColumns = true;
bind.DataSource = dataset.Tables[0];
dataGridView1.DataSource = bind;
cn.Close();
dataGridView1.Columns[4].Visible = false;
}
}
// string linksev = Convert.ToString(System.Configuration.ConfigurationManager.AppSettings["linkserver"].Trim());
//string linksev = @"[127.0.0.1\dell].fintrak.dbo.volume_analysis_rundates";
public List <VolumeAnalysisRundatesModel> GetAllVolumeAnalysisRundates()
{
List <VolumeAnalysisRundatesModel> obuList = new List <VolumeAnalysisRundatesModel>();
using (var con = new System.Data.SqlClient.SqlConnection(connectionString))
{
var cmd = new System.Data.SqlClient.SqlCommand("", con);
//string ls = linksev + ".volume_analysis_rundates";
con.Open();
//cmd.CommandText = "SELECT * from [10.1.14.49\fintrack].fintrakreport.dbo.volume_analysis_rundates";
//cmd.CommandText = " SELECT * from @LINKSERVER ";
//var sqlString = string.Format("insert into {0} (time, date, pin) values (@time, @date, @pin)", linksev);
//var sqlString = string.Format("SELECT * from {0} ", linksev);
//cmd.CommandText = sqlString;
// cmd.Parameters.AddWithValue("@LINKSERVER", linksev + ".dbo.volume_analysis_rundates");
cmd.CommandText = "SELECT * from volume_analysis_rundates";
System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var ob = new VolumeAnalysisRundatesModel();
ob.Id = reader["Id"] != DBNull.Value ? Int32.Parse(reader["Id"].ToString()) : 0;
ob.rundate = reader["rundate"] != DBNull.Value ? Convert.ToDateTime(reader["rundate"].ToString()).Date : Convert.ToDateTime("2001-01-01").Date;
ob.visible = reader["visible"] != DBNull.Value ? Convert.ToString(reader["visible"]) : "";
obuList.Add(ob);
}
con.Close();
}
return(obuList);
}
public void loadmenu()
{
String query = "select * from Menu_Makanan s join Kategori_Makanan k on s.Kategori= k.Kategori where ID_Restoran = (select ID_Restoran from Restoran where ID_Restoran = '" + list_Restoran.idres + "') ";
SqlDataReader reader = null;
System.Data.SqlClient.SqlConnection conn = konn.GetConn();
try
{
conn.Open();
SqlCommand command = new SqlCommand(query, conn);
command.ExecuteNonQuery();
reader = command.ExecuteReader();
listView8.Items.Clear();
if (reader.HasRows)
{
while (reader.Read())
{
if (!reader["ID_Makanan"].ToString().Equals("0"))
{
ListViewItem listViewItem = new ListViewItem(reader["ID_Makanan"].ToString());
listViewItem.SubItems.Add(reader["Nama_Makanan"].ToString());
listViewItem.SubItems.Add(reader["Harga"].ToString());
listViewItem.SubItems.Add(reader["Nama_Kategori"].ToString());
listView8.Items.Add(listViewItem);
}
}
reader.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
}
public BO.AssetInventoryTracking.workorder GetByIDworkorder(int workorderID)
{
BO.AssetInventoryTracking.workorder xworkorder = new BO.AssetInventoryTracking.workorder();
string query = "SELECT [workorderID],[date_created],[date_completed],[status],[inventoryID] FROM dbo.[workorder] WHERE workorderID=@workorderID";
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["db_AssetInventoryTracking"].ConnectionString)) {
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn)) {
cmd.Parameters.AddWithValue("@workorderID", workorderID);
conn.Open();
using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
if (reader.Read())
{
if (!object.ReferenceEquals(reader["workorderID"], DBNull.Value))
{
xworkorder.workorderID = int.Parse(reader["workorderID"].ToString());
}
if (!object.ReferenceEquals(reader["date_created"], DBNull.Value))
{
xworkorder.date_created = DateTime.Parse(reader["date_created"].ToString());
}
if (!object.ReferenceEquals(reader["date_completed"], DBNull.Value))
{
xworkorder.date_completed = DateTime.Parse(reader["date_completed"].ToString());
}
if (!object.ReferenceEquals(reader["status"], DBNull.Value))
{
xworkorder.status = reader["status"].ToString();
}
if (!object.ReferenceEquals(reader["inventoryID"], DBNull.Value))
{
xworkorder.inventoryID = int.Parse(reader["inventoryID"].ToString());
}
}
}
}
}
return(xworkorder);
}
public List <Podcast> getAllPodcasts()
{
System.Data.SqlClient.SqlConnection conn = getConnectionData();
List <Podcast> aPodcastsList = new List <Podcast>();
String requete = "";
try
{
conn.Open();
requete = "SELECT PodcastId " +
"FROM Podcasts";
SqlCommand command = new SqlCommand(requete, conn);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Podcast oCurrentPodcast = new Podcast();
oCurrentPodcast.initWithId(Convert.ToInt32(reader["PodcastId"]));
aPodcastsList.Add(oCurrentPodcast);
}
reader.Close();
}
catch (Exception ex)
{
MessageBox.Show("Erreur de connexion à la base de donnée!");
log.Error("--------------------\n" + "Requete: " + requete + "\n" + ex.ToString());
}
finally
{
conn.Close();
}
return(aPodcastsList);
}
protected void OnRowUpdating(object sender, GridViewUpdateEventArgs e)
{
GridViewRow row = GridView1.Rows[e.RowIndex];
int ID = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values[0]);
string firstName = (row.FindControl("txtFirstName") as TextBox).Text;
string lastname = (row.FindControl("txtLastName") as TextBox).Text;
DateTime InfractionDate = Convert.ToDateTime((row.FindControl("txtInfractionDate") as TextBox).Text);
string Description = (row.FindControl("txtDescription") as TextBox).Text;
string county = (row.FindControl("txtCounty") as TextBox).Text;
string court = (row.FindControl("txtCourt") as TextBox).Text;
DateTime HearingDate = Convert.ToDateTime((row.FindControl("txtHearingDate") as TextBox).Text);
System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
sc.ConnectionString = @"Server =Localhost;Database=TeamProject;Trusted_Connection=Yes;";
using (SqlCommand cmd = new SqlCommand("InfractionGroup6_CRUD"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Action", "UPDATE");
cmd.Parameters.AddWithValue("@ID", ID);
cmd.Parameters.AddWithValue("@FirstName", firstName);
cmd.Parameters.AddWithValue("@LastName", lastname);
cmd.Parameters.AddWithValue("@InfractionDate", InfractionDate);
cmd.Parameters.AddWithValue("@Description", Description);
cmd.Parameters.AddWithValue("@County", county);
cmd.Parameters.AddWithValue("@Court", court);
cmd.Parameters.AddWithValue("@HearingDate", HearingDate);
cmd.Connection = sc;
sc.Open();
cmd.ExecuteNonQuery();
sc.Close();
}
GridView1.EditIndex = -1;
this.BindGrid();
}
public bool LogIn(string username, string password)
{
bool result = false;
DataSet ds = new DataSet();
try
{
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection();
connection.ConnectionString = ConfigurationManager.ConnectionStrings["DadabaseNameConnectionString"].ConnectionString;
connection.Open();
string strSQL = "SELECT * FROM (tabel Name) WHERE UserName = '******' AND Password = '******'";
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
command.CommandText = strSQL;
command.Connection = connection;
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(ds, "userExist");
if (ds.Tables[0].Rows.Count > 0)
{
result = true;
}
else
{
result = false;
}
connection.Close();
return(result);
}
catch (System.Data.SqlClient.SqlException oleExp)
{
return(false);
}
}
private void LoadTableNames(string connection, ComboBox comboBox)
{
using (SqlConnection c = new System.Data.SqlClient.SqlConnection(connection))
{
SqlDataAdapter da = new SqlDataAdapter("sp_databases", c);
DataTable t = new DataTable();
Exception err = null;
try
{
if (c.State != ConnectionState.Open)
{
c.Open();
}
Version sqlServerVersion = new Version(c.ServerVersion);
if (sqlServerVersion.Major > 8)
{
da.SelectCommand.CommandText = "SELECT * FROM information_schema.tables";
}
da.Fill(t);
}
catch (Exception e)
{
err = e;
}
List <string> names = new List <string>();
foreach (DataRow r in t.Rows)
{
names.Add(r["TABLE_NAME"] as string);
}
RefreshItems(comboBox, names.OrderBy(p => p).ToList());
}
}
private void Buy(object sender, EventArgs e)
{
con = new System.Data.SqlClient.SqlConnection();
con.ConnectionString = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\properties.mdf;Integrated Security=True;Connect Timeout=30";
con.Open();
if (garage.Text == "Yes")
{
garage.Text = "1";
}
else if (garage.Text == "No")
{
garage.Text = "0";
}
SqlDataAdapter sda = new SqlDataAdapter("SELECT Id,Price FROM [Table01] where 1=1" +
"And (Price >= '" + Minprice.Text + "' OR '" + Minprice.Text + "' =' ')" +
"And (Price <= '" + Maxprice.Text + "'OR'" + Maxprice.Text + "'=' ')" +
"And (Bedrooms >='" + Minbed.Text + "'OR'" + Minbed.Text + "' =' ')" +
"And (Bedrooms <='" + Maxbed.Text + "'OR'" + Maxbed.Text + "' =' ')" +
"And (Isparking ='" + garage.Text + "'OR'" + garage.Text + "' =' ')" +
"And (Bathrooms >= '" + bathroom.Text + "'OR'" + bathroom.Text + "'=' ')" +
"And (LotSize >= '" + Minlot.Text + "'OR '" + Minlot.Text + "' =' ')" +
"And (Zipcode = '" + zipcode.Text + "'OR'" + zipcode.Text + "' =' ')" +
"And (HouseType ='" + type.Text + "'OR'" + type.Text + "' =' ')" +
"And (Action ='Buy')", con); //Search properties base on value user enter.
DataTable dt = new DataTable();
sda.Fill(dt);
dt.DefaultView.Sort = "Price";
dt = dt.DefaultView.ToTable();
dataGridView1.DataSource = dt;
con.Close();
}
private void btnInfoCnn_Click(object sender, EventArgs e)
{
var cryp = new CCryptorEngine();
var cnn_str = cryp.Desencriptar(Properties.Settings.Default[Config.Key_Cnn_DB_Molinetes].ToString());
var array_items = cnn_str.Split(";".ToCharArray());
for (int i = 0; i < array_items.Length; i++)
{
if (array_items[i].ToLower().Contains("password"))
{
array_items[i] = "Password=*****";
}
}
if (MessageBox.Show("La cadena de conexión es:\n\n" + string.Join(";", array_items) + "\n\n¿Desea modificarla?", "Pregunta:", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
{
var result = Microsoft.VisualBasic.Interaction.InputBox("Ingrese la nueva cadena de conexión:", "");
var cnn = new System.Data.SqlClient.SqlConnection();
try
{
cnn.ConnectionString = result;
cnn.Open();
if (cnn.State != ConnectionState.Open)
{
throw new Exception("No fue posible abrir la conexión de la base de datos.");
}
}
catch (Exception ex)
{
MessageBox.Show("Se produjo un error en la configuración:\n" + ex.Message + "\nVuelva a intentar.", "Atención:", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
Properties.Settings.Default[Config.Key_Cnn_DB_Molinetes] = cryp.Encriptar(result);
Properties.Settings.Default.Save();
MessageBox.Show("La conexión con la base de datos fue modificada.", "Atención:", MessageBoxButtons.OK, MessageBoxIcon.Information);
cnn.Dispose();
cnn = null;
}
}
public void DeliveriesForm_Load(object sender, EventArgs e)
{
btnCancelAdd.Visible = false;
//On form load loads the connection to the database and gets all the data from the Clients Tbl in order to display the records
newCon = new System.Data.SqlClient.SqlConnection();
newCon.ConnectionString = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Mitko Bozhilov\\Work Year 6\\Software Eng\\Ass\\MyDataBase\\MyDataBase\\BookingSystemDataBase.mdf;Integrated Security=True; Connect Timeout=30";
dsCustomer = new DataSet();
//a private string to pass the data from the database to.
String sqlGetWhat;
//in order to read the dataabse sql code is used as shown below
sqlGetWhat = "SELECT * From DeliveriesTbl ";
try
{
//open the database connection
newCon.Open();
//setting the data adapter and filling it with the information
daCustomer = new System.Data.SqlClient.SqlDataAdapter(sqlGetWhat, newCon);
daCustomer.Fill(dsCustomer, "Deliveries");
//calling move records method created above to display the data inot the text boxes
MoveRecords();
countrec = dsCustomer.Tables["Deliveries"].Rows.Count;
}
catch
{
//message if there is no connection with the database
MessageBox.Show("no connection ");
}
newCon.Close();
}
public void addPart(Part part)
{
//Need to get correct inHouse or Outsourced information
Console.WriteLine(inHouseID);
Console.WriteLine(outSourcedID);
Console.WriteLine(machineID);
Console.WriteLine(companyName);
SqlConnection con = new System.Data.SqlClient.SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB; AttachDbFilename=" + Application.StartupPath + "\\DB.mdf; Integrated Security=True");
SqlCommand command = con.CreateCommand();
con.Open();
SqlTransaction transaction;
// Start a local transaction.
transaction = con.BeginTransaction();
// Must assign both transaction object and connection
// to Command object for a pending local transaction
command.Connection = con;
command.Transaction = transaction;
command.CommandText =
"INSERT INTO [dbo].[partTable] ([partID], [name], [price], [inStock], [min], [max], [inHouse], [outsourced] ,[companyName],[machineID]) VALUES (@partID, @name, @price, @inStock, @min, @max, @inHouseID,@outSourcedID,@companyName,@machineID); SELECT partID, name, price, inStock, min, max, inHouse, outsourced ,companyName,machineID FROM partTable WHERE(partID = @partID)";
command.Parameters.AddWithValue("@partID", part.getParttID());
command.Parameters.AddWithValue("@name", part.getName());
command.Parameters.AddWithValue("@price", part.getPrice());
command.Parameters.AddWithValue("@inStock", part.getInStock());
command.Parameters.AddWithValue("@min", part.getMin());
command.Parameters.AddWithValue("@max", part.getMax());
command.Parameters.AddWithValue("@inHouseID", inHouseID);
command.Parameters.AddWithValue("@outSourcedID", outSourcedID);
command.Parameters.AddWithValue("@companyName", companyName);
command.Parameters.AddWithValue("@machineID", machineID);
command.ExecuteNonQuery();
transaction.Commit();
con.Close();
}
public IEnumerable GetList(DateTime Fromdate, DateTime Todate, decimal?WarehouseId)
{
// var lst = _context.Database.SqlQuery<ProductInfoViewModel>(
//"usp_BaoCaoXuatNhapTon").ToList();
var List = new List <ProductInfoViewModel>();
try
{
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.CommandText = "usp_BaoCaoXuatNhapTonDay";
cmd.Parameters.AddWithValue("@FromDate", Fromdate);
cmd.Parameters.AddWithValue("@ToDate", Todate);
cmd.Parameters.AddWithValue("@WarehouseId", WarehouseId);
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
// do
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var v = ListViewDay(dr);
List.Add(v);
}
}
conn.Close();
}
}
return(List);
}
catch
{
return(List);
}
}
bool Completed; //did the translation complete?
public DataTranslator(DataSet ds, ImportTemplateSettings ImportSettings, List <String> tl, string gb)
{
Data2Translate = ds;
TemplateSettings = ImportSettings;
List2Translate = tl;
GrowerBlock = gb;
Completed = false; //Did tranlation complete? Not yet.
try
{
//get Commodity data from database
string connString = Properties.Settings.Default.ConnectionString;
string query = "select [Data_Column_Name],[Description],[Value], [Custom_Value] FROM Translation_Validation_Table"; // WHERE Famous_Validate = 1";
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn);
conn.Open();
DataSet commodityDataSet = new DataSet();
// create data adapter
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
// this will query the database and return the result to your datatable
da.Fill(commodityDataSet);
conn.Close();
da.Dispose();
CommodityTable = commodityDataSet.Tables[0]; //set the translation table
commodityDataSet.Dispose();
}
catch (Exception e)
{
MessageBox.Show("There was an error while trying to load the Commodity data for translation. \n" +
" Note what happened and contact administrator for help or see error log. \n");
Error_Logging el = new Error_Logging("There was an error while trying to load theCommodity data for translation. \n" + e);
ds.Dispose();
return;
}
}
private void button1_Click(object sender, EventArgs e)
{
name = textBox1.Text;
if (name == "")
{
MessageBox.Show(@"Не заполнено поле 'Название банка'!", @"Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
label3.Visible = false;
dataGridView1.Visible = false;
return;
}
label3.Visible = true;
dataGridView1.Visible = true;
using (SqlConnection cn = new System.Data.SqlClient.SqlConnection())
{
cn.ConnectionString = address;
try
{
cn.Open();
}
catch
{
MessageBox.Show(@"Нет соединения с базой данных. Повторите запрос позднее!", @"Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string strSql = String.Format(@"SELECT t1.Name as 'NameService', t1.MinRequirement as 'MinRequirement', t1.Description as 'Description', t2.Name as 'NameBank' FROM Bank t2 INNER JOIN Service t1 ON t1.Id_bank=t2.Id_bank WHERE t2.Name = '{0}'", name);
SqlCommand cmd = new SqlCommand(strSql, cn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
dataset.Reset();
adapter.Fill(dataset);
cn.Close();
dataGridView1.AutoGenerateColumns = true;
bind.DataSource = dataset.Tables[0];
dataGridView1.DataSource = bind;
}
}
private static void LogToDatabase(string message, Enums.LogTypes logTypeId)
{
System.Data.SqlClient.SqlConnection cnn = null;
try
{
cnn = new System.Data.SqlClient.SqlConnection(Configurations.Database.ConnectionString);
var cmd = new System.Data.SqlClient.SqlCommand
{
CommandText = "SP_ErroLogs_Insert",
CommandType = System.Data.CommandType.StoredProcedure,
Connection = cnn
};
cmd.Parameters.AddWithValue("@ApplicationName", ApplicationName);
cmd.Parameters.AddWithValue("@LogTypeId", logTypeId);
cmd.Parameters.AddWithValue("@ErrorDescription", message);
cnn.Open();
cmd.ExecuteScalar();
}
catch (Exception)
{
// ignored
}
finally
{
try
{
if (cnn != null)
{
cnn.Close();
}
}
catch (Exception)
{
// ignored
}
}
}
protected void TextBox4_TextChanged(object sender, EventArgs e)
{
try
{
{
System.Data.DataTable lsp = new System.Data.DataTable();
{
string sql = "select id_loai_san_pham, ten_loai_san_pham from loai_san_pham where cap_do_loai_san_pham = " + (int.Parse(TextBox4.Text) - 1);
WebApplication2.YNNSHOP56131778.CONGFIG.connect cnt = new WebApplication2.YNNSHOP56131778.CONGFIG.connect();
System.Data.SqlClient.SqlConnection ketnoi = new System.Data.SqlClient.SqlConnection(connect.getconnect());
System.Data.SqlClient.SqlCommand lenh = new System.Data.SqlClient.SqlCommand(sql, ketnoi);
ketnoi.Open();
System.Data.SqlClient.SqlDataAdapter data = new System.Data.SqlClient.SqlDataAdapter(lenh);
data.Fill(lsp);
ketnoi.Close();
for (int i = 0; i < lsp.Rows.Count; i++)
{
lsp.Rows[i][1] = mH.Base64Decode(lsp.Rows[i][1].ToString());
}
if ((int.Parse(TextBox4.Text)) == 0)
{
System.Data.DataRow row;
row = lsp.NewRow();
row[0] = 0;
row[1] = "cao nhất";
lsp.Rows.Add(row);
}
DropDownList1.DataSource = lsp;
DropDownList1.DataTextField = "ten_loai_san_pham";
DropDownList1.DataValueField = "id_loai_san_pham";
DropDownList1.DataBind();
}
}
}
catch (Exception x)
{
}
}
public ActionResult Cancel(int id)
{
OrderReturnModel model = _context.OrderReturnModel
.Where(p => p.OrderReturnMasterId == id)
.FirstOrDefault();
var Resuilt = "";
if (model == null)
{
Resuilt = "Không tìm thấy đơn hàng yêu cầu !";
}
else
{
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.CommandText = "usp_SellReturnCanceled";
cmd.Parameters.AddWithValue("@OrderReturnMasterId", model.OrderReturnMasterId);
cmd.Parameters.AddWithValue("@DeletedDate", DateTime.Now);
AccountModel Account = _context.AccountModel.Where(p => p.UserName == model.CreatedAccount).FirstOrDefault();
model.DeletedEmployeeId = Account.EmployeeId;
cmd.Parameters.AddWithValue("@DeletedAccount", currentAccount.UserName);
cmd.Parameters.AddWithValue("@DeletedEmployeeId", model.DeletedEmployeeId);
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
//_context.Entry(model).State = System.Data.Entity.EntityState.Modified;
//_context.SaveChanges();
Resuilt = "success";
}
return(Json(Resuilt, JsonRequestBehavior.AllowGet));
}
