public void UpdateSnap(SnapImage MySnapImage) { /* * SqlCommand MySqlCommand = new SqlCommand(commandText, MySqlConnection); * * MySqlConnection.Open(); * * SqlCommand updatecmd = new SqlCommand("UPDATE Products set ProductName=@ProductName,CategoryID=@CategoryID,Price=@Price,InStore=@InStore,Description=@Description where ProductID=@ProductID", conn); * * updatecmd.Parameters.Add(new SqlParameter("@ProductName", pro.ProductName)); * * updatecmd.Parameters.Add(new SqlParameter("CategoryID", pro.CategoryID)); * * updatecmd.Parameters.Add(new SqlParameter("@Price", pro.Price)); * * updatecmd.Parameters.Add(new SqlParameter("@InStore", pro.InStore)); * * updatecmd.Parameters.Add(new SqlParameter("@Description", pro.Description)); * * updatecmd.Parameters.Add(new SqlParameter("@ProductID", pro.ProductID)); * * conn.Open(); * * updatecmd.ExecuteNonQuery(); * * conn.Close(); */ }
public SnapImage FindSnapForSnapID(string LockIDStr, string SnapID, string InConnectionString) { //List<SnapImage> MyAllSnapImage = new List<SnapImage>(); if (InConnectionString != null) { ConnectionString = InConnectionString; } ; SqlConnection MySqlConnection = new SqlConnection(ConnectionString); //string commandText = "select * from LockKey where LockID='"+LockIDStr+"'"; //string commandText = "select SnapID,SnapTypeID,SnapUUID,ViewFlag,Date from " + MySQLViewName + " where SnapID="+SnapID+ " and Image is not null "; string MySQLViewName = "snap" + LockIDStr; string commandText = "select SnapID,SnapTypeID,SnapUUID,ViewFlag,CreateTime from " + MySQLViewName + " where SnapID=" + SnapID; SqlCommand MySqlCommand = new SqlCommand(commandText, MySqlConnection); MySqlConnection.Open(); SnapImage AnySnapImage = null; SqlDataReader MySqlDataReader = MySqlCommand.ExecuteReader(); while (MySqlDataReader.Read()) { AnySnapImage = new SnapImage(); AnySnapImage.SnapID = (long)MySqlDataReader["SnapID"]; AnySnapImage.SnapTypeID = (int)MySqlDataReader["SnapTypeID"]; AnySnapImage.SnapUUID = (string)MySqlDataReader["SnapUUID"]; AnySnapImage.ViewFlag = (int)MySqlDataReader["ViewFlag"]; AnySnapImage.CreateTime = (DateTime)MySqlDataReader["CreateTime"]; //MyAllSnapImage.Add(AnySnapImage); break; } MySqlDataReader.Close(); MySqlConnection.Close(); return(AnySnapImage); }
public List <SnapImage> LoadAllSnap(string LockIDStr, string InConnectionString) { List <SnapImage> MyAllSnapImage = new List <SnapImage>(); if (InConnectionString != null) { ConnectionString = InConnectionString; } SqlConnection MySqlConnection = new SqlConnection(ConnectionString); //string commandText = "select * from LockKey where LockID='"+LockIDStr+"'"; string MySQLViewName = "snap" + LockIDStr; string commandText = "select SnapID,SnapTypeID,SnapUUID,ViewFlag,CreateTime from " + MySQLViewName + " where SnapID > 0 and Image is not null ORDER BY SnapID DESC"; SqlCommand MySqlCommand = new SqlCommand(commandText, MySqlConnection); MySqlConnection.Open(); SqlDataReader MySqlDataReader = MySqlCommand.ExecuteReader(); while (MySqlDataReader.Read()) { SnapImage AnySnapImage = new SnapImage(); AnySnapImage.SnapID = (long)MySqlDataReader["SnapID"]; AnySnapImage.SnapTypeID = (int)MySqlDataReader["SnapTypeID"]; AnySnapImage.SnapUUID = (string)MySqlDataReader["SnapUUID"]; AnySnapImage.ViewFlag = (int)MySqlDataReader["ViewFlag"]; AnySnapImage.CreateTime = (DateTime)MySqlDataReader["CreateTime"]; MyAllSnapImage.Add(AnySnapImage); } MySqlDataReader.Close(); MySqlConnection.Close(); return(MyAllSnapImage); }
public void DeleteSnap(SnapImage MySnapImage) { /* * string ConnectionString = "Server=VMXPSP3_LGJ;database=CloudLock;uid=sa;pwd=123456"; * SqlConnection MySqlConnection = new SqlConnection(ConnectionString); * * string MySQLViewName = "key" + MyLockKey.LockID; * string MyCommandText = "delete from " + MySQLViewName + " where Number=@Number"; * * SqlCommand MySqlCommand = new SqlCommand(MyCommandText, MySqlConnection); * * MySqlCommand.Parameters.Add(new SqlParameter("@Number", MyLockKey.LockKeyID)); * * MySqlConnection.Open(); * * MySqlCommand.ExecuteNonQuery(); * * MySqlConnection.Close(); * */ }
public int InsertSnap(SnapImage MySnapImage, ref long SnapID) { SqlConnection MySqlConnection = new SqlConnection(ConnectionString); SqlCommand MySqlCommand = new SqlCommand("AddSnapImage", MySqlConnection); MySqlCommand.CommandType = CommandType.StoredProcedure; MySqlConnection.Open(); SqlParameter parm = new SqlParameter(); parm.ParameterName = "@LockID"; parm.Value = MySnapImage.LockID;// LockID; MySqlCommand.Parameters.Add(parm); parm = new SqlParameter(); parm.ParameterName = "@SnapTypeID"; parm.Value = MySnapImage.SnapTypeID;// SnapTypeID;//0,1,2 MySqlCommand.Parameters.Add(parm); parm = new SqlParameter(); parm.ParameterName = "@SnapUUID"; parm.Value = MySnapImage.SnapUUID;// SnapUUID; MySqlCommand.Parameters.Add(parm); MySqlCommand.Parameters.Add(new SqlParameter("@ReturnValue", SqlDbType.Int)); MySqlCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.Output; MySqlCommand.Parameters.Add(new SqlParameter("@ReturnSnapID", SqlDbType.BigInt)); MySqlCommand.Parameters["@ReturnSnapID"].Direction = ParameterDirection.Output; int MyCount = MySqlCommand.ExecuteNonQuery(); int ReturnValue = (int)MySqlCommand.Parameters["@ReturnValue"].Value; SnapID = (long)MySqlCommand.Parameters["@ReturnSnapID"].Value; MySqlConnection.Close(); return(ReturnValue); }
public void InsertSnap(SnapImage MySnapImage) { /* * string ConnectionString = "Server=VMXPSP3_LGJ;database=CloudLock;uid=sa;pwd=123456"; * SqlConnection MySqlConnection = new SqlConnection(ConnectionString); * string MySQLViewName = "key" + MyLockKey.LockID; * string MyCommandText = "insert into "+MySQLViewName+" (LockID,Number, Name, KeyStr, Date) values (@LockID,@Number, @Name, @KeyStr, @Date)"; * * SqlCommand MySqlCommand = new SqlCommand(MyCommandText, MySqlConnection); * MySqlCommand.Parameters.Add(new SqlParameter("@LockID", MyLockKey.LockID)); * MySqlCommand.Parameters.Add(new SqlParameter("@Number", MyLockKey.LockKeyID)); * MySqlCommand.Parameters.Add(new SqlParameter("@Name", MyLockKey.OwerName)); * MySqlCommand.Parameters.Add(new SqlParameter("@KeyStr", MyLockKey.KeyString)); * MySqlCommand.Parameters.Add(new SqlParameter("@Date", MyLockKey.GetCreateTime)); * * * MySqlConnection.Open(); * * MySqlCommand.ExecuteNonQuery(); * * MySqlConnection.Close(); * */ }
public List <SnapImage> LoadAllSnap(string LockIDStr, string InConnectionString, string SnapTypeID, string TimeRange) { List <SnapImage> MyAllSnapImage = new List <SnapImage>(); if (InConnectionString != null) { ConnectionString = InConnectionString; } SqlConnection MySqlConnection = new SqlConnection(ConnectionString); //string commandText = "select * from LockKey where LockID='"+LockIDStr+"'"; string MySQLViewName = "snap" + LockIDStr; //---1.------------------------------------------- string SnapTypeIDFlag = ""; if (SnapTypeID == "0") { SnapTypeIDFlag = "and SnapTypeID > -1"; } if (SnapTypeID == "1") { SnapTypeIDFlag = "and SnapTypeID= 0"; } if (SnapTypeID == "2") { SnapTypeIDFlag = "and SnapTypeID=1"; } if (SnapTypeID == "3") { SnapTypeIDFlag = "and SnapTypeID=2"; } //--2.------------------------------------------------- string DateIDFlag = ""; string NowDateStr = DateTime.Now.ToString(); NowDateStr = NowDateStr.Substring(0, NowDateStr.IndexOf(" ")); string AddSubNowDateStr = NowDateStr.Replace("-", ""); //Select DATEADD(DAY,1,'20130101') if (TimeRange == "0")//当天 { DateIDFlag = " and CreateTime>='" + NowDateStr + "'"; } if (TimeRange == "1")//7天之内 { AddSubNowDateStr = "(Select DATEADD(DAY,-7,'" + AddSubNowDateStr + "')) "; DateIDFlag = " and CreateTime>=" + AddSubNowDateStr; } if (TimeRange == "2")//10天之内 { AddSubNowDateStr = "(Select DATEADD(DAY,-10,'" + AddSubNowDateStr + "')) "; DateIDFlag = " and CreateTime>=" + AddSubNowDateStr; } if (TimeRange == "3")//30天之内 { AddSubNowDateStr = "(Select DATEADD(DAY,-30,'" + AddSubNowDateStr + "')) "; DateIDFlag = " and CreateTime>=" + AddSubNowDateStr; } if (TimeRange == "4")//2*30天之内 { AddSubNowDateStr = "(Select DATEADD(DAY,-60,'" + AddSubNowDateStr + "')) "; DateIDFlag = " and CreateTime>=" + AddSubNowDateStr; } if (TimeRange == "5")//3*30天之内 { AddSubNowDateStr = "(Select DATEADD(DAY,-90,'" + AddSubNowDateStr + "')) "; DateIDFlag = " and CreateTime>=" + AddSubNowDateStr; } if (TimeRange == "6")//6*30天之内 { AddSubNowDateStr = "(Select DATEADD(DAY,-180,'" + AddSubNowDateStr + "')) "; DateIDFlag = " and CreateTime>=" + AddSubNowDateStr; } if (TimeRange == "7")//一年之内 { AddSubNowDateStr = "(Select DATEADD(DAY,-365,'" + AddSubNowDateStr + "')) "; DateIDFlag = " and CreateTime>=" + AddSubNowDateStr; } if (TimeRange == "8")//三年之内 { //string TempDateStr = "-1095"; //AddSubNowDateStr = "(Select DATEADD(DAY,"+TempDateStr+",'" + AddSubNowDateStr + "')) "; AddSubNowDateStr = "(Select DATEADD(DAY,-1095,'" + AddSubNowDateStr + "')) "; DateIDFlag = " and CreateTime>=" + AddSubNowDateStr; } if (TimeRange == "9")//五年之内 { //string TempDateStr = "-1825"; //AddSubNowDateStr = "(Select DATEADD(DAY," + TempDateStr + ",'" + AddSubNowDateStr + "')) "; AddSubNowDateStr = "(Select DATEADD(DAY,-1825,'" + AddSubNowDateStr + "')) "; DateIDFlag = " and CreateTime>=" + AddSubNowDateStr; } if (TimeRange == "10")//全部 { DateIDFlag = ""; } // string commandText = "select SnapID,SnapTypeID,SnapUUID,ViewFlag,Date from " + MySQLViewName + " where SnapID > 0 and Image is not null ORDER BY SnapID DESC"; // string commandText = "select SnapID,SnapTypeID,SnapUUID,ViewFlag,Date from " + MySQLViewName + " where SnapID > 0 and Image is not null "+SnapTypeIDFlag+ DateIDFlag+" ORDER BY SnapID DESC"; string commandText = "select SnapID,SnapTypeID,SnapUUID,ViewFlag,CreateTime from " + MySQLViewName + " where SnapID > 0 " + SnapTypeIDFlag + DateIDFlag + " ORDER BY SnapID DESC"; SqlCommand MySqlCommand = new SqlCommand(commandText, MySqlConnection); MySqlConnection.Open(); SqlDataReader MySqlDataReader = MySqlCommand.ExecuteReader(); while (MySqlDataReader.Read()) { SnapImage AnySnapImage = new SnapImage(); AnySnapImage.SnapID = (long)MySqlDataReader["SnapID"]; AnySnapImage.SnapTypeID = (int)MySqlDataReader["SnapTypeID"]; AnySnapImage.SnapUUID = (string)MySqlDataReader["SnapUUID"]; AnySnapImage.ViewFlag = (int)MySqlDataReader["ViewFlag"]; AnySnapImage.CreateTime = (DateTime)MySqlDataReader["CreateTime"]; MyAllSnapImage.Add(AnySnapImage); } MySqlDataReader.Close(); MySqlConnection.Close(); return(MyAllSnapImage); }