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);
}
