コード例 #1
0
ファイル: LockKeyManager.cs プロジェクト: hnlylgj/smarthome
        public LockKey FindOneLockKey(string LockID, int Number)
        {
            SqlConnection MySqlConnection = new SqlConnection(ConnectionString);

            string MySQLViewName = "key" + LockID;
            //string MyCommandText = "select LockID,Number, Name, Date, KeyDateStr from  " + MySQLViewName + "  where StateID=0 and Number=@Number ORDER BY Number DESC";
            string     MyCommandText = "select LockID,Number, Name, Date, KeyDateStr from  " + MySQLViewName + "  where StateID=0 and Number=@Number";
            SqlCommand MySqlCommand  = new SqlCommand(MyCommandText, MySqlConnection);

            MySqlCommand.Parameters.Add(new SqlParameter("@Number", Number));

            MySqlConnection.Open();
            SqlDataReader MySqlDataReader = MySqlCommand.ExecuteReader();
            LockKey       AnyLockKey      = null;

            while (MySqlDataReader.Read())
            {
                AnyLockKey            = new LockKey();
                AnyLockKey.LockID     = (string)MySqlDataReader["LockID"];
                AnyLockKey.LockKeyID  = (int)MySqlDataReader["Number"];
                AnyLockKey.KeyDateStr = (string)MySqlDataReader["KeyDateStr"];
                break;
            }

            MySqlDataReader.Close();

            MySqlConnection.Close();

            return(AnyLockKey);
        }
コード例 #2
0
ファイル: LockKeyManager.cs プロジェクト: hnlylgj/smarthome
        public int ShareXSynchAddLockKey(LockKey MyLockKey)
        {
            //SqlConnection MySqlConnection = new SqlConnection(ConnectionString);
            SqlCommand MySqlCommand = new SqlCommand("SynchAddLockKey", MySqlConnection);

            MySqlCommand.CommandType = CommandType.StoredProcedure;

            MySqlCommand.Parameters.Add(new SqlParameter("@LockID", MyLockKey.LockID));
            MySqlCommand.Parameters.Add(new SqlParameter("@Name", MyLockKey.OwerName));
            MySqlCommand.Parameters.Add(new SqlParameter("@Number", MyLockKey.LockKeyID));
            MySqlCommand.Parameters.Add(new SqlParameter("@CreateDate", MyLockKey.CreateTime));
            MySqlCommand.Parameters.Add(new SqlParameter("@KeyDateStr", MyLockKey.KeyDateStr));

            MySqlCommand.Parameters.Add(new SqlParameter("@ReturnValue", SqlDbType.Int));
            MySqlCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.Output;
            MySqlCommand.Parameters.Add(new SqlParameter("@ReturnNumber", SqlDbType.Int));
            MySqlCommand.Parameters["@ReturnNumber"].Direction = ParameterDirection.Output;

            //MySqlConnection.Open();

            int MyCount      = MySqlCommand.ExecuteNonQuery();
            int ReturnValue  = (int)MySqlCommand.Parameters["@ReturnValue"].Value;
            int ReturnNumber = (int)MySqlCommand.Parameters["@ReturnNumber"].Value;

            //MySqlConnection.Close();
            return(ReturnValue);
        }
コード例 #3
0
ファイル: OpenDoorManager.cs プロジェクト: hnlylgj/smarthome
        public List <LockKey> FindKeyOwerEx(string LockID)
        {
            List <LockKey> MyAllKeyOwerName = new List <LockKey>();

            MyAllKeyOwerName.Add(new LockKey(null, 0, "全部", null));
            SqlConnection MySqlConnection = new SqlConnection(ConnectionString);

            //string MyCommandText = "select Number,Name from  LockKey where LockID='" + LockID + "'" + " and StateID=0";
            string MySQLViewName = "key" + LockID;
            string MyCommandText = "select Number,Name from  " + MySQLViewName + "  where StateID=0 ORDER BY Number DESC";

            SqlCommand MySqlCommand = new SqlCommand(MyCommandText, MySqlConnection);

            MySqlConnection.Open();

            SqlDataReader MySqlDataReader = MySqlCommand.ExecuteReader();

            while (MySqlDataReader.Read())
            {
                LockKey AnyLockKey = new LockKey();
                AnyLockKey.LockKeyID = (int)MySqlDataReader["Number"];
                AnyLockKey.OwerName  = (string)MySqlDataReader["Name"];

                MyAllKeyOwerName.Add(AnyLockKey);
            }

            MySqlDataReader.Close();

            MySqlConnection.Close();

            return(MyAllKeyOwerName);
        }
