public List <OpenDoor> FindOpenDoorBase(string LockID) { List <OpenDoor> MyAllOpenDoor = new List <OpenDoor>(); SqlConnection MySqlConnection = new SqlConnection(ConnectionString); string MyCommandText = "select * from OpenDoor where LockID='" + LockID + "'"; SqlCommand MySqlCommand = new SqlCommand(MyCommandText, MySqlConnection); MySqlConnection.Open(); SqlDataReader MySqlDataReader = MySqlCommand.ExecuteReader(); OpenDoor AnyOpenDoor = null; while (MySqlDataReader.Read()) { AnyOpenDoor = new OpenDoor(); AnyOpenDoor.LockID = (string)MySqlDataReader["LockID"]; AnyOpenDoor.OwerName = (string)MySqlDataReader["Name"]; AnyOpenDoor.KeyID = (int)MySqlDataReader["KeyID"]; AnyOpenDoor.OpenDate = (DateTime)MySqlDataReader["CreateDate"]; MyAllOpenDoor.Add(AnyOpenDoor); } MySqlDataReader.Close(); MySqlConnection.Close(); return(MyAllOpenDoor); }
public int InsertOpenDoor(OpenDoor MyOpenDoor) { SqlConnection MySqlConnection = new SqlConnection(ConnectionString); string MyCommandText = "insert into OpenDoor (LockID,KeyID,CreateDate,DateStr) values (@LockID,@KeyID,@CreateDate,@DateStr)"; SqlCommand MySqlCommand = new SqlCommand(MyCommandText, MySqlConnection); MySqlCommand.Parameters.Add(new SqlParameter("@LockID", MyOpenDoor.LockID)); MySqlCommand.Parameters.Add(new SqlParameter("@KeyID", MyOpenDoor.KeyID)); MySqlCommand.Parameters.Add(new SqlParameter("@CreateDate", MyOpenDoor.OpenDate)); MySqlCommand.Parameters.Add(new SqlParameter("@DateStr", MyOpenDoor.OpenDateStr)); MySqlConnection.Open(); int RowCount = MySqlCommand.ExecuteNonQuery(); MySqlConnection.Close(); return(RowCount); }
public List <OpenDoor> FindOpenDoorEx(string LockID, string InConnectionString, string Name, string TimeRange) { List <OpenDoor> MyAllOpenDoor = new List <OpenDoor>(); if (InConnectionString != null) { ConnectionString = InConnectionString; } SqlConnection MySqlConnection = new SqlConnection(ConnectionString); string MyNameFilter = ""; string MyDateFilter = ""; string MyAllFilter = ""; if (Name == "全部") { MyNameFilter = ""; } else { MyNameFilter = " AND Name='" + Name + "' "; } //--2.------------------------------------------------- string NowDateStr = DateTime.Now.ToString(); NowDateStr = NowDateStr.Substring(0, NowDateStr.IndexOf(" ")); string AddSubNowDateStr = NowDateStr.Replace("-", ""); //Select DATEADD(DAY,1,'20130101') if (TimeRange == "0") //当天 { MyDateFilter = " and CreateDate>='" + NowDateStr + "'"; } if (TimeRange == "1") //7天之内 { AddSubNowDateStr = "(Select DATEADD(DAY,-7,'" + AddSubNowDateStr + "')) "; MyDateFilter = " and CreateDate>=" + AddSubNowDateStr; } if (TimeRange == "2") //10天之内 { AddSubNowDateStr = "(Select DATEADD(DAY,-10,'" + AddSubNowDateStr + "')) "; MyDateFilter = " and CreateDate>=" + AddSubNowDateStr; } if (TimeRange == "3") //30天之内 { AddSubNowDateStr = "(Select DATEADD(DAY,-30,'" + AddSubNowDateStr + "')) "; MyDateFilter = " and CreateDate>=" + AddSubNowDateStr; } if (TimeRange == "4") //2*30天之内 { AddSubNowDateStr = "(Select DATEADD(DAY,-60,'" + AddSubNowDateStr + "')) "; MyDateFilter = " and CreateDate>=" + AddSubNowDateStr; } if (TimeRange == "5") //3*30天之内 { AddSubNowDateStr = "(Select DATEADD(DAY,-90,'" + AddSubNowDateStr + "')) "; MyDateFilter = " and CreateDate>=" + AddSubNowDateStr; } if (TimeRange == "6") //6*30天之内 { AddSubNowDateStr = "(Select DATEADD(DAY,-180,'" + AddSubNowDateStr + "')) "; MyDateFilter = " and CreateDate>=" + AddSubNowDateStr; } if (TimeRange == "7") //一年之内 { AddSubNowDateStr = "(Select DATEADD(DAY,-365,'" + AddSubNowDateStr + "')) "; MyDateFilter = " and CreateDate>=" + AddSubNowDateStr; } if (TimeRange == "8") //三年之内 { //string TempDateStr = "-1095"; //AddSubNowDateStr = "(Select DATEADD(DAY,"+TempDateStr+",'" + AddSubNowDateStr + "')) "; AddSubNowDateStr = "(Select DATEADD(DAY,-1095,'" + AddSubNowDateStr + "')) "; MyDateFilter = " and CreateDate>=" + AddSubNowDateStr; } if (TimeRange == "9") //五年之内 { //string TempDateStr = "-1825"; //AddSubNowDateStr = "(Select DATEADD(DAY," + TempDateStr + ",'" + AddSubNowDateStr + "')) "; AddSubNowDateStr = "(Select DATEADD(DAY,-1825,'" + AddSubNowDateStr + "')) "; MyDateFilter = " and CreateDate>=" + AddSubNowDateStr; } if (TimeRange == "10") //全部 { MyDateFilter = ""; } //=========================================================================================== if (MyNameFilter == "" && MyDateFilter == "") { MyAllFilter = ""; } else { MyAllFilter = MyNameFilter + MyDateFilter; /* * if (MyNameFilter == "") * { * MyAllFilter = MyAllFilter.Trim(); * MyAllFilter = MyAllFilter.Substring(3); //去掉“and”; * } * */ } //string MyCommandText = "select * from OpenDoor where LockID='" + LockID + "'"; //string MyCommandText = "select Name,KeyID,CreateDate from OpenDoor where " + MyNameFilter + MyDateFilter + " ORDER BY CreateDate DESC"; string MyCommandText = "select Name,KeyID,CreateDate from OpenDoor where LockID=" + "'" + LockID + "'" + MyAllFilter + " ORDER BY CreateDate DESC"; SqlCommand MySqlCommand = new SqlCommand(MyCommandText, MySqlConnection); MySqlConnection.Open(); SqlDataReader MySqlDataReader = MySqlCommand.ExecuteReader(); OpenDoor AnyOpenDoor = null; while (MySqlDataReader.Read()) { AnyOpenDoor = new OpenDoor(); //AnyOpenDoor.LockID = (string)MySqlDataReader["LockID"]; AnyOpenDoor.OwerName = (string)MySqlDataReader["Name"]; AnyOpenDoor.KeyID = (int)MySqlDataReader["KeyID"]; AnyOpenDoor.OpenDate = (DateTime)MySqlDataReader["CreateDate"]; MyAllOpenDoor.Add(AnyOpenDoor); } MySqlDataReader.Close(); MySqlConnection.Close(); return(MyAllOpenDoor); }