コード例 #4
0
ファイル: LockKeyManager.cs プロジェクト: hnlylgj/smarthome
        public void UpdateLockKey(LockKey MyLockKey)
        {
            SqlConnection MySqlConnection = new SqlConnection(ConnectionString);
            string        MyCommandText   = "UPDATE LockKey set StateID=0 where LockID=@LockID and Number=@Number";

            SqlCommand MySqlCommand = new SqlCommand(MyCommandText, MySqlConnection);

            MySqlCommand.Parameters.Add(new SqlParameter("@Number", MyLockKey.LockKeyID));

            MySqlCommand.Parameters.Add(new SqlParameter("@LockID", MyLockKey.LockID));

            MySqlConnection.Open();

            MySqlCommand.ExecuteNonQuery();

            MySqlConnection.Close();
        }
コード例 #5
0
ファイル: LockKeyManager.cs プロジェクト: hnlylgj/smarthome
        public void ShareXDeleteLockKeyEx(LockKey MyLockKey)
        {
            //---采用标志删除---------------------------------------------------------------
            //SqlConnection MySqlConnection = new SqlConnection(ConnectionString);
            //MySqlConnection.Open();
            //string MySQLViewName = "key" + MyLockKey.LockID;
            //string MyCommandText = "UPDATE  "+MySQLViewName+"  set StateID=-1 where LockID=@LockID and Number=@Number";

            string     MyCommandText = "UPDATE LockKey set StateID=-1 where LockID=@LockID and Number=@Number";
            SqlCommand MySqlCommand  = new SqlCommand(MyCommandText, MySqlConnection);

            MySqlCommand.Parameters.Add(new SqlParameter("@LockID", MyLockKey.LockID));
            MySqlCommand.Parameters.Add(new SqlParameter("@Number", MyLockKey.LockKeyID));

            MySqlCommand.ExecuteNonQuery();

            //MySqlConnection.Close();
        }
コード例 #6
0
ファイル: LockKeyManager.cs プロジェクト: hnlylgj/smarthome
        public List <LockKey> LoadAllLockKey(string LockIDStr)
        {
            List <LockKey> MyAllLockKey = new List <LockKey>();
            //ConnectionString = "Server=VMXPSP3_LGJ;database=CloudLock;uid=sa;pwd=123456";


            //string commandText = "select * from LockKey where LockID='"+LockIDStr+"'";
            //string commandText = "select * from "+MySQLViewName;
            SqlConnection MySqlConnection = new SqlConnection(ConnectionString);
            string        MySQLViewName   = "key" + LockIDStr;

            string commandText = "select * from  " + MySQLViewName + "  where StateID=0 ORDER BY Number DESC";

            SqlCommand MySqlCommand = new SqlCommand(commandText, MySqlConnection);

            MySqlConnection.Open();

            SqlDataReader MySqlDataReader = MySqlCommand.ExecuteReader();

            while (MySqlDataReader.Read())
            {
                LockKey AnyLockKey = new LockKey();
                AnyLockKey.LockID = (string)MySqlDataReader["LockID"];

                AnyLockKey.LockKeyID = (int)MySqlDataReader["Number"];

                AnyLockKey.OwerName = (string)MySqlDataReader["Name"];

                AnyLockKey.CreateTime = (DateTime)MySqlDataReader["Date"];

                AnyLockKey.KeyString = (string)MySqlDataReader["KeyStr"];

                MyAllLockKey.Add(AnyLockKey);
            }

            MySqlDataReader.Close();

            MySqlConnection.Close();

            return(MyAllLockKey);
        }
コード例 #7
0
ファイル: LockKeyManager.cs プロジェクト: hnlylgj/smarthome
        public void DeleteLockKey(LockKey MyLockKey)
        {
            //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();


            //---采用标志删除---------------------------------------------------------------

            /*
             * SqlConnection MySqlConnection = new SqlConnection(ConnectionString);
             *
             * //string MySQLViewName = "key" + MyLockKey.LockID;
             * //string MyCommandText = "UPDATE  "+MySQLViewName+"  set StateID=-1 where LockID=@LockID and Number=@Number";
             * string MyCommandText = "UPDATE LockKey set StateID=-1 where LockID=@LockID and Number=@Number";
             *
             * SqlCommand MySqlCommand = new SqlCommand(MyCommandText, MySqlConnection);
             *
             * MySqlCommand.Parameters.Add(new SqlParameter("@Number", MyLockKey.LockKeyID));
             *
             * MySqlCommand.Parameters.Add(new SqlParameter("@LockID", MyLockKey.LockID));
             *
             * MySqlConnection.Open();
             *
             * MySqlCommand.ExecuteNonQuery();
             *
             * MySqlConnection.Close();
             * */
        }
コード例 #8
0
ファイル: LockKeyManager.cs プロジェクト: hnlylgj/smarthome
        public void InsertLockKey(LockKey MyLockKey)
        {
            //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();
        